Places+to+Place+Details+Conversion

toc Splitting Places into Place and Place Details is an onerous job in RootsMagic: you have to edit every fact/event that used the original Place. Until RootsMagic comes up with a utility to make it easier to do, we need a better way. With some judicious SQLite queries and editing of RM's Place List, there is a faster, better way. The opposite direction is much easier.

=Split Place into Place and Place Detail= Here's an example of how SQLite can carry out the bulk conversion of Places having too detailed data to Place Details of another Master Place, e.g.: Place: Mount Pleasant Cemetery, London, Ont. to Place: London, Middlesex Co., Ontario, Canada Place Detail: Mount Pleasant Cemetery and accordingly revise the 5, 50, 500 facts/events that used the original Place or any other Place having "London, Ont" in its name.

The example uses a series of SQLite queries run one at a time in sequence, but, first, make sure that you have your desired master Place in your Place List, e.g., "London, Middlesex Co., Ontario, Canada" and MAKE A BACKUP: code format="sql" -- Pick out the PlaceID of the desired Master Place, i.e., "London, Middlesex Co., Ontario, Canada", -- from the results of this query

SELECT * FROM PlaceTable WHERE Name LIKE 'London,%';

-- This will be the MasterID value for the UPDATE - in this example PlaceID=519 code code format="sql" -- Establish a suitable WHERE constraint that finds all the Places that need to be converted to Place Detail -- The following worked well. Note the underscore character is a wildcard for any one character and -- thus precludes a name beginning "London..." from the results. -- The percent character is a wild card for any number of characters.

SELECT * FROM PlaceTable WHERE Name LIKE '_%London, Ont%' AND PlaceType=0;

-- PlaceType of 0=Place, 2=Place Detail (1=Temple) code code format="sql" -- Use that WHERE constraint in the following -- Convert Places in PlaceTable to Place Details of the Master Place (PlaceID=519 in this example)

UPDATE PlaceTable SET PlaceType=2, MasterID=519 WHERE Name LIKE '_%London, Ont%' AND PlaceType=0; code SQLite manager reports 4 records updated. code format="sql" -- Convert Place and Place Detail cited in the EventTable

UPDATE EventTable SET SiteID=PlaceID, PlaceID=519 WHERE PlaceID IN ( SELECT PlaceID FROM PlaceTable WHERE Name LIKE '_%London, Ont%' AND PlaceType=2 ) AND SiteID=0; code SQLite manager reports 5 records updated, i.e., five events used at least some of the four Places previously converted to Place Details and now use the master Place and the corresponding converted Place Details.

Now look up the Master Place (London, Ontario, Canada) in the RM4 Place List to confirm that the former Places are now its Place Details.

Editing the Place Detail name in RootsMagic 4 rather than in SQLite is necessary because of the proprietary RMNOCASE collation sequence. It is possible to edit the Place Detail name of a RootsMagic 5 database with SQLiteSpy + the fake RMNOCASE extension and then use RM5's File > Database tools > Rebuild indexes function to correct probable index errors.
 * 1) Optionally merge variants of the same place with the target master Place.
 * 2) Print > Place List > all events in a single place (our "London, Middlesex..." master), check the box "Print place details".
 * 3) Inspect the report for unused Place Details and delete them at your discretion.
 * 4) You may have variants of the same Place Detail that you want to unify; can be done with further queries but for a few, just go to Edit Person and revise the fact. Reiterate from step 2.
 * 5) Edit each remaining Place Detail to remove the now redundant Master Place info (i.e.", London, Ont...")

=Convert Place + Place Detail to Place= This is a much easier, global procedure but needs a SQLite manager that supports a fake RMNOCASE collation, such as SQLiteSpy with the fake RMNOCASE extension (see RMNOCASE - faking it in SQLiteSpy) or SQLite Expert (see RMNOCASE - faking it in SQLite Expert...).

Download this file and load it into SQLiteSpy, having opened your database with it first. Of course, you've made a backup! Run the script. The results should be a series of UPDATE statements. Copy these results into a new SQL page in the SQL editor, delete the first line which has the column heading 'Statement' and execute all the statements. Place Details are now Places.

You may think you are finished, but not quite. In RM5, do File > Database > Integrity Check. If you see errors reported, then choose Reindex from the same menu and that should clear them. Unfortunately for RM4 users, there is no equivalent tool so you are at greater risk of some form of index corruption due to the fake collation.

code format="sql" /* Place_Details-Convert_to_Place.sql Converts Place Details + Places into Places; preserves apostrophes through some tricky substitutions but other characters may be problematic. 2012-01-22 ve3meo

Three steps: 1. Update EventTable SET PlaceID=SiteID, SiteID=0 WHERE SiteID>0 2. Auto generate a statement for each Place Detail of the form Update PlaceTable SET Name = SiteName, PlaceName, PlaceType = 0, MasterID = 0 3. Manually copy the resulting series of Update Statements to a SQLite editor and run them -- Step 1 Open database and run this script UPDATE EventTable SET PlaceID=SiteID, SiteID=0 WHERE SiteID>0 -- Step 2 automatically generates the UPDATE statements for each Place Detail SELECT 'UPDATE PlaceTable SET Name = ''' || REPLACE(Site.Name || ', ' || Place.Name, ', ) || '      || ', PlaceType = 0, MasterID = 0 WHERE PlaceID = ' || Site.PlaceID || ';' AS Statement FROM PlaceTable AS Site INNER JOIN PlaceTable AS Place ON Site.MasterID = Place.PlaceID -- Step 3 Copy the resulting UPDATE statements to your SQLite Editor and run them against the database code