[Dbix-class] SQL::Builder propaganda review

[Sebastian]

Howdy,

> 1. add HAVING

Currently there is HAVING support. SQL::Builder::Having. It's
available through SQL::Builder::Select, too

> 2. the ability to specify complex joins

Currently there is SQL::Builder::JoinGroup which would help with this,
but of course it's likely to change at some point because I'm not too
happy with how I've been dealing with JOINs so far.

>  3. a richer abstraction of partial queries

The SQL::Builder::Select object is relatively small because most of
its functionality relies on other modules representing smaller pieces
of SQL. SQL::Builder::Select is comprised of:

SQL::Builder::Distinct;
SQL::Builder::FromList;
SQL::Builder::Where;
SQL::Builder::GroupBy;
SQL::Builder::Having;
SQL::Builder::OrderBy;
SQL::Builder::Limit;

Most of which break down further to the Table, Column, ColumnList,
Join, et al... Point being it should be possible to represent partial
queries and basic SQL constructs without a fuss and hopefully the
finished API will make it easy to manipulate/create others.

Logically reusing views is a major goal. There are still a bit of work
to be done about how this can be done intelligently, though.

>    Of course only someone writing ORMs would really prefer such a
>   syntax over the Domain Specific Language for this task, SQL.  I'm
>   also assuming a Tangram::Expr-style simplification of writing
>   complex expression ASTs using overloading.  And only someone writing
>   a high level tool like Tangram would be interested in many of the
>   features of an API like that.

I think something like you suggest would have a broader application
rather than ORM writers. Even in non-ORM systems it'd be nice to alter
an existing view to return some aggregate function results and
slightly tweak a WHERE condition. I'm in favor of a
nice/abstract/overloaded interface as long as it's not a required part
of the API. I'd like to build a solid foundation, then apply a syntax.
Hopefully it'll be more flexible in the long run


I was wondering when someone would reply to my post!

- sebastian


On 10/25/05, Sam Vilain <sam at vilain.net> wrote:
> On Sat, 2005-10-22 at 04:43 -0700, Sebastian wrote:
> > Hi,
> >
> > For those that don't know, SQL::Builder is a collection of modules for
> > SQL manipulation. It's not "yet another SQL abstraction module" or ORM
> > so please read some of what I say before flaming. Currently it's under
> > development, but it's on its way. My next release (0.03alpha) next
> > week will have documentation and more complete functionality. The
> > source is available at
> > http://unf.be/~sili/projects/sqlbuilder/SQL-Builder/
> >
> > Anyhow, I've been revising the documentation and would like to get
> > some feedback on the following bit of propaganda extracted from
> > SQL::Builder's pod. Thanks in advance
>
> This looks like a good start, and is something I'll need when I shave
> off the dreadlocks of hairy code inside Tangram's internals, and
> SQL::Abstract is just not enough to express all that you can with Tangram.
>
> Let's go over some "corner cases" I don't think are easily described in
> your API.
>
>  1. add HAVING
>
>     I see GROUP BY, but no HAVING (which is how you filter on aggregated
>     columns without a subselect).
>
>  2. the ability to specify complex joins
>
>     In essence, listing multiple tables and "where" conditions that just
>     happen to form a 1:X or 1:1 relation between the selected tables, is
>     a nasty hack way to perform an explicit JOIN.  It's a nasty hack
>     because the query optimiser has to figure it out from your query, or
>     else fall back to cartesian product calculation.
>
>     This is very important if you're doing complicated joins; say you
>     have two tables, which need to be joined together to get the full
>     "object".  Then, you do an outer join on those.  This is a nested
>     join.
>
>     Here's the example; I have two tables, "Person", and
>     "NaturalPerson", which are joined on ID.  I want to find all the
>     people who either have no parent on file, or whose parent is older
>     than 72.  We assume that "age" is a property of "Person", and
>     "parent" is a property of "NaturalPerson".  Oh, and person 1 needs
>      to have a name starting with "M".
>
>     I would write;
>
>       select
>           p1.id,
>           p2.id    -- etc
>       from
>           (Person p1
>              inner join NaturalPerson np1
>              on (np1.id = p1.id))
>           left join
>           (Person p2
>              inner join NaturalPerson np2
>              on (np2.id = p2.id))
>           on ( np2.id = np1.parent_id and
>                p2.age > 72 )
>       where
>           p1.name like 'M%'
>
>  3. a richer abstraction of partial queries
>
>     Note that each selection of data from a table is a view, and joining
>     another table on (or making a cartesian product, for that matter)
>     makes another view.  I want to be able to use these views and
>     logically combine them together to make subsequent views, and
>     wherever possible, I want to avoid the necessity to do that with a
>     sub-select - as most optimisers out there don't optimise out
>     sub-selects to joins everywhere that they could.
>
>     So, perhaps if you start with a logical element of a view, and make
>     sure that these views can be represented cleanly without resorting
>     to the "select()" function, then you can write select() in terms of
>     it.
>
>     In essence you have a heirarchy, each level builds on the one above
>     it:
>
>         FROM     - (tables or views)
>         T1.X,... - (expressions of tables or views)
>         JOIN     - (joins between tables or views)
>         WHERE    - (filtered views)
>         GROUP BY - (aggregations)
>         HAVING   - (filtered aggregations)
>         (SELECT) - (sub-select)
>
>     Of course any element in an expression may be a very complicated
>     object.  Sub-selects may be used for tables in queries, as well as
>     in expressions.
>
>     So with such a system, the above query might look something like;
>
>      my ($p1, $p2, $np1, $np2) = table( ("Person") x 2,
>                                         ("NaturalPerson") x 2 );
>
>      my $person1 = $p1->join($np1, $p1->{id} == $np1->{id});
>      my $person2 = $p2->join($np2, $p2->{id} == $np2->{id});
>
>      my $outer_join = $person1->left_join
>                          ($person2,
>                           ( ($person2->{id} == $person1->{parent_id}) &
>                             ($person2->{age} > 72) ) );
>
>      my $final_view = $outer_join->where($person1->{name}->like("M%"));
>
>      my $select_statement = $final_view->select
>                          ($person1->{id}, $person2->{id});
>
>     Of course only someone writing ORMs would really prefer such a
>     syntax over the Domain Specific Language for this task, SQL.  I'm
>     also assuming a Tangram::Expr-style simplification of writing
>     complex expression ASTs using overloading.  And only someone writing
>     a high level tool like Tangram would be interested in many of the
>     features of an API like that.
>
> Is this a direction you'd like to move SQL::Builder in ?
>
> Sam.
>
>


--