-- Citations, Invisible - List.sql
/*
2012-09-10 by Tom Holden

Lists citations for the Primary Name of a person which are invisible on 
key user screens and reports. RM4 and 5 generate these citations on import 
of GEDCOM from GenBox and Ancestry.com and, possibly, from others. 
 
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. 

The list produced by this query contains just the key data with which one can review
a database through RootsMagic to confirm the invisible citations and to find the 
corresponding records using SQLite to inspect the database tables. The listed data are:
CitationID - the record in the CitationTable
RIN - the PersonID in the PersonTable and the OwnerID in the NameTable; use for
  fast lookup in RootsMagic Explorer
Surname, Given names - the names from the NameTable pointed to by the OwnerID from 
  the CitationTable
Source Name - the user defined name assigned to the cited source, as seen in the 
  Source List report
Details - the field values for the Source Details for the citation are squeezed 
  between the XML tags and are what are listed under the citation in the 
  Source List report  
*/


SELECT CitationID, NameTable.OwnerID AS RIN, Surname, Given, 
SourceTable.Name AS "Source Name", CAST(CitationTable.Fields AS TEXT) AS 
Details
FROM CitationTable
LEFT JOIN NameTable
ON CitationTable.OwnerID = NameTable.NameID
LEFT JOIN SourceTable
ON CitationTable.SourceID = SourceTable.SourceID
WHERE CitationTable.OwnerType = 7 AND +NameTable.IsPrimary
;