無精で短気で傲慢なプログラマ

mysqldiff を使って継続的に MySQL のデータベーススキーマの差分をチェックする を読んで。

mysqldiff は知らなかったんだけど、こういう方法もある。

mydevdb が開発用DB、mydb が本番用DBとして、

$ diff -F "^CREATE" -c -I "Dump completed on" \
  <(mysqldump mydevdb -no-data | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/') \
  <(mysqldump mydb --no-data | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/')

mysqldump --no-data で、スキーマ定義だけを出力し、
diff コマンドで差分を取っている。

単純な diff だと、例えば 20個カラムを持つテーブルの
真ん中あたりのカラムで差分が出た場合、どのテーブルの
差分なのかわかりづらい。-F "^CREATE" は、差分があった場合、
前の行にさかのぼって、マッチする行を表示してくれる。

例えば下記の例だと、"CREATE TABLE `hoge_table` (" とあるので、
hoge_table テーブルの add_column カラムに差異あり、というのがわかる
(-F を指定しないと、どのテーブルなのか表示されない)。

*************** CREATE TABLE `hoge_table` (
*** 71,76 ****
--- 71,77 ----
`last_checked_datetime` datetime DEFAULT NULL,
`last_notified_datetime` datetime DEFAULT NULL,
`notified_last_days` int(11) DEFAULT NULL,
+ `add_column` int(11) DEFAULT NULL,
`insert_datetime` datetime NOT NULL,
`update_datetime` datetime NOT NULL,
PRIMARY KEY (`uid`,`domain_id`)

-F オプションは、context diff や unified diff でないと
効かないので、この例では -c で contxt diff にしてある。
"^CREATE" だけのパターンで大丈夫かはわからないが、もし
漏れがあったとしても、差分自体が出ないわけではないので
大事故にはならないでしょう。なお、diff -F は 1989年あたりから
あるっぽいので、Linux でも *BSD でも使えるはず。

さらに、ダンプ末尾の
　-- Dump completed on 2017-04-03 13:44:47
というのも毎回差分が出るので、grep -I で無視している。

AUTO_INCREMENT も差分が出てしまうが、
) ENGINE=InnoDB AUTO_INCREMENT=310458 DEFAULT CHARSET=utf8;
などと他に重要な情報が出ているので、
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
というふうに AUTO_INCREMENT 部分だけつぶすようにしている。

コマンド <(サブコマンド1) <(サブコマンド2)
という書き方は、bash 専用。
詳しくは リダイレクト を。

ちなみに、5環境分まとめてチェックしたかったり、開発テーブルではこのカラムAが
5番目にあるけど、本番テーブルでは末尾にあるが、これは無視したいとか、細かな
要望がいろいろ出てきて、結局は自前でスクリプト組むことになる気もする。

あと、http://x68000.q-e-d.net/~68user/unix/pickup?mysql
という、MySQL に関することをひととおり盛り込んだ解説をちまちまと書いているので、
よかったら読んでください。

perl+DBI プログラムの鉄則 のつづき。

鉄則その4.

  my $sql =
    "SELECT ".
    "   hoge, ".
    "   fuga ".
    "  FROM table1 ".
    " WHERE foo = ?";

SQL 文の可読性を高めるため、必ずインデントを付けること。SQL はプログラムと
データベースの界面である。プログラムが正しくて、なおかつデータベースが正しく
ても、SQL 文が誤っていたら全ては台無しとなる。

しかも SQL 文は多機能なわりに短く書けるので、情報密度が高い (逆に言うと
重要な部分を読み取りづらい)。SQL 文の可読性を高めずして、プログラムの質の
向上はない。

  $dbh->prepare("SELECT hoge, fuga FROM table1 WHERE foo = ?");

などと prepare や execute の引数に直接 SQL 文を書いてはいけない。必ず一度
$sql などの変数に代入しよう。どんなに気をつけても、手で書いた SQL には
大抵ミスがあるものである。一度 SQL に代入しておけば、

  print "$sql\\n";

とすることで print デバッグができる。特に初心者には作業効率の向上に
つながるだろう。なお、「一度変数に代入した方がよい」というのは SQL に
限らずプログラム一般に言えるノウハウである。print (printf) デバッグも
できるし、ソースレベルデバッガを使っている場合でも、どんな値が入って
いるか確認しやすい。


1行につき、項目・条件は 1つだけ書く。
  my $sql =
    "SELECT ".
    "   hoge, fuga ".
    "  FROM table1 ".
    " WHERE foo = ? AND bar = ?";
と 1行に複数項目・複数条件を書かない。理由は可読性の向上に加え、コメントを付け
やすくするため。

  " WHERE NVL(foo, 0) = ?". # ほげ情報が未指定の場合は foo は NULL なので 0 に変換する
  " AND bar = ?";

などと書けるようにする (NVL は NULL なら指定された値に変換する Oracle の
関数。MySQL なら IFNULL。SQL の規格的にこういう関数があるかどうかは知らない)。

もうひとつの理由は、変更点を明確にするため。CVS や Subversion や VSS などの
バージョン管理ツールには、変更した箇所を表示する機能があるが、いずれも
行単位でしか変更点を表示できない。差分を知りたいときに

(変更前) "SELECT hoge, fuga, CASE WHEN moge=1 THEN 'a' moge=2 THEN 'b' ELSE 'c' END FROM table1 WHERE foo = ? AND bar = ? GROUP BY baz"
(変更後) "SELECT hoge, fuga, CASE WHEN moge=1 THEN 'a' moge=2 THEN 'b' ELSE 'd' END FROM table1 WHERE foo = ? AND bar = ? GROUP BY baz"

などと表示されたとしても、どこが変更されたのさっぱりわからない。複数行に分けて
書けば、どの行が変更されたのかが明確になる。

flag について悩む の コメントより

名前というより、データ型をBOOLEAN　NOT NULLにしたら、2値ということでいいんでないでしょうか。そうでなければ、2値以外もありえる、たとえそのときは選択肢が２つしかなくとも、設計者は増える可能性を考えていた（仕様変更含め）、という認識で、データ型重視で。もちろん、『is_admin』なんて項目に、「管理者/保守ユーザ/一般ユーザ」なんて値が設定可能だったら、「なんでそんな名前やねん！」となりますが、それは単に設計者のセンスが悪かったのだと割り切ればいいんでないでしょうか。まあ、どっちにしても、なにをBOOLEANにするか？迷いどころなので、問題は解決できていないのですが、BOOLEANにしちゃったら、もう小手先の変更では取り返しがつかないというプレッシャーがあるので、よく考えた上でそうなっているはずだと、名前より重要な位置づけで考えられると思います。

なるほど、Boolean NOT NULL ですか。どこまで腹をくくれるかって話ですね。

Boolean のことをよく知らないので調べてみました。

Boolean が規格化されたのは SQL-99 なので、まだまだ使えない RDBMS が多いのが最大の欠点。各 RDBMS の対応状況は
　- PostgreSQL は Boolean に対応
　- MySQL だと tinyint (-128～127) のシノニム
　- Oracle は未対応?
　- DB2も使用不可っぽい?
てな感じ。ただ、MySQL は ENUM 型 を使って、
　ENUM('TRUE','FALSE')
　ENUM(0, 1)
　ENUM(0, 1) NOT NULL
などと型の定義ができる (Oracle や DB2 もできる?)。まぁ制約を使えばどんな RDMBS でも何とかなるんだけれども。

てな感じでしょうか。

で、仮に Boolean が使えるとして、自分が使うかどうかを考えてみました。その結果、
　- 他システムとデータのやりとりをする場合、他システムが決めた項目が 2値なら使用しない
　　(3値以上になる可能性が高いから)。逆に自システム用で 2値と決定しなたら使用する。
　- レコード数が数百万を超えるなら使わないかもしれない
　　(型変更に対する DB メンテナンス時間がかかるから)
という結論になりました。わたしには「2値でいくぞ」という覚悟が足らないのかもしれない。

2ch プログラマ板の DB 関連のスレッドで (どのスレかは忘れた)、
　「hoge_flag という項目名をつけるのであれば、とりうる値は 2種類で
　　あるべき。flag は旗なんだから、上げるか下げるかのどちらかだ」
という趣旨の書き込みを見たことがある。

そのときは何となく納得して、2値の場合は hoge_flag、3値以上の場合は
hoge_code や hoge_type などと命名するようにしたが、本当に旗は上げるか
下げるかのどちらかなのだろうか。

たとえば手旗信号は
　- 真上に上げる
　- 斜め上 45度
　- 水平
　- 斜め下 45度
　- 真下に下げる
の 5種類あるように見える。

参考: 手旗信号

しかし「flag bit」という言葉もある。「flag bit」なら 2値限定、「flag」
なら 3値以上もアリ、なのだろうか。


とりあえず、仮に「2値なら hoge_flag、3値以上は hoge_code・hoge_type」と
いうルールを決めたとする。しかし最初は 2値であると思っていたが、後になって
考えると 3値以上を割り当てたくなるというのはよくある話。

ということは、最初から hoge_flag という名前を使わない方がよいのだろうか。

あるいは 3値以上になった場合、項目名を改名するのがよいだろうか。その場合、
DB 設計書・プログラム設計書・プログラム・DBのカラム名・関連する全 SQL の
すべてを改修する必要がある。はたしてそれだけの労力を費やす価値があるのだろうか。

…と、この 3年ほど項目名を決めるたびに悩んでいるような気がします。

http://www.itmedia.co.jp/news/articles/0510/08/news012.html より。

米Oracleは10月7日、フィンランドのオープンソースソフト企業Innobase OYの買収を発表した。買収条件は非公開。

MySQL 的に一番嫌なのは、これにより InnoDB の開発が停滞し、現在注力している
エンタープライズ分野への進出がストップしてしまうこと、だろうか。

とりあえずいつでも PostgreSQL に切り替えられるように、MySQL の方言は極力
使わないようにしておこう。

MySQL リファレンスマニュアル: SQL-92 標準に対する MySQL 拡張機能

とはいえ、
　　INSERT INTO TABLE SET col1=val1, col2=val2
はカラム名と値の対応がわかりやすいので便利だ。それと、
　　INSERT INTO TABLE values (col1, col2), (col3, col4), (col5, col6);
という複数行の同時 INSERT も結構楽ちん。いずれもプログラム中で使うことは
あまりないが、マスタテーブルを更新するための SQL を手書きするようなケースでは多用する。