SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Direct Import of RM8 database into RM7 – Part 2

Posted on by Tom Holden

WARNING 2022-06-18: This script has been found defective in the handling of reused citations – see Comments at the bottom.

UPDATE 2022-06-12: a version of the script now imports the RM8 ConfigTable to RM7 as it appears to be compatible with RM7, so far.

Because the RM8 import or conversion of a RM7 files seems to preserve the RM7 ConfigTable records such as settings for a Book in Publisher, the return of the data to RM7 will include those, making the round trip from RM7 to RM8 and back transparent except for Research Items and Folders.

Because it is possible that the RM8 ConfigTable could become incompatible with RM7, both the earlier and the amended versions of the script are listed below.

UPDATE 2022-05-14: with one fix, there is a procedure in RM8 that would be more attractive operationally than using these outboard scripts:

RM8’s File > Export Data > DropBox creates a copy of the active .RMTREE file to a RM7 compatible .RMGC file intended for use with the RootsMagic Mobile app (now retired for Android). It is a complete conversion except, in my test, for Roles for shared events, which should be an easy fix for the developers. In my test, the RoleTable was empty which invalidates every shared event.

Moreover, that Export copies over the ConfigTable records which appear to include Custom Reports and Book Publisher settings that originated in the RM7 database which had been converted to RM8, thereby making the round trip from RM7 to RM8 back to RM7 pretty much intact, except for possible losses in the area of Research Logs and Folders due to structural changes and the hopefully-to-be-fixed Roles for shared events.

Summary

This enhanced script now transfers Ancestry TreeShare data and FamilySearch data from RM8 to RM7, seemingly transparently, and converts #RM8 Tasks and Folders to #RM7 To-Do’s and Logs with some changes due to the different structures for these areas of the two versions of database. It is now a complete data transfer, including between ConfigTables.

Direct import with the script is orders-of-magnitude faster than GEDCOM export-import, approximately 2 minutes from a 330MB RM8 database file, a few seconds for a small one. It’s possible to edit in RM8 and view the result in a report from RM7 as fast or faster than in a report from RM8!

Procedure

Same as for Direct Import of RM8 database into RM7 – Part 1 but with one of these scripts:

Import_RM8_to_RM7-Part2.sqlDownload

Above version does not import the RM8 ConfigTable; the amended version below does.

Import_RM8_to_RM7-Part2A.sqlDownload

Folders, and Logs, and Tags, and Tasks, and To-Do’s! Oh my!

Because of structural changes in these areas, I found it very difficult to understand fully how they relate and may not have succeeded. There is an element of irreversible conversion from RM7 to 8 that compromises what can come back because Folders and Logs are not identical. This is the least transparent part of the import and I won’t be surprised to hear of some problem with it.

Caveats

  • The script was developed and tested (not very fully) using SQLiteSpy 1.9.15 with an extension for a fake RMNOCASE collation between databases from RootsMagic versions 8.1.8 and 7.7 on Windows 10. At some point, RootsMagic 8 or its successor will change in a way that breaks the script.
  • It’s recommended to run the RM7 File>Database Tools>Rebuild Index on the target database after importing because the script applies a mismatching collation to the database table indexes, which may give rise to some erroneous behaviour in searching and sorting, more likely with alphabets other than Basic Latin.
  • I can keep the source database file open in RM8 and the destination database file open in RM7 and run the script in SQLiteSpy which reads the first and reads and writes the second. There is a risk that a database file may appear to be locked by one of the applications which may result in an error in RootsMagic for which the only recourse is to close the program. The SQLite manager may get around the problem without restarting by issuing the command END TRANSACTION. If the RM8 database (alias “RM8db”) is still ATTACHed to the main database (the target RM7 database alias “main”) then issue the command DETACH DATABASE RM8db.

Direct Import of RM8 database into RM7 – Part 1

Posted on by Tom Holden

WARNING 2022-06-18: This script has been found defective in the handling of reused citations - see Comments at the bottom of Direct Import of RM8 database into RM7 – Part 2.

Issue

As described in Returning to RM7.5 custom reports, groups and Publisher from RM8 via GEDCOM, there are reasons some users of RM8 would like to be able to transfer data to RM7 more directly than through GEDCOM as the medium is unsatisfactory. In addition to inherent losses identified in GEDCOM & DnD transfer losses, it involves more steps than one might have with a more direct approach.

Solution

Both RM7 and RM8 use similar SQLite databases so, obviously, a transfer of data through SQLite operations is the most direct means possible. However, "similar" is not "identical" and there are challenges with transferring Tasks, TreeShare, FamilySearch, Custom Reports and Report Settings and other File Settings, History, Bookmarks, et al. Therefore, this first part of a direct import solution addresses the low-hanging fruit and maybe that's where it will stop, depending on the difficulty of going further. Nonetheless, it may be adequate for many purposes and is being published now so that procedural problems it may have with certain databases may be identified through usage by more people.

Procedure

  1. In RM8, open and prepare your RM8 database file for export by running the set of File>Tools>Database Tools. Note the file's full path and name for entry into the SQLite script. Close the database.
  2. In RM7, create a new empty database or open the database you wish to overwrite (I'll leave it to you to backup or make a copy!). If an existing file, run the RM7 set of File>Database Tools. Note the file's full path and name so you can find it with your SQLite manager.
  3. Open the target RM7 database with your SQLite manager (one with a fake RMNOCASE collation - see #rmnocase).
  4. Open SQLite manager's SQL Editor on this database and load into it the script Import_RM8_to_RM7.sql.
  5. Edit this script to change the path and name in the ATTACH DATABASE line to that of your source RM8 database.
  6. Execute the script.
  7. On reopening the target RM7 database with RM7, run File>Database Tools.
  8. Media links that pointed to items below the Windows User's root folder will be broken as will those that were downloaded via TreeShare to a sub-folder of that containing the source RM8 database. RM8 uses symbols for those paths that RM7 does not recognise. Use the Media Gallery's Fix Broken Media Links tool or, much faster, Search & Replace (Ctrl+H) on Media filenames to fix them.

Considerations

  1. If you upgraded from a RM7 database to RM8 when your RM7 database had a well-developed Book specification or extensive Custom Reports, try importing the data from RM8 back into a copy of that database. You may be pleasantly surprised that those reports still work, even though you may have made many changes in your tree.
  2. This procedure transfers groups; GEDCOM does not.
  3. Color-coding suffers in translation because RM8 has 28 colours, 13 of which are outside the range for RM7 and those that are within do not all map to the corresponding color.
  4. I was 'lazy' with data typing despite there having been changes between the two versions. SQLite itself is also 'loose' with enforcement. For example, many fields in RM7 that were type BLOB became type TEXT in RM8. My prior experience with RM in the past was that it did not care when the content was textual so I've made no attempt to CAST these TEXT fields back to BLOB on import. Yet, it's possible it may give rise to some obscure error.
  5. I get a memory access error in citations using Find Everywhere on a file that originated in RM7 and imported back from RM8, yet there is no such error in the original nor in the RM8 upgrade. That's an obscure error not present in other files I've imported.
  6. Please let me know what errors you encounter, discoveries you've made, benefits you've realised..., probably best through the Forum, given its message editor is superior to the Comment editor.
  7. Good luck!

Returning to RM7.5 custom reports, groups and Publisher from RM8 via GEDCOM

Posted on by Tom Holden

UPDATE 2022-05-14: better than GEDCOM is to convert directly from RM8 to RM7 for which there are a couple of possibilities:

  1. RM8’s File > Export Data > DropBox creates a copy of the active .RMTREE file to a RM7 compatible .RMGC file intended for use with the RootsMagic Mobile app (now retired for Android). It is a complete conversion except, in my test, for Roles for shared events, which should be an easy fix for the developers. In my test, the RoleTable was empty which invalidates every shared event.
  2. Use one of my SQLite scripts that were developed subsequent to this original post:
    Direct Import of RM8 database into RM7 – Part 1
    Direct Import of RM8 database into RM7 – Part 2

Once the RM8 Export to DropBox is fixed, it would be operationally more attractive and is supported by RM Inc.

Issue

As of writing, still no Book Publisher feature in RM8 and along with other persistent issues, some users wish to return to the RM7.5 database from which they had ‘upgraded’. However, the only way back using the RM user interface is via #GEDCOM which, along with other attendant losses (see GEDCOM & DnD transfer losses) does not import #namedgroup and custom #reports. The original #RM7 database contains the custom reports and book specifications and possibly useful groups as they existed at the time of committing to the upgrade but importing into it results in an overload of duplicates, as described by PatrickR in his Apr 9 post to the RootsMagic User Community: Is there a way to copy custom reports to another database?

A solution

Years ago, I had an approach to creating a quasi-empty shell database file from a populated database that I thought might be adapted to this particular case: Depopulate but keep Customs, Places, Sources. Maybe a variant that depopulated but instead kept groups, custom reports, and book definitions could be used to import the GEDCOM from RM8 to good effect. I’m pleased to report that my experiment was successful, to the extent that GEDCOM transfer allows.

Procedure

  1. Review GEDCOM & DnD transfer losses and, in your RM8 database, ensure that your Fact Types are all set to export to GEDCOM. Run the RM8 database tools and Export the database having chosen to export everyone and everything.
  2. In RM7, make a copy or backup of the original database from which the RM8 ‘child’ was created. Run the RM7 database tools. Close the RM7 database file.
  3. Open the RM7 database file with your SQLite manager (one with a fake #RMNOCASE extension), load and execute this SQL file:
Depopulate-RM7_5.sqlDownload
  1. Reopen the now-depopulated database file with RM7.
  2. Run the RM7 Database Tools.
  3. Import the GEDCOM file from RM8 choosing the option
    Preserve record numbers
  4. Done! Review what you’ve got. Note that the Media Gallery has to build thumbnails anew.

Possibilities

It should be possible to transfer data more completely by directly copying data from the RM8 database to the RM7.5 database via SQLite. However, because of changes in database structure, it’s not straightforward for several tables. Also, there may be serious incompatibility between the ConfigTable content of the two versions which may preclude the carryover of custom reports and other settings.

Colorcoding #RM8 #RM9

Posted on by Tom Holden

RM9 Color Picker Changed & Color Coding Sets Added

Versions prior to RM9 have but one set of color codes; RM9 added 9 more sets for a total of 10, any one of which can be active. Each color in each set and each set can be uniquely labelled to make it easier to understand what the colors mean; the labels appear in the Tool Tip when the cursor is hovered over a color block in some of the main views. The RM9 colors, color names and database color numbers are identical to those of RM8.

Databases migrated from prior versions will have their one color coding set preserved in RM9’s “Color code set 1” which is the column named “Color” in PersonTable. “Color code set 2” is confusingly stored in the PersonTable.Color1 column… and “Color code set 10” in column “Color9”.

The color picker has changed from the grid of RM8 to a list, due probably to the enhancement to custom label each color. Unfortunately, that undermines the perception of color families presented by the RM8 grid and potential color selection strategies based on the grid as discussed below. The labels, however, likely outweigh those considerations.

RM8 Color Picker Chart with Names & Codes

Colors available in RootsMagic 8 color coding, labelled with names and database code numbers.

The above chart is an adaptation of a file posted by Lynn Christiansen to facebook Dec 31, 2021. He took a screenshot of the RM8 Color Coding window color picker and labelled each patch with the name. I’ve added the Color Number to each corresponding to the value the program assigns to the Color field for a row in the PersonTable.

RM8 Colors Sorted by Name and Code

The following table images provide ready lookup of color samples by the Color Code number used in the RM8 database and by the label in the RM8 Color Coding color picker.

RM8 Color ‘Families’

Starting with the diagonality of the RM8 Color Picker chart for the gray colours (Blank or no colour, Silver, Grey, Slate) there appears to be more or less parallel diagonalities which we might call colour families, naming them by the highest member on the chart. For example, the Mauve family comprises also Purple and Aubergine; the Aqua family includes Azure, Blue, and Navy. In the following overlay of the Color Picker, each member of a family is framed by the colour of the uppermost member and has a line connecting its centre with the other members. Hope that’s clear! Families of RM8 Colors

RM8 Color Selection Strategies

How colours will appear in isolation or in juxtaposition with other colours can be tricky. Whether it is text or filled cell or both that is coloured is another variable. Saturation and brightness contrasts are factors to consider, especially for text on a white background and especially when printed in grayscale.

Text

The following images are from a screen capture of a PDF saved from the RM8 Individual List report. The database contains only ‘people’ with the names of the colours from the RM8 Color Picker in their Surname and the database color number for the Given. The RIN corresponds with the color number except for Color 0 (Blank fill or Black text) which is RIN 28. The grayscale image was derived by a conversion of the colour screenshot to “black & white” in Paint.net.

Avoid most of  the top two rows in the RM8 Colour Picker for coloured text on white or light backgrounds; Blank (black text) and Red are good.

Fill

The following two images are from a screen capture of an Excel spreadsheet which computes the approximate brightness of a color’s RGB values using the formula:
brightness = (red / 255.0) * 0.3 + (green / 255.0) * 0.59 + (blue / 255.0) * 0.11

Perceived brightness will vary with monitor, printer and viewer and, in the case of conversion to grayscale, the conversion software. The grayscale image was converted from the colour image by Paint.net’s “black & white” Adjustment tool which may calculate brightness with a different algorithm. If you perceive some colour’s grayscale to be out of order, it may be due to any one or any combination of these variables. RM8 Color Codes by approx brightness RM8 Color Codes by brightness – grayscale

Juxtapositions

  • because some adjacent colors in the picker are hard to distinguish (e.g., Lemon-Chartreuse; Mint-Aqua; Maroon-Brown; Olive-Forest-Teal) do not follow a horizontal pattern through the Color-Picker for adjacent generations. However, it might work for a couple…
  • the diagonals (down 1, right 2 like a knight’s move in chess) shown through each colour family could work well for juxtaposed colours
  • the opposing diagonal would contrast well (e.g., Aqua-Orange-Blue-Khaki) (down 1, left 2)
  • diagonals of -45 degrees or down-right are a bit weak around Chartreuse-Yellow and Lime-Green; diagonals of -135 degrees or down-left avoid those issues
  • vertical trails through the colour picker seem to be distinctive, e.g., start at the top of a column, working down to the bottom and then to the top of the next column.

Migration of RM4-7 Color Codes to RM8

RM8 expanded the number of color selections to 28 (codes 0-27) from 15 (codes 0-14) in earlier versions. RM7 databases imported into RM8 retain their original color code numbers but the expanded range results in some name changes and color shifts in order to accommodate the extra colors. For example, Fuschia (4) is no more and is replaced by Pink. The following table compares the colors from RM7 with those in RM8 having the same code number. Note that the switch from coloring text in RM7 to a colored block in RM8 certainly necessitated that code 0 change from black to blank (or no color). RM7 colors and the RM8 colors having the same code number.

Naming Citations in Preparation for RM8 #RM7 #Citations

Posted on by Pat Jones

In RM8, citations are re-useable and have a name which is made by concatenating the contents of the citation fields.
So I run this script on my RM version 7 database so that it is ready for importing into RM version 8.

I have taken advantage of this concatenation to get meaningful names by inserting a new field in my own templates as the first one in a citation and then setting the value in the citations to the source name.

This will only work for user templates, not for supplied ones (SourceTable.TemplateID > 10000)

/* update citations for RM8
 add in new field - CitItemD for citation name and put in source name - creates good citation name in RM8
*/
UPDATE SourceTemplateTable
SET (FieldDefs) = cast(Replace(cast(FieldDefs as Text),'<Root><Fields>','<Root><Fields><Field><FieldName>CitItemD</FieldName><DisplayName>Item Name</DisplayName><Type>Text</Type><Hint>Item Name</Hint><LongHint>To distinguish citations</LongHint><CitationField>True</CitationField></Field>') as blob)
WHERE 
TemplateID > 10000;

-- or this could be TemplateID = a specific template number to just change one template

-- add extra field for name - at the start so it is used for citation name when converting to v8
UPDATE CitationTable 
SET (Fields) = (SELECT cast(Replace(cast(CitationTable.Fields as Text),'<Root><Fields>','<Root><Fields><Field><Name>CitItemD</Name><Value>' || SourceTable.Name ||  '</Value></Field>') as blob) 
                                FROM  SourceTable 
                                WHERE SourceTable.SourceID = CitationTable.SourceID )
WHERE
    EXISTS (
       SELECT *
       FROM SourceTable 
        WHERE (SourceTable.SourceID = CitationTable.SourceID) 
AND SourceTable.TemplateID > 10000
-- or this could be TemplateID = a specific template number, to just change one template
   );
Add-name-field-to-citations-RM7Download

Citation ‘Uses’ and Duplicates #rm8 #citations #duplicates

Posted on by Tom Holden

RootsMagic 8 introduced indirection between citation data (text fields) and the fact to which a citation is attached, In RM7, the citation data and linkage was combined in the CitationTable. To use the same citation data for more than one fact meant that it had to be replicated in another record, making revisions to the data repetitious and onerous. RM8 adds the CitationLinkTable which allows one set of citation data to be linked to or ‘used’ by multiple facts. The one set of citation data then becomes a ‘master’ citation whose edits then carry through to all ‘uses’ of it, just as revisions to a Master Source affect all citations of it in all versions of RM. This is an example of improved database normalisation.

Concomitant with this change is an added feature “Merge All Duplicate Citations” whose effect is to replace all records in the CitationTable having the same Master Source and matching text fields (Citation Details) with one record linked via the CitationLinkTable to all the facts that its duplicates were previously attached. RM7 has no equivalent.

However, as of RM8 Preview 7.9.310, “Merge All Duplicate Citations” can result in duplicate ‘uses’ of the merged or master citation where there were previously duplicate but independent citations linked to the same fact. Outputs, such as reports, will still show duplicate footnotes for the fact. In this case, “Merge All Duplicate Citations” fails to deliver the desired (and logically expected) result. The problem was raised and illustrated with screenshots in a 2021-07-19 post by Tom Holden to the RootsMagic 8 Community Preview Users facebook Group.

At the time of writing, there is no provision in RM8 to automatically or manually delete duplicate uses of citation. The following SQL script does the job and shows that it should be a pretty trivial addition to “Merge All Duplicate Citations” requiring no change to the User Interface.

DeleteDuplicateCitationLinks-RM8.sqlDownload

Problems with Email Distribution #admin

Posted on by Tom Holden

Please excuse the recent behaviour of this WordPress site. You have likely heard nothing by email from it since March 2 until yesterday and then some of you may have received flurries of duplicate messages. New subscribers probably received no confirmation email nor password reset notices. This post may not be delivered to subscribers. I’m working on correcting the problem but there seem to be multiple issues and it is proving difficult to isolate the causes:

  • starting March 2, every notice failed with an error “Could not instantiate mail function”
    • appears to have been resolved, more or less, by adding a second MX record and revising the priority of the first in the sqlitetoolsforrootsmagic.com domain – that just seems weird
  • resending notices from the WP Mail Logging plugin has revealed a bug causing them to multiply; the plugin is no longer actively supported
  • resending notices such as comments on blog posts fail because the address (From:) wordpress@sqlitetoolsforrootsmagic.com is “invalid”, despite being valid until March 2; must be an issue with the Hostinger mail server. Resending notices from the Forum uses a different, valid (From:) address (but get multiplied)
  • phpmail() function is said to be unreliable; Hostinger is recommending the use of a SMTP plugin which I am looking into.

I may lose the log of blocked notices or never get to resending ones that should have gone to you, such as password resets.

Understanding the RootsMagic 8 database – Type Decodes #rm8 #datadefinitions

Posted on by Pat Jones

Many tables within RootsMagic have fields which are know to mean something but do not have lookup tables. So far I have collected the following: (more to follow)

Fields named OwnerType – see Understanding the RootsMagic 8 database – Ownership

Addresses

AddressType field

ValueMeaning
0Correspondence
1Repository

Citations

The Quality field in the Citation Link table is three characters, each character having meanings.

Character PositionValueMeaning
1PPrimary Information Quality
1SSecondary Information Quality
1~Unknown Information Quality
2DDirect Evidence Quality
2IIndirect Evidence Quality
2NNegative Evidence Quality
2~Unknown Evidence Quality
3OOriginal Source Quality
3XDerived Source Quality
3~Unknown Source Quality

Multimedia

Field MediaType defines the media format of the file

ValueMeaning
1Image
2File
3Sound
4Video

Names

The NameType field gives the type of the name

ValueMeaning
0undefined/any
1aka
2birth
3immigrant
4maiden
5married
6nickname
7other spelling

Places

PlaceType field

ValueMeaning
0Place
1LDS Temples (Built In)
2PlaceDetail

Tags

TagType field defines what is being tagged. This equates to the label table in RM7

ValueMeaning
0Group
1Task Folder

Tasks

TaskType field – this has changed from RM7

ValueMeaning
1Research Log/ Task Folder
2ToDo
3Correspondence

TaskStatus field – some changes in RM8

ValueMeaning
0New
1Open
2Completed
3Pending – On Hold
4Problem
5Cancelled

Understanding the RootsMagic 8 database – Ownership #database #datadefinitions #rm8

Posted on by Pat Jones

The RootsMagic 8 database structure has seen a few changes from RM7 and one of these is that more objects can “own” other objects in the data. A task can have media, citations and web tags for example.

To achieve this many tables have an OwnerType field and an OwnerID field and the owner type is consistent so for example 0 in owner type always means the owner is a person and the owner id is the person record number (RIN).

In order to write code against this structure I have produced the following chart where items in the left hand column can be the owner and the items across the top are owned.

Owner Type ValueOwner TypeCan Own URLCan Own PlaceCan Own Place DetailCan Own MediaCan Own TaskCan Own AddressCan Own CitationCan Own NameCan Own ChildCan Own Event
0PersonY  YYYYY Y
1Family   YYYY YY
2Event YYYY Y  
3SourceY  Y YY  
4CitationY  Y     
5PlaceY YYY    
6TaskY  Y  YY  
7Name   YY Y  
8not used (was General task in RM7 with 0 ownerid)         
14Place DetailY  YY    
15not used (was Research Item in RM7)         
18Task Folder (update of Research Log)    Y    
Ownership table – RM8

As a further note:
When it says “Tags” in RM8 application it refers to Owner Links which can be edited at this point in the program

When it says “Used” in RM8 application it refers to Owner Links but they are not editable at this point in the program

When it says “Web Tags” in RM8 application it refers to URL

Database Diagram for RM8