/**
* 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 * 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);
}
/**
* Translates basic syntax differences of the current database
*
* @param string $sql The SQL to translate
* @return string The translated SQL
*/
private function translateBasicSyntax($sql)
{
if ($this->database->getType() == 'db2') {
$regex = array('#\\brandom\\(#i' => 'RAND(', '#\\bceil\\(#i' => 'CEILING(', '#\\btrue\\b#i' => "'1'", '#\\bfalse\\b#i' => "'0'", '#\\bpi\\(\\)#i' => '3.14159265358979', '#\\bcot\\(\\s*((?>[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s*\\)#i' => '(1/TAN(\\1))', '#\\blength\\(\\s*((?>[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s*\\)#i' => 'CHARACTER_LENGTH(\\1, CODEUNITS32)', '#\\bsubstr\\(\\s*((?>[^(),]+|\\((?1)(?:,(?1))?\\)|\\(\\))+)\\s*,\\s*((?>[^(),]+|\\((?2)(?:,(?2))?\\)|\\(\\))+)\\s*,\\s*((?>[^(),]+|\\((?3)(?:,(?3))?\\)|\\(\\))+)\\s*\\)#i' => 'SUBSTRING(\\1, \\2, \\3, CODEUNITS32)', '#(?:\\b|^)((?>[^()%\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s*%(?!\\d+\\$[lbdfristp]\\b)\\s*((?>[^()\\s]+|\\(((?:[^()]+|\\((?4)\\))*)\\))+)(?:\\b|$)#i' => 'MOD(\\1, \\3)', '#(?<!["\\w.])((?>[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s+(NOT\\s+)?LIKE\\s+((?>[^()\\s]+|\\(((?:[^()]+|\\((?4)\\))*)\\))+)(?:\\b|$)#i' => 'LOWER(\\1) \\3LIKE LOWER(\\4)', '#\\blog\\(\\s*((?>[^(),]+|\\((?1)(?:,(?1))?\\)|\\(\\))+)\\s*,\\s*((?>[^(),]+|\\((?2)(?:,(?2))?\\)|\\(\\))+)\\s*\\)#i' => '(LN(\\2)/LN(\\1))');
} elseif ($this->database->getType() == 'mssql') {
$regex = array('#\\bbegin\\s*(?!tran)#i' => 'BEGIN TRANSACTION ', '#\\brandom\\(#i' => 'RAND(', '#\\batan2\\(#i' => 'ATN2(', '#\\bceil\\(#i' => 'CEILING(', '#\\bln\\(#i' => 'LOG(', '#\\blength\\(#i' => 'LEN(', '#\\bsubstr\\(#i' => 'SUBSTRING(', '#\\btrue\\b#i' => "'1'", '#\\bfalse\\b#i' => "'0'", '#\\|\\|#i' => '+', '#\\btrim\\(\\s*((?>[^(),]+|\\((?1)\\)|\\(\\))+)\\s*\\)#i' => 'RTRIM(LTRIM(\\1))', '#\\bround\\(\\s*((?>[^(),]+|\\((?1)\\)|\\(\\))+)\\s*\\)#i' => 'round(\\1, 0)', '#\\blog\\(\\s*((?>[^(),]+|\\((?1)(?:,(?1))?\\)|\\(\\))+)\\s*,\\s*((?>[^(),]+|\\((?2)(?:,(?2))?\\)|\\(\\))+)\\s*\\)#i' => '(LOG(\\2)/LOG(\\1))');
} elseif ($this->database->getType() == 'mysql') {
$regex = array('#\\brandom\\(#i' => 'rand(', '#\\bpi\\(\\)#i' => '(pi()+0.0000000000000)', '#\\blength\\(#i' => 'CHAR_LENGTH(');
} elseif ($this->database->getType() == 'oracle') {
$regex = array('#\\btrue\\b#i' => '1', '#\\bfalse\\b#i' => '0', '#\\bceiling\\(#i' => 'CEIL(', '#\\brandom\\(\\)#i' => '(ABS(DBMS_RANDOM.RANDOM)/2147483647)', '#\\bpi\\(\\)#i' => '3.14159265358979', '#\\bcot\\(\\s*((?>[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s*\\)#i' => '(1/TAN(\\1))', '#\\bdegrees\\(\\s*((?>[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s*\\)#i' => '(\\1 * 57.295779513083)', '#\\bradians\\(\\s*((?>[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s*\\)#i' => '(\\1 * 0.017453292519943)', '#(?:\\b|^)((?>[^()%\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s*%(?!\\d+\\$[lbdfristp]\\b)\\s*((?>[^()\\s]+|\\(((?:[^()]+|\\((?4)\\))*)\\))+)(?:\\b|$)#i' => 'MOD(\\1, \\3)', '#(?<!["\\w.])((?>[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s+(NOT\\s+)?LIKE\\s+((?>[^()\\s]+|\\(((?:[^()]+|\\((?4)\\))*)\\))+)(?:\\b|$)#i' => 'LOWER(\\1) \\3LIKE LOWER(\\4)');
} elseif ($this->database->getType() == 'postgresql') {
$regex = array('#(?<!["\\w.])(["\\w.]+)\\s+(not\\s+)?like\\b#i' => 'CAST(\\1 AS VARCHAR) \\2ILIKE', '#\\blower\\(\\s*(?<!["\\w.])(["\\w.]+)\\s*\\)#i' => 'LOWER(CAST(\\1 AS VARCHAR))', '#\\blike\\b#i' => 'ILIKE', '#\\b(INSERT\\s+INTO\\s+(?:\\w+|"[^"]+")\\s+)\\(\\s*\\)\\s+VALUES\\s+\\(\\s*\\)#i' => '\\1DEFAULT VALUES');
} elseif ($this->database->getType() == 'sqlite') {
if (version_compare($this->database->getVersion(), 3, '>=')) {
$regex = array('#\\bcurrent_timestamp\\b#i' => "datetime(CURRENT_TIMESTAMP, 'localtime')", '#\\btrue\\b#i' => "'1'", '#\\bfalse\\b#i' => "'0'", '#\\brandom\\(\\)#i' => '(ABS(RANDOM())/9223372036854775807)');
} else {
$regex = array('#\\bcurrent_timestamp\\b#i' => "CURRENT_TIMESTAMP()", '#\\bcurrent_time\\b#i' => "CURRENT_TIME()", '#\\bcurrent_date\\b#i' => "CURRENT_DATE()", '#\\btrue\\b#i' => "'1'", '#\\bfalse\\b#i' => "'0'", '#\\brandom\\(\\)#i' => '(ABS(RANDOM())/9223372036854775807)', '#\\bcast\\(\\s*((?:[^()\\s]+|\\(((?:[^()]+|\\((?2)\\))*)\\))+)\\s+as\\s+(?:[^()\\s]+|\\(((?:[^()]+|\\((?3)\\))*)\\))+\\s*\\)#i' => '\\1');
}
}
return preg_replace(array_keys($regex), array_values($regex), $sql);
}
