-- Sources-UnifyAliases_RepositoryLoc.sql
/*
2014-12-13 Tom Holden ve3meo

Replaces fields [RepositoryAddress], [RepositoryInfo] with [RepositoryLoc] 
to harmonize with standard built-in templates. 
Other aliases for [RepositoryLoc] can be added to the script for replacement.
[Repository] is common to all.
[RepositoryReference] is common to many built-in templates
*/
BEGIN TRANSACTION
;
DROP TABLE IF EXISTS TemplatesWithRepositoryLocAlias
;
-- temp table with structure identical to SourceTemplateTable
CREATE TEMP TABLE TemplatesWithRepositoryLocAlias (
  TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB
);

INSERT INTO TemplatesWithRepositoryLocAlias
SELECT * FROM SourceTemplateTable 
WHERE FieldDefs LIKE '%<FieldName>RepositoryAddress</FieldName>%'
OR    FieldDefs LIKE '%<FieldName>RepositoryInfo</FieldName>%'
-- OR other aliases
;
--COMMIT TRANSACTION
;

-- replace aliases for RepositoryLoc in temp Template table
UPDATE TemplatesWithRepositoryLocAlias
SET 
-- RepositoryAddress
  Footnote = REPLACE(Footnote, '[RepositoryAddress]', '[RepositoryLoc]')  
  , ShortFootnote = REPLACE(ShortFootnote, '[RepositoryAddress]', '[RepositoryLoc]')
  , Bibliography = REPLACE(Bibliography, '[RepositoryAddress]', '[RepositoryLoc]')
  , FieldDefs = CAST(REPLACE(FieldDefs, '<FieldName>RepositoryAddress</FieldName>', '<FieldName>RepositoryLoc</FieldName>') AS BLOB)
;
UPDATE TemplatesWithRepositoryLocAlias
SET 
-- RepositoryInfo
  Footnote = REPLACE(Footnote, '[RepositoryInfo]', '[RepositoryLoc]')  
  , ShortFootnote = REPLACE(ShortFootnote, '[RepositoryInfo]', '[RepositoryLoc]')
  , Bibliography = REPLACE(Bibliography, '[RepositoryInfo]', '[RepositoryLoc]')
  , FieldDefs = CAST(REPLACE(FieldDefs, '<FieldName>RepositoryInfo</FieldName>', '<FieldName>RepositoryLoc</FieldName>') AS BLOB)
;
-- Insert UPDATE REPLACEs for any other aliases of RepositoryLoc

-- Hold that thought and revise the Sources using these templates first

DROP TABLE IF EXISTS SourcesWithRepositoryLocAlias
;
-- temp table with structure identical to SourceTable
CREATE TEMP TABLE SourcesWithRepositoryLocAlias (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB )
;
--BEGIN TRANSACTION
;
INSERT INTO SourcesWithRepositoryLocAlias
SELECT * FROM SourceTable 
WHERE TemplateID IN (SELECT TemplateID FROM TemplatesWithRepositoryLocAlias)
;

-- replace aliases for RepositoryLoc in temp Source table
UPDATE SourcesWithRepositoryLocAlias
SET Fields = CAST(REPLACE(Fields, '<Name>RepositoryAddress</Name>', '<Name>RepositoryLoc</Name>') AS BLOB)
;
UPDATE SourcesWithRepositoryLocAlias
SET Fields = CAST(REPLACE(Fields, '<Name>RepositoryInfo</Name>', '<Name>RepositoryLoc</Name>') AS BLOB)
; 
-- Insert UPDATE REPLACEs for any other aliases of RepositoryLoc
--COMMIT TRANSACTION
;

--BEGIN TRANSACTION
;
-- Now replace the records in SourceTable with the revised ones from the temp table
INSERT OR REPLACE INTO SourceTable
SELECT * FROM SourcesWithRepositoryLocAlias
;
--COMMIT TRANSACTION
;

--BEGIN TRANSACTION
;
-- and replace the records in SoureTemplateTable with the revised ones from the temp table
INSERT OR REPLACE INTO SourceTemplateTable
SELECT * FROM TemplatesWithRepositoryLocAlias
;
COMMIT TRANSACTION
;

/*
That's it. The database source templates and associated sources should now have 
the field [RepositoryLoc] instead of the other aliases
*/