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
