[development] Advanced DB features

[Ivan Sergio Borgonovo]

On Thu, 1 May 2008 09:24:44 -0400
"Ken Rickard" <agentrickard at gmail.com> wrote:

> Now, can Drupal learn from and exploit the value of stored
> procedures -- possibly, given the requirement restrictions that
> Larry mentions.  In fact, we need people with such experience to be
> active developers on the project.

In my humble experience it would be hard to write an abstraction
layer that let you write stored procedures across several DB.

Joshua mentioned Catalyst. I don't know what does he meant when he
wrote it can manage stored procedures. The best I can think is it
offer standard methods to wrap stored procedures and then you'd have
to write custom one for every DB you support.

Not all stored procedures are functions and not all return records.
I don't know if all the DB Drupal is planning to support provide DB
functions that return records but if they do... it could be
reasonable to write custom db functions that return the most
important objects in Drupal, so people will be able to write SQL
similarly to

select one.a,two.b,two.c from someobject(id) as one join
othertable as two on one.id=two.id

But this really doesn't look to belong to the DB abstraction layer
and if you put it in the object layer you risk to make it dependent
on the DB you chose that is not a good help to isolate the DB layer
and make it's API DB agnostic.

One thing I've to fight with when I've to code sp is that db_query
prepare and execute the query. I'd prefer this functions to be
separated for debug purposes. It seems that D7 is going to support
prepared statements but splitting "prepare" and "execute" in db_query
would have been nice even before.
Then to avoid a lot of boilerplate code you could wrap prepare and
execute again in db_query but people could use the
"prepare" (or cast and escape) part for their purpose independently.

I think the path that compete the DB layer would be to let developer
provide easier different functions for different DB without resorting
to checking db_type.

Stored procedures are great for encapsulation, coherency, transaction
management, access control... but I think it is hard to offer a
flexible framework for dev that is based on sp and I don't think they
are suited for Drupal audience.

I use stored procedures with Drupal. I think there is nothing missing
in Drupal that could help to use stored procedures across several DB.

You write different versions of your stored procedures in .install,
wrap them in functions, put them in different files that are loaded
according to which DB you're using and you're done.
The largest portion of the job is to write different versions of sp
for different DB, there is no shortcut.

Support for transaction may be easier, provide a more agnostic API
and be quite useful, I'd say there are several part of core where
transaction could be used proficiently to make the overall more solid.

Something that would be harder to plan would be to make transactions
extend across hooks. eg. it would be nice to make the overall process
of creating/updating a user transactional so that if a contrib module
add proprieties to a user the user never get half created.
I don't think that db that don't support transactionq will suffer
from supporting transactions at this level, they just won't offer that
level of coherency.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it