Re: Schema version control

On Fri, Feb 11, 2011 at 8:35 AM, Daniel Popowich
<danielpopowich(at)gmail(dot)com>wrote:

> FWIW, this is what I do:
>
> 1. I have a table in my database, meta, that contains exactly one
> row, and holds configuration information. A minimal version of this
> table:
>
> CREATE TABLE meta (
> id integer DEFAULT 1 NOT NULL CHECK (id = 1),
> major integer NOT NULL,
> minor integer NOT NULL,
> patch integer NOT NULL
> );
>

What both Rails and my own system does is, rather than having a version,
each migration has a name; for example,
"20110211-193000-create_initial_db". The table is the set of which
migrations have been applied to the database, eg.
"20110211-193000-create_initial_db". The migrations are applied in sorted
order; hence the prefixed timestamp.

The benefit of this is when you're merging changes from separate branches.
Disconnected branches can each add their own migration rules for the part of
the database they affect. When the two branches are merged, they fit
together naturally. This doesn't work at all when you have ordered "version
numbers".

For example, if "20110211-add_column" is added to trunk, and then a branch
is merged which has "20110210-add_table", the merged migration fits in
implicitly and there are no collisions as with version numbers. (It's
applied out of order if you already ran add_column, of course, so you still
need to be sure that branches don't collide with each other.) Databases
migrate straightforwardly after the merge, regardless of whether they were
migrated to trunk or to the branch.

--
Glenn Maynard