-- SourceTemplates-SetUKquotePunctuation.sql
-- 2013-09-29 Tom Holden ve3meo
/* Most of the built-in source templates have the comma or period separating
a quotation or ending the sentence with a quotation inside the marks. This is 
known as 'US' style. 'UK' or 'logical' style places them outside. This query 
attempts to find all such instances and move them to the 'UK' position.

The effective query is a series after the following comment block which contains 
some queries used in reviewing and exploring the existing templates. 

Only the built-in source templates are modified because custom templates can be
edited within RootsMagic and it is possible that such sentences may contain
unanticipated combinations of quotes and punctuation atypical of that seen
in the built-in templates.

*/

/*
-- Review built-in source templates
SELECT TemplateID, Name, Footnote FROM SourceTemplateTable WHERE TemplateID < 10000;
SELECT TemplateID, Name, ShortFootnote FROM SourceTemplateTable WHERE TemplateID < 10000;
SELECT TemplateID, Name, Bibliography FROM SourceTemplateTable WHERE TemplateID < 10000;

-- Footnotes not having US punctuation within quotes
SELECT TemplateID    --, Name, Footnote
FROM SourceTemplateTable
WHERE Footnote REGEXP('"')
AND Footnote NOT REGEXP(',"') 
AND Footnote NOT REGEXP('[/.]"') 
AND Footnote NOT REGEXP('" [/(]')
AND Footnote NOT REGEXP('";')
AND TemplateID < 10000
;

SELECT TemplateID, Name, ShortFootnote
FROM SourceTemplateTable
WHERE ShortFootnote REGEXP('"')
AND ShortFootnote NOT REGEXP(',"') 
AND ShortFootnote NOT REGEXP('[/.]"') 
AND TemplateID < 10000
;

SELECT TemplateID, Name, Bibliography
FROM SourceTemplateTable
WHERE Bibliography REGEXP('"')
AND Bibliography NOT REGEXP(',"')
AND Bibliography NOT REGEXP('[/.]"')
AND TemplateID < 10000
;

-- explore punctuation within quotes
SELECT TemplateID, Name, Footnote, ShortFootnote, Bibliography
FROM SourceTemplateTable
WHERE TemplateID
IN
(
SELECT TemplateID
--       , Footnote
FROM SourceTemplateTable
WHERE SUBSTR(Footnote, INSTR(Footnote,'"')+1) REGEXP('[,/.]"')
AND TemplateID < 10000

UNION

SELECT TemplateID
--       , ShortFootnote
FROM SourceTemplateTable
WHERE SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1) REGEXP('[,/.]"')
AND TemplateID < 10000

UNION

SELECT TemplateID
--       , Bibliography
FROM SourceTemplateTable
WHERE SUBSTR(Bibliography, INSTR(Bibliography,'"')+1) REGEXP('[,/.]"')
AND TemplateID < 10000
)
;
*/

------------- HERE WE GO! ---------------

-- FOOTNOTES
-- Review commas moved out of quotes
SELECT templateid, SUBSTR(Footnote, 1, INSTR(Footnote,'"')) ||
       REPLACE(SUBSTR(Footnote, INSTR(Footnote,'"')+1), ',"', '",')
FROM SourceTemplateTable
WHERE SUBSTR(Footnote, INSTR(Footnote,'"')+1) REGEXP(',"')
AND TemplateID < 10000
;
-- MOVE commas out of quotes 
UPDATE SourceTemplateTable
SET Footnote = SUBSTR(Footnote, 1, INSTR(Footnote,'"')) ||
       REPLACE(SUBSTR(Footnote, INSTR(Footnote,'"')+1), ',"', '",')
WHERE SUBSTR(Footnote, INSTR(Footnote,'"')+1) REGEXP(',"')
AND TemplateID < 10000
;

-- Review Move periods out of quotes
SELECT templateid, SUBSTR(Footnote, 1, INSTR(Footnote,'"')) ||
       REPLACE(SUBSTR(Footnote, INSTR(Footnote,'"')+1), '."', '".')
FROM SourceTemplateTable
WHERE SUBSTR(Footnote, INSTR(Footnote,'"')+1) REGEXP('[/.]"')
AND TemplateID < 10000
;
-- MOVE periods out of quotes
UPDATE SourceTemplateTable
SET Footnote = SUBSTR(Footnote, 1, INSTR(Footnote,'"')) ||
       REPLACE(SUBSTR(Footnote, INSTR(Footnote,'"')+1), '."', '".')
WHERE SUBSTR(Footnote, INSTR(Footnote,'"')+1) REGEXP('[/.]"')
AND TemplateID < 10000
;
----------------------------
--SHORTFOOTNOTES
-- Review commas moved out of quotes
SELECT templateid, SUBSTR(ShortFootnote, 1, INSTR(ShortFootnote,'"')) ||
       REPLACE(SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1), ',"', '",')
FROM SourceTemplateTable
WHERE SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1) REGEXP(',"')
AND TemplateID < 10000
;
-- MOVE commas out of quotes 
UPDATE SourceTemplateTable
SET ShortFootnote = SUBSTR(ShortFootnote, 1, INSTR(ShortFootnote,'"')) ||
       REPLACE(SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1), ',"', '",')
WHERE SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1) REGEXP(',"')
AND TemplateID < 10000
;

-- Review Move periods out of quotes
SELECT templateid, SUBSTR(ShortFootnote, 1, INSTR(ShortFootnote,'"')) ||
       REPLACE(SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1), '."', '".')
FROM SourceTemplateTable
WHERE SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1) REGEXP('[/.]"')
AND TemplateID < 10000
;
-- MOVE periods out of quotes
UPDATE SourceTemplateTable
SET ShortFootnote = SUBSTR(ShortFootnote, 1, INSTR(ShortFootnote,'"')) ||
       REPLACE(SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1), '."', '".')
WHERE SUBSTR(ShortFootnote, INSTR(ShortFootnote,'"')+1) REGEXP('[/.]"')
AND TemplateID < 10000
;
----------------------------
-- BIBLIOGRAPHY
-- Review commas moved out of quotes
SELECT templateid, SUBSTR(Bibliography, 1, INSTR(Bibliography, '"')) ||
       REPLACE(SUBSTR(Bibliography, INSTR(Bibliography, '"')+1), ',"', '",')
FROM SourceTemplateTable
WHERE SUBSTR(Bibliography, INSTR(Bibliography, '"')+1) REGEXP(',"')
AND TemplateID < 10000
;
-- MOVE commas out of quotes 
UPDATE SourceTemplateTable
SET Bibliography = SUBSTR(Bibliography, 1, INSTR(Bibliography, '"')) ||
       REPLACE(SUBSTR(Bibliography, INSTR(Bibliography, '"')+1), ',"', '",')
WHERE SUBSTR(Bibliography, INSTR(Bibliography, '"')+1) REGEXP(',"')
AND TemplateID < 10000
;

-- Review Move periods out of quotes
SELECT templateid, SUBSTR(Bibliography, 1, INSTR(Bibliography, '"')) ||
       REPLACE(SUBSTR(Bibliography, INSTR(Bibliography, '"')+1), '."', '".')
FROM SourceTemplateTable
WHERE SUBSTR(Bibliography, INSTR(Bibliography, '"')+1) REGEXP('."')
AND TemplateID < 10000
;
-- MOVE periods out of quotes
UPDATE SourceTemplateTable
SET Bibliography = SUBSTR(Bibliography, 1, INSTR(Bibliography, '"')) ||
       REPLACE(SUBSTR(Bibliography, INSTR(Bibliography, '"')+1), '."', '".')
WHERE SUBSTR(Bibliography, INSTR(Bibliography, '"')+1) REGEXP('."')
AND TemplateID < 10000
;
----------------------------