Towards a Better dbDelta
When it comes to creating and updating database tables, WordPress has what appears to be a very handy utility function – dbDelta. In theory, this function can take one or more CREATE TABLE queries, compare them to the tables already in the database and automatically figure out how to bring them up to date, adding or modifying columns and indexes as necessary.
In practice, dbDelta is fragile, undocumented and prone to fail silently when something goes wrong. It can only parse queries tailored to its idiosyncratic requirements, doesn’t know how to drop obsolete columns, isn’t aware of character sets and collation, chokes on extra whitespace… Nasty, but evidently Good Enough™ for most plugins, even for WordPress itself. It’s still better than doing schema updates manually, and in most cases you can work around the limitations.
Then again, sometimes you can’t. Just a few days ago I ran into a situation that dbDelta just couldn’t handle (due to the aforementioned lack of collation awareness). This prompted me to refactor dbDelta into something more flexible. The result was the blcTableDelta class – still somewhat finicky with regards to query syntax, but much more flexible otherwise.
In the remainder of this post discuss how my version compares to the built-in dbDelta function, explain how to use it, and provide the source code.
Advantages
My version of dbDelta is by no means perfect – or, for that matter, even particularly good. That would require implementing a full-blown SQL parser, which is clearly overkill for a utility function that gets called only once or twice per install. Instead, it’s just Better Enough™. Here’s how blcTableDelta fares when compared to dbDelta :
- More lenient with whitespace – no more ridiculous requirements like “there must be exactly two spaces between the words PRIMARY KEY and the definition of your primary key”.
- Detects changes not just in column data type, but also in collation settings and NULL/NOT NULL/AUTO INCREMENT flags.
- Can optionally remove obsolete columns and indexes.
- Returns a complete query log, including execution status and any MySQL error messages.
- Doesn’t choke on “IF NOT EXISTS”.
- Has slightly better documentation.
The downside is that this class isn’t nearly as battle-tested as the native dbDelta function. It also doesn’t support INSERT queries.
Usage
Here’s a simple example of how to use the blcTableDelta class :
//Define the table structure you want to achieve. The function returns an array with two elements. The first element is a list of human-readable messages explaining what schema changes would need to be performed to update the database. The second returned value is the query log. Each item of the query log array is also an array, with up to three keys : The source code is too long to include in the post, so I’ve placed it in a separate .zip archive. Download it here. Just place the .php file found therein somewhere inside your plugin’s directory and
$sql = <<
Source Code
include it before calling blcTableDelta::delta.
Subscribe via RSS
Get Updates by Email
Thanks for posting this. I ran into the whitespace issue just today.
CREATE TABLE ‘mytablename’….
Has 2 spaces after TABLE and this caused dbDelta to fail silently when installing my test plugin. That took a while to diagnose until I spotted it by chance.
Hopefully the folks at WordPress/Automattic are taking note of this.
If your looking for something better than dbDelta you might also find good inspiration in the BackPress SQL schema parser class which is I believe evolved on from dbDelta
http://backpress.automattic.com/browser/trunk/includes/class.bp-sql-schema-parser.php
Would you be interested in submitting a patch for this? http://core.trac.wordpress.org/
@ westi: Sounds interesting, I’ll take a look at it.
@ Andrew: I’m afraid it’s still too untested to become a proper patch.
It sounds like something that will require some massaging and plenty of unit tests, but every patch starts somewhere.
Great job! This has saved me many hours of work. I’m modifying this to work with CodeIgniter so I can auto-update schema when I distribute new versions of web apps, and I found three bugs–
1) It doesn’t like comments on columns
2) It doesn’t like “CURRENT_TIMESTAMP” as a default value
3) It doesn’t like lines that are commented out– I’m using it to check a whole SQL dump of table structure against the existing schema, and your code tries to process the #’ed out lines. I fixed it for my hack, but you might want to add a quick check for commented out lines.
Thanks again for publishing this
Best,
Sam
I’ve actually written a more advanced version that can handle single and multi-line comments as well as arbitrary whitespace, but right now the code is way too messy to release publicly. Perhaps I will one day rewrite the lousy thing and make it into a proper SQL parser.
hey white,
i checked your class and would like to work a bit on it. can you send me your current version, so i can clean up the code and test a bit. you’ll get my version back when i’m done. this would help me so much in my effort to build a wp plugin framework which combines column definitions like rails mongo-ids field :name, :type => ‘string’ to generate code to migrate databasechanges via delta()
thx for the inspiration!
manuel
Here it is. Most of the meaty stuff is in naive-sql-parser.php.
I wrote a function for creating tables within WordPress functions last year. Maybe it will help some of you. It is one the biggest sources of traffic on my blog 🙂
Function for Creating Tables with WordPress Plugins:
http://shoultes.net/function-for-creating-tables-with-wordpress-plugins/
Excellent work. I know a lot of people take issue with the various requirements of dbDelta (white-space, etc.). Those kinds of things I can get around, but this class provides that “killer” feature of dropping obsolete columns. Aside from this, dbDelta has always been able to get the job done, this will be my new db delta utility of choice. Many thanks!