How to Query RootsMagic #sqlitespy #integrity #database

Posted on 2013-10-162022-04-28 by Tom Holden

Promo

Using many of these SQL queries or procedures is pretty easy with a SQLite manager such as SQLiteSpy as illustrated in the example below. Even easier, because you don’t even see the SQL scripts that underlay them, is to use the utilities bundled in the dead simple RMtrix application. The stories following relate the experiences of novice users who accomplished the same task, converting shared facts to individual ones for GEDCOM export to software that does not support RM’s shared events. One used SQLiteSpy; the other did it with RMtrix.

SQLiteSpy User Experience

Responding to a message bemoaning the problem with shared facts, Kim Mills posted this on RootsMagic-Users-L:

Bonnie, I use the shared facts to and this used to be a real frustration
point for me to. But there are a few solutions.

If you just want the _SHAR part removed so it doesn't repeat in the
RootsWeb tree. Go to your tree settings, then to living persons, under
there is a section to exclude tags. Paste _SHAR there. Now it will
look like a normal census or event for JUST the primary person.

But if you want the census or other events to be converted to a real
individual fact for each person, Tom on this group came up with a great
little program for that.
Here is a link to the page that shows how it works.
Facts+-+Split+Shared+to+Individual

I think he has also made it part of the RMtrix program that makes it
easier to run.
Bundled+Utilities+-+RMtrix

Basically make a COPY of your database, then run the utility to split
the facts, then the second one to remove the shared facts. This way the
primary person doesn't have 2 copies of the original fact. Then export
the gedcom as you normally would.

Kim

On 02/26/2013 06:54 PM, Bonnie Weisel wrote:
> I thought it would be a convenience and time saver to start using
> "Shared Facts". Unfortunately it doesn't work out when exporting and
> uploading a gedcom to wc.rootsweb.ancestry.com.
>
>

RMtrix User Experience

In the same thread, Bonnie responded that she had become a new RMtrix user!

I'd like to thank Denise and Kim for their replies on 2/26 to my
questions about exporting a database with shared facts. When exporting
a Rootsmagic database, if you want the sharee to have a copy of the
facts, you will need to use two sqlite procedures.
 
Last night I installed and ran Rmtrix, exported my database, all in less
than 5 minutes. My suggestion is this is the easiest route to
accomplish the task. I've copied my how-to notes below.
 
Installing Rmtrix:
1) The source for Rmtrix is
http://sqlitetoolsforrootsmagic.wikispaces.co/Bundled+Utilities+-+RMtrix
2) Download Rmtrix and run setup.
 
 
To upload a tree with shared facts:
1) Make a COPY of the Rootsmagic database
2) Run Rmtrix and open the COPY of the database
Events->Convert Shared to Individual
a) ->SPLIT Shared to Individual
b) ->Unshare
3) Export to a gedcom (this file can be uploaded to
wc.rootsweb.ancestry.com)
4) Delete the COPY of the database
 
 
-Bonnie

Example – SQLiteSpy Quick Integrity Check

SQLiteSpy is one of my favourites – for its clean interface, speed and display of results. Its installation is really simple – download a ZIP file from here and extract SQLiteSpy.exe to wherever you want. There is no Windows install to perform, the one exe file is complete and ready to run. You will want to create a shortcut and place it in your Start Menu and/or Desktop so you can readily launch it later. Once past the basic familiarisation, you will want to liberate SQLiteSpy from the shackles of RootsMagic proprietary RMNOCASE collation sequence by following the instructions on RMNOCASE – faking it in SQLiteSpy.

SQLiteSpy follows the conventions for Windows programs pretty well. To open a RootsMagic database, use File > Open Database to open the conventional Open dialog window. By default, you will only see files with the extension ‘.db’; to see RootsMagic files, either select ‘Any file (*.*)’ from the Files of type selection box OR in the File name entry box, type ‘*.rmgc’ (without the quotes). You may also want to be extra safe by checking the box Open as read-only.

Once SQLiteSpy has opened a RootsMagic database, you will see a screen as below, but with the right side empty.

Type a SQL command or set of commands in the SQL Editor area (each command must end with the semi-colon). In the example is the command to do an integrity check without checking the indices (SQLiteSpy cannot do the full integrity check on a RootsMagic database).

Press function key F9 or click Execute and select Execute SQL to execute the commands in the SQL Editor.

The results of the commands will be shown in the middle pane (if the integrity check had passed, it would simply display ‘ok’. Any cell that is selected in the results pane will be expanded in the bottom pane. You can adjust the dividers between the panes to see more or less.

Pretty easy! If you got this far using SQLiteSpy, then you are away to the races!

The File menu is where you can load one of the SQL command files you can download from this site and save a command file you have made yourself. SQLiteSpy does a nice job of tracking recently opened databases and SQL files – just use the Reopen selection.

SQLiteSpy can execute all the commands in the SQL Editor (F9) or just a command that is highlighted (Ctrl-F9). Remember, each command ends with a semi-colon and those that retrieve data begin with SELECT. You don’t modify anything with a SELECT.

Don’t forget – RMNOCASE – faking it in SQLiteSpy for maximum access to your database.

Example – Change fact type globally

The following problem was posted in the RootsMagic Forums by user royviggo at
http://forums.rootsmagic.com/index.php?/topic/13013-change-fact-type-globally-after-import-from-legacy/#entry60476:

Hi, I’m a new RootsMagic user, and I imported a tree from Legacy. Got my custom fact types in Legacy as new fact types in RootsMagic, and thats nice. But some of them already exists as fact types in RootsMagic, so I wonder if there is a way to change fact type globally? I can change fact type on one fact at a time, but it will be very boring to change about 500 facts for 2500 persons…
I can edit the new fact types, but I think it’s silly to have two Residence facts, so I want to change them if possible. Any tips?

Forum user vyger responded with this well-written guide to using SQLiteSpy to accomplish the task:

I would use the SQLliteSpy option and do regularly as I try as far as possible to keep within the Rootsmagic standard set of facts. I do this to maintain some compatibility with the recognised standard and often import gedcoms from other users with multiple custom facts. A simple explanation of how to achieve this global change appears later in this post.

Personally I would go to Tom’s page below and download SQLiteSpy, remember to follow the link to “fake collation” and follow his instructions.

http://sqlitetoolsfo…/SQLiteManagers

For a novice with no database knowledge this can look very complicated but a lot can be achieved with very basic single table changes which this question asks/requests, so do read on.

I would first make a copy of your database by finding it in Windows Explorer, Copy it and Paste it which will give you an exact copy, rename this copy as something so you will easily recognize it as your test database and try SQLiteSpy on that database first. Even when sure what you are now about to do on your working database always back it up before running any queries on it.

Once you have opened your Rootsmagic database with SQLiteSpy remembering to change the “SQLite3 Database” file option to “Any File” you will see all the tables in the database to the left.

Double Click on FactTypeTable and you will see a list of all Fact Types (one per row) and each identified by a unique number FactTypeID. As TomH said anything greater than 999 is a user defined fact.

Now Double Click on EventTable, Don’t look at it in too much detail but the EventType number corresponds to the FactTypeID in the FactTypeTable, in other words they are linked and this EventTable contains all your facts both Individual and Family.

Jump back to the FactTypeTable and examine your facts. For the purpose of explanation I will use an example where a FactTypeID #1016 is found called BORN, well we know that this is should probably be the Rootsmagic built in Birth Fact which is FactTypeID #1. So once we are happy that we want to globally make that change go back to the EventTable by double clicking on it and all we want to do is change all instances of EventType #1016 to EventType#1, really just a find and replace operation. Copy the simple code below into the pane at the top of SQLiteSpy and press F9. The bottom left info bar below the list of tables will now report the time it took to complete the operation and how many records were updated.

```
UPDATE EventTable
SET EventType = 1
WHERE EventType = 1016
;
```
At this point open the database again with Rootsmagic, use File > Database Tools > Rebuild Indexes and check the changes you just made.

TomH would be very expert in these queries but everything in baby steps and I can understand how some of his more complex queries can appear a bit foreboding so a primary school beginners guide would certainly get more people into using SQL IMO.
😉

Edit : Hopefully nobody pasted the code snippet literally without changing the the SET EventType – Where EventType numbers to their own requirements. I was going to say hopefully it goes without saying but it’s better to be said just in case

:rolleyes:
and remember to experiment first and always back up before applying any queries.

Discussions & comments from Wikispaces site

Facts – Add custom MRIN event to each family #facttypes #events #mrin

Posted on 2013-10-162019-01-27 by Tom Holden

Why? How?

Example of an MRIN event added by the script. (click for full size)

Main Script | Mismatched FMNO Report | Earlier GEDCOM Solution

RootsMagic 6 hides the record number commonly known as MRIN except as an option in the title of the Family Group Sheet and in the Marriage List report. This is not good enough for some users who, perhaps migrating from experience with other family tree software, have a document filing system organised on MRIN. In RM, the “MRIN” comes from the FamilyID in the FamilyTable and is not based on a Marriage event; hereafter, I refer to it as FMNO. It is not preserved in data transfers via GEDCOM or drag’n’drop between RM databases, except in special circumstances. With that caveat, the following SQLite script creates a custom FMNO family event and adds it to every couple and single parent with their FamilyID number stored in the event description with the prefix “FMNO “. A second script reports on mismatches between the FamilyID number and the FMNO event value as could arise from a transfer to another database. And to round things out showing that there is always more than one way to accomplish something, see a non-SQLite method for batch creation of the FMNO events at the bottom of the page.

Main Script

Events-FMNO.sql

-- Events-FMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Exposes otherwise invisible FamilyID (except optionally in title of FGS)
by adding an event to each spouse containing the FamilyID.
The event added is a custom Family event named *FMNO, abbreviated FMNO which is
first created by the script if one having the same abbreviation does not exist.
*/
-- Add a FMNO fact type if none exists
INSERT
    OR IGNORE
INTO FactTypeTable(OwnerType, NAME, Abbrev, GedcomTag, UseValue, UseDate, UsePlace, Sentence, Flags)
SELECT 1
    ,'*FMNO'
    ,'FMNO'
    ,'EVEN'
    ,1
    ,0
    ,0
    ,CAST('[Desc].' AS BLOB)
    ,- 1
WHERE (
        SELECT FactTypeID
        FROM FactTypeTable
        WHERE ABBREV LIKE 'FMNO'
            AND OwnerType = 1
        ) ISNULL;
 
-- Delete all FMNO events  N.B. RM will delete all events for a FactType that you delete through it.
-- DELETE FROM EventTable WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE ABBREV LIKE 'FMNO' AND OwnerType=1);
-- Add FMNO event to each couple without such an event, description to contain FamilyID (invisible FMNO)
INSERT
    OR
 
ROLLBACK
INTO EventTable(EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate, IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note)
 
SELECT (
        SELECT FactTypeID
        FROM FactTypeTable
        WHERE ABBREV LIKE 'FMNO'
            AND OwnerType = 1
        ) AS EventType
    ,1 AS OwnerType -- a Family event is type 1
    ,FamilyID AS OwnerID
    ,0 AS FamilyID
    ,0 AS PlaceID
    ,0 AS SiteID
    ,'.' AS DATE
    ,1 AS SortDate -- places event at or near top of list in Edit Person screen
    ,0 AS IsPrimary
    ,1 AS IsPrivate -- presumably having this event set as private will provide needed output control
    ,0 AS Proof
    ,0 AS STATUS
    ,(
        SELECT JULIANDAY('now', 'localtime') - 2415018.5
        ) AS EditDate -- does not affect Date last edited in People view
    ,NULL AS Sentence
    ,'FMNO ' || FamilyID AS Details -- having the prefix "FMNO" helps to stand out in tabular reports
    ,NULL AS Note
FROM FamilyTable
WHERE FamilyID -- don't add an FMNO event to those couples already having one
    NOT IN (
        SELECT DISTINCT OwnerID -- list of all FamilyID's already having the FMNO event
        FROM EventTable
        WHERE OwnerType = 1
            AND EventType IN -- in case there is more than one FMNO family fact type
            (
                SELECT FactTypeID
                FROM FactTypeTable
                WHERE ABBREV LIKE 'FMNO'
                    AND OwnerType = 1
                )
        );

Mismatched FMNO Report

Example of mismatches after a drag’n’drop transfer to a new database, as displayed in SQLite Expert. (click for full size)

Unfortunately, a drag’n’drop of Everyone from one database to a new, empty database does not necessarily preserve all FamilyID and PersonID numbers. Many were preserved but many not, as seen in this extract from the following script’s output; fully 1/3 of almost 400 FamilyIDs were changed while PersonID changes were many more. What happens is that new PersonTable and FamilyTable tables are generated and records unused in the originating database are reused in the new, thus shifting the record numbers down. GEDCOM import into an empty database offers the option to Preserve record numbers but this only applies to the person, not the family. A document filing system based on either or both the MRIN/FMNO and RIN of the originating database will be out of sync with the internal record numbers for many couples and, probably, persons in the target database. Carrying over the FMNO and RIN as events to the target database preserves the document trail for as long as those events are preserved and helps to realign the filing system with the database. Also see Copy RIN to REFN.

Events-MismatchedFMNO.sql

-- Events-MismatchedFMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Compares the OwnerID for Family events having the Abbrev "FMNO" against
the value stored in the event description, listing those that do not match
along with the names and RINs of the couple.
Used in conjunction with Events-FMNO.sql.
*/
SELECT SUBSTR(CAST(E.[Details] AS TEXT), 6) AS FMNO -- extracts everything after "FMNO "
    ,E.[OwnerID] AS FamilyID
    ,ifnull(N1.Given || ' ' || UPPER(N1.[Surname]) || '-' || FM.FatherID, '')
   || ' + ' ||
   ifnull(N2.Given || ' ' || UPPER(N2.[Surname]) || '-' || FM.MotherID, '') AS Couple
FROM EventTable E
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
LEFT JOIN NameTable N1 ON FM.FatherID = N1.OwnerID
    AND + N1.IsPrimary
LEFT JOIN NameTable N2 ON FM.MotherID = N2.OwnerID
    AND + N2.IsPrimary
WHERE E.OwnerID NOT LIKE SUBSTR(Details, 6)
    AND E.OwnerType = 1 -- Family event
    AND FT.[Abbrev] LIKE 'FMNO';

Earlier GEDCOM Solution

I discovered Alfred’s 9 April 2009 solution using Microsoft Word wildcard search and replace on the GEDCOM and Romer’s 8 July 2009 reiteration of that after having developed the SQLite scripts.

Source Templates – Set Quotation Punctuation to ‘UK’ or ‘Logical’ Placement #sourcetemplates #update

Posted on 2013-09-292019-01-27 by Tom Holden

Most of the built-in source templates have the comma or period separating a quotation from the rest of a Footnote, Short Footnote or Bibliography sentence (or ending the sentence with a quotation) inside the quotation marks. This is known as ‘US’ style or convention. ‘UK’ or ‘logical’ convention places them outside the quotation. This query attempts to find all such instances and move them to the ‘UK’ position.

These names for the two styles do not indicate exclusive use in either country. There are divergences in practice not only trans-Atlantic but also north-south, between sectors, fiction and non-fiction et al according to Grammar-Monster.com. It characterises the innie-outie placement of periods and commas relative to the quotation as US vs UK convention, granting that that is just a convenient oversimplification. An article by Grammar Girl echoes that and cites references that indicate the ‘US’ practice follows that of early typesetters and compositors using now-obsolete technology while the ‘UK’ practice is “logical”. Note that I placed the period ending the previous sentence outside the quotes; I cannot see any reason that it should be within.

I learned my grammar in the 50’s and 60’s in Canada which some have said is culturally mid-Atlantic, also an over-simplification, but, always trying to be logical, I evidently use “UK rules”. Apparently, Canadian practice has evolved: the government’s own guide to “The Canadian Style” encourages placing period and comma within the quotes except “when a very high degree of accuracy is required” in which case ‘logical’ placement is used. I would argue that Evidence style citations are supposed to be accurate and, therefore, …

SourceTemplates-SetUKquotePunctuation.sql
This query requires the REGEXP function which is supported in SQLiteSpy but not by SQLite Expert Personal without loading an extension.

Source Templates – Revision Utilities #sourcetemplates

Posted on 2013-09-252022-04-29 by Tom Holden

RootsMagic invited users to submit any issues they are having with the built in source templates as of version 6.3.0.3 in the Forum thread Sentence Templates To Be Fixed on 20 Sep 2013. Extraneous punctuation and text when one or more fields is left empty was identified as an issue and one that the RootsMagician intends to fix, at least for those templates so used more than rarely. It’s not clear what other issues might be addressed but new/modified functionality was ruled out from this round.

In an attempt to identify the scope of the extraneous characters problem, a series of queries were developed that readily show the outputs for empty citations of empty sources for each of the built-in source templates. The result lends itself to editing the templates and may be helpful in identifying and resolving other issues.

SourceTemplateRevisionUtilities-CitationManager+EndNote.png

Empty citations on copies of built-in source templates showing extraneous punctuation and text from one and its resulting report endnote.

SourceTemplateRevisionUtilities.sql rev 2013-09-25

-- SourceTemplateRevisionUtilities.sql
/* 2013-09-24 ve3meo
2013-09-25 added ripple update of the name of the source template through to the citation comment
 
This series of queries is intended to facilitate review and editing of
copies of the builtin source templates to remove extraneous punctuation
and text when fields are left empty. The series assumes a clean database
with one person RIN=1 to whom an empty citation using each source template
will be linked. The series:
1. Creates a copy of each builtin source template which can then be edited in RM, name prefixed with *
2. Creates an empty source for each copy of the builtin source templates, source name = template name
3. Creates an empty citation of each source linked to a person whose RIN is 1.
4. Ripples the name of the source template through to citation comment for use in end notes.
 
The database can be opened by RM on the Citation Manager for Person 1. The names of the sources
are ordered alphabetically to match the order in the Source Template List. Selecting a citation
shows the three sentences resulting from the empty source citation. Using an external SQLite manager,
the SourceTemplateTable sentence templates for Footnote, Short Footnote and Bibliography can be edited,
thus saving much drilling up and down in RM. Simply selecting another citation in RM and returning
refreshes the sentence generation to catch up to the changes in the template.
 
Of course, there may other ways of working effectively, such as two instances of RootsMagic (resident and portable)
open on the common database, one in the Citation Manager, the other in the Source Templates window.
*/
 
-- Create a copy of each builtin source template
INSERT INTO SourceTemplateTable
SELECT TemplateID + 10000
    ,'*' || NAME
    ,Description
    ,Favorite
    ,Category
    ,Footnote
    ,ShortFootnote
    ,Bibliography
    ,FieldDefs
FROM SourceTemplateTable
WHERE TemplateID < 10000;
 
-- Create an empty source for each copy of the builtin source templates
INSERT INTO SourceTable
SELECT NULL AS SourceID
    ,NAME
    ,'' AS RefNumber
    ,'' AS ActualText
    ,'' AS Comments
    ,0 AS IsPrimary
    ,TemplateID
    ,(
        SELECT CAST('<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
        ) AS FIELDS
FROM SourceTemplateTable ST
WHERE ST.TemplateID > 10000;
 
-- Create an empty citation for each empty source based on copies of the builtin source templates
-- all these citations are to the person with RIN=1
INSERT INTO CitationTable
SELECT NULL AS CitationID
    ,0 AS OwnerType
    ,SourceID
    ,1 AS OwnerID
    ,'[[user:ve3meo]]' AS Quality
    ,0 AS IsPrimary
    ,'' AS Comments
    ,'' AS ActualText
    ,'' AS RefNumber
    ,0 AS Flags
    ,(
        SELECT CAST('<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
        ) AS FIELDS
FROM SourceTable S
WHERE S.TemplateID > 10000
ORDER BY S.NAME;
 
/* Ripple SourceTemplate name through to Citation comments
 via Source comments for listing in end notes. Having the name
 in Source comments may also be convenient.
*/
UPDATE SourceTable
SET Comments = (
        SELECT ST.NAME
        FROM SourceTemplateTable ST
        WHERE SourceTable.TemplateID = ST.TemplateID
        );
 
UPDATE CitationTable
SET Comments = CAST(x '0A' AS TEXT) || '{' || (
        SELECT NAME
        FROM SourceTable S
        WHERE CitationTable.SourceID = S.SourceID
        ) || '}';

Media – Set Primary Photo for Persons #media #update

Posted on 2013-09-232025-04-18 by Tom Holden

This query responds to a problem with an import from Family Historian via FTM-flavoured GEDCOM reported by Stewartrb in the RootsMagic Forums thread
GEDCOM import with media. The images linked to the person in Family Historian were ultimately tagged as such in RootsMagic but none was marked as the Primary photo. Consequently, none showed on the main screen nor in reports other than Scrapbooks. This query sets the last image-type media file added to the Gallery among those that have been tagged to a person as that person’s Primary photo, when none of the tags for that person have been so checked. It could be readily modified to be the last tag added to the person rather than the last imported mediafile or to be the first instead of the last.

MediaTags-SetPrimaryForPersons.sql

MediaTags-SetPrimaryForPersons-FirstTag.sql Added 2025-04-18 Sets the first mediatag as the Primary.
Both versions are compatible with RM4 to #RM10.

-- MediaTags-SetPrimaryForPersons.sql
/* 2013-09-23 ve3meo
Sets the last mediatag as the Primary photo for a person with 1 or more tags for image-type media, none of which are so checked.
*/
UPDATE MediaLinkTable
SET    IsPrimary = 1
WHERE  LinkID IN
       ( -- a list of the last LinkIDs for persons with image-type media tags, none of which marked as the Primary photo
       SELECT  LinkID
       FROM    ( SELECT  *
               FROM     ( SELECT    ML.LinkID        -- tag number
                                 , ML.OwnerID        -- RIN for person, given the constraint below
                                 , ML.IsPrimary      -- 1 if tag box checked "Primary photo for this person" else 0
                        FROM       MediaLinkTable ML -- media tags table
                                   INNER JOIN MultiMediaTable MM
                        USING      (MediaID)        -- mediafiles table
                        WHERE      ML.OwnerType = 0 -- person
                        AND        MM.MediaType = 1 -- image type
                        ORDER BY   ML.OwnerID       -- sort by ownerid then
                                 , ML.IsPrimary     -- isprimary, putting any tag for the person set as primary to the bottom of the list
                        )
               GROUP BY OwnerID -- only the last LinkID in the list for the person comes out
               )
       WHERE   NOT IsPrimary -- only the last LinkID for a person with tags, none marked primary
       );

Group – Persons with Text Dates #date #namedgroup

Posted on 2013-09-012024-10-07 by Tom Holden

Creates a Named Group in the open database with the name “SQL: Text Dates” if one does not already exist
Deletes all members from that named group
Populates that named group with persons having an event date that is backgrounded in yellow on the Edit Person screen, i.e., an invalid date format interpreted by RootsMagic as Text

To refresh the named group’s membership list to reflect corrections you have made to the format of event dates, rerun the query to regenerate the RootsMagic group table and then, in RootsMagic, select a different group and then re-select the “SQL: Text Dates” group. You should see that names have disappeared from the group, provided you have corrected all the text dates for the persons you worked on.

Voila! A Dynamic Group! Just a few clicks more than if RootsMagic had it built in.

Group-PersonsWithTextDates-RM10 2024-10-08 for #RM10 & probably suitable for RM8 and RM9

Group-PersonsWithTextDates.sql for all versions RM4-RM7
2013-09-01 superseded PersonsWithTextDates.sql which was flawed and incomplete.
rev 2018-07-07 bug fixed when no other existing group

Bundled Utilities – RMtrix #application #visualc

Posted on 2013-08-092022-04-27 by Tom Holden

RootsMagic 8 has changes to its database design that render many of the items in RMtrix unusable. It will throw an unhandled exception error in most, if not all, cases.

RMtrix collects a bunch of SQLite queries under a simple, easy-to-use interface. It does many of the queries reported on this wiki – some of the menu items are dormant. RMtrix is intended to progress the development of a more comprehensive set of utilities going beyond the limitations of the database language by itself and make them available to and usable by the average user of the RootsMagic software.

Kim Mills has done a very clear, well illustrated explanation of how to use functions in RMtrix to split shared events prior to exporting and uploading to Rootsweb and Ancestry.com trees. Read it on her Footsteps of the Past blog.

Alpha-7 21 Jan 2013

Alpha-7 has many additional tricks gathered from the last nine months of SQLite Tools for RootsMagic..

Screenshot from RMtrix alpha-7 querying the properties of the RootsMagic 6 database stored in the file “RM6-PLAY.rmgc”. Now uses a RM6.0.0.4 database (reported as 6000) as the reference for comparing source templates, fact definitions, etc.

Note that you can sort on any column by clicking on the header and you can change the order of the columns by dragging one at a time. These arrangements do not persist through to the next running of the query.

The following menu items are active (red text signifies a change from alpha-6, 9 Mar 2012):

File > Select
Help > About
Addresses > Identify Blank Names | How Used
Database > Analyze | De-Analyze | Integrity Check | Reindex | Properties| Vacuum
Events > Set same date sort order | Shared events with missing witnesses | Sharable events
| Birth Year Mismatch List | Death Year Mismatch List | UPDATE Birth and Death Years
| Convert Shared to Individual (SPLIT Shared to Individual | UNSHARE | UNDO Splits and RESHARE | HIDE Tracks)
Fact Types > View current outputs | Set all to GEDCOM | Snapshot current | View snapshot outputs | Restore from snapshot | Delete snapshot
Media > File Links | Media List | DELETE Unused | RESET Media Type
| Media Tags (COPY from Shared Events to Individual | DELETE Personals duplicating Events)
People > Duplicate Name Search | Resolved Duplicate Names | Multiple spouses | Nameless People – DELETE
| Alternate Names (List | Delete Orphans | SET Type “Married” | ADD “Married”)
Places > Errors | Having Place Details | Unused Places | Orphaned Place Details (List | Delete) | DELETE Unused
Reports > Paragraphing Strip|Add | Dummy family Add|Delete | LifeLines
Sources > Master Sources | Source List | Invisible Name Citations (List | Convert to Personal Citations)
To-Do > To-do List
Tools > DELETE Phantoms
Web Tags > MAKE WebTags from citation Comments | COPY from Citations to Persons | DELETE Duplicates

Sorry! No Help screens yet…

Caveat Emptor!

I’m a novice at Visual Studio and C# and have not taken steps to automatically protect or backup your database. So get to know RMtrix by using it on a copy of your database. Some of these functions modify your database and are so flagged in the Tool Tip popup when the mouse pointer hovers over the menu item.

Integrity Check, Reindex and Vacuum – Beware!

The Integrity Check will almost invariably report errors on a RootsMagic database that has NOT been Reindexed or Vacuumed by RMtrix. And RootsMagic 5 will almost invariably report errors on a database that HAS been Reindexed or Vacuumed by RMtrix. That is because RootsMagic 4 and 5 use a secret multilingual collation sequence for comparing strings of text and putting them in order. RMtrix uses a simpler collation; indexes produced by two different collation sequences will sometimes differ. Therefore, it is recommended that:

RM5 & 6 databases may be Reindexed or Vacuumed in RMtrix for its use and then, on return to working with RM5, use its File > Database Tools > Rebuild Indexes.
RM4 databases should NOT be Reindexed or Vacuumed in RMtrix.

At some future stage of development, hopefully there will be protections built in to prevent changes to RM4 databases that could result in indexing errors.

Download and Installation

RMtrix32.zip RMtrix64.zip zip files dated 21 Jan 2013 ver 0.1.7

Download the 32 bit or 64 bit version (match your computer’s operating system) to a temporary location on your computer. If your operating system does not open the zip file, you will need an unzip utility to extract all the files and sub-folders to another temporary location. Then open the file “setup.exe”. If your computer does not have the .NET Framework 4 already installed, the installer program should automatically download it from Microsoft and install it before completing the installation of RMtrix. If the installation is successful, RMtrix should open on screen. You should also find a RMtrix folder in your Start Menu.

Please report successful or problem installation to me at ve3meo at gmail dot com and indicate which version of Windows is on your computer and whether 32 or 64 bit. This will help confirm that I have correctly used the Visual Studio Publish tool.

Won’t run?

Getting an error message when you select a database along the lines of “Could not load file or assembly ‘System.Data.SQLite … or one of its dependencies”?

I have an older ThinkPad running Windows XP which was giving me this problem getting the 32-bit version of RMtrix to work. The same issues might pertain to computers running later versions of Windows with either the 32 or 64 bit version of RMtrix. I develop on a 64-bit Windows 7 computer with all the bells and whistles of .NET Framework 4 and the full system.data.SQLite install so getting it to work there does not necessarily guarantee that it will work on other systems.

RMtrix uses the version of SQLite that requires Windows .NET 4 Framework (not just the Client). The installation of RMtrix may not have resulted in the appropriate version of .NET, so Install .NET 4 Framework, the full version, not the Client. You will be given a choice of three things to install – you only need the first of the three, unless you are a developer, the one for running applications.

Even though I distribute the appropriate version of System.Data.SQLite.dll in the installation zip files and it is installed along with RMtrix, that does not seem to be sufficient. I have not found a way in Visual Studio to make the installed app find the DLL. So it is necessary to visit the System.Data.SQLite Downloads page and download and install the package that is appropriate for your computer:
sqlite-netFx40-setup-bundle-x86-2010-1.0.79.0.exe or later, for 32-bit Windows,
sqlite-netFx40-setup-bundle-x64-2010-1.0.79.0.exe or later, for 64-bit Windows.
If you have not previously installed the correct version of .NET 4, System.Data.SQLite will not install.

Discussions & comments from Wikispaces site

Installed 64 bit version

thejerrybryan
17 February 2012 02:59:54

The install was fine – totally uneventful.

I’ve played with the functionality slightly, and I have no comments at this time because I haven’t had time to play enough just yet. The packaging is the first thing that needs to be right, and then the functionality can be developed from there. The packaging seems to be doing well.

Jerry

ve3meo

ve3meo
17 February 2012 03:15:30

Thanks for the report, Jerry. I have also received private confirmation of a successful 32 bit and another 64 bit installation so the distribution mechanics seem to be right. I’ve had one report of a 32 bit installation failure – program starts but no query works – which I hope is due to it being installed on a 64 bit machine/OS.

FYI, I am building on a 64 bit Win 7 platform with the sqlite.interop.dll and system.data.sqlite.dll from the 64 bit distribution. That’s needed to debug and run on my computer and for the 64 bit published version. However, on 32 bit machines, the 32 bit system.data.sqlite.dll is required, but I cannot debug and build with it. So I replace the 64 bit dll with the 32 bit dll in the source directory and re-publish, ignoring the warning about a mismatch. I could simply tell people to install the 64 bit version and replace the dll but Windows One Click installs it deep in an unsearchable area.

That info may be helpful to you when you are ready to publish your Group and Color-coding Manager.

Tom

Geraniums

After installing

Geraniums
19 March 2012 16:49:30

Is it OK to delete the RMtrix64 set up files after installing the program?

ve3meo

ve3meo
19 March 2012 17:26:25

Yes. For the next upgrade, download to the same place you installed this version from else it will not let you upgrade without uninstalling.

Pheney

RMTrix64.zip

Pheney
28 December 2015 06:47:04

After some fiddling and false starts, probably because I have not worked with RMTrix for a while, I have installed RMTrix under Windows 10 64-bit. It has already helped identify the broken links in my media album. Thanks, Tom!
Rex Sinnott

ve3meo

ve3meo
28 December 2015 13:16:43

I’m relieved to hear that, Rex, because I had no idea how to figure out what went wrong.

ve3meo

Comment: “The following menu items are active (red text…”

ve3meo
03 September 2018 20:04:44

ve3meo Feb 14, 2012

I have been busy adding more of the queries from the wiki. Some wrinkles here and there but progressing, including some that DO modify the database. Didn’t have time to build the 32 bit version – maybe tomorrow night – so holding off on the upload.

But don’t let that deter you from installing the current version – I’d like to know that the package is complete and installs successfully on both 32 bit and 64 bit systems.

Source Code

Here’s the source code for the Visual Studio 10 C# Project in one 7Z compressed file (9MB). You are welcome to use it as you see fit. I have not touched Visual Studio in years so I cannot offer any assistance.

Tom Holden
2021-06-22

RMtrix.7z Download

Sources – Merge Duplicate Masters #merge #sources

Posted on 2013-08-082019-03-02 by Tom Holden

Why?

The RootsMagic Source List window has an AutoMerge function that can merge Master Sources that are identical in every respect, including the pointer to the Source Template and the name of the Master Source. Thus it fails to merge sources that are practically identical and that we would wish were merged. Source Templates – Merge Duplicates addresses the first barrier for Source AutoMerge. The script below offers an external Source AutoMerge that does not mind if:

the Master Source names are different
there are white space differences around or within Source Text or Comments but the non-blank texts are otherwise identical

It is more forgiving than the built-in Source AutoMerge yet does not corrupt the core properties of the final, common Master Sources.

Practically Identical Master Sources

Under what conditions can such practically identical sources arise that RootsMagic’s Source AutoMerge does not recognise them?

Sources added to a person within FamilySearch Family Tree have their name prefixed by the person’s name. When imported into RootsMagic via its Share Data utility, that becomes the Master Source name. Thus, the same source assigned to two different persons will have different Master Source names.
Sources that have outer white space following the Master Source Text or Comments lose that on export or transfer via drag’n’drop. Should these sources return to the database through collaboration with another party, SmartMerge will merge the persons but not the sources because of this differing white space.
The Ultimate Splitting tool described on Sources – Adventures in Extreme Splitting adds person name(s) and event to the name of the split Master Source, which, in many instances, will be identical to other Master Sources.split from a common lumpy Master Source.

Outcomes

An example of a small database, starting with lumpy sources:

State	Sources	Citations
Original	13	58
after Ultimate Split	71	116
after Delete Lumpies After Split	58	58
after RM Source AutoMerge	58	58
after SQLite Sources – Merge Duplicates	28	58

Download

Sources-MergeDuplicates.sql Rev, 2013-08-08 now ignores differences in white space within Source Text and Comments

Usage

Make a copy of your database on which you can run these procedures so that you can return to the original if you are not satisfied with the results.
Open the database in your SQLite manager with a RMNOCASE extension.
REINDEX the database using SQLite.
Sources-MergeDuplicates.sql does require that the sources use the same Source Template and so Source Templates – Merge Duplicates should be run first if there is any thought that there may be duplicate source templates.
It is also advisable to run the SQLite Delete Phantoms to clean up the database for faster execution of the following script but this may be more aggressive than you might like.
Execute Sources-MergeDuplicates.sql to merge the practically identical sources; this is a very intensive series of SQL statements and may be rather slow.
In RootsMagic, run File > Database tools > Rebuild Indexes on this database; not needed if its “Test database integrity” reports OK.

Delete Phantoms #database #delete #phantom

Posted on 2013-08-072019-02-12 by Tom Holden

Rev 2019-02-12

Whether you have deleted or merged within RootsMagic itself or used one of the Delete Many procedures, there remain in various tables residues from the deleted targets that unnecessarily clutter the database and may give rise to phantom appearances related to the deleted targets. For very large databases built from many merges, the clutter may adversely affect performance. This procedure cleans up most of these artifacts.

WARNINGS:

The procedures are not reversible and there is no guarantee that the results are perfect or what you may want. Use at your own risk and MAKE A BACKUP first.
Shared events are deleted from all sharees if the Principal is not found.
Unused Media, Sources, Places, Addresses are deleted.

This procedure must be followed by these steps:

RootsMagic > File > Database Tools:
1. Rebuild Indexes
2. Compact database

DeletePhantoms.sql Original query, fairly comprehensive and aggressive but overlooked unused Sources and Repositories.
DeletePhantoms2.sql As original except less aggressive as it does not delete unused Media, Places…
DeletePhantoms3.sql More comprehensive and aggressive than original, deleting unused Sources, Repositories, unused custom Fact Types and Roles not associated with a remaining fact type. 2013-08-07 corrected error where repository use was mistakenly based on CitationID not SourceID. 2019-02-12 now deletes orphaned TreeShare links.

-- DeletePhantoms.sql
/*
2012-10-27 Tom Holden ve3meo
2012-11-13 added query to correct PersonTable.SpouseID to 0 for those whose family (spouse)
was deleted.
 
Cleans database of unused records in most tables,
some of which may give rise to phantoms in displays and reports.
These arise from incomplete cleanup by RootsMagic when persons, families, places, sources
are deleted directly or by merging and, most certainly, by simple SQLite deletions
such as DeleteByColorCode.
 
Users may find this cleanup to be too aggressive and are advised to comment out
any sections that would delete unused items they may wish to preserve, e.g.,
unused sources or places.
 
There is an as-yet-unaddressed problem with shared events arising from the deletion of the Principal;
the event is deleted so there is nothing to share. It would be desirable to convert shared events to
unshared singular events for every sharee.
 
*/
-- Set SpouseID=0 for persons with invalid FamilyID (TAH 2012-11-13)
UPDATE PersonTable SET SpouseID=0
  WHERE SpouseID NOT IN (SELECT FamilyID FROM FamilyTable)
;
 
-- Delete Child from ChildTable if Child does not exist in PersonTable
DELETE FROM ChildTable WHERE ChildID NOT IN (SELECT PersonID FROM PersonTable);
 
-- Delete Child from ChildTable if FamilyID gone from FamilyTable
DELETE FROM ChildTable WHERE FamilyID NOT IN (SELECT FamilyID FROM FamilyTable);
 
-- Delete Names from NameTable if OwnerID is not a PersonID in PersonTable
DELETE FROM NameTable WHERE OwnerID NOT IN (SELECT PersonID FROM PersonTable);
 
-- Delete Individual's Events from EventTable if OwnerID not in PersonTable
DELETE FROM EventTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
 
-- Delete Family Events from EventTable if OwnerID not in FamilyTable
DELETE FROM EventTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
 
-- Clean out Citations that no do not link to an active record
-- Personal
DELETE FROM CitationTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
-- Family
DELETE FROM CitationTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
-- Event citations
DELETE FROM CitationTable WHERE OwnerType = 2 AND OwnerID NOT IN (SELECT EventID FROM EventTable);
-- Alternate Name citatoins
DELETE FROM CitationTable WHERE OwnerType = 7 AND OwnerID NOT IN (SELECT NameID FROM NameTable WHERE NOT IsPrimary);
 
-- Delete Citations having lost their Source
-- DeleteUnsourcedCitations.sql
-- 2010-01-29 ve3meo
DELETE FROM CitationTable
  WHERE CitationID IN
   (SELECT CitationID FROM CitationTable c
      LEFT JOIN SourceTable s ON c.SourceID=s.SourceID
      WHERE s.SourceID ISNULL);
 
 
-- Clean up addresses
-- Addresses that are linked to non-existant persons or families
DELETE FROM AddressTable
 WHERE AddressID NOT IN
 (
  -- Addresses that are linked to existing persons
  SELECT AddressID FROM AddressLinkTable WHERE OwnerType = 0 AND OwnerID IN (SELECT PersonID FROM PersonTable)
  UNION
  -- Addresses that are linked to existing families
  SELECT AddressID FROM AddressLinkTable WHERE OwnerType = 1 AND OwnerID IN (SELECT FamilyID FROM FamilyTable)
  )
 AND
  AddressType = 0
  ;
 
-- Remove orphaned records from AddressLinkTable
  -- Links to non-existing persons
  DELETE FROM AddressLinkTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
  -- Links to non-existing families
  DELETE FROM AddressLinkTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
  -- Links to non-existing addresses
  DELETE FROM AddressLinkTable WHERE AddressID NOT IN (SELECT AddressID FROM AddressTable);
 
-- Delete Unused Media
DELETE FROM MultimediaTable
 WHERE MediaID NOT IN
 (
-- Person media
  SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 0 AND OwnerID IN (SELECT PersonID FROM PersonTable)
  UNION
-- Family media
  SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 1 AND OwnerID IN (SELECT FamilyID FROM FamilyTable)
  UNION
  -- Event Media
  SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 2 AND OwnerID IN (SELECT EventID FROM EventTable)
  UNION
  -- Master Source Media
  SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 3 AND OwnerID IN (SELECT SourceID FROM SourceTable)
  UNION
  -- Citation Media
  SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 4 AND OwnerID IN (SELECT CitationID FROM CitationTable)
  UNION
  -- Place Media
  SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 5 AND OwnerID IN (SELECT PlaceID FROM PlaceTable)
  );
 
-- DELETE Orphaned MediaLinks
  -- Having a non-existant MediaID
  DELETE FROM MediaLinkTable
  WHERE MediaID NOT IN
  (SELECT MediaID FROM MultiMediaTable);
 
  -- Having a link to non-exsiting
    -- Person media
    DELETE FROM MediaLinkTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
    -- Family media
    DELETE FROM MediaLinkTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
    -- Event Media
    DELETE FROM MediaLinkTable WHERE OwnerType = 2 AND OwnerID NOT IN (SELECT EventID FROM EventTable);
    -- Master Source Media
    DELETE FROM MediaLinkTable WHERE OwnerType = 3 AND OwnerID NOT IN (SELECT SourceID FROM SourceTable);
    -- Citation Media
    DELETE FROM MediaLinkTable WHERE OwnerType = 4 AND OwnerID NOT IN (SELECT CitationID FROM CitationTable);
    -- Place Media
    DELETE FROM MediaLinkTable WHERE OwnerType = 5 AND OwnerID NOT IN (SELECT PlaceID FROM PlaceTable);
 
-- Delete Unused Places and Place Details
 -- Places
 DELETE FROM PlaceTable WHERE PlaceID IN
 (SELECT PlaceID FROM PlaceTable WHERE PlaceTable.PlaceType = 0 EXCEPT SELECT PlaceID FROM EventTable) ;
 -- Place Details
 DELETE FROM PlaceTable WHERE PlaceID IN
 (SELECT PlaceID FROM PlaceTable WHERE PlaceTable.PlaceType = 2 EXCEPT SELECT SiteID FROM EventTable) ;
 -- Orphaned Place Details having no Master Place
 DELETE FROM PlaceTable WHERE PlaceType = 2 AND MasterID IN
 ( SELECT MasterID FROM PlaceTable WHERE PlaceTable.PlaceType = 2 EXCEPT SELECT PlaceID FROM PlaceTable) ;
 
 
-- Delete orphaned Tasks
  -- individuals
  DELETE FROM ResearchTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT OwnerID FROM PersonTable);
  -- families
  DELETE FROM ResearchTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
 
-- Delete Headless Witnesses
DELETE FROM WitnessTable
WHERE WitnessTable.PersonID > 0
AND WitnessTable.PersonID
NOT IN
(SELECT PersonID FROM PersonTable)
;

Search & Replace #rmnocase #search #replace #update

Posted on 2013-07-312019-01-27 by Tom Holden

Restricted in RootsMagic

RootsMagic 4 users are sometimes frustrated that they cannot do a search or search/replace on certain fields within the application. RM4’s Search & Replace is especially restricted to a small set of fields: Person name fields, Place name, Multimedia filenames and Notes (General, Family & Facts). SQLite opens up all fields to searching and, at least, some fields for replacing. This page gives some examples of search and replace in a RootsMagic database using SQLite.

Wide open with SQLite

If the SQLIte manager supports a fake RMNOCASE collation, then all fields can be modified else only those fields in a table that are not collated by RMNOCASE. Most managers do not and that includes all free software except SQLite Expert Personal with an extension or SQLiteSpy with its extension. SQLite Developer also does but costs $30. There are attendant risks in faking RMNOCASE. This page was written while using the free SQLiteSpy before the fake RMNOCASE extension became available.

Tables that can be fully edited using SQLite3 without RMNOCASE (including record deletions and additions) comprise 10 of the 22 making up a RootsMagic 4 database:

AddressLinkTable, ChildTable*, CitationTable*, ConfigTable*, EventTable*, ExclusionTable, FamilyTable*, GroupTable, LinkTable*, PersonTable*

* tables so marked have one or more fields of type BLOB, stored in binary format, requiring special treatment. These inlude ActualText, Comments and the XML data stored in the FIELDS field for each citation (the fields and data seen in the Source Details part of the Source screen), fields not accessible from RM4’s S&R.

Search

Every prior SQLite query on this wiki has an example of a more or less complex combination of displayed fields and conditional expressions of the form:

SELECT list_of_fields FROM TABLES WHERE some_condition;

The WHERE clause is the filter which is the basis of our search: we can set the condition to some_field comparator some_expression and only those records having a value satisfying the comparison will be displayed. Unfortunately, the sought-for word or phrase won’t be highlighted but, at least, we have reduced the number of records displayed.

For a complete list of the comparators and the format of SQLite expressions, see SQL As Understood By SQLite. For text searches, the LIKE, GLOB and REGEXP operators are most useful, in increasing order of power and complexity. MATCH is not supported by SQLiteSpy. Suppose we want to find records with the abbreviated “obit” for the full word “obituary” in the Notes field for Persons. These queries will give similar, but not necessarily identical, results:

SELECT Note FROM PersonTable WHERE Note LIKE '%obit %';
--> 312 records
 
SELECT Note FROM PersonTable WHERE Note GLOB '*obit *';
--> 305 records
 
SELECT Note FROM PersonTable WHERE Note REGEXP '.+obit .+';
--> 299 records
 
SELECT Note FROM PersonTable WHERE Note REGEXP '.*[Oo][Bb][Ii][Tt][^U^u].*';
--> 357 records

The first three SELECTs look for “obit “, i.e. “obit” followed by a space. LIKE is case insensitive and does not care where the search pattern occurs; it can match a single or any number of unknown character using the wildcards ‘_’ and ‘%’, respectively. GLOB is like LIKE but is case-sensitive and can also match a character in a group of characters enclosed in square braces, e.g. ‘[CB]’ – note that GLOB use different wildcard characters ‘?’ and ‘*’. The first REGEXP search pattern is case and position sensitive – the .+ wildcard requires one or more characters so “obit ” at the beginning or end of the value is ignored; had we used ‘.*obit .*’ pattern, the results would have been identical to GLOB (the .* wildcard matches 0 or more characters).

The second REGEXP search pattern sweeps up every case-insensitive pattern of “obit” not followed by a “u”, e.g., “obit)”, “obits”, etc., not found by the other comparisons. REGEXP is much more flexible and powerful; for the regular expression syntax almost fully supported by SQLiteSpy, see the ICU User Guide.

BLOBs

Curiously, while the PersonTable NOTE field is defined as type BLOB, SQLiteSpy quite happily displays and operates on it as text without special measures. Such is not the case for all BLOB fields – the FIELDS field of CitationTable, for example, is displayed in the spreadsheet view with a hexadecimal value and the value display below merely says “Blob”. Some other SQLite managers have a BLOB interpreter or editor that automatically opens on a BLOB field value. To read the FIELDS field, we need to temporarily change the values to type TEXT:

SELECT CAST(FIELDS AS TEXT) FROM CitationTable;

For string comparisons, we must likewise cast the value as text:

SELECT CAST(FIELDS AS TEXT) FROM CitationTable
 WHERE CAST(FIELDS AS TEXT) GLOB '*Page</Name><Value>Vol. 20*';

This searches citations for the Page field value beginning with “Vol. 20”.

Replace

Having identified a set of records that satisfy some search criterion, we could proceed to edit each one manually using SQLiteSpy’s editing tools. Keep the results in one screen and open a second SQL page on the table for editing. Use the rowid (add ROWID to the SELECTs above) from the query to find the record in the table editor. This could be tedious for a large set so we would like a way to do a global replace.

While I was disappointed to find that one cannot use a regular expression search and replace in SQLiteSpy, SQLite does support a string search and replace function replace(x,y,z), described under Core Functions of SQL As Understood By SQLite. These three queries demonstrate:

the modification of the FIELDS value in just the result set as text, where “Vol. 20” is replaced by “Vol.XXX 20”,
then those results cast as BLOB (note that “AS BLOB” is unnecessary by default, written here for clarity),
and the next shows how we store the modified field to the database:

-- show replace() operating on results only
SELECT REPLACE(CAST(FIELDS AS TEXT),'Page</Name><Value>Vol. 20', 'Page</Name><Value>Vol.XXX 20')
 FROM CitationTable
  WHERE CAST(FIELDS AS TEXT) LIKE '%Page</Name><Value>Vol. 20%';
 
-- show casting above back to BLOB
SELECT CAST(REPLACE(CAST(FIELDS AS TEXT),'Page</Name><Value>Vol. 20', 'Page</Name><Value>Vol.XXX 20') AS BLOB)
 FROM CitationTable
  WHERE CAST(FIELDS AS TEXT) LIKE '%Page</Name><Value>Vol. 20%';
 
-- show record being updated with revised BLOB
UPDATE CitationTable
 SET FIELDS = CAST(REPLACE(CAST(FIELDS AS TEXT),'Page</Name><Value>Vol. 20', 'Page</Name><Value>Vol.XXX 20') AS BLOB)
  WHERE CAST(FIELDS AS TEXT) LIKE '%Page</Name><Value>Vol. 20%';

In the UPDATE query, we could have deleted the WHERE clause without affecting any other record. If REPLACE() does not find a search string in the target, it merely returns the target.

Promo

SQLiteSpy User Experience

RMtrix User Experience

Example – SQLiteSpy Quick Integrity Check

Example – Change fact type globally

Discussions & comments from Wikispaces site

SqliteSpy copying to the clipboatd

How to have query accept input from the keyboard.

Why? How?

Main Script

Mismatched FMNO Report

Earlier GEDCOM Solution

Alpha-7 21 Jan 2013

Caveat Emptor!

Integrity Check, Reindex and Vacuum – Beware!

Download and Installation

Won’t run?

Discussions & comments from Wikispaces site

Installed 64 bit version

After installing

RMTrix64.zip

Comment: “The following menu items are active (red text…”

Source Code

Why?

Practically Identical Master Sources

Outcomes

Download

Usage

Restricted in RootsMagic

Wide open with SQLite

Search

BLOBs

Replace