Your layout seems somewhat similar to the layout we use at our company. Historically we have used files similar to
updates/${table}_${rev_from}_to_${rev_to}.sql to make our updates. Our update process has also allowed for files of the form
updates/${table}_${md5_from}_to_${md5_to}.sql .
However, after getting tired of renaming those tables and having left over schema lying around, we have now opted for a second more compact way of doing things. For our schema we now can have up to 3 files for a table definition:
schema/$table.sql
schema/$table.inserts
schema/$table.updates
The $table.sql file contains the correct creation schema including extents and engine information. If using DBIx::Class::Schema::Versioned I'm sure this would likely be closer to a YAML file definition.
The $table.inserts file contains initial data for tables that require fixed data.
The $table.updates file replaces the updates/$table_... files. Inside of $table.updates there are sections that look like this:
------- BEGIN $sha1_from -> $sha1_to -----
# (vc rev and/or project #)
ALTER TABLE `$table` MODIFY ...;
------- END -----
Parsing of this file is fast. It is easy to take the existing table definition active in the db, and then find all of the different ways it can be morphed into new schema based simply on the sha1s of the source and destination.
New definitions normally go at the top of this file. Older defintions can slowly be culled from the bottom when all known schema installations have upgraded past a certain point (or just leave them there - it will be fast until you get past a couple hundred revisions).
my @a=qw(random brilliant braindead); print $a[rand(@a)];