Problem

In a discussion on the RootsMagic Forums, maginnis reported a problem with People View. After customising to add Place and Place Detail for many fact types, People View repeatedly threw up the error message “SQLite Error 1 – at most 64 tables in a join” until RootsMagic closed. The same error cropped up when People View was selected after re-opening the database. SQLite does have a limit of 64 tables being simultaneously joined, increased from 32 in 2007. It appears that the way RootsMagic builds the SQLite query to populate the People View is to join one or more tables for each column and does not limit the total to 64 or less, hence the error.

This error has no effect on the database and RootsMagic can continue to be used in every other way after reopening; only the People View is unusable.

How to Restore People View?

GEDCOM export and Drag & Drop are unaffected so one way is to use either method to transfer the data to a new database. That may be satisfactory to many users but not to some. We know that those transfers are not perfectly transparent yet may be good enough. Trailing white space in Notes is truncated on transfer for certain. We do not know if there are any other losses (provided all Fact Types used are set to be included in GEDCOM) other than unused items such as custom source templates, custom fact types, roles, places and place details, media, et al. RMtrix contains utilities (also available as SQLite queries) to add Carriage Return/Line Feed to the end of Notes and to review and set Fact Types to GEDCOM (which also governs D&D).

Another way is to dive into the database to reduce the number of columns to be included in People View. This technique is sure to have no effect on the data and is what is described here.

People View Settings

The column definitions for People View are stored in the first record of ConfigTable in the DataRec column. DataRec is a BLOB (binary) type containing XML text so we have to CAST it to TEXT type to view and CAST modified text to BLOB type to save.

-- Inspect the configuration setting for People View
SELECT CAST(DataRec AS TEXT)
FROM ConfigTable
WHERE RecID=1;

The result is a long collection of XML tags and values that control many things on opening the database. The People View columns are defined by one set of XML tags for each column of the form:

<PersViewCol11><FieldType>10000</FieldType><EventType>4</EventType><DataType>4</DataType></PersViewCol11>

where, in this example, Column 11 (0 being the first column to the right of the person’s name)is EventType 4 (the Burial fact) and DataType 4 is the Place Detail for that fact.

What we want to do is to reduce the number of joined tables by reducing the number of columns. Before doing so, use RM to make a backup of the database and then exit RM.

Now copy the entire result of the above query to a text editor such as PSPad or an XML editor such as XMLPad. The latter is very convenient because you can see the XML in a table view:
Here, the highlighted block is the snippet of XML from above.

Reduce the Number of Columns

Delete some of the highest numbered columns making sure that you have deleted each pair of <PersViewCol#>…</PersViewCol#> tags and all between. Do not delete from anywhere else; gaps in the column numbering will give unpredictable results.

Copy the resulting source code (XML text) into the following UPDATE query and execute:

-- Revising the People View settings
-- paste the modified XML text over
-- "modifiedXMLtext", preserving the
-- surrounding single quotes as shown
 
UPDATE ConfigTable
SET DataRec = CAST('modifiedXMLtext'
AS BLOB)
WHERE RecID = 1
;

Re-open the database with RootsMagic and inspect People View. If the same error persists, repeat the procedure to delete more columns. If a different error arises, you have made an error in the procedure – start over from your backup.