Timetravel- en temporale tabellen
Uit Yapf
Inhoud |
Wat zijn het?
Timetravel- en temporale tabellen zijn tabellen die een geschiedenis bijhouden van de veranderingen in de tabel. Dit maakt het mogelijk om terug te kijken wat er allemaal met de records gebeurd is en het is mogelijk om op te vragen hoe elk record er op elk willekeurig moment in het verleden uitzag.
Dit wordt hoofdzakelijk gebruikt voor auditing; het bijhouden van welke medewerker welke veranderingen heeft doorgevoerd. Een minder bekende toepassing is versiebeheer; door een datum+tijd op te geven krijg je de records zoals ze waren op dat tijdstip. Als er sindsdien nieuwe veranderingen zijn gemaakt krijg je die gewoon niet te zien tot je om een latere datum gaat vragen.
De tabel ziet er bijvoorbeeld zo uit:
userid - naam - adres - startdate - enddate 1 - K.Plop - Plostraat 4 - 2009-01-01 - 2009-01-04 1 - K.Plop - PlopStraat 14 - 2009-01-04 - 2009-01-07 1 - K.Plop - PlopStraat 40 - 2009-01-07 - NULL
Meneer K.plop is ingevoerd op 2009-01-01 en aan het tweede record kun je zien dat op 2009-1-04 een verandering is gemaakt in zijn naam en adres. Op 2009-01-07 is nog een keer zijn adres veranderd en sindsdien is er niets meer gewijzigd.
Wat zijn het niet?
Verwar de historie van een record niet met de historie van de data die het record representeert.
Als je auto's verhuurt dan komt er elke zoveel dagen een nieuwe klant voor elke auto. Elke keer dat dat gebeurt heb je te maken met een nieuwe gebeurtenis en dus een nieuw record, geen verandering van een bestaand record.
Wanneer een klant aangeeft dat er een verkeerde datum op de bon staat, dan is dat een verandering van een bestaande gebeurtenis en dus van een bestaand record.
Een implementatie in postgreSQL 8.x
Dit gedeelte toont een voorbeeld van hoe temporale tabellen in PostgreSQL opgezet kunnen worden. Ik zeg bewust "kunnen", omdat dit maar één manier is om het voor elkaar te krijgen. Het principe is zo simpel dat het ook in PHP gedaan zou kunnen worden, maar als het in de database kan dan is dat altijd een betere optie, dus hier gaan we.
Noot: Bij temporale tabellen heb je meerdere kopieen van de data in de tabel staan. Ik maak daarom onderscheid tussen records en items. Een item is een stuk informatie, wat in een gewone tabel dus een record zou zijn. Een record is nog steeds gewoon een record zoals je dat krijgt met een INSERT statement. Van elk item bestaan dus meerdere records, één vor elke verandering van het item.
Probleemstelling
Er zijn een aantal zaken die moeten worden geregeld om het werkend te krijgen.
- De gebruiker mag alleen de nieuwste versie van de de items zien.
- Bij het invoeren van een nieuw record in de tabel moet de startdate gelijk zijn aan NOW, en de enddate gelijk zijn aan NULL.
- Bij het bijwerken van een item moet er een nieuwe kopie van het bestaande nieuwste record gemaakt worden.
- Bij het verwijderen van een item uit de tabel moet het nieuwste record een enddate van NOW() krijgen.
Dit is verbazend eenvoudig te doen via triggers en rules.
Nieuwe items toevoegen
Om een item toe te voegen kun je gewoon een INSERT query draaien, maar je moet dan wel zeker weten dat de startdate gelijk is aan NOW(). Dit kun je het eenvoudigst bereiken door in de tabeldefinite een DEFAULT NOW() op te nemen voor de startdate, en de NOT NULL weg te laten voor de enddate. Vanuit je applicatie zul je nooit een start- of enddate opgeven voor het item dat je invoert, dus zet de database dat automatisch goed.
Er blijft dan nog maar één gaatje over; wat als de applicatie hetzelfde item twee keer probeert in te voeren? Dit kun je op twee manieren afvangen;
- met een unique index op het id en enddate, met als voorwaarde dat "enddate is null"
Dit boert gewoon een foutmelding.
- met een trigger die kijkt of het item al bestaat en zoja de insert omschrijft naar een update.
Dit boert geen foutmelding, maar houdt er rekening mee dat het nu dus geen echte INSERT meer is. Als je applicatie ervanuit gaat dat een dubbele INSERT een foutmelding oplevert dan breek ja dat nu, met allerlei spannende gevolgen van dien.
Bestaande items updaten
Wanneer een item in de tabel wordt verandert moeten er twee dingen gebeuren.
- Het oude record moet een enddate van NOW() krijgen.
- Er moet een nieuw record worden aangemaakt met een startdate van NOW() en een enddate van NULL.
In eerste instantie denk je dus aan iets als:
UPDATE tabel SET enddate=NOW() WHERE PK=waarde AND enddate IS NULL; INSERT INTO tabel (PK, startdate,enddate) VALUES (waarde,NOW(), NULL);
Dit blijkt lastig omdat je voor het nieuwe record alle data van het oude moet hebben. Een UPDATE zal immers niet alle kolommen raken en dus ook niet alle kolommen meegeven vanuit het script.
De oplossing hiervoor is een subtiele verandering in de werkvolgorde. Wat je wilt bereiken is dat het oude record beschikbaar blijft zoals het nu is. Dat kun je doen door het niet aan te raken, maar je kunt het ook doen door er een kopie van te maken. Als je eerst een kopie maakt van het oude record, dan kun je datzelfde oude record vervolgens met de originele UPDATE gewoon veranderen.
Lang leve triggers. Een trigger heeft namelijk toegang tot NEW en OLD. NEW is de data die vanuit de query is meegegeven voor toepassing op het record, en OLD is het complete oude record. Die oude data kan dus worden gebruikt om een kopie te maken van hoe het record was voordat de update werd uitgevoerd en vervolgens kan de UPDATE gewoon zijn gang gaan op de originele kopie.
In feite blijft de UPDATE dus altijd hetzelfde record veranderen maar elke keer dat het gebeurt wordt er vooraf een kopie van gemaakt.
Effectief krijg je dan iets als:
INSERT INTO tabel (alle...kolommen, ENDDATE) VALUES (alles..uit..OLD, NOW()); UPDATE tabel SET startdate=NOW() WHERE PK=waarde AND enddate IS NULL;
In PgSQL triggers is dat:
DECLARE
oldrecord RECORD;
BEGIN
IF OLD.enddate IS NOT NULL THEN -- NEW.edate can be non-null
RETURN NULL; -- no update
END IF;
IF NEW.enddate IS NULL THEN
INSERT INTO tt(id, name, price, startdate, enddate)
VALUES (OLD . id, OLD . price, now(), OLD . startdate);
NEW.sdate = now();
END IF;
RETURN NEW;
END;Noot: Je moet in de trigger nog wel hardcoded opgeven welke kolommen er geraakt moeten worden. Automatiseer dit niet want de performance-penalty weegt totaal niet op tegen het flutbeetje typetijd dat je uitspaart.
Referentiele integriteit
Een van de meest complexe handelingen uit het hele tijdreisverhaal is de referentiele integriteit, omdat de tabellen die verwijzen naar de tijdreistabel vrijwel altijd moeten verwijzen naar de nieuwste versie van het record.
Het probleem hiermee is dat het meest recente record wordt bepaald door twee dingen: ofwel de hoogste startdatum, ofwel enddate IS NULL. De hoogste startdatum kun je niet zomaar opgeven als voorwaarde, en NULL kan niet gebruikt worden in een Foreign key.
De opplossing is dan ook het gebruik van triggers. Een trigger kan 'ON INSERT' en 'ON UPDATE' actie ondernemen om te controleren of de nieuwe waarde voorkomt in een andere tabel. Omgekeerd kan een trigger op de temporele tabel record uit andere tabellen wissen.
Het grote nadeel hiervan is natuurlijk dat de DBA die relaties in de triggers moet vastleggen. Als een verwijzende tabel wordt weggehaald dan moet ook de trigger worden aangepast.
De triggers zelf zijn eenvoudig, om te controleren of een verwijzende tabel een id gebruikt dat bestaat in de temporele tabel gebruik je EXISTS:
DECLARE BEGIN IF NOT EXISTS (SELECT true FROM tt WHERE id=NEW.tt_id AND enddate IS NULL) THEN RAISE EXCEPTION 'Illegal value for id:"%"', NEW.id; RETURN NULL; END IF; RETURN NEW; END;
En om het record te laten wissen vanuit de temporele tabel kun je het toevoegen aan de ON DELETE rule:
CREATE RULE "tt_rl_ondelete" AS ON DELETE TO "public"."tt" DO INSTEAD ( DELETE FROM tt_linked WHERE tt_linked.tt_id = old.id; UPDATE tt SET enddate = now() WHERE tt.enddate IS NULL AND tt.id = old.id; );
Q&A
Wat is nou de juiste keuze voor de Primary key?
Daar zijn de meningen over verdeeld.
Stiktgenomen (ala Celko [1]) moet de PK een uniek record aanwijzen. In die zin moet het de Primary-Key dus altijd de startdate bevatten.
De kolomnamen zitten hardcoded in def procedure?
Q:Als de tabel verandert moet ik de procedure ook aanpassen, dat wil ik niet!
Willen, moeten, kunnen en mogen zijn vier verschillende zaken:
- In een goed datamodel verandert er vrijwel nooit iets in de tabelstructuur.
- Het is bijzonder eenvoudig om een scriptje te schrijven dat de tabelstructuur uitleest en daar de juiste triggers voor fabriceert.
- Als je het dynamisch aanpakt verspil je veel te veel tijd per trigger.
NOT NULL met een DEFAULT NOW()?
Ja. De startdate moet voor nieuwe records altijd NOW() zijn en geen enkele routine past de startdate aan. Het is nog netter om met iets van een trigger of rule af te dwingen dat de startdate niet veranderd kan worden.