[Phpug] Continuous integration and databases

[Chuck Burgess]

On Wed, May 21, 2008 at 9:43 AM, Kae Verens <kae at verens.com> wrote:

> Cormac Parle wrote:
> > If so - what do you do about an application's database? Is there any way
> > to track changes to the DB schema that you can then apply to the
> > staging/live db without affecting the data?
>
> that's a very interesting question.
>
> in my KFM project, I manage that by checking the version number and
> running associated DB scripts based on that. The DB scripts need to be
> created by hand (exclusively "alter" and "create" commands, usually),
> which can of course mean mistakes every now and then.
>
> I'm very interested to hear how other people do it.
>


That sounds like what I think most shops with CI do use... they call them
"migration" scripts.  You have to build these by hand, but they become
permanent parts of your codebase, for making DB modifications that are
needed as you upgrade an installation to each newer version of your
application.  This is also big in RubyOnRails.

As far as "not affecting the data", that would all depend on how you write
your migration scripts.  I'm not aware of anything out there that will *
automagically* stare/compare your old schema with representation of your
"new" schema and show you differences, if that's what you're after.  That
would probably require using database library (like MDB2_Schema) to dump a
representation of both old and new databases in order to spot differences.
-- 
CRB