Enhance cross database compatibility with PDO, MySQL, PostgreSQL, Oracle, SQLite and DB2

@Crell: totally agree about we may have a huge revamp of database abstraction layer in D7, but how will it be? And so this patch: try to approach our target step by step. First work out something, and so prove our idea by code implementation, testing and benchmarking. May be we don't even need a totally revamp of abstraction, which both keep backward compatibility, forward supporting with PDO + other databases, and also booting up performance? No one know the answer, until we try and implement it :)

I just did a simple benchmarking for v0.3, with following setup:

1. Apache/2.2.3 (Debian) Zend Core/2.0.1 PHP/5.2.3 mod_ssl/2.2.3
2. MySQL: 5.0.32-Debian_7etch1-log
3. VMware client with 1GB Ram and 2000Mhz AMD CPU
4. Users: 2000
5. Node: 5000
6. Comments: 10000
7. Vocabularies : 15
8. taxonomy: 250

And here is the benchmarking result. You may also find the complete benchmarking result set (please remove the tail .txt within filename) in attachment (c: Concurrency Level; n: Complete requests; all counted in ms):

                  +-------------+---------------+---------------+
                  | c1, n500    | c5, n500      | c10, n500     |
+-----------------+-------------+---------------+---------------+
| D6 HEAD         | 311 +- 33.5 | 1538 +- 337.9 | 3031 +- 736.6 |
+                 +-------------+---------------+---------------+
|                 | 309 +- 29.8 | 1504 +- 634.7 | 2972 +- 762.9 |
+                 +-------------+---------------+---------------+
|                 | 305 +- 30.8 | 1497 +- 330.8 | 3040 +- 702.0 |
+-----------------+-------------+---------------+---------------+
| D6 HEAD + patch | 304 +- 30.4 | 1526 +- 325.0 | 3015 +- 558.9 |
+                 +-------------+---------------+---------------+
|                 | 308 +- 30.5 | 1582 +- 826.6 | 3003 +- 739.0 |
+                 +-------------+---------------+---------------+
|                 | 307 +- 25.6 | 1515 +- 307.3 | 2974 +- 853.7 |
+-----------------+-------------+---------------+---------------+

According to the above benchmarking result, we may conclude that:

1. The extend of abstraction layer may not greatly reduce the overall performance. Don't forget we are now adding 3 types of extra abstraction: decimal and float callback, '%s' syntax clean up, and also with standardized LOBs value handling
2. The extend of abstraction can help about other database development. I just try to migrate existing D6 LOBs patch within this implementation, which is already found as very important for Oracle and DB2 functioning
3. Keep the _db_query_callback() in sharing can simplify the develop and maintenance of all database driver. As the abstraction will not greatly reduce our performance (even adding 3 extra types of abstraction), I will suggest to keep it as sharing
4. _db_query_callback() is something that need to be extend, but not remove, reduce, or even isolate into each database driver implementation. This patch show that we are still missing those abstraction handling for %d|%f|%s|%c, based on cross database compatibility concern
5. Single benchmarking result is not solid enough. All of the above 18 times of benchmarking are both running on same VMware client, but you can find there is a lots of different and variation. Multiple benchmarking results are something that even not able to trust, so how about a single?

Latest research progress result: with v0.3 progress, plus abstract some database specific function by using API, e.g. SUBSTR vs SUBSTRING, LENGTH vs DATALENGTH, random and concatenate strings handling. It is mainly cloned from ADOdb (http://phplens.com/lens/adodb/docs-adodb.htm#substr). The implementation is not perfect: I am consider about changing those function call as define constant, but seems will generate conflict with other db_* functions?

Code patched with latest drupal-6.x-dev CVS HEAD. Not yet fully tested. Seems a bit buggy in admin/user/user filter handling (mainly related to the change of '%%%s%%') ;p

Patch update with latest CVS HEAD, with minor bug fixes, includes %s handling. It is now including:

1. Standardized LOBs value handling (http://drupal.org/node/147947)
2. Updated _db_query_callback() handling, include %d|%f function wrapper abstraction, and update of %s syntax among queries (http://www.php.net/pdo, http://edin.no-ip.com/html/?q=node/310).
   

   As D7 may equal to the use of PHP5, but PDO is not the absolute choose for PHP5 (on or before PHP5.2.x), it is required to keep this minor abstraction for backward compatibility, unless we are now shipping with PHP6 :(

3. Additional abstraction on database specific special SQL functions, e.g. LENGTH, SUBSTRING, RAND, etc (http://drupal.org/node/167335)

Next Target: enclose all table/column/constraint name with [], for solving database specific reserved words conflict (http://drupal.org/node/371).

Schema API (in XML style) and INSERT/UPDATE/DELETE API should be handled as individual issue (http://edin.no-ip.com/html/?q=node/310). We should tread them as bonus feature for D7 database abstraction layer, but not essential elements, as they will not directly affect the overall PDO and other database drivers development. It is all about developers friendly :)

The actual PDO driver development can get start after next stage of exploration, which should also comes together with legacy mysql/mysqli/pgsql/oracle/db2 drivers supporting.

Here is the benchmarking result, for ensure no degrade of overall performance. It reuse above benchmarking system setup and drupal configuration. The result is positive:

                      +-------------+---------------+----------------+
                      | c1, n500    | c5, n500      | c10, n500      |
+---------------------+-------------+---------------+----------------+
| drupal-6.x-dev-cvs  | 301 +- 21.0 | 1467 +- 211.7 | 3035 +- 782.7  |
+                     +-------------+---------------+----------------+
|                     | 297 +- 20.7 | 1469 +- 182.9 | 2861 +- 652.7  |
+                     +-------------+---------------+----------------+
|                     | 296 +- 33.9 | 1430 +- 239.9 | 2898 +- 698.2  |
+---------------------+-------------+---------------+----------------+
| drupal-6.x-dev-pre7 | 295 +- 25.4 | 1462 +- 221.5 | 2928 +- 873.4  |
+                     +-------------+---------------+----------------+
|                     | 308 +- 37.7 | 1461 +- 193.4 | 2889 +- 856.8  |
+                     +-------------+---------------+----------------+
|                     | 296 +- 23.9 | 1453 +- 700.1 | 2923 +- 1514.8 |
+---------------------+-------------+---------------+----------------+

Notice: this patched version (include devel patch) is able to pass though all installation procedures, but it is not yet fully tested as ALL functioning. This is only a technical preview for exploration. Please use it with your own risk.

a little round up: all queries under /includes are now enclosed with [] for identifiers and name escape (together with updated handling in database.mysql*.inc). as stated in http://drupal.org/node/371, this additional syntax should able to fix the reserved word conflict permanently, under different conditions (e.g. version change in MySQL, or add other database supporting such as Oracle and DB2).

as this is a huge job (i need to change EVERY queries line by line... sometime even need to trace overall program logic as queries are dynamically generated...), it is now only implemented for MySQL version: PgSQL and Oracle will added afterward. benchmarking will also be done after changing all core modules and tested as functioning, in order to ensure there is no greatly performance degrade based on this additional handling :)

P.S. also comes together with a minor update of LOBs API. BTW, i am now trying to explore if we will able to merge INSERT/UPDATE/DELETE APIs together with existing db_update_clob() and db_update_blob() (may be coming with additional arguments based on LOBs needs...), so keep our API as simple as possibile :)

The patch contain the progresses of following research result:

1. MySQL and PgSQL database drivers cleanup: #183902, #183892 and #183896
2. Simplify db_query_range() and db_query_temporary(): #183910
3. INSERT/UPDATE/DELETE API + drupal_drop_record() + drupal_write_record() hack: #183148
4. Database-specific SQL functions/operators abstraction: #182864
5. CLOB/BLOB hack with INSERT/UPDATE/DELETE API + drupal_write_record(): #147947

This patch is tested for both MySQL and PgSQL with latest D6 CVS HEAD, which worked as an extension of cross database compatible issue. Since we may move to PHP5.2 + PDO in D7, The progress of this patch may not directly contribute to the coming version, e.g. database.mysql*.inc may replaced as database.pdo_mysql.inc with new PDO API.

BTW, all of the above progress WON'T benefited by the change using PDO, so they are REQUIRED based on cross database compatibility concern; on the other hand, unofficial D6 Oracle driver will based on this patch: this patch handle most database variation with minimum changes, so oracle driver will able to implement as a drop-in version (#39260).

P.S. the following issue is not included within this patch. They are required for a complete revamp of queries implementation, which is not easy to be handle within a single patch:

1. Remove SQL reserved word conflict by using [] syntax: #371
2. Replace all '%s' syntax: this issue

Just be honest about my motivation of cleaning up drupal_write_record():

1. Oracle need special INSERT/UPDATE handling based on LOBs limitation. I ask one of the PDO core developer (wez@php.net) once before, and that is not much hope to solve this within near furture.
2. INSERT/UPDATE/DELETE API can help the above problem. Recall Crell's comment, this APIs shouldn't be a bad idea: just depend on how we implement it.
3. drupal_write_record() and drupal_drop_record() can simplify and standardize by using INSERT/UPDATE/DELETE API.

From my point of view, that is not too meaningful if we just focusing on cleaning up drupal_write_record() itself: it should combine with some other enhancement, or at least come with drupal_drop_record().

Anyway, it is already ban by Dires (http://drupal.org/node/183148#comment-614475), even I try to propose both w/o INSERT/UPDATE/DELETE API version (http://drupal.org/node/183148#comment-614473). Even it is too pity, I need to wait for D7 :(

Minor update:

1. Code update via CVS HEAD, fully checked with mysql, mysqli and pgsql.
2. Update INSERT/UDPATE/DELETE API syntax. Use db_query_insert($table, $values) rather than db_query_insert("{". $table ."}", $values) syntax, which synchronize with drupal_write_record() and drupal_drop_record().
3. Merge progress with database.pgsql.inc code cleanup (http://drupal.org/node/183896).

Minor update: just simply coding style clean up, and reroll via CVS HEAD. Test with both MySQL and PgSQL.

P.S. Patch may seems so large; BTW, most of the changes (except INSERT/UPDATE/DELETE API and LOBs handling) should able to commit within D6. If someone would like to help about keeping this unofficial DB API enhancement as simple as possible, please give me a hand about issues listed in #10. Thank you.

Latest research progress, which integrate ALL points as mentioned in this issue, plus some additional changes discovered during implantation.

The patch is GIANT since I need to change ALL core queries into new coding standard: remove %%, simplify '%s' as %s and add [] syntax for all table/column/constrain name. It seems to be crazy, but this is the main point for a complete PDO and Oracle driver implementation.

I am going to split the progress result into number of small patches for D7; on the other hand, I will host a project call Siren, which provide this unofficial database driver supporting along D6 life cycle.

Latest research progress. For checkout latest development snapshot:

cvs -z6 -d:pserver:anonymous:anonymous@edin.no-ip.com:/cvs/siren checkout siren

Following on the first release candidate of Drupal 6.0, Siren 1.0 RC1 is now released, too. There is no critical changes for Siren since beta4. Attachment below is for patching Drupal 6.0 RC1.

Read more of this story at my personal research project Siren.

Following on the second release candidate of Drupal 6.0, I am proud to present to you the Siren 1.0 RC2. I fix some hidden and critical bug since Siren 1.0 RC1, including syntax bug in drupal_write_record() and mishandling in oci8's db_escape_string(). There is no critical change in case of MySQL and PostgreSQL. I am confident that the code is stable enough for wider testing by the community. Attachment below is for patching Drupal 6.0 RC2.

Read more of this story at my personal research project Siren.

Following on the thrid release candidate of Drupal 6.0, I am proud to present to you the Siren 1.0 RC3. There is a lot of changes since Siren 1.0 RC2, including the REMOVE of CLOB abstraction (replaced with BLOB), complete revamp some driver implementation (including oci8, pdo_mysql and pdo_pgsql), add pdo_oci driver with limited supporting, and an indeed cross diff for coding style synchronize among different database driver implementation. I am confident that the code is stable enough for wider testing by the community.

Read more of this story at my personal research project Siren.

A minor progress report on today: just able to complete Drupal 6.x installer (hacked with Siren) with SQLite, face the welcome screen, install all optional module (except forum and search), generate dummy content with devel, and finally pass a AB stress test together with MySQL/PostgreSQL/Oracle. So now we have both 4 databases on hand, and all of them come with PDO implementation (even some is not yet perfect).

I did a simple review and benchmarking for this. Patch below is the latest research progress.

I have been waiting for a workable implementation from http://drupal.org/node/225450 since last comment; BTW, according to information provided by that issue, the installer is still "almost done" with some module support are broken (e.g. poll, message from IRC #drupal), up to this moment. It is not suitable to say as "superseded" with an incomplete and still under development implementation, especially when this issue have been proposal, complete and keep update for more than 2 months (since #15), where progress already split for individual review, too.

On the other hand, this issue mention a workable implementation for Drupal 6.x. Even they have different approach for next generation, but not means it is suitable to judge each other as duplicated. I would like to contribute the research progress of this issue for our next generation, especially when they are ready to split for individual review; but up to this moment, it is still too early.

I am now requesting a new project in SourceForge for public CVS service, rather than in-house implementation. I guess it will soon be done, please feel free to contact me if you hope to give a hand for it, or grain for commit right :-)

On the other hand, it is a good point of using simpletest. I learn from chx's project and run parallel test with mysqli and pgsql, for both drupal-7.x-dev and my implementation. Most coming with same result report (except forum and search module, as they contain non-ANSI standard queries which are not within my concern), and some even better, e.g. profile test and module test. Based on this result, we can for sure that MOST core patches for query syntax update are backward compatible with CVS HEAD.

Moreover, since I split the implementation into 3 sections: schema, common and database, which only database.*.inc are PHP driver specific (please refer to http://groups.drupal.org/node/8907#drupal-7.x), we can figure out MOST other bugs for pdo_mysql/pdo_pgsql driver are related to PHP PDO implementation ITSELF, e.g. the CLIENT_FOUND_ROWS bug that chx and Crell have figure out. BTW, that is not our duty, or at least that is not the bug of implementation of this thread. Please don't mix them up.

And once again, please be polite: we may have different approach, but not means we can't contribute and learn form each other. E.g. I learn that SVN/CVS should host by public server for better project image and easy for join development, or using simpletest for professional quality check. It is still too early to judge that issue as "superseded" and this issue as "duplicated" if none of the patch is released for public review, within drupal.org.

All patches below via CVS HEAD. Able to pass though ALL installation procedures, activate MOST core modules, running ALL simpletest checking with similar result as CVS HEAD, generate dummy data by devel for AB benchmarking, etc. Most progresses are split for individual review, please feel free to comment about that :-)

Since my server is down and so loss all existing research progress, I reuse the above uploaded patch and reroll most progress. Below are patches of the missing ring. Moreover, an array style connection string handling is clone/hack/backport from http://drupal.org/node/225450 with simpler implementation, and added to Siren since 1.0 (I will split its progress as individual patch afterward for simple review).

As I promised in #25, a SourceForge project page is now setup as http://sourceforge.net/projects/siren. You may obtain the full packages from there. CVS repository is now uploading and soon ready. I will keep on posting Siren's research progress under this issue, and so keep on contributing Drupal but not running away from it.

@recidive:
1 & 2. Check here for more information: http://drupal.org/node/371
3. Since the original target of siren is "support other database with minimal logical changes", keep using %d but not :value just because this change give NONE OF BENEFIT to multiple database supporting, and so out of the range of Siren's target.
4. We can drop; but once again, there is no different for Siren implementation. As it can support both legacy and PDO driver, and legacy drivers are still keep on supporting within PHP5.x (PHP6 will be purely PDO based), it is not my duty for the removal decision.
5 & 6. Siren is just a project code name, and just cut down Oracle/DB2/SQLite within this patch is not too meaningful: if a single code base can support for multiple database, why we need to split it? On the other hand, I have also split most research progress as individual patches, and this should be more useful for solid and simple review: http://groups.drupal.org/node/8663

About the question from http://drupal.org/node/172541#comment-751091:

1. Problem mentioned is mainly due to the bug of pdo_mysql, e.g. CLIENT_FOUND_ROWS. That is out of the research scope of this issue: research (the missing part of) Drupal with multiple database support.
2. Using PDO::ERRMODE_EXCEPTION will cause exception during install with pdo_mysql (e.g. when variable_get() from table which not yet exists); BTW, the same code base is functioning when work with mysql/mysqli. Therefore the error is according to the implementation of queries of Drupal core but not about the pdo_mysql driver implementation of this issue, since PDO::ERRMODE_EXCEPTION is more strict than normal mysql/mysqli handling.
3. Patched version is tested with simpletest and pass with same result as that of drupal-7.x-dev (only except search and forum module, which are not expected to be covered within this research project). Patch for simpletest is appended on above, too.

For the problem of giant patch, research progress already split as small and simple patches as mentioned in http://groups.drupal.org/node/8663. People who are interesting in Drupal + MySQL/PostgreSQL/Oracle/DB2/MSSQL/SQLite/etc should give a hand to those basic elements, in order to improve Drupal core as less specific database dependent.

@David: What if we need Oracle/DB2/SQLite support on today? This issue is target for such on time needs. It is no point to ask people wait for a year for Drupal 7, with a may/may not support of other databases. Some people are asking me "how to obtain this research progress?" for their project (including a staff from IBM in US which asking for DB2 support, and a company with hosting 30+ website in Oracle 9i which hope to move to Drupal), and so I keep on update this issue. This is not a toy for myself but a solution which target for production, before we have a real hope of other database support in Drupal core.

On the other hand, what chx mentioned in http://drupal.org/node/172541#comment-751091 is about the bug of PHP pdo_mysql implementation. Why that issue should include in this patch, but not in the patch query of PHP? Moreover, the stability of this patch is already proved by simpletest. When Drupal CVS HEAD also face buggy report with simpletest, what is the duty of this patch to become perfect?

This patch itself shouldn't be commit directly since too giant in size; BTW, it have a duty to show out the possibility of Drupal. If we hope to push the case forward, please give a hand to issues mentioned in http://groups.drupal.org/node/8663, but not striking the progress of this issue.