[Solved] Version Control in RDBMS Design question

Creating and using forms

[Solved] Version Control in RDBMS Design question

Postby gkick » Tue Sep 10, 2019 4:24 am

Hi,

This question probably belongs to some other forum, but I wouldn't know where.
GIT allows you to instantly figure out what has changed, when and by whom by comparing snapshots.

Now, to implement version control in a db I guess its a matter of creating multiple change records, if Joe Blow converts from Catholic to Buddhist etc...
Changing the ERD, table structure to do so is fairly straight forward, but it gets a little tricky at the time we want to change the original record in a form designed to show one record at a time.

One option would be to have a boolean "current" field, once de checked, clone the original record for update with timestamp and flag as current, then refresh the form.
But what if the new clone experiences no change at all, kill the record and switch back to the previous version ?
Anyone has done something along the line of change history?
Last edited by gkick on Wed Sep 11, 2019 3:30 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 263
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Version Control in RDBMS Design question

Postby Sliderule » Tue Sep 10, 2019 7:02 pm

With regard to database content . . . I mean the data ( that is the row data values ). . . since you are using HSQL Version 2.5.0 ( and any following version of HSQL ) as your database back-end, I suggest you read about the built in Temporal System-Versioned Tables and SYSTEM_TIME Period

Find a discurssion at the link below, or, in your HSQL PDF Documentation:

http://www.hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_sys_versioned_tables

HSQL Documentation: Temporal System-Versioned Tables and SYSTEM_TIME Period wrote:
System-versioned tables are tables that contain a SYSTEM_TIME period consisting of pair of columns defined as auto-generated TIMESTAMP WITH TIME ZONE, together with the SYSTEM VERSIONING clause.

The basic component is the SYSTEM_TIME period. For each row currently in the table, the start timestamp column, designated as ROW START, contains the UTC timestamp of the transaction of the INSERT or UPDATE statement that last modified the row. The end timestamp column, designated as ROW END, contains a timestamp in the distant future (end of epoch) that indicates the expiration date of the row. HyperSQL uses DATE '10000-01-01' as the expiration timestamp. A table can have the SYSTEM_TIME period without system versioning.

When WITH SYSTEM VERSIONING is used in table definition, any DELETE or UPDATE is performed as usual. But the deleted rows, or the old versions of the updated rows are kept in the table with the expiration timestamp changed to the UTC CURRENT_TIMESTAMP at the start of the transaction that contains the UPDATE or DELETE. For example, a row that is updated twice has two old versions kept in the table as well as the current version.

The history rows cannot be modified. Any DELETE or UPDATE statement only sees the current version of each row of the table and modifies them. SELECT statements also see the current version of the rows, unless the table reference in the SELECT statement is followed by FOR SYSTEM_TIME AS OF <timestamp> or FOR SYSTEM_TIME FROM <start timestamp> TO <end timestamp> or FOR SYSTEM_TIME BETWEEN <start timestamp> AND <end timestamp>.


I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1242
Joined: Thu Nov 29, 2007 9:46 am

Re: Version Control in RDBMS Design question

Postby gkick » Tue Sep 10, 2019 8:37 pm

Thanks for the links Sliderule, was thinking along the lines of temp table and or transaction with rollback
cheers
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 263
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Version Control in RDBMS Design question

Postby Villeroy » Tue Sep 10, 2019 9:13 pm

gkick wrote: if Joe Blow converts from Catholic to Buddhist etc...

Another many-to-many relation between persons and religions with a date of conversion.
The linking table consists of a person ID, a religion ID and a date. You can query the religious state of mind for any person at any date.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28537
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Solved]Re: Version Control in RDBMS Design question

Postby gkick » Wed Sep 11, 2019 3:28 am

Gee, this is fantastic, thanks guys

GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 263
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved]Version Control in RDBMS Design question

Postby Villeroy » Wed Sep 11, 2019 6:35 am

Status_Updates.odb
(24.66 KiB) Downloaded 44 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28537
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved]Version Control in RDBMS Design question

Postby gkick » Wed Sep 11, 2019 7:00 am

Thanks a lot for the db Villeroy, that´s real great!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 263
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests