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:
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?
--Ken
JonL
October 5, 2019, 3:28pm
2
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
October 5, 2019, 4:01pm
3
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.
1 Like
George
October 5, 2019, 4:42pm
4
Interesting, will add it as wel. Didn’t knew they weren’t picked up. @patrick will check it out
2 Likes
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!
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.
1 Like
JonL
October 5, 2019, 8:48pm
7
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?
Apple
September 29, 2024, 5:07am
8
I've stumbled into some obscure Wappler database manager option. Is this working? Did you ever set it up, Ken?
If my name wasn’t attached to this, I’d have sworn I never looked at temporal tables.
I must have moved on.
1 Like
