34. Spåra redigeringshistorik med hjälp av triggers

Ett vanligt krav på produktionsdatabaser är att de ska kunna spåra historik: hur har data ändrats mellan två datum, vem gjorde ändringarna och var skedde de? Vissa GIS-system spårar ändringar genom att inkludera ändringshantering i klientgränssnittet, men det tillför en hel del komplexitet till redigeringsverktygen.

Med hjälp av databasen och triggersystemet är det möjligt att lägga till historikspårning i vilken tabell som helst, samtidigt som man behåller enkel ”direktredigeringsåtkomst” till den primära tabellen.

Historikspårning fungerar genom att en historiktabell sparas för varje redigering:

  • Om en post har skapats, när den lades till och av vem.

  • Om en post har raderats, när den raderades och av vem.

  • Om en post har uppdaterats läggs en borttagningspost (för det gamla tillståndet) och en skapandepost (för det nya tillståndet) till.

34.1. Använda TSTZRANGE

Historiktabellen använder en PostgreSQL-specifik funktion - typen ”timestamp range” - för att lagra det tidsintervall som en historikpost var den ”live” posten. Alla tidsstämpelintervall i historiktabellen för en viss funktion kan förväntas vara icke-överlappande men intilliggande.

Intervallet för en ny post börjar vid now() och har en öppen slutpunkt, så att intervallet täcker all tid från nuvarande tidpunkt in i framtiden.

SELECT tstzrange(current_timestamp, NULL);

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

På samma sätt kommer tidsintervallet för en borttagen post att uppdateras så att den aktuella tiden ingår som slutpunkt för tidsintervallet.

Att söka i tidsintervall är mycket enklare än att söka i ett par tidsstämplar, eftersom ett öppet tidsintervall omfattar all tid från startpunkten till oändligheten. Vi kommer att använda operatorn ”contains” @> för intervall.

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

Intervall kan indexeras mycket effektivt med hjälp av ett GIST-index, precis som spatiala data, vilket vi visar nedan. Detta gör historikfrågor mycket effektiva.

34.2. Uppbyggnad av historiktabellen

Med hjälp av denna information är det möjligt att rekonstruera redigeringstabellens tillstånd vid en viss tidpunkt. I det här exemplet kommer vi att lägga till historikspårning i vår tabell nyc_streets.

  • Lägg först till en ny tabell nyc_streets_history. Det här är den tabell som vi kommer att använda för att lagra all historisk redigeringsinformation. Förutom alla fält från nyc_streets lägger vi till ytterligare fem fält.

    • hid den primära nyckeln för historiktabellen

    • created_by den databasanvändare som orsakade att posten skapades

    • deleted_by den databasanvändare som orsakade att posten markerades som raderad

    • valid_range det tidsintervall inom vilket posten var ”live”

    Observera att vi faktiskt inte raderar några poster i historiktabellen, vi markerar bara den tidpunkt då de upphörde att vara en del av det aktuella tillståndet i redigeringstabellen.

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

  • Därefter importerar vi det aktuella läget för den aktiva tabellen nyc_streets till historiktabellen, så att vi har en startpunkt att spåra historiken från. Observera att vi fyller i skapelsetid och skapande användare, men lämnar slutet av tidsintervallet och den borttagna av informationen 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;

  • Nu behöver vi tre triggers på den aktiva tabellen, för INSERT-, DELETE- och UPDATE-åtgärder. Först skapar vi triggerfunktionerna och binder dem sedan till tabellen som triggers.

    Vid en inmatning lägger vi bara till en ny post i historiktabellen med skapelsetid/användare.

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

    Vid en radering markerar vi bara den aktiva historikposten (den med en NULL-tid för radering) som raderad.

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

    Vid en uppdatering markerar vi först den aktiva historikposten som raderad och infogar sedan en ny post för det uppdaterade tillståndet.

    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. Redigera tabellen

Nu när historiktabellen är aktiverad kan vi göra ändringar i huvudtabellen och se hur loggposterna visas i historiktabellen.

Lägg märke till styrkan i denna databasbaserade metod för historik: oavsett vilket verktyg som används för att göra ändringarna, oavsett om det är SQL-kommandoraden, ett webbaserat JDBC-verktyg eller ett skrivbordsverktyg som QGIS, spåras historiken konsekvent.

34.3.1. SQL-redigeringar

Låt oss förvandla de två gatorna med namnet ”Cumberland Walk” till det mer eleganta ”Cumberland Wynde”:

Om du uppdaterar de två gatorna markeras de ursprungliga gatorna som raderade i historiktabellen, med en raderingstidpunkt som är nu, och två nya gator med det nya namnet läggs till, med en tilläggstidpunkt som är nu. Du kan inspektera de historiska posterna:

34.4. Fråga efter historiktabellen

Nu när vi har en historietabell, vad har vi för nytta av den? Den är användbar för tidsresor! För att resa till en viss tid T måste du konstruera en fråga som inkluderar:

  • Alla poster som skapats före T och som ännu inte raderats; och även

  • Alla poster som skapats före T, men som raderats efter T.

Vi kan använda den här logiken för att skapa en fråga, eller en vy, över datatillståndet i det förflutna. Eftersom alla dina testredigeringar antagligen har skett under de senaste minuterna, låt oss skapa en vy över historiktabellen som visar tabellens tillstånd för 10 minuter sedan, innan du började redigera (alltså originaldata).

-- 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. Se även