平滑-更新完整对象或超过22列 [英] Slick - Update full object or more than 22 columns

问题描述

我有一个表user_permissions，其中有46个权限列以及id和created_date.该表具有对应的UserPermissions类:

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)

光滑的映射表

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>
)

现在，我要更新由id标识的UserPermission集.我拥有的功能是:

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'.]]

这很有意义，因为Slick生成的SQL是:

which makes sense as the SQL generated by Slick is:

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

问题1 因此，我的第一个问题是我无法使用平滑的功能来更新完整的UserPermission对象. 如果我有解决此问题的方法，那就太好了.

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)
}

问题2 ，现在 slick不会更新query.update()函数中的46列.它一次只能处理22列.如何更新我的UserPermissions对象?

我认为一个不好的解决方案是第一次更新22次，然后第二次更新22次，然后在第三次查询中更新2次.这是我不想要的3个数据库更新查询.

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?

依赖项是:

scalaVersion := "2.11.4"

---

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

---

推荐答案

Stefan Zeiger，Slick主管，说，我们不能.但他建议我们将投影嵌套在22+柱平面表上:

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)

可以将带Slick投影的嵌套对象展平为您带入或带走的单个对象.

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

这篇关于平滑-更新完整对象或超过22列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！