参考： Scala でSlick からSQLite に接続してみた
上記記事の Slick のバージョンが 1.0.0、現在最新版が 2.0.2 で仕様がかなり変わっています。
ここでは、Slick 2.0.2 で SQLite に接続して基本的なクエリを流してみます。
やってることは Getting Start（日本語訳）と、Queries（日本語訳）です。
日本語訳は Slick 2.0.0 で、ここで使っている 2.0.2 より少し古いため、ところどころ違う箇所があるので注意して下さい。
基本的に文章は日本語訳を見て、コードは英語の方を見ると良いと思います。
まず、適当に SBT プロジェクトを作成します。
build.sbt ファイルに Slick と SQLite の依存関係を追加します。SLF4J は Slick が内部で使っているのでこれも追加します。
build.sbt
name := "SlickTest"
version := "0.1"
scalaVersion := "2.10.3"
libraryDependencies ++= Seq(
"com.typesafe.slick" %% "slick" % "2.0.2",
"org.slf4j" % "slf4j-nop" % "1.6.4",
"org.xerial" % "sqlite-jdbc" % "3.7.2"
)
次のような感じで Slick が使えるようになります。
SlickTestMain.scala
import scala.slick.driver.SQLiteDriver.simple._
import java.io.File
object SlickTestMain {
// database フォルダの test.sqlite3 ファイルをデータベースとして使用
val DatabasePath = "./database/test.sqlite3"
// データベースファイルが無い場合は初期化
val Initialize = !new File(DatabasePath).exists()
def main(args: Array[String]) {
println("Start")
// コーヒー提供者のスキーマ定義、テーブル名は SUPPLIERS
class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
// 例えば SUP_ID がデータベース側のカラム名、Scala 側では id という名前で参照する
def id = column[Int]("SUP_ID", O.PrimaryKey) // プライマリキー
def name = column[String]("SUP_NAME")
def street = column[String]("STREET")
def city = column[String]("CITY")
def state = column[String]("STATE")
def zip = column[String]("ZIP")
// この * はテーブルごとに必須。通常はすべてのカラムのタプルで良いが、もっと複雑にもできる
def * = (id, name, street, city, state, zip)
}
// テーブルごとにクエリ生成オブジェクトを作成、これを使ってデータベース操作を行う
val suppliers = TableQuery[Suppliers]
// コーヒーのスキーマ定義、テーブル名は COFFEES
class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
def name = column[String]("COF_NAME", O.PrimaryKey) // プライマリキー
def supID = column[Int]("SUP_ID")
def price = column[Double]("PRICE")
def sales = column[Int]("SALES")
def total = column[Int]("TOTAL")
def * = (name, supID, price, sales, total)
// 外部キーの定義
def supplier = foreignKey("SUP_ID", supID, suppliers)(_.id)
}
val coffees = TableQuery[Coffees]
// データベースを初期化する関数。テーブルを create してデータを追加
def initialize()(implicit session: Session) {
// DDL(data definition language)を使うとテーブルの create と drop を行うことができる
(suppliers.ddl ++ coffees.ddl).create
// 一件ずつデータを追加する書き方
suppliers += (101, "Acme, Inc.", "99 Market Street", "Groundsville","CA","95199")
suppliers += (49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460")
suppliers += (150, "The High Ground", "100 Coffee Lane", "Meadows", "CA","93966")
// まとめてデータを追加する書き方
coffees ++= Seq(
("Colombian", 101, 7.99, 0, 0),
("French_Roast", 49, 8.99, 0, 0),
("Espresso", 150, 9.99, 0, 0),
("Colombian_Decaf", 101, 8.99, 0, 0),
("French_Roast_Decaf", 49, 9.99, 0, 0))
}
// ここでデータベースに接続。withTransaction を使うと auto commit を false にできる
val db = Database.forURL("jdbc:sqlite:" + DatabasePath, driver = "org.sqlite.JDBC")
db withSession { implicit session =>
// データベースファイルが無い場合は初期化
if (Initialize) {
initialize()
}
// DB の内容表示。Collection と同じように扱える！
coffees foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("----")
// この書き方だと DB 内部で型変換が行われる
val query1 = for (c <- coffees)
yield LiteralColumn(" ") ++ c.name ++ "\t" ++ c.supID.asColumnOf[String] ++
"\t" ++ c.price.asColumnOf[String] ++ "\t" ++ c.sales.asColumnOf[String] ++
"\t" ++ c.total.asColumnOf[String]
query1 foreach println
println("----")
// フィルタ & テーブル結合
val query2 = for {
c <- coffees if c.price < 9.0
s <- suppliers if s.id === c.supID
} yield (c.name, s.name)
query2 foreach {
case (coffeeName, supplierName) =>
println(" " + coffeeName + "\t" + supplierName)
}
println("----")
// テーブル結合は外部キーを使って行うことができる
val query3 = for {
c <- coffees if c.price < 9.0
s <- c.supplier
} yield (c.name, s.name)
query3 foreach {
case (coffeeName, supplierName) =>
println(" " + coffeeName + "\t" + supplierName)
}
println("---- filter ----")
// フィルタ。イコールには === を使う(is も使える)
val query4 = coffees.filter(_.supID === 101)
query4 foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("---- drop & take ----")
// LIMIT, OFFSET は drop, take でできる。Collection ぽい！
val query5 = coffees.drop(1).take(3)
query5 foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("---- sort ----")
// ソート
val query6 = coffees.sortBy(_.name.desc.nullsFirst)
query6 foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("---- implicit cross join ----")
val implicitCrossJoin = for {
c <- coffees
s <- suppliers
} yield (c.name, s.name)
implicitCrossJoin foreach {
case (coffeeName, supplierName) =>
println(" " + coffeeName + "\t" + supplierName)
}
println("---- implicit inner join ----")
val implicitInnerJoin = for {
c <- coffees
s <- suppliers if c.supID === s.id
} yield (c.name, s.name)
implicitInnerJoin foreach {
case (coffeeName, supplierName) =>
println(" " + coffeeName + "\t" + supplierName)
}
println("---- explicit cross join ----")
val explicitCrossJoin = for {
(c, s) <- coffees innerJoin suppliers
} yield (c.name, s.name)
explicitCrossJoin foreach {
case (coffeeName, supplierName) =>
println(" " + coffeeName + "\t" + supplierName)
}
println("---- explicit inner join ----")
val explicitInnerJoin = for {
(c, s) <- coffees innerJoin suppliers on (_.supID === _.id)
} yield (c.name, s.name)
explicitInnerJoin foreach {
case (coffeeName, supplierName) =>
println(" " + coffeeName + "\t" + supplierName)
}
println("---- explicit left outer join ----")
// s.name の あとに ? が加わっているのに注意。Option にするため。
val explicitLeftOuterJoin = for {
(c, s) <- coffees leftJoin suppliers on (_.supID === _.id)
} yield (c.name, s.name.?)
explicitLeftOuterJoin foreach {
case (coffeeName, Some(supplierName)) =>
println(" " + coffeeName + "\t" + supplierName)
case (coffeeName, None) =>
println(" " + coffeeName + "\t" + "<No Supplier>")
}
// SQLite は right outer join と full outer join をサポートしてない
// [SQLITE_ERROR] SQL error or missing database (RIGHT and FULL OUTER JOINs are not currently supported)
// SQLite は row number に対応していないので、zip 系のクエリは使えない
// SQLite does not support row numbers
println("---- union ----")
val query7 = coffees.filter(_.price < 8.0)
val query8 = coffees.filter(_.price > 9.0)
// 和集合
val unionQuery = query7 union query8
unionQuery foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("---- union all ----")
// ++ は重複を除去しない和集合
val unionAllQuery = query7 ++ query8
unionAllQuery foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("---- aggregation ----")
val query9 = coffees.map(_.price)
val min = query9.min
val max = query9.max
val sum = query9.sum
val avg = query9.avg
println(" " + "min = " + min.run.get)
println(" " + "max = " + max.run.get)
println(" " + "sum = " + sum.run.get)
println(" " + "avg = " + avg.run.get)
val length = query9.length
val exists = query9.exists
println(" " + "length = " + length.run)
println(" " + "exists = " + exists.run)
println("---- group by ----")
// グルーピング
val query10 = (for {
c <- coffees
s <- c.supplier
} yield (c, s)).groupBy(_._1.supID)
val query11 = query10.map {
case (supID, css) => // css は c と s のタプル
(supID, css.length, css.map(_._1.price).avg)
}
query11 foreach {
case (supID, length, avg) =>
println(" " + supID + "\t" + length + "\t" + avg)
}
println("---- insert ----")
coffees += ("Hogehoge", 101, 7.99, 0, 0)
coffees ++= Seq(
("Fugafuga", 49, 8.99, 0, 0),
("Piyopiyo", 150, 9.99, 0, 0))
coffees foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("---- delete ----")
val query12 = coffees.filter(_.name inSetBind Seq("Hogehoge", "Fugafuga","Piyopiyo"))
query12.delete
coffees foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
println("---- update ----")
// update は単一のテーブルからカラムを選ぶ（複数可）
val query13 = for {
c <- coffees if c.name === "Espresso"
} yield c.price
query13.update(10.49)
coffees foreach {
case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" +total)
}
query13.update(9.99)
}
println("End")
}
}
出力
Start
Colombian 101 7.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
French_Roast_Decaf 49 9.99 0 0
----
Colombian 101 7.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
French_Roast_Decaf 49 9.99 0 0
----
Colombian Acme, Inc.
French_Roast Superior Coffee
Colombian_Decaf Acme, Inc.
----
Colombian Acme, Inc.
French_Roast Superior Coffee
Colombian_Decaf Acme, Inc.
---- filter ----
Colombian 101 7.99 0 0
Colombian_Decaf 101 8.99 0 0
---- drop & take ----
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
---- sort ----
French_Roast_Decaf 49 9.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
Colombian 101 7.99 0 0
---- implicit cross join ----
Colombian hoge
Colombian Superior Coffee
Colombian Acme, Inc.
Colombian The High Ground
French_Roast hoge
French_Roast Superior Coffee
French_Roast Acme, Inc.
French_Roast The High Ground
Espresso hoge
Espresso Superior Coffee
Espresso Acme, Inc.
Espresso The High Ground
Colombian_Decaf hoge
Colombian_Decaf Superior Coffee
Colombian_Decaf Acme, Inc.
Colombian_Decaf The High Ground
French_Roast_Decaf hoge
French_Roast_Decaf Superior Coffee
French_Roast_Decaf Acme, Inc.
French_Roast_Decaf The High Ground
---- implicit inner join ----
Colombian Acme, Inc.
French_Roast Superior Coffee
Espresso The High Ground
Colombian_Decaf Acme, Inc.
French_Roast_Decaf Superior Coffee
---- explicit cross join ----
Colombian hoge
Colombian Superior Coffee
Colombian Acme, Inc.
Colombian The High Ground
French_Roast hoge
French_Roast Superior Coffee
French_Roast Acme, Inc.
French_Roast The High Ground
Espresso hoge
Espresso Superior Coffee
Espresso Acme, Inc.
Espresso The High Ground
Colombian_Decaf hoge
Colombian_Decaf Superior Coffee
Colombian_Decaf Acme, Inc.
Colombian_Decaf The High Ground
French_Roast_Decaf hoge
French_Roast_Decaf Superior Coffee
French_Roast_Decaf Acme, Inc.
French_Roast_Decaf The High Ground
---- implicit inner join ----
Colombian Acme, Inc.
French_Roast Superior Coffee
Espresso The High Ground
Colombian_Decaf Acme, Inc.
French_Roast_Decaf Superior Coffee
---- explicit left outer join ----
Colombian Acme, Inc.
French_Roast Superior Coffee
Espresso The High Ground
Colombian_Decaf Acme, Inc.
French_Roast_Decaf Superior Coffee
---- union ----
Colombian 101 7.99 0 0
Espresso 150 9.99 0 0
French_Roast_Decaf 49 9.99 0 0
---- union all ----
Colombian 101 7.99 0 0
Espresso 150 9.99 0 0
French_Roast_Decaf 49 9.99 0 0
---- aggregation ----
min = 7.99
max = 9.99
sum = 45.95
avg = 9.190000000000001
length = 5
exists = true
---- group by ----
49 2 Some(9.49)
101 2 Some(8.49)
150 1 Some(9.99)
---- insert ----
Colombian 101 7.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
French_Roast_Decaf 49 9.99 0 0
Hogehoge 101 7.99 0 0
Fugafuga 49 8.99 0 0
Piyopiyo 150 9.99 0 0
---- delete ----
Colombian 101 7.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
French_Roast_Decaf 49 9.99 0 0
---- update ----
Colombian 101 7.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 10.49 0 0
Colombian_Decaf 101 8.99 0 0
French_Roast_Decaf 49 9.99 0 0
End