PostgreSQLのルールシステムを使ってDELETEが発行されたときに論理削除をするようにしてみた。

Storm（のようなORM）を使うときに、store.remove(object)と削除は削除として実行できるからよい感じ。

Gist:229566

-- PostgreSQLのルールシステムを使った論理削除の例
-- 削除フラグと削除日時を保持するフィールドを持ったテーブルを作成
CREATE TABLE a(
  id serial PRIMARY KEY,
  value text,
  deleted boolean DEFAULT False,
  deleted_on timestamp DEFAULT 'infinity'
);
-- DELETEが実行されたら代わりに削除フラグを立てて削除日時をセットするルール
CREATE RULE a_delete_rule AS ON DELETE TO a
  DO INSTEAD
  UPDATE a SET deleted=True, deleted_on=CURRENT_TIMESTAMP
      WHERE id = OLD.id;

-- ここからはテストデータを投入、削除
-- 三件件挿入
INSERT INTO a(value) VALUES('a');
INSERT INTO a(value) VALUES('b');
INSERT INTO a(value) VALUES('c');
-- 確認
SELECT * FROM a;
-- 削除してみる
DELETE FROM a WHERE id>1;
-- 確認 削除されないで、削除フラグと削除日時が更新されている
SELECT * FROM a;
-- 後始末
DROP TABLE a;

実行すると、こんな感じになります。

pguser=# — PostgreSQLのルールシステムを使った論理削除の例
pguser=# — 削除フラグと削除日時を保持するフィールドを持ったテーブルを作成
pguser=# CREATE TABLE a(
pguser(# id serial PRIMARY KEY,
pguser(# value text,
pguser(# deleted boolean DEFAULT False,
pguser(# deleted_on timestamp DEFAULT ‘infinity’
pguser(# );
NOTICE: CREATE TABLE will create implicit sequence “a_id_seq” for serial column “a.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “a_pkey” for table “a”
CREATE TABLE
pguser=#
pguser=# — DELETEが実行されたら代わりに削除フラグを立てて削除日時をセット
pguser=# CREATE RULE a_delete_rule AS ON DELETE TO a
pguser-# DO INSTEAD
pguser-# UPDATE a SET deleted=True, deleted_on=CURRENT_TIMESTAMP
pguser-# WHERE id = OLD.id;
CREATE RULE
pguser=#
pguser=# — 三件件挿入
pguser=# INSERT INTO a(value) VALUES(‘a’);
INSERT 0 1
pguser=# INSERT INTO a(value) VALUES(‘b’);
INSERT 0 1
pguser=# INSERT INTO a(value) VALUES(‘c’);
INSERT 0 1
pguser=#
pguser=# — 確認
pguser=# SELECT * FROM a;
id | value | deleted | deleted_on
—-+——-+———+————
1 | a | f | infinity
2 | b | f | infinity
3 | c | f | infinity
(3 行)

pguser=#
pguser=# — 削除してみる
pguser=# DELETE FROM a WHERE id>1;
DELETE 0
pguser=#
pguser=# — 確認 削除されないで、削除フラグと削除日時が更新されている
pguser=# SELECT * FROM a;
id | value | deleted | deleted_on
—-+——-+———+—————————-
1 | a | f | infinity
2 | b | t | 2009-09-09 16:51:16.491694
3 | c | t | 2009-09-09 16:51:16.491694
(3 行)

pguser=#
pguser=# — 後始末
pguser=# DROP TABLE a;
DROP TABLE

DELETEの結果件数が0なのが、ORM側でどうなるのかちょっと気になるけれど。

考えたり、調べると、いいことあるなあ。