edin.no-ip.com

This is a quick checklist for develop Drupal 7.x Oracle driver. They need to be solved before Oracle slip into Drupal core.

Reserved word conflict (integrate with siren)

Drupal core use some reserved word of Oracle, e.g. uid. To solve this we have 2 choice: 1. escape all table/column/constrain name with escape characters (http://drupal.org/node/371#comment-636053), or 2. clone Moodle implementation and prevent the use of reserved words for all supported database (http://docs.moodle.org/en/XMLDB_reserved_words).

Here we choose to use solution 2. This come with some extra benefit that, if we need to connect Drupal's database from other projects, e.g. Moodle, they will able to fetch Drupal's column as there is no reserved word conflict. So this can improve both cross database and cross project compatibility.

Related issues:

• resolve ANSI SQL-92/99/2003 reserved words conflict in query statements
  • [Oracle] replace reserved word 'uid' and 'access'

VARCHAR2 is no large enough, use BLOB instead (integrate with siren)

This is another critical issue. Oracle only support VARCHAR2 with maximum 4000 characters. It is always not enough for CMS. As a replacement we can use BLOB because it is more universal.

We also need to clone PostgreSQL's BLOB handling for Oracle, with RETURNING.

Related issues:

• [DBTNG + XDB] Replace some TEXT:BIG with BLOB (27/11)
  • Remap field as BLOB: {boxes}.body (27/11)
    • [DBTNG] revamp modules/block/* with DBTNG syntax (27/11)
  • Remap field as BLOB: {menu_router}.access_arguments and {menu_router}.page_arguments (27/11)
    • [DBTNG] revamp menu.inc with DBTNG syntax (27/11)
  • [DBTNG + BLOB + hash]: Revamp locales_source.source with BLOB and MD5 hashing (27/11)
  • [DBTNG + BLOB]: remap {watchdog}.variables (duplicated)
  • [DBTNG + BLOB] remap {comments}.comment (duplicated)
  • [DBTNG + BLOB] remap {aggregator_item}.description (duplicated)
  • [DBTNG + BLOB] remap {batch}.batch (duplicated)
  • [DBTNG + BLOB]: remap {node_revisions}.body and {node_revisions}.teaser (duplicated)

Empty string is not allow in Oracle, and will translate as NULL (integrate with siren)

This is a critical bug of Oracle, all empty string will translate as NULL automatically. It is NO WAY for Oracle to emulate as other RDBMS because it is a 2-state machine (valid or NULL) but not 3-state (valid, empty or NULL). Logic Diagram:

In order to overcome this limitation, we can first extend as 4 states: valid, "default", empty or NULL. Then restrict the use case as 3 states only, with replace the use of empty string as "default" value. Therefore both Oracle and other RDBMS can have identical support of SQL, and don't need to duel with different between = '' and IS NULL. Logic diagram:

Procedure: 1. update all schema as nullable string if required (only allow null with valid default value but not empty string), 2. restrict all string I/O with no empty string (force translate as NULL), 3. debug and update programming logic for using empty string with "default" value if possible. Therefore:

• Valid value: Keep untouch.
• NULL: Act as default replacement of empty string if no programming logic break.
• 'default': Use as exceptional replacement of empty string, work together with programming logic revamp.

P.S. This change will also benefit with the critical PostgrerSQL Blob bug with empty string (http://bugs.php.net/bug.php?id=41135), because we will no longer use empty string.

Related issues:

• [DBTNG + XDB] NOT NULL fields using a DEFAULT '' clause (27/11)
  • Use 'default' instead of '' as default actions parameters (duplicated)
  • Use 'default' instead of '' as default user name and password (duplicated)
  • Use 'default' instead of '' as undefined default language (duplicated)
• [DBTNG + pgsql] db_insert/db_update buggy with empty string input and BLOB field

Auto upper case table and column name (integrate with siren)

This can be handle with PDO::ATTR_CASE (http://www.php.net/manual/en/pdo.setattribute.php). Becareful! Field names should be reserved word safe.

Related issues:

• Force connection with PDO::CASE_LOWER

Max 30 characters for constraint name restriction (integrate with siren)

Oracle only allow 30 characters for constraint name. Moodle give a good example for solving this problem.

Related issues:

• [DBTNG + XDB] revamp key and index naming

Some SQL functions need abstraction (integrate with siren)

Different database coming with different SQL syntax for same function, e.g. MySQL use SUBSTR, where PostgreSQL use SUBSTRING. Some simple abstraction is required. ADOdb give a good example for solving this problem.

Related issues:

• [DBTNG + pgsql] Handle database-specific SQL functions/operators by simple abstraction

Prepare schema:

1. Check target fields (http://edin.no-ip.com/node/166).
   a. No default + NOT NULL => can't accept NULL so valid value only (1 status only)
   b. Default with '' => remove
2. Update schema with "NOT NULL => FALSE, (remove default)".
3. Running update.php. Ensure schema is valid.
4. Run related simpletest.

Once schema ready for expert install:

1. ORACLE_NULL => NULL
2. Before insert: check '' => NULL (!?)
3. Before normal variable binding: '' => NULL
4. check SQL syntax, change from = '' to IS NULL

Testing procedure:

1. expert mode -> patch -> update
2. normal mode -> patch -> update
3. normal mode + all modules -> patch -> update
4. patch -> install -> all modules

Create testing table "test" as Drupal 7.x, with auto increment sequence + trigger (http://www.databaseanswers.org/sql_scripts/ora_sequence.htm):

Insert sample data:

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:

1. Reduce most text field size mapping as VARCHAR2(1333), which only left text:big map with VARCHAR2(4000).
2. Replace some fields as BLOB (e.g. fields storing serialized data) for unlimited storage size support, where restrict some other fields as varchar 256 or even smaller.

But above tries both face some critical error, and so need to rollback:

1. Some fields (e.g. {menu_router}.access_arguments and {menu_router}.access_arguments) can't use VARCHAR2(1333) only, as they are storing serialized data; BTW, if mapping both to VARCHAR2(4000), ORA-01461 will occur.
2. BLOB field need special handling (i.e drupal_write_record() and db_decode_blob()) which will complicate the I/O task, therefore not suitable for some special fields (e.g. again {menu_router}.access_arguments and {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):

The correct implementation should be:

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?

As like as case before, Siren + pdo_oci is still buggy with ORA-01461 during installation. Well, since pdo_oci doesn't update since 3 years ago (http://pecl.php.net/package/PDO_OCI), for sure that the case will not have any change...

Anyway, this page give a very detail idea of the problem (http://littledan.itpub.net/post/6400/270539). When checking with script provided in document, I found that some table is now buggy with the problem:

If you make sure that there is only 1 LONG and no VARCHAR > 1333 bytes, OR just 1 VARCHAR > 1333 bytes in the table, you cannot hit this problem. The following query will give you all the tables with such a combination of columns:

SELECT * FROM
(SELECT TABLE_NAME, OWNER, count(*) NUM
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE='LONG'
OR (( DATA_TYPE='VARCHAR2'
or DATA_TYPE='CHAR'
or DATA_TYPE='NVARCHAR2'
or DATA_TYPE='NCHAR')
AND DATA_LENGTH > 1333)
AND OWNER NOT IN
('SYS','SYSTEM','SH','OLAPSYS','MDSYS','WKSYS','ODM','XDB','WMSYS')
GROUP BY TABLE_NAME, OWNER)
WHERE NUM > 1;

Both tables watchdog, menu_router and node_type need some modification. The solution maybe a bit simple (from other point of view, very complicated): change those text:big field into blob. This will require for both blob checking for INSERT/UPDATE and after SELECT before use.

Update: the above idea is not work, since sometime fields are using for GROUP BY where BLOB don't support for. As an alternative, I am now trying to restrict most text fields as max 1333, where only text:big as 4000. Therefore some data size limitation to normal storage, and also need to carefully specify which ONE field should be text:big. Seem more simple in solution, but need time to verify the impact...

Giving a try right now...

My Oracle 11gR1 gone crazy with 100% CPU loading when working with Debian sid. This page provide a functional solution:

Shutdown OEM, login as SYSMAN user and restart the provisioning daemon by executing the two packaged procedures

SYSMAN> execute MGMT_PAF_UTL.STOP_DAEMON

PL/SQL procedure successfully completed.

SYSMAN> execute MGMT_PAF_UTL.START_DAEMON

PL/SQL procedure successfully completed.

Start OEM again and the problem is gone.

WARNING: You must take this on your own responsibility!

So here is my detail procedures. For shutdown OEM (login as user oracle where Oracle 11gR1 installed in /u01/app/oracle/product/11.1.0/db_1/bin):

Login as SYSMAN:

Then execute the following PL/SQL commands:

Restart OEM:

Check the CPU loading and it should be fine now :-)

This simple HOWTO will guide you about how to setup Apache2.2 + PHP5.2 + OCI8/PDO_OCI from sketch. Compile all package from tarball can give you the maximum flexibility of functionality, e.g. you can enable both mysql/mysqli/pgsql/oci8/pdo_mysql/pdo_pgsql/pdo_oci within single installation.

Before start, I will assume you have Debian sid and Oracle 11gR1 installed correctly, which will not detail within this document. If you really need some help for that, please refer to my other article for more indeed guideline.

This is a simple HOWTO, for installing Oracle Database 11g Release 1 on Debian sid.

But why we need this HOWTO? Since Oracle only officially support Red Hat Enterprise Linux, SUSE Enterprise Linux and other enterprise level Linux distribution; BTW, there shouldn't be any problem if you hope to use with other distribution, which just need some special configuration and install required dependent packages. On the other hand, some dependent packages are obsoleted in Debian etch, but only available in oldstable or sid, so install with sid should be better than etch + hybrid sid packages.

"Hey man! Please stop for a while! I have no idea about how to working with Debian sid!"... Ooops... If this is your case, my other article should be your cup of tea. Go back now and let's enjoy with that for a bit while :-)

Before start you will need to prepare your Debian with:

• A complete Debian sid installation with X11.
• Static public IP address (or you may bind to localhost during installation).
• Functional hostname, e.g. hardcode it within /etc/hostname and /etc/hosts.

When I am working with pdo_oci development of Siren 1.x (Drupal 6.x), I face a critical problem: pdo_oci is not able to complete the Drupal installation procedure. BTW, as oci8 now share around 90% similar programming logic as that of pdo_oci, it works fine...

Since oci8 and pdo_oci drivers are assumed as interchangeable (they share both common.oracle.inc and schema.oracle.inc, so ~60% codes are share), I try to install with oci8 in order to get the DB up, then further more use pdo_oci during running. It looks all fine (and even pass ab test), but face a critical problem when accessing admin/build/modules (here is one of the error message, the others are similar):

When pressing F5, the case become even worse:

I guess the first ORA-01461 error is all due to pdo_oci mishandling on VARCHAR2(4000). When you search in google, you may found that similar errors were happened once before in JDBC, on 10gR2 and etc... As DB can setup successfully by oci8, there shouldn't be the bug of DB configuration nor programming logic. I will try to report this later ;-(

P.S. the interchangeable of drivers can also apply to MySQL's (mysql, mysqli and pdo_mysql) and PostgreSQL's (pgsql and pdo_pgsql). They are both functioning correctly ;-)