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

[Darren Duncan]

Matija Grabnar wrote:
> I was wondering what the experienced Catalyst developers use to set up a 
> database in a project.
> 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?
> 
> 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?

What I like to do is write as much as is reasonably possible in SQL.

Supplementing Dave's response, I also like to exploit SQL stored procedures and 
define one for each distinct data manipulation or query operation.

So, rather than issue the bulk of an insert/update/delete/select from Perl, I 
wrap those statements in SQL stored procedures and then just invoke those from 
Perl instead.

Design-wise, you would typically have a declared stored procedure parameter for 
each bind variable that the underlying statement(s) would have if issued 
directly, but that you can save on duplication if your procedure is wrapping 
multiple statements that each take the same bind values.

On the Perl side, the DBI bind params would then map to procedure params, as the 
procedure call is then your SQL statement issued from Perl.

When you use SQL stored procedures to mediate access to your tables, then when 
you want to change the tables or columns or indexes etc that you have, it is 
less likely that you will have to make any changes to your Perl, as the 
appropriate manipulation or query changes may be confined to the database, so 
many changes won't require changing anything but the database.

-- Darren Duncan