この章の目標

カタログ型データベースの基盤設計

RDBを用いたデータベースシステムの導入を考える場合、 まずそのテーブル設計に頭を悩ますこととなる。 ここでは、厳密なテーブル設計をほとんどせずに 思いつきに近い設計のまま試験運用が開始できる形態を紹介し、 それをシェルスクリプトに組み込むための手法へと踏み込んでいく。

カタログ型データベースの位置付け

ここでいう「カタログ型」とは便宜上指し示したもので、 とくに固定的な概念を示すものではない。たとえば、 スポーツ選手あるいはアニメ・ゲームのキャラクタの名鑑のような 写真と主な属性を集めたデータベースを示す。 利用者から見た参照したい主体が1種類であるものがこれに該当し、 概ね以下の性質を持つものである。

たとえば、携帯電話端末の住所録のようなものを Web アプリケーション化し、複数人で参照・編集できるようにしたい場合が適合する。 そのようなデータは、表計算形式で管理するなら以下のようになる。

表計算風に格納した住所録例
氏名シメイ住所帰省先等電話番号1電話番号2…備考
公益太郎コウエキタロウ酒田塩山
横浜		0234-45-XXXX---...鶴子集合
飯森花子イイモリハナコ酒田六郷0234-45-YYYY0187-XX-XYYX
080-IIII-BBBB		...天然水

見るからにまずい表で「目で見て分かれば何でもよい」 でデータを入れるとこのようになる。既に分散共有ディレクトリサービスなどを 導入・運用している組織では問題にならないが、そうでない場合は 個人個人がこのようなデータを持ち、 最新の正確な情報に保つのが極めて困難な状態に陥りがちである。 それを打破すべく、集中管理されたデータベースの導入を計画するものの、 まず見積りの段階で「何が必要なデータか」の最終結論を出すことが 誰の立場からも難しいことに直面する。さらには、 業者に見積りを依頼するだけでもコストが掛かる上に、 本当にデータベース化の効果がその組織に対して出るのかは、 未経験の状態では断言しがたいというケースもあろう。

ここでは、「まず簡単なもので運用してみる」ことを念頭に、 カタログ型のデータベースを汎用的に設計することを考える。

なお、本章で検討する設計の形は、 上記のような状況で「必要な属性の分析を行なう段階」でのみ有効で、 将来的に本格RDBシステムでの運用時にも適用できる類ではない。 詳しくは「可変属性列について」で示す。

Key-Valueストアのエミュレーション

Key-Valueストア とはリレーショナルデータベースと対比されて紹介されることの多い データ管理モデルで、データの構造を「キー」とそれに対する「値(value)」 の組みで保存するものである。多くのプログラミング言語に存在する 連想配列、 ハッシュ、辞書 などと呼ばれるデータ構造を基本とする。 カラム設計をせずに使うことができるため、 記憶させたいものが発生した瞬間にすぐデータ格納できる。 ここではSQLite3の特性を活かして、テーブルの組み合わせにより Key-Valueストア感覚で構築するデータベースを考える。

連想配列の例

まず、連想配列のしくみについて簡単に紹介する。 すでに馴染んでいる場合は斜め読みで次節に進んで構わない。

ここでは連想配列を擬似的な言語で表すことを試みる。

{KEY1:VALUE1, KEY2:VALUE2, KEY3:VALUE3, ...}

のように、コロン区切りの組みの集合を中括弧で括って表すものとする。 

taro   = {'氏名':'公益太郎', 'シメイ':'コウエキタロウ', '住所':'酒田'}
hanako = {'氏名':'飯森花子', 'シメイ':'イイモリハナコ', '住所':'酒田'}

キーに対する値は変数に添字指定して取り出すものとする。

taro['氏名']
  → '公益太郎'
hanako['氏名']
  → '飯森花子'

連想配列では、代入すると古い値は上書きされる。

taro['住所']='鶴岡'		# 代入
taro['住所']			# 参照
  → '鶴岡'			# 上書きされる

一つのキーに対しては一つの値が格納される。 では、一つのキーに対して複数の値を入れたい場合はどうしたらよいだろう。 そのようなときは Value として配列(リスト)を代入することにする。 配列の値を以下のように表すこととする。

[ ITEM1, ITEM2, ITEM3, ... ]

上記の例の hanako の '電話番号' キーに複数の値を登録する。'電話番号' に対応する Value には配列値を代入する取り決めとして以下のように代入する。

hanako['電話番号'] = ['0234-45-YYYY', '0187-XX-XYYX', '080-IIII-BBBB']

この代入により hanako の値は以下のようになる。

hanako
  → {'氏名':'飯森花子', 'シメイ':'イイモリハナコ', '住所':'酒田'
      '電話番号':['0234-45-YYYY', '0187-XX-XYYX', '080-IIII-BBBB']}

このように連想配列は、ある主体の持つ属性を自由に設定でき、 名鑑や住所録的なものに柔軟に対応できる。 このような関係性を SQLite で格納するには Key と Value に相当するカラムを定義すればよい。SQLite はカラムに固定的な型を持たせる必要がない。この性質により 任意の Key-Value ペアを格納することができる。他の RDBMS ではありえない設計だが、そもそもシェルスクリプトでは基本的にすべてを 文字列の形式で保持するため、型の混在は問題にならない。

単一値のテーブル設計

まず、値の個数が0個か1個となる項目を保持するテーブルを考える。 格納されているイメージを示すと以下のようになる。

単一値を持つKey-Valueテーブル
idkeyval
taro氏名公益太郎
taroシメイコウエキタロウ
taro住所酒田
hanako氏名飯森花子
hanakoシメイイイモリハナコ
hanako住所酒田

値が一つに定まる、ということはある特定の id と key の組み合わせが一意であることにほかならないので、この組み合わせに UNIQUE 制約をつければよい。以下の例では、データベースファイルを addrbook.sq3 とし、 sqlite3 コマンド起動時に、FOREIGN KEYS が有効になるようにしている。

sqlite3 -cmd 'PRAGMA FOREIGN_KEYS=on;' addrbook.sq3
CREATE TABLE keyvalue_1(id, key, val, UNIQUE(id, key));

この制約下で、tar['住所'] = '鶴岡' に相当する上書きを行なう。

REPLACE INTO keyvalue_1 VALUES('taro', '住所', '鶴岡');

「'taro', '住所'」の組み合わせは重複を許されないため、 該当レコードは以下のように上書きされる。

idkeyval
taro住所鶴岡

なお、id、key に対する UNIQUE 制約だけでなく、 異常値を自動的に排除する制約も付けられるものは付けておくべきである。 たとえば、id も key も空文字列は許されないので

CREATE TABLE keyvalue_1(
  id NOT NULL, key NOT NULL,
  UNIQUE(id, key),
  CHECK(id > ''), CHECK(key > ''));

シェルスクリプトで INSERT 文を発行するときに未定義変数を渡しても "" (空文字列)となるため、空文字列かどうかの検査しか効き目がないが、 他のプログラミング言語では変数値未定義がカラム値 NULL として処理されるため、カラム制約に NOT NULL を指定する習慣を付けておくと異常値の検出に役立つ。

のような制約も有効である。ただし、のちに id、key カラムには、決まった値しか入れられないよう外部キー制約を付けることになるので、 ここでは UNIQUE 制約のみ付しておく。次で述べる多値テーブルも同様である。

多値のテーブル設計

いっぽう、一つのキーに対する値が任意個数あるうるものは id、key に対する UNIQUE 制約をなくせばよい。ただし、全く制約なしにすると REPLACE INTO で同じ値を入れたときにそれが何度も格納されることになり、 たいていの場合それは無駄である。 それを防ぐため、id、key、val の3つに対して UNIQUE 制約をつける。

CREATE TABLE keyvalue_n(id, key, val, UNIQUE(id, key, val));

このようなスキーマを持つテーブルに、 あらかじめ以下のように値が格納されていたとする。

多値を許すKey-Valueテーブル
idkeyval
taro電話番号0234-45-XXXX
hanako電話番号0234-45-YYYY
hanako電話番号0187-XX-XYYX

この状態から以下のようにSQLを発行する。

REPLACE INTO keyvalue_n VALUES('hanako', '電話番号', '0187-XX-XYYX');
REPLACE INTO keyvalue_n VALUES('hanako', '電話番号', '080-IIII-BBBB');

1行目のVALUESでは既に格納されている電話番号を入れ、 2行目では新しい値を入れている。すると以下のようにテーブルが更新される。

idkeyval
taro電話番号0234-45-XXXX
hanako電話番号0234-45-YYYY
hanako電話番号0187-XX-XYYX
hanako電話番号080-IIII-BBBB

既存のものと同じ場合はそのままで、新規のものだけ挿入される。

外部キーの作成

値の個数の違いを表すために UNIQUE 制約を用いた。 さらに一つ制約が必要である。これまでも利用した外部キー制約で、 2つのテーブルのidカラムの値は、データベースに登録されている主体であり、 ある1つのidを削除したら、それをidに持つすべてのレコードを削除する必要がある。 そのために、idのみを保持するテーブルを作成し、上記2つのテーブルにも外部キー制約を加える。 SQL文は以下のとおり。

CREATE TABLE ids(id PRIMARY KEY);
CREATE TABLE kv_1(
  id, key, val,
  UNIQUE(id, key)
  FOREIGN KEY(id) REFERENCES ids(id));
CREATE TABLE kv_n(
  id, key, val,
  UNIQUE(id, key, val)
  FOREIGN KEY(id) REFERENCES ids(id));

このように作成した3テーブルに、表「表計算風に格納した住所録例」に示したデータを格納するための SQL 文を以下に示す。

-- 主キーの格納
INSERT INTO ids VALUES('taro');
INSERT INTO ids VALUES('hanako');
-- 値が一つに定まるものの格納
REPLACE INTO kv_1 VALUES('taro', '氏名', '公益太郎');
REPLACE INTO kv_1 VALUES('taro', '住所', '酒田');
REPLACE INTO kv_1 VALUES('hanako', '氏名', '飯森花子');
REPLACE INTO kv_1 VALUES('hanako', '住所', '酒田');
-- 複数の値を許すものの格納
REPLACE INTO kv_n VALUES('taro', '帰省先等', '塩山');
REPLACE INTO kv_n VALUES('taro', '帰省先等', '横浜');
REPLACE INTO kv_n VALUES('taro', '電話番号', '0234-45-XXXX');
REPLACE INTO kv_n VALUES('taro', '備考', '鶴子集合');
REPLACE INTO kv_n VALUES('hanako', '帰省先等', '六郷');
REPLACE INTO kv_n VALUES('hanako', '電話番号', '0234-45-YYYY');
REPLACE INTO kv_n VALUES('hanako', '電話番号', '0187-XX-XYYX');
REPLACE INTO kv_n VALUES('hanako', '電話番号', '080-IIII-BBBB');
REPLACE INTO kv_n VALUES('hanako', '備考', '天然水');

実際にテーブルに格納されているイメージは以下のようになる。

idsテーブル格納状態
id
taro
hanako
kv_1テーブル格納状態
idkeyval
taro氏名公益太郎
taro住所酒田
hanako氏名飯森花子
hanako住所酒田
kv_nテーブル格納状態
idkeyval
taro帰省先等塩山
taro帰省先等横浜
taro電話番号0234-45-XXXX
taro備考鶴子集合
hanako帰省先等六郷
hanako電話番号0234-45-YYYY
hanako電話番号0187-XX-XYYX
hanako電話番号080-IIII-BBBB
hanako備考天然水

kv_1テーブルとkv_nの和集合が求める住所録となる。id='taro' に関する値を取り出してみる。和集合には UNION ALL を利用する。

SELECT * FROM kv_1 WHERE id='taro'
UNION ALL
SELECT * FROM kv_n WHERE id='taro';
taro|住所|酒田
taro|氏名|公益太郎
taro|備考|鶴子集合
taro|帰省先等|塩山
taro|帰省先等|横浜
taro|電話番号|0234-45-XXXX

項目名で整列した出力

データベースへの格納には順番という概念はなく、 上記出力例を見て分かるように各属性がバラバラに出てくる。 これを整理するには、属性名の順序を持つ表を用意して JOIN 操作すればよい。たとえば、「氏名、住所、帰省先等、電話番号、備考」 の順に揃えたければ、この7つの値をカラムに持つテーブルを用意して UNION ALL で得られる表と JOIN 操作する。

属性名リスト keys
rowidkey
1氏名
2住所
3帰省先等
4電話番号
5備考
6特記事項

×

id='taro' で得られる和集合出力
idkeyval
taro住所酒田
taro氏名公益太郎
taro備考鶴子集合
taro帰省先等塩山
taro帰省先等横浜
taro電話番号0234-45-XXXX

属性名と和集合のJOIN

上記の2つの表の結合は、属性名をすべて含む形にしたいので keys テーブルを左とする LEFT JOIN を用いる。

SELECT b.* val FROM keys a LEFT JOIN 和集合出力 b;

実際のテーブルを以下のように作成する。

keys テーブルの作成

CREATE TABLE keys(key UNIQUE);	-- rowidは自動付加されるので宣言不要
REPLACE INTO keys(rowid, key) VALUES(1, '氏名');
REPLACE INTO keys(rowid, key) VALUES(2, '住所');
REPLACE INTO keys(rowid, key) VALUES(3, '帰省先等');
REPLACE INTO keys(rowid, key) VALUES(4, '電話番号');
REPLACE INTO keys(rowid, key) VALUES(5, '備考');
REPLACE INTO keys(rowid, key) VALUES(6, '特記事項');
-- CREATE TABLE直後にこの順にINSERTすればrowidは連番となるので
-- INSERT INTO keys VALUE('氏名'); などとしても通常は問題ない

なお、上記の例では属性名リスト keys に追加の属性名「6 特記事項」 を加えて、属性値が未登録の場合の対処法を考慮する例に用いる。

図属性名と和集合の JOIN に相当する操作を行なう。 

SELECT b.id, a.key, b.val FROM keys a NATURAL LEFT JOIN (
       SELECT * FROM kv_1 WHERE id='taro'
       UNION ALL
       SELECT * FROM kv_n WHERE id='taro') b ORDER BY a.rowid;

NATURAL JOIN は、2つのテーブル間で同じカラム名のものが 同じ値となる結合条件で JOIN を行なう。 この例の場合、2つのテーブルで共通するカラムは id であるから2テーブルの id が等しい条件での左結合が行なわれ、 出力結果は以下のようになる(表形式で示す)。

b.ida.keyb.val
taro氏名公益太郎
taro住所酒田
taro帰省先等塩山
taro帰省先等横浜
taro電話番号0234-45-XXXX
taro備考鶴子集合
特記事項

key='特記事項' に対応する値が b (和集合で求めた表)にないため、b から取得している id、val が空欄となっている。対応値不在でも id が空欄になるのを防ぐには、keys テーブルと ids テーブルでの JOIN を利用する。

SELECT * FROM ids, keys WHERE id='taro' ORDER BY keys.rowid;
taro|氏名
taro|住所
taro|帰省先等
taro|電話番号
taro|備考
taro|ひとこと

この結果を LEFT JOIN の左辺と置き換えて、住所録全体を得る。 問い合わせ文は以下のとおり。

SELECT a.id, a.key, b.val
FROM	(SELECT keys.rowid,* FROM ids, keys WHERE id='taro') a
	NATURAL LEFT JOIN 
	(SELECT * FROM kv_1 WHERE id='taro'
	 UNION ALL
	 SELECT * FROM kv_n WHERE id='taro') b ORDER BY a.rowid;

この問い合わせによって得られるものを示す。

a.ida.keyb.val
taro氏名公益太郎
taro住所酒田
taro帰省先等塩山
taro帰省先等横浜
taro電話番号0234-45-XXXX
taro備考鶴子集合
taro特記事項

たとえばこの問い合わせから id='taro' の条件指定を除去したものを VIEW 定義しておけば、住所録的な使用に適したものとなるだろう。

可変属性列について

連想配列的に設計したテーブルのような設計は EAV(Entity-Attribute-Value)と呼ばれ、RDB の持つ利点のいくつかを利用できない。 シェルスクリプトでのプロトタイピングを終えて、 大規模データベースシステムの導入の検討に進むことになった場合、 このままの設計では一般的なRDBに馴染まない。EAV が選択されるのは以下のような場合に限られる。

このような性質は、カタログ型のデータベースや、電子カルテ的なものに 共通したものである。この性質が明らかな場合は、NoSQL と呼ばれる RDB 以外のデータベースバックエンドを採用する必要も考慮した方がよい。 ただし、場合によってはシェルスクリプト + SQLite3 で構築したプロトタイプシステムで十分と判断される状況もありうる。 そのような場合、シェルスクリプトとの親和性の高さを考慮すると、NoSQL よりも SQLite3 の方がうまく機能するものを作りやすい。SQLite3 はカラムの型を自由にできることと、 コマンドラインインタフェースが充実していること、 標準的文法が使えることから、 シェルスクリプトから利用できる Key-Value ストアとしても十分によい選択肢となりえる。

練習問題: でたらめな属性値登録の抑制

上述のデータベースでは属性値も key カラムの値として設定した。 このため、以下のようにして任意の属性値をもつものを登録できる。

INSERT INTO kv_n VALUES('taro', '好きな温泉', 'ゆりんこ');
INSERT INTO kv_n VALUES('taro', 'すきな温泉', 'ゆりんこ');

無節操な属性名の登録を防ぐにはどうしたらよいか。

練習問題: 解答例

kv_1 および kv_n テーブルの key カラムに制約をつければよい。 属性として許す文字列一覧は属性名リストをもつテーブル keys にあるので、 それを外部キーとするように kv_1、kv_n テーブルを構築する。 

DROP TABLE IF EXISTS kv_1;
DROP TABLE IF EXISTS kv_n;
CREATE TABLE kv_1(
  id, key, val,
  UNIQUE(id, key),
  FOREIGN KEY(key) REFERENCES keys(key),
  FOREIGN KEY(id) REFERENCES ids(id));
CREATE TABLE kv_n(
  id, key, val,
  UNIQUE(id, key, val),
  FOREIGN KEY(key) REFERENCES keys(key),
  FOREIGN KEY(id) REFERENCES ids(id));

この状態で以下のように、不正属性値を入れてみる。

sqlite3 -cmd 'PRAGMA FOREIGN_KEYS=on;' addrbook.sq3
INSERT INTO kv_n VALUES('taro', '好きな温泉', 'ゆりんこ');
Error: FOREIGN KEY constraint failed

このように、制約から外れる属性値は入れられなくなる。

yuuji@koeki-u.ac.jp