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 -----
[download]

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).

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.