PageRenderTime 64ms CodeModel.GetById 16ms RepoModel.GetById 0ms app.codeStats 1ms

/base/lib/flourishlib/fSchema.php

https://bitbucket.org/thanhtungnguyenphp/monitos
PHP | 3027 lines | 2137 code | 308 blank | 582 comment | 290 complexity | 9e8d9a1ffd7884ec1a5c694a6dfbac72 MD5 | raw 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.0b46
    13. 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  59.  */
    60. 

  60. class fSchema
    61. 

  61. {
    62. 

  62. 	/**
    63. 

  63. 	 * The place to cache to
    64. 

  64. 	 * 
    65. 

  65. 	 * @var fCache
    66. 

  66. 	 */
    67. 

  67. 	private $cache = NULL;
    68. 

  68. 	
    69. 

  69. 	/**
    70. 

  70. 	 * The cache prefix to use for cache entries
    71. 

  71. 	 * 
    72. 

  72. 	 * @var string
    73. 

  73. 	 */
    74. 

  74. 	private $cache_prefix;
    75. 

  75. 	
    76. 

  76. 	/**
    77. 

  77. 	 * The cached column info
    78. 

  78. 	 * 
    79. 

  79. 	 * @var array
    80. 

  80. 	 */
    81. 

  81. 	private $column_info = array();
    82. 

  82. 	
    83. 

  83. 	/**
    84. 

  84. 	 * The column info to override
    85. 

  85. 	 * 
    86. 

  86. 	 * @var array
    87. 

  87. 	 */
    88. 

  88. 	private $column_info_override = array();
    89. 

  89. 	
    90. 

  90. 	/**
    91. 

  91. 	 * A reference to an instance of the fDatabase class
    92. 

  92. 	 * 
    93. 

  93. 	 * @var fDatabase
    94. 

  94. 	 */
    95. 

  95. 	private $database = NULL;
    96. 

  96. 	
    97. 

  97. 	/**
    98. 

  98. 	 * The databases on the current database server
    99. 

  99. 	 * 
    100. 

  100. 	 * @var array
    101. 

  101. 	 */
    102. 

  102. 	private $databases = NULL;
    103. 

  103. 	
    104. 

  104. 	/**
    105. 

  105. 	 * The cached key info
    106. 

  106. 	 * 
    107. 

  107. 	 * @var array
    108. 

  108. 	 */
    109. 

  109. 	private $keys = array();
    110. 

  110. 	
    111. 

  111. 	/**
    112. 

  112. 	 * The key info to override
    113. 

  113. 	 * 
    114. 

  114. 	 * @var array
    115. 

  115. 	 */
    116. 

  116. 	private $keys_override = array();
    117. 

  117. 	
    118. 

  118. 	/**
    119. 

  119. 	 * The merged column info
    120. 

  120. 	 * 
    121. 

  121. 	 * @var array
    122. 

  122. 	 */
    123. 

  123. 	private $merged_column_info = array();
    124. 

  124. 	
    125. 

  125. 	/**
    126. 

  126. 	 * The merged key info
    127. 

  127. 	 * 
    128. 

  128. 	 * @var array
    129. 

  129. 	 */
    130. 

  130. 	private $merged_keys = array();
    131. 

  131. 	
    132. 

  132. 	/**
    133. 

  133. 	 * The relationships in the database
    134. 

  134. 	 * 
    135. 

  135. 	 * @var array
    136. 

  136. 	 */
    137. 

  137. 	private $relationships = array();
    138. 

  138. 	
    139. 

  139. 	/**
    140. 

  140. 	 * The tables in the database
    141. 

  141. 	 * 
    142. 

  142. 	 * @var array
    143. 

  143. 	 */
    144. 

  144. 	private $tables = NULL;
    145. 

  145. 	
    146. 

  146. 	/**
    147. 

  147. 	 * The version of the database being queried
    148. 

  148. 	 * 
    149. 

  149. 	 * This is currently only used for MSSQL and MySQL
    150. 

  150. 	 * 
    151. 

  151. 	 * @var integer
    152. 

  152. 	 */
    153. 

  153. 	private $version = NULL;
    154. 

  154. 	
    155. 

  155. 	
    156. 

  156. 	/**
    157. 

  157. 	 * Sets the database
    158. 

  158. 	 * 
    159. 

  159. 	 * @param  fDatabase $database  The fDatabase instance
    160. 

  160. 	 * @return fSchema
    161. 

  161. 	 */
    162. 

  162. 	public function __construct($database)
    163. 

  163. 	{
    164. 

  164. 		$this->database = $database;
    165. 

  165. 	}
    166. 

  166. 	
    167. 

  167. 	
    168. 

  168. 	/**
    169. 

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

  170. 	 * 
    171. 

  171. 	 * @internal
    172. 

  172. 	 * 
    173. 

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

  174. 	 * @return callback  The callback for the method requested
    175. 

  175. 	 */
    176. 

  176. 	public function __get($method)
    177. 

  177. 	{
    178. 

  178. 		return array($this, $method);		
    179. 

  179. 	}
    180. 

  180. 	
    181. 

  181. 	
    182. 

  182. 	/**
    183. 

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

  184. 	 * 
    185. 

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

  186. 	 * @param  string $column  The column to check
    187. 

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

  188. 	 */
    189. 

  189. 	private function checkForSingleColumnUniqueKey($table, $column)
    190. 

  190. 	{        
    191. 

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

  192. 			if (array($column) == $key) {
    193. 

  193. 				return TRUE;
    194. 

  194. 			}
    195. 

  195. 		}
    196. 

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

  197. 			return TRUE;
    198. 

  198. 		}
    199. 

  199. 		return FALSE;
    200. 

  200. 	}
    201. 

  201. 	
    202. 

  202. 	
    203. 

  203. 	/**
    204. 

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

  205. 	 * 
    206. 

  206. 	 * @internal
    207. 

  207. 	 * 
    208. 

  208. 	 * @return void
    209. 

  209. 	 */
    210. 

  210. 	public function clearCache()
    211. 

  211. 	{
    212. 

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

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

  214. 		$this->keys               = array();
    215. 

  215. 		$this->merged_column_info = array();
    216. 

  216. 		$this->merged_keys        = array();
    217. 

  217. 		$this->relationships      = array();
    218. 

  218. 		$this->tables             = NULL;
    219. 

  219. 		if ($this->cache) {
    220. 

  220. 			$prefix = $this->makeCachePrefix();
    221. 

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

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

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

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

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

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

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

  228. 		}
    229. 

  229. 	}
    230. 

  230. 	
    231. 

  231. 	
    232. 

  232. 	/**
    233. 

  233. 	 * Sets the schema to be cached to the fCache object specified
    234. 

  234. 	 * 
    235. 

  235. 	 * @param  fCache $cache      The cache to cache to
    236. 

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

  237. 	 * @return void
    238. 

  238. 	 */
    239. 

  239. 	public function enableCaching($cache, $key_token=NULL)
    240. 

  240. 	{
    241. 

  241. 		$this->cache = $cache;
    242. 

  242. 		
    243. 

  243. 		if ($key_token !== NULL) {
    244. 

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

  245. 		}
    246. 

  246. 		$prefix = $this->makeCachePrefix();
    247. 

  247. 		
    248. 

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

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

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

  251. 		$this->version            = $this->cache->get($prefix . 'version',              NULL);
    252. 

  252. 		
    253. 

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

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

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

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

  257. 		}
    258. 

  258. 		
    259. 

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

  260. 	}
    261. 

  261. 	
    262. 

  262. 	
    263. 

  263. 	/**
    264. 

  264. 	 * Gets the column info from the database for later access
    265. 

  265. 	 * 
    266. 

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

  267. 	 * @return void
    268. 

  268. 	 */
    269. 

  269. 	private function fetchColumnInfo($table)
    270. 

  270. 	{
    271. 

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

  272. 			return;	
    273. 

  273. 		}
    274. 

  274. 		
    275. 

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

  276. 			case 'db2':
    277. 

  277. 				$column_info = $this->fetchDB2ColumnInfo($table);
    278. 

  278. 				break;
    279. 

  279. 			
    280. 

  280. 			case 'mssql':
    281. 

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

  282. 				break;
    283. 

  283. 			
    284. 

  284. 			case 'mysql':
    285. 

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

  286. 				break;
    287. 

  287. 				
    288. 

  288. 			case 'oracle':
    289. 

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

  290. 				break;
    291. 

  291. 			
    292. 

  292. 			case 'postgresql':
    293. 

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

  294. 				break;
    295. 

  295. 				
    296. 

  296. 			case 'sqlite':
    297. 

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

  298. 				break;
    299. 

  299. 		}
    300. 

  300. 			
    301. 

  301. 		if (!$column_info) {
    302. 

  302. 			return;	
    303. 

  303. 		}
    304. 

  304. 			
    305. 

  305. 		$this->column_info[$table] = $column_info;
    306. 

  306. 		if ($this->cache) {
    307. 

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

  308. 		}
    309. 

  309. 	}
    310. 

  310. 	
    311. 

  311. 	
    312. 

  312. 	/**
    313. 

  313. 	 * Gets the column info from a DB2 database
    314. 

  314. 	 * 
    315. 

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

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

  317. 	 */
    318. 

  318. 	private function fetchDB2ColumnInfo($table)
    319. 

  319. 	{
    320. 

  320. 		$column_info = array();
    321. 

  321. 		
    322. 

  322. 		$schema = strtolower($this->database->getUsername());
    323. 

  323. 		if (strpos($table, '.') !== FALSE) {
    324. 

  324. 			list ($schema, $table) = explode('.', $table);
    325. 

  325. 		}
    326. 

  326. 		
    327. 

  327. 		$data_type_mapping = array(
    328. 

  328. 			'smallint'          => 'integer',
    329. 

  329. 			'integer'           => 'integer',
    330. 

  330. 			'bigint'            => 'integer',
    331. 

  331. 			'timestamp'         => 'timestamp',
    332. 

  332. 			'date'              => 'date',
    333. 

  333. 			'time'              => 'time',
    334. 

  334. 			'varchar'           => 'varchar',
    335. 

  335. 			'long varchar'      => 'varchar',
    336. 

  336. 			'vargraphic'        => 'varchar',
    337. 

  337. 			'long vargraphic'   => 'varchar',
    338. 

  338. 			'character'         => 'char',
    339. 

  339. 			'graphic'           => 'char',
    340. 

  340. 			'real'              => 'float',
    341. 

  341. 			'decimal'           => 'float',
    342. 

  342. 			'numeric'           => 'float',
    343. 

  343. 			'blob'              => 'blob',
    344. 

  344. 			'clob'              => 'text',
    345. 

  345. 			'dbclob'            => 'text'
    346. 

  346. 		);
    347. 

  347. 		
    348. 

  348. 		$max_min_values = array(
    349. 

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

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

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

  352. 		);
    353. 

  353. 		
    354. 

  354. 		// Get the column info
    355. 

  355. 		$sql = "SELECT
    356. 

  356. 					LOWER(C.COLNAME) AS \"COLUMN\",
    357. 

  357. 					C.TYPENAME AS TYPE,
    358. 

  358. 					C.NULLS AS NULLABLE,
    359. 

  359. 					C.DEFAULT,
    360. 

  360. 					C.LENGTH AS MAX_LENGTH,
    361. 

  361. 					C.SCALE,
    362. 

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

  363. 					CH.TEXT AS \"CONSTRAINT\",
    364. 

  364. 					C.REMARKS AS \"COMMENT\"
    365. 

  365. 				FROM
    366. 

  366. 					SYSCAT.COLUMNS AS C LEFT JOIN
    367. 

  367. 					SYSCAT.COLCHECKS AS CC ON C.TABSCHEMA = CC.TABSCHEMA AND C.TABNAME = CC.TABNAME AND C.COLNAME = CC.COLNAME AND CC.USAGE = 'R' LEFT JOIN
    368. 

  368. 					SYSCAT.CHECKS AS CH ON C.TABSCHEMA = CH.TABSCHEMA AND C.TABNAME = CH.TABNAME AND CH.TYPE = 'C' AND CH.CONSTNAME = CC.CONSTNAME
    369. 

  369. 				WHERE
    370. 

  370. 					C.TABSCHEMA = %s AND
    371. 

  371. 					C.TABNAME = %s
    372. 

  372. 				ORDER BY
    373. 

  373. 					C.COLNO ASC";
    374. 

  374. 		
    375. 

  375. 		$result = $this->database->query($sql, strtoupper($schema), strtoupper($table));
    376. 

  376. 		
    377. 

  377. 		foreach ($result as $row) {
    378. 

  378. 			
    379. 

  379. 			$info = array();
    380. 

  380. 			
    381. 

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

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

  383. 					if (isset($max_min_values[$data_type])) {
    384. 

  384. 						$info['min_value'] = $max_min_values[$data_type]['min'];
    385. 

  385. 						$info['max_value'] = $max_min_values[$data_type]['max'];
    386. 

  386. 					}
    387. 

  387. 					$info['type'] = $mapped_data_type;
    388. 

  388. 					break;
    389. 

  389. 				}
    390. 

  390. 			}
    391. 

  391. 			
    392. 

  392. 			// Handle decimal places and min/max for numeric/decimals
    393. 

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

  394. 				$info['decimal_places'] = $row['scale'];
    395. 

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

  396. 				$after_digits  = str_pad('', $row['scale'], '9');
    397. 

  397. 				$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    398. 

  398. 				$info['min_value'] = new fNumber('-' . $max_min);
    399. 

  399. 				$info['max_value'] = new fNumber($max_min);
    400. 

  400. 			}
    401. 

  401. 			
    402. 

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

  403. 				$info['type'] = $row['type'];
    404. 

  404. 			}
    405. 

  405. 			
    406. 

  406. 			// Handle the special data for varchar columns
    407. 

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

  408. 				$info['max_length'] = $row['max_length'];
    409. 

  409. 			}
    410. 

  410. 			
    411. 

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

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

  413. 				if (is_resource($row['constraint'])) {
    414. 

  414. 					$row['constraint'] = stream_get_contents($row['constraint']);
    415. 

  415. 				}
    416. 

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

  417. 					$info['type'] = 'boolean';
    418. 

  418. 					$info['max_length'] = NULL;
    419. 

  419. 				}
    420. 

  420. 			}
    421. 

  421. 			
    422. 

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

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

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

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

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

  427. 					}			
    428. 

  428. 				}
    429. 

  429. 			}
    430. 

  430. 			
    431. 

  431. 			// Handle auto increment
    432. 

  432. 			if ($row['auto_increment']) {
    433. 

  433. 				$info['auto_increment'] = TRUE;
    434. 

  434. 			}
    435. 

  435. 			
    436. 

  436. 			// Handle default values
    437. 

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

  438. 				if ($row['default'] == 'NULL') {
    439. 

  439. 					$info['default'] = NULL;
    440. 

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

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

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

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

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

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

  446. 				} else {
    447. 

  447. 					$info['default'] = $row['default'];
    448. 

  448. 				}
    449. 

  449. 			}
    450. 

  450. 			
    451. 

  451. 			// Handle not null
    452. 

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

  453. 			
    454. 

  454. 			$info['comment'] = $row['comment'];
    455. 

  455. 			
    456. 

  456. 			$column_info[$row['column']] = $info;
    457. 

  457. 		}
    458. 

  458. 		
    459. 

  459. 		return $column_info;
    460. 

  460. 	}
    461. 

  461. 	
    462. 

  462. 	
    463. 

  463. 	/**
    464. 

  464. 	 * Fetches the key info for a DB2 database
    465. 

  465. 	 * 
    466. 

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

  467. 	 */
    468. 

  468. 	private function fetchDB2Keys()
    469. 

  469. 	{
    470. 

  470. 		$keys = array();
    471. 

  471. 		
    472. 

  472. 		$default_schema = strtolower($this->database->getUsername());
    473. 

  473. 		
    474. 

  474. 		$tables = $this->getTables();
    475. 

  475. 		foreach ($tables as $table) {
    476. 

  476. 			$keys[$table] = array();
    477. 

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

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

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

  480. 		}
    481. 

  481. 		
    482. 

  482. 		$params  = array();
    483. 

  483. 		
    484. 

  484. 		$sql  = "(SELECT
    485. 

  485. 					 LOWER(RTRIM(R.TABSCHEMA)) AS \"SCHEMA\",
    486. 

  486. 					 LOWER(R.TABNAME) AS \"TABLE\",
    487. 

  487. 					 R.CONSTNAME AS CONSTRAINT_NAME,
    488. 

  488. 					 'foreign' AS \"TYPE\",
    489. 

  489. 					 LOWER(K.COLNAME) AS \"COLUMN\",
    490. 

  490. 					 LOWER(RTRIM(R.REFTABSCHEMA)) AS FOREIGN_SCHEMA,
    491. 

  491. 					 LOWER(R.REFTABNAME) AS FOREIGN_TABLE,
    492. 

  492. 					 LOWER(FK.COLNAME) AS FOREIGN_COLUMN,
    493. 

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

  494. 					 CASE R.UPDATERULE WHEN 'A' THEN 'no_action' WHEN 'R' THEN 'restrict' END AS ON_UPDATE,
    495. 

  495. 					 K.COLSEQ
    496. 

  496. 				 FROM
    497. 

  497. 					 SYSCAT.REFERENCES AS R INNER JOIN 
    498. 

  498. 					 SYSCAT.KEYCOLUSE AS K ON R.CONSTNAME = K.CONSTNAME AND R.TABSCHEMA = K.TABSCHEMA AND R.TABNAME = K.TABNAME INNER JOIN
    499. 

  499. 					 SYSCAT.KEYCOLUSE AS FK ON R.REFKEYNAME = FK.CONSTNAME AND R.REFTABSCHEMA = FK.TABSCHEMA AND R.REFTABNAME = FK.TABNAME
    500. 

  500. 				 WHERE ";
    501. 

  501. 		
    502. 

  502. 		$conditions = array();
    503. 

  503. 		foreach ($tables as $table) {
    504. 

  504. 			if (strpos($table, '.') === FALSE) {
    505. 

  505. 				$table = $default_schema . '.' . $table;
    506. 

  506. 			}	
    507. 

  507. 			list ($schema, $table) = explode('.', strtoupper($table));
    508. 

  508. 			$conditions[] = "R.TABSCHEMA = %s AND R.TABNAME = %s";
    509. 

  509. 			$params[] = $schema;
    510. 

  510. 			$params[] = $table;
    511. 

  511. 		}
    512. 

  512. 		$sql .= '((' . join(') OR( ', $conditions) . '))';
    513. 

  513. 		 
    514. 

  514. 		$sql .= "
    515. 

  515. 				 ) UNION (
    516. 

  516. 				 SELECT
    517. 

  517. 					 LOWER(RTRIM(I.TABSCHEMA)) AS \"SCHEMA\",
    518. 

  518. 					 LOWER(I.TABNAME) AS \"TABLE\",
    519. 

  519. 					 LOWER(I.INDNAME) AS CONSTRAINT_NAME,
    520. 

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

  521. 					 LOWER(C.COLNAME) AS \"COLUMN\",
    522. 

  522. 					 NULL AS FOREIGN_SCHEMA,
    523. 

  523. 					 NULL AS FOREIGN_TABLE,
    524. 

  524. 					 NULL AS FOREIGN_COLUMN,
    525. 

  525. 					 NULL AS ON_DELETE,
    526. 

  526. 					 NULL AS ON_UPDATE,
    527. 

  527. 					 C.COLSEQ
    528. 

  528. 				 FROM
    529. 

  529. 					 SYSCAT.INDEXES AS I INNER JOIN
    530. 

  530. 					 SYSCAT.INDEXCOLUSE AS C ON I.INDSCHEMA = C.INDSCHEMA AND I.INDNAME = C.INDNAME
    531. 

  531. 				 WHERE
    532. 

  532. 					 I.UNIQUERULE IN ('U', 'P') AND
    533. 

  533. 					 ";
    534. 

  534. 		
    535. 

  535. 		$conditions = array();
    536. 

  536. 		foreach ($tables as $table) {
    537. 

  537. 			if (strpos($table, '.') === FALSE) {
    538. 

  538. 				$table = $default_schema . '.' . $table;
    539. 

  539. 			}	
    540. 

  540. 			list ($schema, $table) = explode('.', strtoupper($table));
    541. 

  541. 			$conditions[] = "I.TABSCHEMA = %s AND I.TABNAME = %s";
    542. 

  542. 			$params[] = $schema;
    543. 

  543. 			$params[] = $table;
    544. 

  544. 		}
    545. 

  545. 		$sql .= '((' . join(') OR( ', $conditions) . '))';
    546. 

  546. 		
    547. 

  547. 		$sql .= "
    548. 

  548. 				 )
    549. 

  549. 				 ORDER BY 4, 1, 2, 3, 11";
    550. 

  550. 		
    551. 

  551. 		$result = $this->database->query($sql, $params);
    552. 

  552. 		
    553. 

  553. 		$last_name  = '';
    554. 

  554. 		$last_table = '';
    555. 

  555. 		$last_type  = '';
    556. 

  556. 		foreach ($result as $row) {
    557. 

  557. 			
    558. 

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

  559. 				
    560. 

  560. 				if ($last_name) {
    561. 

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

  562. 						$keys[$last_table][$last_type][] = $temp;
    563. 

  563. 					} else {
    564. 

  564. 						$keys[$last_table][$last_type] = $temp;
    565. 

  565. 					}
    566. 

  566. 				}
    567. 

  567. 				
    568. 

  568. 				$temp = array();
    569. 

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

  570. 					
    571. 

  571. 					$temp['column']         = $row['column'];
    572. 

  572. 					$temp['foreign_table']  = $row['foreign_table'];
    573. 

  573. 					if ($row['foreign_schema'] != $default_schema) {
    574. 

  574. 						$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];
    575. 

  575. 					}
    576. 

  576. 					$temp['foreign_column'] = $row['foreign_column'];
    577. 

  577. 					$temp['on_delete']      = 'no_action';
    578. 

  578. 					$temp['on_update']      = 'no_action';
    579. 

  579. 					
    580. 

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

  581. 						$temp['on_delete'] = $row['on_delete'];
    582. 

  582. 					}
    583. 

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

  584. 						$temp['on_update'] = $row['on_update'];
    585. 

  585. 					}
    586. 

  586. 					
    587. 

  587. 				} else {
    588. 

  588. 					$temp[] = $row['column'];
    589. 

  589. 				}
    590. 

  590. 				
    591. 

  591. 				$last_table = $row['table'];
    592. 

  592. 				if ($row['schema'] != $default_schema) {
    593. 

  593. 					$last_table = $row['schema'] . '.' . $last_table;
    594. 

  594. 				}
    595. 

  595. 				$last_name  = $row['constraint_name'];
    596. 

  596. 				$last_type  = $row['type'];
    597. 

  597. 				
    598. 

  598. 			} else {
    599. 

  599. 				$temp[] = $row['column'];
    600. 

  600. 			}
    601. 

  601. 		}
    602. 

  602. 		
    603. 

  603. 		if (isset($temp)) {
    604. 

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

  605. 				$keys[$last_table][$last_type][] = $temp;
    606. 

  606. 			} else {
    607. 

  607. 				$keys[$last_table][$last_type] = $temp;
    608. 

  608. 			}
    609. 

  609. 		}
    610. 

  610. 		
    611. 

  611. 		return $keys;
    612. 

  612. 	}
    613. 

  613. 	
    614. 

  614. 	
    615. 

  615. 	/**
    616. 

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

  617. 	 * 
    618. 

  618. 	 * @return void
    619. 

  619. 	 */
    620. 

  620. 	private function fetchKeys()
    621. 

  621. 	{
    622. 

  622. 		if ($this->keys) {
    623. 

  623. 			return;	
    624. 

  624. 		}
    625. 

  625. 		
    626. 

  626. 		switch ($this->database->getType()) {
    627. 

  627. 			case 'db2':
    628. 

  628. 				$keys = $this->fetchDB2Keys();
    629. 

  629. 				break;
    630. 

  630. 			
    631. 

  631. 			case 'mssql':
    632. 

  632. 				$keys = $this->fetchMSSQLKeys();
    633. 

  633. 				break;
    634. 

  634. 				
    635. 

  635. 			case 'mysql':
    636. 

  636. 				$keys = $this->fetchMySQLKeys();
    637. 

  637. 				break;
    638. 

  638. 				
    639. 

  639. 			case 'oracle':
    640. 

  640. 				$keys = $this->fetchOracleKeys();
    641. 

  641. 				break;
    642. 

  642. 			
    643. 

  643. 			case 'postgresql':
    644. 

  644. 				$keys = $this->fetchPostgreSQLKeys();
    645. 

  645. 				break;
    646. 

  646. 			
    647. 

  647. 			case 'sqlite':
    648. 

  648. 				$keys = $this->fetchSQLiteKeys();
    649. 

  649. 				break;
    650. 

  650. 		}
    651. 

  651. 		
    652. 

  652. 		$this->keys = $keys;
    653. 

  653. 		if ($this->cache) {
    654. 

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

  655. 		}
    656. 

  656. 	}
    657. 

  657. 	
    658. 

  658. 	
    659. 

  659. 	/**
    660. 

  660. 	 * Gets the column info from a MSSQL database
    661. 

  661. 	 * 
    662. 

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

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

  664. 	 */
    665. 

  665. 	private function fetchMSSQLColumnInfo($table)
    666. 

  666. 	{
    667. 

  667. 		$column_info = array();
    668. 

  668. 		
    669. 

  669. 		$schema = 'dbo';
    670. 

  670. 		if (strpos($table, '.') !== FALSE) {
    671. 

  671. 			list ($schema, $table) = explode('.', $table);
    672. 

  672. 		}
    673. 

  673. 		
    674. 

  674. 		$data_type_mapping = array(
    675. 

  675. 			'bit'               => 'boolean',
    676. 

  676. 			'tinyint'           => 'integer',
    677. 

  677. 			'smallint'          => 'integer',
    678. 

  678. 			'int'               => 'integer',
    679. 

  679. 			'bigint'            => 'integer',
    680. 

  680. 			'timestamp'         => 'integer',
    681. 

  681. 			'datetime'          => 'timestamp',
    682. 

  682. 			'smalldatetime'     => 'timestamp',
    683. 

  683. 			'datetime2'         => 'timestamp',
    684. 

  684. 			'date'              => 'date',
    685. 

  685. 			'time'              => 'time',
    686. 

  686. 			'varchar'           => 'varchar',
    687. 

  687. 			'nvarchar'          => 'varchar',
    688. 

  688. 			'uniqueidentifier'  => 'varchar',
    689. 

  689. 			'char'              => 'char',
    690. 

  690. 			'nchar'             => 'char',
    691. 

  691. 			'real'              => 'float',
    692. 

  692. 			'float'             => 'float',
    693. 

  693. 			'money'             => 'float',
    694. 

  694. 			'smallmoney'        => 'float',
    695. 

  695. 			'decimal'           => 'float',
    696. 

  696. 			'numeric'           => 'float',
    697. 

  697. 			'binary'            => 'blob',
    698. 

  698. 			'varbinary'         => 'blob',
    699. 

  699. 			'image'             => 'blob',
    700. 

  700. 			'text'              => 'text',
    701. 

  701. 			'ntext'             => 'text',
    702. 

  702. 			'xml'               => 'text'
    703. 

  703. 		);
    704. 

  704. 		
    705. 

  705. 		$max_min_values = array(
    706. 

  706. 			'tinyint'    => array('min' => new fNumber(0),                       'max' => new fNumber(255)),
    707. 

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

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

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

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

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

  712. 		);
    713. 

  713. 		
    714. 

  714. 		if (!$this->version) {
    715. 

  715. 			$this->version = (int) $this->database->query("SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(20))")->fetchScalar();
    716. 

  716. 			if ($this->cache) {
    717. 

  717. 				$this->cache->set($this->makeCachePrefix() . 'version', $this->version);
    718. 

  718. 			}
    719. 

  719. 		}
    720. 

  720. 		
    721. 

  721. 		// Get the column info
    722. 

  722. 		$sql = "SELECT
    723. 

  723. 						c.column_name              AS 'column',
    724. 

  724. 						c.data_type                AS 'type',
    725. 

  725. 						c.is_nullable              AS nullable,
    726. 

  726. 						c.column_default           AS 'default',
    727. 

  727. 						c.character_maximum_length AS max_length,
    728. 

  728. 						c.numeric_precision        AS precision,
    729. 

  729. 						c.numeric_scale            AS decimal_places,
    730. 

  730. 						CASE
    731. 

  731. 							WHEN
    732. 

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

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

  734. 							THEN '1'
    735. 

  735. 							ELSE '0'
    736. 

  736. 						  END AS auto_increment,
    737. 

  737. 						cc.check_clause AS 'constraint',
    738. 

  738. 						CAST(ex.value AS VARCHAR(7500)) AS 'comment'
    739. 

  739. 					FROM
    740. 

  740. 						INFORMATION_SCHEMA.COLUMNS AS c LEFT JOIN
    741. 

  741. 						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
    742. 

  742. 						INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON ccu.constraint_name = cc.constraint_name AND ccu.constraint_catalog = cc.constraint_catalog";
    743. 

  743. 		
    744. 

  744. 		if ($this->version < 9) {
    745. 

  745. 			$sql .= " LEFT JOIN sysproperties AS ex ON ex.id = OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)) AND ex.smallid = c.ordinal_position AND ex.name = 'MS_Description' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMsShipped') = 0 ";
    746. 

  746. 		} else {
    747. 

  747. 			$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 = c.ordinal_position AND ex.name = 'MS_Description' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMsShipped') = 0 ";
    748. 

  748. 		}
    749. 

  749. 		
    750. 

  750. 		$sql .= "
    751. 

  751. 					WHERE
    752. 

  752. 						c.table_name = %s AND
    753. 

  753. 						c.table_schema = %s AND
    754. 

  754. 						c.table_catalog = DB_NAME()";
    755. 

  755. 		
    756. 

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

  757. 		
    758. 

  758. 		foreach ($result as $row) {
    759. 

  759. 			
    760. 

  760. 			$info = array();
    761. 

  761. 			
    762. 

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

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

  764. 					if (isset($max_min_values[$data_type])) {
    765. 

  765. 						$info['min_value'] = $max_min_values[$data_type]['min'];
    766. 

  766. 						$info['max_value'] = $max_min_values[$data_type]['max'];
    767. 

  767. 					}
    768. 

  768. 					$info['type'] = $mapped_data_type;
    769. 

  769. 					break;
    770. 

  770. 				}
    771. 

  771. 			}
    772. 

  772. 			
    773. 

  773. 			// Handle decimal places and min/max for numeric/decimals
    774. 

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

  775. 				$info['decimal_places'] = $row['decimal_places'];
    776. 

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

  777. 				$after_digits  = str_pad('', $row['decimal_places'], '9');
    778. 

  778. 				$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    779. 

  779. 				$info['min_value'] = new fNumber('-' . $max_min);
    780. 

  780. 				$info['max_value'] = new fNumber($max_min);
    781. 

  781. 			}
    782. 

  782. 			
    783. 

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

  784. 				$info['type'] = $row['type'];
    785. 

  785. 			}
    786. 

  786. 			
    787. 

  787. 			// Handle decimal places for money/smallmoney
    788. 

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

  789. 				$info['decimal_places'] = 2;
    790. 

  790. 			}
    791. 

  791. 			
    792. 

  792. 			// Handle the special data for varchar columns
    793. 

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

  794. 				if ($row['type'] == 'uniqueidentifier') {
    795. 

  795. 					$row['max_length'] = 32;
    796. 

  796. 				} elseif ($row['max_length'] == -1) {
    797. 

  797. 					$row['max_length'] = $row['type'] == 'nvarchar' ? 1073741823 : 2147483647;
    798. 

  798. 				}
    799. 

  799. 				$info['max_length'] = $row['max_length'];
    800. 

  800. 			}
    801. 

  801. 			
    802. 

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

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

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

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

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

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

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

  809. 					}
    810. 

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

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

  812. 				}
    813. 

  813. 			}
    814. 

  814. 			
    815. 

  815. 			// Handle auto increment
    816. 

  816. 			if ($row['auto_increment']) {
    817. 

  817. 				$info['auto_increment'] = TRUE;
    818. 

  818. 			}
    819. 

  819. 			
    820. 

  820. 			// Handle default values
    821. 

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

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

  823. 					$info['default'] = 'CURRENT_TIMESTAMP';
    824. 

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

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

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

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

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

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

  830. 				} else {
    831. 

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

  832. 				}
    833. 

  833. 			}
    834. 

  834. 			
    835. 

  835. 			// Handle not null
    836. 

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

  837. 			
    838. 

  838. 			$info['comment'] = $row['comment'];
    839. 

  839. 			
    840. 

  840. 			$column_info[$row['column']] = $info;
    841. 

  841. 		}
    842. 

  842. 		
    843. 

  843. 		return $column_info;
    844. 

  844. 	}
    845. 

  845. 	
    846. 

  846. 	
    847. 

  847. 	/**
    848. 

  848. 	 * Fetches the key info for an MSSQL database
    849. 

  849. 	 * 
    850. 

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

  851. 	 */
    852. 

  852. 	private function fetchMSSQLKeys()
    853. 

  853. 	{
    854. 

  854. 		$keys = array();
    855. 

  855. 		
    856. 

  856. 		$tables   = $this->getTables();
    857. 

  857. 		foreach ($tables as $table) {
    858. 

  858. 			$keys[$table] = array();
    859. 

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

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

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

  862. 		}
    863. 

  863. 		
    864. 

  864. 		$sql  = "SELECT
    865. 

  865. 						c.table_schema AS \"schema\",
    866. 

  866. 						c.table_name AS \"table\",
    867. 

  867. 						kcu.constraint_name AS constraint_name,
    868. 

  868. 						CASE c.constraint_type
    869. 

  869. 							WHEN 'PRIMARY KEY' THEN 'primary'
    870. 

  870. 							WHEN 'FOREIGN KEY' THEN 'foreign'
    871. 

  871. 							WHEN 'UNIQUE' THEN 'unique'
    872. 

  872. 						END AS 'type',
    873. 

  873. 						kcu.column_name AS 'column',
    874. 

  874. 						ccu.table_schema AS foreign_schema,
    875. 

  875. 						ccu.table_name AS foreign_table,
    876. 

  876. 						ccu.column_name AS foreign_column,
    877. 

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

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

  879. 					FROM
    880. 

  880. 						INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN
    881. 

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

  882. 						INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON c.constraint_name = rc.constraint_name LEFT JOIN
    883. 

  883. 						INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.constraint_name = rc.unique_constraint_name
    884. 

  884. 					WHERE
    885. 

  885. 						c.constraint_catalog = DB_NAME() AND
    886. 

  886. 						c.table_name != 'sysdiagrams'
    887. 

  887. 					ORDER BY
    888. 

  888. 						LOWER(c.table_schema),
    889. 

  889. 						LOWER(c.table_name),
    890. 

  890. 						c.constraint_type,
    891. 

  891. 						LOWER(kcu.constraint_name),
    892. 

  892. 						kcu.ordinal_position,
    893. 

  893. 						LOWER(kcu.column_name)";
    894. 

  894. 		
    895. 

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

  896. 		
    897. 

  897. 		$last_name  = '';
    898. 

  898. 		$last_table = '';
    899. 

  899. 		$last_type  = '';
    900. 

  900. 		foreach ($result as $row) {
    901. 

  901. 			
    902. 

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

  903. 				
    904. 

  904. 				if ($last_name) {
    905. 

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

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

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

  908. 						}
    909. 

  909. 						$keys[$last_table][$last_type][] = $temp;
    910. 

  910. 					} else {
    911. 

  911. 						$keys[$last_table][$last_type] = $temp;
    912. 

  912. 					}
    913. 

  913. 				}
    914. 

  914. 				
    915. 

  915. 				$temp = array();
    916. 

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

  917. 					
    918. 

  918. 					$temp['column']         = $row['column'];
    919. 

  919. 					$temp['foreign_table']  = $row['foreign_table'];
    920. 

  920. 					if ($row['foreign_schema'] != 'dbo') {
    921. 

  921. 						 $temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];	
    922. 

  922. 					}
    923. 

  923. 					$temp['foreign_column'] = $row['foreign_column'];
    924. 

  924. 					$temp['on_delete']      = 'no_action';
    925. 

  925. 					$temp['on_update']      = 'no_action';
    926. 

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

  927. 						$temp['on_delete'] = $row['on_delete'];
    928. 

  928. 					}
    929. 

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

  930. 						$temp['on_update'] = $row['on_update'];
    931. 

  931. 					}
    932. 

  932. 					
    933. 

  933. 				} else {
    934. 

  934. 					$temp[] = $row['column'];
    935. 

  935. 				}
    936. 

  936. 				
    937. 

  937. 				$last_table = $row['table'];
    938. 

  938. 				if ($row['schema'] != 'dbo') {
    939. 

  939. 					$last_table = $row['schema'] . '.' . $last_table;	
    940. 

  940. 				}
    941. 

  941. 				$last_name  = $row['constraint_name'];
    942. 

  942. 				$last_type  = $row['type'];
    943. 

  943. 				
    944. 

  944. 			} else {
    945. 

  945. 				$temp[] = $row['column'];
    946. 

  946. 			}
    947. 

  947. 		}
    948. 

  948. 		
    949. 

  949. 		if (isset($temp)) {
    950. 

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

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

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

  953. 				}
    954. 

  954. 				$keys[$last_table][$last_type][] = $temp;
    955. 

  955. 			} else {
    956. 

  956. 				$keys[$last_table][$last_type] = $temp;
    957. 

  957. 			}
    958. 

  958. 		}
    959. 

  959. 		
    960. 

  960. 		return $keys;
    961. 

  961. 	}
    962. 

  962. 	
    963. 

  963. 	
    964. 

  964. 	/**
    965. 

  965. 	 * Gets the column info from a MySQL database
    966. 

  966. 	 * 
    967. 

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

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

  969. 	 */
    970. 

  970. 	private function fetchMySQLColumnInfo($table)
    971. 

  971. 	{
    972. 

  972. 		$data_type_mapping = array(
    973. 

  973. 			'tinyint'			=> 'integer',
    974. 

  974. 			'smallint'			=> 'integer',
    975. 

  975. 			'mediumint'         => 'integer',
    976. 

  976. 			'int'				=> 'integer',
    977. 

  977. 			'bigint'			=> 'integer',
    978. 

  978. 			'datetime'			=> 'timestamp',
    979. 

  979. 			'timestamp'			=> 'timestamp',
    980. 

  980. 			'date'				=> 'date',
    981. 

  981. 			'time'				=> 'time',
    982. 

  982. 			'enum'				=> 'varchar',
    983. 

  983. 			'set'               => 'varchar',
    984. 

  984. 			'varchar'			=> 'varchar',
    985. 

  985. 			'char'				=> 'char',
    986. 

  986. 			'float'				=> 'float',
    987. 

  987. 			'double'			=> 'float',
    988. 

  988. 			'decimal'			=> 'float',
    989. 

  989. 			'binary'            => 'blob',
    990. 

  990. 			'varbinary'         => 'blob',
    991. 

  991. 			'tinyblob'			=> 'blob',
    992. 

  992. 			'blob'				=> 'blob',
    993. 

  993. 			'mediumblob'		=> 'blob',
    994. 

  994. 			'longblob'			=> 'blob',
    995. 

  995. 			'tinytext'			=> 'text',
    996. 

  996. 			'text'				=> 'text',
    997. 

  997. 			'mediumtext'		=> 'text',
    998. 

  998. 			'longtext'			=> 'text'
    999. 

  999. 		);
    1000. 

  1000. 		
    1001. 

  1001. 		$max_min_values = array(
    1002. 

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

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

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

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

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

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

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

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

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

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

  1012. 		);
    1013. 

  1013. 		
    1014. 

  1014. 		$column_info = array();
    1015. 

  1015. 		
    1016. 

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

  1017. 		
    1018. 

  1018. 		try {
    1019. 

  1019. 			$row        = $result->fetchRow();
    1020. 

  1020. 			$create_sql = $row['Create Table'];
    1021. 

  1021. 		} catch (fNoRowsException $e) {
    1022. 

  1022. 			return array();
    1023. 

  1023. 		}
    1024. 

  1024. 		
    1025. 

  1025. 		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);
    1026. 

  1026. 		
    1027. 

  1027. 		foreach ($matches as $match) {
    1028. 

  1028. 			
    1029. 

  1029. 			$info = array();
    1030. 

  1030. 			
    1031. 

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

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

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

  1034. 						$mapped_data_type = 'boolean';
    1035. 

  1035. 					
    1036. 

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

  1037. 						if (isset($max_min_values[$int_match[1]])) {
    1038. 

  1038. 							$info['min_value'] = $max_min_values[$int_match[1]]['min'];
    1039. 

  1039. 							$info['max_value'] = $max_min_values[$int_match[1]]['max'];	
    1040. 

  1040. 						}
    1041. 

  1041. 					}
    1042. 

  1042. 					
    1043. 

  1043. 					$info['type'] = $mapped_data_type;
    1044. 

  1044. 					break;
    1045. 

  1045. 				}
    1046. 

  1046. 			}
    1047. 

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

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

  1049. 			}
    1050. 

  1050. 			
    1051. 

  1051. 			switch ($match[2]) {
    1052. 

  1052. 				case 'tinyblob':
    1053. 

  1053. 				case 'tinytext':
    1054. 

  1054. 					$info['max_length'] = 255;
    1055. 

  1055. 					break;
    1056. 

  1056. 				
    1057. 

  1057. 				case 'blob':
    1058. 

  1058. 				case 'text':
    1059. 

  1059. 					$info['max_length'] = 65535;
    1060. 

  1060. 					break;
    1061. 

  1061. 				
    1062. 

  1062. 				case 'mediumblob':
    1063. 

  1063. 				case 'mediumtext':
    1064. 

  1064. 					$info['max_length'] = 16777215;
    1065. 

  1065. 					break;
    1066. 

  1066. 				
    1067. 

  1067. 				case 'longblob':
    1068. 

  1068. 				case 'longtext':
    1069. 

  1069. 					$info['max_length'] = 4294967295;
    1070. 

  1070. 					break;
    1071. 

  1071. 			}
    1072. 

  1072. 		
    1073. 

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

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

  1075. 				$match[3] = 0;
    1076. 

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

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

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

  1079. 					}
    1080. 

  1080. 				}
    1081. 

  1081. 			}
    1082. 

  1082. 			
    1083. 

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

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

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

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

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

  1088. 			}
    1089. 

  1089. 			
    1090. 

  1090. 			// Type specific information
    1091. 

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

  1092. 				$info['max_length'] = $match[3];
    1093. 

  1093. 			}
    1094. 

  1094. 			
    1095. 

  1095. 			// Grab the number of decimal places
    1096. 

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

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

  1098. 					$info['decimal_places'] = $data_type_info[2];
    1099. 

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

  1100. 					$after_digits  = str_pad('', $info['decimal_places'], '9');
    1101. 

  1101. 					$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    1102. 

  1102. 					$info['min_value'] = new fNumber('-' . $max_min);
    1103. 

  1103. 					$info['max_value'] = new fNumber($max_min);
    1104. 

  1104. 				}
    1105. 

  1105. 			}
    1106. 

  1106. 			
    1107. 

  1107. 			// Not null
    1108. 

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

  1109. 		
    1110. 

  1110. 			// Default values
    1111. 

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

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

  1113. 			}
    1114. 

  1114. 			
    1115. 

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

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

  1117. 			}
    1118. 

  1118. 		
    1119. 

  1119. 			// Auto increment fields
    1120. 

  1120. 			if (!empty($match[7])) {
    1121. 

  1121. 				$info['auto_increment'] = TRUE;
    1122. 

  1122. 			}
    1123. 

  1123. 			
    1124. 

  1124. 			// Column comments
    1125. 

  1125. 			if (!empty($match[8])) {
    1126. 

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

  1127. 			}
    1128. 

  1128. 		
    1129. 

  1129. 			$column_info[$match[1]] = $info;
    1130. 

  1130. 		}
    1131. 

  1131. 		
    1132. 

  1132. 		return $column_info;
    1133. 

  1133. 	}
    1134. 

  1134. 	
    1135. 

  1135. 	
    1136. 

  1136. 	/**
    1137. 

  1137. 	 * Fetches the keys for a MySQL database
    1138. 

  1138. 	 * 
    1139. 

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

  1140. 	 */
    1141. 

  1141. 	private function fetchMySQLKeys()
    1142. 

  1142. 	{
    1143. 

  1143. 		$tables   = $this->getTables();
    1144. 

  1144. 		$keys = array();
    1145. 

  1145. 		
    1146. 

  1146. 		foreach ($tables as $table) {
    1147. 

  1147. 			
    1148. 

  1148. 			$keys[$table] = array();
    1149. 

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

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

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

  1152. 			
    1153. 

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

  1154. 			$row    = $result->fetchRow();
    1155. 

  1155. 			
    1156. 

  1156. 			// Primary keys
    1157. 

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

  1158. 			if (!empty($matches)) {
    1159. 

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

  1160. 			}
    1161. 

  1161. 			
    1162. 

  1162. 			// Unique keys
    1163. 

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

  1164. 			foreach ($matches as $match) {
    1165. 

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

  1166. 			}
    1167. 

  1167. 			
    1168. 

  1168. 			// Foreign keys
    1169. 

  1169. 			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);
    1170. 

  1170. 			foreach ($matches as $match) {
    1171. 

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

  1172. 							  'foreign_table'  => $match[2],
    1173. 

  1173. 							  'foreign_column' => $match[3],
    1174. 

  1174. 							  'on_delete'      => 'no_action',
    1175. 

  1175. 							  'on_update'      => 'no_action');
    1176. 

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

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

  1178. 				}
    1179. 

  1179. 				if (!empty($match[5])) {
    1180. 

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

  1181. 				}
    1182. 

  1182. 				$keys[$table]['foreign'][] = $temp;
    1183. 

  1183. 			}
    1184. 

  1184. 		}
    1185. 

  1185. 		
    1186. 

  1186. 		return $keys;
    1187. 

  1187. 	}
    1188. 

  1188. 	
    1189. 

  1189. 	
    1190. 

  1190. 	/**
    1191. 

  1191. 	 * Gets the column info from an Oracle database
    1192. 

  1192. 	 * 
    1193. 

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

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

  1195. 	 */
    1196. 

  1196. 	private function fetchOracleColumnInfo($table)
    1197. 

  1197. 	{
    1198. 

  1198. 		$table = strtoupper($table);
    1199. 

  1199. 		
    1200. 

  1200. 		$schema = strtoupper($this->database->getUsername());
    1201. 

  1201. 		if (strpos($table, '.') !== FALSE) {
    1202. 

  1202. 			list ($schema, $table) = explode('.', $table);
    1203. 

  1203. 		}
    1204. 

  1204. 		
    1205. 

  1205. 		$column_info = array();
    1206. 

  1206. 		
    1207. 

  1207. 		$data_type_mapping = array(
    1208. 

  1208. 			'boolean'			=> 'boolean',
    1209. 

  1209. 			'number'            => 'integer',
    1210. 

  1210. 			'integer'			=> 'integer',
    1211. 

  1211. 			'timestamp'			=> 'timestamp',
    1212. 

  1212. 			'date'				=> 'date',
    1213. 

  1213. 			'varchar2'          => 'varchar',
    1214. 

  1214. 			'nvarchar2'			=> 'varchar',
    1215. 

  1215. 			'char'              => 'char',
    1216. 

  1216. 			'nchar'             => 'char',
    1217. 

  1217. 			'float'				=> 'float',
    1218. 

  1218. 			'binary_float'      => 'float',
    1219. 

  1219. 			'binary_double'     => 'float',
    1220. 

  1220. 			'blob'				=> 'blob',
    1221. 

  1221. 			'bfile'             => 'varchar',
    1222. 

  1222. 			'clob'				=> 'text',
    1223. 

  1223. 			'nclob'             => 'text'
    1224. 

  1224. 		);
    1225. 

  1225. 		
    1226. 

  1226. 		$sql = "SELECT
    1227. 

  1227. 						LOWER(ATC.COLUMN_NAME) COLUMN_NAME,
    1228. 

  1228. 						CASE
    1229. 

  1229. 							WHEN
    1230. 

  1230. 								ATC.DATA_TYPE = 'NUMBER' AND
    1231. 

  1231. 								ATC.DATA_PRECISION IS NULL AND
    1232. 

  1232. 								ATC.DATA_SCALE = 0
    1233. 

  1233. 							THEN
    1234. 

  1234. 								'integer'
    1235. 

  1235. 							WHEN
    1236. 

  1236. 								ATC.DATA_TYPE = 'NUMBER' AND
    1237. 

  1237. 								ATC.DATA_PRECISION = 1 AND
    1238. 

  1238. 								ATC.DATA_SCALE = 0
    1239. 

  1239. 							THEN
    1240. 

  1240. 								'boolean'
    1241. 

  1241. 							WHEN
    1242. 

  1242. 								ATC.DATA_TYPE = 'NUMBER' AND
    1243. 

  1243. 								ATC.DATA_PRECISION IS NOT NULL AND
    1244. 

  1244. 								ATC.DATA_SCALE != 0 AND
    1245. 

  1245. 								ATC.DATA_SCALE IS NOT NULL
    1246. 

  1246. 							THEN
    1247. 

  1247. 								'float'
    1248. 

  1248. 							ELSE
    1249. 

  1249. 								LOWER(ATC.DATA_TYPE)
    1250. 

  1250. 							END DATA_TYPE,
    1251. 

  1251. 						CASE
    1252. 

  1252. 							WHEN
    1253. 

  1253. 								ATC.CHAR_LENGTH <> 0
    1254. 

  1254. 							THEN
    1255. 

  1255. 								ATC.CHAR_LENGTH
    1256. 

  1256. 							WHEN
    1257. 

  1257. 								ATC.DATA_TYPE = 'NUMBER' AND
    1258. 

  1258. 								ATC.DATA_PRECISION != 1 AND
    1259. 

  1259. 								ATC.DATA_SCALE != 0	AND
    1260. 

  1260. 								ATC.DATA_PRECISION IS NOT NULL
    1261. 

  1261. 							THEN
    1262. 

  1262. 								ATC.DATA_SCALE
    1263. 

  1263. 							ELSE
    1264. 

  1264. 								NULL
    1265. 

  1265. 							END LENGTH,
    1266. 

  1266. 						ATC.DATA_PRECISION PRECISION,
    1267. 

  1267. 						ATC.NULLABLE,
    1268. 

  1268. 						ATC.DATA_DEFAULT,
    1269. 

  1269. 						AC.SEARCH_CONDITION CHECK_CONSTRAINT,
    1270. 

  1270. 						ACCM.COMMENTS
    1271. 

  1271. 					FROM
    1272. 

  1272. 						ALL_TAB_COLUMNS ATC LEFT JOIN
    1273. 

  1273. 						ALL_CONS_COLUMNS ACC ON
    1274. 

  1274. 							ATC.OWNER = ACC.OWNER AND
    1275. 

  1275. 							ATC.COLUMN_NAME = ACC.COLUMN_NAME AND
    1276. 

  1276. 							ATC.TABLE_NAME = ACC.TABLE_NAME AND
    1277. 

  1277. 							ACC.POSITION IS NULL LEFT JOIN
    1278. 

  1278. 						ALL_CONSTRAINTS AC ON
    1279. 

  1279. 							AC.OWNER = ACC.OWNER AND
    1280. 

  1280. 							AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME AND
    1281. 

  1281. 							AC.CONSTRAINT_TYPE = 'C' AND
    1282. 

  1282. 							AC.STATUS = 'ENABLED' LEFT JOIN
    1283. 

  1283. 						ALL_COL_COMMENTS ACCM ON
    1284. 

  1284. 							ATC.OWNER = ACCM.OWNER AND
    1285. 

  1285. 							ATC.COLUMN_NAME = ACCM.COLUMN_NAME AND
    1286. 

  1286. 							ATC.TABLE_NAME = ACCM.TABLE_NAME
    1287. 

  1287. 					WHERE
    1288. 

  1288. 						ATC.TABLE_NAME = %s AND
    1289. 

  1289. 						ATC.OWNER = %s
    1290. 

  1290. 					ORDER BY
    1291. 

  1291. 						ATC.TABLE_NAME ASC,
    1292. 

  1292. 						ATC.COLUMN_ID ASC";
    1293. 

  1293. 		
    1294. 

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

  1295. 		
    1296. 

  1296. 		foreach ($result as $row) {
    1297. 

  1297. 			
    1298. 

  1298. 			$column = $row['column_name'];
    1299. 

  1299. 			
    1300. 

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

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

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

  1303. 			// the check constraint
    1304. 

  1304. 			$duplicate = FALSE;
    1305. 

  1305. 			
    1306. 

  1306. 			if (isset($column_info[$column])) {
    1307. 

  1307. 				$info = $column_info[$column];
    1308. 

  1308. 				$duplicate = TRUE;
    1309. 

  1309. 			} else {
    1310. 

  1310. 				$info = array();
    1311. 

  1311. 			}
    1312. 

  1312. 			
    1313. 

  1313. 			if (!$duplicate) {
    1314. 

  1314. 				// Get the column type
    1315. 

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

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

  1317. 						$info['type'] = $mapped_data_type;
    1318. 

  1318. 						break;
    1319. 

  1319. 					}
    1320. 

  1320. 				}
    1321. 

  1321. 				
    1322. 

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

  1323. 					$info['type'] = $row['data_type'];
    1324. 

  1324. 				}
    1325. 

  1325. 				
    1326. 

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

  1327. 					$info['max_length'] = 4294967295;
    1328. 

  1328. 				}
    1329. 

  1329. 				
    1330. 

  1330. 				if ($row['data_type'] == 'float' && $row['precision']) {
    1331. 

  1331. 					$row['length'] = (int) $row['length'];
    1332. 

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

  1333. 					$after_digits  = str_pad('', $row['length'], '9');
    1334. 

  1334. 					$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    1335. 

  1335. 					$info['min_value'] = new fNumber('-' . $max_min);
    1336. 

  1336. 					$info['max_value'] = new fNumber($max_min);	
    1337. 

  1337. 				}
    1338. 

  1338. 				
    1339. 

  1339. 				// Handle the length of decimal/numeric fields
    1340. 

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

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

  1342. 				}
    1343. 

  1343. 				
    1344. 

  1344. 				// Handle the special data for varchar fields
    1345. 

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

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

  1347. 				}
    1348. 

  1348. 			}
    1349. 

  1349. 			
    1350. 

  1350. 			// Handle check constraints that are just simple lists
    1351. 

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

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

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

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

  1355. 					}			
    1356. 

  1356. 				}
    1357. 

  1357. 			}
    1358. 

  1358. 			
    1359. 

  1359. 			if (!$duplicate) {
    1360. 

  1360. 				// Handle default values
    1361. 

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

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

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

  1364. 						
    1365. 

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

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

  1367. 						
    1368. 

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

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

  1370. 						
    1371. 

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

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

  1373. 						
    1374. 

  1374. 					} else {
    1375. 

  1375. 						$info['default'] = $row['data_default'];
    1376. 

  1376. 					}
    1377. 

  1377. 				}
    1378. 

  1378. 			
    1379. 

  1379. 				// Not null values
    1380. 

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

  1381. 				
    1382. 

  1382. 				$info['comment'] = $row['comments'];
    1383. 

  1383. 			}
    1384. 

  1384. 			
    1385. 

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

  1386. 		}
    1387. 

  1387. 		
    1388. 

  1388. 		$sql = "SELECT
    1389. 

  1389. 						TRIGGER_BODY
    1390. 

  1390. 					FROM
    1391. 

  1391. 						ALL_TRIGGERS
    1392. 

  1392. 					WHERE
    1393. 

  1393. 						TRIGGERING_EVENT LIKE 'INSERT%' AND
    1394. 

  1394. 						STATUS = 'ENABLED' AND
    1395. 

  1395. 						TRIGGER_NAME NOT LIKE 'BIN\$%' AND
    1396. 

  1396. 						TABLE_NAME = %s AND
    1397. 

  1397. 						OWNER = %s";
    1398. 

  1398. 						
    1399. 

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

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

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

  1402. 				$column_info[$column]['auto_increment'] = TRUE;
    1403. 

  1403. 			}
    1404. 

  1404. 		}
    1405. 

  1405. 		
    1406. 

  1406. 		return $column_info;
    1407. 

  1407. 	}
    1408. 

  1408. 	
    1409. 

  1409. 	
    1410. 

  1410. 	/**
    1411. 

  1411. 	 * Fetches the key info for an Oracle database
    1412. 

  1412. 	 * 
    1413. 

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

  1414. 	 */
    1415. 

  1415. 	private function fetchOracleKeys()
    1416. 

  1416. 	{
    1417. 

  1417. 		$keys = array();
    1418. 

  1418. 		
    1419. 

  1419. 		$default_schema = strtolower($this->database->getUsername());
    1420. 

  1420. 		
    1421. 

  1421. 		$tables = $this->getTables();
    1422. 

  1422. 		foreach ($tables as $table) {
    1423. 

  1423. 			$keys[$table] = array();
    1424. 

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

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

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

  1427. 		}
    1428. 

  1428. 		
    1429. 

  1429. 		$params = array();
    1430. 

  1430. 		
    1431. 

  1431. 		$sql  = "SELECT
    1432. 

  1432. 						 LOWER(AC.OWNER) \"SCHEMA\",
    1433. 

  1433. 						 LOWER(AC.TABLE_NAME) \"TABLE\",
    1434. 

  1434. 						 AC.CONSTRAINT_NAME CONSTRAINT_NAME,
    1435. 

  1435. 						 CASE AC.CONSTRAINT_TYPE
    1436. 

  1436. 							 WHEN 'P' THEN 'primary'
    1437. 

  1437. 							 WHEN 'R' THEN 'foreign'
    1438. 

  1438. 							 WHEN 'U' THEN 'unique'
    1439. 

  1439. 							 END TYPE,
    1440. 

  1440. 						 LOWER(ACC.COLUMN_NAME) \"COLUMN\",
    1441. 

  1441. 						 LOWER(FKC.OWNER) FOREIGN_SCHEMA,
    1442. 

  1442. 						 LOWER(FKC.TABLE_NAME) FOREIGN_TABLE,
    1443. 

  1443. 						 LOWER(FKC.COLUMN_NAME) FOREIGN_COLUMN,
    1444. 

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

  1445. 					 FROM
    1446. 

  1446. 						 ALL_CONSTRAINTS AC INNER JOIN
    1447. 

  1447. 						 ALL_CONS_COLUMNS ACC ON AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME AND AC.OWNER = ACC.OWNER LEFT JOIN
    1448. 

  1448. 						 ALL_CONSTRAINTS FK ON AC.R_CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND AC.OWNER = FK.OWNER LEFT JOIN
    1449. 

  1449. 						 ALL_CONS_COLUMNS FKC ON FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME AND FK.OWNER = FKC.OWNER
    1450. 

  1450. 					 WHERE
    1451. 

  1451. 						 AC.CONSTRAINT_TYPE IN ('U', 'P', 'R') AND ";
    1452. 

  1452. 		
    1453. 

  1453. 		$conditions = array();
    1454. 

  1454. 		foreach ($tables as $table) {
    1455. 

  1455. 			if (strpos($table, '.') === FALSE) {
    1456. 

  1456. 				$table = $default_schema . '.' . $table;
    1457. 

  1457. 			}	
    1458. 

  1458. 			list ($schema, $table) = explode('.', strtoupper($table));
    1459. 

  1459. 			$conditions[] = "AC.OWNER = %s AND AC.TABLE_NAME = %s";
    1460. 

  1460. 			$params[] = $schema;
    1461. 

  1461. 			$params[] = $table;
    1462. 

  1462. 		}
    1463. 

  1463. 		$sql .= '((' . join(') OR( ', $conditions) . '))';
    1464. 

  1464. 		
    1465. 

  1465. 		$sql .= " ORDER BY
    1466. 

  1466. 						 AC.OWNER ASC,
    1467. 

  1467. 						 AC.TABLE_NAME ASC,
    1468. 

  1468. 						 AC.CONSTRAINT_TYPE ASC,
    1469. 

  1469. 						 AC.CONSTRAINT_NAME ASC,
    1470. 

  1470. 						 ACC.POSITION ASC";
    1471. 

  1471. 		
    1472. 

  1472. 		$result = $this->database->query($sql, $params);
    1473. 

  1473. 		
    1474. 

  1474. 		$last_name  = '';
    1475. 

  1475. 		$last_table = '';
    1476. 

  1476. 		$last_type  = '';
    1477. 

  1477. 		foreach ($result as $row) {
    1478. 

  1478. 			
    1479. 

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

  1480. 				
    1481. 

  1481. 				if ($last_name) {
    1482. 

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

  1483. 						$keys[$last_table][$last_type][] = $temp;
    1484. 

  1484. 					} else {
    1485. 

  1485. 						$keys[$last_table][$last_type] = $temp;
    1486. 

  1486. 					}
    1487. 

  1487. 				}
    1488. 

  1488. 				
    1489. 

  1489. 				$temp = array();
    1490. 

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

  1491. 					
    1492. 

  1492. 					$temp['column']         = $row['column'];
    1493. 

  1493. 					$temp['foreign_table']  = $row['foreign_table'];
    1494. 

  1494. 					if ($row['foreign_schema'] != $default_schema) {
    1495. 

  1495. 						$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];
    1496. 

  1496. 					}
    1497. 

  1497. 					$temp['foreign_column'] = $row['foreign_column'];
    1498. 

  1498. 					$temp['on_delete']      = 'no_action';
    1499. 

  1499. 					$temp['on_update']      = 'no_action';
    1500. 

  1500. 					
    1501. 

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

  1502. 						$temp['on_delete'] = $row['on_delete'];
    1503. 

  1503. 					}
    1504. 

  1504. 					
    1505. 

  1505. 				} else {
    1506. 

  1506. 					$temp[] = $row['column'];
    1507. 

  1507. 				}
    1508. 

  1508. 				
    1509. 

  1509. 				$last_table = $row['table'];
    1510. 

  1510. 				if ($row['schema'] != $default_schema) {
    1511. 

  1511. 					$last_table = $row['schema'] . '.' . $last_table;
    1512. 

  1512. 				}
    1513. 

  1513. 				$last_name  = $row['constraint_name'];
    1514. 

  1514. 				$last_type  = $row['type'];
    1515. 

  1515. 				
    1516. 

  1516. 			} else {
    1517. 

  1517. 				$temp[] = $row['column'];
    1518. 

  1518. 			}
    1519. 

  1519. 		}
    1520. 

  1520. 		
    1521. 

  1521. 		if (isset($temp)) {
    1522. 

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

  1523. 				$keys[$last_table][$last_type][] = $temp;
    1524. 

  1524. 			} else {
    1525. 

  1525. 				$keys[$last_table][$last_type] = $temp;
    1526. 

  1526. 			}
    1527. 

  1527. 		}
    1528. 

  1528. 		
    1529. 

  1529. 		return $keys;
    1530. 

  1530. 	}
    1531. 

  1531. 	
    1532. 

  1532. 	
    1533. 

  1533. 	/**
    1534. 

  1534. 	 * Gets the column info from a PostgreSQL database
    1535. 

  1535. 	 * 
    1536. 

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

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

  1538. 	 */
    1539. 

  1539. 	private function fetchPostgreSQLColumnInfo($table)
    1540. 

  1540. 	{
    1541. 

  1541. 		$column_info = array();
    1542. 

  1542. 		
    1543. 

  1543. 		$schema = 'public';
    1544. 

  1544. 		if (strpos($table, '.') !== FALSE) {
    1545. 

  1545. 			list ($schema, $table) = explode('.', $table);	
    1546. 

  1546. 		}
    1547. 

  1547. 		
    1548. 

  1548. 		$data_type_mapping = array(
    1549. 

  1549. 			'boolean'			=> 'boolean',
    1550. 

  1550. 			'smallint'			=> 'integer',
    1551. 

  1551. 			'int'				=> 'integer',
    1552. 

  1552. 			'bigint'			=> 'integer',
    1553. 

  1553. 			'serial'			=> 'integer',
    1554. 

  1554. 			'bigserial'			=> 'integer',
    1555. 

  1555. 			'timestamp'			=> 'timestamp',
    1556. 

  1556. 			'date'				=> 'date',
    1557. 

  1557. 			'time'				=> 'time',
    1558. 

  1558. 			'uuid'              => 'varchar',
    1559. 

  1559. 			'character varying'	=> 'varchar',
    1560. 

  1560. 			'character'			=> 'char',
    1561. 

  1561. 			'real'				=> 'float',
    1562. 

  1562. 			'double'			=> 'float',
    1563. 

  1563. 			'numeric'			=> 'float',
    1564. 

  1564. 			'bytea'				=> 'blob',
    1565. 

  1565. 			'text'				=> 'text',
    1566. 

  1566. 			'mediumtext'		=> 'text',
    1567. 

  1567. 			'longtext'			=> 'text'
    1568. 

  1568. 		);
    1569. 

  1569. 		
    1570. 

  1570. 		$max_min_values = array(
    1571. 

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

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

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

  1574. 			'serial'    => array('min' => new fNumber(-2147483648),             'max' => new fNumber(2147483647)),
    1575. 

  1575. 			'bigserial' => array('min' => new fNumber('-9223372036854775808'),  'max' => new fNumber('9223372036854775807'))
    1576. 

  1576. 		);
    1577. 

  1577. 		
    1578. 

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

  1579. 		$sql = "SELECT
    1580. 

  1580. 						pg_attribute.attname                                        AS column,
    1581. 

  1581. 						format_type(pg_attribute.atttypid, pg_attribute.atttypmod)  AS data_type,
    1582. 

  1582. 						pg_attribute.attnotnull                                     AS not_null,
    1583. 

  1583. 						pg_attrdef.adsrc                                            AS default,
    1584. 

  1584. 						pg_get_constraintdef(pg_constraint.oid)                     AS constraint,
    1585. 

  1585. 						col_description(pg_class.oid, pg_attribute.attnum)          AS comment              
    1586. 

  1586. 					FROM
    1587. 

  1587. 						pg_attribute LEFT JOIN
    1588. 

  1588. 						pg_class ON pg_attribute.attrelid = pg_class.oid LEFT JOIN
    1589. 

  1589. 						pg_namespace ON pg_class.relnamespace = pg_namespace.oid LEFT JOIN
    1590. 

  1590. 						pg_type ON pg_type.oid = pg_attribute.atttypid LEFT JOIN
    1591. 

  1591. 						pg_constraint ON pg_constraint.conrelid = pg_class.oid AND
    1592. 

  1592. 										 pg_attribute.attnum = ANY (pg_constraint.conkey) AND
    1593. 

  1593. 										 pg_constraint.contype = 'c' LEFT JOIN
    1594. 

  1594. 						pg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND
    1595. 

  1595. 									  pg_attribute.attnum = pg_attrdef.adnum
    1596. 

  1596. 					WHERE
    1597. 

  1597. 						NOT pg_attribute.attisdropped AND
    1598. 

  1598. 						pg_class.relname = %s AND
    1599. 

  1599. 						pg_namespace.nspname = %s AND
    1600. 

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

  1601. 					ORDER BY
    1602. 

  1602. 						pg_attribute.attnum,
    1603. 

  1603. 						pg_constraint.contype";
    1604. 

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

  1605. 		
    1606. 

  1606. 		foreach ($result as $row) {
    1607. 

  1607. 			
    1608. 

  1608. 			$info = array();
    1609. 

  1609. 			
    1610. 

  1610. 			// Get the column type
    1611. 

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

  1612. 			
    1613. 

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

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

  1615. 					$info['type'] = $mapped_data_type;
    1616. 

  1616. 					if (isset($max_min_values[$data_type])) {
    1617. 

  1617. 						$info['min_value'] = $max_min_values[$data_type]['min'];
    1618. 

  1618. 						$info['max_value'] = $max_min_values[$data_type]['max'];
    1619. 

  1619. 					}
    1620. 

  1620. 					break;
    1621. 

  1621. 				}
    1622. 

  1622. 			}
    1623. 

  1623. 			
    1624. 

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

  1625. 				$info['type'] = $column_data_type[1];
    1626. 

  1626. 			}
    1627. 

  1627. 			
    1628. 

  1628. 			if ($info['type'] == 'blob' || $info['type'] == 'text') {
    1629. 

  1629. 				$info['max_length'] = 1073741824; 
    1630. 

  1630. 			}
    1631. 

  1631. 			
    1632. 

  1632. 			// Handle the length of decimal/numeric fields
    1633. 

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

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

  1635. 				$before_digits = str_pad('', $column_data_type[2] - $info['decimal_places'], '9');
    1636. 

  1636. 				$after_digits  = str_pad('', $info['decimal_places'], '9');
    1637. 

  1637. 				$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    1638. 

  1638. 				$info['min_value'] = new fNumber('-' . $max_min);
    1639. 

  1639. 				$info['max_value'] = new fNumber($max_min);
    1640. 

  1640. 			}
    1641. 

  1641. 			
    1642. 

  1642. 			// Handle the special data for varchar fields
    1643. 

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

  1644. 				if (!empty($column_data_type[2])) {
    1645. 

  1645. 					$info['max_length'] = $column_data_type[2];
    1646. 

  1646. 				} else {
    1647. 

  1647. 					$info['max_length'] = 1073741824; 
    1648. 

  1648. 				}
    1649. 

  1649. 			}
    1650. 

  1650. 			
    1651. 

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

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

  1653. 				$info['max_length'] = 38;	
    1654. 

  1654. 			}
    1655. 

  1655. 			
    1656. 

  1656. 			// Handle check constraints that are just simple lists
    1657. 

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

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

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

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

  1661. 					}
    1662. 

  1662. 				}
    1663. 

  1663. 			}
    1664. 

  1664. 			
    1665. 

  1665. 			// Handle default values and serial data types
    1666. 

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

  1667. 				$info['auto_increment'] = TRUE;
    1668. 

  1668. 				
    1669. 

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

  1670. 				if (preg_match('#^NULL::[\w\s]+$#', $row['default'])) {
    1671. 

  1671. 					$info['default'] = NULL;
    1672. 

  1672. 				} elseif ($row['default'] == 'now()') {
    1673. 

  1673. 					$info['default'] = 'CURRENT_TIMESTAMP';
    1674. 

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

  1675. 					$info['default'] = 'CURRENT_DATE';
    1676. 

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

  1677. 					$info['default'] = 'CURRENT_TIME';	
    1678. 

  1678. 				} else {
    1679. 

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

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

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

  1682. 					}
    1683. 

  1683. 				}
    1684. 

  1684. 			}
    1685. 

  1685. 			
    1686. 

  1686. 			// Not null values
    1687. 

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

  1688. 			
    1689. 

  1689. 			$info['comment'] = $row['comment'];
    1690. 

  1690. 			
    1691. 

  1691. 			$column_info[$row['column']] = $info;
    1692. 

  1692. 		}
    1693. 

  1693. 		
    1694. 

  1694. 		return $column_info;
    1695. 

  1695. 	}
    1696. 

  1696. 	
    1697. 

  1697. 	
    1698. 

  1698. 	/**
    1699. 

  1699. 	 * Fetches the key info for a PostgreSQL database
    1700. 

  1700. 	 * 
    1701. 

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

  1702. 	 */
    1703. 

  1703. 	private function fetchPostgreSQLKeys()
    1704. 

  1704. 	{
    1705. 

  1705. 		$keys = array();
    1706. 

  1706. 		
    1707. 

  1707. 		$tables   = $this->getTables();
    1708. 

  1708. 		foreach ($tables as $table) {
    1709. 

  1709. 			$keys[$table] = array();
    1710. 

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

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

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

  1713. 		}
    1714. 

  1714. 		
    1715. 

  1715. 		$sql  = "(
    1716. 

  1716. 				 SELECT
    1717. 

  1717. 						 s.nspname AS \"schema\",
    1718. 

  1718. 						 t.relname AS \"table\",
    1719. 

  1719. 						 con.conname AS constraint_name,
    1720. 

  1720. 						 CASE con.contype
    1721. 

  1721. 							 WHEN 'f' THEN 'foreign'
    1722. 

  1722. 							 WHEN 'p' THEN 'primary'
    1723. 

  1723. 							 WHEN 'u' THEN 'unique'
    1724. 

  1724. 						 END AS type,
    1725. 

  1725. 						 col.attname AS column,
    1726. 

  1726. 						 fs.nspname AS foreign_schema,
    1727. 

  1727. 						 ft.relname AS foreign_table,
    1728. 

  1728. 						 fc.attname AS foreign_column,
    1729. 

  1729. 						 CASE con.confdeltype
    1730. 

  1730. 							 WHEN 'c' THEN 'cascade'
    1731. 

  1731. 							 WHEN 'a' THEN 'no_action'
    1732. 

  1732. 							 WHEN 'r' THEN 'restrict'
    1733. 

  1733. 							 WHEN 'n' THEN 'set_null'
    1734. 

  1734. 							 WHEN 'd' THEN 'set_default'
    1735. 

  1735. 						 END AS on_delete,
    1736. 

  1736. 						 CASE con.confupdtype
    1737. 

  1737. 							 WHEN 'c' THEN 'cascade'
    1738. 

  1738. 							 WHEN 'a' THEN 'no_action'
    1739. 

  1739. 							 WHEN 'r' THEN 'restrict'
    1740. 

  1740. 							 WHEN 'n' THEN 'set_null'
    1741. 

  1741. 							 WHEN 'd' THEN 'set_default'
    1742. 

  1742. 						 END AS on_update,
    1743. 

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

  1744. 					 FROM
    1745. 

  1745. 						 pg_attribute AS col INNER JOIN
    1746. 

  1746. 						 pg_class AS t ON col.attrelid = t.oid INNER JOIN
    1747. 

  1747. 						 pg_namespace AS s ON t.relnamespace = s.oid INNER JOIN
    1748. 

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

  1749. 												  con.conrelid = t.oid) LEFT JOIN
    1750. 

  1750. 						 pg_class AS ft ON con.confrelid = ft.oid LEFT JOIN
    1751. 

  1751. 						 pg_namespace AS fs ON ft.relnamespace = fs.oid LEFT JOIN
    1752. 

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

  1753. 												ft.oid = fc.attrelid)
    1754. 

  1754. 					 WHERE
    1755. 

  1755. 						 NOT col.attisdropped AND
    1756. 

  1756. 						 (con.contype = 'p' OR
    1757. 

  1757. 						  con.contype = 'f' OR
    1758. 

  1758. 						  con.contype = 'u')
    1759. 

  1759. 				) UNION (
    1760. 

  1760. 				SELECT
    1761. 

  1761. 						n.nspname AS \"schema\",
    1762. 

  1762. 						t.relname AS \"table\",
    1763. 

  1763. 						ic.relname AS constraint_name,
    1764. 

  1764. 						'unique' AS type,
    1765. 

  1765. 						col.attname AS column,
    1766. 

  1766. 						NULL AS foreign_schema,
    1767. 

  1767. 						NULL AS foreign_table,
    1768. 

  1768. 						NULL AS foreign_column,
    1769. 

  1769. 						NULL AS on_delete,
    1770. 

  1770. 						NULL AS on_update,
    1771. 

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

  1772. 					FROM
    1773. 

  1773. 						pg_class AS t INNER JOIN
    1774. 

  1774. 						pg_index AS ind ON ind.indrelid = t.oid INNER JOIN
    1775. 

  1775. 						pg_namespace AS n ON t.relnamespace = n.oid INNER JOIN
    1776. 

  1776. 						pg_class AS ic ON ind.indexrelid = ic.oid LEFT JOIN
    1777. 

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

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

  1779. 					WHERE
    1780. 

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

  1781. 						indisunique = TRUE AND
    1782. 

  1782. 						indisprimary = FALSE AND
    1783. 

  1783. 						con.oid IS NULL AND
    1784. 

  1784. 						0 != ALL ((ind.indkey)::int[])
    1785. 

  1785. 				) ORDER BY 1, 2, 4, 3, 11";
    1786. 

  1786. 		
    1787. 

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

  1788. 		
    1789. 

  1789. 		$last_name  = '';
    1790. 

  1790. 		$last_table = '';
    1791. 

  1791. 		$last_type  = '';
    1792. 

  1792. 		foreach ($result as $row) {
    1793. 

  1793. 			
    1794. 

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

  1795. 				
    1796. 

  1796. 				if ($last_name) {
    1797. 

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

  1798. 						$keys[$last_table][$last_type][] = $temp;
    1799. 

  1799. 					} else {
    1800. 

  1800. 						$keys[$last_table][$last_type] = $temp;
    1801. 

  1801. 					}
    1802. 

  1802. 				}
    1803. 

  1803. 				
    1804. 

  1804. 				$temp = array();
    1805. 

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

  1806. 					
    1807. 

  1807. 					$temp['column']         = $row['column'];
    1808. 

  1808. 					$temp['foreign_table']  = $row['foreign_table'];
    1809. 

  1809. 					if ($row['foreign_schema'] != 'public') {
    1810. 

  1810. 						$temp['foreign_table'] = $row['foreign_schema'] . '.' . $temp['foreign_table'];	
    1811. 

  1811. 					}
    1812. 

  1812. 					$temp['foreign_column'] = $row['foreign_column'];
    1813. 

  1813. 					$temp['on_delete']      = 'no_action';
    1814. 

  1814. 					$temp['on_update']      = 'no_action';
    1815. 

  1815. 					
    1816. 

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

  1817. 						$temp['on_delete'] = $row['on_delete'];
    1818. 

  1818. 					}
    1819. 

  1819. 					
    1820. 

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

  1821. 						$temp['on_update'] = $row['on_update'];
    1822. 

  1822. 					}
    1823. 

  1823. 					
    1824. 

  1824. 				} else {
    1825. 

  1825. 					$temp[] = $row['column'];
    1826. 

  1826. 				}
    1827. 

  1827. 				
    1828. 

  1828. 				$last_table = $row['table'];
    1829. 

  1829. 				if ($row['schema'] != 'public') {
    1830. 

  1830. 					$last_table = $row['schema'] . '.' . $last_table;	
    1831. 

  1831. 				}
    1832. 

  1832. 				$last_name  = $row['constraint_name'];
    1833. 

  1833. 				$last_type  = $row['type'];
    1834. 

  1834. 				
    1835. 

  1835. 			} else {
    1836. 

  1836. 				$temp[] = $row['column'];
    1837. 

  1837. 			}
    1838. 

  1838. 		}
    1839. 

  1839. 		
    1840. 

  1840. 		if (isset($temp)) {
    1841. 

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

  1842. 				$keys[$last_table][$last_type][] = $temp;
    1843. 

  1843. 			} else {
    1844. 

  1844. 				$keys[$last_table][$last_type] = $temp;
    1845. 

  1845. 			}
    1846. 

  1846. 		}
    1847. 

  1847. 		
    1848. 

  1848. 		return $keys;
    1849. 

  1849. 	}
    1850. 

  1850. 	
    1851. 

  1851. 	
    1852. 

  1852. 	/**
    1853. 

  1853. 	 * Gets the column info from a SQLite database
    1854. 

  1854. 	 * 
    1855. 

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

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

  1857. 	 */
    1858. 

  1858. 	private function fetchSQLiteColumnInfo($table)
    1859. 

  1859. 	{
    1860. 

  1860. 		$column_info = array();
    1861. 

  1861. 		
    1862. 

  1862. 		$data_type_mapping = array(
    1863. 

  1863. 			'boolean'			=> 'boolean',
    1864. 

  1864. 			'serial'            => 'integer',
    1865. 

  1865. 			'smallint'			=> 'integer',
    1866. 

  1866. 			'int'				=> 'integer',
    1867. 

  1867. 			'integer'           => 'integer',
    1868. 

  1868. 			'bigint'			=> 'integer',
    1869. 

  1869. 			'timestamp'			=> 'timestamp',
    1870. 

  1870. 			'date'				=> 'date',
    1871. 

  1871. 			'time'				=> 'time',
    1872. 

  1872. 			'varchar'			=> 'varchar',
    1873. 

  1873. 			'char'				=> 'char',
    1874. 

  1874. 			'real'				=> 'float',
    1875. 

  1875. 			'numeric'           => 'float',
    1876. 

  1876. 			'float'             => 'float',
    1877. 

  1877. 			'double'			=> 'float',
    1878. 

  1878. 			'decimal'			=> 'float',
    1879. 

  1879. 			'blob'				=> 'blob',
    1880. 

  1880. 			'text'				=> 'text'
    1881. 

  1881. 		);
    1882. 

  1882. 		
    1883. 

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

  1884. 		
    1885. 

  1885. 		try {
    1886. 

  1886. 			$row        = $result->fetchRow();
    1887. 

  1887. 			$create_sql = $row['sql'];
    1888. 

  1888. 		} catch (fNoRowsException $e) {
    1889. 

  1889. 			return array();			
    1890. 

  1890. 		}
    1891. 

  1891. 		
    1892. 

  1892. 		preg_match_all('#(?<=,|\(|\*/|\n)\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))?)?([ \t]*(?:/\*(?:(?!\*/).)*\*/))?\s*(?:,([ \t]*--[^\n]*\n)?|(--[^\n]*\n)?\s*(?=\)))#msi', $create_sql, $matches, PREG_SET_ORDER);
    1893. 

  1893. 		
    1894. 

  1894. 		foreach ($matches as $match) {
    1895. 

  1895. 			$info = array();
    1896. 

  1896. 			
    1897. 

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

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

  1899. 					$info['type'] = $mapped_data_type;
    1900. 

  1900. 					break;
    1901. 

  1901. 				}
    1902. 

  1902. 			}
    1903. 

  1903. 		
    1904. 

  1904. 			// Type specific information
    1905. 

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

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

  1907. 					$info['max_length'] = $match[3];
    1908. 

  1908. 				} else {
    1909. 

  1909. 					$info['max_length'] = 1000000000;   
    1910. 

  1910. 				}
    1911. 

  1911. 			}
    1912. 

  1912. 			
    1913. 

  1913. 			if ($info['type'] == 'text' || $info['type'] == 'blob') {
    1914. 

  1914. 				$info['max_length'] = 1000000000;   
    1915. 

  1915. 			}
    1916. 

  1916. 			
    1917. 

  1917. 			// Figure out how many decimal places for a decimal
    1918. 

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

  1919. 				$info['decimal_places'] = $match[4];
    1920. 

  1920. 				$before_digits = str_pad('', $match[3] - $match[4], '9');
    1921. 

  1921. 				$after_digits  = str_pad('', $match[4], '9');
    1922. 

  1922. 				$max_min       = $before_digits . ($after_digits ? '.' : '') . $after_digits;
    1923. 

  1923. 				$info['min_value'] = new fNumber('-' . $max_min);
    1924. 

  1924. 				$info['max_value'] = new fNumber($max_min);
    1925. 

  1925. 			}
    1926. 

  1926. 			
    1927. 

  1927. 			// Not null
    1928. 

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

  1929. 		
    1930. 

  1930. 			// Default values
    1931. 

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

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

  1933. 			}
    1934. 

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

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

  1936. 			}
    1937. 

  1937. 		
    1938. 

  1938. 			// Check constraints
    1939. 

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

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

  1941. 			}
    1942. 

  1942. 		
    1943. 

  1943. 			// Auto increment fields
    1944. 

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

  1945. 				$info['auto_increment'] = TRUE;
    1946. 

  1946. 			}
    1947. 

  1947. 			
    1948. 

  1948. 			// Column comments
    1949. 

  1949. 			if (!empty($match[11]) || !empty($match[12]) || !empty($match[13])) {
    1950. 

  1950. 				if (!empty($match[11])) {
    1951. 

  1951. 					$comment = $match[11];
    1952. 

  1952. 				} elseif (!empty($match[12])) {
    1953. 

  1953. 					$comment = $match[12];
    1954. 

  1954. 				} else {
    1955. 

  1955. 					$comment = $match[13];
    1956. 

  1956. 				}
    1957. 

  1957. 				$comment = trim($comment);
    1958. 

  1958. 				$comment = substr($comment, 0, 2) == '--' ? substr($comment, 2) : substr($comment, 2, -2);
    1959. 

  1959. 				$info['comment'] = trim($comment);
    1960. 

  1960. 			}
    1961. 

  1961. 		
    1962. 

  1962. 			$column_info[$match[1]] = $info;
    1963. 

  1963. 		}
    1964. 

  1964. 		
    1965. 

  1965. 		return $column_info;
    1966. 

  1966. 	}
    1967. 

  1967. 	
    1968. 

  1968. 	
    1969. 

  1969. 	/**
    1970. 

  1970. 	 * Fetches the key info for an SQLite database
    1971. 

  1971. 	 * 
    1972. 

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

  1973. 	 */
    1974. 

  1974. 	private function fetchSQLiteKeys()
    1975. 

  1975. 	{
    1976. 

  1976. 		$tables = $this->getTables();
    1977. 

  1977. 		$keys   = array();
    1978. 

  1978. 		
    1979. 

  1979. 		foreach ($tables as $table) {
    1980. 

  1980. 			$keys[$table] = array();
    1981. 

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

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

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

  1984. 			
    1985. 

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

  1986. 			$row        = $result->fetchRow();
    1987. 

  1987. 			$create_sql = $row['sql'];
    1988. 

  1988. 			
    1989. 

  1989. 			// Collapse strings into empty string to make the matching simpler
    1990. 

  1990. 			$create_sql = preg_replace('#\'(?:\'\'|[^\']+)*\'#', "''", $create_sql);
    1991. 

  1991. 			
    1992. 

  1992. 			// Remove single-line comments
    1993. 

  1993. 			$create_sql = preg_replace('#--[^\n]*\n#', "\n", $create_sql);
    1994. 

  1994. 			
    1995. 

  1995. 			// Remove multi-line comments
    1996. 

  1996. 			$create_sql = preg_replace('#/\*((?!\*/).)*\*/#', '', $create_sql);
    1997. 

  1997. 			
    1998. 

  1998. 			// Get column level key definitions
    1999. 

  1999. 			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);
    2000. 

  2000. 			
    2001. 

  2001. 			foreach ($matches as $match) {
    2002. 

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

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

  2004. 				}
    2005. 

  2005. 				
    2006. 

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

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

  2008. 				}
    2009. 

  2009. 				
    2010. 

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

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

  2012. 								  'foreign_table'  => $match[5],
    2013. 

  2013. 								  'foreign_column' => $match[6],
    2014. 

  2014. 								  'on_delete'      => 'no_action',
    2015. 

  2015. 								  'on_update'      => 'no_action');
    2016. 

  2016. 					if (isset($match[7])) {
    2017. 

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

  2018. 					}
    2019. 

  2019. 					if (isset($match[8])) {
    2020. 

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

  2021. 					}
    2022. 

  2022. 					$keys[$table]['foreign'][] = $temp;
    2023. 

  2023. 				}
    2024. 

  2024. 			}
    2025. 

  2025. 			
    2026. 

  2026. 			// Get table level primary key definitions
    2027. 

  2027. 			preg_match_all('#(?<=,|\()\s*PRIMARY\s+KEY\s*\(\s*((?:\s*["`\[]?\w+["`\]]?\s*,\s*)*["`\[]?\w+["`\]]?)\s*\)\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
    2028. 

  2028. 			
    2029. 

  2029. 			foreach ($matches as $match) {
    2030. 

  2030. 				$columns = preg_split('#\s*,\s*#', $match[1]);
    2031. 

  2031. 				foreach ($columns as $column) {
    2032. 

  2032. 					$keys[$table]['primary'][] = str_replace(array('[', '"', '`', ']'), '', $column);	
    2033. 

  2033. 				}
    2034. 

  2034. 			}
    2035. 

  2035. 			
    2036. 

  2036. 			// Get table level foreign key definitions
    2037. 

  2037. 			preg_match_all('#(?<=,|\()\s*FOREIGN\s+KEY\s*(?:["`\[]?(\w+)["`\]]?|\(\s*["`\[]?(\w+)["`\]]?\s*\))\s+REFERENCES\s+["`\[]?(\w+)["`\]]?\s*\(\s*["`\[]?(\w+)["`\]]?\s*\)\s*(?:\s+(?:ON\s+DELETE\s+(CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL|SET\s+DEFAULT)))?(?:\s+(?:ON\s+UPDATE\s+(CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL|SET\s+DEFAULT)))?(?:\s+(?:DEFERRABLE|NOT\s+DEFERRABLE))?\s*(?:,|\s*(?=\)))#mis', $create_sql, $matches, PREG_SET_ORDER);
    2038. 

  2038. 			
    2039. 

  2039. 			foreach ($matches as $match) {
    2040. 

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

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

  2042. 							  'foreign_table'  => $match[3],
    2043. 

  2043. 							  'foreign_column' => $match[4],
    2044. 

  2044. 							  'on_delete'      => 'no_action',
    2045. 

  2045. 							  'on_update'      => 'no_action');
    2046. 

  2046. 				if (isset($match[5])) {
    2047. 

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

  2048. 				}
    2049. 

  2049. 				if (isset($match[6])) {
    2050. 

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

  2051. 				}
    2052. 

  2052. 				$keys[$table]['foreign'][] = $temp;
    2053. 

  2053. 			}
    2054. 

  2054. 			
    2055. 

  2055. 			// Get table level unique key definitions
    2056. 

  2056. 			preg_match_all('#(?<=,|\()\s*UNIQUE\s*\(\s*((?:\s*["`\[]?\w+["`\]]?\s*,\s*)*["`\[]?\w+["`\]]?)\s*\)\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
    2057. 

  2057. 			
    2058. 

  2058. 			foreach ($matches as $match) {
    2059. 

  2059. 				$columns = preg_split('#\s*,\s*#', $match[1]);
    2060. 

  2060. 				$key = array();
    2061. 

  2061. 				foreach ($columns as $column) {
    2062. 

  2062. 					$key[] = str_replace(array('[', '"', '`', ']'), '', $column);
    2063. 

  2063. 				}
    2064. 

  2064. 				$keys[$table]['unique'][] = $key;
    2065. 

  2065. 			}
    2066. 

  2066. 			
    2067. 

  2067. 			// Get all CREATE UNIQUE INDEX statements
    2068. 

  2068. 			$result = $this->database->query("SELECT sql FROM sqlite_master WHERE type = 'index' AND sql <> '' AND tbl_name = %s", $table);
    2069. 

  2069. 			foreach ($result as $row) {
    2070. 

  2070. 				$create_sql = $row['sql'];
    2071. 

  2071. 				if (!preg_match('#^\s*CREATE\s+UNIQUE\s+INDEX\s+(?:\w+\.)?\w+\s+ON\s+\w+\s*\(\s*((?:\s*["`\[]?\w+["`\]]?\s*,\s*)*["`\[]?\w+["`\]]?)\s*\)$#Di', $create_sql, $match)) {
    2072. 

  2072. 					continue;
    2073. 

  2073. 				}
    2074. 

  2074. 				$columns = preg_split('#\s*,\s*#', $match[1]);
    2075. 

  2075. 				$key = array();
    2076. 

  2076. 				foreach ($columns as $column) {
    2077. 

  2077. 					$key[] = str_replace(array('[', '"', '`', ']'), '', $column);
    2078. 

  2078. 				}
    2079. 

  2079. 				$keys[$table]['unique'][] = $key;
    2080. 

  2080. 			}
    2081. 

  2081. 		}
    2082. 

  2082. 		
    2083. 

  2083. 		return $keys;
    2084. 

  2084. 	}
    2085. 

  2085. 	
    2086. 

  2086. 	
    2087. 

  2087. 	/**
    2088. 

  2088. 	 * Finds many-to-many relationship for the table specified
    2089. 

  2089. 	 * 
    2090. 

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

  2091. 	 * @return void
    2092. 

  2092. 	 */
    2093. 

  2093. 	private function findManyToManyRelationships($table)
    2094. 

  2094. 	{
    2095. 

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

  2096. 			return;
    2097. 

  2097. 		}
    2098. 

  2098. 		
    2099. 

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

  2100. 		
    2101. 

  2101. 		$temp = array();
    2102. 

  2102. 		$temp['table']               = $key1['foreign_table'];
    2103. 

  2103. 		$temp['column']              = $key1['foreign_column'];
    2104. 

  2104. 		$temp['related_table']       = $key2['foreign_table'];
    2105. 

  2105. 		$temp['related_column']      = $key2['foreign_column'];
    2106. 

  2106. 		$temp['join_table']          = $table;
    2107. 

  2107. 		$temp['join_column']         = $key1['column'];
    2108. 

  2108. 		$temp['join_related_column'] = $key2['column'];
    2109. 

  2109. 		$temp['on_update']           = $key1['on_update'];
    2110. 

  2110. 		$temp['on_delete']           = $key1['on_delete'];
    2111. 

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

  2112. 		
    2113. 

  2113. 		$temp = array();
    2114. 

  2114. 		$temp['table']               = $key2['foreign_table'];
    2115. 

  2115. 		$temp['column']              = $key2['foreign_column'];
    2116. 

  2116. 		$temp['related_table']       = $key1['foreign_table'];
    2117. 

  2117. 		$temp['related_column']      = $key1['foreign_column'];
    2118. 

  2118. 		$temp['join_table']          = $table;
    2119. 

  2119. 		$temp['join_column']         = $key2['column'];
    2120. 

  2120. 		$temp['join_related_column'] = $key1['column'];
    2121. 

  2121. 		$temp['on_update']           = $key2['on_update'];
    2122. 

  2122. 		$temp['on_delete']           = $key2['on_delete'];
    2123. 

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

  2124. 	}
    2125. 

  2125. 	
    2126. 

  2126. 	
    2127. 

  2127. 	/**
    2128. 

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

  2129. 	 * 
    2130. 

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

  2131. 	 * @return void
    2132. 

  2132. 	 */
    2133. 

  2133. 	private function findOneToManyRelationships($table)
    2134. 

  2134. 	{
    2135. 

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

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

  2137. 			$temp = array();
    2138. 

  2138. 			$temp['table']          = $key['foreign_table'];
    2139. 

  2139. 			$temp['column']         = $key['foreign_column'];
    2140. 

  2140. 			$temp['related_table']  = $table;
    2141. 

  2141. 			$temp['related_column'] = $key['column'];
    2142. 

  2142. 			$temp['on_delete']      = $key['on_delete'];
    2143. 

  2143. 			$temp['on_update']      = $key['on_update'];
    2144. 

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

  2145. 		}
    2146. 

  2146. 	}
    2147. 

  2147. 	
    2148. 

  2148. 	
    2149. 

  2149. 	/**
    2150. 

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

  2151. 	 * 
    2152. 

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

  2153. 	 * @return void
    2154. 

  2154. 	 */
    2155. 

  2155. 	private function findStarToOneRelationships($table)
    2156. 

  2156. 	{
    2157. 

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

  2158. 			$temp = array();
    2159. 

  2159. 			$temp['table']          = $table;
    2160. 

  2160. 			$temp['column']         = $key['column'];
    2161. 

  2161. 			$temp['related_table']  = $key['foreign_table'];
    2162. 

  2162. 			$temp['related_column'] = $key['foreign_column'];
    2163. 

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

  2164. 			if ($type == 'one-to-one') {
    2165. 

  2165. 				$temp['on_delete'] = $key['on_delete'];
    2166. 

  2166. 				$temp['on_update'] = $key['on_update'];	
    2167. 

  2167. 			}
    2168. 

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

  2169. 		}
    2170. 

  2170. 	}
    2171. 

  2171. 	
    2172. 

  2172. 	
    2173. 

  2173. 	/**
    2174. 

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

  2175. 	 * 
    2176. 

  2176. 	 * @return void
    2177. 

  2177. 	 */
    2178. 

  2178. 	private function findRelationships()
    2179. 

  2179. 	{
    2180. 

  2180. 		$this->relationships = array();
    2181. 

  2181. 		$tables = $this->getTables();
    2182. 

  2182. 		
    2183. 

  2183. 		foreach ($tables as $table) {
    2184. 

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

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

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

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

  2188. 		}
    2189. 

  2189. 		
    2190. 

  2190. 		// Calculate the relationships
    2191. 

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

  2192. 			$this->findManyToManyRelationships($table);
    2193. 

  2193. 			if ($this->isJoiningTable($table)) {
    2194. 

  2194. 				continue;
    2195. 

  2195. 			}
    2196. 

  2196. 			
    2197. 

  2197. 			$this->findStarToOneRelationships($table);
    2198. 

  2198. 			$this->findOneToManyRelationships($table);
    2199. 

  2199. 		}
    2200. 

  2200. 		
    2201. 

  2201. 		if ($this->cache) {
    2202. 

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

  2203. 		}
    2204. 

  2204. 	}
    2205. 

  2205. 	
    2206. 

  2206. 	
    2207. 

  2207. 	/**
    2208. 

  2208. 	 * Returns column information for the table specified
    2209. 

  2209. 	 * 
    2210. 

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

  2211. 	 * 
    2212. 

  2212. 	 * {{{
    2213. 

  2213. 	 * array(
    2214. 

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

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

  2216. 	 *         'placeholder'    => (string)  {fDatabase::escape() placeholder for this data type},
    2217. 

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

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

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

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

  2221. 	 *         'min_value'      => (numeric) {the minimum value for an integer/float field},
    2222. 

  2222. 	 *         'max_value'      => (numeric) {the maximum value for an integer/float field},
    2223. 

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

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

  2225. 	 *         'comment'        => (string)  {the SQL comment/description for the column}
    2226. 

  2226. 	 *     ), ...
    2227. 

  2227. 	 * )
    2228. 

  2228. 	 * }}}
    2229. 

  2229. 	 * 
    2230. 

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

  2231. 	 * 
    2232. 

  2232. 	 * {{{
    2233. 

  2233. 	 * array(
    2234. 

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

  2235. 	 *     'placeholder'    => (string)  {fDatabase::escape() placeholder for this data type},
    2236. 

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

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

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

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

  2240. 	 *     'min_value'      => (fNumber) {the minimum value for an integer/float field},
    2241. 

  2241. 	 *     'max_value'      => (fNumber) {the maximum value for an integer/float field},
    2242. 

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

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

  2244. 	 *     'comment'        => (string)  {the SQL comment/description for the column}
    2245. 

  2245. 	 * )
    2246. 

  2246. 	 * }}}
    2247. 

  2247. 	 * 
    2248. 

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

  2249. 	 * 
    2250. 

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

  2251. 	 * 
    2252. 

  2252. 	 *  - `'varchar'`
    2253. 

  2253. 	 *  - `'char'`
    2254. 

  2254. 	 *  - `'text'`
    2255. 

  2255. 	 *  - `'integer'`
    2256. 

  2256. 	 *  - `'float'`
    2257. 

  2257. 	 *  - `'timestamp'`
    2258. 

  2258. 	 *  - `'date'`
    2259. 

  2259. 	 *  - `'time'`
    2260. 

  2260. 	 *  - `'boolean'`
    2261. 

  2261. 	 *  - `'blob'`
    2262. 

  2262. 	 * 
    2263. 

  2263. 	 * Please note that MySQL reports boolean data types as `tinyint(1)`, so
    2264. 

  2264. 	 * all `tinyint(1)` columns will be listed as `boolean`. This can be fixed
    2265. 

  2265. 	 * by calling: 
    2266. 

  2266. 	 * 
    2267. 

  2267. 	 * {{{
    2268. 

  2268. 	 * #!php
    2269. 

  2269. 	 * $schema->setColumnInfoOverride(
    2270. 

  2270. 	 *     array(
    2271. 

  2271. 	 *         'type'        => 'integer',
    2272. 

  2272. 	 *         'placeholder' => '%i',
    2273. 

  2273. 	 *         'default'     => {default integer},
    2274. 

  2274. 	 *         'min_value'   => new fNumber(-128),
    2275. 

  2275. 	 *         'max_value'   => new fNumber(127)
    2276. 

  2276. 	 *     ),
    2277. 

  2277. 	 *     '{table name}',
    2278. 

  2278. 	 *     '{column name}'
    2279. 

  2279. 	 * );
    2280. 

  2280. 	 * }}}
    2281. 

  2281. 	 * 
    2282. 

  2282. 	 * The `'comment'` element pulls from the database's column comment facility
    2283. 

  2283. 	 * with the exception of MSSQL and SQLite.
    2284. 

  2284. 	 * 
    2285. 

  2285. 	 * For MSSQL, the comment is pulled from the `MS_Description` extended
    2286. 

  2286. 	 * property, which can be added via the `Description` field in SQL Server
    2287. 

  2287. 	 * Management Studio, or via the `sp_addextendedproperty` stored procedure.
    2288. 

  2288. 	 * 
    2289. 

  2289. 	 * For SQLite, the comment is extracted from any SQL comment that is placed
    2290. 

  2290. 	 * at the end of the line on which the column is defined:
    2291. 

  2291. 	 * 
    2292. 

  2292. 	 * {{{
    2293. 

  2293. 	 * #!sql
    2294. 

  2294. 	 * CREATE TABLE users (
    2295. 

  2295. 	 *     user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    2296. 

  2296. 	 *     name VARCHAR(200) NOT NULL -- This is the full name
    2297. 

  2297. 	 * );
    2298. 

  2298. 	 * }}}
    2299. 

  2299. 	 * 
    2300. 

  2300. 	 * For the SQLite `users` table defined above, the `name` column will have
    2301. 

  2301. 	 * the comment `This is the full name`.
    2302. 

  2302. 	 * 
    2303. 

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

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

  2305. 	 * @param  string $element  The element to return: `'type'`, `'placeholder'`, `'not_null'`, `'default'`, `'valid_values'`, `'max_length'`, `'min_value'`, `'max_value'`, `'decimal_places'`, `'auto_increment'`, `'comment'`
    2306. 

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

  2307. 	 */
    2308. 

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

  2309. 	{
    2310. 

  2310. 		// Return the saved column info if possible
    2311. 

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

  2312. 			return $this->merged_column_info[$table];
    2313. 

  2313. 		}
    2314. 

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

  2315. 			if ($element !== NULL) {
    2316. 

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

  2317. 					throw new fProgrammerException(
    2318. 

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

  2319. 						$element,
    2320. 

  2320. 						join(', ', array('type', 'placeholder', 'not_null', 'default', 'valid_values', 'max_length', 'min_value', 'max_value', 'decimal_places', 'auto_increment'))
    2321. 

  2321. 					);	
    2322. 

  2322. 				}
    2323. 

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

  2324. 			}
    2325. 

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

  2326. 		}
    2327. 

  2327. 		
    2328. 

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

  2329. 			throw new fProgrammerException(
    2330. 

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

  2331. 				$table
    2332. 

  2332. 			);
    2333. 

  2333. 		}
    2334. 

  2334. 		
    2335. 

  2335. 		$this->fetchColumnInfo($table);
    2336. 

  2336. 		$this->mergeColumnInfo();
    2337. 

  2337. 		
    2338. 

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

  2339. 			throw new fProgrammerException(
    2340. 

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

  2341. 				$column,
    2342. 

  2342. 				$table
    2343. 

  2343. 			);
    2344. 

  2344. 		}
    2345. 

  2345. 		
    2346. 

  2346. 		if ($column) {
    2347. 

  2347. 			if ($element) {
    2348. 

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

  2349. 			}
    2350. 

  2350. 			
    2351. 

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

  2352. 		}
    2353. 

  2353. 		
    2354. 

  2354. 		return $this->merged_column_info[$table];
    2355. 

  2355. 	}
    2356. 

  2356. 	
    2357. 

  2357. 	
    2358. 

  2358. 	/**
    2359. 

  2359. 	 * Returns the databases on the current server
    2360. 

  2360. 	 * 
    2361. 

  2361. 	 * @return array  The databases on the current server
    2362. 

  2362. 	 */
    2363. 

  2363. 	public function getDatabases()
    2364. 

  2364. 	{
    2365. 

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

  2366. 			return $this->databases;
    2367. 

  2367. 		}
    2368. 

  2368. 		
    2369. 

  2369. 		$this->databases = array();
    2370. 

  2370. 		
    2371. 

  2371. 		switch ($this->database->getType()) {
    2372. 

  2372. 			case 'mssql':
    2373. 

  2373. 				$sql = 'EXECUTE sp_databases';
    2374. 

  2374. 				break;
    2375. 

  2375. 			
    2376. 

  2376. 			case 'mysql':
    2377. 

  2377. 				$sql = 'SHOW DATABASES';
    2378. 

  2378. 				break;
    2379. 

  2379. 				
    2380. 

  2380. 			case 'oracle':
    2381. 

  2381. 				$sql = 'SELECT ora_database_name FROM dual';
    2382. 

  2382. 			
    2383. 

  2383. 			case 'postgresql':
    2384. 

  2384. 				$sql = "SELECT
    2385. 

  2385. 								datname
    2386. 

  2386. 							FROM
    2387. 

  2387. 								pg_database
    2388. 

  2388. 							ORDER BY
    2389. 

  2389. 								LOWER(datname)";
    2390. 

  2390. 				break;
    2391. 

  2391. 								
    2392. 

  2392. 			case 'db2':
    2393. 

  2393. 			case 'sqlite':
    2394. 

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

  2395. 				return $this->databases;
    2396. 

  2396. 		}
    2397. 

  2397. 		
    2398. 

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

  2399. 		
    2400. 

  2400. 		foreach ($result as $row) {
    2401. 

  2401. 			$keys = array_keys($row);
    2402. 

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

  2403. 		}
    2404. 

  2404. 		
    2405. 

  2405. 		if ($this->cache) {
    2406. 

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

  2407. 		}
    2408. 

  2408. 		
    2409. 

  2409. 		return $this->databases;
    2410. 

  2410. 	}
    2411. 

  2411. 	
    2412. 

  2412. 	
    2413. 

  2413. 	/**
    2414. 

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

  2415. 	 * 
    2416. 

  2416. 	 * The structure of the returned array is:
    2417. 

  2417. 	 * 
    2418. 

  2418. 	 * {{{
    2419. 

  2419. 	 * array(
    2420. 

  2420. 	 *      'primary' => array(
    2421. 

  2421. 	 *          {column name}, ...
    2422. 

  2422. 	 *      ),
    2423. 

  2423. 	 *      'unique'  => array(
    2424. 

  2424. 	 *          array(
    2425. 

  2425. 	 *              {column name}, ...
    2426. 

  2426. 	 *          ), ...
    2427. 

  2427. 	 *      ),
    2428. 

  2428. 	 *      'foreign' => array(
    2429. 

  2429. 	 *          array(
    2430. 

  2430. 	 *              'column'         => {column name},
    2431. 

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

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

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

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

  2435. 	 *          ), ...
    2436. 

  2436. 	 *      )
    2437. 

  2437. 	 * )
    2438. 

  2438. 	 * }}}
    2439. 

  2439. 	 * 
    2440. 

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

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

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

  2443. 	 */
    2444. 

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

  2445. 	{
    2446. 

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

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

  2448. 			throw new fProgrammerException(
    2449. 

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

  2450. 				$key_type,
    2451. 

  2451. 				join(', ', $valid_key_types)
    2452. 

  2452. 			);
    2453. 

  2453. 		}
    2454. 

  2454. 		
    2455. 

  2455. 		// Return the saved column info if possible
    2456. 

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

  2457. 			return $this->merged_keys[$table];
    2458. 

  2458. 		}
    2459. 

  2459. 		
    2460. 

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

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

  2462. 		}
    2463. 

  2463. 		
    2464. 

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

  2465. 			throw new fProgrammerException(
    2466. 

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

  2467. 				$table
    2468. 

  2468. 			);
    2469. 

  2469. 		}
    2470. 

  2470. 		
    2471. 

  2471. 		$this->fetchKeys();
    2472. 

  2472. 		$this->mergeKeys();
    2473. 

  2473. 		
    2474. 

  2474. 		if ($key_type) {
    2475. 

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

  2476. 		}
    2477. 

  2477. 		
    2478. 

  2478. 		return $this->merged_keys[$table];
    2479. 

  2479. 	}
    2480. 

  2480. 	
    2481. 

  2481. 	
    2482. 

  2482. 	/**
    2483. 

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

  2484. 	 * 
    2485. 

  2485. 	 * The structure of the returned array is:
    2486. 

  2486. 	 * 
    2487. 

  2487. 	 * {{{
    2488. 

  2488. 	 * array(
    2489. 

  2489. 	 *     'one-to-one' => array(
    2490. 

  2490. 	 *         array(
    2491. 

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

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

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

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

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

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

  2497. 	 *         ), ...
    2498. 

  2498. 	 *     ),
    2499. 

  2499. 	 *     'many-to-one' => array(
    2500. 

  2500. 	 *         array(
    2501. 

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

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

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

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

  2505. 	 *         ), ...
    2506. 

  2506. 	 *     ),
    2507. 

  2507. 	 *     'one-to-many' => array(
    2508. 

  2508. 	 *         array(
    2509. 

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

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

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

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

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

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

  2515. 	 *         ), ...
    2516. 

  2516. 	 *     ),
    2517. 

  2517. 	 *     'many-to-many' => array(
    2518. 

  2518. 	 *         array(
    2519. 

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

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

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

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

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

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

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

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

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

  2528. 	 *         ), ...
    2529. 

  2529. 	 *     )
    2530. 

  2530. 	 * )
    2531. 

  2531. 	 * }}}
    2532. 

  2532. 	 * 
    2533. 

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

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

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

  2536. 	 */
    2537. 

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

  2538. 	{
    2539. 

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

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

  2541. 			throw new fProgrammerException(
    2542. 

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

  2543. 				$relationship_type,
    2544. 

  2544. 				join(', ', $valid_relationship_types)
    2545. 

  2545. 			);
    2546. 

  2546. 		}
    2547. 

  2547. 		
    2548. 

  2548. 		// Return the saved column info if possible
    2549. 

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

  2550. 			return $this->relationships[$table];
    2551. 

  2551. 		}
    2552. 

  2552. 		
    2553. 

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

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

  2555. 		}
    2556. 

  2556. 		
    2557. 

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

  2558. 			throw new fProgrammerException(
    2559. 

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

  2560. 				$table
    2561. 

  2561. 			);
    2562. 

  2562. 		}
    2563. 

  2563. 		
    2564. 

  2564. 		$this->fetchKeys();
    2565. 

  2565. 		$this->mergeKeys();
    2566. 

  2566. 		
    2567. 

  2567. 		if ($relationship_type) {
    2568. 

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

  2569. 		}
    2570. 

  2570. 		
    2571. 

  2571. 		return $this->relationships[$table];
    2572. 

  2572. 	}
    2573. 

  2573. 	
    2574. 

  2574. 	
    2575. 

  2575. 	/**
    2576. 

  2576. 	 * Returns the tables in the current database
    2577. 

  2577. 	 * 
    2578. 

  2578. 	 * @return array  The tables in the current database
    2579. 

  2579. 	 */
    2580. 

  2580. 	public function getTables()
    2581. 

  2581. 	{
    2582. 

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

  2583. 			return $this->tables;
    2584. 

  2584. 		}
    2585. 

  2585. 		
    2586. 

  2586. 		switch ($this->database->getType()) {
    2587. 

  2587. 			case 'db2':
    2588. 

  2588. 				$sql = "SELECT
    2589. 

  2589. 								LOWER(RTRIM(TABSCHEMA)) AS \"schema\",
    2590. 

  2590. 								LOWER(TABNAME) AS \"table\"
    2591. 

  2591. 							FROM
    2592. 

  2592. 								SYSCAT.TABLES
    2593. 

  2593. 							WHERE
    2594. 

  2594. 								TYPE = 'T' AND
    2595. 

  2595. 								TABSCHEMA != 'SYSIBM' AND
    2596. 

  2596. 								DEFINER != 'SYSIBM' AND
    2597. 

  2597. 								TABSCHEMA != 'SYSTOOLS' AND
    2598. 

  2598. 								DEFINER != 'SYSTOOLS'
    2599. 

  2599. 							ORDER BY
    2600. 

  2600. 								LOWER(TABNAME)";
    2601. 

  2601. 				break;
    2602. 

  2602. 				
    2603. 

  2603. 			case 'mssql':
    2604. 

  2604. 				$sql = "SELECT
    2605. 

  2605. 								TABLE_SCHEMA AS \"schema\",
    2606. 

  2606. 								TABLE_NAME AS \"table\"
    2607. 

  2607. 							FROM
    2608. 

  2608. 								INFORMATION_SCHEMA.TABLES
    2609. 

  2609. 							WHERE
    2610. 

  2610. 								TABLE_NAME != 'sysdiagrams'
    2611. 

  2611. 							ORDER BY
    2612. 

  2612. 								LOWER(TABLE_NAME)";
    2613. 

  2613. 				break;
    2614. 

  2614. 			
    2615. 

  2615. 			case 'mysql':
    2616. 

  2616. 				if (!$this->version) {
    2617. 

  2617. 					$this->version = $this->database->query("SELECT version()")->fetchScalar();
    2618. 

  2618. 					$this->version = substr($this->version, 0, strpos($this->version, '.'));
    2619. 

  2619. 					if ($this->cache) {
    2620. 

  2620. 						$this->cache->set($this->makeCachePrefix() . 'version', $this->version);
    2621. 

  2621. 					}
    2622. 

  2622. 				}
    2623. 

  2623. 				if ($this->version <= 4) {
    2624. 

  2624. 					$sql = 'SHOW TABLES';
    2625. 

  2625. 				} else {
    2626. 

  2626. 					$sql = "SHOW FULL TABLES WHERE table_type = 'BASE TABLE'";	
    2627. 

  2627. 				}
    2628. 

  2628. 				break;
    2629. 

  2629. 			
    2630. 

  2630. 			case 'oracle':
    2631. 

  2631. 				$sql = "SELECT
    2632. 

  2632. 								LOWER(OWNER) AS \"SCHEMA\",
    2633. 

  2633. 								LOWER(TABLE_NAME) AS \"TABLE\"
    2634. 

  2634. 							FROM
    2635. 

  2635. 								ALL_TABLES
    2636. 

  2636. 							WHERE
    2637. 

  2637. 								OWNER NOT IN (
    2638. 

  2638. 									'SYS',
    2639. 

  2639. 									'SYSTEM',
    2640. 

  2640. 									'OUTLN',
    2641. 

  2641. 									'ANONYMOUS',
    2642. 

  2642. 									'AURORA\$ORB\$UNAUTHENTICATED',
    2643. 

  2643. 									'AWR_STAGE',
    2644. 

  2644. 									'CSMIG',
    2645. 

  2645. 									'CTXSYS',
    2646. 

  2646. 									'DBSNMP',
    2647. 

  2647. 									'DIP',
    2648. 

  2648. 									'DMSYS',
    2649. 

  2649. 									'DSSYS',
    2650. 

  2650. 									'EXFSYS',
    2651. 

  2651. 									'FLOWS_020100',
    2652. 

  2652. 									'FLOWS_FILES',
    2653. 

  2653. 									'LBACSYS',
    2654. 

  2654. 									'MDSYS',
    2655. 

  2655. 									'ORACLE_OCM',
    2656. 

  2656. 									'ORDPLUGINS',
    2657. 

  2657. 									'ORDSYS',
    2658. 

  2658. 									'PERFSTAT',
    2659. 

  2659. 									'TRACESVR',
    2660. 

  2660. 									'TSMSYS',
    2661. 

  2661. 									'XDB'
    2662. 

  2662. 								) AND
    2663. 

  2663. 								DROPPED = 'NO' 
    2664. 

  2664. 							ORDER BY
    2665. 

  2665. 								TABLE_NAME ASC";
    2666. 

  2666. 				break;
    2667. 

  2667. 			
    2668. 

  2668. 			case 'postgresql':
    2669. 

  2669. 				$sql = "SELECT
    2670. 

  2670. 								 schemaname AS \"schema\",
    2671. 

  2671. 								 tablename as \"table\"
    2672. 

  2672. 							FROM
    2673. 

  2673. 								 pg_tables
    2674. 

  2674. 							WHERE
    2675. 

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

  2676. 							ORDER BY
    2677. 

  2677. 								LOWER(tablename)";
    2678. 

  2678. 				break;
    2679. 

  2679. 								
    2680. 

  2680. 			case 'sqlite':
    2681. 

  2681. 				$sql = "SELECT
    2682. 

  2682. 								name
    2683. 

  2683. 							FROM
    2684. 

  2684. 								sqlite_master
    2685. 

  2685. 							WHERE
    2686. 

  2686. 								type = 'table' AND
    2687. 

  2687. 								name NOT LIKE 'sqlite_%'
    2688. 

  2688. 							ORDER BY
    2689. 

  2689. 								name ASC";
    2690. 

  2690. 				break;
    2691. 

  2691. 		}
    2692. 

  2692. 		
    2693. 

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

  2694. 		
    2695. 

  2695. 		$this->tables = array();
    2696. 

  2696. 		
    2697. 

  2697. 		// For databases with schemas we only include the schema
    2698. 

  2698. 		// name if there are conflicting table names
    2699. 

  2699. 		if (!in_array($this->database->getType(), array('mysql', 'sqlite'))) {
    2700. 

  2700. 			
    2701. 

  2701. 			$default_schema_map = array(
    2702. 

  2702. 				'db2'        => strtolower($this->database->getUsername()),
    2703. 

  2703. 				'mssql'      => 'dbo',
    2704. 

  2704. 				'oracle'     => strtolower($this->database->getUsername()),
    2705. 

  2705. 				'postgresql' => 'public'
    2706. 

  2706. 			);
    2707. 

  2707. 			
    2708. 

  2708. 			$default_schema = $default_schema_map[$this->database->getType()];
    2709. 

  2709. 			
    2710. 

  2710. 			foreach ($result as $row) {
    2711. 

  2711. 				if ($row['schema'] == $default_schema) {
    2712. 

  2712. 					$this->tables[] = $row['table'];	
    2713. 

  2713. 				} else {
    2714. 

  2714. 					$this->tables[] = $row['schema'] . '.' . $row['table'];	
    2715. 

  2715. 				}
    2716. 

  2716. 			}
    2717. 

  2717. 				
    2718. 

  2718. 		// SQLite and MySQL don't support schemas
    2719. 

  2719. 		} else {
    2720. 

  2720. 			foreach ($result as $row) {
    2721. 

  2721. 				$keys = array_keys($row);
    2722. 

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

  2723. 			}
    2724. 

  2724. 		}
    2725. 

  2725. 		
    2726. 

  2726. 		sort($this->tables);
    2727. 

  2727. 		
    2728. 

  2728. 		if ($this->cache) {
    2729. 

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

  2730. 		}
    2731. 

  2731. 		
    2732. 

  2732. 		return $this->tables;
    2733. 

  2733. 	}
    2734. 

  2734. 		
    2735. 

  2735. 	
    2736. 

  2736. 	/**
    2737. 

  2737. 	 * Determines if a table is a joining table
    2738. 

  2738. 	 * 
    2739. 

  2739. 	 * @param  string $table  The table to check
    2740. 

  2740. 	 * @return boolean  If the table is a joining table
    2741. 

  2741. 	 */
    2742. 

  2742. 	private function isJoiningTable($table)
    2743. 

  2743. 	{
    2744. 

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

  2745. 		
    2746. 

  2746. 		if (sizeof($primary_key_columns) != 2) {
    2747. 

  2747. 			return FALSE;	
    2748. 

  2748. 		}
    2749. 

  2749. 		
    2750. 

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

  2751. 			$this->getColumnInfo($table);	
    2752. 

  2752. 		}
    2753. 

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

  2754. 			return FALSE;	
    2755. 

  2755. 		}
    2756. 

  2756. 		
    2757. 

  2757. 		$foreign_key_columns = array();
    2758. 

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

  2759. 			$foreign_key_columns[] = $key['column'];
    2760. 

  2760. 		}
    2761. 

  2761. 		
    2762. 

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

  2763. 	}
    2764. 

  2764. 	
    2765. 

  2765. 	
    2766. 

  2766. 	/**
    2767. 

  2767. 	 * Creates a unique cache prefix to help prevent cache conflicts
    2768. 

  2768. 	 * 
    2769. 

  2769. 	 * @return string  The cache prefix to use
    2770. 

  2770. 	 */
    2771. 

  2771. 	private function makeCachePrefix()
    2772. 

  2772. 	{
    2773. 

  2773. 		if (!$this->cache_prefix) {
    2774. 

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

  2775. 			if ($this->database->getHost()) {
    2776. 

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

  2777. 			}
    2778. 

  2778. 			if ($this->database->getPort()) {
    2779. 

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

  2780. 			}
    2781. 

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

  2782. 			if ($this->database->getUsername()) {
    2783. 

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

  2784. 			}
    2785. 

  2785. 			$this->cache_prefix = $prefix;
    2786. 

  2786. 		}
    2787. 

  2787. 		
    2788. 

  2788. 		return $this->cache_prefix;
    2789. 

  2789. 	}
    2790. 

  2790. 	
    2791. 

  2791. 	
    2792. 

  2792. 	/**
    2793. 

  2793. 	 * Merges the column info with the column info override
    2794. 

  2794. 	 * 
    2795. 

  2795. 	 * @return void
    2796. 

  2796. 	 */
    2797. 

  2797. 	private function mergeColumnInfo()
    2798. 

  2798. 	{
    2799. 

  2799. 		$this->merged_column_info = $this->column_info;
    2800. 

  2800. 		
    2801. 

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

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

  2803. 			if ($columns === NULL) {
    2804. 

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

  2805. 				continue;	
    2806. 

  2806. 			}
    2807. 

  2807. 			
    2808. 

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

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

  2810. 			}
    2811. 

  2811. 			
    2812. 

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

  2813. 				// Remove a column if it is set to NULL
    2814. 

  2814. 				if ($info === NULL) {
    2815. 

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

  2816. 					continue;
    2817. 

  2817. 				}
    2818. 

  2818. 				
    2819. 

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

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

  2821. 				}
    2822. 

  2822. 				
    2823. 

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

  2824. 			}
    2825. 

  2825. 		}
    2826. 

  2826. 		
    2827. 

  2827. 		$optional_elements = array(
    2828. 

  2828. 			'not_null',
    2829. 

  2829. 			'default',
    2830. 

  2830. 			'valid_values',
    2831. 

  2831. 			'max_length',
    2832. 

  2832. 			'max_value',
    2833. 

  2833. 			'min_value',
    2834. 

  2834. 			'decimal_places',
    2835. 

  2835. 			'auto_increment',
    2836. 

  2836. 			'comment'
    2837. 

  2837. 		);
    2838. 

  2838. 		
    2839. 

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

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

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

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

  2843. 				}
    2844. 

  2844. 				
    2845. 

  2845. 				if (empty($this->merged_column_info[$table][$column]['placeholder'])) {
    2846. 

  2846. 					$this->merged_column_info[$table][$column]['placeholder'] = strtr(
    2847. 

  2847. 						$info['type'],
    2848. 

  2848. 						array(
    2849. 

  2849. 							'blob'      => '%l',
    2850. 

  2850. 							'boolean'   => '%b',
    2851. 

  2851. 							'date'      => '%d',
    2852. 

  2852. 							'float'     => '%f',
    2853. 

  2853. 							'integer'   => '%i',
    2854. 

  2854. 							'char'      => '%s',
    2855. 

  2855. 							'text'      => '%s',
    2856. 

  2856. 							'varchar'   => '%s',
    2857. 

  2857. 							'time'      => '%t',
    2858. 

  2858. 							'timestamp' => '%p'
    2859. 

  2859. 						)
    2860. 

  2860. 					);
    2861. 

  2861. 				}
    2862. 

  2862. 				
    2863. 

  2863. 				foreach ($optional_elements as $element) {
    2864. 

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

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

  2866. 					}
    2867. 

  2867. 				}
    2868. 

  2868. 			}
    2869. 

  2869. 		}
    2870. 

  2870. 		
    2871. 

  2871. 		if ($this->cache) {
    2872. 

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

  2873. 		}
    2874. 

  2874. 	}
    2875. 

  2875. 		
    2876. 

  2876. 	
    2877. 

  2877. 	/**
    2878. 

  2878. 	 * Merges the keys with the keys override
    2879. 

  2879. 	 * 
    2880. 

  2880. 	 * @return void
    2881. 

  2881. 	 */
    2882. 

  2882. 	private function mergeKeys()
    2883. 

  2883. 	{
    2884. 

  2884. 		// Handle the database and override key info
    2885. 

  2885. 		$this->merged_keys = $this->keys;
    2886. 

  2886. 		
    2887. 

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

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

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

  2890. 			}
    2891. 

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

  2892. 		}
    2893. 

  2893. 		
    2894. 

  2894. 		if ($this->cache) {
    2895. 

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

  2896. 		}
    2897. 

  2897. 		
    2898. 

  2898. 		$this->findRelationships();
    2899. 

  2899. 	}
    2900. 

  2900. 	
    2901. 

  2901. 	
    2902. 

  2902. 	/**
    2903. 

  2903. 	 * Allows overriding of column info
    2904. 

  2904. 	 * 
    2905. 

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

  2906. 	 * 
    2907. 

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

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

  2909. 	 * 
    2910. 

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

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

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

  2913. 	 *  - `'type'`
    2914. 

  2914. 	 *  - `'placeholder'`
    2915. 

  2915. 	 *  - `'not_null'`
    2916. 

  2916. 	 *  - `'default'`
    2917. 

  2917. 	 *  - `'valid_values'`
    2918. 

  2918. 	 *  - `'max_length'`
    2919. 

  2919. 	 *  - `'min_value'`
    2920. 

  2920. 	 *  - `'max_value'`
    2921. 

  2921. 	 *  - `'decimal_places'`
    2922. 

  2922. 	 *  - `'auto_increment'`
    2923. 

  2923. 	 *  - `'comment'`
    2924. 

  2924. 	 * 
    2925. 

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

  2926. 	 *  - `'not_null'`
    2927. 

  2927. 	 *  - `'default'`
    2928. 

  2928. 	 *  - `'valid_values'`
    2929. 

  2929. 	 *  - `'max_length'`
    2930. 

  2930. 	 *  - `'min_value'`
    2931. 

  2931. 	 *  - `'max_value'`
    2932. 

  2932. 	 *  - `'decimal_places'`
    2933. 

  2933. 	 *  - `'comment'`
    2934. 

  2934. 	 *  
    2935. 

  2935. 	 * The key `'auto_increment'` should be a boolean.
    2936. 

  2936. 	 * 
    2937. 

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

  2938. 	 *  - `'blob'`
    2939. 

  2939. 	 *  - `'boolean'`
    2940. 

  2940. 	 *  - `'char'`
    2941. 

  2941. 	 *  - `'date'`
    2942. 

  2942. 	 *  - `'float'`
    2943. 

  2943. 	 *  - `'integer'`
    2944. 

  2944. 	 *  - `'text'`
    2945. 

  2945. 	 *  - `'time'`
    2946. 

  2946. 	 *  - `'timestamp'`
    2947. 

  2947. 	 *  - `'varchar'`
    2948. 

  2948. 	 * 
    2949. 

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

  2950. 	 * @param  string $table        The table to override
    2951. 

  2951. 	 * @param  string $column       The column to override
    2952. 

  2952. 	 * @return void
    2953. 

  2953. 	 */
    2954. 

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

  2955. 	{
    2956. 

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

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

  2958. 		}
    2959. 

  2959. 		
    2960. 

  2960. 		if (!empty($column)) {
    2961. 

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

  2962. 		} else {
    2963. 

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

  2964. 		}
    2965. 

  2965. 		
    2966. 

  2966. 		$this->fetchColumnInfo($table);
    2967. 

  2967. 		$this->mergeColumnInfo();
    2968. 

  2968. 	}
    2969. 

  2969. 	
    2970. 

  2970. 	
    2971. 

  2971. 	/**
    2972. 

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

  2973. 	 * 
    2974. 

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

  2975. 	 * @param  string $table     The table to override
    2976. 

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

  2977. 	 * @return void
    2978. 

  2978. 	 */
    2979. 

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

  2980. 	{
    2981. 

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

  2982. 		if (!in_array($key_type, $valid_key_types)) {
    2983. 

  2983. 			throw new fProgrammerException(
    2984. 

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

  2985. 				$key_type,
    2986. 

  2986. 				join(', ', $valid_key_types)
    2987. 

  2987. 			);
    2988. 

  2988. 		}
    2989. 

  2989. 		
    2990. 

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

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

  2992. 		}
    2993. 

  2993. 		
    2994. 

  2994. 		if (!empty($key_type)) {
    2995. 

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

  2996. 		} else {
    2997. 

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

  2998. 		}
    2999. 

  2999. 		
    3000. 

  3000. 		$this->fetchKeys();
    3001. 

  3001. 		$this->mergeKeys();
    3002. 

  3002. 	}
    3003. 

  3003. }
    3004. 

  3004. 

  3005. 

  3006. 

  3007. /**
    3008. 

  3008.  * Copyright (c) 2007-2011 Will Bond <will@flourishlib.com>
    3009. 

  3009.  * 
    3010. 

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

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

  3012.  * in the Software without restriction, including without limitation the rights
    3013. 

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

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

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

  3016.  * 
    3017. 

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

  3018.  * all copies or substantial portions of the Software.
    3019. 

  3019.  * 
    3020. 

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

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

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

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

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

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

  3026.  * THE SOFTWARE.
    3027. 

  3027.  */
    3028.