First and foremost, I'm not trying to abstract away SQL; DBIx::Pretty is meant to be a cleaner SQL-in-code as opposed to replacing it. That is, DBIx::Pretty is to SQL as HTML::Template (and friends) is to HTML.

Secondly, DBIx::Pretty was renamed from DBI::Pretty due to the naming convention in the DBI:: module tree; that is, only DBI and it's directly database glue should go here, any DBI extentions should go to DBIx::.

The main goal of DBIx::Pretty is to assign names to SQL statements, then be able to call those SQL statements directly through their name in an AUTOLOAD manner; in addition, there's some variable substituion that can go on in the SQL statement for placeholders as well as other variable parts of the SQL statement. You can see more details in my original code.

Now, there are two areas that I'm trying to improve on:

First is the actually extensibility. The way I will set this up is to have DBIx::Pretty, then several subclasses, the default being DBIx::Pretty::Hash. In D:P, the three key functions than any subclass should override in my current scheme are:

• get_available_sql - should return a list of stored sql function names.
• get_sql - given a name, return a sql statement.
• get_sql_properties - given a name, returns a list of properties for that SQL.

The idea is that there can be static or dynamic SQL bindings. In static cases, the SQL is loaded and preparsed, with the preparse data going into an internal cache. In dynamic cases where the SQL might change, the subclass can have, for that sql, return in get_sql_properties some flag that indicates that the sql statement should be processed, but the preprocessing data is not cache and dumped away.

The suclasses are free to define additional methods; for example, I know that D:P:Hash will also have add_sql and remove_sql calls that add to it.

The second aspect is the variable interpolation. As I have it now, there's two types of variables:

• Placeholder variables, defined by "?name". The variable is replaced with a '?' to take advantage of DBI's meta-quoting system.
• Substitution, defined by "$name". The variable is replaced with the passed value just before the statement is "Executed". No further substitution is done.

I'm thinking of adding two more types:

• SQL statement variable, defined by "&name". This replacement is done first, and would restart a parse sequence; it replaces the variable with the sql statement associated with 'name'. That is, given sql statement 'lt_age' (associated with "AGE < ?"), I could write a meta-sql statement as 'SELECT $fields FROM $table WHERE &lt_age'.
• Inclusion, defined by "#name". Similar to substitution, but once the variable has been replaced, the statement is reparsed, so that any other variables in the value passed are also parsed out.

As an example of all this working together, I could define a meta-SQL set of statements as:

query =>    SELECT $fields FROM $table WHERE #conditions
age_check => age > ?age
gender_check => gender LIKE ?gender
...
# actual call...
$dbixprettyobj -> query ( 
  { conditions=>"&age_check AND &gender_check",
    age=>18,
    gender=>'M',
    fields=> "name, id",
    table=>users
  } );
[download]

In addition, I'm considering allowing variables to have at least one addition field, that is associated with the variable in question by appending ":value" to it. The value that follows would be the default value for that variable if it is not defined in the hash that is passed to the AUTOLOAD function. So, in the above, if I has "?age:18", then if I didn't pass an age field in the hash, the code would automatically plug in 18 there.

From the coding standpoint, I know there's a lot here with regards to checking and the like (for example, avoiding infinite recursion loops in the inclusion/sql-statement variable types), but I'm more interested from here on commentary on how the user's interface is laid out. Do the variable types make sense and am I missing anything obvious that might interfere with typical SQL statements?

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain