[Solved] Version Control in RDBMS Design question

[gkick]

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?

[Sliderule]

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

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.

[gkick]

Thanks for the links Sliderule, was thinking along the lines of temp table and or transaction with rollback
cheers
GK

[Villeroy]

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.

[gkick]

Gee, this is fantastic, thanks guys

GK

[Villeroy]

Status_Updates.odb

(24.66 KiB) Downloaded 190 times

[gkick]

Thanks a lot for the db Villeroy, that´s real great!