Source Templates – Merge Duplicates #sourcetemplates #sources #merge

2022-03-24 test with #RM8 successful.

RootsMagic has no Merge Source Templates function and it really needs one. When you copy multiple times from one database to another and there are citations involved that use the same custom source template, each time that source template is brought over it is treated as a new source template and the associated source is, too. So now you have multiple identical source templates with no tool to merge them. And you have multiple identical master sources which cannot be merged by AutoMerge because they use different source template records. You can merge them manually but that is painfully tedious given that the identical properties of the source templates should make it possible to automate it.

This script automatically merges custom source templates that have matching (case and space character insensitive 2016-04-22):

• footnote sentence templates
• field definitions

Variances in name, the sentence templates for Short Footnote and Bibliography and other fields are ignored which means that source templates that are not quite exact duplicates in all respects will be merged. Unfortunately, it is still sensitive to differences in Hints and Long Hints which are not critical to the merging of source templates.

The first source template (lowest TemplateID) in a set of duplicates will be the last one standing. All sources that once used the duplicates will now use it.

The script does leave a trail of two custom tables that could be cleared out with the addition of DROP TABLE statements added at the end or run separately, identical to the two already in the script.

SourceTemplates-MergeDuplicates.sql Rev 2013-08-07 to correct bug.
Rev 2016-04-22 to make tolerant of differences in case and spacing.

-- SourceTemplates-MergeDuplicates.sql
/*
2013-07-30 Tom Holden ve3meo
2013-08-07 corrected error in Update SourceTable which obtained only the first TemplateID from xLookupSourceTemplateTable
2016-04-22 now tolerates differences in case and space characters in Footnote and FieldDefs
 
Merges custom source templates that have identical
Footnote sentence templates and field definitions.
Ignores differences in Short Footnotes, Bibliography and other fields.
Makes the lowest TemplateID of a set of duplicates the master.
 
It is still sensitive to differences in hints in what would be otherwise identical field definitions that could be merged.
- needs parsing to clear that out.
*/
 
--Create a table of the master custom source templates having duplicate(s)
DROP TABLE IF EXISTS xDupSourceTemplateTable
;
 
CREATE TABLE IF NOT EXISTS xDupSourceTemplateTable
AS
SELECT
   TemplateID
  , Name
  , Description
  , Favorite
  , Category
  , FootnoteCore
  , ShortFootnote
  , Bibliography
  , FieldDefsCore
FROM
(
SELECT COUNT()-1 AS Dupes,
   TemplateID
  , Name
  , Description
  , Favorite
  , Category
  , REPLACE(LOWER(Footnote),' ','') AS FootnoteCore
  , ShortFootnote
  , Bibliography
  , REPLACE(LOWER(FieldDefs),' ','') AS FieldDefsCore
FROM
(SELECT * FROM SourceTemplateTable WHERE TemplateID > 999 ORDER BY TemplateID DESC)
GROUP BY FootnoteCore, FieldDefsCore
)
WHERE Dupes > 0
;
 
-- Create table of matching custom source templates
DROP TABLE IF EXISTS xLookupSourceTemplateIDTable;
CREATE TABLE IF NOT EXISTS xLookupSourceTemplateIDTable
AS
SELECT xD.TemplateID AS MasterID, ST.TemplateID FROM xDupSourceTemplateTable xD
INNER JOIN SourceTemplateTable ST
WHERE xD.FootnoteCore LIKE REPLACE(LOWER(ST.Footnote),' ','')
AND xD.FieldDefsCore LIKE REPLACE(LOWER(ST.FieldDefs),' ','')
AND ST.TemplateID > 999
;
 
-- Revise SourceTable to point to master TemplateID
--EXPLAIN QUERY PLAN
UPDATE SourceTable
SET TemplateID = (SELECT MasterID FROM xLookupSourceTemplateIDTable xL WHERE SourceTable.TemplateID=xL.TemplateID)
WHERE TemplateID IN (SELECT TemplateID FROM xLookupSourceTemplateIDTable)
;
 
-- Delete now unused duplicate Templates
DELETE FROM SourceTemplateTable
WHERE TemplateID IN
(
SELECT TemplateID FROM xLookupSourceTemplateIDTable WHERE TemplateID != MasterID
)
;

Discussions & comments from Wikispaces site