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