细谈Slick（6）－ Projection：ProvenShape，强类型的Query结果类型
在Slick官方文档中描述：连接后台数据库后，需要通过定义Projection，即def * 来进行具体库表列column的选择和排序。通过Projection我们可以选择库表中部分列、也可以增加一些自定义列computed column。具体来说Projection提供了数据库表列与Scala值的对应。例如def * = (column1,column2)把库表的column1和column2与(Int,String)对应，column1[Int]，column2[String]。也可以说是与定义column的类参数进行对应。从Slick源代码中我们可以找到Projection定义：
abstract class AbstractTable[T](val tableTag: Tag, val schemaName: Option[String], val tableName: String) extends Rep[T] {
/** The client-side type of the table as defined by its * projection */
type TableElementType
...
/** The * projection of the table used as default for queries and inserts.
* Should include all columns as a tuple, HList or custom shape and optionally
* map them to a custom entity type using the <> operator.
* The `ProvenShape` return type ensures that
* there is a `Shape` available for translating between the `Column`-based
* type in * and the client-side type without `Column` in the table's type
* parameter. */
def * : ProvenShape[T]
...
}
我们看到Projection是个ProvenShape[T]类。再看看ProvenShape是怎么定义的：
/** A limited version of ShapedValue which can be constructed for every type
* that has a valid shape. We use it to enforce that a table's * projection
* has a valid shape. A ProvenShape has itself a Shape so it can be used in
* place of the value that it wraps for purposes of packing and unpacking. */
trait ProvenShape[U] {
def value: Any
val shape: Shape[_ <: FlatShapeLevel, _, U, _]
def packedValue[R](implicit ev: Shape[_ <: FlatShapeLevel, _, U, R]): ShapedValue[R, U]
def toNode = packedValue(shape).toNode
}
object ProvenShape {
/** Convert an appropriately shaped value to a ProvenShape */
implicit def proveShapeOf[T, U](v: T)(implicit sh: Shape[_ <: FlatShapeLevel, T, U, _]): ProvenShape[U] =
new ProvenShape[U] {
def value = v
val shape: Shape[_ <: FlatShapeLevel, _, U, _] = sh.asInstanceOf[Shape[FlatShapeLevel, _, U, _]]
def packedValue[R](implicit ev: Shape[_ <: FlatShapeLevel, _, U, R]): ShapedValue[R, U] = ShapedValue(sh.pack(value).asInstanceOf[R], sh.packedShape.asInstanceOf[Shape[FlatShapeLevel, R, U, _]])
}
/** The Shape for a ProvenShape */
implicit def provenShapeShape[T, P](implicit shape: Shape[_ <: FlatShapeLevel, T, T, P]): Shape[FlatShapeLevel, ProvenShape[T], T, P] = new Shape[FlatShapeLevel, ProvenShape[T], T, P] {
def pack(value: Mixed): Packed =
value.shape.pack(value.value.asInstanceOf[value.shape.Mixed]).asInstanceOf[Packed]
def packedShape: Shape[FlatShapeLevel, Packed, Unpacked, Packed] =
shape.packedShape.asInstanceOf[Shape[FlatShapeLevel, Packed, Unpacked, Packed]]
def buildParams(extract: Any => Unpacked): Packed =
shape.buildParams(extract.asInstanceOf[Any => shape.Unpacked])
def encodeRef(value: Mixed, path: Node) =
value.shape.encodeRef(value.value.asInstanceOf[value.shape.Mixed], path)
def toNode(value: Mixed): Node =
value.shape.toNode(value.value.asInstanceOf[value.shape.Mixed])
}
}
从implicit def proveShapeOf[T,U](v：T)：ProvenShape[U]可以得出对于任何T，如果能提供Shape[_,_,T,U,_]的隐式实例implicit instance的话就能构建出ProvenShape[U]。我们再看看什么是Shape：
/** A type class that encodes the unpacking `Mixed => Unpacked` of a
* `Query[Mixed]` to its result element type `Unpacked` and the packing to a
* fully packed type `Packed`, i.e. a type where everything which is not a
* transparent container is wrapped in a `Column[_]`.
*
* =Example:=
* - Mixed: (Column[Int], Column[(Int, String)], (Int, Option[Double]))
* - Unpacked: (Int, (Int, String), (Int, Option[Double]))
* - Packed: (Column[Int], Column[(Int, String)], (Column[Int], Column[Option[Double]]))
* - Linearized: (Int, Int, String, Int, Option[Double])
*/
abstract class Shape[Level <: ShapeLevel, -Mixed, Unpacked_, Packed_] {...}
上面的Mixed就是ProvenShape的T，Unpacked就是U。如此看来T代表Query[T]的T，而U就是返回结果类型了。如果我们能提供T的Shape隐式实例就能把U升格成ProvenShape[U]。我们来看看Slick官方文件上的例子：
import scala.reflect.ClassTag
// A custom record class
case class Pair[A, B](a: A, b: B)
// A Shape implementation for Pair
final class PairShape[Level <: ShapeLevel, M <: Pair[_,_], U <: Pair[_,_] : ClassTag, P <: Pair[_,_]](
val shapes: Seq[Shape[_, _, _, _]])
extends MappedScalaProductShape[Level, Pair[_,_], M, U, P] {
def buildValue(elems: IndexedSeq[Any]) = Pair(elems(0), elems(1))
def copy(shapes: Seq[Shape[_ <: ShapeLevel, _, _, _]]) = new PairShape(shapes)
}
implicit def pairShape[Level <: ShapeLevel, M1, M2, U1, U2, P1, P2](
implicit s1: Shape[_ <: Level, M1, U1, P1], s2: Shape[_ <: Level, M2, U2, P2]
) = new PairShape[Level, Pair[M1, M2], Pair[U1, U2], Pair[P1, P2]](Seq(s1, s2))
// Use it in a table definition
class A(tag: Tag) extends Table[Pair[Int, String]](tag, "shape_a") {
def id = column[Int]("id", O.PrimaryKey)
def s = column[String]("s")
def * = Pair(id, s)
}
val as = TableQuery[A]
现在Projection可以写成Pair(id,s)。也就是说因为有了implicit def pairShape[...](...)：PairShape所以Pair(id,s)被升格成ProvenShape[Pair]。这样Query的返回类型就是Seq[Pair]了。实际上Slick本身提供了Tuple、Case Class、HList等类型的默认Shape隐式实例，所以我们可以把Projection直接写成 def * = (...) 或 Person(...) 或 Int::String::HNil。下面是Tuple的默认Shape：
trait TupleShapeImplicits {
@inline
implicit final def tuple1Shape[Level <: ShapeLevel, M1, U1, P1](implicit u1: Shape[_ <: Level, M1, U1, P1]): Shape[Level, Tuple1[M1], Tuple1[U1], Tuple1[P1]] =
new TupleShape[Level, Tuple1[M1], Tuple1[U1], Tuple1[P1]](u1)
@inline
implicit final def tuple2Shape[Level <: ShapeLevel, M1,M2, U1,U2, P1,P2](implicit u1: Shape[_ <: Level, M1, U1, P1], u2: Shape[_ <: Level, M2, U2, P2]): Shape[Level, (M1,M2), (U1,U2), (P1,P2)] =
new TupleShape[Level, (M1,M2), (U1,U2), (P1,P2)](u1,u2)
...
回到主题，下面是一个典型的Slick数据库表读取例子：
1 class TupleTypedPerson(tag: Tag) extends Table[(
2 Option[Int],String,Int,Option[String])](tag,"PERSON") {
3 def id = column[Int]("id",O.PrimaryKey,O.AutoInc)
4 def name = column[String]("name")
5 def age = column[Int]("age")
6 def alias = column[Option[String]]("alias")
7 def * = (id.?,name,age,alias)
8 }
9 val tupleTypedPerson = TableQuery[TupleTypedPerson]
10
11 val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
12 val createSchemaAction = tupleTypedPerson.schema.create
13 Await.ready(db.run(createSchemaAction),Duration.Inf)
14 val initDataAction = DBIO.seq {
15 tupleTypedPerson ++= Seq(
16 (Some(0),"Tiger Chan", 45, Some("Tiger_XC")),
17 (Some(0),"Johnny Cox", 17, None),
18 (Some(0),"Cathy Williams", 18, Some("Catty")),
19 (Some(0),"David Wong", 43, None)
20 )
21 }
22 Await.ready(db.run(initDataAction),Duration.Inf)
23 val queryAction = tupleTypedPerson.result
24
25 Await.result(db.run(queryAction),Duration.Inf).foreach {row =>
26 println(s"${row._1.get} ${row._2} ${row._4.getOrElse("")}, ${row._3}")
27 }
在这个例子的表结构定义里默认的Projection是个Tuple。造成的后果是返回的结果行不含字段名，只有字段位置。使用这样的行数据很容易错误对应，或者重复确认正确的列值会影响工作效率。如果返回的结果类型是Seq[Person]这样的话：Person是个带属性的对象如case class，那么我们就可以通过IDE提示的字段名称来选择字段了。上面提过返回结果类型可以通过ProvenShape来确定，如果能实现ProvenShape[A] => ProvenShape[B]这样的转换处理，那么我们就可以把返回结果行类型从Tuple变成有字段名的类型了：
1 class Person(val id: Option[Int],
2 val name: String, val age: Int, val alias: Option[String])
3 def toPerson(t: (Option[Int],String,Int,Option[String])) = new Person (
4 t._1,t._2,t._3,t._4
5 )
6 def fromPerson(p: Person) = Some((p.id,p.name,p.age,p.alias))
7 class TupleMappedPerson(tag: Tag) extends Table[
8 Person](tag,"PERSON") {
9 def id = column[Int]("id",O.PrimaryKey,O.AutoInc)
10 def name = column[String]("name")
11 def age = column[Int]("age")
12 def alias = column[Option[String]]("alias")
13 def * = (id.?,name,age,alias) <> (toPerson,fromPerson)
14 }
15 val tupleMappedPerson = TableQuery[TupleMappedPerson]
16
17 Await.result(db.run(tupleMappedPerson.result),Duration.Inf).foreach {row =>
18 println(s"${row.id.get} ${row.name} ${row.alias.getOrElse("")}, ${row.age}")
19 }
我们用<>函数进行了Tuple=>Person转换。注意toPerson和fromPerson这两个相互转换函数。如果Person是个case class，那么Person.tupled和Person.unapply就是它自备的转换函数，我们可以用case class来构建MappedProjection：
1 case class Person(id: Option[Int]=None, name: String, age: Int, alias: Option[String])
2
3 class MappedTypePerson(tag: Tag) extends Table[Person](tag,"PERSON") {
4 def id = column[Int]("id",O.PrimaryKey,O.AutoInc)
5 def name = column[String]("name")
6 def age = column[Int]("age")
7 def alias = column[Option[String]]("alias")
8 def * = (id.?,name,age,alias) <> (Person.tupled,Person.unapply)
9 }
10 val mappedPeople = TableQuery[MappedTypePerson]
从上面两个例子里我们似乎可以得出ProvenShape[T]的T类型就是Table[T]的T，也就是返回结果行的类型了。我们可以用同样方式来进行HList与Person转换：
1 def hlistToPerson(hl: Option[Int]::String::Int::(Option[String])::HNil) =
2 new Person(hl(0),hl(1),hl(2),hl(3))
3 def personToHList(p: Person) = Some(p.id::p.name::p.age::p.alias::HNil)
4 class HListPerson(tag: Tag) extends Table[Person](tag,"PERSON") {
5 def id = column[Int]("id",O.PrimaryKey,O.AutoInc)
6 def name = column[String]("name")
7 def age = column[Int]("age")
8 def alias = column[Option[String]]("alias")
9 def * = (id.?)::name::age::alias::HNil <> (hlistToPerson,personToHList)
10 }
11 val hlistPerson = TableQuery[HListPerson]
12 Await.result(db.run(hlistPerson.result),Duration.Inf).foreach {row =>
13 println(s"${row.id.get} ${row.name} ${row.alias.getOrElse("")}, ${row.age}")
14 }
同样，必须首先实现hlistToPerson和personToHList转换函数。现在Table的类型参数必须是Person。上面的Projection都是对Table默认Projection的示范。实际上我们可以针对每个Query来自定义Projection，如下：
1 case class YR(name: String, yr: Int)
2
3 val qYear = for {
4 p <- hlistPerson
5 } yield ((p.name, p.age) <> (YR.tupled,YR.unapply))
6
7 Await.result(db.run(qYear.result),Duration.Inf).foreach {row =>
8 println(s"${row.name} ${row.yr}")
9 }
上面这个例子里我们构建了基于case class YR的projection。在join table query情况下只能通过这种方式来构建Projection，看看下面这个例子：
1 case class Title(id: Int, title: String)
2 class PersonTitle(tag: Tag) extends Table[Title](tag,"TITLE") {
3 def id = column[Int]("id")
4 def title = column[String]("title")
5 def * = (id,title) <> (Title.tupled,Title.unapply)
6 }
7 val personTitle = TableQuery[PersonTitle]
8 val createTitleAction = personTitle.schema.create
9 Await.ready(db.run(createTitleAction),Duration.Inf)
10 val initTitleData = DBIO.seq {
11 personTitle ++= Seq(
12 Title(1,"Manager"),
13 Title(2,"Programmer"),
14 Title(3,"Clerk")
15 )
16 }
17 Await.ready(db.run(initTitleData),Duration.Inf)
18
19 case class Titles(id: Int, name: String, title: String)
20 val qPersonWithTitle = for {
21 p <- hlistPerson
22 t <- personTitle if p.id === t.id
23 } yield ((p.id,p.name,t.title) <> (Titles.tupled,Titles.unapply))
24 Await.result(db.run(qPersonWithTitle.result),Duration.Inf).foreach {row =>
25 println(s"${row.id} ${row.name}, ${row.title}")
26 }
现在对任何形式的Query结果我们都能使用强类型（strong typed）的字段名称来进行操作了。
下面是本次示范的源代码：
1 import slick.collection.heterogeneous.{ HList, HCons, HNil }
2 import slick.collection.heterogeneous.syntax._
3 import slick.driver.H2Driver.api._
4
5 import scala.concurrent.ExecutionContext.Implicits.global
6 import scala.concurrent.duration._
7 import scala.concurrent.{Await, Future}
8
9
10 object chkProjection {
11
12 class TupleTypedPerson(tag: Tag) extends Table[(
13 Option[Int],String,Int,Option[String])](tag,"PERSON") {
14 def id = column[Int]("id",O.PrimaryKey,O.AutoInc)
15 def name = column[String]("name")
16 def age = column[Int]("age")
17 def alias = column[Option[String]]("alias")
18 def * = (id.?,name,age,alias)
19 }
20 val tupleTypedPerson = TableQuery[TupleTypedPerson]
21
22 val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
23 val createSchemaAction = tupleTypedPerson.schema.create
24 Await.ready(db.run(createSchemaAction),Duration.Inf)
25 val initDataAction = DBIO.seq {
26 tupleTypedPerson ++= Seq(
27 (Some(0),"Tiger Chan", 45, Some("Tiger_XC")),
28 (Some(0),"Johnny Cox", 17, None),
29 (Some(0),"Cathy Williams", 18, Some("Catty")),
30 (Some(0),"David Wong", 43, None)
31 )
32 }
33 Await.ready(db.run(initDataAction),Duration.Inf)
34
35 val queryAction = tupleTypedPerson.result
36
37 Await.result(db.run(queryAction),Duration.Inf).foreach {row =>
38 println(s"${row._1.get} ${row._2} ${row._4.getOrElse("")}, ${row._3}")
39 }
40
41 class Person(val id: Option[Int],
42 val name: String, val age: Int, val alias: Option[String])
43 def toPerson(t: (Option[Int],String,Int,Option[String])) = new Person (
44 t._1,t._2,t._3,t._4
45 )
46 def fromPerson(p: Person) = Some((p.id,p.name,p.age,p.alias))
47 class TupleMappedPerson(tag: Tag) extends Table[
48 Person](tag,"PERSON") {
49 def id = column[Int]("id",O.PrimaryKey,O.AutoInc)
50 def name = column[String]("name")
51 def age = column[Int]("age")
52 def alias = column[Option[String]]("alias")
53 def * = (id.?,name,age,alias) <> (toPerson,fromPerson)
54 }
55 val tupleMappedPerson = TableQuery[TupleMappedPerson]
56
57 Await.result(db.run(tupleMappedPerson.result),Duration.Inf).foreach {row =>
58 println(s"${row.id.get} ${row.name} ${row.alias.getOrElse("")}, ${row.age}")
59 }
60
61 def hlistToPerson(hl: Option[Int]::String::Int::(Option[String])::HNil) =
62 new Person(hl(0),hl(1),hl(2),hl(3))
63 def personToHList(p: Person) = Some(p.id::p.name::p.age::p.alias::HNil)
64 class HListPerson(tag: Tag) extends Table[Person](tag,"PERSON") {
65 def id = column[Int]("id",O.PrimaryKey,O.AutoInc)
66 def name = column[String]("name")
67 def age = column[Int]("age")
68 def alias = column[Option[String]]("alias")
69 def * = (id.?)::name::age::alias::HNil <> (hlistToPerson,personToHList)
70 }
71 val hlistPerson = TableQuery[HListPerson]
72 Await.result(db.run(hlistPerson.result),Duration.Inf).foreach {row =>
73 println(s"${row.id.get} ${row.name} ${row.alias.getOrElse("")}, ${row.age}")
74 }
75
76 case class YR(name: String, yr: Int)
77
78 val qYear = for {
79 p <- hlistPerson
80 } yield ((p.name, p.age) <> (YR.tupled,YR.unapply))
81
82 Await.result(db.run(qYear.result),Duration.Inf).foreach {row =>
83 println(s"${row.name} ${row.yr}")
84 }
85
86 case class Title(id: Int, title: String)
87 class PersonTitle(tag: Tag) extends Table[Title](tag,"TITLE") {
88 def id = column[Int]("id")
89 def title = column[String]("title")
90 def * = (id,title) <> (Title.tupled,Title.unapply)
91 }
92 val personTitle = TableQuery[PersonTitle]
93 val createTitleAction = personTitle.schema.create
94 Await.ready(db.run(createTitleAction),Duration.Inf)
95 val initTitleData = DBIO.seq {
96 personTitle ++= Seq(
97 Title(1,"Manager"),
98 Title(2,"Programmer"),
99 Title(3,"Clerk")
100 )
101 }
102 Await.ready(db.run(initTitleData),Duration.Inf)
103
104 case class Titles(id: Int, name: String, title: String)
105 val qPersonWithTitle = for {
106 p <- hlistPerson
107 t <- personTitle if p.id === t.id
108 } yield ((p.id,p.name,t.title) <> (Titles.tupled,Titles.unapply))
109 Await.result(db.run(qPersonWithTitle.result),Duration.Inf).foreach {row =>
110 println(s"${row.id} ${row.name}, ${row.title}")
111 }
112
113
114 }
