PageRenderTime 69ms CodeModel.GetById 21ms RepoModel.GetById 1ms app.codeStats 0ms

/classphp/flourish/fSchema.php

https://github.com/jsuarez/Lexer
PHP | 2420 lines | 1527 code | 270 blank | 623 comment | 222 complexity | 57187668bdb93133ef830b98f7478b5e MD5 | raw file
    

  1. <?php

    2. 

  2. /**

    3. 

  3.  * Gets schema information for the selected database

    4. 

  4.  * 

    5. 

  5.  * @copyright  Copyright (c) 2007-2009 Will Bond

    6. 

  6.  * @author     Will Bond [wb] <will@flourishlib.com>

    7. 

  7.  * @license    http://flourishlib.com/license

    8. 

  8.  * 

    9. 

  9.  * @package    Flourish

    10. 

  10.  * @link       http://flourishlib.com/fSchema

    11. 

  11.  * 

    12. 

  12.  * @version    1.0.0b25

    13. 

  13.  * @changes    1.0.0b25  One-to-one relationships utilizing the primary key as a foreign key are now properly detected [wb, 2009-09-22]

    14. 

  14.  * @changes    1.0.0b24  Fixed MSSQL support to work with ODBC database connections [wb, 2009-09-18]

    15. 

  15.  * @changes    1.0.0b23  Fixed a bug where one-to-one relationships were being listed as many-to-one [wb, 2009-07-21]

    16. 

  16.  * @changes    1.0.0b22  PostgreSQL UNIQUE constraints that are created as indexes and not table constraints are now properly detected [wb, 2009-07-08]

    17. 

  17.  * @changes    1.0.0b21  Added support for the UUID data type in PostgreSQL [wb, 2009-06-18]

    18. 

  18.  * @changes    1.0.0b20  Add caching of merged info, improved performance of ::getColumnInfo() [wb, 2009-06-15]

    19. 

  19.  * @changes    1.0.0b19  Fixed a couple of bugs with ::setKeysOverride() [wb, 2009-06-04]

    20. 

  20.  * @changes    1.0.0b18  Added missing support for MySQL mediumint columns [wb, 2009-05-18]

    21. 

  21.  * @changes    1.0.0b17  Fixed a bug with ::clearCache() not properly reseting the tables and databases list [wb, 2009-05-13]

    22. 

  22.  * @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]

    23. 

  23.  * @changes    1.0.0b15  Added support for the three different types of identifier quoting in SQLite [wb, 2009-03-28]

    24. 

  24.  * @changes    1.0.0b14  Added support for MySQL column definitions containing the COLLATE keyword [wb, 2009-03-28]

    25. 

  25.  * @changes    1.0.0b13  Fixed a bug with detecting PostgreSQL columns having both a CHECK constraint and a UNIQUE constraint [wb, 2009-02-27]

    26. 

  26.  * @changes    1.0.0b12  Fixed detection of multi-column primary keys in MySQL [wb, 2009-02-27]

    27. 

  27.  * @changes    1.0.0b11  Fixed an issue parsing MySQL tables with comments [wb, 2009-02-25]

    28. 

  28.  * @changes    1.0.0b10  Added the ::getDatabases() method [wb, 2009-02-24]

    29. 

  29.  * @changes    1.0.0b9   Now detects unsigned and zerofill MySQL data types that do not have a parenthetical part [wb, 2009-02-16]

    30. 

  30.  * @changes    1.0.0b8   Mapped the MySQL data type `'set'` to `'varchar'`, however valid values are not implemented yet [wb, 2009-02-01]

    31. 

  31.  * @changes    1.0.0b7   Fixed a bug with detecting MySQL timestamp columns [wb, 2009-01-28]

    32. 

  32.  * @changes    1.0.0b6   Fixed a bug with detecting MySQL columns that accept `NULL` [wb, 2009-01-19]

    33. 

  33.  * @changes    1.0.0b5   ::setColumnInfo(): fixed a bug with not grabbing the real database schema first, made general improvements [wb, 2009-01-19]

    34. 

  34.  * @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]

    35. 

  35.  * @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]

    36. 

  36.  * @changes    1.0.0b2   Fixed a bug with detecting multi-column unique keys in MySQL [wb, 2009-01-03]

    37. 

  37.  * @changes    1.0.0b    The initial implementation [wb, 2007-09-25]

    38. 

  38.  */

    39. 

  39. class fSchema

    40. 

  40. {

    41. 

  41. 	/**

    42. 

  42. 	 * The place to cache to

    43. 

  43. 	 * 

    44. 

  44. 	 * @var fCache

    45. 

  45. 	 */

    46. 

  46. 	private $cache = NULL;

    47. 

  47. 	

    48. 

  48. 	/**

    49. 

  49. 	 * The cached column info

    50. 

  50. 	 * 

    51. 

  51. 	 * @var array

    52. 

  52. 	 */

    53. 

  53. 	private $column_info = array();

    54. 

  54. 	

    55. 

  55. 	/**

    56. 

  56. 	 * The column info to override

    57. 

  57. 	 * 

    58. 

  58. 	 * @var array

    59. 

  59. 	 */

    60. 

  60. 	private $column_info_override = array();

    61. 

  61. 	

    62. 

  62. 	/**

    63. 

  63. 	 * A reference to an instance of the fDatabase class

    64. 

  64. 	 * 

    65. 

  65. 	 * @var fDatabase

    66. 

  66. 	 */

    67. 

  67. 	private $database = NULL;

    68. 

  68. 	

    69. 

  69. 	/**

    70. 

  70. 	 * The databases on the current database server

    71. 

  71. 	 * 

    72. 

  72. 	 * @var array

    73. 

  73. 	 */

    74. 

  74. 	private $databases = NULL;

    75. 

  75. 	

    76. 

  76. 	/**

    77. 

  77. 	 * The cached key info

    78. 

  78. 	 * 

    79. 

  79. 	 * @var array

    80. 

  80. 	 */

    81. 

  81. 	private $keys = array();

    82. 

  82. 	

    83. 

  83. 	/**

    84. 

  84. 	 * The key info to override

    85. 

  85. 	 * 

    86. 

  86. 	 * @var array

    87. 

  87. 	 */

    88. 

  88. 	private $keys_override = array();

    89. 

  89. 	

    90. 

  90. 	/**

    91. 

  91. 	 * The merged column info

    92. 

  92. 	 * 

    93. 

  93. 	 * @var array

    94. 

  94. 	 */

    95. 

  95. 	private $merged_column_info = array();

    96. 

  96. 	

    97. 

  97. 	/**

    98. 

  98. 	 * The merged key info

    99. 

  99. 	 * 

    100. 

  100. 	 * @var array

    101. 

  101. 	 */

    102. 

  102. 	private $merged_keys = array();

    103. 

  103. 	

    104. 

  104. 	/**

    105. 

  105. 	 * The relationships in the database

    106. 

  106. 	 * 

    107. 

  107. 	 * @var array

    108. 

  108. 	 */

    109. 

  109. 	private $relationships = array();

    110. 

  110. 	

    111. 

  111. 	/**

    112. 

  112. 	 * The tables in the database

    113. 

  113. 	 * 

    114. 

  114. 	 * @var array

    115. 

  115. 	 */

    116. 

  116. 	private $tables = NULL;

    117. 

  117. 	

    118. 

  118. 	

    119. 

  119. 	/**

    120. 

  120. 	 * Sets the database

    121. 

  121. 	 * 

    122. 

  122. 	 * @param  fDatabase $database  The fDatabase instance

    123. 

  123. 	 * @return fSchema

    124. 

  124. 	 */

    125. 

  125. 	public function __construct($database)

    126. 

  126. 	{

    127. 

  127. 		$this->database = $database;

    128. 

  128. 	}

    129. 

  129. 	

    130. 

  130. 	

    131. 

  131. 	/**

    132. 

  132. 	 * All requests that hit this method should be requests for callbacks

    133. 

  133. 	 * 

    134. 

  134. 	 * @internal

    135. 

  135. 	 * 

    136. 

  136. 	 * @param  string $method  The method to create a callback for

    137. 

  137. 	 * @return callback  The callback for the method requested

    138. 

  138. 	 */

    139. 

  139. 	public function __get($method)

    140. 

  140. 	{

    141. 

  141. 		return array($this, $method);		

    142. 

  142. 	}

    143. 

  143. 	

    144. 

  144. 	

    145. 

  145. 	/**

    146. 

  146. 	 * Checks to see if a column is part of a single-column `UNIQUE` key

    147. 

  147. 	 * 

    148. 

  148. 	 * @param  string $table   The table the column is located in

    149. 

  149. 	 * @param  string $column  The column to check

    150. 

  150. 	 * @return boolean  If the column is part of a single-column unique key

    151. 

  151. 	 */

    152. 

  152. 	private function checkForSingleColumnUniqueKey($table, $column)

    153. 

  153. 	{        

    154. 

  154. 		foreach ($this->merged_keys[$table]['unique'] as $key) {

    155. 

  155. 			if (array($column) == $key) {

    156. 

  156. 				return TRUE;

    157. 

  157. 			}

    158. 

  158. 		}

    159. 

  159. 		if (array($column) == $this->merged_keys[$table]['primary']) {

    160. 

  160. 			return TRUE;

    161. 

  161. 		}

    162. 

  162. 		return FALSE;

    163. 

  163. 	}

    164. 

  164. 	

    165. 

  165. 	

    166. 

  166. 	/**

    167. 

  167. 	 * Clears all of the schema info out of the object and, if set, the fCache object

    168. 

  168. 	 * 

    169. 

  169. 	 * @internal

    170. 

  170. 	 * 

    171. 

  171. 	 * @return void

    172. 

  172. 	 */

    173. 

  173. 	public function clearCache()

    174. 

  174. 	{

    175. 

  175. 		$this->column_info        = array();

    176. 

  176. 		$this->databases          = NULL;

    177. 

  177. 		$this->keys               = array();

    178. 

  178. 		$this->merged_column_info = array();

    179. 

  179. 		$this->merged_keys        = array();

    180. 

  180. 		$this->relationships      = array();

    181. 

  181. 		$this->tables             = NULL;

    182. 

  182. 		if ($this->cache) {

    183. 

  183. 			$prefix = $this->makeCachePrefix();

    184. 

  184. 			$this->cache->delete($prefix . 'column_info');

    185. 

  185. 			$this->cache->delete($prefix . 'databases');

    186. 

  186. 			$this->cache->delete($prefix . 'keys');

    187. 

  187. 			$this->cache->delete($prefix . 'merged_column_info');

    188. 

  188. 			$this->cache->delete($prefix . 'merged_keys');

    189. 

  189. 			$this->cache->delete($prefix . 'relationships');

    190. 

  190. 			$this->cache->delete($prefix . 'tables');

    191. 

  191. 		}

    192. 

  192. 	}

    193. 

  193. 	

    194. 

  194. 	

    195. 

  195. 	/**

    196. 

  196. 	 * Sets the schema to be cached to the fCache object specified

    197. 

  197. 	 * 

    198. 

  198. 	 * @param  fCache $cache  The cache to cache to

    199. 

  199. 	 * @return void

    200. 

  200. 	 */

    201. 

  201. 	public function enableCaching($cache)

    202. 

  202. 	{

    203. 

  203. 		$this->cache = $cache;

    204. 

  204. 		

    205. 

  205. 		$prefix = $this->makeCachePrefix();

    206. 

  206. 		$this->column_info        = $this->cache->get($prefix . 'column_info',          array());

    207. 

  207. 		$this->databases          = $this->cache->get($prefix . 'databases',            NULL);

    208. 

  208. 		$this->keys               = $this->cache->get($prefix . 'keys',                 array());

    209. 

  209. 		

    210. 

  210. 		if (!$this->column_info_override && !$this->keys_override) {

    211. 

  211. 			$this->merged_column_info = $this->cache->get($prefix . 'merged_column_info',   array());

    212. 

  212. 			$this->merged_keys        = $this->cache->get($prefix . 'merged_keys',          array());  

    213. 

  213. 			$this->relationships      = $this->cache->get($prefix . 'relationships',        array());

    214. 

  214. 		}

    215. 

  215. 		

    216. 

  216. 		$this->tables             = $this->cache->get($prefix . 'tables',               NULL);   

    217. 

  217. 	}

    218. 

  218. 	

    219. 

  219. 	

    220. 

  220. 	/**

    221. 

  221. 	 * Gets the column info from the database for later access

    222. 

  222. 	 * 

    223. 

  223. 	 * @param  string $table  The table to fetch the column info for

    224. 

  224. 	 * @return void

    225. 

  225. 	 */

    226. 

  226. 	private function fetchColumnInfo($table)

    227. 

  227. 	{

    228. 

  228. 		if (isset($this->column_info[$table])) {

    229. 

  229. 			return;	

    230. 

  230. 		}

    231. 

  231. 		

    232. 

  232. 		switch ($this->database->getType()) {

    233. 

  233. 			case 'mssql':

    234. 

  234. 				$column_info = $this->fetchMSSQLColumnInfo($table);

    235. 

  235. 				break;

    236. 

  236. 			

    237. 

  237. 			case 'mysql':

    238. 

  238. 				$column_info = $this->fetchMySQLColumnInfo($table);

    239. 

  239. 				break;

    240. 

  240. 				

    241. 

  241. 			case 'oracle':

    242. 

  242. 				$column_info = $this->fetchOracleColumnInfo($table);

    243. 

  243. 				break;

    244. 

  244. 			

    245. 

  245. 			case 'postgresql':

    246. 

  246. 				$column_info = $this->fetchPostgreSQLColumnInfo($table);

    247. 

  247. 				break;

    248. 

  248. 				

    249. 

  249. 			case 'sqlite':

    250. 

  250. 				$column_info = $this->fetchSQLiteColumnInfo($table);

    251. 

  251. 				break;

    252. 

  252. 		}

    253. 

  253. 			

    254. 

  254. 		if (!$column_info) {

    255. 

  255. 			return;	

    256. 

  256. 		}

    257. 

  257. 			

    258. 

  258. 		$this->column_info[$table] = $column_info;

    259. 

  259. 		if ($this->cache) {

    260. 

  260. 			$this->cache->set($this->makeCachePrefix() . 'column_info', $this->column_info);	

    261. 

  261. 		}

    262. 

  262. 	}

    263. 

  263. 	

    264. 

  264. 	

    265. 

  265. 	/**

    266. 

  266. 	 * Gets the `PRIMARY KEY`, `FOREIGN KEY` and `UNIQUE` key constraints from the database

    267. 

  267. 	 * 

    268. 

  268. 	 * @return void

    269. 

  269. 	 */

    270. 

  270. 	private function fetchKeys()

    271. 

  271. 	{

    272. 

  272. 		if ($this->keys) {

    273. 

  273. 			return;	

    274. 

  274. 		}

    275. 

  275. 		

    276. 

  276. 		switch ($this->database->getType()) {

    277. 

  277. 			case 'mssql':

    278. 

  278. 				$keys = $this->fetchMSSQLKeys();

    279. 

  279. 				break;

    280. 

  280. 				

    281. 

  281. 			case 'mysql':

    282. 

  282. 				$keys = $this->fetchMySQLKeys();

    283. 

  283. 				break;

    284. 

  284. 				

    285. 

  285. 			case 'oracle':

    286. 

  286. 				$keys = $this->fetchOracleKeys();

    287. 

  287. 				break;

    288. 

  288. 			

    289. 

  289. 			case 'postgresql':

    290. 

  290. 				$keys = $this->fetchPostgreSQLKeys();

    291. 

  291. 				break;

    292. 

  292. 			

    293. 

  293. 			case 'sqlite':

    294. 

  294. 				$keys = $this->fetchSQLiteKeys();

    295. 

  295. 				break;

    296. 

  296. 		}

    297. 

  297. 			

    298. 

  298. 		$this->keys = $keys;

    299. 

  299. 		if ($this->cache) {

    300. 

  300. 			$this->cache->set($this->makeCachePrefix() . 'keys', $this->keys);	

    301. 

  301. 		}

    302. 

  302. 	}

    303. 

  303. 	

    304. 

  304. 	

    305. 

  305. 	/**

    306. 

  306. 	 * Gets the column info from a MSSQL database

    307. 

  307. 	 * 

    308. 

  308. 	 * The returned array is in the format:

    309. 

  309. 	 * 

    310. 

  310. 	 * {{{

    311. 

  311. 	 * array(

    312. 

  312. 	 *     (string) {column name} => array(

    313. 

  313. 	 *         'type'           => (string)  {data type},

    314. 

  314. 	 *         'not_null'       => (boolean) {if value can't be null},

    315. 

  315. 	 *         'default'        => (mixed)   {the default value-may contain special string CURRENT_TIMESTAMP},

    316. 

  316. 	 *         'valid_values'   => (array)   {the valid values for a char/varchar field},

    317. 

  317. 	 *         'max_length'     => (integer) {the maximum length in a char/varchar field},

    318. 

  318. 	 *         'decimal_places' => (integer) {the number of decimal places for a decimal/numeric/money/smallmoney field},

    319. 

  319. 	 *         'auto_increment' => (boolean) {if the integer primary key column is an identity column}

    320. 

  320. 	 *     ), ...

    321. 

  321. 	 * )

    322. 

  322. 	 * }}}

    323. 

  323. 	 * 

    324. 

  324. 	 * @param  string $table  The table to fetch the column info for

    325. 

  325. 	 * @return array  The column info for the table specified - see method description for details

    326. 

  326. 	 */

    327. 

  327. 	private function fetchMSSQLColumnInfo($table)

    328. 

  328. 	{

    329. 

  329. 		$column_info = array();

    330. 

  330. 		

    331. 

  331. 		$data_type_mapping = array(

    332. 

  332. 			'bit'			    => 'boolean',

    333. 

  333. 			'tinyint'           => 'integer',

    334. 

  334. 			'smallint'			=> 'integer',

    335. 

  335. 			'int'				=> 'integer',

    336. 

  336. 			'bigint'			=> 'integer',

    337. 

  337. 			'datetime'			=> 'timestamp',

    338. 

  338. 			'smalldatetime'     => 'timestamp',

    339. 

  339. 			'datetime2'         => 'timestamp',

    340. 

  340. 			'date'              => 'date',

    341. 

  341. 			'time'              => 'time',

    342. 

  342. 			'varchar'	        => 'varchar',

    343. 

  343. 			'nvarchar'          => 'varchar',

    344. 

  344. 			'char'			    => 'char',

    345. 

  345. 			'nchar'             => 'char',

    346. 

  346. 			'real'				=> 'float',

    347. 

  347. 			'float'             => 'float',

    348. 

  348. 			'money'             => 'float',

    349. 

  349. 			'smallmoney'        => 'float',

    350. 

  350. 			'decimal'			=> 'float',

    351. 

  351. 			'numeric'			=> 'float',

    352. 

  352. 			'binary'			=> 'blob',

    353. 

  353. 			'varbinary'         => 'blob',

    354. 

  354. 			'image'             => 'blob',

    355. 

  355. 			'text'				=> 'text',

    356. 

  356. 			'ntext'             => 'text'

    357. 

  357. 		);

    358. 

  358. 		

    359. 

  359. 		// Get the column info

    360. 

  360. 		$sql = "SELECT

    361. 

  361. 						c.column_name              AS 'column',

    362. 

  362. 						c.data_type                AS 'type',

    363. 

  363. 						c.is_nullable              AS nullable,

    364. 

  364. 						c.column_default           AS 'default',

    365. 

  365. 						c.character_maximum_length AS max_length,

    366. 

  366. 						c.numeric_scale            AS decimal_places,

    367. 

  367. 						CASE

    368. 

  368. 							WHEN

    369. 

  369. 							  COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND

    370. 

  370. 							  OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0

    371. 

  371. 							THEN '1'

    372. 

  372. 							ELSE '0'

    373. 

  373. 						  END AS auto_increment,

    374. 

  374. 						cc.check_clause AS 'constraint'

    375. 

  375. 					FROM

    376. 

  376. 						INFORMATION_SCHEMA.COLUMNS AS c LEFT JOIN

    377. 

  377. 						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

    378. 

  378. 						INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON ccu.constraint_name = cc.constraint_name AND ccu.constraint_catalog = cc.constraint_catalog

    379. 

  379. 					WHERE

    380. 

  380. 						c.table_name = '" . $table . "' AND

    381. 

  381. 						c.table_catalog = DB_NAME()";

    382. 

  382. 		$result = $this->database->query($sql);

    383. 

  383. 		

    384. 

  384. 		foreach ($result as $row) {

    385. 

  385. 			

    386. 

  386. 			$info = array();

    387. 

  387. 			

    388. 

  388. 			foreach ($data_type_mapping as $data_type => $mapped_data_type) {

    389. 

  389. 				if (stripos($row['type'], $data_type) === 0) {

    390. 

  390. 					$info['type'] = $mapped_data_type;

    391. 

  391. 					break;

    392. 

  392. 				}

    393. 

  393. 			}

    394. 

  394. 			

    395. 

  395. 			if (!isset($info['type'])) {

    396. 

  396. 				$info['type'] = $row['type'];

    397. 

  397. 			}

    398. 

  398. 			

    399. 

  399. 			// Handle decimal places for numeric/decimals

    400. 

  400. 			if (in_array($row['type'], array('numeric', 'decimal'))) {

    401. 

  401. 				$info['decimal_places'] = $row['decimal_places'];

    402. 

  402. 			}

    403. 

  403. 			

    404. 

  404. 			// Handle decimal places for money/smallmoney

    405. 

  405. 			if (in_array($row['type'], array('money', 'smallmoney'))) {

    406. 

  406. 				$info['decimal_places'] = 2;

    407. 

  407. 			}

    408. 

  408. 			

    409. 

  409. 			// Handle the special data for varchar columns

    410. 

  410. 			if (in_array($info['type'], array('char', 'varchar'))) {

    411. 

  411. 				$info['max_length'] = $row['max_length'];

    412. 

  412. 			}

    413. 

  413. 			

    414. 

  414. 			// If the column has a constraint, look for valid values

    415. 

  415. 			if (in_array($info['type'], array('char', 'varchar')) && !empty($row['constraint'])) {

    416. 

  416. 				if (preg_match('#^\(((?:(?: OR )?\[[^\]]+\]\s*=\s*\'(?:\'\'|[^\']+)+\')+)\)$#D', $row['constraint'], $matches)) {

    417. 

  417. 					$valid_values = explode(' OR ', $matches[1]);

    418. 

  418. 					foreach ($valid_values as $key => $value) {

    419. 

  419. 						$value = preg_replace('#^\s*\[' . preg_quote($row['column'], '#') . '\]\s*=\s*\'(.*)\'\s*$#', '\1', $value);

    420. 

  420. 						$valid_values[$key] = str_replace("''", "'", $value);

    421. 

  421. 					}

    422. 

  422. 					// SQL Server turns CHECK constraint values into a reversed list, so we fix it here

    423. 

  423. 					$info['valid_values'] = array_reverse($valid_values);

    424. 

  424. 				}

    425. 

  425. 			}

    426. 

  426. 			

    427. 

  427. 			// Handle auto increment

    428. 

  428. 			if ($row['auto_increment']) {

    429. 

  429. 				$info['auto_increment'] = TRUE;

    430. 

  430. 			}

    431. 

  431. 			

    432. 

  432. 			// Handle default values

    433. 

  433. 			if ($row['default'] !== NULL) {

    434. 

  434. 				if ($row['default'] == '(getdate())') {

    435. 

  435. 					$info['default'] = 'CURRENT_TIMESTAMP';

    436. 

  436. 				} elseif (in_array($info['type'], array('char', 'varchar', 'text', 'timestamp')) ) {

    437. 

  437. 					$info['default'] = substr($row['default'], 2, -2);

    438. 

  438. 				} elseif ($info['type'] == 'boolean') {

    439. 

  439. 					$info['default'] = (boolean) substr($row['default'], 2, -2);

    440. 

  440. 				} elseif (in_array($info['type'], array('integer', 'float')) ) {

    441. 

  441. 					$info['default'] = str_replace(array('(', ')'), '', $row['default']);

    442. 

  442. 				} else {

    443. 

  443. 					$info['default'] = pack('H*', substr($row['default'], 3, -1));

    444. 

  444. 				}

    445. 

  445. 			}

    446. 

  446. 			

    447. 

  447. 			// Handle not null

    448. 

  448. 			$info['not_null'] = ($row['nullable'] == 'NO') ? TRUE : FALSE;

    449. 

  449. 			

    450. 

  450. 			$column_info[$row['column']] = $info;

    451. 

  451. 		}

    452. 

  452. 		

    453. 

  453. 		return $column_info;

    454. 

  454. 	}

    455. 

  455. 	

    456. 

  456. 	

    457. 

  457. 	/**

    458. 

  458. 	 * Fetches the key info for an MSSQL database

    459. 

  459. 	 * 

    460. 

  460. 	 * The structure of the returned array is:

    461. 

  461. 	 * 

    462. 

  462. 	 * {{{

    463. 

  463. 	 * array(

    464. 

  464. 	 *      'primary' => array(

    465. 

  465. 	 *          {column name}, ...

    466. 

  466. 	 *      ),

    467. 

  467. 	 *      'unique'  => array(

    468. 

  468. 	 *          array(

    469. 

  469. 	 *              {column name}, ...

    470. 

  470. 	 *          ), ...

    471. 

  471. 	 *      ),

    472. 

  472. 	 *      'foreign' => array(

    473. 

  473. 	 *          array(

    474. 

  474. 	 *              'column'         => {column name},

    475. 

  475. 	 *              'foreign_table'  => {foreign table name},

    476. 

  476. 	 *              'foreign_column' => {foreign column name},

    477. 

  477. 	 *              'on_delete'      => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    478. 

  478. 	 *              'on_update'      => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    479. 

  479. 	 *          ), ...

    480. 

  480. 	 *      )

    481. 

  481. 	 * )

    482. 

  482. 	 * }}}

    483. 

  483. 	 * 

    484. 

  484. 	 * @return array  The key info arrays for every table in the database - see method description for details

    485. 

  485. 	 */

    486. 

  486. 	private function fetchMSSQLKeys()

    487. 

  487. 	{

    488. 

  488. 		$keys = array();

    489. 

  489. 		

    490. 

  490. 		$tables   = $this->getTables();

    491. 

  491. 		foreach ($tables as $table) {

    492. 

  492. 			$keys[$table] = array();

    493. 

  493. 			$keys[$table]['primary'] = array();

    494. 

  494. 			$keys[$table]['unique']  = array();

    495. 

  495. 			$keys[$table]['foreign'] = array();

    496. 

  496. 		}

    497. 

  497. 		

    498. 

  498. 		$sql  = "SELECT

    499. 

  499. 						c.table_name AS 'table',

    500. 

  500. 						kcu.constraint_name AS constraint_name,

    501. 

  501. 						CASE c.constraint_type

    502. 

  502. 							WHEN 'PRIMARY KEY' THEN 'primary'

    503. 

  503. 							WHEN 'FOREIGN KEY' THEN 'foreign'

    504. 

  504. 							WHEN 'UNIQUE' THEN 'unique'

    505. 

  505. 						END AS 'type',

    506. 

  506. 						kcu.column_name AS 'column',

    507. 

  507. 						ccu.table_name AS foreign_table,

    508. 

  508. 						ccu.column_name AS foreign_column,

    509. 

  509. 						REPLACE(LOWER(rc.delete_rule), ' ', '_') AS on_delete,

    510. 

  510. 						REPLACE(LOWER(rc.update_rule), ' ', '_') AS on_update

    511. 

  511. 					FROM

    512. 

  512. 						INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN

    513. 

  513. 						INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON c.table_name = kcu.table_name AND c.constraint_name = kcu.constraint_name LEFT JOIN

    514. 

  514. 						INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON c.constraint_name = rc.constraint_name LEFT JOIN

    515. 

  515. 						INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.constraint_name = rc.unique_constraint_name

    516. 

  516. 					WHERE

    517. 

  517. 						c.constraint_catalog = DB_NAME() AND

    518. 

  518. 						c.table_name != 'sysdiagrams'

    519. 

  519. 					ORDER BY

    520. 

  520. 						LOWER(c.table_name),

    521. 

  521. 						c.constraint_type,

    522. 

  522. 						LOWER(kcu.constraint_name),

    523. 

  523. 						kcu.ordinal_position,

    524. 

  524. 						LOWER(kcu.column_name)";

    525. 

  525. 		

    526. 

  526. 		$result = $this->database->query($sql);

    527. 

  527. 		

    528. 

  528. 		$last_name  = '';

    529. 

  529. 		$last_table = '';

    530. 

  530. 		$last_type  = '';

    531. 

  531. 		foreach ($result as $row) {

    532. 

  532. 			

    533. 

  533. 			if ($row['constraint_name'] != $last_name) {

    534. 

  534. 				

    535. 

  535. 				if ($last_name) {

    536. 

  536. 					if ($last_type == 'foreign' || $last_type == 'unique') {

    537. 

  537. 						if (!isset($keys[$last_table][$last_type])) {

    538. 

  538. 							$keys[$last_table][$last_type] = array();		

    539. 

  539. 						}

    540. 

  540. 						$keys[$last_table][$last_type][] = $temp;

    541. 

  541. 					} else {

    542. 

  542. 						$keys[$last_table][$last_type] = $temp;

    543. 

  543. 					}

    544. 

  544. 				}

    545. 

  545. 				

    546. 

  546. 				$temp = array();

    547. 

  547. 				if ($row['type'] == 'foreign') {

    548. 

  548. 					

    549. 

  549. 					$temp['column']         = $row['column'];

    550. 

  550. 					$temp['foreign_table']  = $row['foreign_table'];

    551. 

  551. 					$temp['foreign_column'] = $row['foreign_column'];

    552. 

  552. 					$temp['on_delete']      = 'no_action';

    553. 

  553. 					$temp['on_update']      = 'no_action';

    554. 

  554. 					if (!empty($row['on_delete'])) {

    555. 

  555. 						$temp['on_delete'] = $row['on_delete'];

    556. 

  556. 					}

    557. 

  557. 					if (!empty($row['on_update'])) {

    558. 

  558. 						$temp['on_update'] = $row['on_update'];

    559. 

  559. 					}

    560. 

  560. 					

    561. 

  561. 				} else {

    562. 

  562. 					$temp[] = $row['column'];

    563. 

  563. 				}

    564. 

  564. 				

    565. 

  565. 				$last_table = $row['table'];

    566. 

  566. 				$last_name  = $row['constraint_name'];

    567. 

  567. 				$last_type  = $row['type'];

    568. 

  568. 				

    569. 

  569. 			} else {

    570. 

  570. 				$temp[] = $row['column'];

    571. 

  571. 			}

    572. 

  572. 		}

    573. 

  573. 		

    574. 

  574. 		if (isset($temp)) {

    575. 

  575. 			if ($last_type == 'foreign') {

    576. 

  576. 				if (!isset($keys[$last_table][$last_type])) {

    577. 

  577. 					$keys[$last_table][$last_type] = array();		

    578. 

  578. 				}

    579. 

  579. 				$keys[$last_table][$last_type][] = $temp;

    580. 

  580. 			} else {

    581. 

  581. 				$keys[$last_table][$last_type] = $temp;

    582. 

  582. 			}

    583. 

  583. 		}

    584. 

  584. 		

    585. 

  585. 		return $keys;

    586. 

  586. 	}

    587. 

  587. 	

    588. 

  588. 	

    589. 

  589. 	/**

    590. 

  590. 	 * Gets the column info from a MySQL database

    591. 

  591. 	 * 

    592. 

  592. 	 * The returned array is in the format:

    593. 

  593. 	 * 

    594. 

  594. 	 * {{{

    595. 

  595. 	 * array(

    596. 

  596. 	 *     (string) {column name} => array(

    597. 

  597. 	 *         'type'           => (string)  {data type},

    598. 

  598. 	 *         'not_null'       => (boolean) {if value can't be null},

    599. 

  599. 	 *         'default'        => (mixed)   {the default value-may contain special string CURRENT_TIMESTAMP},

    600. 

  600. 	 *         'valid_values'   => (array)   {the valid values for a char/varchar field},

    601. 

  601. 	 *         'max_length'     => (integer) {the maximum length in a char/varchar field},

    602. 

  602. 	 *         'decimal_places' => (integer) {the number of decimal places for a decimal field},

    603. 

  603. 	 *         'auto_increment' => (boolean) {if the integer primary key column is auto_increment}

    604. 

  604. 	 *     ), ...

    605. 

  605. 	 * )

    606. 

  606. 	 * }}}

    607. 

  607. 	 * 

    608. 

  608. 	 * @param  string $table  The table to fetch the column info for

    609. 

  609. 	 * @return array  The column info for the table specified - see method description for details

    610. 

  610. 	 */

    611. 

  611. 	private function fetchMySQLColumnInfo($table)

    612. 

  612. 	{

    613. 

  613. 		$data_type_mapping = array(

    614. 

  614. 			'tinyint'			=> 'integer',

    615. 

  615. 			'smallint'			=> 'integer',

    616. 

  616. 			'mediumint'         => 'integer',

    617. 

  617. 			'int'				=> 'integer',

    618. 

  618. 			'bigint'			=> 'integer',

    619. 

  619. 			'datetime'			=> 'timestamp',

    620. 

  620. 			'timestamp'			=> 'timestamp',

    621. 

  621. 			'date'				=> 'date',

    622. 

  622. 			'time'				=> 'time',

    623. 

  623. 			'enum'				=> 'varchar',

    624. 

  624. 			'set'               => 'varchar',

    625. 

  625. 			'varchar'			=> 'varchar',

    626. 

  626. 			'char'				=> 'char',

    627. 

  627. 			'float'				=> 'float',

    628. 

  628. 			'double'			=> 'float',

    629. 

  629. 			'decimal'			=> 'float',

    630. 

  630. 			'binary'            => 'blob',

    631. 

  631. 			'varbinary'         => 'blob',

    632. 

  632. 			'tinyblob'			=> 'blob',

    633. 

  633. 			'blob'				=> 'blob',

    634. 

  634. 			'mediumblob'		=> 'blob',

    635. 

  635. 			'longblob'			=> 'blob',

    636. 

  636. 			'tinytext'			=> 'text',

    637. 

  637. 			'text'				=> 'text',

    638. 

  638. 			'mediumtext'		=> 'text',

    639. 

  639. 			'longtext'			=> 'text'

    640. 

  640. 		);

    641. 

  641. 		

    642. 

  642. 		$column_info = array();

    643. 

  643. 		

    644. 

  644. 		$result     = $this->database->query('SHOW CREATE TABLE ' . $table);

    645. 

  645. 		

    646. 

  646. 		try {

    647. 

  647. 			$row        = $result->fetchRow();

    648. 

  648. 			$create_sql = $row['Create Table'];

    649. 

  649. 		} catch (fNoRowsException $e) {

    650. 

  650. 			return array();			

    651. 

  651. 		}

    652. 

  652. 		

    653. 

  653. 		preg_match_all('#(?<=,|\()\s+(?:"|\`)(\w+)(?:"|\`)\s+(?:([a-z]+)(?:\(([^)]+)\))?(?: unsigned| zerofill){0,2})(?: character set [^ ]+)?(?: collate [^ ]+)?(?: NULL)?( NOT NULL)?(?: DEFAULT ((?:[^, \']*|\'(?:\'\'|[^\']+)*\')))?( auto_increment)?( COMMENT \'(?:\'\'|[^\']+)*\')?( ON UPDATE CURRENT_TIMESTAMP)?\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);

    654. 

  654. 		

    655. 

  655. 		foreach ($matches as $match) {

    656. 

  656. 			

    657. 

  657. 			$info = array();

    658. 

  658. 			

    659. 

  659. 			foreach ($data_type_mapping as $data_type => $mapped_data_type) {

    660. 

  660. 				if (stripos($match[2], $data_type) === 0) {

    661. 

  661. 					if ($match[2] == 'tinyint' && $match[3] == 1) {

    662. 

  662. 						$mapped_data_type = 'boolean';	

    663. 

  663. 					}

    664. 

  664. 				

    665. 

  665. 					$info['type'] = $mapped_data_type;

    666. 

  666. 					break;

    667. 

  667. 				}

    668. 

  668. 			}

    669. 

  669. 			if (!isset($info['type'])) {

    670. 

  670. 				$info['type'] = preg_replace('#^([a-z ]+).*$#iD', '\1', $match[2]);

    671. 

  671. 			}

    672. 

  672. 		

    673. 

  673. 			if (stripos($match[2], 'enum') === 0) {

    674. 

  674. 				$info['valid_values'] = preg_replace("/^'|'\$/D", '', explode(",", $match[3]));

    675. 

  675. 				$match[3] = 0;

    676. 

  676. 				foreach ($info['valid_values'] as $valid_value) {

    677. 

  677. 					if (strlen(utf8_decode($valid_value)) > $match[3]) {

    678. 

  678. 						$match[3] = strlen(utf8_decode($valid_value));

    679. 

  679. 					}

    680. 

  680. 				}

    681. 

  681. 			}

    682. 

  682. 			

    683. 

  683. 			// The set data type is currently only supported as a varchar

    684. 

  684. 			// with a max length of all valid values concatenated by ,s

    685. 

  685. 			if (stripos($match[2], 'set') === 0) {

    686. 

  686. 				$values = preg_replace("/^'|'\$/D", '', explode(",", $match[3]));

    687. 

  687. 				$match[3] = strlen(join(',', $values));

    688. 

  688. 			}

    689. 

  689. 			

    690. 

  690. 			// Type specific information

    691. 

  691. 			if (in_array($info['type'], array('char', 'varchar'))) {

    692. 

  692. 				$info['max_length'] = $match[3];

    693. 

  693. 			}

    694. 

  694. 			

    695. 

  695. 			// Grab the number of decimal places

    696. 

  696. 			if (stripos($match[2], 'decimal') === 0) {

    697. 

  697. 				if (preg_match('#^\s*\d+\s*,\s*(\d+)\s*$#D', $match[3], $data_type_info)) {

    698. 

  698. 					$info['decimal_places'] = $data_type_info[1];

    699. 

  699. 				}

    700. 

  700. 			}

    701. 

  701. 			

    702. 

  702. 			// Not null

    703. 

  703. 			$info['not_null'] = (!empty($match[4])) ? TRUE : FALSE;

    704. 

  704. 		

    705. 

  705. 			// Default values

    706. 

  706. 			if (!empty($match[5]) && $match[5] != 'NULL') {

    707. 

  707. 				$info['default'] = preg_replace("/^'|'\$/D", '', $match[5]);

    708. 

  708. 			}

    709. 

  709. 			

    710. 

  710. 			if ($info['type'] == 'boolean' && isset($info['default'])) {

    711. 

  711. 				$info['default'] = (boolean) $info['default'];

    712. 

  712. 			}

    713. 

  713. 		

    714. 

  714. 			// Auto increment fields

    715. 

  715. 			if (!empty($match[6])) {

    716. 

  716. 				$info['auto_increment'] = TRUE;

    717. 

  717. 			}

    718. 

  718. 		

    719. 

  719. 			$column_info[$match[1]] = $info;

    720. 

  720. 		}

    721. 

  721. 		

    722. 

  722. 		return $column_info;

    723. 

  723. 	}

    724. 

  724. 	

    725. 

  725. 	

    726. 

  726. 	/**

    727. 

  727. 	 * Fetches the keys for a MySQL database

    728. 

  728. 	 * 

    729. 

  729. 	 * The structure of the returned array is:

    730. 

  730. 	 * 

    731. 

  731. 	 * {{{

    732. 

  732. 	 * array(

    733. 

  733. 	 *      'primary' => array(

    734. 

  734. 	 *          {column name}, ...

    735. 

  735. 	 *      ),

    736. 

  736. 	 *      'unique'  => array(

    737. 

  737. 	 *          array(

    738. 

  738. 	 *              {column name}, ...

    739. 

  739. 	 *          ), ...

    740. 

  740. 	 *      ),

    741. 

  741. 	 *      'foreign' => array(

    742. 

  742. 	 *          array(

    743. 

  743. 	 *              'column'         => {column name},

    744. 

  744. 	 *              'foreign_table'  => {foreign table name},

    745. 

  745. 	 *              'foreign_column' => {foreign column name},

    746. 

  746. 	 *              'on_delete'      => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    747. 

  747. 	 *              'on_update'      => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    748. 

  748. 	 *          ), ...

    749. 

  749. 	 *      )

    750. 

  750. 	 * )

    751. 

  751. 	 * }}}

    752. 

  752. 	 * 

    753. 

  753. 	 * @return array  The keys arrays for every table in the database - see method description for details

    754. 

  754. 	 */

    755. 

  755. 	private function fetchMySQLKeys()

    756. 

  756. 	{

    757. 

  757. 		$tables   = $this->getTables();

    758. 

  758. 		$keys = array();

    759. 

  759. 		

    760. 

  760. 		foreach ($tables as $table) {

    761. 

  761. 			

    762. 

  762. 			$keys[$table] = array();

    763. 

  763. 			$keys[$table]['primary'] = array();

    764. 

  764. 			$keys[$table]['foreign'] = array();

    765. 

  765. 			$keys[$table]['unique']  = array();

    766. 

  766. 			

    767. 

  767. 			$result = $this->database->query('SHOW CREATE TABLE `' . substr($this->database->escape('string', $table), 1, -1) . '`');

    768. 

  768. 			$row    = $result->fetchRow();

    769. 

  769. 			

    770. 

  770. 			// Primary keys

    771. 

  771. 			preg_match_all('/PRIMARY KEY\s+\("(.*?)"\),?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);

    772. 

  772. 			if (!empty($matches)) {

    773. 

  773. 				$keys[$table]['primary'] = explode('","', $matches[0][1]);

    774. 

  774. 			}

    775. 

  775. 			

    776. 

  776. 			// Unique keys

    777. 

  777. 			preg_match_all('/UNIQUE KEY\s+"([^"]+)"\s+\("(.*?)"\),?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);

    778. 

  778. 			foreach ($matches as $match) {

    779. 

  779. 				$keys[$table]['unique'][] = explode('","', $match[2]);

    780. 

  780. 			}

    781. 

  781. 			

    782. 

  782. 			// Foreign keys

    783. 

  783. 			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);

    784. 

  784. 			foreach ($matches as $match) {

    785. 

  785. 				$temp = array('column'         => $match[1],

    786. 

  786. 							  'foreign_table'  => $match[2],

    787. 

  787. 							  'foreign_column' => $match[3],

    788. 

  788. 							  'on_delete'      => 'no_action',

    789. 

  789. 							  'on_update'      => 'no_action');

    790. 

  790. 				if (isset($match[4])) {

    791. 

  791. 					$temp['on_delete'] = strtolower(str_replace(' ', '_', $match[4]));

    792. 

  792. 				}

    793. 

  793. 				if (isset($match[5])) {

    794. 

  794. 					$temp['on_update'] = strtolower(str_replace(' ', '_', $match[5]));

    795. 

  795. 				}

    796. 

  796. 				$keys[$table]['foreign'][] = $temp;

    797. 

  797. 			}

    798. 

  798. 		}

    799. 

  799. 		

    800. 

  800. 		return $keys;

    801. 

  801. 	}

    802. 

  802. 	

    803. 

  803. 	

    804. 

  804. 	/**

    805. 

  805. 	 * Gets the column info from an Oracle database

    806. 

  806. 	 * 

    807. 

  807. 	 * The returned array is in the format:

    808. 

  808. 	 * 

    809. 

  809. 	 * {{{

    810. 

  810. 	 * array(

    811. 

  811. 	 *     (string) {column name} => array(

    812. 

  812. 	 *         'type'           => (string)  {data type},

    813. 

  813. 	 *         'not_null'       => (boolean) {if value can't be null},

    814. 

  814. 	 *         'default'        => (mixed)   {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},

    815. 

  815. 	 *         'valid_values'   => (array)   {the valid values for a char/varchar field},

    816. 

  816. 	 *         'max_length'     => (integer) {the maximum length in a char/varchar field},

    817. 

  817. 	 *         'decimal_places' => (integer) {the number of decimal places for a decimal field},

    818. 

  818. 	 *         'auto_increment' => (boolean) {if the integer primary key column is auto_increment}

    819. 

  819. 	 *     ), ...

    820. 

  820. 	 * )

    821. 

  821. 	 * }}}

    822. 

  822. 	 * 

    823. 

  823. 	 * @param  string $table  The table to fetch the column info for

    824. 

  824. 	 * @return array  The column info for the table specified - see method description for details

    825. 

  825. 	 */

    826. 

  826. 	private function fetchOracleColumnInfo($table)

    827. 

  827. 	{

    828. 

  828. 		$table = strtoupper($table);

    829. 

  829. 		

    830. 

  830. 		$column_info = array();

    831. 

  831. 		

    832. 

  832. 		$data_type_mapping = array(

    833. 

  833. 			'boolean'			=> 'boolean',

    834. 

  834. 			'integer'			=> 'integer',

    835. 

  835. 			'timestamp'			=> 'timestamp',

    836. 

  836. 			'date'				=> 'date',

    837. 

  837. 			'varchar2'          => 'varchar',

    838. 

  838. 			'nvarchar2'			=> 'varchar',

    839. 

  839. 			'char'              => 'char',

    840. 

  840. 			'nchar'             => 'char',

    841. 

  841. 			'float'				=> 'float',

    842. 

  842. 			'binary_float'      => 'float',

    843. 

  843. 			'binary_double'     => 'float',

    844. 

  844. 			'blob'				=> 'blob',

    845. 

  845. 			'bfile'             => 'varchar',

    846. 

  846. 			'clob'				=> 'text',

    847. 

  847. 			'nclob'             => 'text'

    848. 

  848. 		);

    849. 

  849. 		

    850. 

  850. 		$sql = "SELECT

    851. 

  851. 						LOWER(UTC.COLUMN_NAME) COLUMN_NAME,

    852. 

  852. 						CASE

    853. 

  853. 							WHEN

    854. 

  854. 								UTC.DATA_TYPE = 'NUMBER' AND

    855. 

  855. 								UTC.DATA_PRECISION IS NULL AND

    856. 

  856. 								UTC.DATA_SCALE = 0

    857. 

  857. 							THEN

    858. 

  858. 								'integer'

    859. 

  859. 							WHEN

    860. 

  860. 								UTC.DATA_TYPE = 'NUMBER' AND

    861. 

  861. 								UTC.DATA_PRECISION = 1 AND

    862. 

  862. 								UTC.DATA_SCALE = 0

    863. 

  863. 							THEN

    864. 

  864. 								'boolean'

    865. 

  865. 							WHEN

    866. 

  866. 								UTC.DATA_TYPE = 'NUMBER' AND

    867. 

  867. 								UTC.DATA_PRECISION IS NOT NULL AND

    868. 

  868. 								UTC.DATA_SCALE != 0 AND

    869. 

  869. 								UTC.DATA_SCALE IS NOT NULL

    870. 

  870. 							THEN

    871. 

  871. 								'float'

    872. 

  872. 							ELSE

    873. 

  873. 								LOWER(UTC.DATA_TYPE)

    874. 

  874. 							END DATA_TYPE,

    875. 

  875. 						CASE

    876. 

  876. 							WHEN

    877. 

  877. 								UTC.CHAR_LENGTH <> 0

    878. 

  878. 							THEN

    879. 

  879. 								UTC.CHAR_LENGTH

    880. 

  880. 							WHEN

    881. 

  881. 								UTC.DATA_TYPE = 'NUMBER' AND

    882. 

  882. 								UTC.DATA_PRECISION != 1 AND

    883. 

  883. 								UTC.DATA_SCALE != 0	AND

    884. 

  884. 								UTC.DATA_PRECISION IS NOT NULL

    885. 

  885. 							THEN

    886. 

  886. 								UTC.DATA_SCALE	

    887. 

  887. 							ELSE

    888. 

  888. 								NULL

    889. 

  889. 							END LENGTH,

    890. 

  890. 						UTC.NULLABLE,

    891. 

  891. 						UTC.DATA_DEFAULT,

    892. 

  892. 						UC.SEARCH_CONDITION CHECK_CONSTRAINT

    893. 

  893. 					FROM

    894. 

  894. 						USER_TAB_COLUMNS UTC LEFT JOIN

    895. 

  895. 						USER_CONS_COLUMNS UCC ON

    896. 

  896. 							UTC.COLUMN_NAME = UCC.COLUMN_NAME AND

    897. 

  897. 							UTC.TABLE_NAME = UCC.TABLE_NAME AND

    898. 

  898. 							UCC.POSITION IS NULL LEFT JOIN

    899. 

  899. 						USER_CONSTRAINTS UC ON

    900. 

  900. 							UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND

    901. 

  901. 							UC.CONSTRAINT_TYPE = 'C' AND

    902. 

  902. 							UC.STATUS = 'ENABLED'

    903. 

  903. 					WHERE

    904. 

  904. 						UTC.TABLE_NAME = %s

    905. 

  905. 					ORDER BY

    906. 

  906. 						UTC.TABLE_NAME ASC,

    907. 

  907. 						UTC.COLUMN_ID ASC";

    908. 

  908. 		$result = $this->database->query($sql, $table);

    909. 

  909. 		

    910. 

  910. 		foreach ($result as $row) {

    911. 

  911. 			

    912. 

  912. 			$column = $row['column_name'];

    913. 

  913. 			

    914. 

  914. 			// Since Oracle stores check constraints in LONG columns, it is

    915. 

  915. 			// not possible to check or modify the constraints in SQL which

    916. 

  916. 			// ends up causing multiple rows with duplicate data except for

    917. 

  917. 			// the check constraint

    918. 

  918. 			$duplicate = FALSE;

    919. 

  919. 			

    920. 

  920. 			if (isset($column_info[$column])) {

    921. 

  921. 				$info = $column_info[$column];

    922. 

  922. 				$duplicate = TRUE;	

    923. 

  923. 			} else {

    924. 

  924. 				$info = array();	

    925. 

  925. 			}

    926. 

  926. 			

    927. 

  927. 			if (!$duplicate) {

    928. 

  928. 				// Get the column type

    929. 

  929. 				foreach ($data_type_mapping as $data_type => $mapped_data_type) {

    930. 

  930. 					if (stripos($row['data_type'], $data_type) === 0) {

    931. 

  931. 						$info['type'] = $mapped_data_type;

    932. 

  932. 						break;

    933. 

  933. 					}

    934. 

  934. 				}

    935. 

  935. 				

    936. 

  936. 				if (!isset($info['type'])) {

    937. 

  937. 					$info['type'] = $row['data_type'];

    938. 

  938. 				}

    939. 

  939. 				

    940. 

  940. 				// Handle the length of decimal/numeric fields

    941. 

  941. 				if ($info['type'] == 'float' && $row['length']) {

    942. 

  942. 					$info['decimal_places'] = (int) $row['length'];

    943. 

  943. 				}

    944. 

  944. 				

    945. 

  945. 				// Handle the special data for varchar fields

    946. 

  946. 				if (in_array($info['type'], array('char', 'varchar'))) {

    947. 

  947. 					$info['max_length'] = (int) $row['length'];

    948. 

  948. 				}

    949. 

  949. 			}

    950. 

  950. 			

    951. 

  951. 			// Handle check constraints that are just simple lists

    952. 

  952. 			if (in_array($info['type'], array('varchar', 'char')) && $row['check_constraint']) {

    953. 

  953. 				if (preg_match('/^\s*' . preg_quote($column, '/') . '\s+in\s+\((.*?)\)\s*$/i', $row['check_constraint'], $match)) {

    954. 

  954. 					if (preg_match_all("/(?<!')'((''|[^']+)*)'/", $match[1], $matches, PREG_PATTERN_ORDER)) {

    955. 

  955. 						$info['valid_values'] = str_replace("''", "'", $matches[1]);

    956. 

  956. 					}			

    957. 

  957. 				}

    958. 

  958. 			}

    959. 

  959. 			

    960. 

  960. 			if (!$duplicate) {

    961. 

  961. 				// Handle default values

    962. 

  962. 				if ($row['data_default'] !== NULL) {

    963. 

  963. 					if (in_array($info['type'], array('char', 'varchar', 'text'))) {

    964. 

  964. 						$info['default'] = str_replace("''", "'", substr(trim($row['data_default']), 1, -1));

    965. 

  965. 						

    966. 

  966. 					} elseif ($info['type'] == 'boolean') {

    967. 

  967. 						$info['default'] = (boolean) trim($row['data_default']);

    968. 

  968. 						

    969. 

  969. 					} elseif (in_array($info['type'], array('integer', 'float'))) {

    970. 

  970. 						$info['default'] = trim($row['data_default']);

    971. 

  971. 						

    972. 

  972. 					} else {

    973. 

  973. 						$info['default'] = $row['data_default'];

    974. 

  974. 					}

    975. 

  975. 				}

    976. 

  976. 			

    977. 

  977. 				// Not null values

    978. 

  978. 				$info['not_null'] = ($row['nullable'] == 'N') ? TRUE : FALSE;

    979. 

  979. 			}

    980. 

  980. 			

    981. 

  981. 			$column_info[$column] = $info;

    982. 

  982. 		}

    983. 

  983. 		

    984. 

  984. 		$sql = "SELECT

    985. 

  985. 						TRIGGER_BODY

    986. 

  986. 					FROM

    987. 

  987. 						USER_TRIGGERS

    988. 

  988. 					WHERE

    989. 

  989. 						TRIGGERING_EVENT = 'INSERT' AND

    990. 

  990. 						STATUS = 'ENABLED' AND

    991. 

  991. 						TRIGGER_NAME NOT LIKE 'BIN\$%' AND

    992. 

  992. 						TABLE_NAME = %s";

    993. 

  993. 						

    994. 

  994. 		foreach ($this->database->query($sql, $table) as $row) {

    995. 

  995. 			if (preg_match('#SELECT\s+(\w+).nextval\s+INTO\s+:new\.(\w+)\s+FROM\s+dual#i', $row['trigger_body'], $matches)) {

    996. 

  996. 				$column = strtolower($matches[2]);

    997. 

  997. 				$column_info[$column]['auto_increment'] = TRUE;

    998. 

  998. 			}

    999. 

  999. 		}

    1000. 

  1000. 		

    1001. 

  1001. 		return $column_info;

    1002. 

  1002. 	}

    1003. 

  1003. 	

    1004. 

  1004. 	

    1005. 

  1005. 	/**

    1006. 

  1006. 	 * Fetches the key info for an Oracle database

    1007. 

  1007. 	 * 

    1008. 

  1008. 	 * The structure of the returned array is:

    1009. 

  1009. 	 * 

    1010. 

  1010. 	 * {{{

    1011. 

  1011. 	 * array(

    1012. 

  1012. 	 *      'primary' => array(

    1013. 

  1013. 	 *          {column name}, ...

    1014. 

  1014. 	 *      ),

    1015. 

  1015. 	 *      'unique'  => array(

    1016. 

  1016. 	 *          array(

    1017. 

  1017. 	 *              {column name}, ...

    1018. 

  1018. 	 *          ), ...

    1019. 

  1019. 	 *      ),

    1020. 

  1020. 	 *      'foreign' => array(

    1021. 

  1021. 	 *          array(

    1022. 

  1022. 	 *              'column'         => {column name},

    1023. 

  1023. 	 *              'foreign_table'  => {foreign table name},

    1024. 

  1024. 	 *              'foreign_column' => {foreign column name},

    1025. 

  1025. 	 *              'on_delete'      => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    1026. 

  1026. 	 *              'on_update'      => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    1027. 

  1027. 	 *          ), ...

    1028. 

  1028. 	 *      )

    1029. 

  1029. 	 * )

    1030. 

  1030. 	 * }}}

    1031. 

  1031. 	 * 

    1032. 

  1032. 	 * @return array  The keys arrays for every table in the database - see method description for details

    1033. 

  1033. 	 */

    1034. 

  1034. 	private function fetchOracleKeys()

    1035. 

  1035. 	{

    1036. 

  1036. 		$keys = array();

    1037. 

  1037. 		

    1038. 

  1038. 		$tables = $this->getTables();

    1039. 

  1039. 		foreach ($tables as $table) {

    1040. 

  1040. 			$keys[$table] = array();

    1041. 

  1041. 			$keys[$table]['primary'] = array();

    1042. 

  1042. 			$keys[$table]['unique']  = array();

    1043. 

  1043. 			$keys[$table]['foreign'] = array();

    1044. 

  1044. 		}

    1045. 

  1045. 		

    1046. 

  1046. 		$sql  = "SELECT

    1047. 

  1047. 						 LOWER(UC.TABLE_NAME) \"TABLE\",

    1048. 

  1048. 						 UC.CONSTRAINT_NAME CONSTRAINT_NAME,

    1049. 

  1049. 						 CASE UC.CONSTRAINT_TYPE

    1050. 

  1050. 							 WHEN 'P' THEN 'primary'

    1051. 

  1051. 							 WHEN 'R' THEN 'foreign'

    1052. 

  1052. 							 WHEN 'U' THEN 'unique'

    1053. 

  1053. 							 END TYPE,

    1054. 

  1054. 						 LOWER(UCC.COLUMN_NAME) \"COLUMN\",

    1055. 

  1055. 						 LOWER(FKC.TABLE_NAME) FOREIGN_TABLE,

    1056. 

  1056. 						 LOWER(FKC.COLUMN_NAME) FOREIGN_COLUMN,

    1057. 

  1057. 						 CASE WHEN FKC.TABLE_NAME IS NOT NULL THEN REPLACE(LOWER(UC.DELETE_RULE), ' ', '_') ELSE NULL END ON_DELETE

    1058. 

  1058. 					 FROM

    1059. 

  1059. 						 USER_CONSTRAINTS UC INNER JOIN

    1060. 

  1060. 						 USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME LEFT JOIN

    1061. 

  1061. 						 USER_CONSTRAINTS FK ON UC.R_CONSTRAINT_NAME = FK.CONSTRAINT_NAME LEFT JOIN

    1062. 

  1062. 						 USER_CONS_COLUMNS FKC ON FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME

    1063. 

  1063. 					 WHERE

    1064. 

  1064. 						 UC.CONSTRAINT_TYPE IN ('U', 'P', 'R') AND

    1065. 

  1065. 						 UC.STATUS = 'ENABLED' AND

    1066. 

  1066. 						 SUBSTR(UC.TABLE_NAME, 1, 4) <> 'BIN\$'

    1067. 

  1067. 					 ORDER BY

    1068. 

  1068. 						 UC.TABLE_NAME ASC,

    1069. 

  1069. 						 UC.CONSTRAINT_TYPE ASC,

    1070. 

  1070. 						 UC.CONSTRAINT_NAME ASC,

    1071. 

  1071. 						 UCC.POSITION ASC";

    1072. 

  1072. 		

    1073. 

  1073. 		$result = $this->database->query($sql);

    1074. 

  1074. 		

    1075. 

  1075. 		$last_name  = '';

    1076. 

  1076. 		$last_table = '';

    1077. 

  1077. 		$last_type  = '';

    1078. 

  1078. 		foreach ($result as $row) {

    1079. 

  1079. 			

    1080. 

  1080. 			if ($row['constraint_name'] != $last_name) {

    1081. 

  1081. 				

    1082. 

  1082. 				if ($last_name) {

    1083. 

  1083. 					if ($last_type == 'foreign' || $last_type == 'unique') {

    1084. 

  1084. 						$keys[$last_table][$last_type][] = $temp;

    1085. 

  1085. 					} else {

    1086. 

  1086. 						$keys[$last_table][$last_type] = $temp;

    1087. 

  1087. 					}

    1088. 

  1088. 				}

    1089. 

  1089. 				

    1090. 

  1090. 				$temp = array();

    1091. 

  1091. 				if ($row['type'] == 'foreign') {

    1092. 

  1092. 					

    1093. 

  1093. 					$temp['column']         = $row['column'];

    1094. 

  1094. 					$temp['foreign_table']  = $row['foreign_table'];

    1095. 

  1095. 					$temp['foreign_column'] = $row['foreign_column'];

    1096. 

  1096. 					$temp['on_delete']      = 'no_action';

    1097. 

  1097. 					$temp['on_update']      = 'no_action';

    1098. 

  1098. 					

    1099. 

  1099. 					if (!empty($row['on_delete'])) {

    1100. 

  1100. 						$temp['on_delete'] = $row['on_delete'];

    1101. 

  1101. 					}

    1102. 

  1102. 					

    1103. 

  1103. 				} else {

    1104. 

  1104. 					$temp[] = $row['column'];

    1105. 

  1105. 				}

    1106. 

  1106. 				

    1107. 

  1107. 				$last_table = $row['table'];

    1108. 

  1108. 				$last_name  = $row['constraint_name'];

    1109. 

  1109. 				$last_type  = $row['type'];

    1110. 

  1110. 				

    1111. 

  1111. 			} else {

    1112. 

  1112. 				$temp[] = $row['column'];

    1113. 

  1113. 			}

    1114. 

  1114. 		}

    1115. 

  1115. 		

    1116. 

  1116. 		if (isset($temp)) {

    1117. 

  1117. 			if ($last_type == 'foreign' || $last_type == 'unique') {

    1118. 

  1118. 				$keys[$last_table][$last_type][] = $temp;

    1119. 

  1119. 			} else {

    1120. 

  1120. 				$keys[$last_table][$last_type] = $temp;

    1121. 

  1121. 			}

    1122. 

  1122. 		}

    1123. 

  1123. 		

    1124. 

  1124. 		return $keys;

    1125. 

  1125. 	}

    1126. 

  1126. 	

    1127. 

  1127. 	

    1128. 

  1128. 	/**

    1129. 

  1129. 	 * Gets the column info from a PostgreSQL database

    1130. 

  1130. 	 * 

    1131. 

  1131. 	 * The returned array is in the format:

    1132. 

  1132. 	 * 

    1133. 

  1133. 	 * {{{

    1134. 

  1134. 	 * array(

    1135. 

  1135. 	 *     (string) {column name} => array(

    1136. 

  1136. 	 *         'type'           => (string)  {data type},

    1137. 

  1137. 	 *         'not_null'       => (boolean) {if value can't be null},

    1138. 

  1138. 	 *         'default'        => (mixed)   {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},

    1139. 

  1139. 	 *         'valid_values'   => (array)   {the valid values for a char/varchar field},

    1140. 

  1140. 	 *         'max_length'     => (integer) {the maximum length in a char/varchar field},

    1141. 

  1141. 	 *         'decimal_places' => (integer) {the number of decimal places for a decimal field},

    1142. 

  1142. 	 *         'auto_increment' => (boolean) {if the integer primary key column is auto_increment}

    1143. 

  1143. 	 *     ), ...

    1144. 

  1144. 	 * )

    1145. 

  1145. 	 * }}}

    1146. 

  1146. 	 * 

    1147. 

  1147. 	 * @param  string $table  The table to fetch the column info for

    1148. 

  1148. 	 * @return array  The column info for the table specified - see method description for details

    1149. 

  1149. 	 */

    1150. 

  1150. 	private function fetchPostgreSQLColumnInfo($table)

    1151. 

  1151. 	{

    1152. 

  1152. 		$column_info = array();

    1153. 

  1153. 		

    1154. 

  1154. 		$data_type_mapping = array(

    1155. 

  1155. 			'boolean'			=> 'boolean',

    1156. 

  1156. 			'smallint'			=> 'integer',

    1157. 

  1157. 			'int'				=> 'integer',

    1158. 

  1158. 			'bigint'			=> 'integer',

    1159. 

  1159. 			'serial'			=> 'integer',

    1160. 

  1160. 			'bigserial'			=> 'integer',

    1161. 

  1161. 			'timestamp'			=> 'timestamp',

    1162. 

  1162. 			'date'				=> 'date',

    1163. 

  1163. 			'time'				=> 'time',

    1164. 

  1164. 			'uuid'              => 'varchar',

    1165. 

  1165. 			'character varying'	=> 'varchar',

    1166. 

  1166. 			'character'			=> 'char',

    1167. 

  1167. 			'real'				=> 'float',

    1168. 

  1168. 			'double'			=> 'float',

    1169. 

  1169. 			'numeric'			=> 'float',

    1170. 

  1170. 			'bytea'				=> 'blob',

    1171. 

  1171. 			'text'				=> 'text',

    1172. 

  1172. 			'mediumtext'		=> 'text',

    1173. 

  1173. 			'longtext'			=> 'text'

    1174. 

  1174. 		);

    1175. 

  1175. 		

    1176. 

  1176. 		// PgSQL required this complicated SQL to get the column info

    1177. 

  1177. 		$sql = "SELECT

    1178. 

  1178. 						pg_attribute.attname                                        AS column,

    1179. 

  1179. 						format_type(pg_attribute.atttypid, pg_attribute.atttypmod)  AS data_type,

    1180. 

  1180. 						pg_attribute.attnotnull                                     AS not_null,

    1181. 

  1181. 						pg_attrdef.adsrc                                            AS default,

    1182. 

  1182. 						pg_get_constraintdef(pg_constraint.oid)                     AS constraint

    1183. 

  1183. 					FROM

    1184. 

  1184. 						pg_attribute LEFT JOIN

    1185. 

  1185. 						pg_class ON pg_attribute.attrelid = pg_class.oid LEFT JOIN

    1186. 

  1186. 						pg_type ON pg_type.oid = pg_attribute.atttypid LEFT JOIN

    1187. 

  1187. 						pg_constraint ON pg_constraint.conrelid = pg_class.oid AND

    1188. 

  1188. 										 pg_attribute.attnum = ANY (pg_constraint.conkey) AND

    1189. 

  1189. 										 pg_constraint.contype = 'c' LEFT JOIN

    1190. 

  1190. 						pg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND

    1191. 

  1191. 									  pg_attribute.attnum = pg_attrdef.adnum

    1192. 

  1192. 					WHERE

    1193. 

  1193. 						NOT pg_attribute.attisdropped AND

    1194. 

  1194. 						pg_class.relname = %s AND

    1195. 

  1195. 						pg_type.typname NOT IN ('oid', 'cid', 'xid', 'cid', 'xid', 'tid')

    1196. 

  1196. 					ORDER BY

    1197. 

  1197. 						pg_attribute.attnum,

    1198. 

  1198. 						pg_constraint.contype";

    1199. 

  1199. 		$result = $this->database->query($sql, $table);

    1200. 

  1200. 		

    1201. 

  1201. 		foreach ($result as $row) {

    1202. 

  1202. 			

    1203. 

  1203. 			$info = array();

    1204. 

  1204. 			

    1205. 

  1205. 			// Get the column type

    1206. 

  1206. 			preg_match('#([\w ]+)\s*(?:\(\s*(\d+)(?:\s*,\s*(\d+))?\s*\))?#', $row['data_type'], $column_data_type);

    1207. 

  1207. 			

    1208. 

  1208. 			foreach ($data_type_mapping as $data_type => $mapped_data_type) {

    1209. 

  1209. 				if (stripos($column_data_type[1], $data_type) === 0) {

    1210. 

  1210. 					$info['type'] = $mapped_data_type;

    1211. 

  1211. 					break;

    1212. 

  1212. 				}

    1213. 

  1213. 			}

    1214. 

  1214. 			

    1215. 

  1215. 			if (!isset($info['type'])) {

    1216. 

  1216. 				$info['type'] = $column_data_type[1];

    1217. 

  1217. 			}

    1218. 

  1218. 			

    1219. 

  1219. 			// Handle the length of decimal/numeric fields

    1220. 

  1220. 			if ($info['type'] == 'float' && isset($column_data_type[3]) && strlen($column_data_type[3]) > 0) {

    1221. 

  1221. 				$info['decimal_places'] = (int) $column_data_type[3];

    1222. 

  1222. 			}

    1223. 

  1223. 			

    1224. 

  1224. 			// Handle the special data for varchar fields

    1225. 

  1225. 			if (in_array($info['type'], array('char', 'varchar')) && !empty($column_data_type[2])) {

    1226. 

  1226. 				$info['max_length'] = $column_data_type[2];

    1227. 

  1227. 			}

    1228. 

  1228. 			

    1229. 

  1229. 			// In PostgreSQL, a UUID can be the 32 digits, 32 digits plus 4 hyphens or 32 digits plus 4 hyphens and 2 curly braces

    1230. 

  1230. 			if ($row['data_type'] == 'uuid') {

    1231. 

  1231. 				$info['max_length'] = 38;	

    1232. 

  1232. 			}

    1233. 

  1233. 			

    1234. 

  1234. 			// Handle check constraints that are just simple lists

    1235. 

  1235. 			if (in_array($info['type'], array('varchar', 'char')) && !empty($row['constraint'])) {

    1236. 

  1236. 				if (preg_match('/CHECK[\( "]+' . $row['column'] . '[a-z\) ":]+\s+=\s+/i', $row['constraint'])) {

    1237. 

  1237. 					if (preg_match_all("/(?!').'((''|[^']+)*)'/", $row['constraint'], $matches, PREG_PATTERN_ORDER)) {

    1238. 

  1238. 						$info['valid_values'] = str_replace("''", "'", $matches[1]);

    1239. 

  1239. 					}

    1240. 

  1240. 				}

    1241. 

  1241. 			}

    1242. 

  1242. 			

    1243. 

  1243. 			// Handle default values and serial data types

    1244. 

  1244. 			if ($info['type'] == 'integer' && stripos($row['default'], 'nextval(') !== FALSE) {

    1245. 

  1245. 				$info['auto_increment'] = TRUE;

    1246. 

  1246. 				

    1247. 

  1247. 			} elseif ($row['default'] !== NULL) {

    1248. 

  1248. 				if ($row['default'] == 'now()') {

    1249. 

  1249. 					$info['default'] = 'CURRENT_TIMESTAMP';

    1250. 

  1250. 				} elseif ($row['default'] == "('now'::text)::date") {

    1251. 

  1251. 					$info['default'] = 'CURRENT_DATE';

    1252. 

  1252. 				} elseif ($row['default'] == "('now'::text)::time with time zone") {

    1253. 

  1253. 					$info['default'] = 'CURRENT_TIME';	

    1254. 

  1254. 				} else {

    1255. 

  1255. 					$info['default'] = str_replace("''", "'", preg_replace("/^'(.*)'::[a-z ]+\$/iD", '\1', $row['default']));

    1256. 

  1256. 					if ($info['type'] == 'boolean') {

    1257. 

  1257. 						$info['default'] = ($info['default'] == 'false' || !$info['default']) ? FALSE : TRUE;

    1258. 

  1258. 					}

    1259. 

  1259. 				}

    1260. 

  1260. 			}

    1261. 

  1261. 			

    1262. 

  1262. 			// Not null values

    1263. 

  1263. 			$info['not_null'] = ($row['not_null'] == 't') ? TRUE : FALSE;

    1264. 

  1264. 			

    1265. 

  1265. 			$column_info[$row['column']] = $info;

    1266. 

  1266. 		}

    1267. 

  1267. 		

    1268. 

  1268. 		return $column_info;

    1269. 

  1269. 	}

    1270. 

  1270. 	

    1271. 

  1271. 	

    1272. 

  1272. 	/**

    1273. 

  1273. 	 * Fetches the key info for a PostgreSQL database

    1274. 

  1274. 	 * 

    1275. 

  1275. 	 * The structure of the returned array is:

    1276. 

  1276. 	 * 

    1277. 

  1277. 	 * {{{

    1278. 

  1278. 	 * array(

    1279. 

  1279. 	 *      'primary' => array(

    1280. 

  1280. 	 *          {column name}, ...

    1281. 

  1281. 	 *      ),

    1282. 

  1282. 	 *      'unique'  => array(

    1283. 

  1283. 	 *          array(

    1284. 

  1284. 	 *              {column name}, ...

    1285. 

  1285. 	 *          ), ...

    1286. 

  1286. 	 *      ),

    1287. 

  1287. 	 *      'foreign' => array(

    1288. 

  1288. 	 *          array(

    1289. 

  1289. 	 *              'column'         => {column name},

    1290. 

  1290. 	 *              'foreign_table'  => {foreign table name},

    1291. 

  1291. 	 *              'foreign_column' => {foreign column name},

    1292. 

  1292. 	 *              'on_delete'      => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    1293. 

  1293. 	 *              'on_update'      => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    1294. 

  1294. 	 *          ), ...

    1295. 

  1295. 	 *      )

    1296. 

  1296. 	 * )

    1297. 

  1297. 	 * }}}

    1298. 

  1298. 	 * 

    1299. 

  1299. 	 * @return array  The keys arrays for every table in the database - see method description for details

    1300. 

  1300. 	 */

    1301. 

  1301. 	private function fetchPostgreSQLKeys()

    1302. 

  1302. 	{

    1303. 

  1303. 		$keys = array();

    1304. 

  1304. 		

    1305. 

  1305. 		$tables   = $this->getTables();

    1306. 

  1306. 		foreach ($tables as $table) {

    1307. 

  1307. 			$keys[$table] = array();

    1308. 

  1308. 			$keys[$table]['primary'] = array();

    1309. 

  1309. 			$keys[$table]['unique']  = array();

    1310. 

  1310. 			$keys[$table]['foreign'] = array();

    1311. 

  1311. 		}

    1312. 

  1312. 		

    1313. 

  1313. 		$sql  = "(

    1314. 

  1314. 				 SELECT

    1315. 

  1315. 						 t.relname AS table,

    1316. 

  1316. 						 con.conname AS constraint_name,

    1317. 

  1317. 						 CASE con.contype

    1318. 

  1318. 							 WHEN 'f' THEN 'foreign'

    1319. 

  1319. 							 WHEN 'p' THEN 'primary'

    1320. 

  1320. 							 WHEN 'u' THEN 'unique'

    1321. 

  1321. 						 END AS type,

    1322. 

  1322. 						 col.attname AS column,

    1323. 

  1323. 						 ft.relname AS foreign_table,

    1324. 

  1324. 						 fc.attname AS foreign_column,

    1325. 

  1325. 						 CASE con.confdeltype

    1326. 

  1326. 							 WHEN 'c' THEN 'cascade'

    1327. 

  1327. 							 WHEN 'a' THEN 'no_action'

    1328. 

  1328. 							 WHEN 'r' THEN 'restrict'

    1329. 

  1329. 							 WHEN 'n' THEN 'set_null'

    1330. 

  1330. 							 WHEN 'd' THEN 'set_default'

    1331. 

  1331. 						 END AS on_delete,

    1332. 

  1332. 						 CASE con.confupdtype

    1333. 

  1333. 							 WHEN 'c' THEN 'cascade'

    1334. 

  1334. 							 WHEN 'a' THEN 'no_action'

    1335. 

  1335. 							 WHEN 'r' THEN 'restrict'

    1336. 

  1336. 							 WHEN 'n' THEN 'set_null'

    1337. 

  1337. 							 WHEN 'd' THEN 'set_default'

    1338. 

  1338. 						 END AS on_update,

    1339. 

  1339. 						CASE WHEN con.conkey IS NOT NULL THEN position('-'||col.attnum||'-' in '-'||array_to_string(con.conkey, '-')||'-') ELSE 0 END AS column_order

    1340. 

  1340. 					 FROM

    1341. 

  1341. 						 pg_attribute AS col INNER JOIN

    1342. 

  1342. 						 pg_class AS t ON col.attrelid = t.oid INNER JOIN

    1343. 

  1343. 						 pg_constraint AS con ON (col.attnum = ANY (con.conkey) AND

    1344. 

  1344. 												  con.conrelid = t.oid) LEFT JOIN

    1345. 

  1345. 						 pg_class AS ft ON con.confrelid = ft.oid LEFT JOIN

    1346. 

  1346. 						 pg_attribute AS fc ON (fc.attnum = ANY (con.confkey) AND

    1347. 

  1347. 												ft.oid = fc.attrelid)

    1348. 

  1348. 					 WHERE

    1349. 

  1349. 						 NOT col.attisdropped AND

    1350. 

  1350. 						 (con.contype = 'p' OR

    1351. 

  1351. 						  con.contype = 'f' OR

    1352. 

  1352. 						  con.contype = 'u')

    1353. 

  1353. 				) UNION (

    1354. 

  1354. 				SELECT

    1355. 

  1355. 						t.relname AS table,

    1356. 

  1356. 						ic.relname AS constraint_name,

    1357. 

  1357. 						'unique' AS type,

    1358. 

  1358. 						col.attname AS column,

    1359. 

  1359. 						NULL AS foreign_table,

    1360. 

  1360. 						NULL AS foreign_column,

    1361. 

  1361. 						NULL AS on_delete,

    1362. 

  1362. 						NULL AS on_update,

    1363. 

  1363. 						CASE WHEN ind.indkey IS NOT NULL THEN position('-'||col.attnum||'-' in '-'||array_to_string(ind.indkey, '-')||'-') ELSE 0 END AS column_order

    1364. 

  1364. 					FROM

    1365. 

  1365. 						pg_class AS t INNER JOIN

    1366. 

  1366. 						pg_index AS ind ON ind.indrelid = t.oid INNER JOIN

    1367. 

  1367. 						pg_namespace AS n ON t.relnamespace = n.oid INNER JOIN

    1368. 

  1368. 						pg_class AS ic ON ind.indexrelid = ic.oid LEFT JOIN

    1369. 

  1369. 						pg_constraint AS con ON con.conrelid = t.oid AND con.contype = 'u' AND con.conname = ic.relname INNER JOIN

    1370. 

  1370. 						pg_attribute AS col ON col.attrelid = t.oid AND col.attnum = ANY (ind.indkey)  

    1371. 

  1371. 					WHERE

    1372. 

  1372. 						n.nspname NOT IN ('pg_catalog', 'pg_toast') AND

    1373. 

  1373. 						indisunique = TRUE AND

    1374. 

  1374. 						indisprimary = FALSE AND

    1375. 

  1375. 						con.oid IS NULL

    1376. 

  1376. 				) ORDER BY 1, 3, 2, 9";

    1377. 

  1377. 		

    1378. 

  1378. 		$result = $this->database->query($sql);

    1379. 

  1379. 		

    1380. 

  1380. 		$last_name  = '';

    1381. 

  1381. 		$last_table = '';

    1382. 

  1382. 		$last_type  = '';

    1383. 

  1383. 		foreach ($result as $row) {

    1384. 

  1384. 			

    1385. 

  1385. 			if ($row['constraint_name'] != $last_name) {

    1386. 

  1386. 				

    1387. 

  1387. 				if ($last_name) {

    1388. 

  1388. 					if ($last_type == 'foreign' || $last_type == 'unique') {

    1389. 

  1389. 						$keys[$last_table][$last_type][] = $temp;

    1390. 

  1390. 					} else {

    1391. 

  1391. 						$keys[$last_table][$last_type] = $temp;

    1392. 

  1392. 					}

    1393. 

  1393. 				}

    1394. 

  1394. 				

    1395. 

  1395. 				$temp = array();

    1396. 

  1396. 				if ($row['type'] == 'foreign') {

    1397. 

  1397. 					

    1398. 

  1398. 					$temp['column']         = $row['column'];

    1399. 

  1399. 					$temp['foreign_table']  = $row['foreign_table'];

    1400. 

  1400. 					$temp['foreign_column'] = $row['foreign_column'];

    1401. 

  1401. 					$temp['on_delete']      = 'no_action';

    1402. 

  1402. 					$temp['on_update']      = 'no_action';

    1403. 

  1403. 					

    1404. 

  1404. 					if (!empty($row['on_delete'])) {

    1405. 

  1405. 						$temp['on_delete'] = $row['on_delete'];

    1406. 

  1406. 					}

    1407. 

  1407. 					

    1408. 

  1408. 					if (!empty($row['on_update'])) {

    1409. 

  1409. 						$temp['on_update'] = $row['on_update'];

    1410. 

  1410. 					}

    1411. 

  1411. 					

    1412. 

  1412. 				} else {

    1413. 

  1413. 					$temp[] = $row['column'];

    1414. 

  1414. 				}

    1415. 

  1415. 				

    1416. 

  1416. 				$last_table = $row['table'];

    1417. 

  1417. 				$last_name  = $row['constraint_name'];

    1418. 

  1418. 				$last_type  = $row['type'];

    1419. 

  1419. 				

    1420. 

  1420. 			} else {

    1421. 

  1421. 				$temp[] = $row['column'];

    1422. 

  1422. 			}

    1423. 

  1423. 		}

    1424. 

  1424. 		

    1425. 

  1425. 		if (isset($temp)) {

    1426. 

  1426. 			if ($last_type == 'foreign' || $last_type == 'unique') {

    1427. 

  1427. 				$keys[$last_table][$last_type][] = $temp;

    1428. 

  1428. 			} else {

    1429. 

  1429. 				$keys[$last_table][$last_type] = $temp;

    1430. 

  1430. 			}

    1431. 

  1431. 		}

    1432. 

  1432. 		

    1433. 

  1433. 		return $keys;

    1434. 

  1434. 	}

    1435. 

  1435. 	

    1436. 

  1436. 	

    1437. 

  1437. 	/**

    1438. 

  1438. 	 * Gets the column info from a SQLite database

    1439. 

  1439. 	 * 

    1440. 

  1440. 	 * The returned array is in the format:

    1441. 

  1441. 	 * 

    1442. 

  1442. 	 * {{{

    1443. 

  1443. 	 * array(

    1444. 

  1444. 	 *     (string) {column name} => array(

    1445. 

  1445. 	 *         'type'           => (string)  {data type},

    1446. 

  1446. 	 *         'not_null'       => (boolean) {if value can't be null},

    1447. 

  1447. 	 *         'default'        => (mixed)   {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},

    1448. 

  1448. 	 *         'valid_values'   => (array)   {the valid values for a char/varchar field},

    1449. 

  1449. 	 *         'max_length'     => (integer) {the maximum length in a char/varchar field},

    1450. 

  1450. 	 *         'decimal_places' => (integer) {the number of decimal places for a decimal field},

    1451. 

  1451. 	 *         'auto_increment' => (boolean) {if the integer primary key column is auto_increment}

    1452. 

  1452. 	 *     ), ...

    1453. 

  1453. 	 * )

    1454. 

  1454. 	 * }}}

    1455. 

  1455. 	 * 

    1456. 

  1456. 	 * @param  string $table  The table to fetch the column info for

    1457. 

  1457. 	 * @return array  The column info for the table specified - see method description for details

    1458. 

  1458. 	 */

    1459. 

  1459. 	private function fetchSQLiteColumnInfo($table)

    1460. 

  1460. 	{

    1461. 

  1461. 		$column_info = array();

    1462. 

  1462. 		

    1463. 

  1463. 		$data_type_mapping = array(

    1464. 

  1464. 			'boolean'			=> 'boolean',

    1465. 

  1465. 			'serial'            => 'integer',

    1466. 

  1466. 			'smallint'			=> 'integer',

    1467. 

  1467. 			'int'				=> 'integer',

    1468. 

  1468. 			'integer'           => 'integer',

    1469. 

  1469. 			'bigint'			=> 'integer',

    1470. 

  1470. 			'timestamp'			=> 'timestamp',

    1471. 

  1471. 			'date'				=> 'date',

    1472. 

  1472. 			'time'				=> 'time',

    1473. 

  1473. 			'varchar'			=> 'varchar',

    1474. 

  1474. 			'char'				=> 'char',

    1475. 

  1475. 			'real'				=> 'float',

    1476. 

  1476. 			'numeric'           => 'float',

    1477. 

  1477. 			'float'             => 'float',

    1478. 

  1478. 			'double'			=> 'float',

    1479. 

  1479. 			'decimal'			=> 'float',

    1480. 

  1480. 			'blob'				=> 'blob',

    1481. 

  1481. 			'text'				=> 'text'

    1482. 

  1482. 		);

    1483. 

  1483. 		

    1484. 

  1484. 		$result = $this->database->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = %s", $table);

    1485. 

  1485. 		

    1486. 

  1486. 		try {

    1487. 

  1487. 			$row        = $result->fetchRow();

    1488. 

  1488. 			$create_sql = $row['sql'];

    1489. 

  1489. 		} catch (fNoRowsException $e) {

    1490. 

  1490. 			return array();			

    1491. 

  1491. 		}

    1492. 

  1492. 		

    1493. 

  1493. 		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);

    1494. 

  1494. 		

    1495. 

  1495. 		foreach ($matches as $match) {

    1496. 

  1496. 			$info = array();

    1497. 

  1497. 			

    1498. 

  1498. 			foreach ($data_type_mapping as $data_type => $mapped_data_type) {

    1499. 

  1499. 				if (stripos($match[2], $data_type) === 0) {

    1500. 

  1500. 					$info['type'] = $mapped_data_type;

    1501. 

  1501. 					break;

    1502. 

  1502. 				}

    1503. 

  1503. 			}

    1504. 

  1504. 		

    1505. 

  1505. 			// Type specific information

    1506. 

  1506. 			if (in_array($info['type'], array('char', 'varchar')) && !empty($match[3])) {

    1507. 

  1507. 				$info['max_length'] = $match[3];

    1508. 

  1508. 			}

    1509. 

  1509. 			

    1510. 

  1510. 			// Figure out how many decimal places for a decimal

    1511. 

  1511. 			if (in_array(strtolower($match[2]), array('decimal', 'numeric')) && !empty($match[4])) {

    1512. 

  1512. 				$info['decimal_places'] = $match[4];

    1513. 

  1513. 			}

    1514. 

  1514. 			

    1515. 

  1515. 			// Not null

    1516. 

  1516. 			$info['not_null'] = (!empty($match[5]) || !empty($match[8])) ? TRUE : FALSE;

    1517. 

  1517. 		

    1518. 

  1518. 			// Default values

    1519. 

  1519. 			if (isset($match[6]) && $match[6] != '' && $match[6] != 'NULL') {

    1520. 

  1520. 				$info['default'] = preg_replace("/^'|'\$/D", '', $match[6]);

    1521. 

  1521. 			}

    1522. 

  1522. 			if ($info['type'] == 'boolean' && isset($info['default'])) {

    1523. 

  1523. 				$info['default'] = ($info['default'] == 'f' || $info['default'] == 0 || $info['default'] == 'false') ? FALSE : TRUE;

    1524. 

  1524. 			}

    1525. 

  1525. 		

    1526. 

  1526. 			// Check constraints

    1527. 

  1527. 			if (isset($match[9]) && preg_match('/CHECK\s*\(\s*' . $match[1] . '\s+IN\s+\(\s*((?:(?:[^, \']*|\'(?:\'\'|[^\']+)*\')\s*,\s*)*(?:[^, \']*|\'(?:\'\'|[^\']+)*\'))\s*\)/i', $match[9], $check_match)) {

    1528. 

  1528. 				$info['valid_values'] = str_replace("''", "'", preg_replace("/^'|'\$/D", '', preg_split("#\s*,\s*#", $check_match[1])));

    1529. 

  1529. 			}

    1530. 

  1530. 		

    1531. 

  1531. 			// Auto increment fields

    1532. 

  1532. 			if (!empty($match[8]) && (stripos($match[8], 'autoincrement') !== FALSE || $info['type'] == 'integer')) {

    1533. 

  1533. 				$info['auto_increment'] = TRUE;

    1534. 

  1534. 			}

    1535. 

  1535. 		

    1536. 

  1536. 			$column_info[$match[1]] = $info;

    1537. 

  1537. 		}

    1538. 

  1538. 		

    1539. 

  1539. 		return $column_info;

    1540. 

  1540. 	}

    1541. 

  1541. 	

    1542. 

  1542. 	

    1543. 

  1543. 	/**

    1544. 

  1544. 	 * Fetches the key info for an SQLite database

    1545. 

  1545. 	 * 

    1546. 

  1546. 	 * The structure of the returned array is:

    1547. 

  1547. 	 * 

    1548. 

  1548. 	 * {{{

    1549. 

  1549. 	 * array(

    1550. 

  1550. 	 *      'primary' => array(

    1551. 

  1551. 	 *          {column name}, ...

    1552. 

  1552. 	 *      ),

    1553. 

  1553. 	 *      'unique'  => array(

    1554. 

  1554. 	 *          array(

    1555. 

  1555. 	 *              {column name}, ...

    1556. 

  1556. 	 *          ), ...

    1557. 

  1557. 	 *      ),

    1558. 

  1558. 	 *      'foreign' => array(

    1559. 

  1559. 	 *          array(

    1560. 

  1560. 	 *              'column'         => {column name},

    1561. 

  1561. 	 *              'foreign_table'  => {foreign table name},

    1562. 

  1562. 	 *              'foreign_column' => {foreign column name},

    1563. 

  1563. 	 *              'on_delete'      => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    1564. 

  1564. 	 *              'on_update'      => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    1565. 

  1565. 	 *          ), ...

    1566. 

  1566. 	 *      )

    1567. 

  1567. 	 * )

    1568. 

  1568. 	 * }}}

    1569. 

  1569. 	 * 

    1570. 

  1570. 	 * @return array  The keys arrays for every table in the database - see method description for details

    1571. 

  1571. 	 */

    1572. 

  1572. 	private function fetchSQLiteKeys()

    1573. 

  1573. 	{

    1574. 

  1574. 		$tables = $this->getTables();

    1575. 

  1575. 		$keys   = array();

    1576. 

  1576. 		

    1577. 

  1577. 		foreach ($tables as $table) {

    1578. 

  1578. 			$keys[$table] = array();

    1579. 

  1579. 			$keys[$table]['primary'] = array();

    1580. 

  1580. 			$keys[$table]['foreign'] = array();

    1581. 

  1581. 			$keys[$table]['unique']  = array();

    1582. 

  1582. 			

    1583. 

  1583. 			$result     = $this->database->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = %s", $table);

    1584. 

  1584. 			$row        = $result->fetchRow();

    1585. 

  1585. 			$create_sql = $row['sql'];

    1586. 

  1586. 			

    1587. 

  1587. 			// Get column level key definitions

    1588. 

  1588. 			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);

    1589. 

  1589. 			

    1590. 

  1590. 			foreach ($matches as $match) {

    1591. 

  1591. 				if (!empty($match[2])) {

    1592. 

  1592. 					$keys[$table]['unique'][] = array($match[1]);

    1593. 

  1593. 				}

    1594. 

  1594. 				

    1595. 

  1595. 				if (!empty($match[3])) {

    1596. 

  1596. 					$keys[$table]['primary'] = array($match[1]);

    1597. 

  1597. 				}

    1598. 

  1598. 				

    1599. 

  1599. 				if (!empty($match[4])) {

    1600. 

  1600. 					$temp = array('column'         => $match[1],

    1601. 

  1601. 								  'foreign_table'  => $match[5],

    1602. 

  1602. 								  'foreign_column' => $match[6],

    1603. 

  1603. 								  'on_delete'      => 'no_action',

    1604. 

  1604. 								  'on_update'      => 'no_action');

    1605. 

  1605. 					if (isset($match[7])) {

    1606. 

  1606. 						$temp['on_delete'] = strtolower(str_replace(' ', '_', $match[7]));

    1607. 

  1607. 					}

    1608. 

  1608. 					if (isset($match[8])) {

    1609. 

  1609. 						$temp['on_update'] = strtolower(str_replace(' ', '_', $match[8]));

    1610. 

  1610. 					}

    1611. 

  1611. 					$keys[$table]['foreign'][] = $temp;

    1612. 

  1612. 				}

    1613. 

  1613. 			}

    1614. 

  1614. 			

    1615. 

  1615. 			// Get table level primary key definitions

    1616. 

  1616. 			preg_match_all('#(?<=,|\()\s*PRIMARY\s+KEY\s*\(\s*((?:\s*\w+\s*,\s*)*\w+)\s*\)\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);

    1617. 

  1617. 			

    1618. 

  1618. 			foreach ($matches as $match) {

    1619. 

  1619. 				$keys[$table]['primary'] = preg_split('#\s*,\s*#', $match[1]);

    1620. 

  1620. 			}

    1621. 

  1621. 			

    1622. 

  1622. 			// Get table level foreign key definitions

    1623. 

  1623. 			preg_match_all('#(?<=,|\()\s*FOREIGN\s+KEY\s*(?:(\w+)|\((\w+)\))\s+REFERENCES\s+(\w+)\s*\(\s*(\w+)\s*\)\s*(?:\s+(?:ON\s+DELETE\s+(CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL|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);

    1624. 

  1624. 			

    1625. 

  1625. 			foreach ($matches as $match) {

    1626. 

  1626. 				if (empty($match[1])) { $match[1] = $match[2]; }

    1627. 

  1627. 				$temp = array('column'         => $match[1],

    1628. 

  1628. 							  'foreign_table'  => $match[3],

    1629. 

  1629. 							  'foreign_column' => $match[4],

    1630. 

  1630. 							  'on_delete'      => 'no_action',

    1631. 

  1631. 							  'on_update'      => 'no_action');

    1632. 

  1632. 				if (isset($match[5])) {

    1633. 

  1633. 					$temp['on_delete'] = strtolower(str_replace(' ', '_', $match[5]));

    1634. 

  1634. 				}

    1635. 

  1635. 				if (isset($match[6])) {

    1636. 

  1636. 					$temp['on_update'] = strtolower(str_replace(' ', '_', $match[6]));

    1637. 

  1637. 				}

    1638. 

  1638. 				$keys[$table]['foreign'][] = $temp;

    1639. 

  1639. 			}

    1640. 

  1640. 			

    1641. 

  1641. 			// Get table level unique key definitions

    1642. 

  1642. 			preg_match_all('#(?<=,|\()\s*UNIQUE\s*\(\s*((?:\s*\w+\s*,\s*)*\w+)\s*\)\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);

    1643. 

  1643. 			

    1644. 

  1644. 			foreach ($matches as $match) {

    1645. 

  1645. 				$keys[$table]['unique'][] = preg_split('#\s*,\s*#', $match[1]);

    1646. 

  1646. 			}

    1647. 

  1647. 		}

    1648. 

  1648. 		

    1649. 

  1649. 		return $keys;

    1650. 

  1650. 	}

    1651. 

  1651. 	

    1652. 

  1652. 	

    1653. 

  1653. 	/**

    1654. 

  1654. 	 * Finds many-to-many relationship for the table specified

    1655. 

  1655. 	 * 

    1656. 

  1656. 	 * @param  string $table  The table to find the relationships on

    1657. 

  1657. 	 * @return void

    1658. 

  1658. 	 */

    1659. 

  1659. 	private function findManyToManyRelationships($table)

    1660. 

  1660. 	{

    1661. 

  1661. 		if (!$this->isJoiningTable($table)) {

    1662. 

  1662. 			return;

    1663. 

  1663. 		}

    1664. 

  1664. 		

    1665. 

  1665. 		list ($key1, $key2) = $this->merged_keys[$table]['foreign'];

    1666. 

  1666. 		

    1667. 

  1667. 		$temp = array();

    1668. 

  1668. 		$temp['table']               = $key1['foreign_table'];

    1669. 

  1669. 		$temp['column']              = $key1['foreign_column'];

    1670. 

  1670. 		$temp['related_table']       = $key2['foreign_table'];

    1671. 

  1671. 		$temp['related_column']      = $key2['foreign_column'];

    1672. 

  1672. 		$temp['join_table']          = $table;

    1673. 

  1673. 		$temp['join_column']         = $key1['column'];

    1674. 

  1674. 		$temp['join_related_column'] = $key2['column'];

    1675. 

  1675. 		$temp['on_update']           = $key1['on_update'];

    1676. 

  1676. 		$temp['on_delete']           = $key1['on_delete'];

    1677. 

  1677. 		$this->relationships[$key1['foreign_table']]['many-to-many'][] = $temp;

    1678. 

  1678. 		

    1679. 

  1679. 		$temp = array();

    1680. 

  1680. 		$temp['table']               = $key2['foreign_table'];

    1681. 

  1681. 		$temp['column']              = $key2['foreign_column'];

    1682. 

  1682. 		$temp['related_table']       = $key1['foreign_table'];

    1683. 

  1683. 		$temp['related_column']      = $key1['foreign_column'];

    1684. 

  1684. 		$temp['join_table']          = $table;

    1685. 

  1685. 		$temp['join_column']         = $key2['column'];

    1686. 

  1686. 		$temp['join_related_column'] = $key1['column'];

    1687. 

  1687. 		$temp['on_update']           = $key2['on_update'];

    1688. 

  1688. 		$temp['on_delete']           = $key2['on_delete'];

    1689. 

  1689. 		$this->relationships[$key2['foreign_table']]['many-to-many'][] = $temp;

    1690. 

  1690. 	}

    1691. 

  1691. 	

    1692. 

  1692. 	

    1693. 

  1693. 	/**

    1694. 

  1694. 	 * Finds one-to-many relationship for the table specified

    1695. 

  1695. 	 * 

    1696. 

  1696. 	 * @param  string $table  The table to find the relationships on

    1697. 

  1697. 	 * @return void

    1698. 

  1698. 	 */

    1699. 

  1699. 	private function findOneToManyRelationships($table)

    1700. 

  1700. 	{

    1701. 

  1701. 		foreach ($this->merged_keys[$table]['foreign'] as $key) {

    1702. 

  1702. 			$type = ($this->checkForSingleColumnUniqueKey($table, $key['column'])) ? 'one-to-one' : 'one-to-many';

    1703. 

  1703. 			$temp = array();

    1704. 

  1704. 			$temp['table']          = $key['foreign_table'];

    1705. 

  1705. 			$temp['column']         = $key['foreign_column'];

    1706. 

  1706. 			$temp['related_table']  = $table;

    1707. 

  1707. 			$temp['related_column'] = $key['column'];

    1708. 

  1708. 			if ($type == 'one-to-many') {

    1709. 

  1709. 				$temp['on_delete']      = $key['on_delete'];

    1710. 

  1710. 				$temp['on_update']      = $key['on_update'];

    1711. 

  1711. 			}

    1712. 

  1712. 			$this->relationships[$key['foreign_table']][$type][] = $temp;

    1713. 

  1713. 		}

    1714. 

  1714. 	}

    1715. 

  1715. 	

    1716. 

  1716. 	

    1717. 

  1717. 	/**

    1718. 

  1718. 	 * Finds one-to-one and many-to-one relationship for the table specified

    1719. 

  1719. 	 * 

    1720. 

  1720. 	 * @param  string $table  The table to find the relationships on

    1721. 

  1721. 	 * @return void

    1722. 

  1722. 	 */

    1723. 

  1723. 	private function findStarToOneRelationships($table)

    1724. 

  1724. 	{

    1725. 

  1725. 		foreach ($this->merged_keys[$table]['foreign'] as $key) {

    1726. 

  1726. 			$temp = array();

    1727. 

  1727. 			$temp['table']          = $table;

    1728. 

  1728. 			$temp['column']         = $key['column'];

    1729. 

  1729. 			$temp['related_table']  = $key['foreign_table'];

    1730. 

  1730. 			$temp['related_column'] = $key['foreign_column'];

    1731. 

  1731. 			$type = ($this->checkForSingleColumnUniqueKey($table, $key['column'])) ? 'one-to-one' : 'many-to-one';

    1732. 

  1732. 			$this->relationships[$table][$type][] = $temp;

    1733. 

  1733. 		}

    1734. 

  1734. 	}

    1735. 

  1735. 	

    1736. 

  1736. 	

    1737. 

  1737. 	/**

    1738. 

  1738. 	 * Finds the one-to-one, many-to-one, one-to-many and many-to-many relationships in the database

    1739. 

  1739. 	 * 

    1740. 

  1740. 	 * @return void

    1741. 

  1741. 	 */

    1742. 

  1742. 	private function findRelationships()

    1743. 

  1743. 	{

    1744. 

  1744. 		$this->relationships = array();

    1745. 

  1745. 		$tables = $this->getTables();

    1746. 

  1746. 		

    1747. 

  1747. 		foreach ($tables as $table) {

    1748. 

  1748. 			$this->relationships[$table]['one-to-one']   = array();

    1749. 

  1749. 			$this->relationships[$table]['many-to-one']  = array();

    1750. 

  1750. 			$this->relationships[$table]['one-to-many']  = array();

    1751. 

  1751. 			$this->relationships[$table]['many-to-many'] = array();

    1752. 

  1752. 		}

    1753. 

  1753. 		

    1754. 

  1754. 		// Calculate the relationships

    1755. 

  1755. 		foreach ($this->merged_keys as $table => $keys) {

    1756. 

  1756. 			$this->findManyToManyRelationships($table);

    1757. 

  1757. 			if ($this->isJoiningTable($table)) {

    1758. 

  1758. 				continue;

    1759. 

  1759. 			}

    1760. 

  1760. 			

    1761. 

  1761. 			$this->findStarToOneRelationships($table);

    1762. 

  1762. 			$this->findOneToManyRelationships($table);

    1763. 

  1763. 		}

    1764. 

  1764. 		

    1765. 

  1765. 		if ($this->cache) {

    1766. 

  1766. 			$this->cache->set($this->makeCachePrefix() . 'relationships', $this->relationships);	

    1767. 

  1767. 		}

    1768. 

  1768. 	}

    1769. 

  1769. 	

    1770. 

  1770. 	

    1771. 

  1771. 	/**

    1772. 

  1772. 	 * Returns column information for the table specified

    1773. 

  1773. 	 * 

    1774. 

  1774. 	 * If only a table is specified, column info is in the following format:

    1775. 

  1775. 	 * 

    1776. 

  1776. 	 * {{{

    1777. 

  1777. 	 * array(

    1778. 

  1778. 	 *     (string) {column name} => array(

    1779. 

  1779. 	 *         'type'           => (string)  {data type},

    1780. 

  1780. 	 *         'not_null'       => (boolean) {if value can't be null},

    1781. 

  1781. 	 *         'default'        => (mixed)   {the default value},

    1782. 

  1782. 	 *         'valid_values'   => (array)   {the valid values for a varchar field},

    1783. 

  1783. 	 *         'max_length'     => (integer) {the maximum length in a varchar field},

    1784. 

  1784. 	 *         'decimal_places' => (integer) {the number of decimal places for a decimal/numeric/money/smallmoney field},

    1785. 

  1785. 	 *         'auto_increment' => (boolean) {if the integer primary key column is a serial/autoincrement/auto_increment/indentity column}

    1786. 

  1786. 	 *     ), ...

    1787. 

  1787. 	 * )

    1788. 

  1788. 	 * }}}

    1789. 

  1789. 	 * 

    1790. 

  1790. 	 * If a table and column are specified, column info is in the following format:

    1791. 

  1791. 	 * 

    1792. 

  1792. 	 * {{{

    1793. 

  1793. 	 * array(

    1794. 

  1794. 	 *     'type'           => (string)  {data type},

    1795. 

  1795. 	 *     'not_null'       => (boolean) {if value can't be null},

    1796. 

  1796. 	 *     'default'        => (mixed)   {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},

    1797. 

  1797. 	 *     'valid_values'   => (array)   {the valid values for a varchar field},

    1798. 

  1798. 	 *     'max_length'     => (integer) {the maximum length in a char/varchar field},

    1799. 

  1799. 	 *     'decimal_places' => (integer) {the number of decimal places for a decimal/numeric/money/smallmoney field},

    1800. 

  1800. 	 *     'auto_increment' => (boolean) {if the integer primary key column is a serial/autoincrement/auto_increment/indentity column}

    1801. 

  1801. 	 * )

    1802. 

  1802. 	 * }}}

    1803. 

  1803. 	 * 

    1804. 

  1804. 	 * If a table, column and element are specified, returned value is the single element specified.

    1805. 

  1805. 	 * 

    1806. 

  1806. 	 * The `'type'` element is homogenized to a value from the following list:

    1807. 

  1807. 	 * 

    1808. 

  1808. 	 *  - `'varchar'`

    1809. 

  1809. 	 *  - `'char'`

    1810. 

  1810. 	 *  - `'text'`

    1811. 

  1811. 	 *  - `'integer'`

    1812. 

  1812. 	 *  - `'float'`

    1813. 

  1813. 	 *  - `'timestamp'`

    1814. 

  1814. 	 *  - `'date'`

    1815. 

  1815. 	 *  - `'time'`

    1816. 

  1816. 	 *  - `'boolean'`

    1817. 

  1817. 	 *  - `'blob'`

    1818. 

  1818. 	 * 

    1819. 

  1819. 	 * @param  string $table    The table to get the column info for

    1820. 

  1820. 	 * @param  string $column   The column to get the info for

    1821. 

  1821. 	 * @param  string $element  The element to return: `'type'`, `'not_null'`, `'default'`, `'valid_values'`, `'max_length'`, `'decimal_places'`, `'auto_increment'`

    1822. 

  1822. 	 * @return mixed  The column info for the table/column/element specified - see method description for format

    1823. 

  1823. 	 */

    1824. 

  1824. 	public function getColumnInfo($table, $column=NULL, $element=NULL)

    1825. 

  1825. 	{

    1826. 

  1826. 		// Return the saved column info if possible

    1827. 

  1827. 		if (!$column && isset($this->merged_column_info[$table])) {

    1828. 

  1828. 			return $this->merged_column_info[$table];

    1829. 

  1829. 		}

    1830. 

  1830. 		if ($column && isset($this->merged_column_info[$table][$column])) {

    1831. 

  1831. 			if ($element !== NULL) {

    1832. 

  1832. 				if (!isset($this->merged_column_info[$table][$column][$element]) && !array_key_exists($element, $this->merged_column_info[$table][$column])) {

    1833. 

  1833. 					throw new fProgrammerException(

    1834. 

  1834. 						'The element specified, %1$s, is invalid. Must be one of: %2$s.',

    1835. 

  1835. 						$element,

    1836. 

  1836. 						join(', ', array('type', 'not_null', 'default', 'valid_values', 'max_length', 'decimal_places', 'auto_increment'))

    1837. 

  1837. 					);	

    1838. 

  1838. 				}

    1839. 

  1839. 				return $this->merged_column_info[$table][$column][$element];

    1840. 

  1840. 			}

    1841. 

  1841. 			return $this->merged_column_info[$table][$column];

    1842. 

  1842. 		}

    1843. 

  1843. 		

    1844. 

  1844. 		if (!in_array($table, $this->getTables())) {

    1845. 

  1845. 			throw new fProgrammerException(

    1846. 

  1846. 				'The table specified, %s, does not exist in the database',

    1847. 

  1847. 				$table

    1848. 

  1848. 			);

    1849. 

  1849. 		}

    1850. 

  1850. 		

    1851. 

  1851. 		$this->fetchColumnInfo($table);

    1852. 

  1852. 		$this->mergeColumnInfo();

    1853. 

  1853. 		

    1854. 

  1854. 		if ($column && !isset($this->merged_column_info[$table][$column])) {

    1855. 

  1855. 			throw new fProgrammerException(

    1856. 

  1856. 				'The column specified, %1$s, does not exist in the table %2$s',

    1857. 

  1857. 				$column,

    1858. 

  1858. 				$table

    1859. 

  1859. 			);

    1860. 

  1860. 		}

    1861. 

  1861. 		

    1862. 

  1862. 		if ($column) {

    1863. 

  1863. 			if ($element) {

    1864. 

  1864. 				return $this->merged_column_info[$table][$column][$element];

    1865. 

  1865. 			}

    1866. 

  1866. 			

    1867. 

  1867. 			return $this->merged_column_info[$table][$column];

    1868. 

  1868. 		}

    1869. 

  1869. 		

    1870. 

  1870. 		return $this->merged_column_info[$table];

    1871. 

  1871. 	}

    1872. 

  1872. 	

    1873. 

  1873. 	

    1874. 

  1874. 	/**

    1875. 

  1875. 	 * Returns the databases on the current server

    1876. 

  1876. 	 * 

    1877. 

  1877. 	 * @return array  The databases on the current server

    1878. 

  1878. 	 */

    1879. 

  1879. 	public function getDatabases()

    1880. 

  1880. 	{

    1881. 

  1881. 		if ($this->databases !== NULL) {

    1882. 

  1882. 			return $this->databases;

    1883. 

  1883. 		}

    1884. 

  1884. 		

    1885. 

  1885. 		$this->databases = array();

    1886. 

  1886. 		

    1887. 

  1887. 		switch ($this->database->getType()) {

    1888. 

  1888. 			case 'mssql':

    1889. 

  1889. 				$sql = 'SELECT

    1890. 

  1890. 								DISTINCT CATALOG_NAME

    1891. 

  1891. 							FROM

    1892. 

  1892. 								INFORMATION_SCHEMA.SCHEMATA

    1893. 

  1893. 							ORDER BY

    1894. 

  1894. 								LOWER(CATALOG_NAME)';

    1895. 

  1895. 				break;

    1896. 

  1896. 			

    1897. 

  1897. 			case 'mysql':

    1898. 

  1898. 				$sql = 'SHOW DATABASES';

    1899. 

  1899. 				break;

    1900. 

  1900. 			

    1901. 

  1901. 			case 'postgresql':

    1902. 

  1902. 				$sql = "SELECT

    1903. 

  1903. 								datname

    1904. 

  1904. 							FROM

    1905. 

  1905. 								pg_database

    1906. 

  1906. 							ORDER BY

    1907. 

  1907. 								LOWER(datname)";

    1908. 

  1908. 				break;

    1909. 

  1909. 								

    1910. 

  1910. 			case 'sqlite':

    1911. 

  1911. 				$this->databases[] = $this->database->getDatabase();

    1912. 

  1912. 				return $this->databases;

    1913. 

  1913. 		}

    1914. 

  1914. 		

    1915. 

  1915. 		$result = $this->database->query($sql);

    1916. 

  1916. 		

    1917. 

  1917. 		foreach ($result as $row) {

    1918. 

  1918. 			$keys = array_keys($row);

    1919. 

  1919. 			$this->databases[] = $row[$keys[0]];

    1920. 

  1920. 		}

    1921. 

  1921. 		

    1922. 

  1922. 		if ($this->cache) {

    1923. 

  1923. 			$this->cache->set($this->makeCachePrefix() . 'databases', $this->databases);		

    1924. 

  1924. 		}

    1925. 

  1925. 		

    1926. 

  1926. 		return $this->databases;

    1927. 

  1927. 	}

    1928. 

  1928. 	

    1929. 

  1929. 	

    1930. 

  1930. 	/**

    1931. 

  1931. 	 * Returns a list of primary key, foreign key and unique key constraints for the table specified

    1932. 

  1932. 	 * 

    1933. 

  1933. 	 * The structure of the returned array is:

    1934. 

  1934. 	 * 

    1935. 

  1935. 	 * {{{

    1936. 

  1936. 	 * array(

    1937. 

  1937. 	 *      'primary' => array(

    1938. 

  1938. 	 *          {column name}, ...

    1939. 

  1939. 	 *      ),

    1940. 

  1940. 	 *      'unique'  => array(

    1941. 

  1941. 	 *          array(

    1942. 

  1942. 	 *              {column name}, ...

    1943. 

  1943. 	 *          ), ...

    1944. 

  1944. 	 *      ),

    1945. 

  1945. 	 *      'foreign' => array(

    1946. 

  1946. 	 *          array(

    1947. 

  1947. 	 *              'column'         => {column name},

    1948. 

  1948. 	 *              'foreign_table'  => {foreign table name},

    1949. 

  1949. 	 *              'foreign_column' => {foreign column name},

    1950. 

  1950. 	 *              'on_delete'      => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    1951. 

  1951. 	 *              'on_update'      => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    1952. 

  1952. 	 *          ), ...

    1953. 

  1953. 	 *      )

    1954. 

  1954. 	 * )

    1955. 

  1955. 	 * }}}

    1956. 

  1956. 	 * 

    1957. 

  1957. 	 * @param  string $table     The table to return the keys for

    1958. 

  1958. 	 * @param  string $key_type  The type of key to return: `'primary'`, `'foreign'`, `'unique'`

    1959. 

  1959. 	 * @return array  An array of all keys, or just the type specified - see method description for format

    1960. 

  1960. 	 */

    1961. 

  1961. 	public function getKeys($table, $key_type=NULL)

    1962. 

  1962. 	{

    1963. 

  1963. 		$valid_key_types = array('primary', 'foreign', 'unique');

    1964. 

  1964. 		if ($key_type !== NULL && !in_array($key_type, $valid_key_types)) {

    1965. 

  1965. 			throw new fProgrammerException(

    1966. 

  1966. 				'The key type specified, %1$s, is invalid. Must be one of: %2$s.',

    1967. 

  1967. 				$key_type,

    1968. 

  1968. 				join(', ', $valid_key_types)

    1969. 

  1969. 			);

    1970. 

  1970. 		}

    1971. 

  1971. 		

    1972. 

  1972. 		// Return the saved column info if possible

    1973. 

  1973. 		if (!$key_type && isset($this->merged_keys[$table])) {

    1974. 

  1974. 			return $this->merged_keys[$table];

    1975. 

  1975. 		}

    1976. 

  1976. 		

    1977. 

  1977. 		if ($key_type && isset($this->merged_keys[$table][$key_type])) {

    1978. 

  1978. 			return $this->merged_keys[$table][$key_type];

    1979. 

  1979. 		}

    1980. 

  1980. 		

    1981. 

  1981. 		if (!in_array($table, $this->getTables())) {

    1982. 

  1982. 			throw new fProgrammerException(

    1983. 

  1983. 				'The table specified, %s, does not exist in the database',

    1984. 

  1984. 				$table

    1985. 

  1985. 			);

    1986. 

  1986. 		}

    1987. 

  1987. 		

    1988. 

  1988. 		$this->fetchKeys();

    1989. 

  1989. 		$this->mergeKeys();

    1990. 

  1990. 		

    1991. 

  1991. 		if ($key_type) {

    1992. 

  1992. 			return $this->merged_keys[$table][$key_type];

    1993. 

  1993. 		}

    1994. 

  1994. 		

    1995. 

  1995. 		return $this->merged_keys[$table];

    1996. 

  1996. 	}

    1997. 

  1997. 	

    1998. 

  1998. 	

    1999. 

  1999. 	/**

    2000. 

  2000. 	 * Returns a list of one-to-one, many-to-one, one-to-many and many-to-many relationships for the table specified

    2001. 

  2001. 	 * 

    2002. 

  2002. 	 * The structure of the returned array is:

    2003. 

  2003. 	 * 

    2004. 

  2004. 	 * {{{

    2005. 

  2005. 	 * array(

    2006. 

  2006. 	 *     'one-to-one' => array(

    2007. 

  2007. 	 *         array(

    2008. 

  2008. 	 *             'table'          => (string) {the name of the table this relationship is for},

    2009. 

  2009. 	 *             'column'         => (string) {the column in the specified table},

    2010. 

  2010. 	 *             'related_table'  => (string) {the related table},

    2011. 

  2011. 	 *             'related_column' => (string) {the related column}

    2012. 

  2012. 	 *         ), ...

    2013. 

  2013. 	 *     ),

    2014. 

  2014. 	 *     'many-to-one' => array(

    2015. 

  2015. 	 *         array(

    2016. 

  2016. 	 *             'table'          => (string) {the name of the table this relationship is for},

    2017. 

  2017. 	 *             'column'         => (string) {the column in the specified table},

    2018. 

  2018. 	 *             'related_table'  => (string) {the related table},

    2019. 

  2019. 	 *             'related_column' => (string) {the related column}

    2020. 

  2020. 	 *         ), ...

    2021. 

  2021. 	 *     ),

    2022. 

  2022. 	 *     'one-to-many' => array(

    2023. 

  2023. 	 *         array(

    2024. 

  2024. 	 *             'table'          => (string) {the name of the table this relationship is for},

    2025. 

  2025. 	 *             'column'         => (string) {the column in the specified table},

    2026. 

  2026. 	 *             'related_table'  => (string) {the related table},

    2027. 

  2027. 	 *             'related_column' => (string) {the related column},

    2028. 

  2028. 	 *             'on_delete'      => (string) {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    2029. 

  2029. 	 *             'on_update'      => (string) {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    2030. 

  2030. 	 *         ), ...

    2031. 

  2031. 	 *     ),

    2032. 

  2032. 	 *     'many-to-many' => array(

    2033. 

  2033. 	 *         array(

    2034. 

  2034. 	 *             'table'               => (string) {the name of the table this relationship is for},

    2035. 

  2035. 	 *             'column'              => (string) {the column in the specified table},

    2036. 

  2036. 	 *             'related_table'       => (string) {the related table},

    2037. 

  2037. 	 *             'related_column'      => (string) {the related column},

    2038. 

  2038. 	 *             'join_table'          => (string) {the table that joins the specified table to the related table},

    2039. 

  2039. 	 *             'join_column'         => (string) {the column in the join table that references 'column'},

    2040. 

  2040. 	 *             'join_related_column' => (string) {the column in the join table that references 'related_column'},

    2041. 

  2041. 	 *             'on_delete'           => (string) {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},

    2042. 

  2042. 	 *             'on_update'           => (string) {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}

    2043. 

  2043. 	 *         ), ...

    2044. 

  2044. 	 *     )

    2045. 

  2045. 	 * )

    2046. 

  2046. 	 * }}}

    2047. 

  2047. 	 * 

    2048. 

  2048. 	 * @param  string $table              The table to return the relationships for

    2049. 

  2049. 	 * @param  string $relationship_type  The type of relationship to return: `'one-to-one'`, `'many-to-one'`, `'one-to-many'`, `'many-to-many'`

    2050. 

  2050. 	 * @return array  An array of all relationships, or just the type specified - see method description for format

    2051. 

  2051. 	 */

    2052. 

  2052. 	public function getRelationships($table, $relationship_type=NULL)

    2053. 

  2053. 	{

    2054. 

  2054. 		$valid_relationship_types = array('one-to-one', 'many-to-one', 'one-to-many', 'many-to-many');

    2055. 

  2055. 		if ($relationship_type !== NULL && !in_array($relationship_type, $valid_relationship_types)) {

    2056. 

  2056. 			throw new fProgrammerException(

    2057. 

  2057. 				'The relationship type specified, %1$s, is invalid. Must be one of: %2$s.',

    2058. 

  2058. 				$relationship_type,

    2059. 

  2059. 				join(', ', $valid_relationship_types)

    2060. 

  2060. 			);

    2061. 

  2061. 		}

    2062. 

  2062. 		

    2063. 

  2063. 		// Return the saved column info if possible

    2064. 

  2064. 		if (!$relationship_type && isset($this->relationships[$table])) {

    2065. 

  2065. 			return $this->relationships[$table];

    2066. 

  2066. 		}

    2067. 

  2067. 		

    2068. 

  2068. 		if ($relationship_type && isset($this->relationships[$table][$relationship_type])) {

    2069. 

  2069. 			return $this->relationships[$table][$relationship_type];

    2070. 

  2070. 		}

    2071. 

  2071. 		

    2072. 

  2072. 		if (!in_array($table, $this->getTables())) {

    2073. 

  2073. 			throw new fProgrammerException(

    2074. 

  2074. 				'The table specified, %s, does not exist in the database',

    2075. 

  2075. 				$table

    2076. 

  2076. 			);

    2077. 

  2077. 		}

    2078. 

  2078. 		

    2079. 

  2079. 		$this->fetchKeys();

    2080. 

  2080. 		$this->mergeKeys();

    2081. 

  2081. 		

    2082. 

  2082. 		if ($relationship_type) {

    2083. 

  2083. 			return $this->relationships[$table][$relationship_type];

    2084. 

  2084. 		}

    2085. 

  2085. 		

    2086. 

  2086. 		return $this->relationships[$table];

    2087. 

  2087. 	}

    2088. 

  2088. 	

    2089. 

  2089. 	

    2090. 

  2090. 	/**

    2091. 

  2091. 	 * Returns the tables in the current database

    2092. 

  2092. 	 * 

    2093. 

  2093. 	 * @return array  The tables in the current database

    2094. 

  2094. 	 */

    2095. 

  2095. 	public function getTables()

    2096. 

  2096. 	{

    2097. 

  2097. 		if ($this->tables !== NULL) {

    2098. 

  2098. 			return $this->tables;

    2099. 

  2099. 		}

    2100. 

  2100. 		

    2101. 

  2101. 		switch ($this->database->getType()) {

    2102. 

  2102. 			case 'mssql':

    2103. 

  2103. 				$sql = "SELECT

    2104. 

  2104. 								TABLE_NAME

    2105. 

  2105. 							FROM

    2106. 

  2106. 								INFORMATION_SCHEMA.TABLES

    2107. 

  2107. 							WHERE

    2108. 

  2108. 								TABLE_NAME != 'sysdiagrams'

    2109. 

  2109. 							ORDER BY

    2110. 

  2110. 								LOWER(TABLE_NAME)";

    2111. 

  2111. 				break;

    2112. 

  2112. 			

    2113. 

  2113. 			case 'mysql':

    2114. 

  2114. 				$sql = 'SHOW TABLES';

    2115. 

  2115. 				break;

    2116. 

  2116. 			

    2117. 

  2117. 			case 'oracle':

    2118. 

  2118. 				$sql = "SELECT

    2119. 

  2119. 								LOWER(TABLE_NAME)

    2120. 

  2120. 							FROM

    2121. 

  2121. 								USER_TABLES

    2122. 

  2122. 							WHERE

    2123. 

  2123. 								SUBSTR(TABLE_NAME, 1, 4) <> 'BIN\$'

    2124. 

  2124. 							ORDER BY

    2125. 

  2125. 								TABLE_NAME ASC";

    2126. 

  2126. 				break;

    2127. 

  2127. 			

    2128. 

  2128. 			case 'postgresql':

    2129. 

  2129. 				$sql = "SELECT

    2130. 

  2130. 								 tablename

    2131. 

  2131. 							FROM

    2132. 

  2132. 								 pg_tables

    2133. 

  2133. 							WHERE

    2134. 

  2134. 								 tablename !~ '^(pg|sql)_'

    2135. 

  2135. 							ORDER BY

    2136. 

  2136. 								LOWER(tablename)";

    2137. 

  2137. 				break;

    2138. 

  2138. 								

    2139. 

  2139. 			case 'sqlite':

    2140. 

  2140. 				$sql = "SELECT

    2141. 

  2141. 								name

    2142. 

  2142. 							FROM

    2143. 

  2143. 								sqlite_master

    2144. 

  2144. 							WHERE

    2145. 

  2145. 								type = 'table' AND

    2146. 

  2146. 								name NOT LIKE 'sqlite_%'

    2147. 

  2147. 							ORDER BY

    2148. 

  2148. 								name ASC";

    2149. 

  2149. 				break;

    2150. 

  2150. 		}

    2151. 

  2151. 		

    2152. 

  2152. 		$result = $this->database->query($sql);

    2153. 

  2153. 		

    2154. 

  2154. 		$this->tables = array();

    2155. 

  2155. 		

    2156. 

  2156. 		foreach ($result as $row) {

    2157. 

  2157. 			$keys = array_keys($row);

    2158. 

  2158. 			$this->tables[] = $row[$keys[0]];

    2159. 

  2159. 		}

    2160. 

  2160. 		

    2161. 

  2161. 		if ($this->cache) {

    2162. 

  2162. 			$this->cache->set($this->makeCachePrefix() . 'tables', $this->tables);

    2163. 

  2163. 		}

    2164. 

  2164. 		

    2165. 

  2165. 		return $this->tables;

    2166. 

  2166. 	}

    2167. 

  2167. 		

    2168. 

  2168. 	

    2169. 

  2169. 	/**

    2170. 

  2170. 	 * Determines if a table is a joining table

    2171. 

  2171. 	 * 

    2172. 

  2172. 	 * @param  string $table  The table to check

    2173. 

  2173. 	 * @return boolean  If the table is a joining table

    2174. 

  2174. 	 */

    2175. 

  2175. 	private function isJoiningTable($table)

    2176. 

  2176. 	{

    2177. 

  2177. 		$primary_key_columns = $this->merged_keys[$table]['primary'];

    2178. 

  2178. 		

    2179. 

  2179. 		if (sizeof($primary_key_columns) != 2) {

    2180. 

  2180. 			return FALSE;	

    2181. 

  2181. 		}

    2182. 

  2182. 		

    2183. 

  2183. 		if (empty($this->merged_column_info[$table])) {

    2184. 

  2184. 			$this->getColumnInfo($table);	

    2185. 

  2185. 		}

    2186. 

  2186. 		if (sizeof($this->merged_column_info[$table]) != 2) {

    2187. 

  2187. 			return FALSE;	

    2188. 

  2188. 		}

    2189. 

  2189. 		

    2190. 

  2190. 		$foreign_key_columns = array();

    2191. 

  2191. 		foreach ($this->merged_keys[$table]['foreign'] as $key) {

    2192. 

  2192. 			$foreign_key_columns[] = $key['column'];

    2193. 

  2193. 		}

    2194. 

  2194. 		

    2195. 

  2195. 		return sizeof($foreign_key_columns) == 2 && !array_diff($foreign_key_columns, $primary_key_columns);

    2196. 

  2196. 	}

    2197. 

  2197. 	

    2198. 

  2198. 	

    2199. 

  2199. 	/**

    2200. 

  2200. 	 * Creates a unique cache prefix to help prevent cache conflicts

    2201. 

  2201. 	 * 

    2202. 

  2202. 	 * @return void

    2203. 

  2203. 	 */

    2204. 

  2204. 	private function makeCachePrefix()

    2205. 

  2205. 	{

    2206. 

  2206. 		$prefix  = 'fSchema::' . $this->database->getType() . '::';

    2207. 

  2207. 		if ($this->database->getHost()) {

    2208. 

  2208. 			$prefix .= $this->database->getHost() . '::';	

    2209. 

  2209. 		}

    2210. 

  2210. 		if ($this->database->getPort()) {

    2211. 

  2211. 			$prefix .= $this->database->getPort() . '::';	

    2212. 

  2212. 		}

    2213. 

  2213. 		$prefix .= $this->database->getDatabase() . '::';

    2214. 

  2214. 		if ($this->database->getUsername()) {

    2215. 

  2215. 			$prefix .= $this->database->getUsername() . '::';	

    2216. 

  2216. 		}

    2217. 

  2217. 		return $prefix;	

    2218. 

  2218. 	}

    2219. 

  2219. 	

    2220. 

  2220. 	

    2221. 

  2221. 	/**

    2222. 

  2222. 	 * Merges the column info with the column info override

    2223. 

  2223. 	 * 

    2224. 

  2224. 	 * @return void

    2225. 

  2225. 	 */

    2226. 

  2226. 	private function mergeColumnInfo()

    2227. 

  2227. 	{

    2228. 

  2228. 		$this->merged_column_info = $this->column_info;

    2229. 

  2229. 		

    2230. 

  2230. 		foreach ($this->column_info_override as $table => $columns) {

    2231. 

  2231. 			// Remove a table if the columns are set to NULL

    2232. 

  2232. 			if ($columns === NULL) {

    2233. 

  2233. 				unset($this->merged_column_info[$table]);

    2234. 

  2234. 				continue;	

    2235. 

  2235. 			}

    2236. 

  2236. 			

    2237. 

  2237. 			if (!isset($this->merged_column_info[$table])) {

    2238. 

  2238. 				$this->merged_column_info[$table] = array();

    2239. 

  2239. 			}

    2240. 

  2240. 			

    2241. 

  2241. 			foreach ($columns as $column => $info) {

    2242. 

  2242. 				// Remove a column if it is set to NULL

    2243. 

  2243. 				if ($info === NULL) {

    2244. 

  2244. 					unset($this->merged_column_info[$table][$column]);	

    2245. 

  2245. 					continue;

    2246. 

  2246. 				}

    2247. 

  2247. 				

    2248. 

  2248. 				if (!isset($this->merged_column_info[$table][$column])) {

    2249. 

  2249. 					$this->merged_column_info[$table][$column] = array();

    2250. 

  2250. 				}

    2251. 

  2251. 				

    2252. 

  2252. 				$this->merged_column_info[$table][$column] = array_merge($this->merged_column_info[$table][$column], $info);

    2253. 

  2253. 			}

    2254. 

  2254. 		}

    2255. 

  2255. 		

    2256. 

  2256. 		$optional_elements = array('not_null', 'default', 'valid_values', 'max_length', 'decimal_places', 'auto_increment');

    2257. 

  2257. 		

    2258. 

  2258. 		foreach ($this->merged_column_info as $table => $column_array) {

    2259. 

  2259. 			foreach ($column_array as $column => $info) {

    2260. 

  2260. 				if (empty($info['type'])) {

    2261. 

  2261. 					throw new fProgrammerException('The data type for the column %1$s is empty', $column);	

    2262. 

  2262. 				}

    2263. 

  2263. 				foreach ($optional_elements as $element) {

    2264. 

  2264. 					if (!isset($this->merged_column_info[$table][$column][$element])) {

    2265. 

  2265. 						$this->merged_column_info[$table][$column][$element] = ($element == 'auto_increment') ? FALSE : NULL;

    2266. 

  2266. 					}

    2267. 

  2267. 				}

    2268. 

  2268. 			}

    2269. 

  2269. 		}

    2270. 

  2270. 		

    2271. 

  2271. 		if ($this->cache) {

    2272. 

  2272. 			$this->cache->set($this->makeCachePrefix() . 'merged_column_info', $this->merged_column_info);	

    2273. 

  2273. 		}

    2274. 

  2274. 	}

    2275. 

  2275. 		

    2276. 

  2276. 	

    2277. 

  2277. 	/**

    2278. 

  2278. 	 * Merges the keys with the keys override

    2279. 

  2279. 	 * 

    2280. 

  2280. 	 * @return void

    2281. 

  2281. 	 */

    2282. 

  2282. 	private function mergeKeys()

    2283. 

  2283. 	{

    2284. 

  2284. 		// Handle the database and override key info

    2285. 

  2285. 		$this->merged_keys = $this->keys;

    2286. 

  2286. 		

    2287. 

  2287. 		foreach ($this->keys_override as $table => $info) {

    2288. 

  2288. 			if (!isset($this->merged_keys[$table])) {

    2289. 

  2289. 				$this->merged_keys[$table] = array();

    2290. 

  2290. 			}

    2291. 

  2291. 			$this->merged_keys[$table] = array_merge($this->merged_keys[$table], $info);

    2292. 

  2292. 		}

    2293. 

  2293. 		

    2294. 

  2294. 		if ($this->cache) {

    2295. 

  2295. 			$this->cache->set($this->makeCachePrefix() . 'merged_keys', $this->merged_keys);	

    2296. 

  2296. 		}

    2297. 

  2297. 		

    2298. 

  2298. 		$this->findRelationships();

    2299. 

  2299. 	}

    2300. 

  2300. 	

    2301. 

  2301. 	

    2302. 

  2302. 	/**

    2303. 

  2303. 	 * Allows overriding of column info

    2304. 

  2304. 	 * 

    2305. 

  2305. 	 * Performs an array merge with the column info detected from the database.

    2306. 

  2306. 	 * 

    2307. 

  2307. 	 * To erase a whole table, set the `$column_info` to `NULL`. To erase a

    2308. 

  2308. 	 * column, set the `$column_info` for that column to `NULL`.

    2309. 

  2309. 	 * 

    2310. 

  2310. 	 * If the `$column_info` parameter is not `NULL`, it should be an

    2311. 

  2311. 	 * associative array containing one or more of the following keys. Please

    2312. 

  2312. 	 * see ::getColumnInfo() for a description of each.

    2313. 

  2313. 	 *  - `'type'`

    2314. 

  2314. 	 *  - `'not_null'`

    2315. 

  2315. 	 *  - `'default'`

    2316. 

  2316. 	 *  - `'valid_values'`

    2317. 

  2317. 	 *  - `'max_length'`

    2318. 

  2318. 	 *  - `'decimal_places'`

    2319. 

  2319. 	 *  - `'auto_increment'`

    2320. 

  2320. 	 * 

    2321. 

  2321. 	 * The following keys may be set to `NULL`:

    2322. 

  2322. 	 *  - `'not_null'`

    2323. 

  2323. 	 *  - `'default'`

    2324. 

  2324. 	 *  - `'valid_values'`

    2325. 

  2325. 	 *  - `'max_length'`

    2326. 

  2326. 	 *  - `'decimal_places'`

    2327. 

  2327. 	 *  

    2328. 

  2328. 	 * The key `'auto_increment'` should be a boolean.

    2329. 

  2329. 	 * 

    2330. 

  2330. 	 * The `'type'` key should be one of:

    2331. 

  2331. 	 *  - `'blob'`

    2332. 

  2332. 	 *  - `'boolean'`

    2333. 

  2333. 	 *  - `'char'`

    2334. 

  2334. 	 *  - `'date'`

    2335. 

  2335. 	 *  - `'float'`

    2336. 

  2336. 	 *  - `'integer'`

    2337. 

  2337. 	 *  - `'text'`

    2338. 

  2338. 	 *  - `'time'`

    2339. 

  2339. 	 *  - `'timestamp'`

    2340. 

  2340. 	 *  - `'varchar'`

    2341. 

  2341. 	 * 

    2342. 

  2342. 	 * @param  array  $column_info  The modified column info - see method description for format

    2343. 

  2343. 	 * @param  string $table        The table to override

    2344. 

  2344. 	 * @param  string $column       The column to override

    2345. 

  2345. 	 * @return void

    2346. 

  2346. 	 */

    2347. 

  2347. 	public function setColumnInfoOverride($column_info, $table, $column=NULL)

    2348. 

  2348. 	{

    2349. 

  2349. 		if (!isset($this->column_info_override[$table])) {

    2350. 

  2350. 			$this->column_info_override[$table] = array();

    2351. 

  2351. 		}

    2352. 

  2352. 		

    2353. 

  2353. 		if (!empty($column)) {

    2354. 

  2354. 			$this->column_info_override[$table][$column] = $column_info;

    2355. 

  2355. 		} else {

    2356. 

  2356. 			$this->column_info_override[$table] = $column_info;

    2357. 

  2357. 		}

    2358. 

  2358. 		

    2359. 

  2359. 		$this->fetchColumnInfo($table);

    2360. 

  2360. 		$this->mergeColumnInfo();

    2361. 

  2361. 	}

    2362. 

  2362. 	

    2363. 

  2363. 	

    2364. 

  2364. 	/**

    2365. 

  2365. 	 * Allows overriding of key info. Replaces existing info, so be sure to provide full key info for type selected or all types.

    2366. 

  2366. 	 * 

    2367. 

  2367. 	 * @param  array  $keys      The modified keys - see ::getKeys() for format

    2368. 

  2368. 	 * @param  string $table     The table to override

    2369. 

  2369. 	 * @param  string $key_type  The key type to override: `'primary'`, `'foreign'`, `'unique'`

    2370. 

  2370. 	 * @return void

    2371. 

  2371. 	 */

    2372. 

  2372. 	public function setKeysOverride($keys, $table, $key_type=NULL)

    2373. 

  2373. 	{

    2374. 

  2374. 		$valid_key_types = array('primary', 'foreign', 'unique');

    2375. 

  2375. 		if (!in_array($key_type, $valid_key_types)) {

    2376. 

  2376. 			throw new fProgrammerException(

    2377. 

  2377. 				'The key type specified, %1$s, is invalid. Must be one of: %2$s.',

    2378. 

  2378. 				$key_type,

    2379. 

  2379. 				join(', ', $valid_key_types)

    2380. 

  2380. 			);

    2381. 

  2381. 		}

    2382. 

  2382. 		

    2383. 

  2383. 		if (!isset($this->keys_override[$table])) {

    2384. 

  2384. 			$this->keys_override[$table] = array();

    2385. 

  2385. 		}

    2386. 

  2386. 		

    2387. 

  2387. 		if (!empty($key_type)) {

    2388. 

  2388. 			$this->keys_override[$table][$key_type] = $keys;

    2389. 

  2389. 		} else {

    2390. 

  2390. 			$this->keys_override[$table] = $keys;

    2391. 

  2391. 		}

    2392. 

  2392. 		

    2393. 

  2393. 		$this->fetchKeys();

    2394. 

  2394. 		$this->mergeKeys();

    2395. 

  2395. 	}

    2396. 

  2396. }

    2397. 

  2397. 

    2398. 

  2398. 

    2399. 

  2399. 

    2400. 

  2400. /**

    2401. 

  2401.  * Copyright (c) 2007-2009 Will Bond <will@flourishlib.com>

    2402. 

  2402.  * 

    2403. 

  2403.  * Permission is hereby granted, free of charge, to any person obtaining a copy

    2404. 

  2404.  * of this software and associated documentation files (the "Software"), to deal

    2405. 

  2405.  * in the Software without restriction, including without limitation the rights

    2406. 

  2406.  * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

    2407. 

  2407.  * copies of the Software, and to permit persons to whom the Software is

    2408. 

  2408.  * furnished to do so, subject to the following conditions:

    2409. 

  2409.  * 

    2410. 

  2410.  * The above copyright notice and this permission notice shall be included in

    2411. 

  2411.  * all copies or substantial portions of the Software.

    2412. 

  2412.  * 

    2413. 

  2413.  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

    2414. 

  2414.  * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

    2415. 

  2415.  * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

    2416. 

  2416.  * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

    2417. 

  2417.  * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

    2418. 

  2418.  * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN

    2419. 

  2419.  * THE SOFTWARE.

    2420. 

  2420.  */
    2421.