Starší komentáře ke článku: Ukládání historie změn v relačních databázích
Zpět na článek | Úvodní stránka Interval.cz
Datum vložení: 26.7.2005 9:19:06
Nedávno jsem byl postaven před podobný problém - zajistit kompletní historii dat v aplikaci (s velkým množstvím tabulek). Pro jednoduchou práci s aktuálními i staršími daty se mi nakonec jako nejvhodnější zdála metoda přidat si na každý řádek tabulky sloupečky PLATNOST_DO a VLASTNIKA a místo update & delete jen zneplatnit původní záznam a přidat nový. Platnost aktuálních záznamů nastavuji na nějakých 30 let dopředu ... té doby se ten program určitě nedožije :-)
Datum vložení: 26.7.2005 9:35:37
To je ovsem moznost vhodna pro tabulky s mensim poctem dat. Predstavte si ze mate tabulku s cca pul az milionem zaznamu a kazdy den dojde k cca 2000 zmen. Za rok byste mel v databazi o trictvrte milionu zaznamu vice a dochazelo by ke zpomalovani aplikace. Pokud se historie uklada mimo, ke zpomalovani nedochazi.
Datum vložení: 26.7.2005 11:30:39
Tomu se da celkem snadno zabranit pomoci partitionovani (treba ukladat pro kazdy mesic data do jine tabulky)
Datum vložení: 5.8.2005 16:09:27
Což je ovšem proti logice relačních databází.
Datum vložení: 10.8.2005 21:35:03
To neni pravda, table partitioning je bezne uzivana metoda u velkych tabulek. Ty tabulky totiz nejsou samostatne, ale jsou zavisle na hlavni tabulce, od ktere jsou odvozeny a selecty na tu hlavni tabulku berou data i z tech odvozenych (tedy takhle to funguje u postgresu, ostatni databaze to mozna maji implementovano jinak, ale rozhodne je to bezna vec).
Datum vložení: 26.7.2005 9:56:35
Problém je v tom, že tabulka bez sloupce PLATNOST_DO měla asi nějaký primární klíč. Se sloupcem PLATNOST_DO je primárním klíčem starý klíč + sloupec PLATNOST_DO. Což nemusí být vždy ideální. Hlavně pokud používáte umělý primární klíč. Tady je podle mě problém. U tohoto řešení bych se ale nebál, že tabulky naberou na "objemu". Tento způsob často používají velké informační systémy a nemají s tím problém.
Datum vložení: 26.7.2005 10:11:41
Tak mě napadá, že toto by se dalo řešit přidáním pole starý_PK. U historických záznamů pak nastavím PK na null (nebudou tolik zaclánět mezi aktuálními daty) a starý PK na jeho původní hodnotu.
Datum vložení: 26.7.2005 12:50:18
Až na to, že PK na null nikdy nenastavíte. Jestli ano, není to PK. ;-) A kdyby se Vám to náhodou povedlo, takový přístup mi připadá jako přímá cesta do pekel.
Datum vložení: 26.7.2005 10:17:46
Co se velikosti dat týče, tak způsob by se samozřejmě dal upravit tak, aby se místo starých shodných hodnot uložil NULL. Mě se na prvním postupu popsaném v článku nelíbí to, že je každá tabulka v databázi dvakrát a když přidám nebo změním sloupec, je nutné to udělat na dvou místech. Problém s umělými primárními klíči se dá vyřešit nějak takhle: UPDATE tabulka SET hodnoty = 'nové' WHERE id = @id; INSERT INTO tabulka (hodnoty, vlastnik) VALUES ('staré', 'vlastník'); Aktuálnímu záznamu tak vždy zůstane stejné id, historické záznamy dostávají stále nové id a primární klíč může zůstat (id).
Datum vložení: 26.7.2005 10:31:58
Tady bych chtel upresnit, ze ne kazda tabulka je v db 2x ale pouze tabulky u kterych vas zajimaji zmeny. Tech obvykle nebyva takove mnozstvi, kdyz si odmyslite veskere cisleniky atd. Navic tabulky historie muzete odsunout do jineho tablespace. O usetreni vykonu take neni pochyb.
Datum vložení: 26.7.2005 13:01:00
Pozivam to presne jak pise Jakub - Insertem si zkopiruju puvodni hodnoty na radku a ten zneplatnim, a puvodni radek opavim na spravne hodnoty. Tim mi zustanou zachovany aktualni vazby mezi tabulkama, ty historicky musim opravit podle novyho idecka. Co se tyka narustani - v mem pripade pujde tak o nejakych par tisic novych zaznamu za mesic (z pocatku vice, pak se to snizi), tak myslim, ze se nic moc zpomalovat nebude.
Datum vložení: 27.7.2005 12:22:54
No, částečně máš pravdu, ale pokud v dotazech danou tabulku joinuješ jinou tabulku, a třeba i dvojnásobně, tak zpomalení poznáš velmi rychle.
Datum vložení: 26.7.2005 10:06:33
Pokud použiju první možnost, tabulku historie budu plnit triggerem a nebudu chtít nijak měnit zdrojový text aplikace (aplikační vrstvu), jak zajistím, aby se zapsal do tabulky historie uživatel, který změnu provedl? Zvláště pokud všichni uživatelé aplikace přistupují do databáze pod jedním uživatelem, což je typické pro WWW aplikace. V těle triggeru přece neznám toho uživatele. Tady by byl myslím problém. Jak to udělat aniž bych změnil zdrojové texty aplikační vrstvy?
Datum vložení: 26.7.2005 10:16:19
Velice dobra pripominka. Resi se to takto. V tabulce, u ktere chcete sledovat historii, mate obvykle atribut last_edited_by nebo neco obdobneho. V triggeru si sahnete na tento atribut a ulozite ho do historie jako autora zmeny.
Datum vložení: 26.7.2005 10:14:58
A co když upgraduju aplikaci a změní se struktura tabulek. Tady teprve nastává ten správný oříšek, na který bychom neměli zapomenout vytváření historie. Nevíte někdo o článku na toto téma?
Datum vložení: 26.7.2005 10:30:00
Kdyz uz uprgradujete aplikaci na zaklade zmeny datove struktury, zmenite datovou strukturu i u tabulky historie. Pokud je to pouhe pridani, odebrani atributu, neni s tim vetsi problem. Pokud je to razantni zmena, bude vas to stat stejne tolik usili, ze zmena tabulek historie nebude tim nejhorsim co vas bude cekat.
Datum vložení: 26.7.2005 10:32:12
Velmi dobrý článek. Snad bych jen doplnil. Při evidenci historie se rozlišuje mezi těmito řešeními. 1) Audit log - prostý seznam všech změn případně s původci. Slouží jen ke zobrazení změn provedených na objektu, nelze pracovat jednoduše s historickou verzí objektu. 2)Effectivity - u každého objektu zavedeme vlastnost, která vymezí časové období, v němž byla právě tato vlastnost objektu aktuální. 3) vzor Temporary property - každá vlastnost objektu, u niž je sledována historie, podporuje alterantivní set accessor, který přijímá v jednom argumentu datum určující, že má být vrácena hodnota vlastnosti platná k danému datu. 4)Temporal object - celý objekt má metodu, která přijímá datum a vrací historickou verzi objektu platnou k danému datu. Aby nebylo možné modifikovat data historického objektu, vydává se tzv. snapshot - statický obraz dat objektu v minulosti. (Použita terminologie Martina Fowlera) Tak mě napadá, že by to byl námět na další článek. ;)
Datum vložení: 27.7.2005 7:39:59
Jen doplnění pro případné zájemce - tyto možnosti podrobně Martin Fowler popisuje na http://www.martinfowler.com/ap2/timeNarrative.html
Datum vložení: 27.7.2005 8:39:20
Myslim, ze Audit log je presne to co je tematem tohoto clanku. Ty dalsi vzory potom resi chovani na urovni aplikacni logiky. Nedavno jsme takovy audit log delali do jednoho systemu. Logovani se sice neresilo do databaze, ale do NT Event logu, ale to je celkem jedno. Logovalo se na urovni aplikacni vrstvy, konkretne na DAO objektech a to pomoci AOP (http://en.wikipedia.org/wiki/Aspect-oriented_programming). Konkretne vzniknul aspect, ktery visel nad CUD (Create, Update, Delete) metodami vsech DAO objektu a logoval prichozi domain objekty. V logu pak byl zaznam s detailem napr. Operace: INSERT Parametr #1 = objekt cz.asei.project.core.domain.MUser nazev = romanp jmeno = Roman Pichlík heslo = ****** organizace = A.S.E.I. telefon = +420 382 213 469 email = roman.pichlik@asei.cz poznamka = poznamka idUg = null version = 0 Sila tohoto reseni spocivala ve vyuziti aspectu nebot se nemuselo sahat do stavajiciho kodu.
Datum vložení: 26.7.2005 11:41:11
No, s timhle pozadavkem jsem se setkal docela casto. Spis mne prekvapuje, ze neni pro takoveto veci neni podporav primo v SQL databazi (upozornuji - delam pouze s PostgreSQL, Oracla/Sybase/... jsem nikdy prakticky nevidel!) - predstavoval bych si to tak, ze by se dalo zapnout logovani zmen na urovni radku (urceno PK)/tabulku/databazi/uzivatele a pak mit moznost vypisu historie. SQL databaze by tak mohla provadet lepsi optimalizace (napr. historie se urcite bude pouzivat pro cteni radove mnohem mene, nez aktivni data, moznost specifikovat jine uloziste pro historii (vetsi, ale pomalejsi, nez pro aktivn. data) apod.). Taky by toto v podstate zamezilo moznosti poskozeni databaze uzivatelem (ne adminem) - kompletni rollback vseho, co uzivatel provedl za posledni 2 dny apod. Jasne - vsechno tohle se da resit triggery a temito vecmi, ale prijde mi to jako znovuvymysleni kola...
Datum vložení: 26.7.2005 12:41:46
Tusim, ze neco podobneho umi DB2 od IBM
Datum vložení: 26.7.2005 13:53:51
Tohle přece řeší transakční log
Datum vložení: 26.7.2005 14:02:37
AFAIK tak transkacni log byva pred uzivatelem "ukryt" v implementaci SQL serveru a neni mozne ho nejak cist a zpracovavat pomoci SQL prikazu (ale mozna to nektere SQL servery umi), ale otazkou taky je, jestli lze udrzovat transakcni log i nekolik let (a nekolik restartu/updatu SQL serveru) a zachovat rozumny vykon...
Datum vložení: 26.7.2005 21:59:13
Kdyz pouzivate ten PostgreSQL tak by vas mohlo zajimat tohle: http://pgfoundry.org/projects/tablelog/
Datum vložení: 2.8.2005 7:47:35
RDBMS Oracle ma nadstavbu Oracle Workspace Manager, ktora umoznuje pracu s datami pomocou dlhych transakcii. OWM uklada aj historiu zmien a umoznuje pracovat s datami v casovom reze. Navyse uklada aj rozpracovane data, ktore este nie su platne. Cele sa to navonok chova ako normalna tabulka, v skutocnosti vsak Oracle povodnu tabulku nahradi pohladom s instead-of trigrami. Tato technologia je vsak uz svojou urovnou trochu vyssie ako povodna poziadavka zaznamenavania historie, a nezaobide sa bez zmeny aplikacnej logiky (pred zapisom do tabulky je potrebne zvolit dlhu transakciu, v ramci ktorej sa tento zapis urobi a pod.)
Datum vložení: 27.7.2005 21:17:32
Domnívám se, že popisovaný princip "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" bude fungovat jen pro určité případy - totiž jen pro ty sloupce, které jsou v originální tabulce NOT NULL (žel, v příkladu jsou NOT NULL všechny sloupce :-). Pro sloupce, které mohou být v originální tabulce NULL, a kde bude mít uživatel možnost změnit hodnotu sloupce na NULL, bych pak v tabulce historie nerozeznal případ "ke změně nedošlo" od případu "došlo ke změně na NULL". Je to tak ?
Datum vložení: 28.7.2005 8:17:35
Velmi dobry postreh. :-) Toto je asi nejvetsi problem cele metody. Ale samozrejme je vice mene resitelny. Tzn. kdyz se hodnota nastavuje z NULL na neco je to ok, zapiseme novou hodnotu. Kdyz se meni z neceho na NULL musime zapsat nejakou specialni hodnotu, kterou si sami navrhneme a ktera predstavuji prave nullovani atributu. Tzn. pro integery nejlepe nejakou vysokou zapornou hodnotu pro stringy je to jednodussi, staci urcita specialni kombinace znaku, pro datumy nejlepe rok zacatek letopoctu. Samozrejme je zde problem s univerzalnosti, protoze nam nikdo nemuze zarucit, ze dany atribut nemuze nabyvat prave te nasi specialni hodnoty. To neni v nasich silach osetrit. Obvykle ale k tomuto problemu nedochazi. Navic atributy typu NULL nebyvaji temi, ktere chceme sledovat predevsim.
Datum vložení: 28.7.2005 11:00:10
Aha - a ja myslel, ze v tabulce s historii se u nezmenenych sloupcu zapisuje NULL kvuli uspore mista (tj. kdyz se mi zmeni jen jeden sloupec z 20, tak v historicke tabulce bude 1x hodnota a 19x NULL). Tim "nahradnim" resenim (zapis specialni hodnoty) se ale nic nespori, tedy uz vubec nechapu, proc se v tabulce s historii nepise vzdy novy stav vsech zmenenych sloupcu (tj. posledni radek v historizacni tabulce je vzdy roven radku v originalni tabulce, anebo jeste jinak : v historizacni tabulce je vzdy ten "predposledni" stav). Ma to vase "nahradni" reseni nejakou vyhodu oproti tomu memu ?
Datum vložení: 28.7.2005 11:16:22
To jste me asi nepochopil. U nezmenenych atributu se zapisuje NULL. U zmenenych nova hodnota. Zapis specialni hodnoty je pouze u "zmeny" z NOT NULL hodnoty na NULL.
Datum vložení: 28.7.2005 11:20:50
Mate pravdu v tom, ze jsem vas opravdu spatne pochopil. Chybi mi ale odpoved na otazku - ta vami popisovana metoda je vyhodnejsi jen z hlediska uspory mista, nebo to ma jeste i nejake jine vyhody ?
Datum vložení: 28.7.2005 11:23:46
Vyhoda toho ze zmena je zapsana hodnotou a nezmenene atributy jako null je vhodna predevsim kvuli dohledavani v historii. Pokud byste ukladal vzdy cely stav, nejde tak jednodusse zjistit napr vsechny zmeny daneho atributu v casovem intervalu. Take jak jste rikal se setri misto. Vase reseni ukladani plneho zneni ma ale take jednu vyhodu a to jednodussi rekonstruovani stavu v danem okamziku.
Datum vložení: 12.8.2005 13:24:36
No dovolím si polemizovat, že nejde jednoduše zjistit počet změn v daném období. Pokud tedy umím napsat složitější SQL a mé znalosti nekončí u základů. Co se týká šetření místa, pokud nevím, kolik jakých změn nastává v datech (tedy kolik sloupců se mění), nemůžu tenhle postoj přijmout. Co se týká rychlosti ukládání, tak rozhodně bude rychlejší uložit CELÝ záznam beze změn pouze doplněný o identifikaci autora změny, než porovnávat jednotlivé sloupce OLD a NEW a sledovat, kde došlo ke změně a ukládat NULL když jsou stejné. Primárně by se tedy každý měl vždycky na začátku zamyslet nad tím, jaké množství dat mu bude protékat systémem v čase, jak dlouho bude data ukládat a pak teprve vymýšlet, jak by tyhle požadavky realizoval.
Datum vložení: 12.8.2005 14:52:05
Dulezite bylo slovo "jendoduse" zjistit pocet zmen v danem obdobi. Me znalosti u zakladu nekonci a vim moc dobre jaky vykon bych dosahnul pri ukladani celych zaznamu pri zjistovani zmen. Verte ze v nasi databazi se timto postupem setri mnoho mista.
Datum vložení: 28.7.2005 11:07:23
Zajimalo by mne, zda ono pravidlo "Navic atributy typu NULL nebyvaji temi, ktere chceme sledovat predevsim." odrazi jen vas osobni pocit, nebo prameni z nejakeho "vyzkumu" ? Jeste jsem si ve sve praxi nejak nevsiml, ze by toto pravidlo obecne platilo. No, mozna v jednoduchych webech ano, ale obecne ve slozitejsich systemech, kde eviduji "objekty" které v case prodelavaji nejaky vyvoj a meni casto sve stavy, tak je zcela bezne, ze i sloupce s velmi dulezitými hodnotami mohou byt NULL (napr. v pocatku zivotniho cyklu objektu apod.).
Datum vložení: 28.7.2005 11:30:20
Na co si vymýšlet nějaké speciální hodnoty pro různé datové typy a doufat, že s nimi nic nebude kolidovat, když stačí v případě změny na NULL zapsat původní hodnotu? 'abc' -> 'def' = změna na 'def' 'abc' -> NULL = nedošlo ke změně 'abc' -> 'abc' = změna na NULL
Datum vložení: 28.7.2005 3:09:05
Osobne to resim tak, ze mam vytvorenou jednu logovaci tabulku, do ktere jen pridavam zaznamy s datem, uzivatelem a SQL dotazem (prikazem) + samozrejme zalohuji DB. Pokud chci jen kouknout, kdo kam kouka nebo co meni, tak to neni zadny problem, ale pokud chci neco zmenit, tak to je trosku narocnejsi na cas (obnova ze zalohy + opetovne provedeni SQL dotazu az do potrebneho okamziku), nastesti se to moc casto nestava.
Datum vložení: 28.7.2005 15:50:15
A nebo pouzit nejakou objektove-persistentni nadstavbu nad relacnim SQL serverem jako je treba DataObjects.NET http://www.x-tensive.com.
Datum vložení: 5.8.2005 16:17:40
http://www.krell-software.com/omniaudit/
Datum vložení: 11.8.2005 0:38:26
Dobrý den, Mám dotaz který možná příliš nesouvisí s tímto článkem, leč dotýká se tématu :-) Chtěl bych se zeptat, jak mám optimalizovat databázi(strukturu tabulek, klíče, apod.) aby byla rychlejší. PŘÍPAD: Potřebuji uložit cca. 100000 záznamů o osobách. O každé osobě musím uložit údaj o VLASTNOSTECH OSOBY tj: zálibách, oblébených tématech, vlastnostech, charakteru apod. Všechny tyto VLASTNOSTI OSOB jsou neměnné a je jich cca. 200. Tutíž nastává situace, že musím uložit u každého ze 100000 lidí jeho vlastnosti, kterých má každý 200(datové typy: ENUM a VARCHAR). OTÁZKA: Mám vše uložit do jedné tabulky VLASTNOSTI, která bude mít 200 sloupců? Nebo do více tabulek, přičemž v každé budou jiná data(tabulka_zaliby, tabulka, charakter, tabulka_x) ? A jak nastavit klíče a indexy? Děkuji moc za Vaše rady !
Datum vložení: 11.8.2005 8:39:55
Abych pravdu rekl, presne jsem nepochopil zadani :-). Ty osoby maji napevno nekolikero vlastnosti, ktere mohou nabyvat ruznych hodnot? Ja bych urcite doporucil variantu s vice tabulkami, ktera je daleko cistsi a prehlednejsi. Tabulka s 200 atributy neni to prave orechove. Tedy osoba a k ni 1:N tabulky vlastnosti. Pripadne pokud je to mozne (neznam podrobnosti) pouze jednu tabulku vlastnosti ve vztahu 1:N ve tvaru ID osoby jako cizi klic, typ vlastnosti, hodnota vlastnosti. Bylo by pak vsechno v jedne tabulce ale otazkou je datovy typ vlastnosti. Pokud by nebyl jednoho datoveho typu musi se pouzit metoda vice tabulek.
Datum vložení: 11.8.2005 12:45:34
Díky, Jěště bych se chtěl zeptat jaké datové typy jsou rychlejší? Mám to ukládat jako varchar? Jako ENUM? Nebo dokonce INT? Možná by použití INT bylo nejrychlejší. V databázi budou uložené jen klíče PHP pole $vlastnosti. a v poli $vlastnosti budou pak skutečné textové hodnoty. např. $vlastnosti[0] = "plavání" $vlastnosti[0] = "jízda na kole" apod. Myslíte, že když budu číst pouze INT hodnoty tak že se značně urychlí čtení dat z MySQL? díky moc za názor ! :-)
Datum vložení: 11.8.2005 17:10:46
Misto varchar radeji char. Pokud se vam v mysql v tabulce vyskytne jen jeden atribut typu varchar nebo text, tabulka se automaticky stava dynamickou a ztracite vykon. Pouzijte char a trimujte. Ovsem char je narocnejsi na zabrane misto, coz se da ozelit.
Datum vložení: 22.8.2005 9:52:05
Tohle jsem řešil již mnohokrát a nakonec se mi nejlépe osvědčilo toto: 1) mam tabulku se sloupci Datum, Autor, Tabulka, Polozka, Nova_hodnota, Schvaleni, Stav 2) dale mam funkci UPDATE ktere predavam jmeno tabuky, ID zaznamu (pokud jde o INSERT tak je hodnota FALSE) a pole hodnot ke změně či vložení které se logují, pole hodnot ke zmene ktere se nelogují (např. heslo). 3) funkce provede INSERT či UPDATE (DELETE se neprovadi, pouze se zmeni atribut stavu na SMAZANO) 4) funkce dale provede zalogova zmen do tabulky logů Výhody: 1) Pomocí další funkce mohlu sledovat, za aktuální stav odpovídá poslednímu logu (kontrola konzistence) 2) Na praci s DB si vystacim pouze s vlastnimi funkcemi a proto je snadne cely system prepsat na jineou DB (upravy provedu pouze v te knihovne funkci) 3) daji se tak zaroven resit takove silenosti jako "falesne" transakce pokud je nepodporuje primo DB a to dokonce bez nebezpečí "křížového uzamčení tabulek"