[Catalyst] Best practice for setting up database in a complex project?

[Dave Rolsky]

On Fri, 16 Jul 2010, Matija Grabnar wrote:

> Do you write the database definition mysql/postgresql format, and then dump 
> schema to get the Perl classes, or do you write Perl class definitions and 
> use something else to output the table creation statements for the
> database of your choice?

Personally, I like to write the schema in SQL.

My _reason_ for this is that I actually intend to fully use the database. 
That means defining custom types, adding table constraints, triggers, etc.

The SQL schema file is the primary definition for the database.

> And what do you do when the structure of the database changes (new tables, 
> new columns, new indexes or foreign keys) - do you use a DB versioning thing, 
> or do you do it by hand? If you do use a DB versioning tool,
> which do you recommend?

If you're using things like constraints, triggers, etc., then you'll 
almost certainly need to do this stuff by hand.

I've written some tools to help with this as part of a wiki I'm working 
on. You can see it on CPAN (http://search.cpan.org/dist/Silki) or in my 
mercurial repo (http://hg.urth.org/hg/Silki).

I'm pretty happy with how this works, and I've even written a framework to 
do automated tests of my migrations, which is very helpful. It's very easy 
to make mistakes when writing migrations, so tests are pretty valuable 
here.


-dave

/*============================================================
http://VegGuide.org               http://blog.urth.org
Your guide to all that's veg      House Absolute(ly Pointless)
============================================================*/