Source Templates – Field Count #sourcetemplates

Update 2021-03-10: tested with #RM8 and minor rev for wider compatibility with SQLite managers (Pat Jones)

The complexity of a Source Template is somewhat proportional to the number of fields and their distribution between the Master Source and Source Details (citation-specific). It is probable that a source with a greater number of fields in total AND a greater number of them in Source Details will be more complex than one that has lesser numbers and will suffer more distortion and loss when exported to standard GEDCOM (see Source Templates, A Comparative Example.pdf). There are other factors that complicate the fidelity of export which could well result in some exceptions but they are harder to measure. These include:

• fields in the RM Footnote sentence template out of order from the simple convention of Master fields first followed by Source Detail fields
• words or strings that are within a sentence template switch that are conditional on a non-empty Source Detail field

This query does the easy task of counting the total number of fields and the number of those that are Source Detail fields.

Screenshot from SQLiteSpy showing results from some of the built-in Source Templates. You can sort by any column by clicking on it.

SourceTemplates-FieldCount.sql

SourceTemplates-FieldCount.sql

-- 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.
*/
DROP TABLE IF EXISTS xSrcTpltFldDefs;

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;