main.c

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

        || strcmp(table, "user") == 0)
      system = TRUE;
    else
      system = FALSE;
  }

  mysql_free_result(res);
  return system;
}


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

  table_type()

  Returns the table type.

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

  This function returns a string containing table type or NULL if error.

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

static char *table_type(DB_DATABASE handle, char *table, char *settype)
{
  static char buffer[16];

  const char *query =
      "show table status like '&1'";

  const char *update =
	  "alter table `&1` type = &2";

  MYSQL_RES *res;
  MYSQL_ROW row;

  if (settype){
     if (do_query((MYSQL *)handle, "Cannot set table &1 to type &2", &res, update, 2, table, settype))
        return NULL;
  }

  if (do_query((MYSQL *)handle, "Invalid table: &1", &res, query, 1, table)){
    return NULL;
  }

  if ( mysql_num_rows(res) != 1){
     GB.Error("Unable to check table for: &1", table);
     return NULL;
  }

  row = mysql_fetch_row(res);
  /*GB.NewZeroString(&type, row[1]);*/
  strcpy(buffer, row[1]);
  mysql_free_result(res);
  return buffer;
}


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

  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((MYSQL *)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.

  MySql has several different table types: InnoDB and BDB are transaction safe
  whilst HEAP, ISAM, MERGE and MYISAM are not.

 TYPE =

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

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

  DB.Query.Init();
 // type should be BDB HEAP ISAM InnoDB MERGE MRG_MYISAM MYISAM
 //MySql will validate
  DB.Query.Add("CREATE TABLE `");
  DB.Query.Add(table);
  DB.Query.Add("` ( ");

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

    /*DB.Query.Add("`");*/
    DB.Query.Add(fp->name);
    /*DB.Query.Add("`");*/

    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 || fp->length > 255) //mysql supports upto 255 as varchar
          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("`");
      DB.Query.Add(primary[i]);
      DB.Query.Add("`");

      for (fp = fields; fp; fp = fp->next) //Check type of primary field
      {
              if(strcasecmp(fp->name, primary[i]) == 0){
                if (fp->length <= 0 || fp->length > 255){
                        if (fp->type == GB_T_STRING)
                           DB.Query.Add("(255)");
                }
              }
      }
    }

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

  DB.Query.Add(" )");

  if (tabletype)
  {
    DB.Query.Add(" TYPE = ");
    DB.Query.Add(tabletype);
  }

  /* printf("table_create syntax: %s\n", DB.Query.Get());*/
  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.

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

#if 0
static int field_exist(DB_DATABASE handle, char *table, char *field)
{
  MYSQL *conn = (MYSQL *)handle;
  MYSQL_RES *res;
  int exist;
  MYSQL_FIELD *f;

  /* For some reason under mysql 4.0 some fields are not found by
   * mysql_list_fields( conn, table, field )E.g. Create_tmp_table_priv
   * in host */

  res = mysql_list_fields( conn, table, field);
  if (!res){
     GB.Error("Unable to check field: &1", mysql_error(conn));
     return FALSE;
  }

  /* (BM) need more test because field can contain wildcard ! */

  exist = FALSE;
  if (mysql_num_fields(res) == 1)
  {
    f = mysql_fetch_field(res);
    exist = strcmp(field, f->name) == 0;
  }

  mysql_free_result(res);
  return exist;
}
#endif

static int field_exist(DB_DATABASE handle, char *table, char *field)
{
  const char *query =
      "show columns from `&1` like '&2'";

  MYSQL_RES *res;
  int exist;

  if (do_query((MYSQL *)handle, "Unable to check field: &1", &res, query, 2, table, field))
    return FALSE;

  exist = mysql_num_rows(res) == 1;
  mysql_free_result(res);

  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 =
    "show columns from `&1`";

  long i, n;
  MYSQL *conn = (MYSQL *)handle;
  MYSQL_RES *res;
  MYSQL_ROW row;

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

  n = mysql_num_rows(res);

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

    for (i = 0; i < n; i++){
      row = mysql_fetch_row(res);
      GB.NewString(&(*fields)[i], row[0], 0);
    }
  }

  mysql_free_result(res);
  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 =
      "show columns from `&1` like '&2'";

  MYSQL_RES *res;
  MYSQL_ROW row;
  GB_VARIANT def;
  char *val;
  int type;
  long len;

  if (do_query((MYSQL *)handle, "Unable to get field info: &1", &res, query, 2, table, field))
    return TRUE;

  if (mysql_num_rows(res) != 1)
  {
    GB.Error("Unable to find field &1.&2", table, field);
    return TRUE;
  }

  row = mysql_fetch_row(res);

  if (strcmp(row[0], field))
  {
    GB.Error("Unable to find field &1.&2 !", table, field);
    return TRUE;
  }

  info->name = NULL;
  type = conv_string_type(row[1], &len);

  info->type = conv_type(type, len);
  if (info->type == GB_T_STRING)
  {
    info->length = len;
    /* (BM) That's new! a TEXT field has a length of 65535 */
    if (info->length >= 65535)
      info->length = 0;
  }
  else
    info->length = 0;

  info->def._object.type = GB_T_NULL;

  if (!*row[2] || row[2][0] != 'Y')
  {
    def.type = GB_T_VARIANT;
    def.value._object.type = GB_T_NULL;

    val = row[4];

    /* (BM) seems there is a bug in mysql */
    if (info->type == GB_T_DATE && strlen(val) >= 5 && strncmp(val, "00000", 5) == 0)
      val = NULL;

    if (val && *val)
    {
      conv_data(val, &def.value, type, len);
      GB.StoreVariant(&def, &info->def);
    }
  }

  mysql_free_result(res);
  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 =
      "show index from `&1`";

  MYSQL_RES *res;
  MYSQL_ROW row;
  int i, n;

  if (do_query((MYSQL *)handle, "Unable to check index: &1", &res, query, 1, table))
    return FALSE;

  for ( i = 0, n = 0; i < mysql_num_rows(res); i++ )
  {
    row = mysql_fetch_row(res);
    if (strcmp(index, row[2]) == 0)
        n++;
   }

  mysql_free_result(res);
  if (n <= 0){
     return FALSE;
  }

  return TRUE;
}

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

  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 = "show index from `&1`";

  MYSQL_RES *res;
  MYSQL_ROW row;
  long i, n, no_indexes;

  if (do_query((MYSQL *)handle, "Unable to get indexes: &1", &res, query, 1, table))
    return -1;

  for ( i = 0, no_indexes = 0; i < mysql_num_rows(res); i++ )
  {
    /* Count the number of 1st sequences in Seq_in_index to
       give nmber of indexes. row[3] */
    row = mysql_fetch_row(res);
    if ( atoi(row[3]) == 1)
        no_indexes++;
   }


  GB.NewArray(indexes, sizeof(char *), no_indexes);
  mysql_data_seek(res, 0); /* move back to first record */

  for ( i = 0, n = 0; i < mysql_num_rows(res); i++ )
  {
    row = mysql_fetch_row(res);
    if ( atoi(row[3]) == 1 /* Start of a new index */)
       GB.NewString(&(*indexes)[n++], row[2], 0); /* (BM) The name is row[2], not row[4] */
  }

  mysql_free_result(res);
  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)
{
  const char *query =
    "show index from `&1`";

  MYSQL_RES *res;
  MYSQL_ROW row;
  int i, n;

  if (do_query((MYSQL *)handle, "Unable to get index info: &1", &res, query, 2, table, index))
    return TRUE;

  n = mysql_num_rows(res);
  for ( i = 0; i < n; i++ )
  {
    row = mysql_fetch_row(res);
    if ( strcmp( index, row[2]) == 0)
    { /* (BM) With braces, it should work better :-) */
        n = 1;
        break;
    }
   }

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

  info->name = NULL;
  info->unique = strcmp(row[1], "0") == 0;
  info->primary = strcmp("PRIMARY", row[2]) == 0 ? TRUE : FALSE;

  DB.Query.Init();

  i = 0;
  /* (BM) row can be null if we are seeking the last index */
  while ( row && strcmp(index, row[2]) == 0 )
  {
    if (i > 0)
      DB.Query.Add(",");

    DB.Query.Add(row[4]);
    row = mysql_fetch_row(res);
    i++; /* (BM) i must be incremented */
  }

  mysql_free_result(res);
  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((MYSQL *)handle, "Unable to delete index: &1", NULL,
      "drop index `&1`", 1, index);
}

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

  index_create()

  Creates an index.

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