-- Citations_TMGimport_splitCDtoCD1-2.sql
/*
2014-09-21 Tom Holden ve3meo

RootsMagic 6.3.2 imports TMG Citation Detail memo field into CitationTable.Fields
as the field [CD] defined in the SourceTemplateTable.Fields regardless of TMG
memo splitter "||". The sentence templates imported do not have the [CD] variable,
if the Citation Detail was split, rather they have variables [CD1], [CD2], up to 9.
Consequently, there is no output of the value from CD. If the sentence templates 
were changed to use [CD] then the whole value including the "||" separator is 
output, to the detriment of the order of the sentence and to the length of the
short footnote.

This query addresses the most common case where the Citation Detail memo is split
into two parts. It splits and replaces [CD] in CitationTable.Fields into the two 
parts [CD1] and [CD2]. It splits and replaces Citation Detail [CD] in the fields 
definition in SourceTemplateTable.Fields for the source templates used by these 
citations.

It may be possible to generalise this query to handle a greater number of splits
by using (tricky) recursive techniques within SQLite.  
*/

/* 
xCitFields will store temporarily the CitationID, text form of the 
CitationTable.Fields, and the TemplateID of the source template used
*/
DROP VIEW IF EXISTS xCitFields
;
CREATE TEMP VIEW xCitFields
AS
SELECT 
  C.CitationID
  , CAST(C.Fields AS TEXT) AS CitFieldsText
  , ST.TemplateID
--  , ST.Footnote
FROM CitationTable C
INNER JOIN SourceTable S USING(SourceID)              -- connect citation to master source in order to
INNER JOIN SourceTemplateTable ST USING(TemplateID)   -- connect the citation to the source template
WHERE CitFieldsText                               -- Source details values
LIKE '%<Name>CD</Name><Value>%||%</Value>%'       -- Citation must have [CD] field with || in it
AND FootNote REGEXP '[^\[]*\[Cd[12]{1}\].*'       -- footnote sentence template must have Cd1 and Cd2
AND Footnote NOT REGEXP '[^\[]*\[Cd[3-9]{1}\].*'  -- but not have Cd3 or greater
;

-- revise the Citations to replace [CD] with its value split between [CD1] and [CD2]
UPDATE CitationTable
SET Fields =
CAST(
 (
  SELECT 
  REPLACE
   (
    REPLACE(CitFieldsText, '<Name>CD</Name><Value>', '<Name>CD1</Name><Value>')
    , '||'
    , '</Value></Field><Field><Name>CD2</Name><Value>' 
    )
  FROM xCitFields
  WHERE CitationID = xCitFields.CitationID
 ) AS BLOB
) -- end Cast
WHERE CitationID IN (SELECT CitationID FROM xCitFields)
;

/* 
Revise the SourceTemplateTable.FieldDefs for the templates used
Create a temporary View of the Field Defs in plain text
*/
DROP VIEW IF EXISTS xSrcTpltFieldDefsTxt
;

CREATE TEMP VIEW xSrcTpltFieldDefsTxt
AS
SELECT TemplateID, CAST(FieldDefs AS TEXT) AS FieldDefsText
FROM SourceTemplateTable 
WHERE TemplateID
IN (SELECT DISTINCT TemplateID FROM xCitFields)
;

UPDATE SourceTemplateTable
SET FieldDefs =
CAST
 (
  (
   SELECT
     REPLACE
     (FieldDefsText
      , '<Field><FieldName>CD</FieldName><DisplayName>Citation Detail</DisplayName><Type>Text</Type><Hint/><LongHint/><CitationField>True</CitationField></Field>'
      , '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field><Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
      )
   FROM xSrcTpltFieldDefsTxt AS x
   WHERE TemplateID = x.TemplateID
   )
   AS BLOB
  ) -- end Cast
WHERE TemplateID IN (SELECT DISTINCT TemplateID FROM xSrcTpltFieldDefsTxt)
;