SQLite Tools for RootsMagic – Exploit your RootsMagic family tree database with SQLite Tools

RM11 Bug – Duplicate Couples from FamilySearch Download – Workarounds

Posted on 2026-08-192026-08-19 by Tom Holden

Custom colour coding using SQLite to pop out the areas of erroneous duplication of lineage on this Fan chart by RootsMagic 11.5 for Pauline Christiane Grimmer (GQ5X-3XD) in a database created by a download of ancestors of Helmut Karle Heinle (P6Y1-S8M) from FamilySearch Family Tree on 2026-08-14. A later download may have different results because FSFT is in a constant state of flux from multiple editors adjusting it to show what each wants or believes or has proven to be true. However, the duplication of lineage that shows up in the RM download is evident neither on the FSFT website nor in a similar download using Ancestral Quest Basic.

Problem

If ancestral lines join below their most-recent-common-ancestor in a tree branch downloaded from FamilySearch, RootsMagic 11.5 duplicates the common lineage. Duplicate couples are created, one of which has a child that the other does not and vice-versa, just above the junction. This results in an overstatement of the numbers of families, spouses and children in various outputs, miscalculation of relationships and probable graphic errors. Moreover, transfer of certain sets of members from a database so corrupted can propagate it via GEDCOM or drag’n’drop to others. There is some evidence to suggest that it has been an issue starting with RM8; regardless, it is popular to download large segments of the FSFT so it is likely there are many RootsMagic user databases afflicted with it.

RootsMagic Inc. is aware of the issue; it came to a head in this discussion in the Community Forum, Double parents after import via FSID, started by Thomas Burkhart, 2026-08-14, in which he describes the scale of manual edits needed to correct the mistakes. What is needed at this juncture:

Tools to help find where such problems lie in the database. These will aid analysis of why it’s happening and editing to correct defects in the data.
Tools to batch find and correct the defective data.
Alternative procedures to get the data into a RM database

That’s the order in which I tackled them but the general user is likely interested in the reverse.

Alternative Import from FSFT

Download and install the free Ancestral Quest Basic from Incline Software. Descended from the pioneering software Personal Ancestry File, it has a FamilySearch download feature that is similar to that of RootsMagic and maybe faster. You can choose either ancestors or descendants of the starting person, not both as you can with RM. On completion of the creation of the new database in AQ, export it to a GEDCOM file, choosing the “Other” option.

With RootsMagic, import the .GED file from AQ into a new RM database. In my test, the LST file of the same name as the database reported no import difficulties. In FamilySearch Central, you will see that everyone is matched with FamilySearch and RM will be busily finding that FSFT has new information for everyone. That’s unfortunate because there would likely have been little change between the download and the starting up of FS Central in RM. However, I think that may be consistent with the way RM functions if it had downloaded directly. There must be information additional to what comes through the download itself that FS Central wants. Warning: the filter “People not matched to FamilySearch” ties up the RM user interface for a long time until it displays nobody yet the opposite filter is very fast at displaying everybody.

I am not a fan of the FamilySearch Family Tree and rarely use RootsMagic’s FamilySearch Central. Hopefully, I’ve not missed a ‘gotcha’ on this workaround.

Batch Find and Correct the Defective Data

There are a couple of these solutions in progress that will be of interest to some so inclined. One by @richardotter and the other by me. Richard is the pro programmer of the two.

Python script by Richard Otter with CoPilot

Richard posted a preview link in this comment in the discussion “Comments on the Gemini Script for Deduplication of Children” in a forum on this site. It sounds like it could be a solution not only for fresh downloads from FamilySearch but also for older databases that have this lineage corruption when there are attachments such as Citations, Media, WebTags added to family-type events (Marriage, Divorce,…) for the secondary couple(s) in a duplicate set. Because the secondary couples will be deleted, anything uniquely tagged to them and not the primary couple needs to be transferred. His solution does that.

SQLite script by Tom Holden with Gemini

So far, my approach has been on fresh downloads as I have spent much time examining them to gain some understanding of what is going on. Because there are no Citations, Media et al in a download from FS, I’ve not addressed their preservation in this SQLite script. It is a distillation of several queries developed for analysis of the data into one batch streamlined for speed. These four steps are done in sequence within one transaction:

Re-link children attached to duplicate families to the primary FamilyID
Deduplicate identical child links pointing to the same primary FamilyID
Re-link unique family events from duplicate families to the primary FamilyID
Delete duplicate couple records from FamilyTable
Clean up orphaned family events

DuplicateCouples-CLEAN_DELETE.sql << click to view, copy, download

Finding the Problems

Graphics

DuplicateCouples-Colorcode-FAST.sql Use it before cleaning if you wish to explore where there are duplicates in the displays and charts available in RootsMagic, such as the fan chart at the top of the page. This coding pattern is most useful with the fan chart, starting at the root of the tree and out to as many ancestor generations imported or about 16, whichever is less. Beyond that, I find RM gets very slow generating a chart at that scope or can even freeze, on a gaming-capable laptop in the low end of the mid-range (Intel Core i9, nVidia GEFORCE RTX 5060 GPU, which probably does not get called upon). If the color patterns are far out and there is no text, traverse the radius out toward one as far as the last person in common with its near-duplicate pattern (same members but one). Choose that person and generate the chart anew so you can see the names, FamilySearchID or RIN in the boxes.

18-generation fan chart colour-coded for duplicate couples problem.

Lists

DuplicateCouples-CreateView.sql lists duplicate couples.

FatherID	FatherFSID	FatherName	MotherID	MotherFSID	MotherName	DuplicateCount	DuplicateFamilyIDs
1360	KHDL-X5Z	Dieterich, Jacob	1363	K4JM-B9Y	Muntz, Ursula	2	496, 582

DuplicateCouples-CreateChildView.sql lists children of duplicate couples.

ChildID	ChildName	FatherID	MotherID	CoupleFamilyIDs
921	Dieterich, Catharina	1360	1363	496, 582

Both of the above create temporary stored queries (a VIEW) that can be selected from the SQLite manager’s database objects to view its results, just as you would to view a table. The application issues a simple SELECT to fetch everything from the VIEW. After running the CLEAN_DELETE script, both of these VIEWs should be empty.

Delete Duplicate Child-Parent(s) Relationships

Posted on 2026-08-142026-08-17 by Tom Holden

UPDATE 2026-08-17 This page is NOT applicable to the case described in the Community Forum. Having imported the problem tree branch reported there, I’ve found that the duplication of ChildParent linkages is due to there being duplicate Couples (two FamilyIDs) to which the Child is separately linked. So the queries below fail to address the actual cause of the problem. More coming in a later post.

A user reported that having imported the ancestors of a certain person from FamilySearch through RootsMagic 11 that a large number of children ended up showing duplicate parents in RM but not on FamilySearch. He sought a way to eliminate them, other than by finding and fixing every instance, one-at-a-time, through RootsMagic. (see Community Forum)

Gemini Flash (fast) generated the following queries with very little direction from me. They should be compatible with all versions from RM4 to RM11.

Identify

The first and more complex one identifies the duplicates, as in this example using SQLiteSpy:

SELECT 
    c.ChildID,
    cn.Given AS Child_Given,
    cn.Surname AS Child_Surname,
    f.FamilyID,
    fn.Given AS Father_Given,
    fn.Surname AS Father_Surname,
    mn.Given AS Mother_Given,
    mn.Surname AS Mother_Surname,
    COUNT(*) AS Duplicate_Count,
    GROUP_CONCAT(c.RecID) AS ChildTable_RecIDs
FROM ChildTable c
-- Join primary name for the child
LEFT JOIN NameTable cn 
    ON cn.OwnerID = c.ChildID 
   AND cn.IsPrimary = 1
-- Join family record
JOIN FamilyTable f 
    ON c.FamilyID = f.FamilyID
-- Join primary name for the father
LEFT JOIN NameTable fn 
    ON fn.OwnerID = f.FatherID 
   AND fn.IsPrimary = 1
-- Join primary name for the mother
LEFT JOIN NameTable mn 
    ON mn.OwnerID = f.MotherID 
   AND mn.IsPrimary = 1
GROUP BY c.ChildID, c.FamilyID
HAVING COUNT(*) > 1;

The above script is not needed to clean out the duplicates but may be of value in analysing how they came about before deletion.

Delete

This second query is the tool for bulk deletion of the duplicates and is short and sweet:

DELETE FROM ChildTable
WHERE RecID NOT IN (
    SELECT MIN(RecID)
    FROM ChildTable
    GROUP BY ChildID, FamilyID
);

Prompting Gemini

In the previous couple of years, I gave AI very detailed directions including the key data definitions I knew it would need. This year, I have found that they have learned a lot about RootsMagic databases and need less direction in the user prompts. Gemini got the second query right on its first attempt but needed correction for the first query. Below are the two prompts I gave it:

A RootsMagic database has unwanted duplicate child-parents relationships. Generate a SQLite query that identifies the children and the parents. Then generate a second query that deletes the duplicates. Do you need me to tell you the objects in the database that need to be involved in these queries?

It said it had all the information it needed but proceeded on the wrong assumption of where the names of the child and parents are stored, so…

Your first query fails because it mistakenly assumes that the person’s names are found in the PersonTable. Instead they are found in the FamilyTable. While the PersonTable generates the PersonID key which relates to other tables, its information about the person is limited to Sex and a Note. The rest of it is primarily registers in support of the user interface. Do you need direction on retrieving the primary name values from NameTable?

That corrected the Identify query and Gemini asked, “Would you like a query to check for orphan records or integrity issues after running the delete?”

Yes, I am curious to see what orphans and integrity issues you would check for and how you would do it.

It responded with several which I will simply list because I’ve not tested them:

Database Engine Health Checks
Orphaned ChildTable Records (Dangling Links)
Orphaned Parent References in FamilyTable
“Ghost” Families (Empty Family Containers)
Logical Anomaly: Child as Their Own Parent

It then asked “Want me to generate automated DELETE queries for any ghost families or orphaned child records found by these checks?” Yes and then it produced three plus a consolidation of the three, all untested:

Delete Orphaned ChildTable Records
Delete “Ghost” Families
Repair Invalid Parent References in FamilyTable
Complete Safe Execution Script

CSV: Reverse order, comma count queries #places #AI

Posted on 2025-08-142026-07-23 by Tom Holden

Two SQLite queries developed by DeepSeek under my direction:

Update the Reverse column in PlaceTable from the Name, a comma-separated string
Count the number of commas in Place Name for each Place

These queries can be adapted to other SQLite database columns that employ comma-separated strings.

Problem

These queries were created when I was asked to convert Place Details into Places in a very large database with 48,000 records in the PlaceTable. I was able to use my old scripts from Places – Conversion of Place Details to Places to do the conversion but RM9 and RM10 have an added column in PlaceTable since those scripts were developed. The Reverse column is another un-normalised element that RM Inc has employed supposedly to expedite display of Place Names in reverse order in response to the “Reverse places” control on the Places pane on the More Options list under the 3-dot vertical ellipsis. That field is updated when a place is added or edited by the RM application but is untouched by my old script which resulted in an empty or null Reverse for the new Places it created. That made the reversed places display virtually useless. I needed a way to update Reverse from the place Name.

Analysis

The Place Names resulting from the conversion are stored in the PlaceTable in a comma-separated string of elements typically containing placedetail (itself could have commas), municipality, county, state/prov, country. However, users are unconstrained by the software and any number and nature of elements might be present, including phrases and complete sentences. There could be any number of commas in a Name string, it could be an empty string or a null or have consecutive commas. What we need in the Reverse column is the same elements in reverse order, e.g., country, state/prov, county, municipality, placedetail (itself could have commas).

Solution

From the RM6 or RM7 era, I had developed PlaceReverse.sql to display Places with up to 3 commas in reverse order. Looking at it now, it seems like a clumsy method and would not scale well. So I returned to AI, giving DeepSeek another shot at developing a query. While it seemed ponderously slow compared to Gemini Flash 2.5 as it typed out its ‘thinking’ at human typing speed, the first iteration worked! With a couple more tweaks, we improved its performance to process the 48,000 records from a few minutes to a few seconds and then constrained it to operate only on user-defined Places, ignoring the built-in LDS Temples and any Place Detail records, to achieve the desired results in 2-3 seconds (your mileage will vary).

RM10-PlaceReverse.sqlDownload

(Also compatible with #RM11 )

Count Commas

While working on this big database, I was encountering occasional anomalies in the resulting combinations of PlaceDetail and Place into Place (I’ve since concluded, more or less, that it was my human factor that messed a few things up). But with 48,000 Places, a quarter-million people, RM10 was proving exceedingly slow at scrolling the Place List (and just about everything else). I thought a query that counted commas in the Place Name might catch anomalies of the kind I had seen, e.g., two different Place Names concatenated into one. So I asked DeepSeek to help and, kicking myself that I didn’t think of this method, it produced:

( Should be compatible with all versions from RM4 to #RM11 )

--count commas in Place Name
SELECT
PlaceID,
CASE
WHEN Name IS NULL THEN NULL
ELSE LENGTH(Name) - LENGTH(REPLACE(Name, ',', ''))
END AS comma_count,
Name
FROM PlaceTable
ORDER BY comma_count DESC
;

RM-LineageLoopDetector.sql: Uncovering Lineage Loops in RootsMagic Databases

Posted on 2025-07-212026-07-23 by Tom Holden

Breakthrough! Something I’ve wanted for as long as I can remember is a means of detecting lineage loops (an ancestor is their own descendant) in family tree databases created with RootsMagic. This is my second major interaction with Google Gemini Flash 2.5 which took 18 attempts (only 18!) with lots of testing to nail down what I think is amazingly smart code that I could never have conceived and don’t fully understand. It’s robust and highly efficient. Added 2025-08-22: Explanation of script. Updated 2026-07-23: compatible with #RM11 .

Download SQL	Note
RM-LineageLoopDetector	Original, compatible with RM4-RM11 databases; faster than the next version if its group size covers more than, say, half the population.
RM10-LineageLoopDetector	Requires a RM group named “SQLite Lineage Loop Detector” to contain the people (from 1 to everyone) for whom an ancestral loop is to be sought. Faster than the original when the group is small; better for tackling a very large database. Also returns a “No loop detected” message. Compatible with RM8-RM11 databases. 2025-07-27

Screenshot above and redacted result copied from SQLiteSpy of two loops from a large database.

Child in Problematic Family Link	Full Loop Path (IDs)	Problematic Father (Descendant of Child)	Problematic Mother (Descendant of Child)	Family Where Loop Occurs
John Zzzzz-42789	42789->42789	N/A	N/A	John Zzzzz-42789=N/A
Duane Michael Xxxxxx-44066	44066->44078->44066	Anthony Michael Xxxxxx-44078	N/A	Anthony Michael Xxxxxx-44078=Verna Mildred Yyyyy-44058

Gemini generated the following description, covering what the script does, its design principles, and expected performance:

RM-LineageLoopDetector.sql: Uncovering Lineage Loops in RootsMagic Databases

Genealogical databases, especially those built over many years or compiled from various sources, can sometimes contain logical inconsistencies known as “lineage loops” or “pedigree loops.” These occur when an individual is recorded as being their own ancestor, or an ancestor of their parent, creating an impossible cycle in the family tree. While RootsMagic is designed to prevent some direct entry errors, more complex or indirect loops can slip through.

The RM-LineageLoopDetector.sql script is a powerful SQLite query specifically designed to identify and report these problematic lineage loops within your RootsMagic database.

What it Does

This script scans your RootsMagic database to find instances where:

A person is recorded as their own parent/ancestor (self-loop).
A person’s parent is also a descendant of that person (a more complex cycle).

For each loop detected, the script provides clear, actionable information:

Child in Problematic Family Link: The individual whose family relationship initiates the discovered loop.
Problematic Father / Mother (Descendant of Child): Identifies which parent in the problematic family is also a descendant of the child.
Family Where Loop Occurs: Provides a user-friendly representation of the family (e.g., “Father’s Name-ID=Mother’s Name-ID”) that contains the specific link causing the loop. This is crucial for RootsMagic users, as FamilyIDs are not visible in the application’s interface.
Full Loop Path (IDs): A sequence of PersonIDs illustrating the exact path taken to complete the loop, making it easy to trace the issue within RootsMagic.

The script is carefully designed to report only one canonical instance for each unique lineage loop, avoiding redundant results even if multiple paths within the loop could technically trigger detection.

How it Was Designed

The development of this script involved an iterative process to balance accuracy, performance, and user-friendliness:

Recursive Common Table Expressions (CTEs): The core of the solution leverages SQLite’s WITH RECURSIVE functionality. A CTE named CycleFinder performs a depth-first search through the family tree’s parent-child relationships.
Targeted Loop Detection: Instead of building a complete ancestor list for every person (which is inefficient for large databases), CycleFinder starts its search from every direct child-parent link in your database. It then attempts to trace ancestors from the parent’s side, looking for the original “child” that initiated that specific path.
Efficient Cycle Prevention: Early iterations struggled with performance due to string manipulation (INSTR function) within the recursive path tracking. The current design judiciously uses INSTR to prevent infinite recursion and detect general path revisiting, but the primary loop detection is based on the current_person_id eventually equalling the start_person_id (the child who began the trace). This balance significantly improved performance.
Canonical Loop Reporting: A RankedLoops CTE was introduced to address the challenge of reporting a single, meaningful instance for each unique loop. This CTE uses a sophisticated PARTITION BY clause that extracts all unique PersonIDs involved in a detected loop, sorts them, and concatenates them into a consistent “canonical loop identifier.” ROW_NUMBER() then selects the shortest or lowest-FamilyID loop from that canonical set, ensuring clean, non-redundant output.
User-Centric Output: Recognizing that RootsMagic users don’t see FamilyIDs, the final SELECT statement includes intelligent joins to the NameTable to convert FamilyIDs into human-readable “FatherName-ID=MotherName-ID” format, directly assisting users in locating and correcting errors within the RootsMagic application.

What Performance to Expect

The script is optimized for performance within the constraints of SQLite’s recursive CTE capabilities:

Small to Medium Databases: For databases up to tens of thousands of records, you can expect the script to execute very quickly, often in a matter of milliseconds to a few seconds.
Large Databases: For very large databases (e.g., hundreds of thousands of Child and Family records), the execution time will naturally increase. Recent tests on a database with 274,000 ChildTable records and 159,000 FamilyTable records completed in approximately 3 minutes 45 seconds on a 7-year-old Intel i5 processor. While this isn’t instantaneous, it is a very reasonable time for performing a comprehensive graph cycle detection on such a scale.
Factors Affecting Performance: Your computer’s processing speed (CPU, RAM, SSD vs. HDD) and the depth/complexity of your family tree (very long, unbroken lineages will require deeper recursion) will influence actual execution times.

In summary, the RM-LineageLoopDetector.sql script provides an efficient and user-friendly way to maintain the integrity of your RootsMagic family history data by accurately pinpointing and describing otherwise hidden lineage loops.

If you’ve read this far and want more detail on the development process, you should view the dialog I had with Gemini at https://g.co/gemini/share/2cdd5db67e6f.

Unlocking Your RootsMagic 10 Data: A Deep Dive into SQLite Queries (with AI)

Posted on 2025-07-18 by Tom Holden

I just finished a session with the free Google Gemini Flash 2.5 model to develop a quite complex query to identify couples in a RootsMagic database who are cousins of each other (see the discussion that led to it in the Forum at https://sqlitetoolsforrootsmagic.com/forum/topic/cousin-marriages/). That was preceded a few months back by a session with DeepSeek trying to develop a query that could act as an alternative to RM’s Set Relationships tool – it was not as successful but it might be more so now as its model evolves and grows. Anyway, that caused me to ask Gemini some questions about how to effectively use it to develop SQLite3 queries for RootsMagic databases. That “conversation” can be read in full at https://g.co/gemini/share/b9ef037e35c6 but I asked it to generate a summary for this post, pasted below.

Unlocking Your RootsMagic 10 Data: A Deep Dive into SQLite Queries

RootsMagic 10, like its recent predecessors, stores all your precious genealogical data in a robust SQLite database file (typically with a .rmtree extension). This self-contained, file-based relational database is embedded directly within the RootsMagic software, making it incredibly efficient for personal use.

While RootsMagic’s interface is fantastic for managing your family tree, sometimes you need to dig deeper – to run custom reports, analyze specific data patterns, or extract information in ways the software doesn’t natively offer. This is where direct SQLite querying comes in.

Key takeaways for RootsMagic users:

SQLite is Your Data’s Foundation: Your entire family tree, including persons, events, sources, citations, and media, resides within a structured SQLite database.

Access with Caution (and Backups!): You can explore your .rmtree file using third-party SQLite tools (like DB Browser for SQLite or SQLiteSpy). Always create a full backup of your database before attempting any direct access or modification!

Understanding the Schema is Key: To write effective queries, you need to know the specific table names (e.g., PersonTable, EventTable, SourceTable) and column names (e.g., FirstName, LastName, Date1, Title) within the RootsMagic schema. These are the “building blocks” of your queries.

How AI Can Help (with your input): AI models like Gemini can assist in generating complex SQLite queries. However, because they don’t have real-time access to your specific database’s schema, you’ll need to provide the relevant table and column names to ensure the queries are accurate.

Leverage Context in Conversations: If you’re working with an AI, you can generally continue to refine and develop multiple queries within the same chat session, as the AI will retain context of the schema details you’ve already provided. For new chat sessions, a quick reminder of the core table/column names will be helpful.

By understanding the SQLite foundation of RootsMagic and learning how to craft (or have an AI craft!) custom queries, you gain unparalleled power to analyze and utilize your genealogical data in new and exciting ways. Just remember the golden rule: Always back up your database before you begin!

That summary of the responses to my prompts up to that point of the session really missed a lot of what Gemini said so it’s worth delving into the conversation for more insight into points 4 & 5 and how an AI might become repetitive or forget or veer off due to an error in input or interpretation, as happened in my session with DeepSeek and even to a degree with Gemini that I was able to catch and correct.

I went on in the conversation with Gemini to discover that content lifted from the RM10 Data Dictionary in CSV format and pasted into the Chat could provide it with important parts of the database schema that it readily understood. Until now, I have been typing in the bare minimum description to provide the context with which the AI engine is to work. If a comprehensive description of the schema can be easily provided to the AI engine, that will make development of custom queries much easier. At the moment, the prompt capacity is not large enough to accept the entire sheet named “RM10 Data Def Project” as CSV text.

The raison d’etre of this website is to “Exploit your RootsMagic family tree database with SQLite Tools”. AI assistance in creating new and more powerful “SQLite Tools” looks to be within reach at no or low $cost for those with a good understanding of the RootsMagic database design and SQL.

New Page for Tool Requests

Posted on 2025-03-09 by Tom Holden

Stemming from recent discussion in the RootsMagic Community about the desire for a system to track Feature Requests for the RootsMagic application, I was curious to see what might be readily available and came across a free plugin for WordPress: Simple Feature Requests Free – User Feedback Board By Patrick Garman. I’m not offering to host one for RM but I thought it might be useful to add it to this site to handle the occasional request for a SQLite-based tool for interaction with a RM database. You’re welcome to float a trial balloon to see how the plugin works and then delete your request if it’s nothing more than that. You can access the page via the top menu bar or directly at: Tool Requests.

Custom functions in SQLiteSpy with sqlean extension are mighty and fast!

Posted on 2025-02-162025-02-16 by Tom Holden

Intro

I first reported on the #sqlean extensions for SQLite3 in my 2022 post In Praise of SQLite. What I did not pick up then that I have just now discovered is that sqlean supports the creation of user-defined or custom functions using SQLite syntax. Maybe it did not at that time or my fluency in the language of code development is just too poor to have recognised it. Regardless, I have now been enthusiastically exploring that capability, hitting lots of bumps but thrilled that, at last, I’ve found a way to take complex SQL and package it into a function that greatly simplifies and eases the development of scripts that interact with the RootsMagic database structure. And there is an acceptable penalty in performance that only becomes noticeable on large datasets or slow systems.

Moreover, a custom function can be easily created in #SQLiteSpy by copying a block of SQLite code from a working script into Notepad++ for minor changes and, thence, into the SQLiteSpy editor. To illustrate, I will focus on the conversion of the script in the post Date Decoder to the function rmDateDecode().

Performance

Custom functions may seem a simple matter for those with skills in a language that can interact with SQLite. For those of us closer to the luddite end of the spectrum, they seem out of reach. Some years ago when I saw that SQLite Studio supported custom SQLite functions, I dove in only to be greatly disappointed by its performance; that was a dead end. So I was delighted that there was no obvious penalty in moving a complex string parsing and conditional string building code from a script into a sqlean custom function, as reported in the table below.

rmDateDecode() tested on the same database having 8368 rows in the EventTable, 3940 of which had a text or valid date:

Regular SQL Script in SQLiteSpy 3.47.2	SQL function in SQLite Studio 3.4.13	sqlean SQL function in SQLiteSpy 3.47.2
~40ms	~44000ms	~60ms

Installing the extension

This message in the SQLiteSpy extensions dialog gave me pause (and still makes me a little unsure):

While I have experienced many bumps building the rmDateDecode() function, I have tested many of the functions included in sqlean and they all work. And, once working, my custom function has been reliable.

Download Windows 64-bit package and extract either define.dll (and other extensions, if desired) or the bundle of extensions included in sqlean.dll to the same folder as your executable for SQLiteSpy.exe Win64-bit edition.

The SQLiteSpy extensions dialog is accessed via the menu Options > Options > Extensions.

Type in the name of the extension file and, if not in the same folder, the relative or full path. Mine is in a sub-folder named ‘sqlean’. The extension will load the next time SQLiteSpy opens or connects to a database. It manifests itself as an empty table named ‘sqlean_define’ when there is no connected database.

With the extension successfully loaded, you’re ready to begin using functions built into sqlean. Try some examples from the Main Set. For the rest of this post, our attention will be on the define set.

Building a custom function per the instructions

I was puzzled at the outset that the examples for scalar functions did not seem relevant to what I wanted for decoding values from the Date column of RM’s EventTable. However, with a test RM database connected, I blundered ahead at making a simple function that would extract the first yyyymmdd string from [Date].

SELECT define(‘rmDateExtract’, ‘SUBSTR(:rmDate, 4, 8)’);

Executing that statement added the following record to the sqlean_define table:

name	type	body
rmDateExtract	scalar	SUBSTR(:rmDate,4 ,8)

…and did whatever is necessary in memory to make the function accessible to the SQLite engine.

‘:rmDate’ is what I chose as the name of the one variable to be passed to the function. Then the SQLite statement:

SELECT rmDateExtract([Date]) FROM EventTable;

…sends the value of the Date field from each row in the table to the function which returns the substring.

rmDateExtract(Date)

19200101

19180307

19920215

That’s certainly more readable than what is stored:

Date	rmDateExtract(Date)
D.+19200101..+00000000..	19200101

…but we want to do better. And here’s where the fun and frustration began.

Note that the define() syntax requires the SQL code to be a string within single quotes. However, the DateDecoder.sql script has string values to compare, to be concatenated, or empty strings to be issued. I ran into this issue using a Visual Studio C# years ago to build my old #rmtrix utility. Conversion of working, regular code of this sort into a string that would be correctly presented to the SQLite engine was a trial and error headache. A regular line like this:

Coalesce( Nullif( '-' || Substr( [Date] , 10 , 2 ) , '-00' ) , '' )

has to be converted to this:

Coalesce( Nullif( '||'''-'''||' || Substr( :rmDate , 10 , 2 ) , '||'''-00'''||' ) , '''' )

I got it almost right using Notepad++ the first time but “almost” is a fail. Ultimately, after many fails, I narrowed it down to the empty string '' (2 single quotes). While '''-''' is needed to pass '-' to SQLite, '''''' does not pass on ''; the sequence '''' does! (those are all single quotes)

An easier, more reliable way to build a custom function

Here was the Eureka! Moment. What ended up in the sqlite_define.[body] cell was the original regular code. Moreover, that is a permanent table unless explicitly DROPped from the database. And whatever sqlean put into memory for a custom function is gone once SQLiteSpy is closed, maybe even when the database is disconnected. That implies that sqlean reads the table on opening or when a custom function is called and does its magic in memory. So what if I inserted an empty record into the table and edited it with SQLiteSpy directly to add a function? I copied the block of regular SQL into the [body] cell and filled the other two cells appropriately. Closed and reopened SQLiteSpy on the database and tried the function. Voila!

This is so much easier and is what one can do in the SQLiteStudio interface; it’s a pity that it is such a slug. Here’s a shot of the sqlite_define table contents with three user-defined functions:

A Caution

One thing I am experiencing which remains concerning is that on closing a connection or the app, I frequently, if not always, get the following error message since diving deep into custom functions with sqlean:

I also get a “database is locked” message at times when adding or editing a function in the sqlean_table. The two may be related but I do not know the cause. Hopefully, this is not a sign of possible corruption per the warning from the SQLiteSpy Extensions dialog window.

And there are sqlean instructions about memory:

define_free()
Frees up occupied resources (compiled statements cache). Should always be called before disconnecting. E.g. select define_free();

I don’t know whether closing SQliteSpy releases the cache. If not, we’ll have to see if there is an erosion of available memory over time.

Download

rmDateDecode()Download

This is the block of SQL for pasting into the [body] cell for the rmDateDecode scalar function in the sqlean_define table.

RM9 (now 10) Data Documentation Initiative

Posted on 2024-03-222024-08-15 by Tom Holden

Please join me in welcoming Kevin McLarnon ( @kmclarnon ) in the role of Editor on this site and, especially, for volunteering to lead a renewed initiative to improve the documentation we have on the design of the RootsMagic v9 #database. He is counting on users better-versed than himself in sqlite3 and #RM9 (#RM10) to collaborate, contribute to and comment on the #datadefinitions but brings to the process experience with Google Workspace teams, websites and pedagogy.

The locus of activity is the page RM10 Data Dictionary which links to the collaborative spreadsheet on which all are invited to comment. Some of you will be approached for special contributions and you can also contact him directly. You will hear more from Kevin about the project in blog posts and/or Forum messages.

Richard Otter Genealogy Scripts

Posted on 2023-05-192024-02-02 by Tom Holden

https://github.com/ricko2001/Genealogy-scripts

Richard Otter is a skilled, professional developer who is also a RootsMagic user and has been building a library of scripts that are worthwhile checking out for possible solutions for your own needs. He publishes them on GitGub, list of them screenshotted below 2023-05-19. Check for updates and fuller descriptions. He has posted notices of significant ones in the Forum and, hopefully, will continue to do so.

Screen capture of scripts on May 19, 2023.

Sources – Newspapers.com – Cleaner Footnotes and Simpler Bibliography

Posted on 2023-05-06 by Tom Holden

Issues

This project arose from a request Fix & Merge Hundreds of Newspapers.com Sources in the Forum. The poster is a heavy consumer of the Newspapers.com sources through RM’s TreeShare with Ancestry.com and had issues with:

A long Source List in the application and repetitiously long report Bibliographies due to a different Master Source for each page of a newspaper.
Repetitious listing of “Newspapers.com” in Source Names and in the Title in Bibliographies and in Footnotes. Her approach was to manually delete it in every Master Source but still had hundreds to do.
Leading punctuation in the Footnotes and Bibliographies because the Author value is empty in sources from this Ancestry collection.
“N.p.” and “n.d.” notations in Footnotes and Bibliographies when a value for Publisher, Publish Place or Publish Date is empty.

Solution

Because the sources were imported via TreeShare, they are Ancestry Record type, i.e., they are created using the built-in Ancestry Record Source Template. Built-in Source Templates are uneditable through the RM user interface but are defined in the same table that holds user-defined templates. Thus, the built-in templates can be modified by using SQLite to edit entries in the SourceTemplateTable. We can address Issues #3 and #4 by modifying the Footnote and Bibliography sentence templates in the Ancestry Record template. That will be of benefit also to citations having empty values from some other Ancestry Collections (see Ancestry TreeShare – Impact).

Issues #1 and #2 are more challenging because the values of the source and citation variables that appear in the Footnote and Bibliography sentences are stored in a XML data structure. To solve #1, we want to “lump” all citations of a given newspaper Title under one Master Source. That requires that the data that differentiates the Master Sources for a common newspaper must be deleted or transferred from the Master Source to the Citation Details. For example, the Page # must be extracted from the Source Name in the SourceTable and moved to the Detail ([Page] variable in XML) for each Citation of that Source in the CitationTable. There are more steps than that alone for each of that one newspaper’s multiple Master Sources and Citations.

Once all the data manipulations are complete, there will be multiple identical Master Sources for a given newspaper Title. RM’s AutoMerge Sources function can finish the job.

Before/After Screenshots

The database undergoing modification was from RM7, hence the screenshots are of RM7. However, the solution also works with RM8 and RM9.

Before

Example of one source in the original database. Note that the Source Name and the Title variable (*italics*) are identical and contain the unwanted “Newspapers.com”, the title of the newspaper, the publish date and the page number. The [Page] variable at the Citation level contains description of the item of interest and the publish date (repeated from the [Title] variable). All three sentences have unwanted leading punctuation and white space.

A Master Source for each page cited from a given newspaper. This example of ‘extreme splitting’ of sources is perfectly acceptable for some users while, for others, the long Source List and report Bibliographies are objectionable and ‘lumping’ to one Master Source per newspaper is preferred.

Transition

After

Now just one *Arizona Republic* in the Source List instead of many individual Page #’s. In some cases such as the *Arizona Daily Star* at the top of the list, RM’s Source AutoMerge leaves two Master Sources that look identical and it is necessary to Manual Merge the two to end with just one. Despite fields looking identical in the Source Editor, AutoMerge compares the full XML strings of each source and there’s no match if the order of otherwise identical fields is different.

Download Scripts

Sources-NewspapersCom-LumpClean.sql Download

SourceTemplate-AncestryRecord-cleaned.sql Download

SourceTemplate-AncestryRecord-Reset.sql Download

Procedure

Backup your database in case you need to revert to it.
Open your database with a SQLite manager having RMNOCASE – faking it in SQLiteSpy or RMNOCASE – faking it in SQLite Expert, command-line shell et al and supporting the REGEXP_REPLACE() function.
Load and execute Sources-NewspapersCom-LumpClean.sql.
If the Ancestry Record source template does not have ” – cleaned” appended to it, load and execute SourceTemplate-AncestryRecord-cleaned.sql.
On returning to RM, run Rebuild Indexes in Database Tools.
In RM, open the Source List and run AutoMerge.
If you have two or so remaining sources for the same newspaper using the Ancestry Record template and you wish to have only one, use RM’s Manual Merge for Sources.
Repeat after you have added more Newspapers.com sources via TreeShare.

Notes

Should you have reason to revert the Ancestry Record source template to the format supplied by the application, load and execute in your SQLite manager SourceTemplate-AncestryRecord-Reset.sql, edited to find a RM database file of the same major version number to fetch the built-in format.
Should you upgrade or drag’n’drop to another database, the “Ancestry Record – cleaned” template will revert to the built-in format. Run step #4 on the target database to restore it.
The user reported that TreeShare does not report any change as a consequence of this procedure; it would seem to rely solely on the link to the Ancestry Record stored in the RM7 LinkAncestryTable (AncestryTable in RM8, RM9).
The procedures should work also on RM8 and RM9.
The main script is not what I would call ‘elegant’. It grew like Topsy as I explored the database and evolved the process through a sequence of building blocks. Someone cleverer than I with SQLite might well produce a better, faster version.