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)