How to handle names etc. for Common Table Expressions?
72 views
Skip to first unread message
Thorsten Schöning
Jan 31, 2020, 7:15:13 AM1/31/20
to jooq...@googlegroups.com
Hi all,
I have multiple different plain SQL-files using Common Table
Expressions and need to migrate those to Java. Background is automatic
reporting which gets enhanced in one of our software and it's most
likely that lots of reports get added the same way I'm trying to do
now. So things should be used as templates as much as possible.
CTEs are query-level and therefore not part of the classes jOOQ
generates based on some schema for Java, that's why I wonder how to
best handle names of CTEs themself, the columns they produce etc.
In SQL-files, everything is simply text and in case of changed names,
one uses search&replace of the editor of choice most likely. With Java
OTOH, one could define classes and variables containing names to only
maintain those at one place. Pretty much what jOOQ is doing when
generating classes for tables etc., only that I need to do it myself.
But is that worth it compared to simply using strings in Java as well?
Search&Replace within files/classes isn't more difficult than in
SQL-files. The official docs for CTEs in jOOQ simply use strings for
names as well:
https://www.jooq.org/doc/3.12/manual/sql-building/sql-statements/with-clause/
Would be great if people would simply tell how they deal with those
names in their software. I've additionally attached the result of
converting two CTEs I need into Java and what came into my mind to not
need to duplicate names and stuff. But I'm unsure if it's worth it...
Thanks for your opinions!
Mit freundlichen Grüßen,
Thorsten Schöning
--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/
Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04
AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow
I have multiple different plain SQL-files using Common Table
Expressions and need to migrate those to Java. Background is automatic
reporting which gets enhanced in one of our software and it's most
likely that lots of reports get added the same way I'm trying to do
now. So things should be used as templates as much as possible.
CTEs are query-level and therefore not part of the classes jOOQ
generates based on some schema for Java, that's why I wonder how to
best handle names of CTEs themself, the columns they produce etc.
In SQL-files, everything is simply text and in case of changed names,
one uses search&replace of the editor of choice most likely. With Java
OTOH, one could define classes and variables containing names to only
maintain those at one place. Pretty much what jOOQ is doing when
generating classes for tables etc., only that I need to do it myself.
But is that worth it compared to simply using strings in Java as well?
Search&Replace within files/classes isn't more difficult than in
SQL-files. The official docs for CTEs in jOOQ simply use strings for
names as well:
https://www.jooq.org/doc/3.12/manual/sql-building/sql-statements/with-clause/
Would be great if people would simply tell how they deal with those
names in their software. I've additionally attached the result of
converting two CTEs I need into Java and what came into my mind to not
need to duplicate names and stuff. But I'm unsure if it's worth it...
Thanks for your opinions!
Mit freundlichen Grüßen,
Thorsten Schöning
--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/
Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04
AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow
Thorsten Schöning
Jan 31, 2020, 1:06:05 PM1/31/20
to jooq...@googlegroups.com
Guten Tag Thorsten Schöning,
am Freitag, 31. Januar 2020 um 13:15 schrieben Sie:
> CTEs are query-level and therefore not part of the classes jOOQ
> generates based on some schema for Java, that's why I wonder how to
> best handle names of CTEs themself, the columns they produce etc.
I tried following the approach jOOQ is doing by simply creating
classes with fields for each CTE and their columns. In theory the CTE
itself is the same like a table jOOQ would generate columns for, so I
as well really created one field per column per CTE.
The problem is that things feel bloated already after converting only
one of my queries. I've attached an example again.
The only alternative coming into my mind is outsourcing the individual
SELECT-statements into a view, for which jOOQ would create classes
etc. automatically again. But doing this for each and every CTE for
each and every report in question doesn't feel that right as well.
Every change in any CTE would involve a change in the database schema,
where it seems far easier to change Java-code instead.
The good thing on creating fields and stuff manually for all the CTEs
is that types are available everywhere needed and one doesn't need to
handle that within the query itself. Like in "cte.field(...)" one can
simply reference an already available field definition and jOOQ takes
that name, type etc. At other places, only the name of that field
could be used as needed.
Still need to think about this...
am Freitag, 31. Januar 2020 um 13:15 schrieben Sie:
> CTEs are query-level and therefore not part of the classes jOOQ
> generates based on some schema for Java, that's why I wonder how to
> best handle names of CTEs themself, the columns they produce etc.
classes with fields for each CTE and their columns. In theory the CTE
itself is the same like a table jOOQ would generate columns for, so I
as well really created one field per column per CTE.
The problem is that things feel bloated already after converting only
one of my queries. I've attached an example again.
The only alternative coming into my mind is outsourcing the individual
SELECT-statements into a view, for which jOOQ would create classes
etc. automatically again. But doing this for each and every CTE for
each and every report in question doesn't feel that right as well.
Every change in any CTE would involve a change in the database schema,
where it seems far easier to change Java-code instead.
The good thing on creating fields and stuff manually for all the CTEs
is that types are available everywhere needed and one doesn't need to
handle that within the query itself. Like in "cte.field(...)" one can
simply reference an already available field definition and jOOQ takes
that name, type etc. At other places, only the name of that field
could be used as needed.
Still need to think about this...
Lukas Eder
Feb 6, 2020, 8:43:10 AM2/6/20
to jOOQ User Group
Hi Thorsten,
Thanks for describing your very interesting use case.
In theory the CTE itself is the same like a table jOOQ would generate columns for, so I as well really created one field per column per CTE.
Absolutely! Eventually, we're hoping to be able to offer more powerful APIs to construct such "tables", which are really views:
It doesn't matter if the views are used as derived tables, CTE, correlated subqueries, or actual views. The programming model is very promising, but it isn't easy to extract a simple enough API from it.
The only alternative coming into my mind is outsourcing the individual SELECT-statements into a view, for which jOOQ would create classes etc. automatically again. But doing this for each and every CTE for each and every report in question doesn't feel that right as well. Every change in any CTE would involve a change in the database schema, where it seems far easier to change Java-code instead.
Views are definitely a very good alternative to using jOOQ in some cases, and combine well with jOOQ for partially dynamic SQL. jOOQ's biggest value proposition is with dynamic SQL. Once SQL is static, native SQL can sometimes be easier to work with.
Do note that you can also work with string-based SQL and parse that using jOOQ's DSLContext::parser API:
We're going to invest a lot more into this parser API in the future, as we see it to be a very promising product on its own. See also these two useful applications here, which rely on parsing:
The good thing on creating fields and stuff manually for all the CTEs
is that types are available everywhere needed and one doesn't need to
handle that within the query itself. Like in "cte.field(...)" one can
simply reference an already available field definition and jOOQ takes
that name, type etc. At other places, only the name of that field
could be used as needed.
Your CteDefs class is a good start, but I would recommend you use CustomTable subtypes instead, more similar to generated code:
The main benefit is that your tables will know their fields, and you don't have to register them again.
Clearly, there isn't a silver bullet here. Native SQL is a bit easier to edit than jOOQ code, but in pure string form, it is not type safe. In stored view form, it is type safe and available to the code generator, but less dynamic. Usage of such non-dynamic views heavily depends on join elimination and other non-cost model SQL transformations, and you may not have that, depending on the RDBMS you're using: https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/. So, using jOOQ can lead to better performance than using native SQL, because it is much easier to make SQL logic reusable through dynamicity.
- Lukas
Thorsten Schöning
Feb 7, 2020, 5:50:42 AM2/7/20
to jOOQ User Group
Guten Tag Lukas Eder,
am Donnerstag, 6. Februar 2020 um 14:43 schrieben Sie:
> Do note that you can also work with string-based SQL and parse that using
> jOOQ's DSLContext::parser API:
> https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/
If I understood things correctly, that doesn't help me too much: My
CTEs reference tables managed by the jOOQ code generator at some point
and I want to use those in favour of plain strings. If those tables
change, my CTEs instantly break during compile time, which doesn't
happen when using plain SQL-strings. That was one of the major
drawbacks of what I had implemented before quick&dirty.
So in my opinion things break down to if anything from the code
generator is used or not. If so, one wouldn't want to deal with plain
strings anymore at any place and express everything using names,
fields etc. from jOOQ instead I guess.
Even without using results from the code generator, using jOOQ instead
of plain SQL make things easier readable using methods and
Java-indentation than embedding complex strings in Java. Therefore I
thought of supporting SQL in pre-formatted files and those might be
forwarded to the jOOQ-parser then. But inlining anything else than
trivial SQL in Java doesn't feel right anymore.
> Your CteDefs class is a good start, but I would recommend you use
> CustomTable subtypes instead, more similar to generated code:
> https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/
Thanks, that's what I was actually looking for, as I wanted to mimic
the results of the generator as much as possible. So I ported one of
my tables to CustomTable and ran into some questions again:
How to handle the necessary CustomRecord? Does it need to implement
RecordX-interfaces with concrete types as well? Looks the following
currently:
> @SuppressWarnings("serial")
> static class TimePeriodRec extends CustomRecord<TimePeriodRec>
> {
> protected TimePeriodRec(Table<TimePeriodRec> table)
> {
> super(table);
> }
> }
Does it need to be like the following instead?
> @SuppressWarnings("serial")
> static class TimePeriodRec extends CustomRecord<TimePeriodRec> implements Record2<OffsetDateTime, OffsetDateTime>
That would be what the generator does:
> public class CltCmdRecord extends UpdatableRecordImpl<CltCmdRecord> implements Record8<[...]> {
Or is it about simply adding getters like the generator adds?
> /**
> * Getter for <code>clt_cmd.id</code>.
> */
> public Integer getId() {
> return (Integer) get(0);
> }
The second question is, how do I get an actual CTE using CustomTable?
I thought that I might be able to use the table instance where I use
the CTE, but that doesn't seem to be the case. Instead, I still need
to work with CTE-names and fields manually:
> return DSL.name(TIME_PERIOD.getName())
> .fields(TIME_PERIOD.START_AT.getName(),
> TIME_PERIOD.END_AT .getName())
> .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
vs. before:
> return TIME_PERIOD.NAME
> .fields(TIME_PERIOD.COL_START_AT.getName(),
> TIME_PERIOD.COL_END_AT .getName())
> .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
I thought I could save especially the ".fields(...)"-thing after
reading your following statement:
> The main benefit is that your tables will know their fields, and you don't
> have to register them again.
But didn't get it to work and looking at the docs, CTEs can only be
created by names etc. I don't see how to come from a table to a CTE.
So, by using CustomTable it seems I only follow the approach of the
code generator, but it actually doesn't make creating CTEs themself
easier?
If so, I might stick with my former approach of names and fields only,
because that way it's easier to not misuse as table what acutally is
only a container for CTE-names and fields.
am Donnerstag, 6. Februar 2020 um 14:43 schrieben Sie:
> Do note that you can also work with string-based SQL and parse that using
> jOOQ's DSLContext::parser API:
> https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/
CTEs reference tables managed by the jOOQ code generator at some point
and I want to use those in favour of plain strings. If those tables
change, my CTEs instantly break during compile time, which doesn't
happen when using plain SQL-strings. That was one of the major
drawbacks of what I had implemented before quick&dirty.
So in my opinion things break down to if anything from the code
generator is used or not. If so, one wouldn't want to deal with plain
strings anymore at any place and express everything using names,
fields etc. from jOOQ instead I guess.
Even without using results from the code generator, using jOOQ instead
of plain SQL make things easier readable using methods and
Java-indentation than embedding complex strings in Java. Therefore I
thought of supporting SQL in pre-formatted files and those might be
forwarded to the jOOQ-parser then. But inlining anything else than
trivial SQL in Java doesn't feel right anymore.
> Your CteDefs class is a good start, but I would recommend you use
> CustomTable subtypes instead, more similar to generated code:
> https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/
the results of the generator as much as possible. So I ported one of
my tables to CustomTable and ran into some questions again:
How to handle the necessary CustomRecord? Does it need to implement
RecordX-interfaces with concrete types as well? Looks the following
currently:
> @SuppressWarnings("serial")
> static class TimePeriodRec extends CustomRecord<TimePeriodRec>
> {
> protected TimePeriodRec(Table<TimePeriodRec> table)
> {
> super(table);
> }
> }
Does it need to be like the following instead?
> @SuppressWarnings("serial")
> static class TimePeriodRec extends CustomRecord<TimePeriodRec> implements Record2<OffsetDateTime, OffsetDateTime>
That would be what the generator does:
> public class CltCmdRecord extends UpdatableRecordImpl<CltCmdRecord> implements Record8<[...]> {
Or is it about simply adding getters like the generator adds?
> /**
> * Getter for <code>clt_cmd.id</code>.
> */
> public Integer getId() {
> return (Integer) get(0);
> }
The second question is, how do I get an actual CTE using CustomTable?
I thought that I might be able to use the table instance where I use
the CTE, but that doesn't seem to be the case. Instead, I still need
to work with CTE-names and fields manually:
> return DSL.name(TIME_PERIOD.getName())
> .fields(TIME_PERIOD.START_AT.getName(),
> TIME_PERIOD.END_AT .getName())
> .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
vs. before:
> return TIME_PERIOD.NAME
> .fields(TIME_PERIOD.COL_START_AT.getName(),
> TIME_PERIOD.COL_END_AT .getName())
> .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
I thought I could save especially the ".fields(...)"-thing after
reading your following statement:
> The main benefit is that your tables will know their fields, and you don't
> have to register them again.
created by names etc. I don't see how to come from a table to a CTE.
So, by using CustomTable it seems I only follow the approach of the
code generator, but it actually doesn't make creating CTEs themself
easier?
If so, I might stick with my former approach of names and fields only,
because that way it's easier to not misuse as table what acutally is
only a container for CTE-names and fields.
Lukas Eder
Feb 7, 2020, 7:34:30 AM2/7/20
to jOOQ User Group
On Fri, Feb 7, 2020 at 11:50 AM Thorsten Schöning <tscho...@am-soft.de> wrote:
So in my opinion things break down to if anything from the code
generator is used or not. If so, one wouldn't want to deal with plain
strings anymore at any place and express everything using names,
fields etc. from jOOQ instead I guess.
Sure. But since this is a public list, with a few folks lurking, I always like to open up discussions to broader topics, in case that particular approach would have been viable for someone else who did not know about these features...
Even without using results from the code generator, using jOOQ instead
of plain SQL make things easier readable using methods and
Java-indentation than embedding complex strings in Java. Therefore I
thought of supporting SQL in pre-formatted files and those might be
forwarded to the jOOQ-parser then. But inlining anything else than
trivial SQL in Java doesn't feel right anymore.
Let's wait until we have text blocks! :)
How to handle the necessary CustomRecord? Does it need to implement
RecordX-interfaces with concrete types as well? Looks the following
currently:
> @SuppressWarnings("serial")
> static class TimePeriodRec extends CustomRecord<TimePeriodRec>
> {
> protected TimePeriodRec(Table<TimePeriodRec> table)
> {
> super(table);
> }
> }
You'll have to provide a constructor without arguments. Typically, you'll reference the singleton CustomTable. This should definitely be added to the documentation and Javadoc:
Does it need to be like the following instead?
> @SuppressWarnings("serial")
> static class TimePeriodRec extends CustomRecord<TimePeriodRec> implements Record2<OffsetDateTime, OffsetDateTime>
It doesn't have to. The main benefit is that occasionally, you can use such a TimePeriodRec when a
Record2<OffsetDateTime, OffsetDateTime> would be required, e.g. in unions, or with row() constructor predicates. Usually, you don't need that.
That would be what the generator does:
> public class CltCmdRecord extends UpdatableRecordImpl<CltCmdRecord> implements Record8<[...]> {
Only if <recordsImplementingRecordN/> is activated (it is by default)
Or is it about simply adding getters like the generator adds?
> /**
> * Getter for <code>clt_cmd.id</code>.
> */
> public Integer getId() {
> return (Integer) get(0);
> }
You don't need those, either.
The second question is, how do I get an actual CTE using CustomTable?
I thought that I might be able to use the table instance where I use
the CTE, but that doesn't seem to be the case. Instead, I still need
to work with CTE-names and fields manually:
> return DSL.name(TIME_PERIOD.getName())
> .fields(TIME_PERIOD.START_AT.getName(),
> TIME_PERIOD.END_AT .getName())
> .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
vs. before:
> return TIME_PERIOD.NAME
> .fields(TIME_PERIOD.COL_START_AT.getName(),
> TIME_PERIOD.COL_END_AT .getName())
> .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
Note that you can use Field.getUnqualifiedName().
But yes indeed, I haven't finished thinking this through. You will eventually want to rely on https://github.com/jOOQ/jOOQ/issues/1969 , which has not yet been implemented.
I thought I could save especially the ".fields(...)"-thing after
reading your following statement:
> The main benefit is that your tables will know their fields, and you don't
> have to register them again.
But didn't get it to work and looking at the docs, CTEs can only be
created by names etc. I don't see how to come from a table to a CTE.
So, by using CustomTable it seems I only follow the approach of the
code generator, but it actually doesn't make creating CTEs themself
easier?
No, but it makes working with them easier, as they now behave like ordinary tables / views. You could (but perhaps that does not appear to be very clean) use your CTE definition in the WITH clause, but then reference your CustomTable in the FROM clause and its columns in all other clauses. In principle, you could even automate the addition of the CTE definitions as soon as you reference any of your CustomTable instances in some query.
Thorsten Schöning
Feb 7, 2020, 9:23:19 AM2/7/20
to jOOQ User Group
Guten Tag Lukas Eder,
am Freitag, 7. Februar 2020 um 13:34 schrieben Sie:
> Sure. But since this is a public list, with a few folks lurking, I always
> like to open up discussions to broader topics[...]
And that's exactly how I understood your mail and why I brought my
opinion into this discussion. I see these lists as a valuable place to
document thoughts as well.
> No, but it makes working with them easier, as they now behave like ordinary
> tables / views.
I still don't get it: There's no place where I can interchange CTEs
and my custom tables, only reuse individual components. The only thing
I can see working is what you say seems a bit unclear:
> You could (but perhaps that does not appear to be very
> clean) use your CTE definition in the WITH clause, but then reference your
> CustomTable in the FROM clause and its columns in all other clauses.
Or am I understanding your suggestion wrongly and I need to really
implement CTE-SQL on my custom tables somehow? Like is done in the
docs for custom fields, but I can't override "accept".
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/
am Freitag, 7. Februar 2020 um 13:34 schrieben Sie:
> Sure. But since this is a public list, with a few folks lurking, I always
And that's exactly how I understood your mail and why I brought my
opinion into this discussion. I see these lists as a valuable place to
document thoughts as well.
> No, but it makes working with them easier, as they now behave like ordinary
> tables / views.
and my custom tables, only reuse individual components. The only thing
I can see working is what you say seems a bit unclear:
> You could (but perhaps that does not appear to be very
> clean) use your CTE definition in the WITH clause, but then reference your
> CustomTable in the FROM clause and its columns in all other clauses.
implement CTE-SQL on my custom tables somehow? Like is done in the
docs for custom fields, but I can't override "accept".
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/
Lukas Eder
Feb 7, 2020, 9:37:33 AM2/7/20
to jOOQ User Group
On Fri, Feb 7, 2020 at 3:23 PM Thorsten Schöning <tscho...@am-soft.de> wrote:
> No, but it makes working with them easier, as they now behave like ordinary
> tables / views.
I still don't get it: There's no place where I can interchange CTEs
and my custom tables, only reuse individual components.
In your CteUsage.java file, the version from Jan 31, 2020, 7:06 PM, instead of doing this:
CommonTableExpression<Record8<Integer, String, String, Long, OffsetDateTime, MeterMfctCode, String, MeterType>> recsInTimePeriod = this.cteRecsInTimePeriod();
and then this:
recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_MFCT_CODE),
You would have a globally available variable RECS_IN_TIME_PERIOD (ffrom your RecsInTimePeriod CustomTable), and you could type safely dereference columns from it, e.g. RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE
I.e.
private CommonTableExpression<Record7<MeterMfctCode, String, MeterType, String, String, Integer, OffsetDateTime>>
cteReAndCltPerMeterLid()
{
CommonTableExpression<Record8<Integer, String, String, Long, OffsetDateTime, MeterMfctCode, String, MeterType>> recsInTimePeriod = this.cteRecsInTimePeriod();
// Still need to do some trickery when declaring the CTE
return CteDefs.ReAndCltPerMeterLid.CTE
.fields
(
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_MFCT_CODE.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_READING_SERIAL.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_TYPE.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_REAL_ESTATE_NR.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_CLT_MAC.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_LID_CNT.getName(),
CteDefs.RE_AND_CLT_PER_METER_LID.COL_METER_LID_LAST_CAPTURED_AT.getName()
)
.as
(
// But when using the CTE, it looks like any ordinary SQL
DSL.select
(
RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE,
RECS_IN_TIME_PERIOD.COL_METER_READING_SERIAL,
RECS_IN_TIME_PERIOD.COL_METER_TYPE,
RECS_IN_TIME_PERIOD.COL_REAL_ESTATE_NR,
RECS_IN_TIME_PERIOD.COL_CLT_MAC,
DSL.count().as(RE_AND_CLT_PER_METER_LID.COL_METER_LID_CNT),
DSL
.max(RECS_IN_TIME_PERIOD.COL_CLT_REC_CAPTURED_AT))
.as(RE_AND_CLT_PER_METER_LID.COL_METER_LID_LAST_CAPTURED_AT)
)
.from(RECS_IN_TIME_PERIOD)
.groupBy
(
RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE),
RECS_IN_TIME_PERIOD.COL_METER_READING_SERIAL),
RECS_IN_TIME_PERIOD.COL_METER_TYPE),
RECS_IN_TIME_PERIOD.COL_REAL_ESTATE_NR),
RECS_IN_TIME_PERIOD.COL_CLT_MAC)
)
);
}
Does this make sense?
Or am I understanding your suggestion wrongly and I need to really
implement CTE-SQL on my custom tables somehow? Like is done in the
docs for custom fields, but I can't override "accept".
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/
No, forget accept(). That's when you want to be in control of the generated SQL, mostly useful with CustomField. Imagine DSL.field("abc"), but with the capability of switching over the SQLDialect to produce dialect specific SQL strings.
Thorsten Schöning
Feb 7, 2020, 10:15:46 AM2/7/20
to jooq...@googlegroups.com
Guten Tag Thorsten Schöning,
am Freitag, 31. Januar 2020 um 19:06 schrieben Sie:
> I tried following the approach jOOQ is doing by simply creating
> classes with fields for each CTE and their columns.[...]
One can get exactly what jOOQ does by using the code generator with a
schema for a XMLDatabase:
https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml/
That schema would need to describe the name of the CTE and its columns
and jOOQ would simply generate code for that like it does with tables
from a database. In other messages of this thread, Lukas Eder
suggested to implement CustomTable instead of using a class with names
and fields only like I did, so using the generator directly is pretty
close.
One only needs different setups of the generator, one using the
database as a source, another one using the XML-schema. That might
contain CTE-"tables" for all CTEs of all queries following some naming
scheme or one could create individual schemata for individual queries.
The generator restricts its output to only one target package currently
and stuff like that, while I most likely have many different CTEs in
many different queries in many different packages.
Managing all those different tables isn't that easy currently: Things
are tables for the generator, so classes are put into packages like
"[...].ctes.tables[.records]". While one might prefer something like
"[...].ctes[.records]" directly only. That way one might mix things
into other jOOQ-packages, like in the following example:
> [...].db.jooq
> [...].db.jooq.ctes
> [...].db.jooq.ctes.tables
> [...].db.jooq.ctes.tables.records
> [...].db.jooq.enums
> [...].db.jooq.tables
> [...].db.jooq.tables.record
I'm attaching my generated files and their config for references,
maybe someone finds that approach interesting.
am Freitag, 31. Januar 2020 um 19:06 schrieben Sie:
> I tried following the approach jOOQ is doing by simply creating
One can get exactly what jOOQ does by using the code generator with a
schema for a XMLDatabase:
https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml/
That schema would need to describe the name of the CTE and its columns
and jOOQ would simply generate code for that like it does with tables
from a database. In other messages of this thread, Lukas Eder
suggested to implement CustomTable instead of using a class with names
and fields only like I did, so using the generator directly is pretty
close.
One only needs different setups of the generator, one using the
database as a source, another one using the XML-schema. That might
contain CTE-"tables" for all CTEs of all queries following some naming
scheme or one could create individual schemata for individual queries.
The generator restricts its output to only one target package currently
and stuff like that, while I most likely have many different CTEs in
many different queries in many different packages.
Managing all those different tables isn't that easy currently: Things
are tables for the generator, so classes are put into packages like
"[...].ctes.tables[.records]". While one might prefer something like
"[...].ctes[.records]" directly only. That way one might mix things
into other jOOQ-packages, like in the following example:
> [...].db.jooq
> [...].db.jooq.ctes
> [...].db.jooq.ctes.tables
> [...].db.jooq.ctes.tables.records
> [...].db.jooq.enums
> [...].db.jooq.tables
> [...].db.jooq.tables.record
I'm attaching my generated files and their config for references,
maybe someone finds that approach interesting.
Lukas Eder
Feb 7, 2020, 10:49:17 AM2/7/20
to jOOQ User Group
On Fri, Feb 7, 2020 at 4:15 PM Thorsten Schöning <tscho...@am-soft.de> wrote:
Guten Tag Thorsten Schöning,
am Freitag, 31. Januar 2020 um 19:06 schrieben Sie:
> I tried following the approach jOOQ is doing by simply creating
> classes with fields for each CTE and their columns.[...]
One can get exactly what jOOQ does by using the code generator with a
schema for a XMLDatabase:
https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml/
That schema would need to describe the name of the CTE and its columns
and jOOQ would simply generate code for that like it does with tables
from a database. In other messages of this thread, Lukas Eder
suggested to implement CustomTable instead of using a class with names
and fields only like I did, so using the generator directly is pretty
close.
Yeah, why not. Or you could use the DDLDatabase, which might make the definition of your "CTE" a bit more concise.
Managing all those different tables isn't that easy currently: Things
are tables for the generator, so classes are put into packages like
"[...].ctes.tables[.records]". While one might prefer something like
"[...].ctes[.records]" directly only. That way one might mix things
into other jOOQ-packages, like in the following example:
> [...].db.jooq
> [...].db.jooq.ctes
> [...].db.jooq.ctes.tables
> [...].db.jooq.ctes.tables.records
> [...].db.jooq.enums
> [...].db.jooq.tables
> [...].db.jooq.tables.record
I'm attaching my generated files and their config for references,
maybe someone finds that approach interesting.
Please also have a look at generator strategies:
Thorsten Schöning
Feb 7, 2020, 10:50:21 AM2/7/20
to jOOQ User Group
Guten Tag Lukas Eder,
am Freitag, 7. Februar 2020 um 15:37 schrieben Sie:
> Does this make sense?
Absolutely, thanks! :-) You only have my old code, already changed it
to what you suggested right now. BUT: Not with using a custom table,
but my former approach with individual, unrelated fields only.
Would be great if you could have an additional look and tell me if
my current approach should work or doesn't make any sense. Didn't
execute anything currently.
I had the feeling that my current implementation should work as well,
because jOOQ simply renders given field names and as long those are
available by some CTE, things will simply work. Or do I really NEED
custom tables like in your former suggestion? Because what I have now
is what I want, especially after reading your suggestion, and if I
need custom tables to get that, I will change my definitions to custom
tables.
am Freitag, 7. Februar 2020 um 15:37 schrieben Sie:
> Does this make sense?
Absolutely, thanks! :-) You only have my old code, already changed it
to what you suggested right now. BUT: Not with using a custom table,
but my former approach with individual, unrelated fields only.
Would be great if you could have an additional look and tell me if
my current approach should work or doesn't make any sense. Didn't
execute anything currently.
I had the feeling that my current implementation should work as well,
because jOOQ simply renders given field names and as long those are
available by some CTE, things will simply work. Or do I really NEED
custom tables like in your former suggestion? Because what I have now
is what I want, especially after reading your suggestion, and if I
need custom tables to get that, I will change my definitions to custom
tables.
Lukas Eder
Feb 7, 2020, 11:04:13 AM2/7/20
to jOOQ User Group
On Fri, Feb 7, 2020 at 4:50 PM Thorsten Schöning <tscho...@am-soft.de> wrote:
Guten Tag Lukas Eder,
am Freitag, 7. Februar 2020 um 15:37 schrieben Sie:
> Does this make sense?
Absolutely, thanks! :-) You only have my old code, already changed it
to what you suggested right now. BUT: Not with using a custom table,
but my former approach with individual, unrelated fields only.
Would be great if you could have an additional look and tell me if
my current approach should work or doesn't make any sense. Didn't
execute anything currently.
It's a lot of code. If you could TL;DR-summarize it with 6-7 lines, I'll be happy to look at it.
I had the feeling that my current implementation should work as well,
because jOOQ simply renders given field names and as long those are
available by some CTE, things will simply work. Or do I really NEED
custom tables like in your former suggestion?
No you don't need them. CustomTable and TableImpl (from your generated code) are equivalent, except you should not directly extend the latter, because that's internal API.
Because what I have now
is what I want, especially after reading your suggestion, and if I
need custom tables to get that, I will change my definitions to custom
tables.
Sounds great!
Thorsten Schöning
Feb 7, 2020, 12:38:10 PM2/7/20
to jOOQ User Group
Guten Tag Lukas Eder,
am Freitag, 7. Februar 2020 um 17:04 schrieben Sie:
> It's a lot of code. If you could TL;DR-summarize it with 6-7 lines, I'll be
> happy to look at it.
Couldn't reduce less, so if it's still too much, nevermind, I'll deal
with it when executed and in case it breaks. I know what to do now
thanks to you!
> import static de.am_soft.sm_mtg.backend.daos.db.reports.meter_cnt.last_month.MclmSchema.RECS_IN_TIME_PERIOD;
> import static de.am_soft.sm_mtg.backend.daos.db.reports.meter_cnt.last_month.MclmSchema.RE_AND_CLT_PER_METER_LID;
> return RE_AND_CLT_PER_METER_LID.NAME
> .fields(RE_AND_CLT_PER_METER_LID.COL_METER_MFCT_CODE.getName())
> .as(DSL.select(RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE)
> .from(RECS_IN_TIME_PERIOD.NAME));
My schema, which is NOT generated using the generator, that was only
some other test. Prefer this for now, if it works:
> class MclmSchema
> {
> static class RecsInTimePeriod {
> private RecsInTimePeriod() {}
>
> final Name NAME = DSL.name ("recs_in_time_period");
> final Field<Integer> COL_RC_USER_ID = DSL.field("rc_user_id", READING_COMPANY.USER_ID.getDataType());
> }
>
> static class ReAndCltPerMeterLid {
> private ReAndCltPerMeterLid() {}
>
> final Name NAME = DSL.name ("re_and_clt_per_meter_lid");
> final Field<MeterMfctCode> COL_METER_MFCT_CODE = DSL.field("meter_mfct_code", METER.MFCT_CODE.getDataType());
> }
>
> static final RecsInTimePeriod RECS_IN_TIME_PERIOD = new RecsInTimePeriod();
> static final ReAndCltPerMeterLid RE_AND_CLT_PER_METER_LID = new ReAndCltPerMeterLid();
am Freitag, 7. Februar 2020 um 17:04 schrieben Sie:
> It's a lot of code. If you could TL;DR-summarize it with 6-7 lines, I'll be
> happy to look at it.
with it when executed and in case it breaks. I know what to do now
thanks to you!
> import static de.am_soft.sm_mtg.backend.daos.db.reports.meter_cnt.last_month.MclmSchema.RECS_IN_TIME_PERIOD;
> import static de.am_soft.sm_mtg.backend.daos.db.reports.meter_cnt.last_month.MclmSchema.RE_AND_CLT_PER_METER_LID;
> return RE_AND_CLT_PER_METER_LID.NAME
> .fields(RE_AND_CLT_PER_METER_LID.COL_METER_MFCT_CODE.getName())
> .as(DSL.select(RECS_IN_TIME_PERIOD.COL_METER_MFCT_CODE)
> .from(RECS_IN_TIME_PERIOD.NAME));
My schema, which is NOT generated using the generator, that was only
some other test. Prefer this for now, if it works:
> class MclmSchema
> {
> static class RecsInTimePeriod {
> private RecsInTimePeriod() {}
>
> final Name NAME = DSL.name ("recs_in_time_period");
> final Field<Integer> COL_RC_USER_ID = DSL.field("rc_user_id", READING_COMPANY.USER_ID.getDataType());
> }
>
> static class ReAndCltPerMeterLid {
> private ReAndCltPerMeterLid() {}
>
> final Name NAME = DSL.name ("re_and_clt_per_meter_lid");
> final Field<MeterMfctCode> COL_METER_MFCT_CODE = DSL.field("meter_mfct_code", METER.MFCT_CODE.getDataType());
> }
>
> static final RecsInTimePeriod RECS_IN_TIME_PERIOD = new RecsInTimePeriod();
> static final ReAndCltPerMeterLid RE_AND_CLT_PER_METER_LID = new ReAndCltPerMeterLid();
Reply all
Reply to author
Forward
0 new messages