PageRenderTime 70ms CodeModel.GetById 26ms RepoModel.GetById 0ms app.codeStats 2ms

/inc/flourish/fSQLSchemaTranslation.php

https://github.com/trbs/Graphite-Tattle
PHP | 5387 lines | 4174 code | 652 blank | 561 comment | 586 complexity | c841b19e95f63f33b476ebaaadffc4d0 MD5 | raw file
Possible License(s): Apache-2.0

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

  1. <?php
  2. /**
  3. * Adds cross-database `CREATE TABLE`, `ALTER TABLE` and `COMMENT ON COLUMN` statements to fSQLTranslation
  4. *
  5. * @copyright Copyright (c) 2011 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/fSQLSchemaTranslation
  11. *
  12. * @version 1.0.0b2
  13. * @changes 1.0.0b2 Fixed detection of explicitly named SQLite foreign key constraints [wb, 2011-08-23]
  14. * @changes 1.0.0b The initial implementation [wb, 2011-05-09]
  15. */
  16. class fSQLSchemaTranslation
  17. {
  18. /**
  19. * Converts a SQL identifier to lower case and removes double quotes
  20. *
  21. * @param string $identifier The SQL identifier
  22. * @return string The unescaped identifier
  23. */
  24. static private function unescapeIdentifier($identifier)
  25. {
  26. return str_replace('"', '', strtolower($identifier));
  27. }
  28. /**
  29. * Composes text using fText if loaded
  30. *
  31. * @param string $message The message to compose
  32. * @param mixed $component A string or number to insert into the message
  33. * @param mixed ...
  34. * @return string The composed and possible translated message
  35. */
  36. static protected function compose($message)
  37. {
  38. $args = array_slice(func_get_args(), 1);
  39. if (class_exists('fText', FALSE)) {
  40. return call_user_func_array(
  41. array('fText', 'compose'),
  42. array($message, $args)
  43. );
  44. } else {
  45. return vsprintf($message, $args);
  46. }
  47. }
  48. /**
  49. * Accepts a CREATE TABLE statement and parses out the column definitions
  50. *
  51. * The return value is an associative array with the keys being column
  52. * names and the values being arrays containing the following keys:
  53. * - definition: (string) the complete column definition
  54. * - pieces: (array) an associative array that can be joined back together to make the definition
  55. * - beginning
  56. * - column_name
  57. * - data_type
  58. * - not_null
  59. * - null
  60. * - default
  61. * - unique
  62. * - primary_key
  63. * - check_constraint
  64. * - foreign_key
  65. * - deferrable
  66. * - comment/end
  67. *
  68. * @param string $sql The SQL `CREATE TABLE` statement
  69. * @return array An associative array of information for each column - see method description for details
  70. */
  71. static private function parseSQLiteColumnDefinitions($sql)
  72. {
  73. preg_match_all(
  74. '#(?<=,|\(|\*/|\n)(\s*)[`"\'\[]?(\w+)[`"\'\]]?(\s+(?:[a-z]+)(?:\(\s*(\d+)(?:\s*,\s*(\d+))?\s*\))?)(?:(\s+NOT\s+NULL)|(\s+NULL)|(\s+DEFAULT\s+([^, \'\n]*|\'(?:\'\'|[^\']+)*\'))|(\s+UNIQUE)|(\s+PRIMARY\s+KEY(?:\s+AUTOINCREMENT)?)|(\s+CHECK\s*\("?\w+"?\s+IN\s+\(\s*(?:(?:[^, \'\n]+|\'(?:\'\'|[^\']+)*\')\s*,\s*)*\s*(?:[^, \'\n]+|\'(?:\'\'|[^\']+)*\')\)\)))*(\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*((?:(?!\*/).)*?)\s*\*/))?\s*(?:,[ \t]*(?:--[ \t]*([^\n]*?)[ \t]*(?=\n)|/\*\s*((?:(?!\*/).)*?)\s*\*/)?|(?:--[ \t]*([^\n]*?)[ \t]*(?=\n))?\s*(?=\))))#msi',
  75. $sql,
  76. $matches,
  77. PREG_SET_ORDER
  78. );
  79. $output = array();
  80. foreach ($matches as $match) {
  81. $comment = '';
  82. foreach (array(16, 17, 18, 19) as $key) {
  83. if (isset($match[$key])) {
  84. $comment .= $match[$key];
  85. }
  86. }
  87. $output[strtolower($match[2])] = array(
  88. 'definition' => $match[0],
  89. 'pieces' => array(
  90. 'beginning' => $match[1],
  91. 'column_name' => $match[2],
  92. 'data_type' => $match[3],
  93. 'not_null' => $match[6],
  94. 'null' => $match[7],
  95. 'default' => $match[8],
  96. 'unique' => $match[10],
  97. 'primary_key' => $match[11],
  98. 'check_constraint' => $match[12],
  99. 'foreign_key' => $match[13],
  100. 'deferrable' => $match[14],
  101. 'comment/end' => $match[15]
  102. )
  103. );
  104. }
  105. return $output;
  106. }
  107. /**
  108. * Removes a search string from a `CREATE TABLE` statement
  109. *
  110. * @param string $create_table_sql The SQL `CREATE TABLE` statement
  111. * @param string $search The string to remove
  112. * @return string The modified `CREATE TABLE` statement
  113. */
  114. static private function removeFromSQLiteCreateTable($create_table_sql, $search)
  115. {
  116. if (preg_match('#,(\s*--.*)?\s*$#D', $search)) {
  117. $regex = '#' . preg_quote($search, '#') . '#';
  118. } else {
  119. $regex = '#,(\s*/\*.*?\*/\s*|\s*--[^\n]+\n\s*)?\s*' . preg_quote($search, '#') . '\s*#';
  120. }
  121. return preg_replace($regex, "\\1\n", $create_table_sql);
  122. }
  123. /**
  124. * The fDatabase instance
  125. *
  126. * @var fDatabase
  127. */
  128. private $database;
  129. /**
  130. * Database-specific schema information needed for translation
  131. *
  132. * @var array
  133. */
  134. private $schema_info;
  135. /**
  136. * Sets up the class
  137. *
  138. * @param fDatabase $database The database being translated for
  139. * @return fSQLSchemaTranslation
  140. */
  141. public function __construct($database)
  142. {
  143. $this->database = $database;
  144. $this->schema_info = array();
  145. }
  146. /**
  147. * All requests that hit this method should be requests for callbacks
  148. *
  149. * @internal
  150. *
  151. * @param string $method The method to create a callback for
  152. * @return callback The callback for the method requested
  153. */
  154. public function __get($method)
  155. {
  156. return array($this, $method);
  157. }
  158. /**
  159. * Adds a SQLite index to the internal schema tracker
  160. *
  161. * @param string $name The index name
  162. * @param string $table The table the index applies to
  163. * @param string $sql The SQL definition of the index
  164. * @return void
  165. */
  166. private function addSQLiteIndex($name, $table, $sql)
  167. {
  168. if (!isset($this->schema_info['sqlite_indexes'])) {
  169. $this->schema_info['sqlite_indexes'] = array();
  170. }
  171. $this->schema_info['sqlite_indexes'][$name] = array(
  172. 'table' => $table,
  173. 'sql' => $sql
  174. );
  175. }
  176. /**
  177. * Stores the SQL used to create a table
  178. *
  179. * @param string $table The table to set the `CREATE TABLE` statement for
  180. * @param string $sql The SQL used to create the table
  181. * @return void
  182. */
  183. private function addSQLiteTable($table, $sql)
  184. {
  185. if (!isset($this->schema_info['sqlite_create_tables'])) {
  186. $this->getSQLiteTables();
  187. }
  188. $this->schema_info['sqlite_create_tables'][$table] = $sql;
  189. }
  190. /**
  191. * Adds a SQLite trigger to the internal schema tracker
  192. *
  193. * @param string $name The trigger name
  194. * @param string $table The table the trigger applies to
  195. * @param string $sql The SQL definition of the trigger
  196. * @return void
  197. */
  198. private function addSQLiteTrigger($name, $table, $sql)
  199. {
  200. if (!isset($this->schema_info['sqlite_triggers'])) {
  201. $this->schema_info['sqlite_triggers'] = array();
  202. }
  203. $this->schema_info['sqlite_triggers'][$name] = array(
  204. 'table' => $table,
  205. 'sql' => $sql
  206. );
  207. }
  208. /**
  209. * Creates a trigger for SQLite that handles an on delete clause
  210. *
  211. * @param array &$extra_statements An array of extra SQL statements to be added to the SQL
  212. * @param string $referencing_table The table that contains the foreign key
  213. * @param string $referencing_column The column the foreign key constraint is on
  214. * @param string $referenced_table The table the foreign key references
  215. * @param string $referenced_column The column the foreign key references
  216. * @param string $delete_clause What is to be done on a delete
  217. * @return string The trigger
  218. */
  219. private function createSQLiteForeignKeyTriggerOnDelete(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $delete_clause)
  220. {
  221. switch (strtolower($delete_clause)) {
  222. case 'no action':
  223. case 'restrict':
  224. $name = 'fkd_res_' . $referencing_table . '_' . $referencing_column;
  225. $extra_statements[] = 'CREATE TRIGGER ' . $name . '
  226. BEFORE DELETE ON "' . $referenced_table . '"
  227. FOR EACH ROW BEGIN
  228. SELECT RAISE(ROLLBACK, \'delete on table "' . $referenced_table . '" can not be executed because it would violate the foreign key constraint on column "' . $referencing_column . '" of table "' . $referencing_table . '"\')
  229. WHERE (SELECT "' . $referencing_column . '" FROM "' . $referencing_table . '" WHERE "' . $referencing_column . '" = OLD."' . $referenced_table . '") IS NOT NULL;
  230. END';
  231. $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
  232. break;
  233. case 'set null':
  234. $name = 'fkd_nul_' . $referencing_table . '_' . $referencing_column;
  235. $extra_statements[] = 'CREATE TRIGGER ' . $name . '
  236. BEFORE DELETE ON "' . $referenced_table . '"
  237. FOR EACH ROW BEGIN
  238. UPDATE "' . $referencing_table . '" SET "' . $referencing_column . '" = NULL WHERE "' . $referencing_column . '" = OLD."' . $referenced_column . '";
  239. END';
  240. $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
  241. break;
  242. case 'cascade':
  243. $name = 'fkd_cas_' . $referencing_table . '_' . $referencing_column;
  244. $extra_statements[] = 'CREATE TRIGGER ' . $name . '
  245. BEFORE DELETE ON "' . $referenced_table . '"
  246. FOR EACH ROW BEGIN
  247. DELETE FROM "' . $referencing_table . '" WHERE "' . $referencing_column . '" = OLD."' . $referenced_column . '";
  248. END';
  249. $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
  250. break;
  251. }
  252. }
  253. /**
  254. * Creates a trigger for SQLite that handles an on update clause
  255. *
  256. * @param array &$extra_statements An array of extra SQL statements to be added to the SQL
  257. * @param string $referencing_table The table that contains the foreign key
  258. * @param string $referencing_column The column the foreign key constraint is on
  259. * @param string $referenced_table The table the foreign key references
  260. * @param string $referenced_column The column the foreign key references
  261. * @param string $update_clause What is to be done on an update
  262. * @return string The trigger
  263. */
  264. private function createSQLiteForeignKeyTriggerOnUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $update_clause)
  265. {
  266. switch (strtolower($update_clause)) {
  267. case 'no action':
  268. case 'restrict':
  269. $name = 'fku_res_' . $referencing_table . '_' . $referencing_column;
  270. $extra_statements[] = 'CREATE TRIGGER ' . $name . '
  271. BEFORE UPDATE ON "' . $referenced_table . '"
  272. FOR EACH ROW BEGIN
  273. SELECT RAISE(ROLLBACK, \'update on table "' . $referenced_table . '" can not be executed because it would violate the foreign key constraint on column "' . $referencing_column . '" of table "' . $referencing_table . '"\')
  274. WHERE (SELECT "' . $referencing_column . '" FROM "' . $referencing_table . '" WHERE "' . $referencing_column . '" = OLD."' . $referenced_column . '") IS NOT NULL;
  275. END';
  276. $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
  277. break;
  278. case 'set null':
  279. $name = 'fku_nul_' . $referencing_table . '_' . $referencing_column;
  280. $extra_statements[] = 'CREATE TRIGGER ' . $name . '
  281. BEFORE UPDATE ON "' . $referenced_table . '"
  282. FOR EACH ROW BEGIN
  283. UPDATE "' . $referencing_table . '" SET "' . $referencing_column . '" = NULL WHERE OLD."' . $referenced_column . '" <> NEW."' . $referenced_column . '" AND "' . $referencing_column . '" = OLD."' . $referenced_column . '";
  284. END';
  285. $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
  286. break;
  287. case 'cascade':
  288. $name = 'fku_cas_' . $referencing_table . '_' . $referencing_column;
  289. $extra_statements[] = 'CREATE TRIGGER ' . $name . '
  290. BEFORE UPDATE ON "' . $referenced_table . '"
  291. FOR EACH ROW BEGIN
  292. UPDATE "' . $referencing_table . '" SET "' . $referencing_column . '" = NEW."' . $referenced_column . '" WHERE OLD."' . $referenced_column . '" <> NEW."' . $referenced_column . '" AND "' . $referencing_column . '" = OLD."' . $referenced_column . '";
  293. END';
  294. $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
  295. break;
  296. }
  297. }
  298. /**
  299. * Creates a trigger for SQLite that prevents inserting or updating to values the violate a `FOREIGN KEY` constraint
  300. *
  301. * @param array &$extra_statements An array of extra SQL statements to be added to the SQL
  302. * @param string $referencing_table The table that contains the foreign key
  303. * @param string $referencing_column The column the foriegn key constraint is on
  304. * @param string $referenced_table The table the foreign key references
  305. * @param string $referenced_column The column the foreign key references
  306. * @param boolean $referencing_not_null If the referencing columns is set to not null
  307. * @return string The trigger
  308. */
  309. private function createSQLiteForeignKeyTriggerValidInsertUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $referencing_not_null)
  310. {
  311. // Verify key on inserts
  312. $name = 'fki_ver_' . $referencing_table . '_' . $referencing_column;
  313. $sql = 'CREATE TRIGGER ' . $name . '
  314. BEFORE INSERT ON "' . $referencing_table . '"
  315. FOR EACH ROW BEGIN
  316. SELECT RAISE(ROLLBACK, \'insert on table "' . $referencing_table . '" violates foreign key constraint on column "' . $referencing_column . '"\')
  317. WHERE ';
  318. if (!$referencing_not_null) {
  319. $sql .= 'NEW."' . $referencing_column . '" IS NOT NULL AND ';
  320. }
  321. $sql .= ' (SELECT "' . $referenced_column . '" FROM "' . $referenced_table . '" WHERE "' . $referenced_column . '" = NEW."' . $referencing_column . '") IS NULL;
  322. END';
  323. $extra_statements[] = $sql;
  324. $this->addSQLiteTrigger($name, $referencing_table, end($extra_statements));
  325. // Verify key on updates
  326. $name = 'fku_ver_' . $referencing_table . '_' . $referencing_column;
  327. $sql = 'CREATE TRIGGER ' . $name . '
  328. BEFORE UPDATE ON "' . $referencing_table . '"
  329. FOR EACH ROW BEGIN
  330. SELECT RAISE(ROLLBACK, \'update on table "' . $referencing_table . '" violates foreign key constraint on column "' . $referencing_column . '"\')
  331. WHERE ';
  332. if (!$referencing_not_null) {
  333. $sql .= 'NEW."' . $referencing_column . '" IS NOT NULL AND ';
  334. }
  335. $sql .= ' (SELECT "' . $referenced_column . '" FROM "' . $referenced_table . '" WHERE "' . $referenced_column . '" = NEW."' . $referencing_column . '") IS NULL;
  336. END';
  337. $extra_statements[] = $sql;
  338. $this->addSQLiteTrigger($name, $referencing_table, end($extra_statements));
  339. }
  340. /**
  341. * Generates a 30 character constraint name for use with `ALTER TABLE` statements
  342. *
  343. * @param string $sql The `ALTER TABLE` statement
  344. * @param string $type A 2-character string representing the type of constraint
  345. */
  346. private function generateConstraintName($sql, $type)
  347. {
  348. $constraint = '_' . $type;
  349. $constraint = '_' . substr(time(), -8) . $constraint;
  350. return substr(md5(strtolower($sql)), 0, 30 - strlen($constraint)) . $constraint;
  351. }
  352. /**
  353. * Returns the check constraint for a table and column
  354. *
  355. * @param string $schema The schema the table is in
  356. * @param string $table The table the column is in
  357. * @param string $column The column to get the check constraint for
  358. * @return array|NULL An associative array with the keys: `name` and `definition` or `NULL`
  359. */
  360. private function getDB2CheckConstraint($schema, $table, $column)
  361. {
  362. $constraint = $this->database->query(
  363. "SELECT
  364. CH.TEXT,
  365. CH.CONSTNAME
  366. FROM
  367. SYSCAT.COLUMNS AS C INNER JOIN
  368. SYSCAT.COLCHECKS AS CC ON
  369. C.TABSCHEMA = CC.TABSCHEMA AND
  370. C.TABNAME = CC.TABNAME AND
  371. C.COLNAME = CC.COLNAME AND
  372. CC.USAGE = 'R' INNER JOIN
  373. SYSCAT.CHECKS AS CH ON
  374. C.TABSCHEMA = CH.TABSCHEMA AND
  375. C.TABNAME = CH.TABNAME AND
  376. CH.TYPE = 'C' AND
  377. CH.CONSTNAME = CC.CONSTNAME
  378. WHERE
  379. LOWER(C.TABSCHEMA) = %s AND
  380. LOWER(C.TABNAME) = %s AND
  381. LOWER(C.COLNAME) = %s",
  382. $schema,
  383. $table,
  384. $column
  385. );
  386. if (!$constraint->countReturnedRows()) {
  387. return NULL;
  388. }
  389. $row = $constraint->fetchRow();
  390. return array(
  391. 'name' => $row['constname'],
  392. 'definition' => $row['text']
  393. );
  394. }
  395. /**
  396. * Returns the foreign key constraints that involve a specific table or table and column
  397. *
  398. * @param string $schema The schema the table is in
  399. * @param string $table The table the column is in
  400. * @param string $column The column to get the foreign keys for and the foreign keys that point to
  401. * @return array An associative array of the key being the constraint name and the value being an associative array containing the keys: `schema`, `table`, `column`, `foreign_schema`, `foreign_table`, `foreign_column`, `on_delete` and `on_cascade`
  402. */
  403. private function getDB2ForeignKeyConstraints($schema, $table, $column=NULL)
  404. {
  405. if ($column) {
  406. $where_conditions = "((
  407. LOWER(R.TABSCHEMA) = %s AND
  408. LOWER(R.TABNAME) = %s AND
  409. LOWER(K.COLNAME) = %s
  410. ) OR (
  411. LOWER(R.REFTABSCHEMA) = %s AND
  412. LOWER(R.REFTABNAME) = %s AND
  413. LOWER(FK.COLNAME) = %s
  414. ))";
  415. $params = array(
  416. strtolower($schema),
  417. strtolower($table),
  418. strtolower($column),
  419. strtolower($schema),
  420. strtolower($table),
  421. strtolower($column)
  422. );
  423. } else {
  424. $where_conditions = "LOWER(R.REFTABSCHEMA) = %s AND LOWER(R.REFTABNAME) = %s";
  425. $params = array(
  426. strtolower($schema),
  427. strtolower($table)
  428. );
  429. }
  430. array_unshift(
  431. $params,
  432. "SELECT
  433. R.CONSTNAME AS CONSTRAINT_NAME,
  434. TRIM(LOWER(R.TABSCHEMA)) AS \"SCHEMA\",
  435. LOWER(R.TABNAME) AS \"TABLE\",
  436. LOWER(K.COLNAME) AS \"COLUMN\",
  437. TRIM(LOWER(R.REFTABSCHEMA)) AS FOREIGN_SCHEMA,
  438. LOWER(R.REFTABNAME) AS FOREIGN_TABLE,
  439. LOWER(FK.COLNAME) AS FOREIGN_COLUMN,
  440. CASE R.DELETERULE WHEN 'C' THEN 'CASCADE' WHEN 'A' THEN 'NO ACTION' WHEN 'R' THEN 'RESTRICT' ELSE 'SET NULL' END AS ON_DELETE,
  441. CASE R.UPDATERULE WHEN 'A' THEN 'NO ACTION' WHEN 'R' THEN 'RESTRICT' END AS ON_UPDATE
  442. FROM
  443. SYSCAT.REFERENCES AS R INNER JOIN
  444. SYSCAT.KEYCOLUSE AS K ON
  445. R.CONSTNAME = K.CONSTNAME AND
  446. R.TABSCHEMA = K.TABSCHEMA AND
  447. R.TABNAME = K.TABNAME INNER JOIN
  448. SYSCAT.KEYCOLUSE AS FK ON
  449. R.REFKEYNAME = FK.CONSTNAME AND
  450. R.REFTABSCHEMA = FK.TABSCHEMA AND
  451. R.REFTABNAME = FK.TABNAME
  452. WHERE
  453. $where_conditions
  454. ORDER BY
  455. LOWER(R.CONSTNAME) ASC"
  456. );
  457. $constraints = call_user_func_array($this->database->query, $params);
  458. $keys = array();
  459. foreach ($constraints as $constraint) {
  460. $name = $constraint['constraint_name'] . $constraint['table'];
  461. $keys[$name] = $constraint;
  462. }
  463. return $keys;
  464. }
  465. /**
  466. * Returns the primary key for a table
  467. *
  468. * @param string $schema The schema the table is in
  469. * @param string $table The table to get the primary key for
  470. * @return array The columns in the primary key
  471. */
  472. private function getDB2PrimaryKeyConstraint($schema, $table)
  473. {
  474. $constraints = $this->database->query(
  475. "SELECT
  476. LOWER(C.COLNAME) AS \"COLUMN\"
  477. FROM
  478. SYSCAT.INDEXES AS I INNER JOIN
  479. SYSCAT.INDEXCOLUSE AS C ON
  480. I.INDSCHEMA = C.INDSCHEMA AND
  481. I.INDNAME = C.INDNAME
  482. WHERE
  483. I.UNIQUERULE IN ('P') AND
  484. LOWER(I.TABSCHEMA) = %s AND
  485. LOWER(I.TABNAME) = %s
  486. ORDER BY
  487. LOWER(I.INDNAME) ASC
  488. ",
  489. strtolower($schema),
  490. strtolower($table)
  491. );
  492. $key = array();
  493. foreach ($constraints as $constraint) {
  494. $key[] = $constraint['column'];
  495. }
  496. return $key;
  497. }
  498. /**
  499. * Returns the unique keys for a table and column
  500. *
  501. * @param string $schema The schema the table is in
  502. * @param string $table The table to get the unique keys for
  503. * @param string $column The column to filter the unique keys by
  504. * @return array An associative array of the key being the constraint name and the value being the columns in the unique key
  505. */
  506. private function getDB2UniqueConstraints($schema, $table, $column)
  507. {
  508. $constraints = $this->database->query(
  509. "SELECT
  510. CD.CONSTNAME AS CONSTRAINT_NAME,
  511. LOWER(C.COLNAME) AS \"COLUMN\"
  512. FROM
  513. SYSCAT.INDEXES AS I INNER JOIN
  514. SYSCAT.CONSTDEP AS CD ON
  515. I.TABSCHEMA = CD.TABSCHEMA AND
  516. I.TABNAME = CD.TABNAME AND
  517. CD.BTYPE = 'I' AND
  518. CD.BNAME = I.INDNAME INNER JOIN
  519. SYSCAT.INDEXCOLUSE AS C ON
  520. I.INDSCHEMA = C.INDSCHEMA AND
  521. I.INDNAME = C.INDNAME
  522. WHERE
  523. I.UNIQUERULE IN ('U') AND
  524. LOWER(I.TABSCHEMA) = %s AND
  525. LOWER(I.TABNAME) = %s
  526. ORDER BY
  527. LOWER(I.INDNAME) ASC
  528. ",
  529. strtolower($schema),
  530. strtolower($table)
  531. );
  532. $keys = array();
  533. foreach ($constraints as $constraint) {
  534. if (!isset($keys[$constraint['constraint_name']])) {
  535. $keys[$constraint['constraint_name']] = array();
  536. }
  537. $keys[$constraint['constraint_name']][] = $constraint['column'];
  538. }
  539. $new_keys = array();
  540. $column = strtolower($column);
  541. foreach ($keys as $name => $columns) {
  542. if (!in_array($column, $columns)) {
  543. continue;
  544. }
  545. $new_keys[$name] = $columns;
  546. }
  547. $keys = $new_keys;
  548. return $keys;
  549. }
  550. /**
  551. * Returns the check constraint for a column, if it exists
  552. *
  553. * @param string $schema The schema the column is inside of
  554. * @param string $table The table the column is part of
  555. * @param string $column The column name
  556. * @return array|NULL An associative array with the keys `name` and `definition`, or `NULL`
  557. */
  558. private function getMSSQLCheckConstraint($schema, $table, $column)
  559. {
  560. $constraint = $this->database->query(
  561. "SELECT
  562. cc.check_clause AS 'constraint',
  563. ccu.constraint_name
  564. FROM
  565. INFORMATION_SCHEMA.COLUMNS AS c INNER JOIN
  566. INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON
  567. c.column_name = ccu.column_name AND
  568. c.table_name = ccu.table_name AND
  569. c.table_catalog = ccu.table_catalog INNER JOIN
  570. INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON
  571. ccu.constraint_name = cc.constraint_name AND
  572. ccu.constraint_catalog = cc.constraint_catalog
  573. WHERE
  574. LOWER(c.table_schema) = %s AND
  575. LOWER(c.table_name) = %s AND
  576. LOWER(c.column_name) = %s AND
  577. c.table_catalog = DB_NAME()",
  578. strtolower($schema),
  579. strtolower($table),
  580. strtolower($column)
  581. );
  582. if (!$constraint->countReturnedRows()) {
  583. return NULL;
  584. }
  585. $row = $constraint->fetchRow();
  586. return array(
  587. 'name' => $row['constraint_name'],
  588. 'definition' => $row['constraint']
  589. );
  590. }
  591. /**
  592. * Returns the foreign key constraints that a column is part of
  593. *
  594. * @param string $schema The schema the column is inside of
  595. * @param string $table The table the column is part of
  596. * @param string|array $column The column name(s)
  597. * @return array An array of constraint names that reference the column(s)
  598. */
  599. private function getMSSQLForeignKeyConstraints($schema, $table, $column)
  600. {
  601. settype($column, 'array');
  602. $constraints = $this->database->query(
  603. "SELECT
  604. LOWER(tc.table_schema + '.' + tc.table_name) AS 'table',
  605. LOWER(tc.table_schema) AS 'schema',
  606. LOWER(tc.table_name) AS 'table_without_schema',
  607. LOWER(kcu.column_name) AS 'column',
  608. kcu.constraint_name AS name
  609. FROM
  610. information_schema.table_constraints AS tc INNER JOIN
  611. information_schema.key_column_usage AS kcu ON
  612. tc.constraint_name = kcu.constraint_name AND
  613. tc.constraint_catalog = kcu.constraint_catalog AND
  614. tc.constraint_schema = kcu.constraint_schema AND
  615. tc.table_name = kcu.table_name INNER JOIN
  616. information_schema.referential_constraints AS rc ON
  617. kcu.constraint_name = rc.constraint_name AND
  618. kcu.constraint_catalog = rc.constraint_catalog AND
  619. kcu.constraint_schema = rc.constraint_schema INNER JOIN
  620. information_schema.constraint_column_usage AS ccu ON
  621. ccu.constraint_name = rc.unique_constraint_name AND
  622. ccu.constraint_catalog = rc.constraint_catalog AND
  623. ccu.constraint_schema = rc.constraint_schema
  624. WHERE
  625. tc.constraint_type = 'FOREIGN KEY' AND
  626. (
  627. LOWER(tc.table_schema) = %s AND
  628. LOWER(ccu.table_name) = %s AND
  629. LOWER(ccu.column_name) IN (%s)
  630. ) OR (
  631. LOWER(tc.table_schema) = %s AND
  632. LOWER(kcu.table_name) = %s AND
  633. LOWER(kcu.column_name) IN (%s)
  634. ) AND
  635. tc.constraint_catalog = DB_NAME()",
  636. strtolower($schema),
  637. strtolower($table),
  638. array_map('strtolower', $column),
  639. strtolower($schema),
  640. strtolower($table),
  641. array_map('strtolower', $column)
  642. );
  643. return $constraints->fetchAllRows();
  644. }
  645. /**
  646. * Returns the default constraint for a column, if it exists
  647. *
  648. * @param string $schema The schema the column is inside of
  649. * @param string $table The table the column is part of
  650. * @param string $column The column name
  651. * @return array|NULL An associative array with the keys `name` and `definition`, or `NULL`
  652. */
  653. private function getMSSQLDefaultConstraint($schema, $table, $column)
  654. {
  655. $constraint = $this->database->query(
  656. "SELECT
  657. dc.name,
  658. CAST(dc.definition AS VARCHAR(MAX)) AS definition
  659. FROM
  660. information_schema.columns AS c INNER JOIN
  661. sys.default_constraints AS dc ON
  662. OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)) = dc.parent_object_id AND
  663. COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'ColumnId') = dc.parent_column_id
  664. WHERE
  665. LOWER(c.table_schema) = %s AND
  666. LOWER(c.table_name) = %s AND
  667. LOWER(c.column_name) = %s AND
  668. c.table_catalog = DB_NAME()",
  669. strtolower($schema),
  670. strtolower($table),
  671. strtolower($column)
  672. );
  673. if (!$constraint->countReturnedRows()) {
  674. return NULL;
  675. }
  676. $row = $constraint->fetchRow();
  677. return array(
  678. 'name' => $row['name'],
  679. 'definition' => $row['definition']
  680. );
  681. }
  682. /**
  683. * Returns the primary key constraints for a table
  684. *
  685. * @param string $schema The schema the table is inside of
  686. * @param string $table The table to get the constraint for
  687. * @return array|NULL An associative array with the keys `name`, `columns` and `autoincrement` or `NULL`
  688. */
  689. private function getMSSQLPrimaryKeyConstraint($schema, $table)
  690. {
  691. $column_info = $this->database->query(
  692. "SELECT
  693. kcu.constraint_name AS constraint_name,
  694. LOWER(kcu.column_name) AS column_name,
  695. CASE
  696. WHEN
  697. COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND
  698. OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0
  699. THEN '1'
  700. ELSE '0'
  701. END AS auto_increment
  702. FROM
  703. information_schema.table_constraints AS con INNER JOIN
  704. information_schema.key_column_usage AS kcu ON
  705. con.table_name = kcu.table_name AND
  706. con.table_schema = kcu.table_schema AND
  707. con.constraint_name = kcu.constraint_name INNER JOIN
  708. information_schema.columns AS c ON
  709. c.table_name = kcu.table_name AND
  710. c.table_schema = kcu.table_schema AND
  711. c.column_name = kcu.column_name
  712. WHERE
  713. con.constraint_type = 'PRIMARY KEY' AND
  714. LOWER(con.table_schema) = %s AND
  715. LOWER(con.table_name) = %s AND
  716. con.table_catalog = DB_NAME()",
  717. strtolower($schema),
  718. strtolower($table)
  719. );
  720. if (!$column_info->countReturnedRows()) {
  721. return NULL;
  722. }
  723. $output = array(
  724. 'columns' => array()
  725. );
  726. foreach ($column_info as $row) {
  727. $output['columns'][] = $row['column_name'];
  728. $output['name'] = $row['constraint_name'];
  729. $output['autoincrement'] = (boolean) $row['auto_increment'];
  730. }
  731. return $output;
  732. }
  733. /**
  734. * Returns the unique constraints that a column is part of
  735. *
  736. * @param string $schema The schema the column is inside of
  737. * @param string $table The table the column is part of
  738. * @param string $column The column name
  739. * @return array An associative array of constraint_name => columns
  740. */
  741. private function getMSSQLUniqueConstraints($schema, $table, $column)
  742. {
  743. $constraint_columns = $this->database->query(
  744. "SELECT
  745. c.constraint_name,
  746. LOWER(kcu.column_name) AS column_name
  747. FROM
  748. information_schema.table_constraints AS c INNER JOIN
  749. information_schema.key_column_usage AS kcu ON
  750. c.table_name = kcu.table_name AND
  751. c.constraint_name = kcu.constraint_name
  752. WHERE
  753. c.constraint_name IN (
  754. SELECT
  755. c.constraint_name
  756. FROM
  757. information_schema.table_constraints AS c INNER JOIN
  758. information_schema.key_column_usage AS kcu ON
  759. c.table_name = kcu.table_name AND
  760. c.constraint_name = kcu.constraint_name
  761. WHERE
  762. c.constraint_type = 'UNIQUE' AND
  763. LOWER(c.table_schema) = %s AND
  764. LOWER(c.table_name) = %s AND
  765. LOWER(kcu.column_name) = %s AND
  766. c.table_catalog = DB_NAME()
  767. ) AND
  768. LOWER(c.table_schema) = %s AND
  769. c.table_catalog = DB_NAME()
  770. ORDER BY
  771. c.constraint_name
  772. ",
  773. strtolower($schema),
  774. strtolower($table),
  775. strtolower($column),
  776. strtolower($schema)
  777. );
  778. $unique_constraints = array();
  779. foreach ($constraint_columns as $row) {
  780. if (!isset($unique_constraints[$row['constraint_name']])) {
  781. $unique_constraints[$row['constraint_name']] = array();
  782. }
  783. $unique_constraints[$row['constraint_name']][] = $row['column_name'];
  784. }
  785. return $unique_constraints;
  786. }
  787. /**
  788. * Returns info about all foreign keys that involve the table and one of the columns specified
  789. *
  790. * @param string $table The table
  791. * @param string|array $columns The column, or an array of valid column names
  792. * @column array An array of associative arrays containing the keys `constraint_name`, `table`, `column`, `foreign_table` and `foreign_column`
  793. */
  794. private function getMySQLForeignKeys($table, $columns)
  795. {
  796. if (is_string($columns)) {
  797. $columns = array($columns);
  798. }
  799. $columns = array_map('strtolower', $columns);
  800. $tables = $this->getMySQLTables();
  801. $keys = array();
  802. foreach ($tables as $_table) {
  803. $row = $this->database->query("SHOW CREATE TABLE %r", $_table)->fetchRow();
  804. preg_match_all(
  805. '#CONSTRAINT\s+"(\w+)"\s+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))?#',
  806. $row['Create Table'],
  807. $matches,
  808. PREG_SET_ORDER
  809. );
  810. foreach ($matches as $match) {
  811. $points_to_column = strtolower($match[3]) == strtolower($table) && in_array(strtolower($match[4]), $columns);
  812. $is_column = strtolower($_table) == strtolower($table) && in_array(strtolower($match[2]), $columns);
  813. if (!$points_to_column && !$is_column) {
  814. continue;
  815. }
  816. $temp = array(
  817. 'constraint_name' => $match[1],
  818. 'table' => $_table,
  819. 'column' => $match[2],
  820. 'foreign_table' => $match[3],
  821. 'foreign_column' => $match[4],
  822. 'on_delete' => 'NO ACTION',
  823. 'on_update' => 'NO ACTION'
  824. );
  825. if (!empty($match[5])) {
  826. $temp['on_delete'] = $match[5];
  827. }
  828. if (!empty($match[6])) {
  829. $temp['on_update'] = $match[6];
  830. }
  831. $keys[] = $temp;
  832. }
  833. }
  834. return $keys;
  835. }
  836. /**
  837. * Returns a list of all tables in the database
  838. *
  839. * @return array An array of table names
  840. */
  841. private function getMySQLTables()
  842. {
  843. if (!isset($this->schema_info['version'])) {
  844. $version = $this->database->query("SELECT version()")->fetchScalar();
  845. $this->schema_info['version'] = substr($version, 0, strpos($version, '.'));
  846. }
  847. if ($this->schema_info['version'] <= 4) {
  848. $sql = 'SHOW TABLES';
  849. } else {
  850. $sql = "SHOW FULL TABLES WHERE table_type = 'BASE TABLE'";
  851. }
  852. $result = $this->database->query($sql);
  853. $tables = array();
  854. foreach ($result as $row) {
  855. $keys = array_keys($row);
  856. $tables[] = $row[$keys[0]];
  857. }
  858. return $tables;
  859. }
  860. /**
  861. * Returns an an array of the column name for a table
  862. *
  863. * @param string $table The table to retrieve the column names for
  864. * @return array The column names for the table
  865. */
  866. private function getSQLiteColumns($table)
  867. {
  868. $create_sql = $this->getSQLiteCreateTable($table);
  869. return array_keys(self::parseSQLiteColumnDefinitions($create_sql));
  870. }
  871. /**
  872. * Returns the SQL used to create a table
  873. *
  874. * @param string $table The table to retrieve the `CREATE TABLE` statement for
  875. * @return string The `CREATE TABLE` SQL statement
  876. */
  877. private function getSQLiteCreateTable($table)
  878. {
  879. if (!isset($this->schema_info['sqlite_create_tables'])) {
  880. $this->getSQLiteTables();
  881. }
  882. if (!isset($this->schema_info['sqlite_create_tables'][$table])) {
  883. return NULL;
  884. }
  885. return $this->schema_info['sqlite_create_tables'][$table];
  886. }
  887. /**
  888. * Returns a list of all foreign keys that reference the table, and optionally, column specified
  889. *
  890. * @param string $table All foreign keys returned will point to this table
  891. * @param string $column Only foreign keys pointing to this column will be returned
  892. * @return array An array of arrays containing they keys: `table`, `column`, `foreign_table`, `foreign_column`, `on_delete` and `on_update`
  893. */
  894. private function getSQLiteForeignKeys($table, $column=NULL)
  895. {
  896. $output = array();
  897. foreach ($this->getSQLiteTables() as $_table) {
  898. $create_sql = $this->getSQLiteCreateTable($_table);
  899. if (stripos($create_sql, 'references') === FALSE) {
  900. continue;
  901. }
  902. preg_match_all('#(?<=,|\(|\*/|\n)\s*[`"\[\']?(\w+)[`"\]\']?\s+(?:[a-z]+)(?:\([^)]*\))?(?:(?:\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\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*(?:,|/\*|(?:--[^\n]*\n)?\s*(?=\)))#mis', $create_sql, $matches, PREG_SET_ORDER);
  903. preg_match_all('#(?<=,|\(|\*/|\n)\s*(?:CONSTRAINT\s+["`\[]?\w+["`\]]?\s+)?FOREIGN\s+KEY\s*\(?\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*(?:,|/\*|(?:--[^\n]*\n)?\s*(?=\)))#mis', $create_sql, $matches2, PREG_SET_ORDER);
  904. foreach (array_merge($matches, $matches2) as $match) {
  905. $_column = $match[1];
  906. $foreign_table = $match[2];
  907. $foreign_column = $match[3];
  908. $on_delete = empty($match[4]) ? 'NO ACTION' : $match[4];
  909. $on_update = empty($match[5]) ? 'NO ACTION' : $match[5];
  910. if ($foreign_table != $table || ($column !== NULL && $column != $foreign_column)) {
  911. continue;
  912. }
  913. if (!$on_delete) {
  914. $on_delete = 'NO ACTION';
  915. }
  916. if (!$on_update) {
  917. $on_update = 'NO ACTION';
  918. }
  919. $output[] = array(
  920. 'table' => $_table,
  921. 'column' => $_column,
  922. 'foreign_table' => $foreign_table,
  923. 'foreign_column' => $foreign_column,
  924. 'on_delete' => $on_delete,
  925. 'on_update' => $on_update
  926. );
  927. }
  928. }
  929. return $output;
  930. }
  931. /**
  932. * Returns the indexes in the current SQLite database
  933. *
  934. * @return array An associative array with the key being the index name and the value an associative arrays, each containing the keys: `table`, `sql`
  935. */
  936. private function getSQLiteIndexes($table=NULL)
  937. {
  938. if (!isset($this->schema_info['sqlite_indexes'])) {
  939. $this->schema_info['sqlite_indexes'] = array();
  940. $rows = $this->database->query(
  941. "SELECT tbl_name AS \"table\", name, sql FROM sqlite_master WHERE type = 'index' AND sql <> ''"
  942. )->fetchAllRows();
  943. foreach ($rows as $row) {
  944. $this->schema_info['sqlite_indexes'][$row['name']] = array(
  945. 'table' => $row['table'],
  946. 'sql' => $row['sql']
  947. );
  948. }
  949. }
  950. $output = $this->schema_info['sqlite_indexes'];
  951. if ($table) {
  952. $new_output = array();
  953. foreach ($output as $name => $index) {
  954. if ($index['table'] != $table) {
  955. continue;
  956. }
  957. $new_output[$name] = $index;
  958. }
  959. $output = $new_output;
  960. }
  961. return $output;
  962. }
  963. /**
  964. * Returns the tables in the current SQLite database
  965. *
  966. * @return array
  967. */
  968. private function getSQLiteTables()
  969. {
  970. if (!isset($this->schema_info['sqlite_create_tables'])) {
  971. $this->schema_info['sqlite_create_tables'] = array();
  972. $res = $this->database->query(
  973. "SELECT name, sql FROM sqlite_master WHERE type = 'table'"
  974. )->fetchAllRows();
  975. foreach ($res as $row) {
  976. $this->schema_info['sqlite_create_tables'][$row['name']] = $row['sql'];
  977. }
  978. }
  979. $tables = array_keys($this->schema_info['sqlite_create_tables']);
  980. natcasesort($tables);
  981. return $tables;
  982. }
  983. /**
  984. * Returns the triggers in the current SQLite database
  985. *
  986. * @return array An associative array with the key being the trigger name and the value an associative arrays, each containing the keys: `table`, `sql`
  987. */
  988. private function getSQLiteTriggers($exclude_table=NULL)
  989. {
  990. if (!isset($this->schema_info['sqlite_triggers'])) {
  991. $this->schema_info['sqlite_triggers'] = array();
  992. $rows = $this->database->query(
  993. "SELECT tbl_name AS \"table\", name, sql FROM sqlite_master WHERE type = 'trigger'"
  994. )->fetchAllRows();
  995. foreach ($rows as $row) {
  996. $this->schema_info['sqlite_triggers'][$row['name']] = array(
  997. 'table' => $row['table'],
  998. 'sql' => $row['sql']
  999. );
  1000. }
  1001. }
  1002. $output = $this->schema_info['sqlite_triggers'];
  1003. if ($exclude_table) {
  1004. $new_output = array();
  1005. foreach ($output as $name => $trigger) {
  1006. if ($trigger['table'] == $exclude_table) {
  1007. continue;
  1008. }
  1009. $new_output[$name] = $trigger;
  1010. }
  1011. $output = $new_output;
  1012. }
  1013. return $output;
  1014. }
  1015. /**
  1016. * Removes the SQLite indexes from the internal schema tracker
  1017. *
  1018. * @param string $table The table to remove the indexes for
  1019. * @return void
  1020. */
  1021. private function removeSQLiteIndexes($table)
  1022. {
  1023. if (!isset($this->schema_info['sqlite_indexes'])) {
  1024. return;
  1025. }
  1026. $indexes = $this->schema_info['sqlite_indexes'];
  1027. $new_indexes = array();
  1028. foreach ($indexes as $name => $index) {
  1029. if ($index['table'] == $table) {
  1030. continue;
  1031. }
  1032. $new_indexes[$name] = $index;
  1033. }
  1034. $this->schema_info['sqlite_indexes'] = $new_indexes;
  1035. }
  1036. /**
  1037. * Removes a table from the list of SQLite table
  1038. *
  1039. * @param string $table The table to remove
  1040. * @return void
  1041. */
  1042. private function removeSQLiteTable($table)
  1043. {
  1044. if (!isset($this->schema_info['sqlite_create_tables'])) {
  1045. return;
  1046. }
  1047. unset($this->schema_info['sqlite_create_tables'][$table]);
  1048. }
  1049. /**
  1050. * Removes a SQLite trigger from the internal schema tracker
  1051. *
  1052. * @param string $name The trigger name
  1053. * @return void
  1054. */
  1055. private function removeSQLiteTrigger($name)
  1056. {
  1057. if (!isset($this->schema_info['sqlite_triggers'])) {
  1058. return;
  1059. }
  1060. unset($this->schema_info['sqlite_triggers'][$name]);
  1061. }
  1062. /**
  1063. * Removes the SQLite triggers for a table from the internal schema tracker
  1064. *
  1065. * @param string $table The table to remove the triggers for
  1066. * @return void
  1067. */
  1068. private function removeSQLiteTriggers($table)
  1069. {
  1070. if (!isset($this->schema_info['sqlite_triggers'])) {
  1071. return;
  1072. }
  1073. $triggers = $this->schema_info['sqlite_triggers'];
  1074. $new_triggers = array();
  1075. foreach ($triggers as $name => $trigger) {
  1076. if ($trigger['table'] == $table) {
  1077. continue;
  1078. }
  1079. $new_triggers[$name] = $trigger;
  1080. }
  1081. $this->schema_info['sqlite_triggers'] = $new_triggers;
  1082. }
  1083. /**
  1084. * Throws an fSQLException with the information provided
  1085. *
  1086. * @param string $error The error that occured
  1087. * @param string $sql The SQL statement that caused the error
  1088. * @return void
  1089. */
  1090. private function throwException($error, $sql)
  1091. {
  1092. $db_type_map = array(
  1093. 'db2' => 'DB2',
  1094. 'mssql' => 'MSSQL',
  1095. 'mysql' => 'MySQL',
  1096. 'oracle' => 'Oracle',
  1097. 'postgresql' => 'PostgreSQL',
  1098. 'sqlite' => 'SQLite'
  1099. );
  1100. throw new fSQLException(
  1101. '%1$s error (%2$s) in %3$s',
  1102. $db_type_map[$this->database->getType()],
  1103. $error,
  1104. $sql
  1105. );
  1106. }
  1107. /**
  1108. * Translates a Flourish SQL DDL statement into the dialect for the current database
  1109. *
  1110. * @internal
  1111. *
  1112. * @param string $sql The SQL statement to translate
  1113. * @param array &$rollback_statements SQL statements to rollback the returned SQL statements if something goes wrong - only applicable for MySQL `ALTER TABLE` statements
  1114. * @return array An array containing the translated `$sql` statement and an array of extra statements
  1115. */
  1116. public function translate($sql, &$rollback_statements=NULL)
  1117. {
  1118. $reset_sqlite_info = FALSE;
  1119. if (!isset($this->schema_info['sqlite_schema_info'])) {
  1120. $this->schema_info['sqlite_schema_info'] = TRUE;
  1121. $reset_sqlite_info = TRUE;
  1122. }
  1123. $new_sql = $sql;
  1124. $exception = NULL;
  1125. try {
  1126. $extra_statements = array();
  1127. if (!is_array($rollback_statements)) {
  1128. $rollback_statements = array();
  1129. }
  1130. $new_sql = $this->translateCreateTableStatements($new_sql, $extra_statements);
  1131. $new_sql = $this->translateAlterTableStatements($new_sql, $extra_statements, $rollback_statements);
  1132. if ($this->database->getType() == 'sqlite') {
  1133. $new_sql = $this->translateSQLiteDropTableStatements($new_sql, $extra_statements);
  1134. }
  1135. } catch (Exception $e) {
  1136. $exception = $e;
  1137. }
  1138. if ($reset_sqlite_info) {
  1139. unset($this->schema_info['sqlite_schema_info']);
  1140. unset($this->schema_info['sqlite_create_tables']);
  1141. unset($this->schema_info['sqlite_indexes']);
  1142. unset($this->schema_info['sqlite_triggers']);
  1143. }
  1144. if ($exception) {
  1145. throw $exception;
  1146. }
  1147. return array($new_sql, $extra_statements);
  1148. }
  1149. /**
  1150. * Translates the structure of `CREATE TABLE` statements to the database specific syntax
  1151. *
  1152. * @param string $sql The SQL to translate
  1153. * @param array &$extra_statements Any extra SQL statements that need to be added
  1154. * @param array &$rollback_statements SQL statements to rollback `$sql` and `$extra_statements` if something goes wrong
  1155. * @return string The translated SQL
  1156. */
  1157. private function translateAlterTableStatements($sql, &$extra_statements, &$rollback_statements=NULL)
  1158. {
  1159. if (!preg_match('#^\s*ALTER\s+TABLE\s+(\w+|"[^"]+")\s+(.*)$#siD', $sql, $table_matches) && !preg_match('#^\s*COMMENT\s+ON\s+COLUMN\s+"?((?:\w+"?\."?)?\w+)"?\.("?\w+"?\s+IS\s+(?:\'.*\'|%\d+\$s))\s*$#Dis', $sql, $table_matches)) {
  1160. return $sql;
  1161. }
  1162. $statement = $table_matches[2];
  1163. $data = array(
  1164. 'table' => $table_matches[1]
  1165. );
  1166. if (preg_match('#"?(\w+)"?\s+IS\s+(\'.*\'|:string\w+|%\d+\$s)\s*$#Dis', $statement, $statement_matches)) {
  1167. $data['type'] = 'column_comment';
  1168. $data['column_name'] = trim($statement_matches[1], '"');
  1169. $data['comment'] = $statement_matches[2];
  1170. } elseif (preg_match('#RENAME\s+TO\s+(\w+|"[^"]+")\s*$#isD', $statement, $statement_matches)) {
  1171. $data['type'] = 'rename_table';
  1172. $data['new_table_name'] = trim($statement_matches[1], '"');
  1173. } elseif (preg_match('#RENAME\s+COLUMN\s+(\w+|"[^"]+")\s+TO\s+(\w+|"[^"]+")\s*$#isD', $statement, $statement_matches)) {
  1174. $data['type'] = 'rename_column';
  1175. $data['column_name'] = trim($statement_matches[1], '"');
  1176. $data['new_column_name'] = trim($statement_matches[2], '"');
  1177. } elseif (preg_match('#ADD\s+COLUMN\s+("?(\w+)"?.*)$#isD', $statement, $statement_matches)) {
  1178. $data['type'] = 'add_column';
  1179. $data['column_definition'] = $statement_matches[1];
  1180. $data['column_name'] = $statement_matches[2];
  1181. } elseif (preg_match('#DROP\s+COLUMN\s+(\w+|"[^"]+")\s*$#isD', $statement, $statement_matches)) {
  1182. $data['type'] = 'drop_column';
  1183. $data['column_name'] = trim($statement_matches[1], '"');
  1184. } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+TYPE\s+(.*?)\s*$#isD', $statement, $statement_matches)) {
  1185. $data['type'] = 'alter_type';
  1186. $data['column_name'] = trim($statement_matches[1], '"');
  1187. $data['data_type'] = $statement_matches[2];
  1188. } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+DROP\s+DEFAULT\s*$#isD', $statement, $statement_matches)) {
  1189. $data['type'] = 'drop_default';
  1190. $data['column_name'] = trim($statement_matches[1], '"');
  1191. } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+SET\s+DEFAULT\s+(.*?)\s*$#isD', $statement, $statement_matches)) {
  1192. $data['type'] = 'set_default';
  1193. $data['column_name'] = trim($statement_matches[1], '"');
  1194. $data['default_value'] = trim($statement_matches[2], '"');
  1195. } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+DROP\s+NOT\s+NULL\s*$#isD', $statement, $statement_matches)) {
  1196. $data['type'] = 'drop_not_null';
  1197. $data['column_name'] = trim($statement_matches[1], '"');
  1198. } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+SET\s+NOT\s+NULL(\s+DEFAULT\s+(.*))?\s*$#isD', $statement, $statement_matches)) {
  1199. $data['type'] = 'set_not_null';
  1200. $data['column_name'] = trim($statement_matches[1], '"');
  1201. if (isset($statement_matches[2])) {
  1202. $data['default'] = $statement_matches[3];
  1203. }
  1204. } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+DROP\s+CHECK\s*$#isD', $statement, $statement_matches)) {
  1205. $data['type'] = 'drop_check_constraint';
  1206. $data['column_name'] = trim($statement_matches[1], '"');
  1207. } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+SET\s+CHECK\s+IN\s+(\(.*?\))\s*$#isD', $statement, $statement_matches)) {
  1208. $data['type'] = 'set_check_constraint';
  1209. $data['column_name'] = trim($statement_matches[1], '"');
  1210. $data['constraint'] = ' CHECK(' . $statement_matches[1] . ' IN ' . $statement_matches[2] . ')';
  1211. } elseif (preg_match('#DROP\s+PRIMARY\s+KEY\s*$#isD', $statement, $statement_matches)) {
  1212. $data['type'] = 'drop_primary_key';
  1213. } elseif (preg_match('#ADD\s+PRIMARY\s+KEY\s*\(\s*([^\)]+?)\s*\)(\s+AUTOINCREMENT)?\s*$#isD', $statement, $statement_matches)) {
  1214. $data['type'] = 'add_primary_key';
  1215. $data['column_names'] = preg_split(
  1216. '#"?\s*,\s*"?#',
  1217. trim($statement_matches[1], '"'),
  1218. -1,
  1219. PREG_SPLIT_NO_EMPTY
  1220. );
  1221. $data['autoincrement'] = count($data['column_names']) == 1 && !empty($statement_matches[2]);
  1222. if (count($data['column_names']) == 1) {
  1223. $data['column_name'] = reset($data['column_names']);
  1224. }
  1225. } elseif (preg_match('#DROP\s+FOREIGN\s+KEY\s*\(\s*(\w+|"[^"]+")\s*\)\s*$#isD', $statement, $statement_matches)) {
  1226. $data['type'] = 'drop_foreign_key';
  1227. $data['column_name'] = trim($statement_matches[1], '"');
  1228. } elseif (preg_match('#ADD\s+FOREIGN\s+KEY\s*\((\w+|"[^"]+")\)\s+REFERENCES\s+("?(\w+)"?\s*\(\s*"?(\w+)"?\s*\)\s*.*)\s*$#isD', $statement, $statement_matches)) {
  1229. $data['type'] = 'add_foreign_key';
  1230. $data['column_name'] = trim($statement_matches[1], '"');
  1231. $data['references'] = $statement_matches[2];
  1232. $data['foreign_table'] = self::unescapeIdentifier($statement_matches[3]);
  1233. $data['foreign_column'] = self::unescapeIdentifier($statement_matches[4]);
  1234. } elseif (preg_match('#DROP\s+UNIQUE\s*\(\s*([^\)]+?)\s*\)\s*$#isD', $statement, $statement_matches)) {
  1235. $data['type'] = 'drop_unique';
  1236. $data['column_names'] = preg_split(
  1237. '#"?\s*,\s*"?#',
  1238. trim($statement_matches[1], '"'),
  1239. -1,
  1240. PREG_SPLIT_NO_EMPTY
  1241. );
  1242. if (count($data['column_names']) == 1) {
  1243. $data['column_name'] = reset($data['column_names']);
  1244. }
  1245. } elseif (preg_match('#ADD\s+UNIQUE\s*\(\s*([^\)]+?)\s*\)\s*$#isD', $statement, $statement_matches)) {
  1246. $data['type'] = 'add_unique';
  1247. $data['column_names'] = preg_split(
  1248. '#"?\s*,\s*"?#',
  1249. trim($statement_matches[1], '"'),
  1250. -1,
  1251. PREG_SPLIT_NO_EMPTY
  1252. );
  1253. if (count($data['column_names']) == 1) {
  1254. $data['column_name'] = reset($data['column_names']);
  1255. }
  1256. } else {
  1257. return $sql;
  1258. }
  1259. $data['table'] = self::unescapeIdentifier($data['table']);
  1260. if (isset($data['new_table_name'])) {
  1261. $data['new_table_name'] = self::unescapeIdentifier($data['new_table_name']);
  1262. }
  1263. if (isset($data['column_name'])) {
  1264. $data['column_name'] = self::unescapeIdentifier($data['column_name']);
  1265. }
  1266. if (isset($data['column_names'])) {
  1267. $data['column_names'] = array_map(
  1268. array('fSQLSchemaTranslation', 'unescapeIdentifier'),
  1269. $data['column_names']
  1270. );
  1271. }
  1272. if (isset($data['new_column_name'])) {
  1273. $data['new_column_name'] = self::unescapeIdentifier($data['new_column_name']);
  1274. }
  1275. if ($this->database->getType() == 'db2') {
  1276. $sql = $this->translateDB2AlterTableStatements($sql, $extra_statements, $data);
  1277. }
  1278. if ($this->database->getType() == 'mssql') {
  1279. $sql = $this->translateMSSQLAlterTableStatements($sql, $extra_statements, $data);
  1280. }
  1281. if ($this->database->getType() == 'mysql') {
  1282. $sql = $this->translateMySQLAlterTableStatements($sql, $extra_statements, $rollback_statements, $data);
  1283. }
  1284. if ($this->database->getType() == 'oracle') {
  1285. $sql = $this->translateOracleAlterTableStatements($sql, $extra_statements, $data);
  1286. }
  1287. if ($this->database->getType() == 'postgresql') {
  1288. $sql = $this->translatePostgreSQLAlterTableStatements($sql, $extra_statements, $data);
  1289. }
  1290. if ($this->database->getType() == 'sqlite') {
  1291. if ($data['type'] == 'rename_table') {
  1292. $sql = $this->translateSQLiteRenameTableStatements($sql, $extra_statements, $data);
  1293. } else {
  1294. $sql = $this->translateSQLiteAlterTableStatements($sql, $extra_statements, $data);
  1295. }
  1296. }
  1297. // All databases except for MySQL and Oracle support transactions around data definition queries
  1298. // All of the Oracle statements will fail on the first query, if at all, so we don't need to
  1299. // worry too much. MySQL is a huge pain though.
  1300. if (!in_array($this->database->getType(), array('mysql', 'oracle'))) {
  1301. array_unshift($extra_statements, $sql);
  1302. if (!$this->databa

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