Drupal 7 Database update

Unless you've been living under a rock for the last six month, you should have already heard about Drupal 7's new-and-shiny database layer, DBTNG. That was only the beginning, though! Much has happened since then to the database, and there's much yet to do. That's where you come in...

What's been done

There have been literally dozens of additional patches committed against the DB later since it first when in, ranging from bug fixes to performance improvements to new features. Perhaps the most significant, though, is that Drupal 7 now fully supports SQLite databases. It only took four years, but Drupal 7 is now fully capable of installing and running from SQLite, and for low-bandwidth sites the speed is about on par with MySQL. Impressive!

We also brought back database query logging, but in a much more flexible way than in Drupal 6. Rather than a hard-coded variable switch in db_query(), the database layer itself now supports query logging, and maintaining multiple logs at once. That means the devel module can simply ask the database layer to start logging for it, then ask for the log when it's done. It also supports much richer meta data about where queries ran than the older system, so we can leverage it in other places than just the devel module.

The connection management has improved as well. Rather than just being able to define connections in settings.php, you can now add new connections at any time. You can also mark targets to be ignored temporarily, which is important for replication support.

On the support front, we now require MySQL 5.0 or later or Postgres 8.3 or later. Earlier versions are mostly out of support by now or will be during the Drupal 7 life cycle, so the effort required to continue supporting them in Drupal 7 (and therefore not using features from later versions) is not worth the effort. We've gone a step beyond that, however. MySQL is now forced into Strict Mode, which disables most of its silent "I think I know what you mean so I won't throw an error" nonsense.

There have also been a number of API improvements as well. Select queries in particular have gotten a fair bit of love, and now support a convenient shorthand for adding multiple fields at once as well as support for subselect queries. That should come in very handy when converting the rest of core over to DBTNG, as well as with specific oddball cases such as node_access.

And of course, we have extensive documentation for most of the API in the handbooks. It even has its own path alias! w00t!

What we still need to do

There's plenty yet to be done, however; far more than the few people focusing on the database layer can do themselves, especially when we still need to get everything past the gatekeepers of Dries and webchick. If you're looking for a place to get involved in Drupal, this is a great place to start! We desperately need people to pick up some issues and carry them home, or to review patches that are still pending and need review before they can go to the committers. If Drupal 7 shipped today, the database layer would still be incomplete. This cannot be allowed to stand! Let's make this our new year's resolution: The database layer fully complete and all of core converted by the end of January. Think we can do it? Here's how you can help:

Keystone tasks

These issues are the most important, as they are blocking further work. Much of the groundwork has already been laid, but it needs to be carried home.

• Make transactions work: Right now they don't work properly, which means they're largely useless. There's so many places Drupal can benefit from transactions, but not until this patch gets in.
• Replace db_placeholders() with a more natural syntax: This is blocking most of the work to update core to use DBTNG.
• db_rewrite_sql() must die! Please help kill it! Kill it dead! We can't let another version of Drupal ship until we've removed that nightmare; this is a must-have patch.
• Table prefixes should be per-connection, not global: Not only does this make it possible to use multiple connections and prefixing in the first place, but it opens up the possibility to do some really cool optimizations in SimpleTest.
• Add query extenders: Extenders are a weird concept, but they're the only way we have right now to support pager and tablesort queries. Oh, and maybe let Views leverage the core query builder. We desperately need reviewers over here, because we can't convert pager and tablesort queries until this gets in.
• Allow selective replication disabling: Replication can't really be used successfully until we are able to selectively disable it when needed. Let's do that.

Important tasks

These need someone who can make them happen. Have some time to spare?

• Replace update_sql() with query logger: Get rid of the evil that is update_sql() now that we have a powerful and flexible query logger in core.
• Foreign keys: Boy this is an old issue. Schema API doesn't support foreign keys. Even if we can't use them directly in core, we can certainly expose that data to systems like Views.
• Virtual fields in schema API: Speaking of Views, don't you hate having to duplicate all of your table data in hook_schema and hook_views_data()? Maybe we can't entirely get rid of the latter, but we can minimize it by adding virtual field support to Schema API.

Other important tasks

OK, these aren't necessarily earth-shattering tasks but they need to happen anyway. Some need someone to review them, others need to be written. In either case, don't just hit-and-run. Stick around and help guide the issue home.

• Bug fix to make merge queries not die if called improperly.
• Better handling of IS NULL and IS NOT NULL in built queries.
• Mark which queries in core are "slave safe": Right now, we aren't actually using our replication support at all.
• hook_query_tag_alter(): hook_form_alter() has an extra hook for each form_id. Why shouldn't hook_query_alter() have a hook for each tag? It should. Let's make it happen.
• Document hook_query_alter(): Yay for documentation! Hooks are now documented directly in core, so let's document hook_query_alter() properly.

And of course, we still have to convert the rest of core over to use the new DBTNG syntax. Grab a module and post a patch!

PostgreSQL on the ropes

Damien Tournoud is also leading a "PostgreSQL surge" to fix up Postgres. Right now Postgres doesn't work properly in Drupal 7, and there's grumbling about removing it if we can't get it to work. The alternative is to have Drupal 7 held back for Postgres support. If you don't want those to happen, check out the PostgreSQL spotlight and lend a hand.

Speaking of, I want to take a moment to thank Damien for his work on the database layer in recent weeks. He was one of the key people responsible for finally getting SQLite in, plus a number of other key patches. Thank you, Damien!

Help!

If nothing here tickles your fancy, have a look at the complete database queue. Alternatively, there's a database-specific spotlight page for tracking issues that we're trying to focus on. If you have spare time, lend us a half hour or hour every few days for a patch review or two. You'd be amazed how much one hour here and there can add up, especially now that the testing bot does so much of the grunt work for us.

Review the documentation, check the issue queue, and if all else fails ping me in IRC. I'm happy to help anyone who's trying to help out with the database layer, but I can't do it alone. We need a full database strike team. Who's with me!