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