-- TreeShare-LinkPastedAncestrySources.sql
/* 2018-01-27 Tom Holden ve3meo
rev 2018-01-28 exclude Other Sources because seemingly person dependent

When a TreeShare linked Ancestry Source is Memorised and Pasted through
RootsMagic Citation Manager, the pasted source loses its TreeShare link.
The TreeShare update to the Ancestry Tree creates a new Other Source rather
than linking to the Ancestry Source. This script creates a TreeShare link 
for such pasted Ancestry Sources.

Developed for RM 7.5.4.0. Lightly tested. USE AT OWN RISK.
  
*/
--List of unique, linked Ancestry Source citations
DROP VIEW IF EXISTS AncestrySourceLinks
;
CREATE TEMP VIEW AncestrySourceLinks AS
SELECT DISTINCT
  --LA.LinkID, LA.rmID, 
  LA.extID
  --, C.CitationID
  , C.SourceID
  , S.Name
  , C.Fields
FROM LinkAncestryTable LA
JOIN CitationTable C ON LA.rmID = C.CitationID AND LA.LinkType=4
JOIN SourceTable S USING(SourceID)
WHERE extID NOT LIKE '%:9000:%' -- exclude Other Sources
GROUP BY LA.rmID
;

-- List of TreeShare linked Ancestry Source citation IDs
DROP VIEW IF EXISTS LinkedCitations
;
CREATE TEMP VIEW LinkedCitations
AS
SELECT rmID AS CitationID 
FROM LinkAncestryTable 
WHERE LinkType = 4 
ORDER BY rmID
;

-- List of Unlinked Citation IDs
DROP VIEW IF EXISTS UnlinkedCitations
;
CREATE TEMP VIEW UnlinkedCitations
AS
SELECT CitationID 
FROM CitationTable
EXCEPT SELECT CitationID FROM LinkedCitations
;

-- Relate Unlinked Pasted Ancestry Sources to matching source TreeShare link
DROP VIEW IF EXISTS extIDforPastedAncestrySources
;
CREATE TEMP VIEW extIDforPastedAncestrySources
AS 
SELECT 
  CitationID
  , extID
  , Name -- for info only
FROM CitationTable
JOIN AncestrySourceLinks USING(SourceID, Fields)
WHERE CitationID IN (SELECT CitationID FROM UnlinkedCitations)
;

-- Add TreeShare Links For Unlinked Ancestry Source Citations
INSERT INTO LinkAncestryTable
SELECT 
  NULL AS LinkID
  , 2 AS extSystem
  , 4 AS LinkType
  , CitationID AS rmID
  , extID
  , 0 AS Modified  -- unsure whether should be 1 or 0; makes no diff in RM7.5.4.0
  , '' AS extVersion
  , 0.0 AS extDate
  , 0 AS Status
  , '' AS Note
FROM extIDforPastedAncestrySources
;

--SELECT 'Completed without SQLite error' AS Status
;
-- end of script