Re: Schema version control

On 02/10/2011 03:59 PM, Bill Moran wrote:
> In response to Rob Sargent <robjsargent(at)gmail(dot)com>:
>
>> Top-posting is frowned upon by some (not me), but since Bill started it...
>
> Oops ... the weird thing is that I'm usually really anal about not top-
> posting ...
>
>> 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.

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.