Overview

Packages

  • awl
    • AuthPlugin
    • AwlDatabase
    • Browser
    • classEditor
    • DataEntry
    • DataUpdate
    • EMail
    • iCalendar
    • MenuSet
    • PgQuery
    • Session
    • Translation
    • User
    • Utilities
    • Validation
    • vCalendar
    • vComponent
    • XMLDocument
    • XMLElement
  • None
  • PHP

Classes

  • AwlDatabase
  • AwlDBDialect
  • AwlQuery
  • AwlUpgrader

Functions

  • _awl_connect_configured_database
  • Overview
  • Package
  • Class
  • Tree
  • Deprecated
  • Todo
  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:         dbg_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: }
401: 
AWL API documentation generated by ApiGen 2.8.0