root/fSchema.php

Class Resources

Revision 871, 93.4 kB (checked in by wbond, 1 week ago)
Completed ticket #470 - updated fORMOrdering::configureOrderingColumn() to make sure the column supports negative values, fixed a documentation bug in fSchema

Line	Hide Line Numbers
1	<?php
2	/**
3	* Gets schema information for the selected database
4	*
5	* @copyright Copyright (c) 2007-2010 Will Bond
6	* @author Will Bond [wb] <will@flourishlib.com>
7	* @license http://flourishlib.com/license
8	*
9	* @package Flourish
10	* @link http://flourishlib.com/fSchema
11	*
12	* @version 1.0.0b40
13	* @changes 1.0.0b40 Fixed bugs in the documentation and error message of ::getColumnInfo() about what are valid elements [wb, 2010-07-21]
14	* @changes 1.0.0b39 Fixed a regression where key detection SQL was not compatible with PostgreSQL 8.1 [wb, 2010-04-13]
15	* @changes 1.0.0b38 Added Oracle support to ::getDatabases() [wb, 2010-04-13]
16	* @changes 1.0.0b37 Fixed ::getDatabases() for MSSQL [wb, 2010-04-09]
17	* @changes 1.0.0b36 Fixed PostgreSQL to properly report explicit `NULL` default values via ::getColumnInfo() [wb, 2010-03-30]
18	* @changes 1.0.0b35 Added `max_length` values for various text and blob data types across all databases [wb, 2010-03-29]
19	* @changes 1.0.0b34 Added `min_value` and `max_value` attributes to ::getColumnInfo() to specify the valid range for numeric columns [wb, 2010-03-16]
20	* @changes 1.0.0b33 Changed it so that PostgreSQL unique indexes containing functions are ignored since they can't be properly detected at this point [wb, 2010-03-14]
21	* @changes 1.0.0b32 Fixed ::getTables() to not include views for MySQL [wb, 2010-03-14]
22	* @changes 1.0.0b31 Fixed the creation of the default caching key for ::enableCaching() [wb, 2010-03-02]
23	* @changes 1.0.0b30 Fixed the class to work with lower privilege Oracle accounts and added detection of Oracle number columns [wb, 2010-01-25]
24	* @changes 1.0.0b29 Added on_delete and on_update elements to one-to-one relationship info retrieved by ::getRelationships() [wb, 2009-12-16]
25	* @changes 1.0.0b28 Fixed a bug with detecting some multi-column unique constraints in SQL Server databases [wb, 2009-11-13]
26	* @changes 1.0.0b27 Added a parameter to ::enableCaching() to provide a key token that will allow cached values to be shared between multiple databases with the same schema [wb, 2009-10-28]
27	* @changes 1.0.0b26 Added the placeholder element to the output of ::getColumnInfo(), added support for PostgreSQL, MSSQL and Oracle "schemas", added support for parsing quoted SQLite identifiers [wb, 2009-10-22]
28	* @changes 1.0.0b25 One-to-one relationships utilizing the primary key as a foreign key are now properly detected [wb, 2009-09-22]
29	* @changes 1.0.0b24 Fixed MSSQL support to work with ODBC database connections [wb, 2009-09-18]
30	* @changes 1.0.0b23 Fixed a bug where one-to-one relationships were being listed as many-to-one [wb, 2009-07-21]
31	* @changes 1.0.0b22 PostgreSQL UNIQUE constraints that are created as indexes and not table constraints are now properly detected [wb, 2009-07-08]
32	* @changes 1.0.0b21 Added support for the UUID data type in PostgreSQL [wb, 2009-06-18]
33	* @changes 1.0.0b20 Add caching of merged info, improved performance of ::getColumnInfo() [wb, 2009-06-15]
34	* @changes 1.0.0b19 Fixed a couple of bugs with ::setKeysOverride() [wb, 2009-06-04]
35	* @changes 1.0.0b18 Added missing support for MySQL mediumint columns [wb, 2009-05-18]
36	* @changes 1.0.0b17 Fixed a bug with ::clearCache() not properly reseting the tables and databases list [wb, 2009-05-13]
37	* @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]
38	* @changes 1.0.0b15 Added support for the three different types of identifier quoting in SQLite [wb, 2009-03-28]
39	* @changes 1.0.0b14 Added support for MySQL column definitions containing the COLLATE keyword [wb, 2009-03-28]
40	* @changes 1.0.0b13 Fixed a bug with detecting PostgreSQL columns having both a CHECK constraint and a UNIQUE constraint [wb, 2009-02-27]
41	* @changes 1.0.0b12 Fixed detection of multi-column primary keys in MySQL [wb, 2009-02-27]
42	* @changes 1.0.0b11 Fixed an issue parsing MySQL tables with comments [wb, 2009-02-25]
43	* @changes 1.0.0b10 Added the ::getDatabases() method [wb, 2009-02-24]
44	* @changes 1.0.0b9 Now detects unsigned and zerofill MySQL data types that do not have a parenthetical part [wb, 2009-02-16]
45	* @changes 1.0.0b8 Mapped the MySQL data type `'set'` to `'varchar'`, however valid values are not implemented yet [wb, 2009-02-01]
46	* @changes 1.0.0b7 Fixed a bug with detecting MySQL timestamp columns [wb, 2009-01-28]
47	* @changes 1.0.0b6 Fixed a bug with detecting MySQL columns that accept `NULL` [wb, 2009-01-19]
48	* @changes 1.0.0b5 ::setColumnInfo(): fixed a bug with not grabbing the real database schema first, made general improvements [wb, 2009-01-19]
49	* @changes 1.0.0b4 Added support for MySQL binary data types, numeric data type options unsigned and zerofill, and per-column character set definitions [wb, 2009-01-17]
50	* @changes 1.0.0b3 Fixed detection of the data type of MySQL timestamp columns, added support for dynamic default date/time values [wb, 2009-01-11]
51	* @changes 1.0.0b2 Fixed a bug with detecting multi-column unique keys in MySQL [wb, 2009-01-03]
52	* @changes 1.0.0b The initial implementation [wb, 2007-09-25]
53	*/
54	class fSchema
55	{
56	/**
57	* The place to cache to
58	*
59	* @var fCache
60	*/
61	private $cache = NULL;
62
63	/**
64	* The cache prefix to use for cache entries
65	*
66	* @var string
67	*/
68	private $cache_prefix;
69
70	/**
71	* The cached column info
72	*
73	* @var array
74	*/
75	private $column_info = array();
76
77	/**
78	* The column info to override
79	*
80	* @var array
81	*/
82	private $column_info_override = array();
83
84	/**
85	* A reference to an instance of the fDatabase class
86	*
87	* @var fDatabase
88	*/
89	private $database = NULL;
90
91	/**
92	* The databases on the current database server
93	*
94	* @var array
95	*/
96	private $databases = NULL;
97
98	/**
99	* The cached key info
100	*
101	* @var array
102	*/
103	private $keys = array();
104
105	/**
106	* The key info to override
107	*
108	* @var array
109	*/
110	private $keys_override = array();
111
112	/**
113	* The merged column info
114	*
115	* @var array
116	*/
117	private $merged_column_info = array();
118
119	/**
120	* The merged key info
121	*
122	* @var array
123	*/
124	private $merged_keys = array();
125
126	/**
127	* The relationships in the database
128	*
129	* @var array
130	*/
131	private $relationships = array();
132
133	/**
134	* The tables in the database
135	*
136	* @var array
137	*/
138	private $tables = NULL;
139
140
141	/**
142	* Sets the database
143	*
144	* @param fDatabase $database The fDatabase instance
145	* @return fSchema
146	*/
147	public function __construct($database)
148	{
149	$this->database = $database;
150	}
151
152
153	/**
154	* All requests that hit this method should be requests for callbacks
155	*
156	* @internal
157	*
158	* @param string $method The method to create a callback for
159	* @return callback The callback for the method requested
160	*/
161	public function __get($method)
162	{
163	return array($this, $method);
164	}
165
166
167	/**
168	* Checks to see if a column is part of a single-column `UNIQUE` key
169	*
170	* @param string $table The table the column is located in
171	* @param string $column The column to check
172	* @return boolean If the column is part of a single-column unique key
173	*/
174	private function checkForSingleColumnUniqueKey($table, $column)
175	{
176	foreach ($this->merged_keys[$table]['unique'] as $key) {
177	if (array($column) == $key) {
178	return TRUE;
179	}
180	}
181	if (array($column) == $this->merged_keys[$table]['primary']) {
182	return TRUE;
183	}
184	return FALSE;
185	}
186
187
188	/**
189	* Clears all of the schema info out of the object and, if set, the fCache object
190	*
191	* @internal
192	*
193	* @return void
194	*/
195	public function clearCache()
196	{
197	$this->column_info = array();
198	$this->databases = NULL;
199	$this->keys = array();
200	$this->merged_column_info = array();
201	$this->merged_keys = array();
202	$this->relationships = array();
203	$this->tables = NULL;
204	if ($this->cache) {
205	$prefix = $this->makeCachePrefix();
206	$this->cache->delete($prefix . 'column_info');
207	$this->cache->delete($prefix . 'databases');
208	$this->cache->delete($prefix . 'keys');
209	$this->cache->delete($prefix . 'merged_column_info');
210	$this->cache->delete($prefix . 'merged_keys');
211	$this->cache->delete($prefix . 'relationships');
212	$this->cache->delete($prefix . 'tables');
213	}
214	}
215
216
217	/**
218	* Sets the schema to be cached to the fCache object specified
219	*
220	* @param fCache $cache The cache to cache to
221	* @param string $key_token Internal use only! (this will be used in the cache key to uniquely identify the cache for this fSchema object)
222	* @return void
223	*/
224	public function enableCaching($cache, $key_token=NULL)
225	{
226	$this->cache = $cache;
227
228	if ($key_token !== NULL) {
229	$this->cache_prefix = 'fSchema::' . $this->database->getType() . '::' . $key_token . '::';
230	}
231	$prefix = $this->makeCachePrefix();
232
233	$this->column_info = $this->cache->get($prefix . 'column_info', array());
234	$this->databases = $this->cache->get($prefix . 'databases', NULL);
235	$this->keys = $this->cache->get($prefix . 'keys', array());
236
237	if (!$this->column_info_override && !$this->keys_override) {
238	$this->merged_column_info = $this->cache->get($prefix . 'merged_column_info', array());
239	$this->merged_keys = $this->cache->get($prefix . 'merged_keys', array());
240	$this->relationships = $this->cache->get($prefix . 'relationships', array());
241	}
242
243	$this->tables = $this->cache->get($prefix . 'tables', NULL);
244	}
245
246
247	/**
248	* Gets the column info from the database for later access
249	*
250	* @param string $table The table to fetch the column info for
251	* @return void
252	*/
253	private function fetchColumnInfo($table)
254	{
255	if (isset($this->column_info[$table])) {
256	return;
257	}
258
259	switch ($this->database->getType()) {
260	case 'db2':
261	$column_info = $this->fetchDB2ColumnInfo($table);
262	break;
263
264	case 'mssql':
265	$column_info = $this->fetchMSSQLColumnInfo($table);
266	break;
267
268	case 'mysql':
269	$column_info = $this->fetchMySQLColumnInfo($table);
270	break;
271
272	case 'oracle':
273	$column_info = $this->fetchOracleColumnInfo($table);
274	break;
275
276	case 'postgresql':
277	$column_info = $this->fetchPostgreSQLColumnInfo($table);
278	break;
279
280	case 'sqlite':
281	$column_info = $this->fetchSQLiteColumnInfo($table);
282	break;
283	}
284
285	if (!$column_info) {
286	return;
287	}
288
289	$this->column_info[$table] = $column_info;
290	if ($this->cache) {
291	$this->cache->set($this->makeCachePrefix() . 'column_info', $this->column_info);
292	}
293	}
294
295
296	/**
297	* Gets the column info from a DB2 database
298	*
299	* @param string $table The table to fetch the column info for
300	* @return array The column info for the table specified - see ::getColumnInfo() for details
301	*/
302	private function fetchDB2ColumnInfo($table)
303	{
304	$column_info = array();
305
306	$schema = strtolower($this->database->getUsername());
307	if (strpos($table, '.') !== FALSE) {
308	list ($schema, $table) = explode('.', $table);
309	}
310
311	$data_type_mapping = array(
312	'smallint' => 'integer',
313	'integer' => 'integer',
314	'bigint' => 'integer',
315	'timestamp' => 'timestamp',
316	'date' => 'date',
317	'time' => 'time',
318	'varchar' => 'varchar',
319	'long varchar' => 'varchar',
320	'vargraphic' => 'varchar',
321	'long vargraphic' => 'varchar',
322	'character' => 'char',
323	'graphic' => 'char',
324	'real' => 'float',
325	'decimal' => 'float',
326	'numeric' => 'float',
327	'blob' => 'blob',
328	'clob' => 'text',
329	'dbclob' => 'text'
330	);
331
332	$max_min_values = array(
333	'smallint' => array('min' => new fNumber(-32768), 'max' => new fNumber(32767)),
334	'integer' => array('min' => new fNumber(-2147483648), 'max' => new fNumber(2147483647)),
335	'bigint' => array('min' => new fNumber('-9223372036854775808'), 'max' => new fNumber('9223372036854775807'))
336	);
337
338	// Get the column info
339	$sql = "SELECT
340	LOWER(C.COLNAME) AS \"COLUMN\",
341	C.TYPENAME AS TYPE,
342	C.NULLS AS NULLABLE,
343	C.DEFAULT,
344	C.LENGTH AS MAX_LENGTH,
345	C.SCALE,
346	CASE WHEN C.IDENTITY = 'Y' AND (C.GENERATED = 'D' OR C.GENERATED = 'A') THEN '1' ELSE '0' END AS AUTO_INCREMENT,
347	CH.TEXT AS \"CONSTRAINT\"
348	FROM
349	SYSCAT.COLUMNS AS C LEFT JOIN
350	SYSCAT.COLCHECKS AS CC ON C.TABSCHEMA = CC.TABSCHEMA AND C.TABNAME = CC.TABNAME AND C.COLNAME = CC.COLNAME AND CC.USAGE = 'R' LEFT JOIN
351	SYSCAT.CHECKS AS CH ON C.TABSCHEMA = CH.TABSCHEMA AND C.TABNAME = CH.TABNAME AND CH.TYPE = 'C' AND CH.CONSTNAME = CC.CONSTNAME
352	WHERE
353	C.TABSCHEMA = %s AND
354	C.TABNAME = %s
355	ORDER BY
356	C.COLNO ASC";
357
358	$result = $this->database->query($sql, strtoupper($schema), strtoupper($table));
359
360	foreach ($result as $row) {
361
362	$info = array();
363
364	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
365	if (stripos($row['type'], $data_type) === 0) {
366	if (isset($max_min_values[$data_type])) {
367	$info['min_value'] = $max_min_values[$data_type]['min'];
368	$info['max_value'] = $max_min_values[$data_type]['max'];
369	}
370	$info['type'] = $mapped_data_type;
371	break;
372	}
373	}
374
375	// Handle decimal places and min/max for numeric/decimals
376	if (in_array(strtolower($row['type']), array('decimal', 'numeric'))) {
377	$info['decimal_places'] = $row['scale'];
378	$before_digits = str_pad('', $row['max_length'] - $row['scale'], '9');
379	$after_digits = str_pad('', $row['scale'], '9');
380	$max_min = $before_digits . ($after_digits ? '.' : '') . $after_digits;
381	$info['min_value'] = new fNumber('-' . $max_min);
382	$info['max_value'] = new fNumber($max_min);
383	}
384
385	if (!isset($info['type'])) {
386	$info['type'] = $row['type'];
387	}
388
389	// Handle the special data for varchar columns
390	if (in_array($info['type'], array('char', 'varchar', 'text', 'blob'))) {
391	$info['max_length'] = $row['max_length'];
392	}
393
394	// The generally accepted practice for boolean on DB2 is a CHAR(1) with a CHECK constraint
395	if ($info['type'] == 'char' && $info['max_length'] == 1 && !empty($row['constraint'])) {
396	if (is_resource($row['constraint'])) {
397	$row['constraint'] = stream_get_contents($row['constraint']);
398	}
399	if (preg_match('/^\s' . preg_quote($row['column'], '/') . '\s+in\s+$\s(\'0\',\s\'1\'\|\'1\',\s\'0\')\s$\s$/i', $row['constraint'])) {
400	$info['type'] = 'boolean';
401	$info['max_length'] = NULL;
402	}
403	}
404
405	// If the column has a constraint, look for valid values
406	if (in_array($info['type'], array('char', 'varchar')) && !empty($row['constraint'])) {
407	if (preg_match('/^\s' . preg_quote($row['column'], '/') . '\s+in\s+$(.?)$\s*$/i', $row['constraint'], $match)) {
408	if (preg_match_all("/(?<!')'((''\|[^']+)*)'/", $match[1], $matches, PREG_PATTERN_ORDER)) {
409	$info['valid_values'] = str_replace("''", "'", $matches[1]);
410	}
411	}
412	}
413
414	// Handle auto increment
415	if ($row['auto_increment']) {
416	$info['auto_increment'] = TRUE;
417	}
418
419	// Handle default values
420	if ($row['default'] !== NULL) {
421	if ($row['default'] == 'NULL') {
422	$info['default'] = NULL;
423	} elseif (in_array($info['type'], array('char', 'varchar', 'text', 'timestamp')) ) {
424	$info['default'] = substr($row['default'], 1, -1);
425	} elseif ($info['type'] == 'boolean') {
426	$info['default'] = (boolean) substr($row['default'], 1, -1);
427	} else {
428	$info['default'] = $row['default'];
429	}
430	}
431
432	// Handle not null
433	$info['not_null'] = ($row['nullable'] == 'N') ? TRUE : FALSE;
434
435	$column_info[$row['column']] = $info;
436	}
437
438	return $column_info;
439	}
440
441
442	/**
443	* Fetches the key info for a DB2 database
444	*
445	* @return array The keys arrays for every table in the database - see ::getKeys() for details
446	*/
447	private function fetchDB2Keys()
448	{
449	$keys = array();
450
451	$default_schema = strtolower($this->database->getUsername());
452
453	$tables = $this->getTables();
454	foreach ($tables as $table) {
455	$keys[$table] = array();
456	$keys[$table]['primary'] = array();
457	$keys[$table]['unique'] = array();
458	$keys[$table]['foreign'] = array();
459	}
460
461	$params = array();
462
463	$sql = "(SELECT
464	LOWER(RTRIM(R.TABSCHEMA)) AS \"SCHEMA\",
465	LOWER(R.TABNAME) AS \"TABLE\",
466	R.CONSTNAME AS CONSTRAINT_NAME,
467	'foreign' AS \"TYPE\",
468	LOWER(K.COLNAME) AS \"COLUMN\",
469	LOWER(RTRIM(R.REFTABSCHEMA)) AS FOREIGN_SCHEMA,
470	LOWER(R.REFTABNAME) AS FOREIGN_TABLE,
471	LOWER(FK.COLNAME) AS FOREIGN_COLUMN,
472	CASE R.DELETERULE WHEN 'C' THEN 'cascade' WHEN 'A' THEN 'no_action' WHEN 'R' THEN 'restrict' ELSE 'set_null' END AS ON_DELETE,
473	CASE R.UPDATERULE WHEN 'A' THEN 'no_action' WHEN 'R' THEN 'restrict' END AS ON_UPDATE,
474	K.COLSEQ
475	FROM
476	SYSCAT.REFERENCES AS R INNER JOIN
477	SYSCAT.KEYCOLUSE AS K ON R.CONSTNAME = K.CONSTNAME AND R.TABSCHEMA = K.TABSCHEMA AND R.TABNAME = K.TABNAME INNER JOIN
478	SYSCAT.KEYCOLUSE AS FK ON R.REFKEYNAME = FK.CONSTNAME AND R.REFTABSCHEMA = FK.TABSCHEMA AND R.REFTABNAME = FK.TABNAME
479	WHERE ";
480
481	$conditions = array();
482	foreach ($tables as $table) {
483	if (strpos($table, '.') === FALSE) {
484	$table = $default_schema . '.' . $table;
485	}
486	list ($schema, $table) = explode('.', strtoupper($table));
487	$conditions[] = "R.TABSCHEMA = %s AND R.TABNAME = %s";
488	$params[] = $schema;
489	$params[] = $table;
490	}
491	$sql .= '((' . join(') OR( ', $conditions) . '))';
492
493	$sql .= "
494	) UNION (
495	SELECT
496	LOWER(RTRIM(I.TABSCHEMA)) AS \"SCHEMA\",
497	LOWER(I.TABNAME) AS \"TABLE\",
498	LOWER(I.INDNAME) AS CONSTRAINT_NAME,
499	CASE I.UNIQUERULE WHEN 'U' THEN 'unique' ELSE 'primary' END AS \"TYPE\",
500	LOWER(C.COLNAME) AS \"COLUMN\",
501	NULL AS FOREIGN_SCHEMA,
502	NULL AS FOREIGN_TABLE,
503	NULL AS FOREIGN_COLUMN,
504	NULL AS ON_DELETE,
505	NULL AS ON_UPDATE,
506	C.COLSEQ
507	FROM
508	SYSCAT.INDEXES AS I INNER JOIN
509	SYSCAT.INDEXCOLUSE AS C ON I.INDSCHEMA = C.INDSCHEMA AND I.INDNAME = C.INDNAME
510	WHERE
511	I.UNIQUERULE IN ('U', 'P') AND
512	";
513
514	$conditions = array();
515	foreach ($tables as $table) {
516	if (strpos($table, '.') === FALSE) {
517	$table = $default_schema . '.' . $table;
518	}
519	list ($schema, $table) = explode('.', strtoupper($table));
520	$conditions[] = "I.TABSCHEMA = %s AND I.TABNAME = %s";
521	$params[] = $schema;
522	$params[] = $table;
523	}
524	$sql .= '((' . join(') OR( ', $conditions) . '))';
525
526	$sql .= "
527	)
528	ORDER BY 4, 1, 2, 3, 11";
529
530	$result = $this->database->query($sql, $params);
531
532	$last_name = '';
533	$last_table = '';
534	$last_type = '';
535	foreach ($result as $row) {
536
537	if ($row['constraint_name'] != $last_name) {
538
539	if ($last_name) {
540	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
541	$keys[$last_table][$last_type][] = $temp;
542	} else {
543	$keys[$last_table][$last_type] = $temp;
544	}
545	}
546
547	$temp = array();
548	if ($row['type'] == 'foreign') {
549
550	$temp['column'] = $row['column'];
551	$temp['foreign_table'] = $row['foreign_table'];
552	if ($row['foreign_schema'] != $default_schema) {
553	$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];
554	}
555	$temp['foreign_column'] = $row['foreign_column'];
556	$temp['on_delete'] = 'no_action';
557	$temp['on_update'] = 'no_action';
558
559	if (!empty($row['on_delete'])) {
560	$temp['on_delete'] = $row['on_delete'];
561	}
562	if (!empty($row['on_update'])) {
563	$temp['on_update'] = $row['on_update'];
564	}
565
566	} else {
567	$temp[] = $row['column'];
568	}
569
570	$last_table = $row['table'];
571	if ($row['schema'] != $default_schema) {
572	$last_table = $row['schema'] . '.' . $last_table;
573	}
574	$last_name = $row['constraint_name'];
575	$last_type = $row['type'];
576
577	} else {
578	$temp[] = $row['column'];
579	}
580	}
581
582	if (isset($temp)) {
583	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
584	$keys[$last_table][$last_type][] = $temp;
585	} else {
586	$keys[$last_table][$last_type] = $temp;
587	}
588	}
589
590	return $keys;
591	}
592
593
594	/**
595	* Gets the `PRIMARY KEY`, `FOREIGN KEY` and `UNIQUE` key constraints from the database
596	*
597	* @return void
598	*/
599	private function fetchKeys()
600	{
601	if ($this->keys) {
602	return;
603	}
604
605	switch ($this->database->getType()) {
606	case 'db2':
607	$keys = $this->fetchDB2Keys();
608	break;
609
610	case 'mssql':
611	$keys = $this->fetchMSSQLKeys();
612	break;
613
614	case 'mysql':
615	$keys = $this->fetchMySQLKeys();
616	break;
617
618	case 'oracle':
619	$keys = $this->fetchOracleKeys();
620	break;
621
622	case 'postgresql':
623	$keys = $this->fetchPostgreSQLKeys();
624	break;
625
626	case 'sqlite':
627	$keys = $this->fetchSQLiteKeys();
628	break;
629	}
630
631	$this->keys = $keys;
632	if ($this->cache) {
633	$this->cache->set($this->makeCachePrefix() . 'keys', $this->keys);
634	}
635	}
636
637
638	/**
639	* Gets the column info from a MSSQL database
640	*
641	* @param string $table The table to fetch the column info for
642	* @return array The column info for the table specified - see ::getColumnInfo() for details
643	*/
644	private function fetchMSSQLColumnInfo($table)
645	{
646	$column_info = array();
647
648	$schema = 'dbo';
649	if (strpos($table, '.') !== FALSE) {
650	list ($schema, $table) = explode('.', $table);
651	}
652
653	$data_type_mapping = array(
654	'bit' => 'boolean',
655	'tinyint' => 'integer',
656	'smallint' => 'integer',
657	'int' => 'integer',
658	'bigint' => 'integer',
659	'timestamp' => 'integer',
660	'datetime' => 'timestamp',
661	'smalldatetime' => 'timestamp',
662	'datetime2' => 'timestamp',
663	'date' => 'date',
664	'time' => 'time',
665	'varchar' => 'varchar',
666	'nvarchar' => 'varchar',
667	'uniqueidentifier' => 'varchar',
668	'char' => 'char',
669	'nchar' => 'char',
670	'real' => 'float',
671	'float' => 'float',
672	'money' => 'float',
673	'smallmoney' => 'float',
674	'decimal' => 'float',
675	'numeric' => 'float',
676	'binary' => 'blob',
677	'varbinary' => 'blob',
678	'image' => 'blob',
679	'text' => 'text',
680	'ntext' => 'text',
681	'xml' => 'text'
682	);
683
684	$max_min_values = array(
685	'tinyint' => array('min' => new fNumber(0), 'max' => new fNumber(255)),
686	'smallint' => array('min' => new fNumber(-32768), 'max' => new fNumber(32767)),
687	'int' => array('min' => new fNumber(-2147483648), 'max' => new fNumber(2147483647)),
688	'bigint' => array('min' => new fNumber('-9223372036854775808'), 'max' => new fNumber('9223372036854775807')),
689	'smallmoney' => array('min' => new fNumber('-214748.3648'), 'max' => new fNumber('214748.3647')),
690	'money' => array('min' => new fNumber('-922337203685477.5808'), 'max' => new fNumber('922337203685477.5807'))
691	);
692
693	// Get the column info
694	$sql = "SELECT
695	c.column_name AS 'column',
696	c.data_type AS 'type',
697	c.is_nullable AS nullable,
698	c.column_default AS 'default',
699	c.character_maximum_length AS max_length,
700	c.numeric_precision AS precision,
701	c.numeric_scale AS decimal_places,
702	CASE
703	WHEN
704	COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND
705	OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0
706	THEN '1'
707	ELSE '0'
708	END AS auto_increment,
709	cc.check_clause AS 'constraint'
710	FROM
711	INFORMATION_SCHEMA.COLUMNS AS c LEFT JOIN
712	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON c.column_name = ccu.column_name AND c.table_name = ccu.table_name AND c.table_catalog = ccu.table_catalog LEFT JOIN
713	INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON ccu.constraint_name = cc.constraint_name AND ccu.constraint_catalog = cc.constraint_catalog
714	WHERE
715	c.table_name = %s AND
716	c.table_schema = %s AND
717	c.table_catalog = DB_NAME()";
718
719	$result = $this->database->query($sql, $table, $schema);
720
721	foreach ($result as $row) {
722
723	$info = array();
724
725	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
726	if (stripos($row['type'], $data_type) === 0) {
727	if (isset($max_min_values[$data_type])) {
728	$info['min_value'] = $max_min_values[$data_type]['min'];
729	$info['max_value'] = $max_min_values[$data_type]['max'];
730	}
731	$info['type'] = $mapped_data_type;
732	break;
733	}
734	}
735
736	// Handle decimal places and min/max for numeric/decimals
737	if (in_array(strtolower($row['type']), array('decimal', 'numeric'))) {
738	$info['decimal_places'] = $row['decimal_places'];
739	$before_digits = str_pad('', $row['precision'] - $row['decimal_places'], '9');
740	$after_digits = str_pad('', $row['decimal_places'], '9');
741	$max_min = $before_digits . ($after_digits ? '.' : '') . $after_digits;
742	$info['min_value'] = new fNumber('-' . $max_min);
743	$info['max_value'] = new fNumber($max_min);
744	}
745
746	if (!isset($info['type'])) {
747	$info['type'] = $row['type'];
748	}
749
750	// Handle decimal places for money/smallmoney
751	if (in_array($row['type'], array('money', 'smallmoney'))) {
752	$info['decimal_places'] = 2;
753	}
754
755	// Handle the special data for varchar columns
756	if (in_array($info['type'], array('char', 'varchar', 'text', 'blob'))) {
757	if ($row['type'] == 'uniqueidentifier') {
758	$row['max_length'] = 32;
759	} elseif ($row['max_length'] == -1) {
760	$row['max_length'] = $row['type'] == 'nvarchar' ? 1073741823 : 2147483647;
761	}
762	$info['max_length'] = $row['max_length'];
763	}
764
765	// If the column has a constraint, look for valid values
766	if (in_array($info['type'], array('char', 'varchar')) && !empty($row['constraint'])) {
767	if (preg_match('#^$((?:(?: OR )?\[[^\]]+\]\s=\s\'(?:\'\'\|[^\']+)+\')+)$$#D', $row['constraint'], $matches)) {
768	$valid_values = explode(' OR ', $matches[1]);
769	foreach ($valid_values as $key => $value) {
770	$value = preg_replace('#^\s\[' . preg_quote($row['column'], '#') . '\]\s=\s\'(.)\'\s*$#', '\1', $value);
771	$valid_values[$key] = str_replace("''", "'", $value);
772	}
773	// SQL Server turns CHECK constraint values into a reversed list, so we fix it here
774	$info['valid_values'] = array_reverse($valid_values);
775	}
776	}
777
778	// Handle auto increment
779	if ($row['auto_increment']) {
780	$info['auto_increment'] = TRUE;
781	}
782
783	// Handle default values
784	if ($row['default'] !== NULL) {
785	if ($row['default'] == '(getdate())') {
786	$info['default'] = 'CURRENT_TIMESTAMP';
787	} elseif (in_array($info['type'], array('char', 'varchar', 'text', 'timestamp')) ) {
788	$info['default'] = substr($row['default'], 2, -2);
789	} elseif ($info['type'] == 'boolean') {
790	$info['default'] = (boolean) substr($row['default'], 2, -2);
791	} elseif (in_array($info['type'], array('integer', 'float')) ) {
792	$info['default'] = str_replace(array('(', ')'), '', $row['default']);
793	} else {
794	$info['default'] = pack('H*', substr($row['default'], 3, -1));
795	}
796	}
797
798	// Handle not null
799	$info['not_null'] = ($row['nullable'] == 'NO') ? TRUE : FALSE;
800
801	$column_info[$row['column']] = $info;
802	}
803
804	return $column_info;
805	}
806
807
808	/**
809	* Fetches the key info for an MSSQL database
810	*
811	* @return array The key info arrays for every table in the database - see ::getKeys() for details
812	*/
813	private function fetchMSSQLKeys()
814	{
815	$keys = array();
816
817	$tables = $this->getTables();
818	foreach ($tables as $table) {
819	$keys[$table] = array();
820	$keys[$table]['primary'] = array();
821	$keys[$table]['unique'] = array();
822	$keys[$table]['foreign'] = array();
823	}
824
825	$sql = "SELECT
826	c.table_schema AS \"schema\",
827	c.table_name AS \"table\",
828	kcu.constraint_name AS constraint_name,
829	CASE c.constraint_type
830	WHEN 'PRIMARY KEY' THEN 'primary'
831	WHEN 'FOREIGN KEY' THEN 'foreign'
832	WHEN 'UNIQUE' THEN 'unique'
833	END AS 'type',
834	kcu.column_name AS 'column',
835	ccu.table_schema AS foreign_schema,
836	ccu.table_name AS foreign_table,
837	ccu.column_name AS foreign_column,
838	REPLACE(LOWER(rc.delete_rule), ' ', '_') AS on_delete,
839	REPLACE(LOWER(rc.update_rule), ' ', '_') AS on_update
840	FROM
841	INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN
842	INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON c.table_name = kcu.table_name AND c.constraint_name = kcu.constraint_name LEFT JOIN
843	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON c.constraint_name = rc.constraint_name LEFT JOIN
844	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.constraint_name = rc.unique_constraint_name
845	WHERE
846	c.constraint_catalog = DB_NAME() AND
847	c.table_name != 'sysdiagrams'
848	ORDER BY
849	LOWER(c.table_schema),
850	LOWER(c.table_name),
851	c.constraint_type,
852	LOWER(kcu.constraint_name),
853	kcu.ordinal_position,
854	LOWER(kcu.column_name)";
855
856	$result = $this->database->query($sql);
857
858	$last_name = '';
859	$last_table = '';
860	$last_type = '';
861	foreach ($result as $row) {
862
863	if ($row['constraint_name'] != $last_name) {
864
865	if ($last_name) {
866	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
867	if (!isset($keys[$last_table][$last_type])) {
868	$keys[$last_table][$last_type] = array();
869	}
870	$keys[$last_table][$last_type][] = $temp;
871	} else {
872	$keys[$last_table][$last_type] = $temp;
873	}
874	}
875
876	$temp = array();
877	if ($row['type'] == 'foreign') {
878
879	$temp['column'] = $row['column'];
880	$temp['foreign_table'] = $row['foreign_table'];
881	if ($row['foreign_schema'] != 'dbo') {
882	$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];
883	}
884	$temp['foreign_column'] = $row['foreign_column'];
885	$temp['on_delete'] = 'no_action';
886	$temp['on_update'] = 'no_action';
887	if (!empty($row['on_delete'])) {
888	$temp['on_delete'] = $row['on_delete'];
889	}
890	if (!empty($row['on_update'])) {
891	$temp['on_update'] = $row['on_update'];
892	}
893
894	} else {
895	$temp[] = $row['column'];
896	}
897
898	$last_table = $row['table'];
899	if ($row['schema'] != 'dbo') {
900	$last_table = $row['schema'] . '.' . $last_table;
901	}
902	$last_name = $row['constraint_name'];
903	$last_type = $row['type'];
904
905	} else {
906	$temp[] = $row['column'];
907	}
908	}
909
910	if (isset($temp)) {
911	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
912	if (!isset($keys[$last_table][$last_type])) {
913	$keys[$last_table][$last_type] = array();
914	}
915	$keys[$last_table][$last_type][] = $temp;
916	} else {
917	$keys[$last_table][$last_type] = $temp;
918	}
919	}
920
921	return $keys;
922	}
923
924
925	/**
926	* Gets the column info from a MySQL database
927	*
928	* @param string $table The table to fetch the column info for
929	* @return array The column info for the table specified - see ::getColumnInfo() for details
930	*/
931	private function fetchMySQLColumnInfo($table)
932	{
933	$data_type_mapping = array(
934	'tinyint' => 'integer',
935	'smallint' => 'integer',
936	'mediumint' => 'integer',
937	'int' => 'integer',
938	'bigint' => 'integer',
939	'datetime' => 'timestamp',
940	'timestamp' => 'timestamp',
941	'date' => 'date',
942	'time' => 'time',
943	'enum' => 'varchar',
944	'set' => 'varchar',
945	'varchar' => 'varchar',
946	'char' => 'char',
947	'float' => 'float',
948	'double' => 'float',
949	'decimal' => 'float',
950	'binary' => 'blob',
951	'varbinary' => 'blob',
952	'tinyblob' => 'blob',
953	'blob' => 'blob',
954	'mediumblob' => 'blob',
955	'longblob' => 'blob',
956	'tinytext' => 'text',
957	'text' => 'text',
958	'mediumtext' => 'text',
959	'longtext' => 'text'
960	);
961
962	$max_min_values = array(
963	'tinyint' => array('min' => new fNumber(-128), 'max' => new fNumber(127)),
964	'unsigned tinyint' => array('min' => new fNumber(0), 'max' => new fNumber(255)),
965	'smallint' => array('min' => new fNumber(-32768), 'max' => new fNumber(32767)),
966	'unsigned smallint' => array('min' => new fNumber(0), 'max' => new fNumber(65535)),
967	'mediumint' => array('min' => new fNumber(-8388608), 'max' => new fNumber(8388607)),
968	'unsigned mediumint' => array('min' => new fNumber(0), 'max' => new fNumber(16777215)),
969	'int' => array('min' => new fNumber(-2147483648), 'max' => new fNumber(2147483647)),
970	'unsigned int' => array('min' => new fNumber(0), 'max' => new fNumber('4294967295')),
971	'bigint' => array('min' => new fNumber('-9223372036854775808'), 'max' => new fNumber('9223372036854775807')),
972	'unsigned bigint' => array('min' => new fNumber(0), 'max' => new fNumber('18446744073709551615'))
973	);
974
975	$column_info = array();
976
977	$result = $this->database->query('SHOW CREATE TABLE ' . $table);
978
979	try {
980	$row = $result->fetchRow();
981	$create_sql = $row['Create Table'];
982	} catch (fNoRowsException $e) {
983	return array();
984	}
985
986	preg_match_all('#(?<=,\|$)\s+(?:"\|\`)(\w+)(?:"\|\`)\s+(?:([a-z]+)(?:\(([^)]+)$)?( unsigned)?(?: zerofill)?)(?: character set [^ ]+)?(?: collate [^ ]+)?(?: NULL)?( NOT NULL)?(?: DEFAULT ((?:[^, \']\|\'(?:\'\'\|[^\']+)\')))?( auto_increment)?( COMMENT \'(?:\'\'\|[^\']+)\')?( ON UPDATE CURRENT_TIMESTAMP)?\s(?:,\|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
987
988	foreach ($matches as $match) {
989
990	$info = array();
991
992	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
993	if (stripos($match[2], $data_type) === 0) {
994	if ($match[2] == 'tinyint' && $match[3] == 1) {
995	$mapped_data_type = 'boolean';
996
997	} elseif (preg_match('#((?:unsigned )?(?:tiny\|small\|medium\|big)?int)#', (isset($match[4]) ? $match[4] . ' ' : '') . $data_type, $int_match)) {
998	if (isset($max_min_values[$int_match[1]])) {
999	$info['min_value'] = $max_min_values[$int_match[1]]['min'];
1000	$info['max_value'] = $max_min_values[$int_match[1]]['max'];
1001	}
1002	}
1003
1004	$info['type'] = $mapped_data_type;
1005	break;
1006	}
1007	}
1008	if (!isset($info['type'])) {
1009	$info['type'] = preg_replace('#^([a-z ]+).*$#iD', '\1', $match[2]);
1010	}
1011
1012	switch ($match[2]) {
1013	case 'tinyblob':
1014	case 'tinytext':
1015	$info['max_length'] = 255;
1016	break;
1017
1018	case 'blob':
1019	case 'text':
1020	$info['max_length'] = 65535;
1021	break;
1022
1023	case 'mediumblob':
1024	case 'mediumtext':
1025	$info['max_length'] = 16777215;
1026	break;
1027
1028	case 'longblob':
1029	case 'longtext':
1030	$info['max_length'] = 4294967295;
1031	break;
1032	}
1033
1034	if (stripos($match[2], 'enum') === 0) {
1035	$info['valid_values'] = preg_replace("/^'\|'\$/D", '', explode(",", $match[3]));
1036	$match[3] = 0;
1037	foreach ($info['valid_values'] as $valid_value) {
1038	if (strlen(utf8_decode($valid_value)) > $match[3]) {
1039	$match[3] = strlen(utf8_decode($valid_value));
1040	}
1041	}
1042	}
1043
1044	// The set data type is currently only supported as a varchar
1045	// with a max length of all valid values concatenated by ,s
1046	if (stripos($match[2], 'set') === 0) {
1047	$values = preg_replace("/^'\|'\$/D", '', explode(",", $match[3]));
1048	$match[3] = strlen(join(',', $values));
1049	}
1050
1051	// Type specific information
1052	if (in_array($info['type'], array('char', 'varchar'))) {
1053	$info['max_length'] = $match[3];
1054	}
1055
1056	// Grab the number of decimal places
1057	if (stripos($match[2], 'decimal') === 0) {
1058	if (preg_match('#^\s(\d+)\s,\s(\d+)\s$#D', $match[3], $data_type_info)) {
1059	$info['decimal_places'] = $data_type_info[2];
1060	$before_digits = str_pad('', $data_type_info[1] - $info['decimal_places'], '9');
1061	$after_digits = str_pad('', $info['decimal_places'], '9');
1062	$max_min = $before_digits . ($after_digits ? '.' : '') . $after_digits;
1063	$info['min_value'] = new fNumber('-' . $max_min);
1064	$info['max_value'] = new fNumber($max_min);
1065	}
1066	}
1067
1068	// Not null
1069	$info['not_null'] = (!empty($match[5])) ? TRUE : FALSE;
1070
1071	// Default values
1072	if (!empty($match[6]) && $match[6] != 'NULL') {
1073	$info['default'] = preg_replace("/^'\|'\$/D", '', $match[6]);
1074	}
1075
1076	if ($info['type'] == 'boolean' && isset($info['default'])) {
1077	$info['default'] = (boolean) $info['default'];
1078	}
1079
1080	// Auto increment fields
1081	if (!empty($match[7])) {
1082	$info['auto_increment'] = TRUE;
1083	}
1084
1085	$column_info[$match[1]] = $info;
1086	}
1087
1088	return $column_info;
1089	}
1090
1091
1092	/**
1093	* Fetches the keys for a MySQL database
1094	*
1095	* @return array The keys arrays for every table in the database - see ::getKeys() for details
1096	*/
1097	private function fetchMySQLKeys()
1098	{
1099	$tables = $this->getTables();
1100	$keys = array();
1101
1102	foreach ($tables as $table) {
1103
1104	$keys[$table] = array();
1105	$keys[$table]['primary'] = array();
1106	$keys[$table]['foreign'] = array();
1107	$keys[$table]['unique'] = array();
1108
1109	$result = $this->database->query('SHOW CREATE TABLE `' . substr($this->database->escape('string', $table), 1, -1) . '`');
1110	$row = $result->fetchRow();
1111
1112	// Primary keys
1113	preg_match_all('/PRIMARY KEY\s+$"(.*?)"$,?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);
1114	if (!empty($matches)) {
1115	$keys[$table]['primary'] = explode('","', $matches[0][1]);
1116	}
1117
1118	// Unique keys
1119	preg_match_all('/UNIQUE KEY\s+"([^"]+)"\s+$"(.*?)"$,?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);
1120	foreach ($matches as $match) {
1121	$keys[$table]['unique'][] = explode('","', $match[2]);
1122	}
1123
1124	// Foreign keys
1125	preg_match_all('#FOREIGN KEY $"([^"]+)"$ REFERENCES "([^"]+)" $"([^"]+)"$(?:\sON\sDELETE\s(SET\sNULL\|SET\sDEFAULT\|CASCADE\|NO\sACTION\|RESTRICT))?(?:\sON\sUPDATE\s(SET\sNULL\|SET\sDEFAULT\|CASCADE\|NO\sACTION\|RESTRICT))?#', $row['Create Table'], $matches, PREG_SET_ORDER);
1126	foreach ($matches as $match) {
1127	$temp = array('column' => $match[1],
1128	'foreign_table' => $match[2],
1129	'foreign_column' => $match[3],
1130	'on_delete' => 'no_action',
1131	'on_update' => 'no_action');
1132	if (isset($match[4])) {
1133	$temp['on_delete'] = strtolower(str_replace(' ', '_', $match[4]));
1134	}
1135	if (isset($match[5])) {
1136	$temp['on_update'] = strtolower(str_replace(' ', '_', $match[5]));
1137	}
1138	$keys[$table]['foreign'][] = $temp;
1139	}
1140	}
1141
1142	return $keys;
1143	}
1144
1145
1146	/**
1147	* Gets the column info from an Oracle database
1148	*
1149	* @param string $table The table to fetch the column info for
1150	* @return array The column info for the table specified - see ::getColumnInfo() for details
1151	*/
1152	private function fetchOracleColumnInfo($table)
1153	{
1154	$table = strtoupper($table);
1155
1156	$schema = strtoupper($this->database->getUsername());
1157	if (strpos($table, '.') !== FALSE) {
1158	list ($schema, $table) = explode('.', $table);
1159	}
1160
1161	$column_info = array();
1162
1163	$data_type_mapping = array(
1164	'boolean' => 'boolean',
1165	'number' => 'integer',
1166	'integer' => 'integer',
1167	'timestamp' => 'timestamp',
1168	'date' => 'date',
1169	'varchar2' => 'varchar',
1170	'nvarchar2' => 'varchar',
1171	'char' => 'char',
1172	'nchar' => 'char',
1173	'float' => 'float',
1174	'binary_float' => 'float',
1175	'binary_double' => 'float',
1176	'blob' => 'blob',
1177	'bfile' => 'varchar',
1178	'clob' => 'text',
1179	'nclob' => 'text'
1180	);
1181
1182	$sql = "SELECT
1183	LOWER(ATC.COLUMN_NAME) COLUMN_NAME,
1184	CASE
1185	WHEN
1186	ATC.DATA_TYPE = 'NUMBER' AND
1187	ATC.DATA_PRECISION IS NULL AND
1188	ATC.DATA_SCALE = 0
1189	THEN
1190	'integer'
1191	WHEN
1192	ATC.DATA_TYPE = 'NUMBER' AND
1193	ATC.DATA_PRECISION = 1 AND
1194	ATC.DATA_SCALE = 0
1195	THEN
1196	'boolean'
1197	WHEN
1198	ATC.DATA_TYPE = 'NUMBER' AND
1199	ATC.DATA_PRECISION IS NOT NULL AND
1200	ATC.DATA_SCALE != 0 AND
1201	ATC.DATA_SCALE IS NOT NULL
1202	THEN
1203	'float'
1204	ELSE
1205	LOWER(ATC.DATA_TYPE)
1206	END DATA_TYPE,
1207	CASE
1208	WHEN
1209	ATC.CHAR_LENGTH <> 0
1210	THEN
1211	ATC.CHAR_LENGTH
1212	WHEN
1213	ATC.DATA_TYPE = 'NUMBER' AND
1214	ATC.DATA_PRECISION != 1 AND
1215	ATC.DATA_SCALE != 0 AND
1216	ATC.DATA_PRECISION IS NOT NULL
1217	THEN
1218	ATC.DATA_SCALE
1219	ELSE
1220	NULL
1221	END LENGTH,
1222	ATC.DATA_PRECISION PRECISION,
1223	ATC.NULLABLE,
1224	ATC.DATA_DEFAULT,
1225	AC.SEARCH_CONDITION CHECK_CONSTRAINT
1226	FROM
1227	ALL_TAB_COLUMNS ATC LEFT JOIN
1228	ALL_CONS_COLUMNS ACC ON
1229	ATC.OWNER = ACC.OWNER AND
1230	ATC.COLUMN_NAME = ACC.COLUMN_NAME AND
1231	ATC.TABLE_NAME = ACC.TABLE_NAME AND
1232	ACC.POSITION IS NULL LEFT JOIN
1233	ALL_CONSTRAINTS AC ON
1234	AC.OWNER = ACC.OWNER AND
1235	AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME AND
1236	AC.CONSTRAINT_TYPE = 'C' AND
1237	AC.STATUS = 'ENABLED'
1238	WHERE
1239	ATC.TABLE_NAME = %s AND
1240	ATC.OWNER = %s
1241	ORDER BY
1242	ATC.TABLE_NAME ASC,
1243	ATC.COLUMN_ID ASC";
1244
1245	$result = $this->database->query($sql, $table, $schema);
1246
1247	foreach ($result as $row) {
1248
1249	$column = $row['column_name'];
1250
1251	// Since Oracle stores check constraints in LONG columns, it is
1252	// not possible to check or modify the constraints in SQL which
1253	// ends up causing multiple rows with duplicate data except for
1254	// the check constraint
1255	$duplicate = FALSE;
1256
1257	if (isset($column_info[$column])) {
1258	$info = $column_info[$column];
1259	$duplicate = TRUE;
1260	} else {
1261	$info = array();
1262	}
1263
1264	if (!$duplicate) {
1265	// Get the column type
1266	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
1267	if (stripos($row['data_type'], $data_type) === 0) {
1268	$info['type'] = $mapped_data_type;
1269	break;
1270	}
1271	}
1272
1273	if (!isset($info['type'])) {
1274	$info['type'] = $row['data_type'];
1275	}
1276
1277	if (in_array($info['type'], array('blob', 'text'))) {
1278	$info['max_length'] = 4294967295;
1279	}
1280
1281	if ($row['data_type'] == 'float' && $row['precision']) {
1282	$row['length'] = (int) $row['length'];
1283	$before_digits = str_pad('', $row['precision'] - $row['length'], '9');
1284	$after_digits = str_pad('', $row['length'], '9');
1285	$max_min = $before_digits . ($after_digits ? '.' : '') . $after_digits;
1286	$info['min_value'] = new fNumber('-' . $max_min);
1287	$info['max_value'] = new fNumber($max_min);
1288	}
1289
1290	// Handle the length of decimal/numeric fields
1291	if ($info['type'] == 'float' && $row['length']) {
1292	$info['decimal_places'] = (int) $row['length'];
1293	}
1294
1295	// Handle the special data for varchar fields
1296	if (in_array($info['type'], array('char', 'varchar'))) {
1297	$info['max_length'] = (int) $row['length'];
1298	}
1299	}
1300
1301	// Handle check constraints that are just simple lists
1302	if (in_array($info['type'], array('varchar', 'char')) && $row['check_constraint']) {
1303	if (preg_match('/^\s' . preg_quote($column, '/') . '\s+in\s+$(.?)$\s*$/i', $row['check_constraint'], $match)) {
1304	if (preg_match_all("/(?<!')'((''\|[^']+)*)'/", $match[1], $matches, PREG_PATTERN_ORDER)) {
1305	$info['valid_values'] = str_replace("''", "'", $matches[1]);
1306	}
1307	}
1308	}
1309
1310	if (!$duplicate) {
1311	// Handle default values
1312	if ($row['data_default'] !== NULL) {
1313	if (in_array($info['type'], array('char', 'varchar', 'text'))) {
1314	$info['default'] = str_replace("''", "'", substr(trim($row['data_default']), 1, -1));
1315
1316	} elseif ($info['type'] == 'boolean') {
1317	$info['default'] = (boolean) trim($row['data_default']);
1318
1319	} elseif (in_array($info['type'], array('integer', 'float'))) {
1320	$info['default'] = trim($row['data_default']);
1321
1322	} else {
1323	$info['default'] = $row['data_default'];
1324	}
1325	}
1326
1327	// Not null values
1328	$info['not_null'] = ($row['nullable'] == 'N') ? TRUE : FALSE;
1329	}
1330
1331	$column_info[$column] = $info;
1332	}
1333
1334	$sql = "SELECT
1335	TRIGGER_BODY
1336	FROM
1337	ALL_TRIGGERS
1338	WHERE
1339	TRIGGERING_EVENT = 'INSERT' AND
1340	STATUS = 'ENABLED' AND
1341	TRIGGER_NAME NOT LIKE 'BIN\$%' AND
1342	TABLE_NAME = %s AND
1343	OWNER = %s";
1344
1345	foreach ($this->database->query($sql, $table, $schema) as $row) {
1346	if (preg_match('#SELECT\s+(["\w.]+).nextval\s+INTO\s+:new\.(\w+)\s+FROM\s+dual#i', $row['trigger_body'], $matches)) {
1347	$column = strtolower($matches[2]);
1348	$column_info[$column]['auto_increment'] = TRUE;
1349	}
1350	}
1351
1352	return $column_info;
1353	}
1354
1355
1356	/**
1357	* Fetches the key info for an Oracle database
1358	*
1359	* @return array The keys arrays for every table in the database - see ::getKeys() for details
1360	*/
1361	private function fetchOracleKeys()
1362	{
1363	$keys = array();
1364
1365	$default_schema = strtolower($this->database->getUsername());
1366
1367	$tables = $this->getTables();
1368	foreach ($tables as $table) {
1369	$keys[$table] = array();
1370	$keys[$table]['primary'] = array();
1371	$keys[$table]['unique'] = array();
1372	$keys[$table]['foreign'] = array();
1373	}
1374
1375	$params = array();
1376
1377	$sql = "SELECT
1378	LOWER(AC.OWNER) \"SCHEMA\",
1379	LOWER(AC.TABLE_NAME) \"TABLE\",
1380	AC.CONSTRAINT_NAME CONSTRAINT_NAME,
1381	CASE AC.CONSTRAINT_TYPE
1382	WHEN 'P' THEN 'primary'
1383	WHEN 'R' THEN 'foreign'
1384	WHEN 'U' THEN 'unique'
1385	END TYPE,
1386	LOWER(ACC.COLUMN_NAME) \"COLUMN\",
1387	LOWER(FKC.OWNER) FOREIGN_SCHEMA,
1388	LOWER(FKC.TABLE_NAME) FOREIGN_TABLE,
1389	LOWER(FKC.COLUMN_NAME) FOREIGN_COLUMN,
1390	CASE WHEN FKC.TABLE_NAME IS NOT NULL THEN REPLACE(LOWER(AC.DELETE_RULE), ' ', '_') ELSE NULL END ON_DELETE
1391	FROM
1392	ALL_CONSTRAINTS AC INNER JOIN
1393	ALL_CONS_COLUMNS ACC ON AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME AND AC.OWNER = ACC.OWNER LEFT JOIN
1394	ALL_CONSTRAINTS FK ON AC.R_CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND AC.OWNER = FK.OWNER LEFT JOIN
1395	ALL_CONS_COLUMNS FKC ON FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME AND FK.OWNER = FKC.OWNER
1396	WHERE
1397	AC.CONSTRAINT_TYPE IN ('U', 'P', 'R') AND ";
1398
1399	$conditions = array();
1400	foreach ($tables as $table) {
1401	if (strpos($table, '.') === FALSE) {
1402	$table = $default_schema . '.' . $table;
1403	}
1404	list ($schema, $table) = explode('.', strtoupper($table));
1405	$conditions[] = "AC.OWNER = %s AND AC.TABLE_NAME = %s";
1406	$params[] = $schema;
1407	$params[] = $table;
1408	}
1409	$sql .= '((' . join(') OR( ', $conditions) . '))';
1410
1411	$sql .= " ORDER BY
1412	AC.OWNER ASC,
1413	AC.TABLE_NAME ASC,
1414	AC.CONSTRAINT_TYPE ASC,
1415	AC.CONSTRAINT_NAME ASC,
1416	ACC.POSITION ASC";
1417
1418	$result = $this->database->query($sql, $params);
1419
1420	$last_name = '';
1421	$last_table = '';
1422	$last_type = '';
1423	foreach ($result as $row) {
1424
1425	if ($row['constraint_name'] != $last_name) {
1426
1427	if ($last_name) {
1428	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
1429	$keys[$last_table][$last_type][] = $temp;
1430	} else {
1431	$keys[$last_table][$last_type] = $temp;
1432	}
1433	}
1434
1435	$temp = array();
1436	if ($row['type'] == 'foreign') {
1437
1438	$temp['column'] = $row['column'];
1439	$temp['foreign_table'] = $row['foreign_table'];
1440	if ($row['foreign_schema'] != $default_schema) {
1441	$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];
1442	}
1443	$temp['foreign_column'] = $row['foreign_column'];
1444	$temp['on_delete'] = 'no_action';
1445	$temp['on_update'] = 'no_action';
1446
1447	if (!empty($row['on_delete'])) {
1448	$temp['on_delete'] = $row['on_delete'];
1449	}
1450
1451	} else {
1452	$temp[] = $row['column'];
1453	}
1454
1455	$last_table = $row['table'];
1456	if ($row['schema'] != $default_schema) {
1457	$last_table = $row['schema'] . '.' . $last_table;
1458	}
1459	$last_name = $row['constraint_name'];
1460	$last_type = $row['type'];
1461
1462	} else {
1463	$temp[] = $row['column'];
1464	}
1465	}
1466
1467	if (isset($temp)) {
1468	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
1469	$keys[$last_table][$last_type][] = $temp;
1470	} else {
1471	$keys[$last_table][$last_type] = $temp;
1472	}
1473	}
1474
1475	return $keys;
1476	}
1477
1478
1479	/**
1480	* Gets the column info from a PostgreSQL database
1481	*
1482	* @param string $table The table to fetch the column info for
1483	* @return array The column info for the table specified - see ::getColumnInfo() for details
1484	*/
1485	private function fetchPostgreSQLColumnInfo($table)
1486	{
1487	$column_info = array();
1488
1489	$schema = 'public';
1490	if (strpos($table, '.') !== FALSE) {
1491	list ($schema, $table) = explode('.', $table);
1492	}
1493
1494	$data_type_mapping = array(
1495	'boolean' => 'boolean',
1496	'smallint' => 'integer',
1497	'int' => 'integer',
1498	'bigint' => 'integer',
1499	'serial' => 'integer',
1500	'bigserial' => 'integer',
1501	'timestamp' => 'timestamp',
1502	'date' => 'date',
1503	'time' => 'time',
1504	'uuid' => 'varchar',
1505	'character varying' => 'varchar',
1506	'character' => 'char',
1507	'real' => 'float',
1508	'double' => 'float',
1509	'numeric' => 'float',
1510	'bytea' => 'blob',
1511	'text' => 'text',
1512	'mediumtext' => 'text',
1513	'longtext' => 'text'
1514	);
1515
1516	$max_min_values = array(
1517	'smallint' => array('min' => new fNumber(-32768), 'max' => new fNumber(32767)),
1518	'int' => array('min' => new fNumber(-2147483648), 'max' => new fNumber(2147483647)),
1519	'bigint' => array('min' => new fNumber('-9223372036854775808'), 'max' => new fNumber('9223372036854775807')),
1520	'serial' => array('min' => new fNumber(-2147483648), 'max' => new fNumber(2147483647)),
1521	'bigserial' => array('min' => new fNumber('-9223372036854775808'), 'max' => new fNumber('9223372036854775807'))
1522	);
1523
1524	// PgSQL required this complicated SQL to get the column info
1525	$sql = "SELECT
1526	pg_attribute.attname AS column,
1527	format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type,
1528	pg_attribute.attnotnull AS not_null,
1529	pg_attrdef.adsrc AS default,
1530	pg_get_constraintdef(pg_constraint.oid) AS constraint
1531	FROM
1532	pg_attribute LEFT JOIN
1533	pg_class ON pg_attribute.attrelid = pg_class.oid LEFT JOIN
1534	pg_namespace ON pg_class.relnamespace = pg_namespace.oid LEFT JOIN
1535	pg_type ON pg_type.oid = pg_attribute.atttypid LEFT JOIN
1536	pg_constraint ON pg_constraint.conrelid = pg_class.oid AND
1537	pg_attribute.attnum = ANY (pg_constraint.conkey) AND
1538	pg_constraint.contype = 'c' LEFT JOIN
1539	pg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND
1540	pg_attribute.attnum = pg_attrdef.adnum
1541	WHERE
1542	NOT pg_attribute.attisdropped AND
1543	pg_class.relname = %s AND
1544	pg_namespace.nspname = %s AND
1545	pg_type.typname NOT IN ('oid', 'cid', 'xid', 'cid', 'xid', 'tid')
1546	ORDER BY
1547	pg_attribute.attnum,
1548	pg_constraint.contype";
1549	$result = $this->database->query($sql, $table, $schema);
1550
1551	foreach ($result as $row) {
1552
1553	$info = array();
1554
1555	// Get the column type
1556	preg_match('#([\w ]+)\s(?:$\s(\d+)(?:\s,\s(\d+))?\s*$)?#', $row['data_type'], $column_data_type);
1557
1558	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
1559	if (stripos($column_data_type[1], $data_type) === 0) {
1560	$info['type'] = $mapped_data_type;
1561	if (isset($max_min_values[$data_type])) {
1562	$info['min_value'] = $max_min_values[$data_type]['min'];
1563	$info['max_value'] = $max_min_values[$data_type]['max'];
1564	}
1565	break;
1566	}
1567	}
1568
1569	if (!isset($info['type'])) {
1570	$info['type'] = $column_data_type[1];
1571	}
1572
1573	if ($info['type'] == 'blob' \|\| $info['type'] == 'text') {
1574	$info['max_length'] = 1073741824;
1575	}
1576
1577	// Handle the length of decimal/numeric fields
1578	if ($info['type'] == 'float' && isset($column_data_type[3]) && strlen($column_data_type[3]) > 0) {
1579	$info['decimal_places'] = (int) $column_data_type[3];
1580	$before_digits = str_pad('', $column_data_type[2] - $info['decimal_places'], '9');
1581	$after_digits = str_pad('', $info['decimal_places'], '9');
1582	$max_min = $before_digits . ($after_digits ? '.' : '') . $after_digits;
1583	$info['min_value'] = new fNumber('-' . $max_min);
1584	$info['max_value'] = new fNumber($max_min);
1585	}
1586
1587	// Handle the special data for varchar fields
1588	if (in_array($info['type'], array('char', 'varchar'))) {
1589	if (!empty($column_data_type[2])) {
1590	$info['max_length'] = $column_data_type[2];
1591	} else {
1592	$info['max_length'] = 1073741824;
1593	}
1594	}
1595
1596	// In PostgreSQL, a UUID can be the 32 digits, 32 digits plus 4 hyphens or 32 digits plus 4 hyphens and 2 curly braces
1597	if ($row['data_type'] == 'uuid') {
1598	$info['max_length'] = 38;
1599	}
1600
1601	// Handle check constraints that are just simple lists
1602	if (in_array($info['type'], array('varchar', 'char')) && !empty($row['constraint'])) {
1603	if (preg_match('/CHECK[$ "]+' . $row['column'] . '[a-z$ ":]+\s+=\s+/i', $row['constraint'])) {
1604	if (preg_match_all("/(?!').'((''\|[^']+)*)'/", $row['constraint'], $matches, PREG_PATTERN_ORDER)) {
1605	$info['valid_values'] = str_replace("''", "'", $matches[1]);
1606	}
1607	}
1608	}
1609
1610	// Handle default values and serial data types
1611	if ($info['type'] == 'integer' && stripos($row['default'], 'nextval(') !== FALSE) {
1612	$info['auto_increment'] = TRUE;
1613
1614	} elseif ($row['default'] !== NULL) {
1615	if (preg_match('#^NULL::[\w\s]+$#', $row['default'])) {
1616	$info['default'] = NULL;
1617	} elseif ($row['default'] == 'now()') {
1618	$info['default'] = 'CURRENT_TIMESTAMP';
1619	} elseif ($row['default'] == "('now'::text)::date") {
1620	$info['default'] = 'CURRENT_DATE';
1621	} elseif ($row['default'] == "('now'::text)::time with time zone") {
1622	$info['default'] = 'CURRENT_TIME';
1623	} else {
1624	$info['default'] = str_replace("''", "'", preg_replace("/^'(.*)'::[a-z ]+\$/iD", '\1', $row['default']));
1625	if ($info['type'] == 'boolean') {
1626	$info['default'] = ($info['default'] == 'false' \|\| !$info['default']) ? FALSE : TRUE;
1627	}
1628	}
1629	}
1630
1631	// Not null values
1632	$info['not_null'] = ($row['not_null'] == 't') ? TRUE : FALSE;
1633
1634	$column_info[$row['column']] = $info;
1635	}
1636
1637	return $column_info;
1638	}
1639
1640
1641	/**
1642	* Fetches the key info for a PostgreSQL database
1643	*
1644	* @return array The keys arrays for every table in the database - see ::getKeys() for details
1645	*/
1646	private function fetchPostgreSQLKeys()
1647	{
1648	$keys = array();
1649
1650	$tables = $this->getTables();
1651	foreach ($tables as $table) {
1652	$keys[$table] = array();
1653	$keys[$table]['primary'] = array();
1654	$keys[$table]['unique'] = array();
1655	$keys[$table]['foreign'] = array();
1656	}
1657
1658	$sql = "(
1659	SELECT
1660	s.nspname AS \"schema\",
1661	t.relname AS \"table\",
1662	con.conname AS constraint_name,
1663	CASE con.contype
1664	WHEN 'f' THEN 'foreign'
1665	WHEN 'p' THEN 'primary'
1666	WHEN 'u' THEN 'unique'
1667	END AS type,
1668	col.attname AS column,
1669	fs.nspname AS foreign_schema,
1670	ft.relname AS foreign_table,
1671	fc.attname AS foreign_column,
1672	CASE con.confdeltype
1673	WHEN 'c' THEN 'cascade'
1674	WHEN 'a' THEN 'no_action'
1675	WHEN 'r' THEN 'restrict'
1676	WHEN 'n' THEN 'set_null'
1677	WHEN 'd' THEN 'set_default'
1678	END AS on_delete,
1679	CASE con.confupdtype
1680	WHEN 'c' THEN 'cascade'
1681	WHEN 'a' THEN 'no_action'
1682	WHEN 'r' THEN 'restrict'
1683	WHEN 'n' THEN 'set_null'
1684	WHEN 'd' THEN 'set_default'
1685	END AS on_update,
1686	CASE WHEN con.conkey IS NOT NULL THEN position('-'\|\|col.attnum\|\|'-' in '-'\|\|array_to_string(con.conkey, '-')\|\|'-') ELSE 0 END AS column_order
1687	FROM
1688	pg_attribute AS col INNER JOIN
1689	pg_class AS t ON col.attrelid = t.oid INNER JOIN
1690	pg_namespace AS s ON t.relnamespace = s.oid INNER JOIN
1691	pg_constraint AS con ON (col.attnum = ANY (con.conkey) AND
1692	con.conrelid = t.oid) LEFT JOIN
1693	pg_class AS ft ON con.confrelid = ft.oid LEFT JOIN
1694	pg_namespace AS fs ON ft.relnamespace = fs.oid LEFT JOIN
1695	pg_attribute AS fc ON (fc.attnum = ANY (con.confkey) AND
1696	ft.oid = fc.attrelid)
1697	WHERE
1698	NOT col.attisdropped AND
1699	(con.contype = 'p' OR
1700	con.contype = 'f' OR
1701	con.contype = 'u')
1702	) UNION (
1703	SELECT
1704	n.nspname AS \"schema\",
1705	t.relname AS \"table\",
1706	ic.relname AS constraint_name,
1707	'unique' AS type,
1708	col.attname AS column,
1709	NULL AS foreign_schema,
1710	NULL AS foreign_table,
1711	NULL AS foreign_column,
1712	NULL AS on_delete,
1713	NULL AS on_update,
1714	CASE WHEN ind.indkey IS NOT NULL THEN position('-'\|\|col.attnum\|\|'-' in '-'\|\|array_to_string(ind.indkey, '-')\|\|'-') ELSE 0 END AS column_order
1715	FROM
1716	pg_class AS t INNER JOIN
1717	pg_index AS ind ON ind.indrelid = t.oid INNER JOIN
1718	pg_namespace AS n ON t.relnamespace = n.oid INNER JOIN
1719	pg_class AS ic ON ind.indexrelid = ic.oid LEFT JOIN
1720	pg_constraint AS con ON con.conrelid = t.oid AND con.contype = 'u' AND con.conname = ic.relname INNER JOIN
1721	pg_attribute AS col ON col.attrelid = t.oid AND col.attnum = ANY (ind.indkey)
1722	WHERE
1723	n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
1724	indisunique = TRUE AND
1725	indisprimary = FALSE AND
1726	con.oid IS NULL AND
1727	0 != ALL ((ind.indkey)::int[])
1728	) ORDER BY 1, 2, 4, 3, 11";
1729
1730	$result = $this->database->query($sql);
1731
1732	$last_name = '';
1733	$last_table = '';
1734	$last_type = '';
1735	foreach ($result as $row) {
1736
1737	if ($row['constraint_name'] != $last_name) {
1738
1739	if ($last_name) {
1740	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
1741	$keys[$last_table][$last_type][] = $temp;
1742	} else {
1743	$keys[$last_table][$last_type] = $temp;
1744	}
1745	}
1746
1747	$temp = array();
1748	if ($row['type'] == 'foreign') {
1749
1750	$temp['column'] = $row['column'];
1751	$temp['foreign_table'] = $row['foreign_table'];
1752	if ($row['foreign_schema'] != 'public') {
1753	$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];
1754	}
1755	$temp['foreign_column'] = $row['foreign_column'];
1756	$temp['on_delete'] = 'no_action';
1757	$temp['on_update'] = 'no_action';
1758
1759	if (!empty($row['on_delete'])) {
1760	$temp['on_delete'] = $row['on_delete'];
1761	}
1762
1763	if (!empty($row['on_update'])) {
1764	$temp['on_update'] = $row['on_update'];
1765	}
1766
1767	} else {
1768	$temp[] = $row['column'];
1769	}
1770
1771	$last_table = $row['table'];
1772	if ($row['schema'] != 'public') {
1773	$last_table = $row['schema'] . '.' . $last_table;
1774	}
1775	$last_name = $row['constraint_name'];
1776	$last_type = $row['type'];
1777
1778	} else {
1779	$temp[] = $row['column'];
1780	}
1781	}
1782
1783	if (isset($temp)) {
1784	if ($last_type == 'foreign' \|\| $last_type == 'unique') {
1785	$keys[$last_table][$last_type][] = $temp;
1786	} else {
1787	$keys[$last_table][$last_type] = $temp;
1788	}
1789	}
1790
1791	return $keys;
1792	}
1793
1794
1795	/**
1796	* Gets the column info from a SQLite database
1797	*
1798	* @param string $table The table to fetch the column info for
1799	* @return array The column info for the table specified - see ::getColumnInfo() for details
1800	*/
1801	private function fetchSQLiteColumnInfo($table)
1802	{
1803	$column_info = array();
1804
1805	$data_type_mapping = array(
1806	'boolean' => 'boolean',
1807	'serial' => 'integer',
1808	'smallint' => 'integer',
1809	'int' => 'integer',
1810	'integer' => 'integer',
1811	'bigint' => 'integer',
1812	'timestamp' => 'timestamp',
1813	'date' => 'date',
1814	'time' => 'time',
1815	'varchar' => 'varchar',
1816	'char' => 'char',
1817	'real' => 'float',
1818	'numeric' => 'float',
1819	'float' => 'float',
1820	'double' => 'float',
1821	'decimal' => 'float',
1822	'blob' => 'blob',
1823	'text' => 'text'
1824	);
1825
1826	$result = $this->database->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = %s", $table);
1827
1828	try {
1829	$row = $result->fetchRow();
1830	$create_sql = $row['sql'];
1831	} catch (fNoRowsException $e) {
1832	return array();
1833	}
1834
1835	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);
1836
1837	foreach ($matches as $match) {
1838	$info = array();
1839
1840	foreach ($data_type_mapping as $data_type => $mapped_data_type) {
1841	if (stripos($match[2], $data_type) === 0) {
1842	$info['type'] = $mapped_data_type;
1843	break;
1844	}
1845	}
1846
1847	// Type specific information
1848	if (in_array($info['type'], array('char', 'varchar'))) {
1849	if (!empty($match[3])) {
1850	$info['max_length'] = $match[3];
1851	} else {
1852	$info['max_length'] = 1000000000;
1853	}
1854	}
1855
1856	if ($info['type'] == 'text' \|\| $info['type'] == 'blob') {
1857	$info['max_length'] = 1000000000;
1858	}
1859
1860	// Figure out how many decimal places for a decimal
1861	if (in_array(strtolower($match[2]), array('decimal', 'numeric')) && !empty($match[4])) {
1862	$info['decimal_places'] = $match[4];
1863	$before_digits = str_pad('', $match[3] - $match[4], '9');
1864	$after_digits = str_pad('', $match[4], '9');
1865	$max_min = $before_digits . ($after_digits ? '.' : '') . $after_digits;
1866	$info['min_value'] = new fNumber('-' . $max_min);
1867	$info['max_value'] = new fNumber($max_min);
1868	}
1869
1870	// Not null
1871	$info['not_null'] = (!empty($match[5]) \|\| !empty($match[8])) ? TRUE : FALSE;
1872
1873	// Default values
1874	if (isset($match[6]) && $match[6] != '' && $match[6] != 'NULL') {
1875	$info['default'] = preg_replace("/^'\|'\$/D", '', $match[6]);
1876	}
1877	if ($info['type'] == 'boolean' && isset($info['default'])) {
1878	$info['default'] = ($info['default'] == 'f' \|\| $info['default'] == 0 \|\| $info['default'] == 'false') ? FALSE : TRUE;
1879	}
1880
1881	// Check constraints
1882	if (isset($match[9]) && preg_match('/CHECK\s$\s' . $match[1] . '\s+IN\s+\(\s((?:(?:[^, \']\|\'(?:\'\'\|[^\']+)\')\s,\s)(?:[^, \']\|\'(?:\'\'\|[^\']+)\'))\s*$/i', $match[9], $check_match)) {
1883	$info['valid_values'] = str_replace("''", "'", preg_replace("/^'\|'\$/D", '', preg_split("#\s,\s#", $check_match[1])));
1884	}
1885
1886	// Auto increment fields
1887	if (!empty($match[8]) && (stripos($match[8], 'autoincrement') !== FALSE \|\| $info['type'] == 'integer')) {
1888	$info['auto_increment'] = TRUE;
1889	}
1890
1891	$column_info[$match[1]] = $info;
1892	}
1893
1894	return $column_info;
1895	}
1896
1897
1898	/**
1899	* Fetches the key info for an SQLite database
1900	*
1901	* @return array The keys arrays for every table in the database - see ::getKeys() for details
1902	*/
1903	private function fetchSQLiteKeys()
1904	{
1905	$tables = $this->getTables();
1906	$keys = array();
1907
1908	foreach ($tables as $table) {
1909	$keys[$table] = array();
1910	$keys[$table]['primary'] = array();
1911	$keys[$table]['foreign'] = array();
1912	$keys[$table]['unique'] = array();
1913
1914	$result = $this->database->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = %s", $table);
1915	$row = $result->fetchRow();
1916	$create_sql = $row['sql'];
1917
1918	// Get column level key definitions
1919	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\|SET\s+DEFAULT)))\|(?:\s+(?: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);
1920
1921	foreach ($matches as $match) {
1922	if (!empty($match[2])) {
1923	$keys[$table]['unique'][] = array($match[1]);
1924	}
1925
1926	if (!empty($match[3])) {
1927	$keys[$table]['primary'] = array($match[1]);
1928	}
1929
1930	if (!empty($match[4])) {
1931	$temp = array('column' => $match[1],
1932	'foreign_table' => $match[5],
1933	'foreign_column' => $match[6],
1934	'on_delete' => 'no_action',
1935	'on_update' => 'no_action');
1936	if (isset($match[7])) {
1937	$temp['on_delete'] = strtolower(str_replace(' ', '_', $match[7]));
1938	}
1939	if (isset($match[8])) {
1940	$temp['on_update'] = strtolower(str_replace(' ', '_', $match[8]));
1941	}
1942	$keys[$table]['foreign'][] = $temp;
1943	}
1944	}
1945
1946	// Get table level primary key definitions
1947	preg_match_all('#(?<=,\|$)\sPRIMARY\s+KEY\s\(\s((?:\s["`\[]?\w+["`\]]?\s,\s)["`\[]?\w+["`\]]?)\s$\s(?:,\|\s(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
1948
1949	foreach ($matches as $match) {
1950	$columns = preg_split('#\s,\s#', $match[1]);
1951	foreach ($columns as $column) {
1952	$keys[$table]['primary'][] = str_replace(array('[', '"', '`', ']'), '', $column);
1953	}
1954	}
1955
1956	// Get table level foreign key definitions
1957	preg_match_all('#(?<=,\|$)\sFOREIGN\s+KEY\s(?:["`\[]?(\w+)["`\]]?\|\(\s["`\[]?(\w+)["`\]]?\s$)\s+REFERENCES\s+["`\[]?(\w+)["`\]]?\s$\s["`\[]?(\w+)["`\]]?\s$\s(?:\s+(?:ON\s+DELETE\s+(CASCADE\|NO\s+ACTION\|RESTRICT\|SET\s+NULL\|SET\s+DEFAULT)))?(?:\s+(?: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);
1958
1959	foreach ($matches as $match) {
1960	if (empty($match[1])) { $match[1] = $match[2]; }
1961	$temp = array('column' => $match[1],
1962	'foreign_table' => $match[3],
1963	'foreign_column' => $match[4],
1964	'on_delete' => 'no_action',
1965	'on_update' => 'no_action');
1966	if (isset($match[5])) {
1967	$temp['on_delete'] = strtolower(str_replace(' ', '_', $match[5]));
1968	}
1969	if (isset($match[6])) {
1970	$temp['on_update'] = strtolower(str_replace(' ', '_', $match[6]));
1971	}
1972	$keys[$table]['foreign'][] = $temp;
1973	}
1974
1975	// Get table level unique key definitions
1976	preg_match_all('#(?<=,\|$)\sUNIQUE\s\(\s((?:\s["`\[]?\w+["`\]]?\s,\s)["`\[]?\w+["`\]]?)\s$\s(?:,\|\s(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
1977
1978	foreach ($matches as $match) {
1979	$columns = preg_split('#\s,\s#', $match[1]);
1980	$key = array();
1981	foreach ($columns as $column) {
1982	$key[] = str_replace(array('[', '"', '`', ']'), '', $column);
1983	}
1984	$keys[$table]['unique'][] = $key;
1985	}
1986	}
1987
1988	return $keys;
1989	}
1990
1991
1992	/**
1993	* Finds many-to-many relationship for the table specified
1994	*
1995	* @param string $table The table to find the relationships on
1996	* @return void
1997	*/
1998	private function findManyToManyRelationships($table)
1999	{
2000	if (!$this->isJoiningTable($table)) {
2001	return;
2002	}
2003
2004	list ($key1, $key2) = $this->merged_keys[$table]['foreign'];
2005
2006	$temp = array();
2007	$temp['table'] = $key1['foreign_table'];
2008	$temp['column'] = $key1['foreign_column'];
2009	$temp['related_table'] = $key2['foreign_table'];
2010	$temp['related_column'] = $key2['foreign_column'];
2011	$temp['join_table'] = $table;
2012	$temp['join_column'] = $key1['column'];
2013	$temp['join_related_column'] = $key2['column'];
2014	$temp['on_update'] = $key1['on_update'];
2015	$temp['on_delete'] = $key1['on_delete'];
2016	$this->relationships[$key1['foreign_table']]['many-to-many'][] = $temp;
2017
2018	$temp = array();
2019	$temp['table'] = $key2['foreign_table'];
2020	$temp['column'] = $key2['foreign_column'];
2021	$temp['related_table'] = $key1['foreign_table'];
2022	$temp['related_column'] = $key1['foreign_column'];
2023	$temp['join_table'] = $table;
2024	$temp['join_column'] = $key2['column'];
2025	$temp['join_related_column'] = $key1['column'];
2026	$temp['on_update'] = $key2['on_update'];
2027	$temp['on_delete'] = $key2['on_delete'];
2028	$this->relationships[$key2['foreign_table']]['many-to-many'][] = $temp;
2029	}
2030
2031
2032	/**
2033	* Finds one-to-many relationship for the table specified
2034	*
2035	* @param string $table The table to find the relationships on
2036	* @return void
2037	*/
2038	private function findOneToManyRelationships($table)
2039	{
2040	foreach ($this->merged_keys[$table]['foreign'] as $key) {
2041	$type = ($this->checkForSingleColumnUniqueKey($table, $key['column'])) ? 'one-to-one' : 'one-to-many';
2042	$temp = array();
2043	$temp['table'] = $key['foreign_table'];
2044	$temp['column'] = $key['foreign_column'];
2045	$temp['related_table'] = $table;
2046	$temp['related_column'] = $key['column'];
2047	$temp['on_delete'] = $key['on_delete'];
2048	$temp['on_update'] = $key['on_update'];
2049	$this->relationships[$key['foreign_table']][$type][] = $temp;
2050	}
2051	}
2052
2053
2054	/**
2055	* Finds one-to-one and many-to-one relationship for the table specified
2056	*
2057	* @param string $table The table to find the relationships on
2058	* @return void
2059	*/
2060	private function findStarToOneRelationships($table)
2061	{
2062	foreach ($this->merged_keys[$table]['foreign'] as $key) {
2063	$temp = array();
2064	$temp['table'] = $table;
2065	$temp['column'] = $key['column'];
2066	$temp['related_table'] = $key['foreign_table'];
2067	$temp['related_column'] = $key['foreign_column'];
2068	$type = ($this->checkForSingleColumnUniqueKey($table, $key['column'])) ? 'one-to-one' : 'many-to-one';
2069	if ($type == 'one-to-one') {
2070	$temp['on_delete'] = $key['on_delete'];
2071	$temp['on_update'] = $key['on_update'];
2072	}
2073	$this->relationships[$table][$type][] = $temp;
2074	}
2075	}
2076
2077
2078	/**
2079	* Finds the one-to-one, many-to-one, one-to-many and many-to-many relationships in the database
2080	*
2081	* @return void
2082	*/
2083	private function findRelationships()
2084	{
2085	$this->relationships = array();
2086	$tables = $this->getTables();
2087
2088	foreach ($tables as $table) {
2089	$this->relationships[$table]['one-to-one'] = array();
2090	$this->relationships[$table]['many-to-one'] = array();
2091	$this->relationships[$table]['one-to-many'] = array();
2092	$this->relationships[$table]['many-to-many'] = array();
2093	}
2094
2095	// Calculate the relationships
2096	foreach ($this->merged_keys as $table => $keys) {
2097	$this->findManyToManyRelationships($table);
2098	if ($this->isJoiningTable($table)) {
2099	continue;
2100	}
2101
2102	$this->findStarToOneRelationships($table);
2103	$this->findOneToManyRelationships($table);
2104	}
2105
2106	if ($this->cache) {
2107	$this->cache->set($this->makeCachePrefix() . 'relationships', $this->relationships);
2108	}
2109	}
2110
2111
2112	/**
2113	* Returns column information for the table specified
2114	*
2115	* If only a table is specified, column info is in the following format:
2116	*
2117	* {{{
2118	* array(
2119	* (string) {column name} => array(
2120	* 'type' => (string) {data type},
2121	* 'placeholder' => (string) {fDatabase::escape() placeholder for this data type},
2122	* 'not_null' => (boolean) {if value can't be null},
2123	* 'default' => (mixed) {the default value},
2124	* 'valid_values' => (array) {the valid values for a varchar field},
2125	* 'max_length' => (integer) {the maximum length in a varchar field},
2126	* 'min_value' => (numeric) {the minimum value for an integer/float field},
2127	* 'max_value' => (numeric) {the maximum value for an integer/float field},
2128	* 'decimal_places' => (integer) {the number of decimal places for a decimal/numeric/money/smallmoney field},
2129	* 'auto_increment' => (boolean) {if the integer primary key column is a serial/autoincrement/auto_increment/indentity column}
2130	* ), ...
2131	* )
2132	* }}}
2133	*
2134	* If a table and column are specified, column info is in the following format:
2135	*
2136	* {{{
2137	* array(
2138	* 'type' => (string) {data type},
2139	* 'placeholder' => (string) {fDatabase::escape() placeholder for this data type},
2140	* 'not_null' => (boolean) {if value can't be null},
2141	* 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
2142	* 'valid_values' => (array) {the valid values for a varchar field},
2143	* 'max_length' => (integer) {the maximum length in a char/varchar field},
2144	* 'min_value' => (fNumber) {the minimum value for an integer/float field},
2145	* 'max_value' => (fNumber) {the maximum value for an integer/float field},
2146	* 'decimal_places' => (integer) {the number of decimal places for a decimal/numeric/money/smallmoney field},
2147	* 'auto_increment' => (boolean) {if the integer primary key column is a serial/autoincrement/auto_increment/indentity column}
2148	* )
2149	* }}}
2150	*
2151	* If a table, column and element are specified, returned value is the single element specified.
2152	*
2153	* The `'type'` element is homogenized to a value from the following list:
2154	*
2155	* - `'varchar'`
2156	* - `'char'`
2157	* - `'text'`
2158	* - `'integer'`
2159	* - `'float'`
2160	* - `'timestamp'`
2161	* - `'date'`
2162	* - `'time'`
2163	* - `'boolean'`
2164	* - `'blob'`
2165	*
2166	* @param string $table The table to get the column info for
2167	* @param string $column The column to get the info for
2168	* @param string $element The element to return: `'type'`, `'placeholder'`, `'not_null'`, `'default'`, `'valid_values'`, `'max_length'`, `'min_value'`, `'max_value'`, `'decimal_places'`, `'auto_increment'`
2169	* @return mixed The column info for the table/column/element specified - see method description for format
2170	*/
2171	public function getColumnInfo($table, $column=NULL, $element=NULL)
2172	{
2173	// Return the saved column info if possible
2174	if (!$column && isset($this->merged_column_info[$table])) {
2175	return $this->merged_column_info[$table];
2176	}
2177	if ($column && isset($this->merged_column_info[$table][$column])) {
2178	if ($element !== NULL) {
2179	if (!isset($this->merged_column_info[$table][$column][$element]) && !array_key_exists($element, $this->merged_column_info[$table][$column])) {
2180	throw new fProgrammerException(
2181	'The element specified, %1$s, is invalid. Must be one of: %2$s.',
2182	$element,
2183	join(', ', array('type', 'placeholder', 'not_null', 'default', 'valid_values', 'max_length', 'min_value', 'max_value', 'decimal_places', 'auto_increment'))
2184	);
2185	}
2186	return $this->merged_column_info[$table][$column][$element];
2187	}
2188	return $this->merged_column_info[$table][$column];
2189	}
2190
2191	if (!in_array($table, $this->getTables())) {
2192	throw new fProgrammerException(
2193	'The table specified, %s, does not exist in the database',
2194	$table
2195	);
2196	}
2197
2198	$this->fetchColumnInfo($table);
2199	$this->mergeColumnInfo();
2200
2201	if ($column && !isset($this->merged_column_info[$table][$column])) {
2202	throw new fProgrammerException(
2203	'The column specified, %1$s, does not exist in the table %2$s',
2204	$column,
2205	$table
2206	);
2207	}
2208
2209	if ($column) {
2210	if ($element) {
2211	return $this->merged_column_info[$table][$column][$element];
2212	}
2213
2214	return $this->merged_column_info[$table][$column];
2215	}
2216
2217	return $this->merged_column_info[$table];
2218	}
2219
2220
2221	/**
2222	* Returns the databases on the current server
2223	*
2224	* @return array The databases on the current server
2225	*/
2226	public function getDatabases()
2227	{
2228	if ($this->databases !== NULL) {
2229	return $this->databases;
2230	}
2231
2232	$this->databases = array();
2233
2234	switch ($this->database->getType()) {
2235	case 'mssql':
2236	$sql = 'EXECUTE sp_databases';
2237	break;
2238
2239	case 'mysql':
2240	$sql = 'SHOW DATABASES';
2241	break;
2242
2243	case 'oracle':
2244	$sql = 'SELECT ora_database_name FROM dual';
2245
2246	case 'postgresql':
2247	$sql = "SELECT
2248	datname
2249	FROM
2250	pg_database
2251	ORDER BY
2252	LOWER(datname)";
2253	break;
2254
2255	case 'db2':
2256	case 'sqlite':
2257	$this->databases[] = $this->database->getDatabase();
2258	return $this->databases;
2259	}
2260
2261	$result = $this->database->query($sql);
2262
2263	foreach ($result as $row) {
2264	$keys = array_keys($row);
2265	$this->databases[] = $row[$keys[0]];
2266	}
2267
2268	if ($this->cache) {
2269	$this->cache->set($this->makeCachePrefix() . 'databases', $this->databases);
2270	}
2271
2272	return $this->databases;
2273	}
2274
2275
2276	/**
2277	* Returns a list of primary key, foreign key and unique key constraints for the table specified
2278	*
2279	* The structure of the returned array is:
2280	*
2281	* {{{
2282	* array(
2283	* 'primary' => array(
2284	* {column name}, ...
2285	* ),
2286	* 'unique' => array(
2287	* array(
2288	* {column name}, ...
2289	* ), ...
2290	* ),
2291	* 'foreign' => array(
2292	* array(
2293	* 'column' => {column name},
2294	* 'foreign_table' => {foreign table name},
2295	* 'foreign_column' => {foreign column name},
2296	* 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
2297	* 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
2298	* ), ...
2299	* )
2300	* )
2301	* }}}
2302	*
2303	* @param string $table The table to return the keys for
2304	* @param string $key_type The type of key to return: `'primary'`, `'foreign'`, `'unique'`
2305	* @return array An array of all keys, or just the type specified - see method description for format
2306	*/
2307	public function getKeys($table, $key_type=NULL)
2308	{
2309	$valid_key_types = array('primary', 'foreign', 'unique');
2310	if ($key_type !== NULL && !in_array($key_type, $valid_key_types)) {
2311	throw new fProgrammerException(
2312	'The key type specified, %1$s, is invalid. Must be one of: %2$s.',
2313	$key_type,
2314	join(', ', $valid_key_types)
2315	);
2316	}
2317
2318	// Return the saved column info if possible
2319	if (!$key_type && isset($this->merged_keys[$table])) {
2320	return $this->merged_keys[$table];
2321	}
2322
2323	if ($key_type && isset($this->merged_keys[$table][$key_type])) {
2324	return $this->merged_keys[$table][$key_type];
2325	}
2326
2327	if (!in_array($table, $this->getTables())) {
2328	throw new fProgrammerException(
2329	'The table specified, %s, does not exist in the database',
2330	$table
2331	);
2332	}
2333
2334	$this->fetchKeys();
2335	$this->mergeKeys();
2336
2337	if ($key_type) {
2338	return $this->merged_keys[$table][$key_type];
2339	}
2340
2341	return $this->merged_keys[$table];
2342	}
2343
2344
2345	/**
2346	* Returns a list of one-to-one, many-to-one, one-to-many and many-to-many relationships for the table specified
2347	*
2348	* The structure of the returned array is:
2349	*
2350	* {{{
2351	* array(
2352	* 'one-to-one' => array(
2353	* array(
2354	* 'table' => (string) {the name of the table this relationship is for},
2355	* 'column' => (string) {the column in the specified table},
2356	* 'related_table' => (string) {the related table},
2357	* 'related_column' => (string) {the related column},
2358	* 'on_delete' => (string) {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
2359	* 'on_update' => (string) {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
2360	* ), ...
2361	* ),
2362	* 'many-to-one' => array(
2363	* array(
2364	* 'table' => (string) {the name of the table this relationship is for},
2365	* 'column' => (string) {the column in the specified table},
2366	* 'related_table' => (string) {the related table},
2367	* 'related_column' => (string) {the related column}
2368	* ), ...
2369	* ),
2370	* 'one-to-many' => array(
2371	* array(
2372	* 'table' => (string) {the name of the table this relationship is for},
2373	* 'column' => (string) {the column in the specified table},
2374	* 'related_table' => (string) {the related table},
2375	* 'related_column' => (string) {the related column},
2376	* 'on_delete' => (string) {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
2377	* 'on_update' => (string) {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
2378	* ), ...
2379	* ),
2380	* 'many-to-many' => array(
2381	* array(
2382	* 'table' => (string) {the name of the table this relationship is for},
2383	* 'column' => (string) {the column in the specified table},
2384	* 'related_table' => (string) {the related table},
2385	* 'related_column' => (string) {the related column},
2386	* 'join_table' => (string) {the table that joins the specified table to the related table},
2387	* 'join_column' => (string) {the column in the join table that references 'column'},
2388	* 'join_related_column' => (string) {the column in the join table that references 'related_column'},
2389	* 'on_delete' => (string) {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
2390	* 'on_update' => (string) {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
2391	* ), ...
2392	* )
2393	* )
2394	* }}}
2395	*
2396	* @param string $table The table to return the relationships for
2397	* @param string $relationship_type The type of relationship to return: `'one-to-one'`, `'many-to-one'`, `'one-to-many'`, `'many-to-many'`
2398	* @return array An array of all relationships, or just the type specified - see method description for format
2399	*/
2400	public function getRelationships($table, $relationship_type=NULL)
2401	{
2402	$valid_relationship_types = array('one-to-one', 'many-to-one', 'one-to-many', 'many-to-many');
2403	if ($relationship_type !== NULL && !in_array($relationship_type, $valid_relationship_types)) {
2404	throw new fProgrammerException(
2405	'The relationship type specified, %1$s, is invalid. Must be one of: %2$s.',
2406	$relationship_type,
2407	join(', ', $valid_relationship_types)
2408	);
2409	}
2410
2411	// Return the saved column info if possible
2412	if (!$relationship_type && isset($this->relationships[$table])) {
2413	return $this->relationships[$table];
2414	}
2415
2416	if ($relationship_type && isset($this->relationships[$table][$relationship_type])) {
2417	return $this->relationships[$table][$relationship_type];
2418	}
2419
2420	if (!in_array($table, $this->getTables())) {
2421	throw new fProgrammerException(
2422	'The table specified, %s, does not exist in the database',
2423	$table
2424	);
2425	}
2426
2427	$this->fetchKeys();
2428	$this->mergeKeys();
2429
2430	if ($relationship_type) {
2431	return $this->relationships[$table][$relationship_type];
2432	}
2433
2434	return $this->relationships[$table];
2435	}
2436
2437
2438	/**
2439	* Returns the tables in the current database
2440	*
2441	* @return array The tables in the current database
2442	*/
2443	public function getTables()
2444	{
2445	if ($this->tables !== NULL) {
2446	return $this->tables;
2447	}
2448
2449	switch ($this->database->getType()) {
2450	case 'db2':
2451	$sql = "SELECT
2452	LOWER(RTRIM(TABSCHEMA)) AS \"schema\",
2453	LOWER(TABNAME) AS \"table\"
2454	FROM
2455	SYSCAT.TABLES
2456	WHERE
2457	TYPE = 'T' AND
2458	TABSCHEMA != 'SYSIBM' AND
2459	DEFINER != 'SYSIBM' AND
2460	TABSCHEMA != 'SYSTOOLS' AND
2461	DEFINER != 'SYSTOOLS'
2462	ORDER BY
2463	LOWER(TABNAME)";
2464	break;
2465
2466	case 'mssql':
2467	$sql = "SELECT
2468	TABLE_SCHEMA AS \"schema\",
2469	TABLE_NAME AS \"table\"
2470	FROM
2471	INFORMATION_SCHEMA.TABLES
2472	WHERE
2473	TABLE_NAME != 'sysdiagrams'
2474	ORDER BY
2475	LOWER(TABLE_NAME)";
2476	break;
2477
2478	case 'mysql':
2479	$version = $this->database->query("SELECT version()")->fetchScalar();
2480	$version = substr($version, 0, strpos($version, '.'));
2481	if ($version <= 4) {
2482	$sql = 'SHOW TABLES';
2483	} else {
2484	$sql = "SHOW FULL TABLES WHERE table_type = 'BASE TABLE'";
2485	}
2486	break;
2487
2488	case 'oracle':
2489	$sql = "SELECT
2490	LOWER(OWNER) AS \"SCHEMA\",
2491	LOWER(TABLE_NAME) AS \"TABLE\"
2492	FROM
2493	ALL_TABLES
2494	WHERE
2495	OWNER NOT IN (
2496	'SYS',
2497	'SYSTEM',
2498	'OUTLN',
2499	'ANONYMOUS',
2500	'AURORA\$ORB\$UNAUTHENTICATED',
2501	'AWR_STAGE',
2502	'CSMIG',
2503	'CTXSYS',
2504	'DBSNMP',
2505	'DIP',
2506	'DMSYS',
2507	'DSSYS',
2508	'EXFSYS',
2509	'FLOWS_020100',
2510	'FLOWS_FILES',
2511	'LBACSYS',
2512	'MDSYS',
2513	'ORACLE_OCM',
2514	'ORDPLUGINS',
2515	'ORDSYS',
2516	'PERFSTAT',
2517	'TRACESVR',
2518	'TSMSYS',
2519	'XDB'
2520	) AND
2521	DROPPED = 'NO'
2522	ORDER BY
2523	TABLE_NAME ASC";
2524	break;
2525
2526	case 'postgresql':
2527	$sql = "SELECT
2528	schemaname AS \"schema\",
2529	tablename as \"table\"
2530	FROM
2531	pg_tables
2532	WHERE
2533	tablename !~ '^(pg\|sql)_'
2534	ORDER BY
2535	LOWER(tablename)";
2536	break;
2537
2538	case 'sqlite':
2539	$sql = "SELECT
2540	name
2541	FROM
2542	sqlite_master
2543	WHERE
2544	type = 'table' AND
2545	name NOT LIKE 'sqlite_%'
2546	ORDER BY
2547	name ASC";
2548	break;
2549	}
2550
2551	$result = $this->database->query($sql);
2552
2553	$this->tables = array();
2554
2555	// For databases with schemas we only include the schema
2556	// name if there are conflicting table names
2557	if (!in_array($this->database->getType(), array('mysql', 'sqlite'))) {
2558
2559	$default_schema_map = array(
2560	'db2' => strtolower($this->database->getUsername()),
2561	'mssql' => 'dbo',
2562	'oracle' => strtolower($this->database->getUsername()),
2563	'postgresql' => 'public'
2564	);
2565
2566	$default_schema = $default_schema_map[$this->database->getType()];
2567
2568	foreach ($result as $row) {
2569	if ($row['schema'] == $default_schema) {
2570	$this->tables[] = $row['table'];
2571	} else {
2572	$this->tables[] = $row['schema'] . '.' . $row['table'];
2573	}
2574	}
2575
2576	// SQLite and MySQL don't support schemas
2577	} else {
2578	foreach ($result as $row) {
2579	$keys = array_keys($row);
2580	$this->tables[] = $row[$keys[0]];
2581	}
2582	}
2583
2584	sort($this->tables);
2585
2586	if ($this->cache) {
2587	$this->cache->set($this->makeCachePrefix() . 'tables', $this->tables);
2588	}
2589
2590	return $this->tables;
2591	}
2592
2593
2594	/**
2595	* Determines if a table is a joining table
2596	*
2597	* @param string $table The table to check
2598	* @return boolean If the table is a joining table
2599	*/
2600	private function isJoiningTable($table)
2601	{
2602	$primary_key_columns = $this->merged_keys[$table]['primary'];
2603
2604	if (sizeof($primary_key_columns) != 2) {
2605	return FALSE;
2606	}
2607
2608	if (empty($this->merged_column_info[$table])) {
2609	$this->getColumnInfo($table);
2610	}
2611	if (sizeof($this->merged_column_info[$table]) != 2) {
2612	return FALSE;
2613	}
2614
2615	$foreign_key_columns = array();
2616	foreach ($this->merged_keys[$table]['foreign'] as $key) {
2617	$foreign_key_columns[] = $key['column'];
2618	}
2619
2620	return sizeof($foreign_key_columns) == 2 && !array_diff($foreign_key_columns, $primary_key_columns);
2621	}
2622
2623
2624	/**
2625	* Creates a unique cache prefix to help prevent cache conflicts
2626	*
2627	* @return string The cache prefix to use
2628	*/
2629	private function makeCachePrefix()
2630	{
2631	if (!$this->cache_prefix) {
2632	$prefix = 'fSchema::' . $this->database->getType() . '::';
2633	if ($this->database->getHost()) {
2634	$prefix .= $this->database->getHost() . '::';
2635	}
2636	if ($this->database->getPort()) {
2637	$prefix .= $this->database->getPort() . '::';
2638	}
2639	$prefix .= $this->database->getDatabase() . '::';
2640	if ($this->database->getUsername()) {
2641	$prefix .= $this->database->getUsername() . '::';
2642	}
2643	$this->cache_prefix = $prefix;
2644	}
2645
2646	return $this->cache_prefix;
2647	}
2648
2649
2650	/**
2651	* Merges the column info with the column info override
2652	*
2653	* @return void
2654	*/
2655	private function mergeColumnInfo()
2656	{
2657	$this->merged_column_info = $this->column_info;
2658
2659	foreach ($this->column_info_override as $table => $columns) {
2660	// Remove a table if the columns are set to NULL
2661	if ($columns === NULL) {
2662	unset($this->merged_column_info[$table]);
2663	continue;
2664	}
2665
2666	if (!isset($this->merged_column_info[$table])) {
2667	$this->merged_column_info[$table] = array();
2668	}
2669
2670	foreach ($columns as $column => $info) {
2671	// Remove a column if it is set to NULL
2672	if ($info === NULL) {
2673	unset($this->merged_column_info[$table][$column]);
2674	continue;
2675	}
2676
2677	if (!isset($this->merged_column_info[$table][$column])) {
2678	$this->merged_column_info[$table][$column] = array();
2679	}
2680
2681	$this->merged_column_info[$table][$column] = array_merge($this->merged_column_info[$table][$column], $info);
2682	}
2683	}
2684
2685	$optional_elements = array(
2686	'not_null',
2687	'default',
2688	'valid_values',
2689	'max_length',
2690	'max_value',
2691	'min_value',
2692	'decimal_places',
2693	'auto_increment'
2694	);
2695
2696	foreach ($this->merged_column_info as $table => $column_array) {
2697	foreach ($column_array as $column => $info) {
2698	if (empty($info['type'])) {
2699	throw new fProgrammerException('The data type for the column %1$s is empty', $column);
2700	}
2701
2702	if (empty($this->merged_column_info[$table][$column]['placeholder'])) {
2703	$this->merged_column_info[$table][$column]['placeholder'] = strtr(
2704	$info['type'],
2705	array(
2706	'blob' => '%l',
2707	'boolean' => '%b',
2708	'date' => '%d',
2709	'float' => '%f',
2710	'integer' => '%i',
2711	'char' => '%s',
2712	'text' => '%s',
2713	'varchar' => '%s',
2714	'time' => '%t',
2715	'timestamp' => '%p'
2716	)
2717	);
2718	}
2719
2720	foreach ($optional_elements as $element) {
2721	if (!isset($this->merged_column_info[$table][$column][$element])) {
2722	$this->merged_column_info[$table][$column][$element] = ($element == 'auto_increment') ? FALSE : NULL;
2723	}
2724	}
2725	}
2726	}
2727
2728	if ($this->cache) {
2729	$this->cache->set($this->makeCachePrefix() . 'merged_column_info', $this->merged_column_info);
2730	}
2731	}
2732
2733
2734	/**
2735	* Merges the keys with the keys override
2736	*
2737	* @return void
2738	*/
2739	private function mergeKeys()
2740	{
2741	// Handle the database and override key info
2742	$this->merged_keys = $this->keys;
2743
2744	foreach ($this->keys_override as $table => $info) {
2745	if (!isset($this->merged_keys[$table])) {
2746	$this->merged_keys[$table] = array();
2747	}
2748	$this->merged_keys[$table] = array_merge($this->merged_keys[$table], $info);
2749	}
2750
2751	if ($this->cache) {
2752	$this->cache->set($this->makeCachePrefix() . 'merged_keys', $this->merged_keys);
2753	}
2754
2755	$this->findRelationships();
2756	}
2757
2758
2759	/**
2760	* Allows overriding of column info
2761	*
2762	* Performs an array merge with the column info detected from the database.
2763	*
2764	* To erase a whole table, set the `$column_info` to `NULL`. To erase a
2765	* column, set the `$column_info` for that column to `NULL`.
2766	*
2767	* If the `$column_info` parameter is not `NULL`, it should be an
2768	* associative array containing one or more of the following keys. Please
2769	* see ::getColumnInfo() for a description of each.
2770	* - `'type'`
2771	* - `'placeholder'`
2772	* - `'not_null'`
2773	* - `'default'`
2774	* - `'valid_values'`
2775	* - `'max_length'`
2776	* - `'min_value'`
2777	* - `'max_value'`
2778	* - `'decimal_places'`
2779	* - `'auto_increment'`
2780	*
2781	* The following keys may be set to `NULL`:
2782	* - `'not_null'`
2783	* - `'default'`
2784	* - `'valid_values'`
2785	* - `'max_length'`
2786	* - `'min_value'`
2787	* - `'max_value'`
2788	* - `'decimal_places'`
2789	*
2790	* The key `'auto_increment'` should be a boolean.
2791	*
2792	* The `'type'` key should be one of:
2793	* - `'blob'`
2794	* - `'boolean'`
2795	* - `'char'`
2796	* - `'date'`
2797	* - `'float'`
2798	* - `'integer'`
2799	* - `'text'`
2800	* - `'time'`
2801	* - `'timestamp'`
2802	* - `'varchar'`
2803	*
2804	* @param array $column_info The modified column info - see method description for format
2805	* @param string $table The table to override
2806	* @param string $column The column to override
2807	* @return void
2808	*/
2809	public function setColumnInfoOverride($column_info, $table, $column=NULL)
2810	{
2811	if (!isset($this->column_info_override[$table])) {
2812	$this->column_info_override[$table] = array();
2813	}
2814
2815	if (!empty($column)) {
2816	$this->column_info_override[$table][$column] = $column_info;
2817	} else {
2818	$this->column_info_override[$table] = $column_info;
2819	}
2820
2821	$this->fetchColumnInfo($table);
2822	$this->mergeColumnInfo();
2823	}
2824
2825
2826	/**
2827	* Allows overriding of key info. Replaces existing info, so be sure to provide full key info for type selected or all types.
2828	*
2829	* @param array $keys The modified keys - see ::getKeys() for format
2830	* @param string $table The table to override
2831	* @param string $key_type The key type to override: `'primary'`, `'foreign'`, `'unique'`
2832	* @return void
2833	*/
2834	public function setKeysOverride($keys, $table, $key_type=NULL)
2835	{
2836	$valid_key_types = array('primary', 'foreign', 'unique');
2837	if (!in_array($key_type, $valid_key_types)) {
2838	throw new fProgrammerException(
2839	'The key type specified, %1$s, is invalid. Must be one of: %2$s.',
2840	$key_type,
2841	join(', ', $valid_key_types)
2842	);
2843	}
2844
2845	if (!isset($this->keys_override[$table])) {
2846	$this->keys_override[$table] = array();
2847	}
2848
2849	if (!empty($key_type)) {
2850	$this->keys_override[$table][$key_type] = $keys;
2851	} else {
2852	$this->keys_override[$table] = $keys;
2853	}
2854
2855	$this->fetchKeys();
2856	$this->mergeKeys();
2857	}
2858	}
2859
2860
2861
2862	/**
2863	* Copyright (c) 2007-2010 Will Bond <will@flourishlib.com>
2864	*
2865	* Permission is hereby granted, free of charge, to any person obtaining a copy
2866	* of this software and associated documentation files (the "Software"), to deal
2867	* in the Software without restriction, including without limitation the rights
2868	* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
2869	* copies of the Software, and to permit persons to whom the Software is
2870	* furnished to do so, subject to the following conditions:
2871	*
2872	* The above copyright notice and this permission notice shall be included in
2873	* all copies or substantial portions of the Software.
2874	*
2875	* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
2876	* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
2877	* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
2878	* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
2879	* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
2880	* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
2881	* THE SOFTWARE.
2882	*/

root/fSchema.php

Class Resources

Download in other formats: