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    * A PostgreSQL Date Format string suitable for returning HTTP (RFC2068) dates
  57    * Preferred is "Sun, 06 Nov 1994 08:49:37 GMT" so we do that.
  58    */
  59   const HttpDateFormat = "'Dy, DD Mon IYYY HH24:MI:SS \"GMT\"'";
  60
  61   /**
  62    * A PostgreSQL Date Format string suitable for returning iCal dates
  63   */
  64   const SqlDateFormat = "'YYYYMMDD\"T\"HH24MISS'";
  65
  66   /**
  67    * A PostgreSQL Date Format string suitable for returning dates which
  68    * have been cast to UTC
  69   */
  70   const SqlUTCFormat = "'YYYYMMDD\"T\"HH24MISS\"Z\"'";
  71
  72   /**
  73    * A PostgreSQL Date Format string suitable for returning iCal durations
  74    *  - this doesn't work for negative intervals, but events should not have such!
  75   */
  76   const SqlDurationFormat = "'\"PT\"HH24\"H\"MI\"M\"'";
  77
  78   /**
  79   * Parses the connection string to ascertain the database dialect. Returns true if the dialect is supported
  80   * and fails if the dialect is not supported. All code to support any given database should be within in an
  81   * external include.
  82   *
  83   * The database will be opened.
  84   *
  85   * @param string $connection_string The PDO connection string, in all it's glory
  86   * @param string $dbuser The database username to connect as
  87   * @param string $dbpass The database password to connect with
  88   * @param array  $options An array of driver options
  89   */
  90   function __construct( $connection_string, $dbuser=null, $dbpass=null, $options=null ) {
  91     if ( preg_match( '/^(pgsql):/', $connection_string, $matches ) ) {
  92       $this->dialect = $matches[1];
  93     }
  94     else {
  95       error_log("Unable to connect to database: ". $e->getMessage() );
  96       trigger_error("Unsupported database connection '".$connection_string."'",E_USER_ERROR);
  97     }
  98     try {
  99       $this->db = new PDO( $connection_string, $dbuser, $dbpass, $options );
 100     } catch (PDOException $e) {
 101       error_log("Unable to connect to database: ". $e->getMessage() );
 102       if ( function_exists('trigger_error') )
 103         trigger_error("PDO connection error '".$connection_string."': ".$e->getMessage(),E_USER_ERROR);
 104       throw $e;
 105     }
 106   }
 107
 108
 109
 110   /**
 111   * Sets the current search path for the database.
 112   */
 113   function SetSearchPath( $search_path = null ) {
 114     if ( !isset($this->dialect) ) {
 115       trigger_error("Unsupported database dialect",E_USER_ERROR);
 116     }
 117
 118     switch ( $this->dialect ) {
 119       case 'pgsql':
 120         if ( $search_path == null ) $search_path = 'public';
 121         $sql = "SET search_path TO " . $this->Quote( $search_path, 'identifier' );
 122         return $sql;
 123     }
 124   }
 125
 126
 127   /**
 128   * Sets the current search path for the database.
 129   * @param handle $pdo A handle to an opened database
 130   */
 131   function GetVersion( ) {
 132     if ( isset($this->version) ) return $this->version;
 133     if ( !isset($this->dialect) ) {
 134       trigger_error("Unsupported database dialect", E_USER_ERROR);
 135     }
 136
 137     $version = $this->dialect.':';
 138
 139     switch ( $this->dialect ) {
 140       case 'pgsql':
 141         $sql = "SELECT version()";
 142         if ( $sth = $this->db->query($sql) ) {
 143           $row = $sth->fetch(PDO::FETCH_NUM);
 144           $version .= preg_replace( '/^PostgreSQL (\d+\.\d+)\..*$/i', '$1', $row[0]);
 145         }
 146         break;
 147       default:
 148         return null;
 149     }
 150     $this->version = $version;
 151     return $version;
 152   }
 153
 154
 155   /**
 156   * Returns the SQL for the current database dialect which will return a two-column resultset containing a
 157   * list of fields and their associated data types.
 158   * @param string $tablename_string The name of the table we want fields from
 159   */
 160   function GetFields( $tablename_string ) {
 161     if ( !isset($this->dialect) ) {
 162       trigger_error("Unsupported database dialect", E_USER_ERROR);
 163     }
 164
 165     switch ( $this->dialect ) {
 166       case 'pgsql':
 167         list( $schema, $table ) = explode('.', $tablename_string, 2);
 168         if ( empty($table) ) {
 169                 $table = $tablename_string;
 170                 $schema = null;
 171         }
 172
 173         $sql = 'SELECT f.attname AS fieldname, t.typname AS typename, f.atttypmod AS precision FROM pg_attribute f';
 174         $sql .= ' JOIN pg_class c ON ( f.attrelid = c.oid )';
 175         $sql .= ' JOIN pg_type t ON ( f.atttypid = t.oid )';
 176         $sql .= ' JOIN pg_namespace ns ON ( c.relnamespace = ns.oid )';
 177         $sql .= ' WHERE relname = '.$this->Quote($table,PDO::PARAM_STR).' AND attnum >= 0 ';
 178         if ( isset($schema) ) $sql .= ' AND ns.nspname = '.$this->Quote($schema,PDO::PARAM_STR);
 179         $sql .= ' ORDER BY f.attnum';
 180         error_log($sql);
 181         return $sql;
 182     }
 183   }
 184
 185
 186   /**
 187   * Translates the given SQL string into a form that will hopefully work for this database dialect. This hook
 188   * is intended to be used by developers to provide support for differences in database operation by translating
 189   * the query string in an arbitrary way, such as through a file or database lookup.
 190   *
 191   * The actual translation to other SQL dialects will be application-specific, so that any routines
 192   * called by this will be external to this library, or will use resources loaded from some source
 193   * external to this library.
 194   *
 195   * The application developer is expected to use this functionality to solve harder translation problems,
 196   * but is less likely to call this directly, hopefully switching ->Prepare to ->PrepareTranslated in those
 197   * cases, and then adding that statement to whatever SQL translation infrastructure is in place.
 198   */
 199   function TranslateSQL( $sql_string ) {
 200     // Noop for the time being...
 201     return $sql_string;
 202   }
 203
 204
 205
 206   /**
 207   * Returns $value escaped in an appropriate way for this database dialect.
 208   * @param mixed $value The value to be escaped
 209   * @param string $value_type The type of escaping desired.  If blank this will
 210   *                be worked out from the type of the $value.  The special type
 211   *                of 'identifier' can also be used for escaping of SQL identifiers.
 212   */
 213   function Quote( $value, $value_type = null ) {
 214     if ( isset($value_type) && $value_type == 'identifier' ) {
 215       if ( $this->dialect == 'mysql' ) {
 216         /** @TODO: Someone should confirm this is correct for MySql */
 217         $rv = '`' . str_replace('`', '\\`', $value ) . '`';
 218       }
 219       else {
 220         $rv = '"' . str_replace('"', '\\"', $value ) . '"';
 221       }
 222       return $rv;
 223     }
 224
 225     if ( !isset($value_type) ) {
 226       if ( !isset($value) )       $value_type = PDO::PARAM_NULL;
 227       elseif ( is_bool($value) )  $value_type = PDO::PARAM_BOOL;
 228       elseif ( is_float($value) ) $value_type = PDO::PARAM_INT;
 229       elseif ( is_numeric($value)) {
 230         if ( preg_match('{^(19|20)\d\d(0[1-9]|1[012])([012]\d|30|31)$}', $value) )
 231           $value_type = PDO::PARAM_STR; // YYYYMMDD
 232         elseif ( preg_match('{^0x}i', $value) )
 233           $value_type = PDO::PARAM_STR; // Any hex numbers will need to be explicitly cast in SQL
 234         elseif ( preg_match('{^[0-9+-]+e[0-9+-]+$}i', $value) )
 235           $value_type = PDO::PARAM_STR; // 72e57650 could easily be a string and will need an explicit cast also
 236         elseif ( preg_match('/^[01]{6,}$/i', $value) )
 237           $value_type = PDO::PARAM_STR; // Binary numbers will need to be explicitly cast in SQL
 238         else
 239           $value_type = PDO::PARAM_INT;
 240       }
 241       else
 242         $value_type = PDO::PARAM_STR;
 243     }
 244
 245     if ( is_string($value_type) ) {
 246       switch( $value_type ) {
 247         case 'null':
 248           $value_type = PDO::PARAM_NULL;
 249           break;
 250         case 'integer':
 251         case 'double' :
 252           $value_type = PDO::PARAM_INT;
 253           break;
 254         case 'boolean':
 255           $value_type = PDO::PARAM_BOOL;
 256           break;
 257         case 'string':
 258           $value_type = PDO::PARAM_STR;
 259           break;
 260       }
 261     }
 262
 263     switch ( $value_type ) {
 264       case PDO::PARAM_NULL:
 265         $rv = 'NULL';
 266         break;
 267       case PDO::PARAM_INT:
 268         $rv = $value;
 269         break;
 270       case PDO::PARAM_BOOL:
 271         $rv = ($value ? 'TRUE' : 'FALSE');
 272         break;
 273       case PDO::PARAM_STR:
 274       default:
 275         /**
 276         * PDO handling of \ seems unreliable.  We can't use $$string$$ syntax because it also doesn't
 277         * work.  We need to replace ':' so no other named parameters accidentally rewrite the content
 278         * inside this string(!), and since we're using ' to delimit the string we need SQL92-compliant
 279         * '' to replace it.
 280         */
 281         $rv = "'".str_replace("'", "''", str_replace(':', '\\x3a', str_replace('\\', '\\x5c', $value)))."'";
 282
 283         if ( $this->dialect == 'pgsql' && strpos( $rv, '\\' ) !== false ) {
 284           /**
 285           * PostgreSQL wants to know when a string might contain escapes, and if this
 286           * happens old versions of PHP::PDO need the ? escaped as well...
 287           */
 288           $rv = 'E'.str_replace('?', '\\x3f', $rv);
 289         }
 290
 291     }
 292
 293     return $rv;
 294
 295   }
 296
 297
 298   /**
 299   * Replaces query parameters with appropriately escaped substitutions.
 300   *
 301   * The function takes a variable number of arguments, the first is the
 302   * SQL string, with replaceable '?' characters (a la DBI).  The subsequent
 303   * parameters being the values to replace into the SQL string.
 304   *
 305   * The values passed to the routine are analyzed for type, and quoted if
 306   * they appear to need quoting.  This can go wrong for (e.g.) NULL or
 307   * other special SQL values which are not straightforwardly identifiable
 308   * as needing quoting (or not).  In such cases the parameter can be forced
 309   * to be inserted unquoted by passing it as "array( 'plain' => $param )".
 310   *
 311   * @param  string The query string with replacable '?' characters.
 312   * @param mixed The values to replace into the SQL string.
 313   * @return The built query string
 314   */
 315   function ReplaceParameters() {
 316     $argc = func_num_args();
 317     $args = func_get_args();
 318
 319     if ( is_array($args[0]) ) {
 320       /**
 321       * If the first argument is an array we treat that as our arguments instead
 322       */
 323       $args = $args[0];
 324       $argc = count($args);
 325     }
 326     $qry = array_shift($args);
 327
 328     if ( is_array($args[0]) ) {
 329       $args = $args[0];
 330       $argc = count($args);
 331     }
 332
 333     if ( ! isset($args[0]) ) return $this->ReplaceNamedParameters($qry,$args);
 334
 335     /**
 336     * We only split into a maximum of $argc chunks.  Any leftover ? will remain in
 337     * the string and may be replaced at Exec rather than Prepare. Scary!
 338     */
 339     $parts = explode( '?', $qry, $argc + 1 );
 340     $querystring = $parts[0];
 341     $z = count($parts);
 342
 343     for( $i = 0; $i < $argc; $i++ ) {
 344       $arg = $args[$i];
 345       $querystring .= $this->Quote($arg);  //parameter
 346       $z = $i+1;
 347       if ( isset($parts[$z]) ) $querystring .= $parts[$z];
 348     }
 349
 350     return $querystring;
 351   }
 352
 353   /**
 354   * Replaces named query parameters of the form :name with appropriately
 355   * escaped substitutions.
 356   *
 357   * The function takes a variable number of arguments, the first is the
 358   * SQL string, with replaceable ':name' characters (a la DBI).  The
 359   * subsequent parameters being the values to replace into the SQL string.
 360   *
 361   * The values passed to the routine are analyzed for type, and quoted if
 362   * they appear to need quoting.  This can go wrong for (e.g.) NULL or
 363   * other special SQL values which are not straightforwardly identifiable
 364   * as needing quoting (or not).
 365   *
 366   * @param  string The query string with replacable ':name' identifiers
 367   * @param mixed A ':name' => 'value' hash of values to replace into the
 368   * SQL string.
 369   * @return The built query string
 370   */
 371   function ReplaceNamedParameters() {
 372     $argc = func_num_args();
 373     $args = func_get_args();
 374
 375     if ( is_array($args[0]) ) {
 376       /**
 377       * If the first argument is an array we treat that as our arguments instead
 378       */
 379       $args = $args[0];
 380       $argc = count($args);
 381     }
 382     $querystring = array_shift($args);
 383
 384     if ( is_array($args[0]) ) {
 385       $args = $args[0];
 386       $argc = count($args);
 387     }
 388
 389     foreach( $args AS $name => $value ) {
 390       if ( substr($name, 0, 1) != ':' ) {
 391         dbg_error_log( "ERROR", "AwlDBDialect: Named parameter '%s' does not begin with a colon.", $name);
 392       }
 393       $replacement = str_replace('$', '\\$', $this->Quote($value));  // No positional replacement in $replacement!
 394       $querystring = preg_replace( '{\Q'.$name.'\E\b}s', $replacement, $querystring );
 395     }
 396
 397     return $querystring;
 398   }
 399
 400 }