Re: Automatic SQL query builder

Jan Hidders wrote:
>
> Jaroslaw Tomczak wrote:
> >
> > As a part o bigger project I develop a kind of automatic query builder.

I am also engaged in a project doing that, i.e. perl SQL::Statement which includes SQL::Parser. My SQL::Parser->parse() method breaks a SQL statement string into a data structure and the SQL::Parser->build() method does the reverse, i.e. takes a data structure and builds a SQL string from it.

> > I'm simply looking for some papers, reports, links that would relate to
> > the problem described above.

Me too, please share if you find some :-)

> If I understand your description of the input correctly you don't need
> any subqueries (and explicit joins are not necessary in SQL). In that
> case the traslation is trival: put all the mentioned tables in the
> from-clause, the select and the join-conditions in the where clause,
> the selected fields in the select-clause and the order conditions in
> the order-by-clause.
>
> So what is exactly the problem?

For use with something like a simple CGI form that provides a table name and some column names, you're right, one doesn't need to pay attention to the details. But I am attempting to do something quite a bit more complex. For example (and this example was submitted by a user with a need for it) suppose you have a series of SQL statement strings and you want to perform a regular transformation on e.g. all of the column names -- you can submit the string to parse() and get back a data structure, perform the transformation on the relevant parts of the data structure then feed that structure to build() and get back a rebuilt SQL string that contains the newly transformed information. For that, one needs to pay attention to all of the details of the syntax, including things like the difference between explicit and implicit joins (even though the end result of the join will be the same). Or should we say that the end result *might* be the same since some rdbms's will optimize joins differently depending on how they are presented.

-- 
Jeff