[after DB:TNG] PostgreSQL schema support

@jmpoure, Thanks for the patch. I slightly changed your submission, so that the patch is attached to it, rather than as a <code>. I'm also attaching an update to your patch, that cleans it up in regards to coding standards.

Since strtok() returns FALSE in newer PHP versions, I've slightly changed the code.

Also added an example to default.settings.php.

For those folks unfamiliar with pgSQL, a note about what the use-cases for different schemas are would be helpful.

I think I get it, but I always test against the default 'public' schema. I presume that there are valid reasons for using a different schema.

@Ken: IMHO that is a bit out of scope. We do not provide such information for MySQL either. AFAIK, using different schemas is dependent on your project-specific database architecture only.

@sun I didn't mean in the patch, just in the thread, so that reviewers know why this patch is important to pgSQL users.

See http://www.postgresql.org/docs/current/interactive/ddl-schemas.html for a comprehensive overview. I did not have to use a schema other than 'public' in a project yet. However, for large-scale enterprise projects, DBAs might need it reasonably.

This patch needs at least two successful tests from users working with PostGreSQL and at least one from a user working with MySQL.

The line

+  if ($schema != '') {

Should probably be changed for better clarity:

+  if (!empty($schema)) {

Yes. However, depending on the PHP version, strtok() returns either "" or FALSE, so a simple

  if ($schema) {
    @pg_query($connection, 'SET search_path TO '. $schema);
  }

Syntax makes sense that way or as !empty, whichever.

Now, here's a great question. How do I test this patch?

That is, how do I install Drupal to a pgSQL schema other than 'public'? Would we not also need a form element in install.php?

Recall PostgreSQl guideline:

5.7.7. Portability

In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. Therefore, many users consider qualified names to really consist of username.tablename. This is how PostgreSQL will effectively behave if you create a per-user schema for every user.

Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use (perhaps even remove) the public schema.

Of course, some SQL database systems might not implement schemas at all, or provide namespace support by allowing (possibly limited) cross-database access. If you need to work with those systems, then maximum portability would be achieved by not using schemas at all.

Seems that is not a great idea for using different schema... Split by database should be a better approach? Please correct me if I am worng ;-)

Moreover, Drupal 6.0 RC2 is already out (http://drupal.org/drupal-6.0-rc2). It seems not to be a suitable timing for this patch sliding into D6, so let's come back when D7 is open for public development?

First of all, my Siren implementation is just a temporary solution: we may change connection string as array format in D7 (http://drupal.org/node/184917). Since I am now working with both ibm_db2, pdo_mysql, pdo_pgsql, etc, it is a critical problem if I don't place the DB type in "fragment" section (It is already off-topic in this issue... Please refer to link above).

Moreover, I don't have much idea about splitting logical storage with database schema (P.S. != Drupal Schema API). We may only need this feature when facing a really HUGE user base and installation. In this case, Oracle RAC may even provide a better solution ;-)

Anyway, it is really too late for D6. I don't think we have chance for sliding this before D7; as D7 may provide more support for multiple database backend (hope so, if it is not striking by some people...), I will come back with this issue when D7 is open for public development :-)

In that context, I have to agree. Since implementing the patch will require modifying the installer, this is a significant change. And we are too late in the release cycle to consider it properly.

hswong3i has been doing a great amount of work in this area, and has frequently had to delay his work to 7.x for the same reasons.

@jmpoure, the best approach in this case is to file a patch that does what you need. Then interested testers can implement it against Drupal 6 and have sufficient time to explore the consequences of such a change. That process makes full support for this feature much more likely.

A new patch is needed for 7.x, provided this issue is still of interest.

A new patch would need to be in the context of #225450: Database Layer: The Next Generation.

The DB:TNG patch is about to land. Postponed until that.

There is #302327: Support cross-schema/database prefixing like we claim to which will add schema support to Drupal 7 via the db_prefix mechanism. Realistically, a database prefix is a poor mans schema. Using the prefix is better because you can prefix tables to reference tables in other schemas, such as sharing a users table across several drupal sites sitting on separate schemas.

With #302327: Support cross-schema/database prefixing like we claim to commited to Drupal 7 (I don't think it will be too far away). Is there any other reason why this issue should be marked as drupal 7?

#302327: Support cross-schema/database prefixing like we claim to was commited, what should now happen with this issue? Is it still required or can we close it?

We need to test that schema support works now. Technically it should but there is always the off chance that its still broken. Moving this to a task because we claim schema support works