[Database] Content Versioning

[Sluxor]

Besten,

Ik ben samen met een paar collega's van me bezig met het ontwerpen van een nieuwe database voor een private CMS. We hebben allerlei eisen opgesteld waaraan deze nieuwe database moet voldoen. Echter nu bij het prototypen van bepaalde functionaliteiten/eisen lopen we tegen enkele tekortkomingen van de database aan.

Het belangrijkste punt waar we mee zitten is versiebeheer voor content. We gebruiken (nu nog) MySQL 4.1 met de engine InnoDB, later dit jaar zullen we overstappen op MySQL 5 of PostgreSql. Voorlopig moeten we het dus nog even doen met deze verouderde versie van MySQL.

Mijn vraag is dan ook, hebben jullie ervaring met het ontwerpen van databases met versiebeheer in de tabellen verwerkt? De oplossing die we zoeken moet vrij generiek zijn. Met generiek bedoel ik in dit geval dat het geen beperkingen oplegt in de dagelijkse omgang met de database.

Zelf zijn we heel erg dol op de onderstaande constructie:

SQL:

 CREATE TABLE `000IFTests`.`tblContent` (
    `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `Version` INT UNSIGNED NOT NULL DEFAULT '1',
    `Type` INT UNSIGNED NOT NULL ,
    `Title` VARCHAR( 80 ) NOT NULL ,
-- etc.
    PRIMARY KEY ( `ID` , `Version` ) ,
    INDEX ( `Type` )
-- etc.
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci ;

Het nadeel van deze constructie is dat we geen relaties kunnen leggen naar één specifiek content item. Je wordt indirect verplicht om relaties aan één versie te koppelen door de gecombineerde primary key.

De gewenste koppeltabel ziet er ongeveer zo uit:

SQL:

 CREATE TABLE `000IFTests`.`tblContent_Content` (
   `Content1` BIGINT UNSIGNED NOT NULL ,
   `Content2` BIGINT UNSIGNED NOT NULL ,
   `RelationType` INT UNSIGNED NOT NULL ,
   PRIMARY KEY ( `Content1` , `Content2` , `RelationType` )
) ENGINE = InnoDB ;

Helaas werkt dit niet als er relaties gelegd gaan worden (on delete cascade). Als je namelijk één bepaalde versie uit de tabel Content zou verwijderen, zullen alle relaties uit de tabel tblContent_Content ook verdwijnen ongeacht of er nog een versie van hetzelfde content item in de tabel tblContent bestaat of niet.

Hebben jullie hier ervaring mee? Wat kunnen jullie me aanraden betreft dit onderwerp?

Alvast bedankt voor jullie input!

[Janoz]

Kijk eens naar envers. Envers is een uitbreiding op hibernate om een audittrail vast te leggen zonder dat het de normale werking met hibernate in de weg zit. Waarschijnlijk is dat in jullie situatie niet toepasbaar, maar hoe envers werkt en welke tabelstructuur ze gebruiken is wel interresant om eens door te kijken.

[Noork]

Sluxor schreef op vrijdag 12 juni 2009 @ 16:14:
Helaas werkt dit niet als er relaties gelegd gaan worden (on delete cascade). Als je namelijk één bepaalde versie uit de tabel Content zou verwijderen, zullen alle relaties uit de tabel tblContent_Content ook verdwijnen ongeacht of er nog een versie van hetzelfde content item in de tabel tblContent bestaat of niet.

Volgens mij klopt je verhaal niet zo. Ik zou eens goed gaan normaliseren. In feite is het gewoon een order-orderregel verhaal. Als je 1 orderregel verwijdert, is het niet zo dat de gehele order verdwijnt, maar wel uiteraard het record in de koppeltabel.

[remmelt]

Pas op. In je eerste tabel, de "content", heb je een autoincrement. Dat is niet wat je wil.

We hebben object A dat in de db wordt geplaatst, met id 1 en versie 1. Daarna komt een update, de hele boel wordt gekopiëerd naar versie 2 met waar nodig geüpdate inhoud. Dat is versie 2 van het object met id 1. De auto increment zorgt ervoor dat je dan naar versie 2 van object 2 zou gaan (nl object 1 + 1 autoincrement) en je nooit meer bij je originele object kan komen. De gecombineerde PK is wel prima.

Uiteindelijk ziet je tabel er dan zo uit:

id	rev	title
1	1	Schrijffaut
2	1	Fijne content
1	2	Schrijffout

Dat lost dan ook meteen je vraag op om te kunnen linken naar een content item ipv naar een enkele revisie.

[ Voor 25% gewijzigd door remmelt op 12-06-2009 16:35 . Reden: Een plaatje zegt meer dan 1000 woorden. Een tabel zegt meer dan 38 woorden. ]

[Sluxor]

Allereerst, mijn excuses voor de late reactie, ik ben er niet eerder aan toe gekomen.

remmelt schreef op vrijdag 12 juni 2009 @ 16:32:
Pas op. In je eerste tabel, de "content", heb je een autoincrement. Dat is niet wat je wil.

We hebben object A dat in de db wordt geplaatst, met id 1 en versie 1. Daarna komt een update, de hele boel wordt gekopiëerd naar versie 2 met waar nodig geüpdate inhoud. Dat is versie 2 van het object met id 1. De auto increment zorgt ervoor dat je dan naar versie 2 van object 2 zou gaan (nl object 1 + 1 autoincrement) en je nooit meer bij je originele object kan komen. De gecombineerde PK is wel prima.

Volgens mij klopt er iets niet helemaal in deze redenering. Het is in mijn optiek juist wel goed om auto_increment te gebruiken over een ID veld omdat je praat over content ID's. Elk content item behoudt zijn eigen unieke ID maar heeft wel verschillende versies.

Doordat er geen combineerde PK is, zul je dus geen problemen ondervinden bij het aanmaken van een nieuwe versie (althans niet in MySQL), natuurlijk moet je er dan wel voor zorgen dat je het ID wel opgeeft bij het INSERT statement.

result = INSERT INTO tblContent (Name, Version) VALUES ('ContentItem1', 1);
INSERT INTO tblContent (ID, Name, Version) VALUES (result, 'ContentItem1 versie 2', 2);

De bovenstaande code zal prima werken, in MySQL en het lijkt me dat dit ook prima werkt in andere DMBSen.

Noork schreef op vrijdag 12 juni 2009 @ 16:25:
[...]

Volgens mij klopt je verhaal niet zo. Ik zou eens goed gaan normaliseren. In feite is het gewoon een order-orderregel verhaal. Als je 1 orderregel verwijdert, is het niet zo dat de gehele order verdwijnt, maar wel uiteraard het record in de koppeltabel.

En het draait ook juist om het record in de koppeltabel. Als ik één versie van een Contentitem verwijder, zullen de andere versies zeker blijven bestaan, alle records in de koppeltabel die gekoppeld zijn aan het ID zullen wel volledig verdwijnen.

Janoz schreef op vrijdag 12 juni 2009 @ 16:23:
Kijk eens naar envers. Envers is een uitbreiding op hibernate om een audittrail vast te leggen zonder dat het de normale werking met hibernate in de weg zit. Waarschijnlijk is dat in jullie situatie niet toepasbaar, maar hoe envers werkt en welke tabelstructuur ze gebruiken is wel interresant om eens door te kijken.

Bedankt voor de tip, ik zal er zeker naar gaan kijken!


Verder, bedankt voor jullie reacties tot dusver en mijn excuses voor het wat slapjes omschreven probleem. Ik zit op het moment in een ontzettend drukke situatie en heb ook om dit bericht te typen maar heel even de tijd.

[jbvo]

Janoz schreef op vrijdag 12 juni 2009 @ 16:23:
Kijk eens naar envers. Envers is een uitbreiding op hibernate om een audittrail vast te leggen zonder dat het de normale werking met hibernate in de weg zit. Waarschijnlijk is dat in jullie situatie niet toepasbaar, maar hoe envers werkt en welke tabelstructuur ze gebruiken is wel interresant om eens door te kijken.

Ik ben momenteel ook met een systeem bezig waar versies bijgehouden moeten worden en ben dus ook maar even gaan kijken naar envers. Ik kon op de website niet zoveel vinden over de achterliggende design patterns en dergelijke, dus ben ik ook maar even gaan googlen en daarbij kwam ik de volgende pagina op de website van Martin Fowler: http://martinfowler.com/ap2/timeNarrative.html. Hier staat meer uitgelegd over (volgens Fowler) gebruikelijke patterns om versioning/logging toe te passen, waarbij vooral Temporal Object en Temporal Property interessant zijn voor in je applicatie/DAL (OO) en Effectivity voor je database ontwerp (relationeel).

Ik hoop dat je er iets wijzer van wordt.

[Apache]

Janoz schreef op vrijdag 12 juni 2009 @ 16:23:
Kijk eens naar envers. Envers is een uitbreiding op hibernate om een audittrail vast te leggen zonder dat het de normale werking met hibernate in de weg zit. Waarschijnlijk is dat in jullie situatie niet toepasbaar, maar hoe envers werkt en welke tabelstructuur ze gebruiken is wel interresant om eens door te kijken.

Heb zelf gekeken naar envers, alsook naar de functionaliteit van historical sessions met eclipselink

Waarbij die laatste mij meer kon bekoren.

Het is al een tijd geleden, maar volgens mij was die envers meer van plan om per attribuut al bijna een aparte tabel aan te maken waarbij je dan gewoon simpelweg de vorige versie had.

In eclipselink word er een volledige copy van je table gemaakt in de db, bvb user & user_history, waarbij de laatste dan over een van/tot datum beschikt om het query'n in het verleden mogelijk te maken.

Het toffe is dat associaties op dat object ook de historische versie betrof dus je krijgt echt je graph terug zoals hij op dat moment in de tijd was.

Afhankelijk van je exacte requirements is er vast wel iets te vinden.

[Janoz]

SneakyViper schreef op vrijdag 19 juni 2009 @ 17:21:
[...]

Ik ben momenteel ook met een systeem bezig waar versies bijgehouden moeten worden en ben dus ook maar even gaan kijken naar envers. Ik kon op de website niet zoveel vinden over de achterliggende design patterns en dergelijke, dus ben ik ook maar even gaan googlen en daarbij kwam ik de volgende pagina op de website van Martin Fowler: http://martinfowler.com/ap2/timeNarrative.html. Hier staat meer uitgelegd over (volgens Fowler) gebruikelijke patterns om versioning/logging toe te passen, waarbij vooral Temporal Object en Temporal Property interessant zijn voor in je applicatie/DAL (OO) en Effectivity voor je database ontwerp (relationeel).

Ik hoop dat je er iets wijzer van wordt.

Temporal properties en temporal objects zijn absoluut niet hetzelfde als versie beheer! Het zijn twee verschillende tijdsdomeinen. Toevallig heb ik daarover nog niet zo heel lang geleden een praatje gehouden. In het kort komt het op het volgende neer:

Versiebeheer/Audit trail:
Hoe veranderd je data door de tijd.

Bijbehorende vragen die je je database zou moeten kunnen stellen zijn:
-Welk adres heben we bij pietje nu
-Welk adres hadden wij vorig jaar voor pietje in de database staan


Temporal properties:
Hoe veranderd de wereld door de tijd

Bijbehorende vragen die je je database zou moeten kunnen stellen zijn:
-Waar woont pietje nu
-Waar woonde pietje vorige week
-Waar zal pietje volgende week wonen

Ook qua inrichting verschillen ze. Versie beheer wil je zo veel mogelijk afhandelen in je datalaag. Temporal properties daarintegen wil je juist in je business logic hebben. Echt spannend wordt het trouwens pas wanneer je beiden tegelijk implementeerd. Dat is waar ik nu bij mijn huidige project mee te maken krijgt. Je kunt dan bijvoorbeeld vragen stellen als:
Wat dachten we vorige week dat pietje volgende week zou wonen en hoe denken we daar nu over.

[Janoz]

Apache schreef op maandag 29 juni 2009 @ 14:11:
[...]


Heb zelf gekeken naar envers, alsook naar de functionaliteit van historical sessions met eclipselink

Waarbij die laatste mij meer kon bekoren.

Het is al een tijd geleden, maar volgens mij was die envers meer van plan om per attribuut al bijna een aparte tabel aan te maken waarbij je dan gewoon simpelweg de vorige versie had.

In eclipselink word er een volledige copy van je table gemaakt in de db, bvb user & user_history, waarbij de laatste dan over een van/tot datum beschikt om het query'n in het verleden mogelijk te maken.

Het toffe is dat associaties op dat object ook de historische versie betrof dus je krijgt echt je graph terug zoals hij op dat moment in de tijd was.

Afhankelijk van je exacte requirements is er vast wel iets te vinden.

Envers werkt qua tabellen hetzelfde. Een user tabel krijgt een user_aud (of wat je maar instelt voor de naamgeving). Deze tabel heeft echter geen start en eind datum, maar een revisie nummer. Dit revisie nummer geldt voor de hele database. Daarnaast is er nog een apparte tabel met hierin de revisie data. Hierin staat een tijdstip van de revisie. Door een eigen revisie entity te maken kun je hier ook nog andere data aan toevoegen (denk bijvoorbeeld aan een userId van degene die de verandering veroorzaakt heeft).

Start en eind datum heeft technische en juridische nadelen.

De belangrijkste is dat je resolutie wordt beperkt tot het bereik van je datum/tijd veld. Zouden er twee transacties binnen dezelfde milliseconden afgerond worden, dan weet je niet meer welke nu eerder was. Een altijd oplopend versie nummer is in dat geval een stuk veiliger. Mocht je het tijdstip willen weten dan kun je gewoon het bijbehorende revisienummer op kunnen vragen uit de metadata tabel.

Een ander belangrijk nadeel is de einddatum. Dit betekend dat je op je history tabel, naast insert, ook updates uitvoert. Je moet immers een bestaande revisie beeindigen en daar een einddatum aan toevoegen. Het voordeel van deze redundante opslag is dat het redelijk simpel is om vorige versies op te vragen. De vraag is of dat opweegt tegen de nadelen. Zou je namelijk alleen het startmoment opslaan dan hoef je in je audit/versie tabel alleen maar inserts te doen en nooit updates. Technisch zorgt dit er voor dat je de de tabel sterk kunt optimaliseren voor enkel inserts (die eventueel best delayed uitgevoerd mogen worden). Juridisch is het daarnaast ook erg prettig wanneer je een audittrail vast kunt leggen in een tabel waarbij het updaten verboden is.

[glmona]

wat is er mis met timestamps bij houden van een object

id	startdate	enddate	value
1	1-1-2009 12:00:00	12-1-2009 14:45:12	schrijfvaut
2	2-1-2009 12:00:00	NULL	fijne content
1	12-1-2009 14:45:12	NULL	schrijffout

null betekent huidige object
wanneer de enddate ingevuld is weet je dat er een nieuwere versie bestaat

selects kan je dan doen op where enddate is null

[ Voor 10% gewijzigd door glmona op 02-07-2009 17:01 ]

[Apache]

Janoz schreef op maandag 29 juni 2009 @ 15:31:
[...]


Envers werkt qua tabellen hetzelfde. Een user tabel krijgt een user_aud (of wat je maar instelt voor de naamgeving). Deze tabel heeft echter geen start en eind datum, maar een revisie nummer. Dit revisie nummer geldt voor de hele database. Daarnaast is er nog een apparte tabel met hierin de revisie data. Hierin staat een tijdstip van de revisie. Door een eigen revisie entity te maken kun je hier ook nog andere data aan toevoegen (denk bijvoorbeeld aan een userId van degene die de verandering veroorzaakt heeft).

Start en eind datum heeft technische en juridische nadelen.

De belangrijkste is dat je resolutie wordt beperkt tot het bereik van je datum/tijd veld. Zouden er twee transacties binnen dezelfde milliseconden afgerond worden, dan weet je niet meer welke nu eerder was. Een altijd oplopend versie nummer is in dat geval een stuk veiliger. Mocht je het tijdstip willen weten dan kun je gewoon het bijbehorende revisienummer op kunnen vragen uit de metadata tabel.

Een ander belangrijk nadeel is de einddatum. Dit betekend dat je op je history tabel, naast insert, ook updates uitvoert. Je moet immers een bestaande revisie beeindigen en daar een einddatum aan toevoegen. Het voordeel van deze redundante opslag is dat het redelijk simpel is om vorige versies op te vragen. De vraag is of dat opweegt tegen de nadelen. Zou je namelijk alleen het startmoment opslaan dan hoef je in je audit/versie tabel alleen maar inserts te doen en nooit updates. Technisch zorgt dit er voor dat je de de tabel sterk kunt optimaliseren voor enkel inserts (die eventueel best delayed uitgevoerd mogen worden). Juridisch is het daarnaast ook erg prettig wanneer je een audittrail vast kunt leggen in een tabel waarbij het updaten verboden is.

Het één sluit het ander niet uit

Een inserted_date en versienr kan je nog altijd de periodes dat deze records geldig waren mee ophalen, soort van hybride oplossing.

Verder heb ik ook op een project gezeten waarbij beide aanwezig was, daar hadden we een geldig_van, geldig_tot, start_datum, eind_datum, en versioning voor wanneer eender wat wijzigde, dus bvb enkel de naam van dat record dan bleef start & einddatum hetzelfde maar geldigheidsdatums werden wel ingevuld.

Combineer dat met grafen (personen, families & relaties met organisatorische personen) en je kreeg best een model waarvoor je best wakker was om daar wat aan te wijzigen

We maakten trouwens geen gebruik van een framework, zelf wat inelkaar geknutseld met jpa event listeners: http://mikedesjardins.us/...-iii-jpa-event-listeners/ .

[ Voor 3% gewijzigd door Apache op 02-07-2009 17:14 ]

[Domdo]

je zou eventueel ook je einddatums in een aparte tabel kunnen zetten met een relatie naar de revisie-tabel en de einddatum daarin inserten als je revisie klaar is

[Janoz]

glmona schreef op donderdag 02 juli 2009 @ 17:00:
wat is er mis met timestamps bij houden van een object

Ik snap dat mijn verhaal lang was, maar

Janoz schreef op maandag 29 juni 2009 @ 15:31:
De belangrijkste is dat je resolutie wordt beperkt tot het bereik van je datum/tijd veld. Zouden er twee transacties binnen dezelfde milliseconden afgerond worden, dan weet je niet meer welke nu eerder was. Een altijd oplopend versie nummer is in dat geval een stuk veiliger. Mocht je het tijdstip willen weten dan kun je gewoon het bijbehorende revisienummer op kunnen vragen uit de metadata tabel.

Apache schreef op donderdag 02 juli 2009 @ 17:13:
Het één sluit het ander niet uit

Een inserted_date en versienr kan je nog altijd de periodes dat deze records geldig waren mee ophalen, soort van hybride oplossing.

Envers heeft voor elk revisienummer ook gewoon een timestamp. Echter wordt het revisie nummer als key gebruikt en is de timestamp enkel 'meta data' van de revisie. Van een datum kun je dus zo bij een revisie nummer komen (en bij een start en eind datum dus ook bij een start en eind revisie nummer). Met een revisie nummer kun je vervolgens de complete state van de database op dat moment terug halen (bedenk immers dat een revisie nummer voor de hele database geld).

Domdo schreef op donderdag 02 juli 2009 @ 17:35:
je zou eventueel ook je einddatums in een aparte tabel kunnen zetten met een relatie naar de revisie-tabel en de einddatum daarin inserten als je revisie klaar is

Waarom zou je. Die gegevens kun je zo achterhalen. Vraag gewoon de eerste revisie die na de huidige is op. Dat kan simpel middels een where revisie > huidige icm een limit/top en een order by.

[Domdo]

Opzich zou je die gegevens kunnen achterhalen maar in het voorbeeld staan al twee revisies zonder eindatum waardoor ik dacht dat er blijkbaar verschillende revisies doorelkaar lopen, of misschien ben ik dom bezig.

[Janoz]

Domdo schreef op donderdag 02 juli 2009 @ 19:28:
Opzich zou je die gegevens kunnen achterhalen maar in het voorbeeld staan al twee revisies zonder eindatum waardoor ik dacht dat er blijkbaar verschillende revisies doorelkaar lopen, of misschien ben ik dom bezig.

De beide records zonder einddatum gaan over een ander gegeven. Ze hebben immers een andere id.

[Domdo]

ja ok, dat detail heb ik helaas over het hoofd gezien. Dus ik was dom bezig