Mariadb Temporal Tables

[mebeingken]

Audit/versioning columns for your db tables in your schema. Yay or nay? What other recurring tables/columns do you use?

Regarding versioning. Check this out for mariadb: It looks promising. [image] You need to add row_start and row_end to the SELECT statement as they are system columns generated by the versioning system.

@JonL , et al

Anybody tried temporal tables yet?

Here’s my first attempt:
Mariadb: 10.3.18
Wappler 2.2.7

Created a new table using mysql terminal command (works fine)

create table contacts ( id int(11) unsigned null, full_name varchar(50) null ) WITH SYSTEM VERSIONING;

I use Datagrip as my db gui, and it sees the new table, and I can add records

But I cannot get Wappler to recognize the new table:

I have refreshed many times in the insert action, and I have re-saved the database connection action several times as well.

@patrick Anything you can adjust on your end?

Temporal Data Tables

–Ken

[JonL]

I haven’t tried them yet in wappler. Could it be because wappler reads from the information_schema database and doesn’t account for tables with type SYSTEM VERSIONED or similar.

[JonL]

Bingo.

$results = getResult("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$database' AND TABLE_TYPE = 'BASE TABLE'");

As SYSTEM VERSIONED is fairly new and probably quite niche it’s not being retrieved by Wappler.

You can try and patch dmxDatabase.php to include them and see what happens while we wait for the reply from the team.

[George]

Interesting, will add it as wel. Didn’t knew they weren’t picked up. @patrick will check it out

[mebeingken]

Good pull Jon. Okay, I have the table showing now using this change to dmxDatabase.php:

$results = getResult("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$database' AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'SYSTEM VERSIONED')");

Will now see how far I can get!

[mebeingken]

This looks very promising and is very cool. Your tables do not have any new visible fields, yet you can just go back in time.

SELECT * FROM contacts FOR SYSTEM_TIME AS OF TIMESTAMP'2019-10-05 17:07:06';

Here are two selects, just changing the timestamp:

… but we’re not there yet.

In Wappler:

I’m assuming it just isn’t prepared for this kind of statement.

[JonL]

Yeah it is quite cool and it is all managed at database level out of the box.

Have you tested partitioning the table or using FOR SYSTEM_TIME ALL?