Support for tstzrange using Joda DateTime type

[dgllghr]

I want to add support for the tstzrange column type using Joda's DateTime. I've attempted this in my Postgres driver:

trait Postgres extends PostgresDriver
with PgArraySupport
with PgDateSupportJoda
with PgRangeSupport
with PgHStoreSupport
with PgPlayJsonSupport
with PgSearchSupport
with PgPostGISSupport {

  override lazy val Implicit = new ImplicitsPlus {}
  override val simple = new SimpleQLPlus {}

  trait ImplicitsPlus extends Implicits
  with ArrayImplicits
  with DateTimeImplicits
  with RangeImplicits
  with JsonImplicits
  with SearchImplicits
  with PostGISImplicits {

    private def toLocalDate(s: String) = LocalDate.parse(s, dateFormatter)
    private def fromLocalDate(ld: LocalDate) = ld.toString(dateFormatter)

    private def toDateTime(s: String) =
      DateTime.parse(s, if(s.indexOf(".") > 0 ) tzDateTimeFormatter else tzDateTimeFormatter_NoFraction)
    private def fromDateTime(v: DateTime) =  v.toString(tzDateTimeFormatter)

    implicit val simpleLocalDateRangeTypeMapper =
      new GenericJdbcType[Range[LocalDate]]("daterange",
        PgRangeSupportUtils.mkRangeFn(toLocalDate),
        PgRangeSupportUtils.toStringFn(fromLocalDate))

    implicit val simpleDateTimeRangeTypeMapper =
      new GenericJdbcType[Range[DateTime]]("tstzrange",
        PgRangeSupportUtils.mkRangeFn(toDateTime),
        PgRangeSupportUtils.toStringFn(fromDateTime))
  }

  trait SimpleQLPlus extends SimpleQL
  with ImplicitsPlus
  with SearchAssistants
  with PostGISAssistants
}

But when I go to insert into the column ats of type Range[DateTime], I get the following error: org.postgresql.util.PSQLException: ERROR: column "ats" is of type tstzrange but expression is of type text

What am I missing so that I can use this type in my tables?

[tminglei]

@dgllghr your type mapper declaration seems ok, can you help provide more details?

[dgllghr]

@tminglei sure thing! This is the table that uses the joda DateTime range:

class Records(tag: Tag) extends Table[(UUID, DateTime, Int, Option[Range[DateTime]], Option[Range[LocalDate]], String)](tag, "records") {
  def key = column[UUID]("key")
  def created = column[DateTime]("created")
  def placeId = column[Int]("placeid")
  def absoluteTimeSpan = column[Option[Range[DateTime]]]("ats")
  def relativeTimeSpan = column[Option[Range[LocalDate]]]("rts")
  def value = column[String]("value")

  def * = (key, created, placeId, absoluteTimeSpan, relativeTimeSpan, value)
}
val records = TableQuery[Records]

And I am trying to do an insert with

db.conn.withSession { implicit s =>
  val key = UUID.randomUUID
  val created = DateTime.now
  val absRange = Some(Range(DateTime.now.minusDays(1), DateTime.now))
  val relRange: Option[Range[LocalDate]] = None
  val value = "foo"
  val placeType = "zip"
  val placeId = "20007"

  db.records.insert(db.placeCodes
    .filter(pc => pc.codeSet === placeType && pc.code === placeId)
    .map(pc => (key.bind, created.bind, pc.placeId, absRange.bind, relRange.bind, values.bind)))
}

For some reason, when I do this from the command line the error I'm getting is different:

error: value bind is not a member of Some[com.github.tminglei.slickpg.Range[org.joda.time.DateTime]]

If it helps, the original stack trace I was seeing when running the insert in the program was:

org.postgresql.util.PSQLException: ERROR: column "ats" is of type tstzrange but expression is of type text
  Hint: You will need to rewrite or cast the expression.
  Position: 159
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
com.zaxxer.hikari.proxy.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:61)
com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeUpdate(PreparedStatementJavassistProxy.java)
scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker$$anonfun$internalInsertQuery$1.apply(    JdbcInsertInvokerComponent.scala:253)
scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker$$anonfun$internalInsertQuery$1.apply(    JdbcInsertInvokerComponent.scala:250)
scala.slick.jdbc.JdbcBackend$SessionDef$class.withPreparedStatement(JdbcBackend.scala:191)
scala.slick.jdbc.JdbcBackend$BaseSession.withPreparedStatement(JdbcBackend.scala:389)
scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.preparedInsert(JdbcInsertInvokerComponent.scala:170)
scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.internalInsertQuery(JdbcInsertInvokerComponent.scala:250)
scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.insert(JdbcInsertInvokerComponent.scala:244)
com.datasembly.dataload.AppendCollector$class.flushRecords$1(AppendCollector.scala:85)
com.datasembly.dataload.AppendCollector$$anonfun$collector$1$$anonfun$applyOrElse$1.apply(AppendCollector.scala:132)
com.datasembly.dataload.AppendCollector$$anonfun$collector$1$$anonfun$applyOrElse$1.apply(AppendCollector.scala:131)
scala.slick.backend.DatabaseComponent$DatabaseDef$$anonfun$withTransaction$1$$anonfun$apply$1.apply(DatabaseComponent.    scala:54)
scala.slick.jdbc.JdbcBackend$BaseSession.withTransaction(JdbcBackend.scala:426)
scala.slick.backend.DatabaseComponent$DatabaseDef$$anonfun$withTransaction$1.apply(DatabaseComponent.scala:54)
scala.slick.backend.DatabaseComponent$DatabaseDef$$anonfun$withTransaction$1.apply(DatabaseComponent.scala:54)
scala.slick.backend.DatabaseComponent$DatabaseDef$class.withSession(DatabaseComponent.scala:34)
scala.slick.jdbc.JdbcBackend$DatabaseFactoryDef$$anon$3.withSession(JdbcBackend.scala:46)
scala.slick.backend.DatabaseComponent$DatabaseDef$class.withTransaction(DatabaseComponent.scala:54)
scala.slick.jdbc.JdbcBackend$DatabaseFactoryDef$$anon$3.withTransaction(JdbcBackend.scala:46)

Thanks for the help, and let me know if there is anything else I can provide.

[tminglei]

Hi @dgllghr I used your declaration to try, and it seems ok.

object Postgres extends PostgresDriver
                  with PgArraySupport
                  with PgDateSupportJoda
                  with PgRangeSupport
                  with PgHStoreSupport
                  with PgPlayJsonSupport
                  with PgSearchSupport
                  with PgPostGISSupport {

  override lazy val Implicit = new ImplicitsPlus {}
  override val simple = new SimpleQLPlus {}

  trait ImplicitsPlus extends Implicits
                        with ArrayImplicits
                        with DateTimeImplicits
                        with RangeImplicits
                        with JsonImplicits
                        with SearchImplicits
                        with PostGISImplicits {

    private def toLocalDate(s: String) = LocalDate.parse(s, jodaDateFormatter)
    private def fromLocalDate(ld: LocalDate) = ld.toString(jodaDateFormatter)

    private def toDateTime(s: String) =
      DateTime.parse(s, if(s.indexOf(".") > 0 ) jodaTzDateTimeFormatter else jodaTzDateTimeFormatter_NoFraction)
    private def fromDateTime(v: DateTime) =  v.toString(jodaTzDateTimeFormatter)

    implicit val simpleLocalDateRangeTypeMapper =
      new GenericJdbcType[Range[LocalDate]]("daterange",
        PgRangeSupportUtils.mkRangeFn(toLocalDate),
        PgRangeSupportUtils.toStringFn(fromLocalDate))

    implicit val simpleDateTimeRangeTypeMapper =
      new GenericJdbcType[Range[DateTime]]("tstzrange",
        PgRangeSupportUtils.mkRangeFn(toDateTime),
        PgRangeSupportUtils.toStringFn(fromDateTime))
  }

  trait SimpleQLPlus extends SimpleQL
                      with ImplicitsPlus
                      with SearchAssistants
                      with PostGISAssistants
}

///
import Postgres.simple._

val db = Database.forURL(url = "jdbc:postgresql://localhost/test?user=postgres", driver = "org.postgresql.Driver")

class Records(tag: Tag) extends Table[(UUID, DateTime, Int, Option[Range[DateTime]], Option[Range[LocalDate]], String)](tag, "test_records") {
  def key = column[UUID]("key")
  def created = column[DateTime]("created")
  def placeId = column[Int]("placeid")
  def absoluteTimeSpan = column[Option[Range[DateTime]]]("ats")
  def relativeTimeSpan = column[Option[Range[LocalDate]]]("rts")
  def value = column[String]("value")

  def * = (key, created, placeId, absoluteTimeSpan, relativeTimeSpan, value)
}
val records = TableQuery[Records]

@Test
def testTt(): Unit = {
  db withSession { implicit s: Session =>
    Try { records.ddl drop }
    Try { records.ddl.createStatements.foreach(s => println(s"[test] >>> $s")) }
    Try { records.ddl create }

    val key = UUID.randomUUID
    val created = DateTime.now
    val placeId = 20007
    val absRange = Some(Range(DateTime.now.minusDays(1), DateTime.now))
    val relRange: Option[Range[LocalDate]] = None
    val value = "foo"
//      val placeType = "zip"

//      records.insert(db.placeCodes
//        .filter(pc => pc.codeSet === placeType && pc.code === placeId)
//        .map(pc => (key.bind, created.bind, pc.placeId, absRange.bind, relRange.bind, values.bind)))
    records += (key, created, placeId, absRange, relRange, value)
  }
}

Which slick-pg version are you using? Because dateFormatter/tzDateTimeFormatter/tzDateTimeFormatter_NoFraction can't exist in latest slick-pg any more?

[dgllghr]

Thanks for testing this. I'm using slick-pg version 0.6.5. Based on the fact that it worked for you with +=, it looks like the problem has to do with inserting a Query, which is probably more likely a slick bug or I'm doing something that isn't supported. I'll upgrade to the latest slick-pg and let you know how it goes, but if it doesn't work I'll refactor how I'm inserting to use += and you can just close this.

[tminglei]

Hi @dgllghr when inserting with a query, it also looks ok:

records += (key, created, placeId, absRange, relRange, value)
records.insert(records.filter(_.key === key.bind).map { r =>
  (UUID.randomUUID.bind, DateTime.now, r.placeId, r.absoluteTimeSpan, r.relativeTimeSpan, r.value)
})

[alfonsovng]

Just a question. It's possible to define list of ranges? I'm trying to define the postgresql type _tsrange as List[com.github.tminglei.slickpg.Range[java.time.LocalDateTime]]. I've this implicit:

import utils.SimpleArrayUtils._

implicit val listTypeWrapperRangeLocalDateTime =  new AdvancedArrayJdbcType[Range[LocalDateTime]]("tsrange",
  fromString(PgRangeSupportUtils.mkRangeFn(fromDateTimeOrInfinity))(_).orNull, mkString(PgRangeSupportUtils.toStringFn(toDateTimeOrInfinity)))

But I've this error:

could not find implicit value for parameter tt: slick.ast.TypedType[List[com.github.tminglei.slickpg.Range[java.time.LocalDateTime]]]

It's possible? Is it anything missing?

[tminglei]

@alfonsovng as the error message said, you should define a Range[LocalDateTime] first, which wasn't built-in supported by slick-pg.

[alfonsovng]

Thanks for your answer @tminglei. I forgot to write it in my comment, but I've defined it and it, alone, not in a list, works fine:

implicit val typeMapperLocalDateTime = new GenericJdbcType[Range[LocalDateTime]]("tsrange",
  PgRangeSupportUtils.mkRangeFn(fromDateTimeOrInfinity),
  PgRangeSupportUtils.toStringFn(toDateTimeOrInfinity)
)

Maybe I should add something to the list definition?

[alfonsovng]

Finally it works. I've added the last implicit val:

    private val stringToRangeLocalDateTime = PgRangeSupportUtils.mkRangeFn(fromDateTimeOrInfinity)
    private val rangeLocalDateTimeToString = PgRangeSupportUtils.toStringFn(toDateTimeOrInfinity)

    implicit val typeMapperLocalDateTime = new GenericJdbcType[Range[LocalDateTime]]("tsrange",
      stringToRangeLocalDateTime, rangeLocalDateTimeToString
    )

    import utils.SimpleArrayUtils._

    implicit val listTypeWrapperRangeLocalDateTime = new AdvancedArrayJdbcType[Range[LocalDateTime]]("tsrange",
      fromString(stringToRangeLocalDateTime)(_).orNull, mkString(rangeLocalDateTimeToString))

    //this is new... and it looks unnecessary, am i wrong?
    implicit val typeMapperRangeLocalDateTime = new GenericJdbcType[List[Range[LocalDateTime]]]("_tsrange",
      fromString(stringToRangeLocalDateTime)(_).orNull.toList, mkString(rangeLocalDateTimeToString))

But I think that it should be unnecessary because AdvancedArrayJdbcType, like GenericJdbcType, is also inheriting from slick.ast.TypedType.

[tminglei]

@alfonsovng well, I read the error message wrongly before.

Yes, the last typeMapperRangeLocalDateTime definition is not necessary.

The problem is you defined a RangeLocalDateTime Seq mapper, but RangeLocalDateTime List mapper is required.

Pls change it to:

implicit val listTypeWrapperRangeLocalDateTime = new AdvancedArrayJdbcType[Range[LocalDateTime]]("tsrange",
      fromString(stringToRangeLocalDateTime)(_).orNull, mkString(rangeLocalDateTimeToString)).to(_.toList)

[alfonsovng]

You are absolutely right! I didn't understand the error message and now it's clear! Thank you very much! I'm learning a lot using your library!

[tminglei]

@alfonsovng you're welcome! :-)