• A list of select clauses
• A list of where clauses (including all the bind parameters)
• A list of groupby clauses
• A list of parameters to pass to $dbh->execute()

It does so by doing the following:

• It extracts all the table names from the select, where, and groupby clauses
• It takes a list of all the tables in your database and how they're connected (you supply this ahead of time.)
• Using the list of connections, it builds the SQL query for you and returns it for usage by the caller.

Usage is something like:

my $sql = $builder->build_sql(
    select => [
        "foo.column AS blah",
        "foo.column2 AS blah2",
        "bar.column AS bar",
    ],
    where => [
        "foo.column3 = ?",
        "bar.column2 = ?",
    ],
    params => [
        3, 5,
    ],
);

## This would generate something like:
SELECT foo.column AS blah
      ,foo.column2 AS blah2
      ,bar.column AS bar
  FROM foo
       JOIN table1 ON (foo.foo = table1.foo)
       JOIN bar ON (table1.bar = bar.bar)
 WHERE foo.column3 = ?
   AND bar.column2 = ?
[download]

Additionally, if you have selectively denormalized your schema for speed purposes, this algorithm would take that into account. (I have, because it's a readonly reporting database. Other possibilities would be for an OLTP database that uses triggers to maintain certain denormalizations.)

Is this something that would solve your problem? I haven't released it yet, but could in a week or two. (I planned on doing so anyways, but could do so as early as the end of next week ...)

Note: This module doesn't do anything with the database. It's actually a text manipulation module that has some graph theory in the backend. So, it's completely RDBMS-agnostic. And, as you can tell, it produces SQL99-compliant queries. It also supports OUTER JOINs, if needed. I use this module to construct queries for both Oracle9i and MySQL 4.1.x.