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


Ksquared333

RMNOCase Error

Ksquared333
02 December 2017 23:08:24

OK. I know I’m being stupid, but I don’t remember how to use RMNOCASE_fake-SQLiteSpy.dll when I’m running SourceTemplates-MergeDuplicates.sql . Where do I load the driver? Also, where do I donate?
Thanks,
Kim Derrick


ve3meo

ve3meo
03 December 2017 02:47:36

This page should answer your question: RMNOCASE+-+faking+it+in+SQLiteSpy

11 Replies to “Source Templates – Merge Duplicates #sourcetemplates #sources #merge

  1. Checked it Tom and it said it updated 35 records but none were corrected. I copied and pasted the above as the link copy and paste had syntax errors. Regards Wayne b

    • I’m sure that if you compare the Source Template list in RM before and after you will find there are 35 fewer. Or inspect the before/after SourceTemplateTable.

      I have no problem when I copy from the browser to SQLiteSpy code from either the webpage or the page that opens when I click on the sql file. Using MS Edge or Chrome.

      And you can also inspect the tables it creates for clues where to look for the differences: xDupSourceTemplateTable, xLookupSourceTemplateIDTable

  2. So the plot thickens. Where a UTCModDate has been assigned to the source template and that looks like it happens during a drag and drop the merge does not occur. I set the value of the field of UTCModDate to null and re-ran the query and that at least merged the citations to the earliest template but did not remove the duplicate name which for now is not an issue. I will just go through the list and manually remove where there is no data in the highest number source template. Jerry bangs on about darg and drop and perhaps this is another issue. I am but an amateur and would love to hear from an expert. PS a simple spreadsheet with a simple formula spotted the issue after a copy and paste of the data from SQLLite

  3. All completed now. One proved very difficult so I let it pickle for a few weeks ( a technical term) and revisited today and was able to spot the smallest of differences in the hints. Aligned and merged. Thanks Tom for you obvious patience. It would still be great to edit the field names in slightly different templates so they to could be merged.

    Regards
    Wayne b

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.