As it has been a while and no reply I thought I'd post a follow up. I decided to stick with my original plan which seems to be the most accurate/secure way of obtaining the MySQL insert_id functionality from a PostgreSQL database.
So incase it may help someone in the future here is my solution, the main functions of importance are the query() and register_auto_insert() functions.
If any mods/admins feel this code block is far too much feel free to trim/strip the unesseccary parts.
Comments welcome......
Code: Select all
<?php
/***********************************************************
* filename pgsqldatabase.cls.php
* description PostgreSQL abstraction layer
* project AppEngine
* author redmonkey
* status alpha (work in progress)
*
* depends file dbcore.cls.php
*
* database
* test env PostgreSQL 7.4.3
*
* copyright 2004 redmonkey
*
* todo get my head round PostgreSQL's internal table
* structures and query syntax so as to optimise
* the list_tables() query
*
* todo review/add to/rewrite/improve result set
* pointer implementation. as of PHP-4.1.0
* the result pointer is now built in but I
* think it still has it's uses
*
* todo speedup/improve the regex used for
* parsing INSERT queries
*
* todo seperate auto_insert_id checking and query
* manipulation from main query function (on
* reflection mat leave it there, undecided)
*
* todo possibly expand upon auto_insert checking
* and manipulation rountine to allow for more
* relaxed INSERT queries being detected and
* handled (now done but only minimal testing)
*
* todo register_auto_insert() should not update last
* alias used, need to grab the last alias prior
* to running register_auto_insert() then
* restore it after function has run. or perhaps
* last alias should be set to the alias passed
* to register_auto_insert() function??
*
******** GLOBAL todos can be found in dbcore.cls.php *******
************************************************************/
require_once('dbcore.cls.php');
if (!defined('SQL_BOTH')) define('SQL_BOTH', PGSQL_BOTH);
if (!defined('SQL_ASSOC')) define('SQL_ASSOC', PGSQL_ASSOC);
if (!defined('SQL_NUM')) define('SQL_NUM', PGSQL_NUM);
class Database extends DatabaseCore
{
/**
* @return void
* @desc constructor, initialize database object
*/
function Database()
{
$this->str_quotes = "'";
$this->col_quotes = '"';
$this->_init_db();
}
/**
* @return resource resource link indentifer returned by database
* @param string _alias the alias used when registering the connection
* @desc establish a connection to a database, store and return
* resource handle
*/
function connect($_alias)
{
return $this->_connect($_alias, false);
}
/**
* @return resource resource link indentifer returned by database
* @param string _alias the alias used when registering the connection
* @desc establish a persistent connection to a database, store
* and return resource handle
*/
function pconnect($_alias)
{
return $this->_connect($_alias, true);
}
/**
* @return resource conn_id resource link indentifer returned by database
* @param string _alias the alias used when registering the connection
* @param bool _persistent true to establish a persistent connect false for a
* non-persistent connection
* @desc establish a connection to a database, store and return
* resource handle
*/
function _connect($_alias, $_persistent = false)
{
$connect = $_persistent ? 'pg_pconnect' : 'pg_connect';
$connect_string = '';
if ($this->connections[$_alias]['host'] != '')
{
$connect_string = ' host=' . $this->connections[$_alias]['host'];
}
if ($this->connections[$_alias]['port'] !== false)
{
$connect_string .= ' port=' . $this->connections[$_alias]['port'];
}
if ($this->connections[$_alias]['name'] != '')
{
$connect_string = ' dbname=' . $this->connections[$_alias]['name'];
}
if ($this->connections[$_alias]['user'] != '')
{
$connect_string .= ' user=' . $this->connections[$_alias]['user'];
}
if ($this->connections[$_alias]['pass'] != '')
{
$connect_string .= ' password=' . $this->connections[$_alias]['pass'];
}
$connect_string = substr($connect_string, 1);
if (!$conn_id = @$connect($connect_string))
{
$this->_error("Failed to connect to database '{$_alias}'", E_USER_ERROR);
}
$this->connections[$_alias]['res_id'] = $conn_id;
$this->last_alias = $_alias;
return $conn_id;
}
/**
* @return resource link_id resource link identifer associated with this query
* @param string _sql the SQL query to execute
* @param string _alias the alias for the required database connection
* @desc query database and return link identifier
*/
function query($_sql, $_alias = false)
{
if (!$conn_id = $this->get_connection($_alias))
{
return false;
}
$auto_increment = false;
$query_type = $this->_query_type($_sql);
if ($query_type == 'INSERT')
{
if (preg_match('/(^INSERT\s+INTO\s+"?(\w+)"?\s+)(?:\(([^)]+)\)\s+)?VALUES\s+\(([^)]+)\)/i', trim($_sql), $match))
{
$id_specified = false;
if (isset($this->connections[$_alias]['auto_inserts'][$match[2]]))
{
$ins_pfx_sql = $match[1];
$ins_tbl_sql = $match[2];
$ins_cols_sql = $match[3];
$ins_vals_sql = $match[4];
$col_pos = $this->connections[$_alias]['auto_inserts'][$ins_tbl_sql]['cpos'];
$next_id_sql = 'NEXTVAL('''. $ins_tbl_sql . '_';
$next_id_sql .= $this->connections[$_alias]['auto_inserts'][$ins_tbl_sql]['name'] . '_seq'')';
$get_id_sql = 'SELECT CURRVAL('''. $ins_tbl_sql . '_';
$get_id_sql .= $this->connections[$_alias]['auto_inserts'][$ins_tbl_sql]['name'] . '_seq'')';
$insert_values = explode(',', $ins_vals_sql);
if (!empty($ins_cols_sql))
{
$columns = explode(',', $ins_cols_sql);
foreach ($columns as $idx => $column)
{
if ($this->_unquote_column($column) == $this->connections[$_alias]['auto_inserts'][$ins_tbl_sql]['name'])
{
$id_specified = $idx;
break;
}
}
if ($id_specified === false)
{
$ins_cols_sql = '"' . $this->connections[$_alias]['auto_inserts'][$ins_tbl_sql]['name'] . '", ' . $ins_cols_sql;
$ins_vals_sql = $next_id_sql . ', ' . $ins_vals_sql;
$auto_increment = true;
}
elseif (strtoupper(trim($insert_values[$id_specified])) == 'NULL')
{
$insert_values[$id_specified] = $next_id_sql;
$ins_vals_sql = implode(',', $insert_values);
$auto_increment = true;
}
}
elseif (strtoupper(trim($insert_values[$col_pos])) == 'NULL')
{
$insert_values[$col_pos] = $next_id_sql;
$ins_vals_sql = implode(',', $insert_values);
$auto_increment = true;
}
$_sql = $ins_pfx_sql;
if (!empty($ins_cols_sql))
{
$_sql .= '(' . $ins_cols_sql . ') ';
}
$_sql .= 'VALUES (' . $ins_vals_sql . ');';
}
}
}
if (!$link_id = @pg_query($conn_id, $_sql))
{
$errmsg = "Invalid Query : {$_sql} : postgreSQL said - " . pg_last_error($conn_id);
$this->_error($errmsg, E_USER_ERROR);
}
if ($auto_increment && $link_id && $id_res = @pg_query($conn_id, $get_id_sql))
{
$this->queries[$id_res]['link_id'] = $id_res;
$this->queries[$id_res]['pointer'] = 0;
$insert_id = array_shift($this->fetch_array($id_res));
$this->connections[$_alias]['insert_id'] = $insert_id;
}
$this->queries[$link_id]['link_id'] = $link_id;
switch($query_type)
{
case 'SELECT':
case 'SHOW' :
$this->queries[$link_id]['pointer'] = 0;
break;
default:
$this->queries[$link_id]['pointer'] = false;
}
$this->last_query_id = $link_id;
return $link_id;
}
/**
* @return resource link_id resource link identifer associated with this query
* @param string _sql the SQL query to execute
* @param string _alias the alias for the required database connection
* @desc query database and return link identifier
*/
function unbuffered_query($_sql, $_alias = false)
{
return $this->query($_sql, $_alias);
}
/**
* @return int num_rows number of rows in result set of the SQL query associated with _id
* @param resource _id resource link identifier
* @desc returns number of rows in result set of a query when given
* the link ID
*/
function num_rows($_id)
{
if ($this->queries[$_id]['pointer'] === false)
{
$errmsg = 'ERROR - Database::num_rows is only valid for SELECT type queries ';
$errmsg .= 'perhaps you should try using Database::affected_rows.';
$this->_error($errmsg, E_USER_WARNING);
}
if (isset($this->queries[$_id]['numrows']))
{
return $this->queries[$_id]['numrows'];
}
else
{
$num_rows = @pg_num_rows($_id);
$this->queries[$_id]['numrows'] = $num_rows;
return $num_rows;
}
}
/**
* @return int num_rows number of affected rows by the SQL query associated with _id
* @param resource _id resource link identifier
* @desc returns the number of rows affected by the last INSERT,
* UPDATE or DELETE query associated with _id
*/
function affected_rows($_id = false)
{
if (!$_id)
{
if ($this->last_query_id == false)
{
return false;
}
$_id = $this->last_query_id;
}
$num_rows = @pg_affected_rows($_id);
return $num_rows;
}
/**
* @return array array array containg a single row of a result set of the SQL query
* associated with _id
* @param resource _id resource link identifier
* @desc returns an array that contains all rows in the result set
* associated with _id
*/
function fetch_all($_id)
{
$array = @pg_fetch_all($this->queries[$_id]['link_id']);
return $array;
}
/**
* @return array array array containg a single row of a result set of the SQL query
* associated with _id
* @param resource _id resource link identifier
* @param int _type type of array to return (ASSOCiative, NUMerical or BOTH)
* @desc returns a single row of a reult set as an array
*/
function fetch_array($_id, $_type = SQL_BOTH)
{
$row = $this->queries[$_id]['pointer'];
if ($row < $this->num_rows($_id))
{
$array = pg_fetch_array($this->queries[$_id]['link_id'], $row, $_type);
$this->queries[$_id]['pointer']++;
}
else
{
$array = false;
}
return $array;
}
/**
* @return array array array containg a single row of a result set of the SQL query
* associated with _id
* @param resource _id resource link identifier
* @param int _row the row of the result set to be returned
* @desc returns a single row of a result set as a numerical indexed array
*/
function fetch_row($_id, $_row = false)
{
if ($_row === false)
{
$_row = $this->queries[$_id]['pointer'];
}
if ($_row < $this->num_rows($_id))
{
$array = @pg_fetch_row($this->queries[$_id]['link_id'], $_row);
$this->queries[$_id]['pointer'] = $_row + 1;
}
else
{
$array = false;
}
return $array;
}
/**
* @return array array array containg a single row of a result set of the SQL query
* associated with _id
* @param resource _id resource link identifier
* @param int _row the row of the result set to be returned
* @desc returns a single row of a result set as a associative indexed array
*/
function fetch_assoc($_id, $_row = false)
{
if ($_row === false)
{
$_row = $this->queries[$_id]['pointer'];
}
if ($_row < $this->num_rows($_id))
{
$array = @pg_fetch_assoc($this->queries[$_id]['link_id'], $_row);
$this->queries[$_id]['pointer'] = $_row + 1;
}
else
{
$array = false;
}
return $array;
}
/**
* @return int last_insert_id the last auto incremented id for a given _alias
* @param string _alias the alias for the required database connection
* @desc returns the id generated for an auto_increment column by
* the previous INSERT query using the given link_identifier
*/
function insert_id($_alias = false)
{
if ($_alias !== false)
{
if (!isset($this->connections[$_alias]['insert_id']))
{
return false;
}
return $this->connections[$_alias]['insert_id'];
}
elseif ($this->last_alias != false)
{
if (isset($this->connections[$this->last_alias]['insert_id']))
{
return $this->connections[$this->last_alias]['insert_id'];
}
}
return false;
}
/**
* @return bool true on success, false on failure
* @param resource _id resource link identifer
* @param int _rownum row number to move internal pointer to
* @desc moves the internal row pointer of the SQL result
* associated with the specified result identifier to point
* to the specified row number
*/
function result_seek($_id, $_rownum)
{
if (@pg_result_seek($_id, $_rownum))
{
$this->queries[$_id]['pointer'] = $_rownum;
return true;
}
return false;
}
/**
* @return bool true on success, false on failure
* @param string _id resource link identifer
* @param int _rownum row number to move internal pointer to
* @desc moves the internal row pointer of the SQL result
* associated with the specified result identifier to point
* to the specified row number. this is an alias for result_seek
*/
function data_seek($_id, $_rownum)
{
return $this->result_seek($_id, $_rownum);
}
/**
* @return bool true if table exists, false otherwise
* @param string _tablename the table name to check existance of
* @param string _alias the alias for the required database connection
* @desc queries database associated with _alias for existance
* of given table name
*/
function table_exists($_tablename, $_alias = false)
{
if (!$conn_id = $this->get_connection($_alias))
{
return false;
}
$sql = 'SELECT 1 FROM ' . $_tablename . ' LIMIT 0';
if (@pg_query($conn_id, $sql))
{
return true;
}
return false;
}
/**
* @return resource link_id resource link identifer associated with this query
* @param string _database name of database to query
* @param string _alias the alias for the required database connection
* @desc query _database for existing tables and return
* resource link identifer for results
*/
function list_tables($_database, $_alias = false)
{
if (!$_alias)
{
foreach ($this->connections as $key => $connection)
{
if ($connection['name'] == $_database)
{
$_alias = $key;
break;
}
}
}
$sql = 'SELECT c.relname AS "name", ';
$sql .= 'CASE c.relkind WHEN ''r'' THEN ''table'' WHEN ''v'' THEN ''view'' ';
$sql .= 'WHEN ''i'' THEN ''index'' WHEN ''S'' THEN ''sequence'' END as "Type" ';
$sql .= 'FROM pg_catalog.pg_class c ';
$sql .= 'WHERE c.relkind IN (''r'', '''') AND pg_catalog.pg_table_is_visible(c.oid) ';
$sql .= 'AND c.relname NOT LIKE ''pg_%'' ';
$sql .= 'ORDER BY 1,2;';
return $this->query($sql, $_alias);
}
/**
* @return bool true on success, false on failure
* @param string _alias the alias for the required database connection
* @param string _tablename the table name containg the auto_incrementing field
* @param string _fieldname the field name to auto increment
* @desc register a table field as an auto incrementing field
* used to simulate MySQLs auto_increment feature
*/
function register_auto_insert($_alias, $_tablename, $_fieldname)
{
if (!isset($this->connections[$_alias]))
{
$this->_error('The ' . $_alias . 'connection must be registered first!', E_USER_NOTICE);
return false;
}
if (!isset($this->connections[$_alias . '_inserts']))
{
$this->register($this->connections[$_alias]['host'],
$this->connections[$_alias]['user'],
$this->connections[$_alias]['pass'],
$this->connections[$_alias]['name'],
$_alias . '_inserts',
$this->connections[$_alias]['port'],
$this->connections[$_alias]['sock']);
}
if (!$this->table_exists($_tablename . '_' . $_fieldname . '_seq', $_alias . '_inserts'))
{
return false;
}
if (!$link_id = $this->query('SELECT * FROM ' . $_tablename . ' LIMIT 1', $_alias))
{
return false;
}
if (0 > ($pos = @pg_field_num($link_id, $_fieldname)))
{
return false;
}
$this->connections[$_alias]['auto_inserts'][$_tablename]['name'] = $_fieldname;
$this->connections[$_alias]['auto_inserts'][$_tablename]['cpos'] = $pos;
return true;
}
/**
* @return bool true on success, false on failure
* @param resource _id resource link identifier
* @desc free all memory associated with the result set for a given _id
*/
function free_result($_id)
{
if($this->queries[$_id]['link_id'] != 0)
{
if (@pg_free_result($this->queries[$_id]['link_id']))
{
$this->queries[$_id]['link_id'] = 0;
return true;
}
}
return false;
}
/**
* @return bool true on success, false on failure
* @param string _alias the alias for the required database connection
* @desc closes the non-persistent connection associated with _alias
*/
function close($_alias = false)
{
if (!$conn_id = $this->get_connection($_alias))
{
return false;
}
return @pg_close($conn_id);
}
/**
* @return string
* @param string _string a string value of a database table or field name
* @desc returns _string with enclosing quotes removed
*/
function _unquote_column($_string)
{
$_string = trim($_string);
if (substr($_string, 0, 1) == '"' && substr($_string, -1) == '"')
{
return substr($_string, 1, -1);
}
return $_string;
}
}
?>