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
|
-- 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),
name varchar(100),
salary int(10),
constraint `dept-emp-fk`
foreign key (dept_id) references dept (dept_id)
on delete cascade
on update restrict
)
with system versioning;
select now() into @ts_0;
insert into dept (dept_id, name) values (10, "accounting");
commit;
select row_start into @ts_1 from dept where dept_id=10;
insert into emp (emp_id, name, salary, dept_id) values (1, "bill", 1000, 10);
commit;
select row_start into @ts_2 from emp where name="bill";
select * from emp;
update emp set salary=2000 where name="bill";
commit;
select row_start into @ts_3 from emp where name="bill";
select * from emp;
select * from emp for system_time as of timestamp @ts_2;
select * from emp for system_time as of timestamp @ts_3;
select * from emp e, dept d
where d.dept_id = 10
and d.dept_id = e.dept_id;
select * from
emp for system_time from timestamp @ts_1 to timestamp @ts_2 e,
dept for system_time from timestamp @ts_1 to timestamp @ts_2 d
where d.dept_id = 10
and d.dept_id = e.dept_id;
set statement system_versioning_asof=@ts_0 for
select * from emp e, dept d
where d.dept_id = 10
and d.dept_id = e.dept_id;
set statement system_versioning_asof=@ts_1 for
select * from emp e, dept d
where d.dept_id = 10
and d.dept_id = e.dept_id;
set statement system_versioning_asof=@ts_2 for
select * from emp e, dept d
where d.dept_id = 10
and d.dept_id = e.dept_id;
set statement system_versioning_asof=@ts_3 for
select * from emp e, dept d
where d.dept_id = 10
and d.dept_id = e.dept_id;
drop table emp, dept;
|
