myrug: mysql rough upgraderHome | What's the Problem

The problem: how do you upgrade a MySQL database schema?

You can always simply use DROP and then CREATE tables but that will destroy all the data.

You can (the traditional method) keep track of all the changes and have a specific update script for changes between versions that will create any new tables, alter and create fields.

But... what if you have no idea of the current schema? What if you or the user has no idea what version they're on now and you've lost track of changes? That is where myrug comes in. It is used like mysqldump on a database (and optionally just one or more tables) and outputs an SQL script that (depending on the options provided) uses the current schema and:

This is a very rough approach and will (by design) generate many errors but at the end of the process you have the new schema. Obviously this relies on you having sensible defaults in new fields and won't take account of things being taken away.

Commonly now I will produce a number of SQL files for an upgrade which can be executed dependent on the circumstance.

To do a fresh upgrade of a database (and loose the data) I can import schema.drop and defaults.

To upgrade the database I import schema (as the upgrade doesn't account for new tables). This will fail on pre-existing tables. Then I import schema.upgrade which updates any already existing tables to new specifications and finally my defaults for the system (which will fail to add pre-existing settings).

-- © Copyright 2008 PurplePixie Systems, all rights reserved.