The mechanics of Perl exporting and the issues of database efficiency are
really separate things. In this case, export is probably not what you need
anyway.
Instead, offer a more object-oriented interface, with no exported functions
at all.
Your client code might look like this:
use My::Storage;
my $fetcher = My::Storage->new(
from_date => $FROM_DATE, to_date => $TO_DATE);
# The fetcher object manages its own state, remembering from and to dates.
# A single query:
$data = $fetcher->retrieve("Tom")
How does this work? When you call My::Storage->new, you're constructing an
object (usually, a Perl hash) that has some state. Then when you call a
method with that object, all that state is available to the method code. If
you do all this stuff in pure Perl 5, not with Moose.pm,
My::Storage::retrieve will probably look something like this (warning, I am
not a DBI expert):
sub retrieve {
my($self, $who) = @_;
$self->{sth}->execute($self->{from_date}, $self->{to_date}, $who);
my $row = $self->{sth}->fetchrow_arrayref;
# check error
return $row;
}
I'm glossing over the DBI details here, but notice that I did not hardcode
the from and to dates. Unless you have evidence that this slows down your
queries substantially, I suggest you do the same. But if you decide you do
need to bake in the dates into the prepared statement, you can do that too,
because you know them at the time you run prepare, which is during
My::Storage::new.
sub new {
my($proto, @params) = @_;
# it's been a while since I did this stuff. You should check the perl oop
docs to see I
# didn't screw this up. Or maybe let Moose do the oop for you.
my $class = ref $proto // $proto;
my $self = bless {}, $class;
# okay, enough boilerplate. Let's initialize the fetcher.
$self->{dbh} = get a database hanlder from somewhere
$self->{sth} = $self->{dbh}->prepare(q(
SELECT a, b, FROM t WHERE entry_date between ? AND ? AND person = ?));
# remember error checking, too.
return $self;
}
On Tue, Jul 16, 2013 at 4:43 PM, Meir Guttman <[email protected]> wrote:
> Dear Perlers,****
>
> ** **
>
> I am going through my first steps of using packages. Here is another
> problem I encountered.****
>
> ** **
>
> 'my_table' is a <i>humongous</i> DB table, having many millions of rows,
> and many keys on it. In particular, two field-keys are relevant, on the
> `person` field and on the `entry_date` one.****
>
> ** **
>
> I often need to retrieve a small subset of the table's data, in this
> example for a list of `person` and for a range of dates. The range of dates
> is the same for all persons. I then apply some processing for the data
> retrieved. The (non-executable) code below is an attempt to represent the
> problem.****
>
> ** **
>
> Since the range of dates is the same, I would like to 'prepare' the SQL
> query with the two dates already built-in, in the hope that the 'prepare'
> stage will optimize access, using the date's field key. But keep in mind
> that these dates are by no means constants. They vary for each run.****
>
> ** **
>
> I would like to place the 'prepare' statement in the same package as the
> 'retrieve' sub as I did here, both because these belong with each other and
> because I don't want to force the many users to write their own 'prepare'
> code and pass the objects to the 'retrieve' sub.****
>
> ** **
>
> I just learned how to EXPORT entities from a package to the calling
> script's name space. But here I need to <i>import</i> two scalars, and make
> the DBI prepare statement execute <i>at run time</i>, well after many
> initiating steps were carried out, in particular getting command-line
> arguments and placing them in program variables.****
>
> ** **
>
> Well, how do I achieve that?****
>
> ** **
>
> <code>****
>
> package My::Package;****
>
> ** **
>
> use Exporter;****
>
> @ISA = qw(Exporter);****
>
> @EXPORT_OK = qw(retrieve);****
>
> ** **
>
> my $sth = $dbh->prepare(****
>
> qq(SELECT `data1`, `data2` FROM `my_table`****
>
> WHERE `entry_date` between '$from' AND '$to'****
>
> AND `person` = ?****
>
> )****
>
> # `entry_date` and `person` have keys on them.****
>
> );****
>
> ** **
>
> sub (retrieve){****
>
> my $person = shift;****
>
> ** **
>
> return my $table_aref = $dbh->selectall_arrayref****
>
> ($sth, {Slice=>{} }, ($my_key));****
>
> # With such an invocation, 'selectall_arrayref' returns a reference****
>
> # to an array of hashes, where hash keys are the 'SELECT'-ed entities***
> *
>
> # that could be SQL aliases.****
>
> }****
>
> ** **
>
> ----- End of file here ---****
>
> ** **
>
> # Main program:****
>
> ** **
>
> use My::Package qw(retrieve);****
>
> ** **
>
> my $oldest_date = EXPR; # Given say as a command-line option****
>
> my $most_recent_date = EXPR; # Ditto!****
>
> ** **
>
> my @persons = qw(Dick Tom Harry);****
>
> ** **
>
> foreach my $i (0 .. $#persons){****
>
> my $table_data_aref = retrieve($persons[$i]);****
>
> ****
>
> # pretty-print @$table_data_aref or whatever...****
>
> }****
>
> ** **
>
> </code>****
>
> _______________________________________________
> Perl mailing list
> [email protected]
> http://mail.perl.org.il/mailman/listinfo/perl
>
--
Gaal Yahas <[email protected]>
http://gaal.livejournal.com/
_______________________________________________
Perl mailing list
[email protected]
http://mail.perl.org.il/mailman/listinfo/perl
