3
* PDO query class and associated functions
5
* This subpackage provides some functions that are useful around database
6
* activity and a PdoDialect, PdoDatabase and PdoQuery classes to simplify
7
* handling of database queries and provide some access for a limited
8
* ability to handle varying database dialects.
10
* The class is intended to be a very lightweight wrapper with some features
11
* that have proved useful in developing and debugging web-based applications:
12
* - All queries are timed, and an expected time can be provided.
13
* - Parameters replaced into the SQL will be escaped correctly in order to
14
* minimise the chances of SQL injection errors.
15
* - Queries which fail, or which exceed their expected execution time, will
16
* be logged for potential further analysis.
17
* - Debug logging of queries may be enabled globally, or restricted to
18
* particular sets of queries.
19
* - Simple syntax for iterating through a result set.
21
* See http://wiki.davical.org/w/PdoQuery for design and usage information.
23
* If not already connected, PdoQuery will attempt to connect to the database,
24
* successively applying connection parameters from the array in $c->pdo_connect.
26
* We will die if the database is not currently connected and we fail to find
27
* a working connection.
30
* @subpackage PdoQuery
31
* @author Andrew McMillan <andrew@morphoss.com>
32
* @copyright Morphoss Ltd
33
* @license http://gnu.org/copyleft/gpl.html GNU GPL v3
34
* @compatibility Requires PHP 5.1 or later
38
* The PdoDialect class handles
47
* Holds the name of the database dialect
55
* Parses the connection string to ascertain the database dialect. Returns true if the dialect is supported
56
* and fails if the dialect is not supported. All code to support any given database should be within in an
58
* @param string $connection_string The full PDO connection string
60
function __construct( $connection_string ) {
61
if ( preg_match( '/^(pgsql):/', $connection_string, $matches ) ) {
62
$this->dialect = $matches[1];
65
trigger_error("Unsupported database connection '".$connection_string."'", E_USER_ERROR);
72
* Returns the SQL for the current database dialect which will return a two-column resultset containing a
73
* list of fields and their associated data types.
74
* @param string $tablename_string The name of the table we want fields from
76
function GetFields( $tablename_string ) {
77
if ( !isset($this->dialect) ) {
78
trigger_error("Unsupported database dialect", E_USER_ERROR);
81
switch ( $this->dialect ) {
83
$tablename_string = $this->Quote($tablename_string, 'identifier');
84
$sql = "SELECT f.attname, t.typname FROM pg_attribute f ";
85
$sql .= "JOIN pg_class c ON ( f.attrelid = c.oid ) ";
86
$sql .= "JOIN pg_type t ON ( f.atttypid = t.oid ) ";
87
$sql .= "WHERE relname = $tablename_string AND attnum >= 0 order by f.attnum;";
94
* Translates the given SQL string into a form that will hopefully work for this database dialect. This hook
95
* is expected to be used by developers to provide support for differences in database operation by translating
96
* the query string in an arbitrary way, such as through a file or database lookup.
98
* The actual translation to other SQL dialects will usually be application-specific, so that any routines
99
* called by this will usually be external to this library, or will use resources external to this library.
101
function Translate( $sql_string ) {
102
// Noop for the time being...
109
* Returns $value escaped in an appropriate way for this database dialect.
110
* @param mixed $value The value to be escaped
111
* @param string $value_type The type of escaping desired. If blank this will be worked out from gettype($value). The special
112
* type of 'identifier' can also be used for escaping of SQL identifiers.
114
function Quote( $value, $value_type = null ) {
116
if ( !isset($value_type) ) {
117
$value_type = gettype($value);
120
switch ( $value_type ) {
121
case 'identifier': // special case will only happen if it is passed in.
122
$rv = '"' . str_replace('"', '\\"', $value ) . '"';
131
$rv = $str ? 'TRUE' : 'FALSE';
135
$str = str_replace("'", "''", $str);
136
if ( strpos( $str, '\\' ) !== false ) {
137
$str = str_replace('\\', '\\\\', $str);
138
if ( $this->dialect == 'pgsql' ) {
139
/** PostgreSQL wants to know when a string might contain escapes */
151
* Replaces query parameters with appropriately escaped substitutions.
153
* The function takes a variable number of arguments, the first is the
154
* SQL string, with replaceable '?' characters (a la DBI). The subsequent
155
* parameters being the values to replace into the SQL string.
157
* The values passed to the routine are analyzed for type, and quoted if
158
* they appear to need quoting. This can go wrong for (e.g.) NULL or
159
* other special SQL values which are not straightforwardly identifiable
160
* as needing quoting (or not). In such cases the parameter can be forced
161
* to be inserted unquoted by passing it as "array( 'plain' => $param )".
163
* @param string The query string with replacable '?' characters.
164
* @param mixed The values to replace into the SQL string.
165
* @return The built query string
167
function ReplaceParameters() {
168
$argc = func_num_args();
169
$qry = func_get_arg(0);
170
$args = func_get_args();
172
if ( is_array($qry) ) {
174
* If the first argument is an array we treat that as our arguments instead
178
$argc = count($args);
182
* We only split into a maximum of $argc chunks. Any leftover ? will remain in
183
* the string and may be replaced at Exec rather than Prepare.
185
$parts = explode( '?', $qry, $argc );
186
$querystring = $parts[0];
189
for( $i = 1; $i < $z; $i++ ) {
191
if ( !isset($arg) ) {
192
$querystring .= 'NULL';
194
elseif ( is_array($arg) && $arg['plain'] != '' ) {
195
// We abuse this, but people should access it through the PgQuery::Plain($v) function
196
$querystring .= $arg['plain'];
199
$querystring .= $this->Quote($arg); //parameter
201
$querystring .= $parts[$i]; //extras eg. ","
203
if ( isset($parts[$z]) ) $querystring .= $parts[$z]; //puts last part on the end
215
* Typically there will only be a single instance of the database level class in an application.
224
* Holds the PDO database connection
229
* Holds the dialect object
234
* Holds the state of the transaction 0 = not started, 1 = in progress, -1 = error pending rollback/commit
236
protected $txnstate = 0;
239
* Holds the count of queries executed so far
241
protected $querycount = 0;
244
* Holds the total duration of queries executed so far
246
protected $querytime = 0;
251
* The connection string is in the standard PDO format. The database won't actually be connected until the first
252
* database query is run against it.
254
* The database object will also initialise and hold an PdoDialect object which will be used to provide database
255
* specific SQL for some queries, as well as translation hooks for instances where it is necessary to modify the
256
* SQL in transit to support additional databases.
257
* @param string $connection_string The PDO connection string, in all it's glory
258
* @param string $dbuser The database username to connect as
259
* @param string $dbpass The database password to connect with
260
* @param array $options An array of driver options
262
function __construct( $connection_string, $dbuser=null, $dbpass=null, $options=null ) {
263
$this->dialect = new PdoDialect( $connection_string );
264
$this->db = new PDO( $connection_string, $dbuser, $dbpass, $options );
269
* Returns a PdoQuery object created using this database, the supplied SQL string, and any parameters given.
270
* @param string $sql_query_string The SQL string containing optional variable replacements
271
* @param mixed ... Subsequent arguments are positionally replaced into the $sql_query_string
273
function Prepare( ) {
274
$qry = new PdoQuery( &$this );
275
$qry->Query(func_get_args());
281
* Construct and execute an SQL statement from the sql_string, replacing the parameters into it.
283
* @param string $sql_query_string The SQL string containing optional variable replacements
284
* @param mixed ... Subsequent arguments are positionally replaced into the $sql_query_string
285
* @return mixed false on error or number of rows affected. Test failure with === false
288
$sql_string = $this->dialect->ReplaceParameters(func_get_args());
290
$start = microtime(true);
291
$result = $db->exec($sql_string);
292
$duration = microtime(true) - $start;
293
$this->querytime += $duration;
301
* Begin a transaction.
304
if ( $this->txnstate == 0 ) {
305
$this->db->beginTransaction();
309
trigger_error("Cannot begin a transaction while a transaction is already active.", E_USER_ERROR);
315
* Complete a transaction.
319
if ( $this->txnstate != 0 ) {
326
* Cancel a transaction in progress.
328
function Rollback() {
330
if ( $this->txnstate != 0 ) {
331
$this->db->rollBack();
334
trigger_error("Cannot rollback unless a transaction is already active.", E_USER_ERROR);
340
* Returns the current state of a transaction, indicating if we have begun a transaction, whether the transaction
341
* has failed, or if we are not in a transaction.
343
function TransactionState() {
344
return $this->txnstate;
349
* Returns the total duration of quries executed so far by this object instance.
351
function TotalDuration() {
352
return $this->querytime;
357
* Returns the total number of quries executed by this object instance.
359
function TotalQueries() {
360
return $this->querycount;
365
* Returns an associative array of field types, keyed by field name, for the requested named table. Internally this
366
* calls PdoDialect::GetFields to get the required SQL and then processes the query in the normal manner.
368
function GetFields( $tablename_string ) {
373
* Operates identically to PdoDatabase::Prepare, except that PdoDialect::Translate() will be called on the query
374
* before any processing.
376
function PrepareTranslated() {
381
* Switches on or off the processing flag controlling whether subsequent calls to PdoDatabase::Prepare are translated
382
* as if PrepareTranslated() had been called.
384
function TranslateAll( $onoff_boolean ) {
391
* A variable of this class is normally constructed through a call to PdoDatabase::Query or PdoDatabase::Prepare,
392
* associating it on construction with the database which is to be queried.
399
private $max_duration = 2;
402
* Where $db is a PdoDatabase object. This constructs the PdoQuery. If there are further parameters they
403
* will be in turn, the sql, and any positional parameters to replace into that, and will be passed to
404
* $this->Query() before returning.
406
function __construct( $db ) {
408
if ( isset($db->default_max_duration) ) {
409
$this->max_duration = $db->default_max_duration;
415
* If the sql is supplied then PDO::prepare will be called with that SQL to prepare the query, and if there
416
* are positional parameters then they will be replaced into the sql_string (with appropriate escaping)
417
* before the call to PDO::prepare. Query preparation time is counted towards total query execution time.
420
$sql_string = $this->dialect->ReplaceParameters(func_get_args());
422
$start = microtime(true);
423
$this->sth = $pdb->db->prepare($sql_string);
424
$duration = microtime(true) - $start;
425
$this->querytime += $duration;
430
* If there are (some) positional parameters in the prepared query, now is the last chance to supply them...
431
* before the query is executed. Returns true on success and false on error.
434
$start = microtime(true);
435
$result = $this->sth->execute(func_get_args());
436
$duration = microtime(true) - $start;
437
$this->querytime += $duration;
445
* Will fetch the next row from the query into an object with elements named for the fields in the result.
448
return $this->sth->fetchObject();
453
* Will fetch the next row from the query into an array with numbered elements and with elements named
454
* for the fields in the result.
456
function FetchArray() {
457
return $this->sth->fetch();
462
* Will fetch all result rows from the query into an array of objects with elements named for the fields in the result.
464
function FetchAll() {
465
return $this->sth->fetchAll(PDO::FETCH_OBJ);
470
* An accessor for the number of rows affected when the query was executed.
473
return $this->sth->rowCount();
478
* Used to set the maximum duration for this query before it will be logged as a slow query.
479
* @param double $seconds The maximum duration for this statement before logging it as 'slow'
481
function MaxDuration( $seconds ) {
482
$this->max_duration = $seconds;
added
removed
inc/PdoQuery.php
