seratch's weblog in Japanese

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

Anorm についてのふりかえり

Scala

Twitter 上でのやりとりを読んでいて、Anorm は 2.4 から Play Framework 本体からは分離されたとはいえ Play チームがメンテし続けるであろうライブラリであることには変わりないので、機能の比較以前にそういったバックアップ体制を重視するなら Anorm を選択するという判断もあるのかなと思いました。

https://github.com/playframework/anorm

Anorm といえば Play 2.0 が出た直後の勉強会で Anorm のコードをざっと読んで短い発表をしたことがありました。当時の Scala の DB ライブラリ事情は ScalQuery、Squeryl、Lift Mapper、Querulous（MySQL のみ対応）といったあたりがメジャーどころで ScalikeJDBC は Querulous にインスパイアされた初期バージョンの段階でした。

http://www.slideshare.net/seratch/reading-anorm-20-12238243

当時の私の印象は「何かすごくいい点があるというわけではないが ScalaQuery とかに比べると使い方は単純でわかりやすそう、SQL を書いて次にどう取り出すか書くという API も直感に合っているし、いろんな場面でツールとしては使えるのかもしれないな」という感じでした。ScalikeJDBC にもその後 SQL オブジェクトの API を追加しましたが、これは見て明らかな通り Anorm に強く影響されています。

Anorm と ScalikeJDBC の比較

Anorm に影響を受けている ScalikeJDBC の機能面での優位性を挙げるなら Anorm でできることができるだけでなく interpolation に SQLSyntax として bind 変数以外の SQL の部品を安全に埋め込める機能があることが最も大きいかと思います。

http://scalikejdbc.org/documentation/sql-interpolation.html

Anorm の interpolation には同等の機能は現在も存在していません（Slick の StaticQuery は #$ で外部パラメータを何でも埋めることができるようです）。他のライブラリがこういうアプローチを真似しない理由はよくわかりませんが、ある程度こういうサポートがないと join クエリをたくさん書いたりする場合にかなりしんどいのではないかなと思います。少なくとも Scala 2.9 時代の ScalikeJDBC はそこがつらいなと自分でも感じていました。

逆に Anorm にあって ScalikeJDBC にないものを挙げるとすれば、あの Parser API かなと思います。

ドキュメントの写経

そもそもちゃんと比較したことがなかったので、思い立って Anorm のドキュメントを ScalikeJDBC で書いてみることにしました。久しぶりに Anorm のドキュメントを眺めてみましたが、2.3.x のものは同じページの中にコピペらしき重複があったりしますね。2.4.x ではそこは直っていましたが。

https://www.playframework.com/documentation/2.3.x/ScalaAnorm

https://www.playframework.com/documentation/2.4.x/ScalaAnorm

書いてみましたが... Anorm 固有の事情や制限のための例が多く、差を出しづらかったので... 途中でやめてしまいました。興味があればどなたかやってみてください。しかし、今となっては interpolation がないコードを書くのは結構つらいですね。

project/build.properties

sbt.version=0.13.7

project/plugins.sbt

addSbtPlugin("com.typesafe.sbt" % "sbt-scalariform" % "1.3.0")
scalacOptions ++= Seq("-unchecked", "-deprecation", "-feature")

build.sbt

scalaVersion := "2.11.5"
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"       % "2.2.4",
  "com.h2database"  %  "h2"                % "1.4.185",
  "ch.qos.logback"  %  "logback-classic"   % "1.1.2"
)
scalariformSettings

src/main/scala/Example.scala

import scalikejdbc._

object Example extends App {

  // initialize JDBC driver & connection pool
  Class.forName("org.h2.Driver")
  ConnectionPool.singleton("jdbc:h2:mem:hello;MODE=PostgreSQL", "user", "pass")
  implicit val session = AutoSession

  sql"create table City (id serial not null primary key, name varchar(100), country varchar(100))".execute.apply()
  sql"create table Country (Code varchar(3) not null primary key, Name varchar(100), Population bigint)".execute.apply()
  sql"create table CountryLanguage (id serial not null primary key, Language varchar(100), CountryCode varchar(3) references Country(Code))".execute.apply()
  sql"create table prod (id varchar(10) not null primary key, name varchar(100), price float)".execute.apply()
  sql"create table tbl (str_arr array)".execute.apply()
  sql"create table item (id varchar(10) not null primary key, last_modified timestamp)".execute.apply()
  sql"create table books (title varchar(100), author varchar(100))".execute.apply()
  sql"create table test (id varchar(10) not null primary key, cat varchar(10), a varchar(10), b varchar(10), c varchar(10), colA varchar(10), colB varchar(10))".execute.apply()

  // ----------------------------------------------
  // Anorm Documentation Examples with ScalikeJDBC
  // https://www.playframework.com/documentation/2.3.x/ScalaAnorm
  // ----------------------------------------------

  {
    /*
import anorm._
import play.api.db.DB
DB.withConnection { implicit c =>
  val result: Boolean = SQL("Select 1").execute()
}
     */
    val result: Boolean = DB.autoCommit { implicit s =>
      SQL("select 1").execute.apply()
    }
  }

  {
    /*
val result: Int = SQL("delete from City where id = 99").executeUpdate()
     */
    val result: Int = SQL("delete from City where id = 99").update.apply()
  }

  {
    /*
val id: Option[Long] =
  SQL("insert into City(name, country) values ({name}, {country})")
  .on('name -> "Cambridge", 'country -> "New Zealand").executeInsert()
     */
    val id: Long =
      SQL("insert into City(name, country) values ({name}, {country})")
        .bindByName('name -> "Cambridge", 'country -> "New Zealand")
        .updateAndReturnGeneratedKey
        .apply()
  }

  {
    /*
import anorm.SqlParser.str
val id: List[String] =
  SQL("insert into City(name, country) values ({name}, {country})")
  .on('name -> "Cambridge", 'country -> "New Zealand")
  .executeInsert(str.+) // insertion returns a list of at least one string keys
     */
    // Although ScalikeJDBC 2.2 doesn't support multiple generated keys, it's possible to specify generated key to be returned
    val id: Long = SQL("insert into City(name, country) values ({name}, {country})")
      .bindByName('name -> "Cambridge", 'country -> "New Zealand")
      .updateAndReturnGeneratedKey("id")
      .apply()
  }

  {
    /*
import anorm.{ SQL, SqlParser }
val code: String = SQL(
  """
    select * from Country c
    join CountryLanguage l on l.CountryCode = c.Code
    where c.code = {countryCode}
  """)
  .on("countryCode" -> "FRA").as(SqlParser.str("code").single)
     */
    val code: Option[String] = SQL("""
      select * from Country c
      join CountryLanguage l on l.CountryCode = c.Code
      where c.code = {countryCode}
      """).bindByName('countryCode -> "FRA").map(rs => rs.get[String]("code")).single.apply()
  }

  {
    /*
// Parsing column by name or position
val parser =
  SqlParser(str("name") ~ float(3) map {
    case name ~ f => (name -> f)
  }
val product: (String, Float) = SQL("SELECT * FROM prod WHERE id = {id}").
  on('id -> "p").as(parser.single)
     */
    val parser = (rs: WrappedResultSet) => rs.get[String]("name") -> rs.get[Float](3)
    val product: Option[(String, Float)] = SQL("SELECT * FROM prod WHERE id = {id}")
      .bindByName('id -> "p").map(parser).single.apply()
  }

  {
    /*
val name = "Cambridge"
val country = "New Zealand"
SQL"insert into City(name, country) values ($name, $country)"
     */
    val (name, country) = ("Cambridge", "New Zealand")
    sql"insert into City(name, country) values ($name, $country)"
    /*
val lang = "French"
val population = 10000000
val margin = 500000
val code: String = SQL"""
  select * from Country c
    join CountryLanguage l on l.CountryCode = c.Code
    where l.Language = $lang and c.Population >= ${population - margin}
    order by c.Population desc limit 1"""
  .as(SqlParser.str("Country.code").single)
     */
    val lang = "French"
    val population = 10000000
    val margin = 500000
    val code: Option[String] = sql"""
      select * from Country c
        join CountryLanguage l on l.CountryCode = c.Code
        where l.Language = $lang and c.Population >= ${population - margin}
        order by c.Population desc limit 1"""
      .map(_.get[String]("Country.code")).single.apply()
  }

  {
    /*
// Create an SQL query
val selectCountries = SQL("Select * from Country")
// Transform the resulting Stream[Row] to a List[(String,String)]
val countries = selectCountries().map(row =>
  row[String]("code") -> row[String]("name")
).toList
     */
    val selectCountries = sql"Select * from Country"
    val countries = selectCountries.map(rs => rs.get[String]("code") -> rs.get[String]("name")).toList.apply()
    /*
// First retrieve the first row
val firstRow = SQL("Select count(*) as c from Country").apply().head
// Next get the content of the 'c' column as Long
val countryCount = firstRow[Long]("c")
     */
    val countryCount: Long = sql"Select count(*) as c from Country".map(_.long("c")).single.apply().get
  }

  {
    /*
// With default formatting (", " as separator)
SQL("SELECT * FROM Test WHERE cat IN ({categories})").
  on('categories -> Seq("a", "b", "c")
    */
    val categories = Seq("a", "b", "c")
    sql"SELECT * FROM Test WHERE cat IN (${categories})"
    /*
// With custom formatting
import anorm.SeqParameter
SQL("SELECT * FROM Test t WHERE {categories}").
  on('categories -> SeqParameter(
    values = Seq("a", "b", "c"), separator = " OR ",
    pre = "EXISTS (SELECT NULL FROM j WHERE t.id=j.id AND name=",
    post = ")"))
    */
    val pre = sqls"EXISTS (SELECT NULL FROM j WHERE t.id=j.id AND name="
    val condition = sqls.joinWithOr(categories.map(c => sqls"$c"): _*)
    val post = sqls")"
    sql"SELECT * FROM Test t WHERE ${pre}${condition}${post}"
  }

  {
    /*
import anorm.SQL
import anorm.SqlParser.{ scalar, * }
// array and element parser
import anorm.Column.{ columnToArray, stringToArray }
val res: List[Array[String]] =
  SQL("SELECT str_arr FROM tbl").as(scalar[Array[String]].*)
     */
    val res = sql"SELECT str_arr FROM tbl".map(_.get[java.sql.Array]("str_arr")).list.apply()
  }

  // Batch update
  {
    /*
import anorm.BatchSql
val batch = BatchSql(
  "INSERT INTO books(title, author) VALUES({title}, {author}",
  Seq(Seq[NamedParameter](
    "title" -> "Play 2 for Scala", "author" -> Peter Hilton"),
    Seq[NamedParameter]("title" -> "Learning Play! Framework 2",
      "author" -> "Andy Petrella")))
val res: Array[Int] = batch.execute() // array of update count
     */
    val paramsList = Seq(
      Seq('title -> "Play 2 for Scala", 'author -> "Peter Hilton"),
      Seq('title -> "Learning Play! Framework 2", 'author -> "Andy Petrella")
    )
    sql"INSERT INTO books(title, author) VALUES({title}, {author})"
      .batchByName(paramsList: _*).apply()
  }

  // Edge cases
  {
    /*
// Wrong #1
val p: Any = "strAsAny"
SQL("SELECT * FROM test WHERE id={id}").on('id -> p) // Erroneous - No conversion Any => ParameterValue
// Right #1
val p = "strAsString"
SQL("SELECT * FROM test WHERE id={id}").on('id -> p)
*/

    val p: Any = "strAsAny"
    // ScalikeJDBC binds params with their actual types
    SQL("SELECT * FROM test WHERE id = {id}").bindByName('id -> p)
      .toMap.list.apply()

    /*
// Wrong #2
val ps = Seq("a", "b", 3) // inferred as Seq[Any]
SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").
  on('a -> ps(0), // ps(0) - No conversion Any => ParameterValue
    'b -> ps(1), 'c -> ps(2))
// Right #2
val ps = Seq[anorm.ParameterValue]("a", "b", 3) // Seq[ParameterValue]
SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").
  on('a -> ps(0), 'b -> ps(1), 'c -> ps(2))
*/
    val ps = Seq("a", "b", 3) // inferred as Seq[Any]
    SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}")
      .bindByName('a -> ps(0), 'b -> ps(1), 'c -> ps(2))
      .toMap.list.apply()

    /*
// Wrong #3
val ts = Seq( // Seq[(String -> Any)] due to _2
  "a" -> "1", "b" -> "2", "c" -> 3)
val nps: Seq[NamedParameter] = ts map { t =>
  val p: NamedParameter = t; p
  // Erroneous - no conversion (String,Any) => NamedParameter
}
SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").on(nps :_*)
// Right #3
val nps = Seq[NamedParameter]( // Tuples as NamedParameter before Any
  "a" -> "1", "b" -> "2", "c" -> 3)
SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").
  on(nps: _*) // Fail - no conversion (String,Any) => NamedParameter
*/
    val ts = Seq( // Seq[(String -> Any)] due to _2
      "a" -> "1", "b" -> "2", "c" -> 3)
    SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").bindByName(ts.map { case (k, v) => Symbol(k) -> v }: _*)
      .toMap.list.apply()

    /*
import anorm.features.anyToStatement
val d = new java.util.Date()
val params: Seq[NamedParameter] = Seq("mod" -> d, "id" -> "idv")
// Values as Any as heterogenous
SQL("UPDATE item SET last_modified = {mod} WHERE id = {id}").on(params:_*)
 */
    val (mod, id) = (new java.util.Date(), "idv")
    sql"UPDATE item SET last_modified = ${mod} WHERE id = ${id}".update.apply()

  }

  {
    /*
case class SmallCountry(name:String)
case class BigCountry(name:String)
case class France
val countries = SQL("Select name,population from Country")().collect {
  case Row("France", _) => France()
  case Row(name:String, pop:Int) if(pop > 1000000) => BigCountry(name)
  case Row(name:String, _) => SmallCountry(name)
}
*/
    sealed trait Country
    case class SmallCountry(name: String) extends Country
    case class BigCountry(name: String) extends Country
    case object France extends Country

    val queryResults = sql"Select name,population from Country"
      .map { rs => rs.get[String]("name") -> rs.get[Int]("population") }
      .list.apply()
    val countries: Seq[Country] = queryResults.map {
      case ("France", _) => France
      case (name, pop) if pop > 1000000 => BigCountry(name)
      case (name, pop) => SmallCountry(name)
    }

  }

  // Using for-comprehension
  {
/*
import anorm.SqlParser.{ str, int }
val parser = for {
  a <- str("colA")
  b <- int("colB")
} yield (a -> b)
val parsed: (String, Int) = SELECT("SELECT * FROM Test").as(parser.single)
*/
     // for-comprehension is not suitable for ScalikeJDBC
     sql"SELECT * FROM Test".map(rs => rs.get[String]("colA") -> rs.get[Int]("colB")).list.apply()
  }

}