Forum

Please or Register to create posts and topics.

RM8 update for Tom's SQL script "Facts- Split Shared to Individual"

Am a novice (at best) with SQL  but want to share this script.  It works for me but please let me know if I've made any mistakes or where my edits are sub-optimal.

[12 Jun 2022 edit - after discussion with Tom Holden I realized that there was an error in the file that I had posted.  The RM8 AncestryTable does not need to be updated with this process because the new 'individual facts' reuse the citation(s) of the original shared fact. That code was removed and the file renamed to "RM8Facts-SplitSharedToIndiv.sql" to avoid any confusion with Tom's rm7 script.]

 

Background:

I like to use the RM feature to "share facts" and also periodically want to push a tree to ancestry.  For RM7, Tom's script solves the problem that many other platforms don't support the 'shared fact' concept. (https://sqlitetoolsforrootsmagic.com/facts-split-shared-to-individual/) There were only a few updates needed to make it work for RM8.

I run it against a copy of my RM db so that the new split facts don't confuse things.  Also, you should run all 4 RM Database Tools after using. Comments have been added to the script and will summarize here:

    •  The new citations from the split fact are a "citation reuse" not a separate citation.
    • This script does not append the RIN to the new fact sentence. (The RIN statement is commented out and could be easily added back in.)

#RM8  #sources  #sharedevent  #sql

Uploaded files:
Tom Holden has reacted to this post.
Tom Holden

Kevin, I'm embarrassed to say that I had forgotten you had created a RM8 version and had not revised the page Facts - Split Shared to Individual to include it. Had I remembered, it would have saved me a few hours of scraping the rust off my sqlite skillset and reacquainting myself with what I'd done so many years ago! When someone inquired about the script for a RM9 database, I plunged in. My result is definitely a hack compared to your clean format but it's on the page now with some screenshots.

One thing I discovered in the original script and I think may apply to yours is that when there are multiple not-in-database people sharing an event, the paragraph appended to the Event for the Principal contains only one. It took me a while to correct that having forgotten the GROUP_CONCAT aggregates function - wasted my time with COALESCE and RECURSIVE CTE before I rediscovered it.

Another cleanup I've added for UNDOing the split is the deletion of the appended paragraph for not-in-database sharers using the REGEXP_REPLACE function which I only recently discovered is included in SQLiteSpy.

Kevin McLarnon has reacted to this post.
Kevin McLarnon

Tom, thank you for jumping in and updating your script to support RM9. I just gave the updated page a quick read and it's excellent work, as usual. (I had not even considered the case of not-in-database people.) Will check it out in more detail and post comments if I encounter anything unexpected.

The depth and breadth of the help you provide to the community is truly inspirational! Well done and Thank You.

Tom Holden has reacted to this post.
Tom Holden