DDLDatabase without code generation?

[ry...@conexus.com]

Hi all,

I have a use case which I think should be easy for JOOQ but I can't seem to figure out because of the heavy emphasis on code generation and my newness to JOOQ.  Basically, I'm trying to take arbitrary DDL (say, as read from a file), turn it into a DDLDatabase, and then establish a JDBC or other direct connection to the resulting H2 database in order to analyze it - examine its column structure, primary and foreign keys, etc.  However, all of the examples I've run across do code generation, which is not required in this scenario.

On a related note, the reason I'm going through H2 / DDLDatabase at all is simply to have access to a SQL grammar (i.e., classes I can write a visitor over / process with structural recursion), and H2 has such classes.  But if JOOQ has a bonafide SQL grammar that all of its input dialects parse into, I'd prefer to use that instead of H2's.  However, in looking through the JOOQ documentation it seems that the only functions one can really write out of "JOOQ SQL" are those that pass through JOOQ code generation on the way to a target SQL dialect.  But in my use case, the target will not be another SQL dialect.

Any help greatly appreciated,

Ryan

[Lukas Eder]

Hi Ryan,

Thanks for your message. You can use DSLContext.meta(String) to get jOOQ to interpret the DDL for you and read the meta data using the org.jooq.Meta API. There isn't even an H2 database behind the scenes (and we'll try to remove the H2 dependency from DDLDatabase also in the future). Details here:

https://www.jooq.org/doc/latest/manual/sql-building/sql-interpreter/ 

Does that help?

 

--
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/129508a9-e951-4c0d-b114-f4bb19e027f4%40googlegroups.com.

[Ryan Wisnesky]

Thanks!  That does indeed solve my use case of getting metadata out of sql/ddl files.  

Any thoughts about processing JOOQ’s internal SQL representation with e.g., a visitor?  The intended use case is not to be broad across SQL features, but rather be broad across vendors in parsing the ‘pure conjunctive queries’ fragment of SQL (basically, just select from where), which is to be translated into a non-SQL formalism.  

Thanks again,

Ryan

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/VKee0sUdG-w/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/CAB4ELO6hBSsKKL%2BFj39dDnPiKPSrPCB43-hK2htNRdQLevOEpQ%40mail.gmail.com.

[Lukas Eder]

There already is VisitListener, but we're working on a much better approach:  https://github.com/jOOQ/jOOQ/issues/9163. If you're not looking for complete SQL feature support, you can get quite far with the existing VisitListener.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/C91B8EF1-DC30-423F-B029-9DDC36BF496D%40conexus.com.

[Ryan Wisnesky]

Perfect - I’ll use VisitListener and keep an eye out for the new API.  Thank you for answering such basic questions!  

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

[Ryan Wisnesky]

Quick follow-up: how do I invoke a VisitListener outside of a SQL code generation context?  I have a Query, and a VisitListener implementation, but am unclear where the “accept(VisitListener l)” method lives.  This tutorial, while helping a great deal about how to set up a stack to essentially turn a SAX parser into a DOM parser, https://blog.jooq.org/tag/visitlistener/ , invokes the listener as part of a derived DSLContext, but I’m only interested in the final state of the VisitListener, and have no new DSLContext (the final state of VisitListener will contain e.g. statistics about the query).  Any pointers here?

[Lukas Eder]

The way it is now, you need to invoke the SQL code generation context. Just call Query.getSQL() and discard the generated SQL string...

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/E4E4ADCB-4574-4DEB-9236-3FAA2BF546F2%40conexus.com.

[Ryan Wisnesky]

Thanks!  That’s working but now I have a dilemma about VisitListener.  In particular, Clause is deprecated, but I’m not sure how to dispatch based on the type of QueryPart alone, especially since most implementing classes are not visible.  For example, suppose I just parsed “SELECT * FROM …” or “SELECT A FROM …” into a QueryPart, and now I want to do something different when I encounter an org.jooq.impl.AsteriskImpl than when I encounter an org.jooq.impl.FieldAlias.  Can I do that without using Clause? How do I actually access the data inside the Impl classes (e.g., get the list of field names), if the Impl classes are not visible?  My current solution to this problem is to print the SQL into H2, and then use H2’s parser to parse into H2’s abstract syntax classes, and then manipulate those, but I’m hoping there’s a way to avoid H2 when processing queries (just like there is when processing meta data) - my use case is JOOQ as ‘abstract syntax for the relational algebra fragment common to most SQL vendors’.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5UVC-b_2YQzDyWuaNSC_-aSWsnKvAMR5%3DrR_MV5-txRQ%40mail.gmail.com.

[Lukas Eder]

Hi Ryan,

What exactly are you trying to do? The original question was about DDL, not about generic SQL transformation...

Thanks,

Lukas

On Wed, Jun 10, 2020 at 7:40 AM Ryan Wisnesky <ry...@conexus.com> wrote:

Thanks!  That’s working but now I have a dilemma about VisitListener.  In particular, Clause is deprecated, but I’m not sure how to dispatch based on the type of QueryPart alone, especially since most implementing classes are not visible.  For example, suppose I just parsed “SELECT * FROM …” or “SELECT A FROM …” into a QueryPart, and now I want to do something different when I encounter an org.jooq.impl.AsteriskImpl than when I encounter an org.jooq.impl.FieldAlias.  Can I do that without using Clause? How do I actually access the data inside the Impl classes (e.g., get the list of field names), if the Impl classes are not visible?  My current solution to this problem is to print the SQL into H2, and then use H2’s parser to parse into H2’s abstract syntax classes, and then manipulate those, but I’m hoping there’s a way to avoid H2 when processing queries (just like there is when processing meta data) - my use case is JOOQ as ‘abstract syntax for the relational algebra fragment common to most SQL vendors’.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/AF072D37-6381-466B-AF7F-FE3BF4B01F82%40conexus.com.

[Ryan Wisnesky]

Yeah, I have been asking about a few different tasks on this list; here’s the high-level overview of this particular task.  Suppose I have a small model of flat select-from-where syntax (relational conjunctive queries), with say fully named AS clauses; something like:

 class Flower {

 Map<String, Pair<String, String>> select;

 Map<String, String> from;

 Formula where;

Anyway, I’ve used JOOQ to ingest arbitrary SQL queries; now, I just want to map as many of those queries as possible into the above model, rejecting those that aren’t flat select-from-where-queries or whose where clauses contain symbols that aren’t on a small list of symbols known in advance.  Right now, I can think of two approaches:

 - print the JOOQ ingested SQL to H2, and use the H2 parser to get an abstract syntax tree to then process to create the above Flower object, or

 - use a JOOQ visitor to construct the Flower object as the JOOQ ingested SQL is printed via a getSQL.  

Thanks again,

Ryan 

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

[Lukas Eder]

I see thanks for the clarification.

Well, we have what's there. The need for an improved way to pattern match our SQL expression trees has been recognised: https://github.com/jOOQ/jOOQ/issues/7642, https://github.com/jOOQ/jOOQ/issues/9163, and various others

Until then, you're going to need to use reflection, or some other trick to access internal API, if you want to use jOOQ for this.

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3E207FCD-37FC-40FC-AD83-F09784E4C9E4%40conexus.com.

[Ryan Wisnesky]

Understood; we’ll probably go the JOOQ -> H2 AST route and eagerly await future JOOQ versions.

I do have a follow-up question about the other task (metadata import) I’ve been evaluating JOOQ for. In particular, the ‘Meta’ object is great for reading off primary keys, foreign keys, uniqueness constraints, check constraints, and column data types. But I can’t seem to find a way to read off ’non-null constraints’. Does the Meta (or some other) object have information about those?

Thanks again,
Ryan

> To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6Q%3DWUqL6HXAweXg_-y81GLtxT%2B5csS6mO1E%3DgVf1Nobw%40mail.gmail.com.

[Lukas Eder]

Use DataType.nullable()

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CBD9407A-3899-4CDC-950B-14FF72051901%40conexus.com.

[Ryan Wisnesky]

Thanks again!  I’m still making progress with my use case and had a few more quick questions: when emitting SQL code, is there a way to force:

 - implicit type coercions to made explicit?  i.e., “1” = 1 ~~~> cast “1” as integer = 1 

 - "select *" to emit as the named expressions?  I.e., select * ~~~> select x.y as z, p.q as r …

 - “from" to name every column?  i.e., from A, B ~~~> from A as a, B.b as b

 - “where” to qualify every field reference?  i.e., where A = B ~~~> a.A = b.B 

 - “join" syntax into select/from/where syntax

-Ryan

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

[Lukas Eder]

Hi Ryan,

Thanks for your message.

I'm assuming you're still using the parser and try to transform the generated SQL in jOOQ. You'll have to find a way to detect the cases that you've mentioned below, but once you do, I think all is possible:

On Mon, Jun 22, 2020 at 6:31 PM Ryan Wisnesky <ry...@conexus.com> wrote:

Thanks again!  I’m still making progress with my use case and had a few more quick questions: when emitting SQL code, is there a way to force:

 - implicit type coercions to made explicit?  i.e., “1” = 1 ~~~> cast “1” as integer = 1 

Use Field.cast(DataType) or DSL.cast(Field, DataType), they're equivalent

 

 - "select *" to emit as the named expressions?  I.e., select * ~~~> select x.y as z, p.q as r …

It depends on much you insist on precisely this transformation. The simplest transformation would be to just wrap the query in a derived table and work with Select.getSelect().

 

 - “from" to name every column?  i.e., from A, B ~~~> from A as a, B.b as b

I'm not sure what this means

 

 - “where” to qualify every field reference?  i.e., where A = B ~~~> a.A = b.B 

The parser does this if you enable Settings.parseWithMetaLookups (you will have to provide jOOQ with some Meta data source via Configuration.set(MetaProvider)). But when the parser does this, it doesn't transform the SQL currently to produce qualified identifiers in the output.

What's the use case of this transformation?

 

 - “join" syntax into select/from/where syntax

You can do this via: https://www.jooq.org/doc/3.13/manual/sql-building/queryparts/sql-transformation/ansi-join-to-table-lists/

In the future, we'll implement quite a few such SQL transformations out of the box:

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

I will create a few issues for the ones you've mentioned here, I think they could be quite useful for others. If you have more such use-cases, I'd love to hear about them!

Lukas

[Ryan Wisnesky]

Thanks for your rapid response!  Here’s some clarifications:

On Jun 22, 2020, at 9:54 AM, Lukas Eder <lukas...@gmail.com> wrote:

Hi Ryan,

Thanks for your message.

I'm assuming you're still using the parser and try to transform the generated SQL in jOOQ. You'll have to find a way to detect the cases that you've mentioned below, but once you do, I think all is possible:

On Mon, Jun 22, 2020 at 6:31 PM Ryan Wisnesky <ry...@conexus.com> wrote:

Thanks again!  I’m still making progress with my use case and had a few more quick questions: when emitting SQL code, is there a way to force:

 - implicit type coercions to made explicit?  i.e., “1” = 1 ~~~> cast “1” as integer = 1 

Use Field.cast(DataType) or DSL.cast(Field, DataType), they're equivalent

Here I’m asking for these to be made present in the SQL that is printed, so that a query that JOOQ parses as “1” = 1 it would print as cast “1” as integer = 1.  

 

 - "select *" to emit as the named expressions?  I.e., select * ~~~> select x.y as z, p.q as r …

It depends on much you insist on precisely this transformation. The simplest transformation would be to just wrap the query in a derived table and work with Select.getSelect().

I’d like for JOOQ to parse “Insert Into Employees Select * From Employees” and have JOOQ print “Insert Into Employees Select e.name as name, e.id as id from Employees as E"

 

 - “from" to name every column?  i.e., from A, B ~~~> from A as a, B.b as b

I'm not sure what this means

I’d like for JOOQ to parse

From Employee, Department

Where Employee.id = Department.name

And have it print:

From Employee as E, Department as d

Where e.id = d.name

i.e. have JOOQ insert as many temporary table names as it can.

 

 - “where” to qualify every field reference?  i.e., where A = B ~~~> a.A = b.B 

The parser does this if you enable Settings.parseWithMetaLookups (you will have to provide jOOQ with some Meta data source via Configuration.set(MetaProvider)). But when the parser does this, it doesn't transform the SQL currently to produce qualified identifiers in the output.

What's the use case of this transformation?

I’m trying to use JOOQ as a “universal parser” for a very simple select/from/where fragment of SQL with a simple abstract syntax, but across a bunch of vendors at once.  But perhaps this is the same problem as the problem above - how to insert new variables in from clauses.  

 

 - “join" syntax into select/from/where syntax

You can do this via: https://www.jooq.org/doc/3.13/manual/sql-building/queryparts/sql-transformation/ansi-join-to-table-lists/

In the future, we'll implement quite a few such SQL transformations out of the box:

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

I will create a few issues for the ones you've mentioned here, I think they could be quite useful for others. If you have more such use-cases, I'd love to hear about them!

Will do!

-Ryan 

Lukas

--
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/VKee0sUdG-w/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/CAB4ELO6Gzmb2zdxY%2BWn3-9LrBMPNBDqO1c5zxqi3zPUD1QdFAw%40mail.gmail.com.

[Lukas Eder]

On Mon, Jun 22, 2020 at 7:31 PM Ryan Wisnesky <ry...@conexus.com> wrote:

What's the use case of this transformation?

I’m trying to use JOOQ as a “universal parser” for a very simple select/from/where fragment of SQL with a simple abstract syntax, but across a bunch of vendors at once.  But perhaps this is the same problem as the problem above - how to insert new variables in from clauses.

But jOOQ's out of the box parser / renderer can already do that. It can parse this simple syntax (or even more complex syntax), and then transform it to whatever dialect is currently supported. You seem to want to have some (cosmetic?) transformations in the generated SQL, which are out of scope for what jOOQ can do right now. Of course, we can add these things eventually, but they will take time to implement.

My question here is less related to the question *what* you want to achieve, but to the *why*. Why do you think ANSI joins work less well? Why do you think all columns have to be qualified? Why do you prefer all tables to be aliased? Without understanding the "whys", I don't think I can provide you with the best possible "hows"...

Lukas

[Ryan Wisnesky]

The short answer to your question is that we’re doing static analysis of SQL queries, trying to prove that they have certain properties via automated techniques.  As such, these transformations are not so much cosmetic as part of a normalization pipeline to construct first order logic from SQL; at no point do we ever run any SQL.  Really what I’m looking for is an SQL -> relational algebra translator that is as complete as is theoretically possible, and JOOQ seems pretty close.  

--
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/VKee0sUdG-w/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/CAB4ELO6xsCzem48B0FkXoR%3D8KL41drZBf1sN_PiGjvyL9DKFhQ%40mail.gmail.com.

[Lukas Eder]

Thanks for the clarifications. I see, that makes sense.

I've had similar ideas in the past for different reasons. The jOOQ diagnostics feature set is a prototype where we will analyse (and for that: normalise) SQL to a more standardised set of features and syntaxes. One reason why we want to do this is to detect duplicate, or "similar" SQL to reduce the load on execution plan caches and the planner in general:

https://www.jooq.org/doc/latest/manual/sql-execution/diagnostics/ 

This is not very advanced yet, but very promising. So, given that the features you're looking for, and what we've already started doing overlap to some extent, I'm positive we can add much more value in this area in the future.

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/86158B01-29C1-47FF-A100-77F91EF40D6F%40conexus.com.