JOOQ used for SQL parsing and autocomplete in code editor

marekgregor

unread,

Jul 17, 2019, 8:36:15 AM7/17/19

to jOOQ User Group

Hi,

I am seriously considering implementation of Jupyter Notebook Kernel for SQL (+Java) language utilizing JOOQ.

The Kernel will be capable to:

- simultaneously connect to various databases for multiple users utilizing connection pooling,

- provide database metadata to client (tables, columns, foreign keys, views, stored procs,...) utilizing JOOQ implementation,

- execute database native SQL or standardized SQL translated to native SQL by JOOQ parser functionality and return it's results to client

Kernel could also support autocomplete and introspection for SQL code written in editor on client side of Jupyter Notebook. The API implemented by kernel for autocomplete is pretty simple: client sends code (SQL) string and cursor position, kernel response contains list of autocomplete recommendation strings etc.

I am curious if it is possible to use JOOQ for parsing SQL to QueryPart(s), identify which QueryPart corresponds to defined SQL string cursor position and return corresponding autocomplete strings (e.g. with names of available tables in FROM clause, or names of columns in SELECT clause). I don't know if it is suitable to use JOOQ for this task and how.

thanks for response

Lukas Eder

unread,

Jul 18, 2019, 9:16:30 AM7/18/19

to jOOQ User Group

Hi Marek,

Thank you very much for your interest in using jOOQ's parser for your product. What you're planning to do sounds exactly like what we want to be able to do, and offer as a library, with the parser / translator.

However, there is still much work to do on our side. In order to be able to associate parser artefacts, such as a mapping between the position in an input string and a QueryPart, we'll have to re-design our QueryPart internals and make them a public API. This will be a priority for the next few jOOQ releases, but we're not quite there yet. Right now, you would have to look into jOOQ's internals (possibly open them up using reflection) to get access to this kind of information.

Do note that there's a related discussion on GitHub where Scott McKinney plans to use jOOQ as a SQL parser for Manifold:

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

If you're interested in continuing your jOOQ evaluation, we'd be very keen on learning what kinds of requirements you may have from a future jOOQ.

Best Regards,

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/eab246d8-2947-4686-9d29-1be0e1ca6182%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

marekgregor

unread,

Jul 18, 2019, 9:48:13 AM7/18/19

to jOOQ User Group

Hi Lukas,

that is definitely great news, so I am sure JOOQ will be the right choice for this functionality. Now I will try to evaluate JOOQ in the prototype version.

Thanks for response and links.

Marek

Dňa štvrtok, 18. júla 2019 15:16:30 UTC+2 Lukas Eder napísal(-a):

Hi Marek,

Thank you very much for your interest in using jOOQ's parser for your product. What you're planning to do sounds exactly like what we want to be able to do, and offer as a library, with the parser / translator.

However, there is still much work to do on our side. In order to be able to associate parser artefacts, such as a mapping between the position in an input string and a QueryPart, we'll have to re-design our QueryPart internals and make them a public API. This will be a priority for the next few jOOQ releases, but we're not quite there yet. Right now, you would have to look into jOOQ's internals (possibly open them up using reflection) to get access to this kind of information.

Do note that there's a related discussion on GitHub where Scott McKinney plans to use jOOQ as a SQL parser for Manifold:
https://github.com/jOOQ/jOOQ/issues/8720

If you're interested in continuing your jOOQ evaluation, we'd be very keen on learning what kinds of requirements you may have from a future jOOQ.

Best Regards,
Lukas

On Wed, Jul 17, 2019 at 2:36 PM marekgregor <marek...@gmail.com> wrote:

Hi,

I am seriously considering implementation of Jupyter Notebook Kernel for SQL (+Java) language utilizing JOOQ.

The Kernel will be capable to:
- simultaneously connect to various databases for multiple users utilizing connection pooling,
- provide database metadata to client (tables, columns, foreign keys, views, stored procs,...) utilizing JOOQ implementation,
- execute database native SQL or standardized SQL translated to native SQL by JOOQ parser functionality and return it's results to client

Kernel could also support autocomplete and introspection for SQL code written in editor on client side of Jupyter Notebook. The API implemented by kernel for autocomplete is pretty simple: client sends code (SQL) string and cursor position, kernel response contains list of autocomplete recommendation strings etc.

I am curious if it is possible to use JOOQ for parsing SQL to QueryPart(s), identify which QueryPart corresponds to defined SQL string cursor position and return corresponding autocomplete strings (e.g. with names of available tables in FROM clause, or names of columns in SELECT clause). I don't know if it is suitable to use JOOQ for this task and how.

thanks for response

--
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...@googlegroups.com.

Lukas Eder

unread,

Jul 26, 2019, 9:36:54 AM7/26/19

to jOOQ User Group

Hi Marek,

Notice, we've decided to include a minor feature in the upcoming version 3.12, which could be helpful to you: https://github.com/jOOQ/jOOQ/issues/8986, in which we will simulate DDL using an in-memory H2 database, which we can reverse engineer again to derive all schema meta information. This will be useful to your parser usage, as a lot of the jOOQ features depend on jOOQ knowing schema meta information that is specified by DDL (probably), in your case.

I hope this helps,

Lukas

Marek Gregor

unread,

Jul 26, 2019, 11:07:24 AM7/26/19

to jooq...@googlegroups.com

Hi Lukas,

thank you, that's amazing I didn't realize before, that this feature is also implementable. For my initial use case JdbcDatabase and it's descendands is far enough - users of my app will pass JDBC connection params, and the app will display database metadata and allow executing standardized SQL processed by JOOQ Parser to get data for reports.

But this brings to my head another question about JOOQ licensing ...

I know that in standard case, model generation (from metadata) and writing parseable SQL is done by developer (therefore I think per developer license is reasonable here). But in my case this JOOQ functionality will be used by application users and not developers, albeit without directly using JOOQ java API. My app takes this case into the extreme, because my Jupiter Notebook Kernel will also allow execute standard java code (besides SQL execution) to further process SQL results (java code execution will be definitely available without JOOQ libraries in classpath). Therefore the essential question is, if the license cost will be counted per developer or per app user for my app.

Possibility to create "universal report" (by using standard, translatable SQL) executable on any supported database with the same schema is one of the amazing features, which are possible to do with JOOQ. There are a lot of applications, which support multiple databases (e.g. like JIRA internally using Hibernate) and the possibility for company to create universal report executable on any db engine is one of the big "sellpoints" of my proposal. There are also other nice features possible, like static validation of existing SQL queries (reports) when schema is upgraded etc. But if it makes any significant change in JOOQ licensing for me I am ready to postpone this feature for now and not to present it as planned for first version (I am planning to start Kickstarter project for it), and use JOOQ only for database metadata analysis tool and native SQL execution wrapper.

thanks for response

Marek

pi 26. 7. 2019 o 15:36 Lukas Eder <lukas...@gmail.com> napísal(a):

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/2wPGf6uB5fI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6818c855-1a51-47be-9af8-73934b3bc407%40googlegroups.com.

Lukas Eder

unread,

Jul 26, 2019, 11:27:21 AM7/26/19

to jOOQ User Group

Hi Marek,

Thank you very much for following up.

On Fri, Jul 26, 2019 at 5:07 PM Marek Gregor <marek....@gmail.com> wrote:

Hi Lukas,

thank you, that's amazing I didn't realize before, that this feature is also implementable. For my initial use case JdbcDatabase and it's descendands is far enough - users of my app will pass JDBC connection params, and the app will display database metadata and allow executing standardized SQL processed by JOOQ Parser to get data for reports.

I'm assuming you mean the JdbcDatabase from jOOQ-meta? Yes, that should be sufficient if the schema is not too big. Unfortunately, its backing JDBC DatabaseMetaData is not the fastest API when schemas get big, so queries against that meta data might become a bit slow on some systems.

But this brings to my head another question about JOOQ licensing ...
I know that in standard case, model generation (from metadata) and writing parseable SQL is done by developer (therefore I think per developer license is reasonable here). But in my case this JOOQ functionality will be used by application users and not developers, albeit without directly using JOOQ java API. My app takes this case into the extreme, because my Jupiter Notebook Kernel will also allow execute standard java code (besides SQL execution) to further process SQL results (java code execution will be definitely available without JOOQ libraries in classpath). Therefore the essential question is, if the license cost will be counted per developer or per app user for my app.

We have your use-case covered in our licensing FAQ:

https://www.jooq.org/legal/licensing/#faq-commercial

All developers who use the jOOQ API will need to be licensed. If your users do not use the jOOQ API directly, they profit from the free, included distribution right that every license ships with. Even if they write SQL code that you parse via the jOOQ API, our current license considers such users non-developers with respect to the jOOQ license and jOOQ API. In a similar way, frontend developers who only code JavaScript and call services that use jOOQ behind the scenes do not need a license, if the jOOQ API is not exposed through the services.

So, you need as many licenses as there are developers working with jOOQ on your own software.

Note, should you need to expose the jOOQ API to your users, we usually negotiate an OEM license that is not covered by our standard price plans. Alternatively, you could ship the jOOQ Open Source Edition and let your users purchase runtime licenses from us directly.

Possibility to create "universal report" (by using standard, translatable SQL) executable on any supported database with the same schema is one of the amazing features, which are possible to do with JOOQ. There are a lot of applications, which support multiple databases (e.g. like JIRA internally using Hibernate) and the possibility for company to create universal report executable on any db engine is one of the big "sellpoints" of my proposal. There are also other nice features possible, like static validation of existing SQL queries (reports) when schema is upgraded etc. But if it makes any significant change in JOOQ licensing for me I am ready to postpone this feature for now and not to present it as planned for first version (I am planning to start Kickstarter project for it), and use JOOQ only for database metadata analysis tool and native SQL execution wrapper.

I'm happy to hear more about the details of that use case, but as I can tell right now, this doesn't sound like something that would modify your licensing situation - as long as your users do not work *directly* with the jOOQ API.

For the avoidance of doubt (as this is a public mailing list that is archived), my responses are valid today on July 25, 2019 given the current jOOQ License and Maintenance Agreement

https://www.jooq.org/legal/180607_jOOQ_License_and_Maintenance_Agreement.pdf

Marek Gregor

unread,

Jul 29, 2019, 6:51:53 AM7/29/19

to jooq...@googlegroups.com

Hi Lukas,

thanks a lot for insightful response, from the licensing point of view that's the best outcome I could imagine. By the way without JOOQ, the only alternative for parsing DB metadata and SQL is opensource code of Squirrel SQL Client, but it is intertwined with Java Swing API and lot of harder to use from the point of API, documentation, support, etc.

I'm assuming you mean the JdbcDatabase from jOOQ-meta?

Sorry I made a mistake. I have meant descendants of org.jooq.meta.AbstractDatabase (PostgresDatabase, MySQLDatabase, ...), I checked the code, and these implementations should be more performant than general JdbcDatabase implementation.

Thanks once more, I will let you know about any serious progress of this project.

Marek

pi 26. 7. 2019 o 17:27 Lukas Eder <lukas...@gmail.com> napísal(a):

--

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/2wPGf6uB5fI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7JeJsanzuZazdQdOWK7NRaqhSARr-yoRDFM4Obx0nPwg%40mail.gmail.com.

Lukas Eder

unread,

Aug 29, 2019, 11:04:16 AM8/29/19

to jOOQ User Group

Hi Marek,

We've just released jOOQ 3.12. With it there is experimental support for running our DDL simulation in the core library though DSLContext.meta(Source...). You can provide a set of DDL scripts, and we'll run them against H2 (translated to H2 dialect), to produce the resulting meta model, which you can use in your tooling, or in the parser.

Any early feedback would be greatly appreciated!

Cheers,

Lukas

To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

Marek Gregor

unread,

Sep 2, 2019, 4:13:42 AM9/2/19

to jooq...@googlegroups.com

Hi Lukas,

thanks for notifying me. I have finally tried the meta() and parser() API methods, here are my notes:

- DSLContext.meta() for existing JDBC connection works perfectly

- DSLContext.parser().parseResultQuery() works for full qualified field names (e.g.: "select alarms.ID from alarms") but not for unqualified names (e.g.: "select ID from alarms") when I have created DslContext with settings: new Settings()

.withParseDialect(SQLDialect.POSTGRES)

.withParseWithMetaLookups(THROW_ON_FAILURE)

.withParseSearchPath(new ParseSearchSchema().withSchema("public"))

.withParseUnsupportedSyntax(FAIL)

.withParseUnknownFunctions(ParseUnknownFunctions.FAIL);

- DSLContext.meta(Source) fails with code:

 public class DbScannerTest {

    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost/DEMO_Basic1_SysCfg";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "swx");

        try (Connection conn = DriverManager.getConnection(url, props)) {
            final DSLContext dsl = DSL.using(conn, SQLDialect.POSTGRES);
            final Meta specialMeta = dsl.meta(Source.of("CREATE TABLE tutorials_tbl ( " +
                    "   id INT NOT NULL, " +
                    "   title VARCHAR(50) NOT NULL, " +
                    "   author VARCHAR(20) NOT NULL, " +
                    "   submission_date DATE, " +
            ");"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

with error:

"C:\Program Files\AdoptOpenJDK\jdk-11.0.3.7-hotspot\bin\java.exe " ..." DbScannerTest
Connected to the target VM, address: '127.0.0.1:49792', transport: 'socket'
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.jooq.tools.reflect.Reflect (file:/C:/Users/mgregor/.m2/repository/org/jooq/jooq/3.12.0/jooq-3.12.0.jar) to constructor java.lang.invoke.MethodHandles$Lookup(java.lang.Class)
WARNING: Please consider reporting this to the maintainers of org.jooq.tools.reflect.Reflect
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
sep 02, 2019 10:11:33 AM org.jooq.tools.JooqLogger error
SEVERE: An exception occurred while parsing a DDL script: Identifier expected: [1:138] ...or VARCHAR(20) NOT NULL,    submission_date DATE, [*]);. Please report this error to https://github.com/jOOQ/jOOQ/issues/new
org.jooq.impl.ParserException: Identifier expected: [1:138] ...or VARCHAR(20) NOT NULL,    submission_date DATE, [*]);
	at org.jooq.impl.ParserContext.expected(ParserImpl.java:10641)
	at org.jooq.impl.ParserImpl.parseIdentifier(ParserImpl.java:8935)
	at org.jooq.impl.ParserImpl.parseIdentifier(ParserImpl.java:8928)
	at org.jooq.impl.ParserImpl.parseCreateTable(ParserImpl.java:3177)
	at org.jooq.impl.ParserImpl.parseCreate(ParserImpl.java:2143)
	at org.jooq.impl.ParserImpl.parseQuery(ParserImpl.java:844)
	at org.jooq.impl.ParserImpl.parse(ParserImpl.java:542)
	at org.jooq.impl.ParserImpl.parse(ParserImpl.java:529)
	at org.jooq.impl.DDLDatabaseInitializer.loadScript(DDLDatabaseInitializer.java:172)
	at org.jooq.impl.DDLDatabaseInitializer.initializeUsing(DDLDatabaseInitializer.java:153)
	at org.jooq.impl.DDLMetaProvider.provide(DDLMetaProvider.java:69)
	at org.jooq.impl.DefaultDSLContext.meta(DefaultDSLContext.java:443)
	at DbScannerTest.main(DbScannerTest.java:24)

Exception in thread "main" org.jooq.impl.ParserException: Identifier expected: [1:138] ...or VARCHAR(20) NOT NULL,    submission_date DATE, [*]);
	at org.jooq.impl.ParserContext.expected(ParserImpl.java:10641)
	at org.jooq.impl.ParserImpl.parseIdentifier(ParserImpl.java:8935)
	at org.jooq.impl.ParserImpl.parseIdentifier(ParserImpl.java:8928)
	at org.jooq.impl.ParserImpl.parseCreateTable(ParserImpl.java:3177)
	at org.jooq.impl.ParserImpl.parseCreate(ParserImpl.java:2143)
	at org.jooq.impl.ParserImpl.parseQuery(ParserImpl.java:844)
	at org.jooq.impl.ParserImpl.parse(ParserImpl.java:542)
	at org.jooq.impl.ParserImpl.parse(ParserImpl.java:529)
	at org.jooq.impl.DDLDatabaseInitializer.loadScript(DDLDatabaseInitializer.java:172)
	at org.jooq.impl.DDLDatabaseInitializer.initializeUsing(DDLDatabaseInitializer.java:153)
	at org.jooq.impl.DDLMetaProvider.provide(DDLMetaProvider.java:69)
	at org.jooq.impl.DefaultDSLContext.meta(DefaultDSLContext.java:443)
	at DbScannerTest.main(DbScannerTest.java:24)

št 29. 8. 2019 o 17:04 Lukas Eder <lukas...@gmail.com> napísal(a):

Lukas

To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7JeJsanzuZazdQdOWK7NRaqhSARr-yoRDFM4Obx0nPwg%40mail.gmail.com.

--

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/2wPGf6uB5fI/unsubscribe.

To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/4d45b28c-4da4-4a1a-9bbc-b5203d32f002%40googlegroups.com.

Lukas Eder

unread,

Sep 3, 2019, 10:14:43 AM9/3/19

to jOOQ User Group

Hi Marek,

Thank you very much for your quick feedback.

Indeed, being able to parse unqualified column references will prove tricky as we will have to defer some lookups until we have all the table expressions available. There are many edge cases, e.g. when using lateral derived tables, where our current single pass recursive descent parser might not be good enough. This will be fixed through https://github.com/jOOQ/jOOQ/issues/9061, hopefully in 3.13.

Regarding that last parse call, what else would you suggest? I mean, the SQL you passed to DSLContext::meta has a syntax error... There's an excess ",". Or do you mean the reflection warning? We're trying to address that as soon as possible.

Thanks,

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CACtQWuQKyNHYpA-y6aTK3_C5oXitoy0J-Vznp7ZbniRfKY96JA%40mail.gmail.com.

Marek Gregor

unread,

Sep 9, 2019, 4:16:09 AM9/9/19

to jooq...@googlegroups.com

Hello Lukas,

thanks a lot for response, I am sorry, I have not noticed the obvious bug in my DDL, the reflection warnings deceived me.

I have not realized that parsing unqualified column names is not so straightforward to implement, I think existing state - using qualified names is perfectly fine for use.

Feature of parsing with meta lookups (.withParseWithMetaLookups(THROW_ON_FAILURE)) is perfect for validation of queries without even running them, this I think will be killer feature for any reporting tool ;). In DslContext.meta() I miss only one thing - possibility to list and run stored procedures (org.jooq.Routine) in some dynamic way without static generation. But this could be also implemented by me when needed, by reverse engineering code of org.jooq.codegen.JavaGenerator#generateRoutines in a way to support dynamic calling of routines without code generation ...

Thank you once more time for marvelous support.

Marek

ut 3. 9. 2019 o 16:14 Lukas Eder <lukas...@gmail.com> napísal(a):

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO43X3g5KRrHjaVhTqTYMFzG1oTR3h1qicAG19gF1ASOeA%40mail.gmail.com.

Lukas Eder

unread,

Sep 9, 2019, 5:17:05 AM9/9/19

to jOOQ User Group

Hi Marek,

On Mon, Sep 9, 2019 at 10:16 AM Marek Gregor <marek....@gmail.com> wrote:

Hello Lukas,

thanks a lot for response, I am sorry, I have not noticed the obvious bug in my DDL, the reflection warnings deceived me.

No worries at all! What jOOQ distribution did you get those reflection warnings with? There's a known issue in the jOOQ Open Source Edition where those warnings persist, but they should have been fixed in the commercial JDK 11 distributions...

I have not realized that parsing unqualified column names is not so straightforward to implement, I think existing state - using qualified names is perfectly fine for use.

Yes, unfortunately, but rest assured, this is a high priority for us.

Feature of parsing with meta lookups (.withParseWithMetaLookups(THROW_ON_FAILURE)) is perfect for validation of queries without even running them, this I think will be killer feature for any reporting tool ;). In DslContext.meta() I miss only one thing - possibility to list and run stored procedures (org.jooq.Routine) in some dynamic way without static generation. But this could be also implemented by me when needed, by reverse engineering code of org.jooq.codegen.JavaGenerator#generateRoutines in a way to support dynamic calling of routines without code generation ...

That is on our roadmap. We might need to implement a few prerequisites first, including a routine meta model (which is currently mixed with the actual routine call):

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

Definitely something we'd like to add soon as well, as we're also going to support parsing routine DDL statements, such as CREATE FUNCTION and CREATE PROCEDURE.

Thanks,

Lukas

Marek Gregor

unread,

Sep 9, 2019, 5:42:30 AM9/9/19

to jooq...@googlegroups.com

Thanks for clarification, I have tried <groupId>org.jooq</groupId>,<artifactId>jooq</artifactId>,<version>3.12.0</version>.

po 9. 9. 2019 o 11:17 Lukas Eder <lukas...@gmail.com> napísal(a):

--

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/2wPGf6uB5fI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7idb5UxrNU8Lr0WAhkdHHaR1ZnaO50JqkLYQzRfxQTSw%40mail.gmail.com.

Lukas Eder

unread,

Sep 9, 2019, 6:01:51 AM9/9/19

to jOOQ User Group

I see. Yes, that version still suffers from https://github.com/jOOQ/jOOQ/issues/9157. I'm hoping to fix this for one of the next patch releases.

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CACtQWuTJECGZO9cf0s91HxBWaDVvnAVaBYCSHvAUQNoSa379fw%40mail.gmail.com.

Reply all

Reply to author

Forward