Perl and database access

The base of all database work with Perl is the DBI module. There is no possible argument about this.

If you need something more high-level, things get dicey.

For the last 2 years I've been using the most excellent DBIx::Class. From all the ORM's that I've used so far, it is the best one out there and I still recommend it if you want to get up and running fast (quick tip: start with DBIx::Class::Schema and the load_namespaces() API, do not use load_classes()).

DBIx::Class has some great features:

• comprehensive test suite: self-explanatory;
• extensible: you can add pretty complex things on top of DBIx::Class. You can override every method using the magical Class::C3 foundation;
• manage schema versions: you can use your DBIx::Class::Schema to manage your SQL schema, including versioning and updates. Some manual tweaking is required, no out-of-the-box tool to do this for you, but that's expected anyway;
• nested transactions: this is killer feature for me. I've worked on a system (IPGng::SQL for those who know what that is) in the past (2001/2003 timeframe) that has this also, and it makes your code much more robust and simple.

Not everything is a good fit for me, though. There are two main sticky points.

The first is the use of SQL::Abstract as the query representation language.

Learning a new language that I can only use in the context of Perl is a personal waste of time. I have been using SQL with Oracle, MySQL, Postgres and SQLite for quite some time, and I can pretty much do whatever I need with it. Even when I hit a wall, I have a couple of Celko books behind me that usually help me jump over them.

So for me, the SQL::Abstract advantages aren't actually.

The second sticky point is a mismatch between DBIx::Class and my needs in terms of Object-oriented modeling.

Usually I have one entity in my object model (say for example Members) that is mapped to multiple tables (personal data, login information).

With DBIx::Class, I have the business logic dispersed into Result and ResultSet classes, per table, not per entity. This sort of works, but I find it messy for more complex projects.

So for a new project that I'm starting, I'm not using DBIx::Class. I don't have a full replacement, just parts that I'm enjoying putting together.

The first two modules that will be part of the final solution are DBIx::Simple and SQL::Interp.

They provide the minimal set of tools that such lower layer over DBI should have.

Above that, I'm still working on it. So far I have a wish list:

• transactions: nested transactions with commit/rollback hooks;
• DSL for schema versions: must have data dictionary features.

The first part will probably be a small Transaction module. I'll most likely use code like the txn_do of DBIx::Class::Storage, I like the syntax very very much.

The commit/roolback hooks are required because I need a "almost" two-phase commit protocol.

Picture this: you have two systems, a transactional database and a non-transaction messaging/pubsub system. Inside a DB transaction, you publish some events. If those events reach subscribers before the DB transaction is committed, they will not find the DB up-to-date.

What I need is to delay the actually publishing of the events until the final commit of the DB.

The second wish is a DSL that I can use to generate the SQL, with tools to manage upgrades between versions. This will be a SQL::Translator-based project, with a Parser class for my DSL.

This allows me to use all the Producers that the SQL::Translator project already has, and also the tools to diff SQL schemas.

The data dictionary part is the important feature. I want to declare types, including Perl code to validate and format values, in a central place, and then use them in the schema.

This DSL must generate also some base set of classes that you can extend with custom behaviors. This also provides a introspection interface that can be used to generate HTML forms and validation profiles.

Its seems a lot of work, and it is. But its also an incremental process going back some years, and I don't mind the wait.