blazar has asked for the wisdom of the Perl Monks concerning the following question:

<premise>

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__

[download]

(code trimmed down to a bare minimum and names changed for convenience)

</premise>

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?

Replies are listed 'Best First'.
Re: [CDBI] avoiding set_sql() redefinitions
by perrin (Chancellor) on Jan 05, 2006 at 16:00 UTC
    This is a very unusual use of Class::DBI. You seem to be using it for nothing more than holding the database connection and the queries. If you don't plan to use it for actual objects with properties that you change and save, there is no good reason to use it. Just use DBI's connect_cached and prepare_cached instead, or use Ima::DBI, which is the module that Class::DBI uses to do all of the funtionality you are using here.
[reply]

      Well:

      1. I'm also using it for "actual objects with properties that I change and save" - elsewhere;
      2. indeed I had arrived at Ima::DBI myself, actually one of the links in my first posts points to it;
      3. 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 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/;

      [download]

      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.

[reply]
[d/l]
        I was saying you could use connect_cached and prepare_cached to do everything shown in your example. If you make use of Class::DBI in the normal way elsewhere, that wouldn't be a good solution.

        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?

[reply]

        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
}

        [download]

        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.

[reply]
[d/l]

Back to Seekers of Perl Wisdom