The documentation for $db_prefix should make clear that "." is allowed

According to both the discussion in #371: resolve ANSI SQL-92/99/2003 reserved words conflict in query statements and #103519: Install doesn't allow "." in db prefix for psql schema (that you vandalized), this should not be. A dot in $db_prefix is supported, and works for both MySQL (allows sharing of specific tables between instances of Drupal that are installed in different databases, with different credentials) and PostgreSQL (schema).

@Damien: I totally understand your point of view, but please don't close an issue without enough discussion. Well, that is another story if you are able to answer my idea point by point with enough support.

Well, that is another story if you are able to answer my idea point by point with enough support.

God, I already did. You simply *don't listen*.

The primary goal of prefixTables() is for table prefix (e.g. table_prefix_) replacement only, but not for database_name.table_prefix_ syntax (since tables are not located in same database):

Queries sent to Drupal should wrap all table names in curly brackets. This function searches for this syntax and adds Drupal's table prefix to all tables, allowing Drupal to coexist with other systems in the same database if necessary.

You have no argument there. You are simply stating your point of view, which is totally unsubstantiated.

The comment from default.settings.php never mention dot as valid syntax clearly, but only use alphanumeric and underscore as example valid database characters:

Be sure to use valid database characters only, usually alphanumeric and underscore.

That's a documentation issue that will need to be fixed, again this is not an argument.

Most online reference from drupal.org or even somewhere else never mention dot as valid syntax:

Here are a host of tutorials that explain how to setup multisites with shared tables:
http://drupal.org/node/43816
http://groups.drupal.org/multisite
http://www.practicalweb.co.uk/blog/08/08/07/drupal-multisite-shared-tabl...

Idem, that's a documentation issue.

By using database_name.table_prefix_ syntax we have a lot of limitation, e.g. both database must come with identical connection parameters, which result as similar effect of locate all different tables under same database instance:

This is because we only define ONE SET of connection parameter correctly. In this case the use of split DB is just equal effect with all tables located within single database instance. As MySQL come with nearly none of maximum number of tables limitation within single database instance, I can't find the usefulness of split DB handling.

That's really not true. In MySQL you can define *per table* and *per database* permissions. That means that you really can have the following setup:

• Site A has full access to database A with A credentials
• Site B has full access to database B with B credentials
• Site B has also access to some tables in database A with B credentials

Even if we define different set of connection parameters in settings.php, as no db_set_active() is ever called there is no actual effect:

The $db_url will only load during connection establish, where db_set_active() will take action for. As we do a tricky cross database switching with $db_prefix => database_name.table_prefix, Drupal is being cheated as all tables belongs to same database. It is function just because we hit the backdoor of both Drupal and MySQL, but not means this syntax is expected as valid :S

This don't mean anything know that I clarified that your previous argument is false.

When we are considering for escape all database constraint name as reserved word safe (e.g. http://drupal.org/node/371), [{tablename}] will not able to convert as `prefix_tablename` simply, since it may result as `dbname.prefix_tablename` (if dot exists in $db_prefix) which is invalid SQL syntax.

Well. I'm pretty sure you can easily change that. I don't see any limitation here, you are only being *lazy*. The world will not bend because you think it should. We will not drop a supported feature because you can't even program a simple replacement logic.

Something need to clarify:

You have no argument there. You are simply stating your point of view, which is totally unsubstantiated.

According to CVS log message, db_prefix_table() was first introduced to Drupal since Jul 10 2003; around 1 year later (Jul 14 2004), it was being well documented and NEVER MENTION cross database setup with dot syntax as valid. Once install.php was introduced for D5 since Jul 13 2006 (including the good works from chx and steven), the installer check $db_prefix correctly as expected: without dot support. If all these development footprint is still not enough for proving my point of view about "db_prefix_table() is originally designed for table prefix only but not for database name switching hack": sorry, I can't say anything more than that.

That's really not true. In MySQL you can define *per table* and *per database* permissions. That means that you really can have the following setup:...

Form this point I need to agree with you; but I still can't find its usefulness. If: 1. MySQL almost come with no limitation of tables within single DB, and 2. MySQL allow for *per table* permissions, so why don't locate all tables within single DB + setup permission per tables + using $db_prefix without tricks? If the world is now bended as incorrect, why don't we give some effort in order to fix it!?

Well. I'm pretty sure you can easily change that. I don't see any limitation here, you are only being *lazy*.

I have *already* change that with an example implementation! Yes, it is not elegant enough as it is just a demonstration of idea, but you also have the right to amen it.

That's a documentation issue that will need to be fixed, again this is not an argument.

Idem, that's a documentation issue.

If you DO think this is a documentation issue, please don't be *lazy* and file your idea in order to correct it. I will suggest you to: 1. change the title of this issue as documentation related, 2. submit YOUR patch and let the others review it, and 3. let it become a real standard that no longer debatable. Simply close and mask this issue don't give any help for people understand its background concern and indeed discussion.

You vandalized one issue already, your points are heard there and in another and it's over. This won't happen sorry.

@chx: My suggestion should be very clear (if this is really a documentation problem ONLY):

If you DO think this is a documentation issue, please don't be *lazy* and file your idea in order to correct it. I will suggest you to: 1. change the title of this issue as documentation related, 2. submit YOUR patch and let the others review it, and 3. let it become a real standard that no longer debatable. Simply close and mask this issue don't give any help for people understand its background concern and indeed discussion.

If you are talking about "won't fix" of the "patch for rollback dot support" itself, just let it be and I don't really care; but if you are talking about we "won't fix the document and keep the confusion" (again, assume this is a documentation problem ONLY), I can't understand your point of view :S

In my opinion blocking cross-db table sharing is unacceptable. While there may not be any theoretical limitation of having a thousand tables in a DB, there certainly are practical limitations. Trying to get novice developers to work with database dumps of all of that would be near impossible.

Hi,
interesting that my blog is referenced in this thread as an argument for preventing database prefixes

http://www.practicalweb.co.uk/blog/08/08/07/drupal-multisite-shared-tables

It's only since writing that post that I realised database prefixes are possible.

The project I am currently working on originally used two databases - one for "drupal" content and another for business data.

The other database was connected to using http://api.drupal.org/api/function/db_set_active/5

But the problem with this approach is that if an error occurs while connected to the non-drupal database - error handling attempts to log to the watchdog table in the wrong database

As a result we merged the two databases - this has had it's own problems.

We currently intend to use the database prefix method to use two databases - with one connection.

and I need to update my blog.

I'm strongly in favour of updating the documentation to refer to the database prefix

Changing this behaviour would break the sites of those who rely on it

see also
http://www.drupaler.co.uk/blog/multi-site-drupal-6x/55
http://drupal.org/node/291373 (Multi-site with single codebase, different content databases, shared user database, shared sign-on)

For Drupal 7, actually, the solution is for selected parts of core that *must* use the main database to bypass db_query() and access the default connection directly. Watchdog and the registry are both good candidates there.

Is it possible for a documentation bug to be fixed in current versions?

I'd like to +1 Damien's suggestion as well as Sean's proposal that the docs change is back-ported. Seems to me the documentation could stand to be clearer. Until Damien told me I could use the "dot" in settings.php, I didn't know - it's a great feature, but I've never seen any documentation about it. =)

It seems that "." is going to cause problems during updates since the way D5 and D6 check for table existence in postgresql doesn't take into account schemas.

Problem seems solved in D7 (does actually D7 support schemas?)

I still have to figure how to "backport" D7 solution to D5 and D6 in a clean way...
I've no idea about how/if D5-7 support schemas (including update path), so I'm not sure if backporting the solution of D7 is going to make any good to D5-6.

It looks there are still some problems to fix in INDEX creation in D7 if schema are going to be supported:

#140860: Consistent table names and database handling of table names is currently debating implementing schema logic seperate from db_prefix logic. This makes sense since schema/db reference in db_prefix has been more accidental than intentional.

I do understand that using schema is a hack. But somehow it is a working manegable hack. (eg.PostgreSQL let you selectively restore schemas over existing DB and moving tables across schema preserve constraint and move sequences etc..).

This issue was about documentation. At the current state . in prefix is permitted but it may cause "solvable" issues.
From my point of view unless D7 fully support schema, a "." in prefix is still going to be permitted, but it is going to cause problems.
It would be nice if that was documented. It is going to be a reminder of the issues people may have to solve for D7, a warning to user updating etc...

I'm building up a reasonable experience with drupal, PostgreSQL and schema. If and when and where you're planning a patch let me know. Thanks.

As a side note: quoting object names may cause other "side effects". Names becomes case sensitive. SQL standard says objects with no quotes should be downfolded to uppercase. Unfortunately this is one of the rare cases where PostgreSQL doesn't follow the standard... and we may incur in other issues.
Furthermore... quoting is a pain ;)
PostgreSQL doesn't support writable VIEWS "out of the box", they may be ready for 8.4 (?) or they may be implemented with RULES... but I wouldn't go that route (at least now).