main.c

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

  {
    GB.NewArray(tables, sizeof(char *), count);

    j = 0;

    for (i = 0; i < PQntuples(res1); i++, j++)
      GB.NewString(&((*tables)[j]), PQgetvalue(res1, i, 0), 0);

    for (i = 0; i < PQntuples(res2); i++, j++)
      GB.NewString(&((*tables)[j]), PQgetvalue(res2, i, 0), 0);
  }

  PQclear(res1);
  PQclear(res2);

  return count;
}

static long table_list(DB_DATABASE handle, char ***tables, long version)
{
  //if (version < 70400)
    return table_list_73(handle, tables);
  //else
  //  return table_list_74(handle, tables);
}



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

  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)
{
  const char *query =
    "select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname "
      "from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
        "where pg_cl.relname = lower('&1_pkey') AND pg_cl.oid = pg_ind.indexrelid "
          "and (pg_cl.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) "
          "and pg_att2.attrelid = pg_ind.indexrelid "
          "and pg_att1.attrelid = pg_ind.indrelid "
          "and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
        "order by pg_att2.attnum";

  PGconn *conn = (PGconn *)handle;
  PGresult *res;
  int i;

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

  GB.NewArray(primary, sizeof(char *), PQntuples(res));

  for (i = 0; i < PQntuples(res); i++)
    GB.NewString(&((*primary)[i]), PQgetvalue(res, i, 0), 0);

  PQclear(res);

  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.

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

static int table_is_system(DB_DATABASE handle, char *table, long version)
{
  const char *query =
    "select 1 from pg_class where (relkind = 'r' or relkind = 'v') "
    "and (relname = lower('&1')) "
    "and (relnamespace not in (select oid from pg_namespace where nspname <> 'public'))";

  const char *query1 =
    "select 1 from pg_tables where tablename = lower('&1') and schemaname = 'public'";

  const char *query2 =
    "select 1 from pg_views where viewname = lower('&1') and schemaname = 'public'";

  PGconn *conn = (PGconn *)handle;
  PGresult *res;
  int exist;

  /* check version */
  /*if (version < 70400){ // version 7.3.?
     if (table[0] == 'p' && table[1] == 'g' && table[2] == '_')
        return TRUE;
     else
        return FALSE;
  }*/

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

  exist = PQntuples(res) == 1;
  PQclear(res);

  if (exist)
    return FALSE;

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

  exist = PQntuples(res) == 1;
  PQclear(res);

  if (exist)
    return FALSE;

  return TRUE;
}


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

  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((PGconn *)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)
{
  PGconn *conn = (PGconn *)handle;
  DB_FIELD *fp;
  int comma;
  char *type;
  int i;

  DB.Query.Init();

  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(QUOTE_STRING);*/
    DB.Query.Add(fp->name);
    /*DB.Query.Add(QUOTE_STRING);*/

    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 = "TIMESTAMP"; 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 = "select pg_attribute.attname from pg_class, pg_attribute "
    "where pg_class.relname = lower('&1') "
    "and (pg_class.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) "
    "and pg_attribute.attname = lower('&2') "
    "and pg_attribute.attnum > 0 "
    "and pg_attribute.attrelid = pg_class.oid ";

  PGresult *res;
  int exist;

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

  exist = PQntuples(res) == 1;

  PQclear(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 = "select pg_attribute.attname from pg_class, pg_attribute "
    "where pg_class.relname = lower('&1') "
    "and (pg_class.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) "
    "and pg_attribute.attnum > 0 "
    "and pg_attribute.attrelid = pg_class.oid";

  PGresult *res;
  int i;
  long count;

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

  if (fields)
  {
    GB.NewArray(fields, sizeof(char *), PQntuples(res));

    for (i = 0; i < PQntuples(res); i++)
      GB.NewString(&((*fields)[i]), PQgetvalue(res, i, 0), 0);
  }

  count = PQntuples(res);

  PQclear(res);
  return count;
}


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

  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 =
    "select pg_attribute.attname, pg_attribute.atttypid::int, "
    "pg_attribute.atttypmod, pg_attribute.attnotnull, pg_attrdef.adsrc "
    "from pg_class, pg_attribute "
    "left join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
    "where pg_class.relname = lower('&1') "
    "and (pg_class.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) "
    "and pg_attribute.attname = lower('&2') "
    "and pg_attribute.attnum > 0 "
    "and pg_attribute.attrelid = pg_class.oid";

  PGresult *res;
  Oid type;
  GB_VARIANT def;
  char *val;

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

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

  info->name = NULL;

  type = atoi(PQgetvalue(res, 0, 1));
  info->type = conv_type(type);

  info->length = 0;
  if (info->type == GB_T_STRING)
  {
    info->length = atoi(PQgetvalue(res, 0, 2));
    if (info->length < 0)
      info->length = 0;
    else
      info->length -= 4;
  }

  info->def._object.type = GB_T_NULL;

  if (conv_boolean(PQgetvalue(res, 0, 3)))
  {
    def.type = GB_T_VARIANT;
    def.value._object.type = GB_T_NULL;

    val = PQgetvalue(res, 0, 4);
    if (val && *val)
    {
      switch(type)
      {
        case GB_T_BOOLEAN:
          def.value._boolean.value = (val[1] == 't');
          def.value._boolean.type = GB_T_BOOLEAN;
          break;

        default:
          conv_data(unquote(val), &def.value, type);
      }

      GB.StoreVariant(&def, &info->def);
    }
  }

  PQclear(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 =
    "select pg_class.relname from pg_class, pg_index, pg_class pg_class2 "
    "where pg_class2.relname = lower('&1') "
    "and (pg_class2.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) "
    "and pg_index.indrelid = pg_class2.oid "
    "and pg_index.indexrelid = pg_class.oid "
    "and pg_class.relname = lower('&2')";

  /*const char *query = "select relname from pg_class, pg_index "
    "where pg_class.relname = '&1' "
    "and pg_index.indexrelid = pg_class.oid ";*/

  PGresult *res;
  int exist;

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

  exist = PQntuples(res) == 1;

  PQclear(res);

  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 pg_class.relname from pg_class, pg_index, pg_class pg_class2 "
    "where pg_class2.relname = lower('&1') "
    "and (pg_class2.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) "
    "and pg_index.indrelid = pg_class2.oid "
    "and pg_index.indexrelid = pg_class.oid ";

  PGresult *res;
  int i;
  long count;

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

  if (indexes)
  {
    GB.NewArray(indexes, sizeof(char *), PQntuples(res));

    for (i = 0; i < PQntuples(res); i++)
      GB.NewString(&((*indexes)[i]), PQgetvalue(res, i, 0), 0);
  }

  count = PQntuples(res);
  PQclear(res);

  return count;
}


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

  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 =
    "select indisunique, indisprimary, indexrelid from pg_class, pg_index, pg_class pg_class2 "
    "where pg_class2.relname = lower('&1') "
    "and (pg_class2.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) "
    "and pg_index.indrelid = pg_class2.oid "
    "and pg_index.indexrelid = pg_class.oid "
    "and pg_class.relname = lower('&2')";

  const char *query_field =
    "select pg_att1.attname "
      "from pg_attribute pg_att1, pg_attribute pg_att2, pg_index pg_ind "
        "where pg_ind.indexrelid = &1 "
          "and pg_att2.attrelid = pg_ind.indexrelid "
          "and pg_att1.attrelid = pg_ind.indrelid "
          "and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
        "order by pg_att2.attnum";

  PGresult *res;
  char indexrelid[16];
  int i;

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

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

  info->name = NULL;
  info->unique = conv_boolean(PQgetvalue(res, 0, 0));