-- FactType - Convert Census_unshared to Year_Census.sql
-- 2014-11-20 Tom Holden ve3meo
-- 2014-11-21 renamed to show it does not 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.

NB THIS VERSION DOES NOT SUPPORT SHARED EVENTS. SEE ITS SIBLING
SCRIPT IF YOU HAVE SOME SHARED CENSUS FACTS.

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
;

-- Now reassign Census Events to the Year-Census types by 
-- replace 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)
;

/*
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.
It depends on the table "xCensusTypeTranspose" created by the conversion.
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)
;

-- 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
 )
;

-- Get rid of the last trace of the conversion
DROP TABLE IF EXISTS xCensusTypeTranspose
;
-- END Reversion

*/