/classes/fSQLSchemaTranslation.php

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-2012 Will Bond
 * @author     Will Bond [wb] <will@flourishlib.com>
 * @license    http://flourishlib.com/license
 * 
 * @package    Flourish
 * @link       http://flourishlib.com/fSQLSchemaTranslation
 * 
 * @version    1.0.0b3
 * @changes    1.0.0b3   Fixed associating a sequence with a column in PostgreSQL when setting auto-increment, fixed detection of some Oracle CHECK(IN) constraints, fixed default values for SQLite `ON DELETE` and `ON UPDATE` clauses [wb, 2012-01-12]
 * @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]) ? 'RESTRICT' : $match[4];
				$on_update      = empty($match[5]) ? 'RESTRICT' : $match[5];

				if ($foreign_table != $table || ($column !== NULL && $column != $foreign_column)) {
					continue;
				}

				if (!$on_delete) {
					$on_delete = 'RESTRICT';
				}
				if (!$on_update) {
					$on_update = 'RESTRICT';
				}

				$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 w…