例如,我有一个表测试在哪里
id is the identifier,
modstamp is the timestamp of the data (never null)
除上述列外,该表还包含簿记列
local_modstamp is the timestamp at which the record was updated
del_modstamp is the timestamp at which the record was deleted
在备份期间,所有记录都从源获取并插入到记录将具有值local_modstamp = null和del_stamp = null的位置.
id |modstamp |local_modstamp |del_modstamp | ---|---------------------------|---------------|-------------| 1 |2016-08-01 15:35:32 +00:00 | | | 2 |2016-07-29 13:39:45 +00:00 | | | 3 |2016-07-21 10:15:09 +00:00 | | |
获得记录后,这些是处理数据的方案(假设参考时间[ref_time]是进程运行的时间)：
>正常插入.
>更新：使用local_modstamp = ref_time更新最新记录.然后插入新记录.
查询将是：
更新测试集local_modstamp =其中id =和local_modstamp不为null且del_modstamp不为null
插入测试值(…)
>删除：使用del_modstamp = ref_time更新最新记录.
更新测试集del_modstamp =其中id =和local_modstamp不为null且del_modstamp不为null
该设计旨在获取local_modstamp不为null且del_modstamp不为null的最新记录.
但是,我遇到了一个问题,我打算使用查询(最内层查询)检索时间点：
select id,max(modstamp) from test where modstamp <= <ref_time> and (del_modstamp is null || del_modstamp <= <ref_time>) group by id;
似乎我犯了一个错误(我有吗？)使用null作为占位符来识别表的最新记录.有没有办法使用现有的设计来获取时间点记录？
如果没有,我想可能的解决方案是将local_modstamp设置为最新记录.这将需要在更新的情况下使用max(local_modstamp)更新逻辑.我可以坚持现有的架构来检索时间点数据吗？
我现在正在使用sql-Server,但这种设计也可以扩展到其他数据库产品.我打算使用更通用的方法来检索数据,而不是使用供应商特定的hacks.
解决方法
create table Entities(
ID int identity primary key,S1 [type],-- Static data
Sn [type],-- more static data
V1 [type],-- Volatile data
Vn [type] -- more volatile data
);
静态数据是在实体的生命周期内不会更改或不需要跟踪的数据.必须跟踪易失性数据更改和这些更改.
将volatile属性移动到单独的表：
create table EntityVersions(
ID int not null,Effective date not null default sysdate(),Deleted bit not null default 0,V1 [type],Vn [type],constraint PK_EntityVersions primary key( ID,Effective ),constraint FK_EntityVersionEntity foreign key( ID )
references Entities( ID )
);
实体表不再包含易失性属性.
插入操作使用静态数据创建主实体记录,从而生成唯一ID值.该值用于插入具有易失性数据初始值的第一个版本.更新通常对主表没有任何作用(除非实际更改了静态值),并且新版本的新易失性数据被写入版本表.请注意,不会对现有版本进行任何更改,尤其是最新版本或“当前版本”.插入新版本,结束操作.
要“撤消”最新版本或任何版本,只需从版本表中删除该版本即可.
例如,具有以下属性的Employees表：
EmployeeNum,HireDate,FirstName,LastName,PayRate,Dept,PhoneExt
当然,EmployeeNum与HireDate和FirstName一起是静态的. PhoneExt可能会不时更改,但我们不在乎.所以它被指定为静态.最终的设计是：
Employees_S =========== EmployeeNum (PK),PhoneExt Employees_V =========== EmployeeNum (PK),Effective (PK),IsDeleted,Dept
2016年1月1日,我们聘请了Sally Smith.静态数据插入到Employees_S中,生成EmployeeNum值1001.我们使用该值也插入第一个版本.
Employees_S =========== 1001,2016-01-01,Sally,12345 Employees_V =========== 1001,Smith,35.00,Eng
3月1日,她获得加薪：
Employees_S =========== 1001,Eng 1001,2016-03-01,40.00,Eng
5月1日,她结婚了：
Employees_S =========== 1001,2016-05-01,Jones,Eng
请注意,除了生效日期不能相同的限制之外,同一实体的版本完全相互独立.
要查看员工1001的当前状态,请查看以下内容：
select s.EmployeeNum,s.HireDate,s.FirstName,v.LastName,v.PayRate,v.Dept,s.PhoneExt
from Employees_S s
join Employees_V v
on v.EmployeeNum = s.EmployeeNum
and v.Effective = ( select Max( Effective )
from Employees_V
where EmployeeNum = v.EmployeeNum
and Effective <= SysDate() )
where s.EmployeeNum = 1001
and v.IsDeleted = 0;
这是很酷的部分.要查看员工1001的状态,比如2月11日,这是查询：
select s.EmployeeNum,s.PhoneExt
from Employees_S s
join Employees_V v
on v.EmployeeNum = s.EmployeeNum
and v.Effective = ( select Max( Effective )
from Employees_V
where EmployeeNum = v.EmployeeNum
and Effective <= '2016-02-11' )
where s.EmployeeNum = 1001
and v.IsDeleted = 0;
它是相同的查询 – 除了子查询的最后一行.当前和历史数据驻留在同一个表中,并使用相同的语句进行查询.
这是另一个很酷的功能.这是7月1日,我们知道,在9月1日,Sally将转移到营销部门,另一次加薪.文书工作已经完成.继续并插入新数据：
Employees_S =========== 1001,2016-09-01,50.00,Mkt
倒数第二个版本仍将显示为当前版本,但在9月1日或之后执行的第一个查询将显示营销数据.
Here是我在技术博览会上做过几次演示的幻灯片.它包含有关如何完成上述所有操作(包括查询)的更多详细信息. here是一个更详细的文档.
