Code generation logic

[Lukas Eder]

Hi Espen,

I hope you've had a nice weekend!

I have noticed that the code you wrote to generate classes for DB2 uses plain JDBC. I am aware of the fact that this is also the case for the HSQLDBTableDefinition (has to be fixed). However, the idea here should be, that jOOQ is used to create SQL statements, also for code generation. This is for three reasons:

1. Proof of concept. Reading from the various meta-data tables provided by Oracle, DB2 etc can be quite complex, especially when determining relations. It's a good way to show that jOOQ can handle these complex queries.
2. Usability testing. When expressing such complex queries, one gets to the limits of jOOQ more quickly than with integration tests. With these limits at hand, we can create new requirements for an improved jOOQ.
   
3. Testing. Integration tests may not cover all types of nested selects, etc. If complex code generation works, then the likelyhood of bugs decreases.

Could you please consider that, if you are planning to write code generation logic in the future?

Thanks,
Lukas

[Espen Strømsnes]

Greetings Lukas,

Weekend was fine. Christmas is closing up real fast....

I used plain JDBC because this was a "chicken and egg" problem. I wasn't able to use jOOQ to generate data because the java entities for the system tables did not exist... :-)

I guess one solution would be to use plain JDBC to generate the classes for the system tables, and then to use these generated java entities when processing relations, procedures, functions etc.

Espen

2010/12/20 Lukas Eder <lukas...@gmail.com>

[Lukas Eder]

Hi Espen,

Hehe, too fast, isn't it? :)

You're right about the "chicken and egg" problem. I'm aware of that. I did exactly what you are suggesting: Generate Tables/Columns with plain JDBC, replace JDBC code by jOOQ code, generate the rest. The other way is to "guess" the generated tables code and write that manually at first. That might work as well...

Cheers,
Lukas

2010/12/20 Espen Strømsnes <estro...@gmail.com>

[Espen Strømsnes]

I'm currently working on generating code for H2 procedures and functions. I'll do as you suggest and first generate code using JDBC and then replace JDBC with generated jOOQ code. I can do the same for the DB2 generator.

[Lukas Eder]

Good idea with the H2 procedures and functions. They seem to have implemented quite a funny syntax to wrap java code in stored objects:

http://www.h2database.com/html/features.html#user_defined_functions

Have fun doing that! :)

2010/12/20 Espen Strømsnes <estro...@gmail.com>

[Espen Strømsnes]

It is quite an unusual way of doing it with "alias" for both functions and procedures and the special case with the connection parameter.

I have a rough idea of how to implement this; void methods and methods returning ResultSet will be implemented as jOOQ procedures and the rest will be implemented as jOOQ functions.

[Lukas Eder]

Yeah. I've seen these kinds of "procedure aliases" before with HSQLDB: http://www.hsqldb.org/doc/guide/ch09.html#create_alias-section. I guess these Java databases all copy ideas from one another. :)

Are there any H2 meta-data tables describing the Java signature, or how will you detect that signature? I would not support methods returning ResultSet for now. Because jOOQ does not have that feature yet. Other databases allow procedures/functions to return "tables", "table types", or "cursors". They will be mapped to JDBC's ResultSet as well. An example for this can be seen here:

http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

I have created the ticket https://sourceforge.net/apps/trac/jooq/ticket/166 for this issue. If we want to start adding support for ResultSet return types and OUT parameters, then it needs to be done thoroughly. Feel free to suggest a thorough solution if you like to do that. Otherwise, let's keep it simple for the moment. The important thing here is to keep in mind that the jOOQ stored procedures features are likely to be used with Oracle, DB2 (with very sophisticated stored procedures), and rather unlikely to be used with H2...

Cheers

[Espen Strømsnes]

I have now committed code where I'm using jOOQ code to generate H2 code.
I'll do the same for DB2.

[Lukas Eder]

That's great news! Thanks a lot.

[Espen Strømsnes]

Thanks for mentioning me on the h2-database google group.

I'm glad I can help.

[Lukas Eder]

So you've found the post? :) I hoped that it's OK when I mention you. I really appreciate your help and you deserve credit for that.

2010/12/20 Espen Strømsnes <estro...@gmail.com>

[Lukas Eder]

Hi Espen,

2010/12/20 Espen Strømsnes <estro...@gmail.com>:

> It is quite an unusual way of doing it with "alias" for both functions and
> procedures and the special case with the connection parameter.
>
> I have a rough idea of how to implement this; void methods and methods
> returning ResultSet will be implemented as jOOQ procedures and the rest will
> be implemented as jOOQ functions.

For the upcoming release 1.6.2, I have now finally implemented support
for H2 stored functions returning java.sql.ResultSet. Oracle REF
CURSORs are also supported. I'll have a look around the other RDBMS to
see which ones support cursor types like Oracle and H2.

I'm really glad we waited with this. A lot of refactorings had been
done in the mean time, which made this implementation a lot easier
today than back in 2010. Feel free to have a look at the new
implementation

Cheers
Lukas