[development] Database / SQL future thoughts

[larry at garfieldtech.com]

Chris Johnson wrote:

*snip*

> Hence, the former course of using only the simplest, most primitive
> SQL statements appeals to me for Drupal.
> 
> It's that last point I'd really like to have criticized.  Is it
> possible to do that?  Especially in light of DBTNG, where is the line
> between simple statements and complex statements?

Simple vs. complex is not a binary statement anyway, but a continuum. 
In D7, some queries are more verbose than they used to be (complex 
update statements, for instance) but a lot of more horrifically complex 
queries are now quite easy.  So we've effectively flattened the curve 
and moved most DB interaction into the "Can't click and drool but you 
can do a lot more" realm.

The other advantage is that we have, at least, points at which 
DB-specific optimizations can be made.  Mostly we're only doing that for 
modifier queries (insert and merge, specifically), because that's where 
the SQL syntax differs the most.  Restructuring a SELECT statement is an 
order of magnitude harder, and as the article notes much of it has to be 
done on the DB side anyway, not the application side.  (Some queries 
simply cannot avoid filesort in MySQL; other DBs may offer workarounds.)

The other catch is that Drupal's heavily-modifiable design often 
precludes "one query to rule them all" design, as that requires knowing 
everything you'll need before hand.  With modules and hooks, you almost 
never do.  See also this great read:

http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx

Really, I see "object store" databases (CouchDB and friends) as simply 
solving a different problem space that formerly was crammed into SQL out 
of "it's the only tool available".  RDBMSs are still around, and will 
remain around, for a LOOOONG time.  Just look at a typical file system 
on your computer.  I'd bet good money it's hierarchical, despite the 
supposed advantages of "huge-data-soup-with-craploads-of-indexes" 
architecture.  However, that exact architecture ran the Palm OS for years.

The author may or may not be right about Oracle and MySQL, but either 
way I'm not worried that SQL databases will go away any time in the next 
decade.  They may have reached a maturity point and not change 
dramatically (dear god I hope so), but SQL is not going anywhere.

There may be advantages to having an "object store" general front-end 
that can then be connected to CouchDB or similar (hm, another handlers 
use case...), but that's separate from having an SQL interface in core. 
  They're just different tools for different problems.  For most of our 
problems, simple relational models are perfectly fine.

> Lastly, although many Drupal-based sites are database limited, many
> are PHP-code interpretation (# of files/lines parsed) or bandwidth
> (fast JS/image pages) limited, too.  My goal is simple:  make my
> hundred module websites respond in less than 2 seconds!  :-D

With the registry now in place we really need to start properly 
splitting up core modules to reduce code weight. :-)

--Larry Garfield