34. トリガを使用した編集履歴の追跡

本番データベースの一般的な要件に、二つの日付の間でデータがどのくらい変わったか、誰が変更したか、どこで発生したか、といった履歴追跡機能があります。GIS システムの中には、クライアントインタフェースに変更管理機能を持たせることで変更を追跡するものがありますが、編集ツールに多くの**複雑性**が加わります。

データベースとトリガシステムを使って、どのテーブルにでも履歴追跡の追加は可能です。単純な「直接編集」を維持することで、主要テーブルにアクセスきるようになります。

編集操作ごとに記録する履歴テーブルを維持することで履歴追跡が働きます:

• レコードが生成された場合は、いつ、誰によって追加されたか。

• レコードが削除された場合は、いつ、誰によって削除されたか。

• レコードが更新された場合は、削除レコード (古い状態) と生成レコード (新しい状態) を追加します。

34.1. TSTZRANGE を使う

履歴テーブルは PostgreSQL 特有の機能である「タイムスタンプ範囲」型を使います。履歴レコードが「生きている」レコードであった時刻範囲を保存します。特定の地物の履歴テーブルのタイムスタンプ範囲は全てオーバラップせずに隣接していると期待できます。

新しいレコードの範囲は「now()」で始まり、閉じてない終了点を持ちます。そのため、範囲は現在時刻から未来までの時刻の全てに対応します。

SELECT tstzrange(current_timestamp, NULL);

               tstzrange
------------------------------------
 ["2021-06-01 14:49:40.910074-07",)

同様に、削除レコードの時刻範囲は、時刻範囲の終点として更新時の時刻を含むように更新されます。

時刻範囲の検索は、時刻を二つ使った検索よりはるかに単純です。閉じていない時刻範囲は、始点から無限大までの時刻の全てを包含するためです。範囲用の「含む」演算子 @> は、後で使用します。

-- Does the range of "ten minutes ago to the future" include now?
-- It should! :)
--
SELECT tstzrange(current_timestamp - '10m'::interval, NULL) @> current_timestamp;

下に示すように、範囲はGiSTインデックスを使って、空間データのように、非常に効率的なインデックスを作ることができます。これによって履歴クエリは非常に効率的になります。

34.2. 履歴テーブルの構築

この情報を使うと、任意の時点での編集テーブルの状態を再構築できます。この例では、履歴追跡を nyc_streets テーブルに追加しています。

• まず nyc_streets_history を新規追加します。これは編集履歴情報を保存するテーブルです。nyc_streets からのフィールド全てに合わせて、5個のフィールドを追加します。

  • hid 履歴テーブルの主キー

  • created_by レコード作成を行ったデータベースユーザ

  • deleted_by レコードへの削除マーク付加を行ったデータベースユーザ

  • valid_range そのレコードが「生きていた」時間範囲

  履歴テーブル内のレコードは、実際には一切削除しません。編集テーブルの現在の状況の一部でなくなった時刻を記録するだけです。

  DROP TABLE IF EXISTS nyc_streets_history;
  CREATE TABLE nyc_streets_history (
    hid SERIAL PRIMARY KEY,
    gid INTEGER,
    id FLOAT8,
    name VARCHAR(200),
    oneway VARCHAR(10),
    type VARCHAR(50),
    geom GEOMETRY(MultiLinestring,26918),
    valid_range TSTZRANGE,
    created_by VARCHAR(32),
    deleted_by VARCHAR(32)
  );
  
  CREATE INDEX nyc_streets_history_geom_x
    ON nyc_streets_history USING GIST (geom);
  
  CREATE INDEX nyc_streets_history_tstz_x
    ON nyc_streets_history USING GIST (valid_range);
  

• 次に、使用している nyc_streets テーブルの現在の状況を履歴テーブルにインポートします。これで履歴追跡のための開始点を持つことになります。生成時刻と生成ユーザは埋まりますが、時刻範囲の終了時刻は放置され、NULLで削除されていることに注意して下さい。

  INSERT INTO nyc_streets_history
    (gid, id, name, oneway, type, geom, valid_range, created_by)
     SELECT gid, id, name, oneway, type, geom,
       tstzrange(now(), NULL),
       current_user
     FROM nyc_streets;
  

• 使用するテーブルには、INSERT、DELETE、UPDATE の各処理のための三つのトリガが必要です。最初にトリガ関数を生成し、その後にトリガとしテーブルにバインドします。

  INSERT では、新しいレコードを履歴テーブルに生成時/ユーザと一緒に追加します。

  CREATE OR REPLACE FUNCTION nyc_streets_insert() RETURNS trigger AS
    $$
      BEGIN
        INSERT INTO nyc_streets_history
          (gid, id, name, oneway, type, geom, valid_range, created_by)
        VALUES
          (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom,
           tstzrange(current_timestamp, NULL), current_user);
        RETURN NEW;
      END;
    $$
    LANGUAGE plpgsql;
  
  CREATE TRIGGER nyc_streets_insert_trigger
  AFTER INSERT ON nyc_streets
    FOR EACH ROW EXECUTE PROCEDURE nyc_streets_insert();
  

  DELETE では、現在有効な (削除時刻が NULL になっている) 履歴レコードに削除済みをマークするだけです。

  CREATE OR REPLACE FUNCTION nyc_streets_delete() RETURNS trigger AS
    $$
      BEGIN
        UPDATE nyc_streets_history
          SET valid_range = tstzrange(lower(valid_range), current_timestamp),
              deleted_by = current_user
          WHERE valid_range @> current_timestamp AND gid = OLD.gid;
        RETURN NULL;
      END;
    $$
    LANGUAGE plpgsql;
  
  
  CREATE TRIGGER nyc_streets_delete_trigger
  AFTER DELETE ON nyc_streets
    FOR EACH ROW EXECUTE PROCEDURE nyc_streets_delete();
  

  UPDATE では、最初に有効な履歴レコードを削除済みにして、更新後に相当する新しいレコードを挿入します。

  CREATE OR REPLACE FUNCTION nyc_streets_update() RETURNS trigger AS
  $$
    BEGIN
  
      UPDATE nyc_streets_history
        SET valid_range = tstzrange(lower(valid_range), current_timestamp),
            deleted_by = current_user
        WHERE valid_range @> current_timestamp AND gid = OLD.gid;
  
      INSERT INTO nyc_streets_history
          (gid, id, name, oneway, type, geom, valid_range, created_by)
        VALUES
          (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom,
           tstzrange(current_timestamp, NULL), current_user);
  
      RETURN NEW;
  
    END;
  $$
  LANGUAGE plpgsql;
  
  CREATE TRIGGER nyc_streets_update_trigger
  AFTER UPDATE ON nyc_streets
    FOR EACH ROW EXECUTE PROCEDURE nyc_streets_update();
  

34.3. テーブルの編集

現在、履歴テーブルが有効になっています。メインテーブル上で編集して、履歴テーブルのログ登録状況を確認できます。

履歴へのデータベースが支援するアプローチのパワーに注意して下さい: 編集に SQL コマンドライン、ウェブベースの JDBC ツール、QGIS のようなデスクトップツールなどのツールの、どういうものを使おうとも、履歴は一貫して追跡できます。

34.3.1. SQL 編集

"Cumberland Walk" という名前の二つのストリートを、よりスタイリッシュな "Cumberland Wynde" に変更してみましょう:

二つのストリートの更新によって、履歴テーブルでは元のストリートに削除済みマーク、削除時刻が付き、二つの名前が変更された新しいストリートが追加され、現在時刻が付きます。履歴レコードを調べることができます:

34.4. 履歴テーブルのクエリ

現在、履歴テーブルを持っています。どういう用途でしょうか? 時間旅行に使うのです! 特定の時刻 T に行くには、次を含むクエリを構築する必要があります:

• T の前に作られ、まだ削除されていない全てのレコードと、さらに

• T の前に作られるが T の後に削除された全てのレコード。

このロジックを使って、過去のデータ状況のクエリやビューを生成できます。おそらく全ての試験編集は過去数分に行われているので、編集開始前 (つまりオリジナルデータ) である、10分前のテーブルの状況を示す履歴テーブルのビューを生成してみましょう。

-- Records with a valid range that includes 10 minutes ago
-- are the ones valid at that moment.

CREATE OR REPLACE VIEW nyc_streets_ten_min_ago AS
  SELECT * FROM nyc_streets_history
    WHERE valid_range @> (now() - '10min'::interval)

We can also create views that show just what a particular user has added, for example:

CREATE OR REPLACE VIEW nyc_streets_postgres AS
  SELECT * FROM nyc_streets_history
    WHERE created_by = 'postgres';

34.5. 関連情報

• QGIS フリーでオープンソースの地理情報システム

• PostgreSQL トリガ関数

• PostgreSQL 範囲型