database support

After investing a lot of time trying to figure out how to do multi-site via a shared database, I've come to the following (personal opinion) conclusion.

Just Say No

Reasons:

Warning - I'm a noob, please be gentle

Here's a pretty much self-documented settings.php file showing how I've got my multi-site working. If someone familiar with Drupal's (v5) database structures could scan this and comment I'd really appreciate it. Note ALL the core tables are listed here, as per grepping "CREATE TABLE" from the .install files.


$db_prefix = array(
'default' => 'watchout_drupal.shb1sh_', /* new tables get created here, /
/ need to analyze and then add (shared or separate?) below /
'cache' => 'shb1sh_', / must be separate /
'cache_filter' => 'shb1sh_', / must be separate /
'cache_menu' => 'shb1sh_', / must be separate /
'cache_page' => 'shb1sh_', / must be separate /
'variable' => 'shb1sh_', / must be separate /
'accesslog' => 'shb1sh_', / site tracking - separate /
'client' => 'shb1sh_', / site tracking - separate /
'client_system' => 'shb1sh_', / site tracking - separate /
'flood' => 'shb1sh_', / site tracking - separate /
'watchdog' => 'shb1sh_', / site tracking - separate

My primary battle plan should be enhance Drupal cross database compatibility. I have involved in this topic for around a year, and I would like to keep it on going. To accomplish this target, I would like to complete the following tasks before D7 code freeze:

<

ul>

I have summarize most of my personal battle targets in here. Most logic are proved as functioning, and they are all get set for the open of D7 public development. On the other hand, I would like to explore if this may integrate with other interest musings with Data API, so we will able get all stuff better in D7 ;-)

So, I've had some free time latley, and I've started back on the database API layers (DB2 and SQL Server currently).
However, I have found 2 disturbing issues.

Overview
First, I'm currently exploring the following 3 ways to connect to sql server:
1. FreeTDS
2. php_mssql driver
3. MSFT SQL Driver ( See pcorbetts posting for a link to the driver )

1. PHP 5.2.5 breaks support for SQL Server

After 4 days (!?) development, a new DB2 driver for Drupal 6.x is now ready for testing. It takes IBM's D5 DB2 implementation as base reference, and reuse almost all Oracle driver for Drupal 6.x progress for overcoming DB2-specific limitations.

After over 1.5 years of development (since 29/11, 2005), plus number of developers contribution, and help of Drupal 6.x schema API, a new Oracle driver for Drupal 6.x is now ready for testing.

The latest version of Oracle driver overcome a lot of Oracle-specific limitation, and able to work well as like as existing Drupal database driver, e.g. MySQL and PgSQL.

Welcome to the Database Schema API group. A number of different Drupal developers have independently suggested and/or coded various approaches to abstracting table creation/alteration. The idea finally seems to have reached critical mass and I suggest that we will be best served by combining everyone's efforts on the topic. Hence, this group.

By way of background, here is some recommended reading on the topic:

Ok, I'm just going to start listing the SQL statements that I find that need changing here. Trying to release patches for them all and keep up is getting out of hand:

Watchdog
- Line 123, remove the u.uid, it is not needed since the w.* already gives you that column.

(more to come soon)

So, I was checking out ODBTP < http://odbtp.sourceforge.net/ > and considering making a drupal driver for it. This would allow many database to now be supported through ODBC. I also hear that ODBTP is just as fast as a native driver and the speed of ODBC really depends on the implementation of the vendor of ODBC. Just seeing if anyone had any experience with it and/or thoughts?

Ok, i'll be the one to call the pink elephant in the room.....stored procedures do exisit. There I said it. :). With MySQL 5 starting to support them, Postgres has had them, and the push latley to start using a wider array and more robust DB's (oracle, mssql, DB2), it is time I think to start thinking about a database abstraction layer to handel stored procedures. I'm thinking perhaps something like this:

/**
* @param $proc_name Name of the stored proc.
* @param $args Argements to send to the proc indexed by the variables name. e.g. array('name' => 'Jim Smith');