型安全なクエリ構築

この章では、LDBCで構築したテーブル定義を使用して、型安全にクエリを構築するための方法について説明します。

プロジェクトに以下の依存関係を設定する必要があります。

libraryDependencies += "io.github.takapi327" %% "ldbc-query-builder" % "0.3.0-beta8"

LDBCでのテーブル定義方法をまだ読んでいない場合は、テーブル定義の章を先に読むことをオススメしましす。

以下のコード例では、以下のimportを想定しています。

import cats.effect.IO
import ldbc.core.*
import ldbc.query.builder.TableQuery

LDBCではTableQueryにテーブル定義を渡すことで型安全なクエリ構築を行います。

case class User(
  id: Long,
  name: String,
  age: Option[Int],
)

val table = Table[User]("user")(
  column("id", BIGINT, AUTO_INCREMENT, PRIMARY_KEY),
  column("name", VARCHAR(255)),
  column("age", INT.UNSIGNED.DEFAULT(None)),
)

val userQuery = TableQuery[User](table)

SELECT

型安全にSELECT文を構築する方法はTableQueryが提供するselectメソッドを使用することです。LDBCではプレーンなクエリに似せて実装されているため直感的にクエリ構築が行えます。またどのようなクエリが構築されているかも一目でわかるような作りになっています。

特定のカラムのみ取得を行うSELECT文を構築するにはselectメソッドで取得したいカラムを指定するだけです。

val select = userQuery.select(_.id)

select.statement === "SELECT `id` FROM user"

複数のカラムを指定する場合はselectメソッドで取得したいカラムを指定して指定したカラムのタプルを返すだけです。

val select = userQuery.select(user => (user.id, user.name))

select.statement === "SELECT `id`, `name` FROM user"

全てのカラムを指定したい場合はTableQueryが提供するselectAllメソッドを使用することで構築できます。

val select = userQuery.selectAll

select.statement === "SELECT `id`, `name`, `age` FROM user"

特定のカラムの数を取得したい場合は、指定したカラムでcountを使用することで構築できます。　

val select = userQuery.select(_.id.count)

select.statement === "SELECT COUNT(id) FROM user"

WHERE

クエリに型安全にWhere条件を設定する方法はwhereメソッドを使用することです。

val select = userQuery.select(_.id).where(_.name === "Test")

select.statement === "SELECT `id` FROM user WHERE name = ?"

whereメソッドで使用できる条件の一覧は以下です。

GROUP BY/Having

クエリに型安全にGROUP BY句を設定する方法はgroupByメソッドを使用することです。

groupByを使用することでselectでデータを取得する時に指定したカラム名の値を基準にグループ化することができます。

val select = userQuery.select(user => (user.id, user.name, user.age)).groupBy(_._3)

select.statement === "SELECT `id`, `name`, `age` FROM user GROUP BY age"

グループ化するとselectで取得できるデータの数はグループの数だけとなります。そこでグループ化を行った場合には、グループ化に指定したカラムの値や、用意された関数を使ってカラムの値をグループ単位で集計した結果などを取得することができます。

havingを使用するとgroupByによってグループ化されて取得したデータに関して、取得する条件を設定することができます。

val select = userQuery.select(user => (user.id, user.name, user.age)).groupBy(_._3).having(_._3 > 20)

select.statement === "SELECT `id`, `name`, `age` FROM user GROUP BY age HAVING age > ?"

ORDER BY

クエリに型安全にORDER BY句を設定する方法はorderByメソッドを使用することです。

orderByを使うことでselectでデータを取得する時に指定したカラムの値を対象にソートした結果を取得することができます。

val select = userQuery.select(user => (user.id, user.name, user.age)).orderBy(_.age)

select.statement === "SELECT `id`, `name`, `age` FROM user ORDER BY age"

昇順/降順を指定したい場合は、それぞれカラムに対して asc/descを呼び出すだけです。

val desc = userQuery.select(user => (user.id, user.name, user.age)).orderBy(_.age.desc)

desc.statement === "SELECT `id`, `name`, `age` FROM user ORDER BY age DESC"

val asc = userQuery.select(user => (user.id, user.name, user.age)).orderBy(_.age.asc)

asc.statement === "SELECT `id`, `name`, `age` FROM user ORDER BY age ASC"

LIMIT/OFFSET

クエリに型安全にLIMIT句とOFFSET句を設定する方法はlimit/offsetメソッドを使用することです。

limitを設定するとselectを実行した時に取得するデータの行数の上限を設定することができ、offsetを設定すると何番目からのデータを取得するのかを指定することができます。

val select = userQuery.select(user => (user.id, user.name, user.age)).limit(100).offset(50)

select.statement === "SELECT `id`, `name`, `age` FROM user LIMIT ? OFFSET ?"

JOIN/LEFT JOIN/RIGHT JOIN

クエリに型安全にJoinを設定する方法はjoin/leftJoin/rightJoinメソッドを使用することです。

Joinでは以下定義をサンプルとして使用します。

case class Country(code: String, name: String)
object Country:
  val table = Table[Country]("country")(
    column("code", CHAR(3), PRIMARY_KEY),
    column("name", VARCHAR(255))
  )

case class City(id: Long, name: String, countryCode: String)
object City:
  val table = Table[City]("city")(
    column("id", BIGINT, AUTO_INCREMENT, PRIMARY_KEY),
    column("name", VARCHAR(255)),
    column("country_code", CHAR(3))
  )

case class CountryLanguage(
  countryCode: String,
  language:    String
)
object CountryLanguage:
  val table: Table[CountryLanguage] = Table[CountryLanguage]("country_language")(
    column("country_code", CHAR(3)),
    column("language", CHAR(30))
  )

val countryQuery = TableQuery[Country](Country.table)
val cityQuery = TableQuery[City](City.table)
val countryLanguageQuery = TableQuery[CountryLanguage](CountryLanguage.table)

まずシンプルなJoinを行いたい場合は、joinを使用します。 joinの第一引数には結合したいテーブルを渡し、第二引数では結合元のテーブルと結合したいテーブルのカラムで比較を行う関数を渡します。これはJoinにおいてのON句に該当します。

Join後のselectは2つのテーブルからカラムを指定することになります。

val join = countryQuery.join(cityQuery)((country, city) => country.code === city.countryCode)
  .select((country, city) => (country.name, city.name))

join.statement = "SELECT country.`name`, city.`name` FROM country JOIN city ON country.code = city.country_code"

次に左外部結合であるLeft Joinを行いたい場合は、leftJoinを使用します。 joinがleftJoinに変わっただけで実装自体はシンプルなJoinの時と同じになります。

val leftJoin = countryQuery.leftJoin(cityQuery)((country, city) => country.code === city.countryCode)
  .select((country, city) => (country.name, city.name))

join.statement = "SELECT country.`name`, city.`name` FROM country LEFT JOIN city ON country.code = city.country_code"

シンプルなJoinとの違いはleftJoinを使用した場合、結合を行うテーブルから取得するレコードはNULLになる可能性があるということです。

そのためLDBCではleftJoinに渡されたテーブルから取得するカラムのレコードは全てOption型になります。

val leftJoin = countryQuery.leftJoin(cityQuery)((country, city) => country.code === city.countryCode)
  .select((country, city) => (country.name, city.name)) // (String, Option[String])

次に右外部結合であるRight Joinを行いたい場合は、rightJoinを使用します。 こちらもjoinがrightJoinに変わっただけで実装自体はシンプルなJoinの時と同じになります。

val rightJoin = countryQuery.rightJoin(cityQuery)((country, city) => country.code === city.countryCode)
  .select((country, city) => (country.name, city.name))

join.statement = "SELECT country.`name`, city.`name` FROM country RIGHT JOIN city ON country.code = city.country_code"

シンプルなJoinとの違いはrightJoinを使用した場合、結合元のテーブルから取得するレコードはNULLになる可能性があるということです。

そのためLDBCではrightJoinを使用した結合元のテーブルから取得するカラムのレコードは全てOption型になります。

val rightJoin = countryQuery.rightJoin(cityQuery)((country, city) => country.code === city.countryCode)
  .select((country, city) => (country.name, city.name)) // (Option[String], String)

複数のJoinを行いたい場合は、メソッドチェーンで任意のJoinメソッドを呼ぶことで実現することができます。

val join = 
  (countryQuery join cityQuery)((country, city) => country.code === city.countryCode)
    .rightJoin(countryLanguageQuery)((_, city, countryLanguage) => city.countryCode === countryLanguage.countryCode)
    .select((country, city, countryLanguage) => (country.name, city.name, countryLanguage.language)) // (Option[String], Option[String], String)]

join.statement =
  """
    |SELECT
    |  country.`name`, 
    |  city.`name`,
    |  country_language.`language`
    |FROM country
    |JOIN city ON country.code = city.country_code
    |RIGHT JOIN country_language ON city.country_code = country_language.country_code
    |""".stripMargin

複数のJoinを行っている状態でrightJoinでの結合を行うと、今までの結合が何であったかにかかわらず直前まで結合していたテーブルから取得するレコードは全てNULL許容なアクセスとなることに注意してください。

Custom Data Type

前章でユーザー独自の型もしくはサポートされていない型を使用するためにDataTypeのmappingメソッドを使用して独自の型とDataTypeのマッピングを行ないました。(参照)

LDBCはテーブル定義とデータベースへの接続処理が分離されています。 そのためデータベースからデータを取得する際にユーザー独自の型もしくはサポートされていない型に変換したい場合は、ResultSetからのデータ取得方法を独自の型もしくはサポートされていない型と紐付けてあげる必要があります。

例えばユーザー定義のEnumを文字列型とマッピングしたい場合は、以下のようになります。

enum Custom:
  case ...

given ResultSetReader[IO, Custom] =
  ResultSetReader.mapping[IO, str, Custom](str => Custom.valueOf(str))

※ この処理は将来のバージョンでDataTypeのマッピングと統合される可能性があります。

INSERT

型安全にINSERT文を構築する方法はTableQueryが提供する以下のメソッドを使用することです。

• insert
• insertInto
• +=
• ++=

insert

insertメソッドには挿入するデータのタプルを渡します。タプルはモデルと同じプロパティの数と型である必要があります。また、挿入されるデータの順番はモデルのプロパティおよびテーブルのカラムと同じ順番である必要があります。

val insert = userQuery.insert((1L, "name", None))

insert.statement === "INSERT INTO user (`id`, `name`, `age`) VALUES(?, ?, ?)"

複数のデータを挿入したい場合は、insertメソッドに複数のタプルを渡すことで構築できます。

val insert = userQuery.insert((1L, "name", None), (2L, "name", None))

insert.statement === "INSERT INTO user (`id`, `name`, `age`) VALUES(?, ?, ?), (?, ?, ?)"

insertInto

insertメソッドはテーブルが持つ全てのカラムにデータ挿入を行いますが、特定のカラムに対してのみデータを挿入したい場合はinsertIntoメソッドを使用します。

これはAutoIncrementやDefault値を持つカラムへのデータ挿入を除外したい場合などに使用できます。

val insert = userQuery.insertInto(user => (user.name, user.age)).values(("name", None))

insert.statement === "INSERT INTO user (`name`, `age`) VALUES(?, ?)"

複数のデータを挿入したい場合は、valuesにタプルの配列を渡すことで構築できます。

val insert = userQuery.insertInto(user => (user.name, user.age)).values(List(("name", None), ("name", Some(20))))

insert.statement === "INSERT INTO user (`name`, `age`) VALUES(?, ?), (?, ?)"

+=

+=メソッドを使用することでモデルを使用してinsert文を構築することができます。モデルを使用する場合は全てのカラムにデータを挿入してしまうことに注意してください。

val insert = userQuery += User(1L, "name", None)

insert.statement === "INSERT INTO user (`id`, `name`, `age`) VALUES(?, ?, ?)"

++=

モデルを使用して複数のデータを挿入したい場合は++=メソッドを使用します。

val insert = userQuery ++= List(User(1L, "name", None), User(2L, "name", None))

insert.statement === "INSERT INTO user (`id`, `name`, `age`) VALUES(?, ?, ?), (?, ?, ?)"

ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE 句を指定し行を挿入すると、UNIQUEインデックスまたはPRIMARY KEYで値が重複する場合、古い行のUPDATEが発生します。

LDBCでこの処理を実現する方法は2種類あり、insertOrUpdate{s}を使用するか、Insertに対してonDuplicateKeyUpdateを使用することです。

val insert = userQuery.insertOrUpdate((1L, "name", None))

insert.statement === "INSERT INTO user (`id`, `name`, `age`) VALUES(?, ?, ?) AS new_user ON DUPLICATE KEY UPDATE `id` = new_user.`id`, `name` = new_user.`name`, `age` = new_user.`age`"

insertOrUpdate{s}を使用した場合、全てのカラムが更新対象となることに注意してください。重複する値があり特定のカラムのみを更新したい場合は、onDuplicateKeyUpdateを使用して更新したいカラムのみを指定するようにしてください。

val insert = userQuery.insert((1L, "name", None)).onDuplicateKeyUpdate(v => (v.name, v.age))

insert.statement === "INSERT INTO user (`id`, `name`, `age`) VALUES(?, ?, ?) AS new_user ON DUPLICATE KEY UPDATE `name` = new_user.`name`, `age` = new_user.`age`"

UPDATE

型安全にUPDATE文を構築する方法はTableQueryが提供するupdateメソッドを使用することです。

updateメソッドの第1引数にはテーブルのカラム名ではなくモデルのプロパティ名を指定し、第2引数に更新したい値を渡します。第2引数に渡す値の型は第1引数で指定したプロパティの型と同じである必要があります。

val update = userQuery.update("name", "update name")

update.statement === "UPDATE user SET name = ?"

第1引数に存在しないプロパティ名を指定した場合コンパイルエラーとなります。

val update = userQuery.update("hoge", "update name") // Compile error

複数のカラムを更新したい場合はsetメソッドを使用します。

val update = userQuery.update("name", "update name").set("age", Some(20))

update.statement === "UPDATE user SET name = ?, age = ?"

setメソッドには条件に応じてクエリを生成させないようにすることもできます。

val update = userQuery.update("name", "update name").set("age", Some(20), false)

update.statement === "UPDATE user SET name = ?"

モデルを使用してupdate文を構築することもできます。モデルを使用する場合は全てのカラムを更新してしまうことに注意してください。

val update = userQuery.update(User(1L, "update name", None))

update.statement === "UPDATE user SET id = ?, name = ?, age = ?"

WHERE

whereメソッドを使用することでupdate文にもWhere条件を設定することができます。

val update = userQuery.update("name", "update name").set("age", Some(20)).where(_.id === 1)

update.statement === "UPDATE user SET name = ?, age = ? WHERE id = ?"

whereメソッドで使用できる条件はSelect文のwhere項目を参照してください。

DELETE

型安全にDELETE文を構築する方法はTableQueryが提供するdeleteメソッドを使用することです。

val delete = userQuery.delete

delete.statement === "DELETE FROM user"

WHERE

whereメソッドを使用することでdelete文にもWhere条件を設定することができます。

val delete = userQuery.delete.where(_.id === 1)

delete.statement === "DELETE FROM user WHERE id = ?"

whereメソッドで使用できる条件はSelect文のwhere項目を参照してください。

DDL

型安全にDDLを構築する方法はTableQueryが提供する以下のメソッドを使用することです。

• createTable
• dropTable
• truncateTable

spec2を使用している場合は以下のようにしてテストの前後にDDLを実行することができます。

import cats.effect.IO
import cats.effect.unsafe.implicits.global

import org.specs2.mutable.Specification
import org.specs2.specification.core.Fragments
import org.specs2.specification.BeforeAfterEach

object Test extends Specification, BeforeAfterEach:

  override def before: Fragments =
    step((tableQuery.createTable.update.autoCommit(dataSource) >> IO.println("Complete create table")).unsafeRunSync())

  override def after: Fragments =
    step((tableQuery.dropTable.update.autoCommit(dataSource) >> IO.println("Complete drop table")).unsafeRunSync())