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.

Rebuild Indexes and Update Birth and Death Years #update #date #birth #death #index #speed #rmnocase

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

Rebuilding the indexes of large databases using RootsMagic’s File > Database tools > Rebuild Indexes function is ponderously slow, still at version 6.0.0.2. A 162000 person database took over 100 minutes. This page provides procedures that take but a few minutes.

In RootsMagic version 5.0.2, the Rebuild Indexes tool was introduced under Menu > File > Database Tools; it not only rebuilds the SQLite database indexes which must be kept accurate but also updates the BirthYear and DeathYear columns of the NameTable. These values are displayed optionally in the sidebar Index and are used in some other views and reports. Prior to 5.0.2, there was no tool to rebuild the SQLite indexes and when the sidebar BirthYear and the Birth fact got out of sync (they still can), the only way to update them in RM was to edit the Birth fact and save it. To help find such mismatches, the following procedures were developed:

I do not recall for certain why no batch update procedure was published at the time. Perhaps it had something to do with the RMNOCASE issue, now worked around by RMNOCASE – faking it in SQLite Expert, command-line shell et al or RMNOCASE – faking it in SQLiteSpy and the fact that whatever index discrepancies between the fake and the real collation can be resolved by running RM’s Rebuild Indexes!

I suspect that what takes RM so long is not the SQLite Re-indexing but rather the recalculating and display of the Birth and Death years because re-indexing with the fake collation in SQLiteSpy takes less than two minutes with this big database. So the shortcut procedure outlined here relies on the assumption that RootsMagic’s SQLite database engine is as efficient as SQLiteSpy’s and completes the critical database indexing in a similar length of time, if such re-indexing is required.

Run RM’s Menu > File > Database tools > Test database integrity. Despite the warning, you can expect it to complete in tens of seconds, not tens of minutes, on even a fairly low end computer, varying with size.
If the result of 1 is OK, skip the next step.
If the result of 1 is NOT OK, then run RM’s Rebuild Indexes. After a few minutes, use the Windows Task Manager to stop RootsMagic. Return to step 1.
Once database integrity is OK, then it is safe to proceed with SQLiteSpy to update the Birth and Death Years using the query below.
Having completed the SQLite query, close and reopen RootsMagic Explorer to view the results. Retest database integrity to be satisfied.

UpdateBirthDeathYears.sql
2012-12-06
Rev A: revised to respect the Primary fact if there are multiples. Completed update of 162,000 person database in 25 seconds.
Rev B: Christen, Baptism and Burial, Cremation now alternate Birth, Death dates in that order. BC dates. Update time doubled to 55 seconds.
Some may prefer the faster, leaner version.

-- UpdateBirthDeathYears.sql
/*
2012-11-14 Tom Holden ve3meo
2012-12-06 revA: priority to first record set to Primary, else first record
 when multiple Birth or Death facts.
           revB: incorporated Christen, Baptism as alternate Birth facts; Burial, Cremation
 as alternate Death facts - in that order. Same priority for Primary facts in same type.
 Now supports BC dates.
 
Sets Birth and Death years as seen in the sidebar index and various other
reports and displays to match the corresponding facts.
 
Close and reopen RootsMagic Explorer to see the results.
 
*/
UPDATE NameTable
  SET
    BirthYear=
    (
      SELECT BirthYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE
          WHEN E.DATE REGEXP '[DR]..dddd.+'
          THEN CAST(MAX(SUBSTR(E.DATE,3,5),0) AS NUMERIC)
          ELSE 0
        END AS BirthYear, E.IsPrimary
       FROM
         Nametable N ,
         Eventtable E
       WHERE
         N.Ownerid = E.Ownerid AND E.Eventtype IN (1,3,7) AND E.Ownertype = 0 AND +N.IsPrimary
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Births
      WHERE NameTable.OwnerID = Births.OwnerID
     ),
    DeathYear=
    (
      SELECT DeathYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE
          WHEN E.DATE REGEXP '[DR]..dddd.+'
          THEN CAST(MAX(SUBSTR(E.DATE,3,5),0) AS NUMERIC)
          ELSE 0
        END AS DeathYear, E.IsPrimary
       FROM
         Nametable N ,
         Eventtable E
       WHERE
         N.Ownerid = E.Ownerid AND E.Eventtype IN (2,4,5) AND E.Ownertype = 0 AND +N.IsPrimary
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Deaths
      WHERE NameTable.OwnerID = Deaths.OwnerID
    )
;

Discussions & comments from Wikispaces site

Inline comments

Convert Database to NOCASE #rmnocase

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

There are a number of reasons that it would be desirable or necessary to get out from under the limitations imposed by the proprietary RMNOCASE collation sequence used for indexing (comparing strings) in RootsMagic 4 and 5. Without a SQLite manager that can provide a fake substitute with the same name, there are several key fields that cannot be edited and tables that cannot have rows added or deleted along with added complexity in queries. Until recently, the only such manager that I had come across was SQLite Developer, the license for which is around $30 for personal use, an obstacle to some not to mention that it is less than an ideal tool. Now, SQLiteSpy can do it with one extension and SQLite Expert can do it with another, superior one.

In Comparing Two RM Databases, Jerry tried unsuccessfully to use SQLite Compare which has no fake RMNOCASE support. That stimulated the idea that simply exporting the data of each database to two other non-RootsMagic SQLite databases would enable SQLite Compare to compare the data with the integral NOCASE collation instead of RMNOCASE. That led to the development of the following queries:

RM4_CREATE_as_DB3_NOCASE.sql This query was built by copying the database schema of the RM database in data definition language (DDL) and replacing all instances of RMNOCASE with NOCASE.

RM4_Copy_data_to_DB3_NOCASE.sql Download and save both to your RM/SQLite workspace. See bottom of page for the RM5 versions.

Usage:

Open your SQLite manager
Create and open a new empty database with the default .db3 extension (this is not mandatory, it could be .rmgc if you intend to open it with RootsMagic)
Load the first query file and execute. The new database structure is created.
Execute this statement, substituting the full pathname to your source RootsMagic database file between the single quotes (and change the RM# to suit): ATTACH DATABASE ‘full URI to the RM4 file’ AS RM4 ;
Load and execute the second file – now you have all the data from your RM4 database in a DB3 with standard collations.
Execute this statement (change the RM# to match step 4) to disconnect your RM database: DETACH RM4 ;

Now you are free to do what you want with the data you have exported from RM4.

For RM5, as the database structure is different, so too must the Create and Copy queries be different from those for RM4.
RM5_CREATE_as_DB3_NOCASE.sql
RM5_Copy_data_to_DB3_NOCASE.sql

Discussions & comments from Wikispaces site

thejerrybryan

This all works, now that I’ve had time to play with it

thejerrybryan
18 December 2011 04:28:54

All I’ve done so far is to make two very small test databases, test1.rmgc and test2.rmgc that originally were identical. I made a minor change to test2.rmgc. Next, I copied the databases to test1.db3 and test2.db3, respectivedly, where the db3 versions had the NOCASE option rather than the RMNOCASE option and where I used Tom’s scripts and directions to accomplish the copy. Finally, I ran SQLiteCompare against the db3 versions of the databases, and all was well. SQLiteCompare did not complain about RMNOCASE, and it accurately and easily identifed the differences between test1.db3 and test2.db3.

One thing I haven’t tried yet is to be sure that RM5 itself can operate directly on the NOCASE version of the databases. As Tom suggests, I’m sure it will work fine. If it does, it will greatly facilitate testing. What I will be doing will be to drag-and-drop (or GEDCOM Export/Import) between two databases and analyze what data, if any, is lost or changed by the process.

Jerry

thejerrybryan

thejerrybryan
18 December 2011 16:27:05

I have confirmed that RM5 can operate on a NOCASE version of a database just fine. So I can make a standard RMNOCASE version of a database and convert it to a NOCASE version of a database just fine. And I can compare NOCASE versions of two databases just fine using the SQLiteCompare utility, which is a very wonderful utility.

But nevertheless, I’m totally unable to complete the project I wished to complete. What I wished to do was to drag-and-drop or Export/Import a RM5 database and then to compare the original with the copy to see what data might be lost in the process. But beyond a very small test database with only one or two people with one or two facts, the project is really hopeless. Here is the problem.

Suppose I start with a new, blank database and add one individual. Suppose I then add a birth fact to the individual, add a death fact to the individual, and delete the birth fact. The EventID for the birth fact was 1 and the EventID for the death fact was (and still is) 2. After a drag-and-drop operation into a new database, the EventID for the death fact is 1. Multiply this effect by tens of thousands of people and hundreds of thousands of events, and the new database will have hundreds of thousands of differences with the old databases, even if the drag-and-drop operation is perfect.

I only kick myself for not thinking about such an obvious problem before even starting this project.

Jerry

Rebuttal by Jeff La Marca #sourcetemplates #sources

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

One of the three source templates compared in the study Source Templates, A Comparative Example.pdf was Jeff La Marca’s Simplified Citations – Census. As it did not fare well in the tests, he has objected strenuously and frequently. To eliminate his need to do so every time I post something that refers to the study, I am including here links to his critiques on his website and in the RootsMagic Forums and invite you, dear reader, to review them and judge for yourself. On reviewing his arguments, I think I have identified why he had such a reaction – see Comment below.

Simple Citations: Making Life Easier for Family Historians Jeff La Marca’s website devoted to his source templates.
Commentary on MyFreeForm “Study” Jeff’s defence of Simple Citations and attack on the methodology of the study.
Problems with MyFreeForm Template Jeff’s attack on the !MyFreeForm3 template.
Forum Discussions:
A Trio of Source Templates (compared)
Adventures in Extreme Splitting
Places / locations on source templates
Whither the Gedcom?

Comment by Tom Holden

I wonder if this entire furor over my study is “Much Ado About Nothing” stemming from Jeff’s missing its basic point and my failing to express it clearly enough. What I advocated in the study and in forum discussions is to use the built-in source templates, Jeff’s if you want, citations from FamilySearch, Ancestry.com, the British Library, on-line generators such as Citation Machine et al to draft a citation that you parse into and save via the Free-Form template, if you want to avoid certain problems, or optionally, !MyFreeForm3 to avoid the same problems and gain an enhancement. I have also advocated for improvement in the RootsMagic application that would mitigate some of these problems and expedite the process of documenting and citing sources.

The point is that the storing of citations under most RM source templates, including Jeff’s custom ones, causes problems for users who may disagree with a template’s split between Master Source and Source Detail (Citation Detail) and produces less than satisfactory, in some cases unacceptable or objectionable, outputs of foot- or end-notes and bibliography. What I concluded from the study was that Free-Form was the storage format most compatible with all the outputs available at the time and that a custom template mimicking Free-Form with some enhancements was about equally compatible while providing the opportunity for Short Footnotes shorter than possible with Free-Form. This !MyFreeForm3 template also allowed the user to set the punctuation between the Master Source part of the footnote and the Source Detail part – that enhancement was motivated in part by Jeff’s own use of “ || ” as the separator in his Simple Citations.

My recommendation was to not use Jeff’s Simple Citations source templates as a storage format, likewise for most of RootsMagic’s built-in source templates. It was not that his source templates should not be used for drafting footnotes and bibliography for parsing into Free-Form, although I pointed out where I thought changes were needed based on the sample source I used (in all three source template sentences). If they suit your style, they readily parse at the “ || ” separator or you can split them elsewhere, according to your position in the “lumping-splitting” spectrum. Unwanted output, such as the Misc.Ref.Num., can be stored in Citation Text Comments. Perhaps that distinction between a source template for storage and source templates for drafting was not made strongly enough.

I think the fundamental cause of confusion is that we tackled two different problems which resulted in what appeared to him as competing and contradictory solutions. From my perspective, they are complementary. Jeff’s solution with Simple Citations is to the problem, and one we share but not addressed by my study, of inputting sources to RootsMagic, starting with having to choose from a plethora of templates. His approach was to seek a universal template from which a small set of sub-templates could be devised to meet the majority of types of sources. This was a commendable effort and one in which I expressed great interest.

The problem my study tackled was that of the outputs. It was disappointing to me that Jeff’s Census template had as many problems as it did (mostly correctable with some change in design) but not surprising that it shared output problems common to most of the built-in source templates, inherent in the design of the RM template system. It became clear that there were serious problems (for me and for others) which could only be resolved by not storing the sources under most of the templates but under Free-Form OR by improvements in the software. I follow the basic principle that inputs and procedures are best designed with reference to the required outputs, i.e., work from the outputs backwards to determine how best to design and use a system to get the results needed.

RootsMagic 5 had as outputs for citations, at the time of the study, three principal output types using citations: printable reports, web pages/sites, and GEDCOM. By 6.2, it has added a new website design and source transfer to FamilySearch Family Tree. Citations delivered through these five different output channels are not altogether consistent (with one newly realised exception) for those stored under the built-in Source Templates except for Free Form and about 17 others of the 413 included. As GEDCOM export to 3rd party software and websites is important to me and to many others, my study was aimed at comparing all the outputs for sources available at the time given the same source citation, one which is generated by an external system (Ancestry.com) and stands up as fulfilling all the requirements of evidence-based reporting as espoused by recognised and respected authorities. The test was to see how closely the outputs of the three source templates could come, via all three channels, to delivering the content of the original citation, completely, accurately, unambiguously, grammatically and acceptably punctuated.

The two new citation output channels and possible bug fixes or other changes since RM 5 may suggest an extension of the study is required but it appears that the five outputs fall into two main groups:

Those that output the Footnote sentence as seen in the Citation Manager (seemingly all but GEDCOM – to be confirmed).
Those that output a hybrid of a part of the Footnote sentence plus a series of field values (to GEDCOM TITL plus PAGE tags, respectively).

Thus the study’s observations may also apply to the new channels if it can be seen that their footnotes belong in one or the other group and not in some third one.

Within the first group, only printable reports deliver also the Short Footnote sentence for citations subsequent to the first of the same source. The old website generator, now in RM6 as “Create HTML files” joins printable reports in having the option to print the Bibliography sentence describing the Master Source for 1 to many citations. Jeff dismisses the latter in his template designs as needing to be no different from the Footnote sentence which is inconsistent with the purpose of a Bibliography – a list of each unique source, regardless of the number of times it has been cited, i.e., all the Master Sources cited in the report, not all the citations.

For those who wish to come close to a finished product with printable reports and HTML pages from RM, consideration should be given also to the quality of the Short Footnotes and Bibliography emanating from a chosen template, given its mapping of source data to its input fields as suggested by the template hints. The study examined all these source outputs and found that Simple Citations – Census needed improvement. Of course, a user is free to not include a Bibliography and Short Footnotes should be suppressed if the Endnote output is selected with the option “Reuse endnote numbers where possible”, thus obviating the need to remedy those sentence templates.

Storing the resulting sentences from any template in free-form affords the opportunity to fix errors without modifying the template, which might have adverse effect on outputs from other sources sharing the template. One is not locked into the straitjacket of a given source template.

Improvements to RootsMagic

The following, in no particular order, are enhancement requests I have made in the past that should result in better quality GEDCOM export and import, promote the use of the built-in source templates, expedite the creation of new sources and citations, achieve greater consistency among citations of the same type of source:

Sentence templates within each Source Template for export to GEDCOM tags AUTH, TITL, PUBL, PAGE. This will eliminate the current corruption of citations from many source templates, exploit the standard fully by using the AUTH and PUBL tags where appropriate. Free-Form would continue to export solely to TITL and PAGE.
Import GEDCOM sources having the AUTH and/or PUBL tags via a GEDCOM compatible Source Template instead of Free-Form.
Source Conversion Utility, 1st priority conversion to Free-Form from all other Source Templates. 2nd priority is conversion between any pair of different templates, including Free-Form.
Construct a Free-Form source using the input screen defined by any Source Templates; implies each Source Template has sentence templates for outputs to Free-Form fields: Footnote, Short Footnote, Page, Bibliography. The Source Conversion Utility could rely on these sentence templates. The PAGE export sentence and the Free-Form Page sentence can be the same by default. A register of what template was used for inputting to a Free-Form source would be useful for later recall. This enhancement promotes the use of the built-in templates for style and consistency while editing the Free-Form loosens the straitjacket.
Browse sources that use a Source Template from the Source Template manager and copy one to a new source; browse citations that use a selected source and copy to a new source/citation
Browse citations of a source from the Source Manager and copy to a new citation of the same source or to a new citation of a new source.
.

Copy RIN to REFN #update #insert #refno #rin

Posted on 2013-07-022020-05-16 by Tom Holden

Some RootsMagic users would like to have a utility to add a Reference Number (REFN) fact to persons that would contain the current Record Number (RIN). The intent is that this REFN would persist through GEDCOM exports and imports, drag’n’drop transfers, and possibly merges, thus providing a reference to the record’s history. I have used this idea to maintain a reference to a family tree published by a cousin from an early version of PAF, to facilitate finding people in his book. There could be many reasons for snapshotting the current RIN. This SQLIte query and attendant procedure makes it easy to do so for any number of persons in a RootsMagic database.

Use RootsMagic:

Backup your database.
Add a REFN fact to any person in your database. You can fill its Description field with garbage or leave blank as you will delete it later. Do NOT add any more facts to anyone until you finish this procedure.
Define a Named Group of persons to whom you want to add REFN facts containing their RINs.
Close RootsMagic (not required but safety first!).

Then use a SQLite manager such as SQLiteSpy:

Open your database
Copy the SQLite query below into the SQL Editor
Open the EventTable and get the EventID from the last row (its EventType should be 35 which is the REFN type).
Substitute the value of EventID from step 2 into the SQLite query in the Editor in place of ‘??EventID??’
Open the LableTable and get the ~~LabelID~~ LabelValue of the row whose LabelName corresponds to the name of your target Group.
Substitute the value of ~~LabelID~~ LabelValue from step 5 into the SQLite query in the Editor in place of ~~‘??LabelID??’~~ ‘??LabelValue??’.
Execute the query.
Assuming no error message, close your database (not required but safety first).

When you next open your database with RootsMagic, you should see the newly added REFN facts in every member of your Named Group. Return to the person you initially added the dummy REFN and delete it.

Here’s the query: rev 2020-05-16 LabelValue instead of LabelID

INSERT OR ROLLBACK INTO EventTable 
        (EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate,
         IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note)
  SELECT EventType, OwnerType, N.OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate,
         IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, 'RIN '||N.OwnerID, Note
   FROM (SELECT P.PersonID AS OwnerID FROM PersonTable P, GroupTable 
         WHERE OwnerID >=StartID AND OwnerID <= EndID 
          AND GroupID=??LabelValue??) N,
         EventTable E 
          WHERE EventID=??EventID?? AND EventType=35;

It’s a variant of the Copy Fact to Group query. The differences are few but one significant change was to get the OwnerID from the PersonTable, not the NameTable, as the latter resulted in the addition of multiple facts to the same person if that person had multiple names.

As written, the Description field of the REFN fact will contain ‘RIN n’, where n is the PersonID number. You can change the query to put out some other string in combination with the PersonID by editing the part

'RIN '||N.OwnerID

If you want leading zeroes or blanks so that the REFN is right-justified, the following will work (increase the number of blanks or zeroes if any RINs>9999):

'RIN'||SUBSTR('   ',LENGTH(N.OwnerID))||N.OwnerID -- leading blanks
'RIN'||SUBSTR('000',LENGTH(N.OwnerID))||N.OwnerID -- leading zeroes

All of the added REFN facts will have every other parameter identical to that of the dummy REFN fact, including the same EditDate, hence it is preferable to use a dummy created on the same day that you run the procedure.
If you want the REFN to appear first in the Edit Person screen, then set the SortDate of the dummy REFN to 1 before running the procedure.

Discussions & comments from Wikispaces site

Forcing REFN to be first fact

DaiyuHurst
17 September 2018 18:54:37

I like my REFNs up top. Before even birth. An easy way to do this is to use this slightly altered SELECT statement.
Leaving out the FROM clause for brevity:

SELECT EventType, OwnerType, N.OwnerID, FamilyID, PlaceID, SiteID, DATE, ‘1’,
IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, ‘RIN ‘||N.OwnerID, Note

The curious thing about doing this is, when you examine the individual records in RM, the Sort Date field will no longer be populated, but the effect of the procedure was good, as the REFN fact is the very first fact in each record.

I hope this is useful to someone.

DaiyuHurst

DaiyuHurst
17 September 2018 18:58:30

Oddly, I did not see the last paragraph describing this.

RM Infrastructure to support FSFT

Posted on 2013-06-242019-01-27 by Tom Holden

This is just curiosity more than anything, but does anybody here know how the infrastructure in RM works to support FSFT? I can’t find any tables that support FSFT, for example, but I’m probably just missing them. For example, how does RM know who in your RM database has been matched against somebody in FSFT and who has not? Is the required data maybe stored on your account at FSFT itself rather than in your RM database?

Thanks,
Jerry

LinkTable

The LinkTable appears to be key to the linkage between RootsMagic and FamilySearch Family Tree. There is one record for each person matched.

DDL

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 INDEX idxLinkExtId ON LinkTable (extID);

Interpretation

This is based on a very limited test with a small set of persons.

Field Name	Type	Key	Typical Values	Notes
LinkID	I	PRIMARY	1,2,3,…	TBA, not obviously linked to any other table despite sharing name with AddressLinkTable
extSystem	I		1	1 probably indicates FamilySearch Family Tree; other values may be available for other external systems.
LinkType	I		0	0 probably indicates a person
rmID	I		1,2,3…	(probably, If LinkType = 0, Then) rmID = PersonID or RIN in RM
extID	T		Text code	(probably, If extSystem = 1 AND maybe LinkType=0, Then) extID = FSFT ID, e.g. “9WMY-1KJ”
Modified	I		0,1	Appears to be 0=False, 1=True or the complement but unclear what triggers it
extVersion	T		hexadecimal string len=40	Changes with changes to the data shared with the FSFT person; thus likely a key component to the comparison and detection of differences over time.
extDate	F		0	unused?
Status	I		0	unused?
Note	B		null	unused?

Query for Sources Without Media #sources #media

Posted on 2013-06-192022-04-29 by thejerrybryan

I haven’t posted here in rather a long time. Here is a little query I wrote to identify Sources without media. By “Sources”, I mean the same thing that RM calls Master Sources, but I think that they should just be called Sources along with their associated Source Details in common with having Places and Place Details. We do not need to speak of Master Places.

This query is a part of my project to move all sourcing information into the Source area of RM rather than storing some of the sourcing information in the Source Details area of RM. As described on the RM Forums in the thread “Adventures in Extreme Splitting”, I think the tools for managing Source Details are completely inadequate, so I just decided to quit using them. The only way to quit using them is to move all sourcing information into the Source area of RM. And as a part of this project, I’m trying to have at least one media file associated with every Source. This query therefore identifies Sources without media.

I doubt that this query will be of much value to anybody but me, but I think it would be pretty easy to adapt it into being a “Source Details without media” query. Also, it suggests some ways to display information about media files that are totally unavailable within RM itself.

Jerry

sources_without_media.sql

Added 06/18/2013

While I was at it, I decided to do a query to display the Master Source name along with the associated footnote sentence. I couldn’t figure out how to do it with SQLiteSpy so I did it with Access. I need to study further one of Tom’s pages on parsing XML. It might be much easier with his technique than with what I was trying to do. Also, it only worked for me because I was using the built-in Free Form source template, so all I had to do was to look for the <Footnote> tag in the XML.

The reason I had to use Access is that the INSTR function is not yet supported in SQLiteSpy, which is my SQLite manager of choice. Tom documented some alternative mangers that do support INSTR. But when I tried to download them, I got into a messy situation where the download sites were trying to download all manner of “annoy-ware” along with the SQLite manager. I was very uncomfortable with what was going on, so I abandoned the downloads and switched to Access instead for this particular query. In case this might help anybody despite that fact that it’s Access, here it is.

SELECT Left(S.Footnote,S.Footnote_Len) AS Footnote, S.Name
FROM (SELECT SS.Name, SS.Footnote, InStr(1,SS.Footnote,"</Value></Field><Field>")-1 AS Footnote_Len
      FROM (SELECT SSS.Name, Mid(SSS.Fields,SSS.Footnote_Begin_Less22+22) AS Footnote
            FROM (SELECT SSSS.Name, SSSS.Fields, InStr(1,SSSS.Fields,"Footnote") AS Footnote_Begin_Less22
                  FROM (SELECT Left(SSSSS.Name,Len(SSSSS.Name)) AS Name, StrConv(SSSSS.Fields,64) AS Fields
                        FROM SourceTable AS SSSSS) AS SSSS) AS SSS) AS SS) AS S
ORDER BY footnote

The StrConv function is sort of the Access version of CAST. The argument 64 is not a length. Rather, it’s a flag of what type of conversion is required. InStr is a string searching function which I believe is identical to INSTR in SQLite. The fact that InStr would work in Access is why I used Access.

Here is a bit of what the report looks like. I like the report because it makes it very easy to eyeball various footnote sentences for consistency. This is a genealogical comment rather than an SQLite one, but I’m not very happy with some aspects of the footnote sentences. For example, I often don’t have the marriage book number or page number – only the license number. But that’s because Knox County does not give public access to the marriage books and the information I get from them usually only has the license number. But the license number plus the year actually is sufficient to uniquely identify the record.

Just as a reminder: because of my Extreme Source Splitting strategy, 100% of the footnote sentence may be found in the Master Source area of RM, which is the whole point of this exercise. No matter how many times the Master Source is cited, I can make any needed edits or corrections to the citation just one time and the change takes place for all instances of the citation.

And as a further reminder (and as documented in the sources_without_media.sql query above), every one of these Master Sources and footnote sentences will be supported by an appropriate collection of media files – and I only have to process each media file one time and it immediately is linked to every citation where the respective Master Source is used, again which is the whole point of this exercise.

Jerry

   Master Source Name                                    Footnote Sentence

Added 6/19/2013

Armed with the new version of SQLiteSpy, I rewrote the footnote query directly in SQLite rather than it having to be in Access. The query is still dependent upon the footnote having been generated by the built-in FreeForm template. Moving forward, I would like to be able to switch over to real Source Templates that place all the data into the Master Source. But if I do, I would still very much like to be able to run something equivalent to this query to display my footnote
sentences in the context of other footnote sentences.

I’m cognizant of the fact this query could have been written without as many levels of nested subqueries, but the subqueries made the query extremely easy to write. Several manipulations have to be performed on the XML to coerce it into being the footnote sentence, and each level of subquery performs one of the needed manipulations.

Jerry

SELECT S.Name AS Name, SUBSTR(S.Footnote,1,S.Footnote_Len) AS Footnote
FROM (SELECT SS.Name, SS.Footnote, INSTR(SS.Footnote,'</Value></Field><Field>')-1 AS Footnote_Len
      FROM (SELECT SSS.Name, SUBSTR(SSS.Fields,SSS.Footnote_Begin) AS Footnote
            FROM (SELECT SSSS.Name, SSSS.Fields, INSTR(SSSS.Fields,'Footnote')+22 AS Footnote_Begin
                  FROM (SELECT SSSSS.Name AS NAME, CAST(SSSSS.Fields AS TEXT) AS Fields
                        FROM SourceTable AS SSSSS) AS SSSS) AS SSS) AS SS) AS S
ORDER BY Footnote

Discussions & comments from Wikispaces site

ve3meo

Inline comment: “INSTR function is not yet supported in SQLiteSpy‍,”

ve3meo
04 September 2018 01:36:57

ve3meo Jun 18, 2013

The current version of SQLiteSpy does support INSTR.
thejerrybryan Jun 18, 2013

Thanks for the update. The newest version appears to be 1.9.3. However, I seem to have created a problem for myself. Namely, there is something wrong with RMNOCASE and SourceTable. Something so simple as “SELECT SourceID from SourceTable” throws the RMNOCASE error, and SourceID is a numeric field. I can’t even double click on the SourceTable just to view the whole thing. It appears to be the case that SourceTable is the only table with this problem.

Jerry
thejerrybryan Jun 18, 2013

SELECT * FROM SourceTable does work, however.
ve3meo Jun 18, 2013

This does not make sense – something is royally screwed up. Even a SELECT of a field defined with the RMNOCASE collation should not throw the error unless it is sorted. And under Options > Options > Extensions, do you have RMNOCASE_fake-SQLiteSpy.dll? If so, then there should be no such errors on any RMNOCASE defined field. In SQLiteSpy, run REINDEX and see if that clears the problem. When you return to RootsMagic, use its Rebuild Indexes tool before resuming normal operations.

Tom
thejerrybryan Jun 18, 2013

I hadn’t applied the fake RMNOCASE since I upgraded to 1.9.3. I have now done so, and the problem is solved. But as you say, there should be no error unless something is sorted. It’s almost like 1.9.3 is sorting something behind the scenes that we don’t know about. And the error is thrown even if I explicitly override the collation with NOCASE.

Also, I was wrong about it being just SourceTable. The error also occurs in AddressTable, MediaLinkTable, ResearchTable, and SourceTemplateTable. These are tables I never run queries against, so the problem could have been there forever. And I didn’t even try to run against SourceTable after I realized that INSTR wouldn’t work with my current version of SQLiteSpy.

If you have 1.9.3, you might try temporarily taking away the fake RMNOCASE collation to see if you also have the problem. Indeed, if you widen the window pane that contains the list of tables on the left side of the screen, the tables in question all have an error message beside them. You don’t even have to run a query or double click one of the tables to see the problem.

After removing the fake RMNOCASE collation, you will have to shut down SQLiteSpy and restart it to see the effect, and vice versa when you put it back.

Jerry
thejerrybryan Jun 18, 2013

On a lark, I restored 1.9.0. It does not have the RMNOCASE problem when the fake RMNOCASE collation is not installed. Which is to say you can access all the tables if you don’t sort. Without the fake RMNOCASE collation, you obviously have to do something to deal with RMNOCASE if you do sort. But the problem I’m describing was definitely introduced sometime between 1.9.0 and 1.9.3.

Jerry
ve3meo Jun 18, 2013

Too late for me now to do the testing you suggest. Between SQLiteSpy 1.9.0 and 1.9.3, SQLite itself has changed and I would suspect the problem or effect of a missing collation lies there. That should also manifest itself in the DOS executable of SQLite.
ve3meo Jun 18, 2013

The behaviour you describe is now inherent in SQLite itself (I see it in the Windows command line shell) and is not the fault of SQLiteSpy. ‘Why’ is a mystery but it may have something to do with query optimisation or auto-indexing. Or maybe it is an unwarranted bug.
thejerrybryan Jun 18, 2013

So I guess the fake RMNOCASE collation is now the only RMNOCASE solution for those tables that have the problem I’m describing. Even explicitly overriding the collation in a query does not take care of the problem. So I’ll probably just go with the fake RMNOCASE from now on and not even try to influence collation explicitly in my queries.

Jerry
ve3meo Jun 18, 2013

And don’t update your odbc connection between Access and SQLite databases. Even though a later version might resolve some issues, maybe it will be unusable. I’m now pretty sure this problem has to do with SQLite’s query optimiser trying to use an Index that is on a field having the RMNOCASE collation. I don’t see any way of turning off the optimiser. Dropping the index might clear that problem but introduce others such as loss of speed and the need to regenerate the index for ongoing use by RootsMagic.

Tom
thejerrybryan Jun 18, 2013

I had wondered what might happen with ODBC and Access. So far, nothing bad has happened to me.

It is the case that Access suffers from the RMNOCASE problem just like SQLiteSpy does. There not being a fake RMNOCASE solution available to Access, the trick that has worked for me 100% of the time is to substring out any RMNOCASE field that I need to sort on, and do the “substring out” thing in a subquery that passes the substring up to the next higher level of subquery. The substring operation I perform makes a “substring” of the whole field without dropping any characters.

There is more information about this issue with RMNOCASE and Access elsewhere in the Wiki. Because of the nature of Access, it’s often the case that your query does not actually need to sort directly on one of the RMNOCASE fields. But you will still want to use a trick to make the field sortable anyway. That’s because of the visual nature of Access where you can click on the column headers of the result of a query and sort by that column even though the column wasn’t sorted in the original query. The ability to sort in this fashion is lost unless you suppress the RMNOCASE problem.

Jerry
ve3meo Jun 18, 2013

I rejoined the sqlite-users list and posted the problem. I have had replies from both D. Richard Hipp, lead developer of SQLite, and Ralf Junker, developer of SQLiteSpy. I have a better understanding of what’s going on, a partial work-around but concluding that any recent SQLite manager must have at least the fake RMNOCASE collation for it to be able to use any of the queries and scripts developed for the RootsMagic4+ databases. Now if only Bruce would reveal the real RMNOCASE (are you reading this, Bruce?). The discussion begins at [sqlite] False Error ‘no such collation sequence’ http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2013-June/046965.html

Tom
ve3meo Jun 18, 2013

The current version of the ODBC driver from http://www.ch-werner.de/sqliteodbc/ wraps the (current) SQLIte 3.7.17 library. Since the command line version sqlite3.exe is built from 3.7.17 and exhibits the same problems as SQLiteSpy 1.9.3, built from 3.7.16.1, I would not be surprised if the current ODBC introduced some sort of problem in your MS Access interface.

Tom

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

Discussions & comments from Wikispaces site

Inline comment: “A 162000 person database took over ‍100 minutes‍”

Inline comments

Comment: This is now questionable. I have seen…

Discussions & comments from Wikispaces site

This all works, now that I’ve had time to play with it

Comment by Tom Holden

Improvements to RootsMagic

Discussions & comments from Wikispaces site

Forcing REFN to be first fact

LinkTable

DDL

Interpretation

Discussions & comments from Wikispaces site

Inline comment: “INSTR function is not yet supported in SQLiteSpy‍,”

Inline comments

Comment: The current version of SQLiteSpy does…