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?
[Solved] Version Control in RDBMS Design question
[Solved] Version Control in RDBMS Design question
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
Re: Version Control in RDBMS Design question
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/dat ... ned_tables
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Find a discurssion at the link below, or, in your HSQL PDF Documentation:
http://www.hsqldb.org/doc/2.0/guide/dat ... ned_tables
I hope this helps, please be sure to let me / us know.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>.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Version Control in RDBMS Design question
Thanks for the links Sliderule, was thinking along the lines of temp table and or transaction with rollback
cheers
GK
cheers
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Version Control in RDBMS Design question
Another many-to-many relation between persons and religions with a date of conversion.gkick wrote: if Joe Blow converts from Catholic to Buddhist etc...
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
[Solved]Re: Version Control in RDBMS Design question
Gee, this is fantastic, thanks guys
GK
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: [Solved]Version Control in RDBMS Design question
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved]Version Control in RDBMS Design question
Thanks a lot for the db Villeroy, that´s real great!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend