7 - 19:26:56.495942

FAQ1

W jaki sposób składować czas ostatniej modyfikacji tabeli?

W niektórych przypadkach chcemy wiedzieć, kiedy dana tabela bazy danych została zmodyfikowana. Istnieje kilka możliwych sposobów osiągnięcia tego celu. 1. Dodanie do tabeli pola Timestamp i zapisywanie w nim czasu ostatniej modyfikacji rekordu. 2. Przechowywanie informacji o ostatniej modyfikacji danej tabel w osobnej tabeli. Struktura takiej tabeli zawiera nazwę tabeli i pole TimeStamp ostatniej modyfikacji całej tabeli. 3. Realizacja przewidziana w tym FAQ - czyli generatory.

Pierwszy sposób

+ wiemy kiedy dana tabela została zmodyfikowana + wiemy również kiedy dowolny z rekordów został zmieniony + daje informacje rzeczywiste (dopiero po zatwierdzeniu transakcji) - dodatkowe pole zajmuje 8 bajtów danych na każdy rekord co może nie być bez znaczenia przy dużych tabelach z milionami rekordów - nie zawsze możemy dodać dodatkowe pole do tabeli nie zaburzając działania istniejącej aplikacji (np. w aplikacji może być instrukcja INSERT INTO .. SELECT * FROM itp.) - szybkość działania wymaga dodania dodatkowego indeksu descending zajmującego kolejne miejsce w bazie danych i spowalniającego operacje Insert i Update - nie daje informacji o modyfikacji w przypadku operacji usuń

Drugi sposób można zrealizować dwojako

a) tabela zawierająca jeden rekord dla jednej monitorowanej tabeli - chyba same minusy? + daje informacje rzeczywiste (dopiero po zatwierdzeniu transakcji) - totalne wersjonowanie - masywne konflikty uaktualnień/konkurencyjności (lock) - brak informacji który dokładnie rekord został zmieniony b) tabela zawierająca "wiele" rekordów dla jednej modyfikowanej tabeli + daje informacje rzeczywiste (dopiero po zatwierdzeniu transakcji) - bardzo duża liczba wersji rekordów - wymaga usuwania starych rekordów a dla każdej modyfikacji tabeli źródłowej musi powstać nowy rekord. - brak informacji który dokładnie rekord został zmieniony

Trzeci sposób wykorzystujący pewną sztuczkę z użyciem generatorów :)

+ szybkość działania + bezproblemowa konkurencyjność + brak wersjonowania - brak informacji który dokładnie rekord został zmieniony - informacje o zmianie pojawiają się dokładnie w chwili gdy generator został zmieniony niezależnie od tego czy był commit czy też rollback

Implementacja sposobu 1

Do tabeli dodajemy pole DT_MOD timestamp
1
2
3
4
5
6
CREATE TABLE TEST ( ID INTEGER CONSTRAINT NK_TEST__ID NOT NULL CONSTRAINT PK_TEST PRIMARY KEY, TABLE_DATA VARCHAR(100), DT_MOD TIMESTAMP );
Teraz tworzymy wyzwalacz który ustawi nam wartość pola DT_MOD w chwili utworzenia nowego rekordu lub jego zmiany
1
2
3
4
5
6
7
8
9
SET TERM ^ ; CREATE TRIGGER MOD__TEST FOR TEST ACTIVE BEFORE INSERT OR UPDATE /* OR DELETE */ POSITION 1000 AS BEGIN NEW.DT_MOD=CURRENT_TIMESTAMP; END^ SET TERM ; ^
Dlaczego nie "OR DELETE"? - bo po usunięciu rekordu to i tak bez znaczenia - nie ma tego pola bo nie ma rekordu.. Teraz aby dowiedzieć się kiedy cała tabela została ostatnio zmodyfikowana wystarczy wykonać zapytanie:
1
2
SELECT MAX(DT_MOD) FROM TEST; PLAN (TEST NATURAL)
Operacj jest wolna - tak więc trzeba dodać dodatkowy index malejący
1
2
3
4
CREATE DESCENDING INDEX IXD_TEST__DT_MOD ON TEST(DT_MOD); SELECT MAX(DT_MOD) FROM TEST; PLAN (TEST ORDER IXD_TEST__DT_MOD)

Implementacja sposobu 2a

Dodajemy dodatkową tabelę z nazwą tabeli i polem DT_MOD timestamp
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE TEST ( ID INTEGER CONSTRAINT NK_TEST__ID NOT NULL CONSTRAINT PK_TEST PRIMARY KEY, TABLE_DATA VARCHAR(100), ); CREATE TABLE TABLE_DT_MOD ( TABLE_NAME VARCHAR(31), DT_MOD TIMESTAMP ); CREATE INDEX IXD_TABLE_DT_MOD ON TABLE_DT_MOD(TABLE_NAME, DT_MOD);
Teraz tworzymy wyzwalacz który ustawi nam wartość pola DT_MOD rekordu dodatkowej tabeli w chwili utworzenia nowego rekordu lub jego zmiany
1
2
3
4
5
6
7
8
9
10
SET TERM ^ ; CREATE TRIGGER TEST__DT_MOD FOR TEST ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 1000 AS BEGIN /* to powoduje te problemy z konkurencyjnością */ UPDATE TABLE_DT_MOD SET DT_MOD=CURENT_TIMESTAMP WHERE TABLE_NAME='TEST'; END^ SET TERM ; ^
Teraz aby dowiedzieć się kiedy cała tabela została ostatnio zmodyfikowana wystarczy wykonać zapytanie:
1
SELECT DT_MOD FROM TABLE_DT_MOD WHERE TABLE_NAME='TEST';

Implementacja sposobu 2b

Dodajemy dodatkową tabelę z nazwą tabeli i polem DT_MOD timestamp
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE TEST ( ID INTEGER CONSTRAINT NK_TEST__ID NOT NULL CONSTRAINT PK_TEST PRIMARY KEY, TABLE_DATA VARCHAR(100), ); CREATE TABLE TABLE_DT_MOD ( TABLE_NAME VARCHAR(31), DT_MOD TIMESTAMP ); CREATE INDEX IXD_TABLE_DT_MOD ON TABLE_DT_MOD(TABLE_NAME, DT_MOD);
Teraz tworzymy wyzwalacz który doda nowy rekord z czasem ostatniej modyfikacji tabeli
1
2
3
4
5
6
7
8
9
10
11
12
13
SET TERM ^ ; CREATE TRIGGER TEST__DT_MOD FOR TEST ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 1000 AS BEGIN /* unikamy problemów z konkurencyjnością przez tworzenie nowych rekordów zamiast aktualizowania */ INSERT INTO TABLE_DT_MOD(TABLE_NAME, DT_MOD) VALUES('TEST', CURENT_TIMESTAMP); /* usuwamy wszystkie wcześniejsze rekordy dla danej tabeli - tylko tu też mamy problemy z konkurencyjnoścą czyż nie? */ /* tak więc tą operację powinno wykonywać coś cyklicznie z zewnątrz */ DELETE FROM TABLE_DT_MOD WHERE TABLE_NAME='TEST' AND DT_MOD<CURENT_TIMESTAMP; END^ SET TERM ; ^
Teraz aby dowiedzieć się kiedy cała tabela została ostatnio zmodyfikowana wystarczy wykonać zapytanie:
1
SELECT MAX(DT_MOD) FROM TABLE_DT_MOD WHERE TABLE_NAME='TEST';

Implementacja sposobu 3

Tworzymy generator który przechowuje dane timestamp :)
1
CREATE GENERATOR LAST_MOD__TEST;
Teraz potrzebujemy wyzwalacz na tabeli dla przykładu tabela TEST
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET TERM ^ ; CREATE TRIGGER TEST_LAST_MOD FOR TEST ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0 AS DECLARE VARIABLE VAR_CURRT TIMESTAMP; DECLARE VARIABLE NEW_GEN BIGINT; BEGIN SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE INTO :VAR_CURRT; NEW_GEN = CAST( EXTRACT(YEAR FROM :VAR_CURRT) || LPAD(EXTRACT(MONTH FROM :VAR_CURRT), 2, '0') || LPAD(EXTRACT(DAY FROM :VAR_CURRT), 2, '0') || LPAD(EXTRACT(HOUR FROM :VAR_CURRT), 2, '0') || LPAD(EXTRACT(MINUTE FROM :VAR_CURRT), 2, '0') || LPAD(CAST(EXTRACT(SECOND FROM CURRENT_TIMESTAMP)*10000 AS INTEGER), 6, '0') AS BIGINT); NEW_GEN = GEN_ID(LAST_MOD__TEST, -GEN_ID(LAST_MOD__TEST, 0) + :NEW_GEN); /* EXECUTE STATEMENT 'SET GENERATOR LAST_MOD__TEST TO ' || :NEW_GEN; */ END^ SET TERM ; ^
Co robi wyzwalacz? Zmienia nam datę i czas na liczbę bigint którą składujemy w generatorze: 201401052217091234 - czyli 2014-01-05 22:17:09,1234 Do otrzymania spowrotem danych typu TimeStamp z takiego generatora, potrzebujemy dodatkowej uniwersalnej procedurki "GET_LAST_MOD_TIMESTAMP"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET TERM ^ ; CREATE PROCEDURE GET_LAST_MOD_TIMESTAMP (GEN_VAL BIGINT) RETURNS (LAST_MOD_TIME TIMESTAMP ) AS BEGIN IF (GEN_VAL=0) THEN LAST_MOD_TIME = NULL; ELSE LAST_MOD_TIME = CAST( SUBSTRING(GEN_VAL FROM 1 FOR 4) || '-' || SUBSTRING(GEN_VAL FROM 5 FOR 2) || '-' || SUBSTRING(GEN_VAL FROM 7 FOR 2) || ' ' || SUBSTRING(GEN_VAL FROM 9 FOR 2) || ':' || SUBSTRING(GEN_VAL FROM 11 FOR 2) || ':' || SUBSTRING(GEN_VAL FROM 13 FOR 2) || '.' || SUBSTRING(GEN_VAL FROM 15 FOR 4) AS TIMESTAMP ); SUSPEND; END^ SET TERM ; ^
Teraz można już pobierać i składować dane bez problemów z konkurencyjnością czy też wydajnością
1
2
3
4
5
6
7
SELECT (SELECT GLMT.LAST_MOD_TIME FROM GET_LAST_MOD_TIMESTAMP(GEN_ID(LAST_MOD__TEST, 0)) GLMT ) AS LAST_MOD_TIME_TABLE_TEST FROM RDB$DATABASE R
Pamiętaj jedynie jak już wcześniej wspomniałem - jako, że generatory nie są obiektami transakcyjnymi ich zmiana następuje natychmiast, niezależnie od tego czy wykonamy commit czy też rollback ani też kiedy wykonamy. Możemy np. commit zrobić 10 minut później a generator już wcześniej pokazuje, że tabela została zmodyfikowana. Używaj tego pomysłu jeśli uważasz go za wystarczająco ciekawy. A jak widać każdy sposób z wymienionych ma jakieś plusy lub minusy.
8 - 19:26:56.495942