First Look at RM8 #Database

More changes than expected with the result that many (most?) of the queries and scripts published on this site won’t work with RM8 databases.

New Tables

CitationLinkTable: was expected to support the announced new feature of master citations.

AncestryTable and FamilySearchTable: replace the former LinkAncestryTable and LinkTable, now gone.

TagTable: looks to have replaced LabelTable, now gone.

TaskTable and TaskLinkTable: appear to have replaced ResearchTable and ResearchItemTable, now gone.

Global Change

Every Table has, as its last column, a new column named UTCModDate, apparently intended to track the date on which a record was added or last changed.

Query to Search Parents by Children’s Given Names #search #family

File above incorporates revision from discussion below to correct the count of children. Tested with #rm7 #rm8 and should be compatible with prior versions. TH 2021-03-03

The following script was developed in response to the discussion Search for family by given names of children (was RM-8)

-- three_childrens_names.sql
-- Jerry Bryan, 22 May 2020
-- This query searches for parents based on given names of their children.
-- It gets a hit when three or more children have any primary or alternate
-- given names that are in a hardwired list. The report lists the parents.

SELECT COUNT(C.ChildID) AS fam_size,
F.FatherID, father.Given, father.Surname, F.MotherID, mother.Given, mother.Surname
FROM ChildTable AS C
JOIN
(SELECT DISTINCT NN.OwnerID
FROM NameTable AS NN
WHERE NN.Given LIKE("%Mary%") OR NN.Given LIKE ("%John%") OR NN.Given LIKE("%Elizabeth%"))
AS N ON N.OwnerID = C.ChildID
JOIN
FamilyTable AS F ON F.FamilyID = C.FamilyID
JOIN
NameTable AS father ON father.OwnerID = F.FatherID
JOIN
NameTable AS mother ON mother.OwnerID = F.MotherID
GROUP BY C.FamilyID
HAVING fam_size >= 3
ORDER BY father.Surname, father.Given, mother.Surname, mother.Given;

Jerry

Aargh! Systemic Error re: GroupID

I've just discovered that I caused a systemic error in several scripts dealing with Groups in relating the LabelTable.LabelID to the GroupTable.GroupID when the latter is actually related to LabelTable.LabelValue. It's of no consequence when groups are consecutively created but not deleted because the LabelID and LabelValue match. The problem arises when a group before the latest created is deleted; the LabelID for a subsequently created group could point to the wrong or a non-existent GroupID.

Over the next while, I will try to find and correct those scripts. A starting point for identifying them would be a site search using both terms "LabelID" and "GroupID".

UPDATE 2020-05-16: not as bad as I feared. The original error was corrected in many but not all scripts in 2011. Here are the exceptions that have been found and are now corrected:

Copy RIN to REFN and embedded script
Copy Fact to Group and CopyFact2Group.sql
Census Needed: Named Group and CensusNeededGroup2.sql

Names – Set Alternate of type Birth to Primary #names #alternatenames #birth

This script does two things:

  1. For those persons having one Alternate Name of type “Birth” and a Primary name of “undefined” type, it sets the Alt name as the Primary name and changes the previous Primary name to an Alternate Name.
  2. For those persons having more than one Name of type “Birth”, it lists the Record Number (RIN) and Surname and Given Names so that the user may resolve the ambiguity down to one so that the script may swap it on the next pass.

If the conclusion is that the current Primary (birth) name is the preferred name, the user may choose to delete the Alternate Name, modify its name type or leave it as is. Either way, the script will not change the Primary to Alternate unless the Primary type is “undefined”.

Sources and media remain attached to the name, whatever its Primary/Alternate state is. However, until RM8, those attached to the Primary Name are essentially inaccessible and unusable (see Citations Invisible – Reveal and Revert).

Download

Names-Swap_AltBirth_to_Primary.sql

 

Combining the contents of 2 queries on the same file

I am having trouble setting up this query.

 

I want the results of the first query to merge/join/union with the second query where the ownerid from the first query equals the ownerid from the second query. The bold line should not be part of the query. I put it in to get the records from the second that equal the ownerid from the first query.

Select n1.ownerid, n1.surname, n1.given, n1.nametype, n1.prefix, n1.isprimary

from nametable n1

where n1.nametype = 1

and n1.isprimary = 0

order by surname, given

I get the results in Pic 1

 

select n2.ownerid, n2.surname, n2.given, n2.nametype, n2.prefix, n2.isprimary

from nametable n2

where n2.nametype = 0

and n2.isprimary = 1

and ownerid in(10518, 10519, 11626, 11976, 3536, 11805, 10773, 11655, 16605, 11802, 255, 129, 130, 15721, 9046, 11495, 11531)

order by surname, given

I get the results in Pic 2

I want the results of query 1 to be combined with the results of query 2 where the ownerids from the first query are only the ownerids of the second query.

So I get something like Pic 3

Privatise Extra BMD Events #events #reports #private

Problem

Many persons in my database have multiple Birth events created from different sources; likewise for other vital facts. Even though I may have marked one as Primary, the others still clutter reports: narratives look like repetitious gobbledygook; calendars have the same person multiple times, even on the same day. I want to clean up the publishing mess without losing the evidence.

This is especially the case for databases downloaded from an Ancestry Member Tree where events are generated by accepting or assigning a source and choosing to make the data into an alternate fact because it varies in some way from an existing fact.

Solution

Many report settings in RootsMagic have an option to include or exclude private events. I could reduce report clutter by setting the non-Primary Birth events to Private without losing the data that led to their creation. The Primary event should have whatever consideration and logic went into it being deemed the most accurate. I haven’t set a Primary in every case where my database has multiples so I want two things:

  1. A batch process that sets Private all the non-Primary events of a set having a Primary.
  2. A list of all the sets of multiple events that do not have a Primary so I can follow up.

Individual Summary with multiple Birth events, highlighted.

Individual Summary excluding Private events, in this case, the extra Birth events.

Implementation

BMD_private_multiples.sql 2020-02-15 Click to view; right-click to download.

The above SQLite script builds a series of temporary SQLite Views (stored queries), does the possible privatising and exits displaying the View “BMDmultstofix” which lists the persons needing follow up attention to set Primary one of the plural (multiple) events. The Views are all in memory and are lost when the SQLite manager exits the database.

Usage

The script executes very quickly on small files so it is possible to keep the SQLite manager open on the database and to re-execute the script at intervals while editing the database in RootsMagic. One could simply set the Primary event out of a group of multiple events for a person in RM’s Edit Person screen and then run the script to set the others Private or manually set them. Re-running the script will shrink the list needing Primaries by those you have set.

For family-type events (Marriage), the View “BMDmultstofix” will list both spouses separately. When the Marriage event is set to Primary on either spouse, the two separate listings will go.

Possible Enhancements

Colour Coding

The list of persons needing attention could be quite large, depending how assiduously you have been setting the Primary flag and the number of people and events in your database. So it might be nice to extend the last query or even last View to pick up the color code for each person which could be used to sort or filter the list.

Private as Default

Instead of privatising only those non-Primary events in groups having a Primary, another approach would be to privatise all non-Primaries. That would really clean out the reports and require user editing to get the desired outputs. This may be preferable.

Enhanced Version

BMD_private_multiples2.sql 2020-02-16

Both of the hypothesized enhancements have been incorporated. To ‘fix’ a group of multiples, one of the events must be set Primary and un-Privatised or another event added that consolidates one’s research into an assertion. By default, a new event is not Private and need only be made Primary.

In SQLiteSpy, any column can be sorted by clicking on the heading. Some other software, SQLite Expert is one example, also support filtering by column. These tools enable grouping color code or by surname without having to modify the script or write your own custom query of BMDmultsToFixClr view.

Screenshot

Screenshot from SQLiteSpy of results from running BMD_private_multiples2.sql

New RootsMagic-Users Mailing List/Forum

RootsWeb is deactivating its Mailing List service on March 2, 2020, but leaving the archive of each List online, for now. A successor has been set up on Groups.io, operated by users. The old and the new list appeal to those who refuse to participate in the now 21,000 member facebook group and others for various reasons. Moreover, RootsMagic Inc. has barred the door to new subscribers to its Forums.

Trying to find duplicate spouses

I am trying to find duplicate spousess.
I created the following query:
select fatherid,
(select n1.surname || “, ” || n1.given from nametable n1 where fatherid = ownerid) as fathername,
motherid,
(select n2.surname || “, ” || n2.given from nametable n2 where motherid = ownerid) as mothername
from familytable
order by fatherid, motherid;

1. Should there be blank fatherid’s for a motherid in the result?
2. Or likewise blank motherids for a fatherid in the results?
3. Am I safe to think that if there are 2 motherid’s for the same fatherid that it is a duplicate?

Backup database open in both SQLite and RootsMagic #backup

Problem

Neither the RootsMagic File>Backup nor File>Copy can proceed because the database file “is being used by another process” (RootsMagic’s own or your SQLite manager or other outboard SQLite connection). And if you’ve chosen Backup, RM gracelessly blocks you from further work with an always-on-top fake news message window “Creating backup file…” requiring closure of RM to clear.

If you were using a SQLite manager that’s connected to the database file, you have to close it in order to use RM’s utilities and then reopen it with numerous steps to finally execute the SQL you’ve been carefully crafting to make some important changes to your data. That disruption takes time, may lose some valuable history from the SQLite manager and, perhaps, derail your train of thought. Same goes for ordinary users getting thrown off track after running Compact and other unknown things that screw up the RM backup.

Outboard zip utilities such as 7Zip, RAR, WinZip,… all, to the best of my knowledge, require the closure of all other processes for them to proceed but do handle such a blockage gracefully, unlike RM4-7. It’s not sufficient that RM get to handle the blockage gracefully; it shouldn’t be blocked at all!

Some Workarounds

Looking into this issue in 2019, I’ve discovered some useful alternatives to RM’s backup and copy utilities that have arisen since the days of RM4 and Windows XP in 2009/10 when the original SQLite Tools for RootsMagic 4 Wiki began. SQLite Expert Professional lists “Backup” as a feature that likely uses the Online Backup API, perhaps the best suited to the task but at a price.

This post presents four free, fast backup methods that allow the database to remain open in RootsMagic and in one (or more) outboard SQLite connections. They have been tested with a database open in RM7.5.9 and simultaneously in three SQLite managers: SQLite Expert Personal 4.2.0.784, SQLiteSpy 1.9.12 and SQLiteStudio  3.2.1.

  1. Windows 10 File Explorer
  2. SQLite Command: VACUUM INTO
  3. SQLite Archive File
  4. Export to SQL Text file

Windows 10 File Explorer

My first surprise was to discover that the Zip function integrated in File Explorer (possibly from even before Win10) does not object to the database file being open. Nor does its copy function. The latter may be the easiest and fastest method.

Copy

With the File Explorer window open and the “filename.rmgc” database file selected, Copy (Ctrl+C) and Paste (Ctrl+V) results in “filename – Copy.rmgc”. Repeat and the copy is automatically serialized as

filename – Copy (2).rmgc”
filename – Copy (3).rmgc”

No manual file naming is needed to prevent overwriting of the last copy and any copy can be immediately opened by RM or any of the SQLite apps. The Created Date timestamp for the copy is the Date Modified timestamp of the original, so its value puts the copy’s version of the data into context, provided you have some log (recorded or remembered) of what you did before and after.

Zip

Right-click on the selected “filename.rmgc” database file and, from the popup context menu, click on Send to > Compressed (zipped) folder. This will popup a dialog box to allow you to change the default name “filename.zip”.

Repeat and File Explorer automatically serializes the name:
filename (2).zip”
filename (3).zip”

You could change the zip filename extension to “.rmgb” at any of these backups so that RootsMagic could find and restore from it but I wouldn’t bother for two reasons:

  1. It interferes with serializing the filename to preserve successive backups.
  2. It’s easy to extract from the Zip folder using File Explorer which gives you great control over where the extracted file goes with opportunity to rename and move it afterward.

Using File Explorer from within the apps

All of the mentioned apps launch a basic Windows File Explorer dialog Window to Open a database or carry out some other operation. All support Copy, Paste and Send to Compressed (zipped) Folder. Because RM’s window filters for .rmgc files, it is quicker selecting the database file. Presumably, the speed of the subsequent operation is independent of the application that opened the File Explorer dialog. When the copy of zip is complete, simply Cancel the window to back out to where you were with the database.

Copying or Zipping from the Command-Line or PowerShell

If File Explorer could copy or zip the database file while open in RootsMagic and SQLite, it seemed reasonable that these other Windows utilities could, too. The Zip is only available in PowerShell but both can copy files. Unfortunately, both the CLI and PS detected another process and refused to proceed.

SQLite command: VACUUM INTO

The VACUUM command was provided in SQLite 3 and all of the referenced SQLite managers from the get-go but it operated directly on the database in memory. About two months ago (ver 3.27), the command was extended with an INTO clause that writes the result of the VACUUM to a new database file without modifying the original. Effectively, this means that one can make a vacuumed copy of the currently opened database from a SQLite manager as a backup. If the original has not been recently vacuumed by either RootsMagic or by an outboard SQLite manager, then the vacuumed copy will be smaller in size.

The copy can be arbitrarily named and, if named with the .rmgc extension, can be opened directly by RootsMagic without going through a Restore process.

Typing the command and filename and ensuring the latter is unique is less efficient than the File Explorer method. A potential productivity gain would be to write a Windows command or Powershell script to launch the current sqlite3.exe command line shell program to open the database file to VACUUM INTO a filename defined by the script; it could incorporate date and time in the filename. Alternatively, the batch or command script could also zip the resulting .rmgc file and give the zip file the unique name.

After some time, the referenced SQLite managers will undoubtedly update to SQLite 3.27 to support the command and some may incorporate some higher level controls such as a file explorer dialog to facilitate the naming of the vacuumed file.

This command should work in SQLite for both Windows and MacOS.

For the SQLite 3 command line shell program, look for sqlite-tools-win32-x86-3270200.zip or later at https://www.sqlite.org/download.html. Also available for MacOS: sqlite-tools-osx-x86-3270200.zip.

SQLite Archive File

Since 2014, there has been development of a SQLite Archiver utility “sqlar” using SQLite and zlib to archive files of any type to a SQLite database container. The file contents are zipped to a Blob-type field in a table with one row for each archived file. In 2018, it has been made into an optional extension to the SQLite source code and is now integrated in the SQLite command-line shell tool mentioned above. It is possible for a SQLite manager to be compiled with this extension but I don’t know of any yet.

The SQLite command-line tool from 3.22 can be opened and at its prompt given the “.ar” command to  create a SQLite Archive file, add|update files to it, list contents, and extract files from it. From 3.23, the recommended use is from the OS CLI to call sqlite3.exe with the -A option.

In this example in sqlite3.exe, I updated the SQLite Archive file “_LGS_pres.db” with the database “_LGS_pres.rmgc” that was open in RM and the 3 SQLite managers.

sqlite> .ar -uvf _LGS_pres.db _LGS_pres.rmgc
_LGS_pres.rmgc

The Archive file could contain many other files, e.g., the media files or other unrelated files, just as a Zip folder can.

Moreover, the SQLite Archiver can operate as a zip utility producing a .zip file (“compressed folder” in Win10) instead of a SQLite Archive file, skipping over the locks that trip up RootsMagic’s backup and 7Zip.

For more, see SQLite Archive Files.

Export to SQL Text file

This is an old method that has merit because it reads the SQLite database, not just copying it, to create SQL commands which can later be executed to build the identical database. Some SQLite managers support it (SQLiteStudio) and it is available in the command-line shell:

Use the “.dump” command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.

The resulting text file is bigger than the database file but it can be piped into a zip utility for compression. Whether there is a limit that a large database might run into remains to be seen.

For more, see Converting An Entire Database To An ASCII Text File

Observations

  1. There are multiple ways to backup a RootsMagic database while it is open in both the RootsMagic application and one or more outboard SQLite connections.
  2. Using these other ways instead of the RootsMagic backup function may also avoid the recurring situation when RootsMagic blocks itself from completing a backup; a forced shutdown is the only way to resume working in RootsMagic.
  3. For the less technically proficient, Windows File Explorer provides a convenient alternative to the RM backup, even from within the RootsMagic user interface.
  4. RootsMagic developers should look into some of these alternatives that work with SQLite to resolve the conflict with outboard SQLite connections. Especially appropriate could be the Online Backup API because it is native to SQLite and aware of other users of the database, allowing them to “continue uninterrupted while a backup of an online database is made”.

Caveats

  1. Testing these has been necessarily superficial so there may be issues as yet undetected. Please comment below if you come across any.
  2. Make sure that the database is not being modified by one of the connections while the ‘backup’ is going on. When the backup is opened, there could be unexpected results. This is especially true of those methods that work without a SQLite connection to the database, i.e., File Explorer, SQLite Archiver.
  3. Those methods that require a SQLite connection to the database, i.e., VACUUM INTO, Export and Online Backup API, need the RMNOCASE collation (see RMNOCASE; faking it in SQLite Expert, command-line shell et al). It is possible that the resulting database will fail the RootsMagic database integrity test; simply use its Rebuild Indexes tool to correct.