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

UNIX や web やプログラムの技術的なことを中心に。

perl+DBI プログラムの鉄則 - SQL 文の書き方

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"

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

スポンサーサイト

PageTop

flag と Boolean

flag について悩むコメントより
名前というより、データ型をBOOLEAN NOT NULLにしたら、2値ということでいいんでないでしょうか。そうでなければ、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値でいくぞ」という覚悟が足らないのかもしれない。
PageTop

「flag」について悩む

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年ほど項目名を決めるたびに悩んでいるような気がします。
PageTop

Oracle、オープンソースソフト企業のInnobase買収

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 を手書きするようなケースでは多用する。
PageTop

MySQL と RaiseError とサーバサイド prepare

昨日から今日にかけて はまったこと。

DBI + DBD::mysql + MySQL で、RaiseError が効かない。

 use DBI;
my $dbh = DBI->connect(
'DBI:mysql:dbname', 'user', 'password',
{AutoCommit => 0, RaiseError => 1, PrintError => 0}
) || die;
my $sth = $dbh->prepare("select * from not_exist_table");
$sth->execute;


上記のように、存在しないテーブルやカラムに対して DML を発行しようとすると、
RaiseError を ON にしておけば SQL 解析の prepare で勝手に die してくれる。

エラーはこんな感じ。
  DBD::mysql::st execute failed: Table 'dbname.not_exist_table' doesn't exist at a line 5.

これは Perl における use strict のようなもので、RaiseError を ON
にするのは当然の話。

しかし、ある環境で実行すると、
  Can't call method "execute" on an undefined value at b line 6.
となってしまった。一応プログラムは異常終了しているのだが、これはテーブルが
存在しないので prepare は undef を返して終了し、undef なものに対して execute
メソッドを実行したから落ちただけのこと。prepare の時点で RaiseError により
落ちたわけではない。

RaiseError も効かないし、PrintError も効かない。それどころか
  my $sth = $dbh->prepare("select * from not_exist_table") || die "$DBI::errstr";

でも落ちない。

どうしたものかと思って調べていたら、

DBD::mysqlより引用:
Prepared statement support (server side prepare)

As of 3.0002_1, server side prepare statements are on by default
(if your server is >= 4.1.3)

To use driver emulated prepared statements, all you need to do
is set the variable mysql_emulated_prepare in the connect:

$dbh = DBI->connect( "DBI:mysql:database=test;host=localhost;mysql_emulated_prepare=1",
"", "", { RaiseError => 1, AutoCommit => 1 } );

* Note: delimiter for this param is ';'

To make sure that the 'make test' step tests whether server prepare works,
you just need to export the env variable MYSQL_SERVER_PREPARE:

export MYSQL_EMULATED_PREPARE=1


ということらしい。DBD::mysql-3.0002_1 から「サーバサイド prepare」というものが
デフォルトで有効になったとか。

というわけで、
 my $dbh = DBI->connect(
    'DBI:mysql:dbname;mysql_emulated_prepare=1', 'user', 'password',
    {AutoCommit => 0, RaiseError => 1, PrintError => 0}
   ) || die;

として解決。

で、「サーバサイド prepare」とは何かと言うと、
 MySQL Lists: mysql-ja: Prepared Statement (訳)
らしい。
  • これまでの DBD::mysql の prepare はクライアントサイドの prepare を使っており、
    速度向上にはあまり寄与していなかった。
  • DBD::mysql-3.0002_1 からサーバサイドの prepare を使うようにした。

というのはわかるが、mysql_emulated_prepare=1、つまり prepare をエミュレートするとは
どういうことか? サーバサイドの prepare はなぜ RaiseError が効かないのか?
ってのが全くわからない。

Oracle だと prepare ってのは要はカーソルオープンしっぱなしにするわけで、
つまりはサーバサイドなわけだが、MySQL の言うサーバサイド prepare ってのは
それと違う?

よくわからん人はおとなしく DBD::mysql-2.9x でも使ってなさいってことか。
PageTop