provide an interface for programmatic generation of SQL queries

Created an attachment (id=838)
First version of interface for review

The first version of the classes for programmatic generation of SQL queries for
review of design only. The classes are not tested at this time, no comments are
included and methods inherited from object (hashcode() and equals()) are not
implemented. Also test cases are missing.

Created an attachment (id=839)
UML-Diagrams of first version as zipped PNG files

I think that adding some comments on the design of this interface may help those
interested in reviewing. On the other side there are some specific questions
that I am interested to be adressed during review.

The first decision I had to take was how to support the syntax of different
database systems like MySQL, Oracle, MS-SQL. At the begining I thought of using
a factory to create the concrete classes but with this design a lot of the
classes including the factory would have been needed to be subclassed for every
database engine. According to this I decided to put all code for the database
specific generation of the SQL string into the class 'SQLBuffer'. With this
design only SQLBuffer have to be subclassed for every supported database and all
the classes that build up the SQL query tree can be declared final as long as
they are not subclassed internally.

Analysing the syntax of SQL-92 it became obvious very early that it would be
very difficult, if not impossible, to define a class hierarchy that will produce
only valid queries. Especially type checking would have caused to subclass
'Expression' to 'StringExpression', 'NumericExpression' and so on and also all
current subclasses of 'Expression' would have been needed to be implemented for
all this datatypes. And even if it had been possible to manage generation of
only valid queries for one database engine taht the same syntax will fail on
another. According to this I tried to find a compromise between the number of
classes and the possibility of generating invalid SQL with the class hierarchy.
As an example of an obviously invalid syntax, it is possible to define a number
as argument to the trim() function of SQL.

The current implementation will support a quite large subset of SQL-92. A lot of
the syntax productions of SQL-92 are guarantied to be valid according to the
class hierarchy of the implementation but there is no type checking. I see a
possibility to implement a validity check on the query tree if we deliver type
information when constructing 'Column' objects, but in my opinon, the database
engines will tell us if the query is valid or not.

At the moment all classes are public and as much as possible are immutable. Most
of them can be created in 2 ways: by their constructor and a factory method in
their superclass. E.g. you can get an instance of the class 'Trim' by using its
constructor Trim(expression) or by calling expression.trim(). If we can abandon
the public constructor (e.g. And, Or, Add, Union, ...) the number of public
classes the user have to deal with can significantly be reduced.

I thought of implementing equals() and hashcode() for all classes as they are
required if we intend to cache query results, but I am not sure with this. The
clone() methode isn't needed in my opinion. If you like to see this methods I
will implement them with taking into account that hashcode() is critical in
performance when using HashMap as a cache.

Even if the primary intention for this implementation has been the refactoring
of the OQL engine with antlr, this package can also make the life easier using
them for the current engine as an intermediat improvement.

Enough talked for the moment, its now time for questions.

What do you think about the package name and seperating the 2 sub packages
assuming that I think of third one called 'drivers'?

Do you like the class and method names?

I want the appendTo() methods to throw a NotSupportedExeption if a database
engine do not support the requested element. Do you agree?

Which of the productions like limit,offset or user functions are not supported
by all database engines?

How can we find out this not supported productions at the time we build up the
query tree? If we know them before the OQL part will create a simpler SQL query
and post process the result. I think of a small number of isXxxSupported()
methods in a database specific class but appreciate any better ideas.

Do you like the idea of formating the SQL query string with newlines and
indention for better readability?

At the end one very important thing: Don't blame me because of the missing
comments and test cases. They will follow when we have found an agreement with
the design.

> What do you think about the package name and seperating the 2 sub packages
> assuming that I think of third one called 'drivers'?

OK, this is a way to sort all those many little classes into two categories.

> Do you like the class and method names?

I can see, you don't use the get/setXxx naming scheme, but name get functions
just like the attribute. I myself don't have a problem with this.

> I want the appendTo() methods to throw a NotSupportedExeption if a database
> engine do not support the requested element. Do you agree?

OK, if you write in the exception message, what exactly is not supported.

> Which of the productions like limit,offset or user functions are not supported
> by all database engines?
> How can we find out this not supported productions at the time we build up the
> query tree? If we know them before the OQL part will create a simpler SQL
> query and post process the result. I think of a small number of isXxxSupported
> methods in a database specific class but appreciate any better ideas.

The problem with limit/offset is not only if it is supported by a database.
It's also that the syntax is different form db to db. How do you handle this?

> Do you like the idea of formating the SQL query string with newlines and
> indention for better readability?

Yes, definitly.

> At the end one very important thing: Don't blame me because of the missing
> comments and test cases. They will follow when we have found an agreement
> with the design.

OK, but I think a few examples would make it easier to see if the interface is
convenient for the purpose we want to use it. (generate SQL statements from a
parser tree)

> OK, if you write in the exception message, what exactly is not supported.

That's exactly what I thought of: the message includes a textual representation
of the not supported element.

> The problem with limit/offset is not only if it is supported by a database.
> It's also that the syntax is different form db to db. How do you handle this?

For every db we have to write a class that extends SQLBuffer (e.g. OracleBuffer,
MySQLBuffer, MSSQLBuffer, ...). SQLBuffer is only a default implementation that
generates SQL-92 syntax for toString() and test cases. If we want a SQL query
string for e.g. oracle we will call:

SQLBuffer buffer = new OracleBuffer();
query.appendTo(buffer);
String querystring = buffer.toString();

> OK, but I think a few examples would make it easier to see if the interface is
> convenient for the purpose we want to use it. (generate SQL statements from a
> parser tree)

I am writing test cases at the moment which may help a little bit to understand
how to use the interface but you are definitly right that we should also test
with a parser tree. I'll adress this after the test cases.

Just starting to read up slowly .. .

> I think of a small number of isXxxSupported
> methods in a database specific class but appreciate any better ideas.
java.sql.DatabaseMetaData.supportsLikeEscapeClause() should return the required
information. Same goes for obtaining lists of supported time and date or system
functions. I am not saying that this is the best way to go, but anything that
prevents us from having to supply numerous isXXXSupported() methods is welcome.

Created an attachment (id=925)
New version with lots of bugfixes including test cases