[wp-hackers] Table updater wish

[Owen Winkler]

Hmm...  It's been a week and no word so I'll clutter up the list in the
hope that it's just me being clueless.

I worte this code to consolidate the database modifications between the
install and upgrade procedures.  Basically, you put the DDL of the
current database into the script (as easily output via phpMyAdmin) and
the dbDelta function builds a set of queries that will transform the
existing database from whatever state it's in to one that matches the
DDL specified.

Some notes on the code:

I had to leave in the index code because otherwise some indices aren't
created on upgraded tables.  Currently, an index is only created if an
index of that name doesn't already exist.

Contrary to a previous message, I have moved the creation of the users
table in with the bulk of the queries because either that table would
not be updated on an upgrade or the code to update that table would be
in two places.

You'll find the all of the DDL queries now in upgrade-functions.php.
They are in a "specific" format.  I basically exported a CVS 1.3
database from phpmyadmin (backquoted tablenames off, autoincrement off)
and performed a couple of regex search-and-replaces, namely:

* Replaced "wp_" with "$wpdb->".
* Replaced "^\).*;" with ");".

One more rule on writing out the DDL- A primary key need not have a
name, but other keys must have names.  If you do a dump from a 1.3 db,
there is an index (user_login) on the users table that is not named, and
you have to give it a name in the DDL script or it will re-add the index
every time.

Presumably, the index detection stuff could be improved to compare the
fields used in the index between the live database and the DDL (rather
than the index name), but what I've got works and there aren't that many
changes to the DB dump as long as you keep the above rule (named
indices) in mind.

All that said, you probably won't need to do another export if you just
add new fields/tables to the DDL in the format provided, but I thought I
would mention the prep just in case.

I did tests on .71, .72, 1.0, 1.0.1, and 1.2, and they all came out with
only minor differences in structure (few fields in a different order,
but all fields present and of correct type).

Give it a try and email me with problems/questions.  Or better yet,
confirm at least that you've received it, since I've sent it twice with
no response.

Owen
-------------- next part --------------
A non-text attachment was scrubbed...
Name: upgrade.zip
Type: application/octet-stream
Size: 23645 bytes
Desc: not available
Url : /pipermail/hackers_wordpress.org/attachments/20040812/f02af609/upgrade-0001.obj