Good idea, jeffa. It's very similar to a construct I often find myself using when i have to construct a lot of dynamic queries.

It all started when I suddenly noticed that every SQL statement follows approximately the same general pattern, which may be briefly summarized by the following BNF-like pseudocode:

keyword value [{separator} value ...]

So, for example, every keyword in a typical SELECT statement has a associated list, which lends itself very nicely to the following hash-based perl structure:

my $sql = { SELECT => [ 'column1', 'column2', 'column3' ],
            FROM   => [ 'table1', 'table2' ],
            WHERE  => [ 'condition1', 'condition2' ],
            GROUP_BY => [ 'column1', 'column2' ],
            HAVING => [ 'condition3]' ],
            ORDER_BY => [ 'column1','column2' ],
            # ... you get the idea };

# Store the separators in another hash:
my $sep = { SELECT => ',',
            FROM   => ',',
            WHERE  => ' AND ',
            HAVING => ' AND ',
            GROUP_BY => ',',
            ORDER_BY => ',' };
[download]

The code to produce a SQL string is almost trivial:

my $query = join ' ', map { join $sep->{$_}, @{$sql->{$_}} } qw(SELECT
+ FROM WHERE GROUP_BY HAVING);
[download]

I suppose one could take this a step further and dynamically generate the where clauses, but for my uses this is beyond the point of diminishing returns (it's more work than it's worth), so for me the where clauses tend to look like 'table2.column4 = "string"' or whatever (in other words the column names must be redundantly specified).

One advantage of this approach it that you can manipulate the set of SELECT columns as a list (array) until the very end when the query is generated. Very often many of the same columns appear in the GROUP BY clause as in the SELECT list. This works incredibly nicely when there is one basic query, which is then modified manifold, based upon various external attributes, and one must make sure the SELECT list remains in sync with the WHERE, GROUP BY, and HAVING clauses, etc.

Of course, in the degenerate case (no join), there is only one table (i.e., the FROM array has only one element) and no GROUP BY or HAVING clauses.

This is just the basic idea; for production quality code, more checking would have to be added along with blank elimination, etc. (Left as the proverbial exercise for the reader... :)

dmm

You can give a man a fish and feed him for a day ...
Or, you can teach him to fish and feed him for a lifetime

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.