Extending Database Abstraction Layer

For all who are interested: while CLOB is the Oracle equivalent of Text, there are substantial differences. If you're interested in ensuring your pet module works with Oracle and you are not familiar with CLOBs, please read this document.

1) Text contains up to 65535 bytes. CLOBs hold up to 4 GB. I don't know about Text storage but CLOBs are stored out of line from the database table. CLOBs are variable length. Each CLOB field, once initialized, contains a pointer to a separate memory area in the Oracle database.

2) CLOBs require initialization before use with the function EMPTY_CLOB(). Initialization can be made implicit by using the 'default EMPTY_CLOB()' phrase as part of the column definition in the CREATE TABLE statement. In the new schema all Text fields with "NOT NULL" qualifiers in DDL are implicitly initialized in the DDL. Any Text field with the NULL qualifier or no qualifier will require initialization when a new row is inserted. :shocked: If you believe that 'NULL' Text fields need to be implicitly initialized please say something. Note that implicit initialization will consume memory with every insert - if a Text field is often left null after a row is inserted then don't ask for the field to be initialized implicitly. :smile:

3) CLOBs are implicitly initialized if a text string is assigned to the field by an SQL Insert statement. If no string is assigned in the insert statement, the field CANNOT be updated without first initializing the field explicitly using the EMPTY_CLOB() function. :evil: This is important. If the new row is not implicitly initialized (via an insert or DDL default statement), subsequent attempts to update the field with a text string will fail unless the field is initialized first. It's may be possible to handle initialization using an column update Trigger that implicitly initializes a CLOB field, but that will cause DML performance to suffer. If possible, column update triggers are a better choice than implicit initialization for those tables where CLOB fields are rarely updated, and may often be null when the row is created. Please advise if you believe a trigger is required. :smile:

4) When updating a CLOB with straight SQL, the CLOB content is replaced. SQL select statements can retrieve the entire content of a CLOB field. Oracle provides a built-in package of "Mutator" and "Observer" functions for enhanced access. Mutators can modify CLOBs (APPEND, COPY, ERASE, TRIM, WRITE) and Observers can read CLOBs (COMPARE, INSTR, GETLENGTH, READ, SUBSTR). Implementation of mutator and observer functions will require careful consideration in the database abstraction layer. If you require these services of the database please advise.

5) Thoughts regarding Caching and CLOBs are welcome. I presume if users endeavor to completely fill a few 4 GB CLOBs and then Drupal attempts to cache those fields, the consequences might not be pretty. I'm not sure how Drupal handles caching but this is something to think about.

6) CLOB alternatives: VARCHAR2 is a variable length text field. Length can be up to 4000 Bytes, and must be specified in DDL. The content is stored inline with the table row. If you want to support multi-byte characters, then assume that the length is 2-bytes per character when assigning length. My review of the DDL shows heavy use of Text fields. Are they all required? If some of these fields can be redefined to VARCHAR2 fields, Oracle DBAs everywhere will sleep better.

:puzzled: A lot to think about here. I welcome all feedback, and I'm not idiot proff ;)(I'm human and make mistakes so if you see something that doesn't look right please speak up).

Cheers,

Mac

Encountered the following serious problems with the Drupal schema in Oracle:

1) Table name 'access' is not allowed
2) Column name 'uid' is not allowed (Tables: accesslog, authmap, comments, history, node, sessions, users, watchdog)
3) Column name 'size' is not allowed (Site table)

:sad:

Hi all,

In MySQL and MsSQL it appears that you can insert and empty string '' into a NOT NULL field without trouble. I assume this is the case because it's done in the build scripts.

Oracle treats an empty string as a Null, and will not allow it to be interested into a NOT NULL field. Further it is not possible to have a NOT NULL column with a DEFAULT '' clause. To address this issue I'm loosing the constraints on fields that contain a DEFAULT '' clause to allow nulls. I'll also allow nulls on all fields that have empty strings inserted into them in the database build script. This only applies to the Oracle build script I'm creating.

In a column definition DEFAULT '' and NULL are redundant in Oracle - I will drop default expressions but make the NULL statement explicit.

- Mac