Custom Jooq Types

September 07, 2020

This is the second part of the two-part series on writ­ing a custom Scala class to rep­re­sent the tstzrange column type in Post­gres. Find the first part here.

Here I’m plac­ing this bind­ing class in the same file as the tstzrange case class:

import java.sql.{SQLFeatureNotSupportedException, Types}
import java.time.{Instant, ZonedDateTime}
import java.util.Objects

import org.jooq._
import org.jooq.impl.DSL


class PostgresTimestampTimezoneRangeBinding extends Binding[Object, TsTzRange] {
  // This regex captures start and end bounds (as either closed or open) and the two values inside
  // We use a lazy quantifier to avoid capturing beyond the next match
  private val rangeRegex = raw"([\[\(])(.*?),(.*?)([\]\)])".r
  override def converter: Converter[Object, TsTzRange] = new Converter[Object, TsTzRange]() {
    override def from(t: Object): TsTzRange =
      if (t == null) null
      else {
        t.toString match {
          case rangeRegex(startBound, start, end, endBound) =>
            val startInstant: Option[Instant] =
              if (start == "") None
              else Some(ZonedDateTime.parse(start.replace("\"", "").replace(" ", "T") + ":00").toInstant)
            val endInstant: Option[Instant] =
              if (end == "") None
              else Some(ZonedDateTime.parse(end.replace("\"", "").replace(" ", "T") + ":00").toInstant)
            TsTzRange(startInstant, endInstant, startBound == "[", endBound == "]")
        }
      }

    override def to(u: TsTzRange): Object =
      if (u == null) null
      else {
        val startBound = if (u.startInclusive) "[" else "("
        val endBound = if (u.endInclusive) "]" else ")"
        val start = u.start.map(_.toString).getOrElse("")
        val end = u.end.map(_.toString).getOrElse("")
        s"$startBound$start,$end$endBound"
      }

    override def fromType: Class[Object] =
      classOf[Object]

    override def toType: Class[TsTzRange] =
      classOf[TsTzRange]
  }

  override def sql(ctx: BindingSQLContext[TsTzRange]): Unit =
    ctx.render.visit(DSL.`val`(ctx.convert[Object](converter).value)).sql("::tstzrange")

  override def register(ctx: BindingRegisterContext[TsTzRange]): Unit =
    ctx.statement.registerOutParameter(ctx.index, Types.OTHER)

  override def set(ctx: BindingSetStatementContext[TsTzRange]): Unit =
    ctx.statement.setString(ctx.index, Objects.toString(ctx.convert[Object](converter).value, null))

  override def get(ctx: BindingGetResultSetContext[TsTzRange]): Unit =
    ctx.convert(converter).value(ctx.resultSet.getString(ctx.index))

  override def get(ctx: BindingGetStatementContext[TsTzRange]): Unit =
    ctx.convert(converter).value(ctx.statement.getString(ctx.index))

  override def set(ctx: BindingSetSQLOutputContext[TsTzRange]): Unit =
    throw new SQLFeatureNotSupportedException

  override def get(ctx: BindingGetSQLInputContext[TsTzRange]): Unit =
    throw new SQLFeatureNotSupportedException
}

A note about the parts of the imple­men­ta­tion high­light­ed above.

First, an expla­na­tion of the regex. The Post­gres string rep­re­sen­ta­tion of tstzrange looks some­thing like this: ["2020-08-21 00:00:00+00","2020-09-15 00:00:00+00"). So what we want to do is to cap­ture the open­ing and clos­ing brack­ets, as well as what­ev­er is between the brack­et and the comma sep­a­ra­tor. Note that we don’t match on the lit­er­al double quotes, since single-unbound­ed ranges don’t have them, e.g. ["2020-08-21 00:00:00+00",) I’m sure there is a way to do it with pure regex, but it’s more straight­for­ward to just remove them with code.

In the next 2 high­light­ed lines, we do what seems like a brit­tle string mung­ing code to turn the time­stamp string into a ISO-8601 string that Java likes. The reason I chose to do this is because I want to rely on Java’s built-in parsers. It’s not easy to define a custom format with SimpleDateFormat or DateTimeFormatter because Post­gres time­stamps have vari­able pre­ci­sion.

See Sec­tion 8.5.2 Date/​Time Output in the Post­gres doc­u­men­ta­tion and this Stack Over­flow answer for more back­ground on whether replac­ing the T sep­a­ra­tor with a space is still con­sid­ered ISO-8601/​RFC3339. Regard­less, Java parsers only like time­stamp strings with the T sep­a­ra­tor.

ISO 8601 spec­i­fies the use of upper­case letter T to sep­a­rate the date and time. Post­greSQL accepts that format on input, but on output it uses a space rather than T, as shown above. This is for read­abil­i­ty and for con­sis­ten­cy with RFC 3339 as well as some other data­base sys­tems.

See also the Java doc­u­men­ta­tion here. Java only accepts time­zone off­sets with hh:mm, not just hh. In my opin­ion, we don’t have to worry about double append­ing the :00 por­tion, since Post­gres nor­mal­izes all time­stamps to UTC on write.

This is an exam­ple of how the trans­for­ma­tion works:

"2020-01-01 00:00:00.000+08"
2020-01-01 00:00:00.000+08
2020-01-01T00:00:00.000+08:00

And final­ly, in your build.scala, add the high­light­ed lines to your exist­ing Jooq gen­er­a­tion config:

object Settings {
  lazy val jooqGen: Seq[Def.Setting[_]] = Seq(
    libraryDependencies ++= Seq(
      "org.postgresql" % "postgresql" % Versions.postgres % "jooq"
    ),
    jooqVersion := Versions.jooq,
    jooqCodegenConfig :=
      <configuration>
        <jdbc>
          <driver>org.postgresql.Driver</driver>
          <url>{
        System.getenv().getOrDefault("JDBC_DATABASE_URL", s"jdbc:postgresql://localhost:5432/mydb?user=postgres")
      }</url>
        </jdbc>
        <generator>
          <name>org.jooq.util.JooqGenerator</name>
          <strategy>
            <name>misc.JooqStrategy</name>
          </strategy>
          <database>
            <name>org.jooq.util.postgres.PostgresDatabase</name>
            <schemata>
              <schema>
                <inputSchema>public</inputSchema>
                <outputSchema>public</outputSchema>
              </schema>
            </schemata>
            <customTypes>
              <customType>
                <name>TimestampTimezoneRange</name>
                <type>misc.TsTzRange</type>
                <binding>misc.PostgresTimestampTimezoneRangeBinding</binding>
              </customType>
             <forcedTypes>
              <forcedType>
                <name>TimestampTimezoneRange</name>
                <types>tstzrange</types>
              </forcedType>
            </forcedTypes>
           </customTypes>
          </database>
          <generate>
            <deprecated>false</deprecated>
            <records>true</records>
            <immutablePojos>true</immutablePojos>
            <fluentSetters>true</fluentSetters>
            <daos>true</daos>
            <validationAnnotations>true</validationAnnotations>
            <routines>false</routines>
          </generate>
          <target>
            <packageName>model.jooq</packageName>
            <directory>{jooqCodegenTargetDirectory.value}</directory>
          </target>
        </generator>
    </configuration>,
      sources in doc in Compile := Seq.empty
  ) ++
    Defaults.packageTaskSettings(packageSrc, jooqCodegenTargetDirectory.map(Path.allSubpaths(_).toSeq)) ++ common
}

Final­ly, this can be used like so:

val results = dsl
  .select()
  .from(APPOINTMENT)
  .fetch$
  .map(_.into(APPOINTMENT))
  
println(results.head.appointmentTime) // TsTzRange(Some(2020-08-19T08:12:56.320Z),true,Some(2021-08-19T00:14:36.320Z),true)

dsl
  .insertInto(
    APPOINTMENT,
    APPOINTMENT.APPOINTMENT_TIME
  )
  .values(
    TsTzRange(Some(Instant.now), true, Some(Instant.now.plusSeconds(1000)), true)
  )
  .execute()