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
> On Jun 10, 2020, at 1:04 AM, Lukas Eder <[email protected]> wrote:
>
> 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 <[email protected]
> <mailto:[email protected]>> 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’.
>
>> On May 28, 2020, at 2:11 AM, Lukas Eder <[email protected]
>> <mailto:[email protected]>> wrote:
>>
>> The way it is now, you need to invoke the SQL code generation context. Just
>> call Query.getSQL() and discard the generated SQL string...
>>
>> On Thu, May 28, 2020 at 3:11 AM Ryan Wisnesky <[email protected]
>> <mailto:[email protected]>> wrote:
>> 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/
>> <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?
>>
>>> On May 27, 2020, at 1:39 AM, Ryan Wisnesky <[email protected]
>>> <mailto:[email protected]>> wrote:
>>>
>>> Perfect - I’ll use VisitListener and keep an eye out for the new API.
>>> Thank you for answering such basic questions!
>>>
>>>> On May 27, 2020, at 1:32 AM, Lukas Eder <[email protected]
>>>> <mailto:[email protected]>> wrote:
>>>>
>>>> There already is VisitListener, but we're working on a much better
>>>> approach: https://github.com/jOOQ/jOOQ/issues/9163
>>>> <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.
>>>>
>>>> On Wed, May 27, 2020 at 10:16 AM Ryan Wisnesky <[email protected]
>>>> <mailto:[email protected]>> wrote:
>>>> 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
>>>>
>>>>> On May 27, 2020, at 1:09 AM, Lukas Eder <[email protected]
>>>>> <mailto:[email protected]>> wrote:
>>>>>
>>>>> 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/
>>>>> <https://www.jooq.org/doc/latest/manual/sql-building/sql-interpreter/>
>>>>>
>>>>> Does that help?
>>>>>
>>>>>
>>>>> On Wed, May 27, 2020 at 6:53 AM <[email protected]
>>>>> <mailto:[email protected]>> wrote:
>>>>> 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
>>>>>
>>>>> --
>>>>> 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 [email protected]
>>>>> <mailto:[email protected]>.
>>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/jooq-user/129508a9-e951-4c0d-b114-f4bb19e027f4%40googlegroups.com
>>>>>
>>>>> <https://groups.google.com/d/msgid/jooq-user/129508a9-e951-4c0d-b114-f4bb19e027f4%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>>
>>>>> --
>>>>> 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
>>>>> <https://groups.google.com/d/topic/jooq-user/VKee0sUdG-w/unsubscribe>.
>>>>> To unsubscribe from this group and all its topics, send an email to
>>>>> [email protected]
>>>>> <mailto:[email protected]>.
>>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/jooq-user/CAB4ELO6hBSsKKL%2BFj39dDnPiKPSrPCB43-hK2htNRdQLevOEpQ%40mail.gmail.com
>>>>>
>>>>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO6hBSsKKL%2BFj39dDnPiKPSrPCB43-hK2htNRdQLevOEpQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>>>>
>>>>
>>>> --
>>>> 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 [email protected]
>>>> <mailto:[email protected]>.
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/jooq-user/C91B8EF1-DC30-423F-B029-9DDC36BF496D%40conexus.com
>>>>
>>>> <https://groups.google.com/d/msgid/jooq-user/C91B8EF1-DC30-423F-B029-9DDC36BF496D%40conexus.com?utm_medium=email&utm_source=footer>.
>>>>
>>>> --
>>>> 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
>>>> <https://groups.google.com/d/topic/jooq-user/VKee0sUdG-w/unsubscribe>.
>>>> To unsubscribe from this group and all its topics, send an email to
>>>> [email protected]
>>>> <mailto:[email protected]>.
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/jooq-user/CAB4ELO5KrHZ0CkSMNXTO9EeZ1297bvAhs7vczMkezF%2BeDtyMng%40mail.gmail.com
>>>>
>>>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO5KrHZ0CkSMNXTO9EeZ1297bvAhs7vczMkezF%2BeDtyMng%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>>>
>>
>>
>> --
>> 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 [email protected]
>> <mailto:[email protected]>.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/jooq-user/E4E4ADCB-4574-4DEB-9236-3FAA2BF546F2%40conexus.com
>>
>> <https://groups.google.com/d/msgid/jooq-user/E4E4ADCB-4574-4DEB-9236-3FAA2BF546F2%40conexus.com?utm_medium=email&utm_source=footer>.
>>
>> --
>> 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
>> <https://groups.google.com/d/topic/jooq-user/VKee0sUdG-w/unsubscribe>.
>> To unsubscribe from this group and all its topics, send an email to
>> [email protected]
>> <mailto:[email protected]>.
>> 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
>>
>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO5UVC-b_2YQzDyWuaNSC_-aSWsnKvAMR5%3DrR_MV5-txRQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>
>
> --
> 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 [email protected]
> <mailto:[email protected]>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/AF072D37-6381-466B-AF7F-FE3BF4B01F82%40conexus.com
>
> <https://groups.google.com/d/msgid/jooq-user/AF072D37-6381-466B-AF7F-FE3BF4B01F82%40conexus.com?utm_medium=email&utm_source=footer>.
>
> --
> 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
> <https://groups.google.com/d/topic/jooq-user/VKee0sUdG-w/unsubscribe>.
> To unsubscribe from this group and all its topics, send an email to
> [email protected]
> <mailto:[email protected]>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/CAB4ELO5vJyoF45nxwji5gWcoVcyJ2dQKfFtesHA-6KBz0Emp%2BA%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO5vJyoF45nxwji5gWcoVcyJ2dQKfFtesHA-6KBz0Emp%2BA%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/3E207FCD-37FC-40FC-AD83-F09784E4C9E4%40conexus.com.
