Versioning Notes (In Progress)
Master Table Update
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.
- 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.
ID 1 2 3 - Find any records in Master Table that match the Update List, and mark them as inactive. They will be replaced with new inserts.
UPDATE _a_tbl_master SET active = 0 FROM #master_update_source _new WHERE _a_tbl_master.id = _new.id;
- 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.
INSERT INTO #master_update_inserts (id, id_group, update_by) SELECT _current.id, _current.id_group, @update_by FROM #master_update_source _source LEFT JOIN _a_tbl_master _current ON _source.id = _current.id
- Apply list of inserts to the Master Table. Use OUTPUT clause to populate a temp table with a list of IDs for each insert.
INSERT INTO _a_tbl_master (id_group, update_by) OUTPUT INSERTED.ID INTO #master_update_new_id SELECT id_group, update_by FROM #master_update_inserts
- 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.
- 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.
ID id_group created update_by update_ip active 2844 2884 2016-12-28 10:13:45.1900000 115 128.163.237.37 False 2845 2845 2016-12-28 10:13:45.1900000 115 128.163.237.37 False 2846 2846 2016-12-28 10:13:45.1900000 115 128.163.237.37 False 2989 2844 2016-12-28 22:42:14.7930000 115 128.163.237.37 True 2990 2845 2016-12-28 22:42:14.7930000 115 128.163.237.37 True 2991 2846 2016-12-28 22:42:14.7930000 115 128.163.237.37 True
Full procedure (in progress)
-- Caskey, Damon V. -- 2016-12-20 -- -- Update master table. Must be run before -- any data table controlled by master is -- updated. Outputs record set containing -- IDs for the updated master that a -- calling data update procedure will need. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[_a_master_update] -- Parameters @arg_id int = NULL, -- Primary key. @arg_update_by int = NULL, -- ID from account table. @arg_update_ip varchar(50) = NULL -- User IP, supplied from application. AS BEGIN -- Let's create the temp tables we'll need. -- List of update requests. All we -- need are IDs. The rest is handled -- by parameters or generated by -- default data binds in the master -- table. CREATE TABLE #master_update_source ( id int ) -- Prepared list of items that -- will be inserted into the master -- table. CREATE TABLE #master_update_inserts ( id int, id_group int ) -- List of new item IDs created when -- inserts are perform on master -- table. CREATE TABLE #master_update_new_id ( id int ) -- Populate update source (for experiment). INSERT INTO #master_update_source (id) VALUES (-1), (-1), (2844), (2845), (2846) -- Find any records that match our -- update list and mark them as inactive. UPDATE _a_tbl_master SET active = 0 FROM #master_update_source _new WHERE _a_tbl_master.id = _new.id; -- Prepare inserts. Here we are adding inserts for new -- records AND for records that already exist. We do the -- later so we can get the current group ID and pass it on. INSERT INTO #master_update_inserts (id, id_group) SELECT _current.id, _current.id_group FROM #master_update_source _source LEFT JOIN _a_tbl_master _current ON _source.id = _current.id -- Apply the insert list (insert into master table). New -- IDs created by the database are output into -- a temp table. INSERT INTO _a_tbl_master (id_group, update_by, update_ip) OUTPUT INSERTED.ID INTO #master_update_new_id SELECT id_group, @arg_update_by, @arg_update_ip FROM #master_update_inserts -- For new records, seed the group ID with -- new record's ID. UPDATE _a_tbl_master SET id_group = _new.id FROM #master_update_new_id _new WHERE _a_tbl_master.id = _new.id AND _a_tbl_master.id_group IS NULL; END