Recent Changes

Yesterday

Friday, January 19

  1. page Facts - Split Shared to Individual edited ... Use shared events and after export apply MarkVS' GEDCOM splitting function integrated in his G…
    ...
    Use shared events and after export apply MarkVS' GEDCOM splitting function integrated in his GED Image Tracer. As of writing, it does a basic split but with flaws as a consequence of the inherent challenges that we will get into below.
    Use shared events and after export import into David Knight's iOS app GedView and save its GEDCOM for use with other software. As of ver 3.4.1: "Basic support added for shared events / facts as used by RootsMagic. GedView will load the event/fact and copy it to the individuals who share it." I have yet to examine the results and it does require the iOS device.
    Applications that import RootsMagic Shared Events
    While most other applications have no support for shared events, a small number do preserve the data when importing from RootsMagic:
    Application
    Converts
    Preserves
    Description
    GedSite
    X
    Generates website from GEDCOM
    GedView
    X
    GEDCOM editor/viewer for iOS
    Legacy Family Tree
    X
    Full family tree/genealogy software
    Converts: converts shared events into individual ones
    Preserves: converts RM shared events into application's version of shared events.

    The Challenges in Converting Shared to Individual Events
    The unique data for the event itself for the Principal role is stored in EventTable and can remain unchanged.
    (view changes)
    5:27 pm

Thursday, January 18

  1. msg Fix for marriages imported thru Ancestry TreeShare - invalid IDs cause "[Couple] were married..." sentences message posted Fix for marriages imported thru Ancestry TreeShare - invalid IDs cause "[Couple] were married.… I have 34 marriage records in my RM file with a sentence that begins with "[couple] married on…
    Fix for marriages imported thru Ancestry TreeShare - invalid IDs cause "[Couple] were married..." sentences
    I have 34 marriage records in my RM file with a sentence that begins with "[couple] married on..." instead of the spouse's names. All affected marriages were from records added in Ancestry and pulled into RM. I've narrowed down the problem in the data: there are marriage events for each of the spouses, but the OwnerType is 0, not 1, and the FamilyID is 0 instead of the FamilyID for the couple.

    Below are the changes I developed. I've verified the data afterwards, by data dumps and printing the narrative reports. I had to do this as a multi-step process; there may be a more way to code the SQL but I'm sure this worked.


    -- test table development below
    -- create temporary table to hold problem records

    DROP TABLE IF EXISTS TesttableEventTable ;

    CREATE TEMP TABLE TesttableEventTable AS
    Select *
    FROM EventTable
    where EventType = 300
    and OwnerType <> 1
    Order by Date;

    -- this SQL Identifies the two rows where individuals also have a valid marriage record (these records to be deleted)
    -- and stores the data into a temporary testing table:
    DROP TABLE IF EXISTS TestEventsToDELETE
    ;
    CREATE TEMP TABLE TestEventsToDELETE AS

    select * from TestTableEventTable T
    Where T.OwnerID in
    (Select F.FatherID from FamilyTable F
    INNER JOIN EventTable E on F.FamilyID = E.OwnerID
    AND E.OwnerType = 1
    And E.EventType = 300
    )
    UNION
    select * from TestTableEventTable T
    Where T.OwnerID in
    (Select F.MotherID from FamilyTable F
    INNER JOIN EventTable E on F.FamilyID = E.OwnerID
    AND E.OwnerType = 1
    And E.EventType = 300
    )

    ;
    -- END this SQL stores the above data into a temporary testing table


    -- this SQL stores the IDs to be updated into a temporary testing table:
    DROP TABLE IF EXISTS TestEventsToUPDATE
    ;
    CREATE TEMP TABLE TestEventsToUPDATE AS

    Select EventID
    FROM TesttableEventTable
    where (EventType = 300
    and OwnerType <> 1)
    AND EventID not in (Select EventID from TestEventsToDELETE)
    GROUP BY Date
    HAVING ( COUNT (Date) > 1)
    ORDER BY Date;
    -- END this SQL stores the IDs to be updated into a temporary testing table


    -- this SQL creates a second test table to run tests against and compare to first:
    DROP TABLE IF EXISTS TesttableEventTable2
    ;
    CREATE TEMP TABLE TesttableEventTable2 AS
    Select *
    FROM EventTable
    where EventType = 300
    and OwnerType <> 1
    Order by Date;
    -- END this SQL creates a second test table to run tests against and compare to first


    DROP table if exists TestEventsChangeData;

    CREATE TEMP TABLE TestEventsChangeData
    AS
    select TETU.EventID,
    TTET.OwnerID,
    F.FamilyID
    from TestEventsToUPDATE TETU, TestTableEventTable TTET, FamilyTable F
    where TETU.EventID = TTET.EventID
    And ((F.FatherID = TTET.OwnerID) OR (F.MotherID = TTET.OwnerID))
    ;


    -- These three steps, run in order, solve the problem:


    BEGIN TRANSACTION;

    -- set OwnerID in EventTable to Family ID from FamilyTable
    UPDATE EventTable

    SET
    OwnerID = (SELECT TestEventsChangeData.FamilyID
    FROM TestEventsChangeData
    WHERE TestEventsChangeData.EventID = EventTable.EventID)
    WHERE
    EXISTS (
    SELECT *
    FROM TestEventsChangeData
    WHERE TestEventsChangeData.EventID = EventTable.EventID
    )
    ;


    -- set OwnerType in EventTable to 1 instead of 0
    UPDATE EventTable
    SET
    OwnerType = (SELECT 1
    FROM TestEventsChangeData
    WHERE TestEventsChangeData.EventID = EventTable.EventID)
    WHERE
    EXISTS (
    SELECT *
    FROM TestEventsChangeData
    WHERE TestEventsChangeData.EventID = EventTable.EventID
    )
    ;

    -- Delete remaining invalid records
    Delete from EventTable
    where EventType = 300
    and OwnerType <> 1
    ;

    COMMIT;
    1:12 pm

Sunday, January 14

Saturday, January 13

Friday, January 5

Thursday, December 28

  1. msg SQLite behaviour changed in Jan 2017 message posted SQLite behaviour changed in Jan 2017 The query still works in SQLiteSpy which was compiled with SQLite 3.13.0 but throws this error in m…
    SQLite behaviour changed in Jan 2017
    The query still works in SQLiteSpy which was compiled with SQLite 3.13.0 but throws this error in my almost current version of SQLite Expert Personal compiled with SQLite 3.19.3. A Google search on the error message returns this ticket https://www.sqlite.org/src/info/25e335f802dd which reports the issue of SQLite3 behaving differently from PostGres which throws the error. It was pretty quickly changed. However, it may take some noodling to understand how to go about changing the above script so that it won't throw the error in these later SQLite managers while supporting the intended inclusiveness by using the LEFT JOINs.
    1:20 pm

Wednesday, December 27

  1. page AllCitations.sql Error edited I am running the following query in SQLite Expert Personal 5.2(x86): -- AllCitations.sql -- 2010…
    I am running the following query in SQLite Expert Personal 5.2(x86):
    -- AllCitations.sql
    -- 2010-01-28 ve3meo
    -- Lists citations for each person
    -- 2010-01-29 rev by ve3meo to use LEFT OUTER JOINS to include the most orphaned citations
    -- Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
    -- and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
    -- reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
    -- Requires a temp table because of speed degradation when incorporated in main selects;
    -- filtering can be done on screen in SQLiteDeveloper.
    -- 2010-01-30 rev by ve3meo. Dropped UNIQUE from INDEX because other SQLite managers objected.
    -- Put QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
    -- managers merely say BLOB.
    -- To Do - maybe add eventtable.SortDate as a sorting criterion
    -- 2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name
    --
    -- BEGIN
    -- all Personal citations for Individual
    DROP TABLE IF EXISTS tmpCitations;
    CREATE TEMP TABLE tmpCitations AS
    SELECT c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born,
    n.deathyear AS Died, 'Personal' AS Citer, s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, c.refnumber AS CitREFN,
    QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    WHERE c.ownertype=0 AND +n.IsPrimary=1
    ;
    INSERT INTO tmpCitations
    -- all Fact citations for Individual
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear,
    n.deathyear, f.NAME AS Citer, s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
    LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
    ;
    INSERT INTO tmpCitations
    -- all Spouse citations for Father|Husband|Partner 1
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, 'Spouse' as 'Citer', s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
    LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    -- LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
    -- LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=1 -- AND e.ownertype=1 AND f.ownertype=1
    AND +n.IsPrimary=1
    ;
    INSERT INTO tmpCitations
    -- all Couple Event citations for Father|Husband|Partner 1
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, f.NAME, s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN familytable fm ON c.ownerid=e.EventID
    LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
    LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
    LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
    LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
    WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
    ;
    INSERT INTO tmpCitations
    -- Citations for Alternate Names
    SELECT c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear,
    n.deathyear, 'Alternate Name' AS Citer, s.NAME AS Source, s.refnumber, s.actualtext, s.comments, c.refnumber,
    c.actualtext, c.comments
    FROM citationtable c
    LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
    LEFT OUTER JOIN nametable n ON n.nameid=c.ownerid
    WHERE c.ownertype=7 AND +n.IsPrimary=0
    ;
    CREATE INDEX tmpCitations_idx ON tmpCitations(CitID);
    -- Now filter the results to get rid of duplicate citation IDs due Alt Names
    SELECT * FROM tmpcitations
    WHERE uniq=1 OR uniq ISNULL
    ORDER BY RIN, Citer , SOURCE
    ;
    -- CitID
    -- RIN, Citer;
    -- END
    I am getting the following error: ON clause references tables to its right.
    I broke the query down and ran each section. I got the error on the RED section.

    (view changes)
    8:06 pm

More