Tabele czasowe (temporal tables) na przykładzie DB2

W grudniu 2011 (czyli już sporo czasu temu...) został opublikowany standard SQL:2011. Główną nowością tego standardu były tabele czasowe (ang temporal tables). Celem ćwiczenia jest sprawdzenie implementacji standardu przez bazę DB2.

Definicja tabel czasowych mówi, że to takie tabele, których wiersze mają przypisany okres czasu. Rozróżniamy dwa rodzaje okresów:

• "system time" czyli po prostu historia zmian (baza sama dba o wypełnianie odpowiednich dat)
• "application time" - aplikacja określa okres ważności danego wiersza - ta sama dana może istnieć w kilku wersjach, różniących się między sobą datami ważności.

Wszystkie testy przeprowadzam na DB2 v11.1 Mod Pack 4 Fix Pack 4. kontener z tą wersją jest dostępny za darmo (trzeba się tylko zalogować oraz zaakceptować licencję) z https://hub.docker.com/_/db2-developer-c-edition

Przypadek 1: historia zmian (system-time temporal tables, SYSTEM_TIME)

Nowe konstrukcje sql

Tabele podlegające historii muszą posiadać kolumny oznaczające początek i koniec okresu kiedy dana wersja obowiązywała. Są to tzw identity columns, definiuje się je jako typu DATE lub TIMESTAMP oraz oznacza jako GENERATED ALWAYS AS ROW BEGIN dla daty początku oraz odpowiednio GENERATED ALWAYS AS ROW END dla daty końcowej. Trzeba jeszcze zdefiniować, że określona para kolumn początek-koniec będzie razem stanowiła okres czasu: PERIOD SYSTEM_TIME (start_column_name, end_column_name).

Dla DB2 trzeba jeszcze dodać dodatkową kolumnę (również typu DATE lub TIMESTAMP) zdefiniowaną jako GENERATED ALWAYS AS TRANSACTION START ID - zapisywany tam będzie początek transakcji, w której określone wiersze zostały zmienione.

Dane testowe

Prosta tabela, dla której chcemy utrzymywać historię zmian:

CREATE TABLE SAMPLEDATA
(
    ID                INTEGER,
    NAME              VARCHAR(100),
    ROW_DATE_START    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
    ROW_DATE_END      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
    TRANSACTION_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (ROW_DATE_START, ROW_DATE_END)
);

Tabela z historią (najprościej stworzyć ją na podstawie poprzedniej):

CREATE TABLE SAMPLEDATA_HIST LIKE SAMPLEDATA;

..i spinamy je razem (nakazując zapis historii):

ALTER TABLE SAMPLEDATA
    ADD VERSIONING USE HISTORY TABLE SAMPLEDATA_HIST;

Jeszcze potrzebujemy danych testowych:

INSERT INTO SAMPLEDATA(ID, NAME) VALUES (1, 'name1');
INSERT INTO SAMPLEDATA(ID, NAME) VALUES (2, 'name2');

Dane w tabeli głównej:

..i zgodnie z oczekiwaniami pusta tabela historyczna:

Test

Robimy pierwszą zmianę:

UPDATE SAMPLEDATA SET NAME='name1-1'where ID = 1;

i ponownie tabela główna:

...oraz historyczna

... i kolejną (drugą) zmianę:

UPDATE SAMPLEDATA SET NAME='name1-2'where ID = 1;

i ponownie tabela główna:

...oraz historyczna

Jak widać historia się ładnie zapisuje bez żadnych dodatkowych obowiązków z naszej strony.

Żeby mieć pełen obraz sytuacji, usunę jeszcze zmieniany wcześniej wiersz:

DELETE FROM SAMPLEDATA WHERE ID = 1;

W tabeli głównej pozostał jeden wpis:

a w historycznej pojawia się wpis z ROW_DATE_END równym czasowi wykonania delete (czyli do kiedy istniał wiersz):

Zapytania

Jak widać powyżej obie tabele można odpytywać standardowym sql (również korzystając z warunków na wartości w kolumnach (ROW_DATE_START, ROW_DATE_END, czyli tak jak robilibyśmy to, gdybyśmy tabelę z historią utrzymywali ręcznie). Można również zastosować nowe warunki FOR SYSTEM_TIME i w ten sposób zlecić bazie znalezienie odpowiedniego wpisu (w tabeli podstawowej i/lub histori):

• FOR SYSTEM_TIME AS OF <data>

pytamy jak wyglądały dane w danym punkcie czasu, np.:

select * from SAMPLEDATA
FOR SYSTEM_TIME AS OF '2019-03-23 17:18:00'
where ID = 1;

i wynik:

• FOR SYSTEM_TIME FROM <data1> TO <data2>

szukamy wszystkich wpisów (aktualnych i historycznych), których data początku ROW_DATE_START<data2 oraz data końca ROW_DATE_END>data1. Wiersze gdzie data1>=data2 nie są zwracane.

Przykład: jako zakres podajemy dokładny czas kiedy istniał wpis pod nazwą name1-1

SELECT * FROM SAMPLEDATA
    FOR SYSTEM_TIME FROM '2019-03-23 17:17:55.353894000' TO '2019-03-23 17:19:40.731336000'
WHERE ID = 1;

i dokładnie pasujący wynik:

• FOR SYSTEM_TIME BETWEEN <data1> AND <data2>

szukamy wszystkich wpisów (aktualnych i historycznych), gdzie daty zawierają się lub są równe podanym datom: ROW_DATE_START<=date2oraz ROW_DATE_END>=data1

Przykład: dla tych samych czasów co powyżej:

SELECT * FROM SAMPLEDATA
   FOR SYSTEM_TIME BETWEEN  '2019-03-23 17:17:55.353894000' AND '2019-03-23 17:19:40.731336000'
WHERE ID = 1;

otrzymujemy już dwa wpisy:

Przypadek 2: okres ważności (application-time temporal tables, BUSINESS_TIME)

Nowe konstrukcje sql

Podobnie jak w przypadku historii, musimy ustalić dwie kolumny określające czas początku oraz końca ważności (typu DATE lub TIMESTAMP), z tą różnicą, że tym razem to aplikacja jest odpowiedzialna za wypełnianie tych pól. Drugie wymaganie dla tych kolumn jest takie, że nie mogą przyjmować pustych wartości (w DDL muszą być oznaczone jako NOT NULL). Podobnie jak poprzednio trzeba zdefiniować, że określona para kolumn początek-koniec będzie razem stanowiła okres czasu: PERIOD BUSINESS_TIME (start_column_name, end_column_name).

W przeciwieństwie do tabel historycznych dane będą zapisywane w jednej tabeli, więc konieczny też będzie PK zawierający ID oraz zdefiniowane okresy czasu (np. PRIMARY KEY(ID, BUS_START_DATE, BUS_END_DATE)). Jeśli chcemy, żeby to baza za nas pilnowała, żeby podane czasy nie nakładały się na siebie, trzeba użyć konstrukcji PRIMARY KEY(ID, BUSINESS_TIME WITHOUT OVERLAPS).

Dane testowe

Załóżmy, że mamy tabelę gdzie notujemy stawki VAT dla określonych grup produktów.

CREATE TABLE VAT
(
    ID             INTEGER NOT NULL,
    PRODUCT_GROUP  INTEGER,
    RATE           FLOAT NOT NULL,
    BUS_START_DATE DATE  ,
    BUS_END_DATE   DATE  ,
    PERIOD         BUSINESS_TIME(BUS_START_DATE, BUS_END_DATE),
    PRIMARY KEY (ID, BUSINESS_TIME WITHOUT OVERLAPS)
);

oraz dwie stawki VAT wraz z ich okresem obowiązywania:

INSERT INTO VAT(ID, PRODUCT_GROUP, RATE,  BUS_START_DATE, BUS_END_DATE) VALUES (1, 1, 12, '2010-01-01', '2011-11-11');
INSERT INTO VAT(ID, PRODUCT_GROUP, RATE,  BUS_START_DATE, BUS_END_DATE) VALUES (1, 1, 20, '2012-01-01', '9999-12-31');

I dla porządku zawartość tabeli:

Test

Na początek spróbóję dodać nakładający się czas:

INSERT INTO VAT(ID, PRODUCT_GROUP, RATE, BUS_START_DATE, BUS_END_DATE) VALUES (1, 1, 20, '2020-01-01', '9999-12-31');

Zgodnie z przewidywaniami baza zgłosi nam naruszenie indeksu unikalności (podany okres nakłada się na już istniejący ze stawką 20%).

Dodanie nowego okresu realizujemy więc przez UPDATE:

UPDATE VAT
    FOR PORTION OF BUSINESS_TIME FROM '2019-05-01' TO '9999-12-31'
SET RATE=25 WHERE PRODUCT_GROUP=1;

Baza sama dokonała obliczeń i skróciła czas obowiązywania stawki 20% tak, żeby nie powstał konflikt czasowy z nowo wprowadzaną stawką 25%:

Podobnie jest z "usuwaniem" z zdanego okresu czasu:

DELETE FROM VAT
    FOR PORTION OF BUSINESS_TIME FROM '2019-06-01' TO '2022-12-31'
WHERE PRODUCT_GROUP=1;

jeśli zostaną znalezione pasujące wpisy (zarówno nakładające się okresem czasu jak i zgodne w stosunku do pozostałych kryteriów z WHERE) to baza dostosuje je tak, aby ich czasy nie objemowały podanego w DELETE zakresu. W naszym przypadku okres obowiązywania stawki 25% został podzielony na dwa okresy

Jeśli dana operacja nie wymaga zmiany czasu, to baza po prostu wykonuje ją na wierszach pasujących do kryteriów:

UPDATE VAT
    FOR PORTION OF BUSINESS_TIME FROM '2019-05-01' TO '9999-12-31'
SET RATE=35 WHERE PRODUCT_GROUP=1;

zmieni tylko dwa ostatnie wpisy (ich daty zawierają się w przedziale podanym w powyższym UPDATE):

Niestety, jeśli oprócz nakładania się zakresów potrzebujemy również dbać o ich ciągłość, to (póki co) baza za nas tego nie zrobi.

Zapytania

Podobnie jak dla historii nowe konstrukcje pozwalają odpytywać o konkretny punkt w czasie lub przedziały, po prostu zamiast SYSTEM-TIME używamy BUSINESS_TIME:

SELECT * FROM VAT
    FOR BUSINESS_TIME AS OF '2019-05-03'
WHERE PRODUCT_GROUP=1;

Przypadek 3: okres ważności oraz historia zmian na raz (bi-temporal tables)

Dane testowe

Wszystkie nowe konstrukcje sql zostały omówione już wcześniej, jedyne co pozostało to zastosować oba typy jednocześnie podczas tworzenia nowej tabeli:

CREATE TABLE EMPLOYEE
(
    ID                INTEGER       NOT NULL,
    DEPARTMENT        INTEGER,
    ADDRESS           VARCHAR(100),
    SAlARY            FLOAT,
    BUS_START_DATE    DATE          NOT NULL,
    BUS_END_DATE      DATE          NOT NULL,
    ROW_DATE_START    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
    ROW_DATE_END      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
    TRANSACTION_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (ROW_DATE_START, ROW_DATE_END),
    PERIOD BUSINESS_TIME (BUS_START_DATE, BUS_END_DATE),
    PRIMARY KEY (ID, BUSINESS_TIME WITHOUT OVERLAPS)
);
CREATE TABLE EMPLOYEE_HIST LIKE EMPLOYEE;

ALTER TABLE EMPLOYEE
    ADD VERSIONING USE HISTORY TABLE EMPLOYEE_HIST;

i wstawiamy dane pracownika:

INSERT INTO EMPLOYEE(ID, DEPARTMENT, ADDRESS, SALARY, BUS_START_DATE, BUS_END_DATE)
    VALUES (1,1,'gdzies',10,'2018-09-20','9999-12-31');

Widok tabeli głównej:

tabela historyczna na razie jest pusta:

Test

HR już dostały informacje, że od maja pracownik zostanie przeniesiony do innego działu:

UPDATE EMPLOYEE
    FOR PORTION OF BUSINESS_TIME FROM '2019-05-01' TO '9999-12-31'
SET DEPARTMENT=2 WHERE ID=1;

Widok tabeli głównej:

tymczasem w historii mamy poprzedni wpis

w między-czasie pan (lub pani) zmienia adres zamieszkania:

UPDATE EMPLOYEE
SET ADDRESS='gdzies indziej' WHERE ID=1;

Widok tabeli głównej:

oraz historycznej:

... a na koniec, jescze przed przejściem do następnego działu pracownik zostaje zwolniony:

DELETE FROM EMPLOYEE WHERE ID=1;

Pusta tabela główna:

i aktualna historyczna

Zapytania

Dla tabel bi-temporal możemy korzystać dowolnie z konstrukcji dla BUSINESS_TIME oraz SYSTEM_TIME (możemy użyć jednej z nich, obu albo żadnej).

Nasz pracownik poszedł do sądu i twierdzi, że zwolnienie było niesłuszne i wynikało, ze złej woli kierownika, który chciał go ukarać za "ucieczkę" do innego działo. Jego teza opiera się na tym, że już przed zwolnieniem było wiadomo, że od maja ma pracować w innym dziale i że zwolnienie nastąpiło po fakcie ustalenia przenosin. Prosi też sąd o sprawdzenie bazy danych HR gdzie na pewno powinna być taka informacja:

SELECT * FROM EMPLOYEE
    FOR BUSINESS_TIME AS OF '2019-05-01'
    FOR SYSTEM_TIME FROM '2019-03-01' TO '2019-03-30'
WHERE ID=1 AND DEPARTMENT=2;

Jak widać pracownik nie kłamał i miał już zaplanowany angaż w innym dziale od maja (BUS_START_DATE) i było to zaplanowane wcześniej (ROW_DATE_START pierwszego wiersza) niż jego zwolnienie (ROW_DATE_END drugiego wiersza).

Przypadek 4: integralność odwołań (referential integrity)

Skoro mamy już bazę relacyjną, to chcielibyśmy wykorzystać ją do zapisu relacji wraz z sprawdzaniem integralności odwołań (referenial integrity). Załóżmy więc, że podczas pracy w danym dziale pracownik podlegał regularnym ocenom. Oceny powinny być ściśle powiązane z okresem pracy.

CREATE TABLE RATINGS
(
    id             INTEGER not null,
    EMP_ID         INTEGER NOT NULL,
    description    VARCHAR(1000),
    BUS_START_DATE DATE    NOT NULL,
    BUS_END_DATE   DATE    NOT NULL,
    PRIMARY KEY (ID),
    PERIOD BUSINESS_TIME (BUS_START_DATE, BUS_END_DATE),
    FOREIGN KEY FK_1 (EMP_ID, PERIOD BUSINESS_TIME)
        REFERENCES EMPLOYEE (ID, PERIOD BUSINESS_TIME)
);

i... pierwsza skucha. An unexpected token "BUSINESS_TIME" was found following "FK_1 Cóż... okazuje się, że takie rzeczy to dopiero w DB 12 i w dodatu na z/Os

Da się nieco pokombinować i zrobić to nieco inaczej:

CREATE TABLE EMPLOYEE
(
    ID                INTEGER       NOT NULL,
    DEPARTMENT        INTEGER,
    ADDRESS           VARCHAR(100),
    SAlARY            FLOAT,
    BUS_START_DATE    DATE          NOT NULL,
    BUS_END_DATE      DATE          NOT NULL,
    ROW_DATE_START    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
    ROW_DATE_END      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
    TRANSACTION_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (ROW_DATE_START, ROW_DATE_END),
    PERIOD BUSINESS_TIME (BUS_START_DATE, BUS_END_DATE),
    PRIMARY KEY (ID, BUS_START_DATE, BUS_END_DATE)
);

ALTER TABLE EMPLOYEE  ADD CONSTRAINT uniqq_1 UNIQUE(ID, BUSINESS_TIME WITHOUT OVERLAPS);

create table RATINGS
(
    ID INTEGER not null,
    EMP_ID INTEGER NOT NULL,
    DESCRIPTION VARCHAR(1000),
    BUS_START_DATE DATE  NOT NULL,
    BUS_END_DATE   DATE  NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY FK_TO_EMPLOYEE (EMP_ID,BUS_START_DATE, BUS_END_DATE)
        REFERENCES EMPLOYEE (ID,BUS_START_DATE, BUS_END_DATE) ON UPDATE NO ACTION
);

możemy tam potem wstawić dane...

    insert into EMPLOYEE(ID, DEPARTMENT, ADDRESS, SALARY, BUS_START_DATE, BUS_END_DATE)
    VALUES (1,1,'jakas ulica',10,'2018-09-20','9999-12-31');

insert into RATINGS (ID, EMP_ID, DESCRIPTION, BUS_START_DATE, BUS_END_DATE)
VALUES(1,1,'bardzo dobrze', '2018-09-20','9999-12-31')

i to by było na tyle..

FK_TO_EMPLOYEE jest ON UPDATE NO ACTION (może być jeszcze restrict) -> więc każda zmiana dla istniejącego wiersza w EMPLOYEE powinna odpowiadać tym w RATINGS:

sql> UPDATE EMPLOYEE
         FOR PORTION OF BUSINESS_TIME FROM '2019-05-01' TO '9999-12-31'
     SET DEPARTMENT=2 WHERE ID=1
[2019-06-07 19:54:25] [23504][-531] The parent key in a parent row of relationship "TEST.FK_TO_EMPLOYEE cannot be updated.. SQLCODE=-531, SQLSTATE=23504, DRIVER=4.23.42

niby wszystko logiczne ale jednak spodziewałem się jakiejś bardziej zaawansowanej obsługi.... No nic, jak już wyżej pisałem trzeba poczekać aż nowości z Db2 z/OS trafią do normalnych wersji...

Przypadek 5: dogrywka z zapytań

Jeśli nie chcemy zmieniać istniejących już zapytań, możemy skorzystać z tkzw specjalnych rejestrów i ustawić pożądany czas przed wykonaniem zapytania. W zależności od okresu mamy dostępne:

• TEMPORAL SYSTEM_TIME
• TEMPORAL BUSINESS_TIME

Wykonanie komend

SET CURRENT TEMPORAL SYSTEM_TIME = TIMESTAMP '2019-03-23 17:18:00';
SELECT * FROM SAMPLEDATA WHERE ID = 1;

da taki sam wynik jak

SELECT * FROM SAMPLEDATA
FOR SYSTEM_TIME AS OF '2019-03-23 17:18:00'
WHERE ID = 1;

czyli:

Trzeba oczywiście pamiętać o wyczyszczeniu rejestrów jeśli nie chcemy z nich więcej korzystać

TODO

• sprawdzić funkcjonalność na pozostałych bazach (Postgres, Oracle, MSSql)

Literatura

• PDF: Temporal features in SQL:2011
• Time Travel Query using temporal tables w oficjalnej dokumentacji DB2