Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re: Versioned database schema git conflicts.

by Rhandom (Curate)
on Apr 02, 2014 at 14:22 UTC ( #1080783=note: print w/replies, xml ) Need Help??


in reply to Versioned database schema git conflicts.

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)];

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1080783]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (7)
As of 2020-10-01 07:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If at first I donít succeed, I Ö










    Results (176 votes). Check out past polls.

    Notices?