APM: Database wrapper classes

[Steve Lane]

Wayne Walker wrote:
> But, more importantly, I want other peoples inputs on good ways to wrap
> database access in some kind of class or package.
> 
> Let's say I have a project that uses three database tables and I need/want
> to hide/bury the SQL away from the application developers (who are new
> to perl, every piece I can hide from them is one less thing they need
> to learn right now).
> 
> I could write some classes that just make the SQL disappear:
> 
> Person
>         add(%data)
>         delete($id)
>         get($id)
> Address
>         add(%data)
>         delete($id)
>         get($id)
> Relations
>         add(%data)
>         delete($id)
>         get($id)
> 
> This alone makes life easier for everyone.  At first...
> 
> Then we run into performance issues, or cross table joins, or searches,
> ...
> 
> At which point this is now fairly ugly.  We end up having the SQL hidden
> away, except for complex steps and then we're doing SQL outside of any
> of the objects.
> 
> So, any recommendations?  Existing CPAN modules that handle this somehow.
> A particular class hierarchy that makes sense for this that you've used
> (a few class names and method names like I did).

i do this kind of thing like this:

use MyDBI;
my $DBI = MyDBI->new(
  DB => 'MyDatabase',
  TABLE => 'MyTable',
  USERNAME = 'steve',
  PASSWORD = 'evets',
);

my $o = $DBI->select_by_id(123);  # does a SELECT * FROM MyTable WHERE id = 123
$o->name('Steve Lane');
$o->address('123 Elm St.');
$o->update;  # does an UPDATE

my $o2 = $DBI->empty;
$o2->name('Steve Lane');
$o2->address('123 Elm St.');
$o2->update;  # does an INSERT

my $o3 = $DBI->select(name => 'Steve Lane');
$o3->delete;

$DBI->delete_by_id(666);
$DBI->delete(name => 'Bob Jones');

my @os = $DBI->selectlike(name => 'S');
map { $_->updated(time); $_->update } @os;

my @os2 = $DBI->select_raw("WHERE name LIKE 'S%' ORDER BY income DESC");
map { $_->income($_->income + 5_000); $_->update } @os;



and so on.  inherit from MyDBI if you want to restrict to
a particular database/table/username/password etc.

if the objects have properties other than their database data,
just put their class in the ->new() call:

use MyDBI;
my $DBI = MyDBI->new(
  DB => 'MyDatabase',
  TABLE => 'MyTable',
  USERNAME = 'steve',
  PASSWORD = 'evets',
  CLASS => 'StripOfBacon',
);

my @os = $DBI->fetch(crispiness => 'thawed');
# ->fry is a StripOfBacon method, not a MyTable field.
# it may modify several object fields, add calories to
# the user's calorie-counter, etc.
map { $_->fry; $_->update } @os;  

have the appropriate amount of fun, and let me know if
MyDBI is implemented :).  comments welcome of course.
--
Steve Lane <sml at zfx.com>