Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI or adding to it

[prev] [thread] [next] [Date index for 2005/07/02]

On Sat, Jul 02, 2005 at 06:50:48AM -0400, Sebastian wrote:
> I don't mean to change CDBI or have it implement anything new, but
> perhaps that CDBI used in conjuction with SQL::Builder could lead to
> cool things. The goal of SQL::Builder is not to replace SQL with Perl,
> but to allow you to express SQL in Perl because you can gain a lot of
> control.  SQL::Builder mechanisms are intended to provide clean and
> flexible interfaces to  SQL. The examples in the article posted
> provide reasons to do this.

SQL::Abstract also provides a means to express SQL as perl data, and is
available as a plugin to Class::DBI - or better, use Class::DBI::Sweet which
has a much more powerful SQL::Abstract subclass that groks Class::DBI
internals.

> [snip simple joins using SQL::Builder]
>
> Notice that in get_b() I'm not repeating anything stated in get_a(),
> I'm only asserting what's relevant. Potentially each query is the
> composition of many method calls (let's ignore performance issues for
> now), but with each only modifying what's relevant, so we avoid
> repeating ourselves. When you're ready to retrieve the data from your
> query, $query->execute will return you a $sth or you can use wrapper
> methods to do work for you.
> 
> Tony, you said that CDBI has methods to remove the drudgery of
> repeating similar queries (think of the initial get_a() method above),
> but I don't understand how to achieve a similar affect with CDBI as I
> did with SQL::Builder above. I must be missing something.

You can't do this with Class::DBI, but Sweet will support something close
- you can happily do (assuming ::employee has_a ::office and your base
class does use base qw/Class::DBI::Sweet/ instead of Class::DBI)

my @emp = My::employee->retrieve_all({ prefetch => 'office' });

and get back an array of employee objects with their ->office value
pre-populated with the appropriate office (Sweet will have figured out
the appropriate join and performed it automatically). If you want, you can
also do (e.g.)

My::employee->default_search_attributes({ prefetch => 'office' });

to make this action the default for all queries on the class.

        -- 
             Matt S Trout           Website: http://www.shadowcatsystems.co.uk
  Technical Director        E-mail:  mst (at) shadowcatsystems.co.uk
Shadowcat Systems Ltd.