Slick 3.0.0 documentation - 07 Queries
Permalink to Queries — Slick 3.0.0 documentation
本章ではselect, insert, update, deleteといった処理を、SlickのクエリAPIで、どのようにして型安全なクエリを記述するのかを説明する。
このAPIは Lifted Embedding と呼ばれる。これは、実際にはScalaの基本的な型を操作するのではなく、Repの型コンストラクタへと 持ち上げられた型 を用いてる事に由来する。以下のように、Scalaのコレクション操作で扱う型と比較すると分かりやすい。
case class Coffee(name: String, price: Double)
val coffees: List[Coffee] = //...
...
val l = coffees.filter(_.price > 8.0).map(_.name)
// ^ ^ ^
// Double Double String
Slickにおいて似たような記述を行うと、以下のようになる。
class Coffees(tag: Tag) extends Table[(String, Double)](tag, "COFFEES") {
def name = column[String]("COF_NAME")
def price = column[Double]("PRICE")
def * = (name, price)
}
val coffees = TableQuery[Coffees]
...
val q = coffees.filter(_.price > 8.0).map(_.name)
// ^ ^ ^
// Rep[Double] Rep[Double] Rep[String]
全ての基本的な型はRepへと持ち上げられる。Coffeesの列を表す型もRep[(String, Double)]として扱われるのと等価になる。8.0というリテラルも、暗黙的変換により、Rep[Double]となる。これは>オペレータがRep[Double]を要求するためである。この持ち上げ操作は、クエリを生成する際のシンタックスツリーを作成するのに必要になる。Scalaの基本的な関数や値はSQLへ変換するのに十分な情報を含んではいない。
レコードでもコレクションでも無い単純なスカラー値は、暗黙的なTypedType[T]が存在し、Rep[T]により表現される。
クエリ内で一般的に用いられるオペレータやメソッドは、ExtensionMethodConversionsで定義された暗黙的な変換を通して利用される。実際のメソッドはAnyExtensionMethods、ColumnExtensionMethods、NumericColumnExtensionMethods、BooleanColumnExtensionMethods、StringColumnExtensionMethodsに存在する。（cf. ExtensionMethods）
Warning
Scalaの基本的な比較演算子は、凡そ同じように動作するものの、
==と!=に関しては、===と=!=を代わりに用いなくてはならない。これはこれらのメソッドがAnyに定義されていることから拡張する事が出来ないためである。
コレクションはQueryクラスによりRep[Seq[T]]のように表現される。ここにはflatMap、filter、take、groupByのような基本的なコレクションメソッドが含まれている。2つの異なる複合型を表すために（持ち上げられたものと、持ち上げられる前のもの e.g. Query[(Rep[Int], Rep[String]), (Int, String), Seq]）、これらのシグネチャはとても複雑なものになっている。ただ意味的には基本的にScalaのコレクションと同じようなものになっていることは確認して欲しい。
SingleColumnQueryExtensionMethodsへの暗黙的変換により、クエリやスカラー値のためのメソッドが数多く用意されている。
並び替えやフィルタリングを行うための様々なメソッドが存在する。これらは、Queryから新しいQueryを生成して返す。
val q1 = coffees.filter(_.supID === 101)
// compiles to SQL (simplified):
// select "COF_NAME", "SUP_ID", "PRICE", "SALES", "TOTAL"
// from "COFFEES"
// where "SUP_ID" = 101
...
val q2 = coffees.drop(10).take(5)
// compiles to SQL (simplified):
// select "COF_NAME", "SUP_ID", "PRICE", "SALES", "TOTAL"
// from "COFFEES"
// limit 5 offset 10
...
val q3 = coffees.sortBy(_.name.desc.nullsFirst)
// compiles to SQL (simplified):
// select "COF_NAME", "SUP_ID", "PRICE", "SALES", "TOTAL"
// from "COFFEES"
// order by "COF_NAME" desc nulls first
...
// building criteria using a "dynamic filter" e.g. from a webform.
val criteriaColombian = Option("Colombian")
val criteriaEspresso = Option("Espresso")
val criteriaRoast:Option[String] = None
...
val q4 = coffees.filter { coffee =>
List(
criteriaColombian.map(coffee.name === _),
criteriaEspresso.map(coffee.name === _),
criteriaRoast.map(coffee.name === _) // not a condition as `criteriaRoast` evaluates to `None`
).collect({case Some(criteria) => criteria}).reduceLeftOption(_ || _).getOrElse(true: Column[Boolean])
}
// compiles to SQL (simplified):
// select "COF_NAME", "SUP_ID", "PRICE", "SALES", "TOTAL"
// from "COFFEES"
// where ("COF_NAME" = 'Colombian' or "COF_NAME" = 'Espresso')
joinは2つの異なるテーブルやクエリに対して、1つのクエリを適用するのに用いられる。ApplicativeとMonadicの2種類のjoinの書き方が存在する。
Applicativeなjoinはそれぞれの結果を取得するクエリに対し、2つのクエリを結合するメソッドを呼ぶ事で実行出来る。SQLにおけるjoinと同様の制約がかかり、右側の式は左側の式に依存しなかったりする。これはScalaのスコープにおけるルールを通して自然に強制される。
val crossJoin = for {
(c, s) <- coffees join suppliers
} yield (c.name, s.name)
// compiles to SQL (simplified):
// select x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2
// inner join "SUPPLIERS" x3
...
val innerJoin = for {
(c, s) <- coffees join suppliers on (_.supID === _.id)
} yield (c.name, s.name)
// compiles to SQL (simplified):
// select x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2
// inner join "SUPPLIERS" x3
// on x2."SUP_ID" = x3."SUP_ID"
...
val leftOuterJoin = for {
(c, s) <- coffees joinLeft suppliers on (_.supID === _.id)
} yield (c.name, s.map(_.name))
// compiles to SQL (simplified):
// select x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2
// left outer join "SUPPLIERS" x3
// on x2."SUP_ID" = x3."SUP_ID"
...
val rightOuterJoin = for {
(c, s) <- coffees joinRight suppliers on (_.supID === _.id)
} yield (c.map(_.name), s.name)
// compiles to SQL (simplified):
// select x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2
// right outer join "SUPPLIERS" x3
// on x2."SUP_ID" = x3."SUP_ID"
...
val fullOuterJoin = for {
(c, s) <- coffees joinFull suppliers on (_.supID === _.id)
} yield (c.map(_.name), s.map(_.name))
// compiles to SQL (simplified):
// select x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2
// full outer join "SUPPLIERS" x3
// on x2."SUP_ID" = x3."SUP_ID"
outer joinの節では、yieldの中でmapしている。これらのjoinにおいては追加でNULLになるようなカラムが生じ、結果のカラム型がOptionに包まって返却されるためである（Noneになるのは、対応する列がなかった時など）。
MonadicなjoinはflatMapを利用する事で自動的に生成される。右辺が左辺に依存するため、理論上MonadicなjoinはApplicativeなjoinより強力なものとなる。一方で、これは通常のSQLに適したものとはならない。そのため、SlickはMonadicなjoinをApplicativeなjoinへと変換している。もしMonadicなjoinを適切な形に変換出来なければ、実行時に失敗する事になるだろう。
cross-joinはQueryのflatMapにより作成される。1つ以上のジェネレータを用いる際には、for式が役立つ。
val monadicCrossJoin = for {
c <- coffees
s <- suppliers
} yield (c.name, s.name)
// compiles to SQL:
// select x2."COF_NAME", x3."SUP_NAME"
// from "COFFEES" x2, "SUPPLIERS" x3
もし何かしらのフィルタリングを行うのなら、それはinner joinとなる。
val monadicInnerJoin = for {
c <- coffees
s <- suppliers if c.supID === s.id
} yield (c.name, s.name)
// compiles to SQL:
// select x2."COF_NAME", x3."SUP_NAME"
// from "COFFEES" x2, "SUPPLIERS" x3
// where x2."SUP_ID" = x3."SUP_ID"
このMonadicなjoinはScalaコレクションのflatMapを利用した時と同じ意味を持つ。
Note
SlickはMonadicなjoinに対し暗黙的なjoin（
select ... from a, b where ...）を、Applicativeなjoinに対し明示的なjoin（select ... from a join b on ...）を生成する。これについては、将来のバージョンで変更があるかもしれない。
関係でデータベースによってサポートされている一般的なApplicative joinに加えて、Slickは2つのクエリのペアを作成するzip joinを提供している。これはzipやzipWithメソッドを用いれば利用でき、Scalaコレクションで利用するものと同じような挙動をするものである。
val zipJoinQuery = for {
(c, s) <- coffees zip suppliers
} yield (c.name, s.name)
...
val zipWithJoin = for {
res <- coffees.zipWith(suppliers, (c: Coffees, s: Suppliers) => (c.name, s.name))
} yield res
また別のzip joinとして、zipWithIndexというものも存在する。これは0から始まる無限数列をクエリ結果と結合してくれるものである。この数列はSQLデータベースによって提供されたものではなく、Slickがサポートしているものでもない。ただの数字を吐く関数とSQLの結果を統合したものとして、zipWithIndexがプリミティブなオペレータとして提供されているのである。
val zipWithIndexJoin = for {
(c, idx) <- coffees.zipWithIndex
} yield (c.name, idx)
互換のある2つのクエリは++（もしくはunionAll）やunionで結合することが出来る。
val q1 = coffees.filter(_.price < 8.0)
val q2 = coffees.filter(_.price > 9.0)
...
val unionQuery = q1 union q2
// compiles to SQL (simplified):
// select x8."COF_NAME", x8."SUP_ID", x8."PRICE", x8."SALES", x8."TOTAL"
// from "COFFEES" x8
// where x8."PRICE" < 8.0
// union select x9."COF_NAME", x9."SUP_ID", x9."PRICE", x9."SALES", x9."TOTAL"
// from "COFFEES" x9
// where x9."PRICE" > 9.0
...
val unionAllQuery = q1 ++ q2
// compiles to SQL (simplified):
// select x8."COF_NAME", x8."SUP_ID", x8."PRICE", x8."SALES", x8."TOTAL"
// from "COFFEES" x8
// where x8."PRICE" < 8.0
// union all select x9."COF_NAME", x9."SUP_ID", x9."PRICE", x9."SALES", x9."TOTAL"
// from "COFFEES" x9
// where x9."PRICE" > 9.0
unionは重複する値については省いてしまうのに対し、++は個々のクエリ結果を単純に、より効率的に繋げるものとなっている。
集約関数はQueryから単一の値、主に計算された数値を返すものである。
val q = coffees.map(_.price)
...
val q1 = q.min
// compiles to SQL (simplified):
...
val q2 = q.max
// compiles to SQL (simplified):
// select max(x4."PRICE") from "COFFEES" x4
...
val q3 = q.sum
// compiles to SQL (simplified):
// select sum(x4."PRICE") from "COFFEES" x4
...
val q4 = q.avg
// compiles to SQL (simplified):
// select avg(x4."PRICE") from "COFFEES" x4
集約クエリはコレクションではなく、スカラー値を返却する。いくつかの集約関数は以下のような恣意的なクエリで定義されている。
val q1 = coffees.length
// compiles to SQL (simplified):
// select count(1) from "COFFEES"
...
val q2 = coffees.exists
// compiles to SQL (simplified):
// select exists(select * from "COFFEES")
グループ化はgroupByメソッドにより処理出来る。これはScalaのコレクションでの操作と同じような意味を持つ。
val q = (for {
c <- coffees
s <- c.supplier
} yield (c, s)).groupBy(_._1.supID)
val q2 = q.map { case (supID, css) =>
(supID, css.length, css.map(_._1.price).avg)
}
// compiles to SQL:
// select x2."SUP_ID", count(1), avg(x2."PRICE")
// from "COFFEES" x2, "SUPPLIERS" x3
// where x3."SUP_ID" = x2."SUP_ID"
// group by x2."SUP_ID"
中間クエリであるqはネストされたQueryの値を持っている。クエリを実行した際に、ネストしたコレクションが返却される。それゆえq2においては、集約関数を用いてネストを解消している。
クエリによる選択はresultメソッドを呼ぶことでActionへ変換される。Actionはストリームか個々に分割された方法、もしくは他のアクションを混在したものとして直接実行される。
val q = coffees.map(_.price)
val action = q.result
val result: Future[Seq[Double]] = db.run(action)
val sql = action.statements.head
もし結果を1つだけ受け取りたいのなら、headかheadOptionを用いれば良い。
削除はクエリの場合と同じように動作する。はじめに削除したい行をクエリで選択した上で、deleteメソッドを呼ぶことで削除を行うActionが得られる。
val q = coffees.filter(_.supID === 15)
val action = q.delete
val affectedRowsCount: Future[Int] = db.run(action)
val sql = action.statements.head
削除を行うクエリは、1つのテーブルのみを指定しなくてはならない。どんな射影も無視され、行はまるまる削除される。
挿入は1つのテーブルから特定のカラムを射影したものに対して実行する。テーブルを直接用いた場合には、挿入は*射影に対して実行される。挿入時にいくつかのカラムを省略した場合には、テーブル定義にあるデフォルト値が用いられるか、明示的なデフォルト値が無い場合には型に応じたデフォルト値が挿入される。挿入Actionに関する全てのメソッドは、CountingInsertActionComposerかReturningInsertActionComposerに定義されている。
val insertActions = DBIO.seq(
coffees += ("Colombian", 101, 7.99, 0, 0),
coffees ++= Seq(
("French_Roast", 49, 8.99, 0, 0),
("Espresso", 150, 9.99, 0, 0)
),
// "sales" と "total" にはデフォルト値として0が入る
coffees.map(c => (c.name, c.supID, c.price)) += ("Colombian_Decaf", 101, 8.99)
)
// insertを行うsqlのステートメントを取得
val sql = coffees.insertStatement
// compiles to SQL:
// INSERT INTO "COFFEES" ("COF_NAME","SUP_ID","PRICE","SALES","TOTAL") VALUES (?,?,?,?,?)
AutoIncがついたカラムが挿入された際には、そのカラムに対する挿入値は無視され、データベースが生成した適切な値が挿入される。大抵の場合、自動で生成された主キーの値などを返り値として取得したいと考えるだろう。デフォルトでは+=は影響を与えた行の数を返却する（普通は成功時に1が返る）。++=はOptionに包まれた結果数を返す。Noneになるのはデータベースシステムが影響を与えた数を返さない時である。これらの返り値はreturningメソッドを用いることで、好きな値が返るように変更出来る。この場合、+=に対して単一の値やタプルを返すように設定すると、++=にはその値のSeqが返却されることになる。以下の様な記述で、AutoIncで生成された主キーを返すことが出来る。
val userId =
(users returning users.map(_.id)) += User(None, "Stefan", "Zeiger")
Note
多くのデータベースでは、1つのテーブルのAutoIncrementな主キーのみを返却することを許可している。もし他のカラムについても同様の事をしようとしたならば、データベースがサポートしていない時には
SlickExceptionが投げられる。
returningにintoを続けて用いると、挿入された値と自動生成された値をもとに返り値を変更する事ができる。得られたidを用いて更新されたオブジェクトを返却する例は以下の通りとなる。
val userWithId =
(users returning users.map(_.id)
into ((user,id) => user.copy(id=Some(id)))
) += User(None, "Stefan", "Zeiger")
クライアント側でデータを挿入する以外にも、データベースサーバ側で実行されるスカラー表現やQueryを作る事でデータを挿入することも出来る。
class Users2(tag: Tag) extends Table[(Int, String)](tag, "users2") {
def id = column[Int]("id", O.PrimaryKey)
def name = column[String]("name")
def * = (id, name)
}
val users2 = TableQuery[Users2]
val actions = DBIO.seq(
users2.schema.create,
users2 forceInsertQuery (users.map { u => (u.id, u.first ++ " " ++ u.last) }),
users2 forceInsertExpr (users.length + 1, "admin")
)
この場合、AutoIncなカラムは 無視されない 。
更新は更新を行いたいデータを選択してから、新しいデータで置き換える事で実行される。更新時の返り値は計算された値ではなく、1つのテーブルから取得された生のカラムをそのまま返却しなくてはならない。更新に関連するメソッドは、UpdateExtensionMethodsで定義されている。
val q = for { c <- coffees if c.name === "Espresso" } yield c.price
val updateAction = q.update(10.49)
// 値を更新することなくステートメントを取得する
val sql = q.updateStatement
// compiles to SQL:
// update "COFFEES" set "PRICE" = ? where "COFFEES"."COF_NAME" = 'Espresso'
現時点では、データベースに用意された更新用の変換関数等を利用したりすることは出来ない。
通常、データベースクエリはいくつかのパラメータに依存している（IDは一致する列を取得するために用いられるなど）。パラメータ化されたQueryオブジェクトを実行の度に作ることも出来るが、これはSlickが毎度クエリをコンパイルするコストが高くついてしまう（パラメータに値を代入しない場合など特に）。パラメータ化されたクエリ関数を事前にSlick側でコンパイルする、より効率的な方法が存在する。
def userNameByIDRange(min: Rep[Int], max: Rep[Int]) =
for {
u <- users if u.id >= min && u.id < max
} yield u.first
val userNameByIDRangeCompiled = Compiled(userNameByIDRange _)
// このクエリは1度しかコンパイルされない
val namesAction1 = userNameByIDRangeCompiled(2, 5).result
val namesAction2 = userNameByIDRangeCompiled(1, 3).result
// .insert にも .update にも .delete にも使える
これは個々のカラムやカラムのrecordsをパラメータに取る全てのメソッドに対し上手く機能し、Queryオブジェクトなどを返却する。CompiledのAPIドキュメントを見て、そのサブクラスなど、コンパイルされたクエリの詳細について学んで欲しい。
ConstColumn[Long]をパラメータに取るtakeやdropを使う場合には気をつけて欲しい。クエリによって計算された他の値に取って代わられるRep[Long]と異なり、ConstColumnはリテラル値かコンパイルされたクエリのパラメータのみを要求する。これは、Slickによって実行される前までに、クエリが実際の値を知っておかなくてはならないためである。
val userPaged = Compiled((d: ConstColumn[Long], t: ConstColumn[Long]) => users.drop(d).take(t))
...
val usersAction1 = userPaged(2, 1).result
val usersAction2 = userPaged(1, 3).result
データの選択、挿入、更新、削除において、コンパイルされたクエリを用いる事ができる。Slick 1.0への後方互換用に、ParametersオブジェクトにflatMapを呼ぶ事で、コンパイルされたクエリを作成する事も可能である。大抵の場合、これはコンパイルされたクエリを1つのfor式で書くのに役立つだろう。
val userNameByID = for {
id <- Parameters[Int]
u <- users if u.id === id
} yield u.first
...
val nameAction = userNameByID(2).result.head
...
val userNameByIDRange = for {
(min, max) <- Parameters[(Int, Int)]
u <- users if u.id >= min && u.id < max
} yield u.first
val namesAction = userNameByIDRange(2, 5).result