Category: Slick

Slick3のSQLクエリを複数結合する

Slickにて、クエリを連続的に結合する方法についてのまとめ
忘備録的なものなので、用語に関してなど、間違っているところがあるかもしれないです。
ツッコミしていただけたらありがたいです。

環境

  • play2.4
  • Slick3.0
  • mysql


Slickでクエリを発行する際に、同一のWeb Requestの中でdb.runを何回も発行するとトランザクションが別になってしまうので、片方のトランザクションだけが失敗してしまって原子性が保たれないなどの場合がある。
同一トランザクションにするためにはすべてのクエリ(DBIOAction)を一つにまとめる必要がある。


データベースに対する操作はDBIOActionと呼ばれる

  • accountTable.schema.create
  • accountTable.map(_.userId === 1).result
  • accountTable += Account(1,”name”,”email)
  • (users returning users.map(_.id)) += User(None, “Stefan”, “Zeiger”)


これらはすべてDBIOActionであり、db.run( DBIOAction ) と実行できる類のものの事になります。

これらを同一のトランザクションとして処理したい場合、単純な方法としてはマニュアルに乗っているようにすればよい。
DBIO.seqの中に全部放り込めば良いので、上記のDBIOActionを例にすると以下のようになる。

Slick3.0.3 Manual
http://slick.typesafe.com/doc/3.0.0/gettingstarted.html#populating-the-database

val setup = DBIO.seq(
  	accountTable.schema.create,
	accountTable.map(_.id == 1).result,
	accountTable += Account(1,"name","email),
	(accountTable returning accountTable .map(_.id)) += Account(1,"name","email)
)
val setupFuture = db.run(setup.transactionally)

これですべてのクエリが実行されるわけだが、クエリの結果は取得することができない。ひとつとして。

さらに実際はSelectした結果をInsertしたい場合もあるだろうし、AutoincrementされたIdを元に他のレコードをUpdateしたい場合など、
クエリの結果を取得したうえで次のクエリを行うには次のように考えれば良い。


参考にしたいのがマニュアルにあるこちらのサンプル
for yield 文を使ってうまくクエリの結合をしている。

Slick3.0.3 Manual
http://slick.typesafe.com/doc/3.0.0/dbio.html#transactions-and-pinned-sessions

val a = (for {
  ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
  _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally

val f: Future[Unit] = db.run(a)

もう少し単純でわかりやすい形のサンプルにしてみる(これは実際に役に立つ例ではないが)

val coffees = TableQuery[CoffeeTable]
val a = (
  for {
    gotIds <- coffees.filter(_.type === "ESPRESSO").map(_.id).result
    gotCoffees <- coffees.filter(_.id === gotId.head).result
} yield (gotCoffees))

val f: Future[Seq[Coffee]] = db.run(a.transactionally)

このサンプルではまず3行目でtypeが“ESPRESSO”のレコードのIDをすべて取得する。そして取得されたすべてのidが左に示したgotIdsにSeqとして代入される。
次4行目でgotIdsと一致するレコードをCoffeeテーブルから取得する。それが左に示したgotCoffeesに代入される。(ここではheadを使い取得したgotIdの最初の1つと一致するものを取得している)
そして最後 yield に指定されたgotCoffeesが最終的にFutureから取得できる結果となり、Seq[Coffee]である。
もしyield(godIds)とすれば最後に取得できる値はSeq[Int] になる。
両方欲しければ yield((gotCoffese, gotIds)) とすれば両方の値が(Seq[Coffee], Seq[Int])とタプルで取得できるようになる。


まとめると
forの中の各行にクエリを記述し、取得される値を左辺の変数に代入、
次の行では同様にクエリを記述でき、前の行で取得した変数のリストを利用することもできる。
これをいくつも繰り返すことができるような形になっている。
そして、取得したい結果を左辺の変数より、yield に記述する。

そして最後にdb.runするまえにtransactionallyを指定しておけばコミット処理やロールバックを自動で行ってくれるため、原子性が守られることになる。


もう一つ少し実用的な応用のサンプルも一つ挙げてきます。
ESPRESSOの種類のCoffeのIDを取得し、時間とともにオーダーに加えるというサンプルです。
取得したIDを元にOrderインスタンスを作成しinsertするようなことも一つのトランザクションで可能です。

val coffees = TableQuery[CoffeeTable]
val orders= TableQuery[OrdersTable]
val a = (
  for {
    gotIds <- coffees.filter(_.type=== "ESPRESSO").map(_.id).result
    _ <- orders += Order(gotIds.head, "order time")
} yield ())

val f: Future[Seq[Unit]] = db.run(a.transactionally)

ところで、なぜこれらのクエリーがfor yield文で記述されるのか気になった方もいると思います。
for yieldはscalaではflatMap関数に変換されて処理されるところがポイントになります。

このfor yield文をflatMap関数で表していくと次のように表すことができます。

coffees.filter(_.type=== "ESPRESSO").map(_.id).result.flatMap( gotIds:Seq[Int] =>
    orders += Order(gotIds.head, "order time").map( _ => ... )

いままでのクエリがどのように組み立てられているか、なんとなくわかったのではないでしょうか。


DBIOAction.flatMap(result1:Any =>
  DBIOAction.flatMap(result2:Any => 
    DBIOAction.flatMap(result3:Any =>
      DBIOAction.flatMap(result4:Any => 

こんなかんじでクエリが作られていくようです。
この辺の仕組みがわかると、ようやくSlick3と仲良くなれたような気がしました。



この記事のトラックバック用URL - http://mashi.exciton.jp/archives/189/trackback


SlickにてAutoincrementされたIDを取得して別テーブルにInsert

SlickでAutoincrementされたIDを追加時に取得し、そのIDを使用して他のテーブルにもレコードを追加する方法
例えばAccountにユーザーを追加、IDはautoincrementされるが、そのIDを利用して他のテーブルにもIDに結びついたレコードを追加したいような場合。
の方法。


環境は
  • play2.4
  • Slick3.0
  • mysql


普通にレコード追加して、そのIDを取得する方法としてはオフィシャルドキュメントに載ってるとおり

Slick3.0.3 Manual
http://slick.typesafe.com/doc/3.0.3/queries.html#inserting

val userWithId =
  (users returning users.map(_.id)
         into ((user,id) => user.copy(id=Some(id)))
  ) += User(None, "Stefan", "Zeiger")

db.action(userWithId.transactionally)

問題ははこの取得したIDを使って他のレコードを追加すること。
トランザクションが別になっても良いのであれば難しいことは何もないのだが、
同一のトランザクションで行うには次のようにすればよい。

def(account: models.Account, another: models.Another){
  val action = (
    for {
      newId <- (accountTable returning accountTable.map(_.userId) )+=account
      _ <- anotherTable += another.copy(userId = newId)
  } yield newId )
  db.action(action.transactionally)
}

4行目でレコードを追加して、autoincrementされたnewIdを確保
5行目でanotherモデルのuserIdプロパティにnewIdをセット(関数型プログラミングならコピーで。)してInsert

これで同一トランザクションで処理できる
ほかの処理を追加したい場合はforの中に追加していく形でできる



この記事のトラックバック用URL - http://mashi.exciton.jp/archives/182/trackback


Slickで削除済みテーブルを使った削除を実装

Slickにて、レコード削除の際、削除したレコードを削除済みテーブルに追加する方法と
それを復活させる方法、
また管理の際に、通常テーブルと削除済みデーブルを結合して取得する方法について。

環境は

  • play2.4
  • Slick3.0
  • mysql

SQLのレコードを削除したいけど、削除済みのデータも残しておきたい。
そんなことも度々あると思うんですが、論理削除として削除フラグをテーブルに作るのも後々管理がしにくい、、ということで
通常テーブルと削除済みテーブルを2つ作ってしまえばいいじゃない!
そして、通常の処理では通常テーブルだけを参照して、
管理画面なんかで見る時だ通常テーブルと削除済みテーブルをUnionするという方法をとっています。
SQLで書くなら

通常の処理

  SELECT * From Account;

管理画面での処理

 (select *, false as Exist from Account) 
   union
 ( select *, true as Exist from AccountDelete)
   order by userId Desc;

こんな感じにしたいわけです。


ここから本題。
まず通常テーブルと削除済みテーブルの定義は以下のとおりで、通常テーブルについているO.AutoInc以外はほとんど同じです。

//通常テーブル
  val table = TableQuery[AccountTable]
  class AccountTable(tag: Tag) extends Table[Account](tag, "Account") {
    def userId = column[Int]("userId", O.PrimaryKey, O.AutoInc)
    def email = column[String]("email")
    def password = column[String]("password")

    def * = (userId.?, email, password) <>(Account.tupled, Account.unapply _)
  }

//削除済みテーブル
  val deleteTable = TableQuery[AccountDeleteTable]
  class AccountDeleteTable(tag: Tag) extends Table[Account](tag, "AccountDelete") {
    def userId = column[Int]("userId", O.PrimaryKey)
    def email = column[String]("email")
    def password = column[String]("password")

    def * = (userId.?, username, email, password) <>(Account.tupled, Account.unapply _)
  }

まず削除処理のSlick3での書き方

  def delete(userId: Int): Future[Unit] = {
    val act = {
      for {
        rows <- table.filter(_.userId === userId).take(1).result
        _ <- DBIO.seq(deleteTable += rows.head,
                      table.filter(_.userId === userId).delete)
      } yield()
    }
    db.run(act.transactionally)
  }

userIdに相当するレコードを探してきて、それを削除済みテーブルに追加して、通常テーブルから削除しています。
単純に3つのクリエをそれぞれに実行してもできますが、
一連の処理を一つのトランザクションで行いたいのでこういう形になります。


レコード復活の場合もほぼ同様ですが、UserIdをそのまま戻したいので、insertOrUpdateを利用しています。

  def restore(userId: Int): Future[Unit] = {
    val act = {
      for {
        rows <- deleteTable.filter(_.userId === userId).take(1).result
        _ <-DBIO.seq( table.insertOrUpdate(rows.head),
                         deleteTable.filter(_.userId === userId).delete)
      } yield ()
    }
    db.run(act.transactionally)
  }

通常の処理ではAccountTableのみを見ればよいですが、管理画面などでは削除されたものとそうでないものをまとめて、しかもID順に並び替えて閲覧したかったので、次のようにしました。
まず二つのテーブルを存在フラグのカラムを追加したうえで結合して、userId順に並び替えています。

  def selectAllWithDelete(): Future[Seq[AccountWithDeleteFrag]] ={
    val tbl = table.map(x => (x.userId.?, x.username, x.email, x.password, LiteralColumn[Boolean](true)) )
    val tblDelete = deleteTable.map(x => (x.userId.?, x.username, x.email, x.password, LiteralColumn[Boolean](false)))
    val union = (tbl union tblDelete).sortBy(_._1.desc)
    db.run(union.result).map(_.map(AccountWithDeleteFrag.tupled(_)))
  }
LiteralColumn[Boolean](true))

を使って、テーブルの定義に対し値を設定したカラムを1つ追加したレコードを取得しています。
それぞれののテーブルからの取得クエリを union して sortby しています。名前がないので、1番目を指定。
取得できるレコードは元のAccountレコードとは構造が変わってきますのでタプルのまま取得してもいいですし、
上記のように新しくbooleanのプロパティを増やしたcase class を作っても良いと思います



この記事のトラックバック用URL - http://mashi.exciton.jp/archives/177/trackback