Exploring Class::DBI, linking tables, and other issues

I’ve been playing with Class::DBI the last week with an eye for using it for ActionDirectory. Right now the current system has over 10,000 lines of code for the DB level objects. Mostly it’s taking requests, selecting the right sql to use, filling in values, executing sql, populating resulting objects, inserting in to the database and doing updates. It’s boring and very slow to code by hand.

Update: There’s a post on perlmonks dealing with the issue of linking tables which is worth reading. And, just for the record, the Class::DBI needs a small book line was stolen from kellan, he deserves the credit. :)

Kellan’s been playing with Class::DBI lately and from what he wrote it seemed interesting. So i’ve been looking at what it would take to start using cdbi.

First off, for easy stuff it’s great. Objects where you’ve only got one table for objects, and relatively simple relationships. For ActionDirectory we have a User object, which has a Contact object which can have a Location which can have City which has a State which has a Country. That was all easy to setup, and worked fine. Saved a lot of coding which when in to the current version which does it more or less manually (using Class::Accessor and DBI of course.)

CDBI doesn’t seem to use joins really. It makes me a little worried. The above select in my code, although taking up many more lines of perl, was only one select, not the 12 that CDBI needs. Twice for each table/object if you’re actually going to use ever object. Basically it selects the essential items, then does another for the full if you actually try and request any of the elements not listed as ‘essential’ it does a second select. All of the selects should be fast as they are based selecting an indexed id, but that was also the case with my join i have been using in the existing code.

If CDBI had caching then it would be fine. ActionDirectory has hooks for caching objects as they come out of the database, and invalidating them when information is updated, but the cache system is mostly wishful thinking at this point. There is some talk that a caching system might be added to CDBI, which would definitely help things.

Another issue is CDBI’s documentation is lacking. Which is not to say that there isn’t plenty of documentation or that the authors haven’t been working on it. There is good documentation and clearly people have put a fair amount of time in to it. The problem is that CDBI deserves a small book worth of documentation and not just the pod which is there now. Part of the reason it’s so important is that CDBI changes your relationship with the database and sql in such a way that you need to re-think how the work. There’s a section for cookbook recipes which hasn’t been written. Maybe i can submit something as i explore using CDBI.

The reason i’m writing this blog entry is actually to discuss Class::DBI and how to use it with linking tables. It’s not obvious at first. Linking tables are for when you want to connect two tables with a many to many relationship. In terms of ActionDirectory we’ve got a table for directories, and a table for organizations. Because organizations can be on more than one directory there is a table called organization_to_directory which just has the id the directory and the id of the organization.

The solution is to create a class for the joining table, OrgsInDirectory. This joining object would then have a has_a relationship with both Organization and Directory. You could get all organizations in a directory by doing a search on directory_id.

The thing i haven’t figure out is this. I want all my normal searches to include this join. When you’re looking at a organizations in a city, you only want organizations in a city which are also on this directory. I think what i will have to do is either override the basic search functions or provide my own which hook in to CDBI parallel to the basic search functions.