Maintenance tools to RM databases

RootsMagic
1

I’m working on a tool that will look at RM databases from RM 7 to 11, it works on Mac and Windows. My primary goal is to look for “orphans” such as names without people, place details without places, etc.

While much of this can be cleared up by doing a drag and drop to another database many things such at reports in publishing are lost in this process.

I’d like to publish the finished product to https://sqlitetoolsforrootsmagic.com/ but I’ll likely need some help from Tom Holden to do that when I’d done’.

2

if you are simply comparing tables such as MediaLinks without Media (MediaID in only in MediaLinks) or Names (OwnerID) is not in PersonTable – those are simple Sql(ite) statements.

3 
SELECT
    mlt.*
FROM
    MediaLinkTable mlt
LEFT JOIN
    MultimediaTable mt ON mlt.MediaID = mt.MediaID
WHERE
    mt.MediaID IS NULL;

should do that if I recall correctly

4

Right, what I’m doing is working to automate the process because it’s tedious to type in dozens of SQL statement and manage the results. It’s more than media, as there are lots of bad links possible in places and names. For example I have a good dozen or more names with personids higher that the highest person id, places with master ids that don’t exist, etc.

5

So this goes a bit beyond what to use RM for – but you can build stuff like that through Power Query / Excel and Power BI.

image
image1319×776 87.5 KB

6

True, but my goal is a simple to use tool that doesn’t require extra configuration that can perform basic maintenance and validation tasks easily, and it should be available for others.

Screenshot 2025-10-31 at 9.48.13 AM
Screenshot 2025-10-31 at 9.48.13 AM1920×1081 117 KB

7

Well, I agree there should be some additional database tools to keep tables cleaner. But if one if having those types of errors –Orphans - not sure why the orphan tool is not catching them - you might want to open a ticket. Or why they are being introduced in first place.

8

I actually have opened several tickets, including one asking for more complete error checking. The database tools only check a subset of issues, and I’ve always felt it should be all links but apparently RM support does not. As far as what’s causing the errors, it’s always been some bug. Citation merge, 16 bit UI bug in place details, SQlite quirks on Mac, etc. I’ve been using RM regularly since 7.5 came out so I’ve hit quite a few. Sometimes the bug is fixed, sometimes it’s not. Either way there’s often cleanup necessary. In any case having a tool to identify and potentially fix issues lets me more accurately report issues to RM when the need does crop up. I also occasionally help others with RM issues, and they often wouldn’t know SQL if it leapt up and bit their backside.

9

I do recall a couple citations around time Preview 8 came out with Merge Citation/sources I was one of the user who reported it and other users confirmed my experience– that particular bug was fixed (though it made a mess of things the only way for most users to recover was backup file). I do not think the average needs to know SQL/Sqlite but it would be helpful if they had better understanding of Databases in general and their structure. I have been around awhile and work with data so I have advantages

10

Sure, happy to help. Message when ready.

11

Thanks Tom, I have a lot of checks to add. Done through GroupTable so far. Probably need to add some UI improvements along with the ability to save all the errors to a PDF or text file.

What is more perplexing is adding a “clean” function. It’s easy to delete a name without a person or a place details without a place. Now an event without a valid place details should likely be set to 0, not deleted. I will ask RM support for their input for those.