Sunday, September 6, 2009

Birth of DBIx::DataModel (or : Perl ORMs in 2005)

DBIx::DataModel is the object-relational mapping (ORM) layer we use for our projects at Geneva's law courts. It is much less known that DBIx::Class, but nevertheless a few people have shown interest in it, so I thought it's worth blogging about some of its design aspects. Today's blog is about history : a couple of words about why and how DBIx::DataModel was born.

Back in 2005, I had already written a number of Perl/DBI programs for Geneva's law courts, for data reporting or statistics purposes. Each of those programs was a separate entity; but the corpus kept growing, and some repetitive patterns started to appear, especially about expressing joins between the many tables that sit in our database. Then came the idea to also choose Perl, no longer just for reporting programs, but for rewriting our old Cobol business application. The application is mission-critical, and programming it in Perl seemed crazy to some people in house, so an initial design and proof of concept were of order to demonstrate feasibility. At this point, it was quite clear that some kind of ORM would be needed on top of DBI, to deal with SQL in a more abstract way.

After a CPAN survey, I identified Class::DBI as being the most likely ORM candidate module for our needs (other contenders at that time were Alzabo, Tangram, DBIx::Recordset, among many others). However, there were a couple of annoying limitations :

  1. columns retrieved from any given table were fixed at table declaration time; no possibility to later modulate the column list at the query level, resulting in unnecessary data transfers;
  2. joins declared in Class::DBI could be used to issue successive queries for navigating between related records, but not for doing a database join in a single query;
  3. conditions as specified in search or search_like were too limited in expressivity (no boolean combinators, no comparison operators, etc.).

In other words, if the goal was to produce SELECT [columns] FROM [tables] WHERE [conditions], there was not enough flexibility for the the columns part, nor for the tables part, nor for the conditions part !

Adapting Class::DBI to work around these limitations seemed quite difficult, and I didn't find any substitute in the other modules mentioned above. So I started designing my own ORM, trying to optimize for SQL flexibility. In particular, adopting the wonderful SQL::Abstract from Nate Wiger was a good solution for expressing any kind of conditions in the WHERE clause as nested Perl datastructures. I also tried to apply the DRY (Don't Repeat Yourself) principle to relationships : if there is a 1-to-n relationship between two tables, better say it in one single place, rather than saying "has_one" on one side and "has_many" on the other. Finally, on the delicate problem of matching database joins to some kind of object-oriented entity, I came to the conclusion that the closest concept was the one of multiple inheritance : a record coming from a join between Artist and CD is an object that belongs to both the Artist and CD classes. This initial design evolved internally for a couple of months, until an initial CPAN release in September 2005.

Meanwhile the outside world had been evolving. In May 2005 came an extension to Class::DBI called Class::DBI::Sweet that also relied on SQL::Abstract. One month later, Class::DBI::Sweet was extended with some features for doing joins (prefetch feature and join argument to search method). Then in August came the first release of DBIx::Class, bringing a fresh design to work around Class::DBI limitations, but with an eye to backwards compatibility. That initial release had no support for database joins, but it was later extended in end September with prefetch, in the same vein as Class::DBI::Sweet. DBIx::Class very quickly showed to have an active and growing community.

At this point, the question was whether or not to adopt DBIx::Class. The advantage would have been to join a community and benefit from shared progress on common concerns. However, DBIx::DataModel had grown to something sufficiently mature and sufficiently different, so switching ORM implied some cost and mental shift. Furthermore, some original ideas of DBIx::DataModel seemed worth pursuing, and actually proved very useful in our projects. So finally the module is still alive, had a major refactoring in 2008 ... and anybody who is interested in helping to improve it is cordially welcomed!

Next blog entries will highlight some specific design features.

No comments:

Post a Comment