reverse engineering database create scripts

[Manuel Rossetti]

I recently review the DDLDatabase presentation here:  https://www.jooq.org/doc/3.11/manual/code-generation/codegen-ddl/

This got me thinking about a problem that I wonder if jooq can address.  We have to write our scripts in specific dialects of SQL. But, I want to write my scripts in jooq.  This can already be done use the DDLQuery.  But what if I have my script already and want to generate the jooq code that would need to be written from the script.  That is, automatically generate the jooq code that represents the database.  With this functionality, you would be able to make a connection to a database for any of the supported dialects and produce the java code equivalent in jooq that represents that database in DDL jooq queries.  Once that is done, no more sql scripts! And, since the DDL is represented in jooq, the creation of the database under a new dialect is then trivial making migration from one database platform to another much easier.

Anyway, I am just wondering if anyone else has thought of this use case.

[Lukas Eder]

Hi Manuel,

This idea has been around for a while. In the old days (jOOQ 2.x), there was a third party parser and jOOQ code generator provided by Gudu Software. It only supported parsing MySQL and PostgreSQL dialects, and didn't get a lot of traction from the community, e.g. absolutely no feedback.

At some point, we decided to no longer ship the integration. It can still be found on Github, though:

https://github.com/sqlparser/sql2jooq

One reason why the jOOQ parser was introduced in jOOQ 3.9 is precisely this idea, though. We want to re-enact this feature eventually, so you might want to follow this issue here:

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

It just took the parser a bit of time to mature (still missing a lot of features), and generating Java code instead of SQL code might also raise a few questions, but I think it might be feasible to introduce a first working draft for jOOQ 3.12. Integration testing should work fine as well, we already have tons of parser unit tests. We'll just have to hook the jdk.compiler in between and check for compilation errors.

Do note that you can already parse and translate your DDL from one dialect to another with jOOQ (except you don't get any intermediary Java code). You can play around with this on our website:

https://www.jooq.org/translate

Or use the ParserCLI command line tool, or write your own, see:

https://blog.jooq.org/2018/01/12/top-5-hidden-jooq-features/#hidden5

I hope this helps,

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.

[Manuel Rossetti]

Dear Lukas,

Always great to get direct feedback like this.  Indeed, I will follow that issue.  Being able to generate jooq java code that can be compliant with any of the jooq supported SQL dialects is (IMO) the important use case here. But, you are right, I could always translate one script into another using the current functionality.

Thanks again,

Manuel

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/eY7ukm9Vdyc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[ta...@mobisy.com]

Hi Lukas Eder,

Where is this repository hosted as of today - https://github.com/sqlparser/sql2jooq, where can I get maven dependency for this example https://www.jooq.org/doc/latest/manual/tools/sql2jooq/.

I have a requirement where I generated dynamic sql queries on the fly. Once this sql is generated by my code, I wish to generate a equivalent jooq code.

Please suggest if I can rely on https://www.jooq.org/doc/latest/manual/tools/sql2jooq/ to fulfil my requirement.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

The contents of this email message and any attachments are intended solely for the addressee(s) only and contains confidential and/or privileged information and may be legally protected from disclosure. If you are not the intended recipient of this message or their agent, or if this message has been addressed to you in error, please immediately alert the sender by reply email and then delete this message and any attachments thereof. Mobisy is not liable for any loss or any damage arising in any way from the use of this e-mail or its attachments.

[Lukas Eder]

Hi there,

Thanks for pointing out that manual link. That page is still active by error. We have removed it from the manual, recently, as we haven't supported this third party product for a while now.

We currently do not offer this functionality as discussed previously on this thread.

Thanks,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/349da891-8296-4bd6-a3a2-267cfbf3a041%40googlegroups.com.