シェルスクリプトから使う場合でも RDBMS が優れた選択肢であることはこれまで述べたとおりだが、RDB の利用には SQL が必須である。ここでは、これまでなんとなく SQL を避けてきた、あるいは文法は覚えてもなかなか実用的な設計が できないと感じている立場を想定して、SQL とうまく付き合うための考え方について説明する。
多くの人がSQLよりも先にプログラミング言語を学ぶ。 一定の水準を越えると、自在にデータ構造を設計でき、 特定のデータ集合に対しての処理を記述できるようになる。そのあとで、SQL を学んだとすると、変数と制御構造の組み合わせだけで何でも表現できてきた 世界との違いにとまどい、以下のような違和感を感じることがある。
これらの疑問に対するここでの提案に共通して言えるのは 「悩んだら特に深く考えず進む」である。 実際にはその悩みを越えた理解が自身に備わっている可能性もある。 個別に説明していく。
この違和感は該当者がいないかもしれないが、 筆者自身が学びたての頃に強く感じていたことなので念のため書いておく。 木構造で値の集合を表現することに慣れていると、RDB のテーブルに、キーとなる値が複数並んでいることに冗長性を感じるかもしれない。 たとえば RDB テーブルと木構造それぞれで「山形県の市」の集合を表現する例を考える。
テーブル表現 山形県 山形市 山形県 米沢市 山形県 鶴岡市 山形県 酒田市 山形県 新庄市 山形県 寒河江市 山形県 上山市 山形県 村山市 山形県 長井市 山形県 天童市 山形県 東根市 山形県 尾花沢市 山形県 南陽市
木構造表現 山形県 山形市 米沢市 鶴岡市 酒田市 新庄市 寒河江市 上山市 村山市 長井市 天童市 東根市 尾花沢市 南陽市
プログラミングの心得があると、 これらの構造を配列とハッシュの使えるプログラミング言語での表現を想像し、 それぞれ次のような式が頭に浮かぶ(配列は [ ] 括りで、ハッシュは { } 括りで表現している)。
table = [["山形県", "山形市"], ["山形県", "米沢市"], ["山形県", "鶴岡市"],
["山形県", "酒田市"], ["山形県", "新庄市"], ["山形県", "寒河江市"],
["山形県", "上山市"], ["山形県", "村山市"], ["山形県", "長井市"],
["山形県", "天童市"], ["山形県", "東根市"], ["山形県", "尾花沢市"],
["山形県", "南陽市"]]
tree = {"山形県" => ["山形市", "米沢市", "鶴岡市", "酒田市", "新庄市",
"寒河江市", "上山市", "村山市", "長井市", "天童市",
"東根市", "尾花沢市", "南陽市"]}
テーブル形式表記の方に同じ単語の羅列があり、 メモリ使用量的に無駄な感じがする。SQL の学習資料では分かりやすさを重視するため、 上記のような具体名が並ぶ表を見せることが多いが実際にはさほど「無駄」とはならない。
また、木構造の方のイメージで配列に無駄がないように感じるが 実際のプログラミング言語では、配列はリスト構造で管理され各要素ごとに 前後へのポインタ等を含むので見た目程スリムな訳ではない。
記録容量的な話でいえば、HDD の大容量化が進んでいる昨今では短い文字列が 1億個集まっても1台のHDDを埋められる程ではない。 いずれにせよ、見掛け冗長に見える部分がデータベースファイルに占める割合は 誤差程度で、気にしても全く意味がないといえる。
学生時代の筆者がリレーショナルモデルについて教わったとき、 正規化について、遠山元道先生はこうおっしゃった。
「これはどれも分かってから読むと分かるんだけどね……」
「奥が深いのかな」と当時思ったものだが、 それだけでなく正規化の説明並びが難しいと感じる場合の理由の一つに、 既に無意識に体得しているルールが含まれる場合がある。つまり、 第3正規化より低いものは、普段表計算ソフトウェアしか使わず更新時の効率も 考えない人達が「やらかして」しまう設計のベカラズ集のような趣と言えなくもない。 スクリプトプログラミングに長けて、普段「効率的なデータ構造」を意識している 人であれば、データ更新が発生したときの書き変え負荷を減らすための工夫をしているはずである。 もしそれが当てはまるなら、 無意識のうちに第3正規化まで完了したテーブル設計ができるはずである。
あまり自信がない場合でも、 入門書等で第3正規化まで把握したところで実用システムを作ってみることで、 「ああなんだ」となるだろう。「完全」な理解は慣れたあとにやってくるはずである。
CREATE TABLE……、とここまで書いて手が止まる。 どんなカラムが要るのだろう。実際に運用してみないと分からないから……。 そのような経験はないだろうか。
分からなくても問題ない。
主キー、つまり表現したい対象を一意に表すものさえ分かっていればよい。 「必要なカラムをあとからどんどん追加したテーブルに変更していく……」 というのはほとんどの場合「ヘボい」設計で、うまく設計すればそうはならない。 安心してカラム数の少ないテーブルで「CREATE TABLE」して構わない。 たいていの場合は、カラム追加で対処せず「テーブルを追加してJOIN」で対処する。 初期段階で本当に必要だと思うカラムだけに絞ってテーブル作成すればよい。
SQLiteであれば主キー指定をしなくても何とかなるので、 まずはなしでやってみるとよい。実際にデータを登録してみて、 「重複があってはいけない組み合わせ」が分かれば、自ずと主キーも見えてくる。
本稿で作成するテーブルでは、PRIMARY KEY を指定していないものもある。詳しくは実際の例を参照してほしい。
テキストファイルでのデータ管理に馴染んでいると、grep や sed を用いた正規表現検索の偉力を知っている。その状況でデータベースから SELECT で値を索くことを想像すると、(標準では)正規表現の使えない SQL に対して物足りなさを感じることはないだろうか。
結論から言うと、たいていの場合問い合わせに正規表現は要らない。 なぜか。他のスクリプト言語等で、正規表現を使う場面を考えてみよう。 主に以下のような状況で利用している。
前者は縦軸から選び取るための検索、 後者は横軸から意味あるフィールドを切り取るための検索と捉えることもできる。 このうち横軸からの検索は SQL ではほとんど必要ない。なぜなら、 データベースに格納する時点でフィールド分割や、 入力値の検査は済んでいるからである。 取り出したものが何を意味するかは確定しており、 それをさらに分割するとなれば固定的な文字列パターンマッチで対処できる。 たとえば email アドレスからドメインパートを切り出す処理の場合を考える。 テキストファイル処理であれば、無関係な文字列に埋もれていることもあるのでまず 1行の中から email と思われるパターンを指定し、さらにそこから @ 以降を切り出す処理をする。 データベースを利用した処理では、あらかじめそのカラムには email アドレスしか入れない処理が前提であるため、得られた値を @ 文字の前後で切るだけでよく、これは SQLite3 の instr(), substr() 関数を使うか、シェルスクリプトの変数展開の ## を利用して ${x##.*@} のようにすればよく、いずれにしても正規表現は必要ない。
続いて前者、縦軸検索だが、 こちらはデータベースからの全文検索などでは正規表現が欲しくなることは 確かにある。その場合 LIKE 演算子か、SQLite3 固有の GLOB 演算子で代用するしかない。
| LIKE | GLOB | |
|---|---|---|
| 任意の1文字にマッチ | _ | ? |
| 任意の文字列にマッチ | % | * |
| 文字クラス | なし | [文字群指定] |
GLOB はシェルのワイルドカード指定と同じパターンマッチング規則を LIKE と同じ文法で指定できる。文字クラスの指定が可能な分幅広い検索ができる。 ただし、LIKE も GLOB も対象カラムの全走査が必要になるため、 データサイズに比例した検索時間を要する。つまり、遅い。 とはいえ、数MB単位の検索では一瞬で結果が返ってくるので最初はあまり気にせず 使いつつ、検索対象カラムが GB 単位になったら別手段を検討する必要があることを意識しておけばよいだろう。 なお、「高速全文検索」で GB 単位の全文検索でも高速処理できるFTSモジュールを紹介しているので 気になる場合は先に参照するとよい。
もう一つ、正規表現はそんなに「賢い」だろうか。
たとえば、「Aは含まれるがBは含まれない」といった肯定と否定を同時指定できないし、数値の範囲を指定できるわけではない。 たとえば「2005年から2012年の範囲」のような数値範囲は指定できない。 もちろん文字列のまま無理矢理以下のように指定できなくはない。
egrep "20(0[5-9]|1[012])" files...
のようにすればよいのだが、これが数値の持つ連続性を全く活かせていない 間に合わせのやり方であるのは理解できるだろうか。「1998年から2012年」と なったら縦棒で区切って、パターン指定文字列を追加しなければならない。 SQLであれば年をカラムに保存しておいて
SELECT text FROM diary WHERE year BETWEEN 1998 AND 2012;
などのように条件の論理を正確に反映させた検索ができる。 このように、本質的な条件指定が論理式として記述可能なのは SQL に限ったことではなく、構造化された他のフォーマットでも同じことである。 正規表現は構造化されていないテキストから欲しい部分を欲しいときに探す 事後アプローチに適しているが、SQL はテキスト以外の部分が事前に構造化された状態で格納されているところから何かを引き出すものであるため、 文字列検索に高い表現力が必要な局面は限られる。 「正規表現が欲しい」と思う場合のいくつかは、正規表現中毒の症状であり 本質的な解決を取らずに済ませたいだけの可能性があることを考慮しなければならない。
また、全文検索的な機能が必要な場合でも、極端に大きなテキスト集合でなければ SQLite からの出力を grep 等で振り分けて利用するのでも十分実用的な速度は得られ、 そうした「組み合わせ処理」はシェルスクリプトの得意分野である。 以上のように正規表現検索関数がないことは多くの場合障害とならない。 逆にいえば、正規表現等の道具を使って事前処理を終わらせた結果を RDB に入れるのであるから、ほとんどの出番が終わった状態ともいえる。
実際のところ、SQLite3 に正規表現ライブラリを組み込んで、REGEXP 演算子を導入することもできるのでどうしても必要なら組み込み可能である。 ただし、正規表現は文字列しか探せないわりには文法解析器が複雑で巨大である。 PCRE ライブラリ(※)が大きく、時おりセキュリティホールを生み出していることは このことを顕著に表していると言ってよい。セキュリティが重視される場所では 正規表現ライブラリを使わないという選択も重要である。
シェルスクリプトとテキストファイルだけで対処していた問題に SQL を導入すると何が変わるのだろう。「データがバイナリファイルになってしまう…」 という不安感はやはり拭えないのではなかろうか。
ある程度まとまった量を持つデータ処理を行なう スクリプトを作成した経験があれば、 データをテキストファイルで保持することの優位性として 以下のことを実感しているはずである。
何物にも代えがたいメリットだが、 以下のような点はテキストファイル処理では心もとない。
テキストファイルに同時更新が来た場合の排他処理を 自前で作る必要がある。どんな環境にも依らない確実な 排他ロジックを自前で作り込むのはそれなりに面倒で経験を要する。 また、親子関係のある2つのデータを消すときに、 親データを消したときに C-c を押して処理を停めても整合性を保つようにするのは難しい。 慣れればできるかもしれないが、更新可能性のあるファイルに 対する処理すべてにロジックを仕込んだり、それらすべてに 負荷テストを掛けて安全性・完全性を確認するのは煩雑である。 煩雑さは手抜きを誘発する。
たとえばシステムのパスワードファイルは分かるだろうか。 /etc/passwd を見れば分かるようにコロン区切りの単純な書式で、 特定ユーザの特定フィールドを更新するのはテキストエディタで容易に行なえる。 しかし、シェルスクリプトなどから更新するにはテキストエディタは 適さないため、非対話的にフィールドを更新するための usermod コマンドが多くのシステムに用意されている(※)。
FreeBSDの場合は pw コマンド。
これを用いることでシェルスクリプトからも 安心してデータの更新が行なえる。これが自分で設計した構造の テキストファイルならどうだろう。コロン区切りのフラットな設計でなく、 親子関係を持つ構造の場合に、特定のフィールドを安全に更新できるコマンドが あるか、というと、それは自分で設計するか、 なんらかのデータ構造操作言語を利用するしかない。 RDB の利用は、データの格納安全性が得られることだけでなく、 値操作に定式化された言語が使えるのが大きなメリットである。
当たり前のことだが、たとえばパスワードファイルから あるユーザの登録行を消したらそのユーザは削除されるが、 そのユーザが所有していたファイルがすべてのファイルシステムから 消えるわけではない。別の例として、 ある科目を履修している学生全員の平常成績を 1つのテキストファイルに保存していたとする。 そのうちの1人が履修解除をしたので履修者名簿ファイルから削除したとしても その者に関する平常成績データが自動的に消えるわけではない。 つまり、複数のデータ集合間で関連性を持つ値に変更が生じた場合の処理は、 自前で行なわねばならない。テキストファイルからの一括置換や一括削除は さほど難しくないが、整合性を保つ責任を、 システム運用中にずっと負い続けなければならない。 それが当たり前と感じていると気づかないものだが、 RDB のテーブル設計を効果的に行なえば 「マスターレコードの何かを削除・修正すると それに関連するものをすべて自動的に削除・修正する」といった整合性保証を データベース側で取ってくれるため、作成するスクリプトの行数を 大幅に減らすことができ、その分バグのないシステムを構築しやすくなる。
以上のことを裏返すとそれがSQL化して得られるメリットである。
最後の制約利用はSQLでなければ得られない大きなメリットで、 これを使うことでスクリプトの記述量が大幅に減り、 結果としてバグの低減につながる。
SQLのメリットは分かったとして、 それを何故わざわざシェルスクリプトで使うかという点について示したい。 答は単純で、
SQLの世界だけで考える癖がつく
からである。多くのスクリプト言語では、SQL 問い合わせ結果を配列やハッシュで受け取り、 様々な繰り返し処理をきめ細かに書けるのだが、 シェルスクリプトで同じことをやろうとすると記述が難解な上に 書けたとしても処理速度が遅くなるのが関の山である。SQL の方で頑張るしかないのである。
SQL ではなく手続型のプログラミング言語の世界で考えてしまう悪い例を示そう。 次の例は手続型言語から SQL を扱う場合にダメ出しされる典型的なプログラムである (言語は筆者がこの場で適当にでっちあげたもので、 必要な例外処理等は一切省略してある)。
db = DB.open("data.db"); /* ここから3月4月のものを探す処理 *
result = new(Array); /* 空の配列を作る */
list = db.query("SELECT * FROM tbl"); /* tblからすべての行を得る */
for row in list { /* 1行ずつ取り出す */
month = row["month"]; /* month カラムの値を得る */
if (Regexp.match(/[34]月/, month) { /* monthが 3月か4月なら */
list.add(row["content"]); /* list配列に content カラム追加 */
}
}
/* 以後、list 配列の値で処理を続ける */
このように「SELECT *」して あとは適当に得意なプログラミング言語の世界で考えるものをしばしば見掛ける。 「SELECT *」がよくないことは様々な文献に書かれているのでさすがにそれはないとしても、 ある問い合わせで得られた結果を変数に代入し、 その変数を用いて次の問い合わせを行なうような処理は SQL の世界で考えることができていないことの現れである。 仮想的なコード例で示すと以下のようなものである。
細切れに問い合わせを行なうダメな例
users = db.query("SELECT userid FROM orders WHERE itemid = '?';", iid);
if (users != Null) { /* 商品ID iid の購入者一覧が空でなければ */
email = new(Array); /* emailを入れる配列を初期化 */
for uid in users { /* 該当ユーザを1人ずつ uid に入れて繰り返す */
e = db.query("SELECT email FROM customers WHERE userid = '?';", uid) {
email.add(e); /* uidをもつ顧客のemailをリストに追加 */
}
}
sendKeppinMail(email) /* 該当者全員に欠品のお詫びメイルを送信する */
最初の問い合わせで得られた結果を users 変数に入れて、 それを次の問い合わせのループの種として利用している。 SQL ライブラリを持つプログラミング言語からは容易なこの処理も、 シェルスクリプトでは、問い合わせで一度取り出した文字列値を再度次の SQL に投入するには、文字列中のクォートのエスケープ処理をやりなおす処理が必要で神経を遣う(プレースホルダが使えない)。 SQL とシェルの世界で値を出し入れせず、SQL の世界のままJOIN操作した方が断然楽で、結果として SQL のもつ集合論理の世界に慣れ親しむことになる。 これは将来他の言語、他の RDBMS を使うときに役立つ経験となるだろう。 ちなみに上記仮想コードの処理を「SQL で頑張るシェルスクリプト」で書くと以下のようになる。
iid=`echo "$iid" | sed "s/'/''/g"` # ' があれば '' に置換(エスケープ処理)
sendKeppinMail $(sqlite3 -separator ' ' \
"SELECT email FROM customers
WHERE userid IN (SELECT userid FROM orders WHERE itemid='$iid');")
このSQL文がシェルスクリプトでなければ書けないという訳ではもちろんなく、 シェルスクリプトで「細切れ問い合わせ」のようなまずいプログラムを 書くことがかなり困難、ということである。
余談だが、研究室でシェルスクリプトの有用性について雑談をしていて、 ほとんどのことはシェルスクリプトで対処可能でCGIも作れると言ったところ、 学生が「SNSサーバとかもできちゃいますか」と質問してきた。 バイナリデータの受け取りができないので無理だろうと直感したのでその場では 「さすがにそれは厳しい」と答えたのだが、本当に作れないのか試してみたくなった。 そこで、POSTされたバイナリデータの処理だけを別プログラムに任せてみたところ、 案外スムーズにでき上がってしまった。 それまで SQL は Ruby から利用するのが主であったが、 シェルスクリプトから利用することで、まさに上記のような発見が得られた。 シェルスクリプトこそ SQL を勉強するための優良な環境である。
yuuji@koeki-u.ac.jp