[Dbix-class] Ideas for database versioning.
aimass at yabarana.com
Tue Nov 6 03:46:06 GMT 2012
On Mon, Nov 5, 2012 at 9:10 PM, Bill Moseley <moseley at hank.org> wrote:
> We manage our schema DDL separately from our DBIC classes (it's not just
> DBIC that accesses the database). That is, we don't generate the schema
> DDL from DBIC.
We do the same. Not for the same reasons but because we feel that Db
modelling should be left for DB experts, not programmers.
> Here's the problem that came up recently:
> We have one large app that uses a DBIC model layer. Someone working on
> Essentially, I'm looking for ideas how to make sure that the DBIC model has
> its dependencies met -- which includes schema updates -- in some automated
> way. Hopefully in a foolproof way, too.
> What system do you use to make sure changes are tracked and are on the right
> database at the right time?
Here is the was we do it:
We maintain or DB model(s) in DIA UML Class Diagrams. We generate the
DDL using dia2code. We use a hacked version of dia2code but we are
soon releasing our patches to the current 0.8.5 on sf.net
The DDL is generated bu custom scripts that we make all our programers
run on their environment. The initial DDL is just part of the DB and
we call it tables.sql which only contains the tables, primary and
foreign keys that come directly from the diagram and the
relationships. Every change to the drawing creates a new tables.sql
In the same directory, we keep subdirectories such as "functions",
"views", "indexes", "procedures", etc. The DB creation scrips goes
through all these directories in order to create a new DB. It also
contains the valu list data and demo data for development In some
projects we make the script interactive (e.g. would you like to load
the demo data?)
A special directory is named "changes". Inside, there are paired files
with the date and a revision number. Each pair is a text file with the
description of the change, affected tables, etc. The second file is an
SQL that has the alters, export/imports etc. that would be needed for
the production databases.
For the development team is no problem because the scripts will always
generate the latest version. 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.
Finally, we have the update_model.sh script which updates the DBIC
model using loader. We use heavily customized Result and ResultSet
classes and the loader works perfectly every time.
Anyway, we've been working like this for years and very rarely have an
issue. I would be more than happy to demo the whole process to you or
anyone on this list. Just shoot me a private email.
More information about the DBIx-Class