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:

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:

SELECT MAX(DT_MOD) FROM TABLE_DT_MOD WHERE TABLE_NAME='TEST';

Implementacja sposobu 3

Tworzymy generator który przechowuje dane timestamp :)

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.

itstop.pl

FAQ1

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

Pierwszy sposób

Drugi sposób można zrealizować dwojako

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

Implementacja sposobu 1

Implementacja sposobu 2a

Implementacja sposobu 2b

Implementacja sposobu 3

itstop.pl