Ready for Testing: New Oracle Driver for Drupal

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.

Special features include: isolated LOBs value handling (require additional LOBs patch to Drupal 6.x), remove db_num_rows() supporting based on duplicate SELECT query concern (require additional "remove db_num_rows()" patch to Drupal 6.x), overcome limitation in table/column/constraint naming with maximum 30 characters, rewrite table/column/constraint name if it is Oracle-specific reserved word, etc. On the other hand, according to number of other Drupal 6.x core patches, most core queries are now Oracle friendly. The above features will also benefit in others database driver implementation.

It is closed to complete, but still not perfect. First of all, as stated above, an additional LOBs patch to Drupal 6.x is required by the latest version of Oracle driver. Since this patch was submitted after code freeze, we will need some more effort about its commit.

Secondly, we need to remove db_num_rows() from Drupal 6.x DB API. This is because oci8's oci_num_rows() only return AFFECTED row number: for case of SELECT query, it will only return the number of rows fetched into memory by using oci_fetch_*(). There is no clear way to implement this API, unless a duplicate query: use first query for fetching totally number of rows, and second query for return result set. It is also a cross-database compatibility problem when using PDO (http://drupal.org/node/134580) nor ADOdb, so it should be removed. Again, since this patch was submitted after code freeze, we will also need some more effort about its commit.

Thirdly, table prefix is limited in maximum 10 characters, even though size of table name is now unlimited. This is due to a conflict when using variable_set() before creating the table "variable", and seems no simple solution. BTW, 10 characters are usually enough for table prefix.

More over, SQL statements are required to follow Drupal's query coding standard strictly. This is mainly because Oracle driver will take some regex replacement to incoming SQL statement, for overcome those Oracle-specific limitation.

Finally, your help are required. Our end goal is to have an official support of enterprise-level database within Drupal 6.x. Oracle support is one of our target, but not the only one. We need your help to point out any missing hook for other database driver implementation, e.g. DB2, SQLite, MSSQL, ODBC, etc.

If you are interesting about this topic, please feel free to test it out, and send back your feedback, bugs, and suggestions.

Drupal 6.x LOBs patch issue: http://drupal.org/node/147947
Drupal 6.x "remove db_num_rows()" issue: http://drupal.org/node/163191
Drupal 6.x Oracle driver issue: http://drupal.org/node/39260

Other resurces:
drupal-6.x-dev + Oracle Database 10g Release 2 mini-HOWTO
Oracle Database 10g Release 2 + Apache2.2 + Zend Core for Oracle v.2, on Debian etch HOWTO