root/fDatabase.php

Class Resources

Revision 890, 93.1 kB (checked in by wbond, 2 weeks ago)
Fixed fDatabase when using the pgsql, mssql and mysql extensions to force a new connection instead of reusing an existing one

Line	Hide Line Numbers
1	<?php
2	/**
3	* Provides a common API for different databases - will automatically use any installed extension
4	*
5	* This class is implemented to use the UTF-8 character encoding. Please see
6	* http://flourishlib.com/docs/UTF-8 for more information.
7	*
8	* The following databases are supported:
9	*
10	* - [http://ibm.com/db2 DB2]
11	* - [http://microsoft.com/sql/ MSSQL]
12	* - [http://mysql.com MySQL]
13	* - [http://oracle.com Oracle]
14	* - [http://postgresql.org PostgreSQL]
15	* - [http://sqlite.org SQLite]
16	*
17	* The class will automatically use the first of the following extensions it finds:
18	*
19	* - DB2
20	* - [http://php.net/ibm_db2 ibm_db2]
21	* - [http://php.net/pdo_ibm pdo_ibm]
22	* - MSSQL
23	* - [http://msdn.microsoft.com/en-us/library/cc296221.aspx sqlsrv]
24	* - [http://php.net/pdo_dblib pdo_dblib]
25	* - [http://php.net/mssql mssql] (or [http://php.net/sybase sybase])
26	* - MySQL
27	* - [http://php.net/mysql mysql]
28	* - [http://php.net/mysqli mysqli]
29	* - [http://php.net/pdo_mysql pdo_mysql]
30	* - Oracle
31	* - [http://php.net/oci8 oci8]
32	* - [http://php.net/pdo_oci pdo_oci]
33	* - PostgreSQL
34	* - [http://php.net/pgsql pgsql]
35	* - [http://php.net/pdo_pgsql pdo_pgsql]
36	* - SQLite
37	* - [http://php.net/pdo_sqlite pdo_sqlite] (for v3.x)
38	* - [http://php.net/sqlite sqlite] (for v2.x)
39	*
40	* The `odbc` and `pdo_odbc` extensions are not supported due to character
41	* encoding and stability issues on Windows, and functionality on non-Windows
42	* operating systems.
43	*
44	* @copyright Copyright (c) 2007-2010 Will Bond
45	* @author Will Bond [wb] <will@flourishlib.com>
46	* @license http://flourishlib.com/license
47	*
48	* @package Flourish
49	* @link http://flourishlib.com/fDatabase
50	*
51	* @version 1.0.0b30
52	* @changes 1.0.0b30 Fixed the pgsql, mssql and mysql extensions to force a new connection instead of reusing an existing one [wb, 2010-08-17]
53	* @changes 1.0.0b29 Backwards Compatibility Break - removed ::enableSlowQueryWarnings(), added ability to replicate via ::registerHookCallback() [wb, 2010-08-10]
54	* @changes 1.0.0b28 Backwards Compatibility Break - removed ODBC support. Added support for the `pdo_ibm` extension. [wb, 2010-07-31]
55	* @changes 1.0.0b27 Fixed a bug with running multiple copies of a SQL statement with string values through a single ::translatedQuery() call [wb, 2010-07-14]
56	* @changes 1.0.0b26 Updated the class to use new fCore functionality [wb, 2010-07-05]
57	* @changes 1.0.0b25 Added IBM DB2 support [wb, 2010-04-13]
58	* @changes 1.0.0b24 Fixed an auto-incrementing transaction bug with Oracle and debugging issues with all databases [wb, 2010-03-17]
59	* @changes 1.0.0b23 Resolved another bug with capturing auto-incrementing values for PostgreSQL and Oracle [wb, 2010-03-15]
60	* @changes 1.0.0b22 Changed ::clearCache() to also clear the cache on the fSQLTranslation [wb, 2010-03-09]
61	* @changes 1.0.0b21 Added ::execute() for result-less SQL queries, ::prepare() and ::translatedPrepare() to create fStatement objects for prepared statements, support for prepared statements in ::query() and ::unbufferedQuery(), fixed default caching key for ::enableCaching() [wb, 2010-03-02]
62	* @changes 1.0.0b20 Added a parameter to ::enableCaching() to provide a key token that will allow cached values to be shared between multiple databases with the same schema [wb, 2009-10-28]
63	* @changes 1.0.0b19 Added support for escaping identifiers (column and table names) to ::escape(), added support for database schemas, rewrote internal SQL string spliting [wb, 2009-10-22]
64	* @changes 1.0.0b18 Updated the class for the new fResult and fUnbufferedResult APIs, fixed ::unescape() to not touch NULLs [wb, 2009-08-12]
65	* @changes 1.0.0b17 Added the ability to pass an array of all values as a single parameter to ::escape() instead of one value per parameter [wb, 2009-08-11]
66	* @changes 1.0.0b16 Fixed PostgreSQL and Oracle from trying to get auto-incrementing values on inserts when explicit values were given [wb, 2009-08-06]
67	* @changes 1.0.0b15 Fixed a bug where auto-incremented values would not be detected when table names were quoted [wb, 2009-07-15]
68	* @changes 1.0.0b14 Changed ::determineExtension() and ::determineCharacterSet() to be protected instead of private [wb, 2009-07-08]
69	* @changes 1.0.0b13 Updated ::escape() to accept arrays of values for insertion into full SQL strings [wb, 2009-07-06]
70	* @changes 1.0.0b12 Updates to ::unescape() to improve performance [wb, 2009-06-15]
71	* @changes 1.0.0b11 Changed replacement values in preg_replace() calls to be properly escaped [wb, 2009-06-11]
72	* @changes 1.0.0b10 Changed date/time/timestamp escaping from `strtotime()` to fDate/fTime/fTimestamp for better localization support [wb, 2009-06-01]
73	* @changes 1.0.0b9 Fixed a bug with ::escape() where floats that start with a . were encoded as `NULL` [wb, 2009-05-09]
74	* @changes 1.0.0b8 Added Oracle support, change PostgreSQL code to no longer cause lastval() warnings, added support for arrays of values to ::escape() [wb, 2009-05-03]
75	* @changes 1.0.0b7 Updated for new fCore API [wb, 2009-02-16]
76	* @changes 1.0.0b6 Fixed a bug with executing transaction queries when using the mysqli extension [wb, 2009-02-12]
77	* @changes 1.0.0b5 Changed @ error suppression operator to `error_reporting()` calls [wb, 2009-01-26]
78	* @changes 1.0.0b4 Added a few error suppression operators back in so that developers don't get errors and exceptions [wb, 2009-01-14]
79	* @changes 1.0.0b3 Removed some unnecessary error suppresion operators [wb, 2008-12-11]
80	* @changes 1.0.0b2 Fixed a bug with PostgreSQL when using the PDO extension and executing an INSERT statement [wb, 2008-12-11]
81	* @changes 1.0.0b The initial implementation [wb, 2007-09-25]
82	*/
83	class fDatabase
84	{
85	/**
86	* Composes text using fText if loaded
87	*
88	* @param string $message The message to compose
89	* @param mixed $component A string or number to insert into the message
90	* @param mixed ...
91	* @return string The composed and possible translated message
92	*/
93	static protected function compose($message)
94	{
95	$args = array_slice(func_get_args(), 1);
96
97	if (class_exists('fText', FALSE)) {
98	return call_user_func_array(
99	array('fText', 'compose'),
100	array($message, $args)
101	);
102	} else {
103	return vsprintf($message, $args);
104	}
105	}
106
107
108	/**
109	* An fCache object to cache the schema info to
110	*
111	* @var fCache
112	*/
113	private $cache;
114
115	/**
116	* The cache prefix to use for cache entries
117	*
118	* @var string
119	*/
120	private $cache_prefix;
121
122	/**
123	* Database connection resource or PDO object
124	*
125	* @var mixed
126	*/
127	private $connection;
128
129	/**
130	* The database name
131	*
132	* @var string
133	*/
134	private $database;
135
136	/**
137	* If debugging is enabled
138	*
139	* @var boolean
140	*/
141	private $debug;
142
143	/**
144	* A temporary error holder for the mssql extension
145	*
146	* @var string
147	*/
148	private $error;
149
150	/**
151	* The extension to use for the database specified
152	*
153	* Options include:
154	*
155	* - `'ibm_db2'`
156	* - `'mssql'`
157	* - `'mysql'`
158	* - `'mysqli'`
159	* - `'oci8'`
160	* - `'pgsql'`
161	* - `'sqlite'`
162	* - `'sqlsrv'`
163	* - `'pdo'`
164	*
165	* @var string
166	*/
167	protected $extension;
168
169	/**
170	* Hooks callbacks to be used for accessing and modifying queries
171	*
172	* This array will have the structure:
173	*
174	* {{{
175	* array(
176	* 'unmodified' => array({callbacks}),
177	* 'extracted' => array({callbacks}),
178	* 'run' => array({callbacks})
179	* )
180	* }}}
181	*
182	* @var array
183	*/
184	private $hook_callbacks;
185
186	/**
187	* The host the database server is located on
188	*
189	* @var string
190	*/
191	private $host;
192
193	/**
194	* If a transaction is in progress
195	*
196	* @var boolean
197	*/
198	private $inside_transaction;
199
200	/**
201	* The password for the user specified
202	*
203	* @var string
204	*/
205	private $password;
206
207	/**
208	* The port number for the host
209	*
210	* @var string
211	*/
212	private $port;
213
214	/**
215	* The total number of seconds spent executing queries
216	*
217	* @var float
218	*/
219	private $query_time;
220
221	/**
222	* A cache of database-specific code
223	*
224	* @var array
225	*/
226	protected $schema_info;
227
228	/**
229	* The last executed fStatement object
230	*
231	* @var fStatement
232	*/
233	private $statement;
234
235	/**
236	* The fSQLTranslation object for this database
237	*
238	* @var object
239	*/
240	private $translation;
241
242	/**
243	* The database type: `'db2'`, `'mssql'`, `'mysql'`, `'oracle'`, `'postgresql'`, or `'sqlite'`
244	*
245	* @var string
246	*/
247	private $type;
248
249	/**
250	* The unbuffered query instance
251	*
252	* @var fUnbufferedResult
253	*/
254	private $unbuffered_result;
255
256	/**
257	* The user to connect to the database as
258	*
259	* @var string
260	*/
261	private $username;
262
263
264	/**
265	* Configures the connection to a database - connection is not made until the first query is executed
266	*
267	* @param string $type The type of the database: `'db2'`, `'mssql'`, `'mysql'`, `'oracle'`, `'postgresql'`, `'sqlite'`
268	* @param string $database Name of the database. If SQLite the path to the database file.
269	* @param string $username Database username - not used for SQLite
270	* @param string $password The password for the username specified - not used for SQLite
271	* @param string $host Database server host or IP, defaults to localhost - not used for SQLite. MySQL socket connection can be made by entering `'sock:'` followed by the socket path. PostgreSQL socket connection can be made by passing just `'sock:'`.
272	* @param integer $port The port to connect to, defaults to the standard port for the database type specified - not used for SQLite
273	* @return fDatabase
274	*/
275	public function __construct($type, $database, $username=NULL, $password=NULL, $host=NULL, $port=NULL)
276	{
277	$valid_types = array('db2', 'mssql', 'mysql', 'oracle', 'postgresql', 'sqlite');
278	if (!in_array($type, $valid_types)) {
279	throw new fProgrammerException(
280	'The database type specified, %1$s, is invalid. Must be one of: %2$s.',
281	$type,
282	join(', ', $valid_types)
283	);
284	}
285
286	if (empty($database)) {
287	throw new fProgrammerException('No database was specified');
288	}
289
290	if ($host === NULL) {
291	$host = 'localhost';
292	}
293
294	$this->type = $type;
295	$this->database = $database;
296	$this->username = $username;
297	$this->password = $password;
298	$this->host = $host;
299	$this->port = $port;
300
301	$this->hook_callbacks = array(
302	'unmodified' => array(),
303	'extracted' => array(),
304	'run' => array()
305	);
306
307	$this->schema_info = array();
308
309	$this->determineExtension();
310	}
311
312
313	/**
314	* Closes the open database connection
315	*
316	* @internal
317	*
318	* @return void
319	*/
320	public function __destruct()
321	{
322	if (!$this->connection) { return; }
323
324	fCore::debug('Total query time: ' . $this->query_time . ' seconds', $this->debug);
325	if ($this->extension == 'ibm_db2') {
326	db2_close($this->connection);
327	} elseif ($this->extension == 'mssql') {
328	mssql_close($this->connection);
329	} elseif ($this->extension == 'mysql') {
330	mysql_close($this->connection);
331	} elseif ($this->extension == 'mysqli') {
332	mysqli_close($this->connection);
333	} elseif ($this->extension == 'oci8') {
334	oci_close($this->connection);
335	} elseif ($this->extension == 'pgsql') {
336	pg_close($this->connection);
337	} elseif ($this->extension == 'sqlite') {
338	sqlite_close($this->connection);
339	} elseif ($this->extension == 'sqlsrv') {
340	sqlsrv_close($this->connection);
341	} elseif ($this->extension == 'pdo') {
342	// PDO objects close their own connections when destroyed
343	}
344	}
345
346
347	/**
348	* All requests that hit this method should be requests for callbacks
349	*
350	* @internal
351	*
352	* @param string $method The method to create a callback for
353	* @return callback The callback for the method requested
354	*/
355	public function __get($method)
356	{
357	return array($this, $method);
358	}
359
360
361	/**
362	* Checks to see if an SQL error occured
363	*
364	* @param fResult\|fUnbufferedResult\|boolean $result The result object for the query
365	* @param mixed $extra_info The sqlite extension will pass a string error message, the oci8 extension will pass the statement resource
366	* @param string $sql The SQL that was executed
367	* @return void
368	*/
369	private function checkForError($result, $extra_info=NULL, $sql=NULL)
370	{
371	if ($result === FALSE \|\| $result->getResult() === FALSE) {
372
373	if ($this->extension == 'ibm_db2') {
374	if (is_resource($extra_info)) {
375	$message = db2_stmt_errormsg($extra_info);
376	} else {
377	$message = db2_stmt_errormsg();
378	}
379	} elseif ($this->extension == 'mssql') {
380	$message = $this->error;
381	unset($this->error);
382	} elseif ($this->extension == 'mysql') {
383	$message = mysql_error($this->connection);
384	} elseif ($this->extension == 'mysqli') {
385	if (is_object($extra_info)) {
386	$message = $extra_info->error;
387	} else {
388	$message = mysqli_error($this->connection);
389	}
390	} elseif ($this->extension == 'oci8') {
391	$error_info = oci_error($extra_info);
392	$message = $error_info['message'];
393	} elseif ($this->extension == 'pgsql') {
394	$message = pg_last_error($this->connection);
395	} elseif ($this->extension == 'sqlite') {
396	$message = $extra_info;
397	} elseif ($this->extension == 'sqlsrv') {
398	$error_info = sqlsrv_errors(SQLSRV_ERR_ALL);
399	$message = $error_info[0]['message'];
400	} elseif ($this->extension == 'pdo') {
401	if ($extra_info instanceof PDOStatement) {
402	$error_info = $extra_info->errorInfo();
403	} else {
404	$error_info = $this->connection->errorInfo();
405	}
406	if (empty($error_info[2])) {
407	$error_info[2] = 'Unknown error - this usually indicates a bug in the PDO driver';
408	}
409	$message = $error_info[2];
410	}
411
412	$db_type_map = array(
413	'db2' => 'DB2',
414	'mssql' => 'MSSQL',
415	'mysql' => 'MySQL',
416	'oracle' => 'Oracle',
417	'postgresql' => 'PostgreSQL',
418	'sqlite' => 'SQLite'
419	);
420
421	throw new fSQLException(
422	'%1$s error (%2$s) in %3$s',
423	$db_type_map[$this->type],
424	$message,
425	is_object($result) ? $result->getSQL() : $sql
426	);
427	}
428	}
429
430
431	/**
432	* Clears all of the schema info out of the object and, if set, the fCache object
433	*
434	* @return void
435	*/
436	public function clearCache()
437	{
438	$this->schema_info = array();
439	if ($this->cache) {
440	$this->cache->delete($this->makeCachePrefix() . 'schema_info');
441	}
442	if ($this->type == 'mssql') {
443	$this->determineCharacterSet();
444	}
445	if ($this->translation) {
446	$this->translation->clearCache();
447	}
448	}
449
450
451	/**
452	* Connects to the database specified if no connection exists
453	*
454	* @return void
455	*/
456	private function connectToDatabase()
457	{
458	// Don't try to reconnect if we are already connected
459	if ($this->connection) { return; }
460
461	// Establish a connection to the database
462	if ($this->extension == 'pdo') {
463	$username = $this->username;
464	$password = $this->password;
465
466	if ($this->type == 'db2') {
467	if ($this->host === NULL && $this->port === NULL) {
468	$dsn = 'ibm:DSN:' . $this->database;
469	} else {
470	$dsn = 'ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=' . $this->database . ';HOSTNAME=' . $this->host . ';';
471	$dsn .= 'PORT=' . ($this->port ? $this->port : 60000) . ';';
472	$dsn .= 'PROTOCOL=TCPIP;UID=' . $username . ';PWD=' . $password . ';';
473	$username = NULL;
474	$password = NULL;
475	}
476
477	} elseif ($this->type == 'mssql') {
478	$separator = (fCore::checkOS('windows')) ? ',' : ':';
479	$port = ($this->port) ? $separator . $this->port : '';
480	$driver = (fCore::checkOs('windows')) ? 'mssql' : 'dblib';
481	$dsn = $driver . ':host=' . $this->host . $port . ';dbname=' . $this->database;
482
483	} elseif ($this->type == 'mysql') {
484	if (substr($this->host, 0, 5) == 'sock:') {
485	$dsn = 'mysql:unix_socket=' . substr($this->host, 5) . ';dbname=' . $this->database;
486	} else {
487	$port = ($this->port) ? ';port=' . $this->port : '';
488	$dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->database . $port;
489	}
490
491	} elseif ($this->type == 'oracle') {
492	$port = ($this->port) ? ':' . $this->port : '';
493	$dsn = 'oci:dbname=' . $this->host . $port . '/' . $this->database . ';charset=AL32UTF8';
494
495	} elseif ($this->type == 'postgresql') {
496
497	$dsn = 'pgsql:dbname=' . $this->database;
498	if ($this->host && $this->host != 'sock:') {
499	$dsn .= ' host=' . $this->host;
500	}
501	if ($this->port) {
502	$dsn .= ' port=' . $this->port;
503	}
504
505	} elseif ($this->type == 'sqlite') {
506	$dsn = 'sqlite:' . $this->database;
507	}
508
509	try {
510	$this->connection = new PDO($dsn, $username, $password);
511	if ($this->type == 'mysql') {
512	$this->connection->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 1);
513	}
514	} catch (PDOException $e) {
515	$this->connection = FALSE;
516	}
517	}
518
519	if ($this->extension == 'sqlite') {
520	$this->connection = sqlite_open($this->database);
521	}
522
523	if ($this->extension == 'ibm_db2') {
524	$username = $this->username;
525	$password = $this->password;
526	if ($this->host === NULL && $this->port === NULL) {
527	$connection_string = $this->database;
528	} else {
529	$connection_string = 'DATABASE=' . $this->database . ';HOSTNAME=' . $this->host . ';';
530	$connection_string .= 'PORT=' . ($this->port ? $this->port : 60000) . ';';
531	$connection_string .= 'PROTOCOL=TCPIP;UID=' . $this->username . ';PWD=' . $this->password . ';';
532	$username = NULL;
533	$password = NULL;
534	}
535	$options = array(
536	'autocommit' => DB2_AUTOCOMMIT_ON,
537	'DB2_ATTR_CASE' => DB2_CASE_LOWER
538	);
539	$this->connection = db2_connect($connection_string, $username, $password, $options);
540	}
541
542	if ($this->extension == 'mssql') {
543	$separator = (fCore::checkOS('windows')) ? ',' : ':';
544	$this->connection = mssql_connect(($this->port) ? $this->host . $separator . $this->port : $this->host, $this->username, $this->password, TRUE);
545	if ($this->connection !== FALSE && mssql_select_db($this->database, $this->connection) === FALSE) {
546	$this->connection = FALSE;
547	}
548	}
549
550	if ($this->extension == 'mysql') {
551	if (substr($this->host, 0, 5) == 'sock:') {
552	$host = substr($this->host, 4);
553	} elseif ($this->port) {
554	$host = $this->host . ':' . $this->port;
555	} else {
556	$host = $this->host;
557	}
558	$this->connection = mysql_connect($host, $this->username, $this->password, TRUE);
559	if ($this->connection !== FALSE && mysql_select_db($this->database, $this->connection) === FALSE) {
560	$this->connection = FALSE;
561	}
562	}
563
564	if ($this->extension == 'mysqli') {
565	if (substr($this->host, 0, 5) == 'sock:') {
566	$this->connection = mysqli_connect('localhost', $this->username, $this->password, $this->database, $this->port, substr($this->host, 5));
567	} elseif ($this->port) {
568	$this->connection = mysqli_connect($this->host, $this->username, $this->password, $this->database, $this->port);
569	} else {
570	$this->connection = mysqli_connect($this->host, $this->username, $this->password, $this->database);
571	}
572	}
573
574	if ($this->extension == 'oci8') {
575	$this->connection = oci_connect($this->username, $this->password, $this->host . ($this->port ? ':' . $this->port : '') . '/' . $this->database, 'AL32UTF8');
576	}
577
578	if ($this->extension == 'pgsql') {
579	$connection_string = "dbname='" . addslashes($this->database) . "'";
580	if ($this->host && $this->host != 'sock:') {
581	$connection_string .= " host='" . addslashes($this->host) . "'";
582	}
583	if ($this->username) {
584	$connection_string .= " user='" . addslashes($this->username) . "'";
585	}
586	if ($this->password) {
587	$connection_string .= " password='" . addslashes($this->password) . "'";
588	}
589	if ($this->port) {
590	$connection_string .= " port='" . $this->port . "'";
591	}
592	$this->connection = pg_connect($connection_string, PGSQL_CONNECT_FORCE_NEW);
593	}
594
595	if ($this->extension == 'sqlsrv') {
596	$options = array(
597	'Database' => $this->database,
598	'UID' => $this->username,
599	'PWD' => $this->password
600	);
601	$this->connection = sqlsrv_connect($this->host . ',' . $this->port, $options);
602	}
603
604	// Ensure the connection was established
605	if ($this->connection === FALSE) {
606	throw new fConnectivityException(
607	'Unable to connect to database'
608	);
609	}
610
611	// Make MySQL act more strict and use UTF-8
612	if ($this->type == 'mysql') {
613	$this->execute("SET SQL_MODE = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE'");
614	$this->execute("SET NAMES 'utf8'");
615	$this->execute("SET CHARACTER SET utf8");
616	}
617
618	// Make SQLite behave like other DBs for assoc arrays
619	if ($this->type == 'sqlite') {
620	$this->execute('PRAGMA short_column_names = 1');
621	}
622
623	// Fix some issues with mssql
624	if ($this->type == 'mssql') {
625	if (!isset($this->schema_info['character_set'])) {
626	$this->determineCharacterSet();
627	}
628	$this->execute('SET TEXTSIZE 65536');
629	$this->execute('SET QUOTED_IDENTIFIER ON');
630	}
631
632	// Make PostgreSQL use UTF-8
633	if ($this->type == 'postgresql') {
634	$this->execute("SET NAMES 'UTF8'");
635	}
636
637	// Oracle has different date and timestamp defaults
638	if ($this->type == 'oracle') {
639	$this->execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
640	$this->execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
641	$this->execute("ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZR'");
642	$this->execute("ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS'");
643	$this->execute("ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH24:MI:SS TZR'");
644	}
645	}
646
647
648	/**
649	* Determines the character set of a SQL Server database
650	*
651	* @return void
652	*/
653	protected function determineCharacterSet()
654	{
655	$this->schema_info['character_set'] = 'WINDOWS-1252';
656	$this->schema_info['character_set'] = $this->query("SELECT 'WINDOWS-' + CONVERT(VARCHAR, COLLATIONPROPERTY(CONVERT(NVARCHAR, DATABASEPROPERTYEX(DB_NAME(), 'Collation')), 'CodePage')) AS charset")->fetchScalar();
657	if ($this->cache) {
658	$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
659	}
660	}
661
662
663	/**
664	* Figures out which extension to use for the database type selected
665	*
666	* @return void
667	*/
668	protected function determineExtension()
669	{
670	switch ($this->type) {
671
672	case 'db2':
673
674	if (extension_loaded('ibm_db2')) {
675	$this->extension = 'ibm_db2';
676
677	} elseif (class_exists('PDO', FALSE) && in_array('ibm', PDO::getAvailableDrivers())) {
678	$this->extension = 'pdo';
679
680	} else {
681	$type = 'DB2';
682	$exts = 'ibm_db2, pdo_ibm';
683	}
684	break;
685
686	case 'mssql':
687
688	if (extension_loaded('sqlsrv')) {
689	$this->extension = 'sqlsrv';
690
691	} elseif (extension_loaded('mssql')) {
692	$this->extension = 'mssql';
693
694	} elseif (class_exists('PDO', FALSE) && (in_array('dblib', PDO::getAvailableDrivers()) \|\| in_array('mssql', PDO::getAvailableDrivers()))) {
695	$this->extension = 'pdo';
696
697	} else {
698	$type = 'MSSQL';
699	$exts = 'mssql, sqlsrv, pdo_dblib (linux), pdo_mssql (windows)';
700	}
701	break;
702
703
704	case 'mysql':
705
706	if (extension_loaded('mysqli')) {
707	$this->extension = 'mysqli';
708
709	} elseif (class_exists('PDO', FALSE) && in_array('mysql', PDO::getAvailableDrivers())) {
710	$this->extension = 'pdo';
711
712	} elseif (extension_loaded('mysql')) {
713	$this->extension = 'mysql';
714
715	} else {
716	$type = 'MySQL';
717	$exts = 'mysql, pdo_mysql, mysqli';
718	}
719	break;
720
721
722	case 'oracle':
723
724	if (extension_loaded('oci8')) {
725	$this->extension = 'oci8';
726
727	} elseif (class_exists('PDO', FALSE) && in_array('oci', PDO::getAvailableDrivers())) {
728	$this->extension = 'pdo';
729
730	} else {
731	$type = 'Oracle';
732	$exts = 'oci8, pdo_oci';
733	}
734	break;
735
736
737	case 'postgresql':
738
739	if (extension_loaded('pgsql')) {
740	$this->extension = 'pgsql';
741
742	} elseif (class_exists('PDO', FALSE) && in_array('pgsql', PDO::getAvailableDrivers())) {
743	$this->extension = 'pdo';
744
745	} else {
746	$type = 'PostgreSQL';
747	$exts = 'pgsql, pdo_pgsql';
748	}
749	break;
750
751
752	case 'sqlite':
753
754	$sqlite_version = 0;
755
756	if (file_exists($this->database)) {
757
758	$database_handle = fopen($this->database, 'r');
759	$database_version = fread($database_handle, 64);
760	fclose($database_handle);
761
762	if (strpos($database_version, 'SQLite format 3') !== FALSE) {
763	$sqlite_version = 3;
764	} elseif (strpos($database_version, ' This file contains an SQLite 2.1 database ') !== FALSE) {
765	$sqlite_version = 2;
766	} else {
767	throw new fConnectivityException(
768	'The database specified does not appear to be a valid %1$s or %2$s database',
769	'SQLite v2.1',
770	'v3'
771	);
772	}
773	}
774
775	if ((!$sqlite_version \|\| $sqlite_version == 3) && class_exists('PDO', FALSE) && in_array('sqlite', PDO::getAvailableDrivers())) {
776	$this->extension = 'pdo';
777
778	} elseif ($sqlite_version == 3 && (!class_exists('PDO', FALSE) \|\| !in_array('sqlite', PDO::getAvailableDrivers()))) {
779	throw new fEnvironmentException(
780	'The database specified is an %1$s database and the %2$s extension is not installed',
781	'SQLite v3',
782	'pdo_sqlite'
783	);
784
785	} elseif ((!$sqlite_version \|\| $sqlite_version == 2) && extension_loaded('sqlite')) {
786	$this->extension = 'sqlite';
787
788	} elseif ($sqlite_version == 2 && !extension_loaded('sqlite')) {
789	throw new fEnvironmentException(
790	'The database specified is an %1$s database and the %2$s extension is not installed',
791	'SQLite v2.1',
792	'sqlite'
793	);
794
795	} else {
796	$type = 'SQLite';
797	$exts = 'pdo_sqlite, sqlite';
798	}
799	break;
800	}
801
802	if (!$this->extension) {
803	throw new fEnvironmentException(
804	'The server does not have any of the following extensions for %2$s support: %2$s',
805	$type,
806	$exts
807	);
808	}
809	}
810
811
812	/**
813	* Sets the schema info to be cached to the fCache object specified
814	*
815	* @param fCache $cache The cache to cache to
816	* @param string $key_token Internal use only! (this will be used in the cache key to uniquely identify the cache for this fDatabase object)
817	* @return void
818	*/
819	public function enableCaching($cache, $key_token=NULL)
820	{
821	$this->cache = $cache;
822
823	if ($key_token !== NULL) {
824	$this->cache_prefix = 'fDatabase::' . $this->type . '::' . $key_token . '::';
825	}
826
827	$this->schema_info = $this->cache->get($this->makeCachePrefix() . 'schema_info', array());
828	}
829
830
831	/**
832	* Sets if debug messages should be shown
833	*
834	* @param boolean $flag If debugging messages should be shown
835	* @return void
836	*/
837	public function enableDebugging($flag)
838	{
839	$this->debug = (boolean) $flag;
840	}
841
842
843	/**
844	* Escapes a value for insertion into SQL
845	*
846	* The valid data types are:
847	*
848	* - `'blob'`
849	* - `'boolean'`
850	* - `'date'`
851	* - `'float'`
852	* - `'identifier'`
853	* - `'integer'`
854	* - `'string'` (also varchar, char or text)
855	* - `'varchar'`
856	* - `'char'`
857	* - `'text'`
858	* - `'time'`
859	* - `'timestamp'`
860	*
861	* In addition to being able to specify the data type, you can also pass
862	* in an SQL statement with data type placeholders in the following form:
863	*
864	* - `%l` for a blob
865	* - `%b` for a boolean
866	* - `%d` for a date
867	* - `%f` for a float
868	* - `%r` for an indentifier (table or column name)
869	* - `%i` for an integer
870	* - `%s` for a string
871	* - `%t` for a time
872	* - `%p` for a timestamp
873	*
874	* Depending on what `$sql_or_type` and `$value` are, the output will be
875	* slightly different. If `$sql_or_type` is a data type or a single
876	* placeholder and `$value` is:
877	*
878	* - a scalar value - an escaped SQL string is returned
879	* - an array - an array of escaped SQL strings is returned
880	*
881	* If `$sql_or_type` is a SQL string and `$value` is:
882	*
883	* - a scalar value - the escaped value is inserted into the SQL string
884	* - an array - the escaped values are inserted into the SQL string separated by commas
885	*
886	* If `$sql_or_type` is a SQL string, it is also possible to pass an array
887	* of all values as a single parameter instead of one value per parameter.
888	* An example would look like the following:
889	*
890	* {{{
891	* #!php
892	* $db->escape(
893	* "SELECT * FROM users WHERE status = %s AND authorization_level = %s",
894	* array('Active', 'Admin')
895	* );
896	* }}}
897	*
898	* @param string $sql_or_type This can either be the data type to escape or an SQL string with a data type placeholder - see method description
899	* @param mixed $value The value to escape - both single values and arrays of values are supported, see method description for details
900	* @param mixed ...
901	* @return mixed The escaped value/SQL or an array of the escaped values
902	*/
903	public function escape($sql_or_type, $value)
904	{
905	$values = array_slice(func_get_args(), 1);
906
907	if (sizeof($values) < 1) {
908	throw new fProgrammerException(
909	'No value was specified to escape'
910	);
911	}
912
913	// Convert all objects into strings
914	$values = $this->scalarize($values);
915
916	$value = array_shift($values);
917
918	// Handle single value escaping
919	$callback = NULL;
920
921	switch ($sql_or_type) {
922	case 'blob':
923	case '%l':
924	$callback = $this->escapeBlob;
925	break;
926	case 'boolean':
927	case '%b':
928	$callback = $this->escapeBoolean;
929	break;
930	case 'date':
931	case '%d':
932	$callback = $this->escapeDate;
933	break;
934	case 'float':
935	case '%f':
936	$callback = $this->escapeFloat;
937	break;
938	case 'identifier':
939	case '%r':
940	$callback = $this->escapeIdentifier;
941	break;
942	case 'integer':
943	case '%i':
944	$callback = $this->escapeInteger;
945	break;
946	case 'string':
947	case 'varchar':
948	case 'char':
949	case 'text':
950	case '%s':
951	$callback = $this->escapeString;
952	break;
953	case 'time':
954	case '%t':
955	$callback = $this->escapeTime;
956	break;
957	case 'timestamp':
958	case '%p':
959	$callback = $this->escapeTimestamp;
960	break;
961	}
962
963	if ($callback) {
964	if (is_array($value)) {
965	// If the values were passed as a single array, this handles that
966	if (count($value) == 1 && is_array(current($value))) {
967	$value = current($value);
968	}
969	return array_map($callback, $value);
970	}
971	return call_user_func($callback, $value);
972	}
973
974	// Fix \' in MySQL and PostgreSQL
975	if(($this->type == 'mysql' \|\| $this->type == 'postgresql') && strpos($sql_or_type, '\\') !== FALSE) {
976	$sql_or_type = preg_replace("#(?<!\\\\)((\\\\{2})*)\\\\'#", "\\1''", $sql_or_type);
977	}
978
979	// Separate the SQL from quoted values
980	$parts = $this->splitSQL($sql_or_type);
981
982	$temp_sql = '';
983	$strings = array();
984
985	// Replace strings with a placeholder so they don't mess up the regex parsing
986	foreach ($parts as $part) {
987	if ($part[0] == "'") {
988	$strings[] = $part;
989	$part = ':string_' . (sizeof($strings)-1);
990	}
991	$temp_sql .= $part;
992	}
993
994	// If the values were passed as a single array, this handles that
995	$placeholders = preg_match_all('#%[lbdfristp]\b#', $temp_sql, $trash);
996	if (count($values) == 0 && is_array($value) && count($value) == $placeholders) {
997	$values = $value;
998	$value = array_shift($values);
999	}
1000
1001	array_unshift($values, $value);
1002
1003	$sql = $this->escapeSQL($temp_sql, $values);
1004
1005	$string_number = 0;
1006	foreach ($strings as $string) {
1007	$string = strtr($string, array('\\' => '\\\\', '$' => '\\$'));
1008	$sql = preg_replace('#:string_' . $string_number++ . '\b#', $string, $sql);
1009	}
1010
1011	return $sql;
1012	}
1013
1014
1015	/**
1016	* Escapes a blob for use in SQL, includes surround quotes when appropriate
1017	*
1018	* A `NULL` value will be returned as `'NULL'`
1019	*
1020	* @param string $value The blob to escape
1021	* @return string The escaped blob
1022	*/
1023	private function escapeBlob($value)
1024	{
1025	if ($value === NULL) {
1026	return 'NULL';
1027	}
1028
1029	$this->connectToDatabase();
1030
1031	if ($this->type == 'db2') {
1032	return "BLOB(X'" . bin2hex($value) . "')";
1033
1034	} elseif ($this->type == 'mysql') {
1035	return "x'" . bin2hex($value) . "'";
1036
1037	} elseif ($this->type == 'postgresql') {
1038	$output = '';
1039	for ($i=0; $i<strlen($value); $i++) {
1040	$output .= '\\\\' . str_pad(decoct(ord($value[$i])), 3, '0', STR_PAD_LEFT);
1041	}
1042	return "E'" . $output . "'";
1043
1044	} elseif ($this->extension == 'sqlite') {
1045	return "'" . bin2hex($value) . "'";
1046
1047	} elseif ($this->type == 'sqlite') {
1048	return "X'" . bin2hex($value) . "'";
1049
1050	} elseif ($this->type == 'mssql') {
1051	return '0x' . bin2hex($value);
1052
1053	} elseif ($this->type == 'oracle') {
1054	return "'" . bin2hex($value) . "'";
1055	}
1056	}
1057
1058
1059	/**
1060	* Escapes a boolean for use in SQL, includes surround quotes when appropriate
1061	*
1062	* A `NULL` value will be returned as `'NULL'`
1063	*
1064	* @param boolean $value The boolean to escape
1065	* @return string The database equivalent of the boolean passed
1066	*/
1067	private function escapeBoolean($value)
1068	{
1069	if ($value === NULL) {
1070	return 'NULL';
1071	}
1072
1073	if (in_array($this->type, array('postgresql', 'mysql'))) {
1074	return ($value) ? 'TRUE' : 'FALSE';
1075	} elseif (in_array($this->type, array('mssql', 'sqlite', 'db2'))) {
1076	return ($value) ? "'1'" : "'0'";
1077	} elseif ($this->type == 'oracle') {
1078	return ($value) ? '1' : '0';
1079	}
1080	}
1081
1082
1083	/**
1084	* Escapes a date for use in SQL, includes surrounding quotes
1085	*
1086	* A `NULL` or invalid value will be returned as `'NULL'`
1087	*
1088	* @param string $value The date to escape
1089	* @return string The escaped date
1090	*/
1091	private function escapeDate($value)
1092	{
1093	if ($value === NULL) {
1094	return 'NULL';
1095	}
1096
1097	try {
1098	$value = new fDate($value);
1099	return "'" . $value->format('Y-m-d') . "'";
1100
1101	} catch (fValidationException $e) {
1102	return 'NULL';
1103	}
1104	}
1105
1106
1107	/**
1108	* Escapes a float for use in SQL
1109	*
1110	* A `NULL` value will be returned as `'NULL'`
1111	*
1112	* @param float $value The float to escape
1113	* @return string The escaped float
1114	*/
1115	private function escapeFloat($value)
1116	{
1117	if ($value === NULL) {
1118	return 'NULL';
1119	}
1120	if (!strlen($value)) {
1121	return 'NULL';
1122	}
1123	if (!preg_match('#^[+\-]?([0-9]+(\.[0-9]+)?\|(\.[0-9]+))$#D', $value)) {
1124	return 'NULL';
1125	}
1126	return (string) $value;
1127	}
1128
1129
1130	/**
1131	* Escapes an identifier for use in SQL, necessary for reserved words
1132	*
1133	* @param string $value The identifier to escape
1134	* @return string The escaped identifier
1135	*/
1136	private function escapeIdentifier($value)
1137	{
1138	$value = '"' . str_replace(
1139	array('"', '.'),
1140	array('', '"."'),
1141	$value
1142	) . '"';
1143	if (in_array($this->type, array('oracle', 'db2'))) {
1144	$value = strtoupper($value);
1145	}
1146	return $value;
1147	}
1148
1149
1150	/**
1151	* Escapes an integer for use in SQL
1152	*
1153	* A `NULL` or invalid value will be returned as `'NULL'`
1154	*
1155	* @param integer $value The integer to escape
1156	* @return string The escaped integer
1157	*/
1158	private function escapeInteger($value)
1159	{
1160	if ($value === NULL) {
1161	return 'NULL';
1162	}
1163	if (!strlen($value)) {
1164	return 'NULL';
1165	}
1166	if (!preg_match('#^([+\-]?[0-9]+)(\.[0-9]*)?$#D', $value, $matches)) {
1167	return 'NULL';
1168	}
1169	return str_replace('+', '', $matches[1]);
1170	}
1171
1172
1173	/**
1174	* Escapes a string for use in SQL, includes surrounding quotes
1175	*
1176	* A `NULL` value will be returned as `'NULL'`.
1177	*
1178	* @param string $value The string to escape
1179	* @return string The escaped string
1180	*/
1181	private function escapeString($value)
1182	{
1183	if ($value === NULL) {
1184	return 'NULL';
1185	}
1186
1187	$this->connectToDatabase();
1188
1189	if ($this->type == 'db2') {
1190	return "'" . str_replace("'", "''", $value) . "'";
1191	} elseif ($this->extension == 'mysql') {
1192	return "'" . mysql_real_escape_string($value, $this->connection) . "'";
1193	} elseif ($this->extension == 'mysqli') {
1194	return "'" . mysqli_real_escape_string($this->connection, $value) . "'";
1195	} elseif ($this->extension == 'pgsql') {
1196	return "'" . pg_escape_string($value) . "'";
1197	} elseif ($this->extension == 'sqlite') {
1198	return "'" . sqlite_escape_string($value) . "'";
1199	} elseif ($this->type == 'oracle') {
1200	return "'" . str_replace("'", "''", $value) . "'";
1201
1202	} elseif ($this->type == 'mssql') {
1203
1204	// If there are any non-ASCII characters, we need to escape
1205	if (preg_match('#[^\x00-\x7F]#', $value)) {
1206	preg_match_all('#.\|^\z#us', $value, $characters);
1207	$output = "";
1208	$last_type = NULL;
1209	foreach ($characters[0] as $character) {
1210	if (strlen($character) > 1) {
1211	$b = array_map('ord', str_split($character));
1212	switch (strlen($character)) {
1213	case 2:
1214	$bin = substr(decbin($b[0]), 3) .
1215	substr(decbin($b[1]), 2);
1216	break;
1217
1218	case 3:
1219	$bin = substr(decbin($b[0]), 4) .
1220	substr(decbin($b[1]), 2) .
1221	substr(decbin($b[2]), 2);
1222	break;
1223
1224	// If it is a 4-byte character, MSSQL can't store it
1225	// so instead store a ?
1226	default:
1227	$output .= '?';
1228	continue;
1229	}
1230	if ($last_type == 'nchar') {
1231	$output .= '+';
1232	} elseif ($last_type == 'char') {
1233	$output .= "'+";
1234	}
1235	$output .= "NCHAR(" . bindec($bin) . ")";
1236	$last_type = 'nchar';
1237	} else {
1238	if (!$last_type) {
1239	$output .= "'";
1240	} elseif ($last_type == 'nchar') {
1241	$output .= "+'";
1242	}
1243	$output .= $character;
1244	// Escape single quotes
1245	if ($character == "'") {
1246	$output .= "'";
1247	}
1248	$last_type = 'char';
1249	}
1250	}
1251	if ($last_type == 'char') {
1252	$output .= "'";
1253	} elseif (!$last_type) {
1254	$output .= "''";
1255	}
1256
1257	// ASCII text is normal
1258	} else {
1259	$output = "'" . str_replace("'", "''", $value) . "'";
1260	}
1261
1262	# a \ before a \r\n has to be escaped with another \
1263	return preg_replace('#(?<!\\\\)\\\\(?=\r\n)#', '\\\\\\\\', $output);
1264
1265	} elseif ($this->extension == 'pdo') {
1266	return $this->connection->quote($value);
1267	}
1268	}
1269
1270
1271	/**
1272	* Takes a SQL string and an array of values and replaces the placeholders with the value
1273	*
1274	* @param string $sql The SQL string containing placeholders
1275	* @param array $values An array of values to escape into the SQL
1276	* @return string The SQL with the values escaped into it
1277	*/
1278	private function escapeSQL($sql, $values)
1279	{
1280	$original_sql = $sql;
1281	$pieces = preg_split('#(%[lbdfristp])\b#', $sql, -1, PREG_SPLIT_DELIM_CAPTURE\|PREG_SPLIT_NO_EMPTY);
1282
1283	$sql = '';
1284	$value = array_shift($values);
1285
1286	$missing_values = -1;
1287
1288	foreach ($pieces as $piece) {
1289	switch ($piece) {
1290	case '%l':
1291	$callback = $this->escapeBlob;
1292	break;
1293	case '%b':
1294	$callback = $this->escapeBoolean;
1295	break;
1296	case '%d':
1297	$callback = $this->escapeDate;
1298	break;
1299	case '%f':
1300	$callback = $this->escapeFloat;
1301	break;
1302	case '%r':
1303	$callback = $this->escapeIdentifier;
1304	break;
1305	case '%i':
1306	$callback = $this->escapeInteger;
1307	break;
1308	case '%s':
1309	$callback = $this->escapeString;
1310	break;
1311	case '%t':
1312	$callback = $this->escapeTime;
1313	break;
1314	case '%p':
1315	$callback = $this->escapeTimestamp;
1316	break;
1317	default:
1318	$sql .= $piece;
1319	continue 2;
1320	}
1321
1322	if (is_array($value)) {
1323	$sql .= join(', ', array_map($callback, $value));
1324	} else {
1325	$sql .= call_user_func($callback, $value);
1326	}
1327
1328	if (sizeof($values)) {
1329	$value = array_shift($values);
1330	} else {
1331	$value = NULL;
1332	$missing_values++;
1333	}
1334	}
1335
1336	if ($missing_values > 0) {
1337	throw new fProgrammerException(
1338	'%1$s value(s) are missing for the placeholders in: %2$s',
1339	$missing_values,
1340	$original_sql
1341	);
1342	}
1343
1344	if (sizeof($values)) {
1345	throw new fProgrammerException(
1346	'%1$s extra value(s) were passed for the placeholders in: %2$s',
1347	sizeof($values),
1348	$original_sql
1349	);
1350	}
1351
1352	return $sql;
1353	}
1354
1355
1356	/**
1357	* Escapes a time for use in SQL, includes surrounding quotes
1358	*
1359	* A `NULL` or invalid value will be returned as `'NULL'`
1360	*
1361	* @param string $value The time to escape
1362	* @return string The escaped time
1363	*/
1364	private function escapeTime($value)
1365	{
1366	if ($value === NULL) {
1367	return 'NULL';
1368	}
1369
1370	try {
1371	$value = new fTime($value);
1372
1373	if ($this->type == 'mssql' \|\| $this->type == 'oracle') {
1374	return "'" . $value->format('1970-01-01 H:i:s') . "'";
1375	}
1376
1377	return "'" . $value->format('H:i:s') . "'";
1378
1379	} catch (fValidationException $e) {
1380	return 'NULL';
1381	}
1382	}
1383
1384
1385	/**
1386	* Escapes a timestamp for use in SQL, includes surrounding quotes
1387	*
1388	* A `NULL` or invalid value will be returned as `'NULL'`
1389	*
1390	* @param string $value The timestamp to escape
1391	* @return string The escaped timestamp
1392	*/
1393	private function escapeTimestamp($value)
1394	{
1395	if ($value === NULL) {
1396	return 'NULL';
1397	}
1398
1399	try {
1400	$value = new fTimestamp($value);
1401	return "'" . $value->format('Y-m-d H:i:s') . "'";
1402
1403	} catch (fValidationException $e) {
1404	return 'NULL';
1405	}
1406	}
1407
1408
1409	/**
1410	* Executes one or more SQL queries without returning any results
1411	*
1412	* @param string\|fStatement $statement One or more SQL statements in a string or an fStatement prepared statement
1413	* @param mixed $value The optional value(s) to place into any placeholders in the SQL - see ::escape() for details
1414	* @param mixed ...
1415	* @return void
1416	*/
1417	public function execute($statement)
1418	{
1419	$args = func_get_args();
1420	$params = array_slice($args, 1);
1421
1422	if (is_object($statement)) {
1423	return $this->run($statement, NULL, $params);
1424	}
1425
1426	$queries = $this->prepareSQL($statement, $params, FALSE);
1427
1428	$output = array();
1429	foreach ($queries as $query) {
1430	$this->run($query);
1431	}
1432	}
1433
1434
1435	/**
1436	* Takes in a string of SQL that contains multiple queries and returns any array of them
1437	*
1438	* @param string $sql The string of SQL to parse for queries
1439	* @return array The individual SQL queries
1440	*/
1441	private function explodeQueries($sql)
1442	{
1443	$sql_queries = array();
1444
1445	// Separate the SQL from quoted values
1446	preg_match_all("#(?:'([^'](?:''))*?')\|(?:[^']+)#", $sql, $matches);
1447
1448	$cur_sql = '';
1449	foreach ($matches[0] as $match) {
1450
1451	// This is a quoted string value, don't do anything to it
1452	if ($match[0] == "'") {
1453	$cur_sql .= $match;
1454
1455	// Handle the SQL, exploding on any ; that isn't escaped with a \
1456	} else {
1457	$sql_strings = preg_split('#(?<!\\\\);#', $match);
1458	$cur_sql .= $sql_strings[0];
1459	for ($i=1; $i < sizeof($sql_strings); $i++) {
1460	$cur_sql = trim($cur_sql);
1461	if ($cur_sql) {
1462	$sql_queries[] = $cur_sql;
1463	}
1464	$cur_sql = $sql_strings[$i];
1465	}
1466	}
1467	}
1468	if (trim($cur_sql)) {
1469	$sql_queries[] = $cur_sql;
1470	}
1471
1472	return $sql_queries;
1473	}
1474
1475
1476	/**
1477	* Returns the database connection resource or object
1478	*
1479	* @return mixed The database connection
1480	*/
1481	public function getConnection()
1482	{
1483	$this->connectToDatabase();
1484	return $this->connection;
1485	}
1486
1487
1488	/**
1489	* Gets the name of the database currently connected to
1490	*
1491	* @return string The name of the database currently connected to
1492	*/
1493	public function getDatabase()
1494	{
1495	return $this->database;
1496	}
1497
1498
1499	/**
1500	* Gets the php extension being used
1501	*
1502	* @internal
1503	*
1504	* @return string The php extension used for database interaction
1505	*/
1506	public function getExtension()
1507	{
1508	return $this->extension;
1509	}
1510
1511
1512	/**
1513	* Gets the host for this database
1514	*
1515	* @return string The host
1516	*/
1517	public function getHost()
1518	{
1519	return $this->host;
1520	}
1521
1522
1523	/**
1524	* Gets the port for this database
1525	*
1526	* @return string The port
1527	*/
1528	public function getPort()
1529	{
1530	return $this->port;
1531	}
1532
1533
1534	/**
1535	* Gets the fSQLTranslation object used for translated queries
1536	*
1537	* @return fSQLTranslation The SQL translation object
1538	*/
1539	public function getSQLTranslation()
1540	{
1541	if (!$this->translation) { new fSQLTranslation($this); }
1542	return $this->translation;
1543	}
1544
1545
1546	/**
1547	* Gets the database type
1548	*
1549	* @return string The database type: `'mssql'`, `'mysql'`, `'postgresql'` or `'sqlite'`
1550	*/
1551	public function getType()
1552	{
1553	return $this->type;
1554	}
1555
1556
1557	/**
1558	* Gets the username for this database
1559	*
1560	* @return string The username
1561	*/
1562	public function getUsername()
1563	{
1564	return $this->username;
1565	}
1566
1567
1568	/**
1569	* Will grab the auto incremented value from the last query (if one exists)
1570	*
1571	* @param fResult $result The result object for the query
1572	* @param mixed $resource Only applicable for `pdo`, `oci8` and `sqlsrv` extentions or `mysqli` prepared statements - this is either the `PDOStatement` object, `mysqli_stmt` object or the `oci8` or `sqlsrv` resource
1573	* @return void
1574	*/
1575	private function handleAutoIncrementedValue($result, $resource=NULL)
1576	{
1577	if (!preg_match('#^\s*INSERT\s+INTO\s+(?:`\|"\|\[)?(["\w.]+)(?:`\|"\|\])?#i', $result->getSQL(), $table_match)) {
1578	$result->setAutoIncrementedValue(NULL);
1579	return;
1580	}
1581	$quoted_table = $table_match[1];
1582	$table = str_replace('"', '', strtolower($table_match[1]));
1583
1584	$insert_id = NULL;
1585
1586	if ($this->type == 'oracle') {
1587	if (!isset($this->schema_info['sequences'])) {
1588	$sql = "SELECT
1589	LOWER(OWNER) AS \"SCHEMA\",
1590	LOWER(TABLE_NAME) AS \"TABLE\",
1591	TRIGGER_BODY
1592	FROM
1593	ALL_TRIGGERS
1594	WHERE
1595	TRIGGERING_EVENT = 'INSERT' AND
1596	STATUS = 'ENABLED' AND
1597	TRIGGER_NAME NOT LIKE 'BIN\$%' AND
1598	OWNER NOT IN (
1599	'SYS',
1600	'SYSTEM',
1601	'OUTLN',
1602	'ANONYMOUS',
1603	'AURORA\$ORB\$UNAUTHENTICATED',
1604	'AWR_STAGE',
1605	'CSMIG',
1606	'CTXSYS',
1607	'DBSNMP',
1608	'DIP',
1609	'DMSYS',
1610	'DSSYS',
1611	'EXFSYS',
1612	'FLOWS_020100',
1613	'FLOWS_FILES',
1614	'LBACSYS',
1615	'MDSYS',
1616	'ORACLE_OCM',
1617	'ORDPLUGINS',
1618	'ORDSYS',
1619	'PERFSTAT',
1620	'TRACESVR',
1621	'TSMSYS',
1622	'XDB'
1623	)";
1624
1625	$this->schema_info['sequences'] = array();
1626
1627	foreach ($this->query($sql) as $row) {
1628	if (preg_match('#SELECT\s+(["\w.]+).nextval\s+INTO\s+:new\.(\w+)\s+FROM\s+dual#i', $row['trigger_body'], $matches)) {
1629	$table_name = $row['table'];
1630	if ($row['schema'] != strtolower($this->username)) {
1631	$table_name = $row['schema'] . '.' . $table_name;
1632	}
1633	$this->schema_info['sequences'][$table_name] = array('sequence' => $matches[1], 'column' => str_replace('"', '', $matches[2]));
1634	}
1635	}
1636
1637	if ($this->cache) {
1638	$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
1639	}
1640	}
1641
1642	if (!isset($this->schema_info['sequences'][$table]) \|\| preg_match('#INSERT\s+INTO\s+"?' . preg_quote($quoted_table, '#') . '"?\s+$[^$]*?(\b\|")' . preg_quote($this->schema_info['sequences'][$table]['column'], '#') . '(\b\|")#i', $result->getSQL())) {
1643	return;
1644	}
1645
1646	$insert_id_sql = "SELECT " . $this->schema_info['sequences'][$table]['sequence'] . ".currval AS INSERT_ID FROM dual";
1647	}
1648
1649	if ($this->type == 'postgresql') {
1650	if (!isset($this->schema_info['sequences'])) {
1651	$sql = "SELECT
1652	pg_namespace.nspname AS \"schema\",
1653	pg_class.relname AS \"table\",
1654	pg_attribute.attname AS column
1655	FROM
1656	pg_attribute INNER JOIN
1657	pg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN
1658	pg_namespace ON pg_class.relnamespace = pg_namespace.oid INNER JOIN
1659	pg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
1660	WHERE
1661	NOT pg_attribute.attisdropped AND
1662	pg_attrdef.adsrc LIKE 'nextval(%'";
1663
1664	$this->schema_info['sequences'] = array();
1665
1666	foreach ($this->query($sql) as $row) {
1667	$table_name = strtolower($row['table']);
1668	if ($row['schema'] != 'public') {
1669	$table_name = $row['schema'] . '.' . $table_name;
1670	}
1671	$this->schema_info['sequences'][$table_name] = $row['column'];
1672	}
1673
1674	if ($this->cache) {
1675	$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
1676	}
1677	}
1678
1679	if (!isset($this->schema_info['sequences'][$table]) \|\| preg_match('#INSERT\s+INTO\s+"?' . preg_quote($quoted_table, '#') . '"?\s+$[^$]*?(\b\|")' . preg_quote($this->schema_info['sequences'][$table], '#') . '(\b\|")#i', $result->getSQL())) {
1680	return;
1681	}
1682	}
1683
1684	if ($this->extension == 'ibm_db2') {
1685	$insert_id_res = db2_exec($this->connection, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
1686	$insert_id_row = db2_fetch_assoc($insert_id_res);
1687	$insert_id = current($insert_id_row);
1688	db2_free_result($insert_id_res);
1689
1690	} elseif ($this->extension == 'mssql') {
1691	$insert_id_res = mssql_query("SELECT @@IDENTITY AS insert_id", $this->connection);
1692	$insert_id = mssql_result($insert_id_res, 0, 'insert_id');
1693	mssql_free_result($insert_id_res);
1694
1695	} elseif ($this->extension == 'mysql') {
1696	$insert_id = mysql_insert_id($this->connection);
1697
1698	} elseif ($this->extension == 'mysqli') {
1699	if (is_object($resource)) {
1700	$insert_id = mysqli_stmt_insert_id($resource);
1701	} else {
1702	$insert_id = mysqli_insert_id($this->connection);
1703	}
1704
1705	} elseif ($this->extension == 'oci8') {
1706	$oci_statement = oci_parse($this->connection, $insert_id_sql);
1707	oci_execute($oci_statement, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS);
1708	$insert_id_row = oci_fetch_array($oci_statement, OCI_ASSOC);
1709	$insert_id = $insert_id_row['INSERT_ID'];
1710	oci_free_statement($oci_statement);
1711
1712	} elseif ($this->extension == 'pgsql') {
1713
1714	$insert_id_res = pg_query($this->connection, "SELECT lastval()");
1715	$insert_id_row = pg_fetch_assoc($insert_id_res);
1716	$insert_id = array_shift($insert_id_row);
1717	pg_free_result($insert_id_res);
1718
1719	} elseif ($this->extension == 'sqlite') {
1720	$insert_id = sqlite_last_insert_rowid($this->connection);
1721
1722	} elseif ($this->extension == 'sqlsrv') {
1723	$insert_id_res = sqlsrv_query($this->connection, "SELECT @@IDENTITY AS insert_id");
1724	$insert_id_row = sqlsrv_fetch_array($insert_id_res, SQLSRV_FETCH_ASSOC);
1725	$insert_id = $insert_id_row['insert_id'];
1726	sqlsrv_free_stmt($insert_id_res);
1727
1728	} elseif ($this->extension == 'pdo') {
1729
1730	switch ($this->type) {
1731	case 'db2':
1732	$insert_id_statement = $this->connection->query("SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
1733	$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
1734	$insert_id = array_shift($insert_id_row);
1735	$insert_id_statement->closeCursor();
1736	unset($insert_id_statement);
1737	break;
1738
1739	case 'mssql':
1740	try {
1741	$insert_id_statement = $this->connection->query("SELECT @@IDENTITY AS insert_id");
1742	if (!$insert_id_statement) {
1743	throw new Exception();
1744	}
1745
1746	$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
1747	$insert_id = array_shift($insert_id_row);
1748
1749	} catch (Exception $e) {
1750	// If there was an error we don't have an insert id
1751	}
1752	break;
1753
1754	case 'oracle':
1755	try {
1756	$insert_id_statement = $this->connection->query($insert_id_sql);
1757	if (!$insert_id_statement) {
1758	throw new Exception();
1759	}
1760
1761	$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
1762	$insert_id = array_shift($insert_id_row);
1763
1764	} catch (Exception $e) {
1765	// If there was an error we don't have an insert id
1766	}
1767	break;
1768
1769	case 'postgresql':
1770
1771	$insert_id_statement = $this->connection->query("SELECT lastval()");
1772	$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
1773	$insert_id = array_shift($insert_id_row);
1774	$insert_id_statement->closeCursor();
1775	unset($insert_id_statement);
1776
1777	break;
1778
1779	case 'mysql':
1780	$insert_id = $this->connection->lastInsertId();
1781	break;
1782
1783	case 'sqlite':
1784	$insert_id = $this->connection->lastInsertId();
1785	break;
1786	}
1787	}
1788
1789	$result->setAutoIncrementedValue($insert_id);
1790	}
1791
1792
1793	/**
1794	* Handles a PHP error to extract error information for the mssql extension
1795	*
1796	* @param array $errors An array of error information from fCore::stopErrorCapture()
1797	* @return void
1798	*/
1799	private function handleErrors($errors)
1800	{
1801	if ($this->extension != 'mssql') {
1802	return;
1803	}
1804
1805	foreach ($errors as $error) {
1806	if (substr($error['string'], 0, 14) == 'mssql_query():') {
1807	if ($this->error) {
1808	$this->error .= " ";
1809	}
1810	$this->error .= preg_replace('#^mssql_query: ([^:]+: )?#', '', $error['string']);
1811	}
1812	}
1813	}
1814
1815
1816	/**
1817	* Makes sure each database and extension handles BEGIN, COMMIT and ROLLBACK
1818	*
1819	* @param string &$sql The SQL to check for a transaction query
1820	* @param string $result_class The type of result object to create
1821	* @return mixed `FALSE` if normal processing should continue, otherwise an object of the type $result_class
1822	*/
1823	private function handleTransactionQueries(&$sql, $result_class)
1824	{
1825	// SQL Server supports transactions, but starts then with BEGIN TRANSACTION
1826	if ($this->type == 'mssql' && preg_match('#^\s(begin\|start(\s+transaction)?)\s#i', $sql)) {
1827	$sql = 'BEGIN TRANSACTION';
1828	}
1829
1830	$begin = FALSE;
1831	$commit = FALSE;
1832	$rollback = FALSE;
1833
1834	// Track transactions since most databases don't support nesting
1835	if (preg_match('#^\s(begin\|start)(\s+(transaction\|work))?\s$#iD', $sql)) {
1836	if ($this->inside_transaction) {
1837	throw new fProgrammerException('A transaction is already in progress');
1838	}
1839	$this->inside_transaction = TRUE;
1840	$begin = TRUE;
1841
1842	} elseif (preg_match('#^\s(commit)(\s+(transaction\|work))?\s$#iD', $sql)) {
1843	if (!$this->inside_transaction) {
1844	throw new fProgrammerException('There is no transaction in progress');
1845	}
1846	$this->inside_transaction = FALSE;
1847	$commit = TRUE;
1848
1849	} elseif (preg_match('#^\s(rollback)(\s+(transaction\|work))?\s$#iD', $sql)) {
1850	if (!$this->inside_transaction) {
1851	throw new fProgrammerException('There is no transaction in progress');
1852	}
1853	$this->inside_transaction = FALSE;
1854	$rollback = TRUE;
1855	}
1856
1857	if (!$begin && !$commit && !$rollback) {
1858	return FALSE;
1859	}
1860
1861	// The PDO, OCI8 and SQLSRV extensions require special handling through methods and functions
1862	$is_pdo = $this->extension == 'pdo';
1863	$is_oci = $this->extension == 'oci8';
1864	$is_sqlsrv = $this->extension == 'sqlsrv';
1865	$is_ibm_db2 = $this->extension == 'ibm_db2';
1866
1867	if (!$is_pdo && !$is_oci && !$is_sqlsrv && !$is_ibm_db2) {
1868	return FALSE;
1869	}
1870
1871	$this->statement = $sql;
1872
1873	// PDO seems to act weird if you try to start transactions through a normal query call
1874	if ($is_pdo) {
1875	try {
1876	$is_mssql = $this->type == 'mssql' && substr($this->database, 0, 4) != 'dsn:';
1877	$is_oracle = $this->type == 'oracle' && substr($this->database, 0, 4) != 'dsn:';
1878	if ($begin) {
1879	// The SQL Server PDO object hasn't implemented transactions
1880	if ($is_mssql) {
1881	$this->connection->exec('BEGIN TRANSACTION');
1882	} elseif ($is_oracle) {
1883	$this->connection->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
1884	} else {
1885	$this->connection->beginTransaction();
1886	}
1887
1888	} elseif ($commit) {
1889	if ($is_mssql) {
1890	$this->connection->exec('COMMIT');
1891	} elseif ($is_oracle) {
1892	$this->connection->exec('COMMIT');
1893	$this->connection->setAttribute(PDO::ATTR_AUTOCOMMIT, TRUE);
1894	} else {
1895	$this->connection->commit();
1896	}
1897
1898	} elseif ($rollback) {
1899	if ($is_mssql) {
1900	$this->connection->exec('ROLLBACK');
1901	} elseif ($is_oracle) {
1902	$this->connection->exec('ROLLBACK');
1903	$this->connection->setAttribute(PDO::ATTR_AUTOCOMMIT, TRUE);
1904	} else {
1905	$this->connection->rollBack();
1906	}
1907	}
1908
1909	} catch (Exception $e) {
1910
1911	$db_type_map = array(
1912	'db2' => 'DB2',
1913	'mssql' => 'MSSQL',
1914	'mysql' => 'MySQL',
1915	'oracle' => 'Oracle',
1916	'postgresql' => 'PostgreSQL',
1917	'sqlite' => 'SQLite'
1918	);
1919
1920	throw new fSQLException(
1921	'%1$s error (%2$s) in %3$s',
1922	$db_type_map[$this->type],
1923	$e->getMessage(),
1924	$sql
1925	);
1926	}
1927
1928	} elseif ($is_oci) {
1929	if ($commit) {
1930	oci_commit($this->connection);
1931	} elseif ($rollback) {
1932	oci_rollback($this->connection);
1933	}
1934
1935	} elseif ($is_sqlsrv) {
1936	if ($begin) {
1937	sqlsrv_begin_transaction($this->connection);
1938	} elseif ($commit) {
1939	sqlsrv_commit($this->connection);
1940	} elseif ($rollback) {
1941	sqlsrv_rollback($this->connection);
1942	}
1943
1944	} elseif ($is_ibm_db2) {
1945	if ($begin) {
1946	db2_autocommit($this->connection, FALSE);
1947	} elseif ($commit) {
1948	db2_commit($this->connection);
1949	db2_autocommit($this->connection, TRUE);
1950	} elseif ($rollback) {
1951	db2_rollback($this->connection);
1952	db2_autocommit($this->connection, TRUE);
1953	}
1954	}
1955
1956	if ($result_class) {
1957	$result = new $result_class($this);
1958	$result->setSQL($sql);
1959	$result->setResult(TRUE);
1960	return $result;
1961	}
1962
1963	return TRUE;
1964	}
1965
1966
1967	/**
1968	* Injects an fSQLTranslation object to handle translation
1969	*
1970	* @internal
1971	*
1972	* @param fSQLTranslation $sql_translation The SQL translation object
1973	* @return void
1974	*/
1975	public function inject($sql_translation)
1976	{
1977	$this->translation = $sql_translation;
1978	}
1979
1980
1981	/**
1982	* Will indicate if a transaction is currently in progress
1983	*
1984	* @return boolean If a transaction has been started and not yet rolled back or committed
1985	*/
1986	public function isInsideTransaction()
1987	{
1988	return $this->inside_transaction;
1989	}
1990
1991
1992	/**
1993	* Creates a unique cache prefix to help prevent cache conflicts
1994	*
1995	* @return string The cache prefix to use
1996	*/
1997	private function makeCachePrefix()
1998	{
1999	if (!$this->cache_prefix) {
2000	$prefix = 'fDatabase::' . $this->type . '::';
2001	if ($this->host) {
2002	$prefix .= $this->host . '::';
2003	}
2004	if ($this->port) {
2005	$prefix .= $this->port . '::';
2006	}
2007	$prefix .= $this->database . '::';
2008	if ($this->username) {
2009	$prefix .= $this->username . '::';
2010	}
2011	$this->cache_prefix = $prefix;
2012	}
2013
2014	return $this->cache_prefix;
2015	}
2016
2017
2018	/**
2019	* Executes a SQL statement
2020	*
2021	* @param string\|fStatement $statement The statement to perform
2022	* @param array $params The parameters for prepared statements
2023	* @return void
2024	*/
2025	private function perform($statement, $params)
2026	{
2027	fCore::startErrorCapture();
2028
2029	$extra = NULL;
2030	if (is_object($statement)) {
2031	$result = $statement->execute($params, $extra, $statement != $this->statement);
2032	} elseif ($this->extension == 'ibm_db2') {
2033	$result = db2_exec($this->connection, $statement, array('cursor' => DB2_FORWARD_ONLY));
2034	} elseif ($this->extension == 'mssql') {
2035	$result = mssql_query($statement, $this->connection);
2036	} elseif ($this->extension == 'mysql') {
2037	$result = mysql_unbuffered_query($statement, $this->connection);
2038	} elseif ($this->extension == 'mysqli') {
2039	$result = mysqli_query($this->connection, $statement, MYSQLI_USE_RESULT);
2040	} elseif ($this->extension == 'oci8') {
2041	$extra = oci_parse($this->connection, $statement);
2042	$result = oci_execute($extra, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS);
2043	} elseif ($this->extension == 'pgsql') {
2044	$result = pg_query($this->connection, $statement);
2045	} elseif ($this->extension == 'sqlite') {
2046	$result = sqlite_exec($this->connection, $statement, $extra);
2047	} elseif ($this->extension == 'sqlsrv') {
2048	$result = sqlsrv_query($this->connection, $statement);
2049	} elseif ($this->extension == 'pdo') {
2050	if ($this->type == 'mssql' && !fCore::checkOS('windows')) {
2051	$result = $this->connection->query($statement);
2052	if ($result instanceof PDOStatement) {
2053	$result->closeCursor();
2054	}
2055	} else {
2056	$result = $this->connection->exec($statement);
2057	}
2058	}
2059	$this->statement = $statement;
2060
2061	$this->handleErrors(fCore::stopErrorCapture());
2062
2063	if ($result === FALSE) {
2064	$this->checkForError($result, $extra, is_object($statement) ? $statement->getSQL() : $statement);
2065
2066	} elseif (!is_bool($result) && $result !== NULL) {
2067	if ($this->extension == 'ibm_db2') {
2068	db2_free_result($result);
2069	} elseif ($this->extension == 'mssql') {
2070	mssql_free_result($result);
2071	} elseif ($this->extension == 'mysql') {
2072	mysql_free_result($result);
2073	} elseif ($this->extension == 'mysqli') {
2074	mysqli_free_result($result);
2075	} elseif ($this->extension == 'oci8') {
2076	oci_free_statement($oci_statement);
2077	} elseif ($this->extension == 'pgsql') {
2078	pg_free_result($result);
2079	} elseif ($this->extension == 'sqlsrv') {
2080	sqlsrv_free_stmt($result);
2081	}
2082	}
2083	}
2084
2085
2086	/**
2087	* Executes an SQL query
2088	*
2089	* @param string\|fStatement $statement The statement to perform
2090	* @param fResult $result The result object for the query
2091	* @param array $params The parameters for prepared statements
2092	* @return void
2093	*/
2094	private function performQuery($statement, $result, $params)
2095	{
2096	fCore::startErrorCapture();
2097
2098	$extra = NULL;
2099	if (is_object($statement)) {
2100	$statement->executeQuery($result, $params, $extra, $statement != $this->statement);
2101
2102	} elseif ($this->extension == 'ibm_db2') {
2103	$extra = db2_exec($this->connection, $statement, array('cursor' => DB2_FORWARD_ONLY));
2104	if (is_resource($extra)) {
2105	$rows = array();
2106	while ($row = db2_fetch_assoc($extra)) {
2107	$rows[] = $row;
2108	}
2109	$result->setResult($rows);
2110	unset($rows);
2111	} else {
2112	$result->setResult($extra);
2113	}
2114
2115	} elseif ($this->extension == 'mssql') {
2116	$result->setResult(mssql_query($result->getSQL(), $this->connection));
2117
2118	} elseif ($this->extension == 'mysql') {
2119	$result->setResult(mysql_query($result->getSQL(), $this->connection));
2120
2121	} elseif ($this->extension == 'mysqli') {
2122	$result->setResult(mysqli_query($this->connection, $result->getSQL()));
2123
2124	} elseif ($this->extension == 'oci8') {
2125	$extra = oci_parse($this->connection, $result->getSQL());
2126	if (oci_execute($extra, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) {
2127	oci_fetch_all($extra, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
2128	$result->setResult($rows);
2129	unset($rows);
2130	} else {
2131	$result->setResult(FALSE);
2132	}
2133
2134	} elseif ($this->extension == 'pgsql') {
2135	$result->setResult(pg_query($this->connection, $result->getSQL()));
2136
2137	} elseif ($this->extension == 'sqlite') {
2138	$result->setResult(sqlite_query($this->connection, $result->getSQL(), SQLITE_ASSOC, $extra));
2139
2140	} elseif ($this->extension == 'sqlsrv') {
2141	$extra = sqlsrv_query($this->connection, $result->getSQL());
2142	if (is_resource($extra)) {
2143	$rows = array();
2144	while ($row = sqlsrv_fetch_array($extra, SQLSRV_FETCH_ASSOC)) {
2145	$rows[] = $row;
2146	}
2147	$result->setResult($rows);
2148	unset($rows);
2149	} else {
2150	$result->setResult($extra);
2151	}
2152
2153	} elseif ($this->extension == 'pdo') {
2154	if (preg_match('#^\s*CREATE(\s+OR\s+REPLACE)?\s+TRIGGER#i', $result->getSQL())) {
2155	$this->connection->exec($result->getSQL());
2156	$extra = FALSE;
2157	$returned_rows = array();
2158	} else {
2159	$extra = $this->connection->query($result->getSQL());
2160	if (is_object($extra)) {
2161	// This fixes a segfault issue with blobs and fetchAll() for pdo_ibm
2162	if ($this->type == 'db2') {
2163	$returned_rows = array();
2164	$scanned_for_blobs = FALSE;
2165	$blob_columns = array();
2166	while (($row = $extra->fetch(PDO::FETCH_ASSOC)) !== FALSE) {
2167	if (!$scanned_for_blobs) {
2168	foreach ($row as $key => $value) {
2169	if (is_resource($value)) {
2170	$blob_columns[] = $key;
2171	}
2172	}
2173	}
2174	foreach ($blob_columns as $blob_column) {
2175	$row[$blob_column] = stream_get_contents($row[$blob_column]);
2176	}
2177	$returned_rows[] = $row;
2178	}
2179	} else {
2180	$returned_rows = $extra->fetchAll(PDO::FETCH_ASSOC);
2181	}
2182	} else {
2183	$returned_rows = $extra;
2184	}
2185
2186	// The pdo_pgsql driver likes to return empty rows equal to the number of affected rows for insert and deletes
2187	if ($this->type == 'postgresql' && $returned_rows && $returned_rows[0] == array()) {
2188	$returned_rows = array();
2189	}
2190	}
2191
2192	$result->setResult($returned_rows);
2193	}
2194	$this->statement = $statement;
2195
2196	$this->handleErrors(fCore::stopErrorCapture());
2197
2198	$this->checkForError($result, $extra);
2199
2200	if ($this->extension == 'ibm_db2') {
2201	$this->setAffectedRows($result, $extra);
2202	if ($extra && !is_object($statement)) {
2203	db2_free_result($extra);
2204	}
2205
2206	} elseif ($this->extension == 'pdo') {
2207	$this->setAffectedRows($result, $extra);
2208	if ($extra && !is_object($statement)) {
2209	$extra->closeCursor();
2210	}
2211
2212	} elseif ($this->extension == 'oci8') {
2213	$this->setAffectedRows($result, $extra);
2214	if ($extra && !is_object($statement)) {
2215	oci_free_statement($extra);
2216	}
2217
2218	} elseif ($this->extension == 'sqlsrv') {
2219	$this->setAffectedRows($result, $extra);
2220	if ($extra && !is_object($statement)) {
2221	sqlsrv_free_stmt($extra);
2222	}
2223
2224	} else {
2225	$this->setAffectedRows($result, $extra);
2226	}
2227
2228	$this->setReturnedRows($result);
2229
2230	$this->handleAutoIncrementedValue($result, $extra);
2231	}
2232
2233
2234	/**
2235	* Executes an unbuffered SQL query
2236	*
2237	* @param string\|fStatement $statement The statement to perform
2238	* @param fUnbufferedResult $result The result object for the query
2239	* @param array $params The parameters for prepared statements
2240	* @return void
2241	*/
2242	private function performUnbufferedQuery($statement, $result, $params)
2243	{
2244	fCore::startErrorCapture();
2245
2246	$extra = NULL;
2247	if (is_object($statement)) {
2248	$statement->executeUnbufferedQuery($result, $params, $extra, $statement != $this->statement);
2249	} elseif ($this->extension == 'ibm_db2') {
2250	$result->setResult(db2_exec($this->connection, $statement, array('cursor' => DB2_FORWARD_ONLY)));
2251	} elseif ($this->extension == 'mssql') {
2252	$result->setResult(mssql_query($result->getSQL(), $this->connection, 20));
2253	} elseif ($this->extension == 'mysql') {
2254	$result->setResult(mysql_unbuffered_query($result->getSQL(), $this->connection));
2255	} elseif ($this->extension == 'mysqli') {
2256	$result->setResult(mysqli_query($this->connection, $result->getSQL(), MYSQLI_USE_RESULT));
2257	} elseif ($this->extension == 'oci8') {
2258	$extra = oci_parse($this->connection, $result->getSQL());
2259	if (oci_execute($extra, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) {
2260	$result->setResult($extra);
2261	} else {
2262	$result->setResult(FALSE);
2263	}
2264	} elseif ($this->extension == 'pgsql') {
2265	$result->setResult(pg_query($this->connection, $result->getSQL()));
2266	} elseif ($this->extension == 'sqlite') {
2267	$result->setResult(sqlite_unbuffered_query($this->connection, $result->getSQL(), SQLITE_ASSOC, $extra));
2268	} elseif ($this->extension == 'sqlsrv') {
2269	$result->setResult(sqlsrv_query($this->connection, $result->getSQL()));
2270	} elseif ($this->extension == 'pdo') {
2271	$result->setResult($this->connection->query($result->getSQL()));
2272	}
2273	$this->statement = $statement;
2274
2275	$this->handleErrors(fCore::stopErrorCapture());
2276
2277	$this->checkForError($result, $extra);
2278	}
2279
2280
2281	/**
2282	* Prepares a single fStatement object to execute prepared statements
2283	*
2284	* Identifier placeholders (%r) are not supported with prepared statements.
2285	* In addition, multiple values can not be escaped by a placeholder - only
2286	* a single value can be provided.
2287	*
2288	* @param string $sql The SQL to prepare
2289	* @return fStatement A prepared statement object that can be passed to ::query(), ::unbufferedQuery() or ::execute()
2290	*/
2291	public function prepare($sql)
2292	{
2293	return $this->prepareStatement($sql);
2294	}
2295
2296
2297	/**
2298	* Prepares a single fStatement object to execute prepared statements
2299	*
2300	* Identifier placeholders (%r) are not supported with prepared statements.
2301	* In addition, multiple values can not be escaped by a placeholder - only
2302	* a single value can be provided.
2303	*
2304	* @param string $sql The SQL to prepare
2305	* @param boolean $translate If the SQL should be translated using fSQLTranslation
2306	* @return fStatement A prepare statement object that can be passed to ::query(), ::unbufferedQuery() or ::execute()
2307	*/
2308	private function prepareStatement($sql, $translate=FALSE)
2309	{
2310	// Ensure an SQL statement was passed
2311	if (empty($sql)) {
2312	throw new fProgrammerException('No SQL statement passed');
2313	}
2314
2315	// This is just to keep the callback method signature consistent
2316	$values = array();
2317
2318	if ($this->hook_callbacks['unmodified']) {
2319	foreach ($this->hook_callbacks['unmodified'] as $callback) {
2320	$params = array(
2321	$this,
2322	&$sql,
2323	&$values
2324	);
2325	call_user_func_array($callback, $params);
2326	}
2327	}
2328
2329	// Fix \' in MySQL and PostgreSQL
2330	if(($this->type == 'mysql' \|\| $this->type == 'postgresql') && strpos($sql, '\\') !== FALSE) {
2331	$sql = preg_replace("#(?<!\\\\)((\\\\{2})*)\\\\'#", "\\1''", $sql);
2332	}
2333
2334	// Separate the SQL from quoted values
2335	$parts = $this->splitSQL($sql);
2336
2337	$query = '';
2338	$strings = array();
2339
2340	foreach ($parts as $part) {
2341	// We split out all strings except for empty ones because Oracle
2342	// has to translate empty strings to NULL
2343	if ($part[0] == "'" && $part != "''") {
2344	$query .= ':string_' . sizeof($strings);
2345	$strings[] = $part;
2346	} else {
2347	$query .= $part;
2348	}
2349	}
2350
2351	if ($this->hook_callbacks['extracted']) {
2352	foreach ($this->hook_callbacks['extracted'] as $callback) {
2353	$params = array(
2354	$this,
2355	&$query,
2356	&$values,
2357	&$strings
2358	);
2359	call_user_func_array($callback, $params);
2360	}
2361	}
2362
2363	$pieces = preg_split('#(%[lbdfistp])\b#', $query, -1, PREG_SPLIT_DELIM_CAPTURE\|PREG_SPLIT_NO_EMPTY);
2364	$placeholders = array();
2365
2366	$new_query = '';
2367	foreach ($pieces as $piece) {
2368	if (strlen($piece) == 2 && $piece[0] == '%') {
2369	$placeholders[] = $piece;
2370	$new_query .= '%s';
2371	} else {
2372	$new_query .= $piece;
2373	}
2374	}
2375	$query = $new_query;
2376
2377	$untranslated_sql = NULL;
2378	if ($translate) {
2379	list($query) = $t->gethisSQLTranslation()->translate(array($query));
2380	$untranslated_sql = $sql;
2381	}
2382
2383	// Unescape literal semicolons in the queries
2384	$query = preg_replace('#(?<!\\\\)\\\\;#', ';', $query);
2385
2386	// Put the strings back into the SQL
2387	foreach ($strings as $index => $string) {
2388	$string = strtr($string, array('\\' => '\\\\', '$' => '\\$'));
2389	$query = preg_replace('#:string_' . $index . '\b#', $string, $query, 1);
2390	}
2391
2392	return new fStatement($this, $query, $placeholders, $untranslated_sql);
2393	}
2394
2395
2396	/**
2397	* Prepares the SQL by escaping values, spliting queries, cleaning escaped semicolons, fixing backslashed single quotes and translating
2398	*
2399	* @param string $sql The SQL to prepare
2400	* @param array $values Literal values to escape into the SQL
2401	* @param boolean $translate If the SQL should be translated
2402	* @return array The split out SQL queries, queries that have been translated will have a string key of a number, `:` and the original SQL, non-translated SQL will have a numeric key
2403	*/
2404	private function prepareSQL($sql, $values, $translate)
2405	{
2406	$this->connectToDatabase();
2407
2408	// Ensure an SQL statement was passed
2409	if (empty($sql)) {
2410	throw new fProgrammerException('No SQL statement passed');
2411	}
2412
2413	if ($this->hook_callbacks['unmodified']) {
2414	foreach ($this->hook_callbacks['unmodified'] as $callback) {
2415	$params = array(
2416	$this,
2417	&$sql,
2418	&$values
2419	);
2420	call_user_func_array($callback, $params);
2421	}
2422	}
2423
2424	// Fix \' in MySQL and PostgreSQL
2425	if(($this->type == 'mysql' \|\| $this->type == 'postgresql') && strpos($sql, '\\') !== FALSE) {
2426	$sql = preg_replace("#(?<!\\\\)((\\\\{2})*)\\\\'#", "\\1''", $sql);
2427	}
2428
2429	$strings = array(array());
2430	$queries = array('');
2431	$number = 0;
2432
2433	// Separate the SQL from quoted values
2434	$parts = $this->splitSQL($sql);
2435
2436	foreach ($parts as $part) {
2437	// We split out all strings except for empty ones because Oracle
2438	// has to translate empty strings to NULL
2439	if ($part[0] == "'" && $part != "''") {
2440	$queries[$number] .= ':string_' . sizeof($strings[$number]);
2441	$strings[$number][] = $part;
2442	} else {
2443	$split_queries = preg_split('#(?<!\\\\);#', $part);
2444
2445	$queries[$number] .= $split_queries[0];
2446
2447	for ($i=1; $i < sizeof($split_queries); $i++) {
2448	$queries[$number] = trim($queries[$number]);
2449	$number++;
2450	$strings[$number] = array();
2451	$queries[$number] = $split_queries[$i];
2452	}
2453	}
2454	}
2455	if (!trim($queries[$number])) {
2456	unset($queries[$number]);
2457	unset($strings[$number]);
2458	} else {
2459	$queries[$number] = trim($queries[$number]);
2460	}
2461
2462	// If the values were passed as a single array, this handles that
2463	$placeholders = preg_match_all('#%[lbdfristp]\b#', join(';', $queries), $trash);
2464	if (count($values) == 1 && is_array($values[0]) && count($values[0]) == $placeholders) {
2465	$values = array_shift($values);
2466	}
2467
2468	// Loop through the queries, chunk the values and add blank strings back in
2469	$chunked_values = array();
2470	$value_number = 0;
2471	foreach (array_keys($queries) as $number) {
2472	$pieces = preg_split('#(%[lbdfristp])\b#', $queries[$number], -1, PREG_SPLIT_DELIM_CAPTURE\|PREG_SPLIT_NO_EMPTY);
2473	$placeholders = 0;
2474
2475	$new_sql = '';
2476	$chunked_values[$number] = array();
2477
2478	foreach ($pieces as $piece) {
2479
2480	// A placeholder
2481	if (strlen($piece) == 2 && $piece[0] == '%') {
2482
2483	$value = $values[$value_number];
2484
2485	// Here we put blank strings back into the SQL so they can be translated for Oracle
2486	if ($piece == '%s' && $value !== NULL && ((string) $value) == '') {
2487	$new_sql .= "''";
2488	$value_number++;
2489
2490	} elseif ($piece == '%r') {
2491	if (is_array($value)) {
2492	$new_sql .= join(', ', array_map($this->escapeIdentifier, $value));
2493	} else {
2494	$new_sql .= $this->escapeIdentifier($value);
2495	}
2496	$value_number++;
2497
2498	// Other placeholder/value combos just get added
2499	} else {
2500	$placeholders++;
2501	$value_number++;
2502	$new_sql .= $piece;
2503	$chunked_values[$number][] = $value;
2504	}
2505
2506	// A piece of SQL
2507	} else {
2508	$new_sql .= $piece;
2509	}
2510	}
2511
2512	$queries[$number] = $new_sql;
2513	}
2514
2515	if ($this->hook_callbacks['extracted']) {
2516	foreach (array_keys($queries) as $number) {
2517	foreach ($this->hook_callbacks['extracted'] as $callback) {
2518	if (!isset($chunked_values[$number])) {
2519	$chunked_values[$number] = array();
2520	}
2521	$params = array(
2522	$this,
2523	&$queries[$number],
2524	&$chunked_values[$number],
2525	&$strings[$number]
2526	);
2527	call_user_func_array($callback, $params);
2528	}
2529	}
2530	}
2531
2532	// Translate the SQL queries
2533	if ($translate) {
2534	$queries = $this->getSQLTranslation()->translate($queries);
2535	}
2536
2537	$output = array();
2538	foreach (array_keys($queries) as $key) {
2539	$query = $queries[$key];
2540	$parts = explode(':', $key, 2);
2541	$number = $parts[0];
2542
2543	// Escape the values into the SQL
2544	if (!empty($chunked_values[$number])) {
2545	$query = $this->escapeSQL($query, $chunked_values[$number]);
2546	}
2547
2548	// Unescape literal semicolons in the queries
2549	$query = preg_replace('#(?<!\\\\)\\\\;#', ';', $query);
2550
2551	// Put the strings back into the SQL
2552	if (isset($strings[$number])) {
2553	foreach ($strings[$number] as $index => $string) {
2554	$string = strtr($string, array('\\' => '\\\\', '$' => '\\$'));
2555	$query = preg_replace('#:string_' . $index . '\b#', $string, $query, 1);
2556	}
2557	}
2558
2559	$output[$key] = $query;
2560	}
2561
2562	return $output;
2563	}
2564
2565
2566	/**
2567	* Executes one or more SQL queries and returns the result(s)
2568	*
2569	* @param string\|fStatement $statement One or more SQL statements in a string or a single fStatement prepared statement
2570	* @param mixed $value The optional value(s) to place into any placeholders in the SQL - see ::escape() for details
2571	* @param mixed ...
2572	* @return fResult\|array The fResult object(s) for the query
2573	*/
2574	public function query($statement)
2575	{
2576	$args = func_get_args();
2577	$params = array_slice($args, 1);
2578
2579	if (is_object($statement)) {
2580	return $this->run($statement, 'fResult', $params);
2581	}
2582
2583	$queries = $this->prepareSQL($statement, $params, FALSE);
2584
2585	$output = array();
2586	foreach ($queries as $query) {
2587	$output[] = $this->run($query, 'fResult');
2588	}
2589
2590	return sizeof($output) == 1 ? $output[0] : $output;
2591	}
2592
2593
2594	/**
2595	* Registers a callback for one of the various query hooks - multiple callbacks can be registered for each hook
2596	*
2597	* The following hooks are available:
2598	* - `'unmodified'`: The original SQL passed to fDatabase, for prepared statements this is called just once before the fStatement object is created
2599	* - `'extracted'`: The SQL after all non-empty strings have been extracted and replaced with `:string_{number}` placeholders
2600	* - `'run'`: After the SQL has been run
2601	*
2602	* Methods for the `'unmodified'` hook should have the following signature:
2603	*
2604	* - `$database`: The fDatabase instance
2605	* - `&$sql`: The original, unedited SQL
2606	* - `&$values`: The values to be escaped into the placeholders in the SQL - this will be empty for prepared statements
2607	*
2608	* Methods for the `'extracted'` hook should have the following signature:
2609	*
2610	* - `$database`: The fDatabase instance
2611	* - `&$sql`: The original, unedited SQL
2612	* - `&$values`: The values to be escaped into the placeholders in the SQL - this will be empty for prepared statements
2613	* - `&$strings` The literal strings that were extracted from the SQL
2614	*
2615	* The `extracted` hook is the best place to modify the SQL since there is
2616	* no risk of breaking string literals. Please note that there may be empty
2617	* strings (`''`) present in the SQL since some database treat those as
2618	* `NULL`.
2619	*
2620	* Methods for the `'run'` hook should have the following signature:
2621	*
2622	* - `$database`: The fDatabase instance
2623	* - `$query`: The (string) SQL or `array(0 => {fStatement object}, 1 => {values array})`
2624	* - `$query_time`: The (float) number of seconds the query took
2625	* - `$result` The fResult or fUnbufferedResult object, or `FALSE` if no result
2626	*
2627	* @param string $hook The hook to register for
2628	* @param callback $callback The callback to register - see the method description for details about the method signature
2629	* @return void
2630	*/
2631	public function registerHookCallback($hook, $callback)
2632	{
2633	$valid_hooks = array(
2634	'unmodified',
2635	'extracted',
2636	'run'
2637	);
2638
2639	if (!in_array($hook, $valid_hooks)) {
2640	throw new fProgrammerException(
2641	'The hook specified, %1$s, should be one of: %2$s.',
2642	$hook,
2643	join(', ', $valid_hooks)
2644	);
2645	}
2646
2647	$this->hook_callbacks[$hook][] = $callback;
2648	}
2649
2650
2651	/**
2652	* Runs a single statement and times it, removes any old unbuffered queries before starting
2653	*
2654	* @param string\|fStatement $statement The SQL statement or prepared statement to execute
2655	* @param string $result_type The type of result object to return, fResult or fUnbufferedResult
2656	* @return fResult\|fUnbufferedResult The result for the query
2657	*/
2658	private function run($statement, $result_type=NULL, $params=array())
2659	{
2660	if ($this->unbuffered_result) {
2661	$this->unbuffered_result->__destruct();
2662	$this->unbuffered_result = NULL;
2663	}
2664
2665	$start_time = microtime(TRUE);
2666
2667	if (is_object($statement)) {
2668	$sql = $statement->getSQL();
2669	} else {
2670	$sql = $statement;
2671	}
2672
2673	if (!$result = $this->handleTransactionQueries($sql, $result_type)) {
2674	if ($result_type) {
2675	$result = new $result_type($this, $this->type == 'mssql' ? $this->schema_info['character_set'] : NULL);
2676	$result->setSQL($sql);
2677
2678	if ($result_type == 'fResult') {
2679	$this->performQuery($statement, $result, $params);
2680	} else {
2681	$this->performUnbufferedQuery($statement, $result, $params);
2682	}
2683	} else {
2684	$this->perform($statement, $params);
2685	}
2686	}
2687
2688	// Write some debugging info
2689	$query_time = microtime(TRUE) - $start_time;
2690	$this->query_time += $query_time;
2691	if (fCore::getDebug($this->debug)) {
2692	fCore::debug(
2693	self::compose(
2694	'Query time was %1$s seconds for:%2$s',
2695	$query_time,
2696	"\n" . $sql
2697	),
2698	$this->debug
2699	);
2700	}
2701
2702	if ($this->hook_callbacks['run']) {
2703	foreach ($this->hook_callbacks['run'] as $callback) {
2704	$callback_params = array(
2705	$this,
2706	is_object($statement) ? array($statement, $params) : $sql,
2707	$query_time,
2708	$result
2709	);
2710	call_user_func_array($callback, $callback_params);
2711	}
2712	}
2713
2714	if ($result_type) {
2715	return $result;
2716	}
2717	}
2718
2719
2720	/**
2721	* Turns an array possibly containing objects into an array of all strings
2722	*
2723	* @param array $values The array of values to scalarize
2724	* @return array The scalarized values
2725	*/
2726	private function scalarize($values)
2727	{
2728	$new_values = array();
2729	foreach ($values as $value) {
2730	if (is_object($value) && is_callable(array($value, '__toString'))) {
2731	$value = $value->__toString();
2732	} elseif (is_object($value)) {
2733	$value = (string) $value;
2734	} elseif (is_array($value)) {
2735	$value = $this->scalarize($value);
2736	}
2737	$new_values[] = $value;
2738	}
2739	return $new_values;
2740	}
2741
2742
2743	/**
2744	* Sets the number of rows affected by the query
2745	*
2746	* @param fResult $result The result object for the query
2747	* @param mixed $resource Only applicable for `ibm_db2`, `pdo`, `oci8` and `sqlsrv` extentions or `mysqli` prepared statements - this is either the `PDOStatement` object, `mysqli_stmt` object or the `oci8` or `sqlsrv` resource
2748	* @return void
2749	*/
2750	private function setAffectedRows($result, $resource=NULL)
2751	{
2752	if ($this->extension == 'ibm_db2') {
2753	$insert_update_delete = preg_match('#^\s*(INSERT\|UPDATE\|DELETE)\b#i', $result->getSQL());
2754	$result->setAffectedRows(!$insert_update_delete ? 0 : db2_num_rows($resource));
2755	} elseif ($this->extension == 'mssql') {
2756	$affected_rows_result = mssql_query('SELECT @@ROWCOUNT AS rows', $this->connection);
2757	$result->setAffectedRows((int) mssql_result($affected_rows_result, 0, 'rows'));
2758	} elseif ($this->extension == 'mysql') {
2759	$result->setAffectedRows(mysql_affected_rows($this->connection));
2760	} elseif ($this->extension == 'mysqli') {
2761	if (is_object($resource)) {
2762	$result->setAffectedRows($resource->affected_rows);
2763	} else {
2764	$result->setAffectedRows(mysqli_affected_rows($this->connection));
2765	}
2766	} elseif ($this->extension == 'oci8') {
2767	$result->setAffectedRows(oci_num_rows($resource));
2768	} elseif ($this->extension == 'pgsql') {
2769	$result->setAffectedRows(pg_affected_rows($result->getResult()));
2770	} elseif ($this->extension == 'sqlite') {
2771	$result->setAffectedRows(sqlite_changes($this->connection));
2772	} elseif ($this->extension == 'sqlsrv') {
2773	$result->setAffectedRows(sqlsrv_rows_affected($resource));
2774	} elseif ($this->extension == 'pdo') {
2775	// This fixes the fact that rowCount is not reset for non INSERT/UPDATE/DELETE statements
2776	try {
2777	if (!$resource \|\| !$resource->fetch()) {
2778	throw new PDOException();
2779	}
2780	$result->setAffectedRows(0);
2781	} catch (PDOException $e) {
2782	// The SQLite PDO driver seems to return 1 when no rows are returned from a SELECT statement
2783	if ($this->type == 'sqlite' && $this->extension == 'pdo' && preg_match('#^\s*SELECT#i', $result->getSQL())) {
2784	$result->setAffectedRows(0);
2785	} elseif (!$resource) {
2786	$result->setAffectedRows(0);
2787	} else {
2788	$result->setAffectedRows($resource->rowCount());
2789	}
2790	}
2791	}
2792	}
2793
2794
2795	/**
2796	* Sets the number of rows returned by the query
2797	*
2798	* @param fResult $result The result object for the query
2799	* @return void
2800	*/
2801	private function setReturnedRows($result)
2802	{
2803	if (is_resource($result->getResult()) \|\| is_object($result->getResult())) {
2804	if ($this->extension == 'mssql') {
2805	$result->setReturnedRows(mssql_num_rows($result->getResult()));
2806	} elseif ($this->extension == 'mysql') {
2807	$result->setReturnedRows(mysql_num_rows($result->getResult()));
2808	} elseif ($this->extension == 'mysqli') {
2809	$result->setReturnedRows(mysqli_num_rows($result->getResult()));
2810	} elseif ($this->extension == 'pgsql') {
2811	$result->setReturnedRows(pg_num_rows($result->getResult()));
2812	} elseif ($this->extension == 'sqlite') {
2813	$result->setReturnedRows(sqlite_num_rows($result->getResult()));
2814	}
2815	} elseif (is_array($result->getResult())) {
2816	$result->setReturnedRows(sizeof($result->getResult()));
2817	}
2818	}
2819
2820
2821	/**
2822	* Splits SQL into pieces of SQL and quoted strings
2823	*
2824	* @param string $sql The SQL to split
2825	* @return array The pieces
2826	*/
2827	private function splitSQL($sql)
2828	{
2829	$parts = array();
2830	$temp_sql = $sql;
2831	$start_pos = 0;
2832	$inside_string = FALSE;
2833	do {
2834	$pos = strpos($temp_sql, "'", $start_pos);
2835	if ($pos !== FALSE) {
2836	if (!$inside_string) {
2837	$parts[] = substr($temp_sql, 0, $pos);
2838	$temp_sql = substr($temp_sql, $pos);
2839	$start_pos = 1;
2840	$inside_string = TRUE;
2841
2842	} elseif ($pos == strlen($temp_sql)) {
2843	$parts[] = $temp_sql;
2844	$temp_sql = '';
2845	$pos = FALSE;
2846
2847	} elseif (strlen($temp_sql) > $pos+1 && $temp_sql[$pos+1] == "'") {
2848	$start_pos = $pos+2;
2849
2850	} else {
2851	$parts[] = substr($temp_sql, 0, $pos+1);
2852	$temp_sql = substr($temp_sql, $pos+1);
2853	$start_pos = 0;
2854	$inside_string = FALSE;
2855	}
2856	}
2857	} while ($pos !== FALSE);
2858	if ($temp_sql) {
2859	$parts[] = $temp_sql;
2860	}
2861
2862	return $parts;
2863	}
2864
2865
2866	/**
2867	* Translates one or more SQL statements using fSQLTranslation and executes them without returning any results
2868	*
2869	* @param string $sql One or more SQL statements
2870	* @param mixed $value The optional value(s) to place into any placeholders in the SQL - see ::escape() for details
2871	* @param mixed ...
2872	* @return void
2873	*/
2874	public function translatedExecute($sql)
2875	{
2876	$args = func_get_args();
2877	$queries = $this->prepareSQL(
2878	$sql,
2879	array_slice($args, 1),
2880	TRUE
2881	);
2882
2883	$output = array();
2884	foreach ($queries as $query) {
2885	$this->run($query);
2886	}
2887	}
2888
2889
2890	/**
2891	* Translates a SQL statement and creates an fStatement object from it
2892	*
2893	* Identifier placeholders (%r) are not supported with prepared statements.
2894	* In addition, multiple values can not be escaped by a placeholder - only
2895	* a single value can be provided.
2896	*
2897	* @param string $sql The SQL to prepare
2898	* @return fStatement A prepared statement object that can be passed to ::query(), ::unbufferedQuery() or ::execute()
2899	*/
2900	public function translatedPrepare($sql)
2901	{
2902	return $this->prepareStatement($sql, TRUE);
2903	}
2904
2905
2906	/**
2907	* Translates one or more SQL statements using fSQLTranslation and executes them
2908	*
2909	* @param string $sql One or more SQL statements
2910	* @param mixed $value The optional value(s) to place into any placeholders in the SQL - see ::escape() for details
2911	* @param mixed ...
2912	* @return fResult\|array The fResult object(s) for the query
2913	*/
2914	public function translatedQuery($sql)
2915	{
2916	$args = func_get_args();
2917	$queries = $this->prepareSQL(
2918	$sql,
2919	array_slice($args, 1),
2920	TRUE
2921	);
2922
2923	$output = array();
2924	foreach ($queries as $key => $query) {
2925	$result = $this->run($query, 'fResult');
2926	if (!is_numeric($key)) {
2927	list($number, $original_query) = explode(':', $key, 2);
2928	$result->setUntranslatedSQL($original_query);
2929	}
2930	$output[] = $result;
2931	}
2932
2933	return sizeof($output) == 1 ? $output[0] : $output;
2934	}
2935
2936
2937	/**
2938	* Executes a single SQL statement in unbuffered mode. This is optimal for
2939	* large results sets since it does not load the whole result set into
2940	* memory first. The gotcha is that only one unbuffered result can exist at
2941	* one time. If another unbuffered query is executed, the old result will
2942	* be deleted.
2943	*
2944	* @param string\|fStatement $statement A single SQL statement
2945	* @param mixed $value The optional value(s) to place into any placeholders in the SQL - see ::escape() for details
2946	* @param mixed ...
2947	* @return fUnbufferedResult The result object for the unbuffered query
2948	*/
2949	public function unbufferedQuery($statement)
2950	{
2951	$args = func_get_args();
2952	$params = array_slice($args, 1);
2953
2954	if (is_object($statement)) {
2955	$result = $this->run($statement, 'fUnbufferedResult', $params);
2956
2957	} else {
2958	$queries = $this->prepareSQL($statement, $params, FALSE);
2959
2960	if (sizeof($queries) > 1) {
2961	throw new fProgrammerException(
2962	'Only a single unbuffered query can be run at a time, however %d were passed',
2963	sizeof($queries)
2964	);
2965	}
2966
2967	$result = $this->run($queries[0], 'fUnbufferedResult');
2968	}
2969
2970	$this->unbuffered_result = $result;
2971
2972	return $result;
2973	}
2974
2975
2976	/**
2977	* Translates the SQL statement using fSQLTranslation and then executes it
2978	* in unbuffered mode. This is optimal for large results sets since it does
2979	* not load the whole result set into memory first. The gotcha is that only
2980	* one unbuffered result can exist at one time. If another unbuffered query
2981	* is executed, the old result will be deleted.
2982	*
2983	* @param string $sql A single SQL statement
2984	* @param mixed $value The optional value(s) to place into any placeholders in the SQL - see ::escape() for details
2985	* @param mixed ...
2986	* @return fUnbufferedResult The result object for the unbuffered query
2987	*/
2988	public function unbufferedTranslatedQuery($sql)
2989	{
2990	$args = func_get_args();
2991	$queries = $this->prepareSQL(
2992	$sql,
2993	array_slice($args, 1),
2994	TRUE
2995	);
2996
2997	if (sizeof($queries) > 1) {
2998	throw new fProgrammerException(
2999	'Only a single unbuffered query can be run at a time, however %d were passed',
3000	sizeof($queries)
3001	);
3002	}
3003
3004	$query_keys = array_keys($queries);
3005	$key = $query_keys[0];
3006	list($number, $original_query) = explode(':', $key, 2);
3007
3008	$result = $this->run($queries[$key], 'fUnbufferedResult');
3009	$result->setUntranslatedSQL($original_query);
3010
3011	$this->unbuffered_result = $result;
3012
3013	return $result;
3014	}
3015
3016
3017	/**
3018	* Unescapes a value coming out of a database based on its data type
3019	*
3020	* The valid data types are:
3021	*
3022	* - `'blob'` (or `'%l'`)
3023	* - `'boolean'` (or `'%b'`)
3024	* - `'date'` (or `'%d'`)
3025	* - `'float'` (or `'%f'`)
3026	* - `'integer'` (or `'%i'`)
3027	* - `'string'` (also `'%s'`, `'varchar'`, `'char'` or `'text'`)
3028	* - `'time'` (or `'%t'`)
3029	* - `'timestamp'` (or `'%p'`)
3030	*
3031	* @param string $data_type The data type being unescaped - see method description for valid values
3032	* @param mixed $value The value or array of values to unescape
3033	* @return mixed The unescaped value
3034	*/
3035	public function unescape($data_type, $value)
3036	{
3037	if ($value === NULL) {
3038	return $value;
3039	}
3040
3041	$callback = NULL;
3042
3043	switch ($data_type) {
3044	// Testing showed that strings tend to be most common,
3045	// and moving this to the top of the switch statement
3046	// improved performance on read-heavy pages
3047	case 'string':
3048	case 'varchar':
3049	case 'char':
3050	case 'text':
3051	case '%s':
3052	return $value;
3053
3054	case 'boolean':
3055	case '%b':
3056	$callback = $this->unescapeBoolean;
3057	break;
3058
3059	case 'date':
3060	case '%d':
3061	$callback = $this->unescapeDate;
3062	break;
3063
3064	case 'float':
3065	case '%f':
3066	return $value;
3067
3068	case 'integer':
3069	case '%i':
3070	return $value;
3071
3072	case 'time':
3073	case '%t':
3074	$callback = $this->unescapeTime;
3075	break;
3076
3077	case 'timestamp':
3078	case '%p':
3079	$callback = $this->unescapeTimestamp;
3080	break;
3081
3082	case 'blob':
3083	case '%l':
3084	$callback = $this->unescapeBlob;
3085	break;
3086	}
3087
3088	if ($callback) {
3089	if (is_array($value)) {
3090	return array_map($callback, $value);
3091	}
3092	return call_user_func($callback, $value);
3093	}
3094
3095	throw new fProgrammerException(
3096	'Unknown data type, %1$s, specified. Must be one of: %2$s.',
3097	$data_type,
3098	'blob, %l, boolean, %b, date, %d, float, %f, integer, %i, string, %s, time, %t, timestamp, %p'
3099	);
3100	}
3101
3102
3103	/**
3104	* Unescapes a blob coming out of the database
3105	*
3106	* @param string $value The value to unescape
3107	* @return binary The binary data
3108	*/
3109	private function unescapeBlob($value)
3110	{
3111	$this->connectToDatabase();
3112
3113	if ($this->extension == 'pgsql') {
3114	return pg_unescape_bytea($value);
3115	} elseif ($this->extension == 'pdo' && is_resource($value)) {
3116	return stream_get_contents($value);
3117	} elseif ($this->extension == 'sqlite') {
3118	return pack('H*', $value);
3119	} else {
3120	return $value;
3121	}
3122	}
3123
3124
3125	/**
3126	* Unescapes a boolean coming out of the database
3127	*
3128	* @param string $value The value to unescape
3129	* @return boolean The boolean
3130	*/
3131	private function unescapeBoolean($value)
3132	{
3133	return ($value === 'f' \|\| !$value) ? FALSE : TRUE;
3134	}
3135
3136
3137	/**
3138	* Unescapes a date coming out of the database
3139	*
3140	* @param string $value The value to unescape
3141	* @return string The date in YYYY-MM-DD format
3142	*/
3143	private function unescapeDate($value)
3144	{
3145	if ($this->extension == 'sqlsrv' && $value instanceof DateTime) {
3146	return $value->format('Y-m-d');
3147	} elseif ($this->type == 'mssql') {
3148	$value = preg_replace('#:\d{3}#', '', $value);
3149	}
3150	return date('Y-m-d', strtotime($value));
3151	}
3152
3153
3154	/**
3155	* Unescapes a time coming out of the database
3156	*
3157	* @param string $value The value to unescape
3158	* @return string The time in `HH:MM:SS` format
3159	*/
3160	private function unescapeTime($value)
3161	{
3162	if ($this->extension == 'sqlsrv' && $value instanceof DateTime) {
3163	return $value->format('H:i:s');
3164	} elseif ($this->type == 'mssql') {
3165	$value = preg_replace('#:\d{3}#', '', $value);
3166	}
3167	return date('H:i:s', strtotime($value));
3168	}
3169
3170
3171	/**
3172	* Unescapes a timestamp coming out of the database
3173	*
3174	* @param string $value The value to unescape
3175	* @return string The timestamp in `YYYY-MM-DD HH:MM:SS` format
3176	*/
3177	private function unescapeTimestamp($value)
3178	{
3179	if ($this->extension == 'sqlsrv' && $value instanceof DateTime) {
3180	return $value->format('Y-m-d H:i:s');
3181	} elseif ($this->type == 'mssql') {
3182	$value = preg_replace('#:\d{3}#', '', $value);
3183	}
3184	return date('Y-m-d H:i:s', strtotime($value));
3185	}
3186	}
3187
3188
3189
3190	/**
3191	* Copyright (c) 2007-2010 Will Bond <will@flourishlib.com>
3192	*
3193	* Permission is hereby granted, free of charge, to any person obtaining a copy
3194	* of this software and associated documentation files (the "Software"), to deal
3195	* in the Software without restriction, including without limitation the rights
3196	* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
3197	* copies of the Software, and to permit persons to whom the Software is
3198	* furnished to do so, subject to the following conditions:
3199	*
3200	* The above copyright notice and this permission notice shall be included in
3201	* all copies or substantial portions of the Software.
3202	*
3203	* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
3204	* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
3205	* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
3206	* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
3207	* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
3208	* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
3209	* THE SOFTWARE.
3210	*/

root/fDatabase.php

Class Resources

Download in other formats: