SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

RM Database Design #datadefinitions #database

Posted on by Tom Holden

RootsMagic 4+ Database Design

You will need to know a little about the RootsMagic database to design your own queries and modify others to suit your needs. A picture is worth many words so here is a series of Entity Relationship Diagrams by different authors:

RM9 Database Diagram #rm9 #database

Roots Magic 8 Database – Database Diagram

Entity Relationship Diagram for RootsMagic v7.5+ by Bill Girimonti, forum 2019-09-21

The following dig deeper into details but are not necessarily updated to the latest version:

File Typesinstalled and created by RootsMagic 4+
each .rmgc database fileTable Summariesrole of each table in the database
Database System Catalogtables and indexes along with SQL commands for their creation
Data Definitionsfield names and types, indexes, and keys from above in tabular form, expanded with typical values and notes on each field
Fact Typescontent of fresh facttypetable
Role Typescontent of fresh witnesstable
RootsMagic.stSource Templatesexternal file that defines built-in Citation Source templates in sourcetemplatetable
databasename.DUPDuplicate Search Merge Databasetemporary database with same filename as .rmgc file, created during Duplicate Search Merge

Database Documents

Version Monitoring: RM4 RM5 RM6
Database System Catalog: RM4 RM5 RM6
The following contain all versions, more or less.
Table Summaries
Data Definitions
Fact Types
Role Types
Source Templates
ConfigTable RecID 1
Duplicate Search Merge Database
Most of the spreadsheets used in the above pages are from the workbook: Database Design Spreadsheets. Refer to to it for latest updates.

RM6 Version Monitoring #database #datadefinitions

Posted on by Tom Holden

The database documentation was done largely on RootsMagic version 6.0.0.2. It’s unlikely that the database schema will change through successive updates. However, any changes may be detected by opening a database created by each version with a SQLite manager, then exporting either a query of the full sqlite_master table or the database schema (just the SQL field of that table wrapped in a transaction) and comparing the exports from each version using a text editor that has a compare function (e.g. Notepad++).

The page RM6 Database System Catalog displays the full sqlite_master table from 6.0.0.2.

For future comparisons, below is the schema from 6.0.0.2. The differences from that shown for 5.0.0.6 in RM5 Version Monitoring are the addition of the ResearchTable and the URLtable.

BEGIN TRANSACTION;
CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB );
CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, Sex INTEGER, EditDate FLOAT, ParentID INTEGER, SpouseID INTEGER, Color INTEGER, Relate1 INTEGER, Relate2 INTEGER, Flags INTEGER, Living INTEGER, IsPrivate INTEGER, Proof INTEGER, Bookmark INTEGER, Note BLOB );
CREATE TABLE FamilyTable (FamilyID INTEGER PRIMARY KEY, FatherID INTEGER, MotherID INTEGER, ChildID INTEGER, HusbOrder INTEGER, WifeOrder INTEGER, IsPrivate INTEGER, Proof INTEGER, SpouseLabel INTEGER, FatherLabel INTEGER, MotherLabel INTEGER, Note BLOB );
CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB );
CREATE TABLE EventTable (EventID INTEGER PRIMARY KEY, EventType INTEGER, OwnerType INTEGER, OwnerID INTEGER, FamilyID INTEGER, PlaceID INTEGER, SiteID INTEGER, DATE TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, STATUS INTEGER, EditDate FLOAT, Sentence BLOB, Details BLOB, Note BLOB );
CREATE TABLE AddressTable (AddressID INTEGER PRIMARY KEY, AddressType INTEGER, Name TEXT COLLATE RMNOCASE, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT, Zip TEXT, Country TEXT, Phone1 TEXT, Phone2 TEXT, Fax TEXT, Email TEXT, URL TEXT, Latitude INTEGER, Longitude INTEGER, Note BLOB );
CREATE TABLE FactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER );
CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB , Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, DATE TEXT, SortDate INTEGER, Description BLOB);
CREATE TABLE MediaLinkTable (LinkID INTEGER PRIMARY KEY, MediaID INTEGER, OwnerType INTEGER, OwnerID INTEGER, IsPrimary INTEGER, Include1 INTEGER, Include2 INTEGER, Include3 INTEGER, Include4 INTEGER, SortOrder INTEGER, RectLeft INTEGER, RectTop INTEGER, RectRight INTEGER, RectBottom INTEGER, Note TEXT, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, DATE TEXT, SortDate INTEGER, Description BLOB );
CREATE TABLE NameTable (NameID INTEGER PRIMARY KEY, OwnerID INTEGER, Surname TEXT COLLATE RMNOCASE, Given TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE, Nickname TEXT COLLATE RMNOCASE, NameType INTEGER, DATE TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, EditDate FLOAT, Sentence BLOB, Note BLOB, BirthYear INTEGER, DeathYear INTEGER );
CREATE TABLE PlaceTable (PlaceID INTEGER PRIMARY KEY, PlaceType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, Normalized TEXT, Latitude INTEGER, Longitude INTEGER, LatLongExact INTEGER, MasterID INTEGER, Note BLOB );
CREATE TABLE ResearchTable (TaskID INTEGER PRIMARY KEY, TaskType INTEGER, OwnerID INTEGER, OwnerType INTEGER, RefNumber TEXT, Name TEXT COLLATE RMNOCASE, STATUS INTEGER, Priority INTEGER, Date1 TEXT, Date2 TEXT, Date3 TEXT, SortDate1 INTEGER, SortDate2 INTEGER, SortDate3 INTEGER, Filename TEXT, Details BLOB );
CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, FIELDS BLOB );
CREATE TABLE CitationTable (CitationID INTEGER PRIMARY KEY, OwnerType INTEGER, SourceID INTEGER, OwnerID INTEGER, Quality TEXT, IsPrivate INTEGER, Comments BLOB, ActualText BLOB, RefNumber TEXT, Flags INTEGER, FIELDS BLOB );
CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT );
CREATE TABLE WitnessTable (WitnessID INTEGER PRIMARY KEY, EventID INTEGER, PersonID INTEGER, WitnessOrder INTEGER, ROLE INTEGER, Sentence TEXT, Note BLOB, Given TEXT COLLATE RMNOCASE, Surname TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE );
CREATE TABLE LinkTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, STATUS INTEGER, Note BLOB );
CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT );
CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER );
CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER );
CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB );
CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT );
CREATE TABLE ResearchItemTable (ItemID INTEGER PRIMARY KEY, LogID INTEGER, DATE TEXT, SortDate INTEGER, RefNumber TEXT, Repository TEXT, Goal TEXT, SOURCE TEXT, RESULT TEXT );
CREATE TABLE URLTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, OwnerID INTEGER, LinkType INTEGER, Name TEXT, URL TEXT, Note BLOB );
CREATE INDEX idxRecType ON ConfigTable (RecType);
CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID);
CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID);
CREATE INDEX idxChildID ON ChildTable (ChildID);
CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID);
CREATE INDEX idxChildOrder ON ChildTable (ChildOrder);
CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType);
CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate);
CREATE INDEX idxAddressName ON AddressTable (Name);
CREATE INDEX idxFactTypeName ON FactTypeTable (Name);
CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev);
CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag);
CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile);
CREATE INDEX idxMediaURL ON MultimediaTable (URL);
CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID);
CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption);
CREATE INDEX idxNameOwnerID ON NameTable (OwnerID);
CREATE INDEX idxSurname ON NameTable (Surname);
CREATE INDEX idxGiven ON NameTable (Given);
CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear);
CREATE INDEX idxNamePrimary ON NameTable (IsPrimary);
CREATE INDEX idxPlaceName ON PlaceTable (Name);
CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev);
CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID);
CREATE INDEX idxResearchName ON ResearchTable (Name);
CREATE INDEX idxSourceName ON SourceTable (Name);
CREATE INDEX idxCitationSourceID ON CitationTable (SourceID);
CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID);
CREATE INDEX idxWitnessEventID ON WitnessTable (EventID);
CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID);
CREATE INDEX idxLinkRmId ON LinkTable (rmID);
CREATE INDEX idxLinkExtId ON LinkTable (extID);
CREATE INDEX idxRoleEventType ON RoleTable (EventType);
CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name);
CREATE INDEX idxLabelType ON LabelTable (LabelType);
CREATE INDEX idxResearchItemLogID ON ResearchItemTable (LogID);
CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2);
COMMIT TRANSACTION;

Groups – Extract most everything for one to a new database #namedgroup #TreeShare

Posted on by Tom Holden

RootsMagic GEDCOM and Drag’n’Drop transfer capabilities and performance leave some things to be desired. Its own File Compare tool shows that differences crop up from the loss of trailing blanks from Notes. Other things are not transferred at all, such as custom default sentences for built-in fact types and roles. The introduction of Ancestry TreeShare motivates one to break out a subset of people from a TreeShared database to work on. I’ve long thought that a direct database to database transfer should be possible and would be far better than going through the machinations and limitations of GEDCOM, which DnD uses behind the scenes. This is an attempt to do just that, a demonstration of a capability that I wish was incorporated in the RootsMagic program.

The main script copies everything pertaining to a named group in an originating database to a new, empty database. Record numbers are preserved (which is why the target database must be empty).

Procedure

  1. You need SQLite Expert Personal or equivalent with a fake RMNOCASE collation extension AND support for run-time parameters (a prompt for the GroupID else edit the SQL statement)
  2. In RootsMagic and in the originating or master database, create a named group that includes all the people you wish to copy over to the target database.
  3. In RM, create a new empty target database and record its full path and name.
  4. Close these databases in RM to avoid any possible conflict with the next steps.
  5. Open the master database with your SQLite manager and leave it open for all the following SQLite steps.
  6. Load and run this statement to find out the GroupID for that named group:
-- Find the GroupID for your Group
SELECT DISTINCT LabelName AS GroupName, GroupID
FROM GroupTable JOIN LabelTable ON GroupID=LabelValue AND LabelType = 0
ORDER BY GroupName;
  1. Load and run this statement to inform the script below which Group to copy.
-- Store that GroupID in a temp table
DROP TABLE IF EXISTS zGroupIDReg
;
CREATE TEMP TABLE zGroupIDReg
AS
SELECT LabelValue AS GroupID, LabelName
FROM main.LabelTable
WHERE LabelValue = $GroupID -- for SQLiteSpy, replace $GroupID with the desired GroupID number;
  1. Load and edit this statement to change the path and name between the quote marks to those of your target database:
ATTACH "C:UsersTomDocumentsFamilyTreeRM7Thomas Mailing families group extract.rmgc" AS Z;
  1. Load and execute this SQLite script. Group-Extract.sql You will be prompted to enter the GroupID you noted earlier unless you replaced $GroupID with the actual number. Sit back and wait for the message of success (or an error!).
  2. Close the master database in the SQLite manager to avoid conflict with the RootsMagic program.
  3. Reopen the target database in RootsMagic and explore.
  4. Try File Compare against the master database – the people in common should perfectly match except for spouses omitted from the group and File Compare overstating mismatches when there are multiple Alternate Names.

Benefits

  • similar to other reasons to extract a subset of a database:
    • smaller, more manageable
    • for upload to a website
    • for sharing with someone
    • for a higher privacy level than for the master
  • perfect replication, unsullied by GEDCOM transformations
  • complete replication; includes related:
    • Tasks,
    • Research Logs,
    • Not a Problem List,
    • customised built-in Fact Type and Role sentences,
    • custom Source Templates,
    • Addresses,
    • Repositories,
    • Correspondence,
    • File Settings,
    • Report Settings,
    • Custom Reports
    • TreeShare and FamilySearch connections
    • et al
  • copy multiple Groups (one by one) from the master to the target (provided the latter has not been edited) without duplication
  • script can be modified to expand or contract data to be copied
  • very fast

Search – wayfinding from data tables to RootsMagic screens #database #fts #search #rm8

Posted on by Tom Holden

Update 2021-03-11: added version by Pat Jones for RM8.

This page brings together a set of scripts that:

  • adds ‘friendly’ Waymarks in views of each database table to aid in navigating through RootsMagic to the responsible screen and to help understand the role of each table
  • builds a Full Text Search table, first described on the page Search – Find Almost Everywhere
  • launches a Full Text Search with results combining the ‘friendly’ Waymarks and a SQLite statement that opens the table row with the matching cell for possible editing
Waymarks.PNG
Sample of WitnessWay, the temporary view of the WitnessTable with Waymarks added to facilitate navigation to the sharee in RootsMagic.
Each RootsMagic table is replicated in a temporary View named the same except ending with “Way” instead of “Table”. Hence, “WitnessWay” is the wayfinding view of “WitnessTable”. To the right, in this screenshot snippet of the SQLite Expert sidebar, we see:

  • the last three RootsMagic tables, below which are
  • the temporary virtual table for Full Text Search and
  • a one record temporary table that stores the Search Term you enter. Below these are
  • the first few wayfinding views, which, when opened, will show the Waymarks as the first column, followed by all the columns of the table being viewed, as in the WitnessWay screenshot above.

The Full Text Search query returns:

  • a snippet of the text with the match to the search term(s) highlighted by the double-asterisks (**),
  • the Waymarks to help find the snippet in RootsMagic,
  • the field names included in the block of text in which the match was found plus
  • a simple SQL query.

This query can be copied to the SQL editor and executed; then the SQLite Expert editing tool can be used to edit these fields and others in the database table it opens.

WayfindViews.PNG
Snippet from SQLite Expert sidebar showing the Wayfinder views.
Wayfinder_FTS_LocateQuery.PNG
Sample result from the Full Text Search on the term “clerk”.
WaymarksFollowed.PNG
Waymarks for result #3 in the search were followed to this screen snip from the RootsMagic Place List.

Hopefully, the Waymarks list is self-explanatory and can be easily followed through RootsMagic. Editing the database is more safely done in RootsMagic but there are circumstances which can only be addressed through SQLite or the user may prefer doing through SQLite. You are reading this because there is something you want to do with your RootsMagic database that you cannot do with RootsMagic itself!

LocateQuery.PNG
The LocateQuery for search result #3 was copied and executed giving this screen. The fields without the “_1” suffix can be edited (take all necessary precautions). With SQLite Expert, right-click on the field and select Text Editor.
EditTextBlob.PNG
Note and other fields designed for large amounts of text are stored as binary BLOBs and are so indicated by the … icon. The SQLite Expert text editor works with them, too.
SQLiteExpertEditingControls.PNG
The Post Edit button is key to affecting the database.

If you do want to edit your RootsMagic database with SQLite Expert, please read its Help pages under the topics:

  • Editing table data
  • Editing live queries

Always run the SQLite Database Tools before and after you make changes externally and make a backup before, too.

While there are several ways to start an edit, making the changes stick in the database requires a click of the Post edit button, enabled when a change has been made to an onscreen field.

Downloads

ScriptHistorySQLite Requirements
1aRM8_WaymarksViews.sql
Creates the Wayfinding views of the RM8 database tables		2021-03-11 added by Pat Jones for #RM8RMNOCASE
1bRM7_5_WaymarksViews.sql
Creates the Wayfinding views of the RM7.5 database tables.		2017-07-01 extended for RM7.5 and corrected bug for null spouse
2019-02-12 corrected omission of famiies from EventWay and dependent views		RMNOCASE
2FindAlmostEverywhere-Build_FTS_table.sql
Creates the FTS4 virtual table xFTStable for full-text searches.		RMNOCASE, FTS4
3FindAlmostEverywhere-Snippet_Waymarks_LocateQuery.sql
Stores user inputted search terms in xSearchTerm,
searches xFTStable and returns snippets, Waymarks,
field names and the SQL queries to examine the matching
records.		RMNOCASE, FTS4, runtime-parameters
SQLite Expert Personal qualifies

Usage

Script #3 is dependent on the execution of both #1 and #2 preceding its running. #1 and #2 are independent of any other script.

On very large databases, scripts 2 and 3 may consume tens of seconds. In one example 40 and 30 seconds, respectively. However, RootsMagic’s Find EveryWhere took over 180 seconds for the same search and does so for each new search. Script 2 need not be re-executed for each new search by script 3 so there is a distinct time advantage.

All the views and tables created by these scripts are temporary, in-memory and are destroyed when the SQLite manager is closed.

Places – Count People and Events for a Group #places #statistics #events #namedgroup

Posted on by Tom Holden

This page presents a family of scripts that:

  1. Creates a SQLite View of Standardized Place names in reverse order N.B.: The Standardized Place field name is [Normalized] and the value is empty unless the Place has been successfully geocoded or the field manually entered. To view the values of the Place [Name] field in reverse order, revise the PlaceReverse.sql script to replace all instances of "Normalized" (sans quotes) with "Name" (sans quotes).
  2. Lists Places used by the events of a Group of people and the quantity per Place of:
    • People
    • Events

An inquiry on Facebook stimulated the development of these scripts.

This first script must be executed before the subsequent ones as it creates a set of SQLite Views used by them.
PlaceReverse.sql
The names of its Views are: xPlaceCommaView, xPlacePartsView, xPlaceReverseView in the order created, each building on its predecessor.

The following scripts are independent of each other but depend on the Views created by PlaceReverse.sql:
Places-PeopleCount.sql Creates the View xPlacePersonView and then runs a SELECT query to produce the desired list.
Places-EventCount.sql Creates the View xPlaceEventsView and then runs a SELECT query to produce the desired list.
In both of the above scripts, there is the requirement to edit the GroupID number as instructed in the Comment. You could create a Group including Everyone in the database or any subset.

Places-PeopleCount.sql.png

Source Templates – Convert Builtins to Editable, Split, Import #sourcetemplates

Posted on by Tom Holden

Prior to RootsMagic 7’s new Import Lists feature, there was no way from within the program to import custom Source Templates from one database to another except through a laborious, one-at-a-time export to a .rmst file which you would then import, one-at-a-time. Or, alternatively, you would create a source against each custom template for a dummy person whom you would then drag’n’drop to the target database – a not very intuitive procedure. The 413 built-in templates are uneditable so you would have to copy them one at a time to make them editable. A workaround was developed that stored all the built in template definitions in one RootsMagicSTuser.rmst file which, when imported, created user-editable copies in the target database. Because this is a plain text XML file, revisions could be made with a text editor, especially with one intended for XML editing. Thus a user could maintain a master custom template .rmst file for import into new databases.

Import Lists made the transfer of custom source templates much easier by skipping the singular .rmst export/import process. Now it is possible to maintain a database of custom source templates, even Master Sources, and import them directly into another database. This page presents:
1. A script that creates editable copies of the 413 built-in source templates in any RootsMagic database:
SourceTemplates_CopyBuiltin2Editable.sql

2. A new database to which the script has been applied, for those who do not want to use SQLite:
SourceTemplatesEditable-normal-2017-04-19.rmgb

3. A script that converts editable (custom) source templates into ultra-split templates (no fields in Source Details):
SourceTemplateExtremeSplitConvert.sql N.B.: WARNING – use this on unused templates; it does not convert existing citations and master sources. For scripts that convert existing sources and the templates they use to ultra-split, see Sources – Adventures in Extreme Splitting.

4. The database from #2 to which the ultra-splitter has been applied:
SourceTemplatesEditable-ultrasplit-2017-04-19.rmgb

SourceTemplatesEditable-normal.PNG
Builtin source templates converted to user-editable templates with splitsbetween Master Source level and Citation or Source Details level unchanged.

All of the standard source templates are copied and fully customisable.
Use this file as a starting point for a new database

OR,

on an existing database,

use File > Import Lists > Source Templates to import the user-editable source templates

OR

compose a draft source in it using a custom template and copy the resulting sentences for Footnote, Short Footnote and Bibliography to a Free Form source in your working database.
These user-editable copies are split at the same level as the built-in ones.

SourceTemplatesEditable-ultrasplit.PNG
Builtin source templates converted to user-editable and ultra-split.

All of the standard source templates are copied and fully customisable. These user-editable copies have all fields in the Master Source and none in Source Details, corresponding to ultimate splitting of sources, a surefire way for footnotes to migrate unsullied through GEDCOM and transfer to FamilySearch Family Tree (and, very likely, through the pending TreeShare with Ancestry).
Use this file as a starting point for a new database

OR,

on an existing database,

use File > Import Lists > Source Templates to import the user-editable source templates

OR

compose a draft source in it using a custom template and copy the resulting sentences for Footnote, Short Footnote and Bibliography to a Free Form source in your working database.

Sources – Adventures in Extreme Splitting #sources #sourcetemplates #replace

Posted on by Tom Holden

This page brings to bear some SQLite tools in support of Jerry Bryan’s discussion in the RootsMagic forums titled Adventures in Extreme Splitting. In summary, the concept is that every unique citation is a Master Source, i.e., all data fields are to be found in the Master Source, none under Source Details. Jerry’s observations are that RootsMagic behaves better as a result in these respects:

  • templated sources export to standard GEDCOM and thus to third-party software and websites with better integrity. See Ultimate Splitting of Sources for Better GEDCOM from RootsMagic (PDF)
  • sources transfer more completely to FamilySearch Family Tree (RM does not transfer Research Notes and Detail WebTags, only Source Text and first Master WebTag so this procedure moves Research Notes and Detail Web Tags to the Master Source fields)
  • easy duplication
  • one page to edit all citations of a source instead of one page per citation, if the change would otherwise be at the conventional Source Details level.
  • one set of images per source which covers all citations of the source
  • a complete source can be created in the Source Manager

A disadvantage is that the Bibliography may be verbose, becoming just an alphabetical sort of the complete endnotes, without duplicates and there is little opportunity to benefit from succinct Short Footnotes.
[Inline comment:

external image user_none_lg.jpg ve3meo Jun 18, 2013

This bears some scrutiny. True if the Bibliography sentence is the same as the footnote but not so if the Bibliography sentence template uses a small subset of the source fields as do many of the built-in templates.
]

Convert Existing Sources and Citations

This query converts all sources and citations and the source templates they use to extremely split sources. It’s a work in progress – feedback invited. The current version converts lumped source templates, sources and citations to extremely split duplicates for review against the original “lumpy” sources. The new split source names are prepended with the carat character (^) to the original source name and appended to it are the name of the person to whom the citation belongs followed by the fact type in parentheses and, conditionally another name for family and alternate name facts. Images, WebTags, Repositories et al are now remapped. This is highly experimental so, of course, you will not want to run it against your working database without the appropriate precautions!!! Try it and see what you get.

  1. May be advisable to run Delete Phantoms and Source Templates – Merge Duplicates first.
  2. Sources-CreateExtremelySplitFromCitations.sql Stop here to review the new “ultimate split” sources and citations against the originals. rev 7 Oct 2014 now handles a Source having no Master Source fields defined in its Source Template (caused a RootsMagic error)
  3. Sources-DeleteAfterSplit.sql 30 Jun 2013. Delete the “lumpy” stuff.
  4. Deletion of unused custom source templates yet to be done; alternatively run the Conversion of Templates procedure below on custom templates so that they are ready to be used as Ultimately Split.
  5. Delete Phantoms: probably advisable to run this after the split.
  6. After the deletion, use RootsMagic Database Tools to test integrity, rebuild indexes if integrity not OK, and compact to recover space.

One problem I have seen with the results is that the differentiation of the Master Source Name due to the appending of the Person Name and Fact Type prevents the Source Manager’s AutoMerge from merging otherwise identical Master Sources. Manual merging is way too laborious so until RootsMagic gives AutoMerge options, another outboard utility is needed. Stay tuned for Sources – Merge Duplicate Masters.

Conversion of Templates

A Free Form template is readily used for extreme splitting as there is but one Source Details field, [Page], which can be left empty and the Master Source fields may be filled arbitrarily with text. There are several built-in templates which are already setup for extreme splitting as they have no Source Details fields:
Artifact, archived
Artifact, Family, photographed (privately held)
Artifact, privately held
Bible Records
Broadside, original
Broadside, reprint
Family Bible Record
Genetic Test (DNA) report
Legal Document, unrecorded (family copy)
Letter, Historic, private
Maps, Historic
Maps, Topographic
Military, Muster Rolls (manuscript)
Photo, Portrait, Archived (Annotated)
Photo, Portrait, Private (Annotated with Provenance)
Photo, Portrait, Private, scanned
School Records (student transcripts)
Slip Laws, Federal
Slip Laws, state
The remaining ~400 templates have Source Details fields that preclude them from being used for extreme splitting unless modified.

The following query moves all Source Details fields into the Master Source for all sources, thus enabling them to be used for extreme splitting.

-- SourceTemplateExtremeSplitConvert.sql
/*
2013-06-18 Tom Holden ve3meo
Converts all fields in the Source Template defined as Source Details or Citation Details
to Master Source fields, thus enabling the template to be used for Extreme Spliting.
 
WARNING - use this on unused templates; it does not convert existing citations and master sources.
*/
UPDATE SourceTemplateTable
SET FieldDefs = CAST(REPLACE(CAST(FieldDefs AS TEXT), '<CitationField>True</CitationField>', '<CitationField>False</CitationField>') AS BLOB)
WHERE 1;-- Apply constraints here, otherwise all Source Templates are modified.
 
-- View FieldDefs
SELECT CAST(FieldDefs AS TEXT)
FROM SourceTemplateTable T;-- view results

As it stands, this query modifies all the built-in templates, which is fine for experimentation but to be avoided in practice. Better to create editable templates first and then run this query with WHERE 1 changed to WHERE TemplateID > 9999. Refer to Source Templates for ways to batch import or convert to editable source templates.

Sources without Media

Needing to find Master Sources lacking media and to move media from Source Details to Master Source as part of his conversion to Extreme Splitting, Jerry posted Query for Sources Without Media.

Inline comments


ve3meo

Comment: This bears some scrutiny. True if the…

ve3meo
18 June 2013 20:21:51

This bears some scrutiny. True if the Bibliography sentence is the same as the footnote but not so if the Bibliography sentence template uses a small subset of the source fields as do many of the built-in templates.

REMATCH to FamilySearch ID in REFN fact #FamilySearch #refno

Posted on by Tom Holden

An earlier script copied the FamilySearch Family Tree ID into the Reference Number fact. One user uploaded a file to an Ancestry Member Tree where it was grown through online evidence-based research and then downloaded the GEDCOM back into RootsMagic. RootsMagic’s Auto-Match feature in FamilySearch Central failed to rematch all the previously matched people. However, the FSID was preserved in the REFN fact through this round trip so his wish was to regenerate the matches from the REFN value. This script does just that.

The script is written as the complement to the one at COPY familySearchID to REFN fact , that is, the REFN value is of the form “fsid: XXXX-XXX”. If your format is different, it will have to be revised.

At time of writing, we are perhaps just weeks away from the Ancestry TreeShare update to RootsMagic 7 which may result in a change to the LinkTable that could cause the script to be rendered invalid without being adapted. However, it also follows that there should also be even less reason for someone to move their database from RM through Ancestry and out again with the attendant loss of FS matches.

REFN-RematchFSID.sql

REFN-RematchFSID.sql-newinfo.PNG
RM’s FamilySearch Central will report there is “new information” for every one of the matches remade by the script.
REFN-RematchFSID.sql-LinkTable.PNG
The rematches in the LinkTable made by the script are like records 1 and 3 with the extVersion empty. After opening the FamilySearch Person Tools on one of the people with “new information”, the record is revised as per #2.