seratch's weblog in Japanese

About Scala, Java and Ruby programming in Japaense. If you need English information, go to http://blog.seratch.net/

SQLSyntaxSupport による DRY な SQL ライフ

ScalikeJDBC Scala

ScalikeJDBC Interpolation 1.4.7 から experimental 扱いで SQLSyntaxSupport という機能が追加されました。この記事ではこの機能の概要について紹介します。

https://github.com/seratch/scalikejdbc/tree/develop/scalikejdbc-interpolation

SQLInterpolation とは何か

まず ScalikeJDBC の SQLInterpolation とはどういうものかをご紹介します。なお SQLInterpolation は Scala 2.10.0 の新機能に依存していますので Scala 2.9 環境では使用できません。

まず、このようなマッピング先を用意しておいて

import scalikejdbc._

case class Group(id: Long, name: String)
case class Member(id: Long, name: String, groupId: Option[Long] = None, group: Option[Group])

object Group { 
  def apply(rs: WrappedResultSet) = new Group(rs.long("id"), rs.string("name"))
}
object Member { 
  def apply(rs: WrappedResltSet) = new Member(rs.long("id"), rs.string("name"), rs.longOpt("group_id"))
}

このように SQL からデータをマッピングします。見ての通り、シンプルなやり方で SQL の中にパラメータをバインド変数として埋めることができます。

import scalikejdbc._
import scalikejdbc.SQLInterpolation._

val id = 123
val member: Option[Member] = 
  sql"select id, name, group_id from members where id = ${id}"
    .map(rs => Member(rs)).single.apply()

バインド変数以外は SQLSyntax という型で渡せばバインド変数ではなく、そのまま SQL の一部になります（もちろん外部からの入力をそのままここに渡すのは NG です）。

val sortingType = SQLSyntax(if (desc) "desc" else "asc")
val members: List[Member] = 
  sql"select id, name, group_id from members order by id ${sortingType}"
    .map(rs => Member(rs)).list.apply()

SQL の必要なところだけバインド変数や SQLSyntax で入れ替えるというスタイルなので RDBMS の力を最大限引き出せるのが大きなメリットです。一方で、SQL を手書きする以上、カラム名を列挙したり、それを join クエリで使い回したりいったことには弱いというデメリットはあります。

object Member { 
  def apply(rs: WrappedResltSet) = new Member(rs.long("id"), rs.string("name"), rs.longOpt("group_id"))
  def withGroup(rs: WrappedResltSet) = {
    new Member(id = rs.long("m_id"), name = rs.string("m_name"), groupId = rs.longOpt("m_group_id"),
      group = rs.longOpt("g_id").map(id => Group(id, rs.string("g_name")))
  }
}

val id = 123
val memberWithGroup: Option[Member] = sql"""
  select 
    m.id as m_id, m.name as m_name, m.group_id as m_group_id,
    g.id as g_id, g.name as g_name 
  from 
    members m left join groups g on m.group_id = g.id
  where 
    m.id = ${id}
  """
  .map(rs => Member.withGroup(rs)).single.apply()

これまでは mapper-generator によるコードの自動生成によってこれをある程度サポートしてきました。

https://github.com/seratch/scalikejdbc/tree/develop/scalikejdbc-mapper-generator

しかし join クエリを簡単に書けるようにするという点でみるとやはり弱さがありました。

SQLSyntaxSupport

今回追加された SQLSyntaxSupport は ORM の一歩手前、DRY な SQL を記述するためのサポート機能です。

まず、コンパニオンオブジェクトに SQLSyntaxSupport[A] という trait を mixin します。tableName でテーブルの名前を指定し、 ResultSet からのマッピング処理を記述します。ResultName という見慣れない型が出てきますが、これについては後述します。

case class Group(id: Long, name: String)
case class Member(id: Long, name: String, groupId: Option[Long] = None, group: Option[Group])

object Group extends SQLSyntaxSupport[Group] {
  override val tableName = "groups"
  def apply(g: ResultName[Group])(rs: WrappedResultSet) = new Group(rs.long(g.id), rs.string(g.name))
}

object Member extends SQLSyntaxSupport[Member] { 
  override val tableName = "groups"
  def apply(m: ResultName[Member])(rs: WrappedResultSet) = new Member(rs.long(m.id), rs.string(m.name), rs.longOpt(m.groupId))
  def apply(m: ResultName[Member], g: ResultName[Group])(rs: WrappedResultSet) =  {
    apply(m)(rs).copy(group = g.longOpt(g.id).map(_ => Group(g)(rs)))
  }
}

これをこのように使います。

val (m, g) = (Member.syntax("m"), Group.syntax("g"))
val id = 123

val memberWithGroup: Option[Member] = sql"""
  select
    ${m.result.*}, ${g.result.*}
  from 
    ${Member.as(m)} left join ${Group.as(g)} on ${m.groupId} = ${g.id}
  where 
    ${m.id} = ${id}
  """
  .map(Member(m, g)).single.apply()

途中に ${...} で多くのものが埋まっていますが、そのまま SQL として十分理解できるのではないかと思います。このコードが実際にどのような SQL を発行するかというと、このようになります。

select 
   m.id as i_on_m, m.name as n_on_m, m.group_id as gi_on_m, g.id as i_on_g, g.name as n_on_g
from 
  memebrs m left join groups g on m.group_id = g.id
where
  m.id = ?

${m.result.*} が全カラム名に変換されていたり ${m.camelCase} から実際のカラム名の snake_case になっていたりといった変換がされていますが、どれも予想のできる変換ではないかと思います。

しかし、おそらくいくつか疑問に思われている点があるかと思いますので、順に説明します。

なぜ ${m.result.*} がどこにも定義していないカラム名の列挙に展開されるのか？

特に定義している箇所がないのに全カラム名の一覧はどうやって取得しているのかというと、これは該当のテーブルへの初回アクセス時にテーブルのメタデータから取得してキャッシュしています。カラム名の一覧は columns という Seq[String] で取得できます。

自動取得させたくない場合や複数のデータソースへのアクセスを使用する場合は、以下のようにして columns にカラム一覧を明示します。

object Group extends SQLSyntaxSupport[Group] {
  override val tableName = "groups"
  override val columns = Seq("id", "name", "group_id")
}

なぜ m.groupId という定義していないフィールドを呼び出せるのか？

どこにも定義されていない m.groupId が呼び出せるのは Scala 2.10 から追加された Type Dynamic（SIP 17） によるものです。これは簡単に言えば Ruby でいう method_missing に似た動的なフィールド、メソッド呼び出しを可能にする仕組みです。

https://docs.google.com/document/d/1XaNgZ06AR7bXJA9-jHrAiBVUwqReqG4-av6beoLaf3U/edit

フィールド名を camelCase で書くと、実際にカラム名として指定するタイミングでは snake_case に変換されます。この変換されたカラム名が columns に存在しない場合には InvalidColumnNameException という例外を throw します。

まだまだ IDE のサポートも十分でないので Type Dynamic を使いたくないという場合は #field や #column で文字列を指定することも出来ます。例えば、以下の 4 つの呼び出しは全て同じ内容となります。

m.groupId
m.field("groupId")
m.column("group_id")
m.c("group_id")

また、カラム名をそのまま DB の外に露出させたくないというケースもあるかと思います。

その場合は nameConverters を設定します。例えば「serice_cd」というカラム名を「serviceCode」として扱いたい場合は、以下のように Map で定義します。これは部分一致なので「Map("Code" -> "cd")」のように一部だけでも OK です。

case class Event(id: Long, name: String, serviceCode: Long)

object Event extends SQLSyntaxSupport[Event] {
  override val tableName = "events"
  override val columns = Seq("id" "name", "service_cd")
  override val nameConverters = Map("serviceCode" -> "service_cd")
}

m.id と m.result.id と m.resultName.id の違いとは？

「val m = Member.syntax("mm")」でつくった m オブジェクトの場合は以下のようになります。

  • m.groupId は "mm.group_id" に変換される
  • m.result.groupId は "mm.group_id as gi_on_mm" に変換される
  • m.resultName.groupId は "gi_on_mm" に変換される

Member.syntax の引数で String を指定しなかった場合は tableName がそのまま入ります。例えば m.result.groupId は「members.group_id as gi_on_members」になります。

これにより

sql"select ${m.result.id} from ${Member.as(m)} where ${m.groupId} = 1"

は

select mm.id as i_on_mm from members mm where m.group_id = 1

に変換され、結果取得は以下のように書くことができます。

val ids: List[Long] = sql"select ${m.result.id} from ${Member.as(m)} where ${m.gourpId} = 1"
  .map(rs => rs.long(m.resultName.id)).list.apply()

また m.resultName は ResultName[Member] という型を返します。これを受け取る Member オブジェクトの apply メソッドを定義すれば #map の記述が簡潔になります。

object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(m: ResultName[User])(rs: WrappedResultSet) = {
    new Member(id = rs.long(m.id), name = rs.string(m.name))
  }
}

val m = Member.syntax("m")
val members = sql"select ${m.result.*} from ${Member.as(m)}".map(Member(m)).list.apply()
// select m.id as i_on_m, m.name as n_on_m from members m

以上が SQLSyntaxSupport の機能概要です。基本的には SQLSyntax を返すだけなので、それほど学習コストがかからずに使いこなせる API になっているのではないかと思います。

one-to-x APIs

ここから先は Scala 2.9 でも使える機能ですが join クエリの結果をマッピングするための API がいくつか追加されました。SQLSyntaxSupport とこれらを組み合わせるのが有効です。これらの API を使わずに対応する場合はこれまで通り #map や #foldLeft を記述することができます。

one-to-many

one-to-many なリレーションで join クエリの結果を取得する場合、one.toMany、one.toManies が便利です。以下のようにチェーンして呼び出します。inner join の場合は「one(Group(g)).toMany(Some(Member(m)))」のように常に Some で返します。

case class Group(id: Long, name: String, members: Seq[Member] = Nil)
case class Member(id: Long, name: String)

object Group extednds SQLSyntaxSupport[Group] { /* 省略 */ }
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def opt(m: ResultName[Member])(rs: WrappedResultSet) = rs.longOpt(m.id).map(_ => Member(m)(rs))
}

val (g, m) = (Group.syntax, Member.syntax)
val groups: List[Group] = sql"""
  select 
    ${g.result.*}, ${m.result.*} 
  from 
    ${Group.as(g)} left join ${Member.as(m)} on ${g.id} = ${m.groupId} 
"""
  .one(Group(g))
  .toMany(Member.opt(m))
  .map { (group, members) => group.copy(members = members) } 
  .list.apply()

one.toManies は 5 つまで join 先を取り出すことが出来ます（= 計 6 つのテーブルを join できる）。

case class Group(id: Long, name: String, events: Seq[Event] = Nil, members: Seq[Member] = Nil)
case class Member(id: Long, name: String)
case class Eevnt(id: Long, name: String)
// companion object の定義は省略 

val (g, m, e) = (Group.syntax, Member.syntax, Event.syntax)
val groups: List[Group] = sql"""
  select 
    ${g.result.*}, ${m.result.*}, ${e.result.*}
  from 
    ${Group.as(g)} 
      left join ${Member.as(m)} on ${g.id} = ${m.groupId} 
      left join ${Event.as(e)} on ${g.id} = ${e.groupId} 
"""
  .one(Group(m))
  .toManies(
    rs => Member.opt(g)(rs), 
    rs => Event(e)(rs))
  .map { (group, members, events) => group.copy(members = members, events = events) }
  .list.apply()

one-to-one

outer join の場合は one.toOptionalOne を使います。

case class Owner(id: Long, name: String)
case class Group(id: Long, name: String, ownerId: Option[Long] = None, owner: Option[Owner] = None)
// companion object の定義は省略 

val (g, o) = (Group.syntax, Owner.syntax)
val groups: List[Group] = sql"""
  select 
    ${g.result.*}, ${o.result.*} 
  from 
    ${Group.as(g)} left join ${Owner.as(o)} on ${g.ownerId} = ${o.id} 
"""
  .one(Group(g))
  .toOptionalOne(Owner.opt(o))
  .map { (group, owner) => group.copy(owner = owner) }
  .list.apply()

inner join の場合は one.toOne があります。

case class Owner(id: Long, name: String)
case class Group(id: Long, name: String, ownerId: Long, owner: Option[Owner] = None)
// companion object の定義は省略 

val (g, o) = (Group.syntax, Owner.syntax)
val groups: List[Group] = sql"""
  select 
    ${g.result.*}, ${o.result.*} 
  from 
    ${Group.as(g)} inner join ${Owner.as(o)} on ${g.ownerId} = ${o.id} 
"""
  .one(Group(g))
  .toOne(Owner(o))
  .map { (group, owner) => group.copy(owner = Some(owner)) }
  .list.apply()

owner を Option 型にしたくなければ #map で一度に取得します。

case class Owner(id: Long, name: String) 
case class Group(id: Long, name: String, ownerId: Long, owner: Owner) 

object Group extends SQLSyntaxSupport[Group] {
  def apply(g: ResultName[Group], o: ResultName[Owner])(rs: WrappedResultSet) = new Group(
    id = rs.long(g.id), name = rs.string(g.name), ownerId = rs.long(g.ownerId),
    group = Owner(id = rs.long(o.id), name = rs.string(o.name))
  )
}

val (g, o) = (Group.syntax, Owner.syntax)
val groups: List[Group] = sql"""
  select 
    ${g.result.*}, ${o.result.*} 
  from 
    ${Group.as(g)} inner join ${Owner.as(o)} on ${g.ownerId} = ${o.id} 
"""
  .map(Group(g, o)).list.apply()

まとめ

このように自動生成できるところは SQLSyntaxSupport に任せることで、全ての SQL を手書きせずにすむようになったので、 ScalikeJDBC でも join クエリを非常に書きやすくなりました。ここでの例はシンプルでしたが、実際の DB の場合には労力を大きく削減でき、またミスも減るはずです。

「不必要に SQL から乖離することなく、しかし、記述はなるべくシンプルに」というバランスをとれたのではないかと思っています。

ぜひ試してみてください。