Oracle Driver for 7.x

Hi all,
i'm happy to say that today i closed the last odd test cases (http://drupal.org/node/464514), and that the 7.x driver for oracle looks working.
It would be very nice if someone wants to help with it to make it work even better / faster. My poor PHP skills makes the code ugly probably, and may be some of you can contribute some ideas to support a wider variety of SQL statements or to remove/improve some Regular Expressions.

The actual state is pretty difficult to explain because i worked on the driver almost for the last year in "free time" so excuse me if do not remember all the points. I'll use this post as a T.O.C. http://drupal.org/node/39260#comment-1124563:

Reserved word conflict

It is actually solved escaping the reserved words used by Drupal with the (") character (e.g. uid -> "UID").
Only those words are escaped: uid, session, file, access, mode, comment, desc. Because this is enought for drupal and most of the modules.

The real list can be obtained with this query on an Oracle database:

select keyword from v$reserved_words where reserved= 'Y' or res_type= 'Y' or res_attr= 'Y' or res_semi= 'Y'

Those applies to any database identifier (eg. tables,columns) or any bind variable name (eg. select * from table where description= :desc ).

Empty string is not allow in Oracle, and will translate as NULL

In an oracle database the empty character IS NULL. Thats it. Say it as you want:
'' == NULL
'' === NULL
'' IS NULL

The biggest problem here are queries like:

select * from variables where value= ''

This query in MySql returns all the rows that has the "value" column == the empty charcater ''

In Oracle is simply a non sense query: nothing is equal to NULL or '' (that is null). So there is
no way to return exactly those rows. For example, translating that query to:

select * from variables where value is null

Means missing the point: in MySQL you can have rows with value == to '' and rows with value == to NULL only the first have to
be returned.

So here i need to do an ugly thing: any '' bind variable is translated to an:
define('ORACLE_EMPTY_STRING_REPLACER','^'); // used to replace '' character in queries

So that this query can be translated to:

select * from variables where value= '^'

sorry, this was the best i can do.

VARCHAR2 is no large enough, use BLOB instead

Almost done. VARCHAR2 is large enough for at least values that have a length of "4000 CHAR" (means even multibyte charactersets).
All "blob" columns are created as VARCHAR2(4000 CHAR). When binding a variable the driver takes care to handle values with a length >4000 and puts those values in a separate table: the BLOBS table indexed by an incremental ID column and sets the bind variable to:

define('ORACLE_BLOB_PREFIX','B^#'); // prefix used for BLOB values

B^#1234 where 1234 is the BLOBS.ID column value.

When fetching a value the driver translate B^#1234 back to the BLOBS.CONTENT (a real blob column) value of the row with ID = 1234.

This kind of approach automagically solves also queries like this:

select distinct b.* from block ...

or

select x, y, count(*) from table group by x, y

where "y" was a BLOB column. (BLOB columns cannot be inserted into DISTINCT, GROUP BY, ORDER BY.... in oracle)

Saving the <4000 values directly in the column saves a lot of work.

Auto upper case table and column name

Any {table} is translated to "TABLE". For columns see point 2.

NOTE: DB Prefixes are treated like schemas (see http://drupal.org/node/513106).

Max 30 characters for constraint name restriction

Oracle do not wants any identifier with a length > 30:

1. Any schema object name (table,column,index,constraint)
2. Any query column alias

ANYTHING

So simplifying: the driver keeps a LONG_IDENTIFIERS tables with a mapping of an ID and a "long identifier" (>30)
When any schema object is created the name of the schema object is passed to PL/SQL function (pragma autonomous_transaction)
to check whether to create a "long identifier". If a long identifier is created any statement containing that long identifier will be translated
to use an identifier like "L#123" eg:

select * from my_very_long_table_name_that_surely_exceeds_30_chars

will be translated to:

select * from "L#123"

The same for columns and query aliases. That means that when fetching a resultset the driver converts those names back for you e.g.:

select name, value, my_very_long_column_name_that_surely_exceeds_30_chars from mytable

is translated to

select name, value, "L#3654" from mytable

suppose you use $obj= $stmt->fetchObject() you would receive:

$obj->name
$obj->value
...
$obj->L#3654 but this is translated back for you to $obj->my_very_long_column_name_that_surely_exceeds_30_chars

Some SQL functions need abstraction

All SQL functions used by Drupal have been translated in a PL/SQL equivalent that does the same thing as on MySQL eg:

MD5 function

or

SUBSTRING_INDEX function

The "if" function is translated to a CASE block:

select if(1=0,x,y) from mytable

is transalted to

select case when 1=0 then x else y end from mytable

Using timestamps inside Oracle

For simplicity of use a PL/SQL package to translate drupal timestamps to Oracle dates and back, is provided:

create or replace package drupal_timestamp
as

/* this package can be used to convert drupal timestamp numbers to oracle dates and vice versa
*
* e.g.: select to_char(drupal_timestamp.todate(created),'DD/MM/YYYY HH24:MI:SS') created from node
*/

function todate(p_timestamp number)
return date
deterministic;

function totimestamp(p_date date)
return number
deterministic;

end;

Other considerations

Obviously DatabaseStatement_oracle extends DatabaseStatementPrefetch.
I should add soon a group by alias parser to handle those queries:

select fn(cdcdd) as myalias, count(*) from mytable group by myalias

will be translated back to:

select fn(cdcdd) as myalias, count(*) from mytable group by fn(cdcdd)

because aliases cannot be used in the group by clause in oracle

That is almost all. Hope you will not find it so ugly. It works, can be made work better, but finally it works.