From be6f2d302cd71677e1fafbeea9347c196f21e1bd Mon Sep 17 00:00:00 2001 From: Daniel Fiala Date: Sun, 19 Jun 2016 07:38:28 +0100 Subject: 0.1: SQL-level System Versioning --- mysql-test/t/insert.test | 73 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 73 insertions(+) (limited to 'mysql-test/t/insert.test') diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 206c5553100..7a4523a3f49 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -573,3 +573,76 @@ insert ignore into t1 values (1,12) on duplicate key update f2=13; set @@old_mode=""; insert ignore into t1 values (1,12); DROP TABLE t1; + +--echo # +--echo # System Versioning Support +--echo # +--echo # + +-- source include/have_innodb.inc + +SET @@session.time_zone='+00:00'; + +CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING; +INSERT INTO t1(x, y) VALUES(3, 4); +INSERT INTO t1(x, y) VALUES(2, 3); +INSERT INTO t1 VALUES(40, 33); +SELECT x, y, Sys_end FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING ENGINE=InnoDB; +INSERT INTO t1(x, y) VALUES(3, 4); +INSERT INTO t1(x, y) VALUES(2, 3); +INSERT INTO t1 VALUES(40, 33); +SELECT x, y, Sys_end FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT, x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end), PRIMARY KEY(id)) WITH SYSTEM VERSIONING; +INSERT INTO t1(x, y) VALUES(33, 44); +INSERT INTO t1(id, x, y) VALUES(20, 33, 44); +INSERT INTO t1 VALUES(40, 33, 44); +SELECT id, x, y, Sys_end FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT, x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end), PRIMARY KEY(id)) WITH SYSTEM VERSIONING ENGINE=InnoDB; +INSERT INTO t1(x, y) VALUES(33, 44); +INSERT INTO t1(id, x, y) VALUES(20, 33, 44); +INSERT INTO t1 VALUES(40, 33, 44); +SELECT id, x, y, Sys_end FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING; +CREATE VIEW vt1_1 AS SELECT x, y FROM t1; +CREATE VIEW vt1_2 AS SELECT x, y, Sys_end FROM t1; +INSERT INTO t1(x, y) VALUES(8001, 9001); +--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER +INSERT INTO t1(x, y, Sys_end) VALUES(8001, 9001, '2015-1-1 0:0:0'); +INSERT INTO vt1_1(x, y) VALUES(1001, 2001); +INSERT INTO vt1_1 VALUES(1002, 2002); +INSERT INTO vt1_2(x, y) VALUES(3001, 4001); +--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER +INSERT INTO vt1_2 VALUES(3002, 4002, '2015-1-1 0:0:0'); +SELECT x, y, Sys_end FROM t1; +SELECT x, y FROM vt1_1; +SELECT x, y, Sys_end FROM vt1_2; +DROP TABLE t1; +DROP VIEW vt1_1; +DROP VIEW vt1_2; + +CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING ENGINE=InnoDB; +CREATE VIEW vt1_1 AS SELECT x, y FROM t1; +CREATE VIEW vt1_2 AS SELECT x, y, Sys_end FROM t1; +INSERT INTO t1(x, y) VALUES(8001, 9001); +--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER +INSERT INTO t1(x, y, Sys_end) VALUES(8001, 9001, '2015-1-1 0:0:0'); +INSERT INTO vt1_1(x, y) VALUES(1001, 2001); +INSERT INTO vt1_1 VALUES(1002, 2002); +INSERT INTO vt1_2(x, y) VALUES(3001, 4001); +--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER +INSERT INTO vt1_2 VALUES(3002, 4002, '2015-1-1 0:0:0'); +SELECT x, y, Sys_end FROM t1; +SELECT x, y FROM vt1_1; +SELECT x, y, Sys_end FROM vt1_2; +DROP TABLE t1; +DROP VIEW vt1_1; +DROP VIEW vt1_2; -- cgit v1.2.1