JDBC Arrays with PostgreSQL

[Adam Mackler]

Hi:

I'm quite new to SLICK, and frankly stuck on the following point.  I would greatly appreciate any guidance on the matter.

I am using PostgreSQL, and I am wanting to insert data into a column whose type is an array of varchar.  I've tried a number of things, but so far none have worked.  If it would help then I can post everything I've tried, but in general I'm doing something in the nature of the following.  I make a typemapper something like this:

  implicit object StringArrayTypeMapper extends BaseTypeMapper[Array[String]]

                                   with TypeMapperDelegate[Array[String]] {

    def apply(p: BasicProfile) = this

    def zero = Array[String]()

    def sqlType = java.sql.Types.ARRAY

    def setValue(value: Array[String], parameters: PositionedParameters) {

      val npos = parameters.pos + 1

      parameters.ps.setArray(

        npos,

        new org.postgresql.jdbc4.Jdbc4Array(

          db.createSession.conn.asInstanceOf[org.postgresql.jdbc4.Jdbc4Connection],

                   1015, // oid of varchar[]

          value.map(_.replaceAll("'","''")).mkString("{", ",", "}") // add quotes as necessary

        )

      )

      parameters.pos = npos

    }

    def setOption(v: Option[Array[String]], p: PositionedParameters) = 

      p.setObjectOption(v, sqlType)

    def nextValue(r: PositionedResult) = // T.B.D...still trying to insert

    def updateValue(v: Array[String], r: PositionedResult) = r.nextObjectOption

    override def valueToSQLLiteral(v: Array[String]) =

       v.map(_.replaceAll("'","''")).mkString("{", ",", "}")

  }

Then my Table subclass would have such a column as

    def myColumn   = column[Array[String]]("my_column")

I can post error output and more details if that would be productive, but at this point I would really just like to know whether or not I'm on the right track.  Whether I'm even in the ballpark of the right track.  In particular, I am quite curious about the fact that the PositionedParameters class has no method that invokes the JDBC setArray() method, since that was my first expectation of how this would be accomplished.  So maybe I'm confused about dealing with JDBC arrays.

Thanks very much,

--

Adam Mackler

[Adam Mackler]

Having partially answered the above question, I'm putting my results on the record here before I forget what I've done.  In three steps I (1) created a PostgreSQL database that uses array-typed columns, (2) put Scala/SLICK code into a file, and (3) loaded that file in the REPL shell and did some array-based interactions with the database.  The upshot is that so far I am able to insert, retrieve, and update array values to the database, but have not yet figured out how to restrict (SELECT) rows from the database using a predicate that filters according to the array value of a column.  Here are the steps to recreate my results:

1) Create a database called sandbox, and give it a table that holds both arrays of integers, and arrays of VARCHARs:

CREATE TABLE hasarrays (

    id integer NOT NULL,

    ofints integer[] NOT NULL,

    ofvarchars character varying(8)[] NOT NULL

);

2) Create a file called ArrayMappers.scala that contains the following code:

import scala.slick.lifted.BaseTypeMapper

import scala.slick.driver.PostgresDriver.simple._

import scala.slick.driver.BasicProfile

import scala.slick.session.{PositionedParameters,PositionedResult}

import language.implicitConversions

object DB {

  def database = {

    val ds = new org.postgresql.ds.PGSimpleDataSource

    ds.setDatabaseName("sandbox")

    scala.slick.session.Database.forDataSource(ds)

  }

  val session = database.createSession()

}

abstract class ArrayTypeMapper[T: scala.reflect.ClassTag] extends

    BaseTypeMapper[Array[T]] with TypeMapperDelegate[Array[T]] {

  def apply(p: BasicProfile) = this

  def sqlType = java.sql.Types.ARRAY

  implicit def stringArrayToSqlArray(in: Array[String]): java.sql.Array =

    DB.session.conn.createArrayOf("varchar", in.map(e => e.asInstanceOf[Object]))

  implicit def intArrayToSqlArray(in: Array[Int]): java.sql.Array = 

    DB.session.conn.createArrayOf("int4", in.map(e => e: Integer))

}

object IntArrayTypeMapper extends ArrayTypeMapper[Int] {

  def zero = Array[Int]()

  def setValue(value: Array[Int], p: PositionedParameters) {

    val npos = p.pos + 1

    p.ps.setArray(npos, value)

    p.pos = npos

  }

  def setOption(o: Option[Array[Int]], p: PositionedParameters) {

    val npos = p.pos + 1

    if(o eq None) p.ps.setNull(npos, sqlType) else p.ps.setArray(npos, o.get)

    p.pos = npos

  }

  def nextValue(r: PositionedResult) =  {

    val npos = r.currentPos + 1

    val a = r.rs.getArray(npos)

    r.skip

    a.getArray.asInstanceOf[Array[Integer]].map(i => i:Int)

  }

  final def updateValue(value: Array[Int], r: PositionedResult) {

    val npos = r.currentPos + 1

    r.rs.updateArray (npos, value)

    r.skip

  }

  // TODO is this correct?

  override def valueToSQLLiteral(v: Array[Int]) = "'" + v + "'"

}

object StringArrayTypeMapper extends ArrayTypeMapper[String] {

  def zero = Array[String]()

  def setValue(value: Array[String], p: PositionedParameters) {

    val npos = p.pos + 1

    p.ps.setArray(npos, value)

    p.pos = npos

  }

  def setOption(value: Option[Array[String]], p: PositionedParameters) {

    val npos = p.pos + 1

    if (value eq None) p.ps.setNull(npos, sqlType)

    else p.ps.setArray(npos, value.get)

    p.pos = npos

  }

  def nextValue(r: PositionedResult) =  {

    val npos = r.currentPos + 1

    val a = r.rs.getArray(npos)

    r.skip

    a.getArray.asInstanceOf[Array[String]]

  }

  final def updateValue(value: Array[String], r: PositionedResult) {

    val npos = r.currentPos + 1

    r.rs.updateArray (npos, value)

    r.skip

  }

  // TODO is this correct?

  override def valueToSQLLiteral(v: Array[String]) = "'" + v + "'"

}

object HasArrays extends Table[(Int, Array[Int], Array[String])]("hasarrays") {

  def id  = column[Int]("id")

  def ofInt = column[Array[Int]]("ofints")(IntArrayTypeMapper)

  def ofString = column[Array[String]]("ofvarchars")(StringArrayTypeMapper)

  def * = id ~ ofInt ~ ofString

}

3) Here is a REPL log showing what worked and didn't work:

scala> :load ArrayMappers.scala

Loading ArrayMappers.scala...

scala> val projection = HasArrays.id ~ HasArrays.ofInt ~ HasArrays.ofString

projection: scala.slick.lifted.Projection3[Int,Array[Int],Array[String]] = Projection3

scala> projection.insert(1, Array(2,5,12,-2), Array("foo","bar","baz"))(DB.session)

res0: Int = 1

scala> HasArrays.map(_.ofString).first()(DB.session)

res1: Array[String] = Array(foo, bar, baz)

scala> HasArrays.map(_.ofInt).first()(DB.session)

res2: Array[Int] = Array(2, 5, 12, -2)

scala> projection.insert(2, Array(2,3,1), Array(" qu'x\\","b}l\"etch","wal,do "))(DB.session)

res3: Int = 1

scala> HasArrays.filter(_.id === 2).map(_.ofString).first()(DB.session)

res4: Array[String] = Array(" qu'x\", b}l"etch, "wal,do ")

scala> HasArrays.filter(_.id === 2).map(_.ofString).update(Array("a","b","d"))(DB.session)

res5: Int = 1

scala> HasArrays.filter(_.id === 2).map(_.ofString).first()(DB.session)

res6: Array[String] = Array(a, b, d)

scala> HasArrays.filter(_.ofInt === Array(2,3,1)).map(_.ofString).first()(DB.session)

<console>:20: error: value === is not a member of scala.slick.lifted.Column[Array[Int]]

              HasArrays.filter(_.ofInt === Array(2,3,1)).map(_.ofString).first()(DB.session)

                                       ^

[virtualeyes]

Impressive body of work there ;-)

Stating the obvious, would breaking the array out into a join table be
of any interest?

Assume not given the effort you've put in to getting the existing
setup working.

As for the filter/select error, does "inSet" provide any relief?

On Oct 24, 11:11 pm, Adam Mackler <adammack...@gmail.com> wrote:
> Having partially answered the above question, I'm putting my results on the
> record here before I forget what I've done.  In three steps I (1) created a
> PostgreSQL database that uses array-typed columns, (2) put Scala/SLICK code
> into a file, and (3) loaded that file in the REPL shell and did some
> array-based interactions with the database.  The upshot is that so far I am
> able to insert, retrieve, and update array values to the database, but have
> not yet figured out how to restrict (SELECT) rows from the database using a
> predicate that filters according to the array value of a column.  Here are
> the steps to recreate my results:
>

> 1) Create a database called *sandbox*, and give it a table that holds both

> arrays of integers, and arrays of VARCHARs:
>

> *CREATE TABLE hasarrays (*
> *    id integer NOT NULL,*
> *    ofints integer[] NOT NULL,*
> *    ofvarchars character varying(8)[] NOT NULL*
> *);*
>
> 2) Create a file called *ArrayMappers.scala* that contains the following
> code:
>
> *import scala.slick.lifted.BaseTypeMapper*
> *import scala.slick.driver.PostgresDriver.simple._*
> *import scala.slick.driver.BasicProfile*
> *import scala.slick.session.{PositionedParameters,PositionedResult}*
> *
> *
> *import language.implicitConversions*
> *
> *
> *object DB {*
> *  def database = {*
> *    val ds = new org.postgresql.ds.PGSimpleDataSource*
> *    ds.setDatabaseName("sandbox")*
> *    scala.slick.session.Database.forDataSource(ds)*
> *  }*
> *
> *
> *  val session = database.createSession()*
> *}*
> *
> *
> *abstract class ArrayTypeMapper[T: scala.reflect.ClassTag] extends*
> *    BaseTypeMapper[Array[T]] with TypeMapperDelegate[Array[T]] {*
> *  def apply(p: BasicProfile) = this*
> *  def sqlType = java.sql.Types.ARRAY*
> *
> *
> *  implicit def stringArrayToSqlArray(in: Array[String]): java.sql.Array =*
> *    DB.session.conn.createArrayOf("varchar", in.map(e =>
> e.asInstanceOf[Object]))*
> *
> *
> *  implicit def intArrayToSqlArray(in: Array[Int]): java.sql.Array = *
> *    DB.session.conn.createArrayOf("int4", in.map(e => e: Integer))*
> *}*
> *
> *
> *object IntArrayTypeMapper extends ArrayTypeMapper[Int] {*
> *
> *
> *  def zero = Array[Int]()*
> *
> *
> *  def setValue(value: Array[Int], p: PositionedParameters) {*
> *    val npos = p.pos + 1*
> *    p.ps.setArray(npos, value)*
> *    p.pos = npos*
> *  }*
> *
> *
> *  def setOption(o: Option[Array[Int]], p: PositionedParameters) {*
> *    val npos = p.pos + 1*
> *    if(o eq None) p.ps.setNull(npos, sqlType) else p.ps.setArray(npos,
> o.get)*
> *    p.pos = npos*
> *  }*
> *
> *
> *  def nextValue(r: PositionedResult) =  {*
> *    val npos = r.currentPos + 1*
> *    val a = r.rs.getArray(npos)*
> *    r.skip*
> *    a.getArray.asInstanceOf[Array[Integer]].map(i => i:Int)*
> *  }*
> *
> *
> *  final def updateValue(value: Array[Int], r: PositionedResult) {*
> *    val npos = r.currentPos + 1*
> *    r.rs.updateArray (npos, value)*
> *    r.skip*
> *  }*
> *  // TODO is this correct?*
> *  override def valueToSQLLiteral(v: Array[Int]) = "'" + v + "'"*
> *}*
> *
> *
> *object StringArrayTypeMapper extends ArrayTypeMapper[String] {*
> *
> *
> *  def zero = Array[String]()*
> *
> *
> *  def setValue(value: Array[String], p: PositionedParameters) {*
> *    val npos = p.pos + 1*
> *    p.ps.setArray(npos, value)*
> *    p.pos = npos*
> *  }*
> *
> *
> *  def setOption(value: Option[Array[String]], p: PositionedParameters) {*
> *    val npos = p.pos + 1*
> *    if (value eq None) p.ps.setNull(npos, sqlType)*
> *    else p.ps.setArray(npos, value.get)*
> *    p.pos = npos*
> *  }*
> *
> *
> *  def nextValue(r: PositionedResult) =  {*
> *    val npos = r.currentPos + 1*
> *    val a = r.rs.getArray(npos)*
> *    r.skip*
> *    a.getArray.asInstanceOf[Array[String]]*
> *  }*
> *
> *
> *  final def updateValue(value: Array[String], r: PositionedResult) {*
> *    val npos = r.currentPos + 1*
> *    r.rs.updateArray (npos, value)*
> *    r.skip*
> *  }*
> *  // TODO is this correct?*
> *  override def valueToSQLLiteral(v: Array[String]) = "'" + v + "'"*
> *}*
> *
> *
> *object HasArrays extends Table[(Int, Array[Int],
> Array[String])]("hasarrays") {*
> *  def id  = column[Int]("id")*
> *  def ofInt = column[Array[Int]]("ofints")(IntArrayTypeMapper)*
> *  def ofString = column[Array[String]]("ofvarchars")(StringArrayTypeMapper)
> *
> *  def * = id ~ ofInt ~ ofString*
> *}*

>
> 3) Here is a REPL log showing what worked and didn't work:
>

> *scala> :load ArrayMappers.scala*
> *Loading ArrayMappers.scala...*
>
> *scala> val projection = HasArrays.id ~ HasArrays.ofInt ~ HasArrays.ofString
> *
> *projection: scala.slick.lifted.Projection3[Int,Array[Int],Array[String]] =
> Projection3*
> *
> *
> *scala> projection.insert(1, Array(2,5,12,-2),
> Array("foo","bar","baz"))(DB.session)*
> *res0: Int = 1*
> *
> *
> *scala> HasArrays.map(_.ofString).first()(DB.session)*
> *res1: Array[String] = Array(foo, bar, baz)*
> *
> *
> *scala> HasArrays.map(_.ofInt).first()(DB.session)*
> *res2: Array[Int] = Array(2, 5, 12, -2)*
> *
> *
> *scala> projection.insert(2, Array(2,3,1), Array("
> qu'x\\","b}l\"etch","wal,do "))(DB.session)*
> *res3: Int = 1*
> *
> *
> *scala> HasArrays.filter(_.id === 2).map(_.ofString).first()(DB.session)*
> *res4: Array[String] = Array(" qu'x\", b}l"etch, "wal,do ")*
> *
> *
> *scala> HasArrays.filter(_.id ===
> 2).map(_.ofString).update(Array("a","b","d"))(DB.session)*
> *res5: Int = 1*
> *
> *
> *scala> HasArrays.filter(_.id === 2).map(_.ofString).first()(DB.session)*
> *res6: Array[String] = Array(a, b, d)*
> *
> *
> *scala> HasArrays.filter(_.ofInt ===
> Array(2,3,1)).map(_.ofString).first()(DB.session)*
> *<console>:20: error: value === is not a member of
> scala.slick.lifted.Column[Array[Int]]*
> *              HasArrays.filter(_.ofInt ===
> Array(2,3,1)).map(_.ofString).first()(DB.session)*
> *                                       ^*

[Adam Mackler]

Thanks for the encouragement.  Your question about using storing array elements in rows of a separate table is apropos, especially as I am personally a strong advocate of higher levels of normalization.  I note in passing that no less an authority than Chris Date acknowledges that multivalued columns, including arrays, may conform to the atomicity requirement of 1NF[1].

One of the practical reasons that I am motivated to use arrays in this situation, specifically in the context of SLICK, is that it appears to me that I would need to cause a separate SQL INSERT statement to be executed for each element, even if I use the InsertInvoker.insertAll() method.  Apparently this may not be true when using a RDBMS other than PostgreSQL, but I am under the impression that the PostgreSQL JDBC driver does not support batch inserts.  I wouldn't mind learning that I'm wrong about this, however until that happens, using an array seems to be the only way to get all the data into the database in one SQL statement (unless I want to start putting SQL into my Scala code, which would defeat the purpose of using SLICK).

Ironically enough, the trigger procedure that I have on the table into which the array values are inserted uses the unnest() and array() functions to convert the array to an in-memory table to sort it and remove duplicates before turning it back into an array for insertion, so while I may not be quite on the fence regarding the table-vs-array issue, I can see that there is green grass on both sides.

Adam Mackler

[1] "[T]he notion of atomicity has no absolute meaning; it just depends on what we want to do with the data.  Sometimes we want to deal with an entire set of part numbers as a single thing; sometimes we want to deal with individual part numbers within that set--but then we're descending to a lower level of detail, or lower level of abstraction."  C. J. Date, SQL and Relational Theory, 2nd ed, p. 53, 

[Adam Mackler]

Update, possibly final:

First, to virtualeyes, thank you for the suggestion to look at "inSet."  Alas, I could not make heads nor tails of what it means or does.

However, the solution to the problem appearing on the last line of the previous post is solved.  The problem was that my BaseTypeMapper extension objects, IntArrayTypeMapper and StringArrayTypeMapper, were not declared as implicit.  Thus the implicit conversion to the class that has the "===" operator, ColumnExtensionMethods, was not getting those objects as the implicit evidence parameter it needed.  I added the implicit keyword to the definition of those two objects and === is now found and used.

That led to the next problem, easily corrected, namely my overriding definitions of the valueToSQLLiteral methods.  I changed them both to:

    "'" + (v: java.sql.Array).toString + "'"

Note that I'm using PostgreSQL.  Since the toString method is defined in the PostgreSQL JDBC driver, it wouldn't surprise me at all if this did not work the same with other database systems.

With those changes in place, filtering on a predicate involving an Array value now works, both with the Integer array that is shown in the last failing line in the previous post, as well as with a String array like this:

HasArrays.filter(_.ofString === Array[String]("a","b","d")).map(_.ofInt).first()(DB.session)

Notice that here I parameterize the Array invocation with the type variable String, whereas I did not do the same for an Integer Array.  Why the parameterization it is necessary for a String Array but not an Integer Array is still a mystery to me, but at least it is not impeding my progress as did the now-solved mysteries that motivated my original post.

--

Adam Mackler

[Pyry-Samuli Lahti]

Any experiences after this? In addition; would you happen to have Slick 1.0 compatible implementation easily available? :)

[Pyry-Samuli Lahti]

FYI: I think the only issue was sqlTypeName definition, which had to be overridden. Anyway this works for us with PostgreSQL: https://gist.github.com/Pyppe/5526629.

However, unfortunately H2 does not support Connection.createArrayOf -method, so this approach does not work with that.

- Pyppe

[Christopher Vogt]

I haven't tried it, but I found this: https://github.com/tminglei/slick-pg

"""
Slick extensions for PostgreSQL, to support a series of pg data types
and related operators/functions.
Currently supported data types:

ARRAY
Range
Hstore
Search(tsquery+tsvector)
Geometry

** tested on postgreSQL 9.2 with Slick 1.0.
"""

/Chris

> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "Slick / ScalaQuery" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to scalaquery+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>