PageRenderTime 61ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 1ms

/inc/flourish/fSchema.php

https://github.com/trbs/Graphite-Tattle
PHP | 3153 lines | 2247 code | 319 blank | 587 comment | 314 complexity | 45c080c223156ed8b16871645b454a99 MD5 | raw file
Possible License(s): Apache-2.0

Large files files are truncated, but you can click here to view the full file

    

  1. <?php
    2. 

  2. /**
    3. 

  3.  * Gets schema information for the selected database
    4. 

  4.  * 
    5. 

  5.  * @copyright  Copyright (c) 2007-2011 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.0b50
    13. 

  13.  * @changes    1.0.0b50  Fixed detection of explicitly named SQLite foreign key constraints [wb, 2011-08-23]
    14. 

  14.  * @changes    1.0.0b49  Added support for spatial/geometric data types in MySQL and PostgreSQL [wb, 2011-05-26]
    15. 

  15.  * @changes    1.0.0b48  Fixed a bug with ::getTables() not working on MySQL 4.x, fixed ::getKeys() to always return a reset array [wb, 2011-05-24]
    16. 

  16.  * @changes    1.0.0b47  Backwards Compatibility Break - ::getTables(), ::getColumnInfo(), ::getDatabases(), ::getKeys() and ::getRelationships() now return database, schema, table and column names in lowercase, added the `$creation_order` parameter to ::getTables(), fixed bugs with getting column and key information from MSSQL, Oracle and SQLite [wb, 2011-05-09]
    17. 

  17.  * @changes    1.0.0b46  Enhanced SQLite schema detection to cover situations where `UNIQUE` constraints are defined separately from the table and when comments are used in `CREATE TABLE` statements [wb, 2011-02-06]
    18. 

  18.  * @changes    1.0.0b45  Fixed Oracle auto incrementing detection to work with `INSERT OR UPDATE` triggers, fixed detection of dynamic default date/time/timestamp values for DB2 and Oracle [wb, 2010-12-04]
    19. 

  19.  * @changes    1.0.0b44  Fixed the list of valid elements for ::getColumnInfo() [wb, 2010-11-28]
    20. 

  20.  * @changes    1.0.0b43  Added the `comment` element to the information returned by ::getColumnInfo() [wb, 2010-11-28]
    21. 

  21.  * @changes    1.0.0b42  Fixed a bug with MySQL detecting default `ON DELETE` clauses [wb, 2010-10-19]
    22. 

  22.  * @changes    1.0.0b41  Fixed handling MySQL table names that require quoting [wb, 2010-08-24]
    23. 

  23.  * @changes    1.0.0b40  Fixed bugs in the documentation and error message of ::getColumnInfo() about what are valid elements [wb, 2010-07-21]
    24. 

  24.  * @changes    1.0.0b39  Fixed a regression where key detection SQL was not compatible with PostgreSQL 8.1 [wb, 2010-04-13]
    25. 

  25.  * @changes    1.0.0b38  Added Oracle support to ::getDatabases() [wb, 2010-04-13]
    26. 

  26.  * @changes    1.0.0b37  Fixed ::getDatabases() for MSSQL [wb, 2010-04-09]
    27. 

  27.  * @changes    1.0.0b36  Fixed PostgreSQL to properly report explicit `NULL` default values via ::getColumnInfo() [wb, 2010-03-30]
    28. 

  28.  * @changes    1.0.0b35  Added `max_length` values for various text and blob data types across all databases [wb, 2010-03-29]
    29. 

  29.  * @changes    1.0.0b34  Added `min_value` and `max_value` attributes to ::getColumnInfo() to specify the valid range for numeric columns [wb, 2010-03-16]
    30. 

  30.  * @changes    1.0.0b33  Changed it so that PostgreSQL unique indexes containing functions are ignored since they can't be properly detected at this point [wb, 2010-03-14]
    31. 

  31.  * @changes    1.0.0b32  Fixed ::getTables() to not include views for MySQL [wb, 2010-03-14]
    32. 

  32.  * @changes    1.0.0b31  Fixed the creation of the default caching key for ::enableCaching() [wb, 2010-03-02]
    33. 

  33.  * @changes    1.0.0b30  Fixed the class to work with lower privilege Oracle accounts and added detection of Oracle number columns [wb, 2010-01-25]
    34. 

  34.  * @changes    1.0.0b29  Added on_delete and on_update elements to one-to-one relationship info retrieved by ::getRelationships() [wb, 2009-12-16]
    35. 

  35.  * @changes    1.0.0b28  Fixed a bug with detecting some multi-column unique constraints in SQL Server databases [wb, 2009-11-13]
    36. 

  36.  * @changes    1.0.0b27  Added a parameter to ::enableCaching() to provide a key token that will allow cached values to be shared between multiple databases with the same schema [wb, 2009-10-28]
    37. 

  37.  * @changes    1.0.0b26  Added the placeholder element to the output of ::getColumnInfo(), added support for PostgreSQL, MSSQL and Oracle "schemas", added support for parsing quoted SQLite identifiers [wb, 2009-10-22]
    38. 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  63.  */
    64. 

  64. class fSchema
    65. 

  65. {
    66. 

  66. 	/**
    67. 

  67. 	 * The place to cache to
    68. 

  68. 	 * 
    69. 

  69. 	 * @var fCache
    70. 

  70. 	 */
    71. 

  71. 	private $cache = NULL;
    72. 

  72. 	
    73. 

  73. 	/**
    74. 

  74. 	 * The cache prefix to use for cache entries
    75. 

  75. 	 * 
    76. 

  76. 	 * @var string
    77. 

  77. 	 */
    78. 

  78. 	private $cache_prefix;
    79. 

  79. 	
    80. 

  80. 	/**
    81. 

  81. 	 * The cached column info
    82. 

  82. 	 * 
    83. 

  83. 	 * @var array
    84. 

  84. 	 */
    85. 

  85. 	private $column_info = array();
    86. 

  86. 	
    87. 

  87. 	/**
    88. 

  88. 	 * The column info to override
    89. 

  89. 	 * 
    90. 

  90. 	 * @var array
    91. 

  91. 	 */
    92. 

  92. 	private $column_info_override = array();
    93. 

  93. 	
    94. 

  94. 	/**
    95. 

  95. 	 * A reference to an instance of the fDatabase class
    96. 

  96. 	 * 
    97. 

  97. 	 * @var fDatabase
    98. 

  98. 	 */
    99. 

  99. 	private $database = NULL;
    100. 

  100. 	
    101. 

  101. 	/**
    102. 

  102. 	 * The databases on the current database server
    103. 

  103. 	 * 
    104. 

  104. 	 * @var array
    105. 

  105. 	 */
    106. 

  106. 	private $databases = NULL;
    107. 

  107. 	
    108. 

  108. 	/**
    109. 

  109. 	 * The cached key info
    110. 

  110. 	 * 
    111. 

  111. 	 * @var array
    112. 

  112. 	 */
    113. 

  113. 	private $keys = array();
    114. 

  114. 	
    115. 

  115. 	/**
    116. 

  116. 	 * The key info to override
    117. 

  117. 	 * 
    118. 

  118. 	 * @var array
    119. 

  119. 	 */
    120. 

  120. 	private $keys_override = array();
    121. 

  121. 	
    122. 

  122. 	/**
    123. 

  123. 	 * The merged column info
    124. 

  124. 	 * 
    125. 

  125. 	 * @var array
    126. 

  126. 	 */
    127. 

  127. 	private $merged_column_info = array();
    128. 

  128. 	
    129. 

  129. 	/**
    130. 

  130. 	 * The merged key info
    131. 

  131. 	 * 
    132. 

  132. 	 * @var array
    133. 

  133. 	 */
    134. 

  134. 	private $merged_keys = array();
    135. 

  135. 	
    136. 

  136. 	/**
    137. 

  137. 	 * The relationships in the database
    138. 

  138. 	 * 
    139. 

  139. 	 * @var array
    140. 

  140. 	 */
    141. 

  141. 	private $relationships = array();
    142. 

  142. 	
    143. 

  143. 	/**
    144. 

  144. 	 * The tables in the database
    145. 

  145. 	 * 
    146. 

  146. 	 * @var array
    147. 

  147. 	 */
    148. 

  148. 	private $tables = NULL;
    149. 

  149. 	
    150. 

  150. 	
    151. 

  151. 	/**
    152. 

  152. 	 * Sets the database
    153. 

  153. 	 * 
    154. 

  154. 	 * @param  fDatabase $database  The fDatabase instance
    155. 

  155. 	 * @return fSchema
    156. 

  156. 	 */
    157. 

  157. 	public function __construct($database)
    158. 

  158. 	{
    159. 

  159. 		$this->database = $database;
    160. 

  160. 	}
    161. 

  161. 	
    162. 

  162. 	
    163. 

  163. 	/**
    164. 

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

  165. 	 * 
    166. 

  166. 	 * @internal
    167. 

  167. 	 * 
    168. 

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

  169. 	 * @return callback  The callback for the method requested
    170. 

  170. 	 */
    171. 

  171. 	public function __get($method)
    172. 

  172. 	{
    173. 

  173. 		return array($this, $method);		
    174. 

  174. 	}
    175. 

  175. 	
    176. 

  176. 	
    177. 

  177. 	/**
    178. 

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

  179. 	 * 
    180. 

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

  181. 	 * @param  string $column  The column to check
    182. 

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

  183. 	 */
    184. 

  184. 	private function checkForSingleColumnUniqueKey($table, $column)
    185. 

  185. 	{        
    186. 

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

  187. 			if (array($column) == $key) {
    188. 

  188. 				return TRUE;
    189. 

  189. 			}
    190. 

  190. 		}
    191. 

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

  192. 			return TRUE;
    193. 

  193. 		}
    194. 

  194. 		return FALSE;
    195. 

  195. 	}
    196. 

  196. 	
    197. 

  197. 	
    198. 

  198. 	/**
    199. 

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

  200. 	 * 
    201. 

  201. 	 * @internal
    202. 

  202. 	 * 
    203. 

  203. 	 * @return void
    204. 

  204. 	 */
    205. 

  205. 	public function clearCache()
    206. 

  206. 	{
    207. 

  207. 		$this->column_info        = array();
    208. 

  208. 		$this->databases          = NULL;
    209. 

  209. 		$this->keys               = array();
    210. 

  210. 		$this->merged_column_info = array();
    211. 

  211. 		$this->merged_keys        = array();
    212. 

  212. 		$this->relationships      = array();
    213. 

  213. 		$this->tables             = NULL;
    214. 

  214. 		if ($this->cache) {
    215. 

  215. 			$prefix = $this->makeCachePrefix();
    216. 

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

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

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

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

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

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

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

  223. 		}
    224. 

  224. 	}
    225. 

  225. 	
    226. 

  226. 	
    227. 

  227. 	/**
    228. 

  228. 	 * Returns an ordered array of table names, in a valid table creation order
    229. 

  229. 	 * 
    230. 

  230. 	 * @param string  $filter_table  The only return this table and tables that rely on it
    231. 

  231. 	 * @return array  An array of table names
    232. 

  232. 	 */
    233. 

  233. 	private function determineTableCreationOrder($filter_table=NULL)
    234. 

  234. 	{
    235. 

  235. 		$found          = array();
    236. 

  236. 		$ignored_found  = array();
    237. 

  237. 		
    238. 

  238. 		$current_tables = $this->getTables();
    239. 

  239. 		while ($current_tables) {
    240. 

  240. 			$remaining_tables = array();
    241. 

  241. 			foreach ($current_tables as $table) {
    242. 

  242. 				$foreign_keys = $this->getKeys($table, 'foreign');
    243. 

  243. 				if (!$foreign_keys) {
    244. 

  244. 					if ($filter_table !== NULL) {
    245. 

  245. 						if ($table == $filter_table) {
    246. 

  246. 							$found[] = $table;
    247. 

  247. 						} else {
    248. 

  248. 							$ignored_found[] = $table;
    249. 

  249. 						}
    250. 

  250. 					} else {
    251. 

  251. 						$found[] = $table;
    252. 

  252. 					}
    253. 

  253. 					
    254. 

  254. 				} else {
    255. 

  255. 					$all_dependencies_met = TRUE;
    256. 

  256. 					$found_dependencies   = 0;
    257. 

  257. 					foreach ($foreign_keys as $foreign_key) {
    258. 

  258. 						if (!in_array($foreign_key['foreign_table'], $found) && !in_array($foreign_key['foreign_table'], $ignored_found)) {
    259. 

  259. 							$all_dependencies_met = FALSE;
    260. 

  260. 							break;
    261. 

  261. 						} elseif (in_array($foreign_key['foreign_table'], $found)) {
    262. 

  262. 							$found_dependencies++;
    263. 

  263. 						}
    264. 

  264. 					}
    265. 

  265. 					if ($all_dependencies_met) {
    266. 

  266. 						if ($filter_table !== NULL) {
    267. 

  267. 							if ($found_dependencies || $table == $filter_table) {
    268. 

  268. 								$found[] = $table;
    269. 

  269. 							} else {
    270. 

  270. 								$ignored_found[] = $table;
    271. 

  271. 							}
    272. 

  272. 						} else {
    273. 

  273. 							$found[] = $table;
    274. 

  274. 						}
    275. 

  275. 						
    276. 

  276. 					} else {
    277. 

  277. 						$remaining_tables[] = $table;
    278. 

  278. 					}
    279. 

  279. 				}
    280. 

  280. 			}
    281. 

  281. 			$current_tables = $remaining_tables;
    282. 

  282. 		}
    283. 

  283. 		
    284. 

  284. 		return $found;
    285. 

  285. 	}
    286. 

  286. 	
    287. 

  287. 	
    288. 

  288. 	/**
    289. 

  289. 	 * Sets the schema to be cached to the fCache object specified
    290. 

  290. 	 * 
    291. 

  291. 	 * @param  fCache $cache      The cache to cache to
    292. 

  292. 	 * @param  string $key_token  Internal use only! (this will be used in the cache key to uniquely identify the cache for this fSchema object) 
    293. 

  293. 	 * @return void
    294. 

  294. 	 */
    295. 

  295. 	public function enableCaching($cache, $key_token=NULL)
    296. 

  296. 	{
    297. 

  297. 		$this->cache = $cache;
    298. 

  298. 		
    299. 

  299. 		if ($key_token !== NULL) {
    300. 

  300. 			$this->cache_prefix = 'fSchema::' . $this->database->getType() . '::' . $key_token . '::';
    301. 

  301. 		}
    302. 

  302. 		$prefix = $this->makeCachePrefix();
    303. 

  303. 		
    304. 

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

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

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

  307. 		
    308. 

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

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

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

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

  312. 		}
    313. 

  313. 		
    314. 

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

  315. 	}
    316. 

  316. 	
    317. 

  317. 	
    318. 

  318. 	/**
    319. 

  319. 	 * Gets the column info from the database for later access
    320. 

  320. 	 * 
    321. 

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

  322. 	 * @return void
    323. 

  323. 	 */
    324. 

  324. 	private function fetchColumnInfo($table)
    325. 

  325. 	{
    326. 

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

  327. 			return;	
    328. 

  328. 		}
    329. 

  329. 		
    330. 

  330. 		switch ($this->database->getType()) {
    331. 

  331. 			case 'db2':
    332. 

  332. 				$column_info = $this->fetchDB2ColumnInfo($table);
    333. 

  333. 				break;
    334. 

  334. 			
    335. 

  335. 			case 'mssql':
    336. 

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

  337. 				break;
    338. 

  338. 			
    339. 

  339. 			case 'mysql':
    340. 

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

  341. 				break;
    342. 

  342. 				
    343. 

  343. 			case 'oracle':
    344. 

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

  345. 				break;
    346. 

  346. 			
    347. 

  347. 			case 'postgresql':
    348. 

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

  349. 				break;
    350. 

  350. 				
    351. 

  351. 			case 'sqlite':
    352. 

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

  353. 				break;
    354. 

  354. 		}
    355. 

  355. 			
    356. 

  356. 		if (!$column_info) {
    357. 

  357. 			return;	
    358. 

  358. 		}
    359. 

  359. 			
    360. 

  360. 		$this->column_info[$table] = $column_info;
    361. 

  361. 		if ($this->cache) {
    362. 

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

  363. 		}
    364. 

  364. 	}
    365. 

  365. 	
    366. 

  366. 	
    367. 

  367. 	/**
    368. 

  368. 	 * Gets the column info from a DB2 database
    369. 

  369. 	 * 
    370. 

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

  371. 	 * @return array  The column info for the table specified - see ::getColumnInfo() for details
    372. 

  372. 	 */
    373. 

  373. 	private function fetchDB2ColumnInfo($table)
    374. 

  374. 	{
    375. 

  375. 		$column_info = array();
    376. 

  376. 		
    377. 

  377. 		$schema = strtolower($this->database->getUsername());
    378. 

  378. 		if (strpos($table, '.') !== FALSE) {
    379. 

  379. 			list ($schema, $table) = explode('.', $table);
    380. 

  380. 		}
    381. 

  381. 		
    382. 

  382. 		$data_type_mapping = array(
    383. 

  383. 			'smallint'          => 'integer',
    384. 

  384. 			'integer'           => 'integer',
    385. 

  385. 			'bigint'            => 'integer',
    386. 

  386. 			'timestamp'         => 'timestamp',
    387. 

  387. 			'date'              => 'date',
    388. 

  388. 			'time'              => 'time',
    389. 

  389. 			'varchar'           => 'varchar',
    390. 

  390. 			'long varchar'      => 'varchar',
    391. 

  391. 			'vargraphic'        => 'varchar',
    392. 

  392. 			'long vargraphic'   => 'varchar',
    393. 

  393. 			'character'         => 'char',
    394. 

  394. 			'graphic'           => 'char',
    395. 

  395. 			'real'              => 'float',
    396. 

  396. 			'decimal'           => 'float',
    397. 

  397. 			'numeric'           => 'float',
    398. 

  398. 			'blob'              => 'blob',
    399. 

  399. 			'clob'              => 'text',
    400. 

  400. 			'dbclob'            => 'text'
    401. 

  401. 		);
    402. 

  402. 		
    403. 

  403. 		$max_min_values = array(
    404. 

  404. 			'smallint'   => array('min' => new fNumber(-32768),                  'max' => new fNumber(32767)),
    405. 

  405. 			'integer'    => array('min' => new fNumber(-2147483648),             'max' => new fNumber(2147483647)),
    406. 

  406. 			'bigint'     => array('min' => new fNumber('-9223372036854775808'),  'max' => new fNumber('9223372036854775807'))
    407. 

  407. 		);
    408. 

  408. 		
    409. 

  409. 		// Get the column info
    410. 

  410. 		$sql = "SELECT
    411. 

  411. 					LOWER(C.COLNAME) AS \"COLUMN\",
    412. 

  412. 					C.TYPENAME AS TYPE,
    413. 

  413. 					C.NULLS AS NULLABLE,
    414. 

  414. 					C.DEFAULT,
    415. 

  415. 					C.LENGTH AS MAX_LENGTH,
    416. 

  416. 					C.SCALE,
    417. 

  417. 					CASE WHEN C.IDENTITY = 'Y' AND (C.GENERATED = 'D' OR C.GENERATED = 'A') THEN '1' ELSE '0' END AS AUTO_INCREMENT,
    418. 

  418. 					CH.TEXT AS \"CONSTRAINT\",
    419. 

  419. 					C.REMARKS AS \"COMMENT\"
    420. 

  420. 				FROM
    421. 

  421. 					SYSCAT.COLUMNS AS C LEFT JOIN
    422. 

  422. 					SYSCAT.COLCHECKS AS CC ON
    423. 

  423. 						C.TABSCHEMA = CC.TABSCHEMA AND
    424. 

  424. 						C.TABNAME = CC.TABNAME AND
    425. 

  425. 						C.COLNAME = CC.COLNAME AND
    426. 

  426. 						CC.USAGE = 'R' LEFT JOIN
    427. 

  427. 					SYSCAT.CHECKS AS CH ON
    428. 

  428. 						C.TABSCHEMA = CH.TABSCHEMA AND
    429. 

  429. 						C.TABNAME = CH.TABNAME AND
    430. 

  430. 						CH.TYPE = 'C' AND
    431. 

  431. 						CH.CONSTNAME = CC.CONSTNAME
    432. 

  432. 				WHERE
    433. 

  433. 					LOWER(C.TABSCHEMA) = %s AND
    434. 

  434. 					LOWER(C.TABNAME) = %s
    435. 

  435. 				ORDER BY
    436. 

  436. 					C.COLNO ASC";
    437. 

  437. 		
    438. 

  438. 		$result = $this->database->query($sql, strtolower($schema), strtolower($table));
    439. 

  439. 		
    440. 

  440. 		foreach ($result as $row) {
    441. 

  441. 			
    442. 

  442. 			$info = array();
    443. 

  443. 			
    444. 

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

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

  446. 					if (isset($max_min_values[$data_type])) {
    447. 

  447. 						$info['min_value'] = $max_min_values[$data_type]['min'];
    448. 

  448. 						$info['max_value'] = $max_min_values[$data_type]['max'];
    449. 

  449. 					}
    450. 

  450. 					$info['type'] = $mapped_data_type;
    451. 

  451. 					break;
    452. 

  452. 				}
    453. 

  453. 			}
    454. 

  454. 			
    455. 

  455. 			// Handle decimal places and min/max for numeric/decimals
    456. 

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

  457. 				$info['decimal_places'] = $row['scale'];
    458. 

  458. 				$before_digits = str_pad('', $row['max_length'] - $row['scale'], '9');
    459. 

  459. 				$after_digits  = str_pad('', $row['scale'], '9');
    460. 

  460. 				$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    461. 

  461. 				$info['min_value'] = new fNumber('-' . $max_min);
    462. 

  462. 				$info['max_value'] = new fNumber($max_min);
    463. 

  463. 			}
    464. 

  464. 			
    465. 

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

  466. 				$info['type'] = $row['type'];
    467. 

  467. 			}
    468. 

  468. 			
    469. 

  469. 			// Handle the special data for varchar columns
    470. 

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

  471. 				$info['max_length'] = $row['max_length'];
    472. 

  472. 			}
    473. 

  473. 			
    474. 

  474. 			// The generally accepted practice for boolean on DB2 is a CHAR(1) with a CHECK constraint
    475. 

  475. 			if ($info['type'] == 'char' && $info['max_length'] == 1 && !empty($row['constraint'])) {
    476. 

  476. 				if (is_resource($row['constraint'])) {
    477. 

  477. 					$row['constraint'] = stream_get_contents($row['constraint']);
    478. 

  478. 				}
    479. 

  479. 				if (preg_match('/^\s*"?' . preg_quote($row['column'], '/') . '"?\s+in\s+\(\s*(\'0\',\s*\'1\'|\'1\',\s*\'0\')\s*\)\s*$/i', $row['constraint'])) {
    480. 

  480. 					$info['type'] = 'boolean';
    481. 

  481. 					$info['max_length'] = NULL;
    482. 

  482. 				}
    483. 

  483. 			}
    484. 

  484. 			
    485. 

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

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

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

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

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

  490. 					}			
    491. 

  491. 				}
    492. 

  492. 			}
    493. 

  493. 			
    494. 

  494. 			// Handle auto increment
    495. 

  495. 			if ($row['auto_increment']) {
    496. 

  496. 				$info['auto_increment'] = TRUE;
    497. 

  497. 			}
    498. 

  498. 			
    499. 

  499. 			// Handle default values
    500. 

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

  501. 				if ($row['default'] == 'NULL') {
    502. 

  502. 					$info['default'] = NULL;
    503. 

  503. 				} elseif (in_array($info['type'], array('timestamp', 'date', 'time')) && $row['default'][0] != "'") {
    504. 

  504. 					$info['default'] = str_replace(' ', '_', $row['default']);
    505. 

  505. 				} elseif (in_array($info['type'], array('char', 'varchar', 'text', 'timestamp', 'date', 'time')) ) {
    506. 

  506. 					$info['default'] = substr($row['default'], 1, -1);
    507. 

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

  508. 					$info['default'] = (boolean) substr($row['default'], 1, -1);
    509. 

  509. 				} else {
    510. 

  510. 					$info['default'] = $row['default'];
    511. 

  511. 				}
    512. 

  512. 			}
    513. 

  513. 			
    514. 

  514. 			// Handle not null
    515. 

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

  516. 			
    517. 

  517. 			$info['comment'] = $row['comment'];
    518. 

  518. 			
    519. 

  519. 			$column_info[$row['column']] = $info;
    520. 

  520. 		}
    521. 

  521. 		
    522. 

  522. 		return $column_info;
    523. 

  523. 	}
    524. 

  524. 	
    525. 

  525. 	
    526. 

  526. 	/**
    527. 

  527. 	 * Fetches the key info for a DB2 database
    528. 

  528. 	 * 
    529. 

  529. 	 * @return array  The keys arrays for every table in the database - see ::getKeys() for details
    530. 

  530. 	 */
    531. 

  531. 	private function fetchDB2Keys()
    532. 

  532. 	{
    533. 

  533. 		$keys = array();
    534. 

  534. 		
    535. 

  535. 		$default_schema = strtolower($this->database->getUsername());
    536. 

  536. 		
    537. 

  537. 		$tables = $this->getTables();
    538. 

  538. 		foreach ($tables as $table) {
    539. 

  539. 			$keys[$table] = array();
    540. 

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

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

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

  543. 		}
    544. 

  544. 		
    545. 

  545. 		$params  = array();
    546. 

  546. 		
    547. 

  547. 		$sql  = "(SELECT
    548. 

  548. 					LOWER(RTRIM(R.TABSCHEMA)) AS \"SCHEMA\",
    549. 

  549. 					LOWER(R.TABNAME) AS \"TABLE\",
    550. 

  550. 					R.CONSTNAME AS CONSTRAINT_NAME,
    551. 

  551. 					'foreign' AS \"TYPE\",
    552. 

  552. 					LOWER(K.COLNAME) AS \"COLUMN\",
    553. 

  553. 					LOWER(RTRIM(R.REFTABSCHEMA)) AS FOREIGN_SCHEMA,
    554. 

  554. 					LOWER(R.REFTABNAME) AS FOREIGN_TABLE,
    555. 

  555. 					LOWER(FK.COLNAME) AS FOREIGN_COLUMN,
    556. 

  556. 					CASE R.DELETERULE WHEN 'C' THEN 'cascade' WHEN 'A' THEN 'no_action' WHEN 'R' THEN 'restrict' ELSE 'set_null' END AS ON_DELETE,
    557. 

  557. 					CASE R.UPDATERULE WHEN 'A' THEN 'no_action' WHEN 'R' THEN 'restrict' END AS ON_UPDATE,
    558. 

  558. 					K.COLSEQ
    559. 

  559. 				FROM
    560. 

  560. 					SYSCAT.REFERENCES AS R INNER JOIN 
    561. 

  561. 					SYSCAT.KEYCOLUSE AS K ON
    562. 

  562. 						R.CONSTNAME = K.CONSTNAME AND
    563. 

  563. 						R.TABSCHEMA = K.TABSCHEMA AND
    564. 

  564. 						R.TABNAME = K.TABNAME INNER JOIN
    565. 

  565. 					SYSCAT.KEYCOLUSE AS FK ON
    566. 

  566. 						R.REFKEYNAME = FK.CONSTNAME AND
    567. 

  567. 						R.REFTABSCHEMA = FK.TABSCHEMA AND
    568. 

  568. 						R.REFTABNAME = FK.TABNAME
    569. 

  569. 				WHERE ";
    570. 

  570. 		
    571. 

  571. 		$conditions = array();
    572. 

  572. 		foreach ($tables as $table) {
    573. 

  573. 			if (strpos($table, '.') === FALSE) {
    574. 

  574. 				$table = $default_schema . '.' . $table;
    575. 

  575. 			}	
    576. 

  576. 			list ($schema, $table) = explode('.', strtolower($table));
    577. 

  577. 			$conditions[] = "LOWER(R.TABSCHEMA) = %s AND LOWER(R.TABNAME) = %s";
    578. 

  578. 			$params[] = $schema;
    579. 

  579. 			$params[] = $table;
    580. 

  580. 		}
    581. 

  581. 		$sql .= '((' . join(') OR( ', $conditions) . '))';
    582. 

  582. 		 
    583. 

  583. 		$sql .= "
    584. 

  584. 				) UNION (
    585. 

  585. 				SELECT
    586. 

  586. 					LOWER(RTRIM(I.TABSCHEMA)) AS \"SCHEMA\",
    587. 

  587. 					LOWER(I.TABNAME) AS \"TABLE\",
    588. 

  588. 					LOWER(I.INDNAME) AS CONSTRAINT_NAME,
    589. 

  589. 					CASE I.UNIQUERULE WHEN 'U' THEN 'unique' ELSE 'primary' END AS \"TYPE\",
    590. 

  590. 					LOWER(C.COLNAME) AS \"COLUMN\",
    591. 

  591. 					NULL AS FOREIGN_SCHEMA,
    592. 

  592. 					NULL AS FOREIGN_TABLE,
    593. 

  593. 					NULL AS FOREIGN_COLUMN,
    594. 

  594. 					NULL AS ON_DELETE,
    595. 

  595. 					NULL AS ON_UPDATE,
    596. 

  596. 					C.COLSEQ
    597. 

  597. 				FROM
    598. 

  598. 					SYSCAT.INDEXES AS I INNER JOIN
    599. 

  599. 					SYSCAT.INDEXCOLUSE AS C ON I.INDSCHEMA = C.INDSCHEMA AND I.INDNAME = C.INDNAME
    600. 

  600. 				WHERE
    601. 

  601. 					I.UNIQUERULE IN ('U', 'P') AND
    602. 

  602. 					";
    603. 

  603. 		
    604. 

  604. 		$conditions = array();
    605. 

  605. 		foreach ($tables as $table) {
    606. 

  606. 			if (strpos($table, '.') === FALSE) {
    607. 

  607. 				$table = $default_schema . '.' . $table;
    608. 

  608. 			}	
    609. 

  609. 			list ($schema, $table) = explode('.', strtolower($table));
    610. 

  610. 			$conditions[] = "LOWER(I.TABSCHEMA) = %s AND LOWER(I.TABNAME) = %s";
    611. 

  611. 			$params[] = $schema;
    612. 

  612. 			$params[] = $table;
    613. 

  613. 		}
    614. 

  614. 		$sql .= '((' . join(') OR( ', $conditions) . '))';
    615. 

  615. 		
    616. 

  616. 		$sql .= "
    617. 

  617. 				 )
    618. 

  618. 				 ORDER BY 4, 1, 2, 3, 11";
    619. 

  619. 		
    620. 

  620. 		$result = $this->database->query($sql, $params);
    621. 

  621. 		
    622. 

  622. 		$last_name  = '';
    623. 

  623. 		$last_table = '';
    624. 

  624. 		$last_type  = '';
    625. 

  625. 		foreach ($result as $row) {
    626. 

  626. 			
    627. 

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

  628. 				
    629. 

  629. 				if ($last_name) {
    630. 

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

  631. 						$keys[$last_table][$last_type][] = $temp;
    632. 

  632. 					} else {
    633. 

  633. 						$keys[$last_table][$last_type] = $temp;
    634. 

  634. 					}
    635. 

  635. 				}
    636. 

  636. 				
    637. 

  637. 				$temp = array();
    638. 

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

  639. 					
    640. 

  640. 					$temp['column']         = $row['column'];
    641. 

  641. 					$temp['foreign_table']  = $row['foreign_table'];
    642. 

  642. 					if ($row['foreign_schema'] != $default_schema) {
    643. 

  643. 						$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];
    644. 

  644. 					}
    645. 

  645. 					$temp['foreign_column'] = $row['foreign_column'];
    646. 

  646. 					$temp['on_delete']      = 'no_action';
    647. 

  647. 					$temp['on_update']      = 'no_action';
    648. 

  648. 					
    649. 

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

  650. 						$temp['on_delete'] = $row['on_delete'];
    651. 

  651. 					}
    652. 

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

  653. 						$temp['on_update'] = $row['on_update'];
    654. 

  654. 					}
    655. 

  655. 					
    656. 

  656. 				} else {
    657. 

  657. 					$temp[] = $row['column'];
    658. 

  658. 				}
    659. 

  659. 				
    660. 

  660. 				$last_table = $row['table'];
    661. 

  661. 				if ($row['schema'] != $default_schema) {
    662. 

  662. 					$last_table = $row['schema'] . '.' . $last_table;
    663. 

  663. 				}
    664. 

  664. 				$last_name  = $row['constraint_name'];
    665. 

  665. 				$last_type  = $row['type'];
    666. 

  666. 				
    667. 

  667. 			} else {
    668. 

  668. 				$temp[] = $row['column'];
    669. 

  669. 			}
    670. 

  670. 		}
    671. 

  671. 		
    672. 

  672. 		if (isset($temp)) {
    673. 

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

  674. 				$keys[$last_table][$last_type][] = $temp;
    675. 

  675. 			} else {
    676. 

  676. 				$keys[$last_table][$last_type] = $temp;
    677. 

  677. 			}
    678. 

  678. 		}
    679. 

  679. 		
    680. 

  680. 		return $keys;
    681. 

  681. 	}
    682. 

  682. 	
    683. 

  683. 	
    684. 

  684. 	/**
    685. 

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

  686. 	 * 
    687. 

  687. 	 * @return void
    688. 

  688. 	 */
    689. 

  689. 	private function fetchKeys()
    690. 

  690. 	{
    691. 

  691. 		if ($this->keys) {
    692. 

  692. 			return;	
    693. 

  693. 		}
    694. 

  694. 		
    695. 

  695. 		switch ($this->database->getType()) {
    696. 

  696. 			case 'db2':
    697. 

  697. 				$keys = $this->fetchDB2Keys();
    698. 

  698. 				break;
    699. 

  699. 			
    700. 

  700. 			case 'mssql':
    701. 

  701. 				$keys = $this->fetchMSSQLKeys();
    702. 

  702. 				break;
    703. 

  703. 				
    704. 

  704. 			case 'mysql':
    705. 

  705. 				$keys = $this->fetchMySQLKeys();
    706. 

  706. 				break;
    707. 

  707. 				
    708. 

  708. 			case 'oracle':
    709. 

  709. 				$keys = $this->fetchOracleKeys();
    710. 

  710. 				break;
    711. 

  711. 			
    712. 

  712. 			case 'postgresql':
    713. 

  713. 				$keys = $this->fetchPostgreSQLKeys();
    714. 

  714. 				break;
    715. 

  715. 			
    716. 

  716. 			case 'sqlite':
    717. 

  717. 				$keys = $this->fetchSQLiteKeys();
    718. 

  718. 				break;
    719. 

  719. 		}
    720. 

  720. 		
    721. 

  721. 		$this->keys = $keys;
    722. 

  722. 		if ($this->cache) {
    723. 

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

  724. 		}
    725. 

  725. 	}
    726. 

  726. 	
    727. 

  727. 	
    728. 

  728. 	/**
    729. 

  729. 	 * Gets the column info from a MSSQL database
    730. 

  730. 	 * 
    731. 

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

  732. 	 * @return array  The column info for the table specified - see ::getColumnInfo() for details
    733. 

  733. 	 */
    734. 

  734. 	private function fetchMSSQLColumnInfo($table)
    735. 

  735. 	{
    736. 

  736. 		$column_info = array();
    737. 

  737. 		
    738. 

  738. 		$schema = 'dbo';
    739. 

  739. 		if (strpos($table, '.') !== FALSE) {
    740. 

  740. 			list ($schema, $table) = explode('.', $table);
    741. 

  741. 		}
    742. 

  742. 		
    743. 

  743. 		$data_type_mapping = array(
    744. 

  744. 			'bit'               => 'boolean',
    745. 

  745. 			'tinyint'           => 'integer',
    746. 

  746. 			'smallint'          => 'integer',
    747. 

  747. 			'int'               => 'integer',
    748. 

  748. 			'bigint'            => 'integer',
    749. 

  749. 			'timestamp'         => 'integer',
    750. 

  750. 			'datetime'          => 'timestamp',
    751. 

  751. 			'smalldatetime'     => 'timestamp',
    752. 

  752. 			'datetime2'         => 'timestamp',
    753. 

  753. 			'date'              => 'date',
    754. 

  754. 			'time'              => 'time',
    755. 

  755. 			'varchar'           => 'varchar',
    756. 

  756. 			'nvarchar'          => 'varchar',
    757. 

  757. 			'uniqueidentifier'  => 'varchar',
    758. 

  758. 			'char'              => 'char',
    759. 

  759. 			'nchar'             => 'char',
    760. 

  760. 			'real'              => 'float',
    761. 

  761. 			'float'             => 'float',
    762. 

  762. 			'money'             => 'float',
    763. 

  763. 			'smallmoney'        => 'float',
    764. 

  764. 			'decimal'           => 'float',
    765. 

  765. 			'numeric'           => 'float',
    766. 

  766. 			'binary'            => 'blob',
    767. 

  767. 			'varbinary'         => 'blob',
    768. 

  768. 			'image'             => 'blob',
    769. 

  769. 			'text'              => 'text',
    770. 

  770. 			'ntext'             => 'text',
    771. 

  771. 			'xml'               => 'text'
    772. 

  772. 		);
    773. 

  773. 		
    774. 

  774. 		$max_min_values = array(
    775. 

  775. 			'tinyint'    => array('min' => new fNumber(0),                       'max' => new fNumber(255)),
    776. 

  776. 			'smallint'   => array('min' => new fNumber(-32768),                  'max' => new fNumber(32767)),
    777. 

  777. 			'int'        => array('min' => new fNumber(-2147483648),             'max' => new fNumber(2147483647)),
    778. 

  778. 			'bigint'     => array('min' => new fNumber('-9223372036854775808'),  'max' => new fNumber('9223372036854775807')),
    779. 

  779. 			'smallmoney' => array('min' => new fNumber('-214748.3648'),          'max' => new fNumber('214748.3647')),
    780. 

  780. 			'money'      => array('min' => new fNumber('-922337203685477.5808'), 'max' => new fNumber('922337203685477.5807'))
    781. 

  781. 		);
    782. 

  782. 		
    783. 

  783. 		// Get the column info
    784. 

  784. 		$sql = "SELECT
    785. 

  785. 					LOWER(c.column_name)       AS 'column',
    786. 

  786. 					c.data_type                AS 'type',
    787. 

  787. 					c.is_nullable              AS nullable,
    788. 

  788. 					c.column_default           AS 'default',
    789. 

  789. 					c.character_maximum_length AS max_length,
    790. 

  790. 					c.numeric_precision        AS precision,
    791. 

  791. 					c.numeric_scale            AS decimal_places,
    792. 

  792. 					CASE
    793. 

  793. 						WHEN
    794. 

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

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

  796. 						THEN '1'
    797. 

  797. 						ELSE '0'
    798. 

  798. 					END AS auto_increment,
    799. 

  799. 					cc.check_clause AS 'constraint',
    800. 

  800. 					CAST(ex.value AS VARCHAR(7500)) AS 'comment'
    801. 

  801. 				FROM
    802. 

  802. 					INFORMATION_SCHEMA.COLUMNS AS c LEFT JOIN
    803. 

  803. 					INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON
    804. 

  804. 						c.column_name = ccu.column_name AND
    805. 

  805. 						c.table_name = ccu.table_name AND
    806. 

  806. 						c.table_catalog = ccu.table_catalog LEFT JOIN
    807. 

  807. 					INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON
    808. 

  808. 						ccu.constraint_name = cc.constraint_name AND
    809. 

  809. 						ccu.constraint_catalog = cc.constraint_catalog";
    810. 

  810. 		
    811. 

  811. 		if (version_compare($this->database->getVersion(), 9, '<')) {
    812. 

  812. 			$sql .= " LEFT JOIN sysproperties AS ex ON ex.id = OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)) AND ex.smallid = COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'ColumnId') AND ex.name = 'MS_Description' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMsShipped') = 0 ";
    813. 

  813. 		} else {
    814. 

  814. 			$sql .= " LEFT JOIN SYS.EXTENDED_PROPERTIES AS ex ON ex.major_id = OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)) AND ex.minor_id = COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'ColumnId') AND ex.name = 'MS_Description' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMsShipped') = 0 ";
    815. 

  815. 		}
    816. 

  816. 		
    817. 

  817. 		$sql .= "
    818. 

  818. 					WHERE
    819. 

  819. 						LOWER(c.table_name) = %s AND
    820. 

  820. 						LOWER(c.table_schema) = %s AND
    821. 

  821. 						c.table_catalog = DB_NAME()";
    822. 

  822. 		
    823. 

  823. 		$result = $this->database->query($sql, strtolower($table), strtolower($schema));
    824. 

  824. 		
    825. 

  825. 		foreach ($result as $row) {
    826. 

  826. 			
    827. 

  827. 			$info = array();
    828. 

  828. 			
    829. 

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

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

  831. 					if (isset($max_min_values[$data_type])) {
    832. 

  832. 						$info['min_value'] = $max_min_values[$data_type]['min'];
    833. 

  833. 						$info['max_value'] = $max_min_values[$data_type]['max'];
    834. 

  834. 					}
    835. 

  835. 					$info['type'] = $mapped_data_type;
    836. 

  836. 					break;
    837. 

  837. 				}
    838. 

  838. 			}
    839. 

  839. 			
    840. 

  840. 			// Handle decimal places and min/max for numeric/decimals
    841. 

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

  842. 				$info['decimal_places'] = $row['decimal_places'];
    843. 

  843. 				$before_digits = str_pad('', $row['precision'] - $row['decimal_places'], '9');
    844. 

  844. 				$after_digits  = str_pad('', $row['decimal_places'], '9');
    845. 

  845. 				$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    846. 

  846. 				$info['min_value'] = new fNumber('-' . $max_min);
    847. 

  847. 				$info['max_value'] = new fNumber($max_min);
    848. 

  848. 			}
    849. 

  849. 			
    850. 

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

  851. 				$info['type'] = $row['type'];
    852. 

  852. 			}
    853. 

  853. 			
    854. 

  854. 			// Handle decimal places for money/smallmoney
    855. 

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

  856. 				$info['decimal_places'] = 2;
    857. 

  857. 			}
    858. 

  858. 			
    859. 

  859. 			// Handle the special data for varchar columns
    860. 

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

  861. 				if ($row['type'] == 'uniqueidentifier') {
    862. 

  862. 					$row['max_length'] = 32;
    863. 

  863. 				} elseif ($row['max_length'] == -1) {
    864. 

  864. 					$row['max_length'] = $row['type'] == 'nvarchar' ? 1073741823 : 2147483647;
    865. 

  865. 				}
    866. 

  866. 				$info['max_length'] = $row['max_length'];
    867. 

  867. 			}
    868. 

  868. 			
    869. 

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

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

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

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

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

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

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

  876. 					}
    877. 

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

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

  879. 				}
    880. 

  880. 			}
    881. 

  881. 			
    882. 

  882. 			// Handle auto increment
    883. 

  883. 			if ($row['auto_increment']) {
    884. 

  884. 				$info['auto_increment'] = TRUE;
    885. 

  885. 			}
    886. 

  886. 			
    887. 

  887. 			// Handle default values
    888. 

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

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

  890. 					$info['default'] = 'CURRENT_TIMESTAMP';
    891. 

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

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

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

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

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

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

  897. 				} else {
    898. 

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

  899. 				}
    900. 

  900. 			}
    901. 

  901. 			
    902. 

  902. 			// Handle not null
    903. 

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

  904. 			
    905. 

  905. 			$info['comment'] = $row['comment'];
    906. 

  906. 			
    907. 

  907. 			$column_info[$row['column']] = $info;
    908. 

  908. 		}
    909. 

  909. 		
    910. 

  910. 		return $column_info;
    911. 

  911. 	}
    912. 

  912. 	
    913. 

  913. 	
    914. 

  914. 	/**
    915. 

  915. 	 * Fetches the key info for an MSSQL database
    916. 

  916. 	 * 
    917. 

  917. 	 * @return array  The key info arrays for every table in the database - see ::getKeys() for details
    918. 

  918. 	 */
    919. 

  919. 	private function fetchMSSQLKeys()
    920. 

  920. 	{
    921. 

  921. 		$keys = array();
    922. 

  922. 		
    923. 

  923. 		$tables   = $this->getTables();
    924. 

  924. 		foreach ($tables as $table) {
    925. 

  925. 			$keys[$table] = array();
    926. 

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

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

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

  929. 		}
    930. 

  930. 		
    931. 

  931. 		$sql  = "SELECT
    932. 

  932. 					LOWER(c.table_schema) AS \"schema\",
    933. 

  933. 					LOWER(c.table_name) AS \"table\",
    934. 

  934. 					kcu.constraint_name AS constraint_name,
    935. 

  935. 					CASE c.constraint_type
    936. 

  936. 						WHEN 'PRIMARY KEY' THEN 'primary'
    937. 

  937. 						WHEN 'FOREIGN KEY' THEN 'foreign'
    938. 

  938. 						WHEN 'UNIQUE' THEN 'unique'
    939. 

  939. 					END AS 'type',
    940. 

  940. 					LOWER(kcu.column_name) AS 'column',
    941. 

  941. 					LOWER(ccu.table_schema) AS foreign_schema,
    942. 

  942. 					LOWER(ccu.table_name) AS foreign_table,
    943. 

  943. 					LOWER(ccu.column_name) AS foreign_column,
    944. 

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

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

  946. 				FROM
    947. 

  947. 					INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN
    948. 

  948. 					INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON
    949. 

  949. 						c.table_name = kcu.table_name AND
    950. 

  950. 						c.constraint_name = kcu.constraint_name LEFT JOIN
    951. 

  951. 					INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON
    952. 

  952. 						c.constraint_name = rc.constraint_name LEFT JOIN
    953. 

  953. 					INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON
    954. 

  954. 						ccu.constraint_name = rc.unique_constraint_name
    955. 

  955. 				WHERE
    956. 

  956. 					c.constraint_catalog = DB_NAME() AND
    957. 

  957. 					c.table_name != 'sysdiagrams'
    958. 

  958. 				ORDER BY
    959. 

  959. 					LOWER(c.table_schema),
    960. 

  960. 					LOWER(c.table_name),
    961. 

  961. 					c.constraint_type,
    962. 

  962. 					LOWER(kcu.constraint_name),
    963. 

  963. 					kcu.ordinal_position,
    964. 

  964. 					LOWER(kcu.column_name)";
    965. 

  965. 		
    966. 

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

  967. 		
    968. 

  968. 		$last_name  = '';
    969. 

  969. 		$last_table = '';
    970. 

  970. 		$last_type  = '';
    971. 

  971. 		foreach ($result as $row) {
    972. 

  972. 			
    973. 

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

  974. 				
    975. 

  975. 				if ($last_name) {
    976. 

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

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

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

  979. 						}
    980. 

  980. 						$keys[$last_table][$last_type][] = $temp;
    981. 

  981. 					} else {
    982. 

  982. 						$keys[$last_table][$last_type] = $temp;
    983. 

  983. 					}
    984. 

  984. 				}
    985. 

  985. 				
    986. 

  986. 				$temp = array();
    987. 

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

  988. 					
    989. 

  989. 					$temp['column']         = $row['column'];
    990. 

  990. 					$temp['foreign_table']  = $row['foreign_table'];
    991. 

  991. 					if ($row['foreign_schema'] != 'dbo') {
    992. 

  992. 						 $temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];	
    993. 

  993. 					}
    994. 

  994. 					$temp['foreign_column'] = $row['foreign_column'];
    995. 

  995. 					$temp['on_delete']      = 'no_action';
    996. 

  996. 					$temp['on_update']      = 'no_action';
    997. 

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

  998. 						$temp['on_delete'] = $row['on_delete'];
    999. 

  999. 					}
    1000. 

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

  1001. 						$temp['on_update'] = $row['on_update'];
    1002. 

  1002. 					}
    1003. 

  1003. 					
    1004. 

  1004. 				} else {
    1005. 

  1005. 					$temp[] = $row['column'];
    1006. 

  1006. 				}
    1007. 

  1007. 				
    1008. 

  1008. 				$last_table = $row['table'];
    1009. 

  1009. 				if ($row['schema'] != 'dbo') {
    1010. 

  1010. 					$last_table = $row['schema'] . '.' . $last_table;	
    1011. 

  1011. 				}
    1012. 

  1012. 				$last_name  = $row['constraint_name'];
    1013. 

  1013. 				$last_type  = $row['type'];
    1014. 

  1014. 				
    1015. 

  1015. 			} else {
    1016. 

  1016. 				$temp[] = $row['column'];
    1017. 

  1017. 			}
    1018. 

  1018. 		}
    1019. 

  1019. 		
    1020. 

  1020. 		if (isset($temp)) {
    1021. 

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

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

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

  1024. 				}
    1025. 

  1025. 				$keys[$last_table][$last_type][] = $temp;
    1026. 

  1026. 			} else {
    1027. 

  1027. 				$keys[$last_table][$last_type] = $temp;
    1028. 

  1028. 			}
    1029. 

  1029. 		}
    1030. 

  1030. 		
    1031. 

  1031. 		return $keys;
    1032. 

  1032. 	}
    1033. 

  1033. 	
    1034. 

  1034. 	
    1035. 

  1035. 	/**
    1036. 

  1036. 	 * Gets the column info from a MySQL database
    1037. 

  1037. 	 * 
    1038. 

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

  1039. 	 * @return array  The column info for the table specified - see ::getColumnInfo() for details
    1040. 

  1040. 	 */
    1041. 

  1041. 	private function fetchMySQLColumnInfo($table)
    1042. 

  1042. 	{
    1043. 

  1043. 		$data_type_mapping = array(
    1044. 

  1044. 			'tinyint'			=> 'integer',
    1045. 

  1045. 			'smallint'			=> 'integer',
    1046. 

  1046. 			'mediumint'         => 'integer',
    1047. 

  1047. 			'int'				=> 'integer',
    1048. 

  1048. 			'bigint'			=> 'integer',
    1049. 

  1049. 			'datetime'			=> 'timestamp',
    1050. 

  1050. 			'timestamp'			=> 'timestamp',
    1051. 

  1051. 			'date'				=> 'date',
    1052. 

  1052. 			'time'				=> 'time',
    1053. 

  1053. 			'enum'				=> 'varchar',
    1054. 

  1054. 			'set'               => 'varchar',
    1055. 

  1055. 			'varchar'			=> 'varchar',
    1056. 

  1056. 			'char'				=> 'char',
    1057. 

  1057. 			'float'				=> 'float',
    1058. 

  1058. 			'double'			=> 'float',
    1059. 

  1059. 			'decimal'			=> 'float',
    1060. 

  1060. 			'binary'            => 'blob',
    1061. 

  1061. 			'varbinary'         => 'blob',
    1062. 

  1062. 			'tinyblob'			=> 'blob',
    1063. 

  1063. 			'blob'				=> 'blob',
    1064. 

  1064. 			'mediumblob'		=> 'blob',
    1065. 

  1065. 			'longblob'			=> 'blob',
    1066. 

  1066. 			'tinytext'			=> 'text',
    1067. 

  1067. 			'text'				=> 'text',
    1068. 

  1068. 			'mediumtext'		=> 'text',
    1069. 

  1069. 			'longtext'			=> 'text',
    1070. 

  1070. 			'geometry'          => 'blob',
    1071. 

  1071. 			'point'             => 'blob',
    1072. 

  1072. 			'linestring'        => 'blob',
    1073. 

  1073. 			'polygon'           => 'blob'
    1074. 

  1074. 		);
    1075. 

  1075. 		
    1076. 

  1076. 		$max_min_values = array(
    1077. 

  1077. 			'tinyint'             => array('min' => new fNumber(-128),                    'max' => new fNumber(127)),
    1078. 

  1078. 			'unsigned tinyint'    => array('min' => new fNumber(0),                       'max' => new fNumber(255)),
    1079. 

  1079. 			'smallint'            => array('min' => new fNumber(-32768),                  'max' => new fNumber(32767)),
    1080. 

  1080. 			'unsigned smallint'   => array('min' => new fNumber(0),                       'max' => new fNumber(65535)),
    1081. 

  1081. 			'mediumint'           => array('min' => new fNumber(-8388608),                'max' => new fNumber(8388607)),
    1082. 

  1082. 			'unsigned mediumint'  => array('min' => new fNumber(0),                       'max' => new fNumber(16777215)),
    1083. 

  1083. 			'int'                 => array('min' => new fNumber(-2147483648),             'max' => new fNumber(2147483647)),
    1084. 

  1084. 			'unsigned int'        => array('min' => new fNumber(0),                       'max' => new fNumber('4294967295')),
    1085. 

  1085. 			'bigint'              => array('min' => new fNumber('-9223372036854775808'),  'max' => new fNumber('9223372036854775807')),
    1086. 

  1086. 			'unsigned bigint'     => array('min' => new fNumber(0),                       'max' => new fNumber('18446744073709551615'))
    1087. 

  1087. 		);
    1088. 

  1088. 		
    1089. 

  1089. 		$column_info = array();
    1090. 

  1090. 		
    1091. 

  1091. 		$result     = $this->database->query('SHOW CREATE TABLE %r', $table);
    1092. 

  1092. 		
    1093. 

  1093. 		try {
    1094. 

  1094. 			$row        = $result->fetchRow();
    1095. 

  1095. 			$create_sql = $row['Create Table'];
    1096. 

  1096. 		} catch (fNoRowsException $e) {
    1097. 

  1097. 			return array();
    1098. 

  1098. 		}
    1099. 

  1099. 		
    1100. 

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

  1101. 		
    1102. 

  1102. 		foreach ($matches as $match) {
    1103. 

  1103. 			
    1104. 

  1104. 			$info = array();
    1105. 

  1105. 			
    1106. 

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

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

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

  1109. 						$mapped_data_type = 'boolean';
    1110. 

  1110. 					
    1111. 

  1111. 					} elseif (preg_match('#((?:unsigned )?(?:tiny|small|medium|big)?int)#', (isset($match[4]) ? $match[4] . ' ' : '') . $data_type, $int_match)) {
    1112. 

  1112. 						if (isset($max_min_values[$int_match[1]])) {
    1113. 

  1113. 							$info['min_value'] = $max_min_values[$int_match[1]]['min'];
    1114. 

  1114. 							$info['max_value'] = $max_min_values[$int_match[1]]['max'];	
    1115. 

  1115. 						}
    1116. 

  1116. 					}
    1117. 

  1117. 					
    1118. 

  1118. 					$info['type'] = $mapped_data_type;
    1119. 

  1119. 					break;
    1120. 

  1120. 				}
    1121. 

  1121. 			}
    1122. 

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

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

  1124. 			}
    1125. 

  1125. 			
    1126. 

  1126. 			switch ($match[2]) {
    1127. 

  1127. 				case 'tinyblob':
    1128. 

  1128. 				case 'tinytext':
    1129. 

  1129. 					$info['max_length'] = 255;
    1130. 

  1130. 					break;
    1131. 

  1131. 				
    1132. 

  1132. 				case 'blob':
    1133. 

  1133. 				case 'text':
    1134. 

  1134. 					$info['max_length'] = 65535;
    1135. 

  1135. 					break;
    1136. 

  1136. 				
    1137. 

  1137. 				case 'mediumblob':
    1138. 

  1138. 				case 'mediumtext':
    1139. 

  1139. 					$info['max_length'] = 16777215;
    1140. 

  1140. 					break;
    1141. 

  1141. 				
    1142. 

  1142. 				case 'longblob':
    1143. 

  1143. 				case 'longtext':
    1144. 

  1144. 					$info['max_length'] = 4294967295;
    1145. 

  1145. 					break;
    1146. 

  1146. 			}
    1147. 

  1147. 		
    1148. 

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

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

  1150. 				$match[3] = 0;
    1151. 

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

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

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

  1154. 					}
    1155. 

  1155. 				}
    1156. 

  1156. 			}
    1157. 

  1157. 			
    1158. 

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

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

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

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

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

  1163. 			}
    1164. 

  1164. 			
    1165. 

  1165. 			// Type specific information
    1166. 

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

  1167. 				$info['max_length'] = $match[3];
    1168. 

  1168. 			}
    1169. 

  1169. 			
    1170. 

  1170. 			// Grab the number of decimal places
    1171. 

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

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

  1173. 					$info['decimal_places'] = $data_type_info[2];
    1174. 

  1174. 					$before_digits = str_pad('', $data_type_info[1] - $info['decimal_places'], '9');
    1175. 

  1175. 					$after_digits  = str_pad('', $info['decimal_places'], '9');
    1176. 

  1176. 					$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    1177. 

  1177. 					$info['min_value'] = new fNumber('-' . $max_min);
    1178. 

  1178. 					$info['max_value'] = new fNumber($max_min);
    1179. 

  1179. 				}
    1180. 

  1180. 			}
    1181. 

  1181. 			
    1182. 

  1182. 			// Not null
    1183. 

  1183. 			$info['not_null'] = (!empty($match[5])) ? TRUE : FALSE;
    1184. 

  1184. 		
    1185. 

  1185. 			// Default values
    1186. 

  1186. 			if (!empty($match[6]) && $match[6] != 'NULL') {
    1187. 

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

  1188. 			}
    1189. 

  1189. 			
    1190. 

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

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

  1192. 			}
    1193. 

  1193. 		
    1194. 

  1194. 			// Auto increment fields
    1195. 

  1195. 			if (!empty($match[7])) {
    1196. 

  1196. 				$info['auto_increment'] = TRUE;
    1197. 

  1197. 			}
    1198. 

  1198. 			
    1199. 

  1199. 			// Column comments
    1200. 

  1200. 			if (!empty($match[8])) {
    1201. 

  1201. 				$info['comment'] = str_replace("''", "'", substr($match[8], 10, -1));
    1202. 

  1202. 			}
    1203. 

  1203. 		
    1204. 

  1204. 			$column_info[strtolower($match[1])] = $info;
    1205. 

  1205. 		}
    1206. 

  1206. 		
    1207. 

  1207. 		return $column_info;
    1208. 

  1208. 	}
    1209. 

  1209. 	
    1210. 

  1210. 	
    1211. 

  1211. 	/**
    1212. 

  1212. 	 * Fetches the keys for a MySQL database
    1213. 

  1213. 	 * 
    1214. 

  1214. 	 * @return array  The keys arrays for every table in the database - see ::getKeys() for details
    1215. 

  1215. 	 */
    1216. 

  1216. 	private function fetchMySQLKeys()
    1217. 

  1217. 	{
    1218. 

  1218. 		$tables   = $this->getTables();
    1219. 

  1219. 		$keys = array();
    1220. 

  1220. 		
    1221. 

  1221. 		foreach ($tables as $table) {
    1222. 

  1222. 			
    1223. 

  1223. 			$keys[$table] = array();
    1224. 

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

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

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

  1227. 			
    1228. 

  1228. 			$result = $this->database->query('SHOW CREATE TABLE %r', $table);
    1229. 

  1229. 			$row    = $result->fetchRow();
    1230. 

  1230. 			
    1231. 

  1231. 			// Primary keys
    1232. 

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

  1233. 			if (!empty($matches)) {
    1234. 

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

  1235. 			}
    1236. 

  1236. 			
    1237. 

  1237. 			// Unique keys
    1238. 

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

  1239. 			foreach ($matches as $match) {
    1240. 

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

  1241. 			}
    1242. 

  1242. 			
    1243. 

  1243. 			// Foreign keys
    1244. 

  1244. 			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);
    1245. 

  1245. 			foreach ($matches as $match) {
    1246. 

  1246. 				$temp = array(
    1247. 

  1247. 					'column'         => strtolower($match[1]),
    1248. 

  1248. 					'foreign_table'  => strtolower($match[2]),
    1249. 

  1249. 					'foreign_column' => strtolower($match[3]),
    1250. 

  1250. 					'on_delete'      => 'no_action',
    1251. 

  1251. 					'on_update'      => 'no_action'
    1252. 

  1252. 				);
    1253. 

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

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

  1255. 				}
    1256. 

  1256. 				if (!empty($match[5])) {
    1257. 

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

  1258. 				}
    1259. 

  1259. 				$keys[$table]['foreign'][] = $temp;
    1260. 

  1260. 			}
    1261. 

  1261. 		}
    1262. 

  1262. 		
    1263. 

  1263. 		return $keys;
    1264. 

  1264. 	}
    1265. 

  1265. 	
    1266. 

  1266. 	
    1267. 

  1267. 	/**
    1268. 

  1268. 	 * Gets the column info from an Oracle database
    1269. 

  1269. 	 * 
    1270. 

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

  1271. 	 * @return array  The column info for the table specified - see ::getColumnInfo() for details
    1272. 

  1272. 	 */
    1273. 

  1273. 	private function fetchOracleColumnInfo($table)
    1274. 

  1274. 	{
    1275. 

  1275. 		$table = strtolower($table);
    1276. 

  1276. 		
    1277. 

  1277. 		$schema = strtolower($this->database->getUsername());
    1278. 

  1278. 		if (strpos($table, '.') !== FALSE) {
    1279. 

  1279. 			list ($schema, $table) = explode('.', $table);
    1280. 

  1280. 		}
    1281. 

  1281. 		
    1282. 

  1282. 		$column_info = array();
    1283. 

  1283. 		
    1284. 

  1284. 		$data_type_mapping = array(
    1285. 

  1285. 			'boolean'			=> 'boolean',
    1286. 

  1286. 			'number'            => 'integer',
    1287. 

  1287. 			'integer'			=> 'integer',
    1288. 

  1288. 			'timestamp'			=> 'timestamp',
    1289. 

  1289. 			'date'				=> 'date',
    1290. 

  1290. 			'varchar2'          => 'varchar',
    1291. 

  1291. 			'nvarchar2'			=> 'varchar',
    1292. 

  1292. 			'char'              => 'char',
    1293. 

  1293. 			'nchar'             => 'char',
    1294. 

  1294. 			'float'				=> 'float',
    1295. 

  1295. 			'binary_float'      => 'float',
    1296. 

  1296. 			'binary_double'     => 'float',
    1297. 

  1297. 			'blob'				=> 'blob',
    1298. 

  1298. 			'bfile'             => 'varchar',
    1299. 

  1299. 			'clob'				=> 'text',
    1300. 

  1300. 			'nclob'             => 'text'
    1301. 

  1301. 		);
    1302. 

  1302. 		
    1303. 

  1303. 		$sql = "SELECT
    1304. 

  1304. 					LOWER(ATC.COLUMN_NAME) COLUMN_NAME,
    1305. 

  1305. 					CASE
    1306. 

  1306. 						WHEN
    1307. 

  1307. 							ATC.DATA_TYPE = 'NUMBER' AND
    1308. 

  1308. 							ATC.DATA_PRECISION IS NULL AND
    1309. 

  1309. 							ATC.DATA_SCALE = 0
    1310. 

  1310. 						THEN
    1311. 

  1311. 							'integer'
    1312. 

  1312. 						WHEN
    1313. 

  1313. 							ATC.DATA_TYPE = 'NUMBER' AND
    1314. 

  1314. 							ATC.DATA_PRECISION = 1 AND
    1315. 

  1315. 							ATC.DATA_SCALE = 0
    1316. 

  1316. 						THEN
    1317. 

  1317. 							'boolean'
    1318. 

  1318. 						WHEN
    1319. 

  1319. 							ATC.DATA_TYPE = 'NUMBER' AND
    1320. 

  1320. 							ATC.DATA_PRECISION IS NOT NULL AND
    1321. 

  1321. 							ATC.DATA_SCALE != 0 AND
    1322. 

  1322. 							ATC.DATA_SCALE IS NOT NULL
    1323. 

  1323. 						THEN
    1324. 

  1324. 							'float'
    1325. 

  1325. 						ELSE
    1326. 

  1326. 							LOWER(ATC.DATA_TYPE)
    1327. 

  1327. 						END DATA_TYPE,
    1328. 

  1328. 					CASE
    1329. 

  1329. 						WHEN
    1330. 

  1330. 							ATC.CHAR_LENGTH <> 0
    1331. 

  1331. 						THEN
    1332. 

  1332. 							ATC.CHAR_LENGTH
    1333. 

  1333. 						WHEN
    1334. 

  1334. 							ATC.DATA_TYPE = 'NUMBER' AND
    1335. 

  1335. 							ATC.DATA_PRECISION != 1 AND
    1336. 

  1336. 							ATC.DATA_SCALE != 0	AND
    1337. 

  1337. 							ATC.DATA_PRECISION IS NOT NULL
    1338. 

  1338. 						THEN
    1339. 

  1339. 							ATC.DATA_SCALE
    1340. 

  1340. 						ELSE
    1341. 

  1341. 							NULL
    1342. 

  1342. 						END LENGTH,
    1343. 

  1343. 					ATC.DATA_PRECISION PRECISION,
    1344. 

  1344. 					ATC.NULLABLE,
    1345. 

  1345. 					ATC.DATA_DEFAULT,
    1346. 

  1346. 					AC.SEARCH_CONDITION CHECK_CONSTRAINT,
    1347. 

  1347. 					ACCM.COMMENTS
    1348. 

  1348. 				FROM
    1349. 

  1349. 					ALL_TAB_COLUMNS ATC LEFT JOIN
    1350. 

  1350. 					ALL_CONS_COLUMNS ACC ON
    1351. 

  1351. 						ATC.OWNER = ACC.OWNER AND
    1352. 

  1352. 						ATC.COLUMN_NAME = ACC.COLUMN_NAME AND
    1353. 

  1353. 						ATC.TABLE_NAME = ACC.TABLE_NAME AND
    1354. 

  1354. 						ACC.POSITION IS NULL LEFT JOIN
    1355. 

  1355. 					ALL_CONSTRAINTS AC ON
    1356. 

  1356. 						AC.OWNER = ACC.OWNER AND
    1357. 

  1357. 						AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME AND
    1358. 

  1358. 						AC.CONSTRAINT_TYPE = 'C' AND
    1359. 

  1359. 						AC.STATUS = 'ENABLED' LEFT JOIN
    1360. 

  1360. 					ALL_COL_COMMENTS ACCM ON
    1361. 

  1361. 						ATC.OWNER = ACCM.OWNER AND
    1362. 

  1362. 						ATC.COLUMN_NAME = ACCM.COLUMN_NAME AND
    1363. 

  1363. 						ATC.TABLE_NAME = ACCM.TABLE_NAME
    1364. 

  1364. 				WHERE
    1365. 

  1365. 					LOWER(ATC.TABLE_NAME) = %s AND
    1366. 

  1366. 					LOWER(ATC.OWNER) = %s
    1367. 

  1367. 				ORDER BY
    1368. 

  1368. 					ATC.TABLE_NAME ASC,
    1369. 

  1369. 					ATC.COLUMN_ID ASC";
    1370. 

  1370. 		
    1371. 

  1371. 		$result = $this->database->query($sql, $table, $schema);
    1372. 

  1372. 		
    1373. 

  1373. 		foreach ($result as $row) {
    1374. 

  1374. 			
    1375. 

  1375. 			$column = $row['column_name'];
    1376. 

  1376. 			
    1377. 

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

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

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

  1380. 			// the check constraint
    1381. 

  1381. 			$duplicate = FALSE;
    1382. 

  1382. 			
    1383. 

  1383. 			if (isset($column_info[$column])) {
    1384. 

  1384. 				$info = $column_info[$column];
    1385. 

  1385. 				$duplicate = TRUE;
    1386. 

  1386. 			} else {
    1387. 

  1387. 				$info = array();
    1388. 

  1388. 			}
    1389. 

  1389. 			
    1390. 

  1390. 			if (!$duplicate) {
    1391. 

  1391. 				// Get the column type
    1392. 

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

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

  1394. 						$info['type'] = $mapped_data_type;
    1395. 

  1395. 						break;
    1396. 

  1396. 					}
    1397. 

  1397. 				}
    1398. 

  1398. 				
    1399. 

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

  1400. 					$info['type'] = $row['data_type'];
    1401. 

  1401. 				}
    1402. 

  1402. 				
    1403. 

  1403. 				if (in_array($info['type'], array('blob', 'text'))) {
    1404. 

  1404. 					$info['max_length'] = 4294967295;
    1405. 

  1405. 				}
    1406. 

  1406. 				
    1407. 

  1407. 				if ($row['data_type'] == 'float' && $row['precision']) {
    1408. 

  1408. 					$row['length'] = (int) $row['length'];
    1409. 

  1409. 					$before_digits = str_pad('', $row['precision'] - $row['length'], '9');
    1410. 

  1410. 					$after_digits  = str_pad('', $row['length'], '9');
    1411. 

  1411. 					$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    1412. 

  1412. 					$info['min_value'] = new fNumber('-' . $max_min);
    1413. 

  1413. 					$info['max_value'] = new fNumber($max_min);	
    1414. 

  1414. 				}
    1415. 

  1415. 				
    1416. 

  1416. 				// Handle the length of decimal/numeric fields
    1417. 

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

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

  1419. 				}
    1420. 

  1420. 				
    1421. 

  1421. 				// Handle the special data for varchar fields
    1422. 

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

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

  1424. 				}
    1425. 

  1425. 			}
    1426. 

  1426. 			
    1427. 

  1427. 			// Handle check constraints that are just simple lists
    1428. 

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

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

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

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

  1432. 					}			
    1433. 

  1433. 				} elseif (preg_match('/^\s*"?' . preg_quote($column, '/') . '"?\s*=\s*\'((\'\'|[^\']+)*)\'(\s+OR\s+"?' . preg_quote($column, '/') . '"?\s*=\s*\'((\'\'|[^\']+)*)\')*\s*$/i', $row['check_constraint'], $match)) {
    1434. 

  1434. 					if (preg_match_all("/(?<!')'((''|[^']+)*)'/", $row['check_constraint'], $matches, PREG_PATTERN_ORDER)) {
    1435. 

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

  1436. 					}			
    1437. 

  1437. 				}
    1438. 

  1438. 			}
    1439. 

  1439. 			
    1440. 

  1440. 			if (!$duplicate) {
    1441. 

  1441. 				// Handle default values
    1442. 

  1442. 				if ($row['data_default'] !== NULL && trim($row['data_default']) != 'NULL') {
    1443. 

  1443. 					if (in_array($info['type'], array('date', 'time', 'timestamp')) && $row['data_default'][0] != "'") {
    1444. 

  1444. 						$info['default'] = trim(preg_replace('#^SYS#', 'CURRENT_', $row['data_default']));
    1445. 

  1445. 						
    1446. 

  1446. 					} elseif (in_array($info['type'], array('char', 'varchar', 'text', 'date', 'time', 'timestamp'))) {
    1447. 

  1447. 						$info['default'] = str_replace("''", "'", substr(trim($row['data_defaul…
    1448.

Large files files are truncated, but you can click here to view the full file