[REQUEST FOR FEEDBACK] New jOOQ code generator: What do you want it to be?

[Lukas Eder]

Dear group,

The jOOQ code generator is one of the biggest assets when using jOOQ. It allows for referring to your schema in a type safe way, allowing to leverage IDE auto completion with tables, columns, procedures, etc.

However, it has grown organically and it thus doesn't allow for the extensibility that some of you would like. It is time to start thinking about the future of jOOQ-codegen and jOOQ-meta, to collect ideas for jOOQ 4.0 in that area, or perhaps even for prior releases. So, this is a good time to chime in and discuss potential requirements.

The relevant issue is:

https://github.com/jOOQ/jOOQ/issues/3883

These are, roughly, the existing features, or existing feature requests:

1. Target languages and forms

jOOQ currently supports Java and Scala - the most popular languages on the JVM. Does it make sense to support other languages? Or perhaps even data formats, like XML?

This is an interesting topic, but it is also very demanding in terms of complexity budget. Alternative languages cannot be integration tested as well as Java without adding substantial effort.

2. Templating language

The code generator currently uses a home-grown templating "language" via a home-grown, internal, undocumented API.

In the past, users have suggested using Xtend (http://www.eclipse.org/xtend) instead. The advantage of Xtend is that the template language is built into the Xtend language, and it feels extremely natural.

At the time, the idea of integrating Xtend was rejected, because the Xtend tooling was very Eclipse dependent. This is no longer true, and Xtend is still a very interesting consideration.

Alternatively, a programmatic language model (like XJC has) could be interesting, although it would be very limiting in terms of what's possible in 1. Target languages.

3. Style

Programmers like endless discussions about curly braces, indentations, tabs and spaces. jOOQ currently doesn't embrace this sort of "creativity" :-)

It would be great if the generated code style could be influenced in one way or another, although, this is a rather low priority.

4. Generator strategies

Most customisation is about naming style. Do you want your database objects in PascalCase? camelCase? UPPER_CASE? lower_case?

Is this feature sufficient? What's missing here?

5. Disambiguation / compiler "optimisations"

jOOQ code compiles almost always. We've thought of many edge-cases where generated code might cause conflict, e.g. because of naming ambiguity, invalid identifiers, etc. This is a must-have in any future implementation.

6. Custom code

Currently, there are a few ad-hoc means of introducing custom code into generated artefacts. This is mostly done via method extension, which is rather limiting.

Recently, there has been quite a bit of criticism about the generated DAOs, and the fact that they're not really useful for use with Spring or CDI. That's true, but we don't want to patch them little by little, adding not well thought through features at this point.

Instead, YOU should be able to generate DAOs (or repositories, or services, or session beans) very easily yourself, using jOOQ-meta and a better jOOQ-codegen. YOU should then be able to publish your jOOQ code generation "plugins" for reuse in the community.

Other use-cases are:

5.1. Support for additional validation annotations

5.2. Support for additional Spring or Java EE annotations

5.3. Support for JPA annotations

5.4. Support for fluent setters

5.5. Support for alternative object types (e.g. repositories, services, etc.)

5.6. Mutable / immutable POJOs.

5.7. "Views" expressed in jOOQ (i.e. SQL strings that should generate as org.jooq.Table)

In fact, I believe that the core code generation functionality should be built upon such a plugin system, rather than providing tons of flags to turn on/off individual features

Be a part of it

I'm looking forward very much to your feedback, and enthusiast discussion!

Lukas

[Bill O'Neil]

Target languages and forms

I think adding target forms could be drastically more useful than just more languages. Adding more languages as you stated will be a lot of overhead and cause even more endless debates about language specific best practices and code style. However being able to represent your entire database in JSON is something I think could be very useful and language agnostic. This JSON could include all the raw table names as well as generated values like display names / class names.

Templating language

After working with both Slick code generation and JOOQ code generation I have been curious why no one has tried to use an HTML templating library. It is code that generates code dynamically and already has tons of useful built in features such as includes / partials / conditionals / loops. I would be interested to try and take a JSON representation of the database and run it through HTML templates and see how natural it feels. I think an added benefit would be the ability to add hooks into the code generation wherever you wanted by just modifying / customizing a few html templates.

Style

Its generated code if I can read it without too much effort I don't care what it looks like. The apis should be fairly self explanatory so you shouldn't need to be digging into the generated code all that frequently. I personally have all of my generated code pushed to a src/generated/java directory so its explicitly clear you should never touch any code in that directory.

 

Generator strategies

I have found JOOQs current offerings plenty sufficient so far but it never hurts to have additional pluggabe strategies. 

Disambiguation / compiler "optimisations"

This is fairly critical also keep in mind this can get more and more complex if you decide to support more languages. 

Custom code

I have had a pretty good experience customizing the JOOQ DAO generators on my own. I was able to auto generate some additional queries that I would use frequently. Getting the code in the right places was sometimes difficult. I felt somewhat limited by the hooks to add custom code. At the same time I don't think adding additional hooks is the right solution. You could end up with tons of before / after hooks for every section of code and that could get out of hand quickly.

I like the idea of a simplistic plugin system to allow users to pick / choose features and customize at will. That could also be quite complicated to write / test / maintain with tons of possible combinations. Maybe have a JOOQ standard that is well maintained and the ability for contributors to maintain their own plugins for anyone who is not happy with your approach?

It might be interesting to pick a subset of all the features you want and we can throw together some proof of concepts to see what works well and how they compare.

Bill

[Lukas Eder]

Thanks a lot for your feedback, Bill.

I will comment on a few items already now:

Templating language

After working with both Slick code generation and JOOQ code generation I have been curious why no one has tried to use an HTML templating library. It is code that generates code dynamically and already has tons of useful built in features such as includes / partials / conditionals / loops. I would be interested to try and take a JSON representation of the database and run it through HTML templates and see how natural it feels. I think an added benefit would be the ability to add hooks into the code generation wherever you wanted by just modifying / customizing a few html templates.

Interesting. I hadn't thought about HTML templating libraries. In the Java ecosystem, velocity used to be a popular templating language. But as most templating languages are external DSLs, they are equally flawed compared to Xtend in that they can't really interact well with Java APIs (e.g. with jOOQ-meta)

Xtend on the other hand is a compiled language with built-in templating capabilities and excellent IDE support:

https://eclipse.org/xtend/documentation/203_xtend_expressions.html#templates

I think it will be hard to beat Xtend in this area, although I'm very open to suggestions...

Custom code

I have had a pretty good experience customizing the JOOQ DAO generators on my own. I was able to auto generate some additional queries that I would use frequently. Getting the code in the right places was sometimes difficult. I felt somewhat limited by the hooks to add custom code. At the same time I don't think adding additional hooks is the right solution. You could end up with tons of before / after hooks for every section of code and that could get out of hand quickly.

Interesting! Would you mind sharing an example of such a method / query? I'm curious to see if a common use-case might be hidden, here.

[onei...@gmail.com]

Interesting. I hadn't thought about HTML templating libraries. In the Java ecosystem, velocity used to be a popular templating language. But as most templating languages are external DSLs, they are equally flawed compared to Xtend in that they can't really interact well with Java APIs (e.g. with jOOQ-meta)

I would probably pick something bare bones like mustache for the HTML templating language. I think there are a few pros and cons. It could make code gen language agnostic, most people already know HTML / HTML templating, code customization could be trivial if set up correctly. The major cons would be no expressions and no type safety which I agree would be very nice. This was just a thought I had when working with the code generators.

I think it will be hard to beat Xtend in this area, although I'm very open to suggestions...

Xtend looks like it could work well. Scala's multi line strings / interpolation also isn't bad for adding expressions mid template.

I believe I posted some of it a long time ago I'll try to get an example later. Basically I added a way to easily add multiple where conditions using a builder.

new UserDao(ctx).where().firstName("first").lastName("last").age(27)

I believe at the time you suggested exposing a way to just pass in a Condition quickly without some of the boilerplate.

t is the user table object

new UserDao(ctx).where(t -> t.FIRST_NAME.eq("first").and(t.LAST_NAME.eq("last")).and(t.AGE.between(min, max)))

Something along those lines. It was just a quick way to skip a few lines of boilerplate .selectFrom's and mapping the objects back to pojos.

[Samir Faci]

Inline comments heavily influenced by my own uses.

On Tue, Dec 29, 2015 at 5:27 AM, Lukas Eder <lukas...@gmail.com> wrote:

Dear group,

The jOOQ code generator is one of the biggest assets when using jOOQ. It allows for referring to your schema in a type safe way, allowing to leverage IDE auto completion with tables, columns, procedures, etc.

However, it has grown organically and it thus doesn't allow for the extensibility that some of you would like. It is time to start thinking about the future of jOOQ-codegen and jOOQ-meta, to collect ideas for jOOQ 4.0 in that area, or perhaps even for prior releases. So, this is a good time to chime in and discuss potential requirements.

The relevant issue is:

https://github.com/jOOQ/jOOQ/issues/3883

These are, roughly, the existing features, or existing feature requests:

1. Target languages and forms

jOOQ currently supports Java and Scala - the most popular languages on the JVM. Does it make sense to support other languages? Or perhaps even data formats, like XML?

This is an interesting topic, but it is also very demanding in terms of complexity budget. Alternative languages cannot be integration tested as well as Java without adding substantial effort.

That's fine for me.  Closure would be nice, but those are two main JVM languages I'm really interested in.  You could leverage things like protobuff to support more languages but honestly probably not worth the effort.  Not sure how XML would come into play.  My favorite feature of Jooq is that I can point it to a database and generate source of truth so that the code reflects the data.  XML (if we're using xml for data definition) would be a step backward, in my view.  To each his own though.

 

2. Templating language

The code generator currently uses a home-grown templating "language" via a home-grown, internal, undocumented API.

In the past, users have suggested using Xtend (http://www.eclipse.org/xtend) instead. The advantage of Xtend is that the template language is built into the Xtend language, and it feels extremely natural.

At the time, the idea of integrating Xtend was rejected, because the Xtend tooling was very Eclipse dependent. This is no longer true, and Xtend is still a very interesting consideration.

Alternatively, a programmatic language model (like XJC has) could be interesting, although it would be very limiting in terms of what's possible in 1. Target languages.

Aside from ease of use internally, is there any advantage to using any of these.  As a simple user of Jooq, I don't think I would need to really interact with the particular templating unless you want to allow the user to override the default auto-gen code. 

 

3. Style

Programmers like endless discussions about curly braces, indentations, tabs and spaces. jOOQ currently doesn't embrace this sort of "creativity" :-)

It would be great if the generated code style could be influenced in one way or another, although, this is a rather low priority.

Irrelevant for autogen code IMO.  Just follow the standard Oracle standard.  

 

4. Generator strategies

Most customization is about naming style. Do you want your database objects in PascalCase? camelCase? UPPER_CASE? lower_case?

Is this feature sufficient? What's missing here?

I'm not sure if this is a Jooq specific or some library that Jooq is using .. but this does annoy me a good bit.  

For example, I would have something like  

//Query Code        final ClientListing listing = dslContext.select(
                CLIENT.ID,
                CLIENT.NAME,
                CLIENT.PARENT_ID
        )
                .from(CLIENT)
                .where(CLIENT.ID.eq(id))
                .fetchOneInto(ClientListing.class);//Bean Construction code below.

    @ConstructorProperties({
            "id",
            "name",
            "parentId"
    })
    public ClientListing(final long id, final String name, final Long parentId) {
        this.id = id; 
        this.name = name; 
        this.parentId = parentId;
    }

​

The fields 'magically' being converted from   PARENT_ID to parentId is obnoxious.  If the table names are auto-generated as parent_id then my constructor options should match the DB field names.  There's something lost in translation where I'm retrieving a field named parent_id but the value intercept by the bean is parentId.

Mainly my own petpeave....

 

5. Disambiguation / compiler "optimisations"

jOOQ code compiles almost always. We've thought of many edge-cases where generated code might cause conflict, e.g. because of naming ambiguity, invalid identifiers, etc. This is a must-have in any future implementation.

I'm not sure how this could be addressed but I thought I'd point it out.

we're using postgres and have multiple schemas, and in certain cases we operate on the same table on multiple schemas.  

Since we can't import both tables in the same class we end up with these odd inner classes.

    // Define Jooq Tables from the UI Schema
    private static class UI {
        protected static final
        com.foobar.package.ui.tables.Client CLIENT =
                com.foobar.package.ui.Tables.CLIENT;

     } 

    private static class DATA {
        protected static final
        com.foobar.package.data.tables.Client CLIENT =
                com.foobar.package.data.Tables.CLIENT;

     }

​

If you have a suggestion on a better pattern to use let me know, but it would be nice if the compiler took some override parameter to say add a prefix to the table name.  so then I could just naturally export the UI_CLIENT table.  

 

6. Custom code

Currently, there are a few ad-hoc means of introducing custom code into generated artefacts. This is mostly done via method extension, which is rather limiting.

Recently, there has been quite a bit of criticism about the generated DAOs, and the fact that they're not really useful for use with Spring or CDI. That's true, but we don't want to patch them little by little, adding not well thought through features at this point.

Instead, YOU should be able to generate DAOs (or repositories, or services, or session beans) very easily yourself, using jOOQ-meta and a better jOOQ-codegen. YOU should then be able to publish your jOOQ code generation "plugins" for reuse in the community.

Other use-cases are:

5.1. Support for additional validation annotations

5.2. Support for additional Spring or Java EE annotations

5.3. Support for JPA annotations

5.4. Support for fluent setters

5.5. Support for alternative object types (e.g. repositories, services, etc.)

5.6. Mutable / immutable POJOs.

5.7. "Views" expressed in jOOQ (i.e. SQL strings that should generate as org.jooq.Table)

In fact, I believe that the core code generation functionality should be built upon such a plugin system, rather than providing tons of flags to turn on/off individual features

Be a part of it

I'm looking forward very much to your feedback, and enthusiast discussion!

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thank you
Samir Faci

[Ben Hood]

Hi Lukas,

Please find comments about your listed items inline.

On Tue, Dec 29, 2015 at 11:27 AM, Lukas Eder <lukas...@gmail.com> wrote:
> 1. Target languages and forms

> 2. Tempting language

I think that (1) and (2) are intrinsically linked and hence are
difficult to treat separately.

For one thing, the code generator needs to not only produce source
that will compile or interpret given an arbitrary
compiler/interpreter, it also needs to conform to an ABI for the
runtime library of the given target language. For different language
frontons on the same platform (e.g. Scala and Java) you can re-target
more easily because you could re-use an single JOOQ runtime library.
Once you start targeting non-JVM stuff, then you either need port the
runtime or somehow transmogrify it (either by static code translation
or by some kind of foreign function invocation back to the original
JVM runtime).

You could take a half way house approach and define the semantics of
the ABI, but then, how would be able to verify that a given
implementation is protocol compliant. Sounds like you'd need a big
test suite. And then once you have that, you still need to make sure
your templates generate valid front end code, both for the target
language and the backend API.

That all said, maybe I've completely misunderstood JOOQ. In my mind
JOOQ essentially provides type safe SQL access, provided you are using
JDBC. Are there any non-JDBC JOOQ backends that can help invalidate my
assumption?

> 3. Style

Code style for generated code is of lesser importance to me.

> 4. Generator strategies

Sounds like you're looking for a pluggable strategy abstraction that
can visit DB things and return a string?

> 5. Disambiguation / compiler "optimizations"

I've run into naming conflicts with legacy schemas before (e.g. DDL
with column names that differ just by case sensitivity alone, for
which JOOQ has produced non-compiling code. That said, I was actually
glad that JOOQ blew up in this way, since it provided an easy check
that something was very wrong with the schema that we were hacking
into. In this situation I started by using the code generator config
to help me avoid the troublesome table, so in the first instance there
was no JOOQ access to this table. Then when I needed to read/write
to/from it, I created a view on the broken table that JOOQ would
compile against.

> 6. Custom code

> Currently, there are a few ad-hoc means of introducing custom code into
> generated artefacts. This is mostly done via method extension, which is
> rather limiting.

I've had to hook into the JavaGenerator class to override a method to
get the generator to play ball with the custom type binding I had
cooked up. But IMHO this could have been circumvented by allowing
application level access to the SQL type indexer in the compiler,
which is currently sealed off to members of the general public. So I
ended up hacking the generator just to achieve the effect of
registering a custom type.

I think maybe most of the questions about custom code relate to DAO
construction? If so, I'll have to back out of the discussion because I
haven't used the DAO side of JOOQ.

Cheers,

Ben

[Lukas Eder]

2015-12-29 16:42 GMT+01:00 <onei...@gmail.com>:

Interesting. I hadn't thought about HTML templating libraries. In the Java ecosystem, velocity used to be a popular templating language. But as most templating languages are external DSLs, they are equally flawed compared to Xtend in that they can't really interact well with Java APIs (e.g. with jOOQ-meta)

I would probably pick something bare bones like mustache for the HTML templating language. I think there are a few pros and cons. It could make code gen language agnostic, most people already know HTML / HTML templating, code customization could be trivial if set up correctly. The major cons would be no expressions and no type safety which I agree would be very nice. This was just a thought I had when working with the code generators.

I think it will be hard to beat Xtend in this area, although I'm very open to suggestions...

Xtend looks like it could work well. Scala's multi line strings / interpolation also isn't bad for adding expressions mid template.

Interesting thoughts, thanks. Indeed, the advantage of something extremely simple like mustache or any other HTML templating language is the ease of customization. That's actually the advantage of an external templating language DSL, as opposed to internal "DSLs" like the current approach, or Xtend/Scala

I think that it might be worth thinking about this more deeply in the context of the possible modularisation via plugins of the code generator. If code generation responsibilities can be delegated to individual plugins, the plugin could use any technology that can be invoked from a Java (i.e. JVM) process, given some input parameters (schema metadata and code generator configuration).

Between Xtend and Scala, I would definitely prefer Xtend for this case, as it is the only language that got indentation right in multiline strings. I.e. it is easy to format both the template AND the output in a single go. It's a bit hard to explain in words, you'll have to try. The idea is that Xtend automatically detects if whitespace is meant to be used to format the template or the output. Scala uses a trick via library support, but I'm not convinced.

I believe I posted some of it a long time ago I'll try to get an example later. Basically I added a way to easily add multiple where conditions using a builder.

new UserDao(ctx).where().firstName("first").lastName("last").age(27) 

I believe at the time you suggested exposing a way to just pass in a Condition quickly without some of the boilerplate.

t is the user table object

new UserDao(ctx).where(t -> t.FIRST_NAME.eq("first").and(t.LAST_NAME.eq("last")).and(t.AGE.between(min, max)))

Something along those lines. It was just a quick way to skip a few lines of boilerplate .selectFrom's and mapping the objects back to pojos.

I see, interesting. I think I remember now.

I agree that simple filtering is a very common use-case with DAOs (or repositories, or services, or whatever they're called). It's certainly worth thinking about this more deeply.

 

[Lukas Eder]

Thank you very much, Samir, for your feedback.

2015-12-30 3:12 GMT+01:00 Samir Faci <sa...@esamir.com>:

1. Target languages and forms

jOOQ currently supports Java and Scala - the most popular languages on the JVM. Does it make sense to support other languages? Or perhaps even data formats, like XML?

This is an interesting topic, but it is also very demanding in terms of complexity budget. Alternative languages cannot be integration tested as well as Java without adding substantial effort.

That's fine for me.  Closure would be nice, but those are two main JVM languages I'm really interested in.  You could leverage things like protobuff to support more languages but honestly probably not worth the effort.

Interesting input about Protobuf. There are indeed language agnostic model formats out there, but using those would drastically limit what is possible in output code. E.g., some things need to be static final, others need to be initialised in constructors, etc. I think it would be hard to implement all the requirements with a non-text-based code generation format.

 

Not sure how XML would come into play.  My favorite feature of Jooq is that I can point it to a database and generate source of truth so that the code reflects the data.  XML (if we're using xml for data definition) would be a step backward, in my view.  To each his own though.

The idea is to be able to dump intermediary formats in XML or JSON, which can be loaded back into the code generator in a second go, or which don't have anything to do with code generation at all. This certainly isn't for everybody, but it's a use-case that has been around time and again. If the code generator is to be re-designed, then these use-cases can certainly be covered.

2. Templating language

The code generator currently uses a home-grown templating "language" via a home-grown, internal, undocumented API.

In the past, users have suggested using Xtend (http://www.eclipse.org/xtend) instead. The advantage of Xtend is that the template language is built into the Xtend language, and it feels extremely natural.

At the time, the idea of integrating Xtend was rejected, because the Xtend tooling was very Eclipse dependent. This is no longer true, and Xtend is still a very interesting consideration.

Alternatively, a programmatic language model (like XJC has) could be interesting, although it would be very limiting in terms of what's possible in 1. Target languages.

Aside from ease of use internally, is there any advantage to using any of these.  As a simple user of Jooq, I don't think I would need to really interact with the particular templating unless you want to allow the user to override the default auto-gen code.

Exactly. And that does happen from time to time.

Also: Given that a plugin system might be implemented where users can generate arbitrary artifacts that play with jOOQ (or are independent of jOOQ but have to do with the database meta data), it would be great to provide a better framework to build upon. One use case is a much improved generator for DAOs (or repositories or EJB/CDI artifacts). Another use-case that I have just recently discussed with a customer is to be able to generate JavaScript "types", which model jOOQ's UpdatableRecords on the client and which can be used for serialisation / deserialisation via REST / JSON. We don't want to offer this out-of-the-box (probably). But it would be great if it were easy to do for jOOQ users.

3. Style

Programmers like endless discussions about curly braces, indentations, tabs and spaces. jOOQ currently doesn't embrace this sort of "creativity" :-)

It would be great if the generated code style could be influenced in one way or another, although, this is a rather low priority.

Irrelevant for autogen code IMO.  Just follow the standard Oracle standard.  

Good point with the Oracle standard.

4. Generator strategies

Most customization is about naming style. Do you want your database objects in PascalCase? camelCase? UPPER_CASE? lower_case?

Is this feature sufficient? What's missing here?

I'm not sure if this is a Jooq specific or some library that Jooq is using .. but this does annoy me a good bit.  

For example, I would have something like  [...]

The fields 'magically' being converted from   PARENT_ID to parentId is obnoxious.  If the table names are auto-generated as parent_id then my constructor options should match the DB field names.  There's something lost in translation where I'm retrieving a field named parent_id but the value intercept by the bean is parentId.

I perfectly understand, which is why this customisation is already possible. The question is whether this is easy enough, and whether it is complete.

Both models are perfectly reasonable: Conversion to adhere to JavaBeans (the default today), which is also what most people do when using JPA, or keeping names as they are reported by the database (the default in jOOQ 4.0, see https://github.com/jOOQ/jOOQ/issues/3570)

5. Disambiguation / compiler "optimisations"

jOOQ code compiles almost always. We've thought of many edge-cases where generated code might cause conflict, e.g. because of naming ambiguity, invalid identifiers, etc. This is a must-have in any future implementation.

I'm not sure how this could be addressed but I thought I'd point it out.

we're using postgres and have multiple schemas, and in certain cases we operate on the same table on multiple schemas.  

Since we can't import both tables in the same class we end up with these odd inner classes.

    // Define Jooq Tables from the UI Schema
    private static class UI {
        protected static final
        com.foobar.package.ui.tables.Client CLIENT =
                com.foobar.package.ui.Tables.CLIENT;

     } 

    private static class DATA {
        protected static final
        com.foobar.package.data.tables.Client CLIENT =
                com.foobar.package.data.Tables.CLIENT;

     }

​

If you have a suggestion on a better pattern to use let me know, but it would be nice if the compiler took some override parameter to say add a prefix to the table name.  so then I could just naturally export the UI_CLIENT table.

I'm glad we talked :) Again, this (the prefixing) can be done already today:

- http://www.jooq.org/doc/latest/manual/code-generation/codegen-generatorstrategy

- http://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy

The question is: Is the current functionality sufficient?

But I really like the idea of static nested classes. This could be done in the new generated Catalog object that will be available from jOOQ 3.8 onwards. In the case of SQL Server, we would have to go even one step further and add a nested class for catalogs, perhaps.

Other than nested classes, the existing mechanism that tables are using could be reused, using a static schema reference and provide instance table references from the schema instance.

In any case, we'll address this issue in jOOQ 3.8:

https://github.com/jOOQ/jOOQ/issues/4869

Thank you again very much for your useful insight. This already helped a lot getting into the right direction.

Best Regards,

Lukas

[Lukas Eder]

Thank you very much, Ben, for your insight!

2015-12-30 3:33 GMT+01:00 Ben Hood <0x6e...@gmail.com>:

On Tue, Dec 29, 2015 at 11:27 AM, Lukas Eder <lukas...@gmail.com> wrote:
> 1. Target languages and forms
> 2. Tempting language

I think that (1) and (2) are intrinsically linked and hence are
difficult to treat separately.

You're right!

 

For one thing, the code generator needs to not only produce source
that will compile or interpret given an arbitrary
compiler/interpreter, it also needs to conform to an ABI for the
runtime library of the given target language. For different language
frontons on the same platform (e.g. Scala and Java) you can re-target
more easily because you could re-use an single JOOQ runtime library.
Once you start targeting non-JVM stuff, then you either need port the
runtime or somehow transmogrify it (either by static code translation
or by some kind of foreign function invocation back to the original
JVM runtime).

You could take a half way house approach and define the semantics of
the ABI, but then, how would be able to verify that a given
implementation is protocol compliant. Sounds like you'd need a big
test suite. And then once you have that, you still need to make sure
your templates generate valid front end code, both for the target
language and the backend API.

That all said, maybe I've completely misunderstood JOOQ. In my mind
JOOQ essentially provides type safe SQL access, provided you are using
JDBC. Are there any non-JDBC JOOQ backends that can help invalidate my
assumption?

No. Maybe in jOOQ 5.0 :-)

But I don't think it will be easy to "transmogrify" jOOQ into other platforms. I'd love to build jOOQ for C++ as it would add great value there, and there's little competition. But C++ templates are a turing complete beast of their own. Knowing how to leverage them for an internal DSL (that works with all compilers) is a lot of work.

But these are very valid points. I think that a text-based template language (what we're doing today, in a way) is the way forward because it doesn't close any future doors.

> 4. Generator strategies

Sounds like you're looking for a pluggable strategy abstraction that
can visit DB things and return a string?

We have that, but are they good enough?

 

> 5. Disambiguation / compiler "optimizations"

I've run into naming conflicts with legacy schemas before (e.g. DDL
with column names that differ just by case sensitivity alone, for
which JOOQ has produced non-compiling code.

True: https://github.com/jOOQ/jOOQ/issues/4702

 

That said, I was actually
glad that JOOQ blew up in this way, since it provided an easy check
that something was very wrong with the schema that we were hacking
into. In this situation I started by using the code generator config
to help me avoid the troublesome table, so in the first instance there
was no JOOQ access to this table. Then when I needed to read/write
to/from it, I created a view on the broken table that JOOQ would
compile against.

I see, interesting. This is probably the first time that not the vendor but the user claimed that a bug is really a feature ;-)

> 6. Custom code

> Currently, there are a few ad-hoc means of introducing custom code into
> generated artefacts. This is mostly done via method extension, which is
> rather limiting.

I've had to hook into the JavaGenerator class to override a method to
get the generator to play ball with the custom type binding I had
cooked up. But IMHO this could have been circumvented by allowing
application level access to the SQL type indexer in the compiler,
which is currently sealed off to members of the general public. So I
ended up hacking the generator just to achieve the effect of
registering a custom type.

Interesting feedback. So, were you really missing something from the runtime library or from the code generator?

The interesting point here is that a better custom code API should also allow for easier workarounds like yours, not just easier additional features.

 

I think maybe most of the questions about custom code relate to DAO
construction? If so, I'll have to back out of the discussion because I
haven't used the DAO side of JOOQ.

Well, recent feedback about DAOs have convinced me to finally have this discussion on the list, but there are other items that users like to customise more heavily than DAOs.

[Witold Szczerba]

Hi,

since we have a special chance to have our say :) I would like to mention about the thing which makes a little trouble now. Let's say I have a class WalletId. It's just a wrapper around String, VARCHAR in SQL. The trouble is, if I want to do a custom mapping for some columns in tables to use WalletId, I have to split the project to have two artifacts. So, instead of one POM, we need now 3: one for the WalletId class, second for the rest of the project and third, a parent for those two. In Microservices, when all the modules are really small, it is even more annoying.

Question is: is it possible to mix the process of code generation with the project's compilation phase, so we could get the auto-generated code to use the types from that module itself?

Other things on my hypothetical wish list would be to support non-JDBC drivers. As we know the JDBC is a blocking API, but there are alternative drivers for some databases using async techniques. There is a lack of standardization in this area, but in my imaginary, perfect world :) the projects like jOOQ could try to change the JVM world in this area! OK, I was just thinking loudly :) 

I won't even mention support for Clojure. Such a feature would cost a lot of time and I guess it would be doable by a 3rd parties if such a desire would emerge.

Thanks,

Witold Szczerba

--

[Lukas Eder]

Hi Witold,

Thank you very much for your feedback!

2015-12-30 15:46 GMT+01:00 Witold Szczerba <witold...@gmail.com>:

Hi,

since we have a special chance to have our say :) I would like to mention about the thing which makes a little trouble now. Let's say I have a class WalletId. It's just a wrapper around String, VARCHAR in SQL. The trouble is, if I want to do a custom mapping for some columns in tables to use WalletId, I have to split the project to have two artifacts. So, instead of one POM, we need now 3: one for the WalletId class, second for the rest of the project and third, a parent for those two. In Microservices, when all the modules are really small, it is even more annoying.

Question is: is it possible to mix the process of code generation with the project's compilation phase, so we could get the auto-generated code to use the types from that module itself?

Sure, those types don't need to be compilation-ready while you generate your code. Unless, of course, you want to load the types in your own, custom code generator enhancement. But the code generator config only refers to types as fully qualified names (strings), so there's no dependency.

The correct Maven phase for this would be generate-sources, or generate-test-sources.

Or am I misunderstanding something?

 

Other things on my hypothetical wish list would be to support non-JDBC drivers. As we know the JDBC is a blocking API, but there are alternative drivers for some databases using async techniques. There is a lack of standardization in this area, but in my imaginary, perfect world :) the projects like jOOQ could try to change the JVM world in this area! OK, I was just thinking loudly :) 

This is on my wishlist, too :-) But it doesn't have anything to do with the code generator.

 

I won't even mention support for Clojure. Such a feature would cost a lot of time and I guess it would be doable by a 3rd parties if such a desire would emerge.

Why not? It might be worth looking into. There is Suricatta, a third-party library that builds on top of jOOQ. Perhaps, the jOOQ code generator can be enhanced to also generate Clojure artefacts. 

[Ben Hood]

On Wed, Dec 30, 2015 at 12:34 PM, Lukas Eder <lukas...@gmail.com> wrote:
> No. Maybe in jOOQ 5.0 :-)

OK, cool - then this means that is fair to say that JOOQ is a JDBC
tool. If that is the case I would like to see less assumptions being
made by the JOOQ runtime about how an app wants to interact with the
DB with a transaction. But in fairness, my comment is a bit off topic
- this thread is about the code generator, not the runtime.

> Interesting feedback. So, were you really missing something from the runtime
> library or from the code generator?

When I mentioned type index I was talking about the private maps
TYPES_BY_NAME, TYPES_BY_TYPE and TYPES_BY_SQL_DATATYPE which are all
immutable to the third party code during code generation and at
runtime. What I'd like to be able to do is to write into these maps so
that

(a) I don't have to override JavaGenerator on a per-column basis to
shadow the output of these immutable indexes

(b) When I generate field references for complex queries at runtime,
that the DSL.field() call can be aware of my custom type - this is
another instance where the immutable type map leads to bogus SQL being
generated at runtime.

[Witold Szczerba]

See my comments in-line:

On Wed, Dec 30, 2015 at 6:16 PM, Lukas Eder <lukas...@gmail.com> wrote:

Hi Witold,

Thank you very much for your feedback!

2015-12-30 15:46 GMT+01:00 Witold Szczerba <witold...@gmail.com>:

Hi,

since we have a special chance to have our say :) I would like to mention about the thing which makes a little trouble now. Let's say I have a class WalletId. It's just a wrapper around String, VARCHAR in SQL. The trouble is, if I want to do a custom mapping for some columns in tables to use WalletId, I have to split the project to have two artifacts. So, instead of one POM, we need now 3: one for the WalletId class, second for the rest of the project and third, a parent for those two. In Microservices, when all the modules are really small, it is even more annoying.

Question is: is it possible to mix the process of code generation with the project's compilation phase, so we could get the auto-generated code to use the types from that module itself?

Sure, those types don't need to be compilation-ready while you generate your code. Unless, of course, you want to load the types in your own, custom code generator enhancement. But the code generator config only refers to types as fully qualified names (strings), so there's no dependency.

The correct Maven phase for this would be generate-sources, or generate-test-sources.

Or am I misunderstanding something?

Well, maybe I am misunderstanding something :)

You say, that as of now, there is no need to create two separate artifacts? Let's say I have a project with two classes:

WalletId and some MessageConsumer (it's just an example). MessageConsumer is intercepting a message and doing an SQL operation using code generated by jOOQ. This code, generated by jOOQ is supposed to use WalletId, so I customize the codegen mapper. Now codegen mapper wants WalletId class, but it is not available (it's not on any classpath), because the project was not build yet. On the other hand, the project cannot build, because it uses code generated by mapper.

My current knowledge is that, as of now, I have to split my project to separate WalletId from MessageConsumer. At least this is how it was when I was starting using jOOQ few years ago. Has it changed?

 

 

Other things on my hypothetical wish list would be to support non-JDBC drivers. As we know the JDBC is a blocking API, but there are alternative drivers for some databases using async techniques. There is a lack of standardization in this area, but in my imaginary, perfect world :) the projects like jOOQ could try to change the JVM world in this area! OK, I was just thinking loudly :) 

This is on my wishlist, too :-) But it doesn't have anything to do with the code generator.

Well, yes. It was not my intention to mix that with code generator "story" described earlier. 

 

I won't even mention support for Clojure. Such a feature would cost a lot of time and I guess it would be doable by a 3rd parties if such a desire would emerge.

Why not? It might be worth looking into. There is Suricatta, a third-party library that builds on top of jOOQ. Perhaps, the jOOQ code generator can be enhanced to also generate Clojure artefacts. 

My biggest concern is that Clojure is a dynamic typing language and, this is only my initial though, it might not benefit that much from using code generator. Maybe I am wrong, because I am just at the very beginning of my "Clojure journey" :) I am still learning basics. It has a power, though! Those immutable (and only immutable, with no excuses) data structures are so... cool!

Of course, jOOQ is not only a code generator. It's just a very nice API. It's JDBC as it meant to be!

Thanks,

Witold Szczerba

[Ben Hood]

On Wed, Dec 30, 2015 at 5:16 PM, Lukas Eder <lukas...@gmail.com> wrote:
> The correct Maven phase for this would be generate-sources, or
> generate-test-sources.
>
> Or am I misunderstanding something?

Depending on the level of customization I need to do with the code
generation (e.g. weaving in custom bindings), I tend to split a flat
Maven project into a multi-POM project so that the chicken and egg
dependency resolution (i.e. app code depends on generated code that
depends on custom code) is solved by each project producing a JAR for
the next consumer.

It's not elegant, but it is sometimes more pragmatic than fighting the
Maven lifecycle.

[RIT]

4. Generator strategies

Most customisation is about naming style. Do you want your database objects in PascalCase? camelCase? UPPER_CASE? lower_case?

Is this feature sufficient? What's missing here?

I know it was raised a year or two back, but it would be nice to be able to write

create.SELECT   (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
      .FROM     (AUTHOR)
      .JOIN     (BOOK).ON(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
      .WHERE    (BOOK.LANGUAGE.eq("DE"))
      .AND      (BOOK.PUBLISHED.gt(date("2008-01-01")))
      .GROUP_BY (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .HAVING   (count().gt(5))
      .ORDER_BY (AUTHOR.LAST_NAME.asc().nullsFirst())
      .LIMIT    (2)
      .OFFSET   (1)

I know its not very Java like, but uppercase is still the SQL way of writing statements. Also _ for spaces is clear than medial capitals (camelCase) if we are able to step away from Java 'standards'.

 My use of JOOQ is still very much that of a DBA doing some coding and so I would like the mapping between SQL scripts and Java code to be as near as possible.

(The spacing is just how I pad out SQL statements as it works for me)

[RIT]

4. Generator strategies

Most customisation is about naming style. Do you want your database objects in PascalCase? camelCase? UPPER_CASE? lower_case?

Is this feature sufficient? What's missing here?

[Lukas Eder]

Hi Ben,

2015-12-30 20:16 GMT+01:00 Ben Hood <0x6e...@gmail.com>:

On Wed, Dec 30, 2015 at 12:34 PM, Lukas Eder <lukas...@gmail.com> wrote:
> No. Maybe in jOOQ 5.0 :-)

OK, cool - then this means that is fair to say that JOOQ is a JDBC
tool.

Right now, yes it is. jOOQ doesn't hide JDBC and is semantically very close to JDBC, apart from different defaults being applied when it comes to lazy or eager fetching / closing resources. This is also described here:

http://www.jooq.org/doc/latest/manual/sql-execution/comparison-with-jdbc/

 

If that is the case I would like to see less assumptions being
made by the JOOQ runtime about how an app wants to interact with the
DB with a transaction. But in fairness, my comment is a bit off topic
- this thread is about the code generator, not the runtime.

Yes, the thread will become a bit confusing, but I'm curious about your feedback anyway. Perhaps start a new thread? I think we still have some spare threads left on Google Groups ;-)

> Interesting feedback. So, were you really missing something from the runtime
> library or from the code generator?

When I mentioned type index I was talking about the private maps
TYPES_BY_NAME, TYPES_BY_TYPE and TYPES_BY_SQL_DATATYPE which are all
immutable to the third party code during code generation and at
runtime.

Oh I see, yes. There is some bad design in the data type implementation from very early on. This definitely needs to be improved in jOOQ 4.0

 

What I'd like to be able to do is to write into these maps so
that

It's better not to open up access to these maps and let you depend on the existing, bad design. We'd rather figure out what would be a better design.

(a) I don't have to override JavaGenerator on a per-column basis to
shadow the output of these immutable indexes

 

If I understand this correctly, we're still discussing the workaround that you implemented and that you'd like to improve. But what is the *use-case*? What are you trying to achieve and why doesn't it work right now?

(b) When I generate field references for complex queries at runtime,
that the DSL.field() call can be aware of my custom type - this is
another instance where the immutable type map leads to bogus SQL being
generated at runtime.

You can. Write DSL.field("...", SQLDataType.VARCHAR.asConvertedDataType(bindingOrConverter))

I'm sensing that we'll spawn off another discussion here about (b), that is about improving this API...? (as it isn't really related to the code generator) 

[Lukas Eder]

2015-12-30 20:16 GMT+01:00 Witold Szczerba <witold...@gmail.com>:

See my comments in-line:

On Wed, Dec 30, 2015 at 6:16 PM, Lukas Eder <lukas...@gmail.com> wrote:

Hi Witold,

Thank you very much for your feedback!

2015-12-30 15:46 GMT+01:00 Witold Szczerba <witold...@gmail.com>:

Hi,

since we have a special chance to have our say :) I would like to mention about the thing which makes a little trouble now. Let's say I have a class WalletId. It's just a wrapper around String, VARCHAR in SQL. The trouble is, if I want to do a custom mapping for some columns in tables to use WalletId, I have to split the project to have two artifacts. So, instead of one POM, we need now 3: one for the WalletId class, second for the rest of the project and third, a parent for those two. In Microservices, when all the modules are really small, it is even more annoying.

Question is: is it possible to mix the process of code generation with the project's compilation phase, so we could get the auto-generated code to use the types from that module itself?

Sure, those types don't need to be compilation-ready while you generate your code. Unless, of course, you want to load the types in your own, custom code generator enhancement. But the code generator config only refers to types as fully qualified names (strings), so there's no dependency.

The correct Maven phase for this would be generate-sources, or generate-test-sources.

Or am I misunderstanding something?

Well, maybe I am misunderstanding something :)

You say, that as of now, there is no need to create two separate artifacts? Let's say I have a project with two classes:

WalletId and some MessageConsumer (it's just an example). MessageConsumer is intercepting a message and doing an SQL operation using code generated by jOOQ. This code, generated by jOOQ is supposed to use WalletId, so I customize the codegen mapper. Now codegen mapper wants WalletId class, but it is not available (it's not on any classpath), because the project was not build yet. On the other hand, the project cannot build, because it uses code generated by mapper.

My current knowledge is that, as of now, I have to split my project to separate WalletId from MessageConsumer. At least this is how it was when I was starting using jOOQ few years ago. Has it changed?

I'm assuming here that my claim "unless, of course, you want to load the types in your own, custom code generator enhancement" applies. If your customised "codegen mapper" really needs to *load* the WalletId class, then you'll need more Maven projects / modules. At least one for your custom mapper, and another one to pre-compile the WalletId class. I think there's nothing jOOQ could do here, that's how Maven's build lifecycle works - see also Ben's comment.

Well, yes. It was not my intention to mix that with code generator "story" described earlier.

No worries, there are existing discussions on the user group about async/reactive SQL where you could join in, or you start a new thread. I'm always willing to discuss ideas!

My biggest concern is that Clojure is a dynamic typing language and, this is only my initial though, it might not benefit that much from using code generator. Maybe I am wrong, because I am just at the very beginning of my "Clojure journey" :) I am still learning basics. It has a power, though! Those immutable (and only immutable, with no excuses) data structures are so... cool!

Of course, jOOQ is not only a code generator. It's just a very nice API. It's JDBC as it meant to be!

Well, even if the target language is a dynamically typed language, jOOQ's runtime library still offers lots of functionality - e.g. SQL dialect abstraction, SQL transformation capabilities (e.g. for row-level security, or multi tenancy), ease of building dynamic SQL, etc. We do have users that use jOOQ with Nashorn, for instance.

It would be interesting to learn more about how jOOQ's mutable Query types, Record types, etc. work with Clojure, though. And how/if that could be dealt with in the code generator, perhaps.

[Lukas Eder]

Hi Roger, thank you very much for chiming in and for your feedback!

Hah! :) Yes, I do remember.

In the context of code generation, this might fit in as we would clearly need to have an API code generator for this, not a metadata code generator. It would be quite nice to have, perhaps even as an independent commercial product, for internal DSL generation, but I'm currently not 100% convinced that the cost/benefit ratio would outshine that of alternative things we could do... Perhaps, I'm missing an easy way forward with such a project, though?

[RIT]

In terms of an "easy way forward", none that I can see and I i know that your knowledge of what can be done in the java space is far greater than mine.

Well maybe a topic for 5.x then :)

[Lukas Eder]

2015-12-31 16:48 GMT+01:00 RIT <r...@pm-mail.co.uk>:

In terms of an "easy way forward", none that I can see and I i know that your knowledge of what can be done in the java space is far greater than mine.

Well maybe a topic for 5.x then :)

That seems to evolve into quite a handy excuse, this version 5.x ;-)

Happy New Year!

Lukas 

[joseph...@gmail.com]

Hi

I'm brand new to jOOQ, so maybe I'll be saying stupid stuff, don't hesitate to tell me if so.

Anyway, after having played with it various points came to me:

A - my need is for a brand new use case, without existing tables. To start from scratch is currently annoying. I need to create the tables and the like, using string constants for names, and then generate the model, and then update my tables creation script with the constants in the generated classes. Yet I'm coming from Java and, actually, some Java model of my domain. As such, it would be nice if, somehow, I could describe my tables and then have the pojo generated and as well a creation script. So basically I'm all for JPA annotations support, or something else if needs be (I'm unsure JPA "philosophy" fits with jOOQ "all of SQL" concept, but then one could complete the edge case on his own).

B - even without going as far as A, when the model is generated from DB it would be nice to have the creation script created too.

C - immutable POJO: I really like the peace of mind immutable objects provide. It would be awesome if I could tell the generator to create immutable pojos only.
 
Regarding your others questions :
1 - target languages, forms
IMHO the generated code is tightly coupled to the DSL itself. So to me it feels like the question is more about the whole lib than just the pojo. Still, being written in Java, it should be usable from most of the others JVM languages. Maybe going for an immutable approach would help for integration with Clojure or Scala code, but once again then is about the whole public API then, which is quite bigger from the generator itself.

For the others points, well, can't tell much apart from the obvious, so I'll skip ;)

Thanks a lot for jOOQ and hopefully it wasn't all nonsense!

Happy new year

best
joseph

[Ben Hood]

On Thu, Dec 31, 2015 at 9:14 AM, Lukas Eder <lukas...@gmail.com> wrote:
> Yes, the thread will become a bit confusing, but I'm curious about your
> feedback anyway. Perhaps start a new thread? I think we still have some
> spare threads left on Google Groups ;-)

OK, I've done this.

> If I understand this correctly, we're still discussing the workaround that
> you implemented and that you'd like to improve. But what is the *use-case*?
> What are you trying to achieve and why doesn't it work right now?

The use case and workaround is described in the thread titled "Oracle
TIMESTAMP in UTC".

>> (b) When I generate field references for complex queries at runtime,
>> that the DSL.field() call can be aware of my custom type - this is
>> another instance where the immutable type map leads to bogus SQL being
>> generated at runtime.
>
>
> You can. Write DSL.field("...",
> SQLDataType.VARCHAR.asConvertedDataType(bindingOrConverter))
>
> I'm sensing that we'll spawn off another discussion here about (b), that is
> about improving this API...? (as it isn't really related to the code
> generator)

I wasn't aware of the asConvertedDataType(..) API - I'll have to check
this out (and maybe report back in a separate thread).

[Lukas Eder]

Hi Joseph,

Thank you very much for chiming in and providing feedback. It's always good to hear the opinion and insight from a new user. You're not yet biased by months of using jOOQ, and even if you might be missing something "obvious", it shows that it is obvious only for those accustomed to using jOOQ (so, e.g. the manual could be improved).

I'm commenting inline:

2016-01-03 23:53 GMT+01:00 <joseph...@gmail.com>:

Hi

I'm brand new to jOOQ, so maybe I'll be saying stupid stuff, don't hesitate to tell me if so.

Anyway, after having played with it various points came to me:

A - my need is for a brand new use case, without existing tables. To start from scratch is currently annoying. I need to create the tables and the like, using string constants for names, and then generate the model, and then update my tables creation script with the constants in the generated classes. Yet I'm coming from Java and, actually, some Java model of my domain. As such, it would be nice if, somehow, I could describe my tables and then have the pojo generated and as well a creation script. So basically I'm all for JPA annotations support, or something else if needs be (I'm unsure JPA "philosophy" fits with jOOQ "all of SQL" concept, but then one could complete the edge case on his own).

There are two ways to look at this.

1. From the dogmatic jOOQ philosophy point of view

JPA / Hibernate have won a lot in popularity precisely because they offer this "I-will-care-about-the-schema-details-later" approach. This is great for prototyping and (arguably) for the very beginning of a project. It's obviously the complete opposite of a good idea if you have a more complex domain, a pre-existing schema (e.g. you went to production already), or specific storage requirements.

From my personal experience, the latter is inevitable, so I tend to say that the little extra effort of writing correct DDL and to think about migrations from the very beginning of a project is an investment that pays off very quickly.

From a jOOQ perspective, it doesn't make sense to go the other way round, i.e. from POJOs to DDL, simply because POJOs are extremely primitive things, whereas DDL can be very sophisticated, and even the generated catalog / schema / table / column information is only a small subset of what you want to model in your database. You would lose on a lot of features (or conversely, we would have a lot of effort in re-implementing this part of JPA).

2. From a pragmatic "time-to-market" point of view

After the inevitable evangelism and dogma on this user group ;-) I can certainly see how advancing a project faster can be useful.

So, if this is really the way to go for you right now, you could annotate your POJOs with JPA annotations, generate the DDL using Hibernate, and generate jOOQ artefacts using jOOQ. For this, you can use the org.jooq.util.jpa.JPADatabase from jOOQ-meta-extensions. I just realised that this isn't really documented. We'll fix this:

https://github.com/jOOQ/jOOQ/issues/4892

The idea is described here. Vlad is the "developer advocate" at Red Hat for Hibernate. I'm sure, he'll appreciate your feedback:

http://vladmihalcea.com/2013/12/06/jooq-facts-from-jpa-annotations-to-jooq-table-mappings

Essentially, you'll have to configure the code generator like this:

<database>

    <name>org.jooq.util.jpa.JPADatabase</name>

    <properties>

        <property>

            <!-- where your annotated entities are located -->

            <key>packages</key>

            <value>org.jooq.test.all.pojos.jpa</value>

        </property>

    </properties>

</database>

So, as an answer to this feature request: We're not going to move ahead with such an integration anytime soon in the future, as we don't want to implement JPA. The reason for this is mainly because existing JPA implementations are already very good, and using jOOQ together with JPA / Hibernate has been proven to work very nicely, both at code generation time (see above) as well as at runtime (see below):

http://blog.jooq.org/2015/05/26/type-safe-queries-for-jpas-native-query-api

B - even without going as far as A, when the model is generated from DB it would be nice to have the creation script created too.

Hmm, which creation script do you mean?

There is a pending feature request to re-generate the DDL from what the jOOQ runtime schema model knows about the underlying schema:

https://github.com/jOOQ/jOOQ/issues/3160

This would be useful for those who like to duplicate a schema on H2 or some other in-memory database.

C - immutable POJO: I really like the peace of mind immutable objects provide. It would be awesome if I could tell the generator to create immutable pojos only.

Yes. Use

  <!-- Generate immutable POJOs for usage of the ResultQuery.fetchInto(Class) API
       This overrides any value set in <pojos/>
       Defaults to false -->
  <immutablePojos>false</immutablePojos>

 

See: http://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced

Regarding your others questions :
1 - target languages, forms
IMHO the generated code is tightly coupled to the DSL itself. So to me it feels like the question is more about the whole lib than just the pojo. Still, being written in Java, it should be usable from most of the others JVM languages. Maybe going for an immutable approach would help for integration with Clojure or Scala code, but once again then is about the whole public API then, which is quite bigger from the generator itself.

Indeed, but there are quick wins here. For instance, in Scala, a case class is more idiomatic than (yet, not that different from) a "POJO". Also, I'm still hoping that we're getting the Int vs. java.lang.Integer thing right, eventually (although there are some caveats)

 

For the others points, well, can't tell much apart from the obvious, so I'll skip ;)

Thanks a lot for jOOQ and hopefully it wasn't all nonsense!

Thank you for your useful feedback. It's always very good to learn about the expectations of new users. 

[Lukas Eder]

Hi Ben,

2016-01-04 1:26 GMT+01:00 Ben Hood <0x6e...@gmail.com>:

On Thu, Dec 31, 2015 at 9:14 AM, Lukas Eder <lukas...@gmail.com> wrote:
> Yes, the thread will become a bit confusing, but I'm curious about your
> feedback anyway. Perhaps start a new thread? I think we still have some
> spare threads left on Google Groups ;-)

OK, I've done this.

> If I understand this correctly, we're still discussing the workaround that
> you implemented and that you'd like to improve. But what is the *use-case*?
> What are you trying to achieve and why doesn't it work right now?

The use case and workaround is described in the thread titled "Oracle
TIMESTAMP in UTC".

I'm sorry, I've overlooked that the timestamp thread still had open points. Will look into that right away!

[joseph...@gmail.com]

Hi Lukas

Thx for your nice and thorough answer :)

Answers inline.

A - my need is for a brand new use case, without existing tables. To start from scratch is currently annoying. (snip)

There are two ways to look at this.

1. From the dogmatic jOOQ philosophy point of view

(snip)

2. From a pragmatic "time-to-market" point of view

(snip)

to make it short : I fully agree. I thought of the hibernate generation upfront, but then the pojo wouldn't be of any help afterward, so it turned me off.

maybe I should explicit a bit more my pain point : when I write the first migration (or any migration adding new/changing stuff), I've to resort to strings. Which then are duplicates of the content in the generated model. This annoys me a bit in terms of "type safety" and more general usability: if I add the foo column in a migration, then going at the foo definition in the model and looking for usage/reference wouldn't show the string "foo" in the migration script. Bad. Currently I'm thinking of updating post model generation the migration script. Tedious, error prone and definitely not sexy ^^. So I'm looking for alternatives.

B - even without going as far as A, when the model is generated from DB it would be nice to have the creation script created too.

 

Hmm, which creation script do you mean?

The DDL queries to create the data structure present in the generated model.
 

There is a pending feature request to re-generate the DDL from what the jOOQ runtime schema model knows about the underlying schema:

https://github.com/jOOQ/jOOQ/issues/3160

This would be useful for those who like to duplicate a schema on H2 or some other in-memory database.

And to start using migration in a pre existing DB.

Or to replace the existing non type safe migration script with a type safe one. If I got your issue right, one could even dream of being able to say "generate me the whole DDL" or "generate the DDL for the delta between this schema and the previous one. Which would rock like hell :)

Actually to me it sounds pretty tricky impl wise, am I right?

C - immutable POJO: I really like the peace of mind immutable objects provide. It would be awesome if I could tell the generator to create immutable pojos only.

(snip)

See: http://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced

my bad ^^

Shouldn't immutable pojo be the default? ^^

Regarding your others questions :
1 - target languages, forms
IMHO the generated code is tightly coupled to the DSL itself. So to me it feels like the question is more about the whole lib than just the pojo. Still, being written in Java, it should be usable from most of the others JVM languages. Maybe going for an immutable approach would help for integration with Clojure or Scala code, but once again then is about the whole public API then, which is quite bigger from the generator itself.

Indeed, but there are quick wins here. For instance, in Scala, a case class is more idiomatic than (yet, not that different from) a "POJO". Also, I'm still hoping that we're getting the Int vs. java.lang.Integer thing right, eventually (although there are some caveats)

 
Well, you're the judge here. I would prefer Query[] queries = ctx.ddl(mySchema); a thousand times, and scalaists just as well I guess ;)

On top, to do "anti corruption layer" is always a good practices and way easier in Scala, so maybe scalaist can wrap/bridge the way they want some nicely done pojos ;) I'm kidding to be honest. I would love case classes if using it in Scala.

What's the issue with Int vs. java.lang.Integer ? But still, the more languages you add the more issues like this you'll encounter. At the expenses of core crazy functionalities, whereas both Scala and Clojure are very proud of their ability to use Java code, so...

In the end generating some platform independent data structure sounds like a nice goal. Once this is done, anyone could marshal it to its one language without fiddling with jooq's core. Pretty nice IMHO. 


Cheers
joseph

[Lukas Eder]

2016-01-07 1:27 GMT+01:00 <joseph...@gmail.com>:

Hi Lukas

Thx for your nice and thorough answer :)

Answers inline.

A - my need is for a brand new use case, without existing tables. To start from scratch is currently annoying. (snip)

There are two ways to look at this.

1. From the dogmatic jOOQ philosophy point of view

(snip)

2. From a pragmatic "time-to-market" point of view

(snip)

to make it short : I fully agree. I thought of the hibernate generation upfront, but then the pojo wouldn't be of any help afterward, so it turned me off.

maybe I should explicit a bit more my pain point : when I write the first migration (or any migration adding new/changing stuff), I've to resort to strings. Which then are duplicates of the content in the generated model. This annoys me a bit in terms of "type safety" and more general usability: if I add the foo column in a migration, then going at the foo definition in the model and looking for usage/reference wouldn't show the string "foo" in the migration script. Bad. Currently I'm thinking of updating post model generation the migration script. Tedious, error prone and definitely not sexy ^^. So I'm looking for alternatives.

Aha, very interesting point of view!

I must say I haven't yet thought about type safe database migrations this way, and I find your ideas very intriguing.

It's a bit of a chicken and egg problem, though. At some point, you will need to operate on strings, at least before you first generate the code after your initial development migration. From then on, the migration can be made type safe for your remaining team members / the build server.

We have a similar chicken and egg situation in jOOQ-meta. We're using jOOQ and generated dictionary views tables (e.g. INFORMATION_SCHEMA or SYS schema) to query the dictionary views. But when we first integrate a new database, those generated tables aren't available yet, and we need the jOOQ-meta integration to generate them for the first time.

Note also, that all of this is only applicable if you check in generated code to version control, something that not everyone agrees upon (although I do).

Anyway, I think that these are separate discussions - the migration type safety and the JPA "philosophy". For jOOQ, it just doesn't really make sense to go "Java first", so I'll try to focus this discussion on how making type safe migrations can be made easier.

There is a pending feature request to re-generate the DDL from what the jOOQ runtime schema model knows about the underlying schema:

https://github.com/jOOQ/jOOQ/issues/3160

This would be useful for those who like to duplicate a schema on H2 or some other in-memory database.

And to start using migration in a pre existing DB.

Or to replace the existing non type safe migration script with a type safe one. If I got your issue right, one could even dream of being able to say "generate me the whole DDL" or "generate the DDL for the delta between this schema and the previous one. Which would rock like hell :)

That's the idea. Although, we're still not there yet :-)

 

Actually to me it sounds pretty tricky impl wise, am I right?

Yes.

 

C - immutable POJO: I really like the peace of mind immutable objects provide. It would be awesome if I could tell the generator to create immutable pojos only.

(snip)

See: http://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced

my bad ^^

Shouldn't immutable pojo be the default? ^^

Perhaps, indeed. Let's write this down:

https://github.com/jOOQ/jOOQ/issues/4909

(But then again, soon, we'll be generating value types anyway...)

Regarding your others questions :
1 - target languages, forms
IMHO the generated code is tightly coupled to the DSL itself. So to me it feels like the question is more about the whole lib than just the pojo. Still, being written in Java, it should be usable from most of the others JVM languages. Maybe going for an immutable approach would help for integration with Clojure or Scala code, but once again then is about the whole public API then, which is quite bigger from the generator itself.

Indeed, but there are quick wins here. For instance, in Scala, a case class is more idiomatic than (yet, not that different from) a "POJO". Also, I'm still hoping that we're getting the Int vs. java.lang.Integer thing right, eventually (although there are some caveats)

 
Well, you're the judge here. I would prefer Query[] queries = ctx.ddl(mySchema); a thousand times, and scalaists just as well I guess ;)

Hmm, how is it related? Or do you mean from a roadmap priority perspective?

 

On top, to do "anti corruption layer" is always a good practices and way easier in Scala, so maybe scalaist can wrap/bridge the way they want some nicely done pojos ;) I'm kidding to be honest. I would love case classes if using it in Scala.

What's the issue with Int vs. java.lang.Integer ?

Int cannot be null. But in SQL, a column reference cannot offer a NOT NULL guarantee, e.g. in the case of OUTER JOIN, GROUPING SETS(), UNION and other set operations, etc. Using Int in Scala is a false promise (for SQL).

The second issue is that Option[Int] is a very bad match for a SQL INT NULL column. While the quick win of having nullability information on records and pojos is flirting with API designers and users alike, it is almost impossible to reason about a nullable type in the context of an internal domain-specific language like jOOQ. Users would be really pissed if they wanted to run a JOIN predicate between a NOT NULL primary key AUTHOR.ID (Field<Integer>) and a NULL foreign key like BOOK.CO_AUTHOR_ID (Field<Optional<Integer>>). The types wouldn't match, even if that's completely irrelevant in SQL. Users would need to wrestle the type system all the time.

Just look at the bad user mood on the Typesafe Slick mailing list. They got this NULL stuff very wrong :-) In other words, we're lucky that Java has similar warts as SQL...

TL;DR: NULL is inevitable in SQL and in a SQL API in Java/Scala.

[Denis Miorandi]

About 

1. code generation: would be great have more options about pojo generation. I.e. generate json-schema / json of tables or xml/xsd of tables. In some situations could be nice.
   
2. something more about xml /xpath /json queries type safeting. I don't know really how but of course db dependent...

Il giorno martedì 29 dicembre 2015 12:27:46 UTC+1, Lukas Eder ha scritto:

Dear group,

The jOOQ code generator is one of the biggest assets when using jOOQ. It allows for referring to your schema in a type safe way, allowing to leverage IDE auto completion with tables, columns, procedures, etc.

However, it has grown organically and it thus doesn't allow for the extensibility that some of you would like. It is time to start thinking about the future of jOOQ-codegen and jOOQ-meta, to collect ideas for jOOQ 4.0 in that area, or perhaps even for prior releases. So, this is a good time to chime in and discuss potential requirements.

The relevant issue is:

https://github.com/jOOQ/jOOQ/issues/3883

These are, roughly, the existing features, or existing feature requests:

1. Target languages and forms

jOOQ currently supports Java and Scala - the most popular languages on the JVM. Does it make sense to support other languages? Or perhaps even data formats, like XML?

This is an interesting topic, but it is also very demanding in terms of complexity budget. Alternative languages cannot be integration tested as well as Java without adding substantial effort.

2. Templating language

The code generator currently uses a home-grown templating "language" via a home-grown, internal, undocumented API.

In the past, users have suggested using Xtend (http://www.eclipse.org/xtend) instead. The advantage of Xtend is that the template language is built into the Xtend language, and it feels extremely natural.

At the time, the idea of integrating Xtend was rejected, because the Xtend tooling was very Eclipse dependent. This is no longer true, and Xtend is still a very interesting consideration.

Alternatively, a programmatic language model (like XJC has) could be interesting, although it would be very limiting in terms of what's possible in 1. Target languages.

3. Style

Programmers like endless discussions about curly braces, indentations, tabs and spaces. jOOQ currently doesn't embrace this sort of "creativity" :-)

It would be great if the generated code style could be influenced in one way or another, although, this is a rather low priority.

4. Generator strategies

Most customisation is about naming style. Do you want your database objects in PascalCase? camelCase? UPPER_CASE? lower_case?

Is this feature sufficient? What's missing here?

[Lukas Eder]

Hi Denis,

Thank you very much for chiming in on this discussion, and sorry for the delay.

2016-01-12 14:57 GMT+01:00 Denis Miorandi <denis.m...@gmail.com>:

1. code generation: would be great have more options about pojo generation. I.e. generate json-schema / json of tables or xml/xsd of tables. In some situations could be nice.

That's very interesting. Let me get this right: You're using SQL Server to store and manipulate XML data that is based on registered XSD - correct? So, jOOQ's code generator could, in principle, fetch these XSD, trigger XJC code generation to produce JAXB annotated objects, and register those via the out-of-the-box AbstractXMLasObjectBinding (http://www.jooq.org/javadoc/latest/org/jooq/impl/AbstractXMLasObjectBinding.html). Am I getting this right?

How about JSON, how does that work in SQL Server?

Also, could you perhaps show an example of what a runtime use-case for these features would be?

2. something more about xml /xpath /json queries type safeting. I don't know really how but of course db dependent...

Yes, this would be DB dependent, indeed. Looks like we could discuss potential new features in the other thread that you've started here:

https://groups.google.com/forum/#!topic/jooq-user/PcYhxv6gWkI

Best Regards,

Lukas