Tabela - Promet

Tabele historyczne
w PostgreSQL
Rafał Piechocki
Promet
Tabele historyczne
wprowadzenie
Cele i korzyści
Cele i korzyści stosowania tabel historycznych
• Archiwum wersji
Podstawowe • Pomoc w sporze
• Śledzenie zmian
Dodatkowe
• PITR
• Backup danych
• Wyłapywanie błędów
Realizacja
tabel historycznych
Realizacja tabel historycznych
Aplikacja
ORM
Baza danych
Serwer bazy danych
Czynniki, które należy brać pod uwagę
Wpływ na
wydajność
Przeźroczystość
rozwiązania
Czynniki?
Łatwość dostępu
do archiwum
Łatwość
implementacji
Przykładowe
implementacje
1. Osobna tabela – change logi
Tabela podstawowa + tabela change logów
ZDJECIE (tabela podstawowa)
id
pesel
status
1
85050500007
0
2 (update)
87070700005
-1
CHANGE_LOG
komunikat
pracownik
Promet
Nie kolorowe
Promet
CHANGE_LOG_VALUE
#
tabela
id
data
operacja
#
kolumna
stara
nowa
3
zdjecie
1
2012-04-01
insert
4
status
0
-1
4
zdjecie
2
2012-12-08
update
4
komunikat
Nie kolorowe
4
pracownik
Promet
1. INSERT INTO dz_oceny (os_id,toc_kod,wart_oc_kolejnosc,prot_id,term_prot_nr)
VALUES ('5094','ZAL-STD','1','136651','1');
2.
INSERT INTO M_CHANGE_LOG (TABLE_NAME, MOD_TIME, MOD_TYPE)
VALUES ('dz_oceny', now(), 'insert');
3.
SELECT MAX(id) as max FROM M_CHANGE_LOG;
4.
INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE)
VALUES (8044, 'os_id', '5094');
5.
INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE)
VALUES (8044, 'toc_kod', 'ZAL-STD');
6.
INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE)
VALUES (8044, 'wart_oc_kolejnosc', '1');
7.
INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE)
VALUES (8044, 'prot_id', '136651');
8.
INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE)
VALUES (8044, 'term_prot_nr', '1');
Tabele typu change log - podsumowanie
Zalety:
• Łatwość wykrywania zmian
• Łatwość migracji danych
• Testowanie spójności danych
Wady:
• Wydajność (liczba zapytań)
• Trudność implementacji
• Nieprzeźroczystość?
Przykładowe
implementacje
2. Tabela + widok
Tabela + widok
ZDJECIE (tabela podstawowa)
id
waznosc
pesel
status
1
2012-04-08
85050500007
0
1
2012-04-15
85050500007
-1
1
2012-08-07
85050500007
0
1
infinity
85050500007
1
2
infinity
87070700007
0
komunikat
pracownik
Nie kolorowe
Promet
Zdjęcie OK
Promet
Filtr: WHERE waznosc=infinity
V_ZDJECIE (widok dla tabeli podstawowej)
id
waznosc
pesel
status
komunikat
pracownik
1
infinity
85050500007
1
Zdjęcie OK
Promet
2
infinity
87070700007
0
Tabela podstawowa
ZDJECIE (tabela podstawowa)
id
waznosc
pesel
status
1
2012-04-08
85050500007
0
1
2012-04-15
85050500007
-1
1
2012-08-07
85050500007
0
1
infinity
85050500007
1
2
infinity
87070700007
0
komunikat
pracownik
Nie kolorowe
Promet
Zdjęcie OK
Promet
Tabela zawiera zarówno bieżące dane jak i archiwalne.
Klucz główny: (ID + waznosc), ograniczenia (pesel + waznosc)
Triggery INSERT, UPDATE, DELETE:
 Oznaczają bieżący rekord jako wygasły
 Wstawiają nowy rekord oznaczony jako ważny
 Chronią archiwalne dane przed zmianą
Widok dla tabeli podstawowej
V_ZDJECIE (widok dla tabeli podstawowej)
id
waznosc
pesel
status
komunikat
pracownik
1
infinity
85050500007
1
Zdjęcie OK
Promet
2
infinity
87070700007
0
Filtr wybiera z tabeli podstawowej wyłącznie te rekordy, które nie wygasły.
Widok zawiera zatem wyłącznie bieżące dane.
Możliwość wprowadzenia reguł dla widoku = możliwość wykonywania na
widoku operacji:
 INSERT
 UPDATE
 DELETE
Demonstracja
Tabela + widok
Tabela + widok - podsumowanie
Zalety:
• Łatwy dostęp do archiwum
• Bezpieczeństwo danych
Wady:
•
•
•
•
Klucze główne, ograniczenia
Rozmiar tabeli podstawowej
Nienaturalne operacje
Problematyczna zmiana struktury
Przykładowe
implementacje
3. Tabela podstawowa
+
tabela archiwalna
Tabela podstawowa + tabela archiwalna
PUBLIC.ZDJECIE (tabela podstawowa)
id
timestamp
pesel
status
komunikat
pracownik
1
2012-07-15
85050500007
1
Zdjęcie OK
Promet
2
2012-08-08
87070700007
0
komunikat
pracownik
Nie kolorowe
Promet
Zdjęcie OK
Promet
ARCHIWUM.ZDJECIE (tabela archiwalna)
id
timestamp
pesel
status
1
2012-04-08
85050500007
0
1
2012-04-08
85050500007
-1
1
2012-06-07
85050500007
0
1
2012-07-15
85050500007
1
2
2012-08-08
87070700007
0
Tabela podstawowa
PUBLIC.ZDJECIE (tabela podstawowa)
id
timestamp
pesel
status
komunikat
pracownik
1
2012-07-15
85050500007
1
Zdjęcie OK
Promet
2
2012-08-08
87070700007
0
 Zawiera wyłącznie aktualne dane
 Triggery INSERT, DELETE, UPDATE kopiują dane do
archiwum, nie zmieniając nic w tabeli podstawowej.
 Mogą dziedziczyć podstawową strukturę z szablonu
Tabela archiwalna
ARCHIWUM.ZDJECIE (tabela archiwalna)
id
timestamp
pesel
status
1
2012-04-08
85050500007
0
1
2012-04-08
85050500007
-1
1
2012-06-07
85050500007
0
1
2012-07-15
85050500007
1
2
2012-08-08
87070700007
0




komunikat
pracownik
Nie kolorowe
Promet
Zdjęcie OK
Promet
Tabela znajduje się w osobnym schemacie DB
Zawiera dane archiwalne + dane bieżące
Dane mogą być chronione przed zmianą/kasowaniem
Schemat może być umieszczony na innym Storage-u
Tabela podstawowa + tabela archiwalna
Wykorzystywane mechanizmy
DBMS:
• Dziedziczenie struktury
• Triggery
• Procedury składowane
Rozwiązane problemy:
• Spójność struktury tabeli
podstawowej z archiwalną
• Łatwość tworzenia tabel archiwalnych
i potrzebnych triggerów
Dziedziczenie tabel
wskazuje jakie
tabele mają
być
archiwizowane
(opcjonalne)
Wzorzec (dla
wszystkich tabel, które
mają mieć archiwum)
Tabela podstawowa
Tabela archiwalna
zapewnienie
spójności
struktury
dla tabel
(konieczne)
Demonstracja
Tabela podstawowa
+
tabela archiwalna
Tabela podstawowa + tabela archiwalna
Zalety:
• Łatwy dostęp do danych
• Dostosowanie się do zmian struktury
• Wydajność
• Przeźroczystość dla aplikacji
• Naturalne zapytania SQL
• Tabela archiwalna jako backup, PITR
Wady:
• Implementacja w innych DBMS?
Kiedy stosować, a kiedy nie?
Kiedy (nie) stosować?
Warto stosować:
• Kluczowe dane
Nie warto stosować:
• Często zmieniające się dane
• Dane statyczne
• Dane wtórne/redundantne
Pytania?