-- TMG-RM_convertTMG_IDtoRIN.sql
/*
2014-09-23 Tom Holden ve3meo
2014-09-24 handles single or small number of changes with proportional time; 
previous version was only suited to changing all; logs potential conflicts and 
warnings in temp table xRIN_TMGIDlog which should be opened if the trial query 
aborts in order to find and resolve the cause of the error; logs a warning if
multiple TMG_ID facts for same person. Log displayed if script completes without
an abort.
2014-10-08 RM 6.3.3.2 now imports TMG_ID into RootsMagic Reference No (REFN) fact type

RootsMagic 6.3.3.2 imports the TMG_ID into the standard Reference No (REFN) fact type.
While this fact type can be displayed after the name of the person in main views
and reports, it does not in the sidebar Index and some other places. Some users
would prefer that it did; the only number that does is the Record Number RIN, which 
is also the fastest search mechanism in RootsMagic Explorer. 

This procedure substitutes the TMG_ID value from REFN for
all references to PersonID or RIN, which can optionally be displayed after the name. 

It is safest to run this script soon after import of the TMG project into a new
database. It relies on the TMG ID being unique so that the resulting RIN is
unique and that there is only one REFN fact per person. 

Takes time - a 5000 record database was operated on for 32 seconds to change 98% 
of the records, 2 seconds to change a few.

****N.B. Be sure to close the database from RM before running this procedure****

*/


DROP TABLE IF EXISTS xRIN_TMGID
;
-- xRIN_TMGID builds a list of all RIN, TMG_ID value pairs
CREATE TEMP TABLE IF NOT EXISTS xRIN_TMGID (RIN INTEGER, TMGID INTEGER)
;
INSERT INTO xRIN_TMGID 
SELECT (OwnerID) AS RIN, (REPLACE(Details, '.', '')) AS TMGID
FROM EventTable  E
INNER JOIN FactTypeTable FT
ON E.EventType = FT.FactTypeID
WHERE FT.FactTypeID = 35   -- FactTypeID of standard Reference No. fact type 2014-10-08
--AND FT.ABBREV LIKE 'REFN'
AND RIN <> TMGID
;

DROP TABLE IF EXISTS xRIN_TMGIDlog
;
-- log of errors leading up to abort and progress beyond
CREATE TEMP TABLE xRIN_TMGIDlog (Time TEXT, Item TEXT, Value TEXT)
; 

-- here we need to test if there is any conflict and abort if necessary
-- TMGID matches a pre-existing RIN that is not among those being replaced 
INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Error: Pre-existing RIN', PersonID AS RIN FROM PersonTable
WHERE PersonID IN
(SELECT DISTINCT TMGID FROM xRIN_TMGID
EXCEPT SELECT DISTINCT RIN FROM xRIN_TMGID) 
; 

-- Are there any duplicate TMG_IDs in the set of changes?
INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Error: Duplicate TMG_ID', TMGID 
FROM 
(
 SELECT RIN, TMGID, COUNT()-1 AS Dupes FROM xRIN_TMGID
 GROUP BY TMGID
 ) WHERE Dupes > 0
;

-- Are there any duplicate RINs (multiple TMG_ID facts for same RIN) in the set of changes?
INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Warning: Multiple TMG_ID facts in RIN', RIN 
FROM 
(
 SELECT RIN, COUNT()-1 AS Dupes FROM xRIN_TMGID
 GROUP BY RIN
 ) WHERE Dupes > 0
;

-- Show errors if any
SELECT * FROM xRIN_TMGIDlog
;

-- Create a Trial table to cause an abort by above errors if any
DROP TABLE IF EXISTS xRINtrial
; 

CREATE TEMP TABLE xRINtrial (PersonID INTEGER PRIMARY KEY)
;

INSERT INTO xRINtrial
SELECT PersonID FROM PersonTable
EXCEPT SELECT RIN FROM xRIN_TMGID
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Starting trial changes of RIN in xRINtrial table', ''
;
INSERT INTO xRIN_TMGIDlog
SELECT '', ' - aborted here if errors listed above', ''
; 

-- Show errors from log if any (does not display)
SELECT * FROM xRIN_TMGIDlog
;

-- any errors should cause an ABORT
INSERT INTO xRINtrial
SELECT TMGID 
FROM xRIN_TMGID
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Copying affected records from PersonTable to xPersonTable', ''
;

DROP TABLE IF EXISTS xPersonTable
;
-- xPersonTable will hold temporarily those records from PersonTable
-- whose RIN will be changed to match TMGID
CREATE TEMP TABLE xPersonTable
AS
SELECT *
FROM PersonTable
WHERE PersonID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Deleting affected records from PersonTable', ''
;
-- those records whose RIN will be changed are deleted from PersonTable
DELETE 
--SELECT *
FROM PersonTable
WHERE PersonID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Modifying copies of affected records in xPersonTable', ''
;
UPDATE xPersonTable
SET PersonID = (SELECT TMGID FROM xRIN_TMGID WHERE PersonID = RIN)
WHERE PersonID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Copying modified records from xPersonTable to PersonTable', ''
;

INSERT INTO PersonTable
SELECT * FROM xPersonTable ORDER BY PersonID
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating AddressLinkTable', ''
;
UPDATE AddressLinkTable
SET OwnerID = (SELECT TMGID FROM xRIN_TMGID WHERE OwnerID = RIN)
WHERE OwnerType = 0
AND OwnerID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating ChildTable', ''
;
UPDATE ChildTable
SET ChildID = (SELECT TMGID FROM xRIN_TMGID WHERE ChildID = RIN)
WHERE ChildID IN (SELECT RIN FROM xRIN_TMGID);

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating CitationTable', ''
;
UPDATE CitationTable
SET OwnerID = (SELECT TMGID FROM xRIN_TMGID WHERE OwnerID = RIN)
WHERE OwnerType = 0
AND OwnerID IN (SELECT RIN FROM xRIN_TMGID);
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating EventTable', ''
;
UPDATE EventTable
SET OwnerID = (SELECT TMGID FROM xRIN_TMGID WHERE OwnerID = RIN)
WHERE OwnerType = 0
AND OwnerID IN (SELECT RIN FROM xRIN_TMGID);
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating FamilyTable', ''
;
INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), ' - FatherIDs', ''
;
UPDATE FamilyTable
SET 
  FatherID = (SELECT TMGID FROM xRIN_TMGID WHERE FatherID = RIN)
WHERE FatherID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), ' - MotherIDs', ''
;
UPDATE FamilyTable
SET 
  MotherID = (SELECT TMGID FROM xRIN_TMGID WHERE MotherID = RIN)
WHERE MotherID IN (SELECT RIN FROM xRIN_TMGID)
;
   
INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), ' - ChildIDs', ''
;
UPDATE FamilyTable
SET 
  ChildID = (SELECT TMGID FROM xRIN_TMGID WHERE ChildID = RIN)
WHERE ChildID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating GroupTable', ''
;
INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), ' - StartIDs', ''
;
UPDATE GroupTable
SET StartID = (SELECT TMGID FROM xRIN_TMGID WHERE StartID = RIN)
WHERE StartID IN (SELECT RIN FROM xRIN_TMGID)
; 

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), ' - EndIDs', ''
;
UPDATE GroupTable
SET EndID = (SELECT TMGID FROM xRIN_TMGID WHERE EndID = RIN)
WHERE EndID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating LinkTable', ''
;
UPDATE LinkTable
SET rmID = (SELECT TMGID FROM xRIN_TMGID WHERE rmID = RIN)
WHERE rmID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating MediaLinkTable', ''
;
UPDATE MediaLinkTable
SET OwnerID = (SELECT TMGID FROM xRIN_TMGID WHERE OwnerID = RIN)
WHERE OwnerType = 0
AND OwnerID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating NameTable', ''
;
UPDATE NameTable
SET OwnerID = (SELECT TMGID FROM xRIN_TMGID WHERE OwnerID = RIN)
WHERE OwnerID IN (SELECT RIN FROM xRIN_TMGID)
;


INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating ResearchTable', ''
;
UPDATE ResearchTable
SET OwnerID = (SELECT TMGID FROM xRIN_TMGID WHERE OwnerID = RIN)
WHERE OwnerID IN (SELECT RIN FROM xRIN_TMGID)
AND OwnerType = 0
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating URLTable', ''
;
UPDATE URLTable
SET OwnerID = (SELECT TMGID FROM xRIN_TMGID WHERE OwnerID = RIN)
WHERE OwnerID IN (SELECT RIN FROM xRIN_TMGID)
AND OwnerType = 0
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'Updating WitnessTable', ''
;
UPDATE WitnessTable
SET PersonID = (SELECT TMGID FROM xRIN_TMGID WHERE PersonID = RIN)
WHERE PersonID IN (SELECT RIN FROM xRIN_TMGID)
;

INSERT INTO xRIN_TMGIDlog
SELECT strftime('%H:%M:%f','now'), 'TMG_ID to RIN conversions COMPLETE', '***'
;

-- Show log
SELECT * FROM xRIN_TMGIDlog
;