main.cpp

Gambas is a graphical development environment based on a Basic interpreter, like Visual Basic. It us

  }


  res->close();

  GB.NewString(&(*tables)[i], "sqlite_master", 0);
  GB.NewString(&(*tables)[++i], "sqlite_temp_master", 0);
  return rows;
}

/*****************************************************************************

  table_primary_key()

  Returns a string representing the primary key of a table.

  <handle> is the database handle.
  <table> is the table name.
  <key> points to a string that will receive the primary key.

  This function returns TRUE if the command has failed, and FALSE if
  everything was OK.

*****************************************************************************/

static int table_primary_key(DB_DATABASE handle, char *table, char ***primary)
{
  char *qindex1 = "PRAGMA index_list('&1')";
  char *qindex2 = "PRAGMA index_info('&1')";

  SqliteDatabase *conn = (SqliteDatabase *)handle;
  Dataset *res;
  int i, n;
  char *sql;

  if (do_query(conn, "Unable to get primary key: &1", &res, qindex1, 1, table))
    return TRUE;

  GB.NewArray(primary, sizeof(char *), 0);

  result_set* r = (result_set*) res->getExecRes();
  //if ((n = r->records.size()) < 1){ //No Indexes found
  //   GB.NewString((char **)GB.Add(primary), "ROWID", 0);
  //   res->close();
  //   return FALSE;
  //}

  n = r->records.size();
  for (i = 0; i < n; i++)
  {
    if (strstr(r->records[i][1].get_asString().data(), "autoindex")){
       GB.NewString( &sql, r->records[i][1].get_asString().data(), 0);
       res->close();

       if (do_query(conn, "Unable to get primary key: &1", &res, qindex2, 1, sql)){
           res->close();
           GB.FreeString(&sql);
           return TRUE;
       }
       GB.FreeString(&sql);

       r = (result_set*) res->getExecRes();
       if ((n = r->records.size()) < 1 ){
                 // No information returned for key
         	 res->close();
	         return TRUE;
       }

       for (i = 0; i < n; i++){
           GB.NewString((char **)GB.Add(primary),
			   r->records[i][2].get_asString().data(),0);
       }
       break;
    }
  }

  res->close();
/*
  if (GB.Count(*primary) == 0){
      GB.NewString((char **)GB.Add(primary), "ROWID", 0);
  }
*/
  return FALSE;
}

/*****************************************************************************

  table_is_system()

  Returns if a table is a system table.

  <handle> is the database handle.
  <table> is the table name.

  This function returns TRUE if the table is a system table, and FALSE if
  not.

  Note: There are only two tables in Sqlite used by the system.
        sqlite_master and sqlite_temp_master
*****************************************************************************/

static int table_is_system(DB_DATABASE handle, char *table, long version)
{
    if (strcmp(table,"sqlite_master") == 0 ||
       strcmp(table, "sqlite_temp_master") == 0)
       return TRUE;
    else
       return FALSE;
}

/*****************************************************************************

  table_type()
  Not Valid in Sqlite

  <handle> is the database handle.
  <table> is the table name.
*****************************************************************************/
static char *table_type(DB_DATABASE handle, char *table, char *type)
{
  if (type)
    GB.Error("SQLite does not have any table types");
  return NULL;
}

/*****************************************************************************

  table_delete()

  Deletes a table.

  <handle> is the database handle.
  <table> is the table name.

  This function returns TRUE if the command has failed, and FALSE if
  everything was OK.

*****************************************************************************/

static int table_delete(DB_DATABASE handle, char *table)
{
  return
    do_query((SqliteDatabase *)handle, "Unable to delete table: &1", NULL,
      "drop table '&1'", 1, table);
}

/*****************************************************************************

  table_create()

  Creates a table.

  <handle> is the database handle.
  <table> is the table name.
  <fields> points to a linked list of field descriptions.
  <key> is the primary key.

  This function returns TRUE if the command has failed, and FALSE if
  everything was OK.

*****************************************************************************/

static int table_create(DB_DATABASE handle, char *table, DB_FIELD *fields, char **primary, char *not_used)
{
  SqliteDatabase *conn = (SqliteDatabase *)handle;
  DB_FIELD *fp;
  int comma;
  char *type;
  int i;

  DB.Query.Init();

  DB.Query.Add("CREATE TABLE ");
  DB.Query.Add(QUOTE_STRING);
  DB.Query.Add(table);
  DB.Query.Add(QUOTE_STRING);
  DB.Query.Add(" ( ");

  comma = FALSE;
  for (fp = fields; fp; fp = fp->next)
  {
    if (comma)
      DB.Query.Add(", ");
    else
      comma = TRUE;

    DB.Query.Add(fp->name);

    switch (fp->type)
    {
      case GB_T_BOOLEAN: type = "BOOL"; break;
      case GB_T_INTEGER: type = "INT4"; break;
      case GB_T_FLOAT: type = "FLOAT8"; break;
      case GB_T_DATE: type = "DATETIME"; break;
      case GB_T_STRING:

        if (fp->length <= 0)
          type = "TEXT";
        else
        {
          sprintf(_buffer, "VARCHAR(%ld)", fp->length);
          type = _buffer;
        }

        break;

      default: type = "TEXT"; break;
    }

    DB.Query.Add(" ");
    DB.Query.Add(type);

    if (fp->def.type != GB_T_NULL)
    {
      DB.Query.Add(" NOT NULL DEFAULT ");
      DB.FormatVariant(&_driver, &fp->def, DB.Query.AddLength);
    }
    else if (DB.StringArray.Find(primary, fp->name) >= 0)
    {
      DB.Query.Add(" NOT NULL ");
    }
  }

  if (primary)
  {
    DB.Query.Add(", PRIMARY KEY (");

    for (i = 0; i < GB.Count(primary); i++)
    {
      if (i > 0)
        DB.Query.Add(",");

      DB.Query.Add(primary[i]);
    }

    DB.Query.Add(")");
  }

  DB.Query.Add(" )");

  return do_query(conn, "Cannot create table: &1", NULL, DB.Query.Get(), 0);
}

/*****************************************************************************

  field_exist()

  Returns if a field exists in a given table

  <handle> is the database handle.
  <table> is the table name.
  <field> is the field name.

  This function returns TRUE if the field exists, and FALSE if not.

*****************************************************************************/

static int field_exist(DB_DATABASE handle, char *table, char *field)
{
  const char *query = "PRAGMA table_info('&1')";

  long i, n;
  SqliteDatabase *conn = (SqliteDatabase *)handle;
  Dataset *res;
  int exist = 0;

  if (do_query(conn, "Unable to find field: &1.&2", &res, query, 2, table, field)){
    return FALSE;
  }

  result_set* r = (result_set*) res->getExecRes();

  n = r->records.size();

  for (i = 0; i < n; i++){
      if (strcmp(field, r->records[i][1].get_asString().data()) == 0)
	      exist++;
  }

  res->close();
  return exist;
}

/*****************************************************************************

  field_list()

  Returns an array containing the name of each field in a given table

  <handle> is the database handle.
  <table> is the table name.
  <fields> points to a variable that will receive the char* array.

  This function returns the number of fields, or -1 if the command has
  failed.

  Be careful: <fields> can be NULL, so that just the count is returned.

*****************************************************************************/

static long field_list(DB_DATABASE handle, char *table, char ***fields)
{
  const char *query = "PRAGMA table_info('&1')";

  long i, n;
  SqliteDatabase *conn = (SqliteDatabase *)handle;
  Dataset *res;

  if (do_query(conn, "Unable to get fields: &1", &res, query, 1, table)){
       return -1;
  }

  result_set* r = (result_set*) res->getExecRes();

  n = r->records.size();

  if (fields) /* (BM) see the function commentary */
  {
    GB.NewArray(fields, sizeof(char *), n);

    for (i = 0; i < n; i++){
      GB.NewString(&(*fields)[i], r->records[i][1].get_asString().data(), 0);
    }
  }

  res->close();
  return n;
}


/*****************************************************************************

  field_info()

  Get field description

  <handle> is the database handle.
  <table> is the table name.
  <field> is the field name.
  <info> points to a structure filled by the function.

  This function returns TRUE if the command has failed, and FALSE if
  everything was OK.

*****************************************************************************/

static int field_info(DB_DATABASE handle, char *table, char *field, DB_FIELD *info)
{
  const char *query = "PRAGMA table_info('&1')";

  Dataset *res;
  GB_VARIANT def;
  char *val;
  SqliteDatabase *conn = (SqliteDatabase *)handle;
  char *_fieldName = NULL;
  char *_fieldType = NULL;
  char *_defaultValue = NULL;
  bool _fieldNotNull = FALSE;
  int i, n;

  if (do_query( conn, "Unable to get fields: &1", &res, query,1, table))
  {
     return TRUE;
  }

  result_set* r = (result_set*) res->getExecRes();
  if ((n = r->records.size()) == 0)
  {
    GB.Error("Unable to find field &1.&2", table, field);
    return TRUE;
  }

  for ( i =0; i < n; i++){
       _fieldName = (char *)r->records[i][1].get_asString().data();

       if (strcmp(_fieldName, field) == 0){
	       _fieldType = (char *)r->records[i][2].get_asString().data();
	       _fieldNotNull = (char *)r->records[i][3].get_asBool();
	       _defaultValue = (char *)r->records[i][4].get_asString().data();
	       break;
       }
  }

  if (strcmp(_fieldName, field))
  {
      GB.Error("Unable to find field &1.&2", table, field);
      return TRUE;
  }

  info->name = NULL;

  info->type = conv_type(GetFieldType(_fieldType, (unsigned int*) &info->length));

  info->def._object.type = GB_T_NULL;

  if ( _fieldNotNull == TRUE )
  {
    def.type = GB_T_VARIANT;
    def.value._object.type = GB_T_NULL;

    val = _defaultValue;

    if (val && *val)
    {
      conv_data(val, &def.value, GetFieldType(_fieldType, (unsigned int*) &info->length));
      GB.StoreVariant(&def, &info->def);
    }
  }

  res->close();
  return FALSE;
}

/*****************************************************************************

  index_exist()

  Returns if an index exists in a given table

  <handle> is the database handle.
  <table> is the table name.
  <field> is the index name.

  This function returns TRUE if the index exists, and FALSE if not.

*****************************************************************************/

static int index_exist(DB_DATABASE handle, char *table, char *index)
{
  const char *query = "select tbl_name from "
	  "( select tbl_name from sqlite_master where type = 'index' and "
	  " name = '&2' union "
	  "select tbl_name from sqlite_temp_master where type = 'index' and "
	  " name = '&2' ) "
	  "where tbl_name = '&1'";

  SqliteDatabase *conn = (SqliteDatabase *)handle;
  Dataset *res;
  int exist;

  if (do_query(conn, "Unable to check table: &1", &res, query, 2, table, index))
	         return FALSE;

  exist = res->num_rows();
  res->close();
  return exist;
}

/*****************************************************************************

  index_list()

  Returns an array containing the name of each index in a given table

  <handle> is the database handle.
  <table> is the table name.
  <indexes> points to a variable that will receive the char* array.

  This function returns the number of indexes, or -1 if the command has
  failed.

  Be careful: <indexes> can be NULL, so that just the count is returned.

*****************************************************************************/

static long index_list(DB_DATABASE handle, char *table, char ***indexes)
{

  const char *query =
   "select name from "
   "( select name from sqlite_master where type = 'index' and tbl_name = '&1' "
   " union select name from sqlite_temp_master where type = 'index' and "
   " tbl_name = '&1')";

  SqliteDatabase *conn = (SqliteDatabase *)handle;
  Dataset *res;
  int no_indexes, i = 0;

  if (do_query(conn, "Unable to get tables: &1", &res, query, 1, table))
        return -1;

  no_indexes = res->num_rows();
  GB.NewArray(indexes, sizeof(char *), no_indexes);

  while ( !res->eof()){
                    //(res->fv("tbl_name").get_asString().data());
    GB.NewString(&(*indexes)[i], res->fv(res->fieldName(0)).get_asString().data(), 0);
    res->next();
    i++;
  }

  res->close();

  return no_indexes;
}

/*****************************************************************************

  index_info()

  Get index description

  <handle> is the database handle.
  <table> is the table name.
  <field> is the index name.
  <info> points to a structure filled by the function.

  This function returns TRUE if the command has failed, and FALSE if
  everything was OK.

*****************************************************************************/

static int index_info(DB_DATABASE handle, char *table, char *index, DB_INDEX *info)
{
  /* sqlite indexes are unique to the database, and not to the table */

  char *qindex1 = "PRAGMA index_list('&1')";
  char *qindex2 = "PRAGMA index_info('&1')";

  SqliteDatabase *conn = (SqliteDatabase *)handle;
  Dataset *res;
  int i, j, n;

  if (do_query(conn, "Unable to get index info for table: &1", &res, qindex1, 1, table))
    return TRUE;

  result_set* r = (result_set*) res->getExecRes();
  if (( n = r->records.size()) == 0){
     /* no indexes found */
     res->close();
    GB.Error("Unable to find index &1.&2", table, index);
     return TRUE;
  }

  for (i = 0, j = 0; i < n; i++)
  { /* Find the required index */
    if ( strcmp( index, r->records[i][1].get_asString().data()) == 0){
	    j++;
	    break;
    }
  }

  if ( j == 0)
  {
      GB.Error("Unable to find index &1.&2", table, index);
      return TRUE;
  }

  info->name = NULL;
  info->unique = strncmp("1",r->records[i][2].get_asString().data(),1) == 0 ? TRUE : FALSE;
  info->primary = strstr(r->records[i][1].get_asString().data(), "autoindex")
	   != NULL ? TRUE : FALSE;

  DB.Query.Init();

  if (do_query(conn, "Unable to get index info for : &1", &res, qindex2, 1, index)){
    res->close();
    return TRUE;
  }

  r = (result_set*) res->getExecRes();
  n = r->records.size();
  i = 0;
  /* (BM) row can be null if we are seeking the last index */
  while ( i < n )
  {
    if (i > 0)
      DB.Query.Add(",");

    /* Get fields in key */
    DB.Query.Add((char *)r->records[i][2].get_asString().data());
    i++;
  }

  res->close();
  info->fields = DB.Query.GetNew();

  return FALSE;
}

/*****************************************************************************

  index_delete()

  Deletes an index.

  <handle> is the database handle.
  <table> is the table name.
  <index> is the index name.

  This function returns TRUE if the command has failed, and FALSE if
  everything was OK.

*****************************************************************************/

static int index_delete(DB_DATABASE handle, char *table, char *index)
{
  return
    do_query((SqliteDatabase *)handle, "Unable to delete index: &1", NULL,
      "drop index '&1'", 1, index);
}

/*****************************************************************************

  index_create()