[CDBI] Sharing Queries with set_sql

[prev] [thread] [next] [Date index for 2005/09/06]

Typically when using Class::DBI and needing a complex query we've  
been using the set_sql method:

   package App::DB::TableA;
   use base qw( App::DB );

   __PACKAGE__->table( 'tableA' );
   __PACkAGE__->columns( Primary => 'id' );
   __PACKAGE__->columns( TEMP => qw( field_b field_c );

   __PACKAGE__->set_sql( some_query => qq{
        select a.id, b.field_b, c.field_c
          from tableA a, tableB b, tableC c
         where a.id = b.id
           and b.id = c.id
   };

When we want to add additional queries we will add it to this file,  
and add more TEMP columns were necessary.

What we've run into is wanting to be able to share the queries  
amongst all our developers but haven't figured out a way to do this  
without defining a table and temp columns.  We would like to  
accomplish the following:

   package Shared::DB::SQL;
   use base qw( Class::DBI::Oracle );

   __PACKAGE__->set_sql( some_query => qq{
        select a.id, b.field_b, c.field_c
          from tableA a, tableB b, tableC c
         where a.id = b.id
           and b.id = c.id
   };


I've found that I can just use Ima::DBI, but the problem there is  
that I don't get back the objects that Class::DBI gives me.  We'd  
like to keep the interface the same whether we're doing a retrieve/ 
search or a call to a set_sql.  I've come up with the work-around  
below to get the data back the way I want.  I would just add this  
method to Shared::DB::SQL:

   sub run_sql {
     my ( $class, $method, @args ) = @_;
     my $sql_method = 'sql_' . $method;
     my $sth = $class->$sql_method;
     $sth->execute( @args );
     my @recs = $sth->fetchall_hash;
     $class->columns( TEMP => ( keys %{ $recs[0] } ) );
     return $class->sth_to_objects( \@recs );
   }


Has anyone else run into this problem and come up with a better/ 
different solution?

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi