Validation/Maintenance Tool for RM databases
Meant to be simple to use, lets you browse the raw data and the validation checks for lots of potential issues with improper foriegn keys. (i.e. names without people, place details without places, etc.)
Just launch it, select your Unifuzz, and open your database. RM version insensitive.
I plan to add more tools in the future, such as fixing RESI to CENSUS and cleaning some text from Notes and replacing with citations. I'd also like to fix the errors found in validation once I've determined the proper fix for each.
Uploaded files:Apart from some odd UX which I figured out, I found the Verify result very informative. I ran it on a database someone had sent to me to solve a specific problem It's a good size. Your utility pointed out almost 25000 records pointing to invalid IDs in other tables. I've been saying for years that RM does not clean up properly after deletions. It's better than it used to be.
Would you be willing to include in your distribution a list of the SQL that's used?
Sure, I'm trying to determine the best way to do that. I was trying to put it in the help page. It's really not very fancy mostly:
SELECT key,fields FROM table WHERE ownertype = x and foriegnkey NOT IN (SELECT keyfield FROM othertable)
For the time being here's the SQL for Verify:
SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)
SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)
SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 3 And OwnerID NOT IN (SELECT SourceId FROM SourceTable)
If App.rmVersion > 6000 Then
SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 6 And OwnerID NOT IN (SELECT TaskID FROM TaskTable)
If App.rmVersion > 6000 Then
SELECT LinkId,anId,rmId FROM AncestryTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)
SELECT LinkId,anId,rmId FROM AncestryTable WHERE LinkType = 4 And rmId NOT IN (SELECT CitationID FROM CitationTable)
SELECT LinkId,anId,rmId FROM AncestryTable WHERE LinkType = 11 And rmId NOT IN (SELECT MediaId FROM MultiMediaTable)
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 2 And OwnerID NOT IN (SELECT EventID FROM EventTable)
If App.rmVersion > 6000 Then
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 6 And OwnerID NOT IN (SELECT TaskID FROM TaskTable)
End If
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 7 And OwnerID NOT IN (SELECT NameID FROM NameTable)
If App.rmVersion > 8000 Then
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 19 And OwnerID NOT IN (SELECT FanID FROM FanTable)
End If
Else
' RM7.5
SELECT LinkId,extId,rmId FROM LinkAncestryTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)
SELECT LinkId,extId,rmId FROM LinkAncestryTable WHERE LinkType = 4 And rmId NOT IN (SELECT CitationID FROM CitationTable)
SELECT LinkId,extId,rmId FROM LinkAncestryTable WHERE LinkType = 11 And rmId NOT IN (SELECT MediaId FROM MultiMediaTable)
End If
SELECT CitationId,SourceId FROM CitationTable WHERE SourceId NOT IN (SELECT SourceId FROM SourceTable)
If App.rmVersion > 9000 Then
SELECT RecId,ID1,ID2 FROM DNATable WHERE ID1 NOT IN (SELECT PersonID FROM PersonTable) OR ID2 NOT IN (SELECT PersonID FROM PersonTable)
End if
SELECT EventId,EventType FROM EventTable WHERE EventType NOT IN (SELECT FactTypeId FROM FactTypeTable)
SELECT EventId,OwnerId FROM EventTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)
SELECT EventId,OwnerId FROM EventTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)
SELECT EventId,FamilyId FROM EventTable WHERE FamilyId > 0 And FamilyId NOT IN (SELECT FamilyID FROM FamilyTable)
SELECT EventId,PlaceId FROM EventTable WHERE PlaceId > 0 And PlaceId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 0)
SELECT EventId,SiteId FROM EventTable WHERE SiteId > 0 And SiteId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 2)
If App.rmVersion > 6000 Then
If App.rmVersion > 8000 Then
SELECT FANId,ID1 FROM FANTable WHERE ID1 NOT IN (SELECT PersonID FROM PersonTable)
SELECT FANId,ID2 FROM FANTable WHERE ID2 NOT IN (SELECT PersonID FROM PersonTable)
SELECT FANId,FANTypeId FROM FANTable WHERE FANTypeId NOT IN (SELECT FANTypeId FROM FANTypeTable)
SELECT FANId,PlaceId FROM FANTable WHERE PlaceId > 0 And PlaceId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 0)
SELECT FANId,SiteId FROM FANTable WHERE SiteId > 0 And SiteId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 2)
End if
SELECT LinkId,fsId,rmId FROM FamilySearchTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)
Else
' RM 7.5
SELECT LinkId,extId,rmId FROM LinkTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)
End if
SELECT FamilyId,FatherId FROM FamilyTable WHERE FatherId > 0 And FatherId NOT IN (SELECT PersonID FROM PersonTable)
SELECT FamilyId,MotherId FROM FamilyTable WHERE MotherId > 0 And MotherId NOT IN (SELECT PersonID FROM PersonTable)
SELECT FamilyId,ChildId FROM FamilyTable WHERE ChildId > 0 And ChildId NOT IN (SELECT PersonID FROM PersonTable)
SELECT LinkId,MediaId FROM MediaLinkTable WHERE MediaId NOT IN (SELECT MediaId FROM MultimediaTable)
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 2 And OwnerID NOT IN (SELECT EventID FROM EventTable)
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 3 AND OwnerId NOT IN (SELECT SourceId FROM SourceTable)
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 4 AND OwnerId NOT IN (SELECT CitationId FROM CitationTable)
If App.rmVersion > 6000 Then
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 6 And OwnerID NOT IN (SELECT TaskID FROM TaskTable)
End If
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 7 And OwnerID NOT IN (SELECT NameID FROM NameTable)
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 14 AND OwnerId NOT IN (SELECT PlaceId FROM PlaceTable)
If App.rmVersion > 8000 Then
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 19 And OwnerID NOT IN (SELECT FanID FROM FanTable)
End if
SELECT NameId,Surname || ', ' || Given as Fullname, OwnerId FROM NameTable WHERE OwnerID NOT IN (SELECT PersonId FROM PersonTable)
SELECT PlaceId,Name,MasterId FROM PlaceTable WHERE MasterId > 0 AND MasterId NOT IN (SELECT PlaceId FROM PlaceTable)
SELECT PlaceId,Name,MasterId FROM PlaceTable WHERE MasterId = 2 AND MasterId NOT IN (SELECT PlaceId FROM PlaceTable)