Tidshåndtering og sporing - del 1 bruksperiode

Tidshåndtering og sporing - del 1 bruksperiode

Tradisjonelt har man tatt vare på hvem som sist har endret dataene og når dette har skjedd. Men dette er ikke tilstrekkelig for å være etterrettelig da man ikke tar vare på tilstanden før endringen. For å få til det må man ta vare på historiske data og kunne spørre informasjonssystemene hvilken informasjon de hadde tilgjengelig på et gitt tidspunkt i tid. 

For å oppfylle lovpålagte krav til sporing av informasjon må vi kunne svare på følgende spørsmål.

  • Hvem har endret informasjonen?
  • Hva er endret?
  • Når har endringen oppstått?
  • Hvilken informasjon hadde vi på et gitt tidspunkt?

Denne artikkelen belyser hva som må til for å etablere sporing ved hjelp av konsistent tidshåndtering og tabellversjonering. Dette er eksemplifisert og det blir også vist hvordan man kan bruke databaseteknologi fra IBM/Oracle for å realisere dette. Artikkelen er en del av en serie.

Tabellversjonering

For å ta vare på tidligere versjoner av informasjonsobjekter så kan man benytte en tenkikk som heter tabellversjonering. I databasen blir informasonsobjekter lagret som rader i tabeller. Tabellversjonering er et konsept som sørger for å lagre unna en kopi av raden som blir endret. For å kunne skille mellom de ulike versjonene må vi legge på metadata-attributter som angir dette.

Det er i hovedsak to måter å versjonere tabeller på:

  1. Alt i en tabell. Man kan legge til ekstra kolonner/metadata (versjonsnr, tidsperioder, m.f.) for å angi siste versjon av raden og for hver gang en rad blir oppdatert så lagers ny rad med nytt versjonnr eller ikkeoverlappende tidsperiode. Raden med høyest versjonsnr, kan f.eks. tolkes som den siste og gyldige. I dette scenariet lagres historiske og gjeldene data i samme tabell. Det er opp til applikasjonen/bruker å skrive spørringer som utnytter dette. Dette kompliserer oppdatering og bruk av gjeldende data.
  2. Skille mellom aktiv og historisk tabell. Man kan flytte de gamle rader inn i en historisk tabell. Denne tilnærmingen er mindre kompleks mhp oppdatering og bruk av gjeldende data.

Tabellversjonering er eneste løsning for å etablere sporing av historiske data. Det finnes softwareløsninger som simulerer tabellversjonering som Hibernate/Envers. Det er mulig å implementere tabellversjonering selv ved hjelp av databasetriggere og lagrede prosedyrer. Men det er nå kommet direkte støtte for tidshåndtering og tabellversjonering i kommersielle databaser som DB2 10+ og Oracle 11g/12c. Utfordringen ligger ikke om vi skal ha tabellversjonering, men heller i hva som er kriteriene for versjonering og hvordan dette kan operasjonaliseres på en enhetlig måte.

Tidshåndtering

I databaseteori er det to velkjente tidsbegrep: valid time og transaction time. Valid time kan oversettes til gyldig tid som angir når dataene er gyldige i virkeligheten (kommer tilbake til dette i del II). Transaction time er også kalt databasetid eller systemtid og det er tid som forvaltes/settes av databasen og er den tiden som data blir opprettet, endret eller slettet i databasen, dvs. tiden dataene er tilgjengelig og kan brukes av databaseklienter. Dato og tidsstempel har lenge vært viktige datatyper og de fleste databaser har ulike datoattributter som har forskjellig semantikk. Vi kan definere to dato-attributter: bruk_fra og bruk_til. Dette er to datoer som databasen i utgangspunktet håndteres uavhengig av databasesystemet. Men sett fra en brukers eller applikasjons ståsted utgjør disse faktisk en periode som angir når dataene er, eller har vært, i bruk.  I siste versjon av SQL standardenså defineres periode som en egen datatype som inneholder tidspunktet fra og med en gitt fratidspunkt til, men ikke inkludert, tiltidspunktet. Grunnen til denne definisjonen er at det er lettere å regne på det og å uttrykke sammenhengende (ikke-overlappende) perioder: eks. [2013-10-12, 2014-03-01), [2014-03-01, 2015-12-30). For å definere en sammenhengende periode så setter man samme tildato på foregående periode som fradato på neste periode. 

Bruksperiode

La følgende eksempel illustrere hensiktsmessigheten til tabellversjonering med hensyn på bruksperiode. Vi har en tabell som holder styr på en persons adresse og husleie og så gjennomfører vi følgende transaksjoner:

  1. Per blir registrert første gang med adresse Oslo og husleie 9300 av bruker B1.
  2. Pers husleie blir oppdatert til 20000 av bruker B2.
  3. Pers adresse og husleie blir oppdatert til London, 15000 av bruker B3.
  4. Pers adresse og husleie endres til Paris, 12000 av bruker B4.

Vi oppretter en tabell med tre attributter: person, adresse og husleie. Så lager vi to metadata-attributter registrert_av og bruksperiode som har bruk_fra og bruk_til.

Transaksjon 1: Utføres 2009-01-01 - Per blir registrert første gang med adresse Oslo og husleie 9300 av bruker B1

Person

Adresse

Husleie

registert_av

bruksperiode 

Per

Oslo

9300

B1

2009-01-01 – 9999-12-31


Bruksperioden angir hvor lenge dataene er tenkt brukt. Her bruker vi 9999-12-31 til å angi at raden kan bli brukt i all overskuelig fremtid.

Transaksjon 2: Utføres 2013-02-03 - Pers husleie blir oppdatert til 20000 av bruker B2

Når oppdateringen utføres så skal ikke lengre den gamle informasjonen være i bruk. Vi må derfor lukke tidsperioden for den raden og lage en ny rad som angir den nye informasjonen som kan brukes fra 2013-02-03.

Person

Adresse

Husleie

registert_av

bruksperiode 

Per

Oslo

9300

B1

2009-01-01 – 2013-02-03

Per

Oslo

20000

B2

2013-02-03 – 9999-12-31


Transaksjon 3: Utføres 2013-12-05 - Pers adresse og husleie blir oppdatert til London, 15000 av bruker B3

Som i transaksjon 2 så lukker vi den raden som ikke lengre skal være i bruk og legger til en ny med riktig startdato.

Person

Adresse

Husleie

registert_av

bruksperiode 

Per

Oslo

9300

B1

2009-01-01 – 2013-02-03

Per

Oslo

20000

B2

2013-02-03 – 2013-12-05

Per London 15000 B3 2013-12-05 – 9999-12-31


Transaksjon 4: Utføres 2014-02-20 - Pers adresse og husleie endres til Paris, 12000 av bruker B4

Som i transaksjon 3 så lukker vi den raden som ikke lengre skal være i bruk og legger til en ny med riktig startdato.

Person

Adresse

Husleie

registert_av

bruksperiode 

Per

Oslo

9300

B1

2009-01-01 – 2013-02-03

Per

Oslo

20000

B2

2013-02-03 – 2013-12-05

Per London 15000 B3 2013-12-05 – 2014-02-20
Per Paris 12000 B4 2014-02-20 – 9999-12-31


Ved å følge de enkle fremgangsmåten ved å lukke bruksperioden på de gamle radene etterhvert som ny informasjon legges til kan vi spore hva som er endret og hvem som gjorde det. Ved å se kun på bruksperioden kan vi lett finne ut i hvilken tidsperiode informasjonen har vært i bruk. Vi kan også lett finne den siste raden som er i bruk, det er den med bruk_til lik 9999-12-31. 

Historietabell

For å gjøre det lettere og (mer effektivt) for applikasjonen så kan man flytte de gamle radene over i en historietabell. Dermed trenger ikke applikasjonen å ta hensyn til data som ikke lengre er i bruk i spørringer. Vi må derimot sørge for at data flyttes over ved oppdateringer. Merk at det ikke er applikasjonen som skal sette dato/tidsstempel på bruksperioden, men databasen. Historietabellen holder historiske data (rader), men med den forskjell at bruksperioden er lukket. 

Aktiv-tabell

Person

Adresse

Husleie

registert_av

bruksperiode 

Per Paris 12000 B4 2014-02-20 – 9999-12-31


Historietabell

Person

Adresse

Husleie

registert_av

bruksperiode 

Per

Oslo

9300

B1

2009-01-01 – 2013-02-03

Per

Oslo

20000

B2

2013-02-03 – 2013-12-05

Per London 15000 B3 2013-12-05 – 2014-02-20


Databaseeksempel

Her angis hvordan man kan etablere tabellversjonering i IBMs DB2 og i Oracles 12c. Videre vises hvordan man kan angi bruksperiode og få spørringer til å rapportere historiske data.

Den store forskjellen mellom de to er at Oracle bruker NULL-verdi i periodene for å angi uendelig mens IBM bruker en definert maksverdi. Oracle tillater ikke egne navn for bruksperiode og bruker sine egne metadata-attributter. DB2 krever at man må sette opp tidsperiode og tidsversjoneringsid på en spesiell måte.


DB2 version 10+

Opprett tabell

CREATE TABLE adresse(
    navn VARCHAR(10) NOT NULL,
    adresse VARCHAR(15) NOT NULL,
    husleie REAL NOT NULL,
    registrert_av VARCHAR(10) NOT NULL,
    bruk_fra TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
    bruk_til TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS ROW END,
    ts_id TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD system_time (bruk_fra, bruk_til));
 
CREATE TABLE adresse_historie LIKE adresse;
 
ALTER TABLE adresse ADD VERSIONING USE HISTORY TABLE adresse_historie;

Hente ut historiske data

-- hvordan var tilstanden på et gitt tidspunkt
SELECT FROM adresse FOR system_time AS OF '2013-06-01';
 
-- alle endringer siden 2000-01-01 for Per
SELECT FROM adresse 
FOR system_time BETWEEN '2000-01-01' AND '9999-12-31' 
WHERE navn = 'Per';
 
-- endre systemtid for eksisterende spørringer
SET CURRENT TEMPORAL system_time = '2013-06-01';


Oracle 12c

Opprett tabell

CREATE TABLE adresse(
    navn VARCHAR(10) NOT NULL,
    adresse VARCHAR(15) NOT NULL,
    husleie REAL NOT NULL,
    registrert_av VARCHAR(10) NOT NULL);
 
ALTER TABLE adresse FLASHBACK ARCHIVE;

Oracle lager automatisk flere metadata-attributter for å håndtere versjoner. De viktigste i vår sammenheng er versions_starttime og versions_endtime, som begge er tidsstempler og som tilsammen definerer bruksperioden. Andre metadata-attributter er: versions_startscn, versions_endscn, versions_xid, og versions_operation.

Hente ut historiske data

-- hvordan var tilstanden på et gitt tidspunkt
SELECT FROM adresse AS OF TIMESTAMP TO_TIMESTAMP('2013-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
 
-- alle endringer siden 2000-01-01 for Per
SELECT navn, adresse, registrert_av, versions_starttime, versions_endtime FROM adresse 
VERSIONS BETWEEN TIMESTAMP 
    TO_TIMESTAMP('2000-01-01 00:00:00''YYYY-MM-DD HH24:MI:SS'AND 
    TO_TIMESTAMP('9999-12-31 00:00:00''YYYY-MM-DD HH24:MI:SS'
WHERE navn = 'Per';
 
-- endre systemtid for eksisterende spørringer
EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(TIMESTAMP '2013-06-01 12:00:00');

Oppsummering tabellversjonering mhp bruksperiode

I dag er det enkelt å realisere sporing i databaser. De største databaseleverandørene har  støtte for tabellversjonering. Det som er interessant er at denne teknikken kan brukes på eksisterende databaser. Man kan identifisere hvilke tabeller man ønsker sporing på og så endre tabellene til å støtte dette. Dette kan gjøres uten å endre eksisterende applikasjoner da disse går mot aktiv tabell som har siste versjon av raden, mens databasen lagrer unna endringer i historietabellen.  Ved behov kan man "lure" databasen ved å sette systemtid tilbake i tid. Databasen sørger for å skrive om spørringene til å gå mot historietabellen og returnere data som var i bruk på det tidspunktet. Dette er mulig fordi vi for hver versjon tar vare på når og hvor lenge raden var i bruk. Selv om man nå må ta vare på en del mer data så er dette en enkel måte å etablere sporing på. Vi kan til og med lage spørringer (views) som angir hva som er endret og hvem som har gjort endringen for en gitt tidsperiode helt uten å måtte rulle databasen tilbake.

Referanser

Skogan, David

Konsulent
david.skogan@acando.no
comments powered by Disqus