So I have 2 Oracle installations running great on 4.7.3, and 4.7.4.
So I thought 5.0 would be easy to tackle....totally wrong.
I was on the phone with Oracle late tonight. I'll try and keep this rant short. But, the reason that Oracle will work with 4.7 and not 5.0 is that in 4.7, you have to run your SQL statements manually, and in 5.0 Drupal does this for you. This is a really nice feature. However, that's where the problem comes in:
Currently, the Oracle implementation use sequences and triggers to simulator auto_increment and serial fields. The problem is that you can not create a trigger from PHP, you much run a trigger DDL from Oracle's SQL+ (or if you have Java). For some reason, it's a bug in the oci8 module.
E.g. if you run this code:
CREATE OR REPLACE TRIGGER \"BOXES_BE_IN\"
BEFORE INSERT ON boxes
FOR EACH ROW
BEGIN
IF :NEW.bid IS NULL THEN
SELECT boxes_bid_seq.NEXTVAL
INTO :NEW.bid
FROM dual;
END IF;
END;
/from SQL+ (command line bascially) it will run fine. However, if you run it inside of php:
$query = "CREATE OR REPLACE TRIGGER \"BOXES_BE_IN\"
BEFORE INSERT ON boxes
FOR EACH ROW
BEGIN
IF :NEW.bid IS NULL THEN
SELECT boxes_bid_seq.NEXTVAL
INTO :NEW.bid
FROM dual;
END IF;
END;
/";
$result = OCIParse($connection, $query);
OCIExecute($result, OCI_DEFAULT);
if ($myerr = ocierror($result)) {
print_r($myerr);
}You will get an error. Some random, non-helpful message that says "success with errors". And then it gets better when you try and re-compile the trigger from the command line: "encountered "" expecting: ....". That's nice. Encountered a space between words. I guess oracle wanted Created to be CreatedByOracleTheGreatOne. Anyway, so that's the current state of Oracle in 5.0 as it will stand until I can get the urge to hack at it again (which won't be for a while at this rate).
So, I installed SQL-Server on my laptop tonight....full steam ahead on MSSQL.
Comments
kill the sequences
those auto-increment fields should be used throughout, or we should do away with our own sequences. this limbo state is bad. oracle has no notion of auto-increment fields - amazing.
why not using db_next_id?
Due to my understanding, the auto-increment will only happened on INSERT statement. As drupal DB API already provide db_next_id function for cross DB compatible, why don't we make use of it? Maybe that will be a hung work for correct the existing SQL statements, but this seems to be more acceptable and reasonable? And seems this is also the suggestion from drupal's handbook (http://drupal.org/node/1395)
Edison Wong
CEO, Co-founder
PantaRei Design Limited
Agreed
@hswong3i ,
I totally agree, however, there are some that are in disagreement. The core tables use auto_increment, serial, etc. type fields a lot. The core modules would have to be changed to use db_next_id instead, since they are relyning on the DB to create the ID for them.
Now, using auto_increment instead of an application-layer/style db_next_id field does give a performance boost; and killes has pointed out ( http://drupal.org/node/38585 ) that he would rather have the performance boost from auto_increment than switch to db_next_id just for 'standards' sake. Perhaps I'm mis-reading this view point, or it has changed.
In the mean time, perhaps the I could release the oracle driver, people would have to run part of it from the command line to create the triggers, and I shall just not that db_next_id must be used with the Oracle driver unless you want to create a sequence and trigger for each table that you want to have an auto_increment type field.
I must admit, clients that we have on a Drupal/Oracle stack are very happy with it. It just runs, and there are no issues/errors....lets hope it stays that way.
I'll be posting a 'Case Study' after I finish this MS-SQL implementation also. I love doing 'application' work. It's my speciality. I see lots of 'social community' work (which some what bores me) with Drupal, but not much in the way of 'applications'. I am hoping that with more DB support, speed enhancements, etc., that more people will start to use Drupal for true 'applications', and not just 'social community' solutions.
Souvent22
This is the answer that i am finding for
@Souvent22,
So this is the real point that why drupal still employing auto_increment within core modules - speed, which is also my consideration. BTW I think your suggestion about manually create part of the tables is a great idea, as we have did this for many years already ;-) Surly that will be much better if we can handle all table creation by .install, but world always not perfect ;(
On the other hand, my team (together with mike2854) already work out drupal on oracle for http://asia.playstation.com which is now running as production (http://drupal.org/node/100195). Anyway, we found that our current implementation for this seems not good enough during review. As we are going to start another project which have a similar background, we chose to implement drupal on oracle for drupal 5.0. Hope we can work together and so such contributions can be submitted back to drupal core as soon as we can ;-)
hswong3i
Edison Wong
CEO, Co-founder
PantaRei Design Limited
I referenced your post here
I referenced your post here http://drupal.org/node/105049 . Thanks hswong3i.