Re: Schema version control

In response to Rob Sargent <robjsargent(at)gmail(dot)com>:
>
> On 02/10/2011 03:59 PM, Bill Moran wrote:
> > In response to Rob Sargent <robjsargent(at)gmail(dot)com>:
> >> I for one will be waiting to see your dbsteward. How does it compare
> >> functionally or stylistically with Ruby's migration tools (which I found
> >> to be pretty cool and frustrating all in one go).
> >
> > I'm not familiar with Ruby's migration tools, so I can't say much.
> >
> > The overview:
> > You store your schema and data as XML (this is easy to migrate to, because
> > it includes a tool that makes the XML from a live database)
> > Keep your XML schema files in some RCS.
> > When it's time for a new deployment, you run the dbsteward tool against
> > the schema XML and it turns it into DDL and DML.
> > When it's time for an upgrade, you run the dbsteward tool against two
> > schema XML files, and it calculates what has changed and generates the
> > appropriate DDL and DML to upgrade.
> >
> > So ... you know, however that compares with the Ruby stuff is how it
> > does.
> >
> Now at the bottom :)
>
> It's been a couple years since I played with Ruby ActiveRecord but it's
> (of course) radically than what you describe. The ddl is in the ruby
> code and naturally the code is in RCS. So a revision is a new instance
> of ActiveRecord (iirc) which does the change(s) (create table ttt, alter
> table vvv etc). Maybe skip a rev. Rollback to a rev is definitely
> there because one writes the undo for each new revision. This include
> manipulating the data of course, so there are limitations.

dbsteward can do downgrades ... you just feed it the old schema and
the new schema in reverse of how you'd do an upgrade ;)

Oh, also, it allows us to do installation-specific overrides. We use
this ONLY for DML for lookup lists where some clients have slightly
different names for things than others. In theory, it could do DDL
overrides as well, but we decided on a policy of not utilizing that
because we wanted the schemas to be consistent on all our installs.

> I personally am leary of the 'make the prod match the dev db' approach.
> Who knows what extras lurk in the depths. I think one should be able to
> make the dev db from scratch and write the necessary scripts to change
> to (and from if possible) each revision. Apply to prod when tested.

dbsteward allows us to do all this. A developer can make a change,
rebuild a test database from their change to make sure it works, then
test the upgrade process as well, all before even checking the code in.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/