Oracle support and O.R.M.

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
aaaristo's picture

Hi all,
i'am one of the guys who is triyng to build an oracle driver for drupal.... (http://drupal.org/project/oracle)
I actually wrote 2 drivers: 1 for the 6.x and 1 for the 7.x. Both based on PDO_OCI, and both working pretty
well with the core, (i'am still working on the 7.x SimpleTest test cases but 90% is ok).

I started using the 6.x driver in production on February (when it was on sourceforge.net as drupaloracle).

A couple of days a go, one of my company web developers, told me:

"Hey.. we should use this X module.... It is cool!"

I installed it, but unfortunately, there are some queries inside that are really far from working on Oracle.

Like this query:

SELECT q.*, COUNT(s.sqid) AS subqueues FROM {nodequeue_queue} q LEFT
JOIN {nodequeue_subqueue} s ON q.qid = s.qid WHERE q.qid IN
($placeholders) GROUP BY q.qid

The meaning of this query in MySQL is somenthing like:

       Outer join "s" table to "q" table returning all "q" columns
        toghether with the count of child rows on "s" table

(grouped by the reference key)
.. where clause..

...the same query can be expressed in oracle in this way:

select q.*, (select count(s.sqid) from {nodequeue_subqueue} s where
q.qid = s.qid) as subqueues from {nodequeue_queue} q where q.qid in
($placeholders)

..the result is exactly the same, but the concept is very different:

    select all columns from "q" table and for each row query the

"s" table to count
the child records.... where clause...

I do not see a straight way to translate the first in the second query
without using a good SQL Parser... This is one of the cases where ONE (or precisely ME)
would like to have an O.R.M. so that module developers do not have
access to SQL code.

I think is absolutely normal and "good" that module developers focus on their
module business logic, not on the "ANSIness" of their SQL.. Mostly they use
MySQL.. They create great modules... That i cannot use on Oracle, so:

1) You may think: "ok, well, use mysql, stop trying making stupid things like drupal/oracle"

2) Let's find a suitable solution.

My questions are:

1) do we really expect to be portable?

2) do we really expect module developer that have access to SQL to write ANSI-SQL?

3) What about using a complete abstraction like http://en.wikipedia.org/wiki/Object-relational_mapping or similar?

WDYT??

Thanks,
Andrea

ps: actually i end up using an "exception query translator" for the moment, so that if a module query take an exception
you have a chance to translate it in your settings.php.

Comments

No ORM

Crell's picture

A full on ORM for all SQL is simply not feasible. Drupal 7 is already too slow as is, and any fully-abstracted ORM is essentially a custom language all its own that just has to be reparsed, translated, recompiled, and then reparsed again on the SQL server. That's way more work than we can reasonably expect the server to do, as well as way more custom API knowledge than we can expect a casual Drupal developer to have or care to learn. It would also involve completely rethinking the entire way that Drupal works. :-)

If the query is a dynamic query (db_select()), you start with a parsed data structure to begin with so you can do any DB-specific compilation there.

In this case, if there is a more ANSI-compatible and cross-DB way of writing the query, submit a patch. We knew going into the new DB API that it wasn't going to solve all cross-DB issues magically, nor was it intended to. A lot of cross-DB syntax issues are still going to be handled manually simply because the only way to automate all of it is to develop our entirely own database engine that happens to use MySQL/Postgres/SQLite/whatever as a backend. We're not doing that.

Crell Here is an ORM that

asavasamuel's picture

Crell

Here is an ORM that works with Oracle
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

aaaristo's picture

Thanks Crell,
sorry for my crappy english in advance,
i was wondering about some of your sentences:

1) "A full on ORM for all SQL is simply not feasible. Drupal 7 is already too slow as is.."

I'am not really a drupal core expert but, are you sure that Drupal7 is not so slow because
you have added a new abstraction layer? And if is so, don't you think you can add a better
abstraction layer?

2) "and any fully-abstracted ORM is essentially a custom language all
its own that just has to be reparsed, translated, recompiled,
and then reparsed again on the SQL server.
That's way more work than we can reasonably expect the server to do.."

You are partially right: using ORM sql dialects is not the only way of using an ORM.
I really do not know enought about PHP's ORM internal implementations... but in other
languages ORM or similar frameworks, outperform the SQL approach in many applications
and that because of the concept of "Locality of reference" (http://en.wikipedia.org/wiki/Locality_of_reference).
Basically you are faster because your cpu can access "frequently accessed data" faster.
RDBMS have been there for a long time, they are still there because we feel them
as a confortable and reliable place where to store our data... But the music is changing:

http://couchdb.apache.org/
http://www.oracle.com/technology/products/coherence/index.html
http://labs.google.com/papers/bigtable.html
http://msdn.microsoft.com/en-us/data/cc655792.aspx
...etc

3) "as well as way more custom API knowledge than we can expect a casual Drupal developer to have or care to learn."

This is interesting... You think that a convention based object API is harder to learn than SQL?

4) "It would also involve completely rethinking the entire way that Drupal works. :-)"

You are completely right... +1 for you.

5) "If the query is a dynamic query (db_select()),
you start with a parsed data structure to begin with
so you can do any DB-specific compilation there."

Yes it is great! But only if we can force
developers to use this API only without writing SQL select statements.
To me (developer), sounds a bit like "use SQL ... but don't use it ..."
frustrating. What about if would like to use i hierarchical "connect by"
query because my beautiful SQL engine can resolve hierarchies for me?

Wouldn't be better if me (the developer) can access some fancy convention based
ORM api and stop thinking about SQL to focus only on my module business logic?

6) "In this case, if there is a more ANSI-compatible and cross-DB way of writing the query,
submit a patch."

I think that the power of Drupal is that somenthing like +2'000 modules are around
to play with.. I'am really not happy to start thinking to patch it all. I'am really happier
to find a way so that the next +2000 (i hope) are portable against SQL engines (or other
persistence providers).

7) "We knew going into the new DB API that it wasn't going to solve all cross-DB issues magically,
nor was it intended to."

So, why you wrote that?

8) "A lot of cross-DB syntax issues are still going to be handled manually
simply because the only way to automate all of it is to develop our entirely own database engine
that happens to use MySQL/Postgres/SQLite/whatever as a backend. We're not doing that."

I don't think so: simply use another abstraction layer. May be that the point here
is that PHP has not a good ORM engine and this is obviously out of the drupal project scope.

What about Doctrine?

By the way, it is pretty late here so, thanks for the patience..
I hope this post helps to avoid dropping the conversation on those concerns.

On ORMs

Crell's picture

Hi Andrea. I started writing a reply here, but in usual Crell fashion it got really really long. :-) So I've turned it into a blog post instead:

http://www.garfieldtech.com/blog/orm-vs-query-builders

Hopefully that will explain the logic behind DBTNG's design a bit better.

Need help

nicola.cuccurazzu's picture

Hi @everyone, im a new user here, im trying to install oracle support over Drupal 6.14 but after follow the installation guidelines system return an error like this one:
In your ./sites/default/settings.php file you have configured Drupal to use a oracle server, however your PHP installation currently does not support this database type.

Seems that the DataBase contains some tables schema that blocks the normal esecution.
Im trying to connect in a new Oracle instance without any user object.

Stay tuned! :D

Have you installed pdo_oci and patched the install.inc ?

aaaristo's picture

You should install pdo_oci and check if the install.inc contains the 'oracle' database...
Andrea

Great ( Grande andrea )

nicola.cuccurazzu's picture

Thanks for your support and for your fast answer. Viewing my web server configuration appears that i don't have this module ... now i request it to my IT department support and i'll try once installed.

Thanks in advance and sorry for my bad english.

Bye.

pdo_oci finally installed

nicola.cuccurazzu's picture

Hi guys,
finally my IT support department had installed pdo_oci module so i can start my test.
First of all, looking over the net i found that the syntax to use to connect into Oracle DB is similar the following:
$db_url = 'oracle://username:psw@tnsnames-entry';
$db_prefix = 'DD_';

but the error returned is:
Failed to connect to your Oracle database server. Oracle reports the following message: SQLSTATE[42S02]: pdo_oci_handle_factory: ORA-12154: TNS:could not resolve the connect identifier specified (/tmp/pear/cache/PDO_OCI-1.0/oci_driver.c:463).

I'm pretty sure that tha parameters are right ... maybe i need some abstraction layer like ADOdb o something like that?

Thanks in advance ..

Bye,
Nicola.

connection settings

aaaristo's picture

oracle://user:pwd@host:port/sid

Next installation error

nicola.cuccurazzu's picture

Thanks a lot for fast answering ...
now the probles is:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1031 OCIStmtExecute: ORA-01031: insufficient privileges ... and a lot of other errors

Looking inside the code and adding some debug backtrace ( ) ... i found this call:
oracle_query("begin identifier.check_db_prefix(?); end;",array($db_prefix));

connecting directly to my DB and trying to execute handly the statement ( begin identifier.check_db_prefix('D_'); end; ) where 'D_' is my prefix Oracle return the following error:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1031 OCIStmtExecute: ORA-01031: insufficient privileges...

Could you explain me what kind of operation, the script, is tryng to complete? I don't know wich privileges add to my user ...

I'm sorry ... i'm a newbie with drupal+oracle....

Bye,
Nicola.

read the INSTALL.oracle.txt...

aaaristo's picture

if yo want to use the $db_prefix (or use simpletest)

grant dba, create any trigger, drop any table, SELECT ANY table, SELECT ANY sequence, create user to drupal identified by drupal;

you have to grant a lot of privileges actually tothe drupal user... This is because prefixes are implemented like oracle schemas..

read this:
http://groups.drupal.org/node/24398 (section "Max 30 characters for constraint name restriction")

IT WORKS

nicola.cuccurazzu's picture

Hi,
finally my Drupal 6.14 works under Oracle DB.
Thanks a lot Andrea for your support and for your driver.

Byez,
Nicola.

Testing..

deepthivankadari's picture

Hi i am working on writing db2 driver for drupal i did with development can i know what test suites u have used for testing the driver.Is simple test suite module is correct for this to test..if so please let me know the procedure of doing..

Database

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: