[wp-hackers] Handling upgrades to a plugin's database scheme

[Travis Snoozy]

On Tue, 30 Oct 2007 19:41:52 +0000, James Davis
<james at freecharity.org.uk> wrote:

> I've written a plugin which uses it's own database table to store
> data. I've since extended the plugin which now uses a slightly
> different schema for that table.
> 
> What's the best way to handle the change in schema when users upgrade 
> from the previous version of the plugin to the current one?

There may be better ways for others, but I've settled on always making a
discrete table to contain data of a specific version (e.g., "version 5"
data) and then doing a conversion/insertion of the data from the old
table ("version 4") into the new one. After the conversion, the old
table is dropped. (Example code is available for the curious[1][2]).

I use a progressive upgrade scheme - there's a chunk of code that
converts v1 data to v2 data, and another for v2 -> v3, and so on.
Doing a (conditional) creation followed by insertion means that if I
somehow get v2, v4, and v5 data in the same database, the conversion
will still work:

* v2 data is detected
	* v3 table is created
	* v2 data is merged into the (empty) v3 table
	* v2 table is dropped
* v3 data is detected
	* v4 table already exists
	* v3 data is merged into the v4 table
	* v3 table is dropped

And so on. Using this method means that there are no special cases (the
SQL for table creation always stays the same), and that the upgrade
logic is completely self-contained -- it does not need to be revisited
after it has been proved solid. It is also extremely straightforward to
detect what version of the data is in use by the name of the table in
question. Finally, it lays the foundation for solid upgrade support
from any old version to any newer version.

This upgrade process takes longer when upgrading really old data to the
newest version (vs. a direct upgrade), but the upgrade process is not
frequent, and is one-shot when it does occur. Upgrades from a recent
version to the newest are impacted only marginally (by however long it
takes to do a bunch of no-op upgrades). The lack of speed when
upgrading from old versions is also tempered by the fact that
correctness is better ensured by the tried-and-tested upgrade code,
compared to newly-written direct upgrade code.



Because MySQL uses a temporary table to perform ALTER TABLE
operations[3], I don't see a compelling reason to utilize ALTER TABLE
vs. creating, mapping, and dropping the tables directly. ALTER TABLE
also makes merging awkward; from the example above:

* v2 data is detected
	* ALTER TABLE the v2 data to a v3-temp table
	* Is v3 already present?
		* Yes: ALTER TABLE to rename v3-temp to the v3 table
		* No: merge the v2 data into the v3 table
	* Drop the v2 table if it still exists



I don't particularly like dbDelta, because it encourages
skipping straight to the newest schema, without going through
(potentially important) intermediate steps. You have to worry about
v1->v4, v2->v4 and v3->v4, instead of just having to worry only about
v3->v4. This means that the testing and coding space gets larger and
larger as the product gets older, and that you have to write new code
to deal with code that you are less and less familiar with (that is,
the v1 code is not likely fresh in your memory by v4). It can
potentially be faster, but it is also more prone to special cases, and
therefore error.


My $0.02,

-- 
Travis 

In Series maintainer
Random coder & quality guy
<http://remstate.com/>

[1] http://svn.wp-plugins.org/in-series/tags/3.0.7/in-series-init.php
(do_*_metadata_convert and initialize_*_database functions)
[2]
http://svn.wp-plugins.org/in-series/tags/3.0.7/in-series-internal.php
(activate function)
[3] http://dev.mysql.com/doc/refman/5.0/en/alter-table-problems.html