Friday, 1 May 2009

Complex databases with DBIx::Class

One thing that is tricky is combining the use of an Object-Relational Mapper, such as DBIx::Class or Rose::DB::Object with complex real world situations. Simple database queries are easy in SQL, but there's still a big gain from using an ORM to handle them as there are fewer lines of code and you can abstract away physical database changes to field names or relations without the calling code even knowing it has happened. Adding in complex joins and the use of database extensions inside triggers or stored procedures makes it difficult to get plain old SQL right, let alone in extra business methods to an ORM class that depend on the physical database structure and engine.

Here is where some examples come in handy.

Matt Trout has written a useful talk on how he implemented a combination of DBIx::Class ORM, custom queries and OO database extensions in a talk he gave to PostgreSQL WEST 2008 (note: requires Firefox to view).

The Catalyst Wiki MojoMojo implements a complex Entity-Relationship schema including hierarchical page nodes. It is well worth looking at the MojoMojo source code to see how it was done.

No comments: