Supporting PostgreSQL schema level
Dear friends, First of all, thank you for developing Drupal. I plan to use Drupal for my community site (2.000.000 pages a month) and would like to contribute some code as regards PostgreSQL support. You can find here a detailed HOWTO written for PhpBB community, but I guess it applies for Drupal too : PostgreSQL query optimisation HOWTO : http://area51.phpbb.com/phpBB/viewtopic.php?f=3&t=29292 Now, a question about my first contribution: I would like to add support for PostgreSQL schema. A schema is a separate logical space in a database. I would like to allow the following syntax: $db_url = 'pgsql://username:password@localhost/databasename/schemaname'; Conditions: 1) Old syntax still valid $db_url = 'pgsql://username:password@localhost/databasename'; connects to public schema and is still valid. 2) SQL required The proposed changes only means that we run a single SQL query after connecting to the PostgreSQL database: if (isset($url['port'])) { $conn_string .= ' port='. urldecode($url['port']); } $schema=''; if (isset($url['schema'])) { $schema .= urldecode($url['schema']); } // pg_last_error() does not return a useful error message for database // connection errors. We must turn on error tracking to get at a good error // message, which will be stored in $php_errormsg. $track_errors_previous = ini_get('track_errors'); ini_set('track_errors', 1); $connection = @pg_connect($conn_string); if (!$connection) { require_once './includes/unicode.inc'; _db_error_page(decode_entities($php_errormsg)); } if ($schema !== '') { @pg_query($connection, 'SET search_path TO ' . $schema); } This does not change other queries. Now, I would like your opinion on these changes. Would you like a patch supporting PostgreSQL schema in Drupal code? Kind regards, Jean-Michel
Here is the proposed patch for PostgreSQL schema support: http://drupal.org/node/211117 Kind regards, Jean-Michel ******************************************************************* Index: includes/database.pgsql.inc =================================================================== RCS file: /cvs/drupal/drupal/includes/database.pgsql.inc,v retrieving revision 1.68 diff -u -r1.68 database.pgsql.inc --- includes/database.pgsql.inc 4 Jan 2008 09:31:48 -0000 1.68 +++ includes/database.pgsql.inc 17 Jan 2008 11:56:03 -0000 @@ -52,8 +52,12 @@ $url = parse_url($url); $conn_string = ''; + $schema=''; // Decode url-encoded information in the db connection string + // Syntax is 'pgsql://username:password@localhost/databasename' + // or 'pgsql://username:password@localhost/databasename/schema' + if (isset($url['user'])) { $conn_string .= ' user='. urldecode($url['user']); } @@ -64,7 +68,9 @@ $conn_string .= ' host='. urldecode($url['host']); } if (isset($url['path'])) { - $conn_string .= ' dbname='. substr(urldecode($url['path']), 1); + $conn_string .= ' dbname='.strtok(urldecode($url['path']), '/'); + // check for additional schema information if required + $schema .= strtok( '/'); } if (isset($url['port'])) { $conn_string .= ' port='. urldecode($url['port']); @@ -85,6 +91,11 @@ // Restore error tracking setting ini_set('track_errors', $track_errors_previous); + if ($schema !== '') + { + @pg_query($connection, 'SET search_path TO ' . $schema); + } + return $connection; }
On Thursday, January 17 2008 2:54:32 am Jean-Michel Pouré wrote:
Now, a question about my first contribution:
I would like to add support for PostgreSQL schema. A schema is a separate logical space in a database.
I would like to allow the following syntax:
$db_url = 'pgsql://username:password@localhost/databasename/schemaname';
I don't know if it provides what you are referring to, or if you're using the same term to describe something different, but in Drupal 6, there is a new Schema API: http://drupal.org/node/146843 -- Jason Flatt http://www.oadaeh.net/ Father of Six: http://www.flattfamily.com/ (Joseph, 14; Cramer, 12; Travis, 10; Angela; Harry, 7; and William, 12:04 am, 12-29-2005) Linux User: http://www.xubuntu.org/ Drupal Fanatic: http://drupal.org/
On jeu, 2008-01-17 at 08:56 -0800, Jason Flatt wrote:
I don't know if it provides what you are referring to, or if you're using the same term to describe something different, but in Drupal 6, there is a new Schema API: http://drupal.org/node/146843
It is not the same. I was refering to PostgreSQL schema : http://www.postgresql.org/docs/8.2/static/ddl-schemas.html database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema may contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user may access objects in any of the schemas in the database he is connected to, if he has privileges to do so. There are several reasons why one might want to use schemas: * To allow many users to use one database without interfering with each other. * To organize database objects into logical groups to make them more manageable. * Third-party applications can be put into separate schemas so they cannot collide with the names of other objects. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.
Quoting Jean-Michel Pouré <jm@poure.com>:
I was refering to PostgreSQL schema : http://www.postgresql.org/docs/8.2/static/ddl-schemas.html
database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema may contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user may access objects in any of the schemas in the database he is connected to, if he has privileges to do so.
There are several reasons why one might want to use schemas:
* To allow many users to use one database without interfering with each other.
* To organize database objects into logical groups to make them more manageable.
* Third-party applications can be put into separate schemas so they cannot collide with the names of other objects.
Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.
BTW, Oracle has something similar. Think of them sub-databases contained within the same database file sets. You have different table objects containing different sets of data but described the same way and the table objects have different users. Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
On 17 Jan 2008, at 8:52 PM, Earnie Boyd wrote:
BTW, Oracle has something similar. Think of them sub-databases contained within the same database file sets. You have different table objects containing different sets of data but described the same way and the table objects have different users.
nope. they are not sub databases per se, but actually well thought out and properly implemented table prefixing (which is a hack in mysql/drupal).
Actually, MySQL is the exception for NOT implementing SQL standards. All of the big commercial databases and Postgres support database objects named by a hierarchical triple. In Postgres, they are: database.schema.object In Oracle, they are: database.owner.object MySQL only implements the last 2 positions: [missing.]database.object In MySQL, a database is analogous to a Postgres schema.
participants (5)
-
adrian rossouw -
Chris Johnson -
Earnie Boyd -
Jason Flatt -
Jean-Michel Pouré