http://qs1969.pair.com?node_id=86902

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); };

 
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

 
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.

      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

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";


    -mk
Re: Metadata and DBI Abstraction
by princepawn (Parson) on Jun 09, 2001 at 21:33 UTC