[Poop-group] RFC -- Class::PersistentObject

[Curtis P. <cp...@on...>]

Hi all,

I'm familiar with the modules at http://poop.sourceforge.net/, yet somehow,
I still want to write another one.  Silly me, eh?

RDBMS to OO mappers are great, but have a lot of overhead in dynamically
generating SQL and the SQL is not always easy to hand over to a DBA for
optimization.

Persistent Object systems like Tangram are wonderful -- if you don't have an
existing database.

Class::PhraseBook::SQL is nice in that you don't have to dynamically
generate the SQL, thus saving some overhead (and the phrasebook pattern is
great if you have multiple database programs), but the SQL still gets
hardcoded into an XML file.

What I'm considering is Class::PersistentObject (though I need a better
name).  The idea that I am working on is combining a *very* lightweight
persistent object system with phrasebooks.  My core persistent object code
is only 400 lines long and likely won't need to be much longer as much of
the extra functionality of things like Class::DBI will be superfluous with
phrasebook SQL.  This is hardly a novel solution, but what if the phrasebook
SQL is built dynamically and cached to disk?

Each object knows its table and column names and by running a registration
system, each object can register its dynamic SQL via a registration class.
However, in production, the registration class is not used and instead the
cached copy of the SQL is used (thus eliminating runtime generation of this
SQL).  If you need to change something in the database, you can update the
information in one spot, reregister the changes and everything automatically
picks it up.

Goals:

* Enforce decoupling of the application from the database.
* Have phrasebook generated dynamically by a utility program,
  thus not interfering with production performance.
* Core persistent object code is very lightweight.
* Hook registration system into test suite a halting registration
  on test failures.
* Optional DBI hooks in registration to warn about phrasebook SQL
  that's probably not tested?
* Simple changes to database require updating in one and only
  one spot.

Sample registration SQL:

my $phrasebook  = Class::PersistentObject::Registration->new($file);
my $customers = Customer->table;
my $orders    = Order   ->table;
my %c         = Customer->columns;
my %o         = Order   ->column;

my $sql = <<"END_SQL";
SELECT          $c{first}, $c{last}, $o{id}
FROM            $customers
LEFT OUTER JOIN $orders
             ON $o{cust_id} = $c{id}
WHERE           $c{id} = ?
END_SQL
$phrasebook->register($sql, $type, $name);

Notes:

* the above is *not* run in production, but in preproduction.
* columns are returned in form [table].[column] (alternate formats
available?)
* it's tedious, but this is only for custom SQL.  Basic SELECTs, INSERTs,
UPDATEs, and DELETEs would be generated dynamically.
* when registered, this might get written to the phrasebook as:

SELECT          customers.firstName, customers.lastName, orders.id
FROM            customers
LEFT OUTER JOIN orders
             ON orders.customerID = customers.id
WHERE           customers.id = ?

If you change a column or table name, fix it in the accessor/column map in
the appropriate object and rerun the registration system.

Does this make sense?  Looking forward to flames and feedback.

Cheers,
Ovid