Drupal 7 Database update

Unless ye've been livin' under a rock fer th' last six month, ye should have already heard about Drupal 7's new-and-shiny database layer, DBTNG, Ya horn swogglin' scurvy cur, by Blackbeard's sword! That were bein' only th' beginnin', though, me Jolly Roger Much has happened since then t' th' database, an' thar's much yet t' do. That's where ye come in...

What's been done

There have been literally dozens o' additional patches committed against th' DB later since it first when in, rangin' from bug fixes t' performance improvements t' new features, feed the fishes Perhaps th' most significant, though, is that Drupal 7 now fully supports SQLite databases. It only took four years, but Drupal 7 is now fully capable o' installin' an' runnin' from SQLite, an' fer low-bandwidth sites th' 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, Get out of me rum! Yaaarrrrr! Rather than a hard-coded variable switch in db_query(), th' database layer itself now supports query loggin', an' maintainin' multiple logs at once. That means th' devel module can simply ask th' database layer t' start loggin' fer it, then ask fer th' log when 'tis done. It also supports much richer meta data about where queries ran than th' older system, so we can leverage it in other places than just th' devel module.

The connection management has improved as well, to be sure. Rather than just bein' able t' define connections in settin's.php, ye can now add new connections at any time. You can also mark targets t' be ignored temporarily, which is important fer replication support.

On th' support front, we now require MySQL 5.0 or later or Postgres 8.3 or later. Earlier versions be mostly out o' support by now or will be durin' th' Drupal 7 life cycle, so th' effort required t' continue supportin' them in Drupal 7 (an' therefore not usin' features from later versions) is not worth th' effort. Oho, to be sure! We've gone a step beyond that, however, we'll keel-haul ye, Ya horn swogglin' scurvy cur! MySQL is now forced into Strict Mode, which disables most o' its silent "I think I know what ye mean so I won't throw an error" nonsense.

There have also been a number o' API improvements as well. Select queries in particular have gotten a fair bit o' love, an' now support a convenient shorthand for adding multiple fields at once as well as support fer subselect queries. That should come in very handy when convertin' th' rest o' core o'er t' DBTNG, as well as with specific oddball cases such as node_access.

And o' course, we have extensive documentation fer most o' th' API in th' handbooks. It even has its own path alias! w00t!

What we still need t' do

There's plenty yet t' be done, however; far more than th' few scallywags focusin' on th' database layer can do themselves, especially when we still need t' get everythin' past th' gatekeepers o' Dries an' webchick. If ye're lookin' fer a place t' get involved in Drupal, this is a great place t' start! We desperately need scallywags t' pick up some issues an' carry them home, or t' review patches that be still pendin' an' need review before they can go t' th' committers. If Drupal 7 shipped today, th' database layer would still be incomplete. This cannot be allowed t' stan'! Ahoy! Let's make this our new year's resolution: The database layer fully complete an' all o' core converted by th' end o' January. Think we can do it? Here's how ye can help:

Keystone tasks

These issues be th' most important, as they be blockin' further work. Much o' th' groundwork has already been laid, but it needs t' be carried home.

  • Make transactions work: Right now they dern'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 blockin' most o' th' work t' update core t' use DBTNG.
  • db_rewrite_sql() must die! Please help kill it! Fire the cannons! Kill it dead! We can't let another version o' 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 t' use multiple connections an' prefixin' in th' first place, but it opens up th' possibility t' do some really cool optimizations in SimpleTest.
  • Add query extenders: Extenders be a weird concept, but they're th' only way we have right now t' support pager an' tablesort queries. Oh, an' maybe let Views leverage th' core query builder, All Hands Hoay, by Blackbeard's sword! We desperately need reviewers o'er here, because we can't convert pager an' tablesort queries until this gets in.
  • Allow selective replication disabling: Replication can't really be used successfully until we be able t' selectively disable it when needed, Ya lily livered swabbie! Let's do that.

Important tasks

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

  • Replace update_sql() with query logger: Get rid o' th' evil that is update_sql() now that we have a powerful an' flexible query logger in core.
  • Foreign keys: Boy this is an auld issue. Schema API doesn't support foreign keys. Even if we can't use them directly in core, we can certainly expose that data t' systems like Views, Avast me hearties, on a dead man's chest!
  • Virtual fields in schema API: Speakin' o' Views, dern't ye hate havin' t' duplicate all o' yer table data in hook_schema an' hook_views_data(), with a chest full of booty? Maybe we can't entirely get rid o' th' latter, but we can minimize it by addin' virtual field support t' Schema API.

Other important tasks

OK, these aren't necessarily earth-shatterin' tasks but they need t' happen anyway. Some need someone t' review them, others need t' be written. In either case, dern't just hit-an'-run. Stick aroun' an' help guide th' issue home.

And o' course, we still have t' convert th' rest o' core o'er t' use th' new DBTNG syntax, pass the grog! Grab a module an' post a patch! Ye'll be sleepin' with the fishes!

PostgreSQL on th' ropes

Damien Tournoud is also leadin' a "PostgreSQL surge" t' fix up Postgres. Right now Postgres doesn't work properly in Drupal 7, an' thar's grumblin' about removing it if we can't get it t' work. The alternative is t' have Drupal 7 held back fer Postgres support. If ye dern't want those t' happen, check out th' PostgreSQL spotlight an' lend a han'.

Speakin' o', I want t' take a moment t' thank Damien fer his work on th' database layer in recent weeks. The ornery cuss were bein' one o' th' key scallywags responsible fer finally gettin' SQLite in, plus a number o' other key patches. Thank ye, Damien!


If nothin' here tickles yer fancy, have a look at th' complete database queue. Alternatively, thar's a database-specific spotlight page fer trackin' issues that we're tryin' t' focus on. If ye have spare time, lend us a half hour or hour every few days fer a patch review or two. You'd be amazed how much one hour here an' thar can add up, especially now that th' testin' bot does so much o' th' grunt work fer us.

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


You wrotethis about

You wrotethis about SQLite:

...an' fer low-bandwidth sites th' speed is about on par with MySQL. Shiver me timbers! Impressive!...

So th' SQLite implementation isn't ready fer high traffic sites yet?

SQLite is not...

... And swab the deck, ya bilge rat! a database server. Load the cannons! Walk the plank! If yer site is mostly read only then it can handle such traffic ye would not believe. However, writes can be a problem. So 'tis more adequate t' say "SQLite is on par with MySQL until ye begin concurrently writin' th' DB".


You wrotethis about SQLite:

...an' fer low-bandwidth sites th' speed is about on par with MySQL. Impressive!...

So th' SQLite implementation isn't ready fer high traffic sites yet?