gcdb | DB-API (proposed)

File: db.php

Described implementation:

• MySQL : PHP module = mysql; functions described by mysql.php
• PostgreSQL : PHP module = pgsql; functions described by pgsql.php

  NOTE: DB-API functionality currently requires that add-on database functions beyond mysql require mysql NATIVE behavior. See discussion mysql_fetch_array() vs pg_fetch_array().

rdbms_pconnect(DBHOST, DBUSER, DBPASSWORD, DBNAME)

Arguments:
DBHOST: hostname
DBUSER: database username/login
DBPASSWORD: database password
DBNAME: database name

Description: obtains a persistent database connection from the backend
Returns: database handle
Implementation:
mysql:
$db = mysql_pconnect($DBHOST, $DBUSER, $DBPASSWORD);
mysql_select_db($DBNAME, $db);

pgsql:
$db = pg_pconnect("host=$DBHOST dbname=$DBNAME user=$DBUSER password=$DBPASSWORD");
NOTE: The argument order seemed to be important or it connected to the wrong DB name. Persistent might work if DBHOST is left blank, as to allow connection via UNIX socket files.

rdbms_connect(DBHOST, DBUSER, DBPASSWORD, DBNAME)

Arguments:
DBHOST: hostname
DBUSER: database username/login
DBPASSWORD: database password
DBNAME: database name

Description: obtains a database connection from the backend
Returns: database handle
Implementation:
mysql:
$db = mysql_connect($DBHOST, $DBUSER, $DBPASSWORD);
mysql_select_db($DBNAME, $db);

pgsql:
$db = pg_connect("host=$DBHOST dbname=$DBNAME user=$DBUSER password=$DBPASSWORD");
NOTE: The argument order seemed to be important or it connected to the wrong DB name. This works great.

rdbms_close(db)

Argument:
db: database handle

Description: closes a connection to the database backend
Implementation:
mysql:
??

pgsql:
pg_close($db);
NOTE: with DBHOST=localhost and PERSISTENT='On', PostgreSQL seems to create a child connection for each request without truely using the persistent connection.

rdbms_query(query,db)

Arguments:
query: SQL query (string)
db: database handle

Description: sends a SQL query to the database backend
Returns: result handle

Implementation:
mysql:
# somehow, mysql doesn't need a database handle
$result = mysql_query($query);

pgsql:
Required functions: next_pg_row()
$result = pg_exec($db,$query);
# new result, reset result pointer to first row
$nrow = next_pg_row(-1);
NOTE: next_pg_row(-1) sets a result pointer to the first row of the result. This is needed to mimic the operation of mysql_fetch_array().

rdbms_fetch_array(result)

Argument:
result: result handle

Description: fetches the next row from the result handle
Returns: associated array of elements from row in the passed result. If no more rows exist in the result handle, NULL is returned.
Implementation:
mysql:
$row = mysql_fetch_array($result);

pgsql:
Required functions: next_pg_row(), db_tools_fixfields()
# increment row counter for result
$nrow = next_pg_row(0);
$row = "";
if (pg_numrows($result)>$nrow) {
$row = pg_fetch_array($result,$nrow);
# translate lowercase field names to case-sensitive names
$row = db_tools_fixfields($row);
}

NOTE: next_pg_row(0) asks for the next row of the result. This is needed to mimic the operation of mysql_fetch_array().

rdbms_num_rows(result)

Argument:
result: result handle

Description: counts the number of rows in the result handle
Returns: integer, number of rows in the result handle
Implementation:
mysql:
$count = mysql_num_rows($result);
pgsql:
$count = pg_numrows($result);

rdbms_db_error(db)

Argument:
db: database handle

Description: fetches last error in the database backend
Returns: string, last error message in the database backend
Implementation:
mysql:
$test = mysql_errno();
$estr = "";
if($test != 0) {
$estr=mysql_errno().":".mysql_error();
}
return $estr;
pgsql:
return pg_errormessage($db);

rdbms_insert_id(result, table, field, db)

Description: returns the last id used with by a SQL insert
Returns: integer, id number of last SQL insert, not OID
Implementation:
mysql:
$num = mysql_insert_id($db);
pgsql:

$oid = pg_getlastoid($result);
$res = rdbms_query("SELECT $field FROM $table WHERE OID=$oid",$db);
$row = rdbms_fetch_array($res,0);
$row = db_tools_fixfields($row);
$num = $row[$field];
return $num;

next_pg_row(iflag, result)

Argument:
iflag: integer flag, advance(0) or reset(-1) the result pointer result: result handle
Description: keeps track of a result pointer using a static variable (hash); the pointer can be reset(-1) or advanced(0); this function does not have any knowledge about the result row; the hash in this case is the result handle to track several row pointers for several results
Returns: next integer row

Functions found in db-tools.php:

db_tools_fixfields(associative array)

Argument:
assoc array: associative array of elements describing a row of a result handle
Description: converts lowercase fields to gcdb case sensitive fields; required to assist case insensitive databases become case sensitive
Returns: additional elements with proper field value pairs; the original data still remains

• PHP4 : Manual :
  • mysql
  • pgsql