comment on

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

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.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

In reply to Re: Handling near-redundant SQL queries by dragonchild
in thread Handling near-redundant SQL queries by Anonymous Monk

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! —

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:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

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

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