[Dbix-class] SQL::Builder propaganda review

[Sam Vilain]

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.