-- MergeAncestryURLsToFTM.sql
-- 2012-11-05 Tom Holden ve3meo
/*
GEDCOM from Ancestry.com does not contain links to citation images
 but does contain links to Ancestry citation pages.
GEDCOM from Family Tree Maker 2012 sync'd to same tree contains links to automatically
 downloaded image files but no link to Ancestry citation page.
This script attempts to merge the URLs from a database imported from the Ancestry
 GEDCOM into the database imported from the FTM GEDCOM so that Source citations
 have both the downloaded images and links to the Ancestry citation page.
 
The URLs for source citations are contained in the Comments field of CitationTable in the
 database imported from the Ancestry GEDCOM. The script prepends this Comments field value to
 the value of the Comments field in the CitationTable of the database from FTM2012.

The script wants the FTM imported rmgc file as the main database and the database 
 imported from the Ancestry GEDCOM as the ATTACHed database. It creates two temporary
 tables Pri and Sec from identical queries on the two databases. From a SELECT query on these 
 temporary tables that matches key distinguishing fields to relate the CitationIDs 
 of the two databases, an UPDATE is performed on the Comments field of every record
 in the database imported from FTM.   


*/

-- **** Change the path and filename in the following line to match yours ****
ATTACH 'C:\MyDocs\FamilyTree\Holden-Cudworth_Wilson-Wason\AncestryGED.rmgc' AS B
;

-- CREATE Primary table from query of primary or main database, the one 
--  imported from the Family Tree Maker GEDCOM
DROP TABLE IF EXISTS Pri
;
CREATE TEMP TABLE Pri AS

-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, e.EventID AS EvtID, f.NAME AS Citer, e.Date AS EvtDate, 
  s.NAME AS Source, s.refnumber, s.actualtext AS SrcTxt, s.comments AS SrcComment, s.fields AS SrcFlds,
  c.refnumber, c.actualtext AS ActualText, c.comments, c.fields AS CitFlds
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid = s.sourceid
  LEFT OUTER JOIN eventtable e ON c.ownerid = e.eventid
  LEFT OUTER JOIN persontable p ON e.ownerid = p.personid
  LEFT OUTER JOIN nametable n ON p.personid = n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype = f.facttypeid
WHERE c.ownertype = 2 AND e.ownertype = 0 AND f.ownertype = 0 AND +n.IsPrimary = 1 
;

INSERT INTO Pri
-- all Personal citations for Individual
SELECT  c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname AS Surname, n.suffix AS Sfx, n.prefix AS Pfx, n.given AS Givens, n.birthyear AS Born, 
  n.deathyear AS Died, 0 AS EvtID, 'Personal' AS Citer, '' AS EvtDate,
  s.NAME AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, s.fields AS SrcFlds,
  c.refnumber AS CitREFN, c.actualtext AS CitTxt, c.comments AS CitComment, c.fields AS CitFlds
FROM  citationtable c 
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN persontable p ON c.ownerid=p.personid 
  LEFT OUTER JOIN  nametable n ON p.personid=n.ownerid
WHERE  c.ownertype=0 AND +n.IsPrimary=1
;

INSERT INTO Pri
-- Citations for Names 
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, 0 AS EvtID, 'Alternate Name' AS Citer, '' AS EvtDate, 
  s.NAME AS Source, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
  c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds 
FROM  citationtable c 
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid
WHERE  c.ownertype=7 --AND +n.IsPrimary=0
;


-- CREATE secondary table from query of secondary or ATTACHed database, the one imported
--  from the Ancestry GEDCOM
DROP TABLE IF EXISTS Sec
;
CREATE TEMP TABLE Sec AS

-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, e.EventID AS EvtID, f.NAME AS Citer, e.Date AS EvtDate, 
  s.NAME AS Source, s.refnumber, s.actualtext AS SrcTxt, s.comments AS SrcComment, s.fields AS SrcFlds,
  c.refnumber, c.actualtext AS ActualText, c.comments, c.fields AS CitFlds
FROM  B.citationtable c
  LEFT OUTER JOIN B.sourcetable s ON c.sourceid = s.sourceid
  LEFT OUTER JOIN B.eventtable e ON c.ownerid = e.eventid
  LEFT OUTER JOIN B.persontable p ON e.ownerid = p.personid
  LEFT OUTER JOIN B.nametable n ON p.personid = n.ownerid
  LEFT OUTER JOIN B.facttypetable f ON e.eventtype = f.facttypeid
WHERE c.ownertype = 2 AND e.ownertype = 0 AND f.ownertype = 0 AND +n.IsPrimary = 1 
;

INSERT INTO Sec
-- all Personal citations for Individual
SELECT  c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname AS Surname, n.suffix AS Sfx, n.prefix AS Pfx, n.given AS Givens, n.birthyear AS Born, 
  n.deathyear AS Died, 0 AS EvtID, 'Personal' AS Citer, '' AS EvtDate,
  s.NAME AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, s.fields AS SrcFlds,
  c.refnumber AS CitREFN, c.actualtext AS CitTxt, c.comments AS CitComment, c.fields AS CitFlds
FROM  B.citationtable c 
  LEFT OUTER JOIN B.sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN B.persontable p ON c.ownerid=p.personid 
  LEFT OUTER JOIN  B.nametable n ON p.personid=n.ownerid
WHERE  c.ownertype=0 AND +n.IsPrimary=1
;

INSERT INTO Sec
-- Citations for Names 
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, 0 AS EvtID, 'Alternate Name' AS Citer, '' AS EvtDate, 
  s.NAME AS Source, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
  c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds 
FROM  B.citationtable c 
  LEFT OUTER JOIN B.sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN  B.nametable n ON n.nameid=c.ownerid
WHERE  c.ownertype=7 --AND +n.IsPrimary=0
;

UPDATE CitationTable SET Comments =
 ifnull((SELECT 
   Sec.Comments || CAST(x'0D0A' AS TEXT) || Pri.Comments
  
--SELECT Pri.CitationID, Sec.CitationID, Pri.Comments, Sec.Comments
FROM Pri, Sec
WHERE
 Pri.Surname LIKE Sec.Surname AND
 Pri.Suffix LIKE Sec.Suffix AND
 Pri.Prefix LIKE Sec.Prefix AND
 Pri.Given LIKE Sec.Given AND
 Pri.BirthYear = Sec.BirthYear AND
 Pri.DeathYear = Sec.DeathYear AND
 Pri.Citer LIKE Sec.Citer AND
 Pri.EvtDate LIKE Sec.EvtDate AND 
 Pri.Source LIKE Sec.Source AND
 Pri.SrcTxt LIKE Sec.SrcTxt AND
 Pri.SrcComment LIKE Sec.SrcComment AND
 --Pri.SrcFlds LIKE Sec.SrcFlds
 --Pri.ActualText LIKE Sec.ActualText
 REPLACE(REPLACE(Pri.ActualText, CAST(x'0D0A' AS TEXT), ''),' ','') LIKE REPLACE(Sec.ActualText, ' ', '')
 AND CitationTable.CitationID = Pri.CitationID
), Comments)
;

-- Convert Principal Name citations not visible in RM to Personal 

UPDATE CitationTable
SET
 OwnerType = 0,
 OwnerID =
 (
  SELECT n.OwnerID FROM NameTable N 
  WHERE CitationTable.OwnerID = N.NameID
  )
 WHERE 
  CitationTable.OwnerID 
  IN
  (SELECT C.OwnerID FROM CitationTable C, NameTable N
    WHERE C.OwnerID = n.NameID
    AND C.OwnerType = 7
    AND +N.IsPrimary
   )
  AND
  CitationTable.OwnerType = 7 
;  

-- End of script --------------------------