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