{"id":275,"date":"2014-05-12T15:35:15","date_gmt":"2014-05-12T13:35:15","guid":{"rendered":"http:\/\/wordpress.duerr.name\/?p=275"},"modified":"2015-11-07T11:11:29","modified_gmt":"2015-11-07T10:11:29","slug":"insert-only","status":"publish","type":"post","link":"https:\/\/www.jd-engineering.de\/insert-only\/","title":{"rendered":"Insert Only"},"content":{"rendered":"

I’m currently attending the online lecture ‚in memory computing‘ by http:\/\/openhpi.com<\/a>. One of the founders of SAP, Hasso Plattner, stated that they use insert only approach to keep a history of the record in the table. Since only ~10% of the records get updated during lifetime, there should be no performance penalty being expected.<\/p>\n

I was very curious if this is possible with ADS aswell and implemented it using triggers.<\/p>\n

Prepare the table<\/h1>\n

First of all the table has to be created and prepared. In order to know the lifecycle of a record, two timetamp fields are being used: validFrom and validTo. A field of type ROWVERSION keeps track of modifications in the table. So the resulting table looks like:<\/p>\n

\r\nCREATE TABLE tbTest( \r\n      id Char(36),\r\n      fname Char(30),\r\n      lname Char(30),\r\n      dob Date,\r\n      gender Char(1),\r\n      country Char(30),\r\n      city Char(30),\r\n      validFrom TimeStamp,\r\n      validTo TimeStamp,\r\n      rv RowVersion);\r\n<\/pre>\n
Invalid (deleted) records shouldn’t be displayed, so we need to filter them out. Easiest is to work only with a view of the table:<\/p>\n
\r\nCREATE VIEW test AS \r\n  SELECT id, fname, lname, dob, gender, country, city, rv \r\n  FROM tbtest\r\n  WHERE validTo IS NULL;\r\n<\/pre>\n
Inserts<\/h1>\n
On an INSERT operation, a unique ID should be given to the record if it’s not already set. Then we need to make sure our validity columns get populated: validFrom has to be set to the actual timestamp and validTo has to be reset to NULL. Since this is an INSTEAD OF INSERT trigger that replaces the INSERT operation, we finally need to make sure the record gets inserted into the table.<\/p>\n
\r\nCREATE TRIGGER trig_ins ON tbTest INSTEAD OF INSERT \r\nBEGIN \r\n  update __new set id=newidstring(d) where id is null;\r\n  update __new set validFrom=now(), validTo=NULL;\r\n  insert into tbtest select * from __new;\r\nEND;\r\n<\/pre>\n
Deletes<\/h1>\n
A DELETE doesn’t remove the record from the table, but invalidates it by setting it’s validTo column to the actual timestamp:<\/p>\n
\r\nCREATE TRIGGER trig_del ON tbTest INSTEAD OF DELETE \r\nBEGIN \r\n  update tbtest set validTo=now() where rowid = ::stmt.TrigRowId and validTo is null;\r\nEND;\r\n<\/pre>\n
Updates<\/h1>\n
An UPDATE operation invaliates the actual record and inserts a new record with the new values. This trigger is a bit more complicated because a delete will also fire the update and so removing the record from the view will not work. To bypass this, we’ll check the new record if it’s validTo field is still unset (DELETE willset this field for the new record).<\/p>\n
\r\nCREATE TRIGGER trig_upd ON tbTest INSTEAD OF UPDATE \r\nBEGIN \r\n  update tbtest set validto=now() where rowid = ::stmt.TrigRowId and validto is null;\r\n  if __new.validto is null then\r\n    insert into tbtest select * from __new;\r\n  end;\r\nEND;\r\n<\/pre>\n
Testing<\/h1>\n
As a first test let’s insert a new record:<\/p>\n
\r\nINSERT INTO test (fname, lname, dob, gender, country, city)\r\n  VALUES ('Joe', 'Doe', '2001-09-11', 'm', 'U.S.', 'New York');\r\n<\/pre>\n
Looking at the view ‚test‘ the record is there. In the table ‚tbTest‘ the record can also be found an the validFrom field is set. When updating the record (e.g. Joe Doe moves to Chicago), an updated version will be displayed in the view and the table will now contain two records (one with validTo set and one with validTo unset).<\/p>\n
\r\nUPDATE test SET city='Chicago' WHERE id = '79298981-cc05-2245-b6d4-e413996f14dd';\r\n<\/pre>\n
So the behaviour is exactly what we want: Only actual records are shown in the view, but there’s still a full history available in the table.<\/p>\n
Let’s try a delete:<\/p>\n
\r\nDELETE FROM test WHERE id = '79298981-cc05-2245-b6d4-e413996f14dd'\r\n<\/pre>\n
This command removes the record from the view, but the invalidated copy is still left in the table. So this works aswell.<\/p>\n
Bulk inserts, updates and deletes<\/h1>\n
Bulk inserts and deletes will still work, but if you now try to update all records, it will result in an ifinite loop:<\/p>\n
\r\nUPDATE test SET city='Chicago';\r\n<\/pre>\n
This is caused by using a live view: any update will append a new record and so it will never come to an end. To bypass this behaviour, we need to use a ROWVERSION column (and that’s the reason why I’ve created it in the first place):<\/p>\n
\r\nUPDATE test SET city='Chicago' WHERE rv<=(SELECT max(rv) FROM test);\r\n<\/pre>\n
Summary<\/h1>\n
Implementing an INSERT ONLY approach into ADS using triggers is possible as long as you only access one record at a time. But you need to be very careful on bulk updates in order to prevent an infinite loop.<\/p>\n","protected":false},"excerpt":{"rendered":"
I’m currently attending the online lecture ‚in memory computing‘ by http:\/\/openhpi.com. One of the founders of SAP, Hasso Plattner, stated that they use insert only approach to keep a history of the record in the table. Since only ~10% of<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_newsletter_tier_id":0,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[4],"tags":[5,9,10],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6r3Yz-4r","_links":{"self":[{"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/posts\/275"}],"collection":[{"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/comments?post=275"}],"version-history":[{"count":2,"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/posts\/275\/revisions"}],"predecessor-version":[{"id":347,"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/posts\/275\/revisions\/347"}],"wp:attachment":[{"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/media?parent=275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/categories?post=275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jd-engineering.de\/wp-json\/wp\/v2\/tags?post=275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}