i've table user_permissions has 46 permission columns along id , created_date. table has 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 want update userpermission set identified id. function 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 not working , throwing exception:
play.api.application$$anon$1: execution exception[[sqlserverexception: cannot update identity column 'id'.]] which makes sense sql generated slick is:
update "users_permissions" set "id" = ?, "created_date" = ?, ... problem 1 first problem i'm unable update full userpermission object slick. if i've solution problem great.
since i'm unable update full object thought yield columns want update fire update query. code looks this:
def update(obj: userpermission)(implicit session: session) = { val query = { 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 slick not updating 46 columns in query.update() function. can handle 22 columns @ 1 time. how can update userpermissions object?
one bad solution can think update 22 first time, 22 second, 2 in third query. it'll 3 db update queries don't want.
any solutions problem?
dependencies are:
scalaversion := "2.11.4" "com.typesafe.play" %% "play-slick" % "0.8.1" "com.typesafe.slick" %% "slick-extensions" % "2.1.0"
stefan zeiger, slick lead, said couldn't. suggested have nested projections on flat 22+ columns table:
// 2 classes 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 it's table[int] -- map 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") // default projection -- doesn't have contain columns def * = id // instead, use nested tuples full projection: def = ( 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) ) // , override create_* ddl columns. // yeah, ugly. used simpler in scalaquery. // can add helper method simplify it. override def create_* = all.shaped.packednode.collect { case select(ref(intrinsicsymbol(in)), f: fieldsymbol) if in == => f }.toseq.distinct } t.ddl.create // insert t.all. ".shaped" call needed because cannot // types in implicit conversion due 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) ) // nested tuples in query val q1 = t.map(_.all) println(q1.first) // map result 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 test @ slick including 1 version 3. 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 slick's projections can flattened single object bring in, or take away with.
Comments
Post a Comment