Re: Maven code generatation

[Lukas Eder]

Hello Sergey,

As this is probably of general interest, I CC'ed this mail to the jOOQ
user group

> I've found idea in "DBRE Add-On" to separate introspection phase and code
> generation phase.
> During introspection it generates xml file (example attached). This file
> contains schema metadata.
> You can keep it in source control. Code generation phase take it from some
> folder (for example,  src/main/jooq) and generate code (
> target/generated-sources/jooq).
> What do you think, it it good?

If I understand you correctly, you'd like to use spring DBRE for
database reverse-engineering (schema data -> XML), and then jOOQ to
generate source code (XML -> Java). Is that right? It should be fairly
simple to extend org.jooq.util.AbstractDatabase in jooq-meta and
provide the code generator with meta data taken from XML rather than
from the database. This isn't only useful for Spring, but also for
Hibernate's hbm.xml mapping files! I have filed this as a feature
request, which will probably be included in the next release:
https://sourceforge.net/apps/trac/jooq/ticket/875

Have you started implementing such an extension?

Cheers
Lukas

[Sergey Epik]

Hello Lukas,

On Sun, Oct 16, 2011 at 5:28 PM, Lukas Eder <lukas...@gmail.com> wrote:

If I understand you correctly, you'd like to use spring DBRE for
database reverse-engineering (schema data -> XML), and then jOOQ to
generate source code (XML -> Java). Is that right?

The idea was to separate introspection phase and code generation phase in jooq-maven-plugin.
It may be useful, if you do not want keep generated code on source control (like in jooq-meta)
and do not want access database connection during build process.

In introspection phase (for example, using development profile) jooq-maven-plugin generates xml file, that contains database metadata.
You can keep in this file on source control  (for example, in src/main/jooq) and manually change it.
Code generation phase uses it as model and generates code (into target/generated-sources/jooq).

Best regards,
Sergey

[Lukas Eder]

Hello Sergey,

> The idea was to separate introspection phase and code generation phase in
> jooq-maven-plugin.
> It may be useful, if you do not want keep generated code on source control
> (like in jooq-meta)
> and do not want access database connection during build process.
>
> In introspection phase (for example, using development profile)
> jooq-maven-plugin generates xml file, that contains database metadata.
> You can keep in this file on source control  (for example, in src/main/jooq)
> and manually change it.
> Code generation phase uses it as model and generates code (into
> target/generated-sources/jooq).

To be clear about separation of concerns: The jooq-maven-plugin just
accesses jooq-codegen / jooq-meta. It has no functionality of its own.

I like your idea of having this intermediate step. In principle, once
we have XML as a schema definition for jOOQ-relevant artefacts, the
source code generator could actually just be a simple set of XSLT
stylesheets. That would allow for much more flexibility, as users
could override naming schemes when generating Java classes from XML.
This would immediately resolve some open feature requests about being
able to have user-defined prefixes / suffixes in generated tables.

I might actually consider a cooperation with SchemaSpy or
SchemaCrawler for that sake. SchemaSpy supports a large variety of
databases and has a neat XML representation for your database schema:

http://schemaspy.sourceforge.net/sample/library.xml

But I'm not sure if they will correctly support UDT's and UDF's.
Unfortunately, the JDBC API for database meta data navigation is not
very reliable... See also my blog post about schema navigation:

http://lukaseder.wordpress.com/2011/09/11/database-schema-navigation-in-java-2/

Cheers
Lukas

[Lukas Eder]

> BTW: I've seen this way of code generation in Binary notes project
> http://bnotes.sourceforge.net/.
> It generates ASN.1 encoder/decoder using XSLT templates.

Very nice!

[Lukas Eder]

Hi Sergey,

2011/11/1 Lukas Eder <lukas...@gmail.com>:

I am tracking these ideas as

https://sourceforge.net/apps/trac/jooq/ticket/875 (jOOQ-meta using XML
input/output)
https://sourceforge.net/apps/trac/jooq/ticket/911 (jOOQ-codegen using
XSLT for generation)

I probably won't have time to implement this in 2011. But those are
very interesting ideas indeed.

Cheers
Lukas

[Lukas Eder]

Hi Sergey,

Another, related issue to the question of whether schema introspection should be separated from code generation was discussed in this thread:
https://github.com/lukaseder/jOOQ/issues/2

It is an issue that's more focussed on a concrete problem that may arise from different development environment setups. As suggested by Daniel Carleton, schema name overriding at code generation time can solve some immediate problems in such setups. The separation of introspection and generation is much more general, though.

Cheers
Lukas

[Sergey Epik]

Hello Lukas,

We also faced this problem.

Each developer in our environment has it's own schema, for example,
PROJCODE_DEV1,
PROJCODE_DEV2,
PROJCODE_TEST,
PROJCODE_PROD, etc..

We decided keep generated source code on version control (I hope, temporarily).
Generated code has references to the schema PROJCODE_DEV1.
As a workaround, after generation, special ant script (using maven-antrun-plugin) fixes several issues:

1.  renames PROJCODE_DEV1 to PROJCODE ( in runtime we use schema mapping and map this name to PROJCODE_<schema_suffix>)

2.  changes data type of one column from java.sql.Date to java.sql.Timestamp.
By default, jooq maps Oracle DATE type to java.sql.Date.
java.sql.Date doesn't contain time, but Oracle DATE type contains seconds.
So we have to change type of field in generated record, parameter in procedure etc. (Fortunately, one column only :)
This problem is related to http://groups.google.com/group/jooq-user/browse_thread/thread/4ad607718d4e9533
(no custom type mapping)

3. changes type of sequences from BigInteger to long. Most of primary keys in our schema are NUMERIC(18,0) and jOOQ generates long type for them. I thought that may be possible consider sequence's MAXVALUE for defining type. For example, if we declare sequence with MAXVALUE 999999999999999999 (18 digits) then Long type will be selected, otherwise - BigInteger.

So, I agree with Daniel, it would be great to have additional configuration parameter in jooq-codegen-maven, that defines target schema name:

                    <jdbc>
                        <schema>PROJCODE_DEV1</schema>
                    </jdbc>
                    <generator>
                        <target>
                            <schema>PROJCODE</schema>
                        </target>
                    </generator>

--
Best regards,
Sergey

[Lukas Eder]

Hello Sergey,

Thanks a lot for your feedback!

> 1.  renames PROJCODE_DEV1 to PROJCODE ( in runtime we use schema mapping and
> map this name to PROJCODE_<schema_suffix>)

So a schema re-writing functionality is really a nice feature for many
of you. Let's raise the priority on this, then:
https://sourceforge.net/apps/trac/jooq/ticket/958

> 2.  changes data type of one column from java.sql.Date to
> java.sql.Timestamp.
> By default, jooq maps Oracle DATE type to java.sql.Date.
> java.sql.Date doesn't contain time, but Oracle DATE type contains seconds.
> So we have to change type of field in generated record, parameter in
> procedure etc. (Fortunately, one column only :)
> This problem is related to
> http://groups.google.com/group/jooq-user/browse_thread/thread/4ad607718d4e9533
> (no custom type mapping)

Yes, the custom type mapping will be handy for various use cases. I'm
aware of this problem. I'll start a new thread about this topic to
find out what you guys really need.
In this case, though, beware of weird performance impacts when binding
timestamp instead of date:
http://stackoverflow.com/questions/6612679/non-negligible-execution-plan-difference-with-oracle-when-using-jdbc-timestamp-o

> 3. changes type of sequences from BigInteger to long. Most of primary keys
> in our schema are NUMERIC(18,0) and jOOQ generates long type for them. I
> thought that may be possible consider sequence's MAXVALUE for defining type.
> For example, if we declare sequence with MAXVALUE 999999999999999999 (18
> digits) then Long type will be selected, otherwise - BigInteger.

That's a nice improvement. In jOOQ 2.0, I have already introduced a
generic type parameter to org.jooq.Sequence<T extends Number>. I
wasn't aware of the MAXVALUE field in Oracle, though. I'll file this
as a feature request:
https://sourceforge.net/apps/trac/jooq/ticket/961

Cheers
Lukas

[Lukas Eder]

Hello Sergey,

> So, I agree with Daniel, it would be great to have additional configuration
> parameter in jooq-codegen-maven, that defines target schema name:
>
>                     <jdbc>
>                         <schema>PROJCODE_DEV1</schema>
>                     </jdbc>
>                     <generator>
>                         <target>
>                             <schema>PROJCODE</schema>
>                         </target>
>                     </generator>

This schema re-writing functionality will work like this:

<configuration>
<jdbc>
... the schema here is deprecated.
it'll be removed later
<schema>${schemaLocalName}</schema>
</jdbc>
<generator>
<database>
... the input schema is used for
reading data in jooq-meta

<inputSchema>${schemaLocalName}</inputSchema>
... the output schema is used for
code generation

<inputSchema>SchemaReliableName</inputSchema>

I'll create a sample configuration in the jooq-codegen-maven-example
Maven artefact. Both code generation and runtime will be integration
tested in a new integration test suite.

Cheers
Lukas

[Sergey Epik]

Hello Lukas,

Do you mean <outputSchema>SchemaReliableName</outputSchema>?

Thank you.

Best regards,

[Lukas Eder]

Yes, of course...

2011/11/22 Sergey Epik <serge...@gmail.com>: