/**
* Translates Flourish SQL `ALTER TABLE * RENAME TO` 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 translateSQLiteRenameTableStatements($sql, &$extra_statements, $data)
{
$tables = $this->getSQLiteTables();
if (in_array($data['new_table_name'], $tables)) {
$this->throwException(self::compose('A table with the name "%1$s" already exists', $data['new_table_name']), $sql);
}
if (!in_array($data['table'], $tables)) {
$this->throwException(self::compose('The table specified, "%1$s", does not exist', $data['table']), $sql);
}
// We start by dropping all references to this table
$foreign_keys = $this->getSQLiteForeignKeys($data['table']);
foreach ($foreign_keys as $foreign_key) {
$extra_statements = array_merge($extra_statements, $this->database->preprocess("ALTER TABLE %r DROP FOREIGN KEY (%r)", array($foreign_key['table'], $foreign_key['column']), TRUE));
}
// SQLite 2 does not natively support renaming tables, so we have to do
// it by creating a new table name and copying all data and indexes
if (version_compare($this->database->getVersion(), 3, '<')) {
$renamed_create_sql = preg_replace('#^\\s*CREATE\\s+TABLE\\s+["\\[`\']?\\w+["\\]`\']?\\s+#i', 'CREATE TABLE "' . $data['new_table_name'] . '" ', $this->getSQLiteCreateTable($data['table']));
$this->addSQLiteTable($data['new_table_name'], $renamed_create_sql);
// We rename string placeholders to prevent confusion with
// string placeholders that are added by call to fDatabase
$renamed_create_sql = str_replace(':string_', ':sub_string_', $renamed_create_sql);
$create_statements = str_replace(':sub_string_', ':string_', $this->database->preprocess($renamed_create_sql, array(), TRUE));
$extra_statements[] = array_shift($create_statements);
// Recreate the indexes on the new 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);
// Fix the table name to the new table
$create_sql = str_replace($match[1], preg_replace('#(?:`|\'|"|\\[|\\b)?' . preg_quote($data['table'], '#') . '(?:`|\'|"|\\]|\\b)?#i', '"' . $data['new_table_name'] . '"', $match[1]), $create_sql);
// We change the name of the index to keep it in sync
// with the new table name
$new_name = preg_replace('#^' . preg_quote($data['table'], '#') . '_#i', $data['new_table_name'] . '_', $name);
$create_sql = preg_replace('#[\'"`\\[]?' . preg_quote($name, '#') . '[\'"`\\]]?(\\s+ON\\s+)#i', '"' . $new_name . '"\\1', $create_sql);
$extra_statements[] = $create_sql;
$this->addSQLiteIndex($new_name, $data['new_table_name'], $create_sql);
}
$column_names = $this->getSQLiteColumns($data['table']);
$extra_statements[] = $this->database->escape("INSERT INTO %r (%r) SELECT %r FROM %r", $data['new_table_name'], $column_names, $column_names, $data['table']);
$extra_statements = array_merge($extra_statements, $create_statements);
$extra_statements = array_merge($extra_statements, $this->database->preprocess("DROP TABLE %r", array($data['table']), TRUE));
// SQLite 3 natively supports renaming tables, but it does not fix
// references to the old table name inside of trigger bodies
} else {
// We add the rename SQL in the middle so it happens after we drop the
// foreign key constraints and before we re-add them
$extra_statements[] = $sql;
$this->addSQLiteTable($data['new_table_name'], preg_replace('#^\\s*CREATE\\s+TABLE\\s+[\'"\\[`]?\\w+[\'"\\]`]?\\s+#i', 'CREATE TABLE "' . $data['new_table_name'] . '" ', $this->getSQLiteCreateTable($data['table'])));
$this->removeSQLiteTable($data['table']);
// Copy the trigger definitions to the new table name
foreach ($this->getSQLiteTriggers() as $name => $trigger) {
if ($trigger['table'] == $data['table']) {
$this->addSQLiteTrigger($name, $data['new_table_name'], $trigger['sql']);
}
}
// Move the index definitions to the new table name
foreach ($this->getSQLiteIndexes($data['table']) as $name => $index) {
$this->addSQLiteIndex($name, $data['new_table_name'], preg_replace('#(\\s+ON\\s+)["\'`\\[]?\\w+["\'`\\]]?#', '\\1"' . preg_quote($data['new_table_name'], '#') . '"', $index['sql']));
}
foreach ($this->getSQLiteTriggers() as $name => $trigger) {
$create_sql = $trigger['sql'];
$create_sql = preg_replace('#( on table )"' . $data['table'] . '"#i', '\\1"' . $data['new_table_name'] . '"', $create_sql);
$create_sql = preg_replace('#(\\s+FROM\\s+)(`' . $data['table'] . '`|"' . $data['table'] . '"|\'' . $data['table'] . '\'|' . $data['table'] . '|\\[' . $data['table'] . '\\])#i', '\\1"' . $data['new_table_name'] . '"', $create_sql);
if ($create_sql != $trigger['sql']) {
$extra_statements[] = $this->database->escape("DROP TRIGGER %r", $name);
$this->removeSQLiteTrigger($name);
$this->addSQLiteTrigger($name, $data['new_table_name'], $create_sql);
$extra_statements[] = $create_sql;
}
}
}
// Here we recreate the references that we dropped at the beginning
foreach ($foreign_keys as $foreign_key) {
$extra_statements = array_merge($extra_statements, $this->database->preprocess("ALTER TABLE %r ADD FOREIGN KEY (%r) REFERENCES %r(%r) ON UPDATE " . $foreign_key['on_update'] . " ON DELETE " . $foreign_key['on_delete'], array($foreign_key['table'], $foreign_key['column'], $data['new_table_name'], $foreign_key['foreign_column']), TRUE));
}
// Remove any nested transactions
$extra_statements = array_diff($extra_statements, array("BEGIN", "COMMIT"));
// Since the actual rename or create/drop has to happen after adjusting
// foreign keys, we previously added it in the appropriate place and
// now need to provide the first statement to be run
return array_shift($extra_statements);
}
