-- FactType - Convert Census_shared to Year_Census.sql
-- 2014-11-20 Tom Holden ve3meo
-- 2014-11-21 adapted from the unshared Census version to support shared events
/*
Converts standard Census and Census (family) events to custom 
fact types "yyyy Census" and "yyyy Census (family)". Then any 
one or more of these yyyy Census facts can be displayed in the
People View with advantages over the standard Census fact which
only shows one census event per person. With multiple yyyy Census 
columns, holes in the census pattern and migration can be easily
seen. Note that People View only shows the Census fields for a
Principal, not for any persons with non-Principal roles.

Requires a SQLite Manager with an extension for a RMNOCASE 
collation sequence.

Usage:
1. Execute once and once only if you intend this to be a temporary change.
2. To revert said temporary change to the original, a table is stored in
   the database, named "xCensusTypeTranspose"; the script in comments 
   at the end of this script is then selected and executed and the special
   table is dropped from the database.
3. If, after step 1, you add more standard events and re-execute, the 
   reversion script can only return to the state prior to the last conversion.
4. A separate reversion script may be developed to convert all events of 
   fact type name like "yyyy Census%" to standard Census types.
*/


-- compile a view of the events having the standard Census fact type
DROP VIEW IF EXISTS CensusEvents
;

CREATE TEMP VIEW CensusEvents
AS
SELECT EventID, EventType, OwnerType, Date, SUBSTR(Date,4,4) AS Year 
FROM EventTable 
WHERE EventType IN
 (SELECT FactTypeID 
  FROM FactTypeTable 
  WHERE Name LIKE 'Census%'
  )
ORDER BY Year, OwnerType
;

-- compile a view of the parameters for new custom fact types
-- "yyyy Census..."
DROP VIEW IF EXISTS CensusTypes
;

CREATE TEMP VIEW CensusTypes
AS
SELECT DISTINCT Year, OwnerType FROM CensusEvents
;

-- create the new custom fact types "yyyy Census" and "yyyy Census (family)"
INSERT INTO FactTypeTable
  SELECT
    Null AS FactTypeID
    , OwnerType
    , Year || ' ' || Name AS Name
    , Year || ' ' || Abbrev AS Abbrev
    , GedcomTag
    , UseValue
    , UseDate
    , UsePlace
    , Sentence
    , Flags
  FROM FactTypeTable FT
  INNER JOIN CensusTypes
  USING (OwnerType)
  WHERE Name LIKE 'Census%'
;

-- compile a cross-reference for census type events relating
-- the current EventType to the new EventType
DROP TABLE IF EXISTS xCensusTypeTranspose
;

CREATE TABLE xCensusTypeTranspose
AS
SELECT EventID, EventType AS OldEventType, FactTypeID AS NewEventType 
FROM CensusEvents
INNER JOIN FactTypeTable
USING (OwnerType)
WHERE Name LIKE Year || ' ' || 'Census%'
ORDER BY EventID
;


-- compile a view cross-referencing the new Census type to the standard
DROP VIEW IF EXISTS OldNewCensusType
;
CREATE TEMP VIEW OldNewCensusType
AS
SELECT DISTINCT NewEventType, OldEventType FROM xCensusTypeTranspose ORDER BY NewEventType
;

-- create roles for the new census types from the old
INSERT INTO RoleTable
SELECT Null AS RoleID
       , RoleName
       , NewEventType AS EventType
       , RoleType
       , Sentence
FROM OldNewCensusType O
INNER JOIN RoleTable R
WHERE R.EventType = O.OldEventType
;

-- compile a cross-reference for RoleIDs
-- between the standard and new Census types
DROP TABLE IF EXISTS xCensusRoleTranspose
;
CREATE TABLE xCensusRoleTranspose
AS
SELECT Old.RoleID, Old.EventType, New.RoleID, New.EventType
FROM 
  (SELECT RoleID, EventType, RoleName || Sentence AS Role FROM RoleTable WHERE EventType IN (SELECT DISTINCT OldEventType FROM OldNewCensusType ORDER BY OldEventType)) AS Old
INNER JOIN
  (SELECT RoleID, EventType, RoleName || Sentence AS Role FROM RoleTable WHERE EventType IN (SELECT NewEventType FROM OldNewCensusType)) AS New
USING (Role)
;

-- compile a view of the Witness records to be changed
DROP TABLE IF EXISTS xWitnessRoleTranspose
;

CREATE TABLE xWitnessRoleTranspose
AS
SELECT W.WitnessID, W.Role AS OldRoleID, xCRT."New.RoleID" AS NewRoleID
FROM WitnessTable W
INNER JOIN xCensusRoleTranspose xCRT ON W.Role = xCRT."Old.RoleID"
INNER JOIN xCensusTypeTranspose xCTT USING(EventID)
WHERE xCTT.NewEventType = xCRT."New.EventType"
;



-- Reassign Census Events to the Year-Census types by 
-- replacing the current EventType with the new EventType
UPDATE EventTable
  SET EventType = (SELECT NewEventType FROM xCensusTypeTranspose xC WHERE EventTable.EventID = xC.EventID)
WHERE EventID IN (SELECT EventID FROM xCensusTypeTranspose)
;

-- Reassign Witness Roles to those of the Year-Census type
UPDATE WitnessTable
  SET Role = (SELECT NewRoleID FROM xWitnessRoleTranspose xWRT WHERE WitnessTable.WitnessID = xWRT.WitnessID)
WHERE WitnessID IN (SELECT WitnessID FROM xWitnessRoleTranspose)
;



/*
The following commented out script is for reverting the database 
to the state it was in prior to the last execution of the foregoing script,
except for changes made since. For example, changes to yyyy Census roles 
are lost as they revert to the those of the standard Census fact types.

It depends on the tables "xCensusTypeTranspose", "xCensusRoleTranspose" 
created by the conversion else it cannot revert.

Select (highlight) the script from START to END and use your SQLite manager's
controls to execute just the highlighted statements.    

-- START Reversion
-- revert events to standard Census types
UPDATE EventTable 
  SET EventType = (SELECT OldEventType FROM xCensusTypeTranspose xC WHERE EventTable.EventID = xC.EventID)
WHERE EventID IN (SELECT EventID FROM xCensusTypeTranspose)

-- revert witnesses or sharers roles to those of the standard Census types
UPDATE WitnessTable
  SET Role = (SELECT [Old.RoleID] FROM xCensusRoleTranspose xCRT WHERE WitnessTable.Role = xCRT.[New.RoleID])
WHERE Role IN (SELECT DISTINCT [New.RoleID] FROM xCensusRoleTranspose ORDER BY 1)
;

-- compile a view of all fact types in use
DROP VIEW IF EXISTS FactTypesUsed
;
CREATE TEMP VIEW FactTypesUsed
AS
SELECT DISTINCT EventType FROM EventTable
ORDER BY 1
;


-- delete the unused "yyyy Census..." fact types
DELETE FROM FactTypeTable
WHERE FactTypeID IN
(
 SELECT DISTINCT NewEventType
 FROM xCensusTypeTranspose
 ORDER BY 1
 )
AND FactTypeID NOT IN
(
 SELECT EventType FROM FactTypesUsed
 )
;

-- Delete the Roles for deleted FactTypes
DELETE FROM RoleTable
WHERE EventType NOT IN
  (SELECT FactTypeID FROM FactTypeTable ORDER BY 1)
;

-- Get rid of the last trace of the conversion
DROP TABLE IF EXISTS xCensusTypeTranspose
;
DROP TABLE IF EXISTS xCensusRoleTranspose
;
DROP TABLE IF EXISTS xWitnessRoleTranspose
;

-- END Reversion

*/