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
_______________________________________________
List: [email protected]
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/[email protected]/
Dev site: http://dev.catalyst.perl.org/
