/**
* Returns the tables in the current database
*
* @return array The tables in the current database
*/
public function getTables()
{
if ($this->tables !== NULL) {
return $this->tables;
}
switch ($this->database->getType()) {
case 'mssql':
$sql = "SELECT\r\n\t\t\t\t\t\t\t\tTABLE_NAME\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tINFORMATION_SCHEMA.TABLES\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tTABLE_NAME != 'sysdiagrams'\r\n\t\t\t\t\t\t\tORDER BY\r\n\t\t\t\t\t\t\t\tLOWER(TABLE_NAME)";
break;
case 'mysql':
$sql = 'SHOW TABLES';
break;
case 'oracle':
$sql = "SELECT\r\n\t\t\t\t\t\t\t\tLOWER(TABLE_NAME)\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tUSER_TABLES\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tSUBSTR(TABLE_NAME, 1, 4) <> 'BIN\$'\r\n\t\t\t\t\t\t\tORDER BY\r\n\t\t\t\t\t\t\t\tTABLE_NAME ASC";
break;
case 'postgresql':
$sql = "SELECT\r\n\t\t\t\t\t\t\t\t tablename\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t pg_tables\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\t tablename !~ '^(pg|sql)_'\r\n\t\t\t\t\t\t\tORDER BY\r\n\t\t\t\t\t\t\t\tLOWER(tablename)";
break;
case 'sqlite':
$sql = "SELECT\r\n\t\t\t\t\t\t\t\tname\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tsqlite_master\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\ttype = 'table' AND\r\n\t\t\t\t\t\t\t\tname NOT LIKE 'sqlite_%'\r\n\t\t\t\t\t\t\tORDER BY\r\n\t\t\t\t\t\t\t\tname ASC";
break;
}
$result = $this->database->query($sql);
$this->tables = array();
foreach ($result as $row) {
$keys = array_keys($row);
$this->tables[] = $row[$keys[0]];
}
if ($this->cache) {
$this->cache->set($this->makeCachePrefix() . 'tables', $this->tables);
}
return $this->tables;
}
/**
* Returns the tables in the current database
*
* @param boolean|string $creation_order `TRUE` to return in a valid table creation order, or a table name to return that table and any tables that depend on it, in table creation order
* @return array The tables in the current database, all converted to lowercase
*/
public function getTables($creation_order = NULL)
{
if ($creation_order) {
return $this->determineTableCreationOrder(is_bool($creation_order) ? NULL : $creation_order);
}
if ($this->tables !== NULL) {
return $this->tables;
}
switch ($this->database->getType()) {
case 'db2':
$sql = "SELECT\n\t\t\t\t\t\t\tLOWER(RTRIM(TABSCHEMA)) AS \"schema\",\n\t\t\t\t\t\t\tLOWER(TABNAME) AS \"table\"\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tSYSCAT.TABLES\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tTYPE = 'T' AND\n\t\t\t\t\t\t\tTABSCHEMA != 'SYSIBM' AND\n\t\t\t\t\t\t\tDEFINER != 'SYSIBM' AND\n\t\t\t\t\t\t\tTABSCHEMA != 'SYSTOOLS' AND\n\t\t\t\t\t\t\tDEFINER != 'SYSTOOLS'\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\tLOWER(TABNAME)";
break;
case 'mssql':
$sql = "SELECT\n\t\t\t\t\t\t\tTABLE_SCHEMA AS \"schema\",\n\t\t\t\t\t\t\tTABLE_NAME AS \"table\"\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tINFORMATION_SCHEMA.TABLES\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tTABLE_NAME != 'sysdiagrams'\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\tLOWER(TABLE_NAME)";
break;
case 'mysql':
if (version_compare($this->database->getVersion(), 5, '<')) {
$sql = 'SHOW TABLES';
} else {
$sql = "SHOW FULL TABLES WHERE table_type = 'BASE TABLE'";
}
break;
case 'oracle':
$sql = "SELECT\n\t\t\t\t\t\t\tLOWER(OWNER) AS \"SCHEMA\",\n\t\t\t\t\t\t\tLOWER(TABLE_NAME) AS \"TABLE\"\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tALL_TABLES\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tOWNER NOT IN (\n\t\t\t\t\t\t\t\t'SYS',\n\t\t\t\t\t\t\t\t'SYSTEM',\n\t\t\t\t\t\t\t\t'OUTLN',\n\t\t\t\t\t\t\t\t'ANONYMOUS',\n\t\t\t\t\t\t\t\t'AURORA\$ORB\$UNAUTHENTICATED',\n\t\t\t\t\t\t\t\t'AWR_STAGE',\n\t\t\t\t\t\t\t\t'CSMIG',\n\t\t\t\t\t\t\t\t'CTXSYS',\n\t\t\t\t\t\t\t\t'DBSNMP',\n\t\t\t\t\t\t\t\t'DIP',\n\t\t\t\t\t\t\t\t'DMSYS',\n\t\t\t\t\t\t\t\t'DSSYS',\n\t\t\t\t\t\t\t\t'EXFSYS',\n\t\t\t\t\t\t\t\t'FLOWS_020100',\n\t\t\t\t\t\t\t\t'FLOWS_FILES',\n\t\t\t\t\t\t\t\t'LBACSYS',\n\t\t\t\t\t\t\t\t'MDSYS',\n\t\t\t\t\t\t\t\t'ORACLE_OCM',\n\t\t\t\t\t\t\t\t'ORDPLUGINS',\n\t\t\t\t\t\t\t\t'ORDSYS',\n\t\t\t\t\t\t\t\t'PERFSTAT',\n\t\t\t\t\t\t\t\t'TRACESVR',\n\t\t\t\t\t\t\t\t'TSMSYS',\n\t\t\t\t\t\t\t\t'XDB'\n\t\t\t\t\t\t\t) AND\n\t\t\t\t\t\t\tDROPPED = 'NO'\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\tTABLE_NAME ASC";
break;
case 'postgresql':
$sql = "SELECT\n\t\t\t\t\t\t\t schemaname AS \"schema\",\n\t\t\t\t\t\t\t tablename as \"table\"\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t pg_tables\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t tablename !~ '^(pg|sql)_'\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\tLOWER(tablename)";
break;
case 'sqlite':
$sql = "SELECT\n\t\t\t\t\t\t\tname\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tsqlite_master\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\ttype = 'table' AND\n\t\t\t\t\t\t\tname NOT LIKE 'sqlite_%'\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\tname ASC";
break;
}
$result = $this->database->query($sql);
$this->tables = array();
// For databases with schemas we only include the schema
// name if there are conflicting table names
if (!in_array($this->database->getType(), array('mysql', 'sqlite'))) {
$default_schema_map = array('db2' => strtolower($this->database->getUsername()), 'mssql' => 'dbo', 'oracle' => strtolower($this->database->getUsername()), 'postgresql' => 'public');
$default_schema = $default_schema_map[$this->database->getType()];
foreach ($result as $row) {
if ($row['schema'] == $default_schema) {
$this->tables[] = strtolower($row['table']);
} else {
$this->tables[] = strtolower($row['schema'] . '.' . $row['table']);
}
}
// SQLite and MySQL don't support schemas
} else {
foreach ($result as $row) {
$keys = array_keys($row);
$this->tables[] = strtolower($row[$keys[0]]);
}
}
sort($this->tables);
if ($this->cache) {
$this->cache->set($this->makeCachePrefix() . 'tables', $this->tables);
}
return $this->tables;
}
/**
* Translates Flourish SQL `ALTER TABLE` statements to the appropriate
* statements for SQLite
*
* @param string $sql The SQL statements that will be executed against the database
* @param array &$extra_statements Any extra SQL statements required for SQLite
* @param array $data Data parsed from the `ALTER TABLE` statement
* @return string The modified SQL statement
*/
private function translateSQLiteAlterTableStatements($sql, &$extra_statements, $data)
{
$toggle_foreign_key_support = FALSE;
if (!isset($this->schema_info['foreign_keys_enabled'])) {
$toggle_foreign_key_support = TRUE;
$foreign_keys_res = $this->database->query('PRAGMA foreign_keys');
if ($foreign_keys_res->countReturnedRows() && $foreign_keys_res->fetchScalar()) {
$this->schema_info['foreign_keys_enabled'] = TRUE;
$this->database->query("PRAGMA foreign_keys = 0");
} else {
$this->schema_info['foreign_keys_enabled'] = FALSE;
}
}
$temp_create_table_sql = $this->getSQLiteCreateTable($data['table']);
if ($temp_create_table_sql === NULL) {
$this->throwException(self::compose('The table "%1$s" does not exist', $data['table']), $sql);
}
$temp_create_table_sql = preg_replace('#(^\\s*CREATE\\s+TABLE\\s+)(?:`|\'|"|\\[)?(\\w+)(?:`|\'|"|\\])?(\\s*\\()#i', '\\1"fl_tmp_' . $data['table'] . '"\\3', $temp_create_table_sql);
if ($data['type'] == 'drop_primary_key' && !preg_match('#\\bPRIMARY\\s+KEY\\b#', $temp_create_table_sql)) {
$this->throwException(self::compose('The table "%1$s" does not have a primary key constraint', $data['table']), $sql);
}
if ($data['type'] == 'add_primary_key' && preg_match('#\\bPRIMARY\\s+KEY\\b#', $temp_create_table_sql)) {
$this->throwException(self::compose('The table "%1$s" already has a primary key constraint', $data['table']), $sql);
}
if ($data['type'] == 'drop_unique') {
$dropped_unique = FALSE;
}
if ($data['type'] == 'drop_foreign_key') {
$dropped_foreign_key = FALSE;
}
if (in_array($data['type'], array('column_comment', 'alter_type', 'set_not_null', 'drop_not_null', 'drop_default', 'set_default', 'drop_primary_key', 'add_primary_key', 'drop_foreign_key', 'add_foreign_key', 'drop_unique', 'add_unique', 'set_check_constraint', 'drop_check_constraint'))) {
$column_info = self::parseSQLiteColumnDefinitions($temp_create_table_sql);
if (isset($data['column_name']) && !isset($column_info[$data['column_name']])) {
$this->throwException(self::compose('The column "%1$s" does not exist in the table "%2$s"', $data['column_name'], $data['table']), $sql);
}
foreach ($column_info as $column => $info) {
if (isset($data['column_name']) && $column == $data['column_name']) {
if ($data['type'] == 'alter_type') {
$info['pieces']['data_type'] = ' ' . $data['data_type'];
} elseif ($data['type'] == 'set_not_null') {
$info['pieces']['not_null'] = ' NOT NULL';
$info['pieces']['null'] = '';
if (isset($data['default'])) {
$info['pieces']['default'] = ' DEFAULT ' . $data['default'];
}
} elseif ($data['type'] == 'drop_not_null') {
$info['pieces']['not_null'] = '';
$info['pieces']['null'] = '';
} elseif ($data['type'] == 'set_default') {
$info['pieces']['default'] = ' DEFAULT ' . $data['default_value'];
} elseif ($data['type'] == 'drop_default') {
$info['pieces']['default'] = '';
} elseif ($data['type'] == 'set_check_constraint') {
$info['pieces']['check_constraint'] = $data['constraint'];
} elseif ($data['type'] == 'drop_check_constraint') {
$info['pieces']['check_constraint'] = '';
} elseif ($data['type'] == 'add_primary_key') {
$info['pieces']['primary_key'] = ' PRIMARY KEY' . (version_compare($this->database->getVersion(), 3, '>=') && $data['autoincrement'] ? ' AUTOINCREMENT' : '');
} elseif ($data['type'] == 'drop_foreign_key') {
if (trim($info['pieces']['foreign_key'])) {
$dropped_foreign_key = TRUE;
}
$info['pieces']['foreign_key'] = '';
} elseif ($data['type'] == 'add_foreign_key') {
$foreign_create_table = $this->getSQLiteCreateTable($data['foreign_table']);
if ($foreign_create_table === NULL) {
$this->throwException(self::compose('The referenced table "%1$s" does not exist', $data['foreign_table']), $sql);
}
$foreign_column_info = self::parseSQLiteColumnDefinitions($foreign_create_table);
if (!isset($foreign_column_info[$data['foreign_column']])) {
$this->throwException(self::compose('The referenced column "%1$s" does not exist in the referenced table "%2$s"', $data['foreign_column'], $data['foreign_table']), $sql);
}
$info['pieces']['foreign_key'] = ' REFERENCES ' . $data['references'];
} elseif ($data['type'] == 'drop_unique') {
if (trim($info['pieces']['unique'])) {
$dropped_unique = TRUE;
}
$info['pieces']['unique'] = '';
} elseif ($data['type'] == 'add_unique') {
$info['pieces']['unique'] = ' UNIQUE';
} elseif ($data['type'] == 'column_comment') {
if (preg_match('#(^\\s*,)|(^\\s*/\\*\\s*((?:(?!\\*/).)*?)\\s*\\*/\\s*,)#', $info['pieces']['comment/end'])) {
$info['pieces']['comment/end'] = ',';
}
$comment = str_replace("''", "'", substr($data['comment'], 1, -1));
if (strlen(trim($comment))) {
$info['pieces']['comment/end'] .= ' -- ' . $comment . "\n";
}
}
} elseif ($data['type'] == 'drop_primary_key') {
if (trim($info['pieces']['primary_key'])) {
$data['column_name'] = $column;
}
$info['pieces']['not_null'] = ' NOT NULL';
$info['pieces']['primary_key'] = '';
}
$temp_create_table_sql = str_replace($info['definition'], join('', $info['pieces']), $temp_create_table_sql);
}
}
$primary_key_regex = '#(?<=,|\\()\\s*(?:CONSTRAINT\\s+["`\\[]?\\w+["`\\]]?\\s+)?PRIMARY\\s+KEY\\s*\\(\\s*((?:\\s*[\'"`\\[]?\\w+[\'"`\\]]?\\s*,\\s*)*[\'"`\\[]?\\w+[\'"`\\]]?)\\s*\\)\\s*(?:,|\\s*(?=\\)))#mi';
$foreign_key_regex = '#(?<=,|\\(|\\*/|\\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*(?:,(?:[ \\t]*--[^\\n]*\\n)?|(?:--[^\\n]*\\n)?\\s*(?=\\)))#mis';
$unique_constraint_regex = '#(?<=,|\\()\\s*(?:CONSTRAINT\\s+["`\\[]?\\w+["`\\]]?\\s+)?UNIQUE\\s*\\(\\s*((?:\\s*[\'"`\\[]?\\w+[\'"`\\]]?\\s*,\\s*)*[\'"`\\[]?\\w+[\'"`\\]]?)\\s*\\)\\s*(?:,|\\s*(?=\\)))#mi';
if (isset($data['column_name'])) {
$column_regex = '#(?:`|\'|"|\\[|\\b)' . preg_quote($data['column_name'], '#') . '(?:`|\'|"|\\]|\\b)#i';
}
if ($data['type'] == 'drop_primary_key' || $data['type'] == 'drop_column') {
// Drop any table-level primary keys
preg_match_all($primary_key_regex, $temp_create_table_sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
$columns = preg_split('#[\'"`\\]]?\\s*,\\s*[\'"`\\[]?#', strtolower(trim($match[1], '\'[]`"')));
sort($columns);
if ($data['type'] == 'drop_primary_key' && count($columns) == 1) {
$data['column_name'] = reset($columns);
} elseif ($data['type'] == 'drop_column') {
if (!in_array($data['column_name'], $columns)) {
continue;
}
}
$temp_create_table_sql = self::removeFromSQLiteCreateTable($temp_create_table_sql, $match[0]);
}
}
if ($data['type'] == 'drop_foreign_key' || $data['type'] == 'drop_column') {
// Drop any table-level foreign keys
preg_match_all($foreign_key_regex, $temp_create_table_sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
// Ignore foreign keys for other columns
if (strtolower($match[2]) != $data['column_name']) {
continue;
}
$dropped_foreign_key = TRUE;
$temp_create_table_sql = self::removeFromSQLiteCreateTable($temp_create_table_sql, $match[0]);
}
}
if ($data['type'] == 'drop_foreign_key' && !$dropped_foreign_key) {
$this->throwException(self::compose('The column "%1$s" in the table "%2$s" does not have a foreign key constraint', $data['column_name'], $data['table']), $sql);
}
if ($data['type'] == 'drop_unique' || $data['type'] == 'drop_column') {
// Drop any table-level unique keys
preg_match_all($unique_constraint_regex, $temp_create_table_sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
$columns = preg_split('#[\'"`\\]]?\\s*,\\s*[\'"`\\[]?#', strtolower(trim($match[1], '\'[]`"')));
sort($columns);
if ($data['type'] == 'drop_column') {
if (!in_array($data['column_name'], $columns)) {
continue;
}
} else {
sort($data['column_names']);
if ($columns != $data['column_names']) {
continue;
}
$dropped_unique = TRUE;
}
$temp_create_table_sql = self::removeFromSQLiteCreateTable($temp_create_table_sql, $match[0]);
}
}
if ($data['type'] == 'rename_column') {
// Rename the column in the column definition and check constraint
$column_info = self::parseSQLiteColumnDefinitions($temp_create_table_sql);
if (!isset($column_info[$data['column_name']])) {
$this->throwException(self::compose('The column "%1$s" does not exist in the table "%2$s"', $data['column_name'], $data['table']), $sql);
}
if (isset($column_info[$data['new_column_name']])) {
$this->throwException(self::compose('The column "%1$s" already exists in the table "%2$s"', $data['new_column_name'], $data['table']), $sql);
}
$info = $column_info[$data['column_name']];
$temp_create_table_sql = str_replace($info['definition'], preg_replace('#^(\\s*)[`"\'\\[]?' . preg_quote($data['column_name'], '#') . '[`"\'\\]]?(\\s+)#i', '\\1"' . $data['new_column_name'] . '"\\2', $info['definition']), $temp_create_table_sql);
if ($info['pieces']['check_constraint']) {
$temp_create_table_sql = str_replace($info['pieces']['check_constraint'], preg_replace('#^(\\s*CHECK\\s*\\(\\s*)[`"\'\\[]?' . preg_quote($data['column_name'], '#') . '[`"\'\\]]?(\\s+)#i', '\\1"' . $data['new_column_name'] . '"\\2', $info['pieces']['check_constraint']), $temp_create_table_sql);
}
// Rename the column in table-level primary key
preg_match_all($primary_key_regex, $temp_create_table_sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
$temp_create_table_sql = str_replace($match[0], preg_replace($column_regex, '"' . $data['new_column_name'] . '"', $match[0]), $temp_create_table_sql);
}
// Rename the column in table-level foreign key definitions
preg_match_all($foreign_key_regex, $temp_create_table_sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
$temp_create_table_sql = str_replace($match[0], str_replace($match[1], preg_replace($column_regex, '"' . $data['new_column_name'] . '"', $match[1]), $match[0]), $temp_create_table_sql);
}
// Rename the column in table-level unique constraints
preg_match_all($unique_constraint_regex, $temp_create_table_sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
$temp_create_table_sql = str_replace($match[0], preg_replace($column_regex, '"' . $data['new_column_name'] . '"', $match[0]), $temp_create_table_sql);
}
}
if ($data['type'] == 'drop_column') {
$column_info = self::parseSQLiteColumnDefinitions($temp_create_table_sql);
if (!isset($column_info[$data['column_name']])) {
$this->throwException(self::compose('The column "%1$s" does not exist in the table "%2$s"', $data['column_name'], $data['table']), $sql);
}
$temp_create_table_sql = self::removeFromSQLiteCreateTable($temp_create_table_sql, $column_info[$data['column_name']]['definition']);
}
if ($data['type'] == 'add_primary_key' && count($data['column_names']) > 1) {
$temp_create_table_sql = preg_replace('#\\s*\\)\\s*$#D', $this->database->escape(",\n PRIMARY KEY(%r)\n)", $data['column_names']), $temp_create_table_sql);
}
if ($data['type'] == 'add_unique' && count($data['column_names']) > 1) {
$temp_create_table_sql = preg_replace('#\\s*\\)\\s*$#D', $this->database->escape(",\n UNIQUE(%r)\n)", $data['column_names']), $temp_create_table_sql);
}
if ($data['type'] == 'add_column') {
if (!preg_match('#\\s*"?\\w+"?\\s+\\w+#', $data['column_definition'])) {
$this->throwException(self::compose('Please specify a data type for the column "%1$s"', $data['column_name']), $sql);
}
preg_match('#^(.*?)((?:(?<=,|\\*/|\\n)\\s*(?:CONSTRAINT\\s+["`\\[]?\\w+["`\\]]?\\s+)?\\b(?:FOREIGN\\s+KEY\\b|PRIMARY\\s+KEY\\b|UNIQUE\\b).*)|(?:\\s*\\)\\s*))$#Dis', $temp_create_table_sql, $match);
if (trim($match[2]) != ')') {
$prefix = '';
$suffix = ',';
} else {
$prefix = ',';
$suffix = '';
}
// Be sure to add any necessary comma before a single-line SQL comment
// because if it is placed after, the comma will be part of the comment
if ($prefix) {
$original_match_1 = $match[1];
$match[1] = preg_replace('#(\\s*--[^\\n]+)(\\s*)?$#Di', ',\\1\\2', $match[1]);
if ($match[1] != $original_match_1) {
$prefix = '';
}
}
if (version_compare($this->database->getVersion(), 3, '>=')) {
$data['column_definition'] = preg_replace('#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\s+primary\\s+key\\b#i', 'INTEGER PRIMARY KEY AUTOINCREMENT', $data['column_definition']);
$data['column_definition'] = preg_replace("#datetime\\(\\s*CURRENT_TIMESTAMP\\s*,\\s*'localtime'\\s*\\)#i", 'CURRENT_TIMESTAMP', $data['column_definition']);
} else {
$data['column_definition'] = preg_replace('#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\s+primary\\s+key\\b#i', 'INTEGER PRIMARY KEY', $data['column_definition']);
$data['column_definition'] = preg_replace('#CURRENT_TIMESTAMP\\(\\)#i', 'CURRENT_TIMESTAMP', $data['column_definition']);
}
$match[1] .= $prefix . "\n\t" . $data['column_definition'] . $suffix;
$temp_create_table_sql = $match[1] . $match[2];
}
// Clean up extra line breaks
$temp_create_table_sql = preg_replace('#\\n([ \\t]*\\n)+#', "\n", $temp_create_table_sql);
// SQLite 3 supports renaming a table, so we need the full create
// table with all of the translated triggers, etc
if (version_compare($this->database->getVersion(), 3, '>=')) {
// We rename string placeholders to prevent confusion with
// string placeholders that are added by call to fDatabase
$temp_create_table_sql = str_replace('%', '%%', $temp_create_table_sql);
$extra_statements = array_merge($extra_statements, str_replace('%%', '%', $this->database->preprocess($temp_create_table_sql, array(), TRUE)));
// For SQLite 2 we can't rename the table, so we end up needing to
// create a new one so the temporary table doesn't need triggers
} else {
$extra_statements[] = $temp_create_table_sql;
}
$this->addSQLiteTable('fl_tmp_' . $data['table'], $temp_create_table_sql);
// Next we copy the data from the original table to the temp table
if ($data['type'] == 'rename_column') {
$column_names = $this->getSQLiteColumns($data['table']);
$new_column_names = $column_names;
$column_position = array_search($data['column_name'], $new_column_names);
$new_column_names[$column_position] = $data['new_column_name'];
} elseif ($data['type'] == 'drop_column') {
$column_names = array_diff($this->getSQLiteColumns($data['table']), array($data['column_name']));
$new_column_names = $column_names;
} else {
$column_names = $this->getSQLiteColumns($data['table']);
$new_column_names = $column_names;
}
$extra_statements[] = $this->database->escape("INSERT INTO %r (%r) SELECT %r FROM %r", 'fl_tmp_' . $data['table'], $new_column_names, $column_names, $data['table']);
// Recreate the indexes for the temp table
$indexes = $this->getSQLiteIndexes($data['table']);
foreach ($indexes as $name => $index) {
$create_sql = $index['sql'];
preg_match('#^\\s*CREATE\\s+(UNIQUE\\s+)?INDEX\\s+(?:[\'"`\\[]?\\w+[\'"`\\]]?\\.)?[\'"`\\[]?\\w+[\'"`\\]]?\\s+(ON\\s+[\'"`\\[]?\\w+[\'"`\\]]?)\\s*(\\((\\s*(?:\\s*[\'"`\\[]?\\w+[\'"`\\]]?\\s*,\\s*)*[\'"`\\[]?\\w+[\'"`\\]]?\\s*)\\))\\s*$#Di', $create_sql, $match);
$columns = preg_split('#[\'"`\\]]?\\s*,\\s*[\'"`\\[]?#', strtolower(trim($match[4], '\'[]`"')));
if ($data['type'] == 'rename_column') {
$create_sql = str_replace($match[3], preg_replace($column_regex, '"' . $data['new_column_name'] . '"', $match[3]), $create_sql);
} elseif ($data['type'] == 'drop_column') {
if (in_array($data['column_name'], $columns)) {
continue;
}
} elseif ($data['type'] == 'drop_unique') {
sort($columns);
sort($data['column_names']);
if ($columns == $data['column_names']) {
$dropped_unique = TRUE;
continue;
}
}
// The index needs to be altered to be created on the new table
$create_sql = str_replace($match[2], preg_replace('#(?:`|\'|"|\\[|\\b)?' . preg_quote($data['table'], '#') . '(?:`|\'|"|\\]|\\b)?#', '"fl_tmp_' . $data['table'] . '"', $match[2]), $create_sql);
// We fix the name of the index to keep in sync with the table name
if (version_compare($this->database->getVersion(), 3, '>=')) {
$new_name = $name;
} else {
$new_name = preg_replace('#^' . preg_quote($data['table'], '#') . '#', 'fl_tmp_' . $data['table'], $name);
}
// Ensure we have a unique index name
while (isset($indexes[$new_name])) {
if (preg_match('#(?<=_)(\\d+)$#D', $new_name, $match)) {
$new_name = preg_replace('#(?<=_)\\d+$#D', $match[1] + 1, $new_name);
} else {
$new_name .= '_2';
}
}
$create_sql = preg_replace('#[\'"`\\[]?' . preg_quote($name, '#') . '[\'"`\\]]?(\\s+ON\\s+)#i', '"' . $new_name . '"\\1', $create_sql);
$this->addSQLiteIndex($new_name, 'fl_tmp_' . $data['table'], $create_sql);
$extra_statements[] = $create_sql;
}
if ($data['type'] == 'drop_unique' && !$dropped_unique) {
if (count($data['column_names']) > 1) {
$message = self::compose('The columns "%1$s" in the table "%2$s" do not have a unique constraint', join('", "', $data['column_names']), $data['table']);
} else {
$message = self::compose('The column "%1$s" in the table "%2$s" does not have a unique constraint', reset($data['column_names']), $data['table']);
}
$this->throwException($message, $sql);
}
if (in_array($data['type'], array('rename_column', 'drop_column')) || $data['type'] == 'drop_primary_key' && isset($data['column_name'])) {
$foreign_keys = $this->getSQLiteForeignKeys($data['table'], $data['column_name']);
foreach ($foreign_keys as $key) {
$extra_statements = array_merge($extra_statements, $this->database->preprocess("ALTER TABLE %r DROP FOREIGN KEY (%r)", array($key['table'], $key['column']), TRUE));
}
}
// Drop the original table
$extra_statements = array_merge($extra_statements, $this->database->preprocess("DROP TABLE %r", array($data['table']), TRUE));
// Rename the temp table to the original name
$extra_statements = array_merge($extra_statements, $this->database->preprocess("ALTER TABLE %r RENAME TO %r", array('fl_tmp_' . $data['table'], $data['table']), TRUE));
// Re-add the foreign key constraints for renamed columns
if ($data['type'] == 'rename_column') {
foreach ($foreign_keys as $key) {
$extra_statements = array_merge($extra_statements, $this->database->preprocess("ALTER TABLE %r ADD FOREIGN KEY (%r) REFERENCES %r(%r) ON UPDATE " . $key['on_update'] . " ON DELETE " . $key['on_delete'], array($key['table'], $key['column'], $data['table'], $data['new_column_name']), TRUE));
}
}
// Finally, we turn back on foreign keys
if ($toggle_foreign_key_support) {
if ($this->schema_info['foreign_keys_enabled']) {
$this->database->query("PRAGMA foreign_keys = 1");
}
unset($this->schema_info['foreign_keys_enabled']);
}
// Remove any nested transactions
$extra_statements = array_diff($extra_statements, array("BEGIN", "COMMIT"));
// Overwrite the original ALTER TABLE SQL
$sql = array_shift($extra_statements);
return $sql;
}
/**
* Fixes pulling unicode data out of national data type MSSQL columns
*
* @param string $sql The SQL to fix
* @return string The fixed SQL
*/
private function fixMSSQLNationalColumns($sql)
{
if (!preg_match_all('#select((?:(?:(?!\\sfrom\\s)[^()])+|\\(((?:[^()]+|\\((?2)\\))*)\\))*\\s)from((?:(?:(?!\\sunion\\s|\\swhere\\s|\\sgroup by\\s|\\slimit\\s|\\sorder by\\s)[^()])+|\\(((?:[^()]+|\\((?4)\\))*)\\))*)(?=\\swhere\\s|\\sgroup by\\s|\\slimit\\s|\\sorder by\\s|\\sunion\\s|\\)|$)#i', $sql, $matches, PREG_SET_ORDER)) {
return $sql;
}
if (!isset($this->schema_info['national_columns'])) {
$result = $this->database->query("SELECT\n\t\t\t\t\t\tc.table_schema AS \"schema\",\n\t\t\t\t\t\tc.table_name AS \"table\",\t\t\t\t\t\t\n\t\t\t\t\t\tc.column_name AS \"column\",\n\t\t\t\t\t\tc.data_type AS \"type\"\n\t\t\t\t\tFROM\n\t\t\t\t\t\tINFORMATION_SCHEMA.COLUMNS AS c\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t(c.data_type = 'nvarchar' OR\n\t\t\t\t\t\t c.data_type = 'ntext' OR\n\t\t\t\t\t\t c.data_type = 'nchar') AND\n\t\t\t\t\t\tc.table_catalog = DB_NAME()\n\t\t\t\t\tORDER BY\n\t\t\t\t\t\tlower(c.table_name) ASC,\n\t\t\t\t\t\tlower(c.column_name) ASC");
$national_columns = array();
$national_types = array();
foreach ($result as $row) {
if (!isset($national_columns[$row['table']])) {
$national_columns[$row['table']] = array();
$national_types[$row['table']] = array();
$national_columns[$row['schema'] . '.' . $row['table']] = array();
$national_types[$row['schema'] . '.' . $row['table']] = array();
}
$national_columns[$row['table']][] = $row['column'];
$national_types[$row['table']][$row['column']] = $row['type'];
$national_columns[$row['schema'] . '.' . $row['table']][] = $row['column'];
$national_types[$row['schema'] . '.' . $row['table']][$row['column']] = $row['type'];
}
$this->schema_info['national_columns'] = $national_columns;
$this->schema_info['national_types'] = $national_types;
if ($this->cache) {
$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
}
} else {
$national_columns = $this->schema_info['national_columns'];
$national_types = $this->schema_info['national_types'];
}
$additions = array();
foreach ($matches as $select) {
$select_clause = trim($select[1]);
$from_clause = trim($select[3]);
$sub_selects = array();
if (preg_match_all('#\\((\\s*SELECT\\s+((?:[^()]+|\\((?2)\\))*))\\)#i', $from_clause, $from_matches)) {
$sub_selects = $from_matches[0];
foreach ($sub_selects as $i => $sub_select) {
$from_clause = preg_replace('#' . preg_quote($sub_select, '#') . '#', ':sub_select_' . $i, $from_clause, 1);
}
}
$table_aliases = self::parseTableAliases($from_clause);
preg_match_all('#([^,()]+|\\((?:(?1)|,)*\\))+#i', $select_clause, $selections);
$selections = array_map('trim', $selections[0]);
$to_fix = array();
foreach ($selections as $selection) {
// We just skip CASE statements since we can't really do those reliably
if (preg_match('#^case#i', $selection)) {
continue;
}
if (preg_match('#(("?\\w+"?\\.)"?\\w+"?)\\.\\*#i', $selection, $match)) {
$match[1] = str_replace('"', '', $match[1]);
$table = $table_aliases[$match[1]];
if (empty($national_columns[$table])) {
continue;
}
if (!isset($to_fix[$table])) {
$to_fix[$table] = array();
}
$to_fix[$table] = array_merge($to_fix[$table], $national_columns[$table]);
} elseif (preg_match('#\\*#', $selection, $match)) {
foreach ($table_aliases as $alias => $table) {
if (empty($national_columns[$table])) {
continue;
}
if (!isset($to_fix[$table])) {
$to_fix[$table] = array();
}
$to_fix[$table] = array_merge($to_fix[$table], $national_columns[$table]);
}
} elseif (preg_match('#^(?:((?:"?\\w+"?\\.)?"?\\w+"?)\\.("?\\w+"?)|((?:min|max|trim|rtrim|ltrim|substring|replace)\\(((?:"?\\w+"?\\.)"?\\w+"?)\\.("?\\w+"?).*?\\)))(?:\\s+as\\s+("?\\w+"?))?$#iD', $selection, $match)) {
$table = $match[1] . (isset($match[4]) ? $match[4] : '');
$column = $match[2] . (isset($match[5]) ? $match[5] : '');
// Unquote identifiers
$table = str_replace('"', '', $table);
$column = str_replace('"', '', $column);
$table = $table_aliases[$table];
if (empty($national_columns[$table]) || !in_array($column, $national_columns[$table])) {
continue;
}
if (!isset($to_fix[$table])) {
$to_fix[$table] = array();
}
// Handle column aliasing
if (!empty($match[6])) {
$column = array('column' => $column, 'alias' => str_replace('"', '', $match[6]));
}
if (!empty($match[3])) {
if (!is_array($column)) {
$column = array('column' => $column);
}
$column['expression'] = $match[3];
}
$to_fix[$table] = array_merge($to_fix[$table], array($column));
// Match unqualified column names
} elseif (preg_match('#^(?:("?\\w+"?)|((?:min|max|trim|rtrim|ltrim|substring|replace)\\(("?\\w+"?).*?\\)))(?:\\s+as\\s+("?\\w+"?))?$#iD', $selection, $match)) {
$column = $match[1] . (isset($match[3]) ? $match[3] : '');
// Unquote the identifiers
$column = str_replace('"', '', $column);
foreach ($table_aliases as $alias => $table) {
if (empty($national_columns[$table])) {
continue;
}
if (!in_array($column, $national_columns[$table])) {
continue;
}
if (!isset($to_fix[$table])) {
$to_fix[$table] = array();
}
// Handle column aliasing
if (!empty($match[4])) {
$column = array('column' => $column, 'alias' => str_replace('"', '', $match[4]));
}
if (!empty($match[2])) {
if (!is_array($column)) {
$column = array('column' => $column);
}
$column['expression'] = $match[2];
}
$to_fix[$table] = array_merge($to_fix[$table], array($column));
}
}
}
$reverse_table_aliases = array_flip($table_aliases);
foreach ($to_fix as $table => $columns) {
$columns = array_unique($columns);
$alias = $reverse_table_aliases[$table];
foreach ($columns as $column) {
if (is_array($column)) {
if (isset($column['alias'])) {
$as = ' AS fmssqln__' . $column['alias'];
} else {
$as = ' AS fmssqln__' . $column['column'];
}
if (isset($column['expression'])) {
$expression = $column['expression'];
} else {
$expression = '"' . $alias . '"."' . $column['column'] . '"';
}
$column = $column['column'];
} else {
$as = ' AS fmssqln__' . $column;
$expression = '"' . $alias . '"."' . $column . '"';
}
if ($national_types[$table][$column] == 'ntext') {
$cast = 'CAST(' . $expression . ' AS IMAGE)';
} else {
$cast = 'CAST(' . $expression . ' AS VARBINARY(MAX))';
}
$additions[] = $cast . $as;
}
}
foreach ($sub_selects as $i => $sub_select) {
$sql = preg_replace('#:sub_select_' . $i . '\\b#', strtr($this->fixMSSQLNationalColumns($sub_select), array('\\' => '\\\\', '$' => '\\$')), $sql, 1);
}
$replace = preg_replace('#\\bselect\\s+' . preg_quote($select_clause, '#') . '#i', 'SELECT ' . strtr(join(', ', array_merge($selections, $additions)), array('\\' => '\\\\', '$' => '\\$')), $select);
$sql = str_replace($select, $replace, $sql);
}
return $sql;
}
