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

In reply to A Better way? (Lots of routines with similar structure) by Ryszard

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.