At work we have a whole infrastructure built mainly around CDBI for what regards db interface. I'm fairly new to it, but with the help of the developers here I'm managing my way through it.
I'm doing some statistics, generally involving joins, and I wrote a generic base class Newstats which accepts, as "main" parameter a subref to be used as a callback. Then I subclass it so that the typical module for a specific statistic may look like this:
# -*- Perl -*-
use strict;
use warnings;
package Newstats::Fred_by_this;
use Foo::DBI::Fred;
use base 'Newstats';
Foo::DBI::Fred->set_sql(by_this => <<".EOSQL");
SELECT this,nvl(that(*),0) as tot
FROM __TABLE(=r)__, __TABLE(Foo::DBI::Barney=n)__
WHERE status='cool'
AND r.quux=n.quux
AND (tstamp between ? AND ?)
GROUP BY this
.EOSQL
sub new {
my $rf=Foo::DBI::Fred->sql_by_this;
Newstats->new( action => sub {
my $caller=shift;
$rf->execute(@_) or
$caller->complain("Error executing sql_by_this() method.");
$rf->fetchall_arrayref;
},
other_param => 'something else' );
}
1;
__END__
(code trimmed down to a bare minimum and names changed for convenience)
Now I have many such modules and I thought: what could possibly happen if I call set_sql() on the same class and with the same $sql_name (in the terminology of the docs) as I've already done? I searched with Google, which brough up pages like Using joins - ClassDBI none of which really cleared my perplexities. So I conjured up a quick experiment and I see that CDBI doesn't complain, and I guess it just redefines the method. I would prefer the former alternative instead. I also checked the documentation, to no avail, or maybe the answer is just in front of me and I just fail to see it...
So, to make a long story short: how can I have set_sql complain if I try to redefine a method that has already been generated? Or more generally, how do I -easily- avoid doing so?
Well:
- I'm also using it for "actual objects with properties that I change and save" - elsewhere;
- indeed I had arrived at [mod://Ima::DBI] myself, actually one of the links in my first posts points to it;
- unfortunately I'm not much familiar with any of "these technologies" - but I'm slowly learning...
WRT the last point in particular: do you mean that I can use [mod://DBI] methods directly on my objects? All I know is that our "top level" class, from which all other ones do inherit is a
use base qw/Class::DBI::Oracle Class::DBI::Cacheable/;
and I find that that provides me a convenient way to access and possibly write data hiding the underlying db details, which is good since I know Perl far better than I know databases themselves.
In short: I'd prefer to keep lo and behold as far as I can get it to work. Accessing data through methods is fine and since I can have my own objects incorporate callbacks that are actually closures around suitable lexical variables, that keeps me fine as the resulting code is clear and maintainable and easy to understand and nicely "encapsulated" (not necessarily in the strict OO acceptation) for my tastes.
Yes, you can use DBI directly by getting the database handle. This can be done by calling class->db_Main(). You can use it exactly like you would use regular DBI.
sub foo
{
my $class = shift;
my $dbh = $class->db_Main();
my $sth = $dbh->prepare_cached("SELECT ...");
$sth->execute(...);
#... usual DBI stuff
}
If you then want your return to be objects from the data you retrieved, you may want to look into sth_to_objects() or construct() methods.
Regarding what you're doing with set_sql here, this is also pretty unusual. The set_sql stuff is supposed to be part of the class definition and it adds permanent class methods to your CDBI module. Changing these on the fly means you probably should be doing this part in some other way, like using DBI directly.
I don't quite understand why you're overwriting this query. Are you trying to have subclasses that all define this query differently?
Regarding what you're doing with set_sql here, this is also pretty unusual. The set_sql stuff is supposed to be part of the class definition and it adds permanent class methods to your CDBI module. Changing these on the fly means you probably should be doing this part in some other way, like using DBI directly.
I must say that, albeit in my ignorance, I had a perception that this was the case - i.e.: I didn't have the slightest idea of what the solution may have been, but I understand what is the problem.
I don't quite understand why you're overwriting this query. Are you trying to have subclasses that all define this query differently?
Indeed I'm not overwriting it. I do not want to inadvertently overwrite it. That is the problem I refer to above.
Now, your suggestion which I'm understanding more and more is to skip set_sql() altogether, since I have other ways to get the only thing that it really buys me, that avoid its side effects (and I take this to be the heart of your comment to my code). For the moment I have devised a patch. As time permits and I become more confident with this stuff, I'll modify all existing modules written in this style at once.
# in package DB::Contribition
__PACKAGE__->set_sql(average_dollars => <<'END_SQL' );
SELECT AVG(amount)
FROM contribution
END_SQL
sub average_dollars {
my $class = shift;
my $sth = $class->sql_average_dollars();
$sth->execute();
return $sth->select_val() || 0;
}