Support Oracle databases

Hi puregin,
Thanks for opening this thread, I was willing to do this since my first discover of Drupal a year ago.
Since I first went to Drupal, I saw very few request on supporting Oracle DB schema, but there is some.
So I thought it could be a good way back to contrib such DB layer and I would like to help on this.

Since I first test Drupal for our intranet, I wished to connect to our internal Oracle DB. At the begining, I wished to port it, but I rapidly faced the problems listed in the threads you posted and I just let it down.
But (there's always a but ;) ) as a request for our intranet, it should be able to extract some datas from our DB. So I made some very ugly patches to open connections and extract datas.

I didn't went further since that day.

A few weeks ago, I was contacted by Cainan who asks me about my little experience and told me that he would like to begin this work with help of his colleagues. So I guess he started some work.

For my part, I saw the release of Oracle XE (free to develop, deploy, and distribute) and I think I would start some investigation about this.

I think, that we should begin by describe all the problem we could face with such port:

Database Schema:

Oracle Reserved Words:

This is a big problem we face in Drupal DB schema. A few words Drupal uses for tables/columns names may respect the SQL ANSI reserved words, but Oracle extends it a little and introduces some problems. Words like UID, ACCESS

• Oracle documentation repository
• Oracle reserved words (also take care that tables can't begin by "SYS_"), * show an ANSI reserved word.
• SQL ANSI validator

So as Dries stated, a good way would be to rename "on the fly" those words or investigate moshe option regarding PEAR library.

Datatypes:

This shouldn't be a problem, Oracle support a wide range of datatypes, we just need to update the schema.
I suggest something like :
mysql/pgsql -> oracle

• TINYINT,MEDIUMINT,INT/SMALLINT, INTEGER -> NUMBER (define an apropriate size)
• VARCHAR -> VARCHAR2
• LONGTEXT/TEXT -> CLOB (oracle deprecated the use of LONG)
• BLOB -> BLOB (investigate if they work the same way)
• FLOAT -> NUMBER (define the apropriate precision)
• AUTO_INCREMENT/SERIAL -> SEQUENCES (oracle use sequences, the use od db_next_id should help supporting this, btw, there should be a problem regarding tables which doesn't use this feature and rely on AUTO_INCREMENT/SERIAL columns)
• unsigned -> should we take care of this one ?

Functions:

Some functions are used in pgSQL schema, it shouldn't be a problem to convert them.

• greatest(integer, integer) -> max(number, number)
• greatest(integer, integer, integer) -> max(number, max(number,number))
• rand -> use DBMS_RANDOM
• concat(text, text) -> exists in Oracle

ANSI SQL:

Since 9i, Oracle DB support ANSI SQL JOINs (RIGHT JOIN etc...). I'm a big fan of Oracle notation to make OUTER JOINs = (+) but this is not very compatible.
Now, I hope that Drupal queries would work.
Maybe some attention should be given in the future to the modules, but that's common with Piotr (Cvbge) Krukowiecki's comment about "Educate MySQL users" on how to write 'standard' SQL code.
So I think we should support Oracle 9i and up (10g, XE...)

Drupal DB abstraction layer:

database.oracle.inc

We already have some examples with mysql, mysqli and pgsql includes.
Now the main challenge will be to choose which tools to use to execute queries.
Currently I listed :

• oci8 this is a native PHP library, you just need to compile php with "--with-oci8" flag to enable it. I think there should have some care to take, because a lot of functions seems to be for php5. To investigate.
• Pear I used this to connect to our oracle DB.
• ADOdb for PHP
• PDO

For the moment, I'm pretty stuck with which library and version of php we should go with. Send your ideas/experiences.
Some links of interest:
Oracle 10g XE and PHP
PHP Developer Center (Oracle.com)
The Oracle+PHP Cookbook
The DeployPHP Series (Oracle.com)
Installing PHP and the Oracle 10g Instant Client for Linux and Windows Oracle Instant Client
The PHP 5 Data Object (PDO) Abstraction Layer and Oracle
Scaling Oracle and PHP
There's also some articles about Zend, but I think this is for later ;)

updates.inc

I think we should do the same as for mysqli, only support updates when Oracle DB would be ready for core. But the way undaptes.inc is built shouldn't make any problems to upgrade Oracle DB.

To Do:

Well, I think I made a pretty clear panorama of the situation, if there's some volunteers, reply in this thread and discuss about the choices and option we get.
I won't have a lot of time these next weeks, but I hope to make a little more work by the end of this year.
Then I think we could try to make this compatible with Drupal 4.7, and if drupal devs are ok, maybe think in an official support in 4.8 or 4.9 (let's dream ;) ).

I finish there, thanks a lot for reading this, and let's go back to work.

since Drupal has moved away from Pear, oracle support shoudl use the native
php library calls.

I just checked the php doc and it seems that oci is available in php4 and didn't suffer major changes in php5.
So I think a first draft should be the db schema.
The first posted by rinehart raise interesting points :
- he made use of trigger to simulate the auto_increment/SERIAL columns. Should we take care of this or rely on db_next_id function ? (if a core dev have a look at this thread ;) )
- he uses the datatypes I was talking on my previous post, I just didn't knew the EMPTY_CLOB() and problems aroung it (will have a look at the oracle doc).
- he puts prefixes before each column and table name. IMHO I think we should avoid this and only postfix UID columns and ACCESS table like UID_ORA / ACCESS_ORA. Any comments ?
- functions are created in a package but the creation of public synonym may give a conflict.

I would start to elaborate a schema later.

A very little update, I currently only started to create the DB schema for tables begining by 'a' to have a look at Oracle XE (of 15% of Drupal tables :) ).
I have a script for creation and deletion.
It's pretty tough as I make all this by hand checking each column and conditions, but I'm commited to conclude this.
BTW I came to a good observation, it's possible to create object using ACCESS and UID reserved words :), you just have to put them between "" for creation and for SELECT/INSERT...
Example :

CREATE TABLE "access" ( 
  "uid"  NUMBER (2) ) ; 
INSERT INTO "access" ("uid") VALUES (1);
SELECT a."uid" FROM "access" a;
----------------------------------------
uid
---
1

So, it would be very easy to fixe the queries from drupal just adding "" when we used a reserved keyword (a little regex should do the trick).

Well, as I told previously, I would be pretty busy, but I keep going on this work.

This is strictly a guess in the dark, since i've been pretty swamped this week, but i am figuring we can add code to db_rewrite_sql() to find occurance of
restricted columns/fields, and quote them as needed.

This shoudl allow us to be pretty much transparent to the rest of the Drupal codebase.

Well db_rewrite_sql() isn't called a lot (only when usefull if I remember fine), so it won't suit there.
In fact a new function like db_escape_sql() should suit, but as it's only for Oracle, I doubt this could be included in core... Let's work it a little when I start the DB layer.
FYI, DB schema is 56% done ;)

I just would like to mention that oracle has the same greatest function as postgres. (You need to have an oracle web account to follow this link.) So you no need to change this code!

Here we go ;)
Ok I ended translating the DB schema (well almost... just need a little time to check out how to deal with the RAND function that should be hidden in DBMS packages and with the "IF" func) and it seemed to work pretty fine.

Now yesterday I manage to make my first connection to Oracle using a very ugly hacked oracle include. So after a lot of errors, I finally get the front page working disabling a lot of things (all the users, session, perm and cache stuff to avoid dealing with blob and reserved words for the moments).

So firsts impressions, I think we can accomplish it but...
There's always some "but" lurking...
I had to use PHP5 because if we stick to PHP functions, PHP4 lack the oci_fetch_object and oci_fetch_array that Drupal's DB layer use intensivelly.
As I tried to get something working quickly, I decided to make the switch, what do you think about that ?

I would work a little more on the abstraction layer and when I get something acceptable, I would share it through my sandbox.

While everyone knows that I am not too fond of *cough* PHP 5, I find this acceptable. If you want Oracle, then use PHP5. It's extermely unlikely that you'll run in a shared environment w/ Oracle where you need to use what the hoster provides,

You can use ocifetchinto() which will give you array, and convert to object if needed.

Thanks for the tip about ocifetchinto I didn't spent a lot of time in php doc as I was wishing to came out with something quick. But I think a PHP4 support should be brought a little later.

Now, let's share my last discoveries :
- the "sanitize" method for avoiding Oracle reserved words seems to works pretty fine but I need a more robust function using regexp, I have to look at this.
Regexp should be something like replace all these words (in an array) in my string if they're complete (I mean replace "access" and not "access_log") and if they don't belong to a SQL string (don't replace 'comment'). If a regexp expert comes here, if not I would have to investigate a little, that's good for me ;)

- I'm using the CLOB (Character Large Object) datatype for having text like columns. The problem is that it returns an object when I make $result->my_clob (my_clob is the col name which Datatype is CLOB). I just figure out (reading about ocifetchinto) that there was an OCI_RETURN_LOBS (return the value of a LOB instead of the descriptor) attribute that should make the job, I would have a look at it. If not, rebuilding the object $result should be easy to take car of this behaviour.
In fact I already do this in order to get all columns names in lower case as Oracle returns upper cases. I read a comment on PHP doc telling that columns created with "" are case insensitive, I didn't test it for the moment, I will (in fact I already have some column with "" in order to avoid reserved words).

- I faced an odd thing when building the DB schema. We have this schema for menu and later this insert

CREATE TABLE menu (
  mid int(10) unsigned NOT NULL default '0',
  pid int(10) unsigned NOT NULL default '0',
  path varchar(255) NOT NULL default '',
  title varchar(255) NOT NULL default '',
  description varchar(255) NOT NULL default '',
  weight tinyint(4) NOT NULL default '0',
  type int(2) unsigned NOT NULL default '0',
  PRIMARY KEY (mid)
) TYPE=MyISAM;

INSERT INTO menu VALUES (2, 0, '', 'Primary links', '', 0, 115);

And MySQL doesn't complain. So what's wrong with him (or maybe me :D). How can I add a 3 digits value in a 2 digits column ?
I even try to make an "INSERT INTO `menu` VALUES ('40', '0', '', '', '', '0', '1000000000000');" and it doesn't compain anymore, it stored a '4294967295' value in the type column and nothing more !!!
So am I wrong or is there something I missed about MySQL magic ?
Whatever, the problem now is that I'm have a big doubt if I can rely on the schema I created using MySQL's one. It's likely than drupal over passed SQL limit defined by schema, so I MySQL doesn't whine, Oracle will.
Any idea ?

MySQL ignores alot of errors and perhforms silent type conversions. This breaks ANSI SQL rules, but is backwards compatible. I think the behaviour can be turned off with a connection setting.

Well the "only" drawback of such magic is that my schema is now to be rebuilt :( and adopt column with no size as Postgre schema has...

type int(2) unsigned NOT NULL default '0',
[...]
And MySQL doesn't complain. So what's wrong with him (or maybe me :D). How can I add a 3 digits value in a 2 digits column ?

Both ;)
It's not a 2 digits column. int(2) is the same as int(10) or just int.

It's not a 2 digits column. int(2) is the same as int(10) or just int.

So why do we put precision if we don't use it there ?

Sorry for the noob questions, but I mainly developp with Oracle and it complains about such things, discovering MySQL doesn't surprise me a little ;)

Just a very little update.
I've done my first regexp today : [[:space:]\.]uid[[:space:],]
This is to filter Oracle reserved Words, they have to begin by a space or a point (like u.uid) and end by a space or a coma (like "u.uid," or access, ...)
If I made a mistake, feel free to comment it ;)

Yesterday I tried to dig a little more in this and notice some very bothering behaviours about CLOB columns (I think this would be the major pain about this schema)...

1/ CLOB columns doesn't support DISTINCT clauses...
From Oracle doc :

You cannot specify a LOB column in a SELECT... DISTINCT or SELECT...
UNIQUE statement or in a join. However, you can specify a LOB attribute of an
object type column in a SELECT... DISTINCT statement or in a query that uses
the UNION or MINUS set operator if the column's object type has a MAP or
ORDER function defined on it.

So that's a pretty annoying behaviour as I'm trying to display correctly the first Drupal page and it comes with this query (user.module):
$result = db_query('SELECT DISTINCT(p.perm) FROM {role} r INNER JOIN {permission} p ON p.rid = r.rid INNER JOIN {users_roles} ur ON ur.rid = r.rid WHERE ur.uid = %d', $account->uid);
I don't know yet how to overcome this pretty big limitation, and also if there's more in the code...
I could eventually use a VARCHAR2 datatype but it's limited to 4000 characters. I think there won't have troubles using such size for permissions (personaly, I don't like the way perms are handled, but that's another discussion ;) )...

2/ I had (yes I manage to solve it) some strange behaviour with oci_fetch_array to get the CLOB value, but with "OCI_ASSOC + OCI_RETURN_LOBS" it now returns me an array with the correct keys, so no more problem here :)

That's all for the moment ;)

Sorry if this is a dumb question.

How interchangeable are Oracle XE (10g) and Oracle 9i?

I am not the dba, I'm the company Drupal guy. We run a 9i server. I understand that I can test in the XE 10g version, but will that translate to success in Oracle 9i?

Put another way, what versions of Oracle are supported by this project? If there is a dependency on v10, we may not be interested at all, since I doubt we want to upgrade.

Again, sorry if this takes the discussion a step backwards. Thanks in advance.

- Ken

There's no dumb question ;)

How interchangeable are Oracle XE (10g) and Oracle 9i?

I'm developping this mainly at home, that's why I decided to use the XE 10g version. But, the goal is to be fully compatible with 9i and up.
I choose 9i because it's the first that have a full support of ANSI JOINs (INNER, LEFT...) , and also 10g doesn't modify much the way SQL is handled (at a query level of course, there's a bunch of new optimizations to run faster I think ;) )
Also we use an 9i DB in my company, so I would be able to test it there to unsure compatibility.

For the moment, the only restriction I put to begin this project, was to use PHP5 in order to get advantages of the new oci function (oci_fetch_array) that are available. But as mention Cvbge, I think I can get a PHP4 working modifying the DB layer.
There's still a lot of work to do, but it's progressing, I would let you know through this issue how I'm going.

Great, thanks. I may try to install XE to do some testing (and get some Oracle experience).

Our core developer (that's enterprise core, not Drupal core) was just at OSCMS and we talked to Dries about Oracle support. If our company decides to go forward with Drupal it is almost assured that we will throw resources at Oracle 9i (ANSI compliant) development. We have a wealth of legacy data in Oracle, and we'd like to have a single db environment if possible.

Love the work you've done so far, btw. Will use this issue to track progress and (hopefully) contribute.

A litlle update today :

function _db_sql_reserved_words($query) {
  $sql_reserved_word = Array('access', 'comment', 'session', 'uid');
  foreach ($sql_reserved_word as $word) {
    $query = eregi_replace('([[:space:]\.\(,])'.$word.'([[:space:],\)=!><\+-\*/]|$)','\\1"'.$word.'"\\2',$query) ;
  }
  return $query;
}

This looks better for my SQL replacement function to add quotes to Oracle reserved words.
I have to look at a lot of characters after the $word. At first I thought only to replace if my character was a space, ',' or a ')' as normally, all queries have to separate column from operation signs (example : ur.uid = 0 and not ur.uid=0) but as this may happens, I add this security.
Any comment ?

Ok I refactor my db_fetch_array and db_fetch_object function to get them working and returning lowercases and I get my first Drupal page without error/warning ;)
So I would work on this a little more, expect a pre-alpha release soon. I still have some function that I think won't be included in this release (or maybe I delayed it a little to see how to solve this).
They are :
- db_affected_rows : I don't have any clue at this moment how to get this back from Oracle as there is no oci_affected_rows function.
- db_decode_blob/db_encode_blob : I didn't see if there's some treatment to apply to BLOB, I guess, there isn't.
- db_error : oci_error seems to works strangely, I would investigate this a little more.
- db_escape_string : there's no such function for oci so I think I would begin by a simple quote replacement.
- db_lock_table/db_unlock_table : That's easy with LOCK TABLE/COMMIT clause.
- db_next_id : will be similar to PgSql function.
- db_num_rows : oci_num_rows will do the job.
- db_query_range : there's no LIMIT clause in Oracle, but I think we could handle this using a subquery and ROWNUM.
- db_query_temporary : I have to investigate a little how to manage this, but temporary tables exists, so it's a matter of working a little on it.

So a lot of functions are still missing, but at least I can handle the first page for the moment.

More news latter.

Once again I had to face a problem...

You can't update a LOB with a value bigger than 4000 characters and this limit is reach very fast with cache_set for example.
So I get my drupal test working but without this feature.

To solve this, I need to use OCI_LOB package and that mean binding LOB column with php variable, see oci_bind_by_name. In theory, this is not difficult to make with a normal php script, BUT, we're dealing with Drupal DB layer and it splits functions so making a simple bind is not so easy at first sight.
So I'm stuck with this for the moment, I would work on it a little more this weekend.

On another hand, there's some improvements ;)
- db_affected_rows : this is done by oci_num_rows
- db_num_rows : oci_num_rows only works if all rows has been fetched in SELECT statement and this is a major problem... It means I first have to fetch all rows to know how many I have in the query and then re-execute this to get the cursor back on first record.
This is very annoying because I'll had to run query twice when using this function :(
And it's worst when I make query to get the nextvalue of a sequence.
For exemple, we have this

function db_result($result, $row = 0) {
  if ($result && mysql_num_rows($result) > $row) {
    return mysql_result($result, $row);
  }
}

function db_next_id($name) {
  $name = db_prefix_tables($name);
  db_query('LOCK TABLES {sequences} WRITE');
  $id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) + 1;
  db_query("REPLACE INTO {sequences} VALUES ('%s', %d)", $name, $id);
  db_query('UNLOCK TABLES');

  return $id;
}

So when I look for a sequence value, I'll add to make the query twice and lost an ID...

- db_decode_blob/db_encode_blob : Still under investigation
- db_error : Should work fine now.
- db_lock_table/db_unlock_table : Should work fine now.
- db_query_range : A little more work to do on this.

That's all for the moment.

Sorry for the lack of update, I don't have a lot of spare time for the moment and I'm also very stuck with this oci_bind_by_name stff :(

I had to decompose the INSERT / UPDATE queries to bind variables and I don't find a satisfaying way to do this...
If someone has a clue, I need to decompose an INSERT / UPDATE query in order to get back the column names + values to check if column name is a CLOB regarding the table name...
Then I can recompose the query with the binding stuff...
I may not be so clear, but if you need more precision, feel free to ask.

http://drupal.org/node/4907

Marked this a duplicate of this issue.

A quick couple of notes from what I've read above...

-It looks like you're trying to find out the next value for a sequence, for that you can use {sequence}.nextval, and for the last dispatched value you can use {sequence}.curval. If you want to determine the ID of the last record you entered, its just a matter of selecting max(ID) with some unique (to your insert) data so you pull back the correct record (or just the max(ID) if you're inside a transaction).

-If you want a SQL query to determine the type of column, use the following query:
select data_type
from USER_TAB_COLUMNS
where table_name ='{tablename}' and column_name = '{column name}'

I havent looked at any of the code, I'll go grab whats in the CVS and play around with it later this week.

Thanks for getting into this.

- regarding the sequence, I handled this using sequences combined with a trigger to simulate auto_increment columns.

- for the type of the column, thanks for the notice, I think this would fit the trick. The only problem is that's shoul request a huge amount of query in case of doing insert/update statement, which is not a good thing... Maybe this could be stored in cache later. Btw the main problem remain how to find the columns/values from the query that drupal sent back to the db layer...

For the moment my code is a little old (4.7beta4 I think) and as I started to mess around with CLOB, it doesn't work fin for the moment... I will try to get some time to clean it to commit it to CVS.

OK, so I've gone over the Oracle Schema creation script and made sure it was up to par with the 4.7 release. I also added many notes at the top and in a few places throughout so anyone reading this will better understand why things are done in certain ways with respect to Oracle.

The Oracle schema creation script is attached. Anyone out there who wants can test it and make sure it works. Supported databases are 9i and newer. Please post any bugs or errors found. Thanks.

Good work.
Just a little observation :

--  Oracle tables MUST be prefixed with some string. Many table names used below
--    cannot be used in Oracle because they are reserved words. 

It's not true.
Yes table may contain reserved word (access for example), that's why it's between double quotes and it works as is, without any prefix ;)

Yea I just realized that after I uploaded that. I had that in there from an earlier attempt at getting around keywords without using quotes (because with quotes the table/field name becomes case sensitive).

Just as an update, I'd thought I'd note that I'm still working and progressing a little. However I am experiencing difficultly in writing the regular expressions to divide up the insert and update statements.

The basic process will be to tear down every insert/update that passes through _db_query, break it into parts (column list, value list), check each column for data type, replacing with bind variables as needed, and then finally reassembling the query and sending to the DB.

Any help with the regexps will be greatly appreciated.

Well my first cut at _db_query and the other functions are attached (based on previous work). Standard disclaimers about the first cut of code apply: it could be slow, in need of bugfixes and refactoring would't hurt either. I dont get any complile or runtime errors so far, however I can only get as far as..

1. Run DB Creation script.
2. Run index.php the first time...

Before I get an "Access Denied" error (Drupal generated, not HTTP 403). The next time I try to run index.php, PHP brings down my apache instance (PHP 4.4.2, Apache 2.0.55), and I have to restart apache (so fair warning to those out there: try this on a personal development server).

I'm done for today, I'm going to try and figure out why I'm getting access denied errors tomorrow.

I also had apache down due to this kind of script.
It was due to a lot of query to update cache table (check it out to see what have been inserted into).
I didn't took the time to investigate it.

Impressive regexp and bind stuff to handle CLOB :o
Congrats

Oracle DB Schema creation script compatible with v 4.7.2.

I got Apache to stop crashing. I think PHP was choking on malformed data that was being serialize() and unserialize() and thats whats causing the crash.

So to fix that bug, I found that the _db_reserved_words() function was occasionally replacing words that were in the data portion of the SQL Statement. The column/value list would look like... data='...foo/access/bar...' and the function would change it and return data='...foo/"access"/bar...' even though that shouldnt be changed.

So I had to go in and while I'm breaking down the Insert/Update statement, process the table name and column names seperately, and leave the data untouched. Now to find out why it isnt prompting me to create the first user and create entries.

I got a big chunk working today.

Most of todays work involved reworking db_num_rows(). Here are my notes (from the source code):

NOTE: ocirowcount doesn't return the number of lines selected in a SELECT query. It cannot be used here. Further, there is no way to implement this function cleanly with an Oracle DB. Oracle stores its query results on the server side, therefore the client does not have to download the entire result set when the query is executed. However, since the client can not see the entire result set, it cant (from within the client only) determine how many rows were selected. Unfortunately, the cleanest possible way I could figure out was to have two result resources for SELECT statements, one for retrieving data and the other for counting rows. The alternate resource is set above in _db_query.

Since ocirowcount works properly for insert, update and delete statements, there is no need for multiple executions.

Once I got that out of the way, I was able to find some problems/issues with some modules. I dont know whether or not I need to file seperate issues or not, so I'll start by posting them here.

Comment Module:
line 740:
- $query .= ' GROUP BY c.cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, u.picture, c.homepage, u.uid, u.name, u.picture, u.data, c.score, c.users, c.thread, c.status';
+ //$query .= ' GROUP BY c.cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, u.picture, c.homepage, u.uid, u.name, u.picture, u.data, c.score, c.users, c.thread, c.status';

This is due to the fact that Oracle cannot group by LOBs. This would need to be implemented some other way.

Watchdog Module:
line 111:
- $sql = "SELECT w.*, u.name, u.uid FROM {watchdog} w INNER JOIN {users} u ON w.uid = u.uid";
+ $sql = "SELECT w.*, u.name, u.uid as u_uid FROM {watchdog} w INNER JOIN {users} u ON w.uid = u.uid";

This is due to there being a UID column in both Watchdog and Users. This is probably invalid SQL, but for some reason MySQL and PGSQL dont have problems with it.

Node Module:
line 1126:
- $result = pager_query('SELECT n.*, u.name, u.uid FROM {node} n '. $filter['join'] .' INNER JOIN {users} u ON n.uid = u.uid '. $filter['where'] .' ORDER BY n.changed DESC', 50, 0, NULL, $filter['args']);
+ $result = pager_query('SELECT n.*, u.name, u.uid as u_uid FROM {node} n '. $filter['join'] .' INNER JOIN {users} u ON n.uid = u.uid '. $filter['where'] .' ORDER BY n.changed DESC', 50, 0, NULL, $filter['args']);

This is due to there being a UID column in both Node and Users. This is probably invalid SQL, but for some reason MySQL and PGSQL dont have problems with it.

I still have one other bug yet to be squashed, for some reason, if you set your time zone to something other than GMT, it wont work right. I know where the code is erroring out - format_date() in common.inc, I just dont know why I'm getting negative timestamps from the calling function.

I think I might have joined the party a little late to save you guys a bit of pain but here goes anyway. I don't mean to steal your thunder but I am putting the finishing touches to performance testing my own effort at porting Core Drupal 4.7 under ORACLE 9i and PHP 4.4.2 and it's all looking pretty sweet. I opted for 9i and PHP 4 since a) I am yet to install 10g and b) the intention is that this work will carry over to Oracle 10g and PHP 5 with zero effort (wishful thinking perhaps). All going well, it should be complete by the end of this week (just in time for the World Cup!)

If you guys are interested, I have posted up a few scribblings on my web site (see profile) which you might find useful. At the very least, you may get advance warning of some issue or other that's lurking behind the bushes and waiting to get you.

I am hoping to make the code available for download not long from now, so if anyone wishes to help out with some testing, benchmarking, being a second pair of eyes and so on, feel free to drop me a line.

I would certainly have a look at your jobs.
Good luck

Where were you two weeks ago? =^)

I'll be happy to look at your code. I'm not too keen on big changes to the Core of Drupal (this is my first contribution to an OSS project), but I do agree that there are some wonky queries in the DB code. Plus I also found out some other issues this morning like queries trying to use = on CLOBs (which wont work, you need to use the DBMS_LOB package instead). Its like we have to put the entire query through a huge translation routine to get it to run in oracle.. Good Grief.

Its like we have to put the entire query through a huge translation routine to get it to run in oracle..

Yes, that's pretty much the substance of the problem at hand. You can try hard to be as platform-neutral as possible with respect to database interaction but when you're doing non-trivial things, the reality is not dissimilar to the situation with XHTML and the various web browsers out there.

The major issue, which has been highlighted already, is having to massage some of the core queries into a form which ORACLE can digest. One could opt to make the changes permanent in-core or translate them on-the-fly. I reluctantly opted for the latter approach (seemingly the lesser of two evils) with the major benefit being that integration with the core Drupal code is as pain-free as possible for a site administrator. This convenience does come at the cost of some CPU cycles, though. The ideal solution would be different but this is a practical solution, not an ideal one.

If the goal was solely to try to complete this with minimal (if not zero) changes to core Drupal. I don't think I've done too badly in this respect - just a single core function needed modification and this was completely unavoidable owing to the way one needs to interact with the ORACLE platform. It could also be argued that this function (db_query) should be pushed out of the platform-neutral category of Drupal's database functions and into the platform-specific category (i.e., make it reside inside database.mysql.inc, database.pgsql.inc etc). Other than that, the solution did not involve any necessary core modifications.

However, I'm rather more interested in the database side of things and am currently looking into how much more juice I can squeeze out of the db. Drupal/ORACLE 9i fares very respectably in the response time stakes when compared to Drupal/MySQL 4.1. It's early days yet and there's still more benchmarking and stress testing to be done but I think what I am currently seeing is quite respectable. I expect to have something uploaded to the site for you to look at and maybe even play with if inclined to do so (under PHP 4.4.2+/OCI8/Drupal 4.7.2) by tomorrow.

Still, I have to say this project could have been a lot more painful than it actually was. Take a quick look under the covers of some of the other popular CMS offerings to see what I mean.

Although I stoped developping this db layer, I'm very interested in your work Archimedes, especially when you told "Still, I have to say this project could have been a /lot/ more painful than it actually was".

In fact, I quit this project, a little for the time I didn't had anymore and then for the difficulties found dealing with oracle special features. I was very desperate when I understand the need to break down the query, analyse it to deal with CLOB... Also the lack of ocinumrows support didn't help...

But if you guys found a good way to solve this, congratulations ;)

That is a very interesting idea to have db_query moved out into the vendor specific DB implementation. I had always framed my work on the Oracle port with the idea that when the next major release comes around (5.0) we could effect substantial change in the DB implementation. We dont need to throw it out and start over, but we could cherry pick some small things that would result in a lot simpler code when it comes to other DB platforms. I wondered if we cant look at things like removing the five Oracle keywords from use in Drupal, rearranging some of the queries (the group by/distinct issue mentioned above is one of them), cleaning up queries for DB versions no longer supported, etc. I dont know who heads Drupal DB development but it sure would be nice to work so the Oracle DB port would eventually become simpler. It doesnt need to be as easy as the MySQL implementation, but given my experiences the past two weeks

Also its really neat you are working on the performance aspect. I hadn't given that much thought since the site I'm porting for has extremely low usage (under 1,000 hits per day). It was important, but not enough for me to spend tons of time on it. Anyways, I am eager to see your implementation (mainly because my boss kinda wants it done already, though at this point one more day wont matter, especially since I'm still working on the LDAP auth and how to structure the site in terms of teams).

@Tostinni
Reading some of the earlier posts, it's easy to see how using ORACLE can make what in theory is a simple task into something much less simple. You fix one problem and you're faced with another. I took a very different approach compared to what you described and it looks like it gets the job done reasonably well. Remember that I was developing under PHP 4. Maybe PHP 5 makes things more difficult? Anyway, you are very welcome to visit my site and take a look at what I have done so far.

@SirOmega
I think you're right about the direction of the D/B implementation because it's clear that the current scheme did not lend itself very well to coping with the various differences between one platform and another, hence the hackery involved with query rewriting. ORACLE, is a prime candidate for highlighting these things, being such a colossus of a DBMS in terms of features and subtleties.

Anyhow, ditto the last sentence for Tostinni. We have one foot on the first rung on the ladder now. There's still PHP 5 and ORACLE 10g to play with at some stage. Something tells me there's more fun in store but I'm looking forward to it!

Siromega, Archimedes or others. You guys have spent months on this discussion. it would be great if someone could post a brief summary of what is known about this Oracle support project idea, so we non technical types can understand just what the near and longer term prospects are for it. Is it in fact possible? Posting it in the forums under Databases would be great too. A bit more visibility might even encourage more to get involved. Thanks for all the efforts!

At this point, Archimedes has (had? I dont see it anymore) posted initial code on his website to get Drupal working with Oracle. I found less bugs in it that I did in my code, so I used his. I haven't had any problems so far. It would be nice if it was submitted to the CVS and became a part of the project (I'm sure they might want to talk about it too since it is big deal to offer a new database to support).

Any status up dates on this thread?

Not really, I stopped development when I got Archimedes code and its been working just fine on the base install of Drupal 4.7.2. I had problems with several extensions, but I really think if we want to get full-on Oracle support, we're going to have to go and have the database portion of Drupal rewritten and abstracted in a different way (seperating table names, column names, parameters, etc) and have each DB implementation work around any issues they have. Its unfortunate but with all the caveats Oracle has when it comes to their implementation its hard to port such a large and extensible application over.

Can anyone post the current schema and code for Oracle. I really would like to help we are a full oracle shop and would like to run drupal on oracle for a client. Im sure that we will be able to generate good feed back and provide fixes if any.

Hello,

I am new to drupal but need oracle module for it, because the project requires it. Is it possible someone to get links to Archimedes files, please?

Thank you in advance.

Ok, I try to run drupal with Oracla DB, when I put wrong parameters, drupal reports error..when I put correct login info for the DB - it says - Page cannot be displayed.

Any ideas?

Hello all. Regarding my earlier posts, I'd just like to point out that what I made available back in June 2006 was strictly for the purpose of evaluation and feedback and was not ready for production use, as mentioned in the accompanying document. We have moved on a bit since then (see later) but I would like to prevent peoples' expectations from sky-rocketing.

No doubt if you've taken the time to go through details of this thread, you'll be aware of the issue of a small but significant percentage of Drupal's SQL queries being incompatible with ORACLE's digestive system. I think I have also hinted that I dislike the approach I've taken of massaging queries at run-time. Nevertheless, it does the trick but it is no silver bullet: it really is an interim solution until such time as when module-to-database interaction does not require this kind of approach. What is also true is that this query-massaging method doesn't stop at the boundaries of Core Drupal: it certainly applies to many contributed Drupal modules. Be aware, therefore, that my efforts thus far cover only the core Drupal modules (minus blogapi, Drupal, ping and the legacy modules) but there is a mechanism present for embracing contributed modules and making them ORACLE-compatible too.

Ok. With the caveats out of the way, the good news is that I've been putting this thing to work and it's behaving itself very well. I have since managed to get Core Drupal to work under the following configurations:
- PHP4 and ORACLE 9i
- PHP5 and ORACLE 9i
- PHP4 and ORACLE 10g
- PHP5 and ORACLE 10g

In the meantime, I have unfortunately been burdened with the task of finding a decent web hosting provider. I am [still] on the hunt for a reliable one from which to host my site. When that hurdle is out of the way, hopefully within the next week or so, I shall be in a position to publish once more. If anyone can personally recommend a reliable (preferably UK-based) web hosting provider please use the contact tab to get in touch.

@Megalex,
If you are interested in providing feedback, suggestions, etc. I'd be very interested in getting some. Any feedback at all would be gratefully received. I shall set up a feedback page for this purpose.

The same invitation also goes out to anyone else who managed to pick up a copy of the files before my site took a torpedo in the stern.

Please check my Drupal Personal information page over the coming weeks for the URL to my web site. I will update it once my site is once again available.

Thanks.

Hello again,

I managed to get to the home page of Drupal under oracle with the existing files, attached above, but after several page refreshes, Drupal says: "ORA-00020: maximum number of processes (%s) exceeded ".
Has anyone experienced such a problem and how has he managed to solve it?

Thank you in advance!

Svet

I have a problem with the following query:

SELECT * FROM (SELECT sub.*, rownum AS line FROM (SELECT w.*, u.name, u."uid" FROM watchdog w INNER JOIN users u ON w."uid" = u."uid" ORDER BY w.wid DESC) sub) WHERE line BETWEEN 1 AND 50

The error given is:

ORA-00918: column ambiguously defined

I looked around and found that everyone suggests the above structure, but in our case it does not work :(

Any ideas?

Using rownum for paging could be a serious performance problem when the quantity of data grow.
I've had some terrible experiences with this technique in the past.
It would be better to use this function
int oci_fetch_all ( resource statement, array &output [, int skip [, int maxrows [, int flags]]] )
with the optional parameters skip and maxrows to limiting the rows returned.

I have a problem with the following query:

SELECT * FROM (SELECT sub.*, rownum AS line FROM (SELECT w.*, u.name, u."uid" FROM watchdog w INNER JOIN users u ON w."uid" = u."uid" ORDER BY w.wid DESC) sub) WHERE line BETWEEN 1 AND 50

The error given is:
ORA-00918: column ambiguously defined

Hi,
Siromega spoke about this error, and I had also noticed it. The problem is that using *, it defines ambiguous columns because uid exists both in watchdog and users table and Oracle doesn't like it.

To solve the problem, normally, giving an alias as siromega did, or removing the u."uid" column should make the trick.

Good luck

Yes, I removed the u."uid", I noticed that I do not need it for now..thank:)

In fact you didn't need it AT ALL :)
I don't understand the purpose of this column here as there isn't an OUTER JOIN query.

I guess we do not need u."uid" in the node.module either..I removed it and Drupal works just fine :)

Hello again,

I have stucked for 2 days on this problem:

"SELECT COUNT(cid) FROM {comments} WHERE pid = %d AND nid = %d AND subject = '%s' AND comment = '%s'"

The problem is that searching in CLOB field is not an easy thing. I tried to parse the SQL like it is done in the INSERT and UPDATE sections, but here we can have ">", "<" and "!=" for example, which make it more difficult.
Any ideas are appreciated.

Svet

Hello. Please open another ticket for this issue, because we are going to have to do a search a replace for searching on CLOB fields. E.g.:

select u.user_uid, u.login_text, p.profile_name_text
  from profile p, user u
 where u.acct_closed_ind = 0
   and u.user_uid = p.user_uid
   and (
dbms_lob.instr(clob_field, 'first string to find',1,1) > 0 or
        dbms_lob.instr(clob_field, 'second string to find',1,1) > 0
);

Note the use of the dbms_lob function. If you go to oracle.com, read up on the dbms_lob functions, these are the functions that must be used when manipulating a lob field.

Link to DMBS_LOB documentation (This is the 8i version, there is an updated 10g version, however we will only be using basic function in Drupal, so this documentation should be ok. Someone else can post the 10g link.)

http://www.cs.umbc.edu/help/oracle8/server.815/a68001/dbms_lob.htm

I notices following omission in the RegEx for the INSERT INTO statement:

OLD:
$re = "/^insert into ([a-z0-9_\"]+)(?:\s*\((.*)\))? values \((.*)\)$/Dsi";
UPDATED:
$re = "/^insert into ([a-z0-9_\"]+)(?:\s*\((.*)\))? values\s+ \((.*)\)$/Dsi";

The old RegEx leads to the following error in the block.module:
ociexecute() [function.ociexecute]: ORA-00928: missing SELECT keyword in C:\Program Files\xampp\htdocs\drupal\includes\database.oracle.inc on line 230.

Cheers,
Svet

Svet,

thanks. I'll add this to my patch.

Souvent22

When I activated search module, I tried to search for any string and the following error occured:

ORA-14459: missing GLOBAL keyword

The SQL query is:

CREATE TEMPORARY TABLE temp_search_sids SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS matches FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n."uid" = u."uid" WHERE n.status = 1 AND (i.word = 'asdsadasd') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1

Anyone has an idea what the problem is?

Thanks
Svet

Svet,

Yes. When you create temporary tables in Oracle, you must use the "GLOBAL" key word (well, we want to anyway). This make Oracle handle the deletation and maintinanace of the temp spaces, etc.

So, we need to have a "rewrite" clause that rewrites all "CREATE TEMPORARY TABLE" to
"CREATE GLOBAL TEMPORARY TABLE"....we also need to append " ON COMMIT PRESERVE ROWS" to the end. Example:

From:

CREATE TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
);

To:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

I'm not too good at reg-ex's, but i'll try and get this in soon. i need to put this in my sandbox too.

Sorry,

that's supposed to be " ON DELETE PRESERVE ROWS"

UGH, I mean:

that's supposed to be " ON COMMIT PRESERVE ROWS"

Thank you, I figured this out :)
Can anybody tell me how to check if a table exists or not? I was looking around and I found nothing :(

How to tell if a table exists in Oracle:

select table_name from user_tables where table_name = 'TABLENAMEHERE';

user_tables is for the current user, if you ever wanted to look for a table name and you didnt care who the owner was, just use all_tables.

Ok, I almost got the db_query_temporary function working with Drupal. The problem now is that Oracle keeps temporary table and after second execution of db_query_temporary, Oracle complains that table already exists. Do you think that it will be a problem for Drupal if I check if the table exists and if so, to drop it before creating it again?

Another issue is when make statement:

CREATE GLOBAL TEMPORARY TABLE temp_search_sids AS SELECT ....

does not insert rows in temporary table...any ideas?

Thanks
Svet

Svet,

Hm. I'll have to look into that. I would think that the temp table would only be open for that connection/session. However, I'm thinking that with the oci driver, Oracle keeps the "session" open on it's end (connection pooling), and thus thinks the "session" is still active, and may use that session on the next connection. But I'm not sure.

Souvent22

I mean that Drupal uses CREATE TEMPORARY TABLE temp_search_sids SELECT .... and this statement inserts into the temporary table. I tried with different DB tools to execute this query in Oracle and it does not insert the rows :(

Sorry,
I am new to this project.
I want to do something for this project,
but I do not have the latest patch file.
I can only get the files from other threads,
and result in error.
Could you place recent verion here?
thank you.

Here you go the files I am using:

The temp tables should be defined as...

CREATE GLOBAL TEMPORARY TABLE tablename
ON COMMIT PRESERVE ROWS
AS
Select ....;

Thanks..

row 673 in node.module:

$ranking[] = '%d * POW(2, (GREATEST(n.created, n.changed, c.last_comment_timestamp) - %d) * 6.43e-8)';
POW should become POWER

Cheers
Svet

Sorry,
I want to ask,
is any version available here?
As I can only error message after patching,
I'm afraid if my patching is wrong.
I am pleased to do modification base on it.

Mike,

I cannot understand you quite well? What do you need exactly?

Svet

After patching the drupal cvs version,
I have seen several line of error messages only,
I am afraid if I have patch it or config it wrongly.
I want to ensure the error msg isn't due to
my mis-configuration.

If you tell us the errors messages, we can probably help you. Most of the problems are already discussed in this thread. If you have anything different, we will try to solve the problems.

At this moment:

Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: ORA-00942: table or view does not exist in C:\html\drupal-cvs\includes\database.oracle.inc on line 519
SELECT data, created, headers, expire FROM cache_page WHERE cid = 'http://localhost/drupal-cvs/'

I don't know where does the table/view cache_page come from.
Thank you

Mike,

are you running drupal head or cvs? Currently, it is only ported for 4.7, but I have a head version, but I have not release it to the sandbox yet.

I'm using drupal cvs version,
as the database.oracle_3.inc provided before seems for cvs version.

Now I change back to use 4.7.3 for test,
using the .inc from svetbg.
study ORA-24374 in create content.
Thank you for your help

For the LOB handling,
I want some advise to do so.
I have only considered two ways.

One is modify the .inc,
check if LOB column exists in query,
modify the query with dbms_lob functions.
Another one is just go to every module,
modify the query directly.

If my ways aren't suitable,
could anybody give some hints to me?
thank you.

Mike,

I would say handle i the .inc file. I believe currently it does attempt to do this. It checks to see if you are using LOB fields, and modifies the query accordingly.

Souvent22 sandbox version,
database.oracle.sql seems have mistake.

Line 835:

- chCLOB VARCHAR2(128),
+ chtext VARCHAR2(128),

The chtext is the table for poll.module.
Without modifying poll.module,
the column name should be chtext.

Mike,

There is a code in the database.oracle.inc that manages LOB fields. If you have found errors, please tell us.

Svet

SELECT f.fid, f.title, f.description, f.image, MAX(i.timestamp) AS last FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title, f.description, f.image ORDER BY last DESC, f.title

Any suggestions for this query? (aggregator.module)

I have used this one:

SELECT f1.fid, f1.title, f2.description, f2.image, f1.last FROM (SELECT f.fid, f.title, MAX(i.timestamp) AS last FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title ) f1, aggregator_feed f2 WHERE f2.fid = f1.fid ORDER BY f1.last DESC, f1.title

Originally created by Archimedes.
It works with MySQL too. Do you think it is good idea this query to be put instead the old one in the Drupal core?

Svet

See http://drupal.org/node/83556 . The image can just be taken out. It upsets Oracle. It should upset any DB for that matter...you asking the DB to group on a BLOB.

I try to make the views module can be used in Drupal 4.7 with Oracle,
I found that there is some problem handling CLOB data INSERT and SELECT.
To avoid SQL injection, all single quote (') are replaced to 2 single quotes (''),
then stored in database.
So, the single quote will be shown in data selection,
e.g. Jim's World becomes Jim''s World
This is serious in views because it store a complete SQL statement in CLOB field.
I want to ask if this bug fixed, or I just got a outdated version of database.oracle.inc here?
The version I am using is updated in 15th Nov 2006 from Souvent22's sendbox.
Thank you.

Mike.

I want to ask where cursors are created?
I found that when I install several modules and create some menu items
Maximum open cursors exceed error exists.
one of key modules is path.module,
it seems the module execute too many SELECT query,
it search the urlalias for each menu item,
about 300 SELECT queries is used for the admin/menu page.
After searching solution,
it seems that the open cursors limit is about 300.
however , I cannot find the source to solve the problem.
Hope somebody could give a hand

Warning: ociexecute() [function.ociexecute]: ORA-01000: maximum open cursors exceeded in database.oracle.inc

From Oracle Doc:

ORA-01000 maximum open cursors exceeded
Cause: A host language program attempted to open too many cursors. The
initialization parameter OPEN_CURSORS determines the maximum number of
cursors per user.
Action: Modify the program to use fewer cursors. If this error occurs often,
shut down Oracle, increase the value of OPEN_CURSORS, and then restart
Oracle.

As I think it's not possible to reduce the number of queries used by path module, you should investigate 2 things:
- increase the limit of opened cursors in Oracle config file
- investigate if cursors aren't closed in the oracle db layer and what could be done to avoid this, because even if you increase the number of cursors, you'll reach the same problem when the number of users will also increase.

This is support.

For maximum cursor,
I found the resultset of SELECT queries are store in a global array,
so cursor cannot be free.
The main usage for the array of resultset is having row-count,
there is no other functions using it.
So I modify the storage and make the global array to store row-count value,
not the resultset,
the maximum cursor error not exist anymore.

By the way,
I have developed a Drupal 4.7.4 on Oracle base on Souvent22's and svetbg's coding,
includes some modules such as views, views_bonus, panels, cck ...
I want more friends to use and test it,
how could I do?

Oracle has this on their site.

http://www.oracle.com/technology/pub/articles/pedros-drupal.html

my partner and i just submit a complete patch/package for oracle supporting in drupal-6.x-dev (http://drupal.org/node/136649#comment-249207). as we have already skip the chance in drupal-5.x, i really hope oracle supporting can goes into drupal-6.x.

the problem of trigger creation, w/o DB prefix and .install supporting is already solved by my partner. the patched package is now able to function as like as normal drupal-6.x-dev under oracle DB backend. we are now seeking for tester, and hope this is able to merge into drupal-6.x-dev before code frozen on 2007-06-01.

if someone interested in this topic and able to test/debug, please contact me. and i will try my best to setup a testbed (debian etch + oracle 10gR2 + ZendCore 2 + drupal-6.x-dev) for it if required ;-)

complete oracle patched drupal-6.x-dev package

http://edin.no-ip.com/html/files/drupal-6.x-dev-0517-souvent22-2007-05-2...

oracle patche for drupal-6.x-dev

http://edin.no-ip.com/html/files/drupal-6.x-dev-0517-with-oracle-2.0.patch

original drupal-6.x-dev tarball on 2007-05-17

http://edin.no-ip.com/html/files/drupal-6.x-dev-2007-05-17.tar.gz

I don't know how this will affect your work, but Barry Jaspan's Schema patch for Drupal 6 just got committed.

He has started documenting the changes, which are largely about installing database tables. Since Schema is the new database installation layer, it would seem that Oracle installation needs to be tested against the new standard.

See http://drupal.org/node/146843

You may also want to track what's happening on the Developer mailing list. http://drupal.org/mailing-lists

wow thanks a lot! this is what i really needed for: will DB schema API become standard within drupal-6.x, or not. as it is now going to be standard, i will ask my team partner to follow the change on time ;-)

BTW, as they also stay that:

The Schema API does not change the way that current install and update hooks function and track schema version numbers. However, once hook_schema() is defined, a typical module's .install file becomes a lot simpler:

i think we can start the testing and debug now, and add the DDL feature afterward. DDL just benefit in development and installation, but will not affect the DML works, e.g. core functions and modules work flow.

i think the most important part of DB supporting is not DDL, but DML and other logical handling. this is what database.xxx.inc mainly target for, and also required for most of the debug time :(

here is a stay alone patch for patching 2006-05-17 drupal-6.x-dev tarball. we will soon follow the new DB schema API and add those feature into here.

Use preg instead of ereg. Supporting Schema is very important because without it every DB update would need to be written for Oracle, too. All, in all, I would like to see this in D6.

will DB schema API become standard within drupal-6.x, or not

Yes, it will. The Schema API will replace the current switch() cases that handle different database SQL used in module.install files.

thanks a lot, my team already get that :)

on the other hand, now we face 2 problem:

1. create index for primary key: as oracle don't allow for this case, we will need to take some special handling. this can be handle within oracle driver. planning to take some extra checking during db_add_index(), and don't create it if it is already primary key; some extra checking will also preform during db_drop_index(), so if it is not exists, we will skip it ;)
2. maximum 30 characters limitation for naming: we are trying to add a new hook within db_query() (see http://drupal.org/node/147434) for long-to-short name mapping; on the other hand, we will feather more handle short-to-long mapping within db_fetch_object(), db_fetch_array() and db_result(). normal developer are not expected to touch the masked short form name

we will submit a patch, which can correctly install without changing *.schema within days, but we will also need your help about testing and patch commit. if someone are interesting about oracle supporting among drupal-6.x-dev, please help about the commit of this new hook: http://drupal.org/node/147434, thanks ;)

this patch include:

1. support of drupal-6.x-dev installation method: DB checking, creation
2. no change to *.schema. oracle driver is now able to function with new schema API
3. no create index for primary key. checking is already preformed
4. support "core of core" modules. Block, Filter, Node, System, User and Watchdog are all supported and function

this patch not include:

1. oracle maximum 30 characters limitation. need commit of related patch (http://drupal.org/node/147692)
2. complete testing about "optional core modules". will preform after this patch is committed

some other minor bugs will soon be submit as other patch, e.g. watchdog's include some SQL which "GROUP BY" some text:big field, which should not suggest to do so. most of them are not oracle friendly, and able to handle in other ways.

after submit of oracle driver v4.0, i try to split most of modules-related change into numbers of patch and submit them:

1. node.module (http://drupal.org/node/147880): committed
2. aggretator.module (http://drupal.org/node/147860): committed
3. dblog.module (http://drupal.org/node/147761): committed
4. database.inc (http://drupal.org/node/147692): committed
5. user.module (http://drupal.org/node/147886): on progress
6. block.module (http://drupal.org/node/147865): on progress
7. menu.inc (http://drupal.org/node/147873): broken, need more work
8. check if text:big fields are required (http://drupal.org/node/147947): need more work to confirm which fields are not required as text:big

oracle driver v5.0 is build from latest HEAD, and will split into 2 part: core and additional. core patch include:

1. all progress of committed patch
2. code clean up
3. etc...

it is tested with all required and optional core modules, most of them pass the installation, except:

1. book.module: insert too large value to menu_router.description (255). crash the system and make other install not available. patch will submit afterward if required
2. menu.module: failed to install without warning, and checkbox left un-ticked after install. we have not much idea about this right now

additional patch contain some oracle-required patch in current status, include patch not yet commit and broken patch. ONLY apply additional patch if you will use oracle for development and testing.

BTW, core patch is ready for review and commit. feather more debug and feature enhancement are planed to be complete with RC state ;-)

additional patch for oracle driver v5.0

version 5.2 contain the following update:

1. make use of db_query, add _db_optimize_sql() as wrapper function for further more feature add
2. handle the mapping of != '' with IS NOT NULL. original hack with menu.inc according to this topic will no longer required: http://drupal.org/node/147873
3. crazily code clean up ;D

we still miss some exceptional case handling and feature add:

1. table locked during user logout, so user not able to logout: http://drupal.org/node/147886. need to check why driver not perform COMMIT correctly. it is not a critical bug.
2. oracle don't reload default value from DB during UPDATE: http://drupal.org/node/147865. need to do some more research about this. it is not a critical bug.
3. a mapping table/method for overcome naming limitation in maximum 30 characters under oracle
4. performance boost by caching optimized SQL query

this patch include some additional handling to block.module and user.module. as stayed above, they are not critical patch, but just used to ensure the functionality of current oracle driver. i have test it with mysql, and it is function as like as current CVS HEAD ;D

i have some log about how to install oracle 10gR2 with ZendCore for oracle under debian:
http://edin.no-ip.com/html/?q=oracle_10g_apache2_zend_core_oracle_debian....

i will soon update it for latest version of software combination. if you are able to, and interested in testing such new field of drupal, please refer to above HOWTO, or even contact me directly. i will try my best to provide any help about installation as i can ;)

version 5.3 contain the following update:

1. rewrite db_unlock_tables() as db_query() independence: use OCICommit($active_db), so db_unlock_tables() can use in whatever location
2. rewrite most DML related function: db_unlock_tables() will call on every function return. so all transaction will forced to be committed
3. a bit code clean up :D

as this 2 patch: http://drupal.org/node/147873, http://drupal.org/node/147865, should be a normal bug fix but not oracle driver related, this version of patch will not include them.

this patch only contain the required change to drupal-6.x-dev core, and completely tested by mike and i. it pass all normal installation procedure, functioning with all required core modules, and partly checked with optional modules (refer to #109). it is now able to tread as independence patch, and ready to be review or commit ;D

just update my oracle installation log: http://edin.no-ip.com/html/?q=oracle_database_10g_release_2_apache2_2_ze...

it will now cover Debian etch, Apache2.2, Oracle Database 10g Release 2 and Zend Core for Oracle v.2 (PHP Version 5.2.1). following this guideline can let you install oracle with no pain: the only pain is if you have enough physical memory (1GB recommended), swap space (2GB recommended) and HD space (8GB recommended) for playing Drupal with Oracle :D

on the other hand, i will soon summarize the installation instruction for drupal-6.x-dev with Oracle, including all required steps (with screenshots). hope this can help more people to testing it ;)

a complete instruction about how to install drupal-6.x-dev on oracle with no pain: http://edin.no-ip.com/html/?q=drupal_6_x_dev_oracle_database_10g_release...

it cover the required configuration of both apache and oracle, how to obtain and patch drupal-6.x-dev with latest oracle driver, and complete instruction for drupal installation with oracle, with required screenshots :D

if you are interesting in running drupal with oracle, it is now the chance for you to get help about this project ;)

version 5.4 including crazily code clean up, including:

1. rewrite connection handling. it is now support custom connection charset, other than system default (defined in $NLS_LANG). use the naming format as DB_NAME/CONN_CHARSET for database name within drupal installer. oracle driver will use CONN_CHARSET as connection charset, if you hope to connect your database AL32UTF8 with unicode, name the database name as AL32UTF8/AL32UTF8. refer to here for more detail information
   

   BECAREFUL! the concept of database and username is completely different between MySQL and Oracle. for oracle you will always have SINGLE database under SINGLE database instance (while MySQL have numbers of database within single instance); on the other hand, oracle will provide different "working area" for different user, so each user can create their own table without sharing with others (similar as MySQL "database" conecpt). as $NLS_LANG is used for single database instance, and will share among different users which under it, it is usually not a problem if you have only one database instance. but if you have more than one database instance with different default charset, this option will be very useful for you :D

2. rewrite some functions, include:
   

   db_connect()
   db_fetch_object()
   db_fetch_array()
   db_query_temporary()
   db_create_table_sql()
   _db_create_sequence_sql()
   _db_create_trigger_sql()
   _db_create_field_sql()
   

3. rename some internal helper function
4. a lot of others coding format correction :(

TODO:

1. a mapping table/method for overcome naming limitation in maximum 30 characters under oracle
2. a query caching engine. drupal oracle driver required to do a lot of different rewrite to target query for compatibility concern, e.g. reserved word handling, change != '' as IS NOT NULL, rewrite SELECT for CLOB handling, etc. it is meaningless to phase the same query each time. i would like to create a cache engine for it for performance boost
3. rewrite _db_query(). it is a huge function with 487 lines. it should be able to split into different functions, and merge with query cache engine

starting from this version, no more additional dependence patches are required (at least not yet discover). simply following this guideline and you will get a complete version of drupal-6.x-dev running with oracle :D

a lot of change for version 6.0, mainly code clean up and programming logic update:

1. rewrite the use of most OCI8 function into PHP5 standard. some function are aliased for backward compatible concern, e.g. ocicommit() is alias of oci_commit(), ocifetchinto() is now split into 4 function: oci_fetch_array(), oci_fetch_object(), oci_fetch_assoc() and oci_fetch_row()
2. remove db_next_id(), and add db_last_insert_id(). we follow the latest change of drupal DB API
3. add _db_constraint_exists(). this function will check if constraint exists in target table. it is useful for the change of db_add_index()
4. remove the use of global variables $_index_list and $_seq_trg_list. after review, we update the programming logic and so they are no longer required
5. rewrite some functions, include:
   

   db_fetch_object()
   db_fetch_array()
   db_result()
   db_create_table_sql()
   _db_create_field_sql()
   db_add_field()
   db_add_index()

6. again, a lot of code clean up :)

TODO:

1. a mapping table/method for overcome naming limitation in maximum 30 characters under oracle
2. a query caching engine. drupal oracle driver required to do a lot of different rewrite to target query for compatibility concern, e.g. reserved word handling, change != '' as IS NOT NULL, rewrite SELECT for CLOB handling, etc. it is meaningless to phase the same query each time. i would like to create a cache engine for it for performance boost
3. rewrite _db_query(). it is a huge function with 487 lines. it should be able to split into different functions, and merge with query cache engine

starting from this version, drupal-6.x-dev oracle driver will only support PHP5 or above. it is tested with normal core installation, and all required modules are install successfully. simply following this guideline and you will get a complete version of drupal-6.x-dev running with oracle :D

http://edin.no-ip.com/html/files/drupal-6.x-dev-200706090339.tar.gz
http://edin.no-ip.com/html/files/drupal-6.x-dev-with-oracle-6.0.patch
http://edin.no-ip.com/html/files/drupal-6.x-dev-with-oracle-6.0.tar.gz

Wow, hswong3i - great work!

See also http://drupal.org/node/83584
You oracle guys should try to somehow merge your efforts ...

the true story is: my team's oracle driver is originally coming from Souvent22 works, with some feature add. i have contact Souvent22 some days ago, and he also feel comfortable to help my side :)

actually, we are already working in cooperate mode :)

this oracle driver face a critical logical bug: it replace all %s, %d, %f, etc before doing regex rewrite of SQL statement, for oracle friendly. this cause a "out of memory" error when working with a huge size of SQL statement, e.g. when saving the result of menu cache, the regex rewrite if working on a SQL statement with 110,000 characters, and so out of memory. so you will always face a "write page" error when saving theme/module configuration.

the only solution that i can explore is: keep the original SQL in variable format during all regex rewrite action, and hide all variable up to the last moment, just before running oci_execute(). this can greatly decrease the memory requirement, on the other hand, more logical in programming.

BTW, this change will cause other major problems:

1. need to review all programming logic for _db_query(), as it is original handling an expended SQL statement.
2. a great problem for CLOB handling. oracle CLOB need to be handle in at least 3 steps: 1) bind a new variable for CLOB storage; 2) save your information into this binded variable (just seems it as C/C++ variable pointer); 3) save the CLOB pointer into table. as you see that we must first figure out which column must handle as CLOB, grep its value out for special preprocessing, before running our target SQL statement. this will need to be handle during regex rewrite, so it is not easy to split the rewrite/query in a clear format.
3. it is not easy to cover all different cases according to SQL syntax. may be we can handle some simple query rewrite (SELECT/INSERT/UPDATE/DELETE), but that is not easy if we will try to running with "INSERT INTO ... SELECT ... FROM ... WHERE" or nested "SELECT" statement. BTW, if we can say that "the driver will be accepted, if it can handle all drupal core modules SQL statement", then this will not be a great problem :)

conclusion: we figure out the solution, but it is not easy to complete before drupal-6.1-dev code freeze (1/7). if someone are able to give me a handle, please contact me for feather more discussion :)

hswong3i...

I tried your packaged version of Drupal with oracle and it does not work when you use SIDs.

The problem is on line 38 of install.oracle.inc

The $connection_string for this should just be the SID name and not contain forward slashes etc. This is when using tnsnames.ora .

latest progress result, patched with latest CVS. completely rewrite, remove handy SQL rewrite handling.

pre-requirement: improved LOBs handling (http://drupal.org/node/147947#comment-273196)

REMARK: pass all core modules installation, administrator user setup, and reach first login screen. BTW, face "white screen" error after page refresh. seems mainly due to cache handling. need more work about this :(

finally a more complete version of oracle driver for drupal-6.x-dev. version 7.0 comes with following changes:

1. completely rewrite query handling. for all version before v7.0, we try to split, rewrite and rebuild the incoming query into oracle friendly. BTW, we found that is totally crazy, as we can never handle all SQL combinations, unless we build a complete SQL parser and rebuilder. this version try to split the LOBs handling (please refer to http://drupal.org/node/147947#comment-276468) away, which is the most difficult part of oracle driver implementation, and use helper function to handle it. no more hard core query rewrite is existing, and so can support for almost all cases of incoming SQL
2. code cleanup for most function. dummy code, e.g. for overcoming "max30" problem, are review and re-implement in better way
3. make use of new data type "CLOB", which keep data size flexibility. so text:big will keep its feature as like as other text type. on the other hand, by using CLOB we can handle user input which larger than 4000 characters, e.g. most node body input
4. tested with most core modules. for core modules, all installation is passed; for modules with updated CLOB handling, both install and functional test are passed; for optional core modules, all install test are passed
5. according to removal of handy query rewrite, performance are crazily boost
6. again, according to removal of handy query rewrite, "out of memory" problem is also solved

TODO:

1. still handle "max30" problem by a simple text trimming, which means we will still need to build the mapping and caching system for a complete solution. BTW, it is now safe for all core modules
2. some more performance boosting. maybe by query caching and remapping?

pre-requirement: improved LOBs handling(http://drupal.org/node/147947#comment-276468)

if you are interesting in drupal + oracle, it is now the time for you to join the test :)

demo site:
http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev/

username: user
password: user123

error on http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev/?q=user/2 :

    * notice: Undefined index: user_profile_category in /home/hswong3i/project/drupal/drupal-6.x-dev-with-oracle-7.0/includes/form.inc on line 1085.
    * notice: Undefined index: user_profile_item in /home/hswong3i/project/drupal/drupal-6.x-dev-with-oracle-7.0/includes/form.inc on line 1085.

currently, i use same code base and setup 2 demo sites for mysql and oracle, with similar settings. both come with username "user" with password "user123":

• mysql: http://edin.no-ip.com/~hswong3i/drupal-6.x-dev/
• oracle: http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev/

i found the similar error message (http://drupal.org/node/39260#comment-276659) on mysql version. as both version are CLOB-patched (http://drupal.org/node/147947#comment-276468), should this be CLOB problem, or actually oracle driver's problem?

also setup a non-patched cvs version, and face the same error message:

• oracle + CLOB patch: http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev/
• mysql + CLOB patch: http://edin.no-ip.com/~hswong3i/drupal-6.x-dev/
• mysql without patch: http://edin.no-ip.com/~hswong3i/drupal-6.x-cvs

i guess it should not be the problem of CLOB patch, nor problem of oracle driver patch :)

code enhancement version, version 7.1 include:

1. add "unsigned" supporting for DB schema API. this will be handled by oracle inline constraint checking
2. review and update _db_update_lob(). now _db_update_lob() will handle query as like as _db_query(), which support variable_get('dev_query', 0) and db_num_rows() (as we are now handling LOBs update as like as normal SELECT query)
3. minor code cleanup for error message handling

demo site (username: user, password: user123):

1. oracle + CLOB patch: http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev/
2. mysql + CLOB patch: http://edin.no-ip.com/~hswong3i/drupal-6.x-dev/
3. mysql without patch: http://edin.no-ip.com/~hswong3i/drupal-6.x-cvs/

pre-requirement: improved LOBs handling(http://drupal.org/node/147947#comment-276847)

minor update, with better documentation. version 7.2 come with:

1. add "rand()" function support, but no "if()" supporting. i take http://drupal.org/node/39260#comment-101732 as reference
2. will only rewrite reserved word and "max30" for LOWER case phases. please refer to http://drupal.org/node/2497 for drupal's query coding standard
3. fix minor bug for variable_get('dev_query', 0) handling within _db_update_lob(). add back the setup code for it
4. add INSTALL.oracle.txt for oracle installation instruction. copy from old D5 implementation with minor update

demo site (username: user, password: user123):

1. oracle + CLOB patch: http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev/
2. mysql + CLOB patch: http://edin.no-ip.com/~hswong3i/drupal-6.x-dev/
3. mysql without patch: http://edin.no-ip.com/~hswong3i/drupal-6.x-cvs/

installation guideline:

1. oracle + ZendCore + Debian
2. drupal-6.x-dev + oracle

pre-requirement: improved LOBs handling (http://drupal.org/node/147947#comment-276847)

version 7.3 is an update according to LOBs patch version 1.1 (http://drupal.org/node/147947#comment-277757). update db_update_*() API with some code clean up.

"max30" mapping and reverse mapping will be handle after this release. schedule to implement within current preg_replace() for "max30" (for long-to-short mapping, and also the setup of mapping), and db_fetch_object/array() (for short-to-long reverse mapping). a new table for this mapping will be added within system.install. please feel free to give any suggestion about this idea :)

version 7.4 add long-to-short and short-to-long mapping for overing oracle maximum 30 characters limitation, of table/column/constraint naming. it is done by:

1. create a new table call "db_variable" within system.install. note that this table name will not be cached for "max30", which means that you can't apply a table prefix with more than 19 characters (it is a chicken-and-egg problem...)
2. add db_variable_init() into db_connect(), which initialize all oracle driver's variables at the beginning. db_variable_init() will not use most db_*() functions which due to dependence problem (again, a chicken-and-egg problem...)
3. update ORACLE_MAX30_REGEXP, and use preg_replace_callback for rewrite "max30" from long-to-short. the callback function _db_query_callback_oracle_max30() will fetch the saved mapping, or create a new mapping if it is not currently exists. this will be done by calling db_variable_get() and db_variable_set() (they are written in raw mode, due to the similar reason as above...)
4. finally, do a short-to-long mapping within db_fetch_object() and db_fetch_array()

with the above handling, mapping will be hidden from normal developers: they just need to use table/column/constraint name with larger than 30 characters as normal, and oracle driver will do the translation at the backend. this also means table/column name created by CCK or Views can simply inject into oracle DB without any special hacking (since these naming are combining by table name + column name + etc, which usually over 30 characters...)

up to this point, most functions are completely implemented within oracle driver for drupal-6.x-dev. the coming on version update will mainly focus on performance boosting and code clean up.

a new set of demo sites, which all based on 2007/07/21 00:11 CVS (username: user, password: user123):

• patch free (mysqli): http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev-cvs/
• with LOBs patch (mysqli): http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev-clob/
• with LOBs patch + oracle driver (oracle): http://edin.no-ip.com:8080/~hswong3i/drupal-6.x-dev-clob-oracle/

they are all running within same VMware instance, so please feel free to play with them :)

version 7.4 already did what we needed for, but version 7.5 do it more better :)

1. improve ORACLE_RESERVED_REGEXP. reference to http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_keywd...., add most oracle reserved words which are not included in that of ANSI for rewrite
2. improve ORACLE_MAX30_REGEXP and its callback, which greatly trim down code size, and reuse variable_get/set(). a bit funny store: we even use variable_get() within _db_query(), so can't we also use it within _db_query_callback_oracle_max30(), rather than create our own set? yes, it seems logical, but face the chicken-and-egg problem again: we will call variable_get/set() before having table "variable"... why don't we first cache all value locally, and always try to save all of them by variable_set()? we are not always facing table/column/constraint name which greater than 30, a bit handy handling trade a great improvement of code size, seems acceptable :)

BTW, the changes just try to let works become better :)

minor update, with performance boost in ORACLE_MAX30_REGEXP handling. version 7.6 include:

1. only rename phases which greater than 30 characters. previous version will also rewrite phases with 30 characters, which is meaningless
2. make use of db_table_exists(), so keep the number of query in linear proportional, or even smaller. previous version always try to save all cached values into DB, e.g. if we need to rewrite total n values within same connection, number of query will growth in form of 1+2+3+4+...+n; after tweaking, for normal case (table "variable" already exists), we will only call db_table_exists() once, and reuse this result in coming on rewrite, e.g. number of query will growth in form of 2 (db_table_exists() + variable_set()) + 1+1+1+...+1
3. can only have maximum 12 characters for table prefix, and suggested to use prefix within 10 characters. logically, we should support for maximum 22 characters table prefix, as table "variable" come with 8 characters (22 + 8 == 30, just reach the limitation of oracle). BTW, according to testing result, we are limited within 12 characters. i don't have any idea about why this happened :(

rc1 update some document when compare with MySQL/PgSQL update, and LOBs patch update:

1. detail rewrite action preformed within db_fetch_array() and db_fetch_object(), further more set default return value as FALSE. this is according to MySQL /PgSQL API document update (http://drupal.org/node/159748)
2. detail why db_update_blob() and db_update_clob() is required within oracle driver implementation. this is according to LOBs patch update (http://drupal.org/node/147947#comment-278731)

@Dries: document updated, based on http://drupal.org/node/147947#comment-278902. detail of changes: http://rafb.net/p/Xin3ch51.html.

on the other hand, since splitting out LOBs handling as individual patch, oracle abstraction layer only comes with 4 parts:

1. install.oracle.inc, database.oracle.inc, INSTALL.oracle.txt (new, required)
2. install.inc (patched, required, to enable the new oracle option during installation)

oracle abstraction layer is always ready for "dropped into" core, whenever LOBs patch is committed. no additional patch to core is required for its functionality :)

SO: Solaris 5.8
Apache/2.2.4 (Unix)
PHP/5.2.3
Drupal version: drupal-6.x-dev-with-oracle-6.0.tar.gz + drupal-6.x-dev-with-oracle-6.1-pre7.diff + drupal-6.x-dev-with-oracle-7.1.diff + drupal-6.x-dev-with-oracle-7.2.diff + drupal-6.x-dev-with-oracle-7.3.diff + drupal-6.x-dev-with-oracle-7.4.diff + drupal-6.x-dev-with-oracle-7.5.diff + drupal-6.x-dev-with-oracle-7.6.diff +drupal-6.x-dev-oracle-rc1.diff + drupal-6.x-dev-oracle-rc2.diff
Oracle 9.2 (Database name: DBINN, User: INNOV, Pwd: INNOV)

The installation process stop visualizing on the browser:"CREATE OR REPLACE FUNCTION rand RETURN FLOAT AS retval FLOAT; BEGIN SELECT dbms_random.value INTO retval FROM DUAL; RETURN retval; END;"

If necessary i am available in order to make ulterior tests.

by

first of all, as stated within INSTALL.oracle.txt, this driver is only fully tested with oracle 10gR2. since 9iR2, case may be different.

moreover, the error is due to requesting dbms_random package, which is not existed. i googled and guess this may help you:
http://www.dbasupport.com/oracle/ora9i/random_numbers.shtml

P.S. this is a problem with oracle, but not of drupal's driver ;p

the file attached is the dbmsrand.sql, coming from oracle 10gR2 (from my installation: /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmsrand.sql). hope this will be useful :)

rc3 add additional checking for DBMS_RANDOM package existent, within install.oracle.inc.

P.S. i think we can even remove "rand" implementation, since it is not ANSI compatible

P.P.S. HOWTO apply oracle patch: drupal-6.x CVS HEAD + LOB patch (latest version: http://drupal.org/node/147947) + oracle patch (latest version, find within this issue). they are not incremental patches, so you only need to apply all latest patches to drupal-6.x CVS HEAD :)

hswong3i, way to keep up with the updates! I think that this would be great addition, especially for some enterprise users. Unfortunately, looking at the amount of people replying to this issue I don't think that there are many Oracle users to test this. I assume that is wont be possible to get the patch committed without extensive testing so let's all hope that more testers rally around your efforts.

i have contact numbers of user/developer, most of them feel interesting about this progress. the reason for lack of tester is very simple: oracle is toooooo complicated, even for a flash installation, when compare with modern Win32 programs... that's why i wrote a mini-HOWTO about integrating Drupal + ZendCore + Oracle + Debian in a simple way :)

on the other hand, since LOB patch is not yet commit, people is waiting for a relatively static version before testing...

BTW, case of oracle driver is much better than before: create required DB user after system setup, get latest drupal CVS + patch, than simply follow the web installation interface, and that's all :)

minor update for rc4: update db_escape_string(), which will now trim input $text into maximum 4000 characters.

since all "text" types are mapped to VARCHAR(4000) (which is still relatively small in size when compare with MySQL, e.g. longtext => max. 4GB), it is meaningless to insert/update something larger than 4000 characters, and generate an error message. just try our best to store as much as information, based on oracle's VARCHAR2 limitation :)

according to my complete benchmarking for rc4, oracle driver show its performance as:

Oracle driver's performance is within estimation, around 200% when compare with MySQL (which also indicated my simple stress test result is not accurate enough...). this is mainly due to the duplicate queries for SELECT statement, plus numbers of query rewrite handling

i did some more study about mike's previous works, and found it is almost a missions impossible...

NOTE: ocirowcount doesn't return the number of lines selected in a SELECT query. It cannot be used here. Further, there is no way to implement this function cleanly with an Oracle DB. Oracle stores its query results on the server side, therefore the client does not have to download the entire result set when the query is executed. However, since the client can not see the entire result set, it cant (from within the client only) determine how many rows were selected.

Unfortunately, the cleanest possible way I could figure out was to have two result resources for SELECT statements, one for retrieving data and the other for counting rows. The alternate resource is set above in _db_query.

BTW, within estimation, so let it be :)

There is a critical bug.. It does not work with TNS_NAMES !! I cant even test it because it requires a local database to be present on the server. (look at my first post above)...

Made it work by changing the conn_string lines to say:
$conn_string .= $url['path'];

Now on the configure site screen i get:

* warning: oci_error() expects parameter 1 to be resource, boolean given in /websites/dev2.workzonesafety.org/includes/database.oracle.inc on line 238.
* user warning: query: in /websites/dev2.workzonesafety.org/includes/database.oracle.inc on line 239.
* warning: oci_error() expects parameter 1 to be resource, boolean given in /websites/dev2.workzonesafety.org/includes/database.oracle.inc on line 238.
* user warning: query: in /websites/dev2.workzonesafety.org/includes/database.oracle.inc on line 239.

@megalex: so you means we can only use localhost server, rather than other? i guess we are able to... :(

may you help me, too? 1. use localhost server and test the driver; 2. let's try to find out how to let both localhost and remote server be function :)

oracle driver RC5 remove db_num_rows() implementation, based on "remove db_num_rows()" patches (http://drupal.org/node/163191). the reason is simple: if db_num_rows() is a database-dependent function, which can be replaced by using SELECT COUNT(...) query or programming logic re-factor, remove db_num_rows() will be the best method for boosting up oracle driver's performance (== remove duplicate SELECT query). according to benchmarking result, RC5 show for around 14% performance improvement, when comparing with RC4.

RC5 is depending on 2 extra patches: LOBs patch (http://drupal.org/node/147947), based on functionality concern; and "remove db_num_rows()" patches (http://drupal.org/node/163191) based on performance concern. you will need to apply these 2 patch to CVS HEAD, and then oracle driver patch.

sorry, the real benchmarking result: http://edin.no-ip.com/html/?q=node/282

@megalex: i think this is not a bug of oracle driver implementation, but a problem of system setup. using ZendCore for Oracle + Apache is the simplest way that i can discover. you may have a look in my HOWTO for more detail information :)

i setup testbeds with following configuration:

1. server (.21): Apache/2.2.3 (Debian) Zend Core/2.0.1 PHP/5.2.3 + Oracle 10gR2
2. client (.22): Apache/2.2.3 (Debian) Zend Core/2.0.1 PHP/5.2.3

by using same copy of code (both drupal's source and its settings.php), i am able to connect .21's oracle server, from both .21 (by localhost or public IP) and .22 (by public IP). a localhost database server IS NOT REQUIRED for latest oracle driver implementation, remote server is also supported :)

on the other hand, as stated in mini-HOWTO, database name in DB_NAME/DB_CHARSET format is also supported, for both localhost and remote server connection. it is tested with above configuration, and it works correctly :)

minor document update: add information about $db_url formatting in /sites/default/default.settings.php, and update its document in /INSTALL.oracle.txt. it is based on rc5, and tested with latest CVS HEAD.

no special feature add in RC7, but mainly code cleanup:

1. COMPATIBILITY: remove special checking for "create index on primary key" (http://drupal.org/node/165766). it is not make sense to index a primary key field: it is already indexed
2. COMPATIBILITY: add foot notes for ORACLE_RESERVED_REGEXP, since "session" is belongs to ANSI standard, which should be handle individually (http://drupal.org/node/371)
3. COMPATIBILITY: remove $row supporting in db_result (http://drupal.org/node/166205). most database don't support for such random row fetching feature, so simply remove it, as we don't really need it
4. CLEANUP: clean up code for sequence and trigger creation, based on serial field. just review programming logic and rewrite it
5. ENHANCE: remove special handling for variable_get/set (http://drupal.org/node/165957). handle this problem within database driver will greatly reduce performance, so simply split it away, as like as cache-install.inc handling
6. ENHANCE: fully make use of variable_get/set, within db_fetch_array/object. try to reduce duplicate rewrite handling with single function call, with cached value
7. DOCUMENT: add error message for db_set_active(), which will also show oracle as supported database

pre-required patches:

1. Drupal 6.x "standardize LOBs handling for cross DB compatibility" issue: http://drupal.org/node/147947
2. Drupal 6.x "remove db_num_rows()" issue: http://drupal.org/node/163191
3. Drupal 6.x "remove $row support from db_result()" issue: http://drupal.org/node/166205
4. Drupal 6.x "make variable_get/set function during installation" issue: http://drupal.org/node/165957

minor update based on reserved words problem: now i am understanding that, it is not easy to remove all existing reserved words among drupal core schema, so i try to split the handling. add ANSI_RESERVED_REGEXP for those drupal core schema conflict with ANSI standard, and so split away from oracle's reserved word handling. maybe someday we will able to cleanup those conflict, and so we can simply remove ANSI_RESERVED_REGEXP from oracle driver :)

minor bug fix update: remove $charset parameter support in oci_connect(). so the case will be much simple: we need a correct setup of NLS_LANG, and that's all. on the other hand, also update some minor syntax bug.

patch required:
http://drupal.org/node/165957
http://drupal.org/node/147947

patch via latest CVS HEAD. minor update: change driver name from "oracle" into "oci8", so prepare for next generation PDO_OCI driver supporting with no conflict. tested with no error for all core modules installation.

patch required:
http://drupal.org/node/147947
http://drupal.org/node/165957
http://drupal.org/node/168403

As a human regression checker I tested the following functionality to work correctly on MySQL after applying the four Oracle patches:

General: Install
Action: Assign, Delete
Aggregator: Create Category, Create Feed, Update Feed, View Feed, View Sources, View (empty), View Sources (empty)
Blog: Create, Edit, Delete
Block: Enable Block, View Block
Book: Create, Edit, Delete
Comment: Create, Edit, Delete
Content translation: Add Predefined Language, Edit Predefined Language, Delete Predefined Language, Add Custom Language, Edit Custom Language, Delete Custom Language
Dblog: View Logs, Filter Logs
Forum: Create Forum, Create Topic, Create Container, Edit Forum, Edit Topic, Edit Container, Delete Forum, Delete Topic, Delete Container
Menu: Add Menu, Add Item, Edit Item, Delete Item, Disable Item, Enable Item, Edit Menu, Delete Menu
Poll: Create, Edit, Delete, Vote, Cancel Vote
Profile: Add Field, Edit Field, Delete Field, Fill-in Field, View Field
Search: Search, Advanced Search, User Search
Statistics: Add Hit, Add Content View
Story: Create, Edit, Delete
Taxonomy: Add Vocabulary, Edit Vocabulary, Delete Vocabulary, Add Term, List Term, Edit Term, Delete Term, Tag Term,
Tracker: All recent posts, My recent posts, All recent posts (empty), My recent posts (empty), Track posts, Track page visits, Track page visits (empty), Add Visit
Update: Check updates
Upload: Attach File, Delete File
User: Add User, Edit User, Delete User, View User, Add Role, Edit Role, Delete Role, Edit Permissions, Search Users

It's not an exhaustive check but it seems like the patches aren't breaking basic functionality in an obvious manner. I found a lot of E_ALL bugs in the testing process as well, woo. One of these years this'll be automated.

I went through the patch and made a number of extremely minor changes that should not have affected anything:

Documentation typos: 21
Code style errors: 105 (some of them are present in the mysql/pgsql code too though)
Language tweaks: 17

I also removed the book page TODO from db_connect() since I figured that it didn't need to be in an RTBC patch - feel free to reinsert it if this was a mistake.

subscribe. great work moving this along, everyone.

as mentioned in http://drupal.org/node/147947, this issue can seems as failed within D6 official development (yup, we fail for this once again... how pity...). BTW, this patch will not died: i will merge it together with DB2 driver (and also their related patches) into a single patch file, and keep on updating it within D6 life cycle. so people who are interested in this topic can get such supporting ON TIME, but not sit and waiting for D7 comes. i guess most of the projects needed for such unofficial patch are enterprise-related project, such little tailor-made handling may be acceptable for their professional and skillful developer :)

Latest progress result of D6 + OCI8. This patch require http://drupal.org/node/172541#comment-631799 for standardize D6 core as "more cross database compatible and friendly". Please apply that before this patch.

P.S. it is far away from perfect. E.g. we still need a lot of preg_replace during handle reserved words conflict (this can implement in somehow better if we have [] syntax for table/column/constrain naming). As the D6 core standardization already limit its change as little as possible, this OCI8 driver should able to functioning among most situation. BTW, I can't promise about its performance; you may also need some manual hack for contribute module or else they may not function. Please use this unofficial OCI8 driver with your own risk :)

Subscribe

Research for oci8 driver is almost complete. People who are interesting about the latest progress, please refer to http://drupal.org/node/172541. It is now make use of Oracle variable binding and statement prepare; also overcome the limitation about max.30 limitation, bug with name auto upper case, reserved words conflict, bug with treading empty string as NULL, etc.

BTW, as a trade-off, this unofficial D6 Oracle driver supporting require for a GIANT patch: ALL core queries need to be update with new coding standard. I have document about how to hack 3rd party contribute module in /3RD_PARTY_MODULE_HACK_HOWTO.txt. Hack is not complicated, but time consuming ;-(

I will host a project call Siren, which provide unofficial Oracle supporting for D6 core, plus some useful contribute module hack, along D6 life cycle. If you face any difficulties about hacking core or contribute module, please contact me and I will try my best for that. Hope this can help people who are asking for open source CMS + Oracle solution.

Some update information about Oracle driver development progress: oci8 driver development is now stable enough within my personal research project Siren; moreover, its portal site is now hosting with Oracle 11gR1 + Siren 1.0 dev (oci8 driver). Forum and search modules are still not supported since part of their queries are too specific for MySQL/PostgreSQL, and so too complicated for other databases. I will left them behind for D7.

On the other hand, pdo_oci development is about 70%, too. Most of the work is complete, but face critical error with duplicated transaction handling. I have no idea about this right now: isn't a problem caused by my implementation, or just because pdo_oci is not stable enough? Please checkout database.pdo_oci.inc CVS log for more information. I guess some indeed study is required ;-(

Anyway, coming with Siren 1.0 RC2, oci8 is enough stable for public test. Patch is posted in http://drupal.org/node/172541 and so I will not duplicate in here. If you would like to give a hand in pdo_oci driver development, please always obtain code from latest CVS HEAD ;-)

Hi,

How can I help you ?

I have worked with Oracle DB since 2001 since 7i version.

Best regards.

___________________________
http://www.softnew.it

@softnew: for Drupal 6.x life cycle, since we are too late, official Oracle support won't happened. If you need an on time Oracle support, please refer to my Siren project, and report bug within this issue. I will follow up and handle them. In case of Drupal 7.x, please refer to this Wiki tasklist and help its review and testing. Issues listed should be the common code base that Oracle driver needed for. So whenever we clean all of those issues, implement Oracle driver within Drupal core is just a very simple job.

Has anyone tried the package from Oracle called oradrup. Quite a bit of work has been done on this. The link is:

http://www.oracle.com/technology/pub/articles/pedros-drupal.html

The link includes source code for the integrated Oracle and Drupal.

Emery Gordon

@emergjr: sure we do know the OraDrup project, since it is the starting point of Siren. Recently, Siren already reuse most of OraDrup research progress, with further more enhancement ;-)

I need to connect a Drupal 6.x site to an oracle database to fetch a few tables of data.

How?

If it's just to fetch some datas, you should access Oracle's tables using the oci8 functions of php and creating a node/block with php to achieve this.

Is there an existing project for Drupal to interface with Oracle databases?

Really, Drupal is getting popular and it is now even used in companies that use Oracle databases.

@337 - Please keep this issue marked for 7.x as only bugfixes are being considered for 6.x. If you are wanting Oracle support in future versions of Drupal the best place focus your effort is on the new database abstraction layer that is being written for Drupal 7 (http://drupal.org/node/225450). The new database layer will use PDO (http://ca.php.net/pdo). It looks like PDO support for Oracle is still "experimental" so that may hinder any effort to add in Oracle as another database option.

changing back to 7.x

Marking postponed against #225450: Database Layer: The Next Generation.

actually changing status this time.

I am still here and studying our new generation of DB abstraction layout for D7. As I am now able to handle the integration of pdo_oci + D6, I will migrate those research progress with CVS HEAD. For legacy D6 Oracle support, please refer to my research project homepage.

As a note, the new DB layer supports contrib-based DB drivers. I believe an Oracle driver should live in contrib, not in core. Let's move work over to a contrib project, therefore.

I hope to have documentation written up for the new DB API soon, which should make it clearer how to implement new drivers. In the meantime, I suggest studying the Postgres implementation as that is closer to what the Oracle driver would look like than the MySQL driver is.

for 6.4 or 6.5 check http://drupal.org/node/310747 we have done some work..
http://sourceforge.net/projects/drupaloracle/

I just submit a new patch for solving reserved word conflict, which is the most important part of Oracle driver implementation (http://drupal.org/node/371#comment-1023578).

I do need some helper and tester, install it with MySQL, check it correctness and performance with simpletest and ab, and so on. May someone give me a hand for that, in order to speed up Oracle driver implementation for D7 officially?

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

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'

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

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:

|---------------------------------------------|
|         |     valid |     empty |      NULL |
|---------------------------------------------|
|  Oracle |   Support |        NA |   Support |
|---------------------------------------------|
| ANSI-92 |   Support |   Support |   Support |
|---------------------------------------------|

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:

|---------------------------------------------------------|
|         |     valid | "default" |     empty |      NULL |
|---------------------------------------------------------|
|  Oracle |   Support |   Support |        NA |   Support |
|---------------------------------------------------------|
| ANSI-92 |   Support |   Support |   Support |   Support |
|---------------------------------------------------------|

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
  • Use 'default' instead of '' as default actions parameters
  • Use 'default' instead of '' as default user name and password
  • Use 'default' instead of '' as undefined default language
• [DBTNG + pgsql] db_insert/db_update buggy with empty string input and BLOB field

VARCHAR2 is no large enough, use BLOB instead

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
  • Remap field as BLOB: {boxes}.body
    • [DBTNG] revamp modules/block/* with DBTNG syntax
  • Remap field as BLOB: {menu_router}.access_arguments and {menu_router}.page_arguments
    • [DBTNG] revamp menu.inc with DBTNG syntax
  • [DBTNG + BLOB + hash]: Revamp locales_source.source with BLOB and MD5 hashing
  • [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)

Auto upper case table and column name

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

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

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

Duplicate now that this project exists: http://drupal.org/project/oracle