Tools – Problem Search – Spouse Order Questioned #tools #spouse

Posted on 2013-01-082019-01-26 by Tom Holden

The order in which multiple spouses and families are listed in RootsMagic’s Individual Report and in narrative reports is determined by the order that they are also shown in the dropdown list from the Spouses button in the upper left of the Main View window. Some prefer that this order should always be chronological while others may want to put first the spouse that is in their line of inquiry, regardless of that spouse’s position in the series of marriages for the person of interest. The determination that the output will be in the desired order cannot be proven until the proof-reading of a perhaps voluminous report. There is no tool in RootsMagic 6 to search for such problems. This SQLite query attempts to provide such a tool for finding instances where the order of spouses does not correspond with the order of spousal (family) events such as Marriage, Divorce, Census (family) and Residence (family).

RIN	Color	Spouse order questioned
66	1	Wives
234	0	Wives
239	0	Husbands
264	0	Wives
340	13	Husbands
366	0	Husbands
514	9	Wives
924	0	Husbands
1013	0	Husbands
1091	0	Wives

This is an example of results from the query. The first column gives the record number or RIN of the person with multiple spouses whose order appears to be inconsistent with chronology. The second column gives the color code number for that person and the third merely indicates whether the multiple spouses are husbands or wives.

I would use the RIN with RM Explorer to find, inspect events to get the sense of what the spouse order should be and maybe even fix the Sort Date of certain family events that are out of order. Then select the Person in the Main View followed by the Spouses button and Rearrange if necessary. If I have a great many spouse orders in question, I would narrow my focus to those persons in my line of interest by using color coding for that line. Then I could click on the Color column in SQLiteSpy to sort by color number and work only on those RINs having my color of interest.

SpouseOrderQuestioned.sql

/* SpouseOrderQuestioned.sql
2013-01-08 Tom Holden ve3meo
 
Lists RINs of persons with multiple spouses (families) whose
spouse order may be inconsistent with the SortDates of the
person's family events. This affects the order in which spouses
are listed in RM reports.
 
It's possible that this query can throw up some false positives
due to undated family events; it may also overlook some positives,
depending on the order that family events were entered.
 
*/
SELECT *
FROM
(
SELECT FatherID AS RIN, Color, 'Wives' AS 'Spouse order questioned'
FROM
(
SELECT *
FROM
(
SELECT COUNT() AS Wives,* FROM
(
-- 1 Family Event per Family
SELECT F.FamilyID, FatherID, MotherID, HusbOrder, WifeOrder, Name, SortDate, Color
FROM FamilyTable F
INNER JOIN EventTable E
ON F.FamilyID = OwnerID AND E.OwnerType = 1
INNER JOIN FactTypeTable FT
ON EventType = FactTypeID
INNER JOIN PersonTable ON FatherID = PersonID
GROUP BY F.FamilyID
ORDER BY FatherID, SortDate
)
GROUP BY FatherID
)
WHERE Wives > 1
)
WHERE WifeOrder <> Wives +1
 
UNION
 
SELECT MotherID AS RIN, Color, 'Husbands' AS 'Spouse order questioned'
FROM
(
SELECT *
FROM
(
SELECT COUNT() AS Husbands,* FROM
(
-- 1 Family Event per Family
SELECT F.FamilyID, FatherID, MotherID, HusbOrder, WifeOrder, Name, SortDate, Color
FROM FamilyTable F
INNER JOIN EventTable E
ON F.FamilyID = OwnerID AND E.OwnerType = 1
INNER JOIN FactTypeTable FT
ON EventType = FactTypeID
INNER JOIN PersonTable ON MotherID = PersonID
GROUP BY F.FamilyID
ORDER BY MotherID, SortDate
)
GROUP BY MotherID
)
WHERE Husbands > 1
)
WHERE HusbOrder <> Husbands +1
)
;

Facts – Sort Order Problems #events #date #sortdate

Posted on 2013-01-072019-01-26 by Tom Holden

Introduction

There has been a longstanding wish that RootsMagic would sort events that have no date or the same date (which might be as coarse as just the year) in an order that was natural or logical, e.g., Birth before Death being the simplest and most obvious example. The problem is that RootsMagic will sort these same date events in the order in which they were entered, i.e., in table row number order. The effect in a narrative report can be quite jarring.

To make matters worse, RM does not even provide a tool to find instances of such disturbing sequences. Its Tools > Problem Search > Problem list feature does have promising criteria:

Proper order of events
Birth before parent’s marriage
Birth before parent’s birth
Birth after father’s death
Birth after mother’s death

Unfortunately, same or empty dates are ignored!

A prior SQLite workaround, Dates – Same Day Sort Order, addresses many of these problems by modifying the SortDate value in the EventTable with offsets that correspond to “hyphen” dates in the Sort date field of the Edit Person screen, e.g., dd mmm yyyy – 10. This even works for blank dates – however, the offset Sort date remains blank. The script is limited to offsetting dates without certain modifiers because of the SortDate decoder used at the time. That, together with the small number of fact types it addresses, still leaves certain problems with event ordering that can only be resolved with manual editing.

How can one easily and quickly find these outstanding problems, given that the RM Problem Search does not, leaving one to rely on proof reading voluminous reports to detect disturbing sequences of sentences? This page offers a concept and a script that attempts to address this deficiency.

Concept

My initial approach was to build on the offset concept used in Dates – Same Day Sort Order, i.e., an ordering factor similar to its offsets: the lower the order number the earlier the event in the natural order, the higher the order number, the later. Then I realised that some events necessarily occur not before nor later than some other event which led to a minimum and maximum order number. So I thought a lookup table for the order range for each fact type would be needed and built it with the help of Excel:

FactTypeID	Name	OrderMin	OrderMax	SQL
503	Stillborn	9	10	INSERT OR REPLACE INTO xFactOrderTable VALUES (503,’Stillborn’,9,10);
6	Adoption	10	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (6,’Adoption’,10,200);
10	Blessing	10	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (10,’Blessing’,10,200);
18	Census	10	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (18,’Census’,10,200);
29	Residence	10	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (29,’Residence’,10,200);
504	Illness	10	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (504,’Illness’,10,200);
505	Living	10	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (505,’Living’,10,200);
1	Birth	10	10	INSERT OR REPLACE INTO xFactOrderTable VALUES (1,’Birth’,10,10);
3	Christen	20	20	INSERT OR REPLACE INTO xFactOrderTable VALUES (3,’Christen’,20,20);
7	Baptism	30	30	INSERT OR REPLACE INTO xFactOrderTable VALUES (7,’Baptism’,30,30);
12	Confirmation	40	40	INSERT OR REPLACE INTO xFactOrderTable VALUES (12,’Confirmation’,40,40);
13	First communion	50	50	INSERT OR REPLACE INTO xFactOrderTable VALUES (13,’First communion’,50,50);
304	Engagement	95	100	INSERT OR REPLACE INTO xFactOrderTable VALUES (304,’Engagement’,95,100);
305	Marriage Bann	96	100	INSERT OR REPLACE INTO xFactOrderTable VALUES (305,’Marriage Bann’,96,100);
306	Marriage Contract	97	100	INSERT OR REPLACE INTO xFactOrderTable VALUES (306,’Marriage Contract’,97,100);
307	Marriage License	98	100	INSERT OR REPLACE INTO xFactOrderTable VALUES (307,’Marriage License’,98,100);
308	Marriage Settlement	99	100	INSERT OR REPLACE INTO xFactOrderTable VALUES (308,’Marriage Settlement’,99,100);
510	Separation	105	120	INSERT OR REPLACE INTO xFactOrderTable VALUES (510,’Separation’,105,120);
14	Ordination	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (14,’Ordination’,100,200);
21	Graduation	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (21,’Graduation’,100,200);
22	Retirement	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (22,’Retirement’,100,200);
24	Education	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (24,’Education’,100,200);
300	Marriage	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (300,’Marriage’,100,200);
310	Residence (family)	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (310,’Residence (family)’,100,200);
311	Census (family)	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (311,’Census (family)’,100,200);
500	Degree	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (500,’Degree’,100,200);
501	Military	100	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (501,’Military’,100,200);
301	Annulment	110	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (301,’Annulment’,110,200);
303	Divorce filed	115	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (303,’Divorce filed’,115,200);
302	Divorce	120	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (302,’Divorce’,120,200);
2	Death	200	200	INSERT OR REPLACE INTO xFactOrderTable VALUES (2,’Death’,200,200);
5	Cremation	210	210	INSERT OR REPLACE INTO xFactOrderTable VALUES (5,’Cremation’,210,210);
4	Burial	220	220	INSERT OR REPLACE INTO xFactOrderTable VALUES (4,’Burial’,220,220);
20	Will	230	230	INSERT OR REPLACE INTO xFactOrderTable VALUES (20,’Will’,230,230);
19	Probate	240	240	INSERT OR REPLACE INTO xFactOrderTable VALUES (19,’Probate’,240,240);

My mind was spinning by the time I got this far so I went on to build a partial ordering table and a prototype script to test out the concept. I realized rather quickly as I tried to assign the order values that this concept is flawed because some events could be conditional – I found it confusing if I thought an order value was somehow indicative of probable age in addition to natural order. The implication is that the analysis should be probabilistic, not deterministic, something like RM’s Duplicate Search Merge or my Duplicate Name Search – query.

Fact Order Table

The above series of INSERTs were copied and pasted into a script that builds a temporary table
xFactOrderTable within the target database. You open the database with SQLiteSpy and then open and execute this script.
FactOrderTable.sql

If you want to play with the order values, you can download this spreadsheet that includes all of RM’s standard fact types and replace the series of INSERTs in
FactOrderTable.sql and re-run it:
FactOrder.xlsx

Sort Order Problems Query

This initial script uses only the OrderMin values in a deterministic algorithm and gives results like this:

Problem	Date	Sharer Name	Sharer Name:1
Marriage before Birth: BLAKESTON , W. J.-1047		PARSLOW , Ann J.-1048
Marriage before Birth: PARSLOW , Ann J.-1048		BLAKESTON , W. J.-1047
Marriage before Birth: [WOOLDRIDGE] , Anne-1085		WOOLDRIDGE , Elipat-1084
Burial before Marriage: HOLDEN , James-1103	1846		CASKER , Elizabeth-1104
Death before Marriage: HOLDEN , James-1103	1846-04-07 – 1846-04-10		CASKER , Elizabeth-1104
Death before Birth: *ORDER , Birth Death-1189	1900

The Problem list of RootsMagic 6 returned 15 problems with all the criteria shown in the Introduction included, 11 for solely ‘Proper order of events’. For the same database, this script returned 157 potential problems. Note that the script does detect events on blank dates that are out of natural order in their position in the EventTable. It also reports as problems some sequences that may not cause a problem in reports because of the separation of ‘family’ events from ‘individual’ events, e.g. most of those listed above; should RM be revised to make narrative reports purely chronological, these could well become problems, if nothing is done to provide natural ordering of same date events. Sorting the results on Problem can put the focus on common types of problems; initially, the problems are sorted by RIN.
SortOrderProblems.sql Remember to run FactOrderTable.sql before running this script. The temporary tables they add to the database are dropped when SQLiteSpy closes the database.

You could run this script before and after running Dates – Same Day Sort Order to see how many (and what types of) problems it resolves.

This script is an experiment, a work-in-progress that may go no further; it has not been optimised for speed and will likely run very slowly on even moderate size databases.

In developing this query, it became clear that additional (not implemented) special measures are needed to detect the marriage of a person prior to their divorce from a previous spouse.

Dates – SortDate Algorithm #date #sortdate

Posted on 2013-01-022021-02-27 by Tom Holden

Initial SortDate Algorithm

The SortDate column in the EventTable contains very large numbers that have defied until now any easy method of decoding. After many experiments with date entry and inspection of the stored values, I now have deduced an algorithm that works fine for encoding single, pure dates and decoding corresponding SortDates. Date modifiers such as Bef, Aft, From-To, etc. have effects that I do not fully understand but there are patterns emerging which can be more completely identified only with more comprehensive testing.

My current model of the RM4 SortDate encoding algorithm begins with a base year of 10000BC that has an already pretty large number as a constant, C. From the date, the year number is extracted, added to 10000 and the result multiplied by a very large coefficient, Ky. Added to that is the month number multiplied by the month coefficient, Km, plus the day number multiplied by the day coefficient, Kd. Algebraically, the encoding expression looks like this:

SortDate (Ds) = Ky*(Y + 10000) + Km*M + Kd*D + C,
where
Ky = 562949953421312  (2^49)
Km = 35184372088832   (2^45)
Kd = 549755813888     (2^39)
C  = 17178820620      (2^33.9999119432032)
Y  = yyyy
M  = mm
D  = dd

The decoding expressions are the inverse:

Y = (Ds - C) / Ky - 10000
M = (Ds - C) % Ky / Km
D = (Ds - C) % Ky % Km / Kd
R = (Ds - C) % Ky % Km % Kd
where
Ds = SortDate
R  = remainder
%  = modulus operator

The remainder, R, is significant for the decoding of modifiers while M=15, and D=63 are results when modifiers are used, as can be seen in the filtered result set below from a RM4 database, using the current decoder:

Date                        SortDate               Year  Month  Day   Remainder
Single Dates
D.+19470000..+00000000..    6725563110703235084    1947    0     0     0
D.+19470100..+00000000..    6725598295075323916    1947    1     0     0
D.+19470101..+00000000..    6725598844831137804    1947    1     1     0
D.+19470102..+00000000..    6725599394586951692    1947    1     2     0
D.+18501214..+00000000..    6671386874267828236    1850    12    14    0
 
Modified Dates
After
DA+18280000..+00000000..    6659134466444753951    1828    15    63    1047571
DA+18710000..+00000000..    6683341314441870367    1871    15    63    1047571
Range
DR+18100000..+18120000..    6648438962291474447    1809    15    63    544962772995
DR+19160000..+19180000..    6708111657465282575    1915    15    63    545073922051
DS+18980000..+19500000..    6697978558337253394    1897    15    63    545107476486
Before
DB+18250000..+00000000..    6656883216385835008    1824    15    63    549755813876
DB+19130000..+00000000..    6706422812286910464    1912    15    63    549755813876

It appears that the Before and After modifiers result in remainders that are constants, independent of the date, while the Range modifiers result in varying remainders. Thus inspection of the remainder and testing for known constants should result in ascertaining the type of modifier that was used.

Download

SortDateEncodeDecode.sql
Contains both the encoder query and the decoder query. Separate as required. Most GUI SQLite managers allow you to select the statements you want to execute. This script is deprecated; see below for more complete solutions.

Advanced Date Algorithms by R. Steven Turley

Here are the algorithms for decoding RM5 dates. These may be the same as RM4, but I haven’t tested them (ve3meo Dec 31, 2012 tested in RM6 and okay). They work for all normal dates, but I haven’t tested them on old dates or Quaker dates. I’m hoping those will just translate to their Julian equivalents and work okay. I’ll report those tests later. These dates are best understood in terms of bit fields on a long (64 bit) integer. Bit 0 is the least significant bit.

bits	value
0-9	flag indicating date modifiers (see table below)
10-15	day for the second date; 0 if no second date
16-19	month for the second date (1=Jan, 2=Feb, etc), 0 if no second date
20-33	year for the second date+10000; 0x3fff if no second date
34-38	these were always zero in my tests, must be reserved for something else
39-44	day for the first date; 0 if no day is supplied
45-48	month for the first date; 0 if no month is supplied
49-64	year for the first date + 10000

If the date is missing, the value 0xffffffff (2^65-1) is stored. This makes an event with no date, the last date in the list as far as the sort order is concerned.

The flag fields tell what kind of modifiers the date has. Certainty modifiers are not included in SortDates since they don’t affect the sort order. Here are the possible flag values:

flag value	meaning
0	before
3	by
6	to
9	until
12	normal date (no modifier)
15	between/and
18	from/to
21	dash (25 Oct 2011-28 Oct 2011)
24	or
27	from
30	since
31	after

Note that the value of these flags determines the sort order for dates as listed above. Since dates that don’t have ranges store their year as 0x3fff, they will always come after dates with ranges that have a year less than this.

Algorithms

For almost all compilers, shift and bit-wise mask operators are faster than the division and remainder operations outlines above. If your system supports it, I recommend the following algorithms to extract the dates and flag from sort dates. In these formulas i>>j is the operation of shifting the number i j bits to the right. The operation i<<j shifts the number i j bits to the left. The operation i&j performs a bitwise and of the two numbers. This is the notation used in C, C++, C#, and python. I’m not sure about other languages. Let Ds be the sort date, as above, Y1 be the year of the first date, M1 be the month of the first date, D1 be the day of the first date, Y2 be the year of the second date, M2 be the month of the second date, D2 by the day of the second date, and F by the flag.

Ds = ((Y1 + 10000)<<49) & (M1<<45) & (D1<<39) & (Y2<<20) & (M2<<16) & (D2<<10) & F ??
‍Ds = If no Date then
9223372036854775807
else
((Y1 + 10000)<<49) + (M1<<45) + (D1<<39) + (If Date2 then (Y2 + 10000)<<20 else
17178820608)
+ (M2<<16) + (D2<<10) + F

Y1 = (Ds>>49) – 10000
M1 = (Ds>>45) & 0xf
D1 = (Ds>>39) & 0x3f
Y2 = (Ds>>20) & 0x3fff – 10000
M2 = (Ds>>16) & 0xf
D2 = (Ds>>10) & 0x3f
F = Ds & 0x3ff

Downloads

SortDateDecodeDev.sql an example decoder using Steve’s algorithm
SortDateEncoderDev2.sql an encoding example using the modified version of Steve’s algorithm; streamlined, faster and more readable code than SortDateEncoderDev.sql.
SortDates (2013-01-01).rmgbRM6 database with samples of most date formats for testing with above.

Discussions & comments from Wikispaces site

Inline comments

Named Group Refresh #namedgroup

Posted on 2012-12-072019-01-26 by Tom Holden

Intro

RootsMagic 4+ users have been seeking an improvement to Named Groups, namely “dynamic refresh” or, at least, a manual refresh button. The idea is that a group’s membership should be updated on demand following the rules that set it up in the first place. Currently, a group is static, requiring the user to edit the group, setting up the rules all over again.These sql scripts provide outboard manual refresh for specific groups.

This page collects queries designed to build membership lists for pre-named groups in RM4 databases. These queries search the subject database’s list of named groups for one whose name matches the query’s hard-coded or parameterised criteria. They delete the current members from the group and build a new set according to the programmed rules. In RM4+, selecting another group and then re-selecting the target named group updates its displayed list of members.

The queries can also include a list of persons’ Record Numbers to be unmarked from the group and a list to be marked (included) into the group. These may not be advisable for parameterised queries which use a common script as the manual inclusions and exclusions may need to be different.

For parameterised queries, SQLite Expert Personal or SQLite Developer is required. SQLiteSpy does not support run-time parameters.

Typical Usage

Backup your database before you do this every time until you are confident that it does no harm.
In RM4+, create a named group having the key words required by the script. Make sure first that you have no empty groups before creating a new one – there’s a bug in RM4 – and immediately add someone to your group before creating any other new ones.
Open your RM4 database with SQLite Expert Personal and load the sql script into the SQL editor.
Execute the query (F5)
You will be prompted to enter a value for one or more “Missing parameters”, e.g., a RIN, a Year, a Place….
When the query has finished, you will need to refresh the RM4 sidebar view of your group by selecting another group and then re-selecting it.

Named Group Scripts

NB- LabelID instead of LabelValue error corrected 2011-11-27 21:45 EST – Replace all downloaded scripts!
Ancestors Named Group: ancestors of a user-defined person, including all parental relationships
Census Needed – Named Group: persons whose lifetime probably spanned a user-defined Census Year and who had some event in the user-defined jurisdiction but not a Census fact for that year.
Named Group – Mark or Unmark List refresh: a one-button refresh of a group with arbitrary selection of persons to be marked or unmarked. Can be used after an outboard rule-based refresh on the same group (but not on any group refreshed from within RM4+).

Ranges vs Row per Person

The RM4+ GroupTable allows for group members to be defined in ranges of consecutive numbers between a StartID and EndID, thus shortening the table versus one row per member. These scripts make no attempt to use this capability so it consumes one row per member with EndID=StartID. I don’t know if there is any significant efficiency gained as a result of ranges.

Update 7 Dec 2012.
I finally figured out a way to generate the StartID-EndID ranges from a list of numbers. The query GroupTableWrite.sql listed below does the job and could be incorporated in the Named Group Scripts. Whether that is of any real advantage is even more in question now that I have examined the question a little more closely. In fact, one might argue that a table with one row per GroupID, PersonID with just one PersonID field could operate faster than the current design.

In a sample database of ~162,000 persons, ~54,000 contain the same surname string as part of the surname. Grouped into ranges, they are described by ~31,000 records in GroupTable, of which, ~23,000 are for just one person. 54,000 records is 74% more than 31,000 records but is hardly a deal-breaker – memory is cheap. Moreover, the 31,000 records store 4 values = 124,000 values while 54,000 records storing just 3 values = 162,000, only 31% more, closer to a draw on the memory front. What about the number of operations? For the current table design, there are 5 comparisons per GroupTable record for each person (PersonID >= StartID AND PersonID <=EndID) x 31,000 records X 162,000 persons = 25 billion comparisons. In the simpler concept, there is but one comparison per GroupTable record for each person (PersonID = MarkID) x 54,000 records X 162,000 persons = <9 billion comparisons. Furthermore, the single person per row structure is perfectly suited for a SQLite table index which may provide the biggest advantage as there is currently no defined index for GroupTable and it may not lend itself to SQLite’s AutoIndex.

As it turns out, writing the GroupTable with ranges is itself not majorly time-consuming, at least not for GroupTableWrite, which wrote the 31,000 records from the 54,000 record list (itself 4 subqueries of another previously defined group) in less than 5 seconds.

GroupTableWrite.sql

-- GroupTableWrite.sql
/*
2012-12-07 Tom Holden ve3meo
 
Creates StartID-EndID ranges for use in GroupTable from
a list of Marked PersonIDs and
Writes records to GroupTable
 
Replace marklist with the desired list, however generated, in
the form 1,3,479,480,481,...
 
In the Update GroupTable section,
replace the GroupID value with the one desired.
 
*/
 
DROP TABLE IF EXISTS tmpStartIDTable;
DROP TABLE IF EXISTS tmpEndIDTable;
CREATE TEMP TABLE tmpStartIDTable
AS
 
 SELECT PersonID AS StartID
 FROM PersonTable
 WHERE
 StartID IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 AND
 StartID -1 NOT IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 
;
 
CREATE TEMP TABLE tmpEndIDTable
AS
 
 SELECT PersonID AS EndID
 FROM PersonTable
 WHERE
 EndID IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 AND
 EndID +1 NOT IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 
;
 
-- Update GroupTable
-- change GroupID value to match LabelValue for group from LabelTable
DELETE FROM GroupTable WHERE GroupID = 3
;
 
INSERT INTO GroupTable
SELECT NULL AS RecID, 3 AS GroupID, * FROM tmpStartIDTable StartID INNER JOIN tmpEndIDTable EndID
ON StartID.rowid = EndID.rowid
;

Backup Media with Database – RAR #batch #backup #media #rar

Posted on 2012-12-052020-09-03 by Tom Holden

(See Backup Media with Database – 7Zip for a similar procedure using 7-zip.)

Here is a one-click backup procedure that stores the database file and all the media files it references in one solid, compressed backup file, preserving the path to each. Many have lamented the fact that RootsMagic 4 neither stores images in its database file nor backs up media files together with the database file so that all the files can be transported together between drives, computers, cloud storage, etc. A workaround is to follow the procedure Create a Shareable CD and zip the collected files found in its temporary folder prior to burning the CD but this has proven cumbersome, slow, the folder hierachy is collapsed and multiple links to the same media file result in as many copies.

The Zip encoder RM4-7 uses for Backup may not be able to handle a large database with many media files – another reason to use outboard procedures until its limitations are lifted. See this article in Wikipedia on the early Zip limits.

This procedure is based on a very simple query that lists the paths and names of all the media files referenced by the database:

SELECT DISTINCT MediaPath || MediaFile FROM MultiMediaTable;

The following command line procedure or batch file runs the command line version of SQLite3, opening the RootsMagic database whose name is passed in the call to the procedure and executes the query. The resulting list of media files is passed via STDIN to RAR which creates or updates the backup file. When RAR finishes the list from sqlite, it sees the database filename and processes it, too. The resulting .rar file can be opened by WinRAR and a variety of other archiving utilities. The procedure could be revised to use a different archiver.

ECHO OFF
REM RMfullbackup.bat
REM by Tom Holden 2011-02-02
REM Backs up a RootsMagic database file and all the media files referenced by it to one, compressed RAR file.
REM
REM Command syntax: RMfullbackup.bat <databasename>
REM Close the database file before running.
REM
REM The first backup can take considerable time, depending on the total of the file sizes.
REM To save time on subsequent backups, RMfullbackup merely updates the backup file for those files added, changed or deleted.
REM Install the SQLite3 command line version in the same directory as the RMGC database file(s)
REM  OR set a system PATH to where sqlite3.exe is located
REM  OR prepend the path to sqlite3.exe in the command below.
REM Likewise for RAR.exe
REM Under the RM Data folder, create a folder Backups; if a different path is to be used, edit the command line below.
REM
REM The backup file will be written to the Backups folder and will have the name <databasename>_fullbackup.rar
REM An error log will also be written to the Backups folder with the name <databasename>_fullbackuperror.log
REM
REM backup media files listed by query followed by the database file
 
sqlite3.exe %1.rmgc "SELECT DISTINCT MediaPath || MediaFile from MultiMediaTable;" | rar a -u -as -ilog.backups%1_fullbackuperror.log backups%1_fullbackup @ %1.rmgc
PAUSE
END

Download and save in the folder where you have your RootsMagic databases:
RMfullbackup.bat.bak (.bak added due some systems’ security. Remove after d/l)

Create a shortcut to RMfullbackup.bat <databasename> and name and place it where it is convenient. Do not include “.rmgc” in the database name. Make as many of these shortcuts as there are different databases to be backed up.

Clicking on the shortcut will launch a command window where you will see RAR working through the list. When it is finished the window will stay open until you press a key, allowing an opportunity for you to inspect the messages. Error messages from RAR are stored in the error log for later review. Note that the RM database must be closed in order for the database file to be processed by RAR.

Utilities by R. Steven Turley #application

Posted on 2012-12-032019-01-26 by Tom Holden

This page gathers links to the RootsMagic utilities developed by R. Steven Turley.

2012-11-08 Copying Custom Reports copies a custom report from one database to another.

2012-11-08 Source Template Maintenance

list all of the sources using a particular template
find a template containing certain phrases
list those custom templates I have created
list the templates I am actually using in my sources

2012-10-04 AltMarried checks GEDCOM for the existence of an Alternate Name (Married) fact for each wife.

2012-01-03 SplitTree (AKA RMsplit) creates and maintains a Named Group

Role Types #datadefinitions #roles

Posted on 2012-12-022019-01-26 by Tom Holden

RootsMagic 4+ Built-In Roles

The built-in roles for RM4 and later can be found in RoleTable with RoleID up through 58. User-defined/custom types are assigned values of 59 and higher by the program when created.

Built-in roles and their unedited values within the table are as follows:

RoleID	RoleName	EventType	RoleType	Sentence
1	Witness	1	0	[ThisPerson] witnessed the birth of [person]< [Date]>< [PlaceDetails]>< [Place]>.
2	Doctor	1	0	[ThisPerson] delivered [person]< [Date]>< [PlaceDetails]>< [Place]>.
3	Witness	2	0	[ThisPerson] witnessed the death of [person]< [Date]>< [PlaceDetails]>< [Place]>.
4	Witness	3	0	[ThisPerson] witnessed the christening of [person]< [Date]>< [PlaceDetails]>< [Place]>.
5	Witness	4	0	[ThisPerson] witnessed the burial of [person]< [Date]>< [PlaceDetails]>< [Place]>.
6	Witness	5	0	[ThisPerson] witnessed the cremation of [person]< [Date]>< [PlaceDetails]>< [Place]>.
7	Witness	6	0	[ThisPerson] witnessed the adoption of [person]< [Date]>< [PlaceDetails]>< [Place]>.
8	Witness	7	0	[ThisPerson] witnessed the baptism of [person]< [Date]>< [PlaceDetails]>< [Place]>.
9	Witness	8	0	[ThisPerson] witnessed the bar mitzvah of [person]< [Date]>< [PlaceDetails]>< [Place]>.
10	Witness	9	0	[ThisPerson] witnessed the bas mitzvah of [person]< [Date]>< [PlaceDetails]>< [Place]>.
11	Witness	10	0	[ThisPerson] witnessed the blessing of [person]< [Date]>< [PlaceDetails]>< [Place]>.
12	Witness	11	0	[ThisPerson] witnessed the christening of [person]< [Date]>< [PlaceDetails]>< [Place]>.
13	Witness	12	0	[ThisPerson] witnessed the confirmation of [person]< [Date]>< [PlaceDetails]>< [Place]>.
14	Witness	13	0	[ThisPerson] witnessed the first communion of [person]< [Date]>< [PlaceDetails]>< [Place]>.
15	Witness	14	0	[ThisPerson] witnessed the ordaination of [person]< [Date]>< [PlaceDetails]>< [Place]>.
16	Witness	15	0	[ThisPerson] witnessed the naturalization of [person]< [Date]>< [PlaceDetails]>< [Place]>.
17	Witness	16	0	[ThisPerson] witnessed the emigration of [person]< [Date]>< [PlaceDetails]>< [Place]>.
18	Witness	17	0	[ThisPerson] witnessed the immigration of [person]< [Date]>< [PlaceDetails]>< [Place]>.
19	Witness	18	0	[ThisPerson] appeared in the household of [person] in the census< [Date]>< [PlaceDetails]>< [Place]>.
20	Witness	19	0	[ThisPerson] witnessed the probate of [person]< [Date]>< [PlaceDetails]>< [Place]>.
21	Witness	20	0	[ThisPerson] witnessed the will of [person]< [Date]>< [PlaceDetails]>< [Place]>.
22	Heir	20	0	[ThisPerson] was named an heir in the will of [person]< [Date]>< [PlaceDetails]>< [Place]>.
23	Executor	20	0	[ThisPerson] was named an executor for the will of [person]< [Date]>< [PlaceDetails]>< [Place]>.
24	Witness	21	0	[ThisPerson] witnessed the graduation of [person]< [Date]>< [PlaceDetails]>< [Place]>.
25	Witness	22	0	[ThisPerson] witnessed the retirement of [person]< [Date]>< [PlaceDetails]>< [Place]>.
26	Witness	23	0	[ThisPerson] witnessed the appearance of [person] as [Desc]< [Date]>< [PlaceDetails]>< [Place]>.
27	Witness	24	0	[ThisPerson] witnessed the education of [person]< [Date]>< [PlaceDetails]>< [Place]>.
28	Witness	25	0	[ThisPerson] witnessed the nationality of [person] as [Desc]< [Date]>< [PlaceDetails]>< [Place]>.
29	Witness	26	0	[ThisPerson] witnessed the occupation of [person]< [Date]> as [Desc:A]< [PlaceDetails]>< [Place]>.
30	Witness	27	0	[ThisPerson] witnessed the property of [person] as [Desc:A]< [Date]>< [PlaceDetails]>< [Place]>.
31	Witness	28	0	[ThisPerson] witnessed the religion of [person] as [Desc:A]< [Date]>< [PlaceDetails]>< [Place]>.
32	Witness	29	0	[ThisPerson] lived in the residence of [person] < [PlaceDetails]>< [Place]>< [Date]>.
33	Witness	31	0	[ThisPerson] witnessed the baptism of [person] into the LDS Church< [Date]>< [PlaceDetails]>< [Place]>.
34	Witness	32	0	[ThisPerson] witnessed the endowment of [person]< [Date]>< [PlaceDetails]>< [Place]>.
35	Witness	33	0	[ThisPerson] witnessed the sealed to parents of [person]< [Date]>< [PlaceDetails]>< [Place]>.
36	Witness	36	0	[ThisPerson] witnessed the associated with [Desc] of [person]< [Date]>< [PlaceDetails]>< [Place]>.
37	Witness	38	0	[ThisPerson] witnessed the comfirmation of [person] as a member of the LDS Church< [Date]>< [PlaceDetails]>< [Place]>.
38	Witness	39	0	[ThisPerson] witnessed the LDS initiatory of [person]< [Date]>< [PlaceDetails]>< [Place]>.
39	Witness	300	0	[ThisPerson] witnessed the marriage of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
40	Witness	301	0	[ThisPerson] witnessed the annullment of the marriage of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
41	Witness	302	0	[ThisPerson] witnessed the divorce of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
42	Witness	303	0	[ThisPerson] witnessed the divorce filing of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
43	Witness	304	0	[ThisPerson] witnessed the engagement of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
44	Witness	305	0	[ThisPerson] witnessed the publishing of the marriage banns of [couple]<[Date]>< [PlaceDetails]>< [Place]>.
45	Witness	306	0	[ThisPerson] witnessed the marriage contract of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
46	Witness	307	0	[ThisPerson] witnessed the obtaining of the marriage license by [couple]< [Date]>< [PlaceDetails]>< [Place]>.
47	Witness	308	0	[ThisPerson] witnessed the marriage settlement of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
48	Witness	309	0	[ThisPerson] witnessed the sealing of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
49	Witness	311	0	[ThisPerson] appeared in the census in the household of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
50	Witness	500	0	[ThisPerson] witnessed the degree of [person]< [Date]>< [PlaceDetails]>< [Place]>.
51	Witness	501	0	[ThisPerson] witnessed the military service of [person]< [Date]>< [PlaceDetails]>< [Place]>.
52	Witness	502	0	[ThisPerson] witnessed the mission of [person]< [Date]>< [PlaceDetails]>< [Place]>.
53	Witness	503	0	[ThisPerson] witnessed the stillbirth of [person]< [Date]>< [PlaceDetails]>< [Place]>.
54	Witness	504	0	[ThisPerson] witnessed the illness of [person] with [Desc]< [Date]>< [PlaceDetails]>< [Place]>.
55	Witness	507	0	[ThisPerson] witnessed the election of [person] as [Desc]< [Date]>< [PlaceDetails]>< [Place]>.
56	Witness	508	0	[ThisPerson] witnessed the excommunication of [person]< [Date]>< [PlaceDetails]>< [Place]>.
57	Witness	510	0	[ThisPerson] witnessed the separation of [couple]< [Date]>< [PlaceDetails]>< [Place]>.
58	Witness	999	0	[person]< [Desc]>< [Date]>< [PlaceDetails]>< [Place]>.

Fact Types #datadefinitions #facttypes

Posted on 2012-12-022023-05-22 by Tom Holden

RootsMagic 4+ Built-In Fact Types

2023-05-22 updated to RM9

The built-in fact types for RM4 and later can be found in the FactTypeTable with FactTypeID less than 1000. User-defined/custom types are assigned values of 1000 and higher by the program when created. Between RM4 and RM9, only the 902-Associations fact type has been added. There may have been changes in Sentence templates and Flags (latter might be related to 32-bit vs 64-bit numbers). And the UTCModDate column has been added.

Following is a summary of the RM9 Built-in fact types in order of FactTypeID. note the clustering of “vital” facts (1-5 or 6 et al scattered), religious facts (7-14 except 28-Religion), citizenship (15-18 plus others), “family” (300 series except 510-Separation), unconventionals (90x series). These clusters may be a holdover from some early concept for sequencing the listing of events in other than chronological order. And the invisible-to-the-user FactTypeID appears to be the primary sort key in RM9 when the Edit Person option “Sort facts by” > “Fact type” is chosen – a seemingly odd result when intuitively one expects an alphabetical sort by fact Name.

FactTypeID	OwnerType	FactName	Abbrev	GedcomTag	RM4ID
1	0	Birth	Birth	BIRT	1
2	0	Death	Death	DEAT	2
3	0	Christen	Chr	CHR	3
4	0	Burial	Burial	BURI	4
5	0	Cremation	Cremation	CREM	5
6	0	Adoption	Adoption	ADOP	6
7	0	Baptism	Baptism	BAPM	7
8	0	Bar Mitzvah	Bar Mitzvah	BARM	8
9	0	Bas Mitzvah	Bas Mitzvah	BASM	9
10	0	Blessing	Blessing	BLES	10
11	0	Christen (adult)	Chr (adult)	CHRA	11
12	0	Confirmation	Confirmation	CONF	12
13	0	First communion	First comm	FCOM	13
14	0	Ordination	Ordination	ORDN	14
15	0	Naturalization	Naturalization	NATU	15
16	0	Emigration	Emigration	EMIG	16
17	0	Immigration	Immigration	IMMI	17
18	0	Census	Census	CENS	18
19	0	Probate	Probate	PROB	19
20	0	Will	Will	WILL	20
21	0	Graduation	Graduation	GRAD	21
22	0	Retirement	Retirement	RETI	22
23	0	Description	Description	DSCR	23
24	0	Education	Education	EDUC	24
25	0	Nationality	Nationality	NATI	25
26	0	Occupation	Occupation	OCCU	26
27	0	Property	Property	PROP	27
28	0	Religion	Religion	RELI	28
29	0	Residence	Residence	RESI	29
30	0	Soc Sec No	SSN	SSN	30
31	0	LDS Baptism	LDS Bapt	BAPL	31
32	0	LDS Endowment	LDS Endow	ENDL	32
33	0	LDS Seal to parents	LDS SealPar	SLGC	33
34	0	Ancestral File Number	AFN	AFN	34
35	0	Reference No	Ref #	REFN	35
36	0	Caste	Caste	CAST	36
37	0	Title (Nobility)	Title	TITL	37
38	0	LDS Confirmation	LDS Conf	CONL	38
39	0	LDS Initiatory	LDS Init	WAC	39
300	1	Marriage	Marriage	MARR	300
301	1	Annulment	Annulment	ANUL	301
302	1	Divorce	Divorce	DIV	302
303	1	Divorce filed	Div. filed	DIVF	303
304	1	Engagement	Engagement	ENGA	304
305	1	Marriage Bann	Marr Bann	MARB	305
306	1	Marriage Contract	Marr Contract	MARC	306
307	1	Marriage License	Marr Lic	MARL	307
308	1	Marriage Settlement	Marr Settlement	MARS	308
309	1	LDS Seal to spouse	LDS SealSp	SLGS	309
310	1	Residence (family)	Residence (fam)	RESI	310
311	1	Census (family)	Census (fam)	CENS	311
500	0	Degree	Degree	EVEN	500
501	0	Military	Military	EVEN	501
502	0	Mission	Mission	EVEN	502
503	0	Stillborn	Stillborn	EVEN	503
504	0	Illness	Illness	EVEN	504
505	0	Living	Living	EVEN	505
507	0	Election	Elected	EVEN	507
508	0	Excommunication	Excomm	EVEN	508
509	0	Namesake	Namesake	EVEN	509
510	1	Separation	Separation	EVEN	510
900	0	Alternate name	Alt. Name	EVEN	900
901	0	DNA test	DNA	_DNA	901
902	0	Association	Association	EVEN
999	0	Miscellaneous	Misc	EVEN	999

RM6 Database System Catalog #database #datadefinitions

Posted on 2012-12-022019-01-26 by Tom Holden

A singular system table, sqlite_master, resides in a SQLite database. This table in the RootsMagic 6 database essentially defines the RootsMagic 6 database tables, indices, and fields, as below.

The difference in structure with RootsMagic 5 is the addition of one new table, URLTable (no index) for WebTags.

type	name	tbl_name	rootpage	sql
table	AddressLinkTable	AddressLinkTable	21	CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT )
table	AddressTable	AddressTable	9	CREATE TABLE AddressTable (AddressID INTEGER PRIMARY KEY, AddressType INTEGER, Name TEXT COLLATE RMNOCASE, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT, Zip TEXT, Country TEXT, Phone1 TEXT, Phone2 TEXT, Fax TEXT, Email TEXT, URL TEXT, Latitude INTEGER, Longitude INTEGER, Note BLOB )
index	idxAddressName	AddressTable	42	CREATE INDEX idxAddressName ON AddressTable (Name)
table	ChildTable	ChildTable	5	CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB )
index	idxChildID	ChildTable	37	CREATE INDEX idxChildID ON ChildTable (ChildID)
index	idxChildFamilyID	ChildTable	38	CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID)
index	idxChildOrder	ChildTable	39	CREATE INDEX idxChildOrder ON ChildTable (ChildOrder)
table	CitationTable	CitationTable	20	CREATE TABLE CitationTable (CitationID INTEGER PRIMARY KEY, OwnerType INTEGER, SourceID INTEGER, OwnerID INTEGER, Quality TEXT, IsPrivate INTEGER, Comments BLOB, ActualText BLOB, RefNumber TEXT, Flags INTEGER, Fields BLOB )
index	idxCitationSourceID	CitationTable	62	CREATE INDEX idxCitationSourceID ON CitationTable (SourceID)
index	idxCitationOwnerID	CitationTable	63	CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID)
table	ConfigTable	ConfigTable	2	CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB )
index	idxRecType	ConfigTable	34	CREATE INDEX idxRecType ON ConfigTable (RecType)
table	EventTable	EventTable	7	CREATE TABLE EventTable (EventID INTEGER PRIMARY KEY, EventType INTEGER, OwnerType INTEGER, OwnerID INTEGER, FamilyID INTEGER, PlaceID INTEGER, SiteID INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, Status INTEGER, EditDate FLOAT, Sentence BLOB, Details BLOB, Note BLOB )
index	idxOwnerEvent	EventTable	40	CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType)
index	idxOwnerDate	EventTable	41	CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate)
table	ExclusionTable	ExclusionTable	28	CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER )
index	idxExclusionIndex	ExclusionTable	73	CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2)
table	FactTypeTable	FactTypeTable	10	CREATE TABLE FactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER )
index	idxFactTypeName	FactTypeTable	43	CREATE INDEX idxFactTypeName ON FactTypeTable (Name)
index	idxFactTypeAbbrev	FactTypeTable	45	CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev)
index	idxFactTypeGedcomTag	FactTypeTable	46	CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag)
table	FamilyTable	FamilyTable	4	CREATE TABLE FamilyTable (FamilyID INTEGER PRIMARY KEY, FatherID INTEGER, MotherID INTEGER, ChildID INTEGER, HusbOrder INTEGER, WifeOrder INTEGER, IsPrivate INTEGER, Proof INTEGER, SpouseLabel INTEGER, FatherLabel INTEGER, MotherLabel INTEGER, Note BLOB )
index	idxFamilyFatherID	FamilyTable	35	CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID)
index	idxFamilyMotherID	FamilyTable	36	CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID)
table	GroupTable	GroupTable	26	CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER )
table	LabelTable	LabelTable	30	CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT )
index	idxLabelType	LabelTable	71	CREATE INDEX idxLabelType ON LabelTable (LabelType)
table	LinkTable	LinkTable	24	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 )
index	idxLinkRmId	LinkTable	67	CREATE INDEX idxLinkRmId ON LinkTable (rmID)
index	idxLinkExtId	LinkTable	68	CREATE INDEX idxLinkExtId ON LinkTable (extID)
table	MediaLinkTable	MediaLinkTable	13	CREATE TABLE MediaLinkTable (LinkID INTEGER PRIMARY KEY, MediaID INTEGER, OwnerType INTEGER, OwnerID INTEGER, IsPrimary INTEGER, Include1 INTEGER, Include2 INTEGER, Include3 INTEGER, Include4 INTEGER, SortOrder INTEGER, RectLeft INTEGER, RectTop INTEGER, RectRight INTEGER, RectBottom INTEGER, Note TEXT, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT, SortDate INTEGER, Description BLOB )
index	idxMediaOwnerID	MediaLinkTable	49	CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID)
index	idxMediaCaption	MediaLinkTable	50	CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption)
table	MultimediaTable	MultimediaTable	11	CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB , Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT, SortDate INTEGER, Description BLOB)
index	idxMediaFile	MultimediaTable	47	CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile)
index	idxMediaURL	MultimediaTable	48	CREATE INDEX idxMediaURL ON MultimediaTable (URL)
table	NameTable	NameTable	14	CREATE TABLE NameTable (NameID INTEGER PRIMARY KEY, OwnerID INTEGER, Surname TEXT COLLATE RMNOCASE, Given TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE, Nickname TEXT COLLATE RMNOCASE, NameType INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, EditDate FLOAT, Sentence BLOB, Note BLOB, BirthYear INTEGER, DeathYear INTEGER )
index	idxNameOwnerID	NameTable	51	CREATE INDEX idxNameOwnerID ON NameTable (OwnerID)
index	idxSurname	NameTable	52	CREATE INDEX idxSurname ON NameTable (Surname)
index	idxGiven	NameTable	53	CREATE INDEX idxGiven ON NameTable (Given)
index	idxSurnameGiven	NameTable	54	CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear)
index	idxNamePrimary	NameTable	56	CREATE INDEX idxNamePrimary ON NameTable (IsPrimary)
table	PersonTable	PersonTable	3	CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, Sex INTEGER, EditDate FLOAT, ParentID INTEGER, SpouseID INTEGER, Color INTEGER, Relate1 INTEGER, Relate2 INTEGER, Flags INTEGER, Living INTEGER, IsPrivate INTEGER, Proof INTEGER, Bookmark INTEGER, Note BLOB )
table	PlaceTable	PlaceTable	16	CREATE TABLE PlaceTable (PlaceID INTEGER PRIMARY KEY, PlaceType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, Normalized TEXT, Latitude INTEGER, Longitude INTEGER, LatLongExact INTEGER, MasterID INTEGER, Note BLOB )
index	idxPlaceName	PlaceTable	57	CREATE INDEX idxPlaceName ON PlaceTable (Name)
index	idxPlaceAbbrev	PlaceTable	58	CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev)
table	ResearchItemTable	ResearchItemTable	31	CREATE TABLE ResearchItemTable (ItemID INTEGER PRIMARY KEY, LogID INTEGER, Date TEXT, SortDate INTEGER, RefNumber TEXT, Repository TEXT, Goal TEXT, Source TEXT, Result TEXT )
index	idxResearchItemLogID	ResearchItemTable	72	CREATE INDEX idxResearchItemLogID ON ResearchItemTable (LogID)
table	ResearchTable	ResearchTable	17	CREATE TABLE ResearchTable (TaskID INTEGER PRIMARY KEY, TaskType INTEGER, OwnerID INTEGER, OwnerType INTEGER, RefNumber TEXT, Name TEXT COLLATE RMNOCASE, Status INTEGER, Priority INTEGER, Date1 TEXT, Date2 TEXT, Date3 TEXT, SortDate1 INTEGER, SortDate2 INTEGER, SortDate3 INTEGER, Filename TEXT, Details BLOB )
index	idxResearchOwnerID	ResearchTable	59	CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID)
index	idxResearchName	ResearchTable	60	CREATE INDEX idxResearchName ON ResearchTable (Name)
table	RoleTable	RoleTable	25	CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT )
index	idxRoleEventType	RoleTable	69	CREATE INDEX idxRoleEventType ON RoleTable (EventType)
table	SourceTable	SourceTable	19	CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB )
index	idxSourceName	SourceTable	61	CREATE INDEX idxSourceName ON SourceTable (Name)
table	SourceTemplateTable	SourceTemplateTable	29	CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB )
index	idxSourceTemplateName	SourceTemplateTable	70	CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name)
table	URLTable	URLTable	32	CREATE TABLE URLTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, OwnerID INTEGER, LinkType INTEGER, Name TEXT, URL TEXT, Note BLOB )
table	WitnessTable	WitnessTable	23	CREATE TABLE WitnessTable (WitnessID INTEGER PRIMARY KEY, EventID INTEGER, PersonID INTEGER, WitnessOrder INTEGER, Role INTEGER, Sentence TEXT, Note BLOB, Given TEXT COLLATE RMNOCASE, Surname TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE )
index	idxWitnessEventID	WitnessTable	64	CREATE INDEX idxWitnessEventID ON WitnessTable (EventID)
index	idxWitnessPersonID	WitnessTable	65	CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID)

People View Error – at most 64 tables in a Join #error #config

Posted on 2012-03-192022-04-28 by Tom Holden

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.