-- SrcTmpltsRevert2.sql
-- 2010-02-08 ve3meo
-- Reverts a database that has had SrcTmpltsConvert2.sql run against it
--  to, hopefully, its original state.
-- 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.

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
;
-- create a set of UPDATE commands to replace the TemplateID in SourceTable
-- with the new TemplateID
SELECT DISTINCT 'UPDATE sourcetable SET TemplateID='|| NEWTEMPLATEID || ' WHERE TemplateID=' || OLDTEMPLATEID || ';' FROM TMPTEMPLATEID;

-- 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=10 WHERE TemplateID=10017;
--UPDATE sourcetable SET TemplateID=12 WHERE TemplateID=10019;
--UPDATE sourcetable SET TemplateID=144 WHERE TemplateID=10332;
--UPDATE sourcetable SET TemplateID=347 WHERE TemplateID=10409;
--UPDATE sourcetable SET TemplateID=372 WHERE TemplateID=10024;
--UPDATE sourcetable SET TemplateID=72 WHERE TemplateID=10163;
--UPDATE sourcetable SET TemplateID=80 WHERE TemplateID=10182;
--COMMIT;

-- SELECT TemplateID FROM SourceTable WHERE TemplateID>0; Check that all are <10000

-- SELECT * FROM SourceTemplateTable WHERE TemplateID>9999; Check user templates