main.c

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

  *count = PQntuples(res);

  info->nfield = PQnfields(res);
}


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

  query_release()

  Free the info structure filled by query_init() and the result handle.

  <result> is the handle of the query result.
  <info> points to the info structure.

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

static void query_release(DB_RESULT result, DB_INFO *info)
{
  PQclear((PGresult *)result);
}


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

  query_fill()

  Fill a result buffer with the value of each field of a record.

  <result> is the handle of the result.
  <pos> is the index of the record in the result.
  <buffer> points to an array having one element for each field in the
  result.

  This function must use GB.StoreVariant() to store the value in the
  buffer.

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

static void query_fill(DB_RESULT result, int pos, GB_VARIANT_VALUE *buffer, int next)
{
  PGresult *res = (PGresult *)result;
  int i;
  char *data;
  GB_VARIANT value;

  for (i = 0; i < PQnfields(res); i++)
  {
    data = PQgetvalue(res, pos, i);

    value.type = GB_T_VARIANT;
    value.value._object.type = GB_T_NULL;

    if (!PQgetisnull(res, pos, i))
      conv_data(data, &value.value, PQftype(res, i));

    GB.StoreVariant(&value, &buffer[i]);
  }
}


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

  field_name()

  Return the name of a field in a result from its index.

  <result> is the result handle.
  <field> is the field index.

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

static char *field_name(DB_RESULT result, int field)
{
  return PQfname((PGresult *)result, field);
}


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

  field_index()

  Return the index of a field in a result from its name.

  <result> is the result handle.
  <name> is the field name.
  <handle> is needed by this driver to enable table.field syntax

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

static int field_index(DB_RESULT Result, char *name, DB_DATABASE handle, long version)
{
  PGresult *result = (PGresult *)Result;
  PGconn *conn = (PGconn *)handle;

  char *fld;
  int index;
  char *table;
  int numfields, oid;
  char *qfield =
    "select oid from pg_class where relname = lower('&1') "
    "and (relnamespace not in (select oid from pg_namespace where nspname = 'information_schema'))";

  fld = strchr(name, (int)FLD_SEP);

  if (fld){
     //Includes table identity.
     //This feature is only available for 7.4 onwards
     /* check version */

     if (version > 70399){ // version 7.4.?
        fld[0] = '.';
        GB.NewString(&table, name, fld - name);
        fld = fld + 1;

        /* Need to find the OID for the table */
        PGresult *oidres;

        if (do_query(conn, "Unable to get OID for table &1", &oidres, qfield, 1, table)){
	    GB.FreeString(&table);
	    return -1;
        }

        if ( PQntuples(oidres) != 1){
        /* Not unique table identifier */
           GB.Error("Table &1 not unique in pg_class", table);
           PQclear(oidres);
           GB.FreeString(&table);
           return -1;
        }

 	oid = atoi(PQgetvalue(oidres, 0, 0));
        PQclear(oidres);
	numfields = PQnfields((PGresult *)result);
        index = PQfnumber((PGresult *)result, fld);

	if (PQftable((PGresult *)result, index) != oid){
	   numfields = PQnfields((PGresult *)result);
	   while ( ++index < numfields){
	       if (strcasecmp(PQfname((PGresult *)result, index),
				       fld) == 0){ //Check Fieldname
	          if (PQftable((PGresult *)result, index) == oid){ //check oid
		     break; // is the required table oid
	          }
	       }
	   }

	   if ( index == numfields ){
	   /* field not found for OID */
              GB.Error("Field &1.&2 not found", table, fld);
	      GB.FreeString(&table);
	      return -1;
	   }

	}
  	GB.FreeString(&table);
     }
     else {
	  /* Using tablename and fieldname in a non supported
	   * version */
           GB.Error("Field &1.&2 not supported below 7.4.1", table, fld);
	   return -1;
     }
  }
  else {
       index = PQfnumber((PGresult *)result, name);
  }

  return index;
}


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

  field_type()

  Return the Gambas type of a field in a result from its index.

  <result> is the result handle.
  <field> is the field index.

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

static GB_TYPE field_type(DB_RESULT result, int field)
{
  return conv_type(PQftype((PGresult *)result, field));
}


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

  field_length()

  Return the length of a field in a result from its index.

  <result> is the result handle.
  <field> is the field index.

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

static int field_length(DB_RESULT result, int field)
{
  GB_TYPE type = conv_type(PQftype((PGresult *)result, field));
  int len;

  if (type != GB_T_STRING)
    return 0;


  len = PQfmod((PGresult *)result, field);
  if (len < 0)
    len = 0;
  else
    len -= 4;

  return len;
}


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

  begin_transaction()

  Begin a transaction.

  <handle> is the database handle.

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

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

static int begin_transaction(DB_DATABASE handle)
{
  return do_query((PGconn *)handle, "Unable to begin transaction: &1", NULL, "BEGIN", 0);
}


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

  commi_transaction()

  Commit a transaction.

  <handle> is the database handle.

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

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

static int commit_transaction(DB_DATABASE handle)
{
  return do_query((PGconn *)handle, "Unable to commit transaction: &1", NULL, "COMMIT", 0);
}


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

  rollback_transaction()

  Rolllback a transaction.

  <handle> is the database handle.

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

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

static int rollback_transaction(DB_DATABASE handle)
{
  return do_query((PGconn *)handle, "Unable to rollback transaction: &1", NULL, "ROLLBACK", 0);
}


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

  table_init()

  Initialize an info structure from table fields.

  <handle> is the database handle.
  <table> is the table name.
  <info> points at the info structure.

  This function must initialize the following info fields:
   - info->nfield must contain the number of fields in the table.
   - info->fields is a char*[] pointing at the name of each field.
   - info->types is a GB_TYPE[] giving the gambas type of each field.

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

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

static int table_init(DB_DATABASE db, char *table, DB_INFO *info)
{
  char *qfield =
    "select pg_attribute.attname, pg_attribute.atttypid::int,pg_attribute.atttypmod "
      "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 ";

  PGconn *conn = (PGconn *)db;
  PGresult *res;
  int i, n;
  DB_FIELD *f;

  /* Nom de la table */

  GB.NewString(&info->table, table, 0);

  /* Liste des champs */

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

  info->nfield = n = PQntuples(res);
  if (n == 0)
  {
    PQclear(res);
    return TRUE;
  }

  GB.Alloc((void **)&info->field, sizeof(DB_FIELD) * n);

  for (i = 0; i < n; i++)
  {
    f = &info->field[i];
    GB.NewString(&f->name, PQgetvalue(res, i, 0), 0);
    f->type = conv_type(atol(PQgetvalue(res, i, 1)));
    f->length = 0;
    if (f->type == GB_T_STRING)
    {
      f->length = atoi(PQgetvalue(res, i, 2));
      if (f->length < 0)
        f->length = 0;
      else
        f->length -= 4;
    }
    /*printf("fields[%d] = '%s'  types[%d] = %ld\n", i, info->fields[i], i, info->types[i]);*/
  }

  PQclear(res);
  return FALSE;
}


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

  table_index()

  Initialize an info structure from table primary index.

  <handle> is the database handle.
  <table> is the table name.
  <info> points at the info structure.

  This function must initialize the following info fields:
   - info->nindex must contain the number of fields in the primary index.
   - info->index is a int[] giving the index of each index field in
     info->fields.

  This function must be called after table_init().

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

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

static int table_index(DB_DATABASE db, char *table, DB_INFO *info)
{
  const char *qindex =
    "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 *)db;
  PGresult *res;
  int i, j, n;

  /* Index primaire */

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

  n = info->nindex = PQntuples(res);

  if (n <= 0)
  {
    GB.Error("Table '&1' has no primary index", table);
    PQclear(res);
    return TRUE;
  }

  GB.Alloc((void **)&info->index, sizeof(int) * n);

  for (i = 0; i < n; i++)
  {
    for (j = 0; j < info->nfield; j++)
    {
      if (strcasecmp(info->field[j].name, PQgetvalue(res, i, 0)) == 0)
      {
        info->index[i] = j;
        /*printf("index[%d] = %d\n", i, j);*/
        break;
      }
    }
  }

  PQclear(res);

  return FALSE;
}


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

  table_release()

  Free the info structure filled by table_init() and/or table_index()

  <handle> is the database handle.
  <info> points at the info structure.

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

static void table_release(DB_DATABASE db, DB_INFO *info)
{
  /* All is done outside the driver */
}


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

  table_exist()

  Returns if a table exists

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

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

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

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

  const char *query74_1 =
    "select tablename from pg_tables where schemaname <> 'information_schema' and tablename = lower('&1')";

  const char *query74_2 =
    "select viewname from pg_views where schemaname <> 'information_schema' and viewname = lower('&1')";

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

  if (1) //version < 70400)
  {
    if (do_query(conn, "Unable to check table: &1", &res, query73, 1, table))
      return FALSE;
  }
  else
  {
    if (do_query(conn, "Unable to check table: &1", &res, query74_1, 1, table))
      return FALSE;

    if (PQntuples(res) == 0)
    {
      PQclear(res);
      if (do_query(conn, "Unable to check table: &1", &res, query74_2, 1, table))
        return TRUE;
    }
  }

  exist = PQntuples(res) == 1;

  PQclear(res);

  return exist;
}



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

  table_list()

  Returns an array containing the name of each table in the database

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

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

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

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

static long table_list_73(DB_DATABASE handle, char ***tables)
{
  const char *query =
    "select relname from pg_class where (relkind = 'r' or relkind = 'v') "
    "and (relnamespace not in (select oid from pg_namespace where nspname = 'information_schema'))";

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

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

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

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

  count = PQntuples(res);

  PQclear(res);

  return count;
}

static long table_list_74(DB_DATABASE handle, char ***tables)
{
  const char *query1 =
    "select tablename from pg_tables where schemaname <> 'information_schema'";

  const char *query2 =
    "select viewname from pg_views where schemaname <> 'information_schema'";

  PGconn *conn = (PGconn *)handle;
  PGresult *res1, *res2;
  int i, j;
  long count;

  if (do_query(conn, "Unable to get tables: &1", &res1, query1, 0))
    return -1;

  if (do_query(conn, "Unable to get tables: &1", &res2, query2, 0))
    return -1;

  count = PQntuples(res1) + PQntuples(res2);

  if (tables)