[MySQL] Veranderingen in database loggen ?

[Verwijderd]

Ik heb het idee dat het wijzelijk is om wijzigingen in een database op bepaalde punten te loggen.

Je zou bijvoorbeeld een loggin kunnen maken van NAW gegevens welke gewijzigd worden, dit ivm het feit dat mensen hun adresgegevens kunnen wijzigen naar iets dat niet overeenkomt.

De vraag is, als je dat zou willen doen, hoe je dat wil doen.

Ik heb een 2-tal manieren in gedachte:

Je maakt een record voor een adres, postcode, etc en maakt hier een kolom bij "old". Als je deze informatie update blijft de oude informatie bestaan in de database en komt er in old een 1-tje te staan en insert je gewoon de nieuwe gegevens. Zo hou je alles bij op userid.

Een 2e mogelijkheid zijn om alle veranderingen op een naw tabel in een bestand te loggen. Dot wordt natuurlijk een vreselijk gezoek als je iets wil weten, maar het kan natuurlijk wel.

Ik stel mijzelf de vraag hoe Hyves en andere sites waar dergelijke informatie van belang kan zijn dit oplossen. Of zouden ze denken van... ach... waarom zouden we het oude adres moeten bewaren ?

[whoami]

Je maakt een history tabel, waarin je een nieuw record insert die de waardes bevat van het record dat gewijzigd wordt (met de waardes voor de wijziging natuurlijk).
Kan gemakkelijk met een trigger.

[BCC]

Je moet eerst weten waarom je dit wil. Wil je zien wie wat gewijzigd heeft? Of wil je ook oude adressen laten zien? Of staan de adressen op een tijdlijn?

Afhankelijk van wat je wil, moet je een implementatie kiezen. Als het puur voor wijzigingen is, dan zou ik inderdaad een audit trail aanleggen met wijzigingen, zoals hierboven aangegeven. Dit kun je trouwens vaak beter in je model-laag doen dan in je database laag (als je die hebt).

[Kettrick]

BCC schreef op maandag 05 januari 2009 @ 15:08:
Dit kun je trouwens vaak beter in je model-laag doen dan in je database laag (als je die hebt).

Zonder verdere gegevens van het project/architectuur kan je daar weinig over zeggen denk ik. In veel gevallen zal een trigger juist de meest eenvoudige oplossing zijn .

[BCC]

offtopic:
Maakt niet welke implementatie, Database logica is altijd evil

[whoami]

BCC schreef op maandag 05 januari 2009 @ 15:33:

offtopic:
Maakt niet welke implementatie, Database logica is altijd evil

Hmm, dergelijke 'logica' doe je imho best met een trigger, want imho is het 'data logica', en geen domain logic.

[Verwijderd]

Ik wil voor mijzelf kunnen inzien wat een gebruiker heeft veranderd. Dit puur uit eigen veiligheid en informatie.

Ik heb er over zitten denken om dit codewise te doen, je haalt je alleen erg veel werk op de hals op die manier.

Tevens heb ik begrepen dat een audit log eigenlijk niet bedoeld is voor dit soort zaken, zijn de menigen wat over verdeeld dus blijkbaar.

[ZaZ]

http://rwijk.blogspot.com...se-triggers-are-evil.html

[kenneth]

BCC schreef op maandag 05 januari 2009 @ 15:33:

offtopic:
Maakt niet welke implementatie, Database logica is altijd evil

Ik word altijd een beetje achterdochtig van formuleringen als $techniek = altijd $waardeoordeel ...
Een history bijhouden heeft toch niks te maken met je domeinlogica? Het is imho puur een technisch aspect, zeker in dit geval zoals de TS aangeeft. En daarbij, using the right tool for the job, met een trigger garandeer je tot op het laagste niveau dat het werkt en hoef je je in je domeinmodel geen zorgen meer te maken over dit soort technische details.

[whoami]

ZaZ schreef op maandag 05 januari 2009 @ 15:43:
http://rwijk.blogspot.com...se-triggers-are-evil.html

Vind ik kort door de bocht.
Niet dat ik triggers & SP's ophemel,verre van. Ik ben zelf voorstander van geen (domein)logica in de DB (cfr het 'dikke db topic' van een tijd geleden), maar dat wil ook niet zeggen dat ik nooit triggers ofzo ga gebruiken.
In sommige gevallen zijn het nu eenmaal de beste oplossing, en imho is dit bv (simple audit/history info bijhouden) een perfecte toepassing.

Van domein logica is er geen sprake, gewoon een trigger die een extra INSERT INTO ... SELECT ... FROM :old statement uitvoert.

Soms moet je ook eens pragmatisch te werk gaan.

en verder met kenneth die perfect verwoord wat ik denk.

[ Voor 3% gewijzigd door whoami op 05-01-2009 15:48 ]

[BCC]

Als je vervolgens ook nog daadwerkelijk iets wil doen met die data (diff van twee objecten trekken, object terugrollen in de tijd, etc), dan wil je dat niet in de DB laag, maar daarom was ook mijn vraag: wat wil de TS precies bereiken? Want een Dynamische Audit Trail implementeren kan heel handig zijn, maar het is ook veel werk.

En triggers hebben zeker hun functie, maar hierbij IMHO niet. Het zorgt ervoor dat je database afhankelijk wordt en dat er 'magisch' regels in je database verschijnen. Kun je uberhaupt wel op een beetje normale manier alle tabellen auditen naar 1 tabel, of moet je dan per tabel een trigger maken die alle velden concat en insert in die audit tabel?

Ik heb het hele zaakje in de model laag en kan bij een model gewoon acts_as_audited aangeven, waarna ik allemaal mooie dingen kan .

[ Voor 9% gewijzigd door BCC op 05-01-2009 15:53 ]

[KabouterSuper]

De database is de logische plek om dit te doen.....of je dit met een trigger doet (simpel, langzamer) of via een pl/sql api (packages nodig, rechten afschermen, sneller) is een keus die je moet maken. Ikzelf zou voor de trigger-aanpak gaan met een auditing tabel voor de oude records. Dit werkt goed en je hoeft de database tabellen niet af te schermen.

[whoami]

BCC schreef op maandag 05 januari 2009 @ 15:52:
Ik heb het hele zaakje in de model laag en kan bij een model gewoon acts_as_audited aangeven, waarna ik allemaal mooie dingen kan .

En hoe doe je dat dan concreet ?

Je decoreert je entity met een attribuut / annotation ofzo en dan ?
Wie interpreteert dit attribuut dan, en hoe bepaal je of iets aan je instance gewijzigd is ? En wie is er uiteindelijk verantwoordelijk voor het persisten van de audit info ?

* whoami curious.

[Kettrick]

BCC schreef op maandag 05 januari 2009 @ 15:52:
Ik heb het hele zaakje in de model laag en kan bij een model gewoon acts_as_audited aangeven, waarna ik allemaal mooie dingen kan .

Het nadeel van die setup is dat je je db praktisch alleen kan bewerken via je applicatie. In de praktijk kom ik regelmatig situaties tegen waarbij "even iets aangepast" moet worden, in dat geval heb je het liever in je database zitten.

In een ideale wereld hoeft dat uiteraard niet, maar daar leven we helaas niet in

[kenneth]

Ik vind al die pl/sql- en Oracle-verhalen uitermate interessant, maar volgens mij gaat het hier over MySQL

[BCC]

whoami schreef op maandag 05 januari 2009 @ 15:59:
[...]

En hoe doe je dat dan concreet ?

Je decoreert je entity met een attribuut / annotation ofzo en dan ?
Wie interpreteert dit attribuut dan, en hoe bepaal je of iets aan je instance gewijzigd is ? En wie is er uiteindelijk verantwoordelijk voor het persisten van de audit info ?

Met het risico dat dit heel off-topic gaat
Heel simpel gezegd hook ik de after_load after_save functies van het ORM (in dit geval ActiveRecord icm Rails).
After load sla je de hash met attributes op in een tijdelijke variable. After save vergelijk je deze met de nieuwe attributes, zodat je een changeset krijgt. Deze changeset gaat in een nieuw Audit model met daarbij de klasse, de actie en de changeset. Het nieuw aanmaken en saven van een medewerker levert bijvoorbeeld op:

code:

Audit.find(:first)
<Audit id: 1, auditable_id: 1, auditable_type: "Employee", user_id: 1, username: 'Administrator', action: "create", changes: {"first_name"=>[nil, 'jan']}, created_at: "2008-01-02 15:31:25">

Door wat hippe functies op de Audit klasse kun je in de tijd zoeken, wijzigingen bekijken en kijken wat mensen users precies uit hebben uitgespookt of wat er met een bepaald object is gebeurd.

RoeLz schreef op maandag 05 januari 2009 @ 16:09:
[...]
Het nadeel van die setup is dat je je db praktisch alleen kan bewerken via je applicatie. In de praktijk kom ik regelmatig situaties tegen waarbij "even iets aangepast" moet worden, in dat geval heb je het liever in je database zitten.

Aangezien Rails standaard een commandline interface heeft met je applicatie vind ik dit juist een voordeel. Voor java begint dit gelukkig nu ook te komen (Groovy of JRuby bijvoorbeeld). Het nadeel van rechtstreeks in je Database trekken is dat je niet door je Business-Rules heen gaat en je dus heel gauw even 'iets' over het hoofd ziet waardoor bijvoorbeeld de data in je database inconsequent wordt... met alle gevolgen van dien. En dan heb ik het uiteraard niet over simpele uniqueness contraints, maar meer dingen als "Als pasen en pinksteren afgelopen jaar op een dag, dan extra voorloop 0 bij code opdrukken"

Einde offtopic

[ Voor 13% gewijzigd door BCC op 05-01-2009 17:25 ]

[Verwijderd]

Ik denk dat de trigger een goede optie is.

Ik vraag me alleen af of ik een dergelijk geval een kopie van de gehele tabel aan zal maken met een extra kolom voor een timestamp en dan een bij iedere wijziging een volledig record "over zal pompen" of niet.

[leuk_he]

De beslissing of je een full record aanmaakt of niet ligt vooral aan hoe je dit gaat gebruiken.

-elke keer een full record kost je (relatief) veel performance en oplsag ruimte, maar data is wel makkelijker op te ruimen. (delete records where last modified date < sysdate -xxx)
-enkel wijzigingen bijhouden maakt het in een interface wel meteen uidelijk wat de wijzingingen zijn, maar voor het reconstureren van oude data moet je door meerdere records en hisotrische delete zijn wat complexer.

[Verwijderd]

Ruimte is opzich niet zo'n probleem meer vandaag de dag gelukkig.

Het is op code niveau makkelijker om de data welke je wilt loggen te inserten denk ik dan via een trigger. Ik heb gezocht maar zie niemand die records welke hij update zo wegschijrft naar een andere tabel zonder PHP code er tussen.

Je kunt natuurlijk wel een update of een insert op een tabel doen, de vraag is alleen of die code uit een andere tabel kan komen.

[leuk_he]

Wat is er mis met dit voorbeeld?

http://db4free.blogspot.c...tables-with-triggers.html

[Verwijderd]

leuk_he schreef op maandag 05 januari 2009 @ 18:29:
Wat is er mis met dit voorbeeld?

http://db4free.blogspot.c...tables-with-triggers.html

Zo te zien weinig... moet je hem wel gevonden hebben thanks.

[BCC]

Dat voorbeeld werkt wel, maar dan moet je dus voor elke tabel een andere log table + trigger bouwen... En hem wijzigen als je iets aan de originele tabel verandert.

[Kettrick]

BCC schreef op maandag 05 januari 2009 @ 18:43:
Dat voorbeeld werkt wel, maar dan moet je dus voor elke tabel een andere log table + trigger bouwen... En hem wijzigen als je iets aan de originele tabel verandert.

Ik weet inhoudelijk weinig van de mysql trigger mogelijkheden, maar met postgres zou je eenvoudig dezelfde trigger op meerdere tabellen kunnen zetten en de logdata opslaan in één tabel.

dan hoef je slechts een create trigger statement uit te voeren om logging aan te zetten.

[Verwijderd]

RoeLz schreef op maandag 05 januari 2009 @ 18:49:
[...]


Ik weet inhoudelijk weinig van de mysql trigger mogelijkheden, maar met postgres zou je eenvoudig dezelfde trigger op meerdere tabellen kunnen zetten en de logdata opslaan in één tabel.

dan hoef je slechts een create trigger statement uit te voeren om logging aan te zetten.

Dat kan met MySQL ook als je wil dacht ik.

PgSQL is niet per defenitie beter, die discussie wil ik hier dus niet aangaan.

[BCC]

Verwijderd schreef op maandag 05 januari 2009 @ 19:09:
[...]
Dat kan met MySQL ook als je wil dacht ik.

Hoe dan? Want volgens mij kan dat niet.

[Verwijderd]

BCC schreef op maandag 05 januari 2009 @ 19:40:
[...]

Hoe dan? Want volgens mij kan dat niet.

Gewoon een goed statement gebruiken lijkt mij.

[Kettrick]

Verwijderd schreef op maandag 05 januari 2009 @ 20:47:
[...]
Gewoon een goed statement gebruiken lijkt mij.

Beetje offtopic ondertussen, maar volgens mij bestaat dat statement niet
Ik copy/paste even iets schaamteloos van de eerste google hit

mysql:

code:

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
...
  END;

De trigger code zit dus in de declaratie, voor zo ver ik weet kan je dat niet recyclen


Postgres :

code:

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
...
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Je kan de create trigger statement op meerdere tables doen, zodat je dezelfde functie gebruikt voor meerdere tables.

[Janoz]

Ikzelf ben op dit moment erg veel bezig met Envers. Het enige wat je hoeft te doen is een paar listeners voor hibernate configureren en je entiteiten annoteren met een 'versioned' annotatie en je bent klaar. Morgen ga ik me met enkele collega's in een ander in hibernate te integreren audittrail plugin verdiepen.