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]

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.

"It provides a variety of tools to allow
people to abstract their SQL and remove the drudgery of repeatedly doing
similar queries."

The goal of SQL::Builder is to do the same thing. Suppose I have function g=
et_a:

sub get_a {
  #do stuff with CDBI classes and build a query. essentially, we end up wit=
h:

 return "
    SELECT *
    FROM employees=20
    JOIN offices ON offices.office_id =3D employees.office_id
 "
}

Now suppose I now need a very similar function. The only difference is
that I need to join employee_attendance table. How could I reuse the
code in get_a? If get_a is returning SQL or a result, this is not
possible, but if have something like:

sub get_a {
=09# note that all functions return objects, not strings
        # ideally we would use class functions defined by CDBI to
access table/column names and whatnot
=09my $query =3D SQL::Builder::Select->new();
=09$query->add_table('employees');
=09$query->add_join(
=09=09sql_join('offices', sql_op('=3D', 'offices.office_id', 'employees.loc=
ation_id'))
=09);
=09$query->cols("*");
=09
=09return $query;
}

then I could add my employee_attedance join with as little work as:

sub get_b=09{
        # again, all functions return objects and ideally we'd be
using CDBI class data methods
=09my $query =3D get_a();
=09$query->add_join(
=09=09sql_join('employee_attendance', sql_op(qw(=3D
employee_attendance.employee_id employees.employee_id)))
=09);
=09
=09return $query;

=09#or if you're happy with it:
=09#return $query->execute
}

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.

- sebastian


On 7/2/05, Tony Bowden <tony-cdbitalk@xxxxx.xxx> wrote:
> On Sat, Jul 02, 2005 at 04:36:57AM -0400, Sebastian wrote:
> > I think that mixing CDBI and something like the solution given at
> > http://groups-beta.google.com/group/comp.lang.perl.modules/browse_threa=
d/thread/266e35238f3d5df6
> > could lead to some really cool things. Please have a look there and at
> > the links provided in the post and provide some feedback!
>=20
> Class::DBI already provides quite a few ways to set up your SQL as class
> data, and to vary it depending on needs, whether through the simple
> sprintf style substitutions, or through more complex transform_sql()
> transformations.
>=20
> What the links you've posted appear to be doing is falling back into the
> trap of trying to replace SQL with Perl - something I've always said
> that I don't want Class::DBI to do.
>=20
> Class::DBI is for people who like SQL and think in SQL - it's not an
> attempt to shield people from it. It provides a variety of tools to allow
> people to abstract their SQL and remove the drudgery of repeatedly doing
> similar queries. But it certainly does not attempt to remove the need for
> writing SQL, not think that removing it is even a valid or sensible goal.
>=20
> Tony
>=20


--=20



-------------
buddhahead@xxxxx.xxx