This post is inspired by handbook article Using schema prefixes with PostgreSQL and #26549: SQL command after database connect , as I was looking for a mean to use a dedicated PostgreSQL schema for storing drupal tables.

It looks like the issue #26549 has been restricted to encoding issues (linked to "How can I change Drupal's character encoding? (UTF-8 and Unicode)",#26990: enable MySQL client side for UTF8, "Problem utf8 mysql 4.1").

IMHO, the feature it suggests would allow to solve gracefully the problem of using Postgres schemas, by just inserting, in settings.php :

$db_prefix = '';
$db_after_connect_sql = "SET search_path TO drupal";

I worked a little on the code to allow more complex SQL scripts, like:

$db_after_connect_sql =  <<< EOSQL
SET client_encoding="UTF8";
SET search_path 
    TO drupal;
UPDATE mytable SET value ='one;two'
  WHERE id=0;  
EOSQL;

Here's part of the code I now have in my includes/database.pgsql.inc:

function db_connect($url) {
  global $db_after_connect_sql;
...
  if (isset($db_after_connect_sql)) {
    // commands end with a semi-colon at end-of line
    $commands = preg_split('/;\s*?' . PHP_EOL . '/' , $db_after_connect_sql);
    foreach($commands as $sql) {
      $sql = trim($sql);
      if (!empty($sql))
        pg_query($connection, $sql);
    }
  }
  return $connection;
}

I enclose the patches for Drupal 6.9 .

HTH,

Michelle Baert

CommentFileSizeAuthor
mysql.patch823 bytesRockyRoad
pgsql.patch1.04 KBRockyRoad

Comments

RockyRoad’s picture

RockyRoad commented

Declaration for new setting

For this to work, the new $db_after_connect_sql variable has to be set globally.

I guess the normal place for this would be just beside $db-url, in conf_init()(includes/bootstrap.inc line 291) .

But it works also by adding the declaration in settings.php, if you prefer leaving core files alone.

global $db_after_connect_sql;
RockyRoad’s picture

RockyRoad commented
Status: Active » Needs review
Issue tags: +PostgreSQL Surge, +database, +Tables, +schema, +SQL queries
josh waihi’s picture

josh waihi commented
Priority: Normal » Minor
Status: Needs review » Postponed (maintainer needs more info)

so this is so you can store drupal tables in there own schema? Its up to the database architect to setup the schemas for Drupal and by default Drupal supports this with the use of db_prefix:

$db_prefix = array(
   'default' => 'drupal.',
   'users' => 'common.',
);

The example above would prefix everytable with "drupal." except for users which would be prefixed to "common.". In this example you could have several drupal installs that share a common users table.

Does this solve what you want or have I missed something?

Again with the client encoding, it is up to the Database Architect to ensure the encoding is set right. I believe UTF-8 is the default for PostgreSQL. I do know that checks are in progress to HEAD to make sure encoding is checked at install time for PostgreSQL.

With out more information, this issue seems invalid

RockyRoad’s picture

RockyRoad commented

Hi Josh,

Thanks for reading and answering this post.

Here are some details as you requested.

about $db_prefix and PG schemas

You wrote:

Its up to the database architect to setup the schemas for Drupal and by default Drupal supports this with the use of db_prefix:

I suggested this feature after reading Using schema prefixes with PostgreSQL, which refers to #40034: Errors when using schemas and updating problem, showing that using $db_prefix doesn't work. I also thought it was a cleaner and more generic solution than tweaking $db_prefix definition and behavior.

Clear namespace separation sounds safer to me, when you consider installing, on the same limited ISP account (most free hosting services allow a single database, with single user), several independent tools using DB access.

Setting current schema in search_path ensures that no drupal module can accidentally corrupt data in the 'public' (default) namespace, at least as long as it respects the Data Abstraction Layer. For example, are you sure that the curly braces around table names are never missing ?

about database encoding

You wrote:

Again with the client encoding, it is up to the Database Architect to ensure the encoding is set right. I believe UTF-8 is the default for PostgreSQL. I do know that checks are in progress to HEAD to make sure encoding is checked at install time for PostgreSQL.

I added the encoding instruction only in an example mini-sql-script, just to reflect the fix to #26549: SQL command after database connect, currently hard-coded in db_connect(), for both mySQL and pgSQL backends. 

pg_query($connection, "set client_encoding=\"UTF8\"");

Wouldn't moving the instruction from core code to settings.php better separate DBA's responsabilities ?

To be more coherent, my patch should include this move, but it's probably better to leave that for a later change, because many users may rely on it. Below, you'll see that it's easier for now to leave default value empty for the new global $db_after_connect_sql.

The patches I submitted don't change anything to settings.php, this is left to the user (site administrator).

BTW, your ISP may not allow you to change encoding at database level.

about genericity

Sorry to go back to that, but issue #26549: SQL command after database connect suggested that the feature could be used, for example to set encoding for newly created tables. The fix released for it addresses only this specific need, by hard-coding a query in db_connect() code.
Similarly, I'm suggesting here another example use for controlling current table namespace (please notice that the title I chose for the issue doesn't mention PG nor schema).

My point is that allowing the site administrator to define custom SQL queries on database connection is likely to help her to solve a wide range of site-specific issues, constraints or needs, without need to ask for a change to Drupal core code.

Complement: about Drupal installation with this feature

When setting up a new drupal installation with this patch applied, you currently need to edit settings.php prior to loading install.php, because I didn't provide a new form field in DB configuration form. Just supply $db_url and $db_after_connect_sql as needed.

Of course, this not necessary if you don't need to set $db_after_connect_sql before drupal database installation. An empty value for it wouldn't change anything.

Implementing this new field for full GUI installation should be straightforward.

Related posts

As I use this patch for my own sites (localhost upgraded to 6.10, and ISP newly installed as 6.10), I had no issues related to this patch.

Though, about the particular use case of using PG Schema, I posted details on several places:

Feel free to tell me if there's anything I missed, or if I can help.

Best regards,

Michelle Baert

josh waihi’s picture

josh waihi commented
Version: 6.9 » 7.x-dev
Status: Postponed (maintainer needs more info) » Postponed

@Michelle Baert,
you know that this:

pg_query($connection, "set client_encoding=\"UTF8\"");

doesn't change the encoding of the database but only the client. I know that PostgreSQL requires this but not sure about MySQL. If such a command isn't in core then I'll back a movement to get it in there.

Core isn't responsible for the contrib code so if someone doesn't use {} around there tables in SQL, then that is considered a bug of the module and should be directed to module in question rather than altering core to compensate for it. That is a trade-off with using contrib modules.

This is a feature request but D6 isn't open for feature implementations. As far as D7 goes - there are steps in this direction at the moment but will still have a lot to cover first. Michelle, I'd encourage you to come help fix a few DB bugs in HEAD to get a better understanding of how the drupal db community works before trying to push this any further just yet as I think there are better and more elegant ways of doing what you're doing here :)

RockyRoad’s picture

RockyRoad commented

I'm afraid I don't read this answer as very elegant.
Explains nothing, not realistic, off-topic , and condescending.

I spent some time to study, analyse, fix, and share.
Apparently you didn't really read me.
Let me concentrate to my own needs for now.

Best regards.

Michelle

damien tournoud’s picture

damien tournoud commented
Priority: Minor » Normal
Status: Postponed » Active
Issue tags: -PostgreSQL Surge, -database, -Tables, -schema, -SQL queries +Novice

I'm in favor of implementing a 'after_connection_callback' parameter to the $databases array in D7. Should be a simple enough task, and a great jump start in our new database layer.

RockyRoad’s picture

RockyRoad commented
Version: 7.x-dev » 6.9

Thanks Damien,

+1 !

I thought of course of a callback or "hook" style implementation would be nice, but for me that's a question of core architecture, which I know is rewritten in D7.

That's beyond my purpose: before discussing *how* and *when*, I preferred talking about *what* and *why* or *why-not*.

The patch I submitted is for D6.9, so I restored the issue version tag. It is intentionally very tiny and respectful of existing code.
Until a stable version of D7 is released, it may interest some users as is.

Of course, I'd love to see the feature integrated in D7, but why not creating a 7x-dev thread for it ?

Best regards,

Michelle

damien tournoud’s picture

damien tournoud commented
Version: 6.9 » 7.x-dev

@RockyRoad: D6 is feature frozen, so this will not happen. Please help us making this feature in D7 instead!

RockyRoad’s picture

RockyRoad commented

Even if the patch is never integrated to 6.x HEAD, please leave it available, along with explanations, to 6.x users who are interested to apply the patch themselves, or discuss on their related issues. In this intent, I included links to this page in the related posts I listed above. They all relate to 6.x .

IMHO, restricting support to an unreleased version is not helping users to trust a software.

At the moment, I need to build reliable sites, so I base them on official release,
While interested, I have no time now to explore D7, sorry for that.

I created a D7-dedicated fork of this issue as #398500: Running site-specific SQL commands on database connect, but left it to
you to set this one back to 6.9, if you agreed that it would be more consistent.

Best regards,

damien tournoud’s picture

damien tournoud commented

@RockyRoad: please don't open several copies of the same issue across Drupal versions. If you want your patch to have a chance to be integrated in D6, you'll first have to make it so in D7. Drupal 6 is fully supported, but the issue queue is not the place to maintain unofficial patches.

Crell’s picture

Crell commented
Status: Active » Postponed (maintainer needs more info)

Why a callback hook in the $databases array? If there's config to be done for a specific database driver, that can, should, and is done in the driver's constructor already.

damien tournoud’s picture

damien tournoud commented

@Crell: the idea is to be able to run site-specific queries just after connection. All required engine-specific queries are / should already be in the constructor.

josh waihi’s picture

josh waihi commented

What about a module hook after a database connection is created? Site specific config can be complied into a module like other site specific customizations that aren't theme related

sun.core’s picture

sun.core commented
Category: feature » task
Status: Postponed (maintainer needs more info) » Active

Proper status.

Crell’s picture

Crell commented
Status: Active » Fixed

I verified in Paris that it's not difficult to write a custom driver that extends an existing driver, and custom queries can happen there. Since this is so edge casey, I'm happy with that approach. So marking fixed.

If you can argue that it's not edge-casey, reopen with a good argument in Drupal 8. :-)

Status: Fixed » Closed (fixed)
Issue tags: -Novice

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue tags: +Novice

Restoring issue tags, see #2125755: System messages removed all issue tags during D7 upgrade.