After put down my multiple database backend research for Drupal among past 4 months, now finally get Drupal 6.x and PDO_OCI working together... It is all about my incorrect pdo_oci driver implementation... The progress is now available in here.
What is the bottleneck?
The main bottleneck of pdo_oci driver implementation is all about size of VARCHAR2 support under AL32UTF8. When working with AL32UTF8, as characters may store up to 2 or 3 bytes, the original maximum 4000 characters support of VARCHAR2 will cut off as 1333 (4000/3). If a single table coming with more than 1 column larger than 1333 characters, an annoying ORA-01461 error message MAY occur. This error message will not trigger when using oci8, but always happened with pdo_oci + Drupal 6.x (Siren 1.x)...
Any tries?
I have try some method to escape from this ORA-01461 error, including:
- Reduce most
textfield size mapping asVARCHAR2(1333), which only lefttext:bigmap withVARCHAR2(4000). - Replace some fields as
BLOB(e.g. fields storing serialized data) for unlimited storage size support, where restrict some other fields asvarchar256 or even smaller.
But above tries both face some critical error, and so need to rollback:
- Some fields (e.g.
{menu_router}.access_argumentsand{menu_router}.access_arguments) can't useVARCHAR2(1333)only, as they are storing serialized data; BTW, if mapping both toVARCHAR2(4000), ORA-01461 will occur. - BLOB field need special handling (i.e
drupal_write_record()anddb_decode_blob()) which will complicate the I/O task, therefore not suitable for some special fields (e.g. again{menu_router}.access_argumentsand{menu_router}.access_arguments). Since{menu_router}will finally store its rendered result in Drupal's cache system (already using BLOB field for storage), a DUPLICATED BLOB I/O handling result as a very fancy and buggy implementation.
Finally...
Well... Back to the main fantastic problem: as Drupal core hack are share among oci8 and pdo_oci version (backend independent), ~60% code and ~80% Oracle related programming logic are shared among oci8 and pdo_oci, where ~80% PDO related programming logic are also shared among pdo_pgsql and pdo_oci, I switch my focus to debug PHP's pdo_oci driver and my Drupal + pdo_oci driver implementation.
So, what is the problem? Christopher Jones on OPAL give me some hints: I have a wrong transaction handling in case of pdo_oci driver implementation. When working with Oracle + BLOB (or even not with BLOB) a transaction is always required, plus a try {} catch (Exception $e) {} in further more operation. Within my old implementation (trimmed version, share and clone between legacy and other PDO driver):
<?php
$active_db->beginTransaction();
// Execute the statement.
$result = $stmt->execute();
if ($result === FALSE) {
// Some error handling code.
$active_db->rollBack();
return FALSE;
}
// Some PDO + BLOB stream I/O handling code.
// On success, commit the transaction.
$active_db->commit();
?>The correct implementation should be:
<?php
$active_db->beginTransaction();
try {
// Execute the statement.
$result = $stmt->execute();
// Some PDO + BLOB stream I/O handling code.
// On success, commit the transaction.
$active_db->commit();
}
catch (Exception $e) {
// Some error handling code.
$active_db->rollBack();
return FALSE;
}
?>Conclusion
Even the research progress is functioning with Drupal 6.x, a lot of effort is still required. As Drupal 7.x will completely switch to PDO, where core database drivers (including MySQL and PostgreSQL) implementation are revamped with OOP, I still need to catch up my missing section among the past 4 months.
The patch for next generation is now RTBC, should I wait for its commit and propose my amendment afterward, or keep hard working and propose the amendment before everything shuttle down?
