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:

1. the modification of the FIELDS value in just the result set as text, where “Vol. 20” is replaced by “Vol.XXX 20”,
2. then those results cast as BLOB (note that “AS BLOB” is unnecessary by default, written here for clarity),
3. 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.