DB abstarction MySQL -> PostgreSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

DB abstarction MySQL -> PostgreSQL

Post by redmonkey »

So I have decided to write a database abstaraction layer. At present the only databases I have access to are MySQL and PostgreSQL so those are the two I'm concentrating on. I'm attacking this as a view to migrating from MySQL to PostgreSQL. I do however have a few problems...

I have what I would describe as an intermediate knowledge level of SQL which is not specific to anyone database so there is a bit of a learning curve there. The first most obvious problem is that of MySQLs insert_id (mysql_insert_id()) function, this functionality is not supported in PostgreSQL, threfore I'm looking for ideas.

I have the basic abstarction layers (MySQL and PostgreSQL) up and running. My currently wrapper for insert_id within PostgreSQL is as follows....

I assign a sequence table to be used as the auto_insert_id counter, when a query is submitted I grep the query to check if it an insert query of a particular format. If so, I break it down check if there is an insert_id sequence associated with the table to be inserted to. If a positive match is found, I then manipulate the original SQL query to add a subselect to get the next value from the sequence table, then execute the query. I then query the database to obtain the current value of the sequence table and store the result should it be required later.

I believe this method is transparent from the user (with the exception of having to assign the sequence table) but, my question is... Has anyone come up with a more cunning/simpler solution to this problem?
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

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;
  }
}
?>
Post Reply