Query to Add Parents Events to RM Database

I have recently added a Parents “fact” to everyone in my RM database who has parents. These Parents events really are new rows added to the EventTable, even though parentage is already denoted in RM by entries in the ChildTable joined with entries in the FamilyTable. The reasons for the new events are two-fold. First, it provides a good place in the RM user interface to attach evidence of parentage. Second, it improves RM narrative reports. A person’s parents are listed explicitly as a fact in a narrative report. The evidence of parentage appears with its respective Parents fact in a narrative report. And a “birth of child” event appears in a narrative report in the parents’ timelines.

The parents fact looks something like the following in my narrative reports.

1. Elza Cordelia (Elzie) Peters12.
Birth: 21 Dec 1898, Anderson County, Tennessee.12
Parents: Alva Edward Peters and Sallie Jane Cole.

The birth of child role looks something like the following in my narrative reports.

1. Alva Edward Peters13.
Birth: 14 Oct 1870, Scarbrough, Anderson County, Tennessee.1,46
Marriage: 28 Nov 1893, Anderson County, Tennessee, age 23, to Sallie Jane Cole.1,3
Birth of Child: 21 Dec 1898, Elza Cordelia (Elzie) Peters.

In order to make this work, a Parents fact has to be added to RM’s FactTypeTable from the RM user interface via Lists->Fact Type List. A sentence template must be added for the Principle role for the Parents fact. In addition, a Parent role must be added to the Parents fact.

Because I use point form sentences, my two sentence templates for the Principle and Parent role for the Parents fact are as follows.

{cr}
<b>Parents:</b>< [Parent].>
 
{cr}
<b>Birth of Child: </b> <[Date:plain]><, [Person:Full]>.

where {cr} is an actual carriage return and line feed sequence entered into the template simply be depressing the Enter key on the PC’s keyboard. The [Parent] variable works in an amazing way in RM because it lists both persons with the Parent role (the father and mother) and separates their names with “and”. The parents names are stored in the Description field of the Parents fact, so the [Parent] variable could be replaced by the [Desc] variable, but the use of the [Parent] variable causes the parents names to included in the Index of Names in a narrative report.

I used the following query to create the Parents fact for everybody in the database who has parents, with the following exceptions. The query is designed not to add the Parents fact if it’s already there. The query is designed not to add the Parents fact for dummy people in my database. The query is designed not to add the Parents fact for people without Birth facts because it uses the Birth fact to create a date and sortdate for the Parents fact.

Jerry

-- Create a view of the EventTable
-- containing only Parents events.
-- This view will be used to prevent
-- creating a Parents event for
-- any individuals who already have
-- a Parents event. The main data needed
-- is the PersonID of the individuals
-- who already have a Parents event.
-- The PersonID manifests itself in
-- the EventTable as OwnerId.
 
 
DROP VIEW IF EXISTS ParentEventView;
CREATE TEMP VIEW ParentEventView AS
SELECT E.*
FROM EventTable AS E
       JOIN
     FactTypeTable AS FT  ON FT.FactTypeID = E.EventType
     WHERE FT.Name LIKE('Parents');
 
 
 
-- Create a view of the EventTable
-- containing only Birth events.
-- These events provides an initial list
-- of candidates of people to receive
-- a Parents event. Also, many of the
-- fields for newly created Parents
-- events will be replicated from the
-- corresponding Birth event.
 
DROP VIEW IF EXISTS BirthEventView;
CREATE TEMP VIEW BirthEventView AS
SELECT Birth.*
FROM EventTable AS Birth
       JOIN
     FactTypeTable AS FT  ON FT.FactTypeID = Birth.EventType
     WHERE FT.Name LIKE('Birth');
 
 
-- Create a view of Birth events for people
-- that don't have a Parent event. This is the
-- first filtering on the list of candidates
-- of people to receive a Parents event.
 
 
DROP VIEW IF EXISTS BirthWithoutParentsEventView;
CREATE TEMP VIEW BirthWithoutParentsEventView AS
SELECT Birth.*
FROM BirthEventView AS Birth
          LEFT JOIN
     ParentEventView AS Parent ON Parent.Ownerid = Birth.OwneriD
WHERE Parent.OwnerID IS NULL;
 
-- Create a view of the NameTable
-- containing only primary names.
-- This view will be a source of
-- the names needed for the newly
-- created Parents events and will
-- prevent any names other than the
-- primary ames from being loaded
-- into -- Parents events. This
-- view also prevents Parents events
-- from being created for any dummy
-- people, designated with an asterisk
-- in the name.
 
DROP VIEW IF EXISTS NameView;
CREATE TEMP VIEW NameView AS
SELECT N.*
FROM NameTable AS N
WHERE N.IsPrimary = 1
  AND N.Surname NOT LIKE('%*%')
  AND N.Given   NOT LIKE('%*%');
 
-- This view performs most of the
-- main processing for this project.
-- It determines which people actually
-- do have parents. Actually having
-- parents is based on being in the
-- ChildTable and has nothing to do
-- with whether a Parents event exists
-- or not. This list of people with parents
-- is then matched against people who are not
-- dummy people, who do have birth
-- events, and who don't already have
-- Parents events. This view also
-- develops the data that will need to
-- be stored in the newly created
-- Parents events.
 
 
DROP VIEW IF EXISTS ChildParentsView;
CREATE TEMP VIEW ChildParentsView AS
SELECT Child.ChildID,  Child.Given  || ' ' || Child.Surname  AS ChildName,
       Father.FatherID, Father.Given || ' ' || Father.Surname AS FatherName,
       Mother.MotherID, Mother.Given || ' ' || Mother.Surname AS MotherName,
       CASE
       WHEN Father.FatherID = 0 THEN Mother.Given || ' ' || Mother.Surname
       WHEN Mother.MotherID = 0 THEN Father.Given || ' ' || Father.Surname
       ELSE Father.Given || ' ' || Father.Surname || ' and ' || Mother.Given || ' ' || Mother.Surname
       END CombinedNames,
       B.DATE,
       B.SortDate,
       B.EditDate
 
FROM  (  SELECT C.RecID,
                C.ChildID,
                N.Given,
                N.Surname
         FROM   ChildTable AS C
                  JOIN
                NameView AS N ON N.Ownerid = C.ChildID
      ) AS Child
 
           JOIN
 
      (  SELECT C.RecID,
                FM.FatherID,
                N.Given,
                N.Surname
         FROM ChildTable AS C
                JOIN
              FamilyTable AS FM ON C.FamilyID =  FM.FamilyID
                LEFT JOIN
              NameView AS N ON N.OwnerID = FM.FatherID
      ) AS Father ON Father.RecID = Child.RecID
 
           JOIN
 
      (  SELECT C.RecID,
                FM.MotherID,
                N.Given,
                N.Surname
         FROM   ChildTable AS C
                  JOIN
                FamilyTable AS FM ON C.FamilyID =  FM.FamilyID
                  LEFT JOIN
             NameView AS N ON N.OwnerID = FM.MotherID
       ) AS Mother ON Mother.RecID = Father.RecID
 
            JOIN
 
       BirthWithoutParentsEventView AS B ON B.OwnerID = Child.ChildID;
 
-- The data from the ChildParentsView needs to be used
-- twice, once to load Parents events into the EventTable
-- and again to load roles into the WitnessTable. The
-- Parents events have to be loaded into the EventsTable
-- first, and one Insert statment in SQLite cannot load
-- data into two tables. However, loading Parents Events
-- into the EventTable will cause the ChildParentsView
-- not to produce the correct results the second time it
-- is used. Therefore, the results from applying the
-- ChildParentsView will be saved into a temporary table
-- called ChildParentsTable. As a table instead of a view,
-- the data in ChildParentsTable can be used two different
-- times and remain the same data both times it is used.
 
DROP TABLE IF EXISTS ChildParentsTable;
CREATE TEMP TABLE ChildParentsTable
(ChildID,ChildName,FatherID,FatherName,MotherID,Mothername,CombinedNames,DATE,SortDate,EditDate);
INSERT INTO ChildParentsTable
(ChildID,ChildName,FatherID,FatherName,MotherID,Mothername,CombinedNames,DATE,SortDate,EditDate)
SELECT CP.*
FROM ChildParentsView AS CP;
 
 
 
-- Load new Parents facts into the EventTable.
 
INSERT OR ROLLBACK INTO EventTable
SELECT NULL AS EventID
    ,(SELECT FT.FactTypeID FROM FactTypeTable AS FT WHERE FT.Name LIKE('Parents') ) AS EventType
    ,0 AS OwnerType
    ,ChildID AS OwnerID
    ,0 AS FamilyID
    ,0 AS PlaceID
    ,0 AS SiteID
    ,DATE AS DATE
    ,SortDate AS SortDate
    ,0 AS IsPrimary
    ,0 AS IsPrivate
    ,0 AS Proof
    ,0 AS STATUS
    ,EditDate AS EditDate
    ,CAST('' AS TEXT) AS Sentence
    ,CAST(CombinedNames AS TEXT) AS Details
    ,CAST('' AS TEXT) AS Note
 FROM ChildParentsTable;
 
 
 
 --   ===========================================================================================================
 --     The following is adapted from Tom Holden to rank same date sort dates.
 --
 --     There are a number of changes by Jerry Bryan that are specific to his
 --     use case.
 --
 --        * The list of fact types which are supported is greatly increased.
 --        * Sort dates including ranked sort dates (date-n) are ranked and even re-ranked
 --          whether or not they match the date from from the fact itself, provided only
 --          that the sort dates in question match each other.
 --             . This allows same "ABT" dates to be ranked.
 --             . This allows same "year only" dates to be ranked if the sort date is
 --               1 July of the year.
 --             . This allows same "year and month only" dates to be ranked if the
 --             . sort date is the 15th of the month.
 --
 --   ===========================================================================================================
 
/* SortDateSameDayOrderCustom.sql
   2011-12-20 ve3meo
   Alters SortDates of any set of Fact types
   to a natural order when any pair or more occur on the same date.
   Could be extended to order other facts also. SortDates are effectively assigned
   (by arithmetical offsets) an absolute suffix -1, -2, ... related to the rank of the FactType.
   Affects only those events whose SortDates correspond to the Fact Date, as computed
   by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all
   Event dates are handled, e.g. "Bef 1960".
*/
DROP TABLE IF EXISTS TmpFactOrder
;
CREATE TEMP TABLE IF NOT EXISTS TmpFactOrder
(Rank INTEGER PRIMARY KEY, FactName TEXT)
;
 
/* list of Fact Names, standard and custom, to be sorted, in rank order.
   Revise the list to suit your needs */
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Birth');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Parents');             -- added by JB
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Birth Certificate');   -- added by JB
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Christen');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Baptism');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Death');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Obituary');            -- moved up by JB
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Death Certificate');   -- added by JB
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Funeral');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Cremation');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial Inscription');  -- Added by JB
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial GPS');          -- Added by JB
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Memorial');
/* revise SortDates */
UPDATE EventTable
SET SortDate = SortDate
  -6692012023  -- this offset goes to Date-1 if the event is a ranked event
  *( (
      SELECT Rank
      FROM TmpFactOrder,
           FactTypeTable
      WHERE FactName LIKE Name
      AND FactTypeID = EventType
      )>0
    )
  +1048576  -- this offset adds steps of 1 to Date-1 multiplied by (rank-1)
  *( (
      SELECT Rank
      FROM TmpFactOrder,
           FactTypeTable
      WHERE FactName LIKE Name
      AND FactTypeID = EventType
      )-1
    ) -- maps the FactType to its order
WHERE EventID
IN (SELECT EventID FROM EventTable
    INNER JOIN
    (SELECT -- matching dates
     SortDate, OwnerID, COUNT()-1 AS Matches
     FROM EventTable
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID
     WHERE EventTable.OwnerType = 0
     AND Name IN (SELECT FactName FROM TmpFactOrder)
/*     AND    -- commented out by JB to handle sort dates not matching fact date when sort dates are equal to each other.
     SortDate =   -- equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it)
       (CASE
        WHEN DATE LIKE '.%'
        THEN 1
        ELSE Substr(DATE,3,5) END
        +10000
        )*562949953421312
        + Substr(DATE,8,2)*35184372088832
        + Substr(DATE,10,2)*549755813888
        + 17178820620 */
      GROUP BY SortDate, OwnerID, EventTable.OwnerType
     )
     USING (OwnerID, SortDate)
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID
     WHERE Matches
     AND EventTable.OwnerType = 0
     AND Name IN (SELECT FactName FROM TmpFactOrder)
    )
;
 
 
-- We now add Parent roles to each Parents event.
-- It is most convenient to add the Parent role
-- for the father in one INSERT and to add the
-- Parent role for the mother as a separate insert.
-- So we first create a view that joins the
-- Parent events with the ChildTable and FamilyTable
-- to get a list of Parent events and their
-- respective fathers and mothers.
 
DROP VIEW IF EXISTS ParentRoleView;
CREATE TEMP VIEW ParentRoleView AS
SELECT PEV.*,
       FM.FatherID, FM.MotherID
FROM ParentEventView AS PEV
        JOIN
     ChildTable AS CT ON CT.ChildID = PEV.OwnerID
        JOIN
     FamilyTable AS FM ON CT.FamilyID =  FM.FamilyID;
 
 
-- Load Parents roles for fathers into the WitnessTable
 
INSERT OR ROLLBACK INTO WitnessTable
SELECT NULL AS WitnessID
    ,PE.EventID AS EventID
    ,Parents.FatherID AS PersonID
    ,0       AS WitnessOrder
    ,(SELECT R.RoleID FROM RoleTable AS R WHERE R.RoleName LIKE ('Parent') ) AS ROLE
    ,CAST('' AS TEXT) AS Sentence
    ,CAST('' AS TEXT) AS Note
    ,CAST('' AS TEXT) AS Given
    ,CAST('' AS TEXT) AS Surname
    ,CAST('' AS TEXT) AS Prefix
    ,CAST('' AS TEXT) AS Suffix
 FROM ChildParentsTable AS Parents
         JOIN
      ParentEventView AS PE ON PE.OwnerID =  Parents.ChildID AND PE.Details = Parents.CombinedNames;
 
-- Load Parents roles for mothers into the WitnessTable
 
INSERT OR ROLLBACK INTO WitnessTable
SELECT NULL AS WitnessID
    ,PE.EventID AS EventID
    ,Parents.MotherID AS PersonID
    ,0       AS WitnessOrder
    ,(SELECT R.RoleID FROM RoleTable AS R WHERE R.RoleName LIKE ('Parent') ) AS ROLE
    ,CAST('' AS TEXT) AS Sentence
    ,CAST('' AS TEXT) AS Note
    ,CAST('' AS TEXT) AS Given
    ,CAST('' AS TEXT) AS Surname
    ,CAST('' AS TEXT) AS Prefix
    ,CAST('' AS TEXT) AS Suffix
 FROM ChildParentsTable AS Parents
         JOIN
      ParentEventView AS PE ON PE.OwnerID =  Parents.ChildID AND PE.Details = Parents.CombinedNames;
;
 

5 Replies to “Query to Add Parents Events to RM Database”

  1. Hi, I am a bit of a newbie to SQL. I am having a problem of missing sentence when running the Query to Add Parents Events to RM Database. I have created the Parents fact in RM, added a sentence template in the Principal and Parent roles. I run the SQL against the database and the Parents fact is added to the people who have birth dates. The fact is then shared to the two parents but the share fact has no sentence template in the edit person view. What have I dine wrong?

    Chris

  2. In RM, go to Lists > Fact Type List and select the Parents fact type you have manually added there. Then select and edit the Parent role for that fact type. Have you entered anything into the sentence template? For example, as Jerry had used in his own case:
    {cr}
    Birth of Child: < [Date:plain]>< , [Person:Full]>.

    Tom

  3. You said you added the requisite sentence templates, but are you sure? On the RM side of the house (not in SQLite) you have to add the Parents fact and Parent role (I think the Principal role is added automatically – if not, add it your self). Having done so, add the sentence templates. You can go into the FactTypeList to be sure the sentence templates are there.

    Here’s one other thing to look at. In my own database, I have a Birth_of_Child role now instead of a Parent role. You can call the fact to be shared anything you like and you can call the role to be shared any thing you like. Be sure the fact and roles match the version of the script you are running. Look for FT.Name LIKE(‘Parents’) and R.RoleName LIKE (‘Parent’) in my script to be sure the names match up with what you are doing in your own RM database.

    Going by memory now, but I also think you need to run the RM database tools immediately after running this script in SQLite. I’m not sure of all the conditions where running the RM database tools is necessary after running an SQLite update script, but this may be one of them.

    Jerry

  4. Thank you for your suggestions, as I could not include images I did open this same question in the forum to include some images of the problem which covered your points.

    Although I do not know how to write Database Queries I do know enough about databases to be dangerous. That said I have managed to find the problem.

    I edited my Parents fact to include an eye-catcher in the sentence field then using SQLiteSpy to view the contents of the RoleTable I found my Parents and Parent sentences. The problem is that I have two Parent roles in the table!

    RoleID 59 Role Parent Event Type 1 sentence
    RoleID 60 Role Parents Event Type 1 sentence ***** This is the Principal Role****
    RoleID 59 Role Parent Event Type 1013 sentence ***** This is the Parent Role****

    Using SQLiteSpy to edit the sentence cell for RoleID 59, then run the query, my sentence field in the RM database gets the sentence field inserted.

    I have now gone back to my live database and found that I have RoleID 59 Role Parent, Event Type 1 already in my database. There is no Parent(s) fact in the database so assuming it must be in another fact. Is there a quick way to find out ?

    Thanks

    Chris

  5. Even without writing any SQL yourself, you can puzzle out a lot of problems just by double clicking a table name in SQLiteSpy and looking at the table. To help things along, you can also click a column name at the top of the display to sort by that column.

    That being said, the Parent role that is RoleID 59 and the Parents role this is RoleID 60 are both associated with EventType 1. EventType 1 is RM’s built-in Birth fact. Those are rational roles to associate with RM’s built-in Birth fact, but that’s not the way my query is set up to work. Rather, it’s set up to create a Parents fact and then to share that fact with the parents rather than sharing the Birth fact with the parents.

    I think that in your test database, you need to remove the roles associated with the Birth fact and work only with the Parents fact and it’s role of Parent. Or do like me and change the name of the role to Birth_of_Parents – if so, you have to change role name both in the query and in RM. Remember that the role name doesn’t really have any particular meaning to RM. It’s just a character string that’s matched up. What really matters is the content of the sentence associated with the role.

    I think this particular use case of sharing RM’s facts is a little confusing, or at least it confuses me and I’m the one who developed the process. If all you wanted to do was to list the birth of their children in the parents timeline, you could simply share the built-in Birth fact with the parents and set up the role sentence so that it prints with the parents. The birth sentence already prints for the child. If all you wanted to do was to list the parents of the children in the children’s timeline (with notes and sources for the parents), you could insert the Parents fact, share the fact with the Parents to make the parents available as variables in the Parents fact sentence, define a sentence for the Parents fact which includes the parents, and leave the role sentence null. But I’m doing both functions with the Parents fact and it’s role, which is where things become confusing. Or as I said, it confuses me.

Leave a Reply