1

I open a new question, since I cannot find a solution.

I would like to use the system versioning of Hsqldb 2.5.1 in order to be able to replicate in a background task, any change on a table (INSERT, DELETE or UPDATE) occurring over a time interval defined by two timestamp (timestart and timestop).

Assuming this is possible, what would be the three queries to use to find the records changed during the time interval (timestart, timestop) by INSERT, UPDATE and DELETE respectively.

Thank you for your help.

psilocybe
  • 35
  • 6

1 Answers1

1

After a lot of research, I found 3 queries that seem to answer my problem. Any correction is welcome, SQL is not what I know best ...

The replication interval is defined by 2 TIMESTAMP WITHOUT TIME ZONE, because the driver I use is the one provided by the UNO API (OpenOffice / LibreOffice) and the getter/setter (getTimestamp/setTimestamp) does not manage TIMESTAMP WITH TIME ZONE, respectively timestart and timestop.

rowstart: is the column declared as TIMESTAMP GENERATED ALWAYS AS ROW START.

rowend: is the column declared as TIMESTAMP GENERATED ALWAYS AS ROW END.

customerid: is the primary key to the customer table.

To find the records that have been updated:

SELECT current.customerid FROM customer FOR SYSTEM_TIME AS OF timestop + SESSION_TIMEZONE() AS current
INNER JOIN customer FOR SYSTEM_TIME FROM timestart + SESSION_TIMEZONE() TO timestop + SESSION_TIMEZONE() AS previous
ON current.customerid = previous.customerid AND current.rowstart = previous.rowend;

To find the records that have been inserted:

SELECT current.customerid FROM customer FOR SYSTEM_TIME AS OF timestop + SESSION_TIMEZONE() current
LEFT JOIN customer FOR SYSTEM_TIME AS OF timestart + SESSION_TIMEZONE() previous
ON current.customerid = previous.customerid WHERE previous.customerid IS NULL;

To find the records that have been deleted:

SELECT previous.customerid FROM customer FOR SYSTEM_TIME AS OF timestart + SESSION_TIMEZONE() previous
LEFT JOIN customer FOR SYSTEM_TIME AS OF timestop + SESSION_TIMEZONE() current
ON previous.customerid = current.customerid WHERE current.customerid IS NULL;

I do not know if the use of DATABASE_TIMEZONE instead of SESSION_TIMEZONE would be more judicious, free to who wants to confirm ...

I did not have time to test massively, but it works quickly even with a lot of record.

Et voila...

Edit: I just noticed that it is important to use the version hsqldb 2.5.1 because I did not manage to have a correct operation under 2.5.0 ...

psilocybe
  • 35
  • 6
  • The queries are fine. Even when you use TIMESTAMP WITHOUT TIME ZONE, it always creates a TIMESTAMP WITH TIME ZONE column and the time zone for each timestamp is UTC. You need SESSION_TIMEZONE() because the timestart and timestop are in your session time zone. – fredt Jul 06 '20 at 00:58
  • For very large tables with lots of updates, you can create an index on (customerid, rowend) columns in order to speed up the updated records query. – fredt Jul 06 '20 at 01:10
  • @fredt: Thank you for your confirmation, we can not find better than you to validate ... – psilocybe Jul 06 '20 at 12:31