Slick - Update full object or more than 22 columns

Question

I've a table user_permissions which has 46 permission columns along with id and created_date. This table has a corresponding UserPermissions class:

class UserPermission(val id: Long,
  val createdDate: Option[Timestamp],
  val permission1: Boolean,
  val permission2: Boolean,
  ...
  val permission46: Boolean)

and slick mapping table

class UserPermissions(tag: Tag) extends Table[UserPermission](tag, "users_permissions") {
  def * = (
    id ::
    createdDate ::
    permission1 ::
    permission2 ::
    ...
    permission46 ::
    HNil).shaped <> (
    { case x => UserPermission(
         x(0), x(1), x(2), ... x(47))
    },
    {
       UserPermission.unapply _
    }
  }
  ... <columns defined here>
)

Now I want to update UserPermission set which is identified by id. The function that I've is:

object UserPermissions {
  val userPermissions = TableQuery[UserPermissions]

  def update(userPermission: UserPermission)(implicit session: Session) = {
    userPermissions.filter(_.id === userPermission.id.get).update(userPermission)
  }
}

This is not working and throwing Exception:

play.api.Application$$anon$1: Execution exception[[SQLServerException: Cannot update identity column 'id'.]]

which makes sense as the SQL generated by Slick is:

update "users_permissions" set "id" = ?, "created_date" = ?, ...

Problem 1 So my first problem is that I'm unable to update a full UserPermission object with slick. If I've a solution to this problem then it would be great.

---

Since I'm unable to update full object then I thought to yield the columns I want to update then fire an update query. The code looks like this:

def update(obj: UserPermission)(implicit session: Session) = {
    val query = for {
      p <- userPermissions
      if p.id === obj.id.get
    } yield (p.permission1, p.permission2, ... p.permission46)
    query.update(obj.permission1, obj.permission2, ... obj.permission46)
}

Problem 2 Now slick is not updating 46 columns in query.update() function. It can handle only 22 columns at one time. How can I update my UserPermissions object?

One bad solution I can think is to update 22 first time, then 22 second, then 2 in third query. It'll be 3 db update queries which I don't want.

Any solutions to my problem?

---

Dependencies are:

---

scalaVersion := "2.11.4"

---

"com.typesafe.play" %% "play-slick" % "0.8.1"
"com.typesafe.slick" %% "slick-extensions" % "2.1.0"

---

Answer 1

This question is tagged by slick-2.0, but it is the first link if you google "slick update more than 22", so I believe it is worth to describe the workaround in details.

As was mentioned before, since v3, Slick has own implementation of Heterogeneous List.

Please find the whole Slick 3.3.3 example below:

import slick.collection.heterogeneous.HNil
import slick.jdbc.H2Profile.api._
import slick.jdbc.JdbcBackend.Database

import scala.concurrent.Await
import scala.concurrent.duration._
import scala.concurrent.ExecutionContext.Implicits.global

object HelloWorld {

  def main(args: Array[String]): Unit = {
    val db = Database.forURL("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", driver="org.h2.Driver")

    val table = new TableQuery(tag => new ManyColumnTable(tag))

    val insertAction = table += Row(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)

    val updateAction = table
      .map { r => r.c1 :: r.c2 :: r.c3 :: r.c4 :: r.c5 :: r.c6 :: r.c7 :: r.c8 :: r.c9 :: r.c10 :: r.c11 :: r.c12 ::
                  r.c13 :: r.c14 :: r.c15 :: r.c16 :: r.c17 :: r.c18 :: r.c19 :: r.c20 :: r.c21 :: r.c22 :: r.c23 ::
                  r.c24 :: HNil }
      .update { 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 :: 0 ::
                0 :: 0 :: 0 :: 0 :: HNil }

    val f = for {
      _ <- db.run(table.schema.create)
      insertedRows <- db.run(insertAction)
      updatedRows <- db.run(updateAction)
    } yield {
      println(s"There were $insertedRows rows inserted")
      println(s"There were $updatedRows rows updated")
    }

    Await.ready(f, 10.seconds)
  }

  case class Row(c1:Int,c2:Int,c3:Int,c4:Int,c5:Int,c6:Int,c7:Int,c8:Int,c9:Int,c10:Int,c11:Int,c12:Int,c13:Int,
                 c14:Int,c15:Int,c16:Int,c17:Int,c18:Int,c19:Int,c20:Int,c21:Int,c22:Int,c23:Int,c24:Int)
  class ManyColumnTable(tag: Tag) extends Table[Row](tag, "many_columns_table") {

    def * = (c1 :: c2 :: c3 :: c4 :: c5 :: c6 :: c7 :: c8 :: c9 :: c10 :: c11 :: c12 :: c13 :: c14 :: c15 :: c16 ::
             c17 :: c18 :: c19 :: c20 :: c21 :: c22 :: c23 :: c24 :: HNil).mapTo[Row]

    def c1 = column[Int]("c1")
    def c2 = column[Int]("c2")
    def c3 = column[Int]("c3")
    def c4 = column[Int]("c4")
    def c5 = column[Int]("c5")
    def c6 = column[Int]("c6")
    def c7 = column[Int]("c7")
    def c8 = column[Int]("c8")
    def c9 = column[Int]("c9")
    def c10 = column[Int]("c10")
    def c11 = column[Int]("c11")
    def c12 = column[Int]("c12")
    def c13 = column[Int]("c13")
    def c14 = column[Int]("c14")
    def c15 = column[Int]("c15")
    def c16 = column[Int]("c16")
    def c17 = column[Int]("c17")
    def c18 = column[Int]("c18")
    def c19 = column[Int]("c19")
    def c20 = column[Int]("c20")
    def c21 = column[Int]("c21")
    def c22 = column[Int]("c22")
    def c23 = column[Int]("c23")
    def c24 = column[Int]("c24")

  }

}

Answer 2

Stefan Zeiger, the Slick lead, said we couldn't. He suggested however that we have nested projections over the flat 22+ columns table:

// 2 classes for the nested structure
case class Part(i1: Int, i2: Int, i3: Int, i4: Int, i5: Int, i6: Int)
case class Whole(id: Int, p1: Part, p2: Part, p3: Part, p4: Part)

// Note that it's a Table[Int] -- we only map the primary key in *
object T extends Table[Int]("t_wide") {
  def id = column[Int]("id", O.PrimaryKey)
  def p1i1 = column[Int]("p1i1")
  def p1i2 = column[Int]("p1i2")
  def p1i3 = column[Int]("p1i3")
  def p1i4 = column[Int]("p1i4")
  def p1i5 = column[Int]("p1i5")
  def p1i6 = column[Int]("p1i6")
  def p2i1 = column[Int]("p2i1")
  def p2i2 = column[Int]("p2i2")
  def p2i3 = column[Int]("p2i3")
  def p2i4 = column[Int]("p2i4")
  def p2i5 = column[Int]("p2i5")
  def p2i6 = column[Int]("p2i6")
  def p3i1 = column[Int]("p3i1")
  def p3i2 = column[Int]("p3i2")
  def p3i3 = column[Int]("p3i3")
  def p3i4 = column[Int]("p3i4")
  def p3i5 = column[Int]("p3i5")
  def p3i6 = column[Int]("p3i6")
  def p4i1 = column[Int]("p4i1")
  def p4i2 = column[Int]("p4i2")
  def p4i3 = column[Int]("p4i3")
  def p4i4 = column[Int]("p4i4")
  def p4i5 = column[Int]("p4i5")
  def p4i6 = column[Int]("p4i6")
  // This is just the default projection -- It doesn't have to contain all columns
  def * = id
  // Instead, we use nested tuples for a full projection:
  def all = (
    id,
    (p1i1, p1i2, p1i3, p1i4, p1i5, p1i6),
    (p2i1, p2i2, p2i3, p2i4, p2i5, p2i6),
    (p3i1, p3i2, p3i3, p3i4, p3i5, p3i6),
    (p4i1, p4i2, p4i3, p4i4, p4i5, p4i6)
  )
  // And override create_* to get the DDL for all columns.
  // Yeah, this is ugly. It used to be much simpler in ScalaQuery.
  // We can add a helper method to simplify it.
  override def create_* =
    all.shaped.packedNode.collect {
      case Select(Ref(IntrinsicSymbol(in)), f: FieldSymbol) if in == this => f
    }.toSeq.distinct
}

T.ddl.create
// Insert into T.all. The extra ".shaped" call is needed because we cannot
// get the types in an implicit conversion due to SI-3346
T.all.shaped.insert(
  0,
  (11, 12, 13, 14, 15, 16),
  (21, 22, 23, 24, 25, 26),
  (31, 32, 33, 34, 35, 36),
  (41, 42, 43, 44, 45, 46)
)

// Get the nested tuples in a query
val q1 = T.map(_.all)
println(q1.first)

// Map the result to the case classes
val i2 = q1.mapResult { case (id, p1, p2, p3, p4) =>
  Whole(id, Part.tupled.apply(p1), Part.tupled.apply(p2), Part.tupled.apply(p3), Part.tupled.apply(p4))
}
println(i2.first)

which is now a test at Slick including one for version 3. As for updating:

val oData = Whole(0,
  Part(11, 12, 13, 14, 15, 16),
  Part(21, 22, 23, 24, 25, 26),
  Part(31, 32, 33, 34, 35, 36),
  Part(41, 42, 43, 44, 45, 46)
)
val oData2 = Whole(10,
  Part(111, 12, 13, 14, 15, 16),
  Part(121, 22, 23, 24, 25, 26),
  Part(131, 32, 33, 34, 35, 36),
  Part(141, 42, 43, 44, 45, 46)
)

ts.ddl.create

ts.insert(oData)
assertEquals(oData, ts.first)

ts.filter(_.p1i2 === 12).update(oData2)
assertEquals(oData2, ts.first)

The nested objects with Slick's projections can be flattened for the single object that you bring in, or take away with.

Answer 3

Some suggestions for problem 2:

• Could you use Slick 3.0? This version seems to have a solution
• Could you change the layout of the database in such a way permissions are rows instead of columns? This seems more extensible anyway