rob_au has asked for the wisdom of the Perl Monks concerning the following question:

Okay, here's what I'm working on at the moment - I am writing a DBI abstraction module which would allow me to reference the elements of the database in an object-orientated fashion. The reason for this is to reduce the coding overhead and hard-coded complexity (in the form of coded PREPARE and SELECT statements) of individual CGI scripts which interface and generate HTML pages for a medium-to-large scale web site.

 
Thus far, I have a working prototype which achieves this end, however involves a number of hard-coded subroutines for the interfacing of objects of each (table) type. This is an aspect of the module which I am wanting to remove, thus making it more universal and portable in usage. To this end, I have the following code for the creation of new database entry objects:
 
sub new {
    my ($parent, $type, $args) = @_;
    my ($self) = {};
    bless ($self);
    foreach my $table ($parent->{dbh}->tables()) {
        if ($table eq $type);
            $self->{type} = $type;
            last;
        };
    };
    return undef unless (defined($self->{type}));
    print STDOUT "new - Creating new object of type '", $self->{type},
+ "'\n" if ($parent->{debug});

    my ($sth) = $parent->{dbh}->prepare("select * from ".$self->{type}
+);
    $sth->execute;
    for (my $index = 0; $index < $sth->{NUM_OF_FIELDS}; $index++) {
        $self->{data}->{$sth->{NAME_lc}->[$index]} = (defined($$args{$
+sth->{NAME_lc}->[$index]})) ? $$args{$sth->{NAME_lc}->[$index]} : und
+ef;
    };
    return ($self);
};

[download]

 
Now this works nicely - I will however be modifying the $self->{data}->{$sth->{NAME_lc}->[$index]} line to call a module subroutine in the same vein of CGI->param(-name => '', -value => '').
 
What I am wanting to achieve however and hence why I post this question for fellow monks is vetting of the values passed in the $args hash with the TYPE, PRECISION and NULLABLE field arguments from the $sth statement handle, in accordance with SQL data types recognised by the DBI module - That is, reject the passed data if it is not in the correct form, for example, a varchar-type argument is passed to be used for a int-type field.
 
The standard values for common SQL data field types returned by TYPE are shown below for reference (from Programming the Perl DBI):
 
SQL_CHAR              1
SQL_NUMERIC           2
SQL_DECIMAL           3
SQL_INTEGER           4
SQL_SMALLINT          5
SQL_FLOAT             6
SQL_REAL              7
SQL_DOUBLE            8
SQL_DATE              9
SQL_TIME             10
SQL_TIMESTAMP        11
SQL_VARCHAR          12
SQL_LONGVARCHAR      -1
SQL_BINARY           -2
SQL_VARBINARY        -3
SQL_LONGVARBINARY    -4
SQL_BIGINT           -5
SQL_TINYINT          -6
SQL_BIT              -7
SQL_WCHAR            -8
SQL_WVARCHAR         -9
SQL_WLONGVARCHAR     -10

[download]

 
I ask though, what do my fellow monks feel would be the best way to implement this sort of functionality? I am quite loathe to large select-type block of code with a separate block of 'vetting' code for each of the 22 standard and common SQL types recognised by DBI, but fear this will be what I will have to resort to.
 
Thanks in advance
 
Ooohhh, Rob no beer function well without!

Replies are listed 'Best First'.
Re: Metadata and DBI Abstraction
by pop (Scribe) on Jun 08, 2001 at 19:13 UTC
    You should have a look to similar existing modules/framework. For instance : I had a look to SPOPS (hi chis! ;-)). And especially SPOPS::SQLInterface it's pretty well writen. And this Class seems to implement a solution to your problem. SPOPS don't seem to support Postgresql (only mysql) but in CVS version At last, have look to past nodes on similar subject.
[reply]

      As pop mentioned, SPOPS is moving along. PostgreSQL support will be in the next version (0.40) which should be released today or tomorrow (June 9-10). I've been using it for a month or so and it's given me no problems. It also supports Sybase ASE/ASA and MS SQL Server (thru DBD::ODBC on Win32). Other database support probably isn't that difficult -- the main differences are auto-generating primary keys and ensuring that the DBD driver supports datatype value binding.

      One of the features of SPOPS (and Class::DBI AFAIK) is that you can retrofit an object representation to existing data. That is: you don't need to modify your schema to use SPOPS. Accessing legacy data is a major strength of Perl, and this makes it easier...

      Chris
      M-x auto-bs-mode

[reply]
Re: Metadata and DBI Abstraction
by marcink (Monk) on Jun 08, 2001 at 18:12 UTC
    I'm afraid you'll need a separate block of validation code for each type, but it can be done very cleanly. Try a variation of the code below:

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

my %validators = (
    'singleletter' => 'm/^[a-zA-Z]$/',
    'integer' => 'm/^\d+$/',
    'nottoolongstring' => 'length $_ < 10'
);

sub check
{
    my ( $type, $value ) = @_;
    my $v = $validators{$type};
    return 0 unless defined $v;
    $_ = $value;
    return eval $v ? 1 : 0;
}

print check( 'singleletter', 'a' ) . "\n";
print check( 'integer', 'a' ) . "\n";
print check( 'nottoolongstring', 'a' ) . "\n";

    [download]


    -mk
[reply]
[d/l]
Re: Metadata and DBI Abstraction
by princepawn (Parson) on Jun 09, 2001 at 21:33 UTC
[reply]

Back to Seekers of Perl Wisdom