-- Places-RecombineFTMfractures.sql
/* 
2012-03-22 Tom Holden ve3meo
Investigation into fractured Place names from FTM2012 where part of the name 
is exported in the Event description (Detail) and the balance in the 
Place name. Typically, these are places from Ancestry Canada Census 
databases with '/' between English/French words, e.g. "west/ouest".

Recombines the fractures and deletes the event description part.

Does not carry Place Details (Site) over to the recombined Place - a revised 
version could by replacing the fractured Name in PlaceTable rather than 
creating a new recombined Place as this script does. Don't know why I didn't 
think of that.

N.B. This is a series of queries intended to be executed one at a time in sequence
although one might go for broke and fire them off as a batch. There would be no chance
to inspect the data.
*/

/*
Find Places with just ')' in name and not the balancing parenthesis, found in the event description, 
as in incorrectly exported places from FTM 2012. 
*/
SELECT PlaceID
	,NAME
FROM PlaceTable
WHERE PlaceType = 0
	AND NAME LIKE '%)%'
	AND NAME NOT LIKE '%(%';

/* Make a Table of EventIDs using the split Place names
*/
DROP TABLE

IF EXISTS xEventPlace;
	CREATE TEMP TABLE

IF NOT EXISTS xEventPlace AS
	SELECT EventID
		,Details
		,Event.PlaceID AS PlaceID
		,Place.NAME AS Place
		,Event.SiteID AS SiteID
		,Site.NAME AS Site
	FROM PlaceTable Place NATURAL
	INNER JOIN EventTable Event
	LEFT JOIN PlaceTable Site ON (Event.SiteID = Site.PlaceID)
		AND Site.PlaceType = 2
	WHERE Place.PlaceID IN (
			SELECT PlaceID
			FROM PlaceTable
			WHERE PlaceType = 0
				AND NAME LIKE '%)%'
				AND NAME NOT LIKE '%(%'
			)
		AND Event.Details LIKE '%(%';

-- Generate recombined Place names in PlaceTable
INSERT INTO PlaceTable
SELECT DISTINCT NULL AS PlaceID
	,0 AS PlaceType
	,Details || '/' || Place AS NAME
	,'' AS Abbrev
	,'' AS Normalized
	,0 AS Latitude
	,0 AS Longitude
	,0 AS LatLongExact
	,0 AS MasterID
	,'Generated by SQLite query from fractured FTM 2012 export: ' || Details || '/' || Place
FROM xEventPlace;

-- Revise events to point to recombined Places
UPDATE EventTable
SET PlaceID = (
		SELECT PlaceTable.PlaceID
		FROM PlaceTable
			,xEventPlace
		WHERE PlaceType = 0
			AND PlaceTable.NAME LIKE EventTable.Details || '/' || xEventPlace.Place
			AND EventTable.EventID = xEventPlace.EventID
		)
WHERE EventID IN (
		SELECT EventID
		FROM xEventPlace
		ORDER BY EventID ASC
		);

-- Verify event places
SELECT EventID
	,xEventPlace.Details
	,xEventPlace.Place
	,PlaceTable.NAME
FROM xEventPlace
LEFT JOIN EventTable USING (EventID)
LEFT JOIN PlaceTable ON (EventTable.PlaceID = PlaceTable.PlaceID)

-- Erase event descriptions
UPDATE EventTable
SET Details = ''
WHERE EventID IN (
		SELECT EventID
		FROM xEventPlace
		ORDER BY EventID ASC
		);
	-- All done