D7 Database update: Almost there

Submitted by Larry on 16 February 2008 - 10:28pm

The new database system for Drupal 7 that I've been talking about for the past few months is nearly ready for submission. With chx's visit to Chicago we were able to refactor it for far better modularity and cleanliness. As of yesterday, the system is able to navigate around Drupal, submit forms, create and edit nodes, and view the insanely heavy modules admin page. I still need to make it work with the installer, but it's looking very promising. A very recent copy of the new code base, pre-Drupal-integration, is available in my sandbox.

Here's a brief list of the features it offers:

  • Ability to connect to multiple database types at the same time.
  • Type-agnostic prepared statements. No more worrying about %s vs. %d!
  • Master/slave replication support.
  • Master/master replication support (or at least the potential for it).
  • "INSERT ... ON DUPLICATE KEY UPDATE ..." support for MySQL, with emulation on other databases.
  • The potential for database drivers to be added by contrib authors without modifying core, not even the installer.
  • A flexible, versatile query builder. (Raw db_query() is still there, though!)
  • The ability to drupal_alter queries, which means the abomination that is db_rewrite_sql() can be removed.
  • Not a single regex or global variable.

I hope to have a patch ready to test within the next few days.

A tip of the hat, a salute, and a can't wait...

It will be easy for a module to use a database separate from, and potentially a different kind, than the rest of Drupal is running on?

It's an import goal to make Drupal work with multiple databases at the same time. Unlike now, you won't need to set active database, just pass in the name of the connection to the factory function and get back a database object to work with... Easy as pie.

Outstanding bullet points! You guys have been so productive in Chicago. I am quite impressed.

I should note that some of our insert/update queries will migrate to the drupal_write_record() model so you might not bother changing them to db_insert() and friends.

Lets add // $Id$ and a @file doxygen for new files.

The new files in the real patch will have $ID and such. I didn't bother in my sandbox, as that's just testing code.

Actually, drupal_write_record() should use db_insert() internally. *All* insert/update/delete queries will need to go through the new APIs in order to support weirder databases that require special LOB/BLOB handling. I suspect, actually, that a lot of the use of drupal_write_record() will go away as there will be no need outside of the DB layer to handle field types.

ximo (not verified)

17 February 2008 - 9:08am

This looks very good! Great work, Crell & co :)

It's hard to believe that it's just around the corner! Maybe it's because I found out about this kind of late.

But I have to ask about the deprecation of %s and %d (et. al.). I'm wondering where the quotes around strings will be inserted. I know I've used %s *without* the quotes around it to choose different tables in a switch/case statement. Mostly I just want to know how much rewriting I should expect.

One of the advantages of using native prepared statements rather than emulating them ourselves, and in fact the feature that attracted me to them in the first place, is that the quoting and escaping is unnecessary. The database layer knows itself the type of the field and what handling it needs. It's more like a function call than a serialized string with all the data in it. So you'll instead say:

<?php
$result = db_query("SELECT a, b FROM {mytable} WHERE d=:thinga AND e=:thingb", array(
  ':thingb' => 5,
  ':thinga' => "Some string here",
));
?>

And the quoting/escaping will all be handled down in C code somewhere. Also note that the order of the parameters array does not have to match the order in the query if you are using named parameters.

On the other hand, formal prepared statements only support the actual value parts of the query. Using %s for the table name will no longer be possible. In the long run that's probably a good thing, as it forces more structured code. You can still build the query string dynamically, just not using %s replacement. Or you can even use the query builder instead. :-)

Hi Larry,

I just stumbled upon your thread and the changes look thrilling.

Just out of curiosity, have you guys thought of using TRIGGERs more extensively in the Drupal db strucutre? Now that they have been out for a while, I have been using theme extensively on my sites, and they are fatnastic, and of course decrease load on Apache...

Cheers,
G