/inc/flourish/fSQLSchemaTranslation.php
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
- <?php
- /**
- * Adds cross-database `CREATE TABLE`, `ALTER TABLE` and `COMMENT ON COLUMN` statements to fSQLTranslation
- *
- * @copyright Copyright (c) 2011 Will Bond
- * @author Will Bond [wb] <will@flourishlib.com>
- * @license http://flourishlib.com/license
- *
- * @package Flourish
- * @link http://flourishlib.com/fSQLSchemaTranslation
- *
- * @version 1.0.0b2
- * @changes 1.0.0b2 Fixed detection of explicitly named SQLite foreign key constraints [wb, 2011-08-23]
- * @changes 1.0.0b The initial implementation [wb, 2011-05-09]
- */
- class fSQLSchemaTranslation
- {
- /**
- * Converts a SQL identifier to lower case and removes double quotes
- *
- * @param string $identifier The SQL identifier
- * @return string The unescaped identifier
- */
- static private function unescapeIdentifier($identifier)
- {
- return str_replace('"', '', strtolower($identifier));
- }
- /**
- * Composes text using fText if loaded
- *
- * @param string $message The message to compose
- * @param mixed $component A string or number to insert into the message
- * @param mixed ...
- * @return string The composed and possible translated message
- */
- static protected function compose($message)
- {
- $args = array_slice(func_get_args(), 1);
-
- if (class_exists('fText', FALSE)) {
- return call_user_func_array(
- array('fText', 'compose'),
- array($message, $args)
- );
- } else {
- return vsprintf($message, $args);
- }
- }
- /**
- * Accepts a CREATE TABLE statement and parses out the column definitions
- *
- * The return value is an associative array with the keys being column
- * names and the values being arrays containing the following keys:
- * - definition: (string) the complete column definition
- * - pieces: (array) an associative array that can be joined back together to make the definition
- * - beginning
- * - column_name
- * - data_type
- * - not_null
- * - null
- * - default
- * - unique
- * - primary_key
- * - check_constraint
- * - foreign_key
- * - deferrable
- * - comment/end
- *
- * @param string $sql The SQL `CREATE TABLE` statement
- * @return array An associative array of information for each column - see method description for details
- */
- static private function parseSQLiteColumnDefinitions($sql)
- {
- preg_match_all(
- '#(?<=,|\(|\*/|\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',
- $sql,
- $matches,
- PREG_SET_ORDER
- );
- $output = array();
- foreach ($matches as $match) {
- $comment = '';
- foreach (array(16, 17, 18, 19) as $key) {
- if (isset($match[$key])) {
- $comment .= $match[$key];
- }
- }
- $output[strtolower($match[2])] = array(
- 'definition' => $match[0],
- 'pieces' => array(
- 'beginning' => $match[1],
- 'column_name' => $match[2],
- 'data_type' => $match[3],
- 'not_null' => $match[6],
- 'null' => $match[7],
- 'default' => $match[8],
- 'unique' => $match[10],
- 'primary_key' => $match[11],
- 'check_constraint' => $match[12],
- 'foreign_key' => $match[13],
- 'deferrable' => $match[14],
- 'comment/end' => $match[15]
- )
- );
- }
- return $output;
- }
-
-
- /**
- * Removes a search string from a `CREATE TABLE` statement
- *
- * @param string $create_table_sql The SQL `CREATE TABLE` statement
- * @param string $search The string to remove
- * @return string The modified `CREATE TABLE` statement
- */
- static private function removeFromSQLiteCreateTable($create_table_sql, $search)
- {
- if (preg_match('#,(\s*--.*)?\s*$#D', $search)) {
- $regex = '#' . preg_quote($search, '#') . '#';
- } else {
- $regex = '#,(\s*/\*.*?\*/\s*|\s*--[^\n]+\n\s*)?\s*' . preg_quote($search, '#') . '\s*#';
- }
- return preg_replace($regex, "\\1\n", $create_table_sql);
- }
-
-
- /**
- * The fDatabase instance
- *
- * @var fDatabase
- */
- private $database;
- /**
- * Database-specific schema information needed for translation
- *
- * @var array
- */
- private $schema_info;
-
-
- /**
- * Sets up the class
- *
- * @param fDatabase $database The database being translated for
- * @return fSQLSchemaTranslation
- */
- public function __construct($database)
- {
- $this->database = $database;
- $this->schema_info = array();
- }
-
-
- /**
- * All requests that hit this method should be requests for callbacks
- *
- * @internal
- *
- * @param string $method The method to create a callback for
- * @return callback The callback for the method requested
- */
- public function __get($method)
- {
- return array($this, $method);
- }
- /**
- * Adds a SQLite index to the internal schema tracker
- *
- * @param string $name The index name
- * @param string $table The table the index applies to
- * @param string $sql The SQL definition of the index
- * @return void
- */
- private function addSQLiteIndex($name, $table, $sql)
- {
- if (!isset($this->schema_info['sqlite_indexes'])) {
- $this->schema_info['sqlite_indexes'] = array();
- }
- $this->schema_info['sqlite_indexes'][$name] = array(
- 'table' => $table,
- 'sql' => $sql
- );
- }
- /**
- * Stores the SQL used to create a table
- *
- * @param string $table The table to set the `CREATE TABLE` statement for
- * @param string $sql The SQL used to create the table
- * @return void
- */
- private function addSQLiteTable($table, $sql)
- {
- if (!isset($this->schema_info['sqlite_create_tables'])) {
- $this->getSQLiteTables();
- }
- $this->schema_info['sqlite_create_tables'][$table] = $sql;
- }
- /**
- * Adds a SQLite trigger to the internal schema tracker
- *
- * @param string $name The trigger name
- * @param string $table The table the trigger applies to
- * @param string $sql The SQL definition of the trigger
- * @return void
- */
- private function addSQLiteTrigger($name, $table, $sql)
- {
- if (!isset($this->schema_info['sqlite_triggers'])) {
- $this->schema_info['sqlite_triggers'] = array();
- }
- $this->schema_info['sqlite_triggers'][$name] = array(
- 'table' => $table,
- 'sql' => $sql
- );
- }
-
-
- /**
- * Creates a trigger for SQLite that handles an on delete clause
- *
- * @param array &$extra_statements An array of extra SQL statements to be added to the SQL
- * @param string $referencing_table The table that contains the foreign key
- * @param string $referencing_column The column the foreign key constraint is on
- * @param string $referenced_table The table the foreign key references
- * @param string $referenced_column The column the foreign key references
- * @param string $delete_clause What is to be done on a delete
- * @return string The trigger
- */
- private function createSQLiteForeignKeyTriggerOnDelete(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $delete_clause)
- {
- switch (strtolower($delete_clause)) {
- case 'no action':
- case 'restrict':
- $name = 'fkd_res_' . $referencing_table . '_' . $referencing_column;
- $extra_statements[] = 'CREATE TRIGGER ' . $name . '
- BEFORE DELETE ON "' . $referenced_table . '"
- FOR EACH ROW BEGIN
- 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 . '"\')
- WHERE (SELECT "' . $referencing_column . '" FROM "' . $referencing_table . '" WHERE "' . $referencing_column . '" = OLD."' . $referenced_table . '") IS NOT NULL;
- END';
- $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
- break;
-
- case 'set null':
- $name = 'fkd_nul_' . $referencing_table . '_' . $referencing_column;
- $extra_statements[] = 'CREATE TRIGGER ' . $name . '
- BEFORE DELETE ON "' . $referenced_table . '"
- FOR EACH ROW BEGIN
- UPDATE "' . $referencing_table . '" SET "' . $referencing_column . '" = NULL WHERE "' . $referencing_column . '" = OLD."' . $referenced_column . '";
- END';
- $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
- break;
-
- case 'cascade':
- $name = 'fkd_cas_' . $referencing_table . '_' . $referencing_column;
- $extra_statements[] = 'CREATE TRIGGER ' . $name . '
- BEFORE DELETE ON "' . $referenced_table . '"
- FOR EACH ROW BEGIN
- DELETE FROM "' . $referencing_table . '" WHERE "' . $referencing_column . '" = OLD."' . $referenced_column . '";
- END';
- $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
- break;
- }
- }
-
-
- /**
- * Creates a trigger for SQLite that handles an on update clause
- *
- * @param array &$extra_statements An array of extra SQL statements to be added to the SQL
- * @param string $referencing_table The table that contains the foreign key
- * @param string $referencing_column The column the foreign key constraint is on
- * @param string $referenced_table The table the foreign key references
- * @param string $referenced_column The column the foreign key references
- * @param string $update_clause What is to be done on an update
- * @return string The trigger
- */
- private function createSQLiteForeignKeyTriggerOnUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $update_clause)
- {
- switch (strtolower($update_clause)) {
- case 'no action':
- case 'restrict':
- $name = 'fku_res_' . $referencing_table . '_' . $referencing_column;
- $extra_statements[] = 'CREATE TRIGGER ' . $name . '
- BEFORE UPDATE ON "' . $referenced_table . '"
- FOR EACH ROW BEGIN
- 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 . '"\')
- WHERE (SELECT "' . $referencing_column . '" FROM "' . $referencing_table . '" WHERE "' . $referencing_column . '" = OLD."' . $referenced_column . '") IS NOT NULL;
- END';
- $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
- break;
-
- case 'set null':
- $name = 'fku_nul_' . $referencing_table . '_' . $referencing_column;
- $extra_statements[] = 'CREATE TRIGGER ' . $name . '
- BEFORE UPDATE ON "' . $referenced_table . '"
- FOR EACH ROW BEGIN
- UPDATE "' . $referencing_table . '" SET "' . $referencing_column . '" = NULL WHERE OLD."' . $referenced_column . '" <> NEW."' . $referenced_column . '" AND "' . $referencing_column . '" = OLD."' . $referenced_column . '";
- END';
- $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
- break;
-
- case 'cascade':
- $name = 'fku_cas_' . $referencing_table . '_' . $referencing_column;
- $extra_statements[] = 'CREATE TRIGGER ' . $name . '
- BEFORE UPDATE ON "' . $referenced_table . '"
- FOR EACH ROW BEGIN
- UPDATE "' . $referencing_table . '" SET "' . $referencing_column . '" = NEW."' . $referenced_column . '" WHERE OLD."' . $referenced_column . '" <> NEW."' . $referenced_column . '" AND "' . $referencing_column . '" = OLD."' . $referenced_column . '";
- END';
- $this->addSQLiteTrigger($name, $referenced_table, end($extra_statements));
- break;
- }
- }
-
-
- /**
- * Creates a trigger for SQLite that prevents inserting or updating to values the violate a `FOREIGN KEY` constraint
- *
- * @param array &$extra_statements An array of extra SQL statements to be added to the SQL
- * @param string $referencing_table The table that contains the foreign key
- * @param string $referencing_column The column the foriegn key constraint is on
- * @param string $referenced_table The table the foreign key references
- * @param string $referenced_column The column the foreign key references
- * @param boolean $referencing_not_null If the referencing columns is set to not null
- * @return string The trigger
- */
- private function createSQLiteForeignKeyTriggerValidInsertUpdate(&$extra_statements, $referencing_table, $referencing_column, $referenced_table, $referenced_column, $referencing_not_null)
- {
- // Verify key on inserts
- $name = 'fki_ver_' . $referencing_table . '_' . $referencing_column;
- $sql = 'CREATE TRIGGER ' . $name . '
- BEFORE INSERT ON "' . $referencing_table . '"
- FOR EACH ROW BEGIN
- SELECT RAISE(ROLLBACK, \'insert on table "' . $referencing_table . '" violates foreign key constraint on column "' . $referencing_column . '"\')
- WHERE ';
- if (!$referencing_not_null) {
- $sql .= 'NEW."' . $referencing_column . '" IS NOT NULL AND ';
- }
- $sql .= ' (SELECT "' . $referenced_column . '" FROM "' . $referenced_table . '" WHERE "' . $referenced_column . '" = NEW."' . $referencing_column . '") IS NULL;
- END';
-
- $extra_statements[] = $sql;
- $this->addSQLiteTrigger($name, $referencing_table, end($extra_statements));
-
- // Verify key on updates
- $name = 'fku_ver_' . $referencing_table . '_' . $referencing_column;
- $sql = 'CREATE TRIGGER ' . $name . '
- BEFORE UPDATE ON "' . $referencing_table . '"
- FOR EACH ROW BEGIN
- SELECT RAISE(ROLLBACK, \'update on table "' . $referencing_table . '" violates foreign key constraint on column "' . $referencing_column . '"\')
- WHERE ';
- if (!$referencing_not_null) {
- $sql .= 'NEW."' . $referencing_column . '" IS NOT NULL AND ';
- }
- $sql .= ' (SELECT "' . $referenced_column . '" FROM "' . $referenced_table . '" WHERE "' . $referenced_column . '" = NEW."' . $referencing_column . '") IS NULL;
- END';
-
- $extra_statements[] = $sql;
- $this->addSQLiteTrigger($name, $referencing_table, end($extra_statements));
- }
-
-
- /**
- * Generates a 30 character constraint name for use with `ALTER TABLE` statements
- *
- * @param string $sql The `ALTER TABLE` statement
- * @param string $type A 2-character string representing the type of constraint
- */
- private function generateConstraintName($sql, $type)
- {
- $constraint = '_' . $type;
- $constraint = '_' . substr(time(), -8) . $constraint;
- return substr(md5(strtolower($sql)), 0, 30 - strlen($constraint)) . $constraint;
- }
- /**
- * Returns the check constraint for a table and column
- *
- * @param string $schema The schema the table is in
- * @param string $table The table the column is in
- * @param string $column The column to get the check constraint for
- * @return array|NULL An associative array with the keys: `name` and `definition` or `NULL`
- */
- private function getDB2CheckConstraint($schema, $table, $column)
- {
- $constraint = $this->database->query(
- "SELECT
- CH.TEXT,
- CH.CONSTNAME
- FROM
- SYSCAT.COLUMNS AS C INNER JOIN
- SYSCAT.COLCHECKS AS CC ON
- C.TABSCHEMA = CC.TABSCHEMA AND
- C.TABNAME = CC.TABNAME AND
- C.COLNAME = CC.COLNAME AND
- CC.USAGE = 'R' INNER JOIN
- SYSCAT.CHECKS AS CH ON
- C.TABSCHEMA = CH.TABSCHEMA AND
- C.TABNAME = CH.TABNAME AND
- CH.TYPE = 'C' AND
- CH.CONSTNAME = CC.CONSTNAME
- WHERE
- LOWER(C.TABSCHEMA) = %s AND
- LOWER(C.TABNAME) = %s AND
- LOWER(C.COLNAME) = %s",
- $schema,
- $table,
- $column
- );
- if (!$constraint->countReturnedRows()) {
- return NULL;
- }
- $row = $constraint->fetchRow();
- return array(
- 'name' => $row['constname'],
- 'definition' => $row['text']
- );
- }
- /**
- * Returns the foreign key constraints that involve a specific table or table and column
- *
- * @param string $schema The schema the table is in
- * @param string $table The table the column is in
- * @param string $column The column to get the foreign keys for and the foreign keys that point to
- * @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`
- */
- private function getDB2ForeignKeyConstraints($schema, $table, $column=NULL)
- {
- if ($column) {
- $where_conditions = "((
- LOWER(R.TABSCHEMA) = %s AND
- LOWER(R.TABNAME) = %s AND
- LOWER(K.COLNAME) = %s
- ) OR (
- LOWER(R.REFTABSCHEMA) = %s AND
- LOWER(R.REFTABNAME) = %s AND
- LOWER(FK.COLNAME) = %s
- ))";
- $params = array(
- strtolower($schema),
- strtolower($table),
- strtolower($column),
- strtolower($schema),
- strtolower($table),
- strtolower($column)
- );
- } else {
- $where_conditions = "LOWER(R.REFTABSCHEMA) = %s AND LOWER(R.REFTABNAME) = %s";
- $params = array(
- strtolower($schema),
- strtolower($table)
- );
- }
- array_unshift(
- $params,
- "SELECT
- R.CONSTNAME AS CONSTRAINT_NAME,
- TRIM(LOWER(R.TABSCHEMA)) AS \"SCHEMA\",
- LOWER(R.TABNAME) AS \"TABLE\",
- LOWER(K.COLNAME) AS \"COLUMN\",
- TRIM(LOWER(R.REFTABSCHEMA)) AS FOREIGN_SCHEMA,
- LOWER(R.REFTABNAME) AS FOREIGN_TABLE,
- LOWER(FK.COLNAME) AS FOREIGN_COLUMN,
- CASE R.DELETERULE WHEN 'C' THEN 'CASCADE' WHEN 'A' THEN 'NO ACTION' WHEN 'R' THEN 'RESTRICT' ELSE 'SET NULL' END AS ON_DELETE,
- CASE R.UPDATERULE WHEN 'A' THEN 'NO ACTION' WHEN 'R' THEN 'RESTRICT' END AS ON_UPDATE
- FROM
- SYSCAT.REFERENCES AS R INNER JOIN
- SYSCAT.KEYCOLUSE AS K ON
- R.CONSTNAME = K.CONSTNAME AND
- R.TABSCHEMA = K.TABSCHEMA AND
- R.TABNAME = K.TABNAME INNER JOIN
- SYSCAT.KEYCOLUSE AS FK ON
- R.REFKEYNAME = FK.CONSTNAME AND
- R.REFTABSCHEMA = FK.TABSCHEMA AND
- R.REFTABNAME = FK.TABNAME
- WHERE
- $where_conditions
- ORDER BY
- LOWER(R.CONSTNAME) ASC"
- );
- $constraints = call_user_func_array($this->database->query, $params);
- $keys = array();
- foreach ($constraints as $constraint) {
- $name = $constraint['constraint_name'] . $constraint['table'];
- $keys[$name] = $constraint;
- }
- return $keys;
- }
- /**
- * Returns the primary key for a table
- *
- * @param string $schema The schema the table is in
- * @param string $table The table to get the primary key for
- * @return array The columns in the primary key
- */
- private function getDB2PrimaryKeyConstraint($schema, $table)
- {
- $constraints = $this->database->query(
- "SELECT
- LOWER(C.COLNAME) AS \"COLUMN\"
- FROM
- SYSCAT.INDEXES AS I INNER JOIN
- SYSCAT.INDEXCOLUSE AS C ON
- I.INDSCHEMA = C.INDSCHEMA AND
- I.INDNAME = C.INDNAME
- WHERE
- I.UNIQUERULE IN ('P') AND
- LOWER(I.TABSCHEMA) = %s AND
- LOWER(I.TABNAME) = %s
- ORDER BY
- LOWER(I.INDNAME) ASC
- ",
- strtolower($schema),
- strtolower($table)
- );
- $key = array();
- foreach ($constraints as $constraint) {
- $key[] = $constraint['column'];
- }
- return $key;
- }
- /**
- * Returns the unique keys for a table and column
- *
- * @param string $schema The schema the table is in
- * @param string $table The table to get the unique keys for
- * @param string $column The column to filter the unique keys by
- * @return array An associative array of the key being the constraint name and the value being the columns in the unique key
- */
- private function getDB2UniqueConstraints($schema, $table, $column)
- {
- $constraints = $this->database->query(
- "SELECT
- CD.CONSTNAME AS CONSTRAINT_NAME,
- LOWER(C.COLNAME) AS \"COLUMN\"
- FROM
- SYSCAT.INDEXES AS I INNER JOIN
- SYSCAT.CONSTDEP AS CD ON
- I.TABSCHEMA = CD.TABSCHEMA AND
- I.TABNAME = CD.TABNAME AND
- CD.BTYPE = 'I' AND
- CD.BNAME = I.INDNAME INNER JOIN
- SYSCAT.INDEXCOLUSE AS C ON
- I.INDSCHEMA = C.INDSCHEMA AND
- I.INDNAME = C.INDNAME
- WHERE
- I.UNIQUERULE IN ('U') AND
- LOWER(I.TABSCHEMA) = %s AND
- LOWER(I.TABNAME) = %s
- ORDER BY
- LOWER(I.INDNAME) ASC
- ",
- strtolower($schema),
- strtolower($table)
- );
- $keys = array();
- foreach ($constraints as $constraint) {
- if (!isset($keys[$constraint['constraint_name']])) {
- $keys[$constraint['constraint_name']] = array();
- }
- $keys[$constraint['constraint_name']][] = $constraint['column'];
- }
- $new_keys = array();
- $column = strtolower($column);
- foreach ($keys as $name => $columns) {
- if (!in_array($column, $columns)) {
- continue;
- }
- $new_keys[$name] = $columns;
- }
- $keys = $new_keys;
- return $keys;
- }
- /**
- * Returns the check constraint for a column, if it exists
- *
- * @param string $schema The schema the column is inside of
- * @param string $table The table the column is part of
- * @param string $column The column name
- * @return array|NULL An associative array with the keys `name` and `definition`, or `NULL`
- */
- private function getMSSQLCheckConstraint($schema, $table, $column)
- {
- $constraint = $this->database->query(
- "SELECT
- cc.check_clause AS 'constraint',
- ccu.constraint_name
- FROM
- INFORMATION_SCHEMA.COLUMNS AS c INNER JOIN
- 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 INNER JOIN
- INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON
- ccu.constraint_name = cc.constraint_name AND
- ccu.constraint_catalog = cc.constraint_catalog
- WHERE
- LOWER(c.table_schema) = %s AND
- LOWER(c.table_name) = %s AND
- LOWER(c.column_name) = %s AND
- c.table_catalog = DB_NAME()",
- strtolower($schema),
- strtolower($table),
- strtolower($column)
- );
-
- if (!$constraint->countReturnedRows()) {
- return NULL;
- }
-
- $row = $constraint->fetchRow();
- return array(
- 'name' => $row['constraint_name'],
- 'definition' => $row['constraint']
- );
- }
- /**
- * Returns the foreign key constraints that a column is part of
- *
- * @param string $schema The schema the column is inside of
- * @param string $table The table the column is part of
- * @param string|array $column The column name(s)
- * @return array An array of constraint names that reference the column(s)
- */
- private function getMSSQLForeignKeyConstraints($schema, $table, $column)
- {
- settype($column, 'array');
- $constraints = $this->database->query(
- "SELECT
- LOWER(tc.table_schema + '.' + tc.table_name) AS 'table',
- LOWER(tc.table_schema) AS 'schema',
- LOWER(tc.table_name) AS 'table_without_schema',
- LOWER(kcu.column_name) AS 'column',
- kcu.constraint_name AS name
- FROM
- information_schema.table_constraints AS tc INNER JOIN
- information_schema.key_column_usage AS kcu ON
- tc.constraint_name = kcu.constraint_name AND
- tc.constraint_catalog = kcu.constraint_catalog AND
- tc.constraint_schema = kcu.constraint_schema AND
- tc.table_name = kcu.table_name INNER JOIN
- information_schema.referential_constraints AS rc ON
- kcu.constraint_name = rc.constraint_name AND
- kcu.constraint_catalog = rc.constraint_catalog AND
- kcu.constraint_schema = rc.constraint_schema INNER JOIN
- information_schema.constraint_column_usage AS ccu ON
- ccu.constraint_name = rc.unique_constraint_name AND
- ccu.constraint_catalog = rc.constraint_catalog AND
- ccu.constraint_schema = rc.constraint_schema
- WHERE
- tc.constraint_type = 'FOREIGN KEY' AND
- (
- LOWER(tc.table_schema) = %s AND
- LOWER(ccu.table_name) = %s AND
- LOWER(ccu.column_name) IN (%s)
- ) OR (
- LOWER(tc.table_schema) = %s AND
- LOWER(kcu.table_name) = %s AND
- LOWER(kcu.column_name) IN (%s)
- ) AND
- tc.constraint_catalog = DB_NAME()",
- strtolower($schema),
- strtolower($table),
- array_map('strtolower', $column),
- strtolower($schema),
- strtolower($table),
- array_map('strtolower', $column)
- );
- return $constraints->fetchAllRows();
- }
- /**
- * Returns the default constraint for a column, if it exists
- *
- * @param string $schema The schema the column is inside of
- * @param string $table The table the column is part of
- * @param string $column The column name
- * @return array|NULL An associative array with the keys `name` and `definition`, or `NULL`
- */
- private function getMSSQLDefaultConstraint($schema, $table, $column)
- {
- $constraint = $this->database->query(
- "SELECT
- dc.name,
- CAST(dc.definition AS VARCHAR(MAX)) AS definition
- FROM
- information_schema.columns AS c INNER JOIN
- sys.default_constraints AS dc ON
- OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)) = dc.parent_object_id AND
- COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'ColumnId') = dc.parent_column_id
- WHERE
- LOWER(c.table_schema) = %s AND
- LOWER(c.table_name) = %s AND
- LOWER(c.column_name) = %s AND
- c.table_catalog = DB_NAME()",
- strtolower($schema),
- strtolower($table),
- strtolower($column)
- );
-
- if (!$constraint->countReturnedRows()) {
- return NULL;
- }
-
- $row = $constraint->fetchRow();
- return array(
- 'name' => $row['name'],
- 'definition' => $row['definition']
- );
- }
- /**
- * Returns the primary key constraints for a table
- *
- * @param string $schema The schema the table is inside of
- * @param string $table The table to get the constraint for
- * @return array|NULL An associative array with the keys `name`, `columns` and `autoincrement` or `NULL`
- */
- private function getMSSQLPrimaryKeyConstraint($schema, $table)
- {
- $column_info = $this->database->query(
- "SELECT
- kcu.constraint_name AS constraint_name,
- LOWER(kcu.column_name) AS column_name,
- CASE
- WHEN
- COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND
- OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0
- THEN '1'
- ELSE '0'
- END AS auto_increment
- FROM
- information_schema.table_constraints AS con INNER JOIN
- information_schema.key_column_usage AS kcu ON
- con.table_name = kcu.table_name AND
- con.table_schema = kcu.table_schema AND
- con.constraint_name = kcu.constraint_name INNER JOIN
- information_schema.columns AS c ON
- c.table_name = kcu.table_name AND
- c.table_schema = kcu.table_schema AND
- c.column_name = kcu.column_name
- WHERE
- con.constraint_type = 'PRIMARY KEY' AND
- LOWER(con.table_schema) = %s AND
- LOWER(con.table_name) = %s AND
- con.table_catalog = DB_NAME()",
- strtolower($schema),
- strtolower($table)
- );
- if (!$column_info->countReturnedRows()) {
- return NULL;
- }
- $output = array(
- 'columns' => array()
- );
- foreach ($column_info as $row) {
- $output['columns'][] = $row['column_name'];
- $output['name'] = $row['constraint_name'];
- $output['autoincrement'] = (boolean) $row['auto_increment'];
- }
- return $output;
- }
- /**
- * Returns the unique constraints that a column is part of
- *
- * @param string $schema The schema the column is inside of
- * @param string $table The table the column is part of
- * @param string $column The column name
- * @return array An associative array of constraint_name => columns
- */
- private function getMSSQLUniqueConstraints($schema, $table, $column)
- {
- $constraint_columns = $this->database->query(
- "SELECT
- c.constraint_name,
- LOWER(kcu.column_name) AS column_name
- FROM
- information_schema.table_constraints AS c INNER JOIN
- information_schema.key_column_usage AS kcu ON
- c.table_name = kcu.table_name AND
- c.constraint_name = kcu.constraint_name
- WHERE
- c.constraint_name IN (
- SELECT
- c.constraint_name
- FROM
- information_schema.table_constraints AS c INNER JOIN
- information_schema.key_column_usage AS kcu ON
- c.table_name = kcu.table_name AND
- c.constraint_name = kcu.constraint_name
- WHERE
- c.constraint_type = 'UNIQUE' AND
- LOWER(c.table_schema) = %s AND
- LOWER(c.table_name) = %s AND
- LOWER(kcu.column_name) = %s AND
- c.table_catalog = DB_NAME()
-
- ) AND
- LOWER(c.table_schema) = %s AND
- c.table_catalog = DB_NAME()
- ORDER BY
- c.constraint_name
- ",
- strtolower($schema),
- strtolower($table),
- strtolower($column),
- strtolower($schema)
- );
- $unique_constraints = array();
- foreach ($constraint_columns as $row) {
- if (!isset($unique_constraints[$row['constraint_name']])) {
- $unique_constraints[$row['constraint_name']] = array();
- }
- $unique_constraints[$row['constraint_name']][] = $row['column_name'];
- }
-
- return $unique_constraints;
- }
- /**
- * Returns info about all foreign keys that involve the table and one of the columns specified
- *
- * @param string $table The table
- * @param string|array $columns The column, or an array of valid column names
- * @column array An array of associative arrays containing the keys `constraint_name`, `table`, `column`, `foreign_table` and `foreign_column`
- */
- private function getMySQLForeignKeys($table, $columns)
- {
- if (is_string($columns)) {
- $columns = array($columns);
- }
- $columns = array_map('strtolower', $columns);
- $tables = $this->getMySQLTables();
-
- $keys = array();
- foreach ($tables as $_table) {
- $row = $this->database->query("SHOW CREATE TABLE %r", $_table)->fetchRow();
-
- preg_match_all(
- '#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))?#',
- $row['Create Table'],
- $matches,
- PREG_SET_ORDER
- );
- foreach ($matches as $match) {
- $points_to_column = strtolower($match[3]) == strtolower($table) && in_array(strtolower($match[4]), $columns);
- $is_column = strtolower($_table) == strtolower($table) && in_array(strtolower($match[2]), $columns);
- if (!$points_to_column && !$is_column) {
- continue;
- }
- $temp = array(
- 'constraint_name' => $match[1],
- 'table' => $_table,
- 'column' => $match[2],
- 'foreign_table' => $match[3],
- 'foreign_column' => $match[4],
- 'on_delete' => 'NO ACTION',
- 'on_update' => 'NO ACTION'
- );
- if (!empty($match[5])) {
- $temp['on_delete'] = $match[5];
- }
- if (!empty($match[6])) {
- $temp['on_update'] = $match[6];
- }
- $keys[] = $temp;
- }
- }
- return $keys;
- }
- /**
- * Returns a list of all tables in the database
- *
- * @return array An array of table names
- */
- private function getMySQLTables()
- {
- if (!isset($this->schema_info['version'])) {
- $version = $this->database->query("SELECT version()")->fetchScalar();
- $this->schema_info['version'] = substr($version, 0, strpos($version, '.'));
- }
- if ($this->schema_info['version'] <= 4) {
- $sql = 'SHOW TABLES';
- } else {
- $sql = "SHOW FULL TABLES WHERE table_type = 'BASE TABLE'";
- }
- $result = $this->database->query($sql);
- $tables = array();
- foreach ($result as $row) {
- $keys = array_keys($row);
- $tables[] = $row[$keys[0]];
- }
- return $tables;
- }
-
- /**
- * Returns an an array of the column name for a table
- *
- * @param string $table The table to retrieve the column names for
- * @return array The column names for the table
- */
- private function getSQLiteColumns($table)
- {
- $create_sql = $this->getSQLiteCreateTable($table);
- return array_keys(self::parseSQLiteColumnDefinitions($create_sql));
- }
- /**
- * Returns the SQL used to create a table
- *
- * @param string $table The table to retrieve the `CREATE TABLE` statement for
- * @return string The `CREATE TABLE` SQL statement
- */
- private function getSQLiteCreateTable($table)
- {
- if (!isset($this->schema_info['sqlite_create_tables'])) {
- $this->getSQLiteTables();
- }
- if (!isset($this->schema_info['sqlite_create_tables'][$table])) {
- return NULL;
- }
- return $this->schema_info['sqlite_create_tables'][$table];
- }
- /**
- * Returns a list of all foreign keys that reference the table, and optionally, column specified
- *
- * @param string $table All foreign keys returned will point to this table
- * @param string $column Only foreign keys pointing to this column will be returned
- * @return array An array of arrays containing they keys: `table`, `column`, `foreign_table`, `foreign_column`, `on_delete` and `on_update`
- */
- private function getSQLiteForeignKeys($table, $column=NULL)
- {
- $output = array();
- foreach ($this->getSQLiteTables() as $_table) {
- $create_sql = $this->getSQLiteCreateTable($_table);
- if (stripos($create_sql, 'references') === FALSE) {
- continue;
- }
- 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);
- 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);
- foreach (array_merge($matches, $matches2) as $match) {
- $_column = $match[1];
- $foreign_table = $match[2];
- $foreign_column = $match[3];
- $on_delete = empty($match[4]) ? 'NO ACTION' : $match[4];
- $on_update = empty($match[5]) ? 'NO ACTION' : $match[5];
- if ($foreign_table != $table || ($column !== NULL && $column != $foreign_column)) {
- continue;
- }
- if (!$on_delete) {
- $on_delete = 'NO ACTION';
- }
- if (!$on_update) {
- $on_update = 'NO ACTION';
- }
- $output[] = array(
- 'table' => $_table,
- 'column' => $_column,
- 'foreign_table' => $foreign_table,
- 'foreign_column' => $foreign_column,
- 'on_delete' => $on_delete,
- 'on_update' => $on_update
- );
- }
- }
- return $output;
- }
- /**
- * Returns the indexes in the current SQLite database
- *
- * @return array An associative array with the key being the index name and the value an associative arrays, each containing the keys: `table`, `sql`
- */
- private function getSQLiteIndexes($table=NULL)
- {
- if (!isset($this->schema_info['sqlite_indexes'])) {
- $this->schema_info['sqlite_indexes'] = array();
- $rows = $this->database->query(
- "SELECT tbl_name AS \"table\", name, sql FROM sqlite_master WHERE type = 'index' AND sql <> ''"
- )->fetchAllRows();
- foreach ($rows as $row) {
- $this->schema_info['sqlite_indexes'][$row['name']] = array(
- 'table' => $row['table'],
- 'sql' => $row['sql']
- );
- }
- }
- $output = $this->schema_info['sqlite_indexes'];
- if ($table) {
- $new_output = array();
- foreach ($output as $name => $index) {
- if ($index['table'] != $table) {
- continue;
- }
- $new_output[$name] = $index;
- }
- $output = $new_output;
- }
- return $output;
- }
- /**
- * Returns the tables in the current SQLite database
- *
- * @return array
- */
- private function getSQLiteTables()
- {
- if (!isset($this->schema_info['sqlite_create_tables'])) {
- $this->schema_info['sqlite_create_tables'] = array();
- $res = $this->database->query(
- "SELECT name, sql FROM sqlite_master WHERE type = 'table'"
- )->fetchAllRows();
- foreach ($res as $row) {
- $this->schema_info['sqlite_create_tables'][$row['name']] = $row['sql'];
- }
- }
- $tables = array_keys($this->schema_info['sqlite_create_tables']);
- natcasesort($tables);
- return $tables;
- }
- /**
- * Returns the triggers in the current SQLite database
- *
- * @return array An associative array with the key being the trigger name and the value an associative arrays, each containing the keys: `table`, `sql`
- */
- private function getSQLiteTriggers($exclude_table=NULL)
- {
- if (!isset($this->schema_info['sqlite_triggers'])) {
- $this->schema_info['sqlite_triggers'] = array();
- $rows = $this->database->query(
- "SELECT tbl_name AS \"table\", name, sql FROM sqlite_master WHERE type = 'trigger'"
- )->fetchAllRows();
- foreach ($rows as $row) {
- $this->schema_info['sqlite_triggers'][$row['name']] = array(
- 'table' => $row['table'],
- 'sql' => $row['sql']
- );
- }
- }
- $output = $this->schema_info['sqlite_triggers'];
- if ($exclude_table) {
- $new_output = array();
- foreach ($output as $name => $trigger) {
- if ($trigger['table'] == $exclude_table) {
- continue;
- }
- $new_output[$name] = $trigger;
- }
- $output = $new_output;
- }
- return $output;
- }
-
-
- /**
- * Removes the SQLite indexes from the internal schema tracker
- *
- * @param string $table The table to remove the indexes for
- * @return void
- */
- private function removeSQLiteIndexes($table)
- {
- if (!isset($this->schema_info['sqlite_indexes'])) {
- return;
- }
- $indexes = $this->schema_info['sqlite_indexes'];
- $new_indexes = array();
- foreach ($indexes as $name => $index) {
- if ($index['table'] == $table) {
- continue;
- }
- $new_indexes[$name] = $index;
- }
- $this->schema_info['sqlite_indexes'] = $new_indexes;
- }
- /**
- * Removes a table from the list of SQLite table
- *
- * @param string $table The table to remove
- * @return void
- */
- private function removeSQLiteTable($table)
- {
- if (!isset($this->schema_info['sqlite_create_tables'])) {
- return;
- }
- unset($this->schema_info['sqlite_create_tables'][$table]);
- }
- /**
- * Removes a SQLite trigger from the internal schema tracker
- *
- * @param string $name The trigger name
- * @return void
- */
- private function removeSQLiteTrigger($name)
- {
- if (!isset($this->schema_info['sqlite_triggers'])) {
- return;
- }
- unset($this->schema_info['sqlite_triggers'][$name]);
- }
- /**
- * Removes the SQLite triggers for a table from the internal schema tracker
- *
- * @param string $table The table to remove the triggers for
- * @return void
- */
- private function removeSQLiteTriggers($table)
- {
- if (!isset($this->schema_info['sqlite_triggers'])) {
- return;
- }
- $triggers = $this->schema_info['sqlite_triggers'];
- $new_triggers = array();
- foreach ($triggers as $name => $trigger) {
- if ($trigger['table'] == $table) {
- continue;
- }
- $new_triggers[$name] = $trigger;
- }
- $this->schema_info['sqlite_triggers'] = $new_triggers;
- }
- /**
- * Throws an fSQLException with the information provided
- *
- * @param string $error The error that occured
- * @param string $sql The SQL statement that caused the error
- * @return void
- */
- private function throwException($error, $sql)
- {
- $db_type_map = array(
- 'db2' => 'DB2',
- 'mssql' => 'MSSQL',
- 'mysql' => 'MySQL',
- 'oracle' => 'Oracle',
- 'postgresql' => 'PostgreSQL',
- 'sqlite' => 'SQLite'
- );
-
- throw new fSQLException(
- '%1$s error (%2$s) in %3$s',
- $db_type_map[$this->database->getType()],
- $error,
- $sql
- );
- }
-
-
- /**
- * Translates a Flourish SQL DDL statement into the dialect for the current database
- *
- * @internal
- *
- * @param string $sql The SQL statement to translate
- * @param array &$rollback_statements SQL statements to rollback the returned SQL statements if something goes wrong - only applicable for MySQL `ALTER TABLE` statements
- * @return array An array containing the translated `$sql` statement and an array of extra statements
- */
- public function translate($sql, &$rollback_statements=NULL)
- {
- $reset_sqlite_info = FALSE;
- if (!isset($this->schema_info['sqlite_schema_info'])) {
- $this->schema_info['sqlite_schema_info'] = TRUE;
- $reset_sqlite_info = TRUE;
- }
- $new_sql = $sql;
- $exception = NULL;
- try {
- $extra_statements = array();
- if (!is_array($rollback_statements)) {
- $rollback_statements = array();
- }
- $new_sql = $this->translateCreateTableStatements($new_sql, $extra_statements);
- $new_sql = $this->translateAlterTableStatements($new_sql, $extra_statements, $rollback_statements);
- if ($this->database->getType() == 'sqlite') {
- $new_sql = $this->translateSQLiteDropTableStatements($new_sql, $extra_statements);
- }
- } catch (Exception $e) {
- $exception = $e;
- }
-
- if ($reset_sqlite_info) {
- unset($this->schema_info['sqlite_schema_info']);
- unset($this->schema_info['sqlite_create_tables']);
- unset($this->schema_info['sqlite_indexes']);
- unset($this->schema_info['sqlite_triggers']);
- }
- if ($exception) {
- throw $exception;
- }
- return array($new_sql, $extra_statements);
- }
-
-
- /**
- * Translates the structure of `CREATE TABLE` statements to the database specific syntax
- *
- * @param string $sql The SQL to translate
- * @param array &$extra_statements Any extra SQL statements that need to be added
- * @param array &$rollback_statements SQL statements to rollback `$sql` and `$extra_statements` if something goes wrong
- * @return string The translated SQL
- */
- private function translateAlterTableStatements($sql, &$extra_statements, &$rollback_statements=NULL)
- {
- 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)) {
- return $sql;
- }
-
- $statement = $table_matches[2];
-
- $data = array(
- 'table' => $table_matches[1]
- );
- if (preg_match('#"?(\w+)"?\s+IS\s+(\'.*\'|:string\w+|%\d+\$s)\s*$#Dis', $statement, $statement_matches)) {
- $data['type'] = 'column_comment';
- $data['column_name'] = trim($statement_matches[1], '"');
- $data['comment'] = $statement_matches[2];
- } elseif (preg_match('#RENAME\s+TO\s+(\w+|"[^"]+")\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'rename_table';
- $data['new_table_name'] = trim($statement_matches[1], '"');
- } elseif (preg_match('#RENAME\s+COLUMN\s+(\w+|"[^"]+")\s+TO\s+(\w+|"[^"]+")\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'rename_column';
- $data['column_name'] = trim($statement_matches[1], '"');
- $data['new_column_name'] = trim($statement_matches[2], '"');
- } elseif (preg_match('#ADD\s+COLUMN\s+("?(\w+)"?.*)$#isD', $statement, $statement_matches)) {
- $data['type'] = 'add_column';
- $data['column_definition'] = $statement_matches[1];
- $data['column_name'] = $statement_matches[2];
- } elseif (preg_match('#DROP\s+COLUMN\s+(\w+|"[^"]+")\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'drop_column';
- $data['column_name'] = trim($statement_matches[1], '"');
- } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+TYPE\s+(.*?)\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'alter_type';
- $data['column_name'] = trim($statement_matches[1], '"');
- $data['data_type'] = $statement_matches[2];
- } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+DROP\s+DEFAULT\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'drop_default';
- $data['column_name'] = trim($statement_matches[1], '"');
- } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+SET\s+DEFAULT\s+(.*?)\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'set_default';
- $data['column_name'] = trim($statement_matches[1], '"');
- $data['default_value'] = trim($statement_matches[2], '"');
- } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+DROP\s+NOT\s+NULL\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'drop_not_null';
- $data['column_name'] = trim($statement_matches[1], '"');
- } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+SET\s+NOT\s+NULL(\s+DEFAULT\s+(.*))?\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'set_not_null';
- $data['column_name'] = trim($statement_matches[1], '"');
- if (isset($statement_matches[2])) {
- $data['default'] = $statement_matches[3];
- }
- } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+DROP\s+CHECK\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'drop_check_constraint';
- $data['column_name'] = trim($statement_matches[1], '"');
- } elseif (preg_match('#ALTER\s+COLUMN\s+(\w+|"[^"]+")\s+SET\s+CHECK\s+IN\s+(\(.*?\))\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'set_check_constraint';
- $data['column_name'] = trim($statement_matches[1], '"');
- $data['constraint'] = ' CHECK(' . $statement_matches[1] . ' IN ' . $statement_matches[2] . ')';
- } elseif (preg_match('#DROP\s+PRIMARY\s+KEY\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'drop_primary_key';
- } elseif (preg_match('#ADD\s+PRIMARY\s+KEY\s*\(\s*([^\)]+?)\s*\)(\s+AUTOINCREMENT)?\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'add_primary_key';
- $data['column_names'] = preg_split(
- '#"?\s*,\s*"?#',
- trim($statement_matches[1], '"'),
- -1,
- PREG_SPLIT_NO_EMPTY
- );
- $data['autoincrement'] = count($data['column_names']) == 1 && !empty($statement_matches[2]);
- if (count($data['column_names']) == 1) {
- $data['column_name'] = reset($data['column_names']);
- }
- } elseif (preg_match('#DROP\s+FOREIGN\s+KEY\s*\(\s*(\w+|"[^"]+")\s*\)\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'drop_foreign_key';
- $data['column_name'] = trim($statement_matches[1], '"');
- } elseif (preg_match('#ADD\s+FOREIGN\s+KEY\s*\((\w+|"[^"]+")\)\s+REFERENCES\s+("?(\w+)"?\s*\(\s*"?(\w+)"?\s*\)\s*.*)\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'add_foreign_key';
- $data['column_name'] = trim($statement_matches[1], '"');
- $data['references'] = $statement_matches[2];
- $data['foreign_table'] = self::unescapeIdentifier($statement_matches[3]);
- $data['foreign_column'] = self::unescapeIdentifier($statement_matches[4]);
- } elseif (preg_match('#DROP\s+UNIQUE\s*\(\s*([^\)]+?)\s*\)\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'drop_unique';
- $data['column_names'] = preg_split(
- '#"?\s*,\s*"?#',
- trim($statement_matches[1], '"'),
- -1,
- PREG_SPLIT_NO_EMPTY
- );
- if (count($data['column_names']) == 1) {
- $data['column_name'] = reset($data['column_names']);
- }
- } elseif (preg_match('#ADD\s+UNIQUE\s*\(\s*([^\)]+?)\s*\)\s*$#isD', $statement, $statement_matches)) {
- $data['type'] = 'add_unique';
- $data['column_names'] = preg_split(
- '#"?\s*,\s*"?#',
- trim($statement_matches[1], '"'),
- -1,
- PREG_SPLIT_NO_EMPTY
- );
- if (count($data['column_names']) == 1) {
- $data['column_name'] = reset($data['column_names']);
- }
- } else {
- return $sql;
- }
- $data['table'] = self::unescapeIdentifier($data['table']);
- if (isset($data['new_table_name'])) {
- $data['new_table_name'] = self::unescapeIdentifier($data['new_table_name']);
- }
- if (isset($data['column_name'])) {
- $data['column_name'] = self::unescapeIdentifier($data['column_name']);
- }
- if (isset($data['column_names'])) {
- $data['column_names'] = array_map(
- array('fSQLSchemaTranslation', 'unescapeIdentifier'),
- $data['column_names']
- );
- }
- if (isset($data['new_column_name'])) {
- $data['new_column_name'] = self::unescapeIdentifier($data['new_column_name']);
- }
-
- if ($this->database->getType() == 'db2') {
- $sql = $this->translateDB2AlterTableStatements($sql, $extra_statements, $data);
- }
- if ($this->database->getType() == 'mssql') {
- $sql = $this->translateMSSQLAlterTableStatements($sql, $extra_statements, $data);
- }
- if ($this->database->getType() == 'mysql') {
- $sql = $this->translateMySQLAlterTableStatements($sql, $extra_statements, $rollback_statements, $data);
- }
- if ($this->database->getType() == 'oracle') {
- $sql = $this->translateOracleAlterTableStatements($sql, $extra_statements, $data);
- }
- if ($this->database->getType() == 'postgresql') {
- $sql = $this->translatePostgreSQLAlterTableStatements($sql, $extra_statements, $data);
- }
- if ($this->database->getType() == 'sqlite') {
- if ($data['type'] == 'rename_table') {
- $sql = $this->translateSQLiteRenameTableStatements($sql, $extra_statements, $data);
- } else {
- $sql = $this->translateSQLiteAlterTableStatements($sql, $extra_statements, $data);
- }
- }
- // All databases except for MySQL and Oracle support transactions around data definition queries
- // All of the Oracle statements will fail on the first query, if at all, so we don't need to
- // worry too much. MySQL is a huge pain though.
- if (!in_array($this->database->getType(), array('mysql', 'oracle'))) {
- array_unshift($extra_statements, $sql);
-
- if (!$this->databa…
Large files files are truncated, but you can click here to view the full file