I've had this pet project for a long time (before I knew about such luxuries as Class::DBI in fact). It's a very simple object-persistence interface. After long years of Perl learning here at the Monastery, I've come back to it and refactored it into a state that I'm very proud of. I know I find it unmeasurably useful, but at the risk of being Yet Another POOP Module, I thought I'd ask for some input here first to see if this idea is release-worthy. Also, naming modules is difficult.. The production name is DBIx::SimpleObj (I'm indifferent to this name so far). I haven't posted the code here, but I certainly can if folks are interested.
You're probably asking, "So if it's not Yet Another POOP Module, what makes it special? I'll just use Class::DBI, dork!" Well, I very rarely find that I need the advanced features of Class::DBI like joins, custom attribute inflation/deflation, etc.. For example, I was cooking up a simple database-backed blog for my homepage recently, with only two tables. In the little apps like this where I use object persistence (mostly for the clean interface and to avoid coding SQL), the object persistence should be 100% automatic because I'm lazy and the schemas are so simple.
So here's the goal of this module: Use the module, give it a $dbh, and without any other intervention on your part (that's two lines total), you should have all the appropriate classes for a persistent-object interface to the data, with foreign key inflation and other niceties. Add another table to your DB schema, and without adding extra stuff to your code, you already have a new class for that table the next time your code runs -- automatically. Having to tell the interface about the structure of your data is boring. In lieu of this, the module will use the metadata in the DB (table, column names, etc) to figure out the structure of things. Obviously I can't match the functionality of the big guns like Class::DBI in this kind of a module, but what I can do make the simple things easier. Here are some of the important details about what it does:
- One class per table
-
Upon scanning the tables, the module auto-generates a class for each table. The package name will be in StudlyCaps to fit with Perl conventions, so the DB table foo_bar would correspond to Perl objects of the FooBar package. All tables must have a column named id that is the primary key and set to auto_increment. All other columns get an accessor/mutator method named after the column (the id gets a read-only accessor).
Classes are also provided with a few class methods: search, new, and fetch, an optimized version of search(id => $id).
You can still override methods if you really need to with SUPER (dumb example):
package Employee; sub ssn { my $self = shift; my $ssn = $self->SUPER::ssn; $ssn =~ s/(\d{3})(\d{2})(\d{4})/$1-$2-$3/; return $ssn; } - Auto-detect foreign key references
-
If a column has the same name as some other table, it is treated as a foreign key. Example: I have two tables, employee and department. The employee table has a column called department, so it is automatically treated as a foreign key: The method call $emp->department will be inflated to a Department object as an accessor (not just return a department ID), and can accept a Department object (or just an ID) as a mutator.
Conversely, the foreign keys work in reverse. I can call $department->employee (read-only) to get a list of Employee objects that reference this department.3 This is just shorthand for Employee->search(department => $department).
You may be thinking, why impose naming restrictions like this? First, to keep our sanity most of us have similar DB naming conventions anyway.2 Also, to support MySQL, I don't want to just look at the foreign key attribute of the columns, which are merely cosmetic in MySQL (and therefore rarely used).
- Lazy loading
- *BLOB and *TEXT columns are only loaded only when that data is requested, so the most common queries can be a bit faster.
- Sort Order
- The first column (other than id) in the table's schema is the sort order of all queries on that table. If the employee table has columns id, name, and department, in that order, then all searches returning a list of Employee objects will be sorted by name.
- Stringification
- If a table has a column named name, its value will be used for an object's stringification value. Otherwise, the stringification will be "classname:id".
- Flyweight implementation
- ..in both senses of the word. Objects are implemented as lightweight references to a scalar containing the object's ID. The ID is used to index instance data in a big lexical hash in the superclass. This way, updates will be automatically shared for all concurrent objects that represent the same tuple in the DB.
Here are some current drawbacks, shortcomings, to-dos:
- So far only supports MySQL. If this causes you to not take me seriously, just forget I mentioned it.
- It would be nice to allow foreign key columns to have an optional _id at the end, as that seems another commonly-used naming convention (the employee table has a column called department_id).
- It would be cool to use some Lingua pluralizer/stemmer to let me say $department->employees (note the plural) for the reversed foreign key accessor.
- So far, no transactioning (yet?) -- everything is autocommit.
- Obviously it doesn't do complicated stuff like joins, but that's missing the point. Maybe it could do many-to-many relationships that bypass the middle table, but I haven't thought of a good way to it yet.
As I said, I really like using this module for my own projects because writing the table schemas to get them to work with the module is how I would have written them anyway! So the two extra lines I add by using this module and giving it the $dbh feel like having my mind read. But would these rules I'm imposing on the schemas be a stretch for other users? Is this something best left just for my own personal use, or do you see a potential niche for something like this? Thanks a lot, and all input is appreciated.
blokhead