-- SourceTemplates-FieldCount.sql
-- 2013-04-08 Tom Holden ve3meo

-- works with RM8 PJ 2021-03-07
/*
Converts the FieldDefs blob from SourceTemplateTable to a 1-line string and
counts the number of occurrences of "<FieldName>" in it (the number of fields)
and the subset of same that are Source Detail fields "<CitationField>True".
Does so by measuring the lengths of the FieldDefsTxt strings with and without
the search string and dividing the difference by the length of the search string.
*/

CREATE TEMP TABLE IF NOT EXISTS xSrcTpltFldDefs AS
	SELECT TemplateID
		,NAME
		,REPLACE(REPLACE(CAST(FieldDefs AS TEXT), X'0A', ''), X'0D', '') AS FieldDefsTxt
	FROM SourceTemplateTable;

SELECT TemplateID
	,NAME
	,(LENGTH(FieldDefsTxt) - LENGTH(REPLACE(FieldDefsTxt, '<FieldName>', ''))) / LENGTH('<FieldName>') AS TotalFields
	,(LENGTH(FieldDefsTxt) - LENGTH(REPLACE(FieldDefsTxt, '<CitationField>True', ''))) / LENGTH('<CitationField>True') AS SrcDtlFields
	,FieldDefsTxt
FROM xSrcTpltFldDefs;