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;
}
[download]

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.³ 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.² 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:

1. So far only supports MySQL. If this causes you to not take me seriously, just forget I mentioned it.
2. 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).
3. 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.
4. So far, no transactioning (yet?) -- everything is autocommit.
5. 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.