Drupal 7 database plans

I've been talking up some evil plans I have for Drupal's database system in Drupal 7 lately, without going into a great deal of detail. For the most part, I've been trying to avoid distracting people, myself included, from the considerable work still remaining on Drupal 6. However, there has been recent work and discussion of making post-freeze changes to Drupal 6's database system, and even backporting them to Drupal 5. Those changes revolve mostly around database replication, which Drupal currently doesn't support in any meaningful way. That becomes important, though, on very heavy sites like, say, I don't know, Drupal.org. :-)

Those changes, though, impact the API changes we can make in Drupal 7. (OK technically they don't, since we change APIs all the time, but if we can set things up so as to minimize API changes over time that does make life easier for everyone.) For that reason, at Dries suggestion, I'm going to try and lay out now a skeleton of what I'm planning for Drupal 7's database system and how we can start adding replication support to Drupal 6 in a way that flows into it.

Before I begin, I just want to clarify that I am in no way trying to undermine the database-related work that others have done latey, particularly Barry Jaspan with the schema system and David Strauss with numerious SQL cleanup patches that have kept drupal.org from dying completely in recent weeks. Both have done great work and my hat's off to them. My goal here is to avoid duplicate or conflicting work, not to disparage the great work that has already been done.

That said, here's the short version of where I intent to try and push Drupal 7's database API.

Step one, replace the PHP 3-era PHP APIs for databases (mysql, pgsql) with PHP 5's PDO. It should be no surprise to anyone that I'm planning to port the backend to PDO, for reasons already stated. :-) That will allow us a number of new options, which in turn has a knock-on effect to other things we can do. The main one is type-agnostic prepared statements. Drupal already emulates prepared statements in user-space and has for years, but pushing that down into C code simplifies code and lets us focus on more important things. PDO's prepared statements have two key features we care about at the moment:

• They're type-agnoistic. That is, you don't need to specify in advance if a field is an int, a string, a float, or whatever. PDO itself knows how to quote/cast things properly. That means at the PHP level you can simplify your code without a loss in security.
• They're associative-array-based. And let's face it, if you don't like associative arrays you're dealing with the wrong CMS. :-)

There's plenty of other things to like about PDO, but those are the features that are relevant to us right now.

There's one other annoying factor. Right now, all Drupal queries go through db_query*(). db_query*() takes a variable number of parameters. Variable parameter count sucks, because it makes default parameter values difficult or impossible. db_query() also takes an array of parameters, but that's not used very often.

Now, let's consider database replication. Without going into detail about how database replication works (I'll be honest, I am not an expert in the subject), in order to be able to write queries against a master server or a slave server you need to know something about the query. You need to know if it's a mutator query (INSERT, UPDATE, DELETE, CREATE, etc.) -- and if so if a delay in writing the data to disk is acceptable -- or if it's a read query (SELECT), and if so whether data that's potentially a few seconds stale is acceptable. Right now, db_query*() gives us none of that information.

So let's give ourselves that information, and simplify SQL's sometimes nasty syntax at the same time.

The API

First, we modify the $db_url variable structure. Specifically, we extend the array depth by one level so that any given database entry can include master and slave variants that list the connection strings for the master and slave servers (in PDO's connection string syntax, so we don't need to parse and repack it). Both of those can themselves then be an array, in which case one is selected at random. That gives us support for multiple slave servers as well as a way to define multiple master servers. Naturally we still have a single server degenerate case as now. The various forms for $db_url then include:

Now that we have multiple servers defined, how do we tell the system which to use? We could use a dedicated slave function, or specify a flag in the db_query*() call itself. It would be cleaner to do it in the db_query*() call itself, but there's that pesky variable parameter count problem. But, since PDO wants an array for prepared statements anyway... get rid of it. db_query() takes only an array of values, not an array or variable parameter count. We then have a function signature that looks like this:

Arguably we could assume that a query is slave-safe unless specified otherwise, but I don't know which case is more common in Drupal right now. We can sort that out later. We could also, potentially, merge db_query_range() in here as well by adding two more optional parameters, but that's a discussion for later as well.

So now we can easily flag those SELECT queries that should get run against the slave server du jour, if available. Otherwise, the query gets run against the master server, of which we could potentially have multiple. (Master-master replication configuration is way out of scope for this blog.) Next, we restrict db_query() to just SELECT statements, or possibly SELECT and schema statements. Perhaps it should even be renamed db_select().

Because the return value is a PDO result object rather than a primitive mysql result resource, and because the API is the same for all databases, we don't need db_fetch_array() and db_fetch_object() anymore. The result object has its own methods that work just fine, and even implements SPL object iterators so you can foreach() over it. All that for free. I like free.

What of INSERT, UPDATE, and DELETE queries then? Those get their own functions, too. Rather than hand-writing those out, though, we can take advantage of the fact that those query types have very simple, regular syntax. They're really just associative arrays broken out in different ways, so we can setup a function to just pass an associative array of values in and let the code do the rest. For a look at how they might work, see the db_helpers module in the helpers module package. For a lengthy explanation of why I think they're a really good thing, see the original thread and this blog comment. PDO's type-insensitive prepared statements, though, solve most of the security concerns they have. The signatures, then, looks like this:

Most of the reasoning for such syntax is in the links above, but I will note the $delay parameter which can toggle INSERT DELAYED and UPDATE LOW_PRIORITY under MySQL or the equivalents in other DBMSes. In MySQL those are only useful on MyISAM tables, but given the number of sites that run MyISAM it's a nice addition.

So we now have all of our query types split out semantically, in a PDO-friendly array syntax, with clear ways to determine the master/slave database that should be used, with type-safe prepared statements, and with meta-data useful for some database systems, in a database-agnostic way. Score. :-)

First steps

The short-term baby step in that direction, then, is slave server support. For that, we do three things.

1. Enhance $db_url's syntax to handle the above configuration for declaring slave servers.
2. Modify db_query(), db_query_range(), and pager_query() to take an extra parameter, $slave_safe, but only in the array version.
3. Convert core to use array-based db_query*() and $slave_safe=TRUE where appropriate. The non-array version still exists for now, so code that doesn't take specific advantage of $slave_safe has no change and we minimize the post-freeze API changes.

That is a bit different from the currently pending patch's implementation, but I believe it is a better long-term structure.

Concept needs feedback

That status doesn't exist on Drupal.org yet, so I'll fake it. :-) There's still some internal design I haven't nailed down yet and feedback on the API is certainly appreciated, even though major work on it won't happen until at least a Drupal 6 RC.

One of these days I will also learn to not say something will be a short explanation, because I should know by now that I never, ever give one.

In any case, let's make Drupal.org faster.