如何查询hsqldb 2.5.1的系统版本控制 [英] How to query system versioning of hsqldb 2.5.1

问题描述

由于找不到解决方案，我提出了一个新问题.

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

我想使用Hsqldb 2.5.1的系统版本控制，以便能够在后台任务中复制表上的任何更改(INSERT，DELETE或UPDATE)，该更改发生在两个时间戳记定义的时间间隔内( timestart和timestop).

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).

假设这是可能的，那三个查询分别用来查找在时间间隔(timestart，timestop)中分别由INSERT，UPDATE和DELETE更改的记录.

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.

谢谢您的帮助.

推荐答案

经过大量研究，我发现了3个查询似乎可以解决我的问题. 欢迎进行任何更正，SQL不是我最了解的...

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 ...

复制间隔由2 TIMESTAMP WITHTIME TIME ZONE定义，因为我使用的驱动程序是UNO API(OpenOffice/LibreOffice)提供的驱动程序，而getter/setter(getTimestamp/setTimestamp)不管理TIMESTAMP WITH TIME ZONE ，分别为timestart和timestop.

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:是始终作为行开始声明为TIMESTAMP的列.

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

rowend:是声明为TIMESTAMP始终作为行尾生成的列.

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

customerid:是客户表的主键.

要查找已更新的记录，请执行以下操作:

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;

要查找已插入的记录:

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;

我不知道使用DATABASE_TIMEZONE代替SESSION_TIMEZONE是否更明智，谁愿意确认...

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.

等等...

我刚刚注意到使用hsqldb 2.5.1版本非常重要，因为在2.5.0下我无法进行正确的操作...

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 ...

这篇关于如何查询hsqldb 2.5.1的系统版本控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！