I'm writing a module whereby i have to update a normalised database. Before I add an entry, i need to check to see if its there before, and if it is, then add it, if not return the primary key et al.
In this case i'm updating a movie database. There are a few tables in it, for example a table for "actors", "plot", "director", "genre" etc etc.
Tieing it all together is the main table that has a foreign key relationship with all the other tables.
The way i'm doing it right now is i'm having one routine for each operation that has to be performed, ie one rouine to check to see the "actor" exists (and return its primary key if it does), then another routine to insert it if it doesnt.
Now this can add up to a whole bunch of routines that are very similar, but essentially the same.
sub checkActor {
my $self = shift;
my %args = @_;
die "Must supply value for name" unless ($args{name});
my $retval = $self->_execute( handle => 'appfme',
statement => 'findActor',
bindvar => [ $args{name} ],
output => 1);
}
sub addActor {
my $self = shift;
my %args = @_;
die "Must supply value for name" unless ($args{name});
my $retval = $self->_execute( handle => 'appfme',
statement => 'addActor',
bindvar => [ $args{name} ],
output => 1);
}
The _execute method is a hookup to an abstraction to the dbi.
So, it occurred to me there would be better method of doing this, its just that, well, i cant think of it right now...
Has anyone had any experience doing this?
The core of the module is below to put it in better context.
package Movies;
use strict;
use DBhandler;
{
# This routine will return a statement based on the arg parsed
sub _get_statement {
my $self = shift;
my %args = @_;
my %sql = (
findactor => 'select count(*) where name = ?',
addactor => qw/insert into movies_actor values (n
+extvat('movies_actor_actor_id_seq'), ?/,
);
return $sql{$args{statement}};
}
# Go execute a statement
sub _execute {
my $self = shift;
my %args = @_;
my $retval;
$self->_fetch_handle if (! $self->{dbo});
$self->{dbo}->add_sql($args{statement} =>
$self->_get_statement(statement => $args
+{statement})
);
$retval = $self->{dbo}->execute(handle => 'appfme',
statement => $args{statement},
bindvar => $args{bindvar} ,
$args{output} => 1,
);
# Return a flag if the user exists
return $retval if (defined $retval);
}
sub _fetch_handle {
my $self = shift;
# Grab a handle to CAPS
$self->{dbo} = DBhandler->new(
handle => 'appfme',
user => 'user',
pwd => 'password',
sid => 'sid',
);
}
}
sub new {
my $pkg = shift;
my $self = bless { }, $pkg;
return $self;
}
sub checkActor {
my $self = shift;
my %args = @_;
die "Must supply value for name" unless ($args{name});
my $retval = $self->_execute( handle => 'appfme',
statement => 'findActor',
bindvar => [ $args{name} ],
output => 1);
}
sub addActor {
my $self = shift;
my %args = @_;
die "Must supply value for name" unless ($args{name});
my $retval = $self->_execute( handle => 'appfme',
statement => 'addActor',
bindvar => [ $args{name} ],
output => 1);
}
1
update (broquaint): added <readmore> tag
