Re: Abstracting sql

[chunlou]

When you need User_data_mysql, User_data_postgre, User_data_flatfiles, etc. or something like User_Data_Mysql::get_data, polymorphism will be a good option to consider.

One reason you don't want to do $sql="select foo,baz,bar from qux where foo=1 and baz=2 order by bar limit 0,20" is that the DB becomes responsible for most of the error handling, whereas something like getdata(\%data) helps trap errors before it even reaches the DB

You probaby would want to start with the design of a generic data access module all your app specific modules will use. That is, something like:

App01.pm --> MyDB.pm --> DBI.pm --> MySQL
App02.pm --> MyDB.pm --> DBI.pm --> Postgre
App03.pm --> MyDB.pm --> DBI.pm --> FooDB

MyDB will most likely have to be OO, since at least you have the persistent data object "DB Handler" hanging around. OO way is more convenient to handle it.

MyDB won't have any table and app specific methods. Only a few generic database methods, such as "insert_row," "delete_row," "show_table_asXML," "show_table_asArrays," etc.

It would work if you confine your methods to do the SQL stuff that are more or less supported by every DB. If not, you might want to split out some DB specific methods into some DB specific module.

At application level, you might want to create two types of modules, one is tables specific but not business logic specific (say, MyAppTable.pm); the others are business logic specific (such MyAppThumbnail.pm, MyAppMessageBoard.pm, etc.). Something like:

MyAppMessageBoard --> MyAppTable --> MyDB --> DBI --> MySQL
MyAppThumbnail --> MyAppTable --> MyDB --> DBI --> MySQL
MyBizLogic --> MyAppTable --> MyDB --> DBI --> WhateverDB

If you want to create methods with hardcoded parameters such as "get_user_data_by_id," "get_user_data_by_name," etc. (sometimes there're good reasons for that, such as being backward compatible with legacy code), you could try to see if you can design in such a way that you can generate the methods as automatically as possible, such as (not a direct example but it shows the idea):

#! /usr/local/bin/perl -w
use strict ;

# ########################################################### 
# Schema: Survey Database 
my %Survey = 
(
    tblq       => { primID => ['q_id'],
                      cols => ['anstmpl_id', 'short_label', 'question'
+, 'updated'] },
    tblqtmpl   => { primID => ['qtmpl_id'],
                      cols => ['name', 'descr', 'updated'] },
    tblqtmpl_q => { primID => ['qtmpl_id', 'q_id'],
                      cols => ['seq', 'notes', 'updated'] },
);

# ########################################################### 
# Package: Survey Database 
{package Survey ;
    
    # assume %Survey defined somewhere
    
    # = = = = = = = = = = = Contructor = = = = = = = = = = #
    sub dbconnect { }
    # = = = = = = = =  = =  Public Methods = = = = = = = = #
    foreach my $tbl (keys %Survey){ 
        # define subroutines <action>_<table> at runtime
        eval qq/sub insert_$tbl {
            return (shift)->_insert_table(_tblname(),\@_)}/ ;
        eval qq/sub update_$tbl {
            return (shift)->_update_table(_tblname(),\@_)}/ ;
        eval qq/sub delete_$tbl {
            return (shift)->_delete_table(_tblname(),\@_)}/ ;
    }
    # = = = = = = = = = = = Private Methods  = = = = = = = =#
    sub _insert_table { 
        my ($self, $tbl, $values) = (shift, shift, shift) ;
        # ... and more... 
    }
    sub _update_table { 
        my ($self, $tbl, $values) = (shift, shift, shift) ;
        # ... and more... 
    }
    sub _delete_table { 
        my ($self, $tbl, $values) = (shift, shift, shift) ;
        # ... and more... 
    }    
    # -----------------------------------------------------
    sub _tblname{   
    # return <tbl>, if called by <action>_<tbl> 
    # e.g. show_tblanstype
        ( my $sub = (caller(1))[3] ) =~ s/.*::.*?_(.*)/$1/ ;
        return $sub ;
    }
}

# ######################################################## 
# Test Script: Survey Database Package 

# tblq
$Survey->insert_tblq(\%data) ;
$Survey->update_tblq(\%data) ;
$Survey->delete_tblq(\%data) ;
# tblqtmpl
$Survey->insert_tblqtmpl(\%data) ;
$Survey->update_tblqtmpl(\%data) ;
$Survey->delete_tblqtmpl(\%data) ;
# tblqtmpl_q
$Survey->insert_tblqtmpl_q(\%data) ;
$Survey->update_tblqtmpl_q(\%data) ;
$Survey->delete_tblqtmpl_q(\%data) ;

___________________
Update: In theory, all your specific SQL statements and stored procedure calls go to MyAppTable and nowhere else. MyDB might have few or no SQL statements at all.