複数の DB に対してクエリでエビデンスを取得するバッチ

背景

• テストの際に、複数の SELECT 文の結果をテキストで保存してエビデンスとする。
• 確認したいデータベースは MySQL と Oracle の二つ。
• 開発時の確認のためだけに使用するクエリなので、速度や脆弱性は度外視する。

... といった場合に必要となるオプションをまとめた。備忘録で乱文。

PostgreSQL については業務ができたら追記する。

クエリ

例として MySQL 用に q1.sql、Oracle 用に q2.sql の二つを用意する。

MySQL 用クエリの書き方

MySQL用q1.sql

SELECT '● users' as "Table Name";
SELECT
      LEFT(FIRST_NAME, 10) AS "FIRST_NAME"
    , LEFT(LAST_NAME, 10) AS "LAST_NAME"
FROM users
ORDER BY
      LAST_NAME
    , FIRST_NAME
;

どのテーブルのエビデンスとなるかを出力するため、最初にテーブル名を出力している。

MySQL文の中でechoのようなことをしたい - Qiita

上記のようにテーブル一つだけなら、後述するバッチ内に echo があっても良い。
通常は複数のテーブルを一緒に確認するだろうから上記のようにすればいいと思う。
テーブルごとに .sql ファイルを作り、バッチで echo なんてことは死ぬ。

カラムの幅が大きい場合、たとえば varchar(80) だったりすると、データ内容がそれ以下でも 80 桁分の空白が表示されてしまう。
それを防ぐために上記のように LEFT() 関数をかませる。

formatting - change mysql output column width - Stack Overflow

Oracle（SQL*Plus） 用クエリの書き方

Oracle用q2.sql

SET TAB OFF;

SET HEADING OFF;
SELECT '● users' AS "TableName" FROM Dual;
SET HEADING ON;

COLUMN FIRST_NAME FORMAT A10
COLUMN LAST_NAME  FORMAT A10
SELECT
      FIRST_NAME
    , LAST_NAME
FROM users
ORDER BY
      LAST_NAME
    , FIRST_NAME
;
EXIT;

こちらも、どのテーブルのエビデンスとなるかを出力するため、最初にテーブル名を出力している。
ただしこちらは SET HEADING OFF; によってヘッダーを省略している。より echo のイメージに近い。
mysql にはこれに該当する機能は -N オプションしかないようだ。

テストの最初から最後まで確実に検索結果がある場合は以下のような方法もある。

Oracle用q2.sql

SET TAB OFF;

COLUMN FIRST_NAME HEADING '● users|FIRST_NAME'
COLUMN FIRST_NAME FORMAT A10
COLUMN LAST_NAME  FORMAT A10
SELECT
      FIRST_NAME
    , LAST_NAME
FROM users
ORDER BY
      LAST_NAME
    , FIRST_NAME
;
EXIT;

SQL*Plusレポートの書式設定

ただし、こっちの方法だと検索結果レコードが無い場合に何も表示されない。テスト中は「レコードがないこと」を確認したいこともあるだろうからこの方法は通常お勧めできない。

SQL*Plus はデフォルトで長い空白を TAB を用いて出力するため、最初に SET TAB OFF; をしている。エビデンスを何で確認するかによるが、表示が崩れることがあるので OFF にしておくのが無難。

こちらもカラムの幅が大きい場合に余計な空白が表示されないように設定しているが、MySQL でのやり方とは異なる。COLUMN FIRST_NAME FORMAT A10 がそれにあたる部分で、10桁だけを表示するようにしている。

COLUMN フォーマット例 - オラクル・Oracle SQL*Plus リファレンス

最後に EXIT; をしないと、バッチはそこでとまってしまう。

バッチ

上記二つのクエリを連続して実行するバッチのコードは以下の通り。

1.bat

@echo off
"C:\Path\To\mysql" --table --silent --host=localhost --database=foo --user=bar -pbaz < q1.sql
"C:\Path\To\sqlplus" -S bar/baz@foo @q2.sql

このバッチを実行し、結果をリダイレクトしてファイルに保存すれば簡単なエビデンスとなる。
テキストなので実行前後で比較することも容易だ。

MySQL のオプション

--table,検索結果を「表」形式で出力する。
--silent,mysql 起動時に表示されるバージョン番号などのメッセージを表示しない。
--host,MySQL サーバーのホスト名を指定する。上の例では localhost がその値。
--database,MySQL データベース名を指定する。上の例では foo がその値。
--user,MySQL に接続するユーザー名を指定する。上の例では bar がその値。
-p,パスワードを指定する。パスワード "baz" と -p との間に空白を入れないように注意する。
クエリの指定,"<" を使って mysql コマンドにクエリを渡す。

Oracle（SQL*Plus）のオプション

-S,SQL*Plus 起動時に表示されるバージョン番号などのメッセージを表示しない。
ユーザー名,上の例では bar がユーザー名。
パスワード,上の例では baz がユーザー名。
接続先スキーマ,上の例では foo がその値。tnsnames.ora にて指定した値と同じ。
クエリの指定,頭に @ をつけて実行したいクエリファイル名を指定する。