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 的所有欄位之外，我們還新增五個欄位。

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 動作。首先，我們建立觸發器函式，然後將它們以觸發器的形式繫結到表格。

對於插入，我們只需將包含建立時間/使用者的新記錄新增到歷史表格。

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();

對於刪除，我們只需將目前的作用中歷史記錄（具有 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();

對於更新，我們首先將作用中歷史記錄標記為已刪除，然後為更新的狀態插入新記錄。

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)

我們還可以建立檢視來顯示特定使用者新增的內容，例如

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

PostGIS 簡介

34. 使用觸發器追蹤編輯歷史¶

34.1. 使用 TSTZRANGE¶

34.2. 建立歷史表格¶

34.3. 編輯表格¶

34.3.1. SQL 編輯¶

34.4. 查詢歷史表格¶

34.5. 另請參閱¶

PostGIS 簡介

34. 使用觸發器追蹤編輯歷史¶

34.1. 使用 TSTZRANGE¶

34.2. 建立歷史表格¶

34.3. 編輯表格¶

34.3.1. SQL 編輯¶

34.4. 查詢歷史表格¶

34.5. 另請參閱¶

導覽