履歴、世代、そして削除についての究極の疑問の答え

履歴や世代、データの削除すべてをリレーショナルデータベースだけで扱う方法。実用上はオーバーキルになることも多いと思われるので、あくまでもインデックスを効かせ整合性制約をすごくちゃんとやるとしたらこうなるよ、程度の知識として捉えてください。

前提

モデル

以下のような多対多のユーザとグループの簡単なモデルを考える。

これを各々のテーブルについて履歴を管理できるよう、トランザクション番号txを付ける。

したがって、各テーブルは従来の主キー + tx が、主キーになる。txは、これらのテーブルに関するいかなる変更でもインクリメントされる (txは時系列オーダーであればよいのでタイムスタンプやULIDなどを用いても良い)。

txと各データの関連をもつtxsテーブルを用意する。(オリジナルテーブルとデータ重複しているように見えるが削除のときは、オリジナルテーブルにはデータ入らずtxsだけにインサートされる)

ある時点での整合性のとれたデータセットを取り出しやすくするためには、以下のようなtagsテーブルを各々用意する。

最新のデータセットを取得するために、それに「HEAD」タグをデータに登録する。このテーブルのみINSERTに加えて、タグの付け替え時にUPDATE操作がされることになる。

データとSQL

TX=1 ユーザ"kawasima"とグループ"admin"を作り所属させる

code:sql

INSERT INTO users(id, tx, name) VALUES (1, 1, 'kawasima');

INSERT INTO groups(id, tx, name) VALUES (1, 1, 'admin');

INSERT INTO memberships(user_id, group_id, tx) VALUES (1, 1, 1);

INSERT INTO user_txs(user_id, tx) VALUES(1,1);

INSERT INTO group_txs(group_id, tx) VALUES(1,1);

INSERT INTO membership_txs(user_id, group_id, tx) VALUES(1,1,1);

INSERT INTO user_tags(user_id, tag, tx) VALUES (1, 'HEAD', 1);

INSERT INTO group_tags(group_id, tag, tx) VALUES (1, 'HEAD', 1);

INSERT INTO membership_tags(user_id, group_id, tag, tx) VALUES (1, 1, 'HEAD', 1);

現在の状態は「HEAD」タグを参照すればよいので以下のSQLで取得できる。

code:sql

SELECT U.name, G.name

FROM users U

JOIN user_tags UT ON U.tx = UT.tx AND U.id = UT.user_id AND UT.tag = 'HEAD'

JOIN memberships M ON M.user_id = U.id

JOIN groups G ON M.group_id = G.id

JOIN group_tags GT ON G.tx = GT.tx AND G.id = GT.group_id AND GT.tag = 'HEAD'

JOIN membership_tags MT ON M.user_id = MT.user_id

AND MT.group_id = M.group_id AND MT.tx = M.tx AND MT.tag='HEAD'

name | name

----------+-------

kawasima | admin

(1 row)

TX=2 グループ”sales”を作りユーザ"kawasima"を所属させる

code:sql

INSERT INTO groups(id, tx, name) VALUES (2, 2, 'sales');

INSERT INTO memberships(user_id, group_id, tx) VALUES (1, 2, 2);

INSERT INTO group_txs(group_id, tx) VALUES(2, 2);

INSERT INTO membership_txs(user_id, group_id, tx) VALUES(1,2,2);

INSERT INTO group_tags(group_id, tag, tx) VALUES (2, 'HEAD', 2);

INSERT INTO membership_tags(user_id, group_id, tag, tx) VALUES (1, 2, 'HEAD', 2);

code:sql

(SQL略)

name | name

----------+-------

kawasima | admin

kawasima | sales

(2 rows)

TX=3 グループ"admin"からユーザ"kawasima"を外す

membershipsはHEADから見えなけばいいだけなので、何もデータ操作しない。txsへのINSERTとHEADタグの付け替えだけをおこなう。

code:sql

INSERT INTO membership_txs(user_id, group_id, tx) VALUES(1, 1, 3);

UPDATE membership_tags SET tx = 3 WHERE user_id =1 AND group_id=1 AND tag = 'HEAD';

code:sql

(SQL略)

name | name

----------+-------

kawasima | sales

(1 row)

タグの付いていない任意の世代のデータセットの取得

これは効率のよいクエリにはならないが、以下のようなSQLで取得できる

code:sql

SELECT U.name, G.name

FROM users U

JOIN (SELECT user_id, MAX(tx) AS tx FROM user_txs WHERE tx < 3 GROUP BY user_id) UTX

ON U.tx = UTX.tx AND U.id = UTX.user_id

JOIN memberships M ON M.user_id = U.id

JOIN groups G ON M.group_id = G.id

JOIN (SELECT group_id, MAX(tx) AS tx FROM group_txs WHERE tx < 3 GROUP BY group_id) GTX ON G.tx = GTX.tx AND G.id = GTX.group_id

JOIN (SELECT user_id, group_id, MAX(tx) AS tx FROM membership_txs WHERE tx < 3 GROUP BY user_id,group_id) MTX ON M.user_id = MTX.user_id AND MTX.group_id = M.group_id AND MTX.tx = M.tx

code:sql

(SQL略)

name | name

----------+-------

kawasima | admin

kawasima | sales

(2 rows)