The point of my presentation will not be to generate all SQL on the fly (though it's often good to do as much of this as possible), but to decouple the application from the the database. For example, imagine that you want to fetch a list of all customers along with their order numbers, if they've ever placed an order. How might this be implemented?

sub order_numbers {
    # assume, for the sake of argument, that order numbers
    # are optional
    my $class = shift;
    my $sql = <<'    END_SQL';
    SELECT c.last_name, c.first_name. o.order_num
    FROM   customers c
    LEFT OUTER JOIN orders o ON c.customer_id = o.cu_id
    END_SQL
    return $class->_fetch_records($sql);
}
[download]

That looks reasonable enough, but what happens if you (for example) want to rename orders.cu_id? Every place in your code that this is embedded will break. Here's a different approach.

sub order_numbers {
    my $class = shift;

    my $customers = $class->_table;
    my $orders    = Order->_table;

    my %cust  = map { $_ => $class->_column($_) }
        qw{ first_name last_name customer_id };
    my %order = map { $_ => Order->_column($_) }
        qw{ order_num cust_id customer_id };

    my $sql = <<"    END_SQL";
    SELECT $customers.$cust{last_name}, $customers.$cust{first_name}
           $orders.$order{order_num}
    FROM   $customers
    LEFT OUTER JOIN $orders 
        ON $customers.$cust{customer_id} = $orders.$order{customer_id}
    END_SQL
    return $class->_fetch_records($sql);
}
[download]

Now, you can change your column and table names at will, but you don't break your code so long as you update your field map in the object that handles the appropriate table. Admittedly, this is much more work, but it's more robust and the bulk of the SQL can still be auto-generated. In my experience, the amount of work you save by abstracting out the rest of your SQL easily offsets messes like the above snippet.

Naturally, since I expect to see a full test suite with all of that, I'll still catch the errors, if any, in said suite.

Cheers,
Ovid

New address of my CGI Course.
Silence is Evil (feel free to copy and distribute widely - note copyright text)

I'm not convinced that this extra abstraction is worth the trouble. Readability definitely suffers, and the ability to hand SQL to a DBA for help is hampered. (You have to evaluate the code and then dump the SQL out first.) More importantly, any non-trivial change to your data structures will still break everything. This only protects from changes in names.

I do have to agree about the readiblity. I suppose one could adjust the _column method to automatically prepend the table name. This could reduce the SQL to something more clear.

    SELECT $last_name, $first_name, $order_number
    FROM   $customers
    LEFT OUTER JOIN $orders ON $customer_id = $o_customer_id
[download]

That is much cleaner, but you are correct about handing it to a DBA :( Perhaps creating an SQL repository that dynamically creates SQL and caches the results, but also allows for easy dumping of the generated SQL? That sounds workable, but I wonder if the added complexity offsets the added work.

In any event, you are correct about this not handling changes in the structure and that's a point that I need to stress in my talk, but hadn't really considered.

Cheers,
Ovid

New address of my CGI Course.
Silence is Evil (feel free to copy and distribute widely - note copyright text)

I've used the join facility in Alzabo for only trivial things but so far it handles things ok.

I've just altered the node and added an example from my own code where I use Alzabo's join facility. You'll see it right inside the while() loop.

sub display_address {
    my $self = shift;

    my $tmpl   = $self->load_tmpl( 'display_address.tmpl' );
    my $schema = $self->param( 'schema' );
    my $q      = $self->query;

    # Retrieve the parameters
    my $street = $q->param( 'street' );
    my $house  = $q->param( 'house' );

    my @people;
    {
        my $roster = $schema->Roster;
        my $history = $schema->History;
        my $roster_fk = $history->RosterFk;
        my $election = $schema->Election;
        my $election_date = $election->Date;
        my $election_desc = $election->Desc;

        my $people = $roster->rows_where(
            where => [ [ $roster->StreetFk,    '=', $street ],
                       [ $roster->HouseNumber, '=', $house ] ],
            order_by => [ $roster->Name ] );

        # Retrieve the result set into an array
        while (my $person = $people->next) {
            my @votes = $schema->join(
                join     => [ $history, $election ],
                select   => [ $election ],
                where    => [ $roster_fk, '=', $person->SosId ],
                order_by => [ $election_date, $election_desc ],
                )->all_rows;

            # Fetch the values from the selected data
            @votes = map $_->Date.': '.$_->Desc, @votes;

            push @people, { History => join("<br />",@votes),
                            Name => $person->Name };
        }
    }

    $tmpl->param('people', \ @people);

    return $tmpl->output;
}
[download]

Alzabo also does outer joins pretty simply. At least, it's simple in terms of scaling up from the complexity of a non-outer join example. There's still that initial learning curve and all ;)

my $cursor =
    $schema->join
        ( select => [ $table1, $table2 ],
          join   =>
          [ [ left_outer_join => $table1 => $table2 ],
            [ $table2 => $table3 ],
          ],
          ...
        );
[download]

The current CVS version also adds support for specifying join conditions as in this SQL:

  SELECT ... FROM Foo
       OUTER JOIN Bar on Bar.foo_id = Foo.foo_id AND
                  Bar.size > 2
  WHERE ...
[download]

The Alzabo syntax for it is a bit ugly so far though.