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

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

}

[download]

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

[download]

update (broquaint): added <readmore> tag

Replies are listed 'Best First'.
Re: A Better way? (Lots of routines with similar structure)
by broquaint (Abbot) on Jun 08, 2003 at 20:19 UTC
    How about a simple add routine, e.g 
    sub add {
  my $self = shift;
  my %args = @_;

  die "Must supply value for name" unless ($args{name});

  ## why bother with $retval?
  return $self->_execute(
    handle    => 'appfme',
    statement => $args{type},
    bindvar   => [ $args{name} ],
    output    => 1
  );
}

    [download]
    Where $args{type} would previously have been the method type.
    HTH

    _________
    broquaint

[reply]
[d/l]
Re: A Better way? (Lots of routines with similar structure)
by zengargoyle (Deacon) on Jun 08, 2003 at 21:12 UTC

    to start, try using the magic of AUTOLOAD

    #!/usr/bin/perl
use strict;
use warnings;

package Thing;
use Quantum::Superpositions;

sub new { 
    my ($class,$txt) = @_;
    return bless \$txt, $class;
}

sub txt {
    my ($self) = @_;
    return $$self;
}

my @allowed = qw[
    findActor
    addActor
    findMovie
    addMovie
];

sub AUTOLOAD {
    my ($self, @args) = @_;
    use vars '$AUTOLOAD';
    (my $funcname = $AUTOLOAD) =~ s/.*:://;
    unless ($funcname eq any(@allowed)) {
        die "don't know how to $funcname on ", $self->txt, $/;
    }
    warn "doing $funcname on ", $self->txt, " with @args$/";
}

sub DESTROY { }   # for AUTOLOAD happiness 

1;

package main;
my $b = Thing->new("bob");
my $c = Thing->new("ralph");

$b->findActor("quincy");
$c->addActor("mary");
$b->doActor("what?");

__END__
doing findActor on bob with quincy
doing addActor on ralph with mary
don't know how to doActor on bob

    [download]

    later down the line you can loop through your supported functions and create anonymous sub's and attach them directly to your package namespace with GLOB magic.

    package Thing2;

sub new { 
    my ($class,$txt) = @_;
    return bless \$txt, $class;
}

sub txt {
    my ($self) = @_;
    return $$self;
}

my @allowed = qw[
    findActor
    addActor
    findMovie
    addMovie
];

foreach my $funcname (@allowed) {
    my $sub = sub {
        my ($self, @args) = @_;
        warn "doing $funcname on ", $self->txt, " with @args$/";
    };
    my $pkgfunc = __PACKAGE__ . "::$funcname";
    no strict 'refs';
    *$pkgfunc = $sub;
}

1;

package main;
my $b = Thing2->new("bob");
my $c = Thing2->new("ralph");

$b->findActor("quincy");
$c->addActor("mary");
$b->doActor("what?");

__END__
doing findActor on bob with quincy
doing addActor on ralph with mary
Can't locate object method "doActor" via package "Thing2" (perhaps you
+ forgot to load "Thing2"?) at ./qs.pl line 76.

    [download]
[reply]
[d/l]
[select]
      This is absolutely the thing i was after, thanks ++.
[reply]
Re: A Better way? (Lots of routines with similar structure)
by greywolf (Priest) on Jun 08, 2003 at 23:00 UTC
    Without getting into any changes to the style of your code. It seems like you could have generic sub that can do both checking and adding if you pass your name and statement as arguments. 
    sub workItem {
    my $self = shift;
    my %args = @_;

    die "Must supply value for name" unless ($args{name});
    die "Must supply value for statement" unless ($args{statement});

    my $retval = $self->_execute( handle    => 'appfme',
                                  statement => [ $args{statement} ],
                                  bindvar   => [ $args{name} ],
                                  output    => 1);

}

    [download]

    Unless I missed something in your original code this should allow to check and add actors/directors/genre etc.

    mr greywolf
[reply]
[d/l]

Back to Seekers of Perl Wisdom