7 - 01:32:09.000019

FAQ1

How to store table modification timestamp?

In some cases, we want to know when a database table was modified. There are several possible ways to achieve this goal. 1. Add a Timestamp field to the table and save in it the last time when a record was modified. 2. Storing information about the last modification of the tables in a separate table. The structure of this new table contains the name of the table and field TimeStamp. 3. The implementation provided in this FAQ - use generators.

First method

+ we know when a table has been modified + we also know when any of the records has been changed. + gives the actual information (only after transaction commit) - additional field occupies 8 bytes of data for each record which can not be irrelevant for large tables with millions of records. - we can not always add an extra field to the existing table without disturbing operation of existing applications (such as in the application may be an INSERT INTO .. SELECT * FROM etc.) - speed ​​of operation requires the addition of an additional index descending occupying more space in the database and defeat the operations Insert and Update. - does not give information about the modifications for delete operations

The second method can be done in two ways

a) The table contains one record for one monitored the table - probably only drawbacks ? + gives the actual information (only after transaction commit) - a total table versioning - massive conflicts update / concurrency (lock) - lack of information that accurately record was changed b) a table containing "many" records for a modified table + gives the actual information (only after transaction commit) - a very large number of records versions - requires removal of old records and for any modification of the source table new record must be stored. - lack of information which record was changed

The third method uses a trick of using generators :)

+ speed + seamless concurrency support + no versioning - lack of information that accurately record was changed - change information appear exactly at the time when the generator has been changed regardless of whether it was a commit or rollback

Method 1 implementation

We add to table field 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 );
Now we create a trigger that sets us DT_MOD field value at the time of the creation of a new record or change
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 ; ^
Why not "OR DELETE"? - Because when record was deleted it does not matter - does not have this field because there is no record .. Now, to find out when the whole table was last modified, we run select:
1
2
SELECT MAX(DT_MOD) FROM TEST; PLAN (TEST NATURAL)
Operation is slow - so you have to add an additional descending index
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)

Method 2a implementation

We add an extra table with field table_name and field 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);
Now we create a trigger that sets us DT_MOD at the time of the creation of a new record or change
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 /* This causes problems with concurrency */ UPDATE TABLE_DT_MOD SET DT_MOD=CURENT_TIMESTAMP WHERE TABLE_NAME='TEST'; END^ SET TERM ; ^
Now, to find out when the whole table was last modified, we run select:
1
SELECT DT_MOD FROM TABLE_DT_MOD WHERE TABLE_NAME='TEST';

Method 2b implementation

We add an extra table with the field table_name and field 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);
Now we create a trigger that adds a new record with the time of last modification of the table
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 /* avoid the problems of concurrency by creating new records instead of updating */ INSERT INTO TABLE_DT_MOD(TABLE_NAME, DT_MOD) VALUES('TEST', CURENT_TIMESTAMP); /* remove all previous records for the table - only here we also have problems with concurrency is not it? */ /* so this operation should be performed periodically somwhere outside */ DELETE FROM TABLE_DT_MOD WHERE TABLE_NAME='TEST' AND DT_MOD<CURENT_TIMESTAMP; END^ SET TERM ; ^
Now, to find out when the whole table was last modified, we run select:
1
SELECT MAX(DT_MOD) FROM TABLE_DT_MOD WHERE TABLE_NAME='TEST';

Method 3 implementation

Create a generator that stores the timestamp :)
1
CREATE GENERATOR LAST_MOD__TEST;
Now we need a trigger on a table for example table 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 ; ^
What does the trigger? Changes us the date and time and store it as bigint into generator: 201401052217091234 - equal to 2014-01-05 22:17:09,1234 To receive data back from generator as TimeStamp type, we need an additional universal stored procedure "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 ; ^
You can now get and store data without the problems of concurrency or speed
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
Remember only as I mentioned earlier - that the generators are not transactional objects and change value immediately, regardless of whether we make a commit or rollback or when we take. E.g. we do commit after 10 minutes but generator already shows that the table has been modified. Use this idea if you think it's interesting enough. And as you can see any way of these has some pluses and minuses.
8 - 01:32:09.000019