-- CheckTMGimportforCodes.sql
-- 2014-09-12 Tom Holden ve3meo
/*
2014-09-14 rev changed from one very long reg exp to series of shorter ones
with tighter control over results and expanded to include opening format codes,
[CIT:]pair and [L=language]
2014-09-21 rev to add Memo splitter "||"
2014-09-25 rev to include Event custom sentences; exclude blank values for speedup

 
Uses a complex regular expression to find, in the most likely places
in a RootsMagic file after importing from a TMG database, fields having TMG-like codes.
There will be false positives where the square braces have been used for text other than 
a TMG Name tag variable, a TMG Tag Sentence variable or a TMG Format code or where RootsMagic
codes match the expression. Source Template sentences may be the main source of false 
positives; inspection of the value is necessary to resolve. 

TMG codes are enclosed in square braces and contain only capital letters and numerals and 
the colon character.
*/

SELECT * FROM
(
------------------AddressTable

SELECT CAST(Note AS TEXT) AS "Value", 'Note' AS "Field", 'AddressTable' AS "Table", ROWID FROM AddressTable
WHERE "Value" NOT LIKE ''
UNION ALL
------------------ChildTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'ChildTable', ROWID FROM ChildTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
-------------------CitationTable

SELECT CAST(Comments AS TEXT) AS CommentsTxt, 'Comments', 'CitationTable', ROWID FROM CitationTable
WHERE CommentsTxt NOT LIKE ''
UNION ALL

SELECT CAST(ActualText AS TEXT) AS ActualTextTxt, 'ActualText', 'CitationTable', ROWID FROM CitationTable
WHERE ActualTextTxt NOT LIKE ''
UNION ALL

SELECT CAST(Fields AS TEXT) AS FieldsTxt, 'Fields', 'CitationTable', ROWID FROM CitationTable
WHERE FieldsTxt NOT LIKE ''
UNION ALL
------------------EventTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'EventTable', ROWID FROM EventTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'EventTable', ROWID FROM EventTable
WHERE SentenceTxt NOT LIKE ''    -- custom ones only
UNION ALL

-------------------FactTypeTable

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'FactTypeTable', ROWID FROM FactTypeTable
WHERE SentenceTxt NOT LIKE ''
AND FactTypeID > 999    -- custom ones only
UNION ALL
--------------------FamilyTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'FamilyTable', ROWID FROM FamilyTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
------------------LinkTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'LinkTable', ROWID FROM LinkTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
------------------MediaLinkTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'MediaLinkTable', ROWID FROM MediaLinkTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Caption AS TEXT) AS CaptionTxt, 'Caption', 'MediaLinkTable', ROWID FROM MediaLinkTable
WHERE CaptionTxt NOT LIKE ''
UNION ALL

SELECT CAST(Description AS TEXT) AS DescriptionTxt, 'Description', 'MediaLinkTable', ROWID FROM MediaLinkTable
WHERE DescriptionTxt NOT LIKE ''
UNION ALL
----------------------MultiMediaTable

SELECT CAST(Caption AS TEXT) AS CaptionTxt, 'Caption', 'MultiMediaTable', ROWID FROM MultiMediaTable
WHERE CaptionTxt NOT LIKE ''
UNION ALL

SELECT CAST(Description AS TEXT) AS DescriptionTxt, 'Description', 'MultiMediaTable', ROWID FROM MultiMediaTable
WHERE DescriptionTxt NOT LIKE ''
UNION ALL
--------------------NameTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'NameTable', ROWID FROM NameTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'NameTable', ROWID FROM NameTable
WHERE SentenceTxt NOT LIKE ''
UNION ALL
---------------------PersonTable

SELECT CAST(Note AS TEXT) NoteTxt, 'Note', 'PersonTable', ROWID FROM PersonTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
---------------------PlaceTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'PlaceTable', ROWID FROM PlaceTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
---------------------ResearchTable

SELECT CAST(Details AS TEXT) AS DetailsTxt, 'Details', 'ResearchTable', ROWID FROM ResearchTable
WHERE DetailsTxt NOT LIKE ''
UNION ALL
-------------------RoleTable

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'RoleTable', ROWID FROM RoleTable
WHERE SentenceTxt NOT LIKE ''
AND RoleID > 58    -- custom ones only

UNION ALL
---------------------SourceTable--------------------

SELECT CAST(ActualText AS TEXT) AS ActualTextTxt, 'ActualText', 'SourceTable', ROWID FROM SourceTable
WHERE ActualTextTxt NOT LIKE ''
UNION ALL

SELECT CAST(Comments AS TEXT) AS CommentsTxt, 'Comments', 'SourceTable', ROWID FROM SourceTable
WHERE CommentsTxt NOT LIKE ''
UNION ALL

SELECT CAST(Fields AS TEXT) AS FieldsTxt, 'Fields', 'SourceTable', ROWID FROM SourceTable
WHERE FieldsTxt NOT LIKE ''
UNION ALL
------------------SourceTemplateTable
SELECT Footnote, 'Footnote', 'SourceTemplateTable', ROWID FROM SourceTemplateTable
WHERE TemplateID > 9999    -- custom ones only
UNION ALL
SELECT ShortFootnote, 'ShortFootnote', 'SourceTemplateTable', ROWID FROM SourceTemplateTable
WHERE TemplateID > 9999    -- custom ones only
UNION ALL
SELECT Bibliography, 'Bibliography', 'SourceTemplateTable', ROWID FROM SourceTemplateTable
WHERE TemplateID > 9999    -- custom ones only
UNION ALL
---------------------URLTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'URLTable', ROWID FROM URLTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
-------------------WitnessTable

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'WitnessTable', ROWID FROM WitnessTable
WHERE SentenceTxt NOT LIKE ''
UNION ALL

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'WitnessTable', ROWID FROM WitnessTable
WHERE NoteTxt NOT LIKE ''
)
/*WHERE 
Value REGEXP 
'\[[:\+ACDFLMOPRSTWYZ]{1}[\+\d:\]ABCDEFGHILMNOPSTZ]{0,1}[:\d\]ACDEFGIJLMNOPRSTU]{1}[12:\]ABDHLMNOPRSTYZ]{0,1}[\]EHILOT]{0,1}[\]BDEILMNPRSTXY]{0,1}[\]GSY]{0,1}[\]E]{0,3}'
-- regular expression derived from the different characters used in each position from [ to ].
*/

-- Perhaps a more easily understood and purer one would be a series of simpler ones by length and OR'd.
WHERE
Value REGEXP '\[[\+ADLPSWY]\]' 			-- len=3
OR
Value REGEXP '\[[ADLMPSW][\+\dADEFGLMOPSTZ]\]' 			-- len=4
OR
Value REGEXP '\[[ALOPSWZ][12ABCFGILMOP][\dEFGIJLNPRS]\]' 			-- len=5
OR
Value REGEXP '\[[:CFHMPSU][12ACHINOPSU][ABDFGILNPRTU][12:BDHOPRSTY]\]' 			-- len=6
OR
Value REGEXP '\[[:BFHIMPS][ABCFHIMOST][ACILMOTZ][ADEHLMNPTZ][:DEHLOP]\]' 			-- len=7
OR
Value REGEXP '\[[:CDFIT][CEFINO][DLMNOTU][ADELNOPT][BDGEILMOPRSTX][:BDEGILMPRSTXY]\]' 			-- len=8
OR
Value REGEXP '\[[CL][AO][TU][LN][OT][NR][GY]\]' 			-- len=9
OR
Value REGEXP '\[ADDRESSEE\]'                         -- [ADDRESSEE]
OR
Value REGEXP '\[R[AEFGLMPS]{0,1}\:[^\[]+\]'          -- [R:rolename] or [Rx:rolename]
OR
Value REGEXP '\[L=[^\[]+\]'                       -- [L=...] Language
OR
Value LIKE '%||%'                          -- Memo splitter "||"

;
----------------END OF SCRIPT-----------------------