-- Citations, Invisible - Convert to Personals + Flagged.sql
/*
2012-09-10 by Tom Holden
rev 2015-12-19 to make reversible by setting Flags to 7
rev 2015-12-24 Flags now set bitwise, 1st bit to 1, as agreed by RootsMagician

Citations with CitationTable.OwnerType=7 that point to a Primary Name are invisible in 
the Edit Person and RM Explorer screens in version 5.0.4.1. They are also suppressed 
in Individual Summary and at least one Narrative Descendant report and probably others. 
Nonetheless, they are shown in the Source List report and survive Drag and Drop transfers. 

RM4 and 5 generate these citations on import of GEDCOM from GenBox and Ancestry.com 
and, possibly, from others. 

This query converts all such citations to Personal citatations, making them visible 
and useful again. It does so by changing the OwnerType to 0 and the OwnerID to the PersonID
that 'owns' the person's Primary Name. 
*/

BEGIN ;
UPDATE OR ROLLBACK CitationTable
SET OwnerID = 
(
 SELECT NameTable.OwnerID 
 FROM CitationTable AS Old
 LEFT JOIN NameTable
 ON Old.OwnerID = NameTable.NameID
 WHERE Old.CitationID = CitationTable.CitationID
),
OwnerType = 0,
Flags = Flags | 1  -- set 1st bit to 1 
WHERE CitationID IN
(
 SELECT CitationID
 FROM CitationTable
 LEFT JOIN NameTable
 ON CitationTable.OwnerID = NameTable.NameID
 WHERE CitationTable.OwnerType = 7 AND +NameTable.IsPrimary
)
;
COMMIT ;