[Dbix-class] Ideas for database versioning.

[Bill Moseley]

Sorry, following up after coffee:

On Tue, Nov 6, 2012 at 3:07 AM, Dave Howorth <dhoworth at mrc-lmb.cam.ac.uk>wr=
ote:

> Alejandro Imass wrote:
> > For the development team is no problem because the scripts will always
> > generate the latest version.
>

Using DBIC Loader?  We don't use Loader and I doubt we could due to the
complexity of the database (and that not every app uses DBIC).



> > The changes directory is mostly to
> > implement the changes in production and the process is very manual, as
> > it should be IOHO. We don't want any automation regarding productions
> > DBs, we want a DBA to apply these changes with care.
>
> I'm with Alejandro here. Writing distributed systems is not trivial.
> Writing distributed systems that perform distributed updates is less
> trivial. Writing such a system for low-volume, high value transactions
> just isn't worth the candle IMHO.
>

That's exactly where I end up when thinking about this.

Schema changes are frequent, but it's not like the volume of code changes.
 Maybe a few new columns every week or so.   The problem is when there's a
mistake it's potentially a big problem.   The trick, like always, is
finding a solution that fits the problem size -- that doesn't introduce
just as much risk or is overly complex.

My concern is writing some system that is used by many developers and tries
to automate every schema change and work in a general way (schema changes
are not always additive and simple ALTERs).  And be foolproof, which is a
challenge with many developers adding changes.

I like the idea of a database table that tracks the "version" of the
database, but that does not guarantee that a column is actually in the
database, or a view was updated correctly.  The only sure way is to
actually check for the column -- or better run a test against the database
that exercises the code that needs that column.

Perhaps a self-imposed constraint, but I also do like having a the full
current DDL in source control repo so that at any point I can look at the
full, commented, schema DDL.   It's easy to do diffs, and it's easy to
build a test database from any checked-out version.

 In a way it makes sense to have every change be in its own separate file
to avoid having to manage both a static, full DDL schema and the many files
with the ALTERs.  But, as a developer I don't like the idea of trying to
figure out what the database looks like as a whole from dozens (if not
eventually hundres) of individual change files that must be run in exactly
the right order.


What I keep coming back to is the only way to be sure is to run application
tests against the database where it will be used.   And if I'm going to do
that then is there really a point to adding a much more complex system to
manage every change on top of that?




> How do you review the changes that the first app made for its impact on
> all the other apps? Their test suites are incomplete with respect to the
> change by definition.
>
> The downside of having many separate apps instead of a monolithic
> application is that you have many more interactions like this. The best
> way I know is to have a single point (i.e. person) responsible for
> managing each interaction. It's best to have human experts participate
> in the operational process so that when there's trouble, they have
> experience. The same reason you want an operation on you done by a
> surgeon who does them regularly.
>

The problem is scalability.   We have five or six databases with hundreds
of tables and multiple environments (each with a different stage of the
database).  One person cannot keep track of everything and the requirements
of each application other than sanity check the ALTER scripts.

We are trying to have one ORM layer per database that multiple apps can use
and make that our "single point" -- but still have to make sure it matches
the database where it's used.



-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20121106/bc2=
52fb9/attachment.htm