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

/classphp/flourish/fSchema.php

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

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

  1. <?php
  2. /**
  3. * Gets schema information for the selected database
  4. *
  5. * @copyright Copyright (c) 2007-2009 Will Bond
  6. * @author Will Bond [wb] <will@flourishlib.com>
  7. * @license http://flourishlib.com/license
  8. *
  9. * @package Flourish
  10. * @link http://flourishlib.com/fSchema
  11. *
  12. * @version 1.0.0b25
  13. * @changes 1.0.0b25 One-to-one relationships utilizing the primary key as a foreign key are now properly detected [wb, 2009-09-22]
  14. * @changes 1.0.0b24 Fixed MSSQL support to work with ODBC database connections [wb, 2009-09-18]
  15. * @changes 1.0.0b23 Fixed a bug where one-to-one relationships were being listed as many-to-one [wb, 2009-07-21]
  16. * @changes 1.0.0b22 PostgreSQL UNIQUE constraints that are created as indexes and not table constraints are now properly detected [wb, 2009-07-08]
  17. * @changes 1.0.0b21 Added support for the UUID data type in PostgreSQL [wb, 2009-06-18]
  18. * @changes 1.0.0b20 Add caching of merged info, improved performance of ::getColumnInfo() [wb, 2009-06-15]
  19. * @changes 1.0.0b19 Fixed a couple of bugs with ::setKeysOverride() [wb, 2009-06-04]
  20. * @changes 1.0.0b18 Added missing support for MySQL mediumint columns [wb, 2009-05-18]
  21. * @changes 1.0.0b17 Fixed a bug with ::clearCache() not properly reseting the tables and databases list [wb, 2009-05-13]
  22. * @changes 1.0.0b16 Backwards Compatibility Break - ::setCacheFile() changed to ::enableCaching() and now requires an fCache object, ::flushInfo() renamed to ::clearCache(), added Oracle support [wb, 2009-05-04]
  23. * @changes 1.0.0b15 Added support for the three different types of identifier quoting in SQLite [wb, 2009-03-28]
  24. * @changes 1.0.0b14 Added support for MySQL column definitions containing the COLLATE keyword [wb, 2009-03-28]
  25. * @changes 1.0.0b13 Fixed a bug with detecting PostgreSQL columns having both a CHECK constraint and a UNIQUE constraint [wb, 2009-02-27]
  26. * @changes 1.0.0b12 Fixed detection of multi-column primary keys in MySQL [wb, 2009-02-27]
  27. * @changes 1.0.0b11 Fixed an issue parsing MySQL tables with comments [wb, 2009-02-25]
  28. * @changes 1.0.0b10 Added the ::getDatabases() method [wb, 2009-02-24]
  29. * @changes 1.0.0b9 Now detects unsigned and zerofill MySQL data types that do not have a parenthetical part [wb, 2009-02-16]
  30. * @changes 1.0.0b8 Mapped the MySQL data type `'set'` to `'varchar'`, however valid values are not implemented yet [wb, 2009-02-01]
  31. * @changes 1.0.0b7 Fixed a bug with detecting MySQL timestamp columns [wb, 2009-01-28]
  32. * @changes 1.0.0b6 Fixed a bug with detecting MySQL columns that accept `NULL` [wb, 2009-01-19]
  33. * @changes 1.0.0b5 ::setColumnInfo(): fixed a bug with not grabbing the real database schema first, made general improvements [wb, 2009-01-19]
  34. * @changes 1.0.0b4 Added support for MySQL binary data types, numeric data type options unsigned and zerofill, and per-column character set definitions [wb, 2009-01-17]
  35. * @changes 1.0.0b3 Fixed detection of the data type of MySQL timestamp columns, added support for dynamic default date/time values [wb, 2009-01-11]
  36. * @changes 1.0.0b2 Fixed a bug with detecting multi-column unique keys in MySQL [wb, 2009-01-03]
  37. * @changes 1.0.0b The initial implementation [wb, 2007-09-25]
  38. */
  39. class fSchema
  40. {
  41. /**
  42. * The place to cache to
  43. *
  44. * @var fCache
  45. */
  46. private $cache = NULL;
  47. /**
  48. * The cached column info
  49. *
  50. * @var array
  51. */
  52. private $column_info = array();
  53. /**
  54. * The column info to override
  55. *
  56. * @var array
  57. */
  58. private $column_info_override = array();
  59. /**
  60. * A reference to an instance of the fDatabase class
  61. *
  62. * @var fDatabase
  63. */
  64. private $database = NULL;
  65. /**
  66. * The databases on the current database server
  67. *
  68. * @var array
  69. */
  70. private $databases = NULL;
  71. /**
  72. * The cached key info
  73. *
  74. * @var array
  75. */
  76. private $keys = array();
  77. /**
  78. * The key info to override
  79. *
  80. * @var array
  81. */
  82. private $keys_override = array();
  83. /**
  84. * The merged column info
  85. *
  86. * @var array
  87. */
  88. private $merged_column_info = array();
  89. /**
  90. * The merged key info
  91. *
  92. * @var array
  93. */
  94. private $merged_keys = array();
  95. /**
  96. * The relationships in the database
  97. *
  98. * @var array
  99. */
  100. private $relationships = array();
  101. /**
  102. * The tables in the database
  103. *
  104. * @var array
  105. */
  106. private $tables = NULL;
  107. /**
  108. * Sets the database
  109. *
  110. * @param fDatabase $database The fDatabase instance
  111. * @return fSchema
  112. */
  113. public function __construct($database)
  114. {
  115. $this->database = $database;
  116. }
  117. /**
  118. * All requests that hit this method should be requests for callbacks
  119. *
  120. * @internal
  121. *
  122. * @param string $method The method to create a callback for
  123. * @return callback The callback for the method requested
  124. */
  125. public function __get($method)
  126. {
  127. return array($this, $method);
  128. }
  129. /**
  130. * Checks to see if a column is part of a single-column `UNIQUE` key
  131. *
  132. * @param string $table The table the column is located in
  133. * @param string $column The column to check
  134. * @return boolean If the column is part of a single-column unique key
  135. */
  136. private function checkForSingleColumnUniqueKey($table, $column)
  137. {
  138. foreach ($this->merged_keys[$table]['unique'] as $key) {
  139. if (array($column) == $key) {
  140. return TRUE;
  141. }
  142. }
  143. if (array($column) == $this->merged_keys[$table]['primary']) {
  144. return TRUE;
  145. }
  146. return FALSE;
  147. }
  148. /**
  149. * Clears all of the schema info out of the object and, if set, the fCache object
  150. *
  151. * @internal
  152. *
  153. * @return void
  154. */
  155. public function clearCache()
  156. {
  157. $this->column_info = array();
  158. $this->databases = NULL;
  159. $this->keys = array();
  160. $this->merged_column_info = array();
  161. $this->merged_keys = array();
  162. $this->relationships = array();
  163. $this->tables = NULL;
  164. if ($this->cache) {
  165. $prefix = $this->makeCachePrefix();
  166. $this->cache->delete($prefix . 'column_info');
  167. $this->cache->delete($prefix . 'databases');
  168. $this->cache->delete($prefix . 'keys');
  169. $this->cache->delete($prefix . 'merged_column_info');
  170. $this->cache->delete($prefix . 'merged_keys');
  171. $this->cache->delete($prefix . 'relationships');
  172. $this->cache->delete($prefix . 'tables');
  173. }
  174. }
  175. /**
  176. * Sets the schema to be cached to the fCache object specified
  177. *
  178. * @param fCache $cache The cache to cache to
  179. * @return void
  180. */
  181. public function enableCaching($cache)
  182. {
  183. $this->cache = $cache;
  184. $prefix = $this->makeCachePrefix();
  185. $this->column_info = $this->cache->get($prefix . 'column_info', array());
  186. $this->databases = $this->cache->get($prefix . 'databases', NULL);
  187. $this->keys = $this->cache->get($prefix . 'keys', array());
  188. if (!$this->column_info_override && !$this->keys_override) {
  189. $this->merged_column_info = $this->cache->get($prefix . 'merged_column_info', array());
  190. $this->merged_keys = $this->cache->get($prefix . 'merged_keys', array());
  191. $this->relationships = $this->cache->get($prefix . 'relationships', array());
  192. }
  193. $this->tables = $this->cache->get($prefix . 'tables', NULL);
  194. }
  195. /**
  196. * Gets the column info from the database for later access
  197. *
  198. * @param string $table The table to fetch the column info for
  199. * @return void
  200. */
  201. private function fetchColumnInfo($table)
  202. {
  203. if (isset($this->column_info[$table])) {
  204. return;
  205. }
  206. switch ($this->database->getType()) {
  207. case 'mssql':
  208. $column_info = $this->fetchMSSQLColumnInfo($table);
  209. break;
  210. case 'mysql':
  211. $column_info = $this->fetchMySQLColumnInfo($table);
  212. break;
  213. case 'oracle':
  214. $column_info = $this->fetchOracleColumnInfo($table);
  215. break;
  216. case 'postgresql':
  217. $column_info = $this->fetchPostgreSQLColumnInfo($table);
  218. break;
  219. case 'sqlite':
  220. $column_info = $this->fetchSQLiteColumnInfo($table);
  221. break;
  222. }
  223. if (!$column_info) {
  224. return;
  225. }
  226. $this->column_info[$table] = $column_info;
  227. if ($this->cache) {
  228. $this->cache->set($this->makeCachePrefix() . 'column_info', $this->column_info);
  229. }
  230. }
  231. /**
  232. * Gets the `PRIMARY KEY`, `FOREIGN KEY` and `UNIQUE` key constraints from the database
  233. *
  234. * @return void
  235. */
  236. private function fetchKeys()
  237. {
  238. if ($this->keys) {
  239. return;
  240. }
  241. switch ($this->database->getType()) {
  242. case 'mssql':
  243. $keys = $this->fetchMSSQLKeys();
  244. break;
  245. case 'mysql':
  246. $keys = $this->fetchMySQLKeys();
  247. break;
  248. case 'oracle':
  249. $keys = $this->fetchOracleKeys();
  250. break;
  251. case 'postgresql':
  252. $keys = $this->fetchPostgreSQLKeys();
  253. break;
  254. case 'sqlite':
  255. $keys = $this->fetchSQLiteKeys();
  256. break;
  257. }
  258. $this->keys = $keys;
  259. if ($this->cache) {
  260. $this->cache->set($this->makeCachePrefix() . 'keys', $this->keys);
  261. }
  262. }
  263. /**
  264. * Gets the column info from a MSSQL database
  265. *
  266. * The returned array is in the format:
  267. *
  268. * {{{
  269. * array(
  270. * (string) {column name} => array(
  271. * 'type' => (string) {data type},
  272. * 'not_null' => (boolean) {if value can't be null},
  273. * 'default' => (mixed) {the default value-may contain special string CURRENT_TIMESTAMP},
  274. * 'valid_values' => (array) {the valid values for a char/varchar field},
  275. * 'max_length' => (integer) {the maximum length in a char/varchar field},
  276. * 'decimal_places' => (integer) {the number of decimal places for a decimal/numeric/money/smallmoney field},
  277. * 'auto_increment' => (boolean) {if the integer primary key column is an identity column}
  278. * ), ...
  279. * )
  280. * }}}
  281. *
  282. * @param string $table The table to fetch the column info for
  283. * @return array The column info for the table specified - see method description for details
  284. */
  285. private function fetchMSSQLColumnInfo($table)
  286. {
  287. $column_info = array();
  288. $data_type_mapping = array(
  289. 'bit' => 'boolean',
  290. 'tinyint' => 'integer',
  291. 'smallint' => 'integer',
  292. 'int' => 'integer',
  293. 'bigint' => 'integer',
  294. 'datetime' => 'timestamp',
  295. 'smalldatetime' => 'timestamp',
  296. 'datetime2' => 'timestamp',
  297. 'date' => 'date',
  298. 'time' => 'time',
  299. 'varchar' => 'varchar',
  300. 'nvarchar' => 'varchar',
  301. 'char' => 'char',
  302. 'nchar' => 'char',
  303. 'real' => 'float',
  304. 'float' => 'float',
  305. 'money' => 'float',
  306. 'smallmoney' => 'float',
  307. 'decimal' => 'float',
  308. 'numeric' => 'float',
  309. 'binary' => 'blob',
  310. 'varbinary' => 'blob',
  311. 'image' => 'blob',
  312. 'text' => 'text',
  313. 'ntext' => 'text'
  314. );
  315. // Get the column info
  316. $sql = "SELECT
  317. c.column_name AS 'column',
  318. c.data_type AS 'type',
  319. c.is_nullable AS nullable,
  320. c.column_default AS 'default',
  321. c.character_maximum_length AS max_length,
  322. c.numeric_scale AS decimal_places,
  323. CASE
  324. WHEN
  325. COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND
  326. OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0
  327. THEN '1'
  328. ELSE '0'
  329. END AS auto_increment,
  330. cc.check_clause AS 'constraint'
  331. FROM
  332. INFORMATION_SCHEMA.COLUMNS AS c LEFT JOIN
  333. 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
  334. INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON ccu.constraint_name = cc.constraint_name AND ccu.constraint_catalog = cc.constraint_catalog
  335. WHERE
  336. c.table_name = '" . $table . "' AND
  337. c.table_catalog = DB_NAME()";
  338. $result = $this->database->query($sql);
  339. foreach ($result as $row) {
  340. $info = array();
  341. foreach ($data_type_mapping as $data_type => $mapped_data_type) {
  342. if (stripos($row['type'], $data_type) === 0) {
  343. $info['type'] = $mapped_data_type;
  344. break;
  345. }
  346. }
  347. if (!isset($info['type'])) {
  348. $info['type'] = $row['type'];
  349. }
  350. // Handle decimal places for numeric/decimals
  351. if (in_array($row['type'], array('numeric', 'decimal'))) {
  352. $info['decimal_places'] = $row['decimal_places'];
  353. }
  354. // Handle decimal places for money/smallmoney
  355. if (in_array($row['type'], array('money', 'smallmoney'))) {
  356. $info['decimal_places'] = 2;
  357. }
  358. // Handle the special data for varchar columns
  359. if (in_array($info['type'], array('char', 'varchar'))) {
  360. $info['max_length'] = $row['max_length'];
  361. }
  362. // If the column has a constraint, look for valid values
  363. if (in_array($info['type'], array('char', 'varchar')) && !empty($row['constraint'])) {
  364. if (preg_match('#^\(((?:(?: OR )?\[[^\]]+\]\s*=\s*\'(?:\'\'|[^\']+)+\')+)\)$#D', $row['constraint'], $matches)) {
  365. $valid_values = explode(' OR ', $matches[1]);
  366. foreach ($valid_values as $key => $value) {
  367. $value = preg_replace('#^\s*\[' . preg_quote($row['column'], '#') . '\]\s*=\s*\'(.*)\'\s*$#', '\1', $value);
  368. $valid_values[$key] = str_replace("''", "'", $value);
  369. }
  370. // SQL Server turns CHECK constraint values into a reversed list, so we fix it here
  371. $info['valid_values'] = array_reverse($valid_values);
  372. }
  373. }
  374. // Handle auto increment
  375. if ($row['auto_increment']) {
  376. $info['auto_increment'] = TRUE;
  377. }
  378. // Handle default values
  379. if ($row['default'] !== NULL) {
  380. if ($row['default'] == '(getdate())') {
  381. $info['default'] = 'CURRENT_TIMESTAMP';
  382. } elseif (in_array($info['type'], array('char', 'varchar', 'text', 'timestamp')) ) {
  383. $info['default'] = substr($row['default'], 2, -2);
  384. } elseif ($info['type'] == 'boolean') {
  385. $info['default'] = (boolean) substr($row['default'], 2, -2);
  386. } elseif (in_array($info['type'], array('integer', 'float')) ) {
  387. $info['default'] = str_replace(array('(', ')'), '', $row['default']);
  388. } else {
  389. $info['default'] = pack('H*', substr($row['default'], 3, -1));
  390. }
  391. }
  392. // Handle not null
  393. $info['not_null'] = ($row['nullable'] == 'NO') ? TRUE : FALSE;
  394. $column_info[$row['column']] = $info;
  395. }
  396. return $column_info;
  397. }
  398. /**
  399. * Fetches the key info for an MSSQL database
  400. *
  401. * The structure of the returned array is:
  402. *
  403. * {{{
  404. * array(
  405. * 'primary' => array(
  406. * {column name}, ...
  407. * ),
  408. * 'unique' => array(
  409. * array(
  410. * {column name}, ...
  411. * ), ...
  412. * ),
  413. * 'foreign' => array(
  414. * array(
  415. * 'column' => {column name},
  416. * 'foreign_table' => {foreign table name},
  417. * 'foreign_column' => {foreign column name},
  418. * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
  419. * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
  420. * ), ...
  421. * )
  422. * )
  423. * }}}
  424. *
  425. * @return array The key info arrays for every table in the database - see method description for details
  426. */
  427. private function fetchMSSQLKeys()
  428. {
  429. $keys = array();
  430. $tables = $this->getTables();
  431. foreach ($tables as $table) {
  432. $keys[$table] = array();
  433. $keys[$table]['primary'] = array();
  434. $keys[$table]['unique'] = array();
  435. $keys[$table]['foreign'] = array();
  436. }
  437. $sql = "SELECT
  438. c.table_name AS 'table',
  439. kcu.constraint_name AS constraint_name,
  440. CASE c.constraint_type
  441. WHEN 'PRIMARY KEY' THEN 'primary'
  442. WHEN 'FOREIGN KEY' THEN 'foreign'
  443. WHEN 'UNIQUE' THEN 'unique'
  444. END AS 'type',
  445. kcu.column_name AS 'column',
  446. ccu.table_name AS foreign_table,
  447. ccu.column_name AS foreign_column,
  448. REPLACE(LOWER(rc.delete_rule), ' ', '_') AS on_delete,
  449. REPLACE(LOWER(rc.update_rule), ' ', '_') AS on_update
  450. FROM
  451. INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN
  452. INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON c.table_name = kcu.table_name AND c.constraint_name = kcu.constraint_name LEFT JOIN
  453. INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON c.constraint_name = rc.constraint_name LEFT JOIN
  454. INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.constraint_name = rc.unique_constraint_name
  455. WHERE
  456. c.constraint_catalog = DB_NAME() AND
  457. c.table_name != 'sysdiagrams'
  458. ORDER BY
  459. LOWER(c.table_name),
  460. c.constraint_type,
  461. LOWER(kcu.constraint_name),
  462. kcu.ordinal_position,
  463. LOWER(kcu.column_name)";
  464. $result = $this->database->query($sql);
  465. $last_name = '';
  466. $last_table = '';
  467. $last_type = '';
  468. foreach ($result as $row) {
  469. if ($row['constraint_name'] != $last_name) {
  470. if ($last_name) {
  471. if ($last_type == 'foreign' || $last_type == 'unique') {
  472. if (!isset($keys[$last_table][$last_type])) {
  473. $keys[$last_table][$last_type] = array();
  474. }
  475. $keys[$last_table][$last_type][] = $temp;
  476. } else {
  477. $keys[$last_table][$last_type] = $temp;
  478. }
  479. }
  480. $temp = array();
  481. if ($row['type'] == 'foreign') {
  482. $temp['column'] = $row['column'];
  483. $temp['foreign_table'] = $row['foreign_table'];
  484. $temp['foreign_column'] = $row['foreign_column'];
  485. $temp['on_delete'] = 'no_action';
  486. $temp['on_update'] = 'no_action';
  487. if (!empty($row['on_delete'])) {
  488. $temp['on_delete'] = $row['on_delete'];
  489. }
  490. if (!empty($row['on_update'])) {
  491. $temp['on_update'] = $row['on_update'];
  492. }
  493. } else {
  494. $temp[] = $row['column'];
  495. }
  496. $last_table = $row['table'];
  497. $last_name = $row['constraint_name'];
  498. $last_type = $row['type'];
  499. } else {
  500. $temp[] = $row['column'];
  501. }
  502. }
  503. if (isset($temp)) {
  504. if ($last_type == 'foreign') {
  505. if (!isset($keys[$last_table][$last_type])) {
  506. $keys[$last_table][$last_type] = array();
  507. }
  508. $keys[$last_table][$last_type][] = $temp;
  509. } else {
  510. $keys[$last_table][$last_type] = $temp;
  511. }
  512. }
  513. return $keys;
  514. }
  515. /**
  516. * Gets the column info from a MySQL database
  517. *
  518. * The returned array is in the format:
  519. *
  520. * {{{
  521. * array(
  522. * (string) {column name} => array(
  523. * 'type' => (string) {data type},
  524. * 'not_null' => (boolean) {if value can't be null},
  525. * 'default' => (mixed) {the default value-may contain special string CURRENT_TIMESTAMP},
  526. * 'valid_values' => (array) {the valid values for a char/varchar field},
  527. * 'max_length' => (integer) {the maximum length in a char/varchar field},
  528. * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
  529. * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
  530. * ), ...
  531. * )
  532. * }}}
  533. *
  534. * @param string $table The table to fetch the column info for
  535. * @return array The column info for the table specified - see method description for details
  536. */
  537. private function fetchMySQLColumnInfo($table)
  538. {
  539. $data_type_mapping = array(
  540. 'tinyint' => 'integer',
  541. 'smallint' => 'integer',
  542. 'mediumint' => 'integer',
  543. 'int' => 'integer',
  544. 'bigint' => 'integer',
  545. 'datetime' => 'timestamp',
  546. 'timestamp' => 'timestamp',
  547. 'date' => 'date',
  548. 'time' => 'time',
  549. 'enum' => 'varchar',
  550. 'set' => 'varchar',
  551. 'varchar' => 'varchar',
  552. 'char' => 'char',
  553. 'float' => 'float',
  554. 'double' => 'float',
  555. 'decimal' => 'float',
  556. 'binary' => 'blob',
  557. 'varbinary' => 'blob',
  558. 'tinyblob' => 'blob',
  559. 'blob' => 'blob',
  560. 'mediumblob' => 'blob',
  561. 'longblob' => 'blob',
  562. 'tinytext' => 'text',
  563. 'text' => 'text',
  564. 'mediumtext' => 'text',
  565. 'longtext' => 'text'
  566. );
  567. $column_info = array();
  568. $result = $this->database->query('SHOW CREATE TABLE ' . $table);
  569. try {
  570. $row = $result->fetchRow();
  571. $create_sql = $row['Create Table'];
  572. } catch (fNoRowsException $e) {
  573. return array();
  574. }
  575. preg_match_all('#(?<=,|\()\s+(?:"|\`)(\w+)(?:"|\`)\s+(?:([a-z]+)(?:\(([^)]+)\))?(?: unsigned| zerofill){0,2})(?: character set [^ ]+)?(?: collate [^ ]+)?(?: NULL)?( NOT NULL)?(?: DEFAULT ((?:[^, \']*|\'(?:\'\'|[^\']+)*\')))?( auto_increment)?( COMMENT \'(?:\'\'|[^\']+)*\')?( ON UPDATE CURRENT_TIMESTAMP)?\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
  576. foreach ($matches as $match) {
  577. $info = array();
  578. foreach ($data_type_mapping as $data_type => $mapped_data_type) {
  579. if (stripos($match[2], $data_type) === 0) {
  580. if ($match[2] == 'tinyint' && $match[3] == 1) {
  581. $mapped_data_type = 'boolean';
  582. }
  583. $info['type'] = $mapped_data_type;
  584. break;
  585. }
  586. }
  587. if (!isset($info['type'])) {
  588. $info['type'] = preg_replace('#^([a-z ]+).*$#iD', '\1', $match[2]);
  589. }
  590. if (stripos($match[2], 'enum') === 0) {
  591. $info['valid_values'] = preg_replace("/^'|'\$/D", '', explode(",", $match[3]));
  592. $match[3] = 0;
  593. foreach ($info['valid_values'] as $valid_value) {
  594. if (strlen(utf8_decode($valid_value)) > $match[3]) {
  595. $match[3] = strlen(utf8_decode($valid_value));
  596. }
  597. }
  598. }
  599. // The set data type is currently only supported as a varchar
  600. // with a max length of all valid values concatenated by ,s
  601. if (stripos($match[2], 'set') === 0) {
  602. $values = preg_replace("/^'|'\$/D", '', explode(",", $match[3]));
  603. $match[3] = strlen(join(',', $values));
  604. }
  605. // Type specific information
  606. if (in_array($info['type'], array('char', 'varchar'))) {
  607. $info['max_length'] = $match[3];
  608. }
  609. // Grab the number of decimal places
  610. if (stripos($match[2], 'decimal') === 0) {
  611. if (preg_match('#^\s*\d+\s*,\s*(\d+)\s*$#D', $match[3], $data_type_info)) {
  612. $info['decimal_places'] = $data_type_info[1];
  613. }
  614. }
  615. // Not null
  616. $info['not_null'] = (!empty($match[4])) ? TRUE : FALSE;
  617. // Default values
  618. if (!empty($match[5]) && $match[5] != 'NULL') {
  619. $info['default'] = preg_replace("/^'|'\$/D", '', $match[5]);
  620. }
  621. if ($info['type'] == 'boolean' && isset($info['default'])) {
  622. $info['default'] = (boolean) $info['default'];
  623. }
  624. // Auto increment fields
  625. if (!empty($match[6])) {
  626. $info['auto_increment'] = TRUE;
  627. }
  628. $column_info[$match[1]] = $info;
  629. }
  630. return $column_info;
  631. }
  632. /**
  633. * Fetches the keys for a MySQL database
  634. *
  635. * The structure of the returned array is:
  636. *
  637. * {{{
  638. * array(
  639. * 'primary' => array(
  640. * {column name}, ...
  641. * ),
  642. * 'unique' => array(
  643. * array(
  644. * {column name}, ...
  645. * ), ...
  646. * ),
  647. * 'foreign' => array(
  648. * array(
  649. * 'column' => {column name},
  650. * 'foreign_table' => {foreign table name},
  651. * 'foreign_column' => {foreign column name},
  652. * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
  653. * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
  654. * ), ...
  655. * )
  656. * )
  657. * }}}
  658. *
  659. * @return array The keys arrays for every table in the database - see method description for details
  660. */
  661. private function fetchMySQLKeys()
  662. {
  663. $tables = $this->getTables();
  664. $keys = array();
  665. foreach ($tables as $table) {
  666. $keys[$table] = array();
  667. $keys[$table]['primary'] = array();
  668. $keys[$table]['foreign'] = array();
  669. $keys[$table]['unique'] = array();
  670. $result = $this->database->query('SHOW CREATE TABLE `' . substr($this->database->escape('string', $table), 1, -1) . '`');
  671. $row = $result->fetchRow();
  672. // Primary keys
  673. preg_match_all('/PRIMARY KEY\s+\("(.*?)"\),?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);
  674. if (!empty($matches)) {
  675. $keys[$table]['primary'] = explode('","', $matches[0][1]);
  676. }
  677. // Unique keys
  678. preg_match_all('/UNIQUE KEY\s+"([^"]+)"\s+\("(.*?)"\),?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);
  679. foreach ($matches as $match) {
  680. $keys[$table]['unique'][] = explode('","', $match[2]);
  681. }
  682. // Foreign keys
  683. 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);
  684. foreach ($matches as $match) {
  685. $temp = array('column' => $match[1],
  686. 'foreign_table' => $match[2],
  687. 'foreign_column' => $match[3],
  688. 'on_delete' => 'no_action',
  689. 'on_update' => 'no_action');
  690. if (isset($match[4])) {
  691. $temp['on_delete'] = strtolower(str_replace(' ', '_', $match[4]));
  692. }
  693. if (isset($match[5])) {
  694. $temp['on_update'] = strtolower(str_replace(' ', '_', $match[5]));
  695. }
  696. $keys[$table]['foreign'][] = $temp;
  697. }
  698. }
  699. return $keys;
  700. }
  701. /**
  702. * Gets the column info from an Oracle database
  703. *
  704. * The returned array is in the format:
  705. *
  706. * {{{
  707. * array(
  708. * (string) {column name} => array(
  709. * 'type' => (string) {data type},
  710. * 'not_null' => (boolean) {if value can't be null},
  711. * 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
  712. * 'valid_values' => (array) {the valid values for a char/varchar field},
  713. * 'max_length' => (integer) {the maximum length in a char/varchar field},
  714. * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
  715. * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
  716. * ), ...
  717. * )
  718. * }}}
  719. *
  720. * @param string $table The table to fetch the column info for
  721. * @return array The column info for the table specified - see method description for details
  722. */
  723. private function fetchOracleColumnInfo($table)
  724. {
  725. $table = strtoupper($table);
  726. $column_info = array();
  727. $data_type_mapping = array(
  728. 'boolean' => 'boolean',
  729. 'integer' => 'integer',
  730. 'timestamp' => 'timestamp',
  731. 'date' => 'date',
  732. 'varchar2' => 'varchar',
  733. 'nvarchar2' => 'varchar',
  734. 'char' => 'char',
  735. 'nchar' => 'char',
  736. 'float' => 'float',
  737. 'binary_float' => 'float',
  738. 'binary_double' => 'float',
  739. 'blob' => 'blob',
  740. 'bfile' => 'varchar',
  741. 'clob' => 'text',
  742. 'nclob' => 'text'
  743. );
  744. $sql = "SELECT
  745. LOWER(UTC.COLUMN_NAME) COLUMN_NAME,
  746. CASE
  747. WHEN
  748. UTC.DATA_TYPE = 'NUMBER' AND
  749. UTC.DATA_PRECISION IS NULL AND
  750. UTC.DATA_SCALE = 0
  751. THEN
  752. 'integer'
  753. WHEN
  754. UTC.DATA_TYPE = 'NUMBER' AND
  755. UTC.DATA_PRECISION = 1 AND
  756. UTC.DATA_SCALE = 0
  757. THEN
  758. 'boolean'
  759. WHEN
  760. UTC.DATA_TYPE = 'NUMBER' AND
  761. UTC.DATA_PRECISION IS NOT NULL AND
  762. UTC.DATA_SCALE != 0 AND
  763. UTC.DATA_SCALE IS NOT NULL
  764. THEN
  765. 'float'
  766. ELSE
  767. LOWER(UTC.DATA_TYPE)
  768. END DATA_TYPE,
  769. CASE
  770. WHEN
  771. UTC.CHAR_LENGTH <> 0
  772. THEN
  773. UTC.CHAR_LENGTH
  774. WHEN
  775. UTC.DATA_TYPE = 'NUMBER' AND
  776. UTC.DATA_PRECISION != 1 AND
  777. UTC.DATA_SCALE != 0 AND
  778. UTC.DATA_PRECISION IS NOT NULL
  779. THEN
  780. UTC.DATA_SCALE
  781. ELSE
  782. NULL
  783. END LENGTH,
  784. UTC.NULLABLE,
  785. UTC.DATA_DEFAULT,
  786. UC.SEARCH_CONDITION CHECK_CONSTRAINT
  787. FROM
  788. USER_TAB_COLUMNS UTC LEFT JOIN
  789. USER_CONS_COLUMNS UCC ON
  790. UTC.COLUMN_NAME = UCC.COLUMN_NAME AND
  791. UTC.TABLE_NAME = UCC.TABLE_NAME AND
  792. UCC.POSITION IS NULL LEFT JOIN
  793. USER_CONSTRAINTS UC ON
  794. UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND
  795. UC.CONSTRAINT_TYPE = 'C' AND
  796. UC.STATUS = 'ENABLED'
  797. WHERE
  798. UTC.TABLE_NAME = %s
  799. ORDER BY
  800. UTC.TABLE_NAME ASC,
  801. UTC.COLUMN_ID ASC";
  802. $result = $this->database->query($sql, $table);
  803. foreach ($result as $row) {
  804. $column = $row['column_name'];
  805. // Since Oracle stores check constraints in LONG columns, it is
  806. // not possible to check or modify the constraints in SQL which
  807. // ends up causing multiple rows with duplicate data except for
  808. // the check constraint
  809. $duplicate = FALSE;
  810. if (isset($column_info[$column])) {
  811. $info = $column_info[$column];
  812. $duplicate = TRUE;
  813. } else {
  814. $info = array();
  815. }
  816. if (!$duplicate) {
  817. // Get the column type
  818. foreach ($data_type_mapping as $data_type => $mapped_data_type) {
  819. if (stripos($row['data_type'], $data_type) === 0) {
  820. $info['type'] = $mapped_data_type;
  821. break;
  822. }
  823. }
  824. if (!isset($info['type'])) {
  825. $info['type'] = $row['data_type'];
  826. }
  827. // Handle the length of decimal/numeric fields
  828. if ($info['type'] == 'float' && $row['length']) {
  829. $info['decimal_places'] = (int) $row['length'];
  830. }
  831. // Handle the special data for varchar fields
  832. if (in_array($info['type'], array('char', 'varchar'))) {
  833. $info['max_length'] = (int) $row['length'];
  834. }
  835. }
  836. // Handle check constraints that are just simple lists
  837. if (in_array($info['type'], array('varchar', 'char')) && $row['check_constraint']) {
  838. if (preg_match('/^\s*' . preg_quote($column, '/') . '\s+in\s+\((.*?)\)\s*$/i', $row['check_constraint'], $match)) {
  839. if (preg_match_all("/(?<!')'((''|[^']+)*)'/", $match[1], $matches, PREG_PATTERN_ORDER)) {
  840. $info['valid_values'] = str_replace("''", "'", $matches[1]);
  841. }
  842. }
  843. }
  844. if (!$duplicate) {
  845. // Handle default values
  846. if ($row['data_default'] !== NULL) {
  847. if (in_array($info['type'], array('char', 'varchar', 'text'))) {
  848. $info['default'] = str_replace("''", "'", substr(trim($row['data_default']), 1, -1));
  849. } elseif ($info['type'] == 'boolean') {
  850. $info['default'] = (boolean) trim($row['data_default']);
  851. } elseif (in_array($info['type'], array('integer', 'float'))) {
  852. $info['default'] = trim($row['data_default']);
  853. } else {
  854. $info['default'] = $row['data_default'];
  855. }
  856. }
  857. // Not null values
  858. $info['not_null'] = ($row['nullable'] == 'N') ? TRUE : FALSE;
  859. }
  860. $column_info[$column] = $info;
  861. }
  862. $sql = "SELECT
  863. TRIGGER_BODY
  864. FROM
  865. USER_TRIGGERS
  866. WHERE
  867. TRIGGERING_EVENT = 'INSERT' AND
  868. STATUS = 'ENABLED' AND
  869. TRIGGER_NAME NOT LIKE 'BIN\$%' AND
  870. TABLE_NAME = %s";
  871. foreach ($this->database->query($sql, $table) as $row) {
  872. if (preg_match('#SELECT\s+(\w+).nextval\s+INTO\s+:new\.(\w+)\s+FROM\s+dual#i', $row['trigger_body'], $matches)) {
  873. $column = strtolower($matches[2]);
  874. $column_info[$column]['auto_increment'] = TRUE;
  875. }
  876. }
  877. return $column_info;
  878. }
  879. /**
  880. * Fetches the key info for an Oracle database
  881. *
  882. * The structure of the returned array is:
  883. *
  884. * {{{
  885. * array(
  886. * 'primary' => array(
  887. * {column name}, ...
  888. * ),
  889. * 'unique' => array(
  890. * array(
  891. * {column name}, ...
  892. * ), ...
  893. * ),
  894. * 'foreign' => array(
  895. * array(
  896. * 'column' => {column name},
  897. * 'foreign_table' => {foreign table name},
  898. * 'foreign_column' => {foreign column name},
  899. * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
  900. * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
  901. * ), ...
  902. * )
  903. * )
  904. * }}}
  905. *
  906. * @return array The keys arrays for every table in the database - see method description for details
  907. */
  908. private function fetchOracleKeys()
  909. {
  910. $keys = array();
  911. $tables = $this->getTables();
  912. foreach ($tables as $table) {
  913. $keys[$table] = array();
  914. $keys[$table]['primary'] = array();
  915. $keys[$table]['unique'] = array();
  916. $keys[$table]['foreign'] = array();
  917. }
  918. $sql = "SELECT
  919. LOWER(UC.TABLE_NAME) \"TABLE\",
  920. UC.CONSTRAINT_NAME CONSTRAINT_NAME,
  921. CASE UC.CONSTRAINT_TYPE
  922. WHEN 'P' THEN 'primary'
  923. WHEN 'R' THEN 'foreign'
  924. WHEN 'U' THEN 'unique'
  925. END TYPE,
  926. LOWER(UCC.COLUMN_NAME) \"COLUMN\",
  927. LOWER(FKC.TABLE_NAME) FOREIGN_TABLE,
  928. LOWER(FKC.COLUMN_NAME) FOREIGN_COLUMN,
  929. CASE WHEN FKC.TABLE_NAME IS NOT NULL THEN REPLACE(LOWER(UC.DELETE_RULE), ' ', '_') ELSE NULL END ON_DELETE
  930. FROM
  931. USER_CONSTRAINTS UC INNER JOIN
  932. USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME LEFT JOIN
  933. USER_CONSTRAINTS FK ON UC.R_CONSTRAINT_NAME = FK.CONSTRAINT_NAME LEFT JOIN
  934. USER_CONS_COLUMNS FKC ON FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME
  935. WHERE
  936. UC.CONSTRAINT_TYPE IN ('U', 'P', 'R') AND
  937. UC.STATUS = 'ENABLED' AND
  938. SUBSTR(UC.TABLE_NAME, 1, 4) <> 'BIN\$'
  939. ORDER BY
  940. UC.TABLE_NAME ASC,
  941. UC.CONSTRAINT_TYPE ASC,
  942. UC.CONSTRAINT_NAME ASC,
  943. UCC.POSITION ASC";
  944. $result = $this->database->query($sql);
  945. $last_name = '';
  946. $last_table = '';
  947. $last_type = '';
  948. foreach ($result as $row) {
  949. if ($row['constraint_name'] != $last_name) {
  950. if ($last_name) {
  951. if ($last_type == 'foreign' || $last_type == 'unique') {
  952. $keys[$last_table][$last_type][] = $temp;
  953. } else {
  954. $keys[$last_table][$last_type] = $temp;
  955. }
  956. }
  957. $temp = array();
  958. if ($row['type'] == 'foreign') {
  959. $temp['column'] = $row['column'];
  960. $temp['foreign_table'] = $row['foreign_table'];
  961. $temp['foreign_column'] = $row['foreign_column'];
  962. $temp['on_delete'] = 'no_action';
  963. $temp['on_update'] = 'no_action';
  964. if (!empty($row['on_delete'])) {
  965. $temp['on_delete'] = $row['on_delete'];
  966. }
  967. } else {
  968. $temp[] = $row['column'];
  969. }
  970. $last_table = $row['table'];
  971. $last_name = $row['constraint_name'];
  972. $last_type = $row['type'];
  973. } else {
  974. $temp[] = $row['column'];
  975. }
  976. }
  977. if (isset($temp)) {
  978. if ($last_type == 'foreign' || $last_type == 'unique') {
  979. $keys[$last_table][$last_type][] = $temp;
  980. } else {
  981. $keys[$last_table][$last_type] = $temp;
  982. }
  983. }
  984. return $keys;
  985. }
  986. /**
  987. * Gets the column info from a PostgreSQL database
  988. *
  989. * The returned array is in the format:
  990. *
  991. * {{{
  992. * array(
  993. * (string) {column name} => array(
  994. * 'type' => (string) {data type},
  995. * 'not_null' => (boolean) {if value can't be null},
  996. * 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
  997. * 'valid_values' => (array) {the valid values for a char/varchar field},
  998. * 'max_length' => (integer) {the maximum length in a char/varchar field},
  999. * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
  1000. * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
  1001. * ), ...
  1002. * )
  1003. * }}}
  1004. *
  1005. * @param string $table The table to fetch the column info for
  1006. * @return array The column info for the table specified - see method description for details
  1007. */
  1008. private function fetchPostgreSQLColumnInfo($table)
  1009. {
  1010. $column_info = array();
  1011. $data_type_mapping = array(
  1012. 'boolean' => 'boolean',
  1013. 'smallint' => 'integer',
  1014. 'int' => 'integer',
  1015. 'bigint' => 'integer',
  1016. 'serial' => 'integer',
  1017. 'bigserial' => 'integer',
  1018. 'timestamp' => 'timestamp',
  1019. 'date' => 'date',
  1020. 'time' => 'time',
  1021. 'uuid' => 'varchar',
  1022. 'character varying' => 'varchar',
  1023. 'character' => 'char',
  1024. 'real' => 'float',
  1025. 'double' => 'float',
  1026. 'numeric' => 'float',
  1027. 'bytea' => 'blob',
  1028. 'text' => 'text',
  1029. 'mediumtext' => 'text',
  1030. 'longtext' => 'text'
  1031. );
  1032. // PgSQL required this complicated SQL to get the column info
  1033. $sql = "SELECT
  1034. pg_attribute.attname AS column,
  1035. format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type,
  1036. pg_attribute.attnotnull AS not_null,
  1037. pg_attrdef.adsrc AS default,
  1038. pg_get_constraintdef(pg_constraint.oid) AS constraint
  1039. FROM
  1040. pg_attribute LEFT JOIN
  1041. pg_class ON pg_attribute.attrelid = pg_class.oid LEFT JOIN
  1042. pg_type ON pg_type.oid = pg_attribute.atttypid LEFT JOIN
  1043. pg_constraint ON pg_constraint.conrelid = pg_class.oid AND
  1044. pg_attribute.attnum = ANY (pg_constraint.conkey) AND
  1045. pg_constraint.contype = 'c' LEFT JOIN
  1046. pg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND
  1047. pg_attribute.attnum = pg_attrdef.adnum
  1048. WHERE
  1049. NOT pg_attribute.attisdropped AND
  1050. pg_class.relname = %s AND
  1051. pg_type.typname NOT IN ('oid', 'cid', 'xid', 'cid', 'xid', 'tid')
  1052. ORDER BY
  1053. pg_attribute.attnum,
  1054. pg_constraint.contype";
  1055. $result = $this->database->query($sql, $table);
  1056. foreach ($result as $row) {
  1057. $info = array();
  1058. // Get the column type
  1059. preg_match('#([\w ]+)\s*(?:\(\s*(\d+)(?:\s*,\s*(\d+))?\s*\))?#', $row['data_type'], $column_data_type);
  1060. foreach ($data_type_mapping as $data_type => $mapped_data_type) {
  1061. if (stripos($column_data_type[1], $data_type) === 0) {
  1062. $info['type'] = $mapped_data_type;
  1063. break;
  1064. }
  1065. }
  1066. if (!isset($info['type'])) {
  1067. $info['type'] = $column_data_type[1];
  1068. }
  1069. // Handle the length of decimal/numeric fields
  1070. if ($info['type'] == 'float' && isset($column_data_type[3]) && strlen($column_data_type[3]) > 0) {
  1071. $info['decimal_places'] = (int) $column_data_type[3];
  1072. }
  1073. // Handle the special data for varchar fields
  1074. if (in_array($info['type'], array('char', 'varchar')) && !empty($column_data_type[2])) {
  1075. $info['max_length'] = $column_data_type[2];
  1076. }
  1077. // In PostgreSQL, a UUID can be the 32 digits, 32 digits plus 4 hyphens or 32 digits plus 4 hyphens and 2 curly braces
  1078. if ($row['data_type'] == 'uuid') {
  1079. $info['max_length'] = 38;
  1080. }
  1081. // Handle check constraints that are just simple lists
  1082. if (in_array($info['type'], array('varchar', 'char')) && !empty($row['constraint'])) {
  1083. if (preg_match('/CHECK[\( "]+' . $row['column'] . '[a-z\) ":]+\s+=\s+/i', $row['constraint'])) {
  1084. if (preg_match_all("/(?!').'((''|[^']+)*)'/", $row['constraint'], $matches, PREG_PATTERN_ORDER)) {
  1085. $info['valid_values'] = str_replace("''", "'", $matches[1]);
  1086. }
  1087. }
  1088. }
  1089. // Handle default values and serial data types
  1090. if ($info['type'] == 'integer' && stripos($row['default'], 'nextval(') !== FALSE) {
  1091. $info['auto_increment'] = TRUE;
  1092. } elseif ($row['default'] !== NULL) {
  1093. if ($row['default'] == 'now()') {
  1094. $info['default'] = 'CURRENT_TIMESTAMP';
  1095. } elseif ($row['default'] == "('now'::text)::date") {
  1096. $info['default'] = 'CURRENT_DATE';
  1097. } elseif ($row['default'] == "('now'::text)::time with time zone") {
  1098. $info['default'] = 'CURRENT_TIME';
  1099. } else {
  1100. $info['default'] = str_replace("''", "'", preg_replace("/^'(.*)'::[a-z ]+\$/iD", '\1', $row['default']));
  1101. if ($info['type'] == 'boolean') {
  1102. $info['default'] = ($info['default'] == 'false' || !$info['default']) ? FALSE : TRUE;
  1103. }
  1104. }
  1105. }
  1106. // Not null values
  1107. $info['not_null'] = ($row['not_null'] == 't') ? TRUE : FALSE;
  1108. $column_info[$row['column']] = $info;
  1109. }
  1110. return $column_info;
  1111. }
  1112. /**
  1113. * Fetches the key info for a PostgreSQL database
  1114. *
  1115. * The structure of the returned array is:
  1116. *
  1117. * {{{
  1118. * array(
  1119. * 'primary' => array(
  1120. * {column name}, ...
  1121. * ),
  1122. * 'unique' => array(
  1123. * array(
  1124. * {column name}, ...
  1125. * ), ...
  1126. * ),
  1127. * 'foreign' => array(
  1128. * array(
  1129. * 'column' => {column name},
  1130. * 'foreign_table' => {foreign table name},
  1131. * 'foreign_column' => {foreign column name},
  1132. * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
  1133. * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
  1134. * ), ...
  1135. * )
  1136. * )
  1137. * }}}
  1138. *
  1139. * @return array The keys arrays for every table in the database - see method description for details
  1140. */
  1141. private function fetchPostgreSQLKeys()
  1142. {
  1143. $keys = array();
  1144. $tables = $this->getTables();
  1145. foreach ($tables as $table) {
  1146. $keys[$table] = array();
  1147. $keys[$table]['primary'] = array();
  1148. $keys[$table]['unique'] = array();
  1149. $keys[$table]['foreign'] = array();
  1150. }
  1151. $sql = "(
  1152. SELECT
  1153. t.relname AS table,
  1154. con.conname AS constraint_name,
  1155. CASE con.contype
  1156. WHEN 'f' THEN 'foreign'
  1157. WHEN 'p' THEN 'primary'
  1158. WHEN 'u' THEN 'unique'
  1159. END AS type,
  1160. col.attname AS column,
  1161. ft.relname AS foreign_table,
  1162. fc.attname AS foreign_column,
  1163. CASE con.confdeltype
  1164. WHEN 'c' THEN 'cascade'
  1165. WHEN 'a' THEN 'no_action'
  1166. WHEN 'r' THEN 'restrict'
  1167. WHEN 'n' THEN 'set_null'
  1168. WHEN 'd' THEN 'set_default'
  1169. END AS on_delete,
  1170. CASE con.confupdtype
  1171. WHEN 'c' THEN 'cascade'
  1172. WHEN 'a' THEN 'no_action'
  1173. WHEN 'r' THEN 'restrict'
  1174. WHEN 'n' THEN 'set_null'
  1175. WHEN 'd' THEN 'set_default'
  1176. END AS on_update,
  1177. CASE WHEN con.conkey IS NOT NULL THEN position('-'||col.attnum||'-' in '-'||array_to_string(con.conkey, '-')||'-') ELSE 0 END AS column_order
  1178. FROM
  1179. pg_attribute AS col INNER JOIN
  1180. pg_class AS t ON col.attrelid = t.oid INNER JOIN
  1181. pg_constraint AS con ON (col.attnum = ANY (con.conkey) AND
  1182. con.conrelid = t.oid) LEFT JOIN
  1183. pg_class AS ft ON con.confrelid = ft.oid LEFT JOIN
  1184. pg_attribute AS fc ON (fc.attnum = ANY (con.confkey) AND
  1185. ft.oid = fc.attrelid)
  1186. WHERE
  1187. NOT col.attisdropped AND
  1188. (con.contype = 'p' OR
  1189. con.contype = 'f' OR
  1190. con.contype = 'u')
  1191. ) UNION (
  1192. SELECT
  1193. t.relname AS table,
  1194. ic.relname AS constraint_name,
  1195. 'unique' AS type,
  1196. col.attname AS column,
  1197. NULL AS foreign_table,
  1198. NULL AS foreign_column,
  1199. NULL AS on_delete,
  1200. NULL AS on_update,
  1201. CASE WHEN ind.indkey IS NOT NULL THEN position('-'||col.attnum||'-' in '-'||array_to_string(ind.indkey, '-')||'-') ELSE 0 END AS column_order
  1202. FROM
  1203. pg_class AS t INNER JOIN
  1204. pg_index AS ind ON ind.indrelid = t.oid INNER JOIN
  1205. pg_namespace AS n ON t.relnamespace = n.oid INNER JOIN
  1206. pg_class AS ic ON ind.indexrelid = ic.oid LEFT JOIN
  1207. pg_constraint AS con ON con.conrelid = t.oid AND con.contype = 'u' AND con.conname = ic.relname INNER JOIN
  1208. pg_attribute AS col ON col.attrelid = t.oid AND col.attnum = ANY (ind.indkey)
  1209. WHERE
  1210. n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
  1211. indisunique = TRUE AND
  1212. indisprimary = FALSE AND
  1213. con.oid IS NULL
  1214. ) ORDER BY 1, 3, 2, 9";
  1215. $result = $this->database->query($sql);
  1216. $last_name = '';
  1217. $last_table = '';
  1218. $last_type = '';
  1219. foreach ($result as $row) {
  1220. if ($row['constraint_name'] != $last_name) {
  1221. if ($last_name) {
  1222. if ($last_type == 'foreign' || $last_type == 'unique') {
  1223. $keys[$last_table][$last_type][] = $temp;
  1224. } else {
  1225. $keys[$last_table][$last_type] = $temp;
  1226. }
  1227. }
  1228. $temp = array();
  1229. if ($row['type'] == 'foreign') {
  1230. $temp['column'] = $row['column'];
  1231. $temp['foreign_table'] = $row['foreign_table'];
  1232. $temp['foreign_column'] = $row['foreign_column'];
  1233. $temp['on_delete'] = 'no_action';
  1234. $temp['on_update'] = 'no_action';
  1235. if (!empty($row['on_delete'])) {
  1236. $temp['on_delete'] = $row['on_delete'];
  1237. }
  1238. if (!empty($row['on_update'])) {
  1239. $temp['on_update'] = $row['on_update'];
  1240. }
  1241. } else {
  1242. $temp[] = $row['column'];
  1243. }
  1244. $last_table = $row['table'];
  1245. $last_name = $row['constraint_name'];
  1246. $last_type = $row['type'];
  1247. } else {
  1248. $temp[] = $row['column'];
  1249. }
  1250. }
  1251. if (isset($temp)) {
  1252. if ($last_type == 'foreign' || $last_type == 'unique') {
  1253. $keys[$last_table][$last_type][] = $temp;
  1254. } else {
  1255. $keys[$last_table][$last_type] = $temp;
  1256. }
  1257. }
  1258. return $keys;
  1259. }
  1260. /**
  1261. * Gets the column info from a SQLite database
  1262. *
  1263. * The returned array is in the format:
  1264. *
  1265. * {{{
  1266. * array(
  1267. * (string) {column name} => array(
  1268. * 'type' => (string) {data type},
  1269. * 'not_null' => (boolean) {if value can't be null},
  1270. * 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
  1271. * 'valid_values' => (array) {the valid values for a char/varchar field},
  1272. * 'max_length' => (integer) {the maximum length in a char/varchar field},
  1273. * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
  1274. * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
  1275. * ), ...
  1276. * )
  1277. * }}}
  1278. *
  1279. * @param string $table The table to fetch the column info for
  1280. * @return array The column info for the table specified - see method description for details
  1281. */
  1282. private function fetchSQLiteColumnInfo($table)
  1283. {
  1284. $column_info = array();
  1285. $data_type_mapping = array(
  1286. 'boolean' => 'boolean',
  1287. 'serial' => 'integer',
  1288. 'smallint' => 'integer',
  1289. 'int' => 'integer',
  1290. 'integer' => 'integer',
  1291. 'bigint' => 'integer',
  1292. 'timestamp' => 'timestamp',
  1293. 'date' => 'date',
  1294. 'time' => 'time',
  1295. 'varchar' => 'varchar',
  1296. 'char' => 'char',
  1297. 'real' => 'float',
  1298. 'numeric' => 'float',
  1299. 'float' => 'float',
  1300. 'double' => 'float',
  1301. 'decimal' => 'float',
  1302. 'blob' => 'blob',
  1303. 'text' => 'text'
  1304. );
  1305. $result = $this->database->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = %s", $table);
  1306. try {
  1307. $row = $result->fetchRow();
  1308. $create_sql = $row['sql'];
  1309. } catch (fNoRowsException $e) {
  1310. return array();
  1311. }
  1312. preg_match_all('#(?<=,|\()\s*(?:`|"|\[)?(\w+)(?:`|"|\])?\s+([a-z]+)(?:\(\s*(\d+)(?:\s*,\s*(\d+))?\s*\))?(?:(\s+NOT\s+NULL)|(?:\s+NULL)|(?:\s+DEFAULT\s+([^, \']*|\'(?:\'\'|[^\']+)*\'))|(\s+UNIQUE)|(\s+PRIMARY\s+KEY(?:\s+AUTOINCREMENT)?)|(\s+CHECK\s*\(\w+\s+IN\s+\(\s*(?:(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\s*,\s*)*\s*(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\)\)))*(\s+REFERENCES\s+\w+\s*\(\s*\w+\s*\)\s*(?:\s+(?:ON\s+DELETE|ON\s+UPDATE)\s+(?:CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL|SET\s+DEFAULT))*(?:\s+(?:DEFERRABLE|NOT\s+DEFERRABLE))?)?\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
  1313. foreach ($matches as $match) {
  1314. $info = array();
  1315. foreach ($data_type_mapping as $data_type => $mapped_data_type) {
  1316. if (stripos($match[2], $data_type) === 0) {
  1317. $info['type'] = $mapped_data_type;
  1318. break;
  1319. }
  1320. }
  1321. // Type specific information
  1322. if (in_array($info['type'], array('char', 'varchar')) && !empty($match[3])) {
  1323. $info['max_length'] = $match[3];
  1324. }
  1325. // Figure out how many decimal places for a decimal
  1326. if (in_array(strtolower($match[2]), array('decimal', 'numeric')) && !empty($match[4])) {
  1327. $info['decimal_places'] = $match[4];
  1328. }
  1329. // Not null
  1330. $info['n…

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