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)

Personally I don't think the SQL should be dynamically generated. It goes against everything I've learned managing heavily loaded production databases - one bad query plan and things start to look really ugly for the users...

Michael

If you are offering an interface that allows complex user-defined queries, then not dynamically generating the SQL would mean pre-defining the exploded combination of all possible queries.

I suppose this could be done programatically, but it's often easier to simply dynamically generate the SQL.