Add support for Jooqs YearToSecond

[Dudeplayz]

I want to use the postgres interval data type. Unfortunately is the value not written to the database. While debugging the query mapping, the build query is correct. But after execution there is nothing in the database. For the read case I understand if it is not supported and I could do the back-mapping by myself, but the write should work correctly because it relies only on existing jooq features.

[jklingsporn]

Can you provide a sample table definition or a reproducer?

[Dudeplayz]

Sorry for late reply, haven't seen your question.
I hope this small example helps.
I am using vertx-jooq-classic-reactive.
Used versions:

• <vertx.version>4.2.7</vertx.version>
• <jooq.version>3.17.6</jooq.version>
• <vertx-jooq.version>6.5.5</vertx-jooq.version>

create table maintenance
(
    maintenance_id       integer generated by default as identity
        primary key,
    last_edit_timestamp  timestamp with time zone default now() not null,
    maintenance_interval interval
);

var dao = MaintenanceDao(...);
var maintenance = Maintenance(null, OffsetDateTime.now(), YearToSecond(YearToMonth(0,1), DayToSecond(0,0,0)));
dao.insertReturningPrimary(maintenance);

                    <plugin>
                        <!-- Specify the maven code generator plugin -->
                        <groupId>org.jooq</groupId>
                        <artifactId>jooq-codegen-maven</artifactId>
                        <version>${jooq.version}</version>

                        <!-- The plugin should hook into the generate goal -->
                        <executions>
                            <execution>
                                <phase>generate-sources</phase>
                                <goals>
                                    <goal>generate</goal>
                                </goals>
                            </execution>
                        </executions>

                        <dependencies>
                            <dependency>
                                <groupId>org.postgresql</groupId>
                                <artifactId>postgresql</artifactId>
                                <version>${postgres.version}</version>
                            </dependency>
                            <dependency>
                                <groupId>io.github.jklingsporn</groupId>
                                <artifactId>vertx-jooq-generate</artifactId>
                                <version>${vertx-jooq.version}</version>
                            </dependency>
                        </dependencies>

                        <configuration>
                            <configurationFile>${jooq.config}</configurationFile>
                            <jdbc>
                                <driver>${database.driver}</driver>
                            </jdbc>

                            <!-- Generator parameters -->
                            <generator>
                                <name>io.github.jklingsporn.vertx.jooq.generate.classic.ClassicReactiveVertxGenerator
                                </name>
                                <!-- use 'io.github.jklingsporn.vertx.jooq.generate.classic.ClassicReactiveGuiceVertxGenerator' to enable Guice DI -->
                                <database>
                                    <name>org.jooq.meta.postgres.PostgresDatabase</name>
                                    <includes>.*</includes>
                                    <forcedTypes>
                                        <forcedType>
                                            <userType>io.vertx.core.json.JsonObject</userType>
                                            <converter>
                                                io.github.jklingsporn.vertx.jooq.shared.postgres.JSONToJsonObjectConverter
                                            </converter>
                                            <expression>data</expression>
                                            <types>(?i:JSON)</types>
                                        </forcedType>
                                    </forcedTypes>
                                    <unsignedTypes>false</unsignedTypes>
                                </database>
                                <target>
                                    <packageName>${jooq-generated.package}</packageName>
                                    <directory>${jooq-generated.dir}</directory>
                                </target>
                                <generate>
                                    <javaTimeTypes>true</javaTimeTypes>
                                    <interfaces>true</interfaces>
                                    <daos>true</daos>
                                    <fluentSetters>true</fluentSetters>
                                </generate>

                                <strategy>
                                    <name>io.github.jklingsporn.vertx.jooq.generate.VertxGeneratorStrategy</name>
                                </strategy>
                            </generator>
                        </configuration>
                    </plugin>

[Dudeplayz]

I tested the generated jooq sql query and copied it to the sql console, then it worked.

I also think the vertx-jooq code generator can support the datatype by mapping the fields of pg-client Interval to the Jooq YearToSecond instance:

import io.vertx.pgclient.data.Interval
import io.vertx.sqlclient.Row

var row = Row();
var interval = row.get(Interval.class, position/column);
obj.value = new YearToSecond(new YearToMonth(interval.year, interval.month), new DayToSecond(...));

[Dudeplayz]

@jklingsporn can you say something new?

[jklingsporn]

What't interesting: while for example there exists a method Row.getOffsetDateTime no such thing exists for the Interval. I will have to find a workaround for that special usecase in my VertxGeneratorBuilder where it builds the RowMapper-classes.

[Dudeplayz]

I think this comes from the point that Interval is not supported in all database drivers.

[jklingsporn]

As you've already mentioned, the Interval is only available for the postgres driver. For that reason I cannot provide an out-of-the-box support easily, because I want to avoid the dependency for language agnostic drivers. For now, please have a look at this generator I am using in the tests. It converts between the postgres Interval type and jOOQs YearToSecond. You obviously need to configure the converter properly in your code generator's configuration.

[Dudeplayz]

Thank you, I will try to implement it following your example. I will give feedback when I'm done.

[Dudeplayz]

Your example works, if the Interval type is the user type, but how I configure it to use jooqs YearToSecond instead?
I also have the problem, that the generated RowMapper tries to do row.getInterval("maintenance_interval"), which needs to be row.get(Intervall.class, "maintenance_interval").

[Dudeplayz]

I also have the problem, that the generated RowMapper tries to do row.getInterval("maintenance_interval"), which needs to be row.get(Intervall.class, "maintenance_interval").

It seems that this comes from the supportedRowTypes in the current release, if I understand the code correctly.

[Dudeplayz]

@jklingsporn can you create a new release containing this change?

[jklingsporn]

I'm really sorry for the late reply. Haven't forgot this and will craft a release as fast as I can ^^

[Dudeplayz]

I'm really sorry for the late reply. Haven't forgot this and will craft a release as fast as I can ^^

Thank you, maybe you also have the time to check #223 😊