[development] db_rewrite_sql

[Larry Garfield]

It depends how it's written and how it's used.

We actually already have semi-query builders for insert, update, and delete statements.  They're in the helpers module because they rejected from Core, since Dries doesn't like the performance implications of a query builders in core.  If there is genuine interest and a decent chance of them getting accepted to Core, I'd be happy to revive moving them to Core.  That's an IF, however. :-)  I don't want to try again if it's not going to be even considered.

For select builders, which are the complex ones, there are cases where a query builder makes the code really simple at the possible cost of SQL statement complexity/performance, and cases where it's a waste of your time and the computer's to not just write the stupid query yourself.  Views is probably the architypical case where a query builder saves gobs of time and code, and similar sorts of interfaces.  In the case of list views, it actually saves a ton of SQL because the entire data set for the page can be pulled in one query rather than 5+ queries per node on the page.  So that's a case where a query builder is a huge win for performance, both developer and computer.

There's also the question of what "query builder" means.  db_query_range() is a query builder, if you want to use a more inclusive definition.  No one objects to that in core (I hope?).  

So no, "always writing Drupal SQL" is a bad idea.  *Sometimes* writing Drupal SQL for more complex stuff, and letting it take care of database issues for you in the process, is a good idea.

To really be a win, though, you need to pair a query builder with schema introspection so it can do some of the heavy lifting for you.  That's hook_views_tables(), or the schema module work that's on-going.  It's also a case where real classes/objects/methods are good for the soul, even in Drupal. :-)

--Larry Garfield

On Mon, 23 Apr 2007 15:20:22 +0200, Wim Leers <bashratthesneaky at gmail.com> wrote:
> I think it's inevitable for a project as modular as Drupal and with
> the amount of modules Drupal has, to eventually need a built-in query
> builder, to keep things maintainable and/or to lift the project to
> "the next level". The question is though, how doable is this? And are
> enough people willing to work on it? My knowledge about query
> builders and SQL is too limited, but I imagine that you can't write
> this in 2 weeks with say, 10 developers working on it. Something like
> this needs time, a lot of time.
> 
> Not to mention we also have to somehow provide enough context
> information for other modules to hook in whenever desired.
> 
> If it's implemented extremely well, we could simply *always* write
> "Drupal SQL", and let the query builder worry about the specific SQL
> (MySQL, PostgreSQL, Oracle, etc.). That would rock! But what would
> the performance implications be? I think that a complete query
> builder in Drupal core might slow down things too much? Or something
> like the Boost module would have to get in core, to balance the
> performance again (virtually no load for anonymous visitors)?
> 
> Just some thoughts...
> 
> Wim Leers
> 
> On Apr 23, 2007, at 11:42 , jp.stacey at torchbox.com wrote:
> 
>> Hi,
>>
>>>> My opinion on that all depends on what you mean by a query builder.
>>>> :-)   Could you elucidate?
>>> along the lines of how views does it.
>>
>> I'd envisage any query builder would be more heavily tied to the
>> structure of the underlying database than views.
>>
>> Rather than adding extra node-based or categories-based options, you'd
>> explicitly add extra database tables by name, and specify quasi-SQL
>> JOIN
>> conditions and WHERE filtering. At the end of it all it could spit out
>> some fairly vanilla SQL that matched the structure you'd set up.
>> Does that
>> sound right?
>>
>> (Of course, query builders aren't great at actually optimising the
>> SQL.)
>>
>> Cheers,
>> J-P