--SourceTemplatesHybridFreeFormConvert.sql
/*
2013-12-16 Tom Holden ve3meo
2013-12-17 rev1. no longer converts !MyFreeForm3 templates
           rev2. possible speed up by reducing use of CAST
Marries source templates up with Free Form for the best of both worlds:
a) use the templated part as an input form to help draft a Free Form source
b) paste a citation from Ancestry, FamilySearch et al into the Free Form part to
refer to while populating the template fields.
c) toggle between sentences outputted from either the Free Form fields or the 
templated fields

EXPERIMENTAL
The revised templates have added fields in Free Form style (FF) and a Force FF field
that determines whether the sentences outputted are from the Free Form fields or
the template fields. 

N.B.: the built-in source templates, while converted, will not transfer 
to another database. Because the target database has only the templated fields 
in the template definitions, the Free Form fields will appear to be lost. Running
this utility on the target database will make them reappear with the values in 
each source intact. 

*/

-- backup SourceTemplateTable builtin templates (you could revert to this version
-- after executing the query the first time)

DROP TABLE IF EXISTS xSourceTemplateTableBak
;
CREATE TABLE IF NOT EXISTS xSourceTemplateTableBak AS
SELECT * FROM SourceTemplateTable
;

/*
-- Restore from backup
INSERT OR REPLACE INTO SourceTemplateTable
SELECT * FROM xSourceTemplateTableBAK
*/

DROP TABLE IF EXISTS xTmpUnconvertedTemplates
;
CREATE TEMP TABLE xTmpUnconvertedTemplates
AS
SELECT TemplateID , CAST(FieldDefs AS TEXT) AS FieldDefsTxt
FROM SourceTemplateTable
;
DELETE FROM xTmpUnconvertedTemplates -- delete the already Hybrid templates or !MyFreeForm3 templates 
WHERE
    (
     FieldDefsTxt LIKE '%<FieldName>ForceFF</FieldName>%' -- ForceFF is added by Hybrid
     OR 
     FieldDefsTxt LIKE '%<FieldName>ShortPage</FieldName>%' -- ShortPage is added by !MyFreeForm3 which does not need conversion     
     )
;

-- Convert Source Template FieldDefs to Hybrids
UPDATE SourceTemplateTable
SET FieldDefs =
CAST(
     REPLACE(CAST(FieldDefs AS TEXT), '<Fields>', '<Fields><Field><FieldName>Footnote</FieldName><DisplayName>Footnote (FF)</DisplayName><Type>Text</Type><Hint>footnote sentence from Free Form source, without the source detail</Hint><LongHint/><CitationField>False</CitationField></Field><Field><FieldName>ShortFootnote</FieldName><DisplayName>Short Footnote (FF)</DisplayName><Type>Text</Type><Hint>short footnote sentence from Free Form source sans source detail</Hint><LongHint/><CitationField>False</CitationField></Field><Field><FieldName>Page</FieldName><DisplayName>Page (FF)</DisplayName><Type>Text</Type><Hint>page value from Free Form source, originally the source detail</Hint><LongHint/><CitationField>True</CitationField></Field><Field><FieldName>Bibliography</FieldName><DisplayName>Bibliography (FF)</DisplayName><Type>Text</Type><Hint>bibliography sentence from FF source</Hint><LongHint/><CitationField>False</CitationField></Field><Field><FieldName>ForceFF</FieldName><DisplayName>FORCE FF</DisplayName><Type>Text</Type><Hint>Any value will force Free Form (FF) sentences</Hint><LongHint>If left empty, the sentences will be generated from the template fields below this field. If non-empty, the sentences will come from the Free Form-like fields above this one or designated (FF).</LongHint><CitationField>False</CitationField></Field>')  
     AS BLOB)
WHERE TemplateID IN (SELECT TemplateID FROM xTmpUnconvertedTemplates)
;

/* 
Some templates already had a [Page] field so now they have two and one must go
The only one we can be sure of having a constant pattern is the one we just added
so we delete it.
*/
UPDATE SourceTemplateTable
SET FieldDefs =
CAST(REPLACE(CAST(FieldDefs AS TEXT)
     ,'<Field><FieldName>Page</FieldName><DisplayName>Page (FF)</DisplayName><Type>Text</Type><Hint>page value from Free Form source, originally the source detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
     ,''
     )
     AS BLOB
    )    
WHERE TemplateID
IN(
   SELECT TemplateID
   FROM  xTmpUnconvertedTemplates
   WHERE FieldDefsTxt LIKE '%<DisplayName>Page</DisplayName>%' -- template already having a [Page] field
   ) 
;

/*
Convert Source Template sentence templates to Hybrid
e.g. Footnote: <? [ForceFF]|[Footnote], [Page].|--originalfootnote--|[Footnote], [Page].>>
*/
UPDATE SourceTemplateTable
SET 
Footnote =
REPLACE(Footnote, Footnote, '<? [ForceFF]|[Footnote], [Page].|' || Footnote || '|[Footnote], [Page].>')
, 
ShortFootnote =
REPLACE(ShortFootnote, ShortFootnote, '<? [ForceFF]|[ShortFootnote], [Page].|' || ShortFootnote || '|[ShortFootnote], [Page].>') 
,
Bibliography =
REPLACE(Bibliography, Bibliography, '<? [ForceFF]|[Bibliography].|' || Bibliography || '|[Bibliography].>') 
WHERE TemplateID IN (SELECT TemplateID FROM xTmpUnconvertedTemplates)
;

/*
Done. 
*/