Ukládání záznamu operací

Školení, která pořádám

Někdy může být důležité evidovat, kdo, kdy, z čeho a na co mění data v databázi. Na škole jsem měl referát o temporálních databázích, které si samy od sebe uchovávají historická data a umožňují s nimi snadno pracovat, takže je možné podívat se např. na přesný otisk dat z nějakého okamžiku v minulosti, běžně rozšířené databáze tuto vlastnost ale nemají.

Pokud se záznamem provedených operací nepotřebujeme intenzivně pracovat a stačí ho mít někde uložený pro případ, že by bylo potřeba výjimečně něco dohledat, nabízí ideální úložiště binární log MySQL. Do toho se zaznamenávají veškeré změny v databázi (nikoliv tedy požadavky na získání dat). Pokud si uložíme otisk databáze a pustíme binární logování, tak v okamžiku, kdy bude potřeba ověřit nějakou změnu, stačí se podívat do tohoto logu. Pokud bychom potřebovali znát předchozí hodnotu, je možné databázi obnovit z otisku a log spustit až do okamžiku zkoumané změny. Pokud bychom potřebovali uchovávat informaci o tom, kdo danou změnu provedl případně další doplňující informace, je možné je připojit v SQL komentáři k dotazu, čímž se do binárního logu také uloží.

Pokud má být záznam provedených operací dostupný běžným uživatelům bez přístupu k binárnímu logu, je možné ho ukládat do běžné databázové tabulky. Na aplikační úrovni to lze zajistit např. takovouto funkcí:

<?php
function mysql_insert($table, $set) {
    $return = mysql_query("INSERT INTO $table (" . implode(", ", array_keys($set)) . ") VALUES (" . implode(", ", $set) . ")");
    if ($return) {
        $id = mysql_insert_id();
        foreach ($set as $key => $val) {
            mysql_query("INSERT INTO log (tabulka, tabulka_id, sloupec, nova, provedeno, id_admin) VALUES ('$table', '$id', '$key', $val, NOW(), '$_SESSION[LOGIN_ID]')");
        }
    }
    return $return;
}
?>

Funkce předpokládá, že v modifikované tabulce bude existovat sloupec AUTO_INCREMENT, v tabulce log tento sloupec naopak být nesmí, protože příští volání mysql_insert_id by vrátilo klíč z tabulky log. Dotazy pro UPDATE a DELETE by vypadaly podobně, s tím rozdílem, že do sloupce stara by se ukládala předchozí hodnota sloupce.

Na tomto místě je vhodné upozornit na MySQL tabulku typu ARCHIVE, která se pro ukládání záznamu operací ideálně hodí – data jsou při ukládání automaticky komprimována, ukládání probíhá ve větších blocích, takže tolik nezdržuje a nejsou umožněny operace UPDATE a DELETE, takže záznamy nelze tak snadno falšovat. Získání dat je pochopitelně pomalé, především proto, že nelze používat indexy.

Řešení na aplikační úrovni má samozřejmě tu nevýhodu, že pokud někdo změnu udělá přímo v databázi, do historie operací se neprojeví. Databáze by samozřejmě měla být zabezpečena tak, aby s ní přímo mohly pracovat jen oprávněné osoby, ale i ty je někdy potřeba kontrolovat. Řešením by bylo místo PHP funkce vytvořit trigger, který by změny automaticky ukládal. Ale vytvořit ho tak, aby automaticky sledoval třeba i nově přidané sloupce do tabulek, by dalo dost práce.

Jakub Vrána, Řešení problému, 19.6.2006, diskuse: 7 (nové: 0)

Diskuse

Jakub Podhorský:

Zdravím,
máš v tom kódu chybu...v tom foreach cyklu by mělo být mysql_query() namísto mysql_q()...snad se nepletu :)

19.6.2006 11:43:02

Jakub Vrána :

Díky za upozornění, opravil jsem to.

19.6.2006 11:46:48

finc:

Rozhodně bych doporučil využití triggeru. Toto jsu přesně případy, kdy je jeho nasazení potřeba.
Tak často strukturu tabulky neměním a pokud ano, tak o jeden SQL příkaz navíc mě nezabije :)
Spíše mě přijde pracnější psát toto v PHP, když se o to samé může postarat DB.
Trigger se dá použít na akční dotazy typu insert, update, delete, čímž i mazaný řádek mohu zálohovat do DB a poté smazat. Mám navíc jistější kontrolu o provedení příkazu.
Navíc, pokud budu potřebovat k DB přistupovat jinou formou, než pomocí PHP. Budu muset tuto problematiku řešit znova.

19.6.2006 11:43:34

Jakub Vrána :

Přiložíte ruku k dílu a pokud nějaký trigger pro tento účel už máte, tak ho uvedete? Já jsem se při zkoumání tohoto problému pokoušel napsat jeden trigger, který by fungoval pro všechny tabulky, ale neuspěl jsem. A udržovat sadu triggerů a při každé změně databáze je nezapomenout aktualizovat mi připadá poměrně pracné. Samozřejmě by se pro přegenerování triggerů dal napsat i skript, ale to už mi přijde šikovnější mnou zvolené řešení (samozřejmě s vědomím zmíněného omezení na použitelnost pouze v dané aplikaci).

19.6.2006 11:51:52

finc:

Otázkou je, jestli potřebuju toto pro všechny tabulky v DB. Trigger se vztahuje k jedné tabulce, nad kterou se má spouštět.
Záleží, jak vytváříte DB. Pokud použijete modelovací nástroj na DB, tak si usnadníte práci pro zjištění, co DB udělá při provédení akčních dotazů.
Navíc ne všude je potřeba používat auto_increment pro ID. Někdy je primárním klíčem něco úplně jiného.
Osobně si nedokážu představit, při velké DB, využití toho logu.
Raději používám zálohu ve formě:
nazev_tabulky
nazev_tabulky_zaloha

Pokud provedu změny či mazání, ukládám odstraněné informace do zalozni tabulky.
nazev_tabulky je InnoDB
nazev_tabulky_zaloha je MyISAM
Pokud např. potřebuji čistit data z důvodu referenční integrity dat, tak je převedu do záložní tabulky. Tímto způsobem udržuji čistá data, o které se mi stará DB.
Nad tímto si hraju s PHP, které řeší zbylou aplikační logiku (stored procedure v MySQL nejsou zatím na moc dobré úrovni). Poté je mnohem snažší programovat aplikaci, když vím, že DB se o zálohy, zalohy změn, strukturu či čistotu dat.
Pokud DB obsahuje desítky tabulek, tak je podle mého lepší zvolit, co potřebuji zálohovat a co nikoli. Dále kam zálohovat, ukládat vše do jedné tabulky je sice pohodlnější, ale ne vždy použitelné. Už kvůli tomu auto_increment

19.6.2006 12:11:10