-- TreeShare-DisconnectButNotCitations.sql
/*
2017-07-21 Tom Holden ve3meo
2017-07-21 rev to correct the Delete statement if multiple AMTrees

Disconnects a TreeShared database from its Ancestry Member Tree but preserves 
citation links for direct "Ancestry Sources" when uploaded to a new Ancestry Tree, 
instead of converting them to "Other Sources".

The database connection is established by the first person (LinkType=0) in 
the LinkAncestryTable whose extID contains a value of the form:
   nnnnnnnnnnnn:nnnn:nnnnnnnnn
The last number appears to be the Ancestry Member Tree number. There may be, by 
accident, a different AMTNO against another person but it is the first that
determines which tree is connected. And more than one LinkType may have the same 
AMTNO suggestive that these are records stored in the Tree. Citation links have a 
different format and look to point to various Ancestry Source databases. 

So this script finds all the different AMTNOs used in the LinkAncestryTable and 
deletes all records having one of those AMTNOs in their extID. When uploaded to 
a new AMT, a new AMTNO will be assigned and the links for persons et al will be 
established anew but the citation links will be as before. 
*/

-- Get all the different AMTNOs into a view - ideally should be only one, else database has some issue
DROP VIEW IF EXISTS AMTNO
;

CREATE TEMP VIEW AMTNO AS
SELECT DISTINCT SUBSTR(SUBSTR(extID,INSTR(extID,':')+1),INSTR(SUBSTR(extID,INSTR(extID,':')+1),':')+1) AS AMTNO
FROM LinkAncestryTable
WHERE LinkType=0 --person
;

-- This disconnects the database by deleting the person links and all others that point
-- to the person's Ancestry Tree but keeps the citation links and maybe others yet unknown
DELETE FROM LinkAncestryTable
WHERE SUBSTR(SUBSTR(extID,INSTR(extID,':')+1),INSTR(SUBSTR(extID,INSTR(extID,':')+1),':')+1) 
IN (SELECT AMTNO FROM AMTNO)
;

SELECT '
DATABASE HAS BEEN DISCONNECTED!

You may proceed to upload the database to a new Ancestry Tree.'
AS Status
;
-- End of Script --