The newest development version also supports many-to-many accessors directly (thanks to contributions from elusion) which your schema will greatly benefit from.

If you need more complex functionality, Class::DBI is the way to go, so check them both out to see which is a better fit for your goals.

Either way, with object-persistence modules like this, you can perform a query and get back a collection of objects. Then you should really be sending the results to an HTML templating system. It should be the current template that decides what fields from the objects should be shown (phone number, etc). The core application logic shouldn't have to be burdened with that. Certainly you wouldn't want a separate function in your code for each possible combination of columns to fetch/show.

• 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.

I have a module that will construct an SQL query given the following...

I took a similar approach in DBIx::SQLEngine, which accepts a very flexible set of arguments and can both generate SQL or optionally execute it for you.

When working at this higher level, the OP may find that they don't need as many subroutines -- for example, rather than having subroutines for each version of a select query based on which columns you need, your get_person_companies() subroutine could take a list of additional columns to include in the results.

Lets say we're working on a database driven mod_perl application...what is your alternative?

Mine? Well, fire up AxKit, create some XSP pages using AxKit::XSP::ESQL, and an XPathScript stylesheet.

You may also want to read ubu's excellent book on AxKit.

Thanks to all for the advice.

I will be taking a closer look at all the solutions mentioned throughout the week.