1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
-- source include/have_innodb.inc
set default_storage_engine=innodb;
create or replace table dept (
dept_id int(10) primary key,
name varchar(100)
)
with system versioning;
create or replace table emp (
emp_id int(10) primary key,
dept_id int(10) not null,
name varchar(100) not null,
mgr int(10),
salary int(10) not null,
constraint `dept-emp-fk`
foreign key (dept_id) references dept (dept_id)
on delete cascade
on update restrict,
constraint `mgr-fk`
foreign key (mgr) references emp (emp_id)
on delete restrict
on update restrict
)
with system versioning;
insert into dept (dept_id, name) values (10, "accounting");
insert into emp (emp_id, name, salary, dept_id, mgr) values
(1, "bill", 1000, 10, null),
(20, "john", 500, 10, 1),
(30, "jane", 750, 10,1 );
select max(sys_trx_start) into @ts_1 from emp;
update emp set mgr=30 where name ="john";
select sys_trx_start into @ts_2 from emp where name="john";
/* All report to 'Bill' */
with recursive
ancestors
as
(
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_1 as e
where name = 'bill'
union
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_1 as e,
ancestors as a
where e.mgr = a.emp_id
)
select * from ancestors;
/* Expected 3 rows */
with recursive
ancestors
as
(
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_2 as e
where name = 'bill'
union
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_2 as e,
ancestors as a
where e.mgr = a.emp_id
)
select * from ancestors;
create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning;
create or replace table addr ( emp_id int, address varchar(100)) with system versioning;
insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1);
insert addr values (1, 'Moscow'), (2, 'New York'), (3, 'London');
set @ts=now(6);
delete from emp;
delete from addr;
with recursive
ancestors
as
(
select e.emp_id, e.name, e.mgr
from emp for system_time as of timestamp @ts as e
where name = 'bill'
union
select ee.emp_id, ee.name, ee.mgr
from emp for system_time as of timestamp @ts as ee, ancestors as a
where ee.mgr = a.emp_id
)
select * from ancestors;
insert emp values (4, 'john', 1);
insert addr values (4, 'Paris');
with ancestors as (select * from emp natural join addr) select * from ancestors;
with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all;
with ancestors as (select * from (select * from emp natural join addr) for system_time all as t) select * from ancestors;
select * from (select * from emp natural join addr) for system_time all as t;
drop table emp;
drop table dept;
drop table addr;
|
