Re: Automatic SQL query builder

Jan Hidders wrote:
>
> Jeff Zucker wrote:
> >
> > [...] 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.
>
> Let's see if I understand you
> correctly. You have a data structure that represents SQL queries and
> you have methods/functions that parse strings into that data structure and
> methods that translate the data structure back to strings.

Right.

> And then you
> also seem to have methods that manipulate the data structure.

That's up to the user. They can dump the data structure into Perl, modify it, then send it back to the build() function to get back how their modifications are converted into a SQL string.

> Now I can
> imagine two requirements here and I'm not sure which one you mean. One
> is that the meaning of the query should remain the same, and the other
> is that the structure should always represent a valid SQL query.

Both are true, but there is a third: that the structure should maintain information about the actual syntax used in the original string. In other words, one could do this all in a way that an explicit join and an implicit join had the exact same structure so one would never know which one the original string had without re-examining the string itself, or one can put in a flag mentioning which was used. One could add a flag to the parse structure of "BETWEEN foo AND bar" to distinguish it from its equivalent with greater than and less thans. It's (mostly) true that in terms of implementing the syntax these things are all equivalent, still that's no reason to throw away information.

> Is that what you are asking?

I wasn't asking anything. I was giving an example of why one might want to retain the actual syntax used in a query rather than just its meaning. You had said in your answer to the OP that "explicit joins are not necessary in SQL" which might be true on some level, but there are other levels in which maintaining the distinction is important (e.g. some implementations optimize differentially depending on the syntax of the explicit join, or e.g. the kinds of transformations I mention above). So even if things like BETWEEN, IN, explicit joins, etc. are really just uneccessary syntactic sugar, there may be reasons to preserve them in a SQL query builder.

-- 
Jeff