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

Sets Hybrid Free Form Templated Sources to output the Free Form footnote,
if not already so set, using the value "GEDCOM" in the ForceFF field.

The complementary script sets those Hybrid templates with "GEDCOM" in the 
ForceFF field to output the templated sentence, as would be desirable for all
other destinations, such as reports.
*/

DROP TABLE IF EXISTS xTmpHybridSourcesSetFF
;
CREATE TEMP TABLE IF NOT EXISTS xTmpHybridSourcesSetFF
AS
SELECT SourceID, CAST(Fields AS TEXT) AS FieldsTxt 
FROM SourceTable
WHERE CAST(Fields AS TEXT) LIKE '%<Field><Name>ForceFF</Name><Value/></Field>%' -- empty FORCE FF field
AND CAST(Fields AS TEXT) LIKE '%<Field><Name>Footnote</Name><Value>%'  -- non-empty Footnote (FF) field
; 

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

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