perlquestion
rob_au
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 <code>PREPARE</code> and <code>SELECT</code> statements) of individual CGI scripts which interface and generate HTML pages for a medium-to-large scale web site.
<readmore>
<br/> <br/>
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:
<br/> <br/>
<dl><dd><code>
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]} : undef;
};
return ($self);
};
</code></dd></dl>
<br/> <br/>
Now this works nicely - I will however be modifying the <code>$self->{data}->{$sth->{NAME_lc}->[$index]}</code> line to call a module subroutine in the same vein of <code>CGI->param(-name => '', -value => '')</code>.
<br/> <br/>
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 <code>$args</code> hash with the <code>TYPE</code>, <code>PRECISION</code> and <code>NULLABLE</code> field arguments from the <code>$sth</code> 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.
<br/> <br/>
The standard values for common SQL data field types returned by TYPE are shown below for reference (from <a href="http://www.oreilly.com/catalog/perldbi/">Programming the Perl DBI</a>):
<br/> <br/>
<dl><dd><code>
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
</code></dd></dl>
<br/> <br/>
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.
<br/> <br/>
Thanks in advance
<br/> <br/>
<i>Ooohhh, Rob no beer function well without!</i>