Create system version table that tracks schema changes

user_5374387 0
Viewed 34

I was recently experimenting with this feature and it works well but I expected schema changes too be saved.

Is there any way to achieve the following?

CREATE table test (id INT NOT NULL, field_1 VARCHAR(10)) ENGINE=InnoDB;

ALTER TABLE test ADD SYSTEM VERSIONING;

INSERT INTO test (id, field_1) VALUES (1, "test");

SELECT NOW(); -> 2022-02-03 22:48:56

ALTER TABLE test DROP COLUMN field_1;

SELECT NOW(); -> 2022-02-03 22:58:56

SELECT * FROM test FOR SYSTEM_TIME AS OF TIMESTAMP '2022-02-03 22:48:56';

+----+---------+
| id | field_3 |
+----+---------+
|  1 | test    |
+----+---------+

SELECT * FROM test FOR SYSTEM_TIME AS OF TIMESTAMP '2022-02-03 22:58:56';

+----+
| id |
+----+
|  1 |
+----+

I am aware that there may be a penalty in terms of storage size but is there any way to achieve a complete history including schema changes with MariaDB/mysql. If not is there any other database where this would work?

mariadb mysql
1 Answers

SQL database definitions that roll forward and roll back in time -- a longstanding dream of many who develop database apps!

Alas, it is not present in system-versioned tables. Yet.

Memory (RAM and SSD) is getting cheap enough that this might be possible. Virtual machine snapsnot tech might be part of the answer.

MariaDB has a fairly robust database server to storage engine interface. One could clone InnoDB and make a storage engine with a time dimension to it.

Somebody is going to do this. It could be you.

user_2056080
Related