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:
Tries to alter a field to the current settings (fails if the field doesn't exist)
Tries to create the field with current settings (fails if the field already exists)
Tries to create the primary index (fails if already exists)
Tries to create indexes for the current index fields (fails if an index already exists)
Optimizes the table (in case you have done lots of changes)
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.
schema.sql from mysqldump without DROP TABLES
schema.drop.sql from mysqldump with DROP TABLES
schema.upgrade.sql from myrug
defaults.sql (any system default settings/new settings I need included)
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).