On Mon, Nov 5, 2012 at 9:10 PM, Bill Moseley <mose...@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 script. 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. Cheers, -- Alejandro Imass _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk