Dear Gaal, Yes, I do see the point, and indeed most OO modules have a ‘new’ method. And you are right, the ‘prepare’ DBI calls belongs there!
(BTW, I do need to “burn” the ‘From_date’/’to_date’ pair in the ‘prepare’ stage! The size of the table and the existence of the key on this warrants it. I call the ‘retrieve’ sub a couple of thousand times in a single run.) Thank you very much for the effort and detail of your answer. The always helpful Gaal J Meir From: [email protected] [mailto:[email protected]] On Behalf Of Gaal Yahas Sent: יום ג 16 יולי 2013 19:11 To: Perl in Israel Subject: Re: [Israel.pm] Importing variables into a package 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
