inc/AwlDBDialect.php

   1 <?php
   2 /**
   3 * AwlDatabase - support for different SQL dialects
   4 *
   5 * This subpackage provides dialect specific support for PostgreSQL, and
   6 * may, over time, be extended to provide support for other SQL dialects.
   7 *
   8 * See http://wiki.davical.org/w/Coding/AwlQuery for design and usage information.
   9 *
  10 * @package   awl
  11 * @subpackage   AwlDatabase
  12 * @author    Andrew McMillan <andrew@morphoss.com>
  13 * @copyright Morphoss Ltd
  14 * @license   http://gnu.org/copyleft/gpl.html GNU GPL v3 or later
  15 * @compatibility Requires PHP 5.1 or later
  16 */
  17
  18 if ( !defined('E_USER_ERROR') ) define('E_USER_ERROR',256);
  19
  20 /**
  21 * The AwlDBDialect class handles support for different SQL dialects
  22 *
  23 * This subpackage provides dialect specific support for PostgreSQL, and
  24 * may, over time, be extended to provide support for other SQL dialects.
  25 *
  26 * If you are looking for the place to add support for other SQL dialects,
  27 * this is the class that you should be looking at.  You might also look at
  28 * the AwlDatabase class which extends this one, but these are the core
  29 * capabilities which most probably need attention.
  30 *
  31 * @package awl
  32 */
  33 class AwlDBDialect {
  34   /**#@+
  35   * @access private
  36   */
  37
  38   /**
  39   * Holds the name of the database dialect
  40   */
  41   protected $dialect;
  42
  43   /**
  44   * Holds the PDO database connection
  45   */
  46   protected $db;
  47
  48   /**
  49   * Holds the version
  50   */
  51   private $version;
  52
  53   /**#@-*/
  54
  55
  56   /**
  57   * Parses the connection string to ascertain the database dialect. Returns true if the dialect is supported
  58   * and fails if the dialect is not supported. All code to support any given database should be within in an
  59   * external include.
  60   *
  61   * The database will be opened.
  62   *
  63   * @param string $connection_string The PDO connection string, in all it's glory
  64   * @param string $dbuser The database username to connect as
  65   * @param string $dbpass The database password to connect with
  66   * @param array  $options An array of driver options
  67   */
  68   function __construct( $connection_string, $dbuser=null, $dbpass=null, $options=null ) {
  69     if ( preg_match( '/^(pgsql):/', $connection_string, $matches ) ) {
  70       $this->dialect = $matches[1];
  71     }
  72     else {
  73       error_log("Unable to connect to database: ". $e->getMessage() );
  74       trigger_error("Unsupported database connection '".$connection_string."'",E_USER_ERROR);
  75     }
  76     try {
  77       $this->db = new PDO( $connection_string, $dbuser, $dbpass, $options );
  78     } catch (PDOException $e) {
  79       error_log("Unable to connect to database: ". $e->getMessage() );
  80       if ( function_exists('trigger_error') )
  81         trigger_error("PDO connection error '".$connection_string."': ".$e->getMessage(),E_USER_ERROR);
  82       throw $e;
  83     }
  84   }
  85
  86
  87
  88   /**
  89   * Sets the current search path for the database.
  90   */
  91   function SetSearchPath( $search_path = null ) {
  92     if ( !isset($this->dialect) ) {
  93       trigger_error("Unsupported database dialect",E_USER_ERROR);
  94     }
  95
  96     switch ( $this->dialect ) {
  97       case 'pgsql':
  98         if ( $search_path == null ) $search_path = 'public';
  99         $sql = "SET search_path TO " . $this->Quote( $search_path, 'identifier' );
 100         return $sql;
 101     }
 102   }
 103
 104
 105   /**
 106   * Sets the current search path for the database.
 107   * @param handle $pdo A handle to an opened database
 108   */
 109   function GetVersion( ) {
 110     if ( isset($this->version) ) return $this->version;
 111     if ( !isset($this->dialect) ) {
 112       trigger_error("Unsupported database dialect", E_USER_ERROR);
 113     }
 114
 115     $version = $this->dialect.':';
 116
 117     switch ( $this->dialect ) {
 118       case 'pgsql':
 119         $sql = "SELECT version()";
 120         if ( $sth = $this->db->query($sql) ) {
 121           $row = $sth->fetch(PDO::FETCH_NUM);
 122           $version .= preg_replace( '/^PostgreSQL (\d+\.\d+)\..*$/i', '$1', $row[0]);
 123         }
 124         break;
 125       default:
 126         return null;
 127     }
 128     $this->version = $version;
 129     return $version;
 130   }
 131
 132
 133   /**
 134   * Returns the SQL for the current database dialect which will return a two-column resultset containing a
 135   * list of fields and their associated data types.
 136   * @param string $tablename_string The name of the table we want fields from
 137   */
 138   function GetFields( $tablename_string ) {
 139     if ( !isset($this->dialect) ) {
 140       trigger_error("Unsupported database dialect", E_USER_ERROR);
 141     }
 142
 143     switch ( $this->dialect ) {
 144       case 'pgsql':
 145         $tablename_string = $this->Quote($tablename_string,PDO::PARAM_STR);
 146         $sql = 'SELECT f.attname AS fieldname, t.typname AS typename, f.atttypmod AS precision FROM pg_attribute f';
 147         $sql .= ' JOIN pg_class c ON ( f.attrelid = c.oid )';
 148         $sql .= ' JOIN pg_type t ON ( f.atttypid = t.oid )';
 149         $sql .= ' WHERE relname = '.$tablename_string.' AND attnum >= 0 order by f.attnum';
 150         return $sql;
 151     }
 152   }
 153
 154
 155   /**
 156   * Translates the given SQL string into a form that will hopefully work for this database dialect. This hook
 157   * is intended to be used by developers to provide support for differences in database operation by translating
 158   * the query string in an arbitrary way, such as through a file or database lookup.
 159   *
 160   * The actual translation to other SQL dialects will be application-specific, so that any routines
 161   * called by this will be external to this library, or will use resources loaded from some source
 162   * external to this library.
 163   *
 164   * The application developer is expected to use this functionality to solve harder translation problems,
 165   * but is less likely to call this directly, hopefully switching ->Prepare to ->PrepareTranslated in those
 166   * cases, and then adding that statement to whatever SQL translation infrastructure is in place.
 167   */
 168   function TranslateSQL( $sql_string ) {
 169     // Noop for the time being...
 170     return $sql_string;
 171   }
 172
 173
 174
 175   /**
 176   * Returns $value escaped in an appropriate way for this database dialect.
 177   * @param mixed $value The value to be escaped
 178   * @param string $value_type The type of escaping desired.  If blank this will
 179   *                be worked out from the type of the $value.  The special type
 180   *                of 'identifier' can also be used for escaping of SQL identifiers.
 181   */
 182   function Quote( $value, $value_type = null ) {
 183     if ( isset($value_type) && $value_type == 'identifier' ) {
 184       if ( $this->dialect == 'mysql' ) {
 185         /** @TODO: Someone should confirm this is correct for MySql */
 186         $rv = '`' . str_replace('`', '\\`', $value ) . '`';
 187       }
 188       else {
 189         $rv = '"' . str_replace('"', '\\"', $value ) . '"';
 190       }
 191       return $rv;
 192     }
 193
 194     if ( !isset($value_type) ) {
 195       if ( !isset($value) )       $value_type = PDO::PARAM_NULL;
 196       elseif ( is_bool($value) )  $value_type = PDO::PARAM_BOOL;
 197       elseif ( is_float($value) ) $value_type = PDO::PARAM_INT;
 198       elseif ( is_numeric($value)) {
 199         if ( preg_match('{^(19|20)\d\d(0[1-9]|1[012])([012]\d|30|31)$}', $value) )
 200           $value_type = PDO::PARAM_STR; // YYYYMMDD
 201         elseif ( preg_match('{^0x}i', $value) )
 202           $value_type = PDO::PARAM_STR; // Any hex numbers will need to be explicitly cast in SQL
 203         elseif ( preg_match('{^[0-9+-]+e[0-9+-]+$}i', $value) )
 204           $value_type = PDO::PARAM_STR; // 72e57650 could easily be a string and will need an explicit cast also
 205         else
 206           $value_type = PDO::PARAM_INT;
 207       }
 208       else
 209         $value_type = PDO::PARAM_STR;
 210     }
 211
 212     if ( is_string($value_type) ) {
 213       switch( $value_type ) {
 214         case 'null':
 215           $value_type = PDO::PARAM_NULL;
 216           break;
 217         case 'integer':
 218         case 'double' :
 219           $value_type = PDO::PARAM_INT;
 220           break;
 221         case 'boolean':
 222           $value_type = PDO::PARAM_BOOL;
 223           break;
 224         case 'string':
 225           $value_type = PDO::PARAM_STR;
 226           break;
 227       }
 228     }
 229
 230     switch ( $value_type ) {
 231       case PDO::PARAM_NULL:
 232         $rv = 'NULL';
 233         break;
 234       case PDO::PARAM_INT:
 235         $rv = $value;
 236         break;
 237       case PDO::PARAM_BOOL:
 238         $rv = ($value ? 'TRUE' : 'FALSE');
 239         break;
 240       case PDO::PARAM_STR:
 241       default:
 242         /**
 243         * PDO handling of \ seems unreliable.  We can't use $$string$$ syntax because it also doesn't
 244         * work.  We need to replace ':' so no other named parameters accidentally rewrite the content
 245         * inside this string(!), and since we're using ' to delimit the string we need SQL92-compliant
 246         * '' to replace it.
 247         */
 248         $rv = "'".str_replace("'", "''", str_replace(':', '\\x3a', str_replace('\\', '\\x5c', $value)))."'";
 249
 250         if ( $this->dialect == 'pgsql' && strpos( $rv, '\\' ) !== false ) {
 251           /**
 252           * PostgreSQL wants to know when a string might contain escapes, and if this
 253           * happens old versions of PHP::PDO need the ? escaped as well...
 254           */
 255           $rv = 'E'.str_replace('?', '\\x3f', $rv);
 256         }
 257
 258     }
 259
 260     return $rv;
 261
 262   }
 263
 264
 265   /**
 266   * Replaces query parameters with appropriately escaped substitutions.
 267   *
 268   * The function takes a variable number of arguments, the first is the
 269   * SQL string, with replaceable '?' characters (a la DBI).  The subsequent
 270   * parameters being the values to replace into the SQL string.
 271   *
 272   * The values passed to the routine are analyzed for type, and quoted if
 273   * they appear to need quoting.  This can go wrong for (e.g.) NULL or
 274   * other special SQL values which are not straightforwardly identifiable
 275   * as needing quoting (or not).  In such cases the parameter can be forced
 276   * to be inserted unquoted by passing it as "array( 'plain' => $param )".
 277   *
 278   * @param  string The query string with replacable '?' characters.
 279   * @param mixed The values to replace into the SQL string.
 280   * @return The built query string
 281   */
 282   function ReplaceParameters() {
 283     $argc = func_num_args();
 284     $args = func_get_args();
 285
 286     if ( is_array($args[0]) ) {
 287       /**
 288       * If the first argument is an array we treat that as our arguments instead
 289       */
 290       $args = $args[0];
 291       $argc = count($args);
 292     }
 293     $qry = array_shift($args);
 294
 295     if ( is_array($args[0]) ) {
 296       $args = $args[0];
 297       $argc = count($args);
 298     }
 299
 300     if ( ! isset($args[0]) ) return $this->ReplaceNamedParameters($qry,$args);
 301
 302     /**
 303     * We only split into a maximum of $argc chunks.  Any leftover ? will remain in
 304     * the string and may be replaced at Exec rather than Prepare. Scary!
 305     */
 306     $parts = explode( '?', $qry, $argc + 1 );
 307     $querystring = $parts[0];
 308     $z = count($parts);
 309
 310     for( $i = 0; $i < $argc; $i++ ) {
 311       $arg = $args[$i];
 312       $querystring .= $this->Quote($arg);  //parameter
 313       $z = $i+1;
 314       if ( isset($parts[$z]) ) $querystring .= $parts[$z];
 315     }
 316
 317     return $querystring;
 318   }
 319
 320   /**
 321   * Replaces named query parameters of the form :name with appropriately
 322   * escaped substitutions.
 323   *
 324   * The function takes a variable number of arguments, the first is the
 325   * SQL string, with replaceable ':name' characters (a la DBI).  The
 326   * subsequent parameters being the values to replace into the SQL string.
 327   *
 328   * The values passed to the routine are analyzed for type, and quoted if
 329   * they appear to need quoting.  This can go wrong for (e.g.) NULL or
 330   * other special SQL values which are not straightforwardly identifiable
 331   * as needing quoting (or not).
 332   *
 333   * @param  string The query string with replacable ':name' identifiers
 334   * @param mixed A ':name' => 'value' hash of values to replace into the
 335   * SQL string.
 336   * @return The built query string
 337   */
 338   function ReplaceNamedParameters() {
 339     $argc = func_num_args();
 340     $args = func_get_args();
 341
 342     if ( is_array($args[0]) ) {
 343       /**
 344       * If the first argument is an array we treat that as our arguments instead
 345       */
 346       $args = $args[0];
 347       $argc = count($args);
 348     }
 349     $querystring = array_shift($args);
 350
 351     if ( is_array($args[0]) ) {
 352       $args = $args[0];
 353       $argc = count($args);
 354     }
 355
 356     foreach( $args AS $name => $value ) {
 357       if ( substr($name, 0, 1) != ':' ) {
 358         dbg_error_log( "ERROR", "AwlDBDialect: Named parameter '%s' does not begin with a colon.", $name);
 359       }
 360       $replacement = str_replace('$', '\\$', $this->Quote($value));  // No positional replacement in $replacement!
 361       $querystring = preg_replace( '{\Q'.$name.'\E\b}s', $replacement, $querystring );
 362     }
 363
 364     return $querystring;
 365   }
 366
 367 }