您有两个表
您想要维护这两个表以及它们之间的关系的历史版本,使得:
对于<强>案例#2,这将导致9个DML操作来链接一个新的<代码>bar到与第一个<代码>bar相关联的<代码>foo,不包括第一行:
foo
和 bar
,它们之间存在一个 M:N
的关系。您想要维护这两个表以及它们之间的关系的历史版本,使得:
您插入一行到
Foo
表中,然后插入一行到Bar
表中,最后插入一行到连接了这两个表的FooBar
表中。您应该能够回溯时间并查看到在Foo
表中的行曾经是独立的,同理在Bar
表中的行也是独立的。然后您再插入一行到
Bar
表中,并将这第二个bar
与第一个foo
连接起来放入到FooBar
表中。您应该能够回溯时间并查看到第一个foo
行只被连接到了第一个bar
行。然后您更新
foo
表中的一个属性。您应该能够回溯时间并查看到在先前属性的情况下,Bar
表中的两行都曾经与那个foo
行连接。
DDL
Foo
--------------
foo_id INT --sequence generated
foo_version_id INT UNIQUE --sequence generated
foo_name VARCHAR
active INT CHECK (active in (0,1))
CONSTRAINT PRIMARY_KEY (foo_id, foo_version_id)
Bar
--------------
bar_id INT --sequence generated
bar_version_id INT UNIQUE --sequence generated
bar_name VARCHAR
active INT CHECK (active in (0,1))
CONSTRAINT PRIMARY_KEY (bar_id, bar_version_id)
FooBar
--------------
foo_version_id FK to foo.foo_version_id
bar_version_id FK to bar.bar_version_id
CONSTRAINT PRIMARY KEY (foo_version_id, bar_version_id)
DML
以下是三种情况的伪代码。我已经将它们实现为过程。
对于第一种情况,这会导致4个DML操作来将两个独立的foo
和bar
链接在一起,不包括前两行:
Insert the first foo row
Insert the first bar row
Update the first foo row and set active to 0.
Insert a new foo row with the same foo_id, foo_name, but new foo_version_id
Update the first bar row and set active to 0
Insert a new bar row and with the same bar_id, bar_name, but new bar_version_id
Insert a row into foo_bar with the foo_version_id and bar_version_id from the newly created active foo and bar rows.
对于<强>案例#2,这将导致9个DML操作来链接一个新的<代码>bar到与第一个<代码>bar相关联的<代码>foo,不包括第一行:
Insert the second bar row
Update the active foo and set active to 0
Insert a new foo row with same foo_id, foo_name, but new foo_version_id
Update the first active bar and set active to 0
Insert a new bar row with same bar_id, bar_name, but new bar_version_id
Update the second active bar and set active to 0
Insert a new bar row with same bar_id, bar_name, but new bar_version_id
Insert a row into foo_bar with the foo_version_id and bar_version_id from the foo and first bar.
Insert a row into foo_bar with the foo_version_id and bar_version_id from the foo and second bar.
对于第三个案例,这会导致进行8次DML操作以更新与两个bars
相关联的foo
上的属性:
Update the active foo and set active to 0
Insert a new foo row with same foo_id, but new foo_version_id, foo_name
(repeat from case #2 starting at line 4)
SQL
在已知 foo_id
的情况下,我可以通过左连接 foo
、foo_bar
和 bar
中的 foo_version_id
和 bar_version_id
,查看特定 foo 的所有可能历史状态。
select f.foo_id, f.foo_version_id, f.foo_name, b.bar_id, b.bar_version_id, b.bar_name
FROM foo f, foo_bar fb, bar b
WHERE 1 = 1
AND f.foo_version_id = fb.foo_version_id (+)
AND fb.bar_version_id = b.bar_version_id (+)
ORDER BY f.foo_version_id, b.bar_version_id
;
foo_id | foo_version_id | foo_name | bar_id | bar_version_id | bar_name
1 | 1 | a | | | -- 1) independent foo
1 | 2 | a | 1 | 2 | b -- 2) link foo to first bar
1 | 3 | a | 1 | 4 | b -- 3) link second bar to foo
1 | 3 | a | 2 | 5 | b2 -- 3) link second bar to foo
1 | 4 | A | 1 | 6 | b -- 4) rename foo_name to A
1 | 4 | A | 2 | 7 | b2 -- 4) rename foo_name to A
foo
和bar
的任意配置。在这种情况下,foo
的定义包括所有相关的bar
,反之亦然。改变当前foo
上的属性会改变相关bar
的性质,反之亦然。 - Matthew Moisen