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

my @a=qw(random brilliant braindead); print $a[rand(@a)];

In reply to Re: Versioned database schema git conflicts. by Rhandom
in thread Versioned database schema git conflicts. by chrestomanci

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.