GarfieldTech

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:

For those who may not have noticed it, it looks like Drupal 7 is going to require not only PHP 5.2, but MySQL 5.0 as well. It makes sense to do. Drupal 7 won't actually ship for another year, by which point MySQL 4.1 will be on life support anyway. It will also lose all support during the Drupal 7 life cycle. So if you're planning a new server, get ahead of the curve and Go MySQL 5! :-)

Drupal 7: The version that gets over the 20th century.

So, Dries wants to know what our Drupal 7 battle plans are. I think this is the first version where I'll have explicit battle plans before hand rather than just "whatever I come up with along the way". :-) So, for those playing along at home, here's my goals for Drupal 7:

• Move Drupal to PDO
• Introduce a function registry
• Begin to solve the "Data API problem"
• Whatever I come up with along the way

So it's been a week since DrupalCon, which means I'm kinda sorta caught up enough to write about it. Hooray!

As with DrupalCon Sunnyvale, I came away with one conclusion fixed in my mind: The Community is Drupal's greatest strength. Virtually everyone upholds a strong community and open source spirit, and will gladly talk to you for hours about subjects both on topic and off, with or without beer (free or otherwise).

But enough about how cool we are. On with the rundown.

There has been some discussion recently, in IRC, issue queues, and blogs, about the Drupal 7 database API and its impact on supporting different database engines. While I am still trying to avoid large amounts of public distraction, especially when we're supposed to be trying to get a Drupal 6 beta 1 out the door, I feel it's important to get a few points cleared up lest they lead to confusion later.

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.

By far the most common idiom when using SQL from a web application (PHP or otherwise) is simply listing records. The standard logic looks something like this (give or take real templating):

That's all well and good, but in practice can be quite limiting. Why? Because you can't then group records, that is, display not one but several tables, one for each color. SQL, of course, offers a GROUP BY clause. That doesn't do what we want, however. GROUP BY is an aggregate clause, and is used for creating totals and summaries of records. We want to cluster records by a field that is not the ordering field, or a value that is calculated off of the record itself.

I've generally used two different methods for PHP-side grouping, one of them much cleaner and more flexible at the cost of a little performance.

Most PHP applications do fundamentally the same thing: Shuffle data from an SQL database to a web page and back again. The details vary with the application, but in general that's what most web apps do. That very quickly runs into the bane of most PHP developers' lives: SQL syntax.

It's not SQL syntax itself that is bad per se. The problem is that it is a string-serialized format, which means you have to take your nice clean data structures and serialize them out into a string that has no semantic meaning to your PHP application. That's boring, dull, and introduces all sorts of places to totally mess up your application with a typo, and that's without even touching on issues of security. And then there are the issues with SQL syntax itself, in particular the way in which INSERT and UPDATE statements, which seem like they should be similar, have no similarity whatsoever. That makes "replace" operations (insert if new or update if not) very tedious to write, particularly if you have a lot of fields.

Fortunately, with a little ingenuity and help from PHP's array handling, we can give ourselves a common syntax for INSERT and UPDATE operations that maintains semantic meaning, and then get DELETE statements free of charge. Let's see how.