Source Templates – Revision Utilities #sourcetemplates

RootsMagic invited users to submit any issues they are having with the built in source templates as of version 6.3.0.3 in the Forum thread Sentence Templates To Be Fixed on 20 Sep 2013. Extraneous punctuation and text when one or more fields is left empty was identified as an issue and one that the RootsMagician intends to fix, at least for those templates so used more than rarely. It’s not clear what other issues might be addressed but new/modified functionality was ruled out from this round.

In an attempt to identify the scope of the extraneous characters problem, a series of queries were developed that readily show the outputs for empty citations of empty sources for each of the built-in source templates. The result lends itself to editing the templates and may be helpful in identifying and resolving other issues.

Empty citations on copies of built-in source templates showing extraneous punctuation and text from one and its resulting report endnote.

SourceTemplateRevisionUtilities.sql rev 2013-09-25

-- SourceTemplateRevisionUtilities.sql
/* 2013-09-24 ve3meo
2013-09-25 added ripple update of the name of the source template through to the citation comment
 
This series of queries is intended to facilitate review and editing of
copies of the builtin source templates to remove extraneous punctuation
and text when fields are left empty. The series assumes a clean database
with one person RIN=1 to whom an empty citation using each source template
will be linked. The series:
1. Creates a copy of each builtin source template which can then be edited in RM, name prefixed with *
2. Creates an empty source for each copy of the builtin source templates, source name = template name
3. Creates an empty citation of each source linked to a person whose RIN is 1.
4. Ripples the name of the source template through to citation comment for use in end notes.
 
The database can be opened by RM on the Citation Manager for Person 1. The names of the sources
are ordered alphabetically to match the order in the Source Template List. Selecting a citation
shows the three sentences resulting from the empty source citation. Using an external SQLite manager,
the SourceTemplateTable sentence templates for Footnote, Short Footnote and Bibliography can be edited,
thus saving much drilling up and down in RM. Simply selecting another citation in RM and returning
refreshes the sentence generation to catch up to the changes in the template.
 
Of course, there may other ways of working effectively, such as two instances of RootsMagic (resident and portable)
open on the common database, one in the Citation Manager, the other in the Source Templates window.
*/
 
-- Create a copy of each builtin source template
INSERT INTO SourceTemplateTable
SELECT TemplateID + 10000
    ,'*' || NAME
    ,Description
    ,Favorite
    ,Category
    ,Footnote
    ,ShortFootnote
    ,Bibliography
    ,FieldDefs
FROM SourceTemplateTable
WHERE TemplateID < 10000;
 
-- Create an empty source for each copy of the builtin source templates
INSERT INTO SourceTable
SELECT NULL AS SourceID
    ,NAME
    ,'' AS RefNumber
    ,'' AS ActualText
    ,'' AS Comments
    ,0 AS IsPrimary
    ,TemplateID
    ,(
        SELECT CAST('<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
        ) AS FIELDS
FROM SourceTemplateTable ST
WHERE ST.TemplateID > 10000;
 
-- Create an empty citation for each empty source based on copies of the builtin source templates
-- all these citations are to the person with RIN=1
INSERT INTO CitationTable
SELECT NULL AS CitationID
    ,0 AS OwnerType
    ,SourceID
    ,1 AS OwnerID
    ,'[[user:ve3meo]]' AS Quality
    ,0 AS IsPrimary
    ,'' AS Comments
    ,'' AS ActualText
    ,'' AS RefNumber
    ,0 AS Flags
    ,(
        SELECT CAST('<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
        ) AS FIELDS
FROM SourceTable S
WHERE S.TemplateID > 10000
ORDER BY S.NAME;
 
/* Ripple SourceTemplate name through to Citation comments
 via Source comments for listing in end notes. Having the name
 in Source comments may also be convenient.
*/
UPDATE SourceTable
SET Comments = (
        SELECT ST.NAME
        FROM SourceTemplateTable ST
        WHERE SourceTable.TemplateID = ST.TemplateID
        );
 
UPDATE CitationTable
SET Comments = CAST(x '0A' AS TEXT) || '{' || (
        SELECT NAME
        FROM SourceTable S
        WHERE CitationTable.SourceID = S.SourceID
        ) || '}';