Privatize Living #private #reports #gedcom

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

This script sets private the events, alternate name facts, and personal notes for persons whose Living flag is set. Thus, reports and GEDCOM can take full advantage of the options:

Privatize living people (GEDCOM only)
Include private facts
Include {private} notes
Strip { } brackets

thus providing many more combinations by which these outputs may be tailored.

This is a first pass script, lightly tested so use the usual precautions and feedback any problems. There is a known problem with privatizing notes that are already privatized – RM may output a closing brace character “}”.

PrivatizeLiving.sql

-- PrivatizeLiving.sql
/* 2013-12-14 Tom Holden ve3meo
Privatizes events, alt names, personal notes for persons with Living flag set
so that reports can be made with names of living persons but no details while
full details are outputted for the deceased.
 
To Do: privatize family note when one spouse is Living.
 
N.B.: There is a series of queries in a comment block at the end which can be run
to UNDO the privatization, it is nevertheless advisable to run this script against
a copy of your database in case the process is not perfectlt reversible. Of course,
you may wish to keep the resulting privatization if it suits your purposes so that
reversal is unnecessary.
*/
 
-- Make a table of persons marked Living
DROP TABLE IF EXISTS xLivingTable
;
CREATE TEMP TABLE IF NOT EXISTS xLivingTable
AS
SELECT PersonID, Note, TRIM(CAST(Note AS TEXT)) AS NoteTxt FROM PersonTable WHERE Living
;
 
-- Make a backup table of EventIDs and their IsPrivate setting
-- for all persons marked Living; must cover individual and family events
DROP TABLE IF EXISTS xLivingEventsBak
;
CREATE TABLE IF NOT EXISTS xLivingEventsBak
AS
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Individual events
INNER JOIN xLivingTable Lv ON E.OwnerID = Lv.PersonID AND E.OwnerType = 0
UNION
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living husband
INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
INNER JOIN xLivingTable Lv ON Fm.FatherID = Lv.PersonID
UNION
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living wife
INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
INNER JOIN xLivingTable Lv ON Fm.MotherID = Lv.PersonID
;
 
-- SET EventTable.IsPrivate = 1 for all events in xLivingEventsBak
UPDATE EventTable
SET IsPrivate = 1
WHERE EventID IN
(SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
;
 
 
/*
-- Alternate Names can also be set private and perhaps desirably so so we repeat
the pattern above
*/
-- Make a backup table of non-primary NameIDs and their IsPrivate setting
-- for all persons marked Living;
DROP TABLE IF EXISTS xLivingNamesBak
;
CREATE TABLE IF NOT EXISTS xLivingNamesBak
AS
SELECT NameID, IsPrivate FROM NameTable
WHERE OwnerID IN
(SELECT PersonID FROM xLivingTable)
ORDER BY NameID
;
 
-- Privatize Alternate Names for Persons marked Living in the database
UPDATE NameTable SET IsPrivate = 1
WHERE NameID IN
(
 SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
)
;
 
 
-- PRIVATIZE Personal Notes for persons marked Living by enclosing in braces {}
UPDATE PersonTable
SET Note =
--SELECT
CAST(REPLACE(CAST(Note AS TEXT), CAST(Note AS TEXT), '{' || CAST(Note AS TEXT) || '}') AS BLOB)
--FROM PersonTable
WHERE PersonID
IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
;
 
/*
-- BLOCK OF UNDO queries to reverse the above changes.
 
-- UNDO Privatize events by SET EventTable.IsPrivate = 0 for all events in
  xLivingEventsBak that have IsPrivate = 0
UPDATE EventTable
SET IsPrivate = 0
WHERE EventID IN
(SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
;
 
-- UNDO privatization of Alt Names for Living persons
UPDATE NameTable SET IsPrivate = 0
WHERE NameID IN
(
 SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
)
;
 
-- UNDO Privatize Personal Notes by updating from xLivingTable
UPDATE PersonTable
SET Note =
(SELECT Note FROM xLivingTable Lv WHERE PersonTable.PersonID = Lv.PersonID)
WHERE PersonID
IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
;
*/
-- END of script

An Example of Using SQLite Views #views #colorcoding

Posted on 2013-11-252019-01-27 by thejerrybryan

I thought I would post some results from a little project I’ve recently been working on. Consider a descendant narrative report in RM in any of the supported formats: outline, NEHGS, etc. In any of these reports, the parents of the spouses will be listed as a part of the birth sentence for the spouse, viz. “Jane Doe, daughter of John Doe and Sarah Smith, was born in 1848″. My project has been to assure that I have all the appropriate data and documentation for the parents, even though the data itself will not appear in this particular report except for the names of the parents.

My strategy is to color code such parents of spouses using navy as the color. Navy is not otherwise used as a color in my database. I can then do searches for individuals who are colored navy or make a Named Group out of them or whatever. In other words, I use my set of navy people as sort of a “to do list” for my research.I should explain that I have a Named Group in my database called Active_Research consisting of all the individuals who I am most actively working on. Everybody in my Active_Research group is color coded green and everybody who is color coded green is in my Active_Research group.

So suppose I want to color code as navy all the parents of the spouses of the descendants of John Doe, and by the way John Doe and all his descendants and their spouses are already color coded green and are already in the Active_Research group. So in RM, I do the following.

Color code John Doe and all his descendants and spouses as teal. This temporarily disturbs their normal green color, but I will restore the green before I’m done. And just like navy, teal normally is never used anywhere in my database.
Color code John Doe and all his descendants but not their spouses as green.

At this point, the direct descendants of John Doe have been restored to green, and the spouses have been colored as teal. So I need to color code the parents of all the teal people as navy, and then restore the teal people to green. I will then have restored all my green people to green, and will have color coded all the parents of the spouses of John Doe’s descendants as navy.

It’s trivial to restore the teal people to green from within RM, but before I do I need to color the parents of all the teal people as navy using SQLite scripts. The script to do so is included below. In order to accomplish the desired processing, I experimented with the idea of using views – something I had never done before in SQLite although I had used views before in other SQL environments.

My SQL tends to use lots of subqueries – probably many more than most people would use. It’s just my style, I guess. And I was thinking that all my subqueries might be a lot easier to develop, debug, and manage if they were views than if they were true subqueries.

To tell you the truth, I sort of went overboard. Because I usually code with lots of subqueries, when I replaced the subqueries with views I ended up with lots of views. Some of my views could have been combined, but splitting the views down into as many small pieces as I did made them really easy to develop.

The process of using views proved to be extremely easy. For each view, I would develop it and debug it as a standard query (not as a subquery). Once it was working, all I had to do to convert the query to a view was to precede it with a CREATE VIEW statement. I used CREATE TEMP VIEW to avoid storing the views themselves in my RM database.

If you look at my script, the first SQL statement that really does any processing of my RM database is the UPDATE statement itself. Within the UPDATE statement, there is a subquery which says “SELECT ParentID AS PersonID from ParentView”. ParentView is one of the views I created, and the reference to ParentView kicks off a whole chain of references to all the other views I created. A possible alternative coding style might have been an UPDATE statement that was 15 or 20 lines long with nested subquery on top of nested subquery. All that nested subquery stuff is still really happening, but it’s hidden in all the nested views.

By the way, I really wanted my UPDATE statement simply to say UPDATE ParentView SET COLOR = 10. Such a statement makes perfectly good sense to me, but you can’t update a view. So I had to update an RM table and move the reference to ParentView into a subquery.

Jerry

DROP VIEW IF EXISTS ChildView;
DROP VIEW IF EXISTS TealView;
DROP VIEW IF EXISTS FamilyView;
DROP VIEW IF EXISTS MotherView;
DROP VIEW IF EXISTS FatherView;
DROP VIEW IF EXISTS ParentView;
 
CREATE TEMP VIEW ChildView AS   -- View to produce the RIN and FamilyID of everyone who has parents
SELECT C.ChildID,
       C.FamilyID
FROM ChildTable AS C
ORDER BY C.ChildID;
 
CREATE TEMP VIEW TealView AS    -- View to produce the RIN of everyone who is colored Teal
SELECT P.PersonID
FROM PersonTable AS P
WHERE Color = 13                -- color is teal
ORDER BY P.PersonID;
 
CREATE TEMP VIEW FamilyView AS  -- View to produce the RIN and FamilyID of everyone who has parents and is colored teal
SELECT N.PersonID, C.FamilyID
FROM TealView AS N
       INNER JOIN
     ChildView AS C ON C.ChildID = N.PersonID
ORDER BY FamilyID;
 
CREATE TEMP VIEW MotherView AS  -- View to produce the RIN of the mother of everyone who has parents and is colored teal
SELECT F.MotherID AS ParentID
FROM FamilyTable AS F
        INNER JOIN
     FamilyView AS G ON F.FamilyID = G.FamilyID
ORDER BY F.MotherID;
 
CREATE TEMP VIEW FatherView AS  -- View to produce the RIN of the father of everyone who has parents and is colored Teal
SELECT F.FatherID AS ParentID
FROM FamilyTable AS F
        INNER JOIN
     FamilyView AS G ON F.FamilyID = G.FamilyID
ORDER BY F.FatherID;
 
CREATE TEMP VIEW ParentView AS  -- View to produce the RIN of the parents of everyone who has parents and is colored Teal
SELECT ParentID FROM MotherView AS ParentID
    UNION
SELECT ParentID FROM FatherView AS ParentID
ORDER BY ParentID;
 
UPDATE PersonTable SET Color = 10 WHERE PersonTable.PersonID IN (SELECT ParentID AS PersonID FROM ParentView); -- Update parents of Teal people to be Navy people
 
SELECT * FROM PersonTable WHERE Color = 10;   -- Display the Navy people, Teal people will be restored to Green back in RM

Places without Geo-codes for Named Groups #places #placedetails #geocoding #namedgroup

Posted on 2013-10-212022-04-28 by Tom Holden

It is daunting to work through the Place List, finding Places and Place Details (sites) that need to be geo-coded (i.e., have latitude and longitude values entered) so that they can be readily mapped and their events included in the Place List report option “Print events near a place”. The larger the database of persons and families, invariably the more places and sites and the bigger the challenge. RootsMagic features such as Color Coding and Named Groups help narrow the focus when working on the facts for people and families of primary interest but, as of version 6, do nothing for the Place List. A desirable enhancement would provide the option to filter the Place List to include only those places and sites used by events for persons and families in a given color code or named group. Moreover, other filters would be helpful, such as showing only those lacking coordinates, or containing some string in any of the fields, etc., i.e., something paralleling RM Explorer for people.

This query provides an interim aid for the specific task of finding non-geo-coded places and sites used by the facts for a subset of the people and families in the database. It lists those places and sites for persons in one or more named groups whose group name contains the string “focus”. One would work within the RootsMagic Place List to go directly to these and assign coordinates using its Geo-code and Online Map functions or other resources such as Wikipedia, Google Maps and Google Earth.

Screenshot from SQLite Expert of query results showing places and sites, either or both of which lack geographical coordinates.

In the above screenshot, there are 10 different places listed from RecNo 24 to 33. In the RM Place List, 10 places, starting at Govan, Lanarkshire, all begin with “G”. Thus we have skipped those that are not of primary interest.

PlacesWithoutGeocodesForGroup.sql

-- PlacesWithoutGeocodesForGroup.sql
-- 2013-10-21 Tom Holden ve3meo
/*
Lists those places and place details (sites) and their geographical coordinates (geocodes)
for events of persons who are members of named groups having a groupname containing the string
"focus" and for which one of the sets of coordinates is 0 (i.e., not geocoded).
*/
SELECT P.NAME AS Place
    ,P.Latitude * 0.0000001 AS PlaceLat
    ,P.Longitude * 0.0000001 AS PlaceLong
    ,PD.NAME AS Detail
    ,PD.[Latitude] * 0.0000001 AS DetLat
    ,PD.[Longitude] * 0.0000001 AS DetLong
FROM PlaceTable P
LEFT JOIN PlaceTable PD ON P.PlaceID = PD.MasterID
WHERE ifnull(PD.PlaceType, 2) = 2
    AND P.PlaceType = 0
    AND (
        (
            P.Latitude = 0
            AND P.Longitude = 0
            )
        OR (
            PD.Latitude = 0
            AND PD.Longitude = 0
            )
        )
    AND P.PlaceID -- restrict to places used by events for persons in named group
    IN (
        SELECT DISTINCT PlaceID
        FROM (
            SELECT OwnerID AS PersonID
                ,EventID
                ,PlaceID
                ,SiteID
            FROM EventTable E
            WHERE OwnerType = 0
 
            UNION
 
            SELECT FatherID AS PersonID
                ,EventID
                ,PlaceID
                ,SiteID
            FROM EventTable E
            INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
            WHERE OwnerType = 1
 
            UNION
 
            SELECT MotherID AS PersonID
                ,EventID
                ,PlaceID
                ,SiteID
            FROM EventTable E
            INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
            WHERE OwnerType = 1
            )
        WHERE PersonID IN (
                SELECT DISTINCT P.PersonID
                FROM PersonTable P
                    ,GroupTable
                WHERE P.PersonID BETWEEN StartID
                        AND EndID
                    AND GroupID IN (
                        SELECT DISTINCT LabelValue
                        FROM LabelTable
                        WHERE LabelType = 0
                            AND LabelName LIKE '%focus%'
                        ) -- persons in groups with name containing "focus"
                )
        )
    AND (
        PD.PlaceID IsNull -- Places with no PlaceDetail
        OR PD.PlaceID -- OR restrict to sites used by events by persons in named group
        IN (
            SELECT DISTINCT SiteID
            FROM (
                SELECT OwnerID AS PersonID
                    ,EventID
                    ,PlaceID
                    ,SiteID
                FROM EventTable E
                WHERE OwnerType = 0
 
                UNION
 
                SELECT FatherID AS PersonID
                    ,EventID
                    ,PlaceID
                    ,SiteID
                FROM EventTable E
                INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
                WHERE OwnerType = 1
 
                UNION
 
                SELECT MotherID AS PersonID
                    ,EventID
                    ,PlaceID
                    ,SiteID
                FROM EventTable E
                INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
                WHERE OwnerType = 1
                )
            WHERE PersonID IN (
                    SELECT DISTINCT P.PersonID
                    FROM PersonTable P
                        ,GroupTable
                    WHERE P.PersonID BETWEEN StartID
                            AND EndID
                        AND GroupID IN (
                            SELECT DISTINCT LabelValue
                            FROM LabelTable
                            WHERE LabelType = 0
                                AND LabelName LIKE '%focus%'
                            ) -- persons in groups with name containing "focus"
                    )
            )
        )
ORDER BY Place
    ,Detail;

MapEvents-KML query #googlemaps #places #placedetails #geocoding #events

Posted on 2013-10-192022-04-28 by Tom Holden

Description

This query helps you plot events from your RootsMagic database on ~~Google Maps~~, Google Earth, and ~~Bing Maps~~, provided there are geo-coded Places and Place Details (sites) in your database. It generates results in the Keyhole Markup Language (KML) that can be pasted into a KML shell file and then imported into these mapping services. ~~Google Maps provides the most useful display but the KML file must be stored on a network server (could be a HTTP server on your local computer)~~. Using their respective API or SDK interface would provide superior results from all three but that is beyond my capability. 2020-05-18 there no longer seems to be a way to convey or overlay a collection of points in a XML/XMZ file to Google Maps and Bing Maps; however, ArcGis and OpenStreetMaps are possibilities.

Google Maps

Preserved here to appreciate what once was possible…

Example showing Placemark callout for the event selected in the sidebar, corresponding to the record highlighted in the SQLiteSpy screenshot below. The underline signifies the coordinates are for the Place Detail (Site).

Example of Google Maps callout when a placemark is clicked that has multiple events with the same coordinates.

Click on this link to view the file that populated the Google Map above: MapEvents.kml
Copy and paste its URL into the Google Maps Search form to view on Google Maps: http://sqlitetoolsforrootsmagic.com/wp-content/uploads/2019/01/MapEvents.kml
Right-click and Save (download) this file to your local drive to import into Bing Maps and Google Earth.

Download Query

Download this query to run on your data: MapEvents-KML.sql
Be sure to change the parameters in the query to suit your requirements. If your data has no events in the Oshawa area before about 1902, my parameters will find nothing!

Copy and paste the results of the query into this shell where instructed using a text editor and save as a .kml file: MapEvents-shell.kml
N.B.: you must restrict the number of Placemarks to 200 or fewer in your KML file.

Screenshot of SQLiteSpy after running query that produced the Placemarks for the KML file on this page.

Google Earth

If the Wikispaces server issued the proper mime type for a KML file and Google Earth is installed on your machine, clicking on: MapEvents.kml would launch Google Earth with these Placemarks imported. As it does not, you have to download the file to your local drive, open Google Earth and import the file from your drive.

Bing Maps

Preserved here to appreciate what once was possible…

The ability to use with Bing Maps a KML or KMZ file or any collection of points in a file has disappeared completely. That the URL reported below worked when the Import control had already disappeared must have been during the transition from full support to none. Tom Holden, 2020-05-18.

Since this page was first authored, I have learned that one can pass the KML file to Bing Maps without having to download and import. In fact, the Import function has disappeared from where it was. Now (and maybe then, too), one can pass the URL of the KML file to Bing Maps in this fashion:

http://www.bing.com/maps/default.aspx?mapurl=URLofKMLfile

For example, this URL should open Bing Maps with the above referenced KML file that we have seen in Google Maps and Google Earth and result in a display similar to this screenshot:

Each RootsMagic event is numbered uniquely. The number inside the place mark is, I think, that of the highest numbered event in that location. Hovering over an event in the list pops up a small title label beside its location on the map. While the Google products enable you to browse all the events in a place, Bing does not directly although one could sort them by place and scroll through while hovering. However, that hover/scroll feature might also be used to advantage with other sort orders – e.g., by person and date; with Google, it would be very clicky traversing a person’s events but with Bing, just hover down the list, clicking only on those where the description is needed.

GeoCommons now ArcGis.com

It appears that the GeoCommons online mapping service was discontinued in or around 2014. There is a possibly related service at ArcGis.com with a free, public tier. (2019-01-27 TH) ArcGis can only import a zipped shapefile (ZIP), a table (CSV), a GPS Exchange Format (GPX), or a GeoJSON file. Support for the CSV format should be a fairly easy adaptation of the script MapEvents-KML.sql.

Preserved here to appreciate what once was possible…

This was an earlier discovery that I never explored very far, perhaps because it did not support KML at the time. Now it does and one need simply place the URL of the KML file in a form. While I am disappointed with its labelling and listing capabilities, it offers other useful features including conversion between data formats, e.g., upload CSV and download KML or vice versa. And it also does geocoding worldwide with finer resolution for USA, down to street address level. Here’s an example of the same KML file rendered in geocommons:

You can explore the same map online at http://geocommons.com/overlays/406864. Unfortunately, the values in the place list are truncated to a useless state and the HTML underlining for Place Details is mishandled.

OpenStreetMaps

There are a number of ways to overlay KML data onto OpenStreetMaps. One of them is uMap. Using the English version from https://umap.openstreetmap.fr/en/ (there are others), I was able to import the XML file to a layer with this result:

Because the labels for events at the same place stack on top of each other, I would restrict my query to one fact type. Further exploration and some other overlay service may offer better display for co-sited events.

How to Query RootsMagic #sqlitespy #integrity #database

Posted on 2013-10-162022-04-28 by Tom Holden

Promo

Using many of these SQL queries or procedures is pretty easy with a SQLite manager such as SQLiteSpy as illustrated in the example below. Even easier, because you don’t even see the SQL scripts that underlay them, is to use the utilities bundled in the dead simple RMtrix application. The stories following relate the experiences of novice users who accomplished the same task, converting shared facts to individual ones for GEDCOM export to software that does not support RM’s shared events. One used SQLiteSpy; the other did it with RMtrix.

SQLiteSpy User Experience

Responding to a message bemoaning the problem with shared facts, Kim Mills posted this on RootsMagic-Users-L:

Bonnie, I use the shared facts to and this used to be a real frustration
point for me to. But there are a few solutions.

If you just want the _SHAR part removed so it doesn't repeat in the
RootsWeb tree. Go to your tree settings, then to living persons, under
there is a section to exclude tags. Paste _SHAR there. Now it will
look like a normal census or event for JUST the primary person.

But if you want the census or other events to be converted to a real
individual fact for each person, Tom on this group came up with a great
little program for that.
Here is a link to the page that shows how it works.
Facts+-+Split+Shared+to+Individual

I think he has also made it part of the RMtrix program that makes it
easier to run.
Bundled+Utilities+-+RMtrix

Basically make a COPY of your database, then run the utility to split
the facts, then the second one to remove the shared facts. This way the
primary person doesn't have 2 copies of the original fact. Then export
the gedcom as you normally would.

Kim

On 02/26/2013 06:54 PM, Bonnie Weisel wrote:
> I thought it would be a convenience and time saver to start using
> "Shared Facts". Unfortunately it doesn't work out when exporting and
> uploading a gedcom to wc.rootsweb.ancestry.com.
>
>

RMtrix User Experience

In the same thread, Bonnie responded that she had become a new RMtrix user!

I'd like to thank Denise and Kim for their replies on 2/26 to my
questions about exporting a database with shared facts. When exporting
a Rootsmagic database, if you want the sharee to have a copy of the
facts, you will need to use two sqlite procedures.
 
Last night I installed and ran Rmtrix, exported my database, all in less
than 5 minutes. My suggestion is this is the easiest route to
accomplish the task. I've copied my how-to notes below.
 
Installing Rmtrix:
1) The source for Rmtrix is
http://sqlitetoolsforrootsmagic.wikispaces.co/Bundled+Utilities+-+RMtrix
2) Download Rmtrix and run setup.
 
 
To upload a tree with shared facts:
1) Make a COPY of the Rootsmagic database
2) Run Rmtrix and open the COPY of the database
Events->Convert Shared to Individual
a) ->SPLIT Shared to Individual
b) ->Unshare
3) Export to a gedcom (this file can be uploaded to
wc.rootsweb.ancestry.com)
4) Delete the COPY of the database
 
 
-Bonnie

Example – SQLiteSpy Quick Integrity Check

SQLiteSpy is one of my favourites – for its clean interface, speed and display of results. Its installation is really simple – download a ZIP file from here and extract SQLiteSpy.exe to wherever you want. There is no Windows install to perform, the one exe file is complete and ready to run. You will want to create a shortcut and place it in your Start Menu and/or Desktop so you can readily launch it later. Once past the basic familiarisation, you will want to liberate SQLiteSpy from the shackles of RootsMagic proprietary RMNOCASE collation sequence by following the instructions on RMNOCASE – faking it in SQLiteSpy.

SQLiteSpy follows the conventions for Windows programs pretty well. To open a RootsMagic database, use File > Open Database to open the conventional Open dialog window. By default, you will only see files with the extension ‘.db’; to see RootsMagic files, either select ‘Any file (*.*)’ from the Files of type selection box OR in the File name entry box, type ‘*.rmgc’ (without the quotes). You may also want to be extra safe by checking the box Open as read-only.

Once SQLiteSpy has opened a RootsMagic database, you will see a screen as below, but with the right side empty.

Type a SQL command or set of commands in the SQL Editor area (each command must end with the semi-colon). In the example is the command to do an integrity check without checking the indices (SQLiteSpy cannot do the full integrity check on a RootsMagic database).

Press function key F9 or click Execute and select Execute SQL to execute the commands in the SQL Editor.

The results of the commands will be shown in the middle pane (if the integrity check had passed, it would simply display ‘ok’. Any cell that is selected in the results pane will be expanded in the bottom pane. You can adjust the dividers between the panes to see more or less.

Pretty easy! If you got this far using SQLiteSpy, then you are away to the races!

The File menu is where you can load one of the SQL command files you can download from this site and save a command file you have made yourself. SQLiteSpy does a nice job of tracking recently opened databases and SQL files – just use the Reopen selection.

SQLiteSpy can execute all the commands in the SQL Editor (F9) or just a command that is highlighted (Ctrl-F9). Remember, each command ends with a semi-colon and those that retrieve data begin with SELECT. You don’t modify anything with a SELECT.

Don’t forget – RMNOCASE – faking it in SQLiteSpy for maximum access to your database.

Example – Change fact type globally

The following problem was posted in the RootsMagic Forums by user royviggo at
http://forums.rootsmagic.com/index.php?/topic/13013-change-fact-type-globally-after-import-from-legacy/#entry60476:

Hi, I’m a new RootsMagic user, and I imported a tree from Legacy. Got my custom fact types in Legacy as new fact types in RootsMagic, and thats nice. But some of them already exists as fact types in RootsMagic, so I wonder if there is a way to change fact type globally? I can change fact type on one fact at a time, but it will be very boring to change about 500 facts for 2500 persons…
I can edit the new fact types, but I think it’s silly to have two Residence facts, so I want to change them if possible. Any tips?

Forum user vyger responded with this well-written guide to using SQLiteSpy to accomplish the task:

I would use the SQLliteSpy option and do regularly as I try as far as possible to keep within the Rootsmagic standard set of facts. I do this to maintain some compatibility with the recognised standard and often import gedcoms from other users with multiple custom facts. A simple explanation of how to achieve this global change appears later in this post.

Personally I would go to Tom’s page below and download SQLiteSpy, remember to follow the link to “fake collation” and follow his instructions.

http://sqlitetoolsfo…/SQLiteManagers

For a novice with no database knowledge this can look very complicated but a lot can be achieved with very basic single table changes which this question asks/requests, so do read on.

I would first make a copy of your database by finding it in Windows Explorer, Copy it and Paste it which will give you an exact copy, rename this copy as something so you will easily recognize it as your test database and try SQLiteSpy on that database first. Even when sure what you are now about to do on your working database always back it up before running any queries on it.

Once you have opened your Rootsmagic database with SQLiteSpy remembering to change the “SQLite3 Database” file option to “Any File” you will see all the tables in the database to the left.

Double Click on FactTypeTable and you will see a list of all Fact Types (one per row) and each identified by a unique number FactTypeID. As TomH said anything greater than 999 is a user defined fact.

Now Double Click on EventTable, Don’t look at it in too much detail but the EventType number corresponds to the FactTypeID in the FactTypeTable, in other words they are linked and this EventTable contains all your facts both Individual and Family.

Jump back to the FactTypeTable and examine your facts. For the purpose of explanation I will use an example where a FactTypeID #1016 is found called BORN, well we know that this is should probably be the Rootsmagic built in Birth Fact which is FactTypeID #1. So once we are happy that we want to globally make that change go back to the EventTable by double clicking on it and all we want to do is change all instances of EventType #1016 to EventType#1, really just a find and replace operation. Copy the simple code below into the pane at the top of SQLiteSpy and press F9. The bottom left info bar below the list of tables will now report the time it took to complete the operation and how many records were updated.

```
UPDATE EventTable
SET EventType = 1
WHERE EventType = 1016
;
```
At this point open the database again with Rootsmagic, use File > Database Tools > Rebuild Indexes and check the changes you just made.

TomH would be very expert in these queries but everything in baby steps and I can understand how some of his more complex queries can appear a bit foreboding so a primary school beginners guide would certainly get more people into using SQL IMO.
😉

Edit : Hopefully nobody pasted the code snippet literally without changing the the SET EventType – Where EventType numbers to their own requirements. I was going to say hopefully it goes without saying but it’s better to be said just in case

:rolleyes:
and remember to experiment first and always back up before applying any queries.

Discussions & comments from Wikispaces site

Facts – Add custom MRIN event to each family #facttypes #events #mrin

Posted on 2013-10-162019-01-27 by Tom Holden

Why? How?

Example of an MRIN event added by the script. (click for full size)

Main Script | Mismatched FMNO Report | Earlier GEDCOM Solution

RootsMagic 6 hides the record number commonly known as MRIN except as an option in the title of the Family Group Sheet and in the Marriage List report. This is not good enough for some users who, perhaps migrating from experience with other family tree software, have a document filing system organised on MRIN. In RM, the “MRIN” comes from the FamilyID in the FamilyTable and is not based on a Marriage event; hereafter, I refer to it as FMNO. It is not preserved in data transfers via GEDCOM or drag’n’drop between RM databases, except in special circumstances. With that caveat, the following SQLite script creates a custom FMNO family event and adds it to every couple and single parent with their FamilyID number stored in the event description with the prefix “FMNO “. A second script reports on mismatches between the FamilyID number and the FMNO event value as could arise from a transfer to another database. And to round things out showing that there is always more than one way to accomplish something, see a non-SQLite method for batch creation of the FMNO events at the bottom of the page.

Main Script

Events-FMNO.sql

-- Events-FMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Exposes otherwise invisible FamilyID (except optionally in title of FGS)
by adding an event to each spouse containing the FamilyID.
The event added is a custom Family event named *FMNO, abbreviated FMNO which is
first created by the script if one having the same abbreviation does not exist.
*/
-- Add a FMNO fact type if none exists
INSERT
    OR IGNORE
INTO FactTypeTable(OwnerType, NAME, Abbrev, GedcomTag, UseValue, UseDate, UsePlace, Sentence, Flags)
SELECT 1
    ,'*FMNO'
    ,'FMNO'
    ,'EVEN'
    ,1
    ,0
    ,0
    ,CAST('[Desc].' AS BLOB)
    ,- 1
WHERE (
        SELECT FactTypeID
        FROM FactTypeTable
        WHERE ABBREV LIKE 'FMNO'
            AND OwnerType = 1
        ) ISNULL;
 
-- Delete all FMNO events  N.B. RM will delete all events for a FactType that you delete through it.
-- DELETE FROM EventTable WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE ABBREV LIKE 'FMNO' AND OwnerType=1);
-- Add FMNO event to each couple without such an event, description to contain FamilyID (invisible FMNO)
INSERT
    OR
 
ROLLBACK
INTO EventTable(EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate, IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note)
 
SELECT (
        SELECT FactTypeID
        FROM FactTypeTable
        WHERE ABBREV LIKE 'FMNO'
            AND OwnerType = 1
        ) AS EventType
    ,1 AS OwnerType -- a Family event is type 1
    ,FamilyID AS OwnerID
    ,0 AS FamilyID
    ,0 AS PlaceID
    ,0 AS SiteID
    ,'.' AS DATE
    ,1 AS SortDate -- places event at or near top of list in Edit Person screen
    ,0 AS IsPrimary
    ,1 AS IsPrivate -- presumably having this event set as private will provide needed output control
    ,0 AS Proof
    ,0 AS STATUS
    ,(
        SELECT JULIANDAY('now', 'localtime') - 2415018.5
        ) AS EditDate -- does not affect Date last edited in People view
    ,NULL AS Sentence
    ,'FMNO ' || FamilyID AS Details -- having the prefix "FMNO" helps to stand out in tabular reports
    ,NULL AS Note
FROM FamilyTable
WHERE FamilyID -- don't add an FMNO event to those couples already having one
    NOT IN (
        SELECT DISTINCT OwnerID -- list of all FamilyID's already having the FMNO event
        FROM EventTable
        WHERE OwnerType = 1
            AND EventType IN -- in case there is more than one FMNO family fact type
            (
                SELECT FactTypeID
                FROM FactTypeTable
                WHERE ABBREV LIKE 'FMNO'
                    AND OwnerType = 1
                )
        );

Mismatched FMNO Report

Example of mismatches after a drag’n’drop transfer to a new database, as displayed in SQLite Expert. (click for full size)

Unfortunately, a drag’n’drop of Everyone from one database to a new, empty database does not necessarily preserve all FamilyID and PersonID numbers. Many were preserved but many not, as seen in this extract from the following script’s output; fully 1/3 of almost 400 FamilyIDs were changed while PersonID changes were many more. What happens is that new PersonTable and FamilyTable tables are generated and records unused in the originating database are reused in the new, thus shifting the record numbers down. GEDCOM import into an empty database offers the option to Preserve record numbers but this only applies to the person, not the family. A document filing system based on either or both the MRIN/FMNO and RIN of the originating database will be out of sync with the internal record numbers for many couples and, probably, persons in the target database. Carrying over the FMNO and RIN as events to the target database preserves the document trail for as long as those events are preserved and helps to realign the filing system with the database. Also see Copy RIN to REFN.

Events-MismatchedFMNO.sql

-- Events-MismatchedFMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Compares the OwnerID for Family events having the Abbrev "FMNO" against
the value stored in the event description, listing those that do not match
along with the names and RINs of the couple.
Used in conjunction with Events-FMNO.sql.
*/
SELECT SUBSTR(CAST(E.[Details] AS TEXT), 6) AS FMNO -- extracts everything after "FMNO "
    ,E.[OwnerID] AS FamilyID
    ,ifnull(N1.Given || ' ' || UPPER(N1.[Surname]) || '-' || FM.FatherID, '')
   || ' + ' ||
   ifnull(N2.Given || ' ' || UPPER(N2.[Surname]) || '-' || FM.MotherID, '') AS Couple
FROM EventTable E
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
LEFT JOIN NameTable N1 ON FM.FatherID = N1.OwnerID
    AND + N1.IsPrimary
LEFT JOIN NameTable N2 ON FM.MotherID = N2.OwnerID
    AND + N2.IsPrimary
WHERE E.OwnerID NOT LIKE SUBSTR(Details, 6)
    AND E.OwnerType = 1 -- Family event
    AND FT.[Abbrev] LIKE 'FMNO';

Earlier GEDCOM Solution

I discovered Alfred’s 9 April 2009 solution using Microsoft Word wildcard search and replace on the GEDCOM and Romer’s 8 July 2009 reiteration of that after having developed the SQLite scripts.

Source Templates – Set Quotation Punctuation to ‘UK’ or ‘Logical’ Placement #sourcetemplates #update

Posted on 2013-09-292019-01-27 by Tom Holden

Most of the built-in source templates have the comma or period separating a quotation from the rest of a Footnote, Short Footnote or Bibliography sentence (or ending the sentence with a quotation) inside the quotation marks. This is known as ‘US’ style or convention. ‘UK’ or ‘logical’ convention places them outside the quotation. This query attempts to find all such instances and move them to the ‘UK’ position.

These names for the two styles do not indicate exclusive use in either country. There are divergences in practice not only trans-Atlantic but also north-south, between sectors, fiction and non-fiction et al according to Grammar-Monster.com. It characterises the innie-outie placement of periods and commas relative to the quotation as US vs UK convention, granting that that is just a convenient oversimplification. An article by Grammar Girl echoes that and cites references that indicate the ‘US’ practice follows that of early typesetters and compositors using now-obsolete technology while the ‘UK’ practice is “logical”. Note that I placed the period ending the previous sentence outside the quotes; I cannot see any reason that it should be within.

I learned my grammar in the 50’s and 60’s in Canada which some have said is culturally mid-Atlantic, also an over-simplification, but, always trying to be logical, I evidently use “UK rules”. Apparently, Canadian practice has evolved: the government’s own guide to “The Canadian Style” encourages placing period and comma within the quotes except “when a very high degree of accuracy is required” in which case ‘logical’ placement is used. I would argue that Evidence style citations are supposed to be accurate and, therefore, …

SourceTemplates-SetUKquotePunctuation.sql
This query requires the REGEXP function which is supported in SQLiteSpy but not by SQLite Expert Personal without loading an extension.

Source Templates – Revision Utilities #sourcetemplates

Posted on 2013-09-252022-04-29 by Tom Holden

RootsMagic invited users to submit any issues they are having with the built in source templates as of version 6.3.0.3 in the Forum thread Sentence Templates To Be Fixed on 20 Sep 2013. Extraneous punctuation and text when one or more fields is left empty was identified as an issue and one that the RootsMagician intends to fix, at least for those templates so used more than rarely. It’s not clear what other issues might be addressed but new/modified functionality was ruled out from this round.

In an attempt to identify the scope of the extraneous characters problem, a series of queries were developed that readily show the outputs for empty citations of empty sources for each of the built-in source templates. The result lends itself to editing the templates and may be helpful in identifying and resolving other issues.

SourceTemplateRevisionUtilities-CitationManager+EndNote.png

Empty citations on copies of built-in source templates showing extraneous punctuation and text from one and its resulting report endnote.

SourceTemplateRevisionUtilities.sql rev 2013-09-25

-- SourceTemplateRevisionUtilities.sql
/* 2013-09-24 ve3meo
2013-09-25 added ripple update of the name of the source template through to the citation comment
 
This series of queries is intended to facilitate review and editing of
copies of the builtin source templates to remove extraneous punctuation
and text when fields are left empty. The series assumes a clean database
with one person RIN=1 to whom an empty citation using each source template
will be linked. The series:
1. Creates a copy of each builtin source template which can then be edited in RM, name prefixed with *
2. Creates an empty source for each copy of the builtin source templates, source name = template name
3. Creates an empty citation of each source linked to a person whose RIN is 1.
4. Ripples the name of the source template through to citation comment for use in end notes.
 
The database can be opened by RM on the Citation Manager for Person 1. The names of the sources
are ordered alphabetically to match the order in the Source Template List. Selecting a citation
shows the three sentences resulting from the empty source citation. Using an external SQLite manager,
the SourceTemplateTable sentence templates for Footnote, Short Footnote and Bibliography can be edited,
thus saving much drilling up and down in RM. Simply selecting another citation in RM and returning
refreshes the sentence generation to catch up to the changes in the template.
 
Of course, there may other ways of working effectively, such as two instances of RootsMagic (resident and portable)
open on the common database, one in the Citation Manager, the other in the Source Templates window.
*/
 
-- Create a copy of each builtin source template
INSERT INTO SourceTemplateTable
SELECT TemplateID + 10000
    ,'*' || NAME
    ,Description
    ,Favorite
    ,Category
    ,Footnote
    ,ShortFootnote
    ,Bibliography
    ,FieldDefs
FROM SourceTemplateTable
WHERE TemplateID < 10000;
 
-- Create an empty source for each copy of the builtin source templates
INSERT INTO SourceTable
SELECT NULL AS SourceID
    ,NAME
    ,'' AS RefNumber
    ,'' AS ActualText
    ,'' AS Comments
    ,0 AS IsPrimary
    ,TemplateID
    ,(
        SELECT CAST('<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
        ) AS FIELDS
FROM SourceTemplateTable ST
WHERE ST.TemplateID > 10000;
 
-- Create an empty citation for each empty source based on copies of the builtin source templates
-- all these citations are to the person with RIN=1
INSERT INTO CitationTable
SELECT NULL AS CitationID
    ,0 AS OwnerType
    ,SourceID
    ,1 AS OwnerID
    ,'[[user:ve3meo]]' AS Quality
    ,0 AS IsPrimary
    ,'' AS Comments
    ,'' AS ActualText
    ,'' AS RefNumber
    ,0 AS Flags
    ,(
        SELECT CAST('<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
        ) AS FIELDS
FROM SourceTable S
WHERE S.TemplateID > 10000
ORDER BY S.NAME;
 
/* Ripple SourceTemplate name through to Citation comments
 via Source comments for listing in end notes. Having the name
 in Source comments may also be convenient.
*/
UPDATE SourceTable
SET Comments = (
        SELECT ST.NAME
        FROM SourceTemplateTable ST
        WHERE SourceTable.TemplateID = ST.TemplateID
        );
 
UPDATE CitationTable
SET Comments = CAST(x '0A' AS TEXT) || '{' || (
        SELECT NAME
        FROM SourceTable S
        WHERE CitationTable.SourceID = S.SourceID
        ) || '}';

Media – Set Primary Photo for Persons #media #update

Posted on 2013-09-232025-04-18 by Tom Holden

This query responds to a problem with an import from Family Historian via FTM-flavoured GEDCOM reported by Stewartrb in the RootsMagic Forums thread
GEDCOM import with media. The images linked to the person in Family Historian were ultimately tagged as such in RootsMagic but none was marked as the Primary photo. Consequently, none showed on the main screen nor in reports other than Scrapbooks. This query sets the last image-type media file added to the Gallery among those that have been tagged to a person as that person’s Primary photo, when none of the tags for that person have been so checked. It could be readily modified to be the last tag added to the person rather than the last imported mediafile or to be the first instead of the last.

MediaTags-SetPrimaryForPersons.sql

MediaTags-SetPrimaryForPersons-FirstTag.sql Added 2025-04-18 Sets the first mediatag as the Primary.
Both versions are compatible with RM4 to #RM10.

-- MediaTags-SetPrimaryForPersons.sql
/* 2013-09-23 ve3meo
Sets the last mediatag as the Primary photo for a person with 1 or more tags for image-type media, none of which are so checked.
*/
UPDATE MediaLinkTable
SET    IsPrimary = 1
WHERE  LinkID IN
       ( -- a list of the last LinkIDs for persons with image-type media tags, none of which marked as the Primary photo
       SELECT  LinkID
       FROM    ( SELECT  *
               FROM     ( SELECT    ML.LinkID        -- tag number
                                 , ML.OwnerID        -- RIN for person, given the constraint below
                                 , ML.IsPrimary      -- 1 if tag box checked "Primary photo for this person" else 0
                        FROM       MediaLinkTable ML -- media tags table
                                   INNER JOIN MultiMediaTable MM
                        USING      (MediaID)        -- mediafiles table
                        WHERE      ML.OwnerType = 0 -- person
                        AND        MM.MediaType = 1 -- image type
                        ORDER BY   ML.OwnerID       -- sort by ownerid then
                                 , ML.IsPrimary     -- isprimary, putting any tag for the person set as primary to the bottom of the list
                        )
               GROUP BY OwnerID -- only the last LinkID in the list for the person comes out
               )
       WHERE   NOT IsPrimary -- only the last LinkID for a person with tags, none marked primary
       );

Group – Persons with Text Dates #date #namedgroup

Posted on 2013-09-012024-10-07 by Tom Holden

Creates a Named Group in the open database with the name “SQL: Text Dates” if one does not already exist
Deletes all members from that named group
Populates that named group with persons having an event date that is backgrounded in yellow on the Edit Person screen, i.e., an invalid date format interpreted by RootsMagic as Text

To refresh the named group’s membership list to reflect corrections you have made to the format of event dates, rerun the query to regenerate the RootsMagic group table and then, in RootsMagic, select a different group and then re-select the “SQL: Text Dates” group. You should see that names have disappeared from the group, provided you have corrected all the text dates for the persons you worked on.

Voila! A Dynamic Group! Just a few clicks more than if RootsMagic had it built in.

Group-PersonsWithTextDates-RM10 2024-10-08 for #RM10 & probably suitable for RM8 and RM9

Group-PersonsWithTextDates.sql for all versions RM4-RM7
2013-09-01 superseded PersonsWithTextDates.sql which was flawed and incomplete.
rev 2018-07-07 bug fixed when no other existing group

Description

Google Maps

Download Query

Google Earth

Bing Maps

GeoCommons now ArcGis.com

OpenStreetMaps

Promo

SQLiteSpy User Experience

RMtrix User Experience

Example – SQLiteSpy Quick Integrity Check

Example – Change fact type globally

Discussions & comments from Wikispaces site

SqliteSpy copying to the clipboatd

How to have query accept input from the keyboard.

Why? How?

Main Script

Mismatched FMNO Report

Earlier GEDCOM Solution