APM: Database wrapper classes

[erik at debill.org]

On Tue, Aug 13, 2002 at 09:38:16AM -0500, Hrunting wrote:
> On Tue, 13 Aug 2002, Wayne Walker wrote:
> : 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)
> 
> That is the best way.  Hide the logic behind the middle layer.  What I
> like to do is create a generic *::DBI class that inherits from DBI and
> stores the information for connecting to the database I need (through
> methods like *::DBI->connect() or *::DBI->connect_ro()) and have the
> wrapper classes use that (so they don't have to know anything about
> user/pass information, etc.
> 
> If I ever need to do more complex work than my wrapper classes allow, I
> just use the *::DBI class to pull my database connection and do the
> work manually.

I'll second this (though I don't inherit from DBI, I wrap it), and add
in a little trick to handle building all the inserts and updates.  I
really don't like high level coding going near SQL.

Basically, we've got so many tables, and our database engine is so
persnickety about quoting columns (quote strings, don't quote numbers,
act really funky with timestamps) that I ended up putting together
generic build_insert and build_update functions.  These functions work
something like this (module names have been changed to protect the guilty):

    my $dbh = DB::connect();
    my $columns = { memberid => 437,
                    first_name => 'Erik',
                    join_date => '2002-05-04 06:04:02.000000' };
    my $sql = DB::build_insert($dbh, "member", $columns);

or maybe

    my $columns = { last_login => 'CURRENT TIMESTAMP' };
    my $sql = DB::build_update($dbh, "member", $columns, "memberid = 437");

and then you do the usual $dbh->prepare() $dbh->execute() bit.  To do
this, I built a module (auto-generated from the table creation SQL)
that maps tablenames to columns and their types.


You still end up manually doing the SQL for joins, though.  There's
still no excuse for high level code having SQL, though.  Those calls
can end up looking like:

    my $logins = MemberLogins::by_username('erik');

    while($logins->next()){
            do something;
    }

which might join between a member table and member_logins.

Still no solution to get around manually writing the join, though (at
least none that's not more work on you than actually writing the SQL).

We've got a few hundred tables, so anyone that comes up with that bit
of trickery, just send it on over...


Erik



-- 
If somebody sues you, you change the algorithm or you just hire a
hit-man to whack the stupid git.

                              -Linus Torvalds
                              (talking about software patents)