Ukládání historie změn v relačních databázích

U větších a složitějších aplikací, ve kterých intenzivně pracuje větší množství uživatelů, jsme často postaveni před otázku, jakým způsobem zaznamenávat historii změn v databázi tak, abychom mohli v každém okamžiku jednoduše zjistit kdo, kdy a jak upravil příslušná data. Tento článek se pokusí popsat různé způsoby, jak takový úkol řešit.

Kdy je potřeba uchovávat historii záznamů?

Na otázku, kdy je potřeba uchovávat historii záznamů, je poměrně jednoduchá odpověď. Vždy, když chceme mít naprostý přehled o stavu příslušných dat v různých časových okamžicích. Jako příklad můžeme uvést publikační systém, kde je dobré uchovávat informace o tom kdy, kým a jaká změna byla provedena v příslušném článku. Dalším příkladem může být sdílená databáze klientů, ve které může provádět změny řada obchodníků. Příkladů bychom našli určitě velké množství. Obecně lze říci, že se jedná většinou o systémy, které se vyznačují velkou dynamikou ve změně dat a větším množstvím uživatelů, kteří mezi sebou data sdílejí a mohou je měnit.

Co od historie požadujeme?

Funkčnost, kterou od historie změn požadujeme, do značné míry ovlivňuje výběr metody, kterou k tomuto účelu zvolíme. Zvážit bychom měli vždy následující body:

• Podrobnost, s jakou chceme změny zaznamenávat. V tomto případě se musíme rozhodnout, jestli nám stačí pouze informace, kdy a kdo provedl změnu, nebo jestli chceme také podrobné informace o tom, k jaké změně došlo.
• Možnost měnit zdrojové kódy stávající aplikace. V některých případech nemáme možnost jednoduše měnit zdrojové kódy aplikace nebo je aplikace příliš rozsáhlá na to, abychom mohli ukládání změn realizovat jednoduše a levně.
• Způsob zpracování a využívání historie změn. Nároky na způsob zaznamenávání změn budou dozajista jiné v případě, kdy nás změny záznamů zajímají jednou do měsíce nebo do půl roku, nebo v případě, že je pro nás sledování změn naprosto zásadní a častou činností. Například pokud se dle počtu změn příslušných dat vyměřuje mzda zaměstnancům, kteří v systému pracují.
• Dostupné technologie, především databázový systém. V tomto bodě nás zajímá především vyspělost používaného databázového systému a místo, které pro zaznamenávání změn máme k dispozici.

Zodpovězení těchto otázek by nás mělo dovést k výběru nejvhodnější metody. V tomto článku budou uvedeny tři způsoby, jak historii změn zaznamenávat. Samozřejmě bychom takových způsobů pravděpodobně našli daleko větší množství.

Tabulky historie

Hned na začátek jsem si připravil, dle mého názoru, nejužitečnější a nejelegantnější způsob zaznamenávání historie změn. Princip celé metody je následující. Ke každé relační tabulce, ke které chceme zaznamenávat změny, vytvoříme speciální tabulku historie následujícím způsobem:

• Tabulka historie bude obsahovat úplně stejné atributy jako tabulka, ke které se váže.
• Navíc bude obsahovat následující atributy:
  • datum změny
  • typ změny (insert, update, případně delete)
  • identifikaci autora změny
• Všechny atributy, kopírující atributy původní tabulky, budou typu NULL.
• Na kopírovaných atributech nesmí být použita žádná integritní pravidla (cizí klíče a podobně).

Následuje triviální příklad, jak vytvořit tabulku historie pro klasickou relační tabulku. Jedná se o jednoduchou tabulku s články v imaginárním publikačním systému.

Tabulka č. 1: Struktura jednoduché tabulky pro ukládání článku

Tabulka č. 2: Struktura tabulky historie pro tabulku č. 1

Pokud máme takové tabulky vytvořené, můžeme přistoupit k logice, se kterou se budou plnit. Při jakékoli změně záznamu v původní tabulce provedeme kontrolu, jaké atributy se změnily, a tyto změněné atributy uložíme do tabulky historie tak, že všechny nezměněné hodnoty budou v záznamu historie nastaveny na NULL a ostatní na nově nastavené hodnoty. Samozřejmostí je také uložit datum změny, autora změny (nejčastěji jako referenci do tabulky uživatelů) a typ změny (insert, update).

Je zřejmé, že při vložení nového záznamu se tento záznam celý zkopíruje do tabulky změn a při dalších změnách se ukládají jen změněné atributy. Tak nám vznikne struktura záznamů, ze kterých jsme schopni k jakémukoli časovému okamžiku sestavit kompletní stav záznamu. V takové tabulce můžeme velice jednoduše vyhledávat kdo, kdy a jaké změny na příslušných záznamech prováděl.

Jak zjišťovat a ukládat změny aneb triggery pracují za nás

Logiku metody již máme za sebou. Nyní vyvstává otázka, jakým způsobem zjišťovat a ukládat změny. V podstatě máme na vybranou dva způsoby:

• Zaznamenávat změny na aplikační úrovni. Toto je způsob trochu náročnější a pracnější. V naší aplikaci musíme realizovat funkčnost, která pokryje zjištění toho, jaké atributy se změnily (je potřeba si uchovávat původní stav záznamu před jeho uložením) a samotné uložení do tabulky historie. Při použití některých databázových systémů, které neumožňují využívat triggery, je tato metoda jediná možná. Vzhledem k tomu, že změny budeme chtít sledovat jen na menší části databázových tabulek, ale nemusí být tento úkol zas tak náročný.
• Zaznamenávat změny pomocí triggerů. Většina databázových systémů umožňuje definovat triggery (pro neznalé metody, které se vykonají při vyskytnutí definované události). Pokud tuto možnost máme, stačí pro každou tabulku, kde chceme historii sledovat, definovat trigger vázaný na události insert, update případně delete. Tento trigger nebude dělat nic jiného, než porovnávat záznam před uložením a po uložení a příslušné změny zaznamenávat do tabulky historie. Jedná se o řešení jednodušší a výkonnější a navíc bez nutnosti zasahovat do kódu aplikace.

Složené entity a vazby 1:N

Velké množství entit v relační databázi se rozkládá do více databázových tabulek. Pokud i v tomto případě chceme sledovat historii změn, musíme vytvořit tabulku historie pro všechny tyto tabulky. Princip je naprosto stejný a v ničem se neliší od principu popsaného v předešlých odstavcích.

Zhodnocení metody

Tato metoda nám přináší absolutní přehled o všech změnách, které se odehrávají v databázi s příslušnou entitou. Přitom její realizace není nikterak složitá. Při využití triggerů nevyžaduje ani zásah do aplikační logiky, navíc nám přináší možnost postihnout i změny záznamů způsobené přímým voláním SQL dotazů na databázovém serveru mimo aplikaci.

Určitou nevýhodou je potřebné místo v databázi. Při větší frekvenci změn může být tabulka hodně velká. Do značné míry může tento způsob také znepřehlednit strukturu databáze a znesnadnit její údržbu. To se dá ovšem vyřešit přesunutím tabulek historie do jiného TABLESPACE, který bude určen výhradně na historii.

Velký pozor si musíme dát na dodržení pravidla o odstranění všech integritních omezení v tabulce historie, protože bychom mohli v budoucnu narazit na neočekávané chyby integritního omezení v případě změn datové struktury. Samozřejmě musíme při takových změnách vždy pamatovat na změny datové struktury tabulek historie.

Klasika aneb logování do souboru

Logování do souboru představuje další z možností, jak zajistit to, abychom alespoň částečně věděli, co se v naší aplikaci děje s vybranými daty. Logování není samozřejmě primárně určeno pro zaznamenávání historie změn, ale pokud už naše aplikace nějaké logování zapracované má (a to by měla mít), můžeme ho rozšířit o logování změn dat v databázi. K tomuto účelu si můžeme vyčlenit speciální logovací soubor, který bude mít určenu pevnou strukturu, a do něj můžeme ukládat informace o tom kdo, kdy, jak a jaký záznam editoval.

Řešení musí být implementováno přímo na aplikační úrovni v příslušných metodách, ve kterých se příslušná data editují. Formát logu je naprosto v naší režii. Můžeme se spokojit pouze s informací, že daný záznam byl editován k příslušnému datu daným uživatelem nebo můžeme logovat úplný stav záznamu před nebo po uložení. Záleží na našich potřebách.

Zhodnocení metody

Logování do souboru je velmi jednoduchý způsob, kterým můžeme informace o změnách dat zaznamenávat, a je mnohem jednodušší, pokud máme v naší aplikaci logování již implementováno. Můžeme si snadno určit a měnit formát logu dle našich potřeb. Navíc většina způsobů logování umožňuje stanovit stupně logování, které jdou za běhu aplikace přepínat. Proto může být v aplikaci implementována taková logika, že při standardním běhu aplikace se zaznamenávají jen základní informace o změnách a při zapnutí plného logování mohou do logu vstupovat kompletní data. Problémy s velikostí logu jdou také jednoduše řešit například pomocí rotování logů po určitých časových intervalech.

Tato metoda má ale jeden velký problém a tím je vyhledávání informací. Je jasné, že v množství souborů se bude těžko dohledávat a stanovovat stav záznamu v určitém čase. Pokud ovšem tuto informaci potřebujeme jednou do měsíce, je logování do souboru naprosto dostačující.

Pro OOP fajnšmekry aneb objektový diff

Jako alternativní a velice zajímavou metodu zde uvedu způsob „diffování“ objektů. Pokud je vám jasné, co je to softwarový objekt a jakým způsobem funguje linuxová utilita diff, asi vás napadne, jakým způsobem tato metoda může fungovat.

Změny dat nás většinou zajímají na úrovni celého objektu, nikoli na úrovni jednotlivých databázových tabulek. Typický softwarový datový objekt čerpá svá data z více relačních tabulek, obvykle nějakým způsobem objektově relačního mapování. Lepším způsobem, než sledovat změny na úrovni jednotlivých tabulek, je sledovat změnu objektu jako celku. V tomto případě můžeme využít způsob, kdy si napíšeme generickou metodu pro zjištění rozdílu (diff) dvou objektů. Tuto metodu následně voláme při jakékoli změně příslušného objektu a zjištěný diff ukládáme do databáze. Způsob realizace metody pro zjišťování rozdílu dvou objektů je samozřejmě odlišný a jinak náročný v různých programovacích jazycích.

Všechny změny můžeme (ale nemusíme) ukládat do jediné tabulky, kde pro každý typ objektu, který sledujeme, vytvoříme atribut, který představuje identifikátor objektu (primární klíč). Dalšími atributy pak jsou datum, autor změny a samozřejmě samotný diff. Následuje ukázka tabulky, ve které sledujeme například změny objektů článek, klient a firma.

Tabulka č. 3: Struktura tabulky historie pro objekty článku, klienta a firmy

Zhodnocení metody

Tato metoda je pravděpodobně nejsložitější na implementaci, ovšem co se týče sledování změn, je asi nejpřirozenější a nejlogičtější z uvedených metod, protože změny sledujeme na celých objektech a ne roztříštěně v jednotlivých tabulkách. Při správně zvoleném formátu diffu jsme posléze schopni zrekonstruovat skutečný stav objektu pro libovolný okamžik. Nevýhodou může být nutnost provádět změny v aplikaci a poměrně vysoká časová složitost operací. Zásadní nevýhodu představuje omezená možnost vyhledávání ve změnách. Například zde nemůžeme tak jednoduše zjistit, u jakých článků měnil titulek příslušný uživatel v konkrétním časovém rozmezí a podobně.

Souhrn

Pokud se vám často stává, že potřebujete zrekonstruovat stav nějakých dat v databázi k určitému datu, a nechce se vám složitě hledat v zálohách nebo potřebujete přesně vědět, co se ve vaší aplikaci s daty děje, kdo je kdy a jak edituje, můžete využít některou z výše uvedených metod. Tyto metody rozhodně nepředstavují kompletní výčet možností, jak historii změn zaznamenávat. Přesto patří k těm nejčastěji používaným.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.