Maria DB with System-Versioned Tables do not show schema

Wappler General How To
#1

Wappler 5.1.4, Mac M1, MariaDB 10.5.x

Adding a table via a third party app like Navicat will not show in Database Manager nor Server Connect if created with system versioning as per below.

CREATE TABLE t (
   x INT
) WITH SYSTEM VERSIONING;

If I create the same table without system versioning, then a schema refresh in either database manager or server connect shows the table, like this

CREATE TABLE t (
   x INT
)

Only work around is to drop the system versioning on all tables, then refresh schema in Wappler, and then go add all the system versioning back, which for a test is fine.

ALTER TABLE t DROP SYSTEM VERSIONING;
/* Refresh Schema in Wappler */
ALTER TABLE t ADD SYSTEM VERSIONING;

As the above solution can not really be used in production because you would defeat the entire purpose of versioning and lose the entire versioned history, I am manually adding the table schema to the .wappler/targets/{target_name}/databases/db.json file and rebooting Wappler to see all my tables correctly.

Wappler 5.1.5 Released
#2

Yes this is some serious issue, i checked the above flow and table was not showing in schema.

1 Like
#3

This is starting to turn into quite a painful bug for me, slowing things down quite a bit.

#4

@Teodor, do you know if this is getting fixed in the next update possibly. Sorry to be a pest, but this problem is causing me a major headache.

#5
#6

System Versioned tables are very special new database table types that we do not support yet.

It is also not fully supported in Knex the database layer that we use in NodeJS.

Why do you need them?

#7

So if you want that we add support for system versioned tables, you can always open a feature request.

Do note that next to the special creation of the system tables, also a specific sql query language is need to do the time based history queries. So you will have to write also a lot of custom sql queries.

So it is quite unlikely that you really need those temporal tables called system version tables for now, but maybe you do have a specific need that require them, so enlighten us and if really needed post a FR.

#8

This is the easiest way to have complete version history of all changes on a table.

This post is about supporting regular tables, not versioned tables.
Wappler stops showing regular tables as soon as versioning is enabled.

Not a problem really. These versioned tables have no use in regular app UI and operations. They are more to be used in cases where regular query builder is not enough anyways.

#9

No that is not true. Regular tables are always supported.

The post is about system versioned tables being invisible as such and they maybe should be visible just as regular tables.

#10

Even if not fully supported as such, it would be great if Wappler could just treat a system versioned table as a normal table, so it can still retrieve the schema.

I know that full support for this would mean allowing their creation etc. However just keeping them visible would be all I personally require.

The reason I need to use this, is easiest explained in giving you an example of my scenario.

I am working with a few different booking providers for accommodation and activities, each sends availability and booking data via web hooks back to my wappler built system.

A booking web hook sends the entire booking, which could be 100+ lines of json with multiple arrays and objects.
Each part of that booking needs to have various actions attached, payments go to stripe, as well as tripletex for invoicing, availability goes to resource manager tools as well as staff scheduling tools.
So the entire booking data gets stored in my database for its various processing needs.

When a user on the third party booking API makes a change to their booking, is where I start having issues, as if a product is removed it needs a refund or that portion, if only a deposit was paid initially then the remainder of the payment needs to be charged and invoiced/credited etc.

The 4 third party booking APIs I am currently working with do not send a web hook update with only the change, but instead send the entire booking again.

So in reality even if the user only added a note to their booking saying, they are vegetarian, I have no way of knowing that only that changed, and there is no need to do more in stripe, invoicing, staff calendar schedules etc.

On the other hand the change could be far more complex and my processing may need to go do many tasks based on the change.

Using versioned tables, I am able to compare each current record with each historic record comparing the contents of each column and foreign table linkages to find only the change, so I can run the additional processing as needed based on what was actually altered.

This is mostly all working currently using versioned tables, and scripts to loop through and do full comparisons.

The only issue is, any alteration to the schema of these tables, as well as any new versioned tables created are invisible in Wappler, and I have to manually cheat the database schema file and manually add the json to allow usage in Wappler.

I understand full support would be a far bigger scope, but for my needs at least, I am doing all the manual work in Navicat, and only need wappler to read the structure.

#11

That sounds reasonable, will make them at least visible just as regular tables.

1 Like
#12

Thank you so much, I shall have a beer in your honor, thank you, will be such a help.

#13

That’s what I meant… but I assumed tables with system versioning enabled would not have anything special about them in terms of schema as I have seen while running queries on them.
If they do get marked as system versioned tables, then yes, just showing them as regular tables is what is needed at the moment.

#14
#15

Fixed in Wappler 5.1.5

#16
#17

This topic was automatically closed after 47 hours. New replies are no longer allowed.