{"id":6105,"date":"2016-12-28T23:57:18","date_gmt":"2016-12-29T04:57:18","guid":{"rendered":"https:\/\/www.caskeys.com\/dc\/?p=6105"},"modified":"2017-01-23T16:22:22","modified_gmt":"2017-01-23T21:22:22","slug":"mssql-relational-record-versioning-in-progress","status":"publish","type":"post","link":"https:\/\/www.caskeys.com\/dc\/mssql-relational-record-versioning-in-progress\/","title":{"rendered":"MSSQL Relational Record Versioning – In Progress"},"content":{"rendered":"

Versioning Notes (In Progress)<\/h1>\n

Master Table Update<\/h2>\n

The master table controls all data tables in the database, including sub tables containing one to many relational data (ex. One Person -> Many Phone Numbers). This means our master update procedure must be able to handle multiple record updates at once and be modular enough to execute by another update procedure – both for the updating the that procedure’s target data table AND any related sub tables. Otherwise the whole encapsulation concept falls apart.<\/p>\n

    \n
  1. Populate a temp table with list of update IDs. These are the record IDs that we want modified (or created as the case may be). Ultimately we will be inserting these as new records with new IDs no matter what, but we’ll need to perform versioning if these IDs already exist in the master table.
    \n\n\n\n\n\n\n\n
    ID<\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\n<\/tr>\n
    2<\/td>\n<\/tr>\n
    3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n
  2. Find any records in Master Table that match the Update List, and mark them as inactive. They will be replaced with new inserts.\n
    \r\nUPDATE\r\n\t\t\t_a_tbl_master\r\n\t\tSET\r\n\t\t\tactive = 0\r\n\t\tFROM\r\n\t\t\t#master_update_source _new\r\n\t\tWHERE \r\n\t\t\t_a_tbl_master.id = _new.id;<\/pre>\n<\/li>\n
  3. Prepare a list of inserts consisting of records where update list and master table IDs math, AND unmatched items in the Update List. The combined list is used to populate a temp table. This is also where we acquire the group ID for existing records. The group ID will be applied to new inserts (versions) of the existing records.\n
    \r\nINSERT INTO \r\n\t\t\t#master_update_inserts (id, id_group, update_by)\r\n\t\tSELECT \r\n\t\t\t_current.id, _current.id_group, @update_by\r\n\t\tFROM #master_update_source _source  \r\n\t\t\tLEFT JOIN _a_tbl_master _current ON _source.id = _current.id\r\n<\/pre>\n<\/li>\n
  4. Apply list of inserts to the Master Table. Use OUTPUT clause to populate a temp table with a list of IDs for each insert.\n
    \r\nINSERT INTO \r\n\t\t\t_a_tbl_master (id_group, update_by)\r\n\t\tOUTPUT \r\n\t\t\tINSERTED.ID \r\n\t\t\t\tINTO #master_update_new_id\r\n\t\tSELECT \r\n\t\t\tid_group, update_by \r\n\t\tFROM \r\n\t\t\t#master_update_inserts<\/pre>\n<\/li>\n
  5. Using the list of IDs created when records were inserted to Master Table, we run an UPDATE against Master Table on the list of newly created IDs, where the id_group field is empty. This is to seed new records (not new versions of existing records) with a group ID.<\/li>\n
  6. Master Table is now populated. New records will have a group ID identical to their ID, while existing records will have a new ID, but retain their previous group ID.
    \n\n\n\n\n\n\n\n\n\n\n
    ID<\/th>\nid_group<\/th>\ncreated<\/th>\nupdate_by<\/th>\nupdate_ip<\/th>\nactive<\/th>\n<\/tr>\n<\/thead>\n
    2844<\/td>\n2884<\/td>\n2016-12-28 10:13:45.1900000<\/td>\n115<\/td>\n128.163.237.37<\/td>\nFalse<\/td>\n<\/tr>\n
    2845<\/td>\n2845<\/td>\n2016-12-28 10:13:45.1900000<\/td>\n115<\/td>\n128.163.237.37<\/td>\nFalse<\/td>\n<\/tr>\n
    2846<\/td>\n2846<\/td>\n2016-12-28 10:13:45.1900000<\/td>\n115<\/td>\n128.163.237.37<\/td>\nFalse<\/td>\n<\/tr>\n
    2989<\/td>\n2844<\/td>\n2016-12-28 22:42:14.7930000<\/td>\n115<\/td>\n128.163.237.37<\/td>\nTrue<\/td>\n<\/tr>\n
    2990<\/td>\n2845<\/td>\n2016-12-28 22:42:14.7930000<\/td>\n115<\/td>\n128.163.237.37<\/td>\nTrue<\/td>\n<\/tr>\n
    2991<\/td>\n2846<\/td>\n2016-12-28 22:42:14.7930000<\/td>\n115<\/td>\n128.163.237.37<\/td>\nTrue<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<\/ol>\n
    Full procedure (in progress)<\/p>\n
    \r\n-- Caskey, Damon V.\r\n-- 2016-12-20\r\n--\r\n-- Update master table. Must be run before\r\n-- any data table controlled by master is\r\n-- updated. Outputs record set containing\r\n-- IDs for the updated master that a\r\n-- calling data update procedure will need.\r\n\r\n\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\nALTER PROCEDURE [dbo].[_a_master_update]\r\n\t\r\n\t-- Parameters\r\n\t@arg_id\t\t\t\t\tint\t\t\t\t= NULL,\t\t-- Primary key. \t\t\t\r\n\t@arg_update_by\t\t\tint\t\t\t\t= NULL,\t\t-- ID from account table.\r\n\t@arg_update_ip\t\t\tvarchar(50)\t\t= NULL\t\t-- User IP, supplied from application.\r\n\t\t\t\r\nAS\r\nBEGIN\r\n\r\n\t-- Let's create the temp tables we'll need.\r\n\t\t\r\n\t\t-- List of update requests. All we\r\n\t\t-- need are IDs. The rest is handled\r\n\t\t-- by parameters or generated by\r\n\t\t-- default data binds in the master\r\n\t\t-- table.\r\n\t\tCREATE TABLE #master_update_source\r\n\t\t(\r\n\t\t\tid\t\t\tint\r\n\t\t)\r\n\r\n\t\t-- Prepared list of items that\r\n\t\t-- will be inserted into the master\r\n\t\t-- table.\r\n\t\tCREATE TABLE #master_update_inserts\r\n\t\t(\r\n\t\t\tid\t\t\tint,\r\n\t\t\tid_group\tint\r\n\t\t)\r\n\r\n\t\t-- List of new item IDs created when\r\n\t\t-- inserts are perform on master\r\n\t\t-- table.\r\n\t\tCREATE TABLE #master_update_new_id\r\n\t\t(\r\n\t\t\tid\t\t\tint\r\n\t\t)\r\n\t\t\r\n\r\n\t-- Populate update source (for experiment).\r\n\tINSERT INTO #master_update_source (id)\r\n\tVALUES (-1), (-1), (2844), (2845), (2846)\r\n\r\n\r\n\t-- Find any records that match our \r\n\t-- update list and mark them as inactive.\r\n\t\tUPDATE\r\n\t\t\t_a_tbl_master\r\n\t\tSET\r\n\t\t\tactive = 0\r\n\t\tFROM\r\n\t\t\t#master_update_source _new\r\n\t\tWHERE \r\n\t\t\t_a_tbl_master.id = _new.id;\r\n\r\n\t-- Prepare inserts. Here we are adding inserts for new\r\n\t-- records AND for records that already exist. We do the\r\n\t-- later so we can get the current group ID and pass it on. \r\n\t\tINSERT INTO \r\n\t\t\t#master_update_inserts (id, id_group)\r\n\t\tSELECT \r\n\t\t\t_current.id, _current.id_group\r\n\t\tFROM #master_update_source _source  \r\n\t\t\tLEFT JOIN _a_tbl_master _current ON _source.id = _current.id\r\n\r\n\t-- Apply the insert list (insert into master table). New\r\n\t-- IDs created by the database are output into\r\n\t-- a temp table.\r\n\t\tINSERT INTO \r\n\t\t\t_a_tbl_master (id_group, update_by, update_ip)\r\n\t\tOUTPUT \r\n\t\t\tINSERTED.ID \r\n\t\t\t\tINTO #master_update_new_id\r\n\t\tSELECT \r\n\t\t\tid_group, @arg_update_by, @arg_update_ip \r\n\t\tFROM \r\n\t\t\t#master_update_inserts\r\n\r\n\t-- For new records, seed the group ID with\r\n\t-- new record's ID.\r\n\t\tUPDATE\r\n\t\t\t_a_tbl_master\r\n\t\tSET\r\n\t\t\tid_group = _new.id\r\n\t\tFROM\r\n\t\t\t#master_update_new_id _new\r\n\t\tWHERE \r\n\t\t\t_a_tbl_master.id = _new.id AND _a_tbl_master.id_group IS NULL;\r\n\r\nEND\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
    Versioned record support with fully encapsulated procedures – no functions.<\/p>\n","protected":false},"author":1,"featured_media":6206,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"MSSQL versioning schema (IP) by yours truly- Fully encapsulated, no functions. #technology_temerity","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[71],"tags":[299,253,271,269,254,286],"class_list":["post-6105","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology-temerity","tag-career-chaos","tag-coding-database","tag-projects-database-record-versioning","tag-applications-inspector-blair","tag-coding-sql","tag-uk-ehs"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.caskeys.com\/dc\/wp-content\/uploads\/2017\/01\/logo-sql.png","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5lNM5-1At","jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6105","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/comments?post=6105"}],"version-history":[{"count":5,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6105\/revisions"}],"predecessor-version":[{"id":6146,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/6105\/revisions\/6146"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/media\/6206"}],"wp:attachment":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/media?parent=6105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/categories?post=6105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/tags?post=6105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}