古臭いデータベースに、Scalaで挑む。

• 昔書いた記事だけど、闇のデータベースが相手なのでアドベントカレンダーにあげておきます。記事には書いてないけどakkaは使っているよ。

#序
システム移行などで、先人が作った古臭いデータベースの中身を把握しなければならないことがある。
（私の場合は、かなり古いバージョンのpostgres）
こうした場合、psqlなどのSQlコマンドラインツールを使って
テーブル一覧を見たり、これは思うテーブルにクエリをかけたりすることになるだろう。

しかし、古臭いデータベースは時として手ごわい。
例えば、本番データとダミーデータとがテーブル上に混在していて、両者をトリッキーな方法で分離しなければならないことなどが次々と起こることがある。加えて、テーブル構造が分かっていないのに個人情報は全て匿名化してくれ、などというげんなりする要求が出てきたりもする。こうした時、SQLだけでは限界があり、やはりプログラム言語の力も借りたいところ。

今回は、
　①　古臭いDBに対応したドライバが見つかりやすいJDBCが使える
　②　そのまま実行可能なSQLをモダンに扱える
　③　強力なコレクションクラスを駆使して、DBからの取得データを変換していける
ということから、scala on JVMでScalikeJDBCを採用して、古臭いデータベースに向き合うことにした。
※上の①はJVM、②はScalikeJDBC、③はscalaと主に関わる

結果、古臭いデータベースにうまく向き合えるようになってきたので、以下に備忘録を残しておく。
ScalikeJDBCはscala的な簡潔な書き方でJDBC接続できる、というもので日本語ドキュメントも充実しているので、javaに疲れたエンジニアの方が気分転換にscala入門する際の言語としても良いかも。
なお、ScalikeJDBCは、さだまさしをscalaでコンパイルした方が作られたデータベースアクセスフレームワークで、webフレームワークのPlay2.xなどを使っている方にはおなじみのもの。

#1. 環境構築
scalaやsbtを入れていない人は、sdkmanなどで入れてほしい（sdkmanはgroovyベースの環境構築管理ツール）。
sbtって何という人は、ググってください。

以下のbuild.sbtでsbtを走らせると、一通り必要なライブラリがそろう。

build.sbt

scalaVersion := "2.12.6"

libraryDependencies ++= Seq(
    "org.scalikejdbc" %% "scalikejdbc"    % "3.3.+",            //適切なScalikeJDBCのバージョンを指定
    "org.postgresql" % "postgresql" % "42.2.4",                 //ここは古臭いデータベースに合わせたドライバーを指定
    "com.typesafe.scala-logging" %% "scala-logging" % "3.9.0",  //ロギング関係（ScalikeJDBCが発行したSQLをログで知る）
    "ch.qos.logback" % "logback-classic" % "1.2.3"
)

#2.普通にJDBC接続
ローカルのPostgresに接続する場合、以下のような感じとなる。
charSet=EUC-JPのところは、接続するデータベースの文字コードに合わせる。
文字コード周りで困るは、古臭いデータベースで困ることあるある、だろう。

.scala

// 概ね、普通のJDBC接続のように書いてデータベースに接続
  val url = "jdbc:postgresql://localhost:5432/データベース名?charSet=EUC-JP"
  val user = "ユーザー名"
  val password = "パスワード"
  ConnectionPool.singleton(url, user, password)

#3.ScalikeJDBC経由でデータベース接続

例えば、psqlで以下のようなSQLを書いていたとしよう。
（実際、目の前の古臭いデータベースにはdataXXXというカラムが多量に含まれる謎のレポートテーブルが多々ある。）

nazo_reportテーブル上の1カラム中の頻出ワードを知る.sql

select data111, count(*) 
from nazo_report 
  group by data111 
  having count(*) > 4
  order by count(*) desc ;

ここでは、ScalikeJDBCの「 SQL インターポレーション」という方式で
指定するワード数の下限を経由で変更できるようにする。

コードは以下のような感じ:

.scala

  def query1(min:Int) : List[Map[String, Any]] = {
    print(s"${min}以上の登録があるワード一覧:")
    DB readOnly {implicit s =>
      //PGのエラー「バイト列0xfc 0xe2」に対し、convert_fromで対処
        sql"""select 
                replace(data111, convert_from('\xfce2', 'EUC-JP'), '？') n1, 
                count(*) c1 
              from nazo_report 
                group by data111 
                having count(*) > ${min} 
                order by count(*) desc;"""
        .map(_.toMap ).list.apply()
    }    
  }

selectの後でpostgresのreplace関数が使われているが、data111カラムで生じた以下のエラー（古いDBあるあるの文字変換エラー）への対処。

(run-main-0) org.postgresql.util.PSQLException: ERROR: 符号化方式"EUC_JP"においてバイト列0xfc 0xe2である文字は符号化方式"UTF8"で等価な文字を持ちません

こうした対応が柔軟に行えるのは、生SQLならでは。

なお、ScalikeJDBCでは、以下も利用できる:

• 素のJDBCと同じテンプレート方式
• 生のSQLをほぼそのまま用いながら、動的なクエリを発行する「実行可能な SQL テンプレート」方式
• SQL的なDSLを用いたタイプセーフなテンプレートによる方式

※　ScalikeJDBCで用いることができる接続方式は、本家クックブックの以下に解説されている。
https://github.com/scalikejdbc/scalikejdbc-cookbook/blob/master/ja/05_sql_template.md

#4.取得結果をscalaらしく加工していく

データベースからデータを取得後は、もろもろの必要に応じ、
コードを書いていくことになる。
ここでは、クエリ結果を表示するという基本的なところだけを書いておく。

.scala

    val ret = query1(20)    // ScalikeJDBCを用いたクエリ関数の呼び出し

    ret.foreach{ //ループを回す
        m => print (m.getOrElse("n1","取得エラー"), m.getOrElse("c1","取得エラー")) 
    }

ポイントは、以下の2点:

• クエリの結果は、Map[String, Any]という型の要素を持つリスト(List)となっている。
  リストの一つ一つの要素をforeachループで取得している(要素には変数名mを割当)。

• 各要素mから、SQLの返り値のカラム名（ここでは、n1、c1）をキーに指定してMapから結果を取得する。
  単にm("n1")と書いても結果は取得できるが、m.getOrElse("n1","取得エラー")と書くことでnull対策ができる。

古臭いデータベースあるあるのnullを含むカラムをMapから取得しようとすると以下のエラーとなる。

[error] java.util.NoSuchElementException: key not found: n1

#終わりに
当然、新しいデータベース相手にもScalikeJDBCは楽しく使えるよ！

ソースコード全体:

.scala

import scalikejdbc._

object ScalikeJDBC extends App {

    // データベースに接続
    val url = "jdbc:postgresql://localhost:5432/arealink-system?charSet=EUC-JP"
    val user = "postgres"
    val password = "buggy3"
    ConnectionPool.singleton(url, user, password)

    def query1(min:Int) : List[Map[String, Any]] = {
        print(s"${min}以上の登録があるワード一覧:")
        DB readOnly {implicit s =>
            //PGのエラー「バイト列0xfc 0xe2」に対し、convert_fromで対処
            sql"""select 
                    replace(data111, convert_from('\xfce2', 'EUC-JP'), '？') n1, 
                    count(*) c1 
                    from nazo_report 
                group by data111 
                having count(*) > ${min} 
                order by count(*) desc;"""
            .map(_.toMap ).list.apply()
        }    
    }

    // クエリの実行と結果表示
    val ret = query1(20)
    ret.foreach{
        m => print (m("n1"), m.getOrElse("c1","取得エラー")) 
    }
}