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

[Callum Macdonald]

Sounds like solid advice. It's a new approach to me, but I like it, I 
might use that on bigger projects. Cheers - Callum.

Travis Snoozy wrote:
> 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,
>
>