# MariaDB Temporal Table
- hackmd version: https://hackmd.io/Iwe0MVlwRpOMZ70W1ZwPtQ?view
- gist version: https://gist.github.com/bluet/5ebdfb65f486e01c13175dcdedf27906

[TOC]

## Description

This is a HOWTO for database record/value version control, not for schema.

With MariaDB, now we can **Versioning database records** (values) `WITH SYSTEM VERSIONING`.
Once we have tables configured correctly, Data Versions will be generated automatically everytime we have new data inserted or values updated.

Also note that when new values are as the same as the old ones, no modifications will be done and no new versions will be created; it just works like a **automatic data dedup** (de-duplication) feature which avoids adding repeating data.
> Unless you really need to log every metric data down (ex, system monitoring), and in that case you might want to check TSDB (time series databases) like InfluxDB, saving repeating unchanged records is just a waste of disk and memory and could also drag system performance down. That's why data dedup is important.

## Prerequirements:
- MariaDB version 10.3.4 or up (better with 10.4 or up, for Application-time Periods)
- With docker: `docker run --name mariadb -e MYSQL_ROOT_PASSWORD=password -d mariadb`

## Prepare

### Connect
Connect with client shipped with docker image [Docker Hub][docker-mariadb]
- `docker exec -it mariadb mysql -u root -p`

### Initialize

Create test database:

CREATE DATABASE Company;
use Company;

#### Table definition (Default)
Create a Temporal table with versioning support for all columns:

CREATE TABLE Person (
  Id int(11) NOT NULL AUTO_INCREMENT,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  Gender char(1) NOT NULL,
  DepartmentId int(11) NOT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT con_gender CHECK (Gender in ('f','m')))
WITH SYSTEM VERSIONING;

#### Exclusive table definition (Optional)
> For excluding columns from versioning, we can append `WITHOUT SYSTEM VERSIONING` in column definition.
> [Reference][exclude-column]
>

CREATE TABLE t (
   x INT,
   y INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;

#### Inclusive table definition (Optional)
> For inclusive column declaration, we can append `WITH VERSIONING`.
> [Reference][exclude-column]
>

CREATE TABLE t (
   x INT WITH SYSTEM VERSIONING,
   y INT
);

#### Partitioning - Storing the History Separately (Recommend)
> If most queries on that table use only current data in stead of historical data,
> partitioning can reduce the size of the table, gain performance on data queries (table scans and index searches).
> [Reference][partitioning]
>

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
  );

## Play with Data

### Insert record
`INSERT` always creates new version, as it creates a new data of values as a new record.

MariaDB [Company]> INSERT INTO Person (FirstName, LastName, Gender, DepartmentId) VALUES ('Rasmus', 'Johansson', 'm', 1);
Query OK, 1 row affected (0.002 sec)

MariaDB [Company]> SELECT * FROM Person;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            1 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)

Above command created the first row of records, and it's timestamps.

MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
1 rows in set (0.001 sec)

### Update record
`UPDATE` doesn't always generates new version:
- If values are really being changed, it will create a new version.
- If values are the same, the `UPDATE` won't modify any value, so no new version.

#### `UPDATE` with different values

MariaDB [Company]> UPDATE Person SET DepartmentId = 2 WHERE Id = 1;
Query OK, 1 row affected (0.050 sec)
Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0

New version shows up.

MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
2 rows in set (0.001 sec)

#### `UPDATE` with the same values as existing ones

MariaDB [Company]> UPDATE Person SET DepartmentId = 2 WHERE Id = 1;
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1  Changed: 0  Inserted: 0  Warnings: 0

No new version created.

MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
2 rows in set (0.001 sec)

#### `UPDATE` with different values, again

MariaDB [Company]> UPDATE Person SET DepartmentId = 3 WHERE Id = 1;
Query OK, 1 row affected (0.011 sec)
Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0

#### UPSERT - `UPDATE` when exist, `INSERT` if not.
[Reference][upsert]

INSERT INTO ins_duplicate VALUES (1,'Antelope') ON DUPLICATE KEY UPDATE animal='Antelope';

### Select data, in different ways

#### Select only current (latest) data

MariaDB [Company]> SELECT * FROM Person;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            3 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)

#### Select all versions of data

MariaDB [Company]> SELECT * FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            1 |
|  1 | Rasmus    | Johansson | m      |            2 |
|  1 | Rasmus    | Johansson | m      |            3 |
+----+-----------+-----------+--------+--------------+
3 rows in set (0.001 sec)

#### Select all versions of data and their duration (time)

MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
|  1 | Rasmus    | Johansson | m      |            3 | 2020-02-19 22:05:39.879380 | 2038-01-19 03:14:07.999999 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
3 rows in set (0.001 sec)

#### Select data at a specific moment

MariaDB [Company]> SELECT * FROM Person FOR SYSTEM_TIME AS OF TIMESTAMP '2020-02-19 22:05:20';
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            2 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)

MariaDB [Company]> SELECT *, ROW_START, ROW_END FROM Person FOR SYSTEM_TIME AS OF TIMESTAMP '2020-02-19 22:05:20'; 
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
1 row in set (0.001 sec)

## Important notes
In some situations it might not work as expected
- When enable versioning (timestamp) for whole table except one column, using upsert (insert ... on duplicate update) to update the unversioning column, might still create new version records.
- https://hackmd.io/WNz_xg9pRYiQrL69QkufrQ?view#on-duplicate-key-update-with-primary-key--multi-unique-key
- a workaround is to use txrid (transaction id) based versioning
- but it doesn't support partitions to separate current and historic data

## References
1. [Automatic Data Versioning in MariaDB Server 10.3](https://mariadb.com/resources/blog/automatic-data-versioning-in-mariadb-server-10-3/)
2. [Temporal Data Tables](https://mariadb.com/kb/en/temporal-data-tables/)
3. [Use Cases for MariaDB Data Versioning](https://mariadb.com/resources/blog/use-cases-for-mariadb-data-versioning/)
4. [Some Notes on MariaDB system-versioned Tables](https://www.percona.com/community-blog/2018/12/14/notes-mariadb-system-versioned-tables/)
5. [MariaDB Enterprise Backup](https://mariadb.com/docs/recovery/mariadb-enterprise-backup/#mariadb-enterprise-backup)
6. [Backing up MariaDB Temporal Database](https://stackoverflow.com/questions/56656104/backing-up-mariadb-temporal-database)

[docker-mariadb]: https://hub.docker.com/_/mariadb "mariadb Docker Official Images"

[exclude-column]: https://mariadb.com/kb/en/temporal-data-tables/#excluding-columns-from-versioning "Excluding Columns From Versioning"

[partitioning]: https://mariadb.com/kb/en/temporal-data-tables/#storing-the-history-separately "Storing the History Separately"
[upsert]: https://mariadb.com/kb/en/insert-on-duplicate-key-update/ "INSERT ON DUPLICATE KEY UPDATE"