Oracle Support in Full Gear

The first problem I'm getting is in paging. Examine this query:

SELECT sub."uid" FROM(
SELECT w.*, u.name, w."uid"
FROM watchdog w INNER JOIN users u ON w."uid" = u."uid" ORDER BY w.wid DESC) sub

If we didn't use reserved words, this would/does run fine. The problem is, you get a "Abiguously Defiend Column" error. I am still looking into why. However, this query does work:

SELECT sub."uid" FROM(
SELECT w.*, u.name
FROM watchdog w INNER JOIN users u ON w."uid" = u."uid" ORDER BY w.wid DESC) sub

Notice that the reserved work is used on the outer Select. Anyone think of a good workaround?

i strongly recommend you use HEAD as your basis. The SQL files were all broken up by module. You will have smaller chunks to work with. Also, your patches will be suitable for review by our committers. They don't look at 4.7 only patches.

Great that you are doing this and MSSQL. Thanks.

Ok, it's time to start the dreaded CLOB discussion.

For a brief and high-level overview, CLOB is Oracle's equivlent to TEXT (both 4GB long).
But, CLOB has some, what they call 'minor' cavets. There's only 1 which really effects Drupal:
1. You can't have a CLOB in a SELECT DISTINCT..SELECT UNIQUE, GROUP BY, or WHERE clause..

So, this causes isses for thing like the comment module. So, anyone know of an Oracle GURU who could
give some insight as to how to progrmatically alter SQL statements so that we can use CLOB's?

Here's an example statement. c."comment" is a CLOB, this will return in error in Oracle:

Ok, so I now have Drupal running Oracle very nicely. It is definatley a nice system thus far. I did a few bench tests for run by importing 2 million nodes and items, and used ab and a script to simulate some users....and I'll post more on that later. Anyway, the reason for this post...CLOB&undefined;s. I only have Oracle running nicely after some modifications to some modules and their queries. This becomes a philisophical question; and the question is:

If you have a large string (e.g. body field), when would you ever use it in a GROUP BY clause?

This question has been plauging me for a while as I went through the core and a few other modules fixing them. For example, on a comment on this thread you can see the code in comment.module:

    SELECT sub.*, rownum AS line FROM ( SELECT c.cid as cid, c.pid, c.nid, c.subject, c."comment", c.format, c.timestamp, c.name, c.mail, c.homepage, u."uid", u.name AS registered_name, u.picture, u.data, c.score, c.users, c.thread, c.status FROM comments c INNER JOIN users u ON c."uid" = u."uid" WHERE c.nid = 1 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 ORDER BY c.thread DESC

Now, why oh why do we have a GROUP BY clause in this SQL statement? There are no aggregate functions contained in the SELECT portion, so what function does this group by have? Let us take another example, aggregator.module:

//$result = db_query('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');

  $result = $result = db_query('SELECT f.fid, f.title, f.description, 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 ORDER BY last DESC, f.title');

Now, the one commented out is the original SQL. The latter is the modified sql. I took out the f.image column because it was not used anywhere in the function aggregator_page_sources, not by any of the functions that it called inside if itself.
There are also some examples where a f., or was used, and thus inadvertantly a CLOB column was included and "GROUPED".

So, right now, it is a combination of some general "lazy" SQL (and who doesn't do lazy SQL, SELECT * :)), and Oracle's CLOB preferences that are giving the headaches for the Oracle Driver implementation.

Currently, to make the driver more "universally compatible", I and considering breaking an incoming SQL statement down if it has a GROUP BY clause, and removing and fields that are CLOBS from the SELECT and the GROUP BY clause (I would also have to fully spell out any table_alias.* fields).

So, not I am pre-plexing myself with this question:
In what situation would I be doing a grouping that include a large data set of random and for all intents and purposes, unique?

E.g. In what situation would you include the body field? I mean, it's bascially like trying to group on a raw binary image? Thoughts?

Make sure to submit patches against CVS HEAD. Oracle support in core would be great. :)

This does sound like it could have some uses. I tested this recently and am unable to loginto Drupal, though that maybe a type or bad setup. There is a sqlite port in development which is the opposite extreme, though the alternative databases are cool. Look at the postgres specific code instead as its more similar to Oracle. Have fun porting the forum module. :)

Where can we obtain the latest database.oracle.inc and database.oracle.sql. I am also trying to get full oracle support and will be able to provide some feedback.

The European Union agency for which I did Drupal work is an Oracle shop, and they would feel much more comfortable running Drupal on Oracle than on MySQL, if for no other reason than they have Oracle expertise in-house. Great work on this. Here's the Digg link to the Drupal.org front page story. Your Digg can help get it on the Digg front page... and in front of a lot more Oracle developers who may now check out Drupal for the first time:

http://digg.com/software/Finally_Drupal_runs_on_Oracle

Hi Souvent22

• cool to see somebody's porting Drupal to Oracle.
  I'm an OCP (oracle certified professional DBA) by the way,
  and VERY experienced with Oracle RDBMS + perf tuning,
  so if you come across some questions or need help with sth
• let me know and I might help!

cheers
Christoph C. Cemper

• http://www.cemper.com
• http://www.marketingfan.com

After over 1.5 years of development (since 29/11, 2005), plus number of developers contribution, and help of Drupal 6.x schema API, a new Oracle driver for Drupal 6.x is now ready for testing.

If you are interesting about this topic, please feel free to test it out, and send back your feedback, bugs, and suggestions.

Drupal 6.x LOBs patch issue: http://drupal.org/node/147947
Drupal 6.x Oracle driver issue: http://drupal.org/node/39260

What do you hope from my personal blog??