[development] DB abstraction layer_S_

[Larry Garfield]

On Mon, 3 Dec 2007 18:56:41 +0100, Ivan Sergio Borgonovo <mail at webthatworks.it> wrote:

>> > I'd provide more than one very thin layer of abstraction.
>> > A higher level could be an ORM specialised on the most important
>> > objects in Drupal (nodes, users, taxonomy...).
>> > At a lower level an SQL rewriting engine that could be extended
>> > later that will support the most popular SQL functions and
>> > operations across different DB.
>> > At the lowest level a way to directly write SQL with a default not
>> > optimised route using the second level of abstraction and a
>> > specific DB route for each DB if the developers are willing to
>> > optimise, but in a more elegant way than writing switch.
> 
>> That's sort of what I'm looking at.  Layer 1 is a revised
> 
> I'm married ;)

ROFL!

>> db_query() to use PDO in a type-agnostic way.  Layer 3 is
>> node_load() and kin.  Layer 2, for insert/update/delete, are new
>> utility functions that build the query for you based on arrays.
> 
> I think we could a) get a broader picture looking at real ORM (as
> SQLAlchemy and Co. and maybe something in RoR) then narrow down what
> we generally (with a reasonable abundant generally) do and later
> write a "sql cross compiler".

I've started looking into code for ADODB, but dear god that thing is ginormous.  I am a big big fan of small but powerful code. :-)

> I did some experiments that should be similar to yours with arrays to
> live with MS SQL and pgsql and recycle Drupal api but well... I'm not
> that convinced. I mean... tons of people did it before me and I don't
> feel as smart as getting it right without looking at real
> implementations reasonably carefully.
> Another important thing is the acceptance of such a method and making
> it uniform to all the rest (schema api).
> So... some how arrays (aka meta info) are the only way to go and it
> looks to late unless we're going to change schema api as well.
> ORM in python are well pythonic. A specialised-ORM in Drupal has to
> be drupalish or there won't be acceptance and it will be hard to
> "remember".

As powerful as arrays are, they're not the end-all and be-all.  I have a feeling that the days of no-objects-in-core are numbered.  We just have to be careful not to reimplement Java. :-)

> BTW I'm working on profile info and it is a pain how forms are so
> similar to the profile array but still different. And it is not that
> fun to write a form2profile function to transform an array in another
> array. It would be very nice if you could juggle schema and query
> meta info. eg. most of the times $header for table api could be
> deduced from the field list or the opposite...
> 
>> The part I'm still trying to figure out is Layer 2 for SELECT
>> statements.  So far the best I've come up with is a very-simple-api
>> query builder (probably a fluent API) object that for complex
>> queries (complex meaning "contains a function") you *must* use.
> 
> I didn't get it.
> 
>> I'm not sure if that is an acceptable solution, however.
> 
> Any sample?

Well, I've nothing written yet.  It's still in the "simmer in the back of my head for a while" stage.  But off the cuff, I'm thinking something vaguely along the lines of:

$select = db_select();

$select->join('node', 'n')->join('user', 'u')->field('uid', 'n')->countField('nid', 'n', 'num_nids')
  ->where('type', 'n', 'page')->group('uid', 'n')->order('num_nids', 'DESC')->limit(0, 5);

$result = $select->execute();

Which in MySQL would translate to:

SELECT n.uid, count(n.nid) as num_nids
FROM node n INNER JOIN users u ON n.uid=u.uid
WHERE n.type='page' 
GROUP BY n.uid
ORDER BY num_nids DESC
LIMIT 5

A Fluent API (mutator methods return $this, so you can chain calls like in jQuery) makes the syntax much easier and shorter, and lets you specify things in any order.  It also then serves as a very easy arbitrary query builder, which (maybe maybe maybe) can then become the core of Views-partially-in-core.  Of course, SELECT statements can get extremely complex, which is why I'm wary of this method as it means we will almost certainly end up making certain edge-case queries impossible.  As I said, still percolating.

As Gabor said, though, let's focus on D6 for the time being. :-)  Contact me off-list if you want to keep up this conversation.

--Larry Garfield