Slick 2.0 で SQLite に接続してみる

参考： [Scala でSlick からSQLite に接続してみた](http://blog.kenkov.jp/2013/04/02/slick.html)

上記記事の Slick のバージョンが 1.0.0、現在最新版が 2.0.2 で仕様がかなり変わっています。
ここでは、Slick 2.0.2 で SQLite に接続して基本的なクエリを流してみます。

やってることは [Getting Start](http://slick.typesafe.com/doc/2.0.2/gettingstarted.html)（[日本語訳](http://krrrr38.github.io/slick-doc-ja/v2.0.out/%E5%A7%8B%E3%82%81%E3%82%88%E3%81%86.html)）と、[Queries](http://slick.typesafe.com/doc/2.0.2/queries.html)（[日本語訳](http://krrrr38.github.io/slick-doc-ja/v2.0.out/Queries.html)）です。

日本語訳は 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 が使えるようになります。

```scala: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
```

### 参考サイト

- [Slick 2.0.2 公式ドキュメント(英語)](http://slick.typesafe.com/doc/2.0.2/)
- [上記の日本語訳(2.0.0なのでちょっと古い)](http://krrrr38.github.io/slick-doc-ja/v2.0.out/slick-doc-ja+2.0.html)
- [Play2 + Slickハンズオン(日本語)](https://github.com/bizreach/play2-hands-on)
- [Slickチートシート(日本語)](https://github.com/bizreach/slick-reference)