-- Sources-HybridFF-UnSetFF.sql
/*
2013-12-17 Tom Holden ve3meo

Sets Hybrid Free Form Templated Sources to output the templated or non-Free Form
sentences, if set to output Free Form by the value "GEDCOM" in the ForceFF field,
as would be desirable for all other destinations, such as reports.

The complementary script sets those Hybrid templates with an empty ForceFF field 
to output the Free Form sentence by adding setting the ForceFF field to "GEDCOM". 
*/

DROP TABLE IF EXISTS xTmpHybridSourcesUnSetFF
;
CREATE TEMP TABLE IF NOT EXISTS xTmpHybridSourcesUnSetFF
AS
SELECT SourceID, CAST(Fields AS TEXT) AS FieldsTxt 
FROM SourceTable
WHERE CAST(Fields AS TEXT) LIKE '%<Field><Name>ForceFF</Name><Value>GEDCOM</Value></Field>%'
; 

UPDATE OR REPLACE xTmpHybridSourcesUnSetFF
SET FieldsTxt = 
REPLACE (
         FieldsTxt
         , '<Field><Name>ForceFF</Name><Value>GEDCOM</Value></Field>'         
         , '<Field><Name>ForceFF</Name><Value/></Field>'
         )         
;

UPDATE OR REPLACE SourceTable
SET Fields =
(
SELECT CAST(FieldsTxt AS BLOB) FROM xTmpHybridSourcesUnSetFF
 WHERE SourceTable.SourceID = xTmpHybridSourcesUnSetFF.SourceID 
)
WHERE SourceID IN (SELECT SourceID FROM xTmpHybridSourcesUnSetFF)
;