Anorm string set from postgres ltree column

Question

I have a table with one of the columns having ltree type, and the following code fetching data from it:

SQL("""select * from "queue"""")()
.map(
    row =>
        {
            val queue =
                Queue(
                    row[String]("path"),
                    row[String]("email_recipients"),
                    new DateTime(row[java.util.Date]("created_at")),
                    row[Boolean]("template_required")
                )
            queue
        }
).toList

which results in the following error:

RuntimeException: TypeDoesNotMatch(Cannot convert notification.en.incident_happened:class org.postgresql.util.PGobject to String for column ColumnName(queue.path,Some(path)))

queue table schema is the following:

CREATE TABLE queue
(
  id serial NOT NULL,
  template_id integer,
  template_version integer,
  path ltree NOT NULL,
  json_params text,
  email_recipients character varying(1024) NOT NULL,
  email_from character varying(128),
  email_subject character varying(512),
  created_at timestamp with time zone NOT NULL,
  sent_at timestamp with time zone,
  failed_recipients character varying(1024),
  template_required boolean NOT NULL DEFAULT true,
  attachments hstore,
  CONSTRAINT pk_queue PRIMARY KEY (id ),
  CONSTRAINT fk_queue__email_template FOREIGN KEY (template_id)
      REFERENCES email_template (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);
ALTER TABLE queue
  OWNER TO postgres;
GRANT ALL ON TABLE queue TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE queue TO writer;
GRANT SELECT ON TABLE queue TO reader;

Why is that? Isn't notification.en.incident_happened just an ordinary string? Or am I missing anything?

UPD:

The question still applies, but here is a workaround:

SQL("""select id, path::varchar, email_recipients, created_at, template_required from "queue"""")()

Addi

What is the schema for the table queue? – Evacuation

This link might help: #20024857 – Devoe

@MandarPandit thx, but it's just a workaround. The root of the problem is still unclear... – Addi

Answer 1

This looked like a fun project so I implemented the ltree column mapper.

I piggybacked off anorm-postgresql, since that project already implements some postgres types in anorm. It looks good, and it would be useful if it implemented the full range of postgres types. My code has been merged in, so you can use that library. Alternatively, just use the following code:

import org.postgresql.util.PGobject
import anorm._
object LTree {
  implicit def rowToStringSeq: Column[Seq[String]] = Column.nonNull { (value, meta) =>
    val MetaDataItem(qualified, nullable, clazz) = meta
      value match {
        case pgo:PGobject => {
          val seq = pgo.getValue().split('.')
          Right(seq.toSeq)
        }
        case x => Left(TypeDoesNotMatch(x.getClass.toString))
      }
    }
    implicit def stringSeqToStatement = new ToStatement[Seq[String]] {
      def set(s: java.sql.PreparedStatement, index: Int, aValue: Seq[String]) {
      val stringRepresentation = aValue.mkString(".")
      val pgo:org.postgresql.util.PGobject = new org.postgresql.util.PGobject()
      pgo.setType("ltree");
      pgo.setValue( stringRepresentation );
      s.setObject(index, pgo)
    }
  }
}

Then you can map an ltree to a Seq[String]. Notice that it is a sequence of path elements order matters so it is a Seq[String], rather than String or Set[String]. If you want a single string just say path.mkString("."). Usage below:

import LTree._

SQL("""select * from "queue"""")()
.map(
    row =>
        {
            val queue =
                Queue(
                    row[Seq[String]]("path"),
                    row[String]("email_recipients"),
                    new DateTime(row[java.util.Date]("created_at")),
                    row[Boolean]("template_required")
                )
            queue
        }
).toList

Ancheta