-- Events-CitationsMissing-UsingViews.sql

/* 2017-04-17 Tom Holden ve3meo
Original scripts by Jerry Bryan, assembled from 
http://sqlitetoolsforrootsmagic.wikispaces.com/A+Sample+Query+Created+with+Views

Lists RIN and Fact for which no citation is tagged.
*/

DROP VIEW IF EXISTS PersonTableView;
CREATE TEMP VIEW PersonTableView AS
SELECT P.*
FROM PersonTable AS P
-- WHERE P.Color = 1
;

-- NameTableView example.-- Create a view of the FactTypeTable filtered to exclude dummy facts.
DROP VIEW IF EXISTS NameTableView;
CREATE TEMP VIEW NameTableView AS
SELECT N.*
FROM NameTable AS N
        INNER JOIN
     PersonTableView AS P ON N.OwnerID = P.PersonID;

-- FamilyTableView example.
--
-- The FamilyTable is filtered to include only individuals who are included in the filtered PersonTable.
-- The DISTINCT parameter is needed to avoid selecting the same row of the FamilyTable twice if
-- both the Father and the Mother are in PersonTableView. The VIEW is set up to select the family
-- if either or both spouses are in the PersonTableView. If you want the VIEW to select the family
-- only if both spouses are in the PersonTableView, change the OR to AND.
DROP VIEW IF EXISTS FamilyTableView;
CREATE TEMP VIEW FamilyTableView AS
SELECT DISTINCT F.*
FROM FamilyTable AS F
        INNER JOIN
     PersonTableView AS P ON (F.FatherID = P.PersonID OR F.MotherID = P.PersonID);
     

-- FactTypeTableView example.
--
-- I use a lot of dummy facts - facts that will never appear in any reports but
-- which record "to do" type of information, research log type of information, etc.
-- Probably nobody else than me does this, but it still provides a good example.
-- The VIEW omits these dummy facts. I use the standard Reference Number fact
-- as a dummy fact (I'm phasing out this usage), and facts whose name incudes an
-- asterisk as dummy facts. This query omits such fact types from the VIEW.
-- If this type of filtering is not needed, you could just reference the FactTypeTable
-- directly without creating a VIEW or else have a FactTypeView that includes
-- the entire FactTypeTable.
--
DROP VIEW IF EXISTS FactTypeTableView;
CREATE TEMP VIEW FactTypeTableView AS
SELECT F.*
FROM FactTypeTable AS F
WHERE (F.Abbrev NOT LIKE '%*%' AND F.Abbrev NOT LIKE 'Ref%');

-- EventTableView example.
--
-- This VIEW simply applies the concept of dummy facts from the FactTypeTable to the EventTable.
-- It also adds one new variable so that that the EventTableView has the name of fact type available
-- to display for each event, a piece of data that is not available in the EventTable itself.
-- I wonder about the wisdom if this approach. We could instead leave out the name of the fact type
-- for now and bring it in later in the query. That way, the EventTableView would be a pure
-- representation of what is in the actual EventTable.
--
DROP VIEW IF EXISTS EventTableView;
CREATE TEMP VIEW EventTableView AS
SELECT E.*,
       F.Abbrev
FROM EventTable AS E
       INNER JOIN
     FactTypeTableView AS F ON E.EventType = F.FactTypeID;
     
-- Two EventTableView's - example to deal with individual facts vs. family facts.
DROP VIEW IF EXISTS IndividualEventView;
CREATE TEMP VIEW IndividualEventView AS
SELECT E.*
FROM EventTableView AS E
        INNER JOIN
     PersonTableView AS P ON E.OwnerID = P.PersonID
WHERE E.OwnerType = 0;
--
DROP VIEW IF EXISTS FamilyEventView;
CREATE TEMP VIEW FamilyEventView AS
SELECT E.*
FROM EventTableView AS E
        INNER JOIN
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
WHERE E.OwnerType = 1;

-- CitationViewIndividualEvents example.
--
-- We use a LEFT JOIN from the individuals to the citations to identify events
-- without citations, and the test for NULL effectively takes place after
-- the JOIN is complete.
--
-- First, we create a VIEW of the CitationTable filtered on OwnerType = 2 (citations for events)
-- The VIEW will be used several times and has the effect of isolating the test for citations
-- for events to being made at only a single place in the overall query.
--
DROP VIEW IF EXISTS CitationViewEvent;
CREATE TEMP VIEW CitationViewEvent AS
SELECT C.*
FROM CitationTable AS C
WHERE C.OwnerType = 2;
--
-- We now create the three queries for individual events, family events for the father, and family events for the mother.
-- The JOIN structure for the family event queries is slightly more complicated than is the JOIN structure for the
-- individual event query. The extra complication is because the RIN number comes from a different table than the
-- name of the fact when a family fact is involved.
--
DROP VIEW IF EXISTS CitationViewIndividualEvents;
CREATE TEMP VIEW CitationViewIndividualEvents AS   -- query for individual events
SELECT E.Ownerid AS RIN,
       E.Abbrev
FROM IndividualEventView AS E
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID
WHERE C.OwnerID IS NULL;
 
DROP VIEW IF EXISTS FatherFactCitations;
CREATE TEMP VIEW FatherFactCitations AS
SELECT F.FatherID AS RIN,
       E.Abbrev
FROM FamilyEventView AS E
       INNER JOIN
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID
WHERE C.OwnerID IS NULL;
 
DROP VIEW IF EXISTS MotherFactCitations;
CREATE TEMP VIEW MotherFactCitations AS
SELECT F.MotherID AS RIN,
       E.Abbrev
FROM FamilyEventView AS E
       INNER JOIN
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID
WHERE C.OwnerID IS NULL;

SELECT * FROM CitationViewIndividualEvents
   UNION
SELECT * FROM FatherFactCitations
   UNION
SELECT * FROM MotherFactCitations;