diff options
author | Dries Buytaert <dries@buytaert.net> | 2010-04-07 15:07:59 +0000 |
---|---|---|
committer | Dries Buytaert <dries@buytaert.net> | 2010-04-07 15:07:59 +0000 |
commit | dde5c67ba041dc65588377808b1943fdd3b57bf6 (patch) | |
tree | 133c901b2517a88d36060da686dd95903e84d079 | |
parent | 626e64025eb85faf819b9d17298df505e9d0526a (diff) | |
download | brdo-dde5c67ba041dc65588377808b1943fdd3b57bf6.tar.gz brdo-dde5c67ba041dc65588377808b1943fdd3b57bf6.tar.bz2 |
- Patch #302327 by Josh Waihi, noahb, Crell, hswong3i: support cross-schema/database prefixing like we claim to.
-rw-r--r-- | INSTALL.pgsql.txt | 16 | ||||
-rw-r--r-- | includes/database/database.inc | 20 | ||||
-rw-r--r-- | includes/database/mysql/schema.inc | 36 | ||||
-rw-r--r-- | includes/database/pgsql/schema.inc | 25 | ||||
-rw-r--r-- | includes/database/schema.inc | 71 | ||||
-rw-r--r-- | includes/database/sqlite/schema.inc | 28 | ||||
-rw-r--r-- | modules/system/system.test | 2 | ||||
-rw-r--r-- | sites/default/default.settings.php | 16 |
8 files changed, 172 insertions, 42 deletions
diff --git a/INSTALL.pgsql.txt b/INSTALL.pgsql.txt index f5f276e58..a2aeaea59 100644 --- a/INSTALL.pgsql.txt +++ b/INSTALL.pgsql.txt @@ -26,3 +26,19 @@ Note that the database must be created with UTF-8 (Unicode) encoding. createdb --encoding=UTF8 --owner=username databasename If there are no errors then the command was successful + +3. CREATE A SCHEMA OR SCHEMAS (Optional advanced) + + Drupal will run across different schemas within your database if you so wish. + By default, Drupal runs inside the 'public' schema but you can use $db_prefix + inside settings.php to define a schema for Drupal to inside of or specify tables + that are shared inside of a separate schema. Drupal will not create schemas for + you, infact the user that Drupal runs as should not be allowed to. You'll need + execute the SQL below as a superuser (such as a postgres user) and replace + 'drupaluser' with the username that Drupal uses to connect to PostgreSQL with + and replace schema_name with a schema name you wish to use such as 'shared': + + CREATE SCHEMA schema_name AUTHORIZATION drupaluser; + + Do this for as many schemas as you need. See default.settings.php for how to + set which tables use which schemas. diff --git a/includes/database/database.inc b/includes/database/database.inc index a85489a5b..aa4cc671f 100644 --- a/includes/database/database.inc +++ b/includes/database/database.inc @@ -384,6 +384,26 @@ abstract class DatabaseConnection extends PDO { } /** + * Find the prefix for a table. + * + * This function is for when you want to know the prefix of a table. This + * is not used in prefixTables due to performance reasons. + */ + public function tablePrefix($table = 'default') { + global $db_prefix; + if (is_array($db_prefix)) { + if (isset($db_prefix[$table])) { + return $db_prefix[$table]; + } + elseif (isset($db_prefix['default'])) { + return $db_prefix['default']; + } + return ''; + } + return $db_prefix; + } + + /** * Prepares a query string and returns the prepared statement. * * This method caches prepared statements, reusing them when diff --git a/includes/database/mysql/schema.inc b/includes/database/mysql/schema.inc index 93c547eea..8a9d0176e 100644 --- a/includes/database/mysql/schema.inc +++ b/includes/database/mysql/schema.inc @@ -25,6 +25,26 @@ class DatabaseSchema_mysql extends DatabaseSchema { const COMMENT_MAX_COLUMN = 255; /** + * Get information about the table and database name from the db_prefix. + * + * @return + * A keyed array with information about the database, table name and prefix. + */ + protected function getPrefixInfo($table = 'default') { + $info = array('prefix' => $this->connection->tablePrefix($table)); + if (($pos = strpos($info['prefix'], '.')) !== FALSE) { + $info['database'] = substr($info['prefix'], 0, $pos); + $info['table'] = substr($info['prefix'], ++$pos) . $table; + } + else { + $db_info = Database::getConnectionInfo(); + $info['database'] = $db_info['default']['database']; + $info['table'] = $info['prefix'] . $table; + } + return $info; + } + + /** * Build a condition to match a table name against a standard information_schema. * * MySQL uses databases like schemas rather than catalogs so when we build @@ -35,16 +55,11 @@ class DatabaseSchema_mysql extends DatabaseSchema { protected function buildTableNameCondition($table_name, $operator = '=') { $info = $this->connection->getConnectionOptions(); - if (strpos($table_name, '.')) { - list($schema, $table_name) = explode('.', $table_name); - } - else { - $schema = $info['database']; - } + $table_info = $this->getPrefixInfo($table_name); $condition = new DatabaseCondition('AND'); - $condition->condition('table_schema', $schema); - $condition->condition('table_name', $table_name, $operator); + $condition->condition('table_schema', $table_info['database']); + $condition->condition('table_name', $table_info['table'], $operator); return $condition; } @@ -273,7 +288,8 @@ class DatabaseSchema_mysql extends DatabaseSchema { throw new DatabaseSchemaObjectExistsException(t("Cannot rename %table to %table_new: table %table_new already exists.", array('%table' => $table, '%table_new' => $new_name))); } - $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO {' . $new_name . '}'); + $info = $this->getPrefixInfo($new_name); + return $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO `' . $info['table'] . '`'); } public function dropTable($table) { @@ -446,7 +462,7 @@ class DatabaseSchema_mysql extends DatabaseSchema { * Retrieve a table or column comment. */ public function getComment($table, $column = NULL) { - $condition = $this->buildTableNameCondition($this->connection->prefixTables('{' . $table . '}')); + $condition = $this->buildTableNameCondition($table); if (isset($column)) { $condition->condition('column_name', $column); $condition->compile($this->connection, $this); diff --git a/includes/database/pgsql/schema.inc b/includes/database/pgsql/schema.inc index fcff331fb..5ababf623 100644 --- a/includes/database/pgsql/schema.inc +++ b/includes/database/pgsql/schema.inc @@ -95,7 +95,7 @@ class DatabaseSchema_pgsql extends DatabaseSchema { } if (isset($table['unique keys']) && is_array($table['unique keys'])) { foreach ($table['unique keys'] as $key_name => $key) { - $sql_keys[] = 'CONSTRAINT {' . $name . '}_' . $key_name . '_key UNIQUE (' . implode(', ', $key) . ')'; + $sql_keys[] = 'CONSTRAINT ' . $this->prefixNonTable($name, $key_name, 'key') . ' UNIQUE (' . implode(', ', $key) . ')'; } } @@ -297,7 +297,9 @@ class DatabaseSchema_pgsql extends DatabaseSchema { } // Now rename the table. - $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO {' . $new_name . '}'); + // Ensure the new table name does not include schema syntax. + $prefixInfo = $this->getPrefixInfo($new_name); + $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO ' . $prefixInfo['table']); } public function dropTable($table) { @@ -409,7 +411,7 @@ class DatabaseSchema_pgsql extends DatabaseSchema { return FALSE; } - $this->connection->query('ALTER TABLE {' . $table . '} DROP CONSTRAINT {' . $table . '}_pkey'); + $this->connection->query('ALTER TABLE {' . $table . '} DROP CONSTRAINT ' . $this->prefixNonTable($table, 'pkey')); return TRUE; } @@ -421,8 +423,7 @@ class DatabaseSchema_pgsql extends DatabaseSchema { throw new DatabaseSchemaObjectExistsException(t("Cannot add unique key %name to table %table: unique key already exists.", array('%table' => $table, '%name' => $name))); } - $name = '{' . $table . '}_' . $name . '_key'; - $this->connection->query('ALTER TABLE {' . $table . '} ADD CONSTRAINT "' . $name . '" UNIQUE (' . implode(',', $fields) . ')'); + $this->connection->query('ALTER TABLE {' . $table . '} ADD CONSTRAINT "' . $this->prefixNonTable($table, $name, 'key') . '" UNIQUE (' . implode(',', $fields) . ')'); } public function dropUniqueKey($table, $name) { @@ -430,8 +431,7 @@ class DatabaseSchema_pgsql extends DatabaseSchema { return FALSE; } - $name = '{' . $table . '}_' . $name . '_key'; - $this->connection->query('ALTER TABLE {' . $table . '} DROP CONSTRAINT "' . $name . '"'); + $this->connection->query('ALTER TABLE {' . $table . '} DROP CONSTRAINT "' . $this->prefixNonTable($table, $name, 'key') . '"'); return TRUE; } @@ -451,8 +451,7 @@ class DatabaseSchema_pgsql extends DatabaseSchema { return FALSE; } - $name = '{' . $table . '}_' . $name . '_idx'; - $this->connection->query('DROP INDEX ' . $name); + $this->connection->query('DROP INDEX ' . $this->prefixNonTable($table, $name, 'idx')); return TRUE; } @@ -495,7 +494,7 @@ class DatabaseSchema_pgsql extends DatabaseSchema { } protected function _createIndexSql($table, $name, $fields) { - $query = 'CREATE INDEX "{' . $table . '}_' . $name . '_idx" ON {' . $table . '} ('; + $query = 'CREATE INDEX "' . $this->prefixNonTable($table, $name, 'idx') . '" ON {' . $table . '} ('; $query .= $this->_createKeySql($fields) . ')'; return $query; } @@ -520,13 +519,13 @@ class DatabaseSchema_pgsql extends DatabaseSchema { * Retrieve a table or column comment. */ public function getComment($table, $column = NULL) { - $table = $this->connection->prefixTables('{' . $table . '}'); + $info = $this->getPrefixInfo($table); // Don't use {} around pg_class, pg_attribute tables. if (isset($column)) { - return $this->connection->query('SELECT col_description(oid, attnum) FROM pg_class, pg_attribute WHERE attrelid = oid AND relname = ? AND attname = ?', array($table, $column))->fetchField(); + return $this->connection->query('SELECT col_description(oid, attnum) FROM pg_class, pg_attribute WHERE attrelid = oid AND relname = ? AND attname = ?', array($info['table'], $column))->fetchField(); } else { - return $this->connection->query('SELECT obj_description(oid, ?) FROM pg_class WHERE relname = ?', array('pg_class', $table))->fetchField(); + return $this->connection->query('SELECT obj_description(oid, ?) FROM pg_class WHERE relname = ?', array('pg_class', $info['table']))->fetchField(); } } } diff --git a/includes/database/schema.inc b/includes/database/schema.inc index ab2a95a03..5302a6d22 100644 --- a/includes/database/schema.inc +++ b/includes/database/schema.inc @@ -150,6 +150,16 @@ abstract class DatabaseSchema implements QueryPlaceholderInterface { */ protected $placeholder = 0; + /** + * Definition of prefixInfo array structure. + * + * Rather than redefining DatabaseSchema::getPrefixInfo() for each driver, + * by defining the defaultSchema variable only MySQL has to re-write the + * method. + * + * @see DatabaseSchema::getPrefixInfo() + */ + protected $defaultSchema = 'public'; public function __construct($connection) { $this->connection = $connection; @@ -160,6 +170,48 @@ abstract class DatabaseSchema implements QueryPlaceholderInterface { } /** + * Get information about the table name and schema from the db_prefix. + * + * @param + * Name of table to look prefix up for. Defaults to 'default' because thats + * default key for db_prefix. + * @return + * A keyed array with information about the schema, table name and prefix. + */ + protected function getPrefixInfo($table = 'default') { + $info = array( + 'schema' => $this->defaultSchema, + 'prefix' => $this->connection->tablePrefix($table), + ); + // If the prefix contains a period in it, then that means the prefix also + // contains a schema reference in which case we will change the schema key + // to the value before the period in the prefix. Everything after the dot + // will be prefixed onto the front of the table. + if ($pos = strpos($info['prefix'], '.') !== FALSE) { + // Grab everything before the period. + $info['schema'] = substr($info['prefix'], 0, $pos); + // Grab everything after the dot, and prefix on to the table. + $info['table'] = substr($info['prefix'], ++$pos) . $table; + } + else { + $info['table'] = $info['prefix'] . $table; + } + return $info; + } + + /** + * Create names for indexes, primary keys and constraints. + * + * This prevents using {} around non-table names like indexes and keys. + */ + function prefixNonTable($table) { + $args = func_get_args(); + $info = $this->getPrefixInfo($table); + $args[0] = $info['table']; + return implode('_', $args); + } + + /** * Build a condition to match a table name against a standard information_schema. * * The information_schema is a SQL standard that provides information about the @@ -175,7 +227,7 @@ abstract class DatabaseSchema implements QueryPlaceholderInterface { * to make all the others work. For example see includes/databases/mysql/schema.inc. * * @param $table_name - * The name of the table to explode. + * The name of the table in question. * @param $operator * The operator to apply on the 'table' part of the condition. * @@ -185,18 +237,13 @@ abstract class DatabaseSchema implements QueryPlaceholderInterface { protected function buildTableNameCondition($table_name, $operator = '=') { $info = $this->connection->getConnectionOptions(); - // The table name may describe the schema eg. schema.table. - if (strpos($table_name, '.')) { - list($schema, $table_name) = explode('.', $table_name); - } - else { - $schema = 'public'; - } + // Retrive the table name and schema + $table_info = $this->getPrefixInfo($table_name); $condition = new DatabaseCondition('AND'); $condition->condition('table_catalog', $info['database']); - $condition->condition('table_schema', $schema); - $condition->condition('table_name', $table_name, $operator); + $condition->condition('table_schema', $table_info['schema']); + $condition->condition('table_name', $table_info['table'], $operator); return $condition; } @@ -210,7 +257,7 @@ abstract class DatabaseSchema implements QueryPlaceholderInterface { * TRUE if the given table exists, otherwise FALSE. */ public function tableExists($table) { - $condition = $this->buildTableNameCondition($this->connection->prefixTables('{' . $table . '}')); + $condition = $this->buildTableNameCondition($table); $condition->compile($this->connection, $this); // Normally, we would heartily discourage the use of string // concatenation for conditionals like this however, we @@ -253,7 +300,7 @@ abstract class DatabaseSchema implements QueryPlaceholderInterface { * TRUE if the given column exists, otherwise FALSE. */ public function fieldExists($table, $column) { - $condition = $this->buildTableNameCondition($this->connection->prefixTables('{' . $table . '}')); + $condition = $this->buildTableNameCondition($table); $condition->condition('column_name', $column); $condition->compile($this->connection, $this); // Normally, we would heartily discourage the use of string diff --git a/includes/database/sqlite/schema.inc b/includes/database/sqlite/schema.inc index 894d51c63..f8f18eb74 100644 --- a/includes/database/sqlite/schema.inc +++ b/includes/database/sqlite/schema.inc @@ -14,6 +14,11 @@ class DatabaseSchema_sqlite extends DatabaseSchema { + /** + * Override DatabaseSchema::$defaultSchema + */ + protected $defaultSchema = 'main'; + public function tableExists($table) { // Don't use {} around sqlite_master table. return (bool) $this->connection->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE '{" . $table . "}'", array(), array())->fetchField(); @@ -45,14 +50,19 @@ class DatabaseSchema_sqlite extends DatabaseSchema { */ protected function createIndexSql($tablename, $schema) { $sql = array(); + $info = $this->getPrefixInfo($tablename); if (!empty($schema['unique keys'])) { foreach ($schema['unique keys'] as $key => $fields) { - $sql[] = 'CREATE UNIQUE INDEX "{' . $tablename . '}_' . $key . '" ON {' . $tablename . '} (' . $this->createKeySql($fields) . "); \n"; + // Normally we don't escape double quotes (we use single quotes) but + // describing the index name like this is faster and is readable. + $index = "\"{$info['schema']}\".\"{$info['table']}_$key\""; + $sql[] = 'CREATE UNIQUE INDEX ' . $index . ' ON ' . $info['table'] . ' (' . $this->createKeySql($fields) . "); \n"; } } if (!empty($schema['indexes'])) { - foreach ($schema['indexes'] as $index => $fields) { - $sql[] = 'CREATE INDEX "{' . $tablename . '}_' . $index . '" ON {' . $tablename . '} (' . $this->createKeySql($fields) . "); \n"; + foreach ($schema['indexes'] as $key => $fields) { + $index = "\"{$info['schema']}\".\"{$info['table']}_$key\""; + $sql[] = 'CREATE INDEX ' . $index . ' ON ' . $info['table'] . ' (' . $this->createKeySql($fields) . "); \n"; } } return $sql; @@ -228,7 +238,13 @@ class DatabaseSchema_sqlite extends DatabaseSchema { $schema = $this->introspectSchema($table); - $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO {' . $new_name . '}'); + // SQLite doesn't allow you to rename tables outside of the current + // database. So the syntax '...RENAME TO database.table' would fail. + // So we must determine the full table name here rather than surrounding + // the table with curly braces incase the db_prefix contains a reference + // to a database outside of our existsing database. + $info = $this->getPrefixInfo($new_name); + $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO ' . $info['table']); // Drop the indexes, there is no RENAME INDEX command in SQLite. if (!empty($schema['unique keys'])) { @@ -445,7 +461,7 @@ class DatabaseSchema_sqlite extends DatabaseSchema { return FALSE; } - $this->connection->query('DROP INDEX ' . '{' . $table . '}_' . $name); + $this->connection->query('DROP INDEX ' . $this->prefixNonTable($table, $name)); return TRUE; } @@ -469,7 +485,7 @@ class DatabaseSchema_sqlite extends DatabaseSchema { return FALSE; } - $this->connection->query('DROP INDEX ' . '{' . $table . '}_' . $name); + $this->connection->query('DROP INDEX ' . $this->prefixNonTable($table, $name)); return TRUE; } diff --git a/modules/system/system.test b/modules/system/system.test index fa33ac3a7..a18112230 100644 --- a/modules/system/system.test +++ b/modules/system/system.test @@ -24,7 +24,7 @@ class ModuleTestCase extends DrupalWebTestCase { * specified base table. Defaults to TRUE. */ function assertTableCount($base_table, $count = TRUE) { - $tables = db_find_tables(Database::getConnection()->prefixTables('{' . $base_table . '}') . '%'); + $tables = db_find_tables($base_table . '%'); if ($count) { return $this->assertTrue($tables, t('Tables matching "@base_table" found.', array('@base_table' => $base_table))); diff --git a/sites/default/default.settings.php b/sites/default/default.settings.php index 6a3a4a77d..b219e67a8 100644 --- a/sites/default/default.settings.php +++ b/sites/default/default.settings.php @@ -131,6 +131,22 @@ * 'authmap' => 'shared_', * ); * + * You can also use db_prefix as a reference to a schema/database. This maybe + * useful if your Drupal installation exists in a schema that is not the default + * or you want to access several databases from the same code base at the same + * time. + * Example: + * + * $db_prefix = array( + * 'default' => 'main.', + * 'users' => 'shared.', + * 'sessions' => 'shared.', + * 'role' => 'shared.', + * 'authmap' => 'shared.', + * ); + * + * NOTE: MySQL and SQLite's definition of a schema is a database. + * * Database configuration format: * $databases['default']['default'] = array( * 'driver' => 'mysql', |