Media Duplicates – Reports and Remedies

Problem

TreeShare update operations that add images from Ancestry to the RootsMagic database can result in successive downloads of the same image file saved to a revised name, each added to the Media Gallery and independently tagged to their respective subjects. I don’t think this is either necessary or desirable (maybe there is a good reason yet to be revealed by the RootsMagician!). Moreover, such duplication (or worse, multiplication) bloats usage of storage space and inflates the Media Gallery making it harder to manage, navigate, find things. The few respondents to my survey  reported 10-20% of their media files were duplicates while an exercise with a 10-person subset of my 420 person Ancestry Tree after many TreeShare ops trying to resolve differences was far higher. The discussion and scripts that follow help understand the magnitude of the bloat and offer surgical tools to reduce it. These tools reduced the 92 media in the Gallery to 49.

Background

The issue predates TreeShare and could arise from combining databases but it is likely that it had not been noticed as frequently or to the same degree. Earlier effort dating back to RM4 (see Media Repair Queries) concerned itself with duplicate file names in different folders, not with duplicate content within differently named files.

RootsMagic places all TreeShare media from Ancestry in one folder for a given database. It does not attempt to substitute an existing duplicate media file for the one to be or is being downloaded; rather, it modifies the name of the downloading duplicate file with a unique suffix. Consequently, the old script would not even detect these differently named files with identical content, only those having the same name in separate folders. Moreover, it could generate false positives for files of the same name with different content leading to the deletion of potentially valuable information.

Finding Duplicates

By comparing RootsMagic thumbnails

RootsMagic’s database has a property for each image file that can be used as a basis for finding duplicate image content – the thumbnail image it creates of the image file. This thumbnail is stored in the database in the Thumbnail field in the MultiMediaTable. However, because it is a tiny thumbnail, it is a crude approximation of the original and it may be possible that similar images, such as some pairs of Census pages or BMD Indexes could give rise to identical thumbnails. Experience will tell. It’s a very convenient parameter to exploit as seen in this query result from a tiny, test database.

Examining the database’s MultimediaTable shows the duplicate file’s revised name:
 In this case, we can confidently surmise from the common part of the filenames AND the identical thumbnails that these undoubtedly have duplicate content.

By using CCleaner

Other files may not have strongly similar filenames despite identical thumbnails and, given the risk of false positives from the latter, a further test is required. The free CCleaner and other utilities can resolve if they have the same or different content; we’ll continue here with CCleaner and its Tools > Duplicate Finder feature set to search the folders of interest for image files that match each other only by Size and by Content. Here is a screenshot of its results:
The highlighted names are of those in the database’s Media Gallery. The others are copies made using Windows File Explorer and are unused by (and therefore unrecorded in) the RM database. The script correctly reported the two matched files used by the database from their identical thumbnails.

CCleaner exports its results as a tab-delimited text file which can be imported into a spreadsheet:

CCleaner Duplicate Files report imported into Google Sheets; headers added by user.

Combining the data from the RootsMagic database with that from the CCleaner Duplicate Files report should provide what is needed to identify and replace duplicates in the Media Gallery and to delete the then unused duplicate files from computer storage. Combining may be done by importing the CCleaner data into the RM file, temporarily, or by exporting RM data to the spreadsheet. What follows is the first approach.

Migrate CCleaner Duplicate Search results to SQLite

With the CCleaner data in the spreadsheet, formulas can be applied to create a SQLite INSERT statement for each reported file. We will create an empty temporary table with a corresponding structure into which the generated statements will insert values from the spreadsheet. I’ve created a publicly accessible Google Sheet which anyone can copy to their own spreadsheet to work on or to use directly.

Scripts for finding Duplicates

Before running any of these scripts, you should use RM’s File > Database Tools:

  1. Test Database Integrity
  2. If #1 fails, Rebuild Indexes and repeat #1
  3. Delete Phantoms

And it is probably advisable to run the latest script from Delete Phantoms to clear out any detritus that may just confuse or exaggerate comparisons of Database Properties before and after the Merge Duplicates script below. Use your OS File Explorer/Manager to get the properties of the media folder(s) used by the database.

Thumbnails

Media-DupThumbnailsList.sql Make sure you open the RM Media Gallery and let thumbnail creation complete before running this script against your database.  It creates a temp table named zDupMediaTable which is displayed at the end of script execution to show you the duplicates found. The script makes no modifications to the database; this table will be used by the merge script.  Note that it ignores media files that are not image files or any file type that is not in the subject database’s Media Gallery. And beware that there is a risk of false positives because thumbnail images are of such low resolution. If you intend to proceed to merge duplicates, do not close the database from the SQLite manager as that will delete the temp table needed.

Files

Media-DupFilesList.sql You have to edit this script to include the INSERT commands you will copy from the spreadsheet before running it on the database. It creates an identically named temp zDupMediaTable to be used by the merge script and makes no changes to the database. Because Ccleaner does a bit-wise or byte-wise comparison, there is no chance of a false match between different images. However, it will not match files having exactly the same image data but a difference in just one character of metadata. If you intend to proceed to merge duplicates, do not close the database from the SQLite manager as that will delete the temp table needed.

Merging Duplicates

Media-MergeDuplicates.sql This is where the rubber hits the road and database changes are afoot. You have taken safety precautions? And your SQLite manager still has the temp zDupMediaTable in the database from either of the above scripts? This script won’t do anything without it. Fire away!

When you next run TreeShare after script execution, all the people whose media has been affected by the merge and is tagged to anything related to the person will be marked as Changed and all previous Changed people will first have been cleared. Nothing material should have been changed by the script but flagging them so helps you to confirm that is the case.

Cleanup

Repeat the initial steps of RM Database Tools and Delete Phantoms  to clean out new detritus and then RM Database Properties for comparison of Before and After.

Delete surplus duplicate media files

The last step in the script lists the commands for deleting duplicate files from the media folder(s) that are no longer used by the database. Copy and paste them into a text editor for review and to avoid their loss if you have to close the database in the SQLite manager (some RootsMagic operations such as Backup and Restore cannot proceed if the database is open in another application). If you are confident that any or all are unused by any other database, copy and paste the respective ERASE commands into the CMD shell. They will execute immediately without asking for confirmation; the script could be modified to require a confirmation of each ERASE. For a large set, break up the command set into chunks.

Repeat getting folder properties for the After results to compare with Before, i.e., number of media files and total size.

Miscellaneous

  1. How well this procedure will scale with the size of the database is unknown. Certainly, once some large numbers of INSERTs and ERASEs is reached, there will be issues with copying and pasting and perhaps the size of the script for the INSERTs. Nor can we predict the speed with which the scripts will execute.
  2. How TreeShare functions remains a mystery in part. Events for a couple, such as Marriage, are especially problematic because we are advised that Ancestry handles them as separate individual events. It seems advisable, if not necessary, to have on the Ancestry Tree identical sources for the Marriage event for each person to be assured that RM will create the couple’s Marriage event with all the sources. And updating via TreeShare sometimes is a tail-chasing exercise in which ostensibly identical citations or media on both sides are perceived as new for the other with the update one way provoking the offer of a new one the other.
  3. The flagging of Changed People is done by setting the Modified field to 1 for records in LinkAncestryTable whose LinkType is 0. The script also sets the Modified field to 1 for records of LinkType 4 (citations) and 11 (media) but these have no effect on anything apparent in RM 7590.
  4. This procedure does not provide ERASE commands for media files that are unique (not duplicates) and unused by the database. The procedure on this early page could be adapted to do so: Scrapbook Files Status

Updates responding to TreeShare

Contents

    Working out the wrinkles in a new script addressing some TreeShare issues, I was using some other scripts to help me understand what was going on and ran into a couple of issues that I’ve fixed:

    Places – Delete Unused #places #placedetails #delete #phantom

    Contents

      RootsMagic 7 is very cautious about deleting Places and Place Details, allowing only one at a time and requiring user confirmation of the “Are you sure?” type. This script is the absolute opposite and deletes all unused Places without hesitation. Were you to transfer your database by drag’n’drop or via GEDCOM to a new database, unused Places (and other unused items) would not be included so what’s the big deal? See if you are comfortable with GEDCOM & DnD transfer losses. Deletion of unused Places in RootsMagic needs empowerment.

      This script deletes all those Places and Place Details records from the PlaceTable if unused by any Event plus all Place Details orphaned by having no master Place. It is simply a series of statements extracted from the DeletePhantoms.sql script at the page Delete Phantoms.

      After executing the script, you should run RootsMagic’s File > Database Tools to correct probable indexing errors arising from the use of the fake RMNOCASE collation.

      PlacesDeleteUnused.sql

      Delete Phantoms #database #delete #phantom

      Contents

        Rev 2019-02-12

        Whether you have deleted or merged within RootsMagic itself or used one of the Delete Many procedures, there remain in various tables residues from the deleted targets that unnecessarily clutter the database and may give rise to phantom appearances related to the deleted targets. For very large databases built from many merges, the clutter may adversely affect performance. This procedure cleans up most of these artifacts.

        WARNINGS:

        1. The procedures are not reversible and there is no guarantee that the results are perfect or what you may want. Use at your own risk and MAKE A BACKUP first.
        2. Shared events are deleted from all sharees if the Principal is not found.
        3. Unused Media, Sources, Places, Addresses are deleted.

        This procedure must be followed by these steps:

        1. RootsMagic > File > Database Tools:
          1. Rebuild Indexes
          2. Compact database

        DeletePhantoms.sqlRMtrix_tiny_check.pngOriginal query, fairly comprehensive and aggressive but overlooked unused Sources and Repositories.
        DeletePhantoms2.sql As original except less aggressive as it does not delete unused Media, Places…
        DeletePhantoms3.sql More comprehensive and aggressive than original, deleting unused Sources, Repositories, unused custom Fact Types and Roles not associated with a remaining fact type. 2013-08-07 corrected error where repository use was mistakenly based on CitationID not SourceID. 2019-02-12 now deletes orphaned TreeShare links.

        -- DeletePhantoms.sql
        /*
        2012-10-27 Tom Holden ve3meo
        2012-11-13 added query to correct PersonTable.SpouseID to 0 for those whose family (spouse)
        was deleted.
         
        Cleans database of unused records in most tables,
        some of which may give rise to phantoms in displays and reports.
        These arise from incomplete cleanup by RootsMagic when persons, families, places, sources
        are deleted directly or by merging and, most certainly, by simple SQLite deletions
        such as DeleteByColorCode.
         
        Users may find this cleanup to be too aggressive and are advised to comment out
        any sections that would delete unused items they may wish to preserve, e.g.,
        unused sources or places.
         
        There is an as-yet-unaddressed problem with shared events arising from the deletion of the Principal;
        the event is deleted so there is nothing to share. It would be desirable to convert shared events to
        unshared singular events for every sharee.
         
        */
        -- Set SpouseID=0 for persons with invalid FamilyID (TAH 2012-11-13)
        UPDATE PersonTable SET SpouseID=0
          WHERE SpouseID NOT IN (SELECT FamilyID FROM FamilyTable)
        ;
         
        -- Delete Child from ChildTable if Child does not exist in PersonTable
        DELETE FROM ChildTable WHERE ChildID NOT IN (SELECT PersonID FROM PersonTable);
         
        -- Delete Child from ChildTable if FamilyID gone from FamilyTable
        DELETE FROM ChildTable WHERE FamilyID NOT IN (SELECT FamilyID FROM FamilyTable);
         
        -- Delete Names from NameTable if OwnerID is not a PersonID in PersonTable
        DELETE FROM NameTable WHERE OwnerID NOT IN (SELECT PersonID FROM PersonTable);
         
        -- Delete Individual's Events from EventTable if OwnerID not in PersonTable
        DELETE FROM EventTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
         
        -- Delete Family Events from EventTable if OwnerID not in FamilyTable
        DELETE FROM EventTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
         
        -- Clean out Citations that no do not link to an active record
        -- Personal
        DELETE FROM CitationTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
        -- Family
        DELETE FROM CitationTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
        -- Event citations
        DELETE FROM CitationTable WHERE OwnerType = 2 AND OwnerID NOT IN (SELECT EventID FROM EventTable);
        -- Alternate Name citatoins
        DELETE FROM CitationTable WHERE OwnerType = 7 AND OwnerID NOT IN (SELECT NameID FROM NameTable WHERE NOT IsPrimary);
         
        -- Delete Citations having lost their Source
        -- DeleteUnsourcedCitations.sql
        -- 2010-01-29 ve3meo
        DELETE FROM CitationTable
          WHERE CitationID IN
           (SELECT CitationID FROM CitationTable c
              LEFT JOIN SourceTable s ON c.SourceID=s.SourceID
              WHERE s.SourceID ISNULL);
         
         
        -- Clean up addresses
        -- Addresses that are linked to non-existant persons or families
        DELETE FROM AddressTable
         WHERE AddressID NOT IN
         (
          -- Addresses that are linked to existing persons
          SELECT AddressID FROM AddressLinkTable WHERE OwnerType = 0 AND OwnerID IN (SELECT PersonID FROM PersonTable)
          UNION
          -- Addresses that are linked to existing families
          SELECT AddressID FROM AddressLinkTable WHERE OwnerType = 1 AND OwnerID IN (SELECT FamilyID FROM FamilyTable)
          )
         AND
          AddressType = 0
          ;
         
        -- Remove orphaned records from AddressLinkTable
          -- Links to non-existing persons
          DELETE FROM AddressLinkTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
          -- Links to non-existing families
          DELETE FROM AddressLinkTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
          -- Links to non-existing addresses
          DELETE FROM AddressLinkTable WHERE AddressID NOT IN (SELECT AddressID FROM AddressTable);
         
        -- Delete Unused Media
        DELETE FROM MultimediaTable
         WHERE MediaID NOT IN
         (
        -- Person media
          SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 0 AND OwnerID IN (SELECT PersonID FROM PersonTable)
          UNION
        -- Family media
          SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 1 AND OwnerID IN (SELECT FamilyID FROM FamilyTable)
          UNION
          -- Event Media
          SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 2 AND OwnerID IN (SELECT EventID FROM EventTable)
          UNION
          -- Master Source Media
          SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 3 AND OwnerID IN (SELECT SourceID FROM SourceTable)
          UNION
          -- Citation Media
          SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 4 AND OwnerID IN (SELECT CitationID FROM CitationTable)
          UNION
          -- Place Media
          SELECT MediaID FROM MediaLinkTable WHERE OwnerType = 5 AND OwnerID IN (SELECT PlaceID FROM PlaceTable)
          );
         
        -- DELETE Orphaned MediaLinks
          -- Having a non-existant MediaID
          DELETE FROM MediaLinkTable
          WHERE MediaID NOT IN
          (SELECT MediaID FROM MultiMediaTable);
         
          -- Having a link to non-exsiting
            -- Person media
            DELETE FROM MediaLinkTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT PersonID FROM PersonTable);
            -- Family media
            DELETE FROM MediaLinkTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
            -- Event Media
            DELETE FROM MediaLinkTable WHERE OwnerType = 2 AND OwnerID NOT IN (SELECT EventID FROM EventTable);
            -- Master Source Media
            DELETE FROM MediaLinkTable WHERE OwnerType = 3 AND OwnerID NOT IN (SELECT SourceID FROM SourceTable);
            -- Citation Media
            DELETE FROM MediaLinkTable WHERE OwnerType = 4 AND OwnerID NOT IN (SELECT CitationID FROM CitationTable);
            -- Place Media
            DELETE FROM MediaLinkTable WHERE OwnerType = 5 AND OwnerID NOT IN (SELECT PlaceID FROM PlaceTable);
         
        -- Delete Unused Places and Place Details
         -- Places
         DELETE FROM PlaceTable WHERE PlaceID IN
         (SELECT PlaceID FROM PlaceTable WHERE PlaceTable.PlaceType = 0 EXCEPT SELECT PlaceID FROM EventTable) ;
         -- Place Details
         DELETE FROM PlaceTable WHERE PlaceID IN
         (SELECT PlaceID FROM PlaceTable WHERE PlaceTable.PlaceType = 2 EXCEPT SELECT SiteID FROM EventTable) ;
         -- Orphaned Place Details having no Master Place
         DELETE FROM PlaceTable WHERE PlaceType = 2 AND MasterID IN
         ( SELECT MasterID FROM PlaceTable WHERE PlaceTable.PlaceType = 2 EXCEPT SELECT PlaceID FROM PlaceTable) ;
         
         
        -- Delete orphaned Tasks
          -- individuals
          DELETE FROM ResearchTable WHERE OwnerType = 0 AND OwnerID NOT IN (SELECT OwnerID FROM PersonTable);
          -- families
          DELETE FROM ResearchTable WHERE OwnerType = 1 AND OwnerID NOT IN (SELECT FamilyID FROM FamilyTable);
         
        -- Delete Headless Witnesses
        DELETE FROM WitnessTable
        WHERE WitnessTable.PersonID > 0
        AND WitnessTable.PersonID
        NOT IN
        (SELECT PersonID FROM PersonTable)
        ;
         
        

        WebTags – Consolidate #webtags #delete #citations

        Rev 2023-02-23 added versions for #RM8

        Getting at a WebTag for a Citation from the Edit Person screen takes many clicks and loses the big picture. If Citation WebTags were also accessible from the WebTags button on the Edit Person screen (and, hopefully, we will soon see this button also on the TimeLine View and RootsMagic Explorer), it would be much faster and the full picture of the person maintained. Ideally, RootsMagic would automatically populate the WebTags dropdown list with the citation WebTags. Until this enhancement is provided, a workaround is to create a duplicate of the citation WebTags for the person whose facts are supported by the citations.

        For further consideration, whether to translate WebTags for Research Items on the person to WebTags for the person.

        RM8 Version

        Pre-RM8 Version

        WebTags-Consolidate.sql RMtrix_tiny_check.pngWebTags-DeleteDuplicates.sql RMtrix_tiny_check.png
        Use if WebTags-Consolidate is repeated or there is other evidence of many duplicate WebTags.

        -- WebTags-Consolidate.sql
        /*
        2012-12-10 Tom Holden ve3meo
         
        Generates a WebTag for the Individual from all WebTags attached to citations
        of that Individual, the Family in which he/she is a spouse, all his/her events
        and alternate names. This results in ready access to all the person's WebTags
        from the button on the Edit Person screen.
        URL Owner Type (0 = Individual, 3 = Source, 4 = Citation, 5 = Place, 15 = Research Item)
        Citation Owner Type (0 = Personal, 1 = Family, 2 = Event, 7 = Alternate Name)
        */
         
        INSERT OR REPLACE INTO URLTable
        --- Citation WebTags ----
        -- Person for citations of Persons
        SELECT
          NULL AS LinkID,
          0 AS OwnerType,
          Cit.OwnerID AS OwnerID,
          0 AS LinkType,
          URL.Name AS Name,
          URL.URL AS URL,
          URL.Note AS Note
          FROM URLTable URL
          INNER JOIN
            CitationTable AS Cit
          ON URL.OwnerID = Cit.CitationID
          WHERE URL.OwnerType = 4 -- Citation webtag
            AND Cit.OwnerType = 0
        UNION
        -- Fathers for citations of families
        SELECT
          NULL AS LinkID,
          0 AS OwnerType,
          Fam.FatherID AS OwnerID,
          0 AS LinkType,
          URL.Name AS Name,
          URL.URL AS URL,
          URL.Note AS Note
          FROM URLTable URL
          INNER JOIN CitationTable AS Cit
            ON URL.OwnerID = Cit.CitationID
          INNER JOIN FamilyTable AS Fam
            ON Cit.OwnerID = Fam.FamilyID
          WHERE URL.OwnerType = 4 -- Citation webtag
            AND Cit.OwnerType = 1
         
        UNION
        -- Mothers for citations of families
        SELECT
          NULL AS LinkID,
          0 AS OwnerType,
          Fam.MotherID AS OwnerID,
          0 AS LinkType,
          URL.Name AS Name,
          URL.URL AS URL,
          URL.Note AS Note
          FROM URLTable URL
          INNER JOIN CitationTable AS Cit
            ON URL.OwnerID = Cit.CitationID
          INNER JOIN FamilyTable AS Fam
            ON Cit.OwnerID = Fam.FamilyID
          WHERE URL.OwnerType = 4 -- Citation webtag
            AND Cit.OwnerType = 1
         
        UNION
        -- Person for citations of individual events
        SELECT
          NULL AS LinkID,
          0 AS OwnerType,
          Evt.OwnerID AS OwnerID,
          0 AS LinkType,
          URL.Name AS Name,
          URL.URL AS URL,
          URL.Note AS Note
          FROM URLTable URL
          INNER JOIN CitationTable AS Cit
            ON URL.OwnerID = Cit.CitationID
          INNER JOIN EventTable Evt
            ON Cit.OwnerID = Evt.EventID
          WHERE URL.OwnerType = 4 -- Citation webtag
            AND Cit.OwnerType = 2 -- Event citation
            AND Evt.OwnerType = 0 -- Person
         
        UNION
        -- Husband for citations of Family events
        SELECT
          NULL AS LinkID,
          0 AS OwnerType,
          Fam.FatherID AS OwnerID,
          0 AS LinkType,
          URL.Name AS Name,
          URL.URL AS URL,
          URL.Note AS Note
          FROM URLTable URL
          INNER JOIN CitationTable AS Cit
            ON URL.OwnerID = Cit.CitationID
          INNER JOIN EventTable Evt
            ON Cit.OwnerID = Evt.EventID
          INNER JOIN FamilyTable Fam
            ON Evt.OwnerID = Fam.FamilyID
          WHERE URL.OwnerType = 4 -- Citation webtag
            AND Cit.OwnerType = 2 -- Event citation
            AND Evt.OwnerType = 1 -- Family
         
        UNION
        -- Wife for citations of Family events
        SELECT
          NULL AS LinkID,
          0 AS OwnerType,
          Fam.MotherID AS OwnerID,
          0 AS LinkType,
          URL.Name AS Name,
          URL.URL AS URL,
          URL.Note AS Note
          FROM URLTable URL
          INNER JOIN CitationTable AS Cit
            ON URL.OwnerID = Cit.CitationID
          INNER JOIN EventTable Evt
            ON Cit.OwnerID = Evt.EventID
          INNER JOIN FamilyTable Fam
            ON Evt.OwnerID = Fam.FamilyID
          WHERE URL.OwnerType = 4 -- Citation webtag
            AND Cit.OwnerType = 2 -- Event citation
            AND Evt.OwnerType = 1 -- Family
         
        UNION
        -- Person for citations of Alternate Names
        SELECT
          NULL AS LinkID,
          0 AS OwnerType,
          Nam.OwnerID AS OwnerID,
          0 AS LinkType,
          URL.Name AS Name,
          URL.URL AS URL,
          URL.Note AS Note
          FROM URLTable URL
          INNER JOIN
            CitationTable AS Cit
            ON URL.OwnerID = Cit.CitationID
          INNER JOIN NameTable Nam
            ON Cit.OwnerID = Nam.NameID
          WHERE URL.OwnerType = 4 -- Citation webtag
            AND Cit.OwnerType = 7 -- Alt Name citation
        --- End of Citation WebTags ----
        ;

        Media – Delete Unused #media #delete

        Contents

          Deletes records from MultiMediaTable untagged by any Person, Family, Fact, Place, Source or Citation. Was formerly included in Delete Phantoms.

          MediaDeleteUnused.sql RMtrix_tiny_check.png

          -- MediaDeleteUnused.sql
          /*
          2013-01-21 Tom Holden ve3meo
           
          Deletes records from MultiMediaTable untagged by
          any Person, Family, Fact, Place, Source or Citation.
           
          Extracted from DeletePhantoms.sql
          */
          DELETE
          FROM MultimediaTable
          WHERE MediaID NOT IN (
                  -- Person media
                  SELECT MediaID
                  FROM MediaLinkTable
                  WHERE OwnerType = 0
                      AND OwnerID IN (
                          SELECT PersonID
                          FROM PersonTable
                          )
           
                  UNION
           
                  -- Family media
                  SELECT MediaID
                  FROM MediaLinkTable
                  WHERE OwnerType = 1
                      AND OwnerID IN (
                          SELECT FamilyID
                          FROM FamilyTable
                          )
           
                  UNION
           
                  -- Event Media
                  SELECT MediaID
                  FROM MediaLinkTable
                  WHERE OwnerType = 2
                      AND OwnerID IN (
                          SELECT EventID
                          FROM EventTable
                          )
           
                  UNION
           
                  -- Master Source Media
                  SELECT MediaID
                  FROM MediaLinkTable
                  WHERE OwnerType = 3
                      AND OwnerID IN (
                          SELECT SourceID
                          FROM SourceTable
                          )
           
                  UNION
           
                  -- Citation Media
                  SELECT MediaID
                  FROM MediaLinkTable
                  WHERE OwnerType = 4
                      AND OwnerID IN (
                          SELECT CitationID
                          FROM CitationTable
                          )
           
                  UNION
           
                  -- Place Media
                  SELECT MediaID
                  FROM MediaLinkTable
                  WHERE OwnerType = 5
                      AND OwnerID IN (
                          SELECT PlaceID
                          FROM PlaceTable
                          )
                  );

          Delete Many #delete

          Problem statement

          A problem for many users is that, even as of RootsMagic 5.0.4.1, there is still no way of deleting persons from the database other than one at a time. Workarounds using RM to partially export to GEDCOM or partially transfer to a new database lose Named Groups, To-Do lists and truncate long event descriptions with other losses identified in GEDCOM & DnD transfer losses.

          Procedures

          Two SQLite procedures have been developed as a workaround without the losses attendant to partial export and drag and drop:

          1. Delete by Color Coding
          2. Delete by Named Group

          These depend on the user building the desired set of persons to be deleted having either a specific color code (Red as written) or belonging to a Named Group having a name beginning with “#DELETE#”.

          WARNING: The procedures are not reversible and there is no guarantee that the results are perfect or what you may want. Use at your own risk and MAKE A BACKUP first.

          Both deletion procedures must be followed by these steps:

          1. Delete Phantoms – a procedure that cleans up the database of unused records from most tables left behind by these bulk deletions or by deletions and merges from within RootsMagic. (This is another item on the RM Wish List)
          2. RootsMagic > File > Database Tools:
            1. Rebuild Indexes
            2. Compact database
          3. RootsMagic > Tools > Count Trees. Inspect and deal with any undesired results.

          Scripts

          Delete by Color Coding

          DeleteByColorCode.sql

          -- DeleteByColorCode.sql
          /*
          2012-10-27 Tom Holden ve3meo
           
          Deletes Persons and Families from PersonTable and FamilyTable respectively for
          persons with a specified color code.
           
          As written, the key color is RED (=1). Edit all instances of "= 1" to the desired code.
           0 = None,
           1 = Red,
           2 = Lime,
           3 = Blue,
           4 = Fuschia,
           5 = Yellow,
           6 = Aqua,
           7 = Silver,
           8 = Maroon,
           9 = Green,
          10 = Navy,
          11 = Purple,
          12 = Brown,
          13 = Teal,
          14 = Gray
           
          N.B.: This procedure results in many types of 'phantoms' and should be followed by
          the procedure DeletePhantoms.sql
          */
           
          -- Delete Family where one of the spouses has a colorcode match
          DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable WHERE PersonID = FatherID) = 1;
          DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable WHERE PersonID = MotherID) = 1;
          -- Delete Person from PersonTable if matched on colorcode
          DELETE FROM PersonTable WHERE Color = 1;
          

          Delete by Named Group

          DeleteByNamedGroup2.sql

          -- DeleteByNamedGroup2.sql
          /*
          2012-10-27 Tom Holden ve3meo
          2013-01-21 v2 - deletes groups with name beginning '#DELETE#'
           
          Deletes Persons and Families from PersonTable and FamilyTable respectively for
          all persons in specific Named Groups having a name starting with #DELETE# .
           
          N.B.: This procedure results in many types of 'phantoms' and should be followed by
          the procedure DeletePhantoms.sql
          */
          -- Delete Family where one of the spouses is in the Group
          -- Fathers or Husbands
          DELETE
          FROM FamilyTable
          WHERE FatherID IN (
                  SELECT PersonID
                  FROM PersonTable
                      ,GroupTable
                  WHERE PersonID BETWEEN StartID
                          AND EndID
                      AND GroupID IN (
                          SELECT LabelValue
                          FROM LabelTable
                          WHERE UPPER(LabelName) LIKE '#DELETE#%'
                          )
                  );
           
          -- Mothers or Wives
          DELETE
          FROM FamilyTable
          WHERE MotherID IN (
                  SELECT PersonID
                  FROM PersonTable
                      ,GroupTable
                  WHERE PersonID BETWEEN StartID
                          AND EndID
                      AND GroupID IN (
                          SELECT LabelValue
                          FROM LabelTable
                          WHERE UPPER(LabelName) LIKE '#DELETE#%'
                          )
                  );
           
          -- Delete Person from PersonTable if in the Group
          DELETE
          FROM PersonTable
          WHERE PersonID IN (
                  SELECT PersonID
                  FROM PersonTable
                      ,GroupTable
                  WHERE PersonID BETWEEN StartID
                          AND EndID
                      AND GroupID IN (
                          SELECT LabelValue
                          FROM LabelTable
                          WHERE UPPER(LabelName) LIKE '#DELETE#%'
                          )
                  );

          Media Repair Queries #filenames #duplicates #media #links #rmnocase #delete #update

          Contents

          Importing and merging duplicates of persons, families, places and sources already in a database can give rise to unwanted proliferation of duplicate items in the Media Gallery and multiple links to the same media item from a person, family, event, place or source. RMGC_Properties – Query now reports the quantities of such duplicates. A problem database that I worked on had 80 duplicate file names and 160 duplicate links from persons, families, events, places or sources. One image had 42 links from the same Place. As the Media Gallery grows, it becomes increasingly difficult to notice duplicate image file names and much harder to identify and remove duplicate links.

          The series of queries in this SQL file help to identify duplicate file names and duplicate links and demonstrates a repair for one case of how duplicate file names can arise and a repair for duplicate links, regardless of cause. The repair queries delete records and, unfortunately, require the use of a SQLite manager capable of faking a RMNOCASE collation sequence to update the associated indexes. The investigative queries can be run on any SQLite manager. CAUTION: untested with RM5 and may give unwanted results due to changes in the media tables.

          MediaRepair.sql

          Query

          1. Lists duplicate media file names

          MediaIDMediaFileMediaPath
          86_East Dyberry Cemetery 1.JPGd:My DocumentsGenealogyGravestonesUSA – East Dyberry (Wayne Co)
          93_East Dyberry Cemetery 1.JPGE:My DocumentsGenealogyGravestonesUSA – East Dyberry (Wayne Co)
          48bailie-agnes,1885-jan-26-(b).jpge:My DocumentsGenealogySurgeonerBirths
          251bailie-agnes,1885-jan-26-(b).jpgd:My DocumentsGenealogySurgeonerBirths

          2. Creates a list of UPDATE commands to replace the MediaID in MediaLinkTable that points to the MultimediaTable record having the path to E drive with the MediaID of the same file on the D drive. Copy this list to another SQL edit page and run it. NB – this query was for the specific problem of this database: the same files were on two different drives having the same paths.

          3. Query #2 produces a list of queries such as this:

          command
          UPDATE medialinktable SET MediaID=106 WHERE MediaID=107;
          UPDATE medialinktable SET MediaID=108 WHERE MediaID=109;
          UPDATE medialinktable SET MediaID=110 WHERE MediaID=111;

          This list is copied into a SQL edit window of the SQLite manager and executed, sans the header “command”.

          4. After UPDATing the MediaIDs in MediaLinkTable to the new MediaIDs, then run this query to delete the records with oldMediaIDs from MultimediaTable. REQUIRES SharpPlus SQLite Developer or other SQLite manager supporting a fake RMNOCASE collation.

          5. Lists duplicate links in MediaLinkTable to media files in MultimediaTable

          LinkIDDUPESLinkedTo
          1381_East Dyberry Cemetery 1.JPG
          1375_East Dyberry Cemetery 7.JPG
          761bailie-agnes,1885-jan-26-(b).jpg
          72341Ballyclare Town Hall.pcx

          6. DELETEs duplicate links from MediaLinkTable. REQUIRES SharpPlus SQLite Developer, SQLiteSpy with extension, or other SQLite manager supporting a fake RMNOCASE collation.

          Delete Phantom Citations – Query #delete #citations #phantom

          Contents

            This query deletes from the CitationsTable those records that have no associated record in the SourceTable. These ‘phantom’ sources or ‘orphaned’ citations manifest themselves in the Edit Person screen as citations that return nothing when opened, are counted in the File > Properties report and may have odd effect in other reports.

            The query is believed to be quite safe to run as it affects nothing dependent on having the proprietary RMNOCASE collation.

            -- DeleteUnsourcedCitations.sql
            -- 2010-01-29 ve3meo
            -- Deletes Citations having lost their Source
             
            DELETE FROM CitationTable
              WHERE CitationID IN
               (SELECT CitationID FROM CitationTable c
                  LEFT JOIN SourceTable s ON c.SourceID=s.SourceID
                  WHERE s.SourceID ISNULL);