[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

Should probably be changed for better clarity:

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

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?

Thank you all for picking up my message.
It is a very nice way to welcome a new member in the community.
So thank you for your time and effort.

I really appreciate that.
I will do my best to follow the rules and work in teams.

Also, I received a private email from hswong3i and downloaded his patch called Siren.
In Siren, hswong3i implements this kind of connector :

[code]+ * $db_url = 'drupal://username:password@localhost/databasename#pdo_pgsql';
+ * $db_url = 'drupal://username:password@localhost/databasename#oci8';[/code]

Also is added support for tablespace.

A tablespace a precise location on disc.
It allows DBas to split a database on several discs.
This is a very usefull feature, which exists in PostgreSQL and Oracle.

Maybe we could have a look at this approach.
Why not implement:
$db_url = 'pgsal://username:password@localhost/databasename#tablespace/schema';
$db_url = 'oci8://username:password@localhost/databasename#tablespace';

where tablespace is the location on disc
AND schema is a logical partition in the database (to make it simple).

This syntax would have the advantage to work for PostgreSQL and Oracle needs.
It would be backwards compatible with databases like MySQL.

What do you think ?
Kind regards,
Jean-Michel

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.