Debian Codegraph @

mysql-test/suite/period/t/versioning.test

raw
1 source suite/versioning/engines.inc;
2 source suite/versioning/common.inc;
3
4 --echo # DELETE
5 --replace_result $sys_datatype_expl SYS_TYPE
6 eval create table t (
7 s date, e date,
8 row_start $sys_datatype_expl as row start invisible,
9 row_end $sys_datatype_expl as row end invisible,
10 period for apptime(s, e),
11 period for system_time (row_start, row_end)) with system versioning;
12 insert into t values('1999-01-01', '2018-12-12'),
13 ('1999-01-01', '1999-12-12');
14
15 --disable_cursor_protocol
16 select row_start into @ins_time from t limit 1;
17 --enable_cursor_protocol
18 select * from t order by s, e;
19
20 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
21 select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
22 from t for system_time all
23 order by s, e, row_start;
24
25 --echo # same for trigger case
26 delete from t;
27 delete history from t;
28 insert into t values('1999-01-01', '2018-12-12'),
29 ('1999-01-01', '1999-12-12');
30 --let $trig_table=t
31 --source suite/period/create_triggers.inc
32
33 --disable_cursor_protocol
34 select row_start into @ins_time from t limit 1;
35 --enable_cursor_protocol
36 select * from t order by s, e;
37
38 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
39 select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
40 from t for system_time all
41 order by s, e, row_start;
42 select * from log_tbl order by id;
43
44 --echo # UPDATE
45 --replace_result $sys_datatype_expl SYS_TYPE
46 eval create or replace table t (x int, s date, e date,
47 row_start $sys_datatype_expl as row start invisible,
48 row_end $sys_datatype_expl as row end invisible,
49 period for apptime(s, e),
50 period for system_time(row_start, row_end)) with system versioning;
51 insert into t values(1, '1999-01-01', '2018-12-12'),
52 (2, '1999-01-01', '1999-12-12');
53
54 --disable_cursor_protocol
55 select row_start into @ins_time from t limit 1;
56 --enable_cursor_protocol
57 --sorted_result
58 select * from t;
59
60 update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
61 select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
62 from t for system_time all
63 order by x, s, e, row_start;
64
65 drop table t,log_tbl;
66 drop procedure log;
67 source suite/versioning/common_finish.inc;