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: