Logo Home    Page list

Source Templates - Field Count #sourcetemplates

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:
This query does the easy task of counting the total number of fields and the number of those that are Source Detail fields.

SourceTemplates-FieldCount.PNG
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
-- 2013-04-08 Tom Holden ve3meo
/*
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;
www.000webhost.com