MariaDB Temporal Tables: Uncut & Uncensored (Part I)

"You must remember this, a kiss is still a kiss. A sigh is just a sigh; the fundamental things apply, as time goes by."

Sam (Dooley Wilson) in Casablanca

System-Versioned

MariaDB [pythian]> CREATE TABLE `sv_table_ts` (
  -> `id` bigint(20) NOT NULL AUTO_INCREMENT,
  -> `x` int(11) DEFAULT NULL,
  -> `text` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`)
  -> ) ENGINE=InnoDB;
 Query OK, 0 rows affected (0.012 sec)
 
 MariaDB [pythian]> INSERT INTO sv_table_ts VALUES (null,1,'First row');
 Query OK, 1 row affected (0.014 sec)
 
 

MariaDB [pythian]> ALTER TABLE sv_table_ts ADD SYSTEM VERSIONING;
 Query OK, 1 row affected (0.033 sec)
 Records: 1 Duplicates: 0 Warnings: 0
 
 

MariaDB [pythian]> select now() into @time1; # capture time
 Query OK, 1 row affected (0.000 sec)
 
 MariaDB [pythian]> insert into sv_table_ts values (null,2,'Second row');
 Query OK, 1 row affected (0.002 sec)
 
 MariaDB [pythian]> select now() into @time2; # capture time
 Query OK, 1 row affected (0.000 sec)
 
 MariaDB [pythian]> update sv_table_ts set x=3 where id=1;
 Query OK, 1 row affected (0.004 sec)
 Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
 
 

MariaDB [pythian]> select * from sv_table_ts;
 +----+------+------------+
 | id | x | text |
 +----+------+------------+
 | 1 | 3 | First row |
 | 2 | 2 | Second row |
 +----+------+------------+
 2 rows in set (0.000 sec)
 
 

How do we query system versions?

MariaDB [pythian]> select * from sv_table_ts for system_time all;
 +----+------+------------+
 | id | x | text |
 +----+------+------------+
 | 1 | 1 | First row |
 | 1 | 3 | First row |
 | 2 | 2 | Second row |
 +----+------+------------+
 3 rows in set (0.000 sec)
 
 

MariaDB [pythian]> select * from sv_table_ts for system_time as of timestamp@time1;
 +----+------+-----------+
 | id | x | text |
 +----+------+-----------+
 | 1 | 1 | First row |
 +----+------+-----------+
 1 row in set (0.000 sec)
 
 MariaDB [pythian]> select * from sv_table_ts for system_time as of timestamp@time2;
 +----+------+------------+
 | id | x | text |
 +----+------+------------+
 | 1 | 1 | First row |
 | 2 | 2 | Second row |
 +----+------+------------+
 2 rows in set (0.000 sec)
 
 

MariaDB [pythian]> delete from sv_table_ts where id=2;
 Query OK, 1 row affected (0.002 sec)
 
 MariaDB [pythian]> select * from sv_table_ts;
 +----+------+-----------+
 | id | x | text |
 +----+------+-----------+
 | 1 | 3 | First row |
 +----+------+-----------+
 1 row in set (0.000 sec)
 
 MariaDB [pythian]> select * from sv_table_ts for system_time all;
 +----+------+------------+
 | id | x | text |
 +----+------+------------+
 | 1 | 1 | First row |
 | 1 | 3 | First row |
 | 2 | 2 | Second row |
 +----+------+------------+
 3 rows in set (0.000 sec)