Jump to content


Photo

RoleTable phantom orphan/corrupt roles

shared roles phantom records SQLite

  • Please log in to reply
2 replies to this topic

#1 GJohn

GJohn

    Member

  • Members
  • PipPip
  • 10 posts

Posted 18 March 2020 - 03:19 PM

I discovered what I believed are several shared-roles related RM7 bugs, which cause other 'anomalies', and at a minimum they leave garbage orphaned/phantom records in the database.rmgc file.
Using latest version 7.6.3.0 on a Win7 PC.

 

1) Using RM7 GUI, I create a user-defined fact type with a shared role, then delete the fact without deleting the roles first, it leaves an internal orphan role record.

 

select Lists->Fact Type List...

click Design new fact type

select Individual Fact Type (duplicated problems with Family Fact Type)

enter info in Edit Fact Type window i.e. Name & Abbr AAA

click OK

select new AAA fact (from Fact Type window list)

click Edit

click Add Role

enter info in Edit Role Type window i.e. Role name XXX

click OK

click Close

Using fact or role does not matter, but optionally use new fact type and role for some test person in your database.

select Lists->Fact Type List...

select new fact AAA (from Fact Type window list)

click Delete

confirm Yes

click Close

 

Problem: At this point, viewed from SQLite (script at end of this post), the internal RoleTable contains an orphaned relic role, where the role record exists RoleTable.RoleID has a non-zero value i.e. primary-key, the RoleTable.RoleName = XXX, and RoleTable.EventType has a non-zero value i.e. a foreign-key, but their is no matching event in the FactTypeTable.FactTypeID i.e. primary key, however, the AAA fact type was correctly removed, making the role an orphan. The "Database tools" do not resolve the issue.

 

2) This is a similar but different RM7 scenario were I create a user-defined fact type and immediately create the shared role, the role does not show up when the fact type is viewed, and it creates an internal corrupt role record.


select Lists->Fact Type List...

click Design new fact type

select Individual Fact Type (duplicated problems with Family Fact Type)

enter info in Edit Fact Type window i.e. Name & Abbr BBB

click Add Role (different order from problem 1)

enter info in Edit Role Type window i.e. Role name YYY

click OK

click OK

select new BBB fact (from Fact Type window list)

click Edit

 

Problem: In Edit Fact Type window Role list the new role YYY is not displayed. Running Database tools does not fix the problem.

Viewed from SQLite (same script at end of this post), the internal RoleTable contains a corrupted role, where the role record exists RoleTable.RoleID has a non-zero value i.e. primary-key, the RoleTable.RoleName = YYY, and RoleTable.EventType has zero value i.e. no foreign-key, and of course their is no matching event in the FactTypeTable.FactTypeID i.e. primary key, however, the BBB fact type was correctly removed, making the role a corrupt orphan. The "Database tools" do not resolve the issue.

 

3) Problem: Over the past years I have seen transient "phantom" roles appear when creating new event types, totally unrelated to that fact, and they sometimes even get saved associated with that fact type and I have to manually delete them to clean things up. I have seen this in several scenarios related to the Fact Type and Shared Roles windows GUI, but often I could not duplicate them consistently to report the issues. I won't go into those detailed steps & occurances its obvious to me now that its associated with these orphaned records in the RoleTable with RoleTable.EventType foreign keys that are zero (no match in FactTypeTable.FactTypeID) of have a value not found in any the FactTypeTable.FactTypeID, both could cause these anomalies I have seen the past years.

 

4) Problem: The RM7 "Database tools..." to "Clean phantom records" does not clean up internal RoleTable corrupted or orphaned records.

I have run this at multiple points in isolating the bugs above, the same result. I've used it alone, but I typically use it in-combination with the "Rebuild indexes" & "Compact database", and then using "Test database integrity: always returns "Integrity check results: ok" but phantom records remain internally when viewed in SQLite.

 

When I manually deleted the orphaned/corrupted RoleTable records using a SQLite script (see second script at end of this post), I observed no negative impact on RM7 GUI behavior, and the transient issues with Problem 3 go away, regardless, before/after running all the Database tools...  I always get "Integrity check results: ok"

 

Background: I discovered this because I am a TMG refugee, I had many user-defined imported fact types, and I've been seeing these Problem 3 anomalies for several years. So I have been systematically cleaning up & re-verifying all the data in my database: I have been re-entering all data using either a RM7 default fact types or new RM7 user-defined ones, and once the data was re-entered I was deleting the imported TMG ones one-at-a-time. All these steps using RM7 GUI (not SQLite scripts). It did not stop the shared-roles anomalies I was seeing.

But viewing from SQLite (which I just did recently used with RM for the first time), I found over two-hundred obsolete RoleTable records.

I was using SQLite to auto-generate shared roles and events for my Narrative Reports using a point-fact narrative (an idea I got off your Forums plus the RM SQLite website posts, sorry, I cannot recall who at the moment to give credit to, nonetheless appreciate the idea).

However, I believe these problems are independent of the TMG import or my SQLite auto-generated events and roles. They can all be created independently directly from the RM7 GUI, and cause anomolies (see Probelm 3).

 

FYI: If it matters, I have related professional technical experience.
 

The SQLite script I used to identify these orphan/phantom records

SELECT
   RT.RoleID
  ,RT.RoleName
  ,RT.EventType
  ,FTT.FactTypeID
  ,FTT.Name
FROM
   RoleTable AS RT
LEFT OUTER JOIN
   FactTypeTable AS FTT
   ON FTT.FactTypeID = RT.EventType

;

The SQLite script I used to delete these orphan/phantom records and make the problems go away:

DELETE
FROM RoleTable
WHERE RoleID IN (
   SELECT
      RT.RoleID
   FROM
      RoleTable AS RT
   LEFT OUTER JOIN
      FactTypeTable AS FTT
      ON FTT.FactTypeID = RT.EventType
   WHERE
      FTT.FactTypeID IS NULL
);



#2 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3929 posts

Posted 18 March 2020 - 08:55 PM

I have one instance of a phantom role in my database. It's for a user defined fact, and it's not a fact for which I have defined a role. The phantom role is Witness. When I do define roles, I never define a role called Witness. Indeed, I find the generic role of Witness to be meaningless and confusing so I have removed a lot of the ones that are there by default.

 

I can't picture how the phantom role might have occurred. And as you have already indicated, there is no way through the RM user interface to clean up phantom roles.

This has made me wonder if there are any other phantom rows in any of my database tables. In principle, they could all be identified with SQLite with a query modeled after yours. Which is to say, run queries such as the following for any foreign key and primary key pair in the database.

SELECT some stuff
FROM ForeignTable AS F
LEFT JOIN
PrimaryTable AS P ON F.ForeignID = P.PrimaryID;

Any null PrimaryID's reflect phantoms or you could add WHERE P.PrimaryID IS NULL

 

Jerry 



#3 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6435 posts

Posted 18 March 2020 - 09:18 PM

This provides a pretty thorough cleanup: https://sqlitetoolsf...tePhantoms3.sql

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.