-- SrcTmpltsConvert2.sql
-- 2010-02-18 ve3meo
-- rev 2014-06-15 changed to prepended asterisk rather than underscore
--   for names of user-editable copies of builtin templates

-- Replaces SrcTmpltsConvert.sql [DO NOT USE as it may result
--  in problems with future imports of user-defined templates]
-- Converts sources using uneditable, built-in Source templates to 
--  editable copies of the built-in templates, equivalent to user-defined.
--  The new Template Names are prefaced with the asterisk "*" character.
-- Requires an SQLite manager that can spoof the RMNOCASE collation,
--  such as SQLite Developer, where you add a new collation sequence, selecting
--  a suitable Unicode collation, e.g.: en_US(e), and name it RMNOCASE.
-- May require increasing the Max_Page_Count to provide space to accommodate the 
--  transaction.

-- Start by opening the database in RootsMagic and importing into the Source 
-- Templates List the file RootsMagicUser.rmst which contains all 413 built-
-- in templates, modified with a TemplateID >9999 to designate that they are 
-- user-defined and with Names prefaced by '*'. CLOSE the database in RM.

-- Script creates a temporary table to store the TemplateIDs used by the database
-- Sources and the new TemplateID for the matching user-editable copy.

DROP TABLE IF EXISTS tmpTemplateID;
CREATE TEMP TABLE tmpTemplateID AS
    SELECT s.TEMPLATEID AS OLDTEMPLATEID, st2.TEMPLATEID AS NewTemplateID 
     FROM sourcetable s 
     INNER JOIN sourcetemplatetable st 
     USING(TEMPLATEID)
     INNER JOIN sourcetemplatetable st2 
     ON st2.name COLLATE NOCASE = '*'||st.name COLLATE NOCASE
      
;

-- Script creates a set of UPDATE commands to replace the TemplateID in SourceTable
-- with the new TemplateID
SELECT 'BEGIN EXCLUSIVE;' AS Line
UNION ALL

SELECT DISTINCT 'UPDATE sourcetable SET TemplateID='|| NEWTEMPLATEID || 
' WHERE TemplateID=' || OLDTEMPLATEID || ';'
 AS Line
 FROM TMPTEMPLATEID

UNION ALL
SELECT 'COMMIT;' AS Line
;

-- Now change the template pointers in the SourceTable table to
--  point to the new templates.

-- Copy the result set to a new query and run. An example result set 
--  embedded in a transaction:

--BEGIN EXCLUSIVE;
--UPDATE sourcetable SET TemplateID=10017 WHERE TemplateID=10;
--UPDATE sourcetable SET TemplateID=10019 WHERE TemplateID=12;
--UPDATE sourcetable SET TemplateID=10024 WHERE TemplateID=372;
--UPDATE sourcetable SET TemplateID=10163 WHERE TemplateID=72;
--UPDATE sourcetable SET TemplateID=10182 WHERE TemplateID=80;
--UPDATE sourcetable SET TemplateID=10332 WHERE TemplateID=144;
--UPDATE sourcetable SET TemplateID=10409 WHERE TemplateID=347;
--COMMIT;

-- SELECT TemplateID FROM SourceTable WHERE TemplateID>0; --Check that they are all over 9999