Navigation

root

Changeset 562

← Previous Changeset
Next Changeset →

Timestamp:

05/05/09 13:55:03 (1 year ago)

Author:

wbond

Message:

BackwardsCompatibilityBreak - removed fORMSchema::enableSmartCaching(), fORM::enableSchemaCaching() now provides equivalent functionality. fSchema::setCacheFile() changed to fSchema::enableCaching() and now requires an fCache object. fSchema::flushInfo() renamed to fSchema::clearCache(). Fixed tickets #139, #144, #173 and #185. Added Oracle support and the fCache class. Added a whole slew of new database tests.

Files:

fCache.php (added)
fDatabase.php (modified) (70 diffs)
fORM.php (modified) (3 diffs)
fORMSchema.php (modified) (5 diffs)
fResult.php (modified) (11 diffs)
fSQLTranslation.php (modified) (42 diffs)
fSchema.php (modified) (35 diffs)
fUnbufferedResult.php (modified) (22 diffs)

Legend:

: Unmodified
: Added
: Removed
: Modified
: Copied
: Moved

fDatabase.php

r514	r562	Hide Line Numbers
10	10	* - [http://microsoft.com/sql/ MSSQL]
11	11	* - [http://mysql.com MySQL]
	12	* - [http://oracle.com Oracle]
12	13	* - [http://postgresql.org PostgreSQL]
13	14	* - [http://sqlite.org SQLite]
…	…
26	27	* - [http://php.net/mysqli mysqli]
27	28	* - [http://php.net/pdo_mysql pdo_mysql]
	29	* - Oracle (via ODBC)
	30	* - [http://php.net/pdo_odbc pdo_odbc]
	31	* - [http://php.net/odbc odbc]
	32	* - Oracle
	33	* - [http://php.net/oci8 oci8]
	34	* - [http://php.net/pdo_oci pdo_oci]
28	35	* - PostgreSQL
29	36	* - [http://php.net/pgsql pgsql]
…	…
40	47	* @link http://flourishlib.com/fDatabase
41	48	*
42		* @version 1.0.0b7
	49	* @version 1.0.0b8
	50	* @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]
43	51	* @changes 1.0.0b7 Updated for new fCore API [wb, 2009-02-16]
44	52	* @changes 1.0.0b6 Fixed a bug with executing transaction queries when using the mysqli extension [wb, 2009-02-12]
…	…
75	83
76	84	/**
77		* ~~The character set that data is coming back as~~
78		*
79		* @var ~~string~~
80		*/
81		private $c~~haracter_set~~;
	85	* An fCache object to cache the schema info to
	86	*
	87	* @var fCache
	88	*/
	89	private $cache;
82	90
83	91	/**
…	…
110	118	* - `'mysql'`
111	119	* - `'mysqli'`
	120	* - `'oci8'`
112	121	* - `'odbc'`
113	122	* - `'pgsql'`
…	…
156	165
157	166	/**
	167	* A cache of database-specific code
	168	*
	169	* @var array
	170	*/
	171	private $schema_info;
	172
	173	/**
158	174	* The millisecond threshold for triggering a warning about SQL performance
159	175	*
…	…
170	186
171	187	/**
172		* The database type: `'mssql'`, `'mysql'`, `'postgresql'`, or `'sqlite'`
	188	* The database type: `'mssql'`, `'mysql'`, `'oracle'`, `'postgresql'`, or `'sqlite'`
173	189	*
174	190	* @var string
…	…
194	210	* Configures the connection to a database - connection is not made until the first query is executed
195	211	*
196		* @param string $type The type of the database: `'mssql'`, `'mysql'`, `'postgresql'`, `'sqlite'`
	212	* @param string $type The type of the database: `'mssql'`, `'mysql'`, `'oracle'`, `'postgresql'`, `'sqlite'`
197	213	* @param string $database Name of the database. If an ODBC connection `'dsn:'` concatenated with the DSN, if SQLite the path to the database file.
198		* @param string $username Database username~~, required for all databases except~~ SQLite
199		* @param string $password The password for the username specified
200		* @param string $host Database server host or ~~ip, defaults to localhost for all databases except SQLite~~
201		* @param integer $port The port to connect to, defaults to the standard port for the database type specified
	214	* @param string $username Database username - not used for SQLite
	215	* @param string $password The password for the username specified - not used for SQLite
	216	* @param string $host Database server host or IP, defaults to localhost - not used for SQLite or ODBC connections. MySQL socket connection can be made by entering `'sock:'` followed by the socket path. PostgreSQL socket connection can be made by passing just `'sock:'`.
	217	* @param integer $port The port to connect to, defaults to the standard port for the database type specified - not used for SQLite or ODBC connections
202	218	* @return fDatabase
203	219	*/
204	220	public function __construct($type, $database, $username=NULL, $password=NULL, $host=NULL, $port=NULL)
205	221	{
206		$valid_types = array('mssql', 'mysql', 'postgresql', 'sqlite');
	222	$valid_types = array('mssql', 'mysql', 'oracle', 'postgresql', 'sqlite');
207	223	if (!in_array($type, $valid_types)) {
208	224	throw new fProgrammerException(
…	…
217	233	}
218	234
219		if ($type != 'sqlite') {
220		if (empty($username)) {
221		throw new fProgrammerException('No username was specified');
222		}
223		if ($host === NULL) {
224		$host = 'localhost';
225		}
	235	if ($host === NULL) {
	236	$host = 'localhost';
226	237	}
227	238
…	…
233	244	$this->port = $port;
234	245
235		$this->~~character_set = NULL~~;
	246	$this->schema_info = array();
236	247
237	248	$this->determineExtension();
…	…
255	266	} elseif ($this->extension == 'mysqli') {
256	267	mysqli_close($this->connection);
	268	} elseif ($this->extension == 'oci8') {
	269	oci_close($this->connection);
257	270	} elseif ($this->extension == 'odbc') {
258	271	odbc_close($this->connection);
…	…
284	297	* Checks to see if an SQL error occured
285	298	*
286		* @param fResult\|fUnbufferedResult $result The result object for the query
287		* @param ~~string $sqlite_error_message If we are using the sqlite extension, this will contain an error message if one exists~~
	299	* @param fResult\|fUnbufferedResult $result The result object for the query
	300	* @param mixed $extra_info The sqlite extension will pass a string error message, the oci8 extension will pass the statement resource
288	301	* @return void
289	302	*/
290		private function checkForError($result, $~~sqlite_error_message~~=NULL)
	303	private function checkForError($result, $extra_info=NULL)
291	304	{
292	305	if ($result->getResult() === FALSE) {
…	…
298	311	} elseif ($this->extension == 'mysqli') {
299	312	$message = mysqli_error($this->connection);
	313	} elseif ($this->extension == 'oci8') {
	314	$error_info = oci_error($extra_info);
	315	$message = $error_info['message'];
300	316	} elseif ($this->extension == 'odbc') {
301	317	$message = odbc_errormsg($this->connection);
…	…
303	319	$message = pg_last_error($this->connection);
304	320	} elseif ($this->extension == 'sqlite') {
305		$message = $~~sqlite_error_message~~;
	321	$message = $extra_info;
306	322	} elseif ($this->extension == 'sqlsrv') {
307	323	$error_info = sqlsrv_errors(SQLSRV_ERR_ALL);
…	…
315	331	'mssql' => 'MSSQL',
316	332	'mysql' => 'MySQL',
	333	'oracle' => 'Oracle',
317	334	'postgresql' => 'PostgreSQL',
318	335	'sqlite' => 'SQLite'
…	…
330	347
331	348	/**
	349	* Clears all of the schema info out of the object and, if set, the fCache object
	350	*
	351	* @return void
	352	*/
	353	public function clearCache()
	354	{
	355	$this->schema_info = array();
	356	if ($this->cache) {
	357	$this->cache->delete($this->makeCachePrefix() . 'schema_info');
	358	}
	359	if ($this->type == 'mssql') {
	360	$this->determineCharacterSet();
	361	}
	362	}
	363
	364
	365	/**
332	366	* Connects to the database specified if no connection exists
333	367	*
…	…
341	375	// Establish a connection to the database
342	376	if ($this->extension == 'pdo') {
	377	$odbc = strtolower(substr($this->database, 0, 4)) == 'dsn:';
343	378	if ($this->type == 'mssql') {
344		~~$odbc = strtolower(substr($this->database, 0, 4)) == 'dsn:';~~
345		~~if ($odbc && in_array('odbc', PDO::getAvailableDrivers())) {~~
346		~~try~~ {
347		~~$this->connection = new PDO('odbc:' . substr($this->database, 4), $this->username, $this->password)~~;
348		~~} catch (PDOException $e) {~~
349		~~$this->connection = FALSE~~;
350		}
	379	if ($odbc) {
	380	$dsn = 'odbc:' . substr($this->database, 4);
	381	} else {
	382	$separator = (fCore::checkOS('windows')) ? ',' : ':';
	383	$port = ($this->port) ? $separator . $this->port : '';
	384	$driver = (fCore::checkOs('windows')) ? 'mssql' : 'dblib';
	385	$dsn = $driver . ':host=' . $this->host . $port . ';dbname=' . $this->database;
351	386	}
352		if (!$odbc && in_array('mssql', PDO::getAvailableDrivers())) {
353		try {
354		$separator = (fCore::checkOS('windows')) ? ',' : ':';
355		$port = ($this->port) ? $separator . $this->port : '';
356		$this->connection = new PDO('mssql:host=' . $this->host . $port . ';dbname=' . $this->database, $this->username, $this->password);
357		} catch (PDOException $e) {
358		$this->connection = FALSE;
359		}
	387
	388	} elseif ($this->type == 'mysql') {
	389	if (substr($this->host, 0, 5) == 'sock:') {
	390	$dsn = 'mysql:unix_socket=' . substr($this->host, 5) . ';dbname=' . $this->database;
	391	} else {
	392	$port = ($this->port) ? ';port=' . $this->port : '';
	393	$dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->database . $port;
360	394	}
361	395
362		} elseif ($this->type == '~~mysql~~') {
363		~~try~~ {
364		$~~this->connection = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->database . (($this->port) ? ';port=' . $this->port : ''), $this->username, $this->password~~);
365		~~$this->connection->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 1);~~
366		~~} catch (PDOException $e) {~~
367		$~~this->connection = FALSE~~;
	396	} elseif ($this->type == 'oracle') {
	397	if ($odbc) {
	398	$dsn = 'odbc:' . substr($this->database, 4);
	399	} else {
	400	$port = ($this->port) ? ':' . $this->port : '';
	401	$dsn = 'oci:dbname=' . $this->host . $port . '/' . $this->database . ';charset=AL32UTF8';
368	402	}
369	403
370	404	} elseif ($this->type == 'postgresql') {
371		~~try {~~
372		~~$this->connection = new PDO('pgsql:host=' . $this->host . ' dbname=' . $this->database, $this->username, $this->password)~~;
373		~~} catch (PDOException $e~~) {
374		$~~this->connection = FALSE;~~
	405
	406	$dsn = 'pgsql:dbname=' . $this->database;
	407	if ($this->host && $this->host != 'sock:') {
	408	$dsn .= ' host=' . $this->host;
375	409	}
	410	if ($this->port) {
	411	$dsn .= ' port=' . $this->port;
	412	}
376	413
377	414	} elseif ($this->type == 'sqlite') {
378		try {
379		$this->connection = new PDO('sqlite:' . $this->database);
380		} catch (PDOException $e) {
381		$this->connection = FALSE;
	415	$dsn = 'sqlite:' . $this->database;
	416	}
	417
	418	try {
	419	$this->connection = new PDO($dsn, $this->username, $this->password);
	420	if ($this->type == 'mysql') {
	421	$this->connection->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 1);
382	422	}
	423	} catch (PDOException $e) {
	424	$this->connection = FALSE;
383	425	}
384	426	}
…	…
397	439
398	440	if ($this->extension == 'mysql') {
399		$this->connection = mysql_connect(($this->port) ? $this->host . ':' . $this->port : $this->host, $this->username, $this->password);
	441	if (substr($this->host, 0, 5) == 'sock:') {
	442	$host = substr($this->host, 4);
	443	} elseif ($this->port) {
	444	$host = $this->host . ':' . $this->port;
	445	} else {
	446	$host = $this->host;
	447	}
	448	$this->connection = mysql_connect($host, $this->username, $this->password);
400	449	if ($this->connection !== FALSE && mysql_select_db($this->database, $this->connection) === FALSE) {
401	450	$this->connection = FALSE;
…	…
404	453
405	454	if ($this->extension == 'mysqli') {
406		if ($this->port) {
	455	if (substr($this->host, 0, 5) == 'sock:') {
	456	$this->connection = mysqli_connect('localhost', $this->username, $this->password, $this->database, $this->port, substr($this->host, 5));
	457	} elseif ($this->port) {
407	458	$this->connection = mysqli_connect($this->host, $this->username, $this->password, $this->database, $this->port);
408	459	} else {
…	…
411	462	}
412	463
	464	if ($this->extension == 'oci8') {
	465	$this->connection = oci_connect($this->username, $this->password, $this->host . ($this->port ? ':' . $this->port : '') . '/' . $this->database, 'AL32UTF8');
	466	}
	467
413	468	if ($this->extension == 'odbc') {
414	469	$this->connection = odbc_connect(substr($this->database, 4), $this->username, $this->password);
…	…
416	471
417	472	if ($this->extension == 'pgsql') {
418		$this->connection = pg_connect("host='" . addslashes($this->host) . "'
419		dbname='" . addslashes($this->database) . "'
420		user='" . addslashes($this->username) . "'
421		password='" . addslashes($this->password) . "'" .
422		(($this->port) ? " port='" . $this->port . "'" : ''));
	473	$connection_string = "dbname='" . addslashes($this->database) . "'";
	474	if ($this->host && $this->host != 'sock:') {
	475	$connection_string .= " host='" . addslashes($this->host) . "'";
	476	}
	477	if ($this->username) {
	478	$connection_string .= " user='" . addslashes($this->username) . "'";
	479	}
	480	if ($this->password) {
	481	$connection_string .= " password='" . addslashes($this->password) . "'";
	482	}
	483	if ($this->port) {
	484	$connection_string .= " port='" . $this->port . "'";
	485	}
	486	$this->connection = pg_connect($connection_string);
423	487	}
424	488
…	…
441	505	// Make MySQL act more strict and use UTF-8
442	506	if ($this->type == 'mysql') {
443		$this->query("SET SQL_MODE = '~~ANSI~~'");
	507	$this->query("SET SQL_MODE = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE'");
444	508	$this->query("SET NAMES 'utf8'");
445	509	$this->query("SET CHARACTER SET utf8");
…	…
453	517	// Fix some issues with mssql
454	518	if ($this->type == 'mssql') {
	519	if (!isset($this->schema_info['character_set'])) {
	520	$this->determineCharacterSet();
	521	}
455	522	$this->query('SET TEXTSIZE 65536');
456		$this->character_set = $this->query("SELECT 'WINDOWS-' + CONVERT(VARCHAR, COLLATIONPROPERTY(CONVERT(NVARCHAR, DATABASEPROPERTYEX(%s, 'Collation')), 'CodePage')) AS charset", $this->database)->fetchScalar();
457	523	}
458	524
…	…
461	527	$this->query("SET NAMES 'UTF8'");
462	528	}
	529
	530	// Oracle has different date and timestamp defaults
	531	if ($this->type == 'oracle') {
	532	$this->query("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
	533	$this->query("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
	534	$this->query("ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZR'");
	535	$this->query("ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS'");
	536	$this->query("ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH24:MI:SS TZR'");
	537	}
	538	}
	539
	540
	541	/**
	542	* Determines the character set of a SQL Server database
	543	*
	544	* @return void
	545	*/
	546	private function determineCharacterSet()
	547	{
	548	$this->schema_info['character_set'] = 'WINDOWS-1252';
	549	$this->schema_info['character_set'] = $this->query("SELECT 'WINDOWS-' + CONVERT(VARCHAR, COLLATIONPROPERTY(CONVERT(NVARCHAR, DATABASEPROPERTYEX(DB_NAME(), 'Collation')), 'CodePage')) AS charset")->fetchScalar();
	550	if ($this->cache) {
	551	$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
	552	}
463	553	}
464	554
…	…
478	568
479	569	if ($odbc) {
480		if (class_exists('PDO', FALSE) && in_array('odbc', PDO::getAvailableDrivers())) {
	570	if (extension_loaded('odbc')) {
	571	$this->extension = 'odbc';
	572
	573	} elseif (class_exists('PDO', FALSE) && in_array('odbc', PDO::getAvailableDrivers())) {
481	574	$this->extension = 'pdo';
482
483		~~} elseif (extension_loaded('odbc')) {~~
484		~~$this->extension = 'odbc';~~
485	575
486	576	} else {
487	577	$type = 'MSSQL (ODBC)';
488		$exts = '~~pdo_odbc,~~ odbc';
	578	$exts = 'odbc, pdo_odbc';
489	579	}
490	580
…	…
493	583	$this->extension = 'sqlsrv';
494	584
495		~~} elseif (class_exists('PDO', FALSE) && in_array('mssql', PDO::getAvailableDrivers())) {~~
496		~~$this->extension = 'pdo';~~
497
498	585	} elseif (extension_loaded('mssql')) {
499	586	$this->extension = 'mssql';
500	587
	588	} elseif (class_exists('PDO', FALSE) && (in_array('dblib', PDO::getAvailableDrivers()) \|\| in_array('mssql', PDO::getAvailableDrivers()))) {
	589	$this->extension = 'pdo';
	590
501	591	} else {
502	592	$type = 'MSSQL';
503		$exts = '~~sqlsrv, pdo_dblib, mssql~~';
	593	$exts = 'mssql, sqlsrv, pdo_dblib (linux), pdo_mssql (windows)';
504	594	}
505	595	}
…	…
509	599	case 'mysql':
510	600
511		if (class_exists('PDO', FALSE) && in_array('mysql', PDO::getAvailableDrivers())) {
	601	if (extension_loaded('mysql')) {
	602	$this->extension = 'mysql';
	603
	604	} elseif (class_exists('PDO', FALSE) && in_array('mysql', PDO::getAvailableDrivers())) {
512	605	$this->extension = 'pdo';
513	606
…	…
515	608	$this->extension = 'mysqli';
516	609
517		} elseif (extension_loaded('mysql')) {
518		$this->extension = 'mysql';
	610	} else {
	611	$type = 'MySQL';
	612	$exts = 'mysql, pdo_mysql, mysqli';
	613	}
	614	break;
	615
	616
	617	case 'oracle':
	618
	619	$odbc = strtolower(substr($this->database, 0, 4)) == 'dsn:';
	620
	621	if ($odbc) {
	622	if (extension_loaded('odbc')) {
	623	$this->extension = 'odbc';
	624
	625	} elseif (class_exists('PDO', FALSE) && in_array('odbc', PDO::getAvailableDrivers())) {
	626	$this->extension = 'pdo';
	627
	628	} else {
	629	$type = 'Oracle (ODBC)';
	630	$exts = 'odbc, pdo_odbc';
	631	}
519	632
520	633	} else {
521		$type = 'MySQL';
522		$exts = 'mysql, mysqli, pdo_mysql';
	634	if (extension_loaded('oci8')) {
	635	$this->extension = 'oci8';
	636
	637	} elseif (class_exists('PDO', FALSE) && in_array('oci', PDO::getAvailableDrivers())) {
	638	$this->extension = 'pdo';
	639
	640	} else {
	641	$type = 'Oracle';
	642	$exts = 'oci8, pdo_oci';
	643	}
523	644	}
524	645	break;
525
526
	646
	647
527	648	case 'postgresql':
528	649
529		if (class_exists('PDO', FALSE) && in_array('pgsql', PDO::getAvailableDrivers())) {
	650	if (extension_loaded('pgsql')) {
	651	$this->extension = 'pgsql';
	652
	653	} elseif (class_exists('PDO', FALSE) && in_array('pgsql', PDO::getAvailableDrivers())) {
530	654	$this->extension = 'pdo';
531
532		~~} elseif (extension_loaded('pgsql')) {~~
533		~~$this->extension = 'pgsql';~~
534	655
535	656	} else {
…	…
573	694	);
574	695
575		} elseif (~~$sqlite_version == 2~~ && extension_loaded('sqlite')) {
	696	} elseif ((!$sqlite_version \|\| $sqlite_version == 2) && extension_loaded('sqlite')) {
576	697	$this->extension = 'sqlite';
577	698
…	…
601	722
602	723	/**
	724	* Sets the schema info to be cached to the fCache object specified
	725	*
	726	* @param fCache $cache The cache to cache to
	727	* @return void
	728	*/
	729	public function enableCaching($cache)
	730	{
	731	$this->cache = $cache;
	732
	733	$this->schema_info = $this->cache->get($this->makeCachePrefix() . 'schema_info', array());
	734	}
	735
	736
	737	/**
603	738	* Sets if debug messages should be shown
604	739	*
…	…
609	744	{
610	745	$this->debug = (boolean) $flag;
611		~~if ($this->translation) {~~
612		~~$this->translation->enableDebugging($this->debug);~~
613		}
614	746	}
615	747
…	…
660	792	*
661	793	* @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
662		* @param mixed $value The value to escape - you should pass a single value ~~if a data type is specified~~ or a value for each placeholder
	794	* @param mixed $value The value to escape - you should pass a single value or an array of values if a data type is specified, or a value for each placeholder
663	795	* @param mixed ...
664		* @return ~~string The escaped value/SQL~~
	796	* @return mixed The escaped value/SQL or an array of the escaped values
665	797	*/
666	798	public function escape($sql_or_type, $value)
…	…
686	818	$values = $new_values;
687	819
	820	$value = array_shift($values);
	821
688	822	// Handle single value escaping
689		$~~value = array_shift($values)~~;
	823	$callback = NULL;
690	824
691	825	switch ($sql_or_type) {
692	826	case 'blob':
693	827	case '%l':
694		return $this->escapeBlob($value);
	828	$callback = $this->escapeBlob;
	829	break;
695	830	case 'boolean':
696	831	case '%b':
697		return $this->escapeBoolean($value);
	832	$callback = $this->escapeBoolean;
	833	break;
698	834	case 'date':
699	835	case '%d':
700		return $this->escapeDate($value);
	836	$callback = $this->escapeDate;
	837	break;
701	838	case 'float':
702	839	case '%f':
703		return $this->escapeFloat($value);
	840	$callback = $this->escapeFloat;
	841	break;
704	842	case 'integer':
705	843	case '%i':
706		return $this->escapeInteger($value);
	844	$callback = $this->escapeInteger;
	845	break;
707	846	case 'string':
708	847	case 'varchar':
…	…
710	849	case 'text':
711	850	case '%s':
712		return $this->escapeString($value);
	851	$callback = $this->escapeString;
	852	break;
713	853	case 'time':
714	854	case '%t':
715		return $this->escapeTime($value);
	855	$callback = $this->escapeTime;
	856	break;
716	857	case 'timestamp':
717	858	case '%p':
718		return $this->escapeTimestamp($value);
	859	$callback = $this->escapeTimestamp;
	860	break;
	861	}
	862
	863	if ($callback) {
	864	if (is_array($value)) {
	865	return array_map($callback, $value);
	866	}
	867	return call_user_func($callback, $value);
719	868	}
720	869
721		// Handle SQL escaping
722		preg_match_all("#(?:'(?:''\|\\\\'\|\\\\[^']\|[^'\\\\]+)*')\|(?:[^']+)#", $sql_or_type, $matches);
	870	// Fix \' in MySQL and PostgreSQL
	871	if(($this->type == 'mysql' \|\| $this->type == 'postgresql') && strpos($sql_or_type, '\\') !== FALSE) {
	872	$sql_or_type = preg_replace("#(?<!\\\\)((\\\\{2})*)\\\\'#", "\\1''", $sql_or_type);
	873	}
	874
	875	// Separate the SQL from quoted values
	876	preg_match_all("#(?:'([^'](?:''))*?')\|(?:[^']+)#", $sql_or_type, $matches);
723	877
724	878	$temp_sql = '';
…	…
818	972	$this->connectToDatabase();
819	973
820		if ($this->extension == 'mysql') {
821		return "'" . mysql_real_escape_string($value, $this->connection) . "'";
822		} elseif ($this->extension == 'mysqli') {
823		return "'" . mysqli_real_escape_string($this->connection, $value) . "'";
824		} elseif ($this->extension == 'pgsql') {
825		return "'" . pg_escape_bytea($this->connection, $value) . "'";
	974	if ($this->type == 'mysql') {
	975	return "x'" . bin2hex($value) . "'";
	976
	977	} elseif ($this->type == 'postgresql') {
	978	$output = '';
	979	for ($i=0; $i<strlen($value); $i++) {
	980	$output .= '\\\\' . str_pad(decoct(ord($value[$i])), 3, '0', STR_PAD_LEFT);
	981	}
	982	return "E'" . $output . "'";
	983
	984	} elseif ($this->extension == 'sqlite') {
	985	return "'" . bin2hex($value) . "'";
	986
826	987	} elseif ($this->type == 'sqlite') {
827	988	return "X'" . bin2hex($value) . "'";
	989
828	990	} elseif ($this->type == 'mssql') {
829	991	return '0x' . bin2hex($value);
830		} elseif ($this->extension == 'pdo') {
831		return $this->connection->quote($value, PDO::PARAM_LOB);
	992
	993	} elseif ($this->type == 'oracle') {
	994	return "'" . bin2hex($value) . "'";
832	995	}
833	996	}
…	…
852	1015	} elseif (in_array($this->type, array('mssql', 'sqlite'))) {
853	1016	return ($value) ? "'1'" : "'0'";
	1017	} elseif ($this->type == 'oracle') {
	1018	return ($value) ? '1' : '0';
854	1019	}
855	1020	}
…	…
946	1111	} elseif ($this->extension == 'sqlite') {
947	1112	return "'" . sqlite_escape_string($value) . "'";
948
	1113	} elseif ($this->type == 'oracle') {
	1114	return "'" . str_replace("'", "''", $value) . "'";
	1115
949	1116	} elseif ($this->type == 'mssql') {
950	1117
951	1118	// If there are any non-ASCII characters, we need to escape
952	1119	if (preg_match('#[^\x00-\x7F]#', $value)) {
953		$characters = preg_split('##u', $value);
954		$output = "'";
955		foreach ($characters as $character) {
	1120	preg_match_all('#.\|^\z#us', $value, $characters);
	1121	$output = "";
	1122	$last_type = NULL;
	1123	foreach ($characters[0] as $character) {
956	1124	if (strlen($character) > 1) {
957	1125	$b = array_map('ord', str_split($character));
…	…
974	1142	continue;
975	1143	}
976		$output .= "'+NCHAR(" . bindec($bin) . ")+'";
	1144	if ($last_type == 'nchar') {
	1145	$output .= '+';
	1146	} elseif ($last_type == 'char') {
	1147	$output .= "'+";
	1148	}
	1149	$output .= "NCHAR(" . bindec($bin) . ")";
	1150	$last_type = 'nchar';
977	1151	} else {
	1152	if (!$last_type) {
	1153	$output .= "'";
	1154	} elseif ($last_type == 'nchar') {
	1155	$output .= "+'";
	1156	}
978	1157	$output .= $character;
979	1158	// Escape single quotes
980		if ($character = "'") {
	1159	if ($character == "'") {
981	1160	$output .= "'";
982	1161	}
	1162	$last_type = 'char';
983	1163	}
984	1164	}
985		$output .= "'";
	1165	if ($last_type == 'char') {
	1166	$output .= "'";
	1167	} elseif (!$last_type) {
	1168	$output .= "''";
	1169	}
986	1170
987	1171	// ASCII text is normal
…	…
1015	1199	return 'NULL';
1016	1200	}
	1201	if ($this->type == 'mssql' \|\| $this->type == 'oracle') {
	1202	return "'" . date('1970-01-01 H:i:s', strtotime($value)) . "'";
	1203	}
1017	1204	return "'" . date('H:i:s', strtotime($value)) . "'";
1018	1205	}
…	…
1047	1234	private function executeQuery($result)
1048	1235	{
	1236	// We don't want errors and an exception
1049	1237	$old_level = error_reporting(error_reporting() & ~E_WARNING);
1050	1238
1051	1239	if ($this->extension == 'mssql') {
1052	1240	$result->setResult(mssql_query($result->getSQL(), $this->connection));
	1241
1053	1242	} elseif ($this->extension == 'mysql') {
1054	1243	$result->setResult(mysql_query($result->getSQL(), $this->connection));
	1244
1055	1245	} elseif ($this->extension == 'mysqli') {
1056	1246	$result->setResult(mysqli_query($this->connection, $result->getSQL()));
	1247
	1248	} elseif ($this->extension == 'oci8') {
	1249	$oci_statement = oci_parse($this->connection, $result->getSQL());
	1250	if (oci_execute($oci_statement, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) {
	1251	oci_fetch_all($oci_statement, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	1252	$result->setResult($rows);
	1253	unset($rows);
	1254	} else {
	1255	$result->setResult(FALSE);
	1256	}
	1257
1057	1258	} elseif ($this->extension == 'odbc') {
1058		~~$rows = array();~~
1059	1259	$resource = odbc_exec($this->connection, $result->getSQL());
1060	1260	if (is_resource($resource)) {
	1261	$rows = array();
1061	1262	// Allow up to 1MB of binary data
1062	1263	odbc_longreadlen($resource, 1048576);
…	…
1066	1267	}
1067	1268	$result->setResult($rows);
	1269	unset($rows);
1068	1270	} else {
1069	1271	$result->setResult($resource);
1070	1272	}
	1273
1071	1274	} elseif ($this->extension == 'pgsql') {
1072	1275	$result->setResult(pg_query($this->connection, $result->getSQL()));
	1276
1073	1277	} elseif ($this->extension == 'sqlite') {
1074	1278	$result->setResult(sqlite_query($this->connection, $result->getSQL(), SQLITE_ASSOC, $sqlite_error_message));
	1279
1075	1280	} elseif ($this->extension == 'sqlsrv') {
1076		~~$rows = array();~~
1077	1281	$resource = sqlsrv_query($this->connection, $result->getSQL());
1078	1282	if (is_resource($resource)) {
	1283	$rows = array();
1079	1284	while ($row = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC)) {
1080	1285	$rows[] = $row;
1081	1286	}
1082	1287	$result->setResult($rows);
	1288	unset($rows);
1083	1289	} else {
1084	1290	$result->setResult($resource);
1085	1291	}
	1292
1086	1293	} elseif ($this->extension == 'pdo') {
1087		$pdo_statement = $this->connection->query($result->getSQL());
1088		$result->setResult((is_object($pdo_statement)) ? $pdo_statement->fetchAll(PDO::FETCH_ASSOC) : $pdo_statement);
	1294	if (preg_match('#^\s*CREATE(\s+OR\s+REPLACE)?\s+TRIGGER#i', $result->getSQL())) {
	1295	$this->connection->exec($result->getSQL());
	1296	$pdo_statement = FALSE;
	1297	$returned_rows = array();
	1298	} else {
	1299	$pdo_statement = $this->connection->query($result->getSQL());
	1300	$returned_rows = (is_object($pdo_statement)) ? $pdo_statement->fetchAll(PDO::FETCH_ASSOC) : $pdo_statement;
	1301
	1302	// The pdo_pgsql driver likes to return empty rows equal to the number of affected rows for insert and deletes
	1303	if ($this->type == 'postgresql' && $returned_rows && $returned_rows[0] == array()) {
	1304	$returned_rows = array();
	1305	}
	1306	}
	1307
	1308	$result->setResult($returned_rows);
1089	1309	}
1090	1310
1091	1311	error_reporting($old_level);
	1312
1092	1313
1093	1314	if ($this->extension == 'sqlite') {
1094	1315	$this->checkForError($result, $sqlite_error_message);
	1316	} elseif ($this->extension == 'oci8') {
	1317	$this->checkForError($result, $oci_statement);
1095	1318	} else {
1096	1319	$this->checkForError($result);
1097	1320	}
1098	1321
	1322
1099	1323	if ($this->extension == 'pdo') {
1100	1324	$this->setAffectedRows($result, $pdo_statement);
1101		$pdo_statement->closeCursor();
	1325	if ($pdo_statement) {
	1326	$pdo_statement->closeCursor();
	1327	}
1102	1328	unset($pdo_statement);
	1329	} elseif ($this->extension == 'oci8') {
	1330	$this->setAffectedRows($result, $oci_statement);
	1331	oci_free_statement($oci_statement);
1103	1332	} elseif ($this->extension == 'odbc') {
1104	1333	$this->setAffectedRows($result, $resource);
…	…
1131	1360	} elseif ($this->extension == 'mysql') {
1132	1361	$result->setResult(mysql_unbuffered_query($result->getSQL(), $this->connection));
1133		} elseif ($this->extension == 'mysqli') {
	1362	} elseif ($this->extension == 'mysqli') {
1134	1363	$result->setResult(mysqli_query($this->connection, $result->getSQL(), MYSQLI_USE_RESULT));
	1364	} elseif ($this->extension == 'oci8') {
	1365	$oci_statement = oci_parse($this->connection, $result->getSQL());
	1366	$result->setResult(oci_execute($oci_statement, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS) ? $oci_statement : FALSE);
1135	1367	} elseif ($this->extension == 'odbc') {
1136	1368	$result->setResult(odbc_exec($this->connection, $result->getSQL()));
…	…
1149	1381	if ($this->extension == 'sqlite') {
1150	1382	$this->checkForError($result, $sqlite_error_message);
	1383	} elseif ($this->extension == 'oci8') {
	1384	$this->checkForError($result, $oci_statement);
1151	1385	} else {
1152	1386	$this->checkForError($result);
…	…
1166	1400
1167	1401	// Separate the SQL from quoted values
1168		preg_match_all("#(?:'(?:''\|\\\\'\|\\\\[^']\|[^'\\\\])')\|(?:[^']+)#", $sql, $matches);
	1402	preg_match_all("#(?:'([^'](?:''))*?')\|(?:[^']+)#", $sql, $matches);
1169	1403
1170	1404	$cur_sql = '';
…	…
1175	1409	$cur_sql .= $match;
1176	1410
1177		// Handle the SQL
	1411	// Handle the SQL, exploding on any ; that isn't escaped with a \
1178	1412	} else {
1179		$sql_strings = ~~explode(';~~', $match);
	1413	$sql_strings = preg_split('#(?<!\\\\);#', $match);
1180	1414	$cur_sql .= $sql_strings[0];
1181	1415	for ($i=1; $i < sizeof($sql_strings); $i++) {
1182		~~// SQLite triggers have a ; before and after the "end"~~
1183		~~if (strtolower(trim($sql_strings[$i])) == 'end') {~~
1184		~~$cur_sql .= "; END";~~
1185		~~$i++;~~
1186		~~if ($i >= sizeof($sql_strings)) {~~
1187		~~break;~~
1188		}
1189		}
1190	1416	$cur_sql = trim($cur_sql);
1191	1417	if ($cur_sql) {
…	…
1205	1431
1206	1432	/**
	1433	* Returns the database connection resource or object
	1434	*
	1435	* @return mixed The database connection
	1436	*/
	1437	public function getConnection()
	1438	{
	1439	$this->connectToDatabase();
	1440	return $this->connection;
	1441	}
	1442
	1443
	1444	/**
1207	1445	* Gets the name of the database currently connected to
1208	1446	*
…	…
1229	1467
1230	1468	/**
	1469	* Gets the host for this database
	1470	*
	1471	* @return string The host
	1472	*/
	1473	public function getHost()
	1474	{
	1475	return $this->host;
	1476	}
	1477
	1478
	1479	/**
	1480	* Gets the port for this database
	1481	*
	1482	* @return string The port
	1483	*/
	1484	public function getPort()
	1485	{
	1486	return $this->port;
	1487	}
	1488
	1489
	1490	/**
	1491	* Gets the fSQLTranslation object used for translated queries
	1492	*
	1493	* @return fSQLTranslation The SQL translation object
	1494	*/
	1495	public function getSQLTranslation()
	1496	{
	1497	if (!$this->translation) { new fSQLTranslation($this); }
	1498	return $this->translation;
	1499	}
	1500
	1501
	1502	/**
1231	1503	* Gets the database type
1232	1504	*
…	…
1236	1508	{
1237	1509	return $this->type;
	1510	}
	1511
	1512
	1513	/**
	1514	* Gets the username for this database
	1515	*
	1516	* @return string The username
	1517	*/
	1518	public function getUsername()
	1519	{
	1520	return $this->username;
1238	1521	}
1239	1522
…	…
1247	1530	private function handleAutoIncrementedValue($result)
1248	1531	{
1249		if (!preg_match('#^\s*INSERT~~#i', $result->getSQL()~~)) {
	1532	if (!preg_match('#^\s*INSERT\s+INTO\s+(\w+)#i', $result->getSQL(), $table_match)) {
1250	1533	$result->setAutoIncrementedValue(NULL);
1251	1534	return;
1252	1535	}
	1536	$table = strtolower($table_match[1]);
1253	1537
1254	1538	$insert_id = NULL;
	1539
	1540	if ($this->type == 'oracle') {
	1541	if (!isset($this->schema_info['sequences'])) {
	1542	$sql = "SELECT
	1543	TABLE_NAME,
	1544	TRIGGER_BODY
	1545	FROM
	1546	USER_TRIGGERS
	1547	WHERE
	1548	TRIGGERING_EVENT = 'INSERT' AND
	1549	STATUS = 'ENABLED' AND
	1550	TRIGGER_NAME NOT LIKE 'BIN\$%'";
	1551
	1552	$this->schema_info['sequences'] = array();
	1553
	1554	foreach ($this->query($sql) as $row) {
	1555	if (preg_match('#SELECT\s+(\w+).nextval\s+INTO\s+:new\.(\w+)\s+FROM\s+dual#i', $row['trigger_body'], $matches)) {
	1556	$this->schema_info['sequences'][strtolower($row['table_name'])] = array('sequence' => $matches[1], 'column' => $matches[2]);
	1557	}
	1558	}
	1559
	1560	if ($this->cache) {
	1561	$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
	1562	}
	1563	}
	1564
	1565	if (!isset($this->schema_info['sequences'][$table]) \|\| preg_match('#INSERT\s+INTO\s+' . preg_quote($table, '#') . '\s+VALUES\s+$[^$]*?\b' . preg_quote($this->schema_info['sequences'][$table]['column'], '#') . '\b#i', $result->getSQL())) {
	1566	return;
	1567	}
	1568
	1569	$insert_id_sql = "SELECT " . $this->schema_info['sequences'][$table]['sequence'] . ".currval AS INSERT_ID FROM dual";
	1570	}
	1571
	1572	if ($this->type == 'postgresql') {
	1573	if (!isset($this->schema_info['sequences'])) {
	1574	$sql = "SELECT
	1575	pg_class.relname AS table_name,
	1576	pg_attribute.attname AS column
	1577	FROM
	1578	pg_attribute INNER JOIN
	1579	pg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN
	1580	pg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
	1581	WHERE
	1582	NOT pg_attribute.attisdropped AND
	1583	pg_attrdef.adsrc LIKE 'nextval(%'";
	1584
	1585	$this->schema_info['sequences'] = array();
	1586
	1587	foreach ($this->query($sql) as $row) {
	1588	$this->schema_info['sequences'][strtolower($row['table_name'])] = $row['column'];
	1589	}
	1590
	1591	if ($this->cache) {
	1592	$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
	1593	}
	1594	}
	1595
	1596	if (!isset($this->schema_info['sequences'][$table]) \|\| preg_match('#INSERT\s+INTO\s+' . preg_quote($table, '#') . '\s+VALUES\s+$[^$]*?\b' . preg_quote($this->schema_info['sequences'][$table], '#') . '\b#i', $result->getSQL())) {
	1597	return;
	1598	}
	1599	}
1255	1600
1256	1601	if ($this->extension == 'mssql') {
…	…
1265	1610	$insert_id = mysqli_insert_id($this->connection);
1266	1611
1267		} elseif ($this->extension == 'odbc') {
1268		$insert_id_res = odbc_exec("SELECT @@IDENTITY AS insert_id", $this->connection);
	1612	} elseif ($this->extension == 'oci8') {
	1613	$oci_statement = oci_parse($this->connection, $insert_id_sql);
	1614	oci_execute($oci_statement);
	1615	$insert_id_row = oci_fetch_array($oci_statement, OCI_ASSOC);
	1616	$insert_id = $insert_id_row['INSERT_ID'];
	1617	oci_free_statement($oci_statement);
	1618
	1619	} elseif ($this->extension == 'odbc' && $this->type == 'mssql') {
	1620	$insert_id_res = odbc_exec($this->connection, "SELECT @@IDENTITY AS insert_id");
1269	1621	$insert_id = odbc_result($insert_id_res, 'insert_id');
1270	1622	odbc_free_result($insert_id_res);
1271	1623
	1624	} elseif ($this->extension == 'odbc' && $this->type == 'oracle') {
	1625	$insert_id_res = odbc_exec($this->connection, $insert_id_sql);
	1626	$insert_id = odbc_result($insert_id_res, 'insert_id');
	1627	odbc_free_result($insert_id_res);
	1628
1272	1629	} elseif ($this->extension == 'pgsql') {
1273	1630
1274		~~if (!$this->isInsideTransaction()) {~~
1275		~~pg_query($this->connection, "BEGIN");~~
1276		~~} else {~~
1277		~~pg_query($this->connection, "SAVEPOINT get_last_val");~~
1278		}
1279
1280		~~$old_level = error_reporting(error_reporting() & ~E_WARNING);~~
1281	1631	$insert_id_res = pg_query($this->connection, "SELECT lastval()");
1282		error_reporting($old_level);
1283
1284		if (is_resource($insert_id_res)) {
1285		$insert_id_row = pg_fetch_assoc($insert_id_res);
1286		$insert_id = array_shift($insert_id_row);
1287		pg_free_result($insert_id_res);
1288
1289		if (!$this->isInsideTransaction()) {
1290		pg_query($this->connection, "COMMIT");
1291		}
1292
1293		} else {
1294		if (!$this->isInsideTransaction()) {
1295		pg_query($this->connection, "ROLLBACK");
1296		} else {
1297		pg_query($this->connection, "ROLLBACK TO get_last_val");
1298		}
1299		}
	1632	$insert_id_row = pg_fetch_assoc($insert_id_res);
	1633	$insert_id = array_shift($insert_id_row);
	1634	pg_free_result($insert_id_res);
1300	1635
1301	1636	} elseif ($this->extension == 'sqlite') {
…	…
1304	1639	} elseif ($this->extension == 'sqlsrv') {
1305	1640	$insert_id_res = sqlsrv_query($this->connection, "SELECT @@IDENTITY AS insert_id");
1306		$insert_id = sqlsrv_get_field($insert_id_res, 0);
	1641	$insert_id_row = sqlsrv_fetch_array($insert_id_res, SQLSRV_FETCH_ASSOC);
	1642	$insert_id = $insert_id_row['insert_id'];
1307	1643	sqlsrv_free_stmt($insert_id_res);
1308	1644
…	…
1324	1660	// If there was an error we don't have an insert id
1325	1661	}
1326
1327		case 'postgresql':
	1662	break;
	1663
	1664	case 'oracle':
1328	1665	try {
1329
1330		if (!$this->isInsideTransaction()) {
1331		$this->connection->beginTransaction();
1332		} else {
1333		$this->connection->query("SAVEPOINT get_last_val");
1334		}
1335
1336		$insert_id_statement = $this->connection->query("SELECT lastval()");
	1666	$insert_id_statement = $this->connection->query($insert_id_sql);
1337	1667	if (!$insert_id_statement) {
1338	1668	throw new Exception();
…	…
1342	1672	$insert_id = array_shift($insert_id_row);
1343	1673
1344		~~if (!$this->isInsideTransaction()) {~~
1345		~~$this->connection->commit();~~
1346		}
1347
1348	1674	} catch (Exception $e) {
1349
1350		if (!$this->isInsideTransaction()) {
1351		$this->connection->rollBack();
1352		} else {
1353		$this->connection->exec("ROLLBACK TO get_last_val");
1354		}
1355
	1675	// If there was an error we don't have an insert id
1356	1676	}
	1677	break;
	1678
	1679	case 'postgresql':
	1680
	1681	$insert_id_statement = $this->connection->query("SELECT lastval()");
	1682	$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
	1683	$insert_id = array_shift($insert_id_row);
	1684	$insert_id_statement->closeCursor();
	1685	unset($insert_id_statement);
	1686
1357	1687	break;
1358	1688
…	…
1372	1702
1373	1703	/**
1374		* ~~Will hand off a transaction query to the PDO method if the current DB connection is via PDO~~
1375		*
1376		* @param string ~~$sql~~ The SQL to check for a transaction query
	1704	* Makes sure each database and extension handles BEGIN, COMMIT and ROLLBACK
	1705	*
	1706	* @param string &$sql The SQL to check for a transaction query
1377	1707	* @param string $result_class The type of result object to create
1378		* @return mixed If the connection is not via PDO will return `FALSE`, otherwise an object of the type $result_class
1379		*/
1380		private function handleTransactionQueries($sql, $result_class)
1381		{
1382		if (!is_object($this->connection) \|\| get_class($this->connection) != 'PDO') {
	1708	* @return mixed `FALSE` if normal processing should continue, otherwise an object of the type $result_class
	1709	*/
	1710	private function handleTransactionQueries(&$sql, $result_class)
	1711	{
	1712	// SQL Server supports transactions, but starts then with BEGIN TRANSACTION
	1713	if ($this->type == 'mssql' && preg_match('#^\s(begin\|start(\s+transaction)?)\s#i', $sql)) {
	1714	$sql = 'BEGIN TRANSACTION';
	1715	}
	1716
	1717	$begin = FALSE;
	1718	$commit = FALSE;
	1719	$rollback = FALSE;
	1720
	1721	// Track transactions since most databases don't support nesting
	1722	if (preg_match('#^\s(begin\|start)(\s+(transaction\|work))?\s$#iD', $sql)) {
	1723	if ($this->inside_transaction) {
	1724	throw new fProgrammerException('A transaction is already in progress');
	1725	}
	1726	$this->inside_transaction = TRUE;
	1727	$begin = TRUE;
	1728
	1729	} elseif (preg_match('#^\s(commit)(\s+(transaction\|work))?\s$#iD', $sql)) {
	1730	if (!$this->inside_transaction) {
	1731	throw new fProgrammerException('There is no transaction in progress');
	1732	}
	1733	$this->inside_transaction = FALSE;
	1734	$commit = TRUE;
	1735
	1736	} elseif (preg_match('#^\s(rollback)(\s+(transaction\|work))?\s$#iD', $sql)) {
	1737	if (!$this->inside_transaction) {
	1738	throw new fProgrammerException('There is no transaction in progress');
	1739	}
	1740	$this->inside_transaction = FALSE;
	1741	$rollback = TRUE;
	1742	}
	1743
	1744	if (!$begin && !$commit && !$rollback) {
	1745	return FALSE;
	1746	}
	1747
	1748	// The PDO, OCI8, ODBC and SQLSRV extensions require special handling through methods and functions
	1749	$is_pdo = $this->extension == 'pdo';
	1750	$is_oci = $this->extension == 'oci8';
	1751	$is_odbc = $this->extension == 'odbc';
	1752	$is_sqlsrv = $this->extension == 'sqlsrv';
	1753
	1754	if (!$is_pdo && !$is_oci && !$is_odbc && !$is_sqlsrv) {
1383	1755	return FALSE;
1384	1756	}
1385	1757
1386		$success = FALSE;
1387
1388		try {
1389		if (preg_match('#^\s(begin\|start)(\s+transaction)?\s$#iD', $sql)) {
1390		$this->connection->beginTransaction();
1391		$success = TRUE;
1392		}
1393		if (preg_match('#^\s(commit)(\s+transaction)?\s$#iD', $sql)) {
1394		$this->connection->commit();
1395		$success = TRUE;
1396		}
1397		if (preg_match('#^\s(rollback)(\s+transaction)?\s$#iD', $sql)) {
1398		$this->connection->rollBack();
1399		$success = TRUE;
1400		}
1401		} catch (Exception $e) {
1402		$db_type_map = array(
1403		'mssql' => 'MSSQL',
1404		'mysql' => 'MySQL',
1405		'postgresql' => 'PostgreSQL',
1406		'sqlite' => 'SQLite'
1407		);
1408
1409		throw new fSQLException(
1410		'%1$s error (%2$s) in %3$s',
1411		$db_type_map[$this->type],
1412		$e->getMessage(),
1413		$sql
1414		);
1415		}
1416
1417		if ($success) {
1418		$result = new $result_class($this->type, $this->extension);
1419		$result->setSQL($sql);
1420		$result->setResult(TRUE);
1421		return $result;
1422		}
1423
1424		return FALSE;
	1758	// PDO seems to act weird if you try to start transactions through a normal query call
	1759	if ($is_pdo) {
	1760	try {
	1761	$is_mssql = $this->type == 'mssql' && substr($this->database, 0, 4) != 'dsn:';
	1762	$is_oracle = $this->type == 'oracle' && substr($this->database, 0, 4) != 'dsn:';
	1763	if ($begin) {
	1764	// The SQL Server PDO object hasn't implemented transactions
	1765	if ($is_mssql) {
	1766	$this->connection->exec('BEGIN TRANSACTION');
	1767	} elseif ($is_oracle) {
	1768	$this->connection->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
	1769	} else {
	1770	$this->connection->beginTransaction();
	1771	}
	1772
	1773	} elseif ($commit) {
	1774	if ($is_mssql) {
	1775	$this->connection->exec('COMMIT');
	1776	} elseif ($is_oracle) {
	1777	$this->connection->exec('COMMIT');
	1778	$this->connection->setAttribute(PDO::ATTR_AUTOCOMMIT, TRUE);
	1779	} else {
	1780	$this->connection->commit();
	1781	}
	1782
	1783	} elseif ($rollback) {
	1784	if ($is_mssql) {
	1785	$this->connection->exec('ROLLBACK');
	1786	} elseif ($is_oracle) {
	1787	$this->connection->exec('ROLLBACK');
	1788	$this->connection->setAttribute(PDO::ATTR_AUTOCOMMIT, TRUE);
	1789	} else {
	1790	$this->connection->rollBack();
	1791	}
	1792	}
	1793
	1794	} catch (Exception $e) {
	1795
	1796	$db_type_map = array(
	1797	'mssql' => 'MSSQL',
	1798	'mysql' => 'MySQL',
	1799	'oracle' => 'Oracle',
	1800	'postgresql' => 'PostgreSQL',
	1801	'sqlite' => 'SQLite'
	1802	);
	1803
	1804	throw new fSQLException(
	1805	'%1$s error (%2$s) in %3$s',
	1806	$db_type_map[$this->type],
	1807	$e->getMessage(),
	1808	$sql
	1809	);
	1810	}
	1811
	1812	} elseif ($is_oci) {
	1813	if ($commit) {
	1814	oci_commit($this->connection);
	1815	} elseif ($rollback) {
	1816	oci_rollback($this->connection);
	1817	}
	1818
	1819	} elseif ($is_odbc) {
	1820	if ($begin) {
	1821	odbc_autocommit($this->connection, FALSE);
	1822	} elseif ($commit) {
	1823	odbc_commit($this->connection);
	1824	odbc_autocommit($this->connection, TRUE);
	1825	} elseif ($rollback) {
	1826	odbc_rollback($this->connection);
	1827	odbc_autocommit($this->connection, TRUE);
	1828	}
	1829
	1830	} elseif ($is_sqlsrv) {
	1831	if ($begin) {
	1832	sqlsrv_begin_transaction($this->connection);
	1833	} elseif ($commit) {
	1834	sqlsrv_commit($this->connection);
	1835	} elseif ($rollback) {
	1836	sqlsrv_rollback($this->connection);
	1837	}
	1838	}
	1839
	1840	$result = new $result_class($this->type, $this->extension);
	1841	$result->setSQL($sql);
	1842	$result->setResult(TRUE);
	1843	return $result;
	1844	}
	1845
	1846
	1847	/**
	1848	* Injects an fSQLTranslation object to handle translation
	1849	*
	1850	* @internal
	1851	*
	1852	* @param fSQLTranslation $sql_translation The SQL translation object
	1853	* @return void
	1854	*/
	1855	public function inject($sql_translation)
	1856	{
	1857	$this->translation = $sql_translation;
1425	1858	}
1426	1859
…	…
1434	1867	{
1435	1868	return $this->inside_transaction;
	1869	}
	1870
	1871
	1872	/**
	1873	* Creates a unique cache prefix to help prevent cache conflicts
	1874	*
	1875	* @return void
	1876	*/
	1877	private function makeCachePrefix()
	1878	{
	1879	$prefix = 'fDatabase::' . $this->type . '::';
	1880	if ($this->host) {
	1881	$prefix .= $this->host . '::';
	1882	}
	1883	if ($this->port) {
	1884	$prefix .= $this->port . '::';
	1885	}
	1886	$prefix .= $this->database . '::';
	1887	if ($this->username) {
	1888	$prefix .= $this->username . '::';
	1889	}
	1890	return $prefix;
	1891	}
	1892
	1893
	1894	/**
	1895	* Prepares the SQL by escaping values, spliting queries, cleaning escaped semicolons, fixing backslashed single quotes and translating
	1896	*
	1897	* @param string $sql The SQL to prepare
	1898	* @param array $values Literal values to escape into the SQL
	1899	* @param boolean $translate If the SQL should be translated
	1900	* @return array The split out SQL queries, queries that have been translated will have a string key of the original SQL, non-translated SQL will have a numeric key
	1901	*/
	1902	private function prepareSQL($sql, $values, $translate)
	1903	{
	1904	$this->connectToDatabase();
	1905
	1906	// Ensure an SQL statement was passed
	1907	if (empty($sql)) {
	1908	throw new fProgrammerException('No SQL statement passed');
	1909	}
	1910
	1911	if ($values) {
	1912	$sql = call_user_func_array(
	1913	$this->escape,
	1914	array_merge(array($sql), $values)
	1915	);
	1916	}
	1917
	1918	// Fix \' in MySQL and PostgreSQL
	1919	if(($this->type == 'mysql' \|\| $this->type == 'postgresql') && strpos($sql, '\\') !== FALSE) {
	1920	$sql = preg_replace("#(?<!\\\\)((\\\\{2})*)\\\\'#", "\\1''", $sql);
	1921	}
	1922
	1923	$strings = array(array());
	1924	$queries = array('');
	1925	$number = 0;
	1926
	1927	// Separate the SQL from quoted values
	1928	preg_match_all("#(?:'([^'](?:''))*?')\|(?:[^']+)#", $sql, $matches);
	1929
	1930	foreach ($matches[0] as $match) {
	1931	if ($match[0] == "'") {
	1932	$queries[$number] .= ':string_' . sizeof($strings[$number]);
	1933	$strings[$number][] = $match;
	1934	} else {
	1935	$split_queries = preg_split('#(?<!\\\\);#', $match);
	1936
	1937	$queries[$number] .= $split_queries[0];
	1938
	1939	for ($i=1; $i < sizeof($split_queries); $i++) {
	1940	$queries[$number] = trim($queries[$number]);
	1941	$number++;
	1942	$strings[$number] = array();
	1943	$queries[$number] = $split_queries[$i];
	1944	}
	1945	}
	1946	}
	1947	if (!trim($queries[$number])) {
	1948	unset($queries[$number]);
	1949	unset($strings[$number]);
	1950	} else {
	1951	$queries[$number] = trim($queries[$number]);
	1952	}
	1953
	1954	// Translate the SQL queries, this takes care of unescaping and reinserting strings
	1955	if ($translate) {
	1956	$output = $this->getSQLTranslation()->translate($queries, $strings);
	1957
	1958	// For untranslated queries we need to unescape and reinsert strings
	1959	} else {
	1960	$output = array();
	1961	foreach ($queries as $number => $query) {
	1962	// Unescape literal semicolons in the queries
	1963	$query = preg_replace('#(?<!\\\\)\\\\;#', ';', $query);
	1964	// Put the strings back into the SQL
	1965	foreach ($strings[$number] as $index => $string) {
	1966	$query = preg_replace('#:string_' . $index . '\b#', str_replace('\\', '\\\\', $string), $query, 1);
	1967	}
	1968	$output[] = $query;
	1969	}
	1970	}
	1971
	1972	return $output;
1436	1973	}
1437	1974
…	…
1447	1984	public function query($sql)
1448	1985	{
1449		$this->connectToDatabase();
1450
1451		// Ensure an SQL statement was passed
1452		if (empty($sql)) {
1453		throw new fProgrammerException('No SQL statement passed');
1454		}
1455
1456		if (func_num_args() > 1) {
1457		$args = func_get_args();
1458		$sql = call_user_func_array($this->escape, $args);
1459		}
1460
1461		// Split multiple queries
1462		if (strpos($sql, ';') !== FALSE) {
1463		$sql_queries = $this->explodeQueries($sql);
1464		$sql = array_shift($sql_queries);
1465		}
1466
1467		$start_time = microtime(TRUE);
1468
1469		$this->trackTransactions($sql);
1470		if (!$result = $this->handleTransactionQueries($sql, 'fResult')) {
1471		$result = new fResult($this->type, $this->extension, $this->character_set);
	1986	$args = func_get_args();
	1987	$queries = $this->prepareSQL(
	1988	$sql,
	1989	array_slice($args, 1),
	1990	FALSE
	1991	);
	1992
	1993	$output = array();
	1994	foreach ($queries as $query) {
	1995	$output[] = $this->runQuery($query, 'fResult');
	1996	}
	1997
	1998	return sizeof($output) == 1 ? $output[0] : $output;
	1999	}
	2000
	2001
	2002	/**
	2003	* Runs a single query and times it, removes any old unbuffered queries before starting
	2004	*
	2005	* @param string $sql The SQL statement to execute
	2006	* @param string $result_type The type of result object to return, fResult or fUnbufferedResult
	2007	* @return fResult\|fUnbufferedResult The result for the query
	2008	*/
	2009	private function runQuery($sql, $result_type)
	2010	{
	2011	if ($this->unbuffered_result) {
	2012	$this->unbuffered_result->__destruct();
	2013	$this->unbuffered_result = NULL;
	2014	}
	2015
	2016	$start_time = microtime(TRUE);
	2017
	2018	if (!$result = $this->handleTransactionQueries($sql, $result_type)) {
	2019	$result = new $result_type($this->type, $this->extension, $this->type == 'mssql' ? $this->schema_info['character_set'] : NULL);
1472	2020	$result->setSQL($sql);
1473	2021
1474		$this->executeQuery($result);
	2022	if ($result_type == 'fResult') {
	2023	$this->executeQuery($result);
	2024	} else {
	2025	$this->executeUnbufferedQuery($result);
	2026	}
1475	2027	}
1476	2028
…	…
1499	2051	}
1500	2052
1501		~~// Handle multiple SQL queries~~
1502		~~if (!empty($sql_queries)) {~~
1503		~~$result = array($result);~~
1504		~~foreach ($sql_queries as $sql_query) {~~
1505		~~$result[] = $this->query($sql_query);~~
1506		}
1507		}
1508
1509	2053	return $result;
1510	2054	}
…	…
1515	2059	*
1516	2060	* @param fResult $result The result object for the query
1517		* @param mixed $resource Only applicable for `pdo`, `o~~dbc` and `sqlsrv` extentions, this is either the `PDOStatement` object or~~ `odbc` or `sqlsrv` resource
	2061	* @param mixed $resource Only applicable for `pdo`, `oci8`, `odbc` and `sqlsrv` extentions, this is either the `PDOStatement` object or the `oci8`, `odbc` or `sqlsrv` resource
1518	2062	* @return void
1519	2063	*/
…	…
1527	2071	} elseif ($this->extension == 'mysqli') {
1528	2072	$result->setAffectedRows(mysqli_affected_rows($this->connection));
	2073	} elseif ($this->extension == 'oci8') {
	2074	$result->setAffectedRows(oci_num_rows($resource));
1529	2075	} elseif ($this->extension == 'odbc') {
1530	2076	$result->setAffectedRows(odbc_num_rows($resource));
…	…
1538	2084	// This fixes the fact that rowCount is not reset for non INSERT/UPDATE/DELETE statements
1539	2085	try {
1540		if (!$resource->fetch()) {
	2086	if (!$resource \|\| !$resource->fetch()) {
1541	2087	throw new PDOException();
1542	2088	}
1543	2089	$result->setAffectedRows(0);
1544	2090	} catch (PDOException $e) {
1545		$result->setAffectedRows($resource->rowCount());
	2091	// The SQLite PDO driver seems to return 1 when no rows are returned from a SELECT statement
	2092	if ($this->type == 'sqlite' && $this->extension == 'pdo' && preg_match('#^\s*SELECT#i', $result->getSQL())) {
	2093	$result->setAffectedRows(0);
	2094	} elseif (!$resource) {
	2095	$result->setAffectedRows(0);
	2096	} else {
	2097	$result->setAffectedRows($resource->rowCount());
	2098	}
1546	2099	}
1547	2100	}
…	…
1576	2129
1577	2130	/**
1578		* Keeps track to see if a transaction is being started or stopped
1579		*
1580		* @param string $sql The SQL to check for a transaction query
1581		* @return void
1582		*/
1583		~~private function trackTransactions($sql)~~
1584		{
1585		~~if (preg_match('#^\s(begin\|start)(\s+transaction)?\s$#iD', $sql)) {~~
1586		~~if ($this->inside_transaction) {~~
1587		~~throw new fProgrammerException('A transaction is already in progress');~~
1588		}
1589		~~$this->inside_transaction = TRUE;~~
1590
1591		~~} elseif (preg_match('#^\s(commit)(\s+transaction)?\s$#iD', $sql)) {~~
1592		~~if (!$this->inside_transaction) {~~
1593		~~throw new fProgrammerException('There is no transaction in progress');~~
1594		}
1595		~~$this->inside_transaction = FALSE;~~
1596
1597		~~} elseif (preg_match('#^\s(rollback)(\s+transaction)?\s$#iD', $sql)) {~~
1598		~~if (!$this->inside_transaction) {~~
1599		~~throw new fProgrammerException('There is no transaction in progress');~~
1600		}
1601		~~$this->inside_transaction = FALSE;~~
1602		}
1603		}
1604
1605
1606		/**
1607	2131	* Translates the SQL statement using fSQLTranslation and executes it
1608	2132	*
…	…
1614	2138	public function translatedQuery($sql)
1615	2139	{
1616		if (!$this->translation) {
1617		$this->connectToDatabase();
1618		$this->translation = new fSQLTranslation($this, $this->connection);
1619		$this->translation->enableDebugging($this->debug);
1620		}
1621
1622		if (func_num_args() > 1) {
1623		$args = func_get_args();
1624		$sql = call_user_func_array($this->escape, $args);
1625		}
1626
1627		$result = $this->query($this->translation->translate($sql));
1628		$result->setUntranslatedSQL($sql);
1629		return $result;
	2140	$args = func_get_args();
	2141	$queries = $this->prepareSQL(
	2142	$sql,
	2143	array_slice($args, 1),
	2144	TRUE
	2145	);
	2146
	2147	$output = array();
	2148	foreach ($queries as $original_query => $query) {
	2149	$result = $this->runQuery($query, 'fResult');
	2150	if (!is_numeric($original_query)) {
	2151	$result->setUntranslatedSQL($original_query);
	2152	}
	2153	$output[] = $result;
	2154	}
	2155
	2156	return sizeof($output) == 1 ? $output[0] : $output;
1630	2157	}
1631	2158
…	…
1645	2172	public function unbufferedQuery($sql)
1646	2173	{
1647		$this->connectToDatabase();
1648
1649		// Ensure an SQL statement was passed
1650		if (empty($sql)) {
1651		throw new fProgrammerException('No SQL statement passed');
1652		}
1653
1654		if (func_num_args() > 1) {
1655		$args = func_get_args();
1656		$sql = call_user_func_array($this->escape, $args);
1657		}
1658
1659		if ($this->unbuffered_result) {
1660		$this->unbuffered_result->__destruct();
1661		}
1662
1663		$start_time = microtime(TRUE);
1664
1665		$this->trackTransactions($sql);
1666		if (!$result = $this->handleTransactionQueries($sql, 'fUnbufferedRequest')) {
1667		$result = new fUnbufferedResult($this->type, $this->extension, $this->character_set);
1668		$result->setSQL($sql);
1669
1670		$this->executeUnbufferedQuery($result);
1671		}
1672
1673		// Write some debugging info
1674		$query_time = microtime(TRUE) - $start_time;
1675		$this->query_time += $query_time;
1676		fCore::debug(
1677		self::compose(
1678		'Query time was %1$s seconds for (unbuffered):%2$s',
1679		$query_time,
1680		"\n" . $result->getSQL()
1681		),
1682		$this->debug
	2174	$args = func_get_args();
	2175	$queries = $this->prepareSQL(
	2176	$sql,
	2177	array_slice($args, 1),
	2178	FALSE
1683	2179	);
1684	2180
1685		if ($this->slow_query_threshold && $query_time > $this->slow_query_threshold) {
1686		trigger_error(
1687		self::compose(
1688		'The following query took %1$s milliseconds, which is above the slow query threshold of %2$s:%3$s',
1689		$query_time,
1690		$this->slow_query_threshold,
1691		"\n" . $result->getSQL()
1692		),
1693		E_USER_WARNING
	2181	if (sizeof($queries) > 1) {
	2182	throw new fProgrammerException(
	2183	'Only a single unbuffered query can be run at a time, however %d were passed',
	2184	sizeof($queries)
1694	2185	);
1695	2186	}
	2187
	2188	$result = $this->runQuery($queries[0], 'fUnbufferedResult');
1696	2189
1697	2190	$this->unbuffered_result = $result;
…	…
1715	2208	public function unbufferedTranslatedQuery($sql)
1716	2209	{
1717		if (!$this->translation) {
1718		$this->connectToDatabase();
1719		$this->translation = new fSQLTranslation($this, $this->connection);
1720		}
1721
1722		if (func_num_args() > 1) {
1723		$args = func_get_args();
1724		$sql = call_user_func_array($this->escape, $args);
1725		}
1726
1727		$result = $this->unbufferedQuery($this->translation->translate($sql));
1728		$result->setUntranslatedSQL($sql);
	2210	$args = func_get_args();
	2211	$queries = $this->prepareSQL(
	2212	$sql,
	2213	array_slice($args, 1),
	2214	TRUE
	2215	);
	2216
	2217	if (sizeof($queries) > 1) {
	2218	throw new fProgrammerException(
	2219	'Only a single unbuffered query can be run at a time, however %d were passed',
	2220	sizeof($queries)
	2221	);
	2222	}
	2223
	2224	$query_keys = array_keys($queries);
	2225	$original_query = $query_keys[0];
	2226
	2227	$result = $this->runQuery($queries[$original_query], 'fUnbufferedResult');
	2228	$result->setUntranslatedSQL($original_query);
	2229
	2230	$this->unbuffered_result = $result;
	2231
1729	2232	return $result;
1730	2233	}
…	…
1746	2249	*
1747	2250	* @param string $data_type The data type being unescaped - see method description for valid values
1748		* @param mixed $value The value to unescape
	2251	* @param mixed $value The value or array of values to unescape
1749	2252	* @return mixed The unescaped value
1750	2253	*/
1751	2254	public function unescape($data_type, $value)
1752	2255	{
	2256	$callback = NULL;
	2257
1753	2258	switch ($data_type) {
1754	2259	case 'blob':
1755	2260	case '%l':
1756		return $this->unescapeBlob($value);
	2261	$callback = $this->unescapeBlob;
	2262	break;
1757	2263	case 'boolean':
1758	2264	case '%b':
1759		return $this->unescapeBoolean($value);
	2265	$callback = $this->unescapeBoolean;
	2266	break;
1760	2267	case 'date':
1761	2268	case '%d':
1762		return $this->unescapeDate($value);
	2269	$callback = $this->unescapeDate;
	2270	break;
1763	2271	case 'float':
1764	2272	case '%f':
1765		return $this->unescapeFloat($value);
	2273	$callback = $this->unescapeFloat;
	2274	break;
1766	2275	case 'integer':
1767	2276	case '%i':
1768		return $this->unescapeInteger($value);
	2277	$callback = $this->unescapeInteger;
	2278	break;
1769	2279	case 'string':
1770	2280	case 'varchar':
…	…
1772	2282	case 'text':
1773	2283	case '%s':
1774		return $this->unescapeString($value);
	2284	$callback = $this->unescapeString;
	2285	break;
1775	2286	case 'time':
1776	2287	case '%t':
1777		return $this->unescapeTime($value);
	2288	$callback = $this->unescapeTime;
	2289	break;
1778	2290	case 'timestamp':
1779	2291	case '%p':
1780		return $this->unescapeTimestamp($value);
	2292	$callback = $this->unescapeTimestamp;
	2293	break;
	2294	}
	2295
	2296	if ($callback) {
	2297	if (is_array($value)) {
	2298	return array_map($callback, $value);
	2299	}
	2300	return call_user_func($callback, $value);
1781	2301	}
1782	2302
…	…
1800	2320
1801	2321	if ($this->extension == 'pgsql') {
1802		return pg_unescape_bytea($this->connection, $value);
1803		} if ($this->extension == 'odbc') {
	2322	return pg_unescape_bytea($value);
	2323	} elseif ($this->extension == 'pdo' && is_resource($value)) {
	2324	return stream_get_contents($value);
	2325	} elseif ($this->type == 'mssql' && (substr($this->database, 0, 4) == 'dsn:')) {
1804	2326	return pack('H*', $value);
1805		} else {
	2327	} elseif ($this->extension == 'sqlite') {
	2328	return pack('H*', $value);
	2329	} else {
1806	2330	return $value;
1807	2331	}
…	…
1829	2353	private function unescapeDate($value)
1830	2354	{
	2355	if ($this->extension == 'sqlsrv' && $value instanceof DateTime) {
	2356	return $value->format('Y-m-d');
	2357	} elseif ($this->type == 'mssql') {
	2358	$value = preg_replace('#:\d{3}#', '', $value);
	2359	}
1831	2360	return date('Y-m-d', strtotime($value));
1832	2361	}
…	…
1877	2406	private function unescapeTime($value)
1878	2407	{
	2408	if ($this->extension == 'sqlsrv' && $value instanceof DateTime) {
	2409	return $value->format('H:i:s');
	2410	} elseif ($this->type == 'mssql') {
	2411	$value = preg_replace('#:\d{3}#', '', $value);
	2412	}
1879	2413	return date('H:i:s', strtotime($value));
1880	2414	}
…	…
1889	2423	private function unescapeTimestamp($value)
1890	2424	{
	2425	if ($this->extension == 'sqlsrv' && $value instanceof DateTime) {
	2426	return $value->format('Y-m-d H:i:s');
	2427	} elseif ($this->type == 'mssql') {
	2428	$value = preg_replace('#:\d{3}#', '', $value);
	2429	}
1891	2430	return date('Y-m-d H:i:s', strtotime($value));
1892	2431	}

fORM.php

r517	r562	Hide Line Numbers
10	10	* @link http://flourishlib.com/fORM
11	11	*
12		* @version 1.0.0b6
	12	* @version 1.0.0b7
	13	* @changes 1.0.0b7 Added ::enableSchemaCaching() to replace fORMSchema::enableSmartCaching() [wb, 2009-05-04]
13	14	* @changes 1.0.0b6 Added the ability to pass a class instance to ::addCustomClassTableMapping() [wb, 2009-02-23]
14	15	* @changes 1.0.0b5 Backwards compatibility break - renamed ::addCustomTableClassMapping() to ::addCustomClassTableMapping() and swapped the parameters [wb, 2009-01-26]
…	…
27	28	const classize = 'fORM::classize';
28	29	const defineActiveRecordClass = 'fORM::defineActiveRecordClass';
	30	const enableSchemaCaching = 'fORM::enableSchemaCaching';
29	31	const getActiveRecordMethod = 'fORM::getActiveRecordMethod';
30	32	const getClass = 'fORM::getClass';
…	…
313	315	$class
314	316	);
	317	}
	318
	319
	320	/**
	321	* Enables caching on the fDatabase, fSQLTranslation and fSchema objects used for the ORM
	322	*
	323	* This method will cache database schema information to the three objects
	324	* that use it during normal ORM operation: fDatabase, fSQLTranslation and
	325	* fSchema. To allow for schema changes without having to manually clear
	326	* the cache, all cached information will be cleared if any
	327	* fUnexpectedException objects are thrown.
	328	*
	329	* This method should be called right after fORMDatabase::attach().
	330	*
	331	* @param fCache $cache The object to cache schema information to
	332	* @return void
	333	*/
	334	static public function enableSchemaCaching($cache)
	335	{
	336	$db = fORMDatabase::retrieve();
	337	$db->enableCaching($cache);
	338	fException::registerCallback($db->clearCache, 'fUnexpectedException');
	339
	340	$sql_translation = $db->getSQLTranslation();
	341	$sql_translation->enableCaching($cache);
	342	fException::registerCallback($sql_translation->clearCache, 'fUnexpectedException');
	343
	344	$schema = fORMSchema::retrieve();
	345	$schema->enableCaching($cache);
	346	fException::registerCallback($schema->clearCache, 'fUnexpectedException');
315	347	}
316	348

fORMSchema.php

r458	r562	Hide Line Numbers
3	3	* Provides fSchema class related functions for ORM code
4	4	*
5		* @copyright Copyright (c) 2007-2008 Will Bond
	5	* @copyright Copyright (c) 2007-2009 Will Bond
6	6	* @author Will Bond [wb] <will@flourishlib.com>
7	7	* @license http://flourishlib.com/license
…	…
10	10	* @link http://flourishlib.com/fORMSchema
11	11	*
12		* @version 1.0.0b
13		* @changes 1.0.0b The initial implementation [wb, 2007-06-14]
	12	* @version 1.0.0b2
	13	* @changes 1.0.0b2 Backwards Compatiblity Break - removed ::enableSmartCaching(), fORM::enableSchemaCaching() now provides equivalent functionality [wb, 2009-05-04]
	14	* @changes 1.0.0b The initial implementation [wb, 2007-06-14]
14	15	*/
15	16	class fORMSchema
…	…
17	18	// The following constants allow for nice looking callbacks to static methods
18	19	const attach = 'fORMSchema::attach';
19		~~const enableSmartCaching = 'fORMSchema::enableSmartCaching';~~
20	20	const getRoute = 'fORMSchema::getRoute';
21	21	const getRouteName = 'fORMSchema::getRouteName';
…	…
43	43	{
44	44	self::$schema_object = $schema;
45		}
46
47
48		/**
49		* Turns on schema caching, using fUnexpectedException flushing
50		*
51		* @param string $cache_file The file to use for caching
52		* @return void
53		*/
54		~~static public function enableSmartCaching($cache_file)~~
55		{
56		~~if (!self::retrieve() instanceof fSchema) {~~
57		~~throw new fProgrammerException(~~
58		~~'Smart caching is only available (and most likely only applicable) if you are using the fSchema object'~~
59		);
60		}
61		~~self::retrieve()->setCacheFile($cache_file);~~
62		~~fException::registerCallback(array(self::retrieve(), 'flushInfo'), 'fUnexpectedException');~~
63	45	}
64	46
…	…
309	291
310	292	/**
311		* Copyright (c) 2007-2008 Will Bond <will@flourishlib.com>
	293	* Copyright (c) 2007-2009 Will Bond <will@flourishlib.com>
312	294	*
313	295	* Permission is hereby granted, free of charge, to any person obtaining a copy

fResult.php

r514	r562	Hide Line Numbers
10	10	* @link http://flourishlib.com/fResult
11	11	*
12		* @version 1.0.0b2
	12	* @version 1.0.0b3
	13	* @changes 1.0.0b3 Added support for Oracle, various bug fixes [wb, 2009-05-04]
13	14	* @changes 1.0.0b2 Updated for new fCore API [wb, 2009-02-16]
14	15	* @changes 1.0.0b The initial implementation [wb, 2007-09-25]
…	…
122	123	* @internal
123	124	*
124		* @param string $type The type of database: `'mssql'`, `'mysql'`, `'postgresql'`, `'sqlite'`
	125	* @param string $type The type of database: `'mssql'`, `'mysql'`, `'oracle'`, `'postgresql'`, `'sqlite'`
125	126	* @param string $extension The database extension used: `'array'`, `'mssql'`, `'mysql'`, `'mysqli'`, `'pgsql'`, `'sqlite'`
126	127	* @param string $character_set MSSQL only: the character set to transcode from since MSSQL doesn't do UTF-8
…	…
129	130	public function __construct($type, $extension, $character_set=NULL)
130	131	{
131		$valid_types = array('mssql', 'mysql', 'postgresql', 'sqlite');
	132	$valid_types = array('mssql', 'mysql', 'oracle', 'postgresql', 'sqlite');
132	133	if (!in_array($type, $valid_types)) {
133	134	throw new fProgrammerException(
…	…
139	140
140	141	// Certain extensions don't offer a buffered query, so it is emulated using an array
141		if (in_array($extension, array('odbc', 'pdo', 'sqlsrv'))) {
	142	if (in_array($extension, array('oci8', 'odbc', 'pdo', 'sqlsrv'))) {
142	143	$extension = 'array';
143	144	}
…	…
182	183	// SQLite doesn't have a way to free a result
183	184	}
	185
	186	$this->result = NULL;
184	187	}
185	188
…	…
206	209	if ($this->extension == 'mssql') {
207	210	$row = mssql_fetch_assoc($this->result);
208		$row = $this->fixDblibMSSQLDriver($row);
209
210		// This is an unfortunate fix that required for databases that don't support limit
211		// clauses with an offset. It prevents unrequested columns from being returned.
212		if ($this->untranslated_sql !== NULL && isset($row['__flourish_limit_offset_row_num'])) {
213		unset($row['__flourish_limit_offset_row_num']);
	211	if (!empty($row)) {
	212	$row = $this->fixDblibMSSQLDriver($row);
214	213	}
215	214
…	…
226	225	}
227	226
	227	// Fix uppercase column names to lowercase
	228	if ($row && $this->type == 'oracle') {
	229	$new_row = array();
	230	foreach ($row as $column => $value) {
	231	$new_row[strtolower($column)] = $value;
	232	}
	233	$row = $new_row;
	234	}
	235
	236	// This is an unfortunate fix that required for databases that don't support limit
	237	// clauses with an offset. It prevents unrequested columns from being returned.
	238	if ($row && ($this->type == 'mssql' \|\| $this->type == 'oracle')) {
	239	if ($this->untranslated_sql !== NULL && isset($row['flourish__row__num'])) {
	240	unset($row['flourish__row__num']);
	241	}
	242	}
	243
228	244	// This decodes the data coming out of MSSQL into UTF-8
229		if ($this->type == 'mssql') {
	245	if ($row && $this->type == 'mssql') {
230	246	if ($this->character_set) {
231	247	foreach ($row as $key => $value) {
232		if (!is_string($value) \|\| strpos($key, '~~__flourish_mssqln_') === 0~~) {
	248	if (!is_string($value) \|\| strpos($key, 'fmssqln__') === 0 \|\| isset($row['fmssqln__' . $key]) \|\| preg_match('#[\x0-\x8\xB\xC\xD-\x1F]#', $value)) {
233	249	continue;
234	250	}
…	…
302	318	private function decodeMSSQLNationalColumns($row)
303	319	{
304		if (strpos($this->sql, '~~__flourish_mssqln~~_') === FALSE) {
	320	if (strpos($this->sql, 'fmssqln__') === FALSE) {
305	321	return $row;
306	322	}
…	…
309	325
310	326	foreach ($columns as $column) {
311		if (substr($column, 0, ~~18) != '__flourish_mssqln~~_') {
	327	if (substr($column, 0, 9) != 'fmssqln__') {
312	328	continue;
313	329	}
314	330
315		$real_column = substr($column, 18);
	331	$real_column = substr($column, 9);
316	332
317	333	$row[$real_column] = iconv('ucs-2le', 'utf-8', $row[$column]);
…	…
394	410	ob_end_clean();
395	411
396		$using_dblib = preg_match('#FreeTDS#ims', $module_info, $match);
	412	$using_dblib = !preg_match('#FreeTDS#ims', $module_info, $match);
397	413	}
398	414	}
…	…
596	612	public function setAffectedRows($affected_rows)
597	613	{
	614	if ($affected_rows === -1) { $affected_rows = 0; }
598	615	$this->affected_rows = (int) $affected_rows;
599	616	}

fSQLTranslation.php

r515	r562	Hide Line Numbers
1	1	<?php
2	2	/**
3		* Takes a subset of SQL from MySQL, PostgreSQL, SQLite and MSSQL and translates into the various dialects allowing for cross-database code
	3	* Takes a subset of SQL from MySQL, PostgreSQL, Oracle, SQLite and MSSQL and translates into the various dialects allowing for cross-database code
4	4	*
5	5	* @copyright Copyright (c) 2007-2009 Will Bond
…	…
10	10	* @link http://flourishlib.com/fSQLTranslation
11	11	*
12		* @internal
13		*
14		* @version 1.0.0b2
	12	* @version 1.0.0b3
	13	* @changes 1.0.0b3 Added Oracle and caching support, various bug fixes [wb, 2009-05-04]
15	14	* @changes 1.0.0b2 Fixed a notice with SQLite foreign key constraints having no `ON` clauses [wb, 2009-02-21]
16	15	* @changes 1.0.0b The initial implementation [wb, 2007-09-25]
…	…
46	45	}
47	46
48
49		/**
50		* Takes a Flourish SQL `SELECT` query and parses it into clauses.
51		*
52		* The select statement must be of the format:
53		*
54		* {{{
55		~~* SELECT [ table_name. \| alias. ]*~~
56		* FROM table [ AS alias ] [ [ INNER \| OUTER ] [ LEFT \| RIGHT ] JOIN other_table ON condition \| , ] ...
57		* [ WHERE condition [ , condition ]... ]
58		* [ GROUP BY conditions ]
59		* [ HAVING conditions ]
60		* [ ORDER BY [ column \| expression ] [ ASC \| DESC ] [ , [ column \| expression ] [ ASC \| DESC ] ] ... ]
61		* [ LIMIT integer [ OFFSET integer ] ]
62		* }}}
63		*
64		* The returned array will contain the following keys, which may have a `NULL` or non-empty string value:
65		*
66		* - `'SELECT'`
67		* - `'FROM'`
68		* - `'WHERE'`
69		* - `'GROUP BY'`
70		* - `'HAVING'`
71		* - `'ORDER BY'`
72		* - `'LIMIT'`
73		*
74		* @param string $sql The SQL to parse
75		* @return array The various clauses of the `SELECT` statement - see method description for details
76		*/
77		~~static private function parseSelectSQL($sql)~~
78		{
79		~~// Split the strings out of the sql so parsing doesn't get messed up by quoted values~~
80		~~preg_match_all("#(?:'(?:''\|\\\\'\|\\\\[^']\|[^'\\\\]+)*')\|(?:[^']+)#", $sql, $matches);~~
81
82		~~$possible_clauses = array('SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT');~~
83		~~$found_clauses = array();~~
84		~~foreach ($possible_clauses as $possible_clause) {~~
85		~~$found_clauses[$possible_clause] = NULL;~~
86		}
87
88		~~$current_clause = 0;~~
89
90		~~foreach ($matches[0] as $match) {~~
91		~~// This is a quoted string value, don't do anything to it~~
92		~~if ($match[0] == "'") {~~
93		~~$found_clauses[$possible_clauses[$current_clause]] .= $match;~~
94
95		~~// Non-quoted strings should be checked for clause markers~~
96		~~} else {~~
97
98		~~// Look to see if a new clause starts in this string~~
99		~~$i = 1;~~
100		~~while ($current_clause+$i < sizeof($possible_clauses)) {~~
101		~~// If the next clause is found in this string~~
102		~~if (stripos($match, $possible_clauses[$current_clause+$i]) !== FALSE) {~~
103		~~list($before, $after) = preg_split('#\s' . $possible_clauses[$current_clause+$i] . '\s#i', $match);~~
104		~~$found_clauses[$possible_clauses[$current_clause]] .= preg_replace('#\s' . $possible_clauses[$current_clause] . '\s#i', '', $before);~~
105		~~$match = $after;~~
106		~~$current_clause = $current_clause + $i;~~
107		~~$i = 0;~~
108		}
109		~~$i++;~~
110		}
111
112		~~// Otherwise just add on to the current clause~~
113		~~if (!empty($match)) {~~
114		~~$found_clauses[$possible_clauses[$current_clause]] .= preg_replace('#\s' . $possible_clauses[$current_clause] . '\s#i', '', $match);~~
115		}
116		}
117		}
118
119		~~return $found_clauses;~~
120		}
121
122	47
123	48	/**
…	…
130	55	{
131	56	$aliases = array();
132
133		~~preg_match_all("#(?:'(?:''\|\\\\'\|\\\\[^']\|[^'\\\\]+)*')\|(?:[^']+)#", $sql, $matches);~~
134
135		~~$sql = '';~~
136		~~// Replace strings with two single quotes~~
137		~~foreach ($matches[0] as $match) {~~
138		~~if ($match[0] == "'") {~~
139		~~$match = "''";~~
140		}
141		~~$sql .= $match;~~
142		}
143	57
144	58	// Turn comma joins into cross joins
…	…
147	61	}
148	62
149		~~// Error out if we can't figure out the join structure~~
150		~~if (!preg_match('#^(?:\w+(?:\s+(?:as\s+)?(?:\w+))?)(?:\s+(?:(?:CROSS\|INNER\|OUTER\|LEFT\|RIGHT)?\s+)JOIN\s+(?:\w+(?:\s+(?:as\s+)?(?:\w+))?)(?:\s+ON\s+.)?)*$#isD', $sql)) {~~
151		~~throw new fProgrammerException(~~
152		~~'Unable to parse FROM clause, does not appears to be in comma style or join style'~~
153		);
154		}
155
156	63	$tables = preg_split('#\s+((?:(?:CROSS\|INNER\|OUTER\|LEFT\|RIGHT)?\s+)*?JOIN)\s+#i', $sql);
157	64
158	65	foreach ($tables as $table) {
159	66	// This grabs the table name and alias (if there is one)
160		preg_match('#~~\s([\w.]+)(?:\s+(?:as\s+)?((?!ON)[\w.]+))?\s(?:ON\s+(.*))?~~#im', $table, $parts);
	67	preg_match('#^\s([\w.]+\|$((?:[^()]+\|\((?2)$))\))(?:\s+(?:as\s+)?((?!ON\|USING)[\w.]+))?\s(?:(?:ON\|USING)\s+(.))?\s*$#im', $table, $parts);
161	68
162	69	$table_name = $parts[1];
163		$table_alias = (isset($parts[2])) ? $parts[2] : $parts[1];
	70	$table_alias = (isset($parts[2])) ? $parts[2] : $parts[1];
164	71
165	72	$aliases[$table_alias] = $table_name;
…	…
181	88	{
182	89	return 1/tan($x);
	90	}
	91
	92
	93	/**
	94	* Callback for custom SQLite function; returns the current date
	95	*
	96	* @internal
	97	*
	98	* @return string The current date
	99	*/
	100	static public function sqliteDate()
	101	{
	102	return date('Y-m-d');
183	103	}
184	104
…	…
220	140
221	141	/**
222		* The database connection resource or PDO object
223		*
224		* @var mixed
225		*/
226		private $connection;
	142	* Callback for custom SQLite function; returns the current time
	143	*
	144	* @internal
	145	*
	146	* @return string The current time
	147	*/
	148	static public function sqliteTime()
	149	{
	150	return date('H:i:s');
	151	}
	152
	153
	154	/**
	155	* Callback for custom SQLite function; returns the current timestamp
	156	*
	157	* @internal
	158	*
	159	* @return string The current date
	160	*/
	161	static public function sqliteTimestamp()
	162	{
	163	return date('Y-m-d H:i:s');
	164	}
	165
	166
	167	/**
	168	* The fCache object to cache schema info and, optionally, translated queries to
	169	*
	170	* @var fCache
	171	*/
	172	private $cache;
227	173
228	174	/**
…	…
240	186	private $debug;
241	187
	188	/**
	189	* Database-specific schema information needed for translation
	190	*
	191	* @var array
	192	*/
	193	private $schema_info;
	194
242	195
243	196	/**
244	197	* Sets up the class and creates functions for SQLite databases
245		*
246		* @internal
247	198	*
248	199	* @param fDatabase $database The database being translated for
…	…
250	201	* @return fSQLTranslation
251	202	*/
252		public function __construct($database, $connection)
253		{
254		if (!is_resource($connection) && !is_object($connection)) {
255		throw new fProgrammerException(
256		'The connection specified, %s, is not a valid database connection',
257		$connection
258		);
259		}
260
261		$this->connection = $connection;
262		$this->database = $database;
	203	public function __construct($database)
	204	{
	205	$this->database = $database;
	206	$this->database->inject($this);
263	207
264	208	if ($database->getType() == 'sqlite') {
265	209	$this->createSQLiteFunctions();
266	210	}
	211
	212	$this->schema_info = array();
	213	}
	214
	215
	216	/**
	217	* Clears all of the schema info out of the object and, if set, the fCache object
	218	*
	219	* @return void
	220	*/
	221	public function clearCache()
	222	{
	223	$this->schema_info = array();
	224	if ($this->cache) {
	225	$prefix = $this->makeCachePrefix();
	226	$this->cache->delete($prefix . 'schema_info');
	227	}
267	228	}
268	229
…	…
271	232	* Creates a trigger for SQLite that handles an on delete clause
272	233	*
	234	* @param array &$extra_statements An array of extra SQL statements to be added to the SQL
273	235	* @param string $referencing_table The table that contains the foreign key
274	236	* @param string $referencing_column The column the foriegn key constraint is on
…	…
278	240	* @return string The trigger
279	241	*/
280		private function createSQLiteForeignKeyTriggerOnDelete($referencing_table, $referencing_column, $referenced_table, $referenced_column, $delete_clause)
	242	private function createSQLiteForeignKeyTriggerOnDelete(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $delete_clause)
281	243	{
282	244	switch (strtolower($delete_clause)) {
283	245	case 'no action':
284	246	case 'restrict':
285		$~~sql = "\n~~CREATE TRIGGER fkd_res_" . $referencing_table . "_" . $referencing_column . "
	247	$extra_statements[] = "CREATE TRIGGER fkd_res_" . $referencing_table . "_" . $referencing_column . "
286	248	BEFORE DELETE ON " . $referenced_table . "
287	249	FOR EACH ROW BEGIN
288	250	SELECT RAISE(ROLLBACK, 'delete on table \"" . $referenced_table . "\" can not be executed because it would violate the foreign key constraint on column \"" . $referencing_column . "\" of table \"" . $referencing_table . "\"')
289	251	WHERE (SELECT " . $referencing_column . " FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_table . ") IS NOT NULL;
290		END;";
	252	END";
291	253	break;
292	254
293	255	case 'set null':
294		$~~sql = "\n~~CREATE TRIGGER fkd_nul_" . $referencing_table . "_" . $referencing_column . "
	256	$extra_statements[] = "CREATE TRIGGER fkd_nul_" . $referencing_table . "_" . $referencing_column . "
295	257	BEFORE DELETE ON " . $referenced_table . "
296	258	FOR EACH ROW BEGIN
297	259	UPDATE " . $referencing_table . " SET " . $referencing_column . " = NULL WHERE " . $referencing_column . " = OLD." . $referenced_column . ";
298		END;";
	260	END";
299	261	break;
300	262
301	263	case 'cascade':
302		$~~sql = "\n~~CREATE TRIGGER fkd_cas_" . $referencing_table . "_" . $referencing_column . "
	264	$extra_statements[] = "CREATE TRIGGER fkd_cas_" . $referencing_table . "_" . $referencing_column . "
303	265	BEFORE DELETE ON " . $referenced_table . "
304	266	FOR EACH ROW BEGIN
305	267	DELETE FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_column . ";
306		END;";
	268	END";
307	269	break;
308	270	}
309		~~return $sql;~~
310	271	}
311	272
…	…
314	275	* Creates a trigger for SQLite that handles an on update clause
315	276	*
	277	* @param array &$extra_statements An array of extra SQL statements to be added to the SQL
316	278	* @param string $referencing_table The table that contains the foreign key
317	279	* @param string $referencing_column The column the foriegn key constraint is on
…	…
321	283	* @return string The trigger
322	284	*/
323		private function createSQLiteForeignKeyTriggerOnUpdate($referencing_table, $referencing_column, $referenced_table, $referenced_column, $update_clause)
	285	private function createSQLiteForeignKeyTriggerOnUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $update_clause)
324	286	{
325	287	switch (strtolower($update_clause)) {
326	288	case 'no action':
327	289	case 'restrict':
328		$~~sql~~ = "\nCREATE TRIGGER fku_res_" . $referencing_table . "_" . $referencing_column . "
	290	$extra_statements[] = "\nCREATE TRIGGER fku_res_" . $referencing_table . "_" . $referencing_column . "
329	291	BEFORE UPDATE ON " . $referenced_table . "
330	292	FOR EACH ROW BEGIN
331	293	SELECT RAISE(ROLLBACK, 'update on table \"" . $referenced_table . "\" can not be executed because it would violate the foreign key constraint on column \"" . $referencing_column . "\" of table \"" . $referencing_table . "\"')
332	294	WHERE (SELECT " . $referencing_column . " FROM " . $referencing_table . " WHERE " . $referencing_column . " = OLD." . $referenced_column . ") IS NOT NULL;
333		END;";
	295	END";
334	296	break;
335	297
336	298	case 'set null':
337		$~~sql~~ = "\nCREATE TRIGGER fku_nul_" . $referencing_table . "_" . $referencing_column . "
	299	$extra_statements[] = "\nCREATE TRIGGER fku_nul_" . $referencing_table . "_" . $referencing_column . "
338	300	BEFORE UPDATE ON " . $referenced_table . "
339	301	FOR EACH ROW BEGIN
340	302	UPDATE " . $referencing_table . " SET " . $referencing_column . " = NULL WHERE OLD." . $referenced_column . " <> NEW." . $referenced_column . " AND " . $referencing_column . " = OLD." . $referenced_column . ";
341		END;";
	303	END";
342	304	break;
343	305
344	306	case 'cascade':
345		$~~sql~~ = "\nCREATE TRIGGER fku_cas_" . $referencing_table . "_" . $referencing_column . "
	307	$extra_statements[] = "\nCREATE TRIGGER fku_cas_" . $referencing_table . "_" . $referencing_column . "
346	308	BEFORE UPDATE ON " . $referenced_table . "
347	309	FOR EACH ROW BEGIN
348	310	UPDATE " . $referencing_table . " SET " . $referencing_column . " = NEW." . $referenced_column . " WHERE OLD." . $referenced_column . " <> NEW." . $referenced_column . " AND " . $referencing_column . " = OLD." . $referenced_column . ";
349		END;";
	311	END";
350	312	break;
351	313	}
352		~~return $sql;~~
353	314	}
354	315
…	…
357	318	* Creates a trigger for SQLite that prevents inserting or updating to values the violate a `FOREIGN KEY` constraint
358	319	*
	320	* @param array &$extra_statements An array of extra SQL statements to be added to the SQL
359	321	* @param string $referencing_table The table that contains the foreign key
360	322	* @param string $referencing_column The column the foriegn key constraint is on
…	…
364	326	* @return string The trigger
365	327	*/
366		private function createSQLiteForeignKeyTriggerValidInsertUpdate($referencing_table, $referencing_column, $referenced_table, $referenced_column, $referencing_not_null)
	328	private function createSQLiteForeignKeyTriggerValidInsertUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $referencing_not_null)
367	329	{
368	330	// Verify key on inserts
…	…
376	338	}
377	339	$sql .= " (SELECT " . $referenced_column . " FROM " . $referenced_table . " WHERE " . $referenced_column . " = NEW." . $referencing_column . ") IS NULL;
378		END;";
	340	END";
	341
	342	$extra_statements[] = $sql;
379	343
380	344	// Verify key on updates
381		$sql .= "\nCREATE TRIGGER fku_ver_" . $referencing_table . "_" . $referencing_column . "
	345	$sql = "\nCREATE TRIGGER fku_ver_" . $referencing_table . "_" . $referencing_column . "
382	346	BEFORE UPDATE ON " . $referencing_table . "
383	347	FOR EACH ROW BEGIN
…	…
388	352	}
389	353	$sql .= " (SELECT " . $referenced_column . " FROM " . $referenced_table . " WHERE " . $referenced_column . " = NEW." . $referencing_column . ") IS NULL;
390		END;";
391
392		~~return~~ $sql;
	354	END";
	355
	356	$extra_statements[] = $sql;
393	357	}
394	358
…	…
415	379	$functions[] = array('ln', 'log', 1);
416	380	$functions[] = array('log', array('fSQLTranslation', 'sqliteLogBaseFirst'), 2);
417		$functions[] = array('pi', 'pi', 1);
418		$functions[] = array('power', 'pow', 1);
	381	$functions[] = array('ltrim', 'ltrim', 1);
	382	$functions[] = array('pi', 'pi', 0);
	383	$functions[] = array('power', 'pow', 2);
419	384	$functions[] = array('radians', 'deg2rad', 1);
	385	$functions[] = array('rtrim', 'rtrim', 1);
420	386	$functions[] = array('sign', array('fSQLTranslation', 'sqliteSign'), 1);
421	387	$functions[] = array('sqrt', 'sqrt', 1);
422	388	$functions[] = array('sin', 'sin', 1);
423	389	$functions[] = array('tan', 'tan', 1);
	390	$functions[] = array('trim', 'trim', 1);
	391
	392	if ($this->database->getExtension() == 'sqlite') {
	393	$functions[] = array('current_date', array('fSQLTranslation', 'sqliteDate'), 0);
	394	$functions[] = array('current_time', array('fSQLTranslation', 'sqliteTime'), 0);
	395	$functions[] = array('current_timestamp', array('fSQLTranslation', 'sqliteTimestamp'), 0);
	396	}
424	397
425	398	foreach ($functions as $function) {
426	399	if ($this->database->getExtension() == 'pdo') {
427		$this->~~connection~~->sqliteCreateFunction($function[0], $function[1], $function[2]);
	400	$this->database->getConnection()->sqliteCreateFunction($function[0], $function[1], $function[2]);
428	401	} else {
429		sqlite_create_function($this->connection, $function[0], $function[1], $function[2]);
430		}
431		}
	402	sqlite_create_function($this->database->getConnection(), $function[0], $function[1], $function[2]);
	403	}
	404	}
	405	}
	406
	407
	408	/**
	409	* Sets the schema info to be cached to the fCache object specified
	410	*
	411	* @param fCache $cache The cache to cache to
	412	* @return void
	413	*/
	414	public function enableCaching($cache)
	415	{
	416	$this->cache = $cache;
	417	$this->schema_info = $this->cache->get($this->makeCachePrefix() . 'schema_info', array());
432	418	}
433	419
…	…
436	422	* Sets if debug messages should be shown
437	423	*
438		* @internal
439		*
440	424	* @param boolean $flag If debugging messages should be shown
441	425	* @return void
…	…
455	439	private function fixMSSQLNationalColumns($sql)
456	440	{
457		if (!preg_match_all('#^\s(select.)$\|$\s(select(?:\s(?:[^()\']+\|\'(?:\'\'\|\\\\\'\|\\\\[^\']\|[^\'\\\\]+)\'\|\((?2)$\|)+\s))\s\)\s(?= union)\|\s+union(?:\s+all)?\s+$\s(select(?:\s(?:[^()\']+\|\'(?:\'\'\|\\\\\'\|\\\\[^\']\|[^\'\\\\]+)\'\|\((?3)$\|)+\s))\s*\)#iD', $sql, $matches)) {
	441	if (!preg_match_all('#select((?:(?:(?!\sfrom\s)[^()])+\|$((?:[^()]+\|\((?2)$))\))\s)from((?:(?:(?!\sunion\s\|\swhere\s\|\sgroup by\s\|\slimit\s\|\sorder by\s)[^()])+\|$((?:[^()]+\|\((?4)$))\)))(?=\swhere\s\|\sgroup by\s\|\slimit\s\|\sorder by\s\|\sunion\s\|\)\|$)#i', $sql, $matches, PREG_SET_ORDER)) {
458	442	return $sql;
459	443	}
460	444
461		static $national_columns = NULL;
462		static $national_types = NULL;
463
464		if ($national_columns === NULL) {
	445	if (!isset($this->schema_info['national_columns'])) {
465	446	$result = $this->database->query(
466	447	"SELECT
…	…
474	455	c.data_type = 'ntext' OR
475	456	c.data_type = 'nchar') AND
476		c.table_catalog = '~~flourish~~'
	457	c.table_catalog = '" . $this->database->getDatabase() . "'
477	458	ORDER BY
478	459	lower(c.table_name) ASC,
…	…
481	462
482	463	$national_columns = array();
	464	$national_types = array();
483	465
484	466	foreach ($result as $row) {
…	…
490	472	$national_types[$row['table']][$row['column']] = $row['type'];
491	473	}
492		}
493
494		$selects = array_merge(
495		array_filter($matches[1]),
496		array_filter($matches[2]),
497		array_filter($matches[3])
498		);
	474
	475	$this->schema_info['national_columns'] = $national_columns;
	476	$this->schema_info['national_types'] = $national_types;
	477
	478	if ($this->cache) {
	479	$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
	480	}
	481
	482	} else {
	483	$national_columns = $this->schema_info['national_columns'];
	484	$national_types = $this->schema_info['national_types'];
	485	}
499	486
500	487	$additions = array();
501	488
502		foreach ($selects as $select) {
503		$clauses = self::parseSelectSQL($select);
504		$table_aliases = self::parseTableAliases($clauses['FROM']);
505
506		preg_match_all('#([^,()\']+\|\'(?>\'\'\|\\\\\'\|\\\\[^\']\|[^\'\\\\]+)\'\|$(?:(?1)\|,)$\|)+#i', $clauses['SELECT'], $selections);
	489	foreach ($matches as $select) {
	490	$select_clause = trim($select[1]);
	491	$from_clause = trim($select[3]);
	492
	493	// This recursively fixes sub-selects
	494	if (preg_match('#\bselect\b#', $from_clause)) {
	495	$from_clause = $this->fixMSSQLNationalColumns($from_clause);
	496	}
	497
	498	$table_aliases = self::parseTableAliases($from_clause);
	499
	500	preg_match_all('#([^,()]+\|$(?:(?1)\|,)*$)+#i', $select_clause, $selections);
507	501	$selections = array_map('trim', $selections[0]);
508	502	$to_fix = array();
…	…
601	595	if (is_array($column)) {
602	596	if (isset($column['alias'])) {
603		$as = ' AS ~~__flourish_mssqln~~_' . $column['alias'];
	597	$as = ' AS fmssqln__' . $column['alias'];
604	598	} else {
605		$as = ' AS ~~__flourish_mssqln~~_' . $column['column'];
	599	$as = ' AS fmssqln__' . $column['column'];
606	600	}
607	601	if (isset($column['expression'])) {
…	…
612	606	$column = $column['column'];
613	607	} else {
614		$as = ' AS ~~__flourish_mssqln~~_' . $column;
	608	$as = ' AS fmssqln__' . $column;
615	609	$expression = $alias . '.' . $column;
616	610	}
…	…
618	612	$cast = 'CAST(' . $expression . ' AS IMAGE)';
619	613	} else {
620		$cast = 'CAST(' . $expression . ' AS VARBINARY(~~8000~~))';
	614	$cast = 'CAST(' . $expression . ' AS VARBINARY(MAX))';
621	615	}
622	616	$additions[] = $cast . $as;
…	…
624	618	}
625	619
626		$replace = preg_replace('#\bselect\s+' . preg_quote($~~clauses['SELECT']~~, '#') . '#i', 'SELECT ' . join(', ', array_merge($selections, $additions)), $select);
	620	$replace = preg_replace('#\bselect\s+' . preg_quote($select_clause, '#') . '#i', 'SELECT ' . join(', ', array_merge($selections, $additions)), $select);
627	621	$sql = str_replace($select, $replace, $sql);
628	622	}
…	…
633	627
634	628	/**
	629	* Fixes pulling unicode data out of national data type MSSQL columns
	630	*
	631	* @param string $sql The SQL to fix
	632	* @param array &$strings The strings from the SQL
	633	* @return string The fixed SQL
	634	*/
	635	private function fixOracleEmptyStrings($sql, &$strings)
	636	{
	637	if (preg_match('#^(UPDATE\s+(?:\w+\.)?\w+\s+)(SET((?:(?:(?!\bwhere\b\|\breturning\b)[^()])+\|$((?:[^()]+\|\((?3)$))\))))(.*)$#i', $sql, $set_match)) {
	638	$sql = $set_match[1] . ':set_clause ' . $set_match[5];
	639	$set_clause = $set_match[2];
	640	} else {
	641	$set_clause = FALSE;
	642	}
	643
	644	foreach ($strings as $number => &$string) {
	645	if ($string == "''") {
	646	$sql = preg_replace('#(\s)=(?=\s+:string_' . $number . '\b)#', '\1IS', $sql, 1, $count_equal);
	647	$sql = preg_replace('#(\s)(!=\|<>)(?=\s+:string_' . $number . '\b)#', '\1IS NOT', $sql, 1, $count_not_equal);
	648	if ($count_equal \|\| $count_not_equal) {
	649	$string = 'NULL';
	650	}
	651	}
	652	}
	653
	654	if ($set_clause) {
	655	$sql = preg_replace('#:set_clause\b#', $set_clause, $sql, 1);
	656	}
	657
	658	return $sql;
	659	}
	660
	661
	662	/**
	663	* Creates a unique cache prefix to help prevent cache conflicts
	664	*
	665	* @return void
	666	*/
	667	private function makeCachePrefix()
	668	{
	669	$prefix = 'fSQLTranslation::' . $this->database->getType() . '::';
	670	if ($this->database->getHost()) {
	671	$prefix .= $this->database->getHost() . '::';
	672	}
	673	if ($this->database->getPort()) {
	674	$prefix .= $this->database->getPort() . '::';
	675	}
	676	$prefix .= $this->database->getDatabase() . '::';
	677	if ($this->database->getUsername()) {
	678	$prefix .= $this->database->getUsername() . '::';
	679	}
	680	return $prefix;
	681	}
	682
	683
	684	/**
635	685	* Translates Flourish SQL into the dialect for the current database
636	686	*
637	687	* @internal
	688	*
	689	* @param array $statements The SQL statements to translate
	690	* @param array $strings The strings to interpolate back into the SQL statements
	691	* @return array The translated SQL statements all ready for execution. Statements that have been translated will have string key of the original SQL, all other will have a numeric key.
	692	*/
	693	public function translate($statements, $strings)
	694	{
	695	$output = array();
	696
	697	foreach ($statements as $number => $sql) {
	698
	699	// These fixes don't need to know about strings
	700	$new_sql = $this->translateBasicSyntax($sql);
	701
	702	if ($this->database->getType() == 'mssql' \|\| $this->database->getType() == 'oracle') {
	703	$new_sql = $this->translateLimitOffsetToRowNumber($new_sql);
	704	}
	705
	706	// SQL Server does not like to give unicode results back to PHP without some coersion
	707	if ($this->database->getType() == 'mssql') {
	708	$new_sql = $this->fixMSSQLNationalColumns($new_sql);
	709	}
	710
	711	// Oracle has this nasty habit of silently translating empty strings to null
	712	if ($this->database->getType() == 'oracle') {
	713	$new_sql = $this->fixOracleEmptyStrings($new_sql, $strings[$number]);
	714	}
	715
	716	// Unescape literal semicolons in the queries
	717	$sql = preg_replace('#(?<!\\\\)\\\\;#', ';', $sql);
	718	$new_sql = preg_replace('#(?<!\\\\)\\\\;#', ';', $new_sql);
	719
	720	$extra_statements = array();
	721	$new_sql = $this->translateCreateTableStatements($new_sql, $extra_statements);
	722
	723	// Put the strings back into the SQL
	724	foreach ($strings[$number] as $index => $string) {
	725	$sql = preg_replace('#:string_' . $index . '\b#', $string, $sql, 1);
	726	$new_sql = preg_replace('#:string_' . $index . '\b#', $string, $new_sql, 1);
	727	}
	728
	729	$new_sql = $this->translateDateFunctions($new_sql);
	730
	731	if ($sql != $new_sql \|\| $extra_statements) {
	732	fCore::debug(
	733	self::compose(
	734	"Original SQL:%s",
	735	"\n" . $sql
	736	),
	737	$this->debug
	738	);
	739	$translated_sql = $new_sql;
	740	if ($extra_statements) {
	741	$translated_sql .= '; ' . join('; ', $extra_statements);
	742	}
	743	fCore::debug(
	744	self::compose(
	745	"Translated SQL:%s",
	746	"\n" . $translated_sql
	747	),
	748	$this->debug
	749	);
	750	}
	751
	752	$output = array_merge($output, array($sql => $new_sql), $extra_statements);
	753	}
	754
	755	return $output;
	756	}
	757
	758
	759	/**
	760	* Translates basic syntax differences of the current database
638	761	*
639	762	* @param string $sql The SQL to translate
640	763	* @return string The translated SQL
641	764	*/
642		~~public function translate($sql)~~
643		{
644		~~// Separate the SQL from quoted values~~
645		~~preg_match_all("#(?:'(?:''\|\\\\'\|\\\\[^']\|[^'\\\\]+)*')\|(?:[^']+)#", $sql, $matches);~~
646
647		~~$new_sql = '';~~
648		~~foreach ($matches[0] as $match) {~~
649		~~// This is a quoted string value, don't do anything to it~~
650		~~if ($match[0] == "'") {~~
651		~~$new_sql .= $match;~~
652
653		~~// Raw SQL should be run through the fixes~~
654		~~} else {~~
655		~~$new_sql .= $this->translateBasicSyntax($match);~~
656		}
657		}
658
659		~~// Fix stuff that includes sql and quotes values~~
660		~~$new_sql = $this->translateDateFunctions($new_sql);~~
661		~~$new_sql = $this->translateComplicatedSyntax($new_sql);~~
662		~~$new_sql = $this->translateCreateTableStatements($new_sql);~~
663
664		~~if ($this->database->getType() == 'mssql') {~~
665		~~$new_sql = $this->fixMSSQLNationalColumns($new_sql);~~
666		}
667
668		~~if ($sql != $new_sql) {~~
669		~~fCore::debug(~~
670		~~self::compose(~~
671		~~"Original SQL:%s",~~
672		~~"\n" .$sql~~
673		),
674		~~$this->debug~~
675		);
676		~~fCore::debug(~~
677		~~self::compose(~~
678		~~"Translated SQL:%s",~~
679		~~"\n" . $new_sql~~
680		),
681		~~$this->debug~~
682		);
683		}
684
685		~~return $new_sql;~~
686		}
687
688
689		/**
690		* Translates basic syntax differences of the current database
691		*
692		* @param string $sql The SQL to translate
693		* @return string The translated SQL
694		*/
695	765	private function translateBasicSyntax($sql)
696	766	{
…	…
699	769
700	770	if ($this->database->getType() == 'sqlite' && $this->database->getExtension() == 'pdo') {
701		static $regex_sqlite = array(
702		'#\binteger\s+autoincrement\s+primary\s+key\b#i' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
703		'#\bcurrent_timestamp\b#i' => "datetime(CURRENT_TIMESTAMP, 'localtime')",
704		'#\btrue\b#i' => "'1'",
705		'#\bfalse\b#i' => "'0'"
	771	$regex_sqlite = array(
	772	'#\binteger(?:$\d+$)?\s+autoincrement\s+primary\s+key\b#i' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
	773	'#\bcurrent_timestamp\b#i' => "datetime(CURRENT_TIMESTAMP, 'localtime')",
	774	'#\btrue\b#i' => "'1'",
	775	'#\bfalse\b#i' => "'0'",
	776	'#\brandom#i' => '(ABS(RANDOM())/9223372036854775807)'
706	777	);
707	778	} else {
708		static $regex_sqlite = array(
709		'#\binteger\s+autoincrement\s+primary\s+key\b#i' => 'INTEGER PRIMARY KEY',
710		'#\bcurrent_timestamp\b#i' => "datetime(CURRENT_TIMESTAMP, 'localtime')",
711		'#\btrue\b#i' => "'1'",
712		'#\bfalse\b#i' => "'0'"
	779	$regex_sqlite = array(
	780	'#\binteger(?:$\d+$)?\s+autoincrement\s+primary\s+key\b#i' => 'INTEGER PRIMARY KEY',
	781	'#\bcurrent_timestamp\b#i' => "CURRENT_TIMESTAMP()",
	782	'#\bcurrent_time\b#i' => "CURRENT_TIME()",
	783	'#\bcurrent_date\b#i' => "CURRENT_DATE()",
	784	'#\btrue\b#i' => "'1'",
	785	'#\bfalse\b#i' => "'0'",
	786	'#\brandom#i' => '(ABS(RANDOM())/9223372036854775807)',
	787	// SQLite 2 doesn't support CAST, but is also type-less, so we remove it
	788	'#\bcast$\s((?:[^()\s]+\|\(((?:[^()]+\|\((?2)$))\))+)\s+as\s+(?:[^()\s]+\|$((?:[^()]+\|\((?3)$))\))+\s\)#i' => '\1'
713	789	);
714	790	}
…	…
720	796	if ($this->database->getType() == 'postgresql') {
721	797	static $regex_postgresql = array(
722		'#\blike\b#i' => 'ILIKE',
723		'#\bblob\b#i' => 'bytea',
724		'#\binteger\s+autoincrement\b#i' => 'serial'
	798	'#\b([\w.]+)\s+like\b#i' => 'CAST(\1 AS VARCHAR) ILIKE',
	799	'#\blike\b#i' => 'ILIKE',
	800	'#\bblob\b#i' => 'BYTEA',
	801	'#\binteger(?:$\d+$)?\s+autoincrement\b#i' => 'SERIAL'
725	802	);
726	803
…	…
731	808	if ($this->database->getType() == 'mysql') {
732	809	static $regex_mysql = array(
733		'#\brandom\(#i' => 'rand(',
734		'#\btext\b#i' => 'MEDIUMTEXT',
735		'#\bblob\b#i' => 'LONGBLOB',
736		'#\btimestamp\b#i' => 'DATETIME',
737		'#\binteger\s+autoincrement\b#i' => 'INTEGER AUTO_INCREMENT'
	810	'#\brandom\(#i' => 'rand(',
	811	'#\btext\b#i' => 'MEDIUMTEXT',
	812	'#\bblob\b#i' => 'LONGBLOB',
	813	'#\btimestamp\b#i' => 'DATETIME',
	814	'#\binteger(?:$\d+$)?\s+autoincrement\b#i' => 'INTEGER AUTO_INCREMENT',
	815	'#\bpi#i' => '(pi()+0.0000000000000)'
738	816	);
739	817
…	…
744	822	if ($this->database->getType() == 'mssql') {
745	823	static $regex_mssql = array(
746		'#\bbegin\s*(?!tran)#i' => 'BEGIN TRANSACTION ',
747		'#\brandom\(#i' => 'RAND(',
748		'#\batan2\(#i' => 'ATN2(',
749		'#\bceil\(#i' => 'CEILING(',
750		'#\bln\(#i' => 'LOG(',
751		'#\blength\(#i' => 'LEN(',
752		'#\bsubstr\(#i' => 'SUBSTRING(',
753		'#\bblob\b#i' => 'IMAGE',
754		'#\btimestamp\b#i' => 'DATETIME',
755		'#\btime\b#i' => 'DATETIME',
756		'#\bdate\b#i' => 'DATETIME',
757		'#\binteger\s+autoincrement\b#i' => 'INTEGER IDENTITY(1,1)',
758		'#\bboolean\b#i' => 'BIT',
759		'#\btrue\b#i' => "'1'",
760		'#\bfalse\b#i' => "'0'",
761		'#\\|\\|#i' => '+'
	824	'#\bbegin\s*(?!tran)#i' => 'BEGIN TRANSACTION ',
	825	'#\brandom\(#i' => 'RAND(',
	826	'#\batan2\(#i' => 'ATN2(',
	827	'#\bceil\(#i' => 'CEILING(',
	828	'#\bln\(#i' => 'LOG(',
	829	'#\blength\(#i' => 'LEN(',
	830	'#\bsubstr\(#i' => 'SUBSTRING(',
	831	'#\bblob\b#i' => 'IMAGE',
	832	'#\btimestamp\b#i' => 'DATETIME',
	833	'#\btime\b#i' => 'DATETIME',
	834	'#\bdate\b#i' => 'DATETIME',
	835	'#\binteger(?:$\d+$)?\s+autoincrement\b#i' => 'INTEGER IDENTITY(1,1)',
	836	'#\bboolean\b#i' => 'BIT',
	837	'#\bvarchar\b#i' => 'NVARCHAR',
	838	'#\bchar\b#i' => 'NCHAR',
	839	'#\btext\b#i' => 'NTEXT',
	840	'#\btrue\b#i' => "'1'",
	841	'#\bfalse\b#i' => "'0'",
	842	'#\\|\\|#i' => '+',
	843	// These wrap multiple mssql functions to accomplish another function
	844	'#\blog$\s((?>[^(),]+\|\((?1)(?:,(?1))?$\|)+)\s,\s((?>[^(),]+\|$(?2)(?:,(?2))?$\|)+)\s\)#i' => '(LOG(\2)/LOG(\1))',
	845	'#\btrim$\s((?>[^(),]+\|\((?1)$\|)+)\s\)#i' => 'RTRIM(LTRIM(\1))',
	846	'#\bround$\s((?>[^(),]+\|\((?1)$\|)+)\s\)#i' => 'round(\1, 0)'
762	847	);
763	848
764	849	return preg_replace(array_keys($regex_mssql), array_values($regex_mssql), $sql);
765	850	}
766		}
767
768
769		/**
770		* Translates more complicated inconsistencies
771		*
772		* @param string $sql The SQL to translate
	851
	852	// Oracle fixes
	853	if ($this->database->getType() == 'oracle') {
	854	static $regex_oracle = array(
	855	'#\bbigint\b#i' => 'INTEGER',
	856	'#\bboolean\b#i' => 'NUMBER(1)',
	857	'#\btext\b#i' => 'CLOB',
	858	'#\bvarchar\b#i' => 'VARCHAR2',
	859	'#\btime\b#i' => 'TIMESTAMP',
	860	'#\btrue\b#i' => '1',
	861	'#\bfalse\b#i' => '0',
	862	'#(?:\b\|^)((?>[^()%\s]+\|$((?:[^()]+\|\((?2)$))\))+)\s%\s((?>[^()\s]+\|$((?:[^()]+\|\((?4)$))\))+)(?:\b\|$)#i' => 'MOD(\1, \3)',
	863	'#(?:\b\|^)((?>[^()\s]+\|$((?:[^()]+\|\((?2)$))\))+)\s+LIKE\s+((?>[^()\s]+\|$((?:[^()]+\|\((?4)$))\))+)(?:\b\|$)#i' => 'LOWER(\1) LIKE LOWER(\3)',
	864	'#\bcot$\s((?>[^()\s]+\|\(((?:[^()]+\|\((?2)$))\))+)\s*\)#i' => '(1/TAN(\1))',
	865	'#\bceiling\(#i' => 'CEIL(',
	866	'#\brandom#i' => '(ABS(DBMS_RANDOM.RANDOM)/2147483647)',
	867	'#\bpi#i' => '3.14159265358979',
	868	'#\bdegrees$\s((?>[^()\s]+\|\(((?:[^()]+\|\((?2)$))\))+)\s\)#i' => '(\1 57.295779513083)',
	869	'#\bradians$\s((?>[^()\s]+\|\(((?:[^()]+\|\((?2)$))\))+)\s\)#i' => '(\1 0.017453292519943)',
	870	);
	871
	872	return preg_replace(array_keys($regex_oracle), array_values($regex_oracle), $sql);
	873	}
	874	}
	875
	876
	877	/**
	878	* Translates the structure of `CREATE TABLE` statements to the database specific syntax
	879	*
	880	* @param string $sql The SQL to translate
	881	* @param array &$extra_statements Any extra SQL statements that need to be added
773	882	* @return string The translated SQL
774	883	*/
775		private function translateComplicatedSyntax($sql)
776		{
777		if ($this->database->getType() == 'mssql') {
778
779		$sql = $this->translateLimitOffsetToRowNumber($sql);
780
781		static $regex_mssql = array(
782		// These wrap multiple mssql functions to accomplish another function
783		'#\blog$\s((?>[^()\',]+\|\'[^\']\'\|\((?1)(?:,(?1))?$\|)+)\s,\s((?>[^()\',]+\|\'[^\']\'\|$(?2)(?:,(?2))?$\|)+)\s\)#i' => '(LOG(\1)/LOG(\2))',
784		'#\btrim$\s((?>[^()\',]+\|\'(?:\'\'\|\\\\\'\|\\\\[^\']\|[^\'\\\\]+)\'\|\((?1)$\|)+)\s*\)#i' => 'RTRIM(LTRIM(\1))'
785		);
786
787		$sql = preg_replace(array_keys($regex_mssql), array_values($regex_mssql), $sql);
788
789		if (preg_match('#select(\s(?:[^()\']+\|\'(?>\'\'\|\\\\\'\|\\\\[^\']\|[^\'\\\\]+)\'\|$(?1)$\|)+\s*)\s+limit\s+(\d+)#i', $sql, $match)) {
790		$sql = str_replace($match[0], 'SELECT TOP ' . $match[2] . $match[1], $sql);
791		}
792		}
793
794		return $sql;
795		}
796
797
798		/**
799		* Translates the structure of `CREATE TABLE` statements to the database specific syntax
800		*
801		* @param string $sql The SQL to translate
802		* @return string The translated SQL
803		*/
804		private function translateCreateTableStatements($sql)
805		{
	884	private function translateCreateTableStatements($sql, &$extra_statements)
	885	{
	886	if (stripos($sql, 'CREATE TABLE') === FALSE) {
	887	return $sql;
	888	}
	889
806	890	// Make sure MySQL uses InnoDB tables
807		if ($this->database->getType() == 'mysql' ~~&& stripos($sql, 'CREATE TABLE') !== FALSE~~) {
808		preg_match_all('#(?<=,\|$)\s(\w+)\s+(?:[a-z]+)(?:\(\d+$)?(?:(\s+NOT\s+NULL)\|(\s+DEFAULT\s+(?:[^, \']\|\'(?:\'\'\|[^\']+)\'))\|(\s+UNIQUE)\|(\s+PRIMARY\s+KEY)\|(\s+CHECK\s$\w+\s+IN\s+(\(\s(?:(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')\s,\s)\s(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')$)\)))(\s+REFERENCES\s+\w+\s$\s\w+\s$\s(?:\s+(?:ON\s+DELETE\|ON\s+UPDATE)\s+(?:CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL\|SET\s+DEFAULT))(?:\s+(?:DEFERRABLE\|NOT\s+DEFERRABLE))?)?\s(?:,\|\s*(?=\)))#mi', $sql, $matches, PREG_SET_ORDER);
	891	if ($this->database->getType() == 'mysql') {
	892	preg_match_all('#(?<=,\|$)\s(\w+)\s+(?:[a-z]+)(?:\(\d+$)?(?:\s+unsigned\|\s+zerofill\|\s+character\s+set\s+[^ ]+\|\s+collate\s+[^ ]+\|\s+NULL\|\s+NOT\s+NULL\|(\s+DEFAULT\s+(?:[^, \']\|\'(?:\'\'\|[^\']+)\'))\|\s+UNIQUE\|\s+PRIMARY\s+KEY\|(\s+CHECK\s$\w+\s+IN\s+(\(\s(?:(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')\s,\s)\s(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')$)\)))(\s+REFERENCES\s+\w+\s$\s\w+\s$\s(?:\s+(?:ON\s+DELETE\|ON\s+UPDATE)\s+(?:CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL\|SET\s+DEFAULT))(?:\s+(?:DEFERRABLE\|NOT\s+DEFERRABLE))?)?\s(,\|\s*(?=\)))#mi', $sql, $matches, PREG_SET_ORDER);
809	893
810	894	foreach ($matches as $match) {
811		if (!empty($match[6])) {
812		$sql = str_replace($match[0], "\n " . $match[1] . ' enum' . $match[7] . $match[2] . $match[3] . $match[4] . $match[5] . $match[8] . ', ', $sql);
	895	// MySQL has the enum data type, so we switch check constraints to that
	896	if (!empty($match[3])) {
	897	$replacement = "\n " . $match[1] . ' enum' . $match[4] . $match[2] . $match[5] . $match[6];
	898	$sql = str_replace($match[0], $replacement, $sql);
	899	// This allows us to do a str_replace below for converting foreign key syntax
	900	$match[0] = $replacement;
	901	}
	902
	903	// Even InnoDB table types don't allow specify foreign key constraints in the column
	904	// definition, so we move it to its own definition on the next line
	905	if (!empty($match[5])) {
	906	$updated_match_0 = str_replace($match[5], ",\nFOREIGN KEY (" . $match[1] . ') ' . $match[5], $match[0]);
	907	$sql = str_replace($match[0], $updated_match_0, $sql);
813	908	}
814	909	}
…	…
823	918
824	919	preg_match_all('#(?:(?<=,\|$)\s(\w+)\s+(?:[a-z]+)(?:\((?:\d+)$)?(?:(\s+NOT\s+NULL)\|(?:\s+DEFAULT\s+(?:[^, \']\|\'(?:\'\'\|[^\']+)\'))\|(?:\s+UNIQUE)\|(?:\s+PRIMARY\s+KEY(?:\s+AUTOINCREMENT)?)\|(?:\s+CHECK\s$\w+\s+IN\s+\(\s(?:(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')\s,\s)\s(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')$\)))(\s+REFERENCES\s+(\w+)\s$\s(\w+)\s$\s(?:\s+(?:ON\s+DELETE\s+(CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL)))?(?:\s+(?:ON\s+UPDATE\s+(CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL)))?)?\s(?:,\|\s(?=\)))\|(?<=,\|$)\sFOREIGN\s+KEY\s(?:(\w+)\|\((\w+)$)\s+REFERENCES\s+(\w+)\s$\s(\w+)\s$\s(?:\s+(?:ON\s+DELETE\s+(CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL)))?(?:\s+(?:ON\s+UPDATE\s+(CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL)))?\s(?:,\|\s(?=\))))#mi', $sql, $matches, PREG_SET_ORDER);
825
826		~~// Make sure we have a semicolon so we can add triggers~~
827		~~$sql = trim($sql);~~
828		~~if (substr($sql, -1) != ';') {~~
829		~~$sql .= ';';~~
830		}
831	920
832	921	$not_null_columns = array();
…	…
847	936	// Handle column level foreign key inserts/updates
848	937	if ($match[1]) {
849		$~~sql .= $this->createSQLiteForeignKeyTriggerValidInsertUpdate(~~$referencing_table, $match[1], $match[4], $match[5], in_array($match[1], $not_null_columns));
	938	$this->createSQLiteForeignKeyTriggerValidInsertUpdate($extra_statements, $referencing_table, $match[1], $match[4], $match[5], in_array($match[1], $not_null_columns));
850	939
851	940	// Handle table level foreign key inserts/update
852	941	} elseif ($match[9]) {
853		$~~sql .= $this->createSQLiteForeignKeyTriggerValidInsertUpdate(~~$referencing_table, $match[9], $match[10], $match[11], in_array($match[9], $not_null_columns));
	942	$this->createSQLiteForeignKeyTriggerValidInsertUpdate($extra_statements, $referencing_table, $match[9], $match[10], $match[11], in_array($match[9], $not_null_columns));
854	943	}
855	944
…	…
862	951	if (!empty($match[3])) {
863	952	if ($match[6]) {
864		$~~sql .= $this->createSQLiteForeignKeyTriggerOnDelete(~~$referencing_table, $match[1], $match[4], $match[5], $match[6]);
	953	$this->createSQLiteForeignKeyTriggerOnDelete($extra_statements, $referencing_table, $match[1], $match[4], $match[5], $match[6]);
865	954	}
866		if (~~$match[7]~~) {
867		$~~sql .= $this->createSQLiteForeignKeyTriggerOnUpdate(~~$referencing_table, $match[1], $match[4], $match[5], $match[7]);
	955	if (!empty($match[7])) {
	956	$this->createSQLiteForeignKeyTriggerOnUpdate($extra_statements, $referencing_table, $match[1], $match[4], $match[5], $match[7]);
868	957	}
869	958	continue;
…	…
872	961	// Handle table level foreign key delete/update clauses
873	962	if ($match[12]) {
874		$~~sql .= $this->createSQLiteForeignKeyTriggerOnDelete(~~$referencing_table, $match[9], $match[10], $match[11], $match[12]);
	963	$this->createSQLiteForeignKeyTriggerOnDelete($extra_statements, $referencing_table, $match[9], $match[10], $match[11], $match[12]);
875	964	}
876	965	if ($match[13]) {
877		$sql .= $this->createSQLiteForeignKeyTriggerOnUpdate($referencing_table, $match[9], $match[10], $match[11], $match[13]);
878		}
879		}
880		}
	966	$this->createSQLiteForeignKeyTriggerOnUpdate($extra_statements, $referencing_table, $match[9], $match[10], $match[11], $match[13]);
	967	}
	968	}
	969
	970
	971	// Create sequences and triggers for Oracle
	972	} elseif ($this->database->getType() == 'oracle' && preg_match('#CREATE\s+TABLE\s+(\w+)#i', $sql, $table_matches) !== FALSE && stripos($sql, 'autoincrement') !== FALSE) {
	973
	974	if (preg_match('#(?<=,\|$)\s(\w+)\s+(?:[a-z]+)(?:\((?:\d+)$)?.?\bAUTOINCREMENT\b[^,\)](?:,\|\s(?=\)))#mi', $sql, $matches)) {
	975	$table_name = $table_matches[1];
	976	$column = $matches[1];
	977
	978	$table_column = substr($table_name . '_' . $column, 0, 26);
	979
	980	$sequence_name = $table_column . '_seq';
	981	$trigger_name = $table_column . '_trg';
	982
	983	$sequence = 'CREATE SEQUENCE ' . $sequence_name;
	984
	985	$trigger = 'CREATE OR REPLACE TRIGGER '. $trigger_name . "\n";
	986	$trigger .= "BEFORE INSERT ON " . $table_name . "\n";
	987	$trigger .= "FOR EACH ROW\n";
	988	$trigger .= "BEGIN\n";
	989	$trigger .= " IF :new." . $column . " IS NULL THEN\n";
	990	$trigger .= " SELECT " . $sequence_name . ".nextval INTO :new." . $column . " FROM dual;\n";
	991	$trigger .= " END IF;\n";
	992	$trigger .= "END;";
	993
	994	$extra_statements[] = $sequence;
	995	$extra_statements[] = $trigger;
	996
	997	$sql = preg_replace('#\s+autoincrement\b#i', '', $sql);
	998	}
	999
	1000	}
	1001
881	1002
882	1003	return $sql;
…	…
892	1013	private function translateDateFunctions($sql)
893	1014	{
894		// fix diff_seconds()
895		preg_match_all("#diff_seconds\$((?>(?:[^()',]+\|'[^']+')\|\\((?1)(?:,(?1))?\$\|\$\$)+)\\s,\\s((?>(?:[^()',]+\|'[^']+')\|\$(?2)(?:,(?2))?\$\|\$\$)+)\\)#ims", $sql, $diff_matches, PREG_SET_ORDER);
	1015	// Handle diff_seconds()
	1016	// diff_seconds() accepts two parameters, the two dates to get the difference between
	1017	// Regex matches:
	1018	// 1 - The first date
	1019	// 2 - The second date
	1020	preg_match_all("#diff_seconds$((?>(?:[^()',]+\|'[^']+')\|\((?1)(?:,(?1))?$\|)+)\s,\s((?>(?:[^()',]+\|'[^']+')\|$(?2)(?:,(?2))?$\|)+)\)#ims", $sql, $diff_matches, PREG_SET_ORDER);
896	1021	foreach ($diff_matches as $match) {
897	1022
…	…
904	1029	$sql = str_replace($match[0], "extract(EPOCH FROM age(" . $match[2] . ", " . $match[1] . "))", $sql);
905	1030
	1031	// Oracle
	1032	} elseif ($this->database->getType() == 'oracle') {
	1033	if (substr($match[1], 0, 1) == "'") {
	1034	$match[1] = 'CAST(' . $match[1] . " AS TIMESTAMP)";
	1035	}
	1036	if (substr($match[2], 0, 1) == "'") {
	1037	$match[2] = 'CAST(' . $match[2] . " AS TIMESTAMP)";
	1038	}
	1039	$sql = str_replace($match[0], "((TO_NUMBER(TO_CHAR(" . $match[2] . ", 'J')) - TO_NUMBER(TO_CHAR(" . $match[1] . ", 'J'))) * 86400) + (TO_NUMBER(TO_CHAR(" . $match[2] . ", 'SSSSS')) - TO_NUMBER(TO_CHAR(" . $match[1] . ", 'SSSSS')))", $sql);
	1040
906	1041	// MySQL
907	1042	} elseif ($this->database->getType() == 'mysql') {
…	…
914	1049	}
915	1050
916		// fix add_interval()
917		preg_match_all("#add_interval\$((?>(?:[^()',]+\|'[^']+')\|\\((?1)(?:,(?1))?\$\|\$\$)+)\\s,\\s'([^']+)'\\s*\\)#i", $sql, $add_matches, PREG_SET_ORDER);
	1051	// Handle add_interval()
	1052	// add_interval() accepts two parameters, the date to modify and the interval to add
	1053	// Regex matches:
	1054	// 1 - The first parameter
	1055	// 2 - The second parameter
	1056	preg_match_all("#add_interval$((?>(?:[^()',]+\|'[^']+')\|\((?1)(?:,(?1))?$\|)+)\s,\s'([^']+)'\s*\)#i", $sql, $add_matches, PREG_SET_ORDER);
918	1057	foreach ($add_matches as $match) {
919	1058
920	1059	// SQLite
921	1060	if ($this->database->getType() == 'sqlite') {
	1061
	1062	// Regex matches:
	1063	// 0 - The adjustment in the form: +/- number units
922	1064	preg_match_all("#(?:\\+\|\\-)\\d+\\s+(?:year\|month\|day\|hour\|minute\|second)(?:s)?#i", $match[2], $individual_matches);
923	1065	$strings = "'" . join("', '", $individual_matches[0]) . "'";
…	…
926	1068	// PostgreSQL
927	1069	} elseif ($this->database->getType() == 'postgresql') {
	1070	if (substr($match[1], 0, 1) == "'") {
	1071	if (preg_match('#^\'\d{4}-\d{2}-\d{2}\'$#', $match[1])) {
	1072	$match[1] = 'DATE ' . $match[1];
	1073	} elseif (preg_match('#^\'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\'$#', $match[1])) {
	1074	$match[1] = 'TIMESTAMP ' . $match[1];
	1075	} elseif (preg_match('#^\'\d{2}:\d{2}:\d{2}\'$#', $match[1])) {
	1076	$match[1] = 'TIME ' . $match[1];
	1077	}
	1078	}
928	1079	$sql = str_replace($match[0], "(" . $match[1] . " + INTERVAL '" . $match[2] . "')", $sql);
929	1080
930		// MySQL
931		} elseif ($this->database->getType() == 'mysql') {
	1081	// MySQL and Oracle
	1082	} elseif ($this->database->getType() == 'mysql' \|\| $this->database->getType() == 'oracle') {
	1083
	1084	// Regex matches:
	1085	// 1 - The sign, +/-
	1086	// 2 - The number
	1087	// 3 - The units, hour, minute, etc
932	1088	preg_match_all("#(\\+\|\\-)(\\d+)\\s+(year\|month\|day\|hour\|minute\|second)(?:s)?#i", $match[2], $individual_matches, PREG_SET_ORDER);
933	1089	$intervals_string = '';
934	1090	foreach ($individual_matches as $individual_match) {
935		$intervals_string .= ' ' . $individual_match[1] . ' INTERVAL ' . $individual_match[2] . ' ' . strtoupper($individual_match[3]);
936		}
	1091	$intervals_string .= ' ' . $individual_match[1] . " INTERVAL '" . $individual_match[2] . "' " . strtoupper($individual_match[3]);
	1092	}
	1093
	1094	if ($this->database->getType() == 'oracle' && substr($match[1], 0, 1) == "'") {
	1095	if (preg_match('#^\'\d{4}-\d{2}-\d{2}\'$#', $match[1])) {
	1096	$match[1] = 'CAST(' . $match[1] . ' AS DATE)';
	1097	} elseif (preg_match('#^\'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\'$#', $match[1])) {
	1098	$match[1] = 'CAST(' . $match[1] . ' AS TIMESTAMP)';
	1099	}
	1100	}
	1101
937	1102	$sql = str_replace($match[0], "(" . $match[1] . $intervals_string . ")", $sql);
938	1103
939	1104	// MSSQL
940	1105	} elseif ($this->database->getType() == 'mssql') {
	1106
	1107	// Regex matches:
	1108	// 1 - The sign, +/-
	1109	// 2 - The number
	1110	// 3 - The units, hour, minute, etc
941	1111	preg_match_all("#(\\+\|\\-)(\\d+)\\s+(year\|month\|day\|hour\|minute\|second)(?:s)?#i", $match[2], $individual_matches, PREG_SET_ORDER);
942	1112	$date_add_string = '';
…	…
947	1117	}
948	1118	$sql = str_replace($match[0], $date_add_string . $match[1] . str_pad('', $stack, ')'), $sql);
	1119
949	1120	}
950	1121	}
…	…
962	1133	private function translateLimitOffsetToRowNumber($sql)
963	1134	{
964		preg_match_all('#((select(?:\s(?:[^()\']+\|\'(?:\'\'\|\\\\\'\|\\\\[^\']\|[^\'\\\\]+)\'\|$(?1)$\|)+\s*))\s+limit\s+(\d+)\s+offset\s+(\d+))#i', $sql, $matches, PREG_SET_ORDER);
	1135	// Regex details:
	1136	// 1 - The SELECT clause
	1137	// 2 - () recursion handler
	1138	// 3 - FROM clause
	1139	// 4 - () recursion handler
	1140	// 5 - ORDER BY clause
	1141	// 6 - () recursion handler
	1142	// 7 - LIMIT number
	1143	// 8 - OFFSET number
	1144	preg_match_all('#select((?:(?:(?!\sfrom\s)[^()])+\|$((?:[^()]+\|\((?2)$))\))\s)(from(?:(?:(?!\slimit\s\|\sorder by\s)[^()])+\|$((?:[^()]+\|\((?4)$))\))\s)(order by(?:(?:(?!\slimit\s)[^()])+\|$((?:[^()]+\|\((?6)$))\))\s)?limit\s+(\d+)(?:\s+offset\s+(\d+))?#i', $sql, $matches, PREG_SET_ORDER);
965	1145
966	1146	foreach ($matches as $match) {
967		$clauses = self::parseSelectSQL($match[1]);
968
969		if ($clauses['ORDER BY'] == NULL) {
970		$clauses['ORDER BY'] = '1 ASC';
971		}
972
973		$replacement = '';
974		foreach ($clauses as $key => $value) {
975		if (empty($value)) {
976		continue;
977		}
978
979		if ($key == 'SELECT') {
980		$replacement .= 'SELECT ' . $value . ', ROW_NUMBER() OVER (';
981		$replacement .= 'ORDER BY ' . $clauses['ORDER BY'];
982		$replacement .= ') AS __flourish_limit_offset_row_num ';
983		} elseif ($key == 'LIMIT' \|\| $key == 'ORDER BY') {
984		// Skip this clause
	1147	if ($this->database->getType() == 'mssql') {
	1148
	1149	// This means we have an offset clause
	1150	if (!empty($match[8])) {
	1151
	1152	if ($match[5] === '') {
	1153	$match[5] = "ORDER BY rand(1) ASC";
	1154	}
	1155
	1156	$select = $match[1] . ', ROW_NUMBER() OVER (';
	1157	$select .= $match[5];
	1158	$select .= ') AS flourish__row__num ';
	1159	$select .= $match[3];
	1160
	1161	$replacement = 'SELECT * FROM (SELECT ' . trim($match[1]) . ', ROW_NUMBER() OVER (' . $match[5] . ') AS flourish__row__num ' . $match[3] . ') AS original_query WHERE flourish__row__num > ' . $match[8] . ' AND flourish__row__num <= ' . ($match[7] + $match[8]) . ' ORDER BY flourish__row__num';
	1162
	1163	// Otherwise we just have a limit
985	1164	} else {
986		$replacement .= $key . ' ' . $value . ' ';
987		}
988		}
989
990		$replacement = 'SELECT * FROM (' . trim($replacement) . ') AS original_query WHERE __flourish_limit_offset_row_num > ' . $match[4] . ' AND __flourish_limit_offset_row_num <= ' . ($match[3] + $match[4]) . ' ORDER BY __flourish_limit_offset_row_num';
991
992		$sql = str_replace($match[1], $replacement, $sql);
	1165	$replacement = 'SELECT TOP ' . $match[7] . ' ' . trim($match[1] . $match[3] . $match[5]);
	1166	}
	1167
	1168	// While Oracle has the row_number() construct, the rownum pseudo-column is better
	1169	} elseif ($this->database->getType() == 'oracle') {
	1170
	1171	// This means we have an offset clause
	1172	if (!empty($match[8])) {
	1173
	1174	$replacement = 'SELECT * FROM (SELECT flourish__sq.*, rownum flourish__row__num FROM (SELECT' . $match[1] . $match[3] . $match[5] . ') flourish__sq WHERE rownum <= ' . ($match[7] + $match[8]) . ') WHERE flourish__row__num > ' . $match[8];
	1175
	1176	// Otherwise we just have a limit
	1177	} else {
	1178	$replacement = 'SELECT * FROM (SELECT' . $match[1] . $match[3] . $match[5] . ') WHERE rownum <= ' . $match[7];
	1179	}
	1180
	1181	}
	1182
	1183	$sql = str_replace($match[0], $replacement, $sql);
993	1184	}
994	1185

fSchema.php

r547	r562	Hide Line Numbers
10	10	* @link http://flourishlib.com/fSchema
11	11	*
12		* @version 1.0.0b15
	12	* @version 1.0.0b16
	13	* @changes 1.0.0b16 Backwards Compatibility Break - ::setCacheFile() changed to ::enableCaching() and now requires an fCache object, ::flushInfo() renamed to ::clearCache(), added Oracle support [wb, 2009-05-04]
13	14	* @changes 1.0.0b15 Added support for the three different types of identifier quoting in SQLite [wb, 2009-03-28]
14	15	* @changes 1.0.0b14 Added support for MySQL column definitions containing the COLLATE keyword [wb, 2009-03-28]
…	…
30	31	{
31	32	/**
32		* The ~~file to cache the info~~ to
33		*
34		* @var ~~string~~
35		*/
36		private $cache~~_file~~ = NULL;
	33	* The place to cache to
	34	*
	35	* @var fCache
	36	*/
	37	private $cache = NULL;
37	38
38	39	/**
…	…
65	66
66	67	/**
67		* If the info has changed (and should be written to cache)
68		*
69		* @var boolean
70		*/
71		~~private $info_changed = FALSE;~~
72
73		/**
74	68	* The cached key info
75	69	*
…	…
105	99	*/
106	100	private $relationships = array();
107
108		/**
109		* The state of the info
110		*
111		* @var string
112		*/
113		~~private $state = 'current';~~
114	101
115	102	/**
…	…
130	117	{
131	118	$this->database = $database;
132		}
133
134
135		/**
136		* Stores the info in the cache file if set
137		*
138		* @return void
139		*/
140		~~public function __destruct()~~
141		{
142		~~if ($this->cache_file && $this->info_changed) {~~
143		~~$contents = serialize(array('column_info' => $this->column_info,~~
144		~~'keys' => $this->keys));~~
145		~~file_put_contents($this->cache_file, $contents);~~
146		}
147	119	}
148	120
…	…
179	151
180	152	/**
	153	* Clears all of the schema info out of the object and, if set, the fCache object
	154	*
	155	* @internal
	156	*
	157	* @return void
	158	*/
	159	public function clearCache()
	160	{
	161	$this->column_info = array();
	162	$this->databases = array();
	163	$this->keys = array();
	164	$this->merged_column_info = array();
	165	$this->merged_keys = array();
	166	$this->relationships = array();
	167	$this->tables = array();
	168	if ($this->cache) {
	169	$prefix = $this->makeCachePrefix();
	170	$this->cache->delete($prefix . 'column_info');
	171	$this->cache->delete($prefix . 'databases');
	172	$this->cache->delete($prefix . 'keys');
	173	$this->cache->delete($prefix . 'tables');
	174	}
	175	}
	176
	177
	178	/**
	179	* Sets the schema to be cached to the fCache object specified
	180	*
	181	* @param fCache $cache The cache to cache to
	182	* @return void
	183	*/
	184	public function enableCaching($cache)
	185	{
	186	$this->cache = $cache;
	187
	188	$prefix = $this->makeCachePrefix();
	189	$this->column_info = $this->cache->get($prefix . 'column_info', array());
	190	$this->databases = $this->cache->get($prefix . 'databases', NULL);
	191	$this->keys = $this->cache->get($prefix . 'keys', array());
	192	$this->tables = $this->cache->get($prefix . 'tables', NULL);
	193	}
	194
	195
	196	/**
181	197	* Gets the column info from the database for later access
182	198	*
…	…
186	202	private function fetchColumnInfo($table)
187	203	{
	204	if (isset($this->column_info[$table])) {
	205	return;
	206	}
	207
188	208	switch ($this->database->getType()) {
189	209	case 'mssql':
…	…
195	215	break;
196	216
	217	case 'oracle':
	218	$column_info = $this->fetchOracleColumnInfo($table);
	219	break;
	220
197	221	case 'postgresql':
198	222	$column_info = $this->fetchPostgreSQLColumnInfo($table);
…	…
209	233
210	234	$this->column_info[$table] = $column_info;
211		$this->info_changed = TRUE;
	235	if ($this->cache) {
	236	$this->cache->set($this->makeCachePrefix() . 'column_info', $this->column_info);
	237	}
212	238	}
213	239
…	…
220	246	private function fetchKeys()
221	247	{
	248	if ($this->keys) {
	249	return;
	250	}
	251
222	252	switch ($this->database->getType()) {
223	253	case 'mssql':
…	…
229	259	break;
230	260
	261	case 'oracle':
	262	$keys = $this->fetchOracleKeys();
	263	break;
	264
231	265	case 'postgresql':
232	266	$keys = $this->fetchPostgreSQLKeys();
…	…
239	273
240	274	$this->keys = $keys;
241		$this->info_changed = TRUE;
	275	if ($this->cache) {
	276	$this->cache->set($this->makeCachePrefix() . 'keys', $this->keys);
	277	}
242	278	}
243	279
…	…
277	313	'datetime' => 'timestamp',
278	314	'smalldatetime' => 'timestamp',
	315	'datetime2' => 'timestamp',
	316	'date' => 'date',
	317	'time' => 'time',
279	318	'varchar' => 'varchar',
280	319	'nvarchar' => 'varchar',
…	…
298	337	c.column_name AS 'column',
299	338	c.data_type AS 'type',
300		c.is_nullable AS n~~ot_null~~,
	339	c.is_nullable AS nullable,
301	340	c.column_default AS 'default',
302	341	c.character_maximum_length AS max_length,
303	342	c.numeric_scale AS decimal_places,
304		CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND
305		OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0
306		THEN '1'
307		ELSE '0'
	343	CASE
	344	WHEN
	345	COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND
	346	OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0
	347	THEN '1'
	348	ELSE '0'
308	349	END AS auto_increment,
309		cc.check_clause AS 'constraint'
	350	cc.check_clause AS 'constraint'
310	351	FROM
311	352	INFORMATION_SCHEMA.COLUMNS AS c LEFT JOIN
…	…
349	390	// If the column has a constraint, look for valid values
350	391	if (in_array($info['type'], array('char', 'varchar')) && !empty($row['constraint'])) {
351		if (preg_match('#^$((?:(?: OR )?\[[^\]]+\]=\'(?:\'\'\|[^\']+)+\')+)$$#D', $row['constraint'], $matches)) {
	392	if (preg_match('#^$((?:(?: OR )?\[[^\]]+\]\s=\s\'(?:\'\'\|[^\']+)+\')+)$$#D', $row['constraint'], $matches)) {
352	393	$valid_values = explode(' OR ', $matches[1]);
353	394	foreach ($valid_values as $key => $value) {
354		$valid_values[$key] = substr($value, 4 + strlen($row['column']), -1);
	395	$value = preg_replace('#^\s\[' . preg_quote($row['column'], '#') . '\]\s=\s\'(.)\'\s*$#', '\1', $value);
	396	$valid_values[$key] = str_replace("''", "'", $value);
355	397	}
356		$info['valid_values'] = $valid_values;
	398	// SQL Server turns CHECK constraint values into a reversed list, so we fix it here
	399	$info['valid_values'] = array_reverse($valid_values);
357	400	}
358	401	}
…	…
369	412	} elseif (in_array($info['type'], array('char', 'varchar', 'text', 'timestamp')) ) {
370	413	$info['default'] = substr($row['default'], 2, -2);
371		} elseif (in_array($info['type'], array('integer', 'float', 'boolean')) ) {
	414	} elseif ($info['type'] == 'boolean') {
	415	$info['default'] = (boolean) substr($row['default'], 2, -2);
	416	} elseif (in_array($info['type'], array('integer', 'float')) ) {
372	417	$info['default'] = str_replace(array('(', ')'), '', $row['default']);
373	418	} else {
…	…
377	422
378	423	// Handle not null
379		$info['not_null'] = ($row['n~~ot_null~~'] == 'NO') ? TRUE : FALSE;
	424	$info['not_null'] = ($row['nullable'] == 'NO') ? TRUE : FALSE;
380	425
381	426	$column_info[$row['column']] = $info;
…	…
446	491	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.constraint_name = rc.unique_constraint_name
447	492	WHERE
448		c.constraint_catalog = '" . $this->database->getDatabase() . "'
	493	c.constraint_catalog = '" . $this->database->getDatabase() . "' AND
	494	c.table_name != 'sysdiagrams'
449	495	ORDER BY
450	496	LOWER(c.table_name),
451	497	c.constraint_type,
452	498	LOWER(kcu.constraint_name),
	499	kcu.ordinal_position,
453	500	LOWER(kcu.column_name)";
454	501
…	…
464	511	if ($last_name) {
465	512	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
	513	if (!isset($keys[$last_table][$last_type])) {
	514	$keys[$last_table][$last_type] = array();
	515	}
466	516	$keys[$last_table][$last_type][] = $temp;
467	517	} else {
…	…
500	550	if (isset($temp)) {
501	551	if ($last_type == 'foreign') {
	552	if (!isset($keys[$last_table][$last_type])) {
	553	$keys[$last_table][$last_type] = array();
	554	}
502	555	$keys[$last_table][$last_type][] = $temp;
503	556	} else {
…	…
535	588	{
536	589	$data_type_mapping = array(
537		~~'tinyint(1)' => 'boolean',~~
538	590	'tinyint' => 'integer',
539	591	'smallint' => 'integer',
…	…
582	634	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
583	635	if (stripos($match[2], $data_type) === 0) {
	636	if ($match[2] == 'tinyint' && $match[3] == 1) {
	637	$mapped_data_type = 'boolean';
	638	}
	639
584	640	$info['type'] = $mapped_data_type;
585	641	break;
…	…
714	770	}
715	771	$keys[$table]['foreign'][] = $temp;
	772	}
	773	}
	774
	775	return $keys;
	776	}
	777
	778
	779	/**
	780	* Gets the column info from an Oracle database
	781	*
	782	* The returned array is in the format:
	783	*
	784	* {{{
	785	* array(
	786	* (string) {column name} => array(
	787	* 'type' => (string) {data type},
	788	* 'not_null' => (boolean) {if value can't be null},
	789	* 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
	790	* 'valid_values' => (array) {the valid values for a char/varchar field},
	791	* 'max_length' => (integer) {the maximum length in a char/varchar field},
	792	* 'decimal_places' => (integer) {the number of decimal places for a decimal field},
	793	* 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
	794	* ), ...
	795	* )
	796	* }}}
	797	*
	798	* @param string $table The table to fetch the column info for
	799	* @return array The column info for the table specified - see method description for details
	800	*/
	801	private function fetchOracleColumnInfo($table)
	802	{
	803	$table = strtoupper($table);
	804
	805	$column_info = array();
	806
	807	$data_type_mapping = array(
	808	'boolean' => 'boolean',
	809	'integer' => 'integer',
	810	'timestamp' => 'timestamp',
	811	'date' => 'date',
	812	'varchar2' => 'varchar',
	813	'nvarchar2' => 'varchar',
	814	'char' => 'char',
	815	'nchar' => 'char',
	816	'float' => 'float',
	817	'binary_float' => 'float',
	818	'binary_double' => 'float',
	819	'blob' => 'blob',
	820	'bfile' => 'varchar',
	821	'clob' => 'text',
	822	'nclob' => 'text'
	823	);
	824
	825	$sql = "SELECT
	826	LOWER(UTC.COLUMN_NAME) COLUMN_NAME,
	827	CASE
	828	WHEN
	829	UTC.DATA_TYPE = 'NUMBER' AND
	830	UTC.DATA_PRECISION IS NULL AND
	831	UTC.DATA_SCALE = 0
	832	THEN
	833	'integer'
	834	WHEN
	835	UTC.DATA_TYPE = 'NUMBER' AND
	836	UTC.DATA_PRECISION = 1 AND
	837	UTC.DATA_SCALE = 0
	838	THEN
	839	'boolean'
	840	WHEN
	841	UTC.DATA_TYPE = 'NUMBER' AND
	842	UTC.DATA_PRECISION IS NOT NULL AND
	843	UTC.DATA_SCALE != 0 AND
	844	UTC.DATA_SCALE IS NOT NULL
	845	THEN
	846	'float'
	847	ELSE
	848	LOWER(UTC.DATA_TYPE)
	849	END DATA_TYPE,
	850	CASE
	851	WHEN
	852	UTC.CHAR_LENGTH <> 0
	853	THEN
	854	UTC.CHAR_LENGTH
	855	WHEN
	856	UTC.DATA_TYPE = 'NUMBER' AND
	857	UTC.DATA_PRECISION != 1 AND
	858	UTC.DATA_SCALE != 0 AND
	859	UTC.DATA_PRECISION IS NOT NULL
	860	THEN
	861	UTC.DATA_SCALE
	862	ELSE
	863	NULL
	864	END LENGTH,
	865	UTC.NULLABLE,
	866	UTC.DATA_DEFAULT,
	867	UC.SEARCH_CONDITION CHECK_CONSTRAINT
	868	FROM
	869	USER_TAB_COLUMNS UTC LEFT JOIN
	870	USER_CONS_COLUMNS UCC ON
	871	UTC.COLUMN_NAME = UCC.COLUMN_NAME AND
	872	UTC.TABLE_NAME = UCC.TABLE_NAME AND
	873	UCC.POSITION IS NULL LEFT JOIN
	874	USER_CONSTRAINTS UC ON
	875	UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND
	876	UC.CONSTRAINT_TYPE = 'C' AND
	877	UC.STATUS = 'ENABLED'
	878	WHERE
	879	UTC.TABLE_NAME = %s
	880	ORDER BY
	881	UTC.TABLE_NAME ASC,
	882	UTC.COLUMN_ID ASC";
	883	$result = $this->database->query($sql, $table);
	884
	885	foreach ($result as $row) {
	886
	887	$column = $row['column_name'];
	888
	889	// Since Oracle stores check constraints in LONG columns, it is
	890	// not possible to check or modify the constraints in SQL which
	891	// ends up causing multiple rows with duplicate data except for
	892	// the check constraint
	893	$duplicate = FALSE;
	894
	895	if (isset($column_info[$column])) {
	896	$info = $column_info[$column];
	897	$duplicate = TRUE;
	898	} else {
	899	$info = array();
	900	}
	901
	902	if (!$duplicate) {
	903	// Get the column type
	904	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
	905	if (stripos($row['data_type'], $data_type) === 0) {
	906	$info['type'] = $mapped_data_type;
	907	break;
	908	}
	909	}
	910
	911	if (!isset($info['type'])) {
	912	$info['type'] = $row['data_type'];
	913	}
	914
	915	// Handle the length of decimal/numeric fields
	916	if ($info['type'] == 'float' && $row['length']) {
	917	$info['decimal_places'] = (int) $row['length'];
	918	}
	919
	920	// Handle the special data for varchar fields
	921	if (in_array($info['type'], array('char', 'varchar'))) {
	922	$info['max_length'] = (int) $row['length'];
	923	}
	924	}
	925
	926	// Handle check constraints that are just simple lists
	927	if (in_array($info['type'], array('varchar', 'char')) && $row['check_constraint']) {
	928	if (preg_match('/^\s' . preg_quote($column, '/') . '\s+in\s+$(.?)$\s*$/i', $row['check_constraint'], $match)) {
	929	if (preg_match_all("/(?<!')'((''\|[^']+)*)'/", $match[1], $matches, PREG_PATTERN_ORDER)) {
	930	$info['valid_values'] = str_replace("''", "'", $matches[1]);
	931	}
	932	}
	933	}
	934
	935	if (!$duplicate) {
	936	// Handle default values
	937	if ($row['data_default'] !== NULL) {
	938	if (in_array($info['type'], array('char', 'varchar', 'text'))) {
	939	$info['default'] = str_replace("''", "'", substr(trim($row['data_default']), 1, -1));
	940
	941	} elseif ($info['type'] == 'boolean') {
	942	$info['default'] = (boolean) trim($row['data_default']);
	943
	944	} elseif (in_array($info['type'], array('integer', 'float'))) {
	945	$info['default'] = trim($row['data_default']);
	946
	947	} else {
	948	$info['default'] = $row['data_default'];
	949	}
	950	}
	951
	952	// Not null values
	953	$info['not_null'] = ($row['nullable'] == 'N') ? TRUE : FALSE;
	954	}
	955
	956	$column_info[$column] = $info;
	957	}
	958
	959	$sql = "SELECT
	960	TRIGGER_BODY
	961	FROM
	962	USER_TRIGGERS
	963	WHERE
	964	TRIGGERING_EVENT = 'INSERT' AND
	965	STATUS = 'ENABLED' AND
	966	TRIGGER_NAME NOT LIKE 'BIN\$%' AND
	967	TABLE_NAME = %s";
	968
	969	foreach ($this->database->query($sql, $table) as $row) {
	970	if (preg_match('#SELECT\s+(\w+).nextval\s+INTO\s+:new\.(\w+)\s+FROM\s+dual#i', $row['trigger_body'], $matches)) {
	971	$column = strtolower($matches[2]);
	972	$column_info[$column]['auto_increment'] = TRUE;
	973	}
	974	}
	975
	976	return $column_info;
	977	}
	978
	979
	980	/**
	981	* Fetches the key info for an Oracle database
	982	*
	983	* The structure of the returned array is:
	984	*
	985	* {{{
	986	* array(
	987	* 'primary' => array(
	988	* {column name}, ...
	989	* ),
	990	* 'unique' => array(
	991	* array(
	992	* {column name}, ...
	993	* ), ...
	994	* ),
	995	* 'foreign' => array(
	996	* array(
	997	* 'column' => {column name},
	998	* 'foreign_table' => {foreign table name},
	999	* 'foreign_column' => {foreign column name},
	1000	* 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
	1001	* 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
	1002	* ), ...
	1003	* )
	1004	* )
	1005	* }}}
	1006	*
	1007	* @return array The keys arrays for every table in the database - see method description for details
	1008	*/
	1009	private function fetchOracleKeys()
	1010	{
	1011	$keys = array();
	1012
	1013	$tables = $this->getTables();
	1014	foreach ($tables as $table) {
	1015	$keys[$table] = array();
	1016	$keys[$table]['primary'] = array();
	1017	$keys[$table]['unique'] = array();
	1018	$keys[$table]['foreign'] = array();
	1019	}
	1020
	1021	$sql = "SELECT
	1022	LOWER(UC.TABLE_NAME) \"TABLE\",
	1023	UC.CONSTRAINT_NAME CONSTRAINT_NAME,
	1024	CASE UC.CONSTRAINT_TYPE
	1025	WHEN 'P' THEN 'primary'
	1026	WHEN 'R' THEN 'foreign'
	1027	WHEN 'U' THEN 'unique'
	1028	END TYPE,
	1029	LOWER(UCC.COLUMN_NAME) \"COLUMN\",
	1030	LOWER(FKC.TABLE_NAME) FOREIGN_TABLE,
	1031	LOWER(FKC.COLUMN_NAME) FOREIGN_COLUMN,
	1032	CASE WHEN FKC.TABLE_NAME IS NOT NULL THEN REPLACE(LOWER(UC.DELETE_RULE), ' ', '_') ELSE NULL END ON_DELETE
	1033	FROM
	1034	USER_CONSTRAINTS UC INNER JOIN
	1035	USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME LEFT JOIN
	1036	USER_CONSTRAINTS FK ON UC.R_CONSTRAINT_NAME = FK.CONSTRAINT_NAME LEFT JOIN
	1037	USER_CONS_COLUMNS FKC ON FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME
	1038	WHERE
	1039	UC.CONSTRAINT_TYPE IN ('U', 'P', 'R') AND
	1040	UC.STATUS = 'ENABLED' AND
	1041	SUBSTR(UC.TABLE_NAME, 1, 4) <> 'BIN\$'
	1042	ORDER BY
	1043	UC.TABLE_NAME ASC,
	1044	UC.CONSTRAINT_TYPE ASC,
	1045	UC.CONSTRAINT_NAME ASC,
	1046	UCC.POSITION ASC";
	1047
	1048	$result = $this->database->query($sql);
	1049
	1050	$last_name = '';
	1051	$last_table = '';
	1052	$last_type = '';
	1053	foreach ($result as $row) {
	1054
	1055	if ($row['constraint_name'] != $last_name) {
	1056
	1057	if ($last_name) {
	1058	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
	1059	$keys[$last_table][$last_type][] = $temp;
	1060	} else {
	1061	$keys[$last_table][$last_type] = $temp;
	1062	}
	1063	}
	1064
	1065	$temp = array();
	1066	if ($row['type'] == 'foreign') {
	1067
	1068	$temp['column'] = $row['column'];
	1069	$temp['foreign_table'] = $row['foreign_table'];
	1070	$temp['foreign_column'] = $row['foreign_column'];
	1071	$temp['on_delete'] = 'no_action';
	1072	$temp['on_update'] = 'no_action';
	1073
	1074	if (!empty($row['on_delete'])) {
	1075	$temp['on_delete'] = $row['on_delete'];
	1076	}
	1077
	1078	} else {
	1079	$temp[] = $row['column'];
	1080	}
	1081
	1082	$last_table = $row['table'];
	1083	$last_name = $row['constraint_name'];
	1084	$last_type = $row['type'];
	1085
	1086	} else {
	1087	$temp[] = $row['column'];
	1088	}
	1089	}
	1090
	1091	if (isset($temp)) {
	1092	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
	1093	$keys[$last_table][$last_type][] = $temp;
	1094	} else {
	1095	$keys[$last_table][$last_type] = $temp;
716	1096	}
717	1097	}
…	…
817	1197
818	1198	// Handle the special data for varchar fields
819		if (in_array($info['type'], array('char', 'varchar'))) {
	1199	if (in_array($info['type'], array('char', 'varchar')) && !empty($column_data_type[2])) {
820	1200	$info['max_length'] = $column_data_type[2];
821	1201	}
…	…
942	1322	con.contype,
943	1323	con.conname,
	1324	CASE WHEN con.conkey IS NOT NULL THEN position('-'\|\|col.attnum\|\|'-' in '-'\|\|array_to_string(con.conkey, '-')\|\|'-') ELSE 0 END,
944	1325	col.attname";
945	1326
…	…
1059	1440	}
1060	1441
1061		preg_match_all('#(?<=,\|$)\s(?:`\|"\|\[)?(\w+)(?:`\|"\|\])?\s+([a-z]+)(?:\(\s(\d+)(?:\s,\s(\d+))?\s$)?(?:(\s+NOT\s+NULL)\|(?:\s+DEFAULT\s+([^, \']\|\'(?:\'\'\|[^\']+)\'))\|(\s+UNIQUE)\|(\s+PRIMARY\s+KEY(?:\s+AUTOINCREMENT)?)\|(\s+CHECK\s$\w+\s+IN\s+\(\s(?:(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')\s,\s)\s(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')$\)))(\s+REFERENCES\s+\w+\s$\s\w+\s$\s(?:\s+(?:ON\s+DELETE\|ON\s+UPDATE)\s+(?:CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL\|SET\s+DEFAULT))(?:\s+(?:DEFERRABLE\|NOT\s+DEFERRABLE))?)?\s(?:,\|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
	1442	preg_match_all('#(?<=,\|$)\s(?:`\|"\|\[)?(\w+)(?:`\|"\|\])?\s+([a-z]+)(?:\(\s(\d+)(?:\s,\s(\d+))?\s$)?(?:(\s+NOT\s+NULL)\|(?:\s+NULL)\|(?:\s+DEFAULT\s+([^, \']\|\'(?:\'\'\|[^\']+)\'))\|(\s+UNIQUE)\|(\s+PRIMARY\s+KEY(?:\s+AUTOINCREMENT)?)\|(\s+CHECK\s$\w+\s+IN\s+\(\s(?:(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')\s,\s)\s(?:[^, \']+\|\'(?:\'\'\|[^\']+)\')$\)))(\s+REFERENCES\s+\w+\s$\s\w+\s$\s(?:\s+(?:ON\s+DELETE\|ON\s+UPDATE)\s+(?:CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL\|SET\s+DEFAULT))(?:\s+(?:DEFERRABLE\|NOT\s+DEFERRABLE))?)?\s(?:,\|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
1062	1443
1063	1444	foreach ($matches as $match) {
…	…
1072	1453
1073	1454	// Type specific information
1074		if (in_array($info['type'], array('char', 'varchar'))) {
	1455	if (in_array($info['type'], array('char', 'varchar')) && !empty($match[3])) {
1075	1456	$info['max_length'] = $match[3];
1076	1457	}
…	…
1140	1521	private function fetchSQLiteKeys()
1141	1522	{
1142		$tables = $this->getTables();
1143		$keys = array();
	1523	$tables = $this->getTables();
	1524	$keys = array();
1144	1525
1145	1526	foreach ($tables as $table) {
…	…
1331	1712
1332	1713	/**
1333		* Makes sure the info is current, if not it deletes it so that all info is current
1334		*
1335		* @internal
1336		*
1337		* @return void
1338		*/
1339		~~public function flushInfo()~~
1340		{
1341		~~if ($this->state != 'current') {~~
1342		~~$this->tables = NULL;~~
1343		~~$this->column_info = array();~~
1344		~~$this->keys = array();~~
1345		~~$this->merged_column_info = array();~~
1346		~~$this->merged_keys = array();~~
1347		~~$this->relationships = array();~~
1348		~~$this->state = 'current';~~
1349		~~$this->info_changed = TRUE;~~
1350		}
1351		}
1352
1353
1354		/**
1355	1714	* Returns column information for the table specified
1356	1715	*
…	…
1503	1862	$keys = array_keys($row);
1504	1863	$this->databases[] = $row[$keys[0]];
	1864	}
	1865
	1866	if ($this->cache) {
	1867	$this->cache->set($this->makeCachePrefix() . 'databases', $this->databases);
1505	1868	}
1506	1869
…	…
1682	2045	switch ($this->database->getType()) {
1683	2046	case 'mssql':
1684		$sql = 'SELECT
	2047	$sql = "SELECT
1685	2048	TABLE_NAME
1686	2049	FROM
1687	2050	INFORMATION_SCHEMA.TABLES
	2051	WHERE
	2052	TABLE_NAME != 'sysdiagrams'
1688	2053	ORDER BY
1689		LOWER(TABLE_NAME)';
	2054	LOWER(TABLE_NAME)";
1690	2055	break;
1691	2056
1692	2057	case 'mysql':
1693	2058	$sql = 'SHOW TABLES';
	2059	break;
	2060
	2061	case 'oracle':
	2062	$sql = "SELECT
	2063	LOWER(TABLE_NAME)
	2064	FROM
	2065	USER_TABLES
	2066	WHERE
	2067	SUBSTR(TABLE_NAME, 1, 4) <> 'BIN\$'
	2068	ORDER BY
	2069	TABLE_NAME ASC";
1694	2070	break;
1695	2071
…	…
1727	2103	}
1728	2104
	2105	if ($this->cache) {
	2106	$this->cache->set($this->makeCachePrefix() . 'tables', $this->tables);
	2107	}
	2108
1729	2109	return $this->tables;
1730	2110	}
…	…
1758	2138
1759	2139	return sizeof($foreign_key_columns) == 2 && !array_diff($foreign_key_columns, $primary_key_columns);
	2140	}
	2141
	2142
	2143	/**
	2144	* Creates a unique cache prefix to help prevent cache conflicts
	2145	*
	2146	* @return void
	2147	*/
	2148	private function makeCachePrefix()
	2149	{
	2150	$prefix = 'fSchema::' . $this->database->getType() . '::';
	2151	if ($this->database->getHost()) {
	2152	$prefix .= $this->database->getHost() . '::';
	2153	}
	2154	if ($this->database->getPort()) {
	2155	$prefix .= $this->database->getPort() . '::';
	2156	}
	2157	$prefix .= $this->database->getDatabase() . '::';
	2158	if ($this->database->getUsername()) {
	2159	$prefix .= $this->database->getUsername() . '::';
	2160	}
	2161	return $prefix;
1760	2162	}
1761	2163
…	…
1796	2198	}
1797	2199
1798		$optional_elements = array('~~default', 'not_null~~', 'valid_values', 'max_length', 'decimal_places', 'auto_increment');
	2200	$optional_elements = array('not_null', 'default', 'valid_values', 'max_length', 'decimal_places', 'auto_increment');
1799	2201
1800	2202	foreach ($this->merged_column_info as $table => $column_array) {
…	…
1831	2233
1832	2234	$this->findRelationships();
1833		}
1834
1835
1836		/**
1837		* Sets a file to cache the schema info to
1838		*
1839		* @param string $file The cache file
1840		* @return void
1841		*/
1842		~~public function setCacheFile($file)~~
1843		{
1844		~~$file = realpath($file);~~
1845
1846		~~if (file_exists($file) && !is_writable($file)) {~~
1847		~~throw new fEnvironmentException(~~
1848		~~'The cache file specified, %s, is not writable',~~
1849		~~$file~~
1850		);
1851		}
1852
1853		~~if (!file_exists($file) && !is_writable(dirname($file))) {~~
1854		~~throw new fEnvironmentException(~~
1855		~~'The cache file specified, %1$s, does not exist and the cache file directory, %2$s, is not writable',~~
1856		~~$file,~~
1857		~~dirname($file) . DIRECTORY_SEPARATOR~~
1858		);
1859		}
1860
1861		~~$this->cache_file = $file;~~
1862
1863		~~$contents = file_get_contents($this->cache_file);~~
1864		~~if ($contents) {~~
1865		~~$info = unserialize($contents);~~
1866		~~$this->tables = $info['tables'];~~
1867		~~$this->column_info = $info['column_info'];~~
1868		~~$this->keys = $info['keys'];~~
1869		}
1870
1871		~~if (!empty($this->column_info) \|\| !empty($this->keys)) {~~
1872		~~$this->state = 'cached';~~
1873		}
1874	2235	}
1875	2236

fUnbufferedResult.php

r514	r562	Hide Line Numbers
10	10	* @link http://flourishlib.com/fUnbufferedResult
11	11	*
12		* @version 1.0.0b2
	12	* @version 1.0.0b3
	13	* @changes 1.0.0b3 Added support for Oracle, various bug fixes [wb, 2009-05-04]
13	14	* @changes 1.0.0b2 Updated for new fCore API [wb, 2009-02-16]
14	15	* @changes 1.0.0b The initial implementation [wb, 2008-05-07]
…	…
101	102	* @internal
102	103	*
103		* @param string $type The type of database: `'mssql'`, `'mysql'`, `'postgresql'`, `'sqlite'`
104		* @param string $extension The database extension used: `'mssql'`, `'mysql'`, `'mysqli'`, `'odbc'`, `'pdo'`, `'pgsql'`, `'sqlite', 'sqlsrv'`
	104	* @param string $type The type of database: `'mssql'`, `'mysql'`, `'oracle'`, `'postgresql'`, `'sqlite'`
	105	* @param string $extension The database extension used: `'mssql'`, `'mysql'`, `'mysqli'`, `'oci8'` `'odbc'`, `'pdo'`, `'pgsql'`, `'sqlite', 'sqlsrv'`
105	106	* @param string $character_set MSSQL only: the character set to transcode from since MSSQL doesn't do UTF-8
106	107	* @return fUnbufferedResult
…	…
108	109	public function __construct($type, $extension, $character_set=NULL)
109	110	{
110		$valid_types = array('mssql', 'mysql', 'postgresql', 'sqlite');
	111	$valid_types = array('mssql', 'mysql', 'oracle', 'postgresql', 'sqlite');
111	112	if (!in_array($type, $valid_types)) {
112	113	throw new fProgrammerException(
…	…
117	118	}
118	119
119		$valid_extensions = array('mssql', 'mysql', 'mysqli', 'odbc', 'pdo', 'pgsql', 'sqlite', 'sqlsrv');
	120	$valid_extensions = array('mssql', 'mysql', 'mysqli', 'oci8', 'odbc', 'pdo', 'pgsql', 'sqlite', 'sqlsrv');
120	121	if (!in_array($extension, $valid_extensions)) {
121	122	throw new fProgrammerException(
…	…
151	152	} elseif ($this->extension == 'mysqli') {
152	153	mysqli_free_result($this->result);
	154	} elseif ($this->extension == 'oci8') {
	155	oci_free_statement($this->result);
153	156	} elseif ($this->extension == 'odbc') {
154	157	odbc_free_result($this->result);
…	…
156	159	pg_free_result($this->result);
157	160	} elseif ($this->extension == 'sqlite') {
158		~~sqlite_fetch_all~~($this->result);
	161	unset($this->result);
159	162	} elseif ($this->extension == 'sqlsrv') {
160	163	sqlsrv_free_stmt($this->result);
…	…
162	165	$this->result->closeCursor();
163	166	}
	167
	168	$this->result = NULL;
164	169	}
165	170
…	…
185	190	{
186	191	if ($this->extension == 'mssql') {
187		$row = mssql_fetch_assoc($this->result);
188		if (empty($row)) {
189		mssql_fetch_batch($this->result);
	192	// For some reason the mssql extension will return an empty row even
	193	// when now rows were returned, so we have to explicitly check for this
	194	if ($this->pointer == 0 && !mssql_num_rows($this->result)) {
	195	$row = FALSE;
	196
	197	} else {
190	198	$row = mssql_fetch_assoc($this->result);
191		}
192		if (!empty($row)) {
193		$row = $this->fixDblibMSSQLDriver($row);
194
195		// This is an unfortunate fix that required for databases that don't support limit
196		// clauses with an offset. It prevents unrequested columns from being returned.
197		if (!empty($row) && $this->untranslated_sql !== NULL && isset($row['__flourish_limit_offset_row_num'])) {
198		unset($row['__flourish_limit_offset_row_num']);
	199	if (empty($row)) {
	200	mssql_fetch_batch($this->result);
	201	$row = mssql_fetch_assoc($this->result);
	202	}
	203	if (!empty($row)) {
	204	$row = $this->fixDblibMSSQLDriver($row);
199	205	}
200	206	}
…	…
204	210	} elseif ($this->extension == 'mysqli') {
205	211	$row = mysqli_fetch_assoc($this->result);
	212	} elseif ($this->extension == 'oci8') {
	213	$row = oci_fetch_assoc($this->result);
206	214	} elseif ($this->extension == 'odbc') {
207	215	$row = odbc_fetch_array($this->result);
…	…
214	222	} elseif ($this->extension == 'pdo') {
215	223	$row = $this->result->fetch(PDO::FETCH_ASSOC);
	224	}
	225
	226	// Fix uppercase column names to lowercase
	227	if ($row && $this->type == 'oracle') {
	228	$new_row = array();
	229	foreach ($row as $column => $value) {
	230	$new_row[strtolower($column)] = $value;
	231	}
	232	$row = $new_row;
	233	}
	234
	235	// This is an unfortunate fix that required for databases that don't support limit
	236	// clauses with an offset. It prevents unrequested columns from being returned.
	237	if ($row && ($this->type == 'mssql' \|\| $this->type == 'oracle')) {
	238	if ($this->untranslated_sql !== NULL && isset($row['flourish__row__num'])) {
	239	unset($row['flourish__row__num']);
	240	}
216	241	}
217	242
…	…
220	245	if ($this->character_set) {
221	246	foreach ($row as $key => $value) {
222		if (!is_string($value) \|\| strpos($key, '__flourish_mssqln_') === 0) {
	247	if (!is_string($value) \|\| strpos($key, '__flourish_mssqln_') === 0 \|\| isset($row['fmssqln__' . $key]) \|\| preg_match('#[\x0-\x8\xB\xC\xD-\x1F]#', $value)) {
223	248	continue;
224	249	}
…	…
244	269	public function current()
245	270	{
	271	$this->validateState();
	272
246	273	// Primes the result set
247	274	if ($this->pointer === NULL) {
…	…
269	296	private function decodeMSSQLNationalColumns($row)
270	297	{
271		if (strpos($this->sql, '~~__flourish_mssqln~~_') === FALSE) {
	298	if (strpos($this->sql, 'fmssqln__') === FALSE) {
272	299	return $row;
273	300	}
…	…
276	303
277	304	foreach ($columns as $column) {
278		if (substr($column, 0, ~~18) != '__flourish_mssqln~~_') {
	305	if (substr($column, 0, 9) != 'fmssqln__') {
279	306	continue;
280	307	}
281	308
282		$real_column = substr($column, 18);
	309	$real_column = substr($column, 9);
283	310
284	311	$row[$real_column] = iconv('ucs-2le', 'utf-8', $row[$column]);
…	…
300	327	public function fetchRow()
301	328	{
	329	$this->validateState();
	330
302	331	$row = $this->current();
303	332	$this->next();
…	…
329	358	ob_end_clean();
330	359
331		$using_dblib = preg_match('#FreeTDS#ims', $module_info, $match);
	360	$using_dblib = !preg_match('#FreeTDS#ims', $module_info, $match);
332	361	}
333	362	}
…	…
381	410	public function getResult()
382	411	{
	412	$this->validateState();
	413
383	414	return $this->result;
384	415	}
…	…
417	448	public function key()
418	449	{
	450	$this->validateState();
	451
419	452	if ($this->pointer === NULL) {
420	453	$this->current();
…	…
435	468	public function next()
436	469	{
	470	$this->validateState();
	471
437	472	if ($this->pointer === NULL) {
438	473	$this->current();
…	…
453	488	public function rewind()
454	489	{
	490	$this->validateState();
	491
455	492	if (!empty($this->pointer)) {
456	493	throw new fProgrammerException(
…	…
531	568	public function valid()
532	569	{
	570	$this->validateState();
	571
533	572	if ($this->pointer === NULL) {
534	573	$this->advanceCurrentRow();
…	…
537	576
538	577	return !empty($this->current_row);
	578	}
	579
	580
	581	/**
	582	* Throws an exception if this object has been deconstructed already
	583	*
	584	* @return void
	585	*/
	586	private function validateState()
	587	{
	588	if ($this->result === NULL) {
	589	throw new fProgrammerException('This unbuffered result has been fully fetched, or replaced by a newer result');
	590	}
539	591	}
540	592	}

Navigation

Changeset 562

Legend:

Download in other formats: