Using schema prefixes with PostgreSQL
This page discusses usage of PostgreSQL schemas for prefixes. "Normal" prefixes can be used in the same way as in MySQL, so they won't be discussed here.
PostgreSQL has something called 'schemas' (http://www.postgresql.org/docs/current/static/ddl-schemas.html). They can be very handy sometimes, but if you don't know what they are, you probably don't actually need them and can stop reading here.
Schemas can be used as prefixes within Drupal. That is, with a multisite setup, each site can reside in its own schema, and shared tables can reside in a "shared" schema (or even in the public schema).
There is one annoyance: the upgrade will fail. This is unfortunate, but nothing can be done as the "normal" (not schema) and schema prefixes are just incompatibile. If you are interested in the details, please see http://drupal.org/node/40034.
But, don't worry. This can be easily fixed by changing the update script (update.php and updates.inc) a bit. The problem lies in the CREATE [UNIQUE] INDEX and ALTER TABLE ... DROP/ADD CONSTRAINT statesments. When schema prefixes are used, queries like this are executed:
CREATE INDEX prefix.search_total_word_idx ON prefix.search_total(word)
ALTER TABLE prefix.boxes DROP CONSTRAINT prefix.boxes_title_key
ALTER TABLE test.contact ADD CONSTRAINT test.contact_category_key UNIQUE (category)The prefix must be removed from the index and constraint name--that is they must be changed to:
CREATE INDEX search_total_word_idx ON prefix.search_total(word)
ALTER TABLE prefix.boxes DROP CONSTRAINT boxes_title_key
ALTER TABLE test.contact ADD CONSTRAINT contact_category_key UNIQUE (category)You can easily search for CREATE INDEX, CREATE UNIQUE INDEX and ADD/DROP CONSTRAINT statements and remove the {} from index/constraint names.
The best way is to run a test upgrade. You'll see a list of failed queries and it will be easier for you to change them.
Another remark: you can't use prefix.sh to prefix the tables, it will produce incorrect CREATE [UNIQUE] INDEX queries. This, also, can be easily fixed, by changing:
s/^CREATE INDEX \(.*\) ON /CREATE INDEX $PREFIX\\1 ON $PREFIX/;
s/^CREATE UNIQUE INDEX \(.*\) ON /CREATE UNIQUE INDEX $PREFIX\\1 ON $PREFIX/;to:
s/^CREATE INDEX \(.*\) ON /CREATE INDEX \\1 ON $PREFIX/;
s/^CREATE UNIQUE INDEX \(.*\) ON /CREATE UNIQUE INDEX \\1 ON $PREFIX/;An alternate approach
Here's what you need to do if you want to solve it this way:
1. Add the following at the top of your database.pgsql file:
CREATE SCHEMA schemaname;
SET search_path TO schemaname;2. Edit drupal/includes/database.pgsql.inc, replacing the function db_connect() with:
<?php
function db_connect($url) {
$url = parse_url($url);
$db_and_schema = explode(".",substr($url['path'], 1));
$conn_string = ' user='. $url['user'] .' dbname='. $db_and_schema['0'] .' password='. $url['pass'] . ' host=' . strtr($url['host'],'+','/');
$conn_string .= isset($url['port']) ? ' port=' . $url['port'] : '';
$connection = pg_connect($conn_string) or die(pg_last_error());
if(!empty($db_and_schema['1'])) pg_query('SET search_path TO '.$db_and_schema['1']);
return $connection;
}
?>3. Finally, use a db_url akin to such in your settings.php file(s):
$db_url = 'pgsql://user:password@+tmp/dbname.schemaname';- Not thoroughly tested, but works for me (on 4.6.3).
- This also fixes the inability to specify a Unix socket as the host - the +tmp gets replaced with /tmp for pg_connect().
Drupal 6.x
SQL command after database connect
Actually, as of Drupal 6.9, I couldn't find a file named updates.inc, and a grep 'CONSTRAINT' update.php modules/update/* returned nothing.
In the second solution, the trick is to
* embed the schema name in $dburl (in settings.php),
* then modify db_connect() code to
o extract the schema name from $dburl
o rebuild a suitable connection string (remove it from $db_url) for
pg_connect()
o build and send a SQL instruction to the engine, telling it to use the specified schema.Searching db_connect() in Drupal site, I found this old post #26549: SQL command after database connect ", unfortunately closed. The links it points to restrict the idea to fixing encoding (now rigidly set to utf-8).
It sounds to me like a cleaner and more flexible version of the second approach described here.
I posted this alternate solution as #375763: Running site-specific SQL commands on database connect
Moving Drupal tables to their dedicated PostgreSQL schema
After applying the patch I submitted in #375763: Running site-specific SQL commands on database connect, I created a blog entry, with PHP code input format, and the following body content:
<?php
global $db_type;
if ($db_type!='pgsql')
die ("This tool is intended for PostgreSQL databases only.
Current engine: $db_type");
$res = db_query("SELECT current_schema();" );
$pg_schema_active = db_result($res);
$pg_schema_new = 'drupal';
if ($pg_schema_active == $pg_schema_new) {
echo "<p>Current schema is '$pg_schema_new' already.\n";
echo "The following is useless.</p>\n";
}
echo <<< EOT
<p>To move your Drupal tables from current PostgreSQL schema
"<code>$pg_schema_active;</code>" to their dedicated
"<code>$pg_schema_new;</code>" namespace, you need to:
</p>
<ol>
<li>save this code in a file, e.g. "<code>migrate-drupal-schema.sql</code>"</li>
<li>put your Drupal site offline (maintenance mode).</li>
<li>Enable PostgreSQL schema support in your Drupal site (Issue [#375763])</li>
<li>execute sql code with 'psql' or 'phppgadmin' for example</li>
<li>edit your <code>settings.php</code> file and add (a good place is just below \$db_url and \$db_prefix):
<pre>
\$db_after_connect_sql = "SET search path TO $pg_schema_new;";
<pre>
</li>
<li>Put your Drupal site back online.</li>
</ol>
EOT;
$drupal_schema = drupal_get_schema(NULL, TRUE);
$migrate_sql = "CREATE schema $pg_schema_new;\n\n";
foreach (array_keys($drupal_schema) as $table_name) {
$migrate_sql .= "ALTER TABLE $pg_schema_active.$table_name SET SCHEMA $pg_schema_new;\n";
}
echo "<textarea cols=\"80\" rows=\"15\" name=\"migrate-drupal-schema\">\n";
echo $migrate_sql;
echo "</textarea>\n";
?>Its output should be self explanatory. Please post comments in the support forum if you have problems.