Delete Phantoms #database #delete #phantom

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)
;
 

6 Replies to “Delete Phantoms #database #delete #phantom

  1. Just in case comments from the old site got mislaid along the way. We communicated about this script and added to it steps to delete from the LinkTable and LinkAncestryTable for people who had been deleted. This would have been a new issue after V7 added those links to those online services. I can’t find the edited script now, but as I recall, it was a pretty simple pair of statements along the lines of:
    DELETE FROM {table} WHERE PersonID NOT IN (SELECT PersonID FROM PersonTable);

    • You are correct that the original script will need updating to deal with deletions affecting the LinkAncestryTable and its sibling, the LinkTable for FamilySearch Family Tree. It is a little more complicated than you suggest because what is in those tables link not only to a Person but also Citations and Media (and potentially more things). Thanks for raising the yellow flag.

      As to the discussion, I wonder if it was on the private messaging service of Wikispaces, not comments on the page. WS exported on-page discussions as text appended to the page and you will see examples of that on other pages. And I went through all WS pages and converted in-line comments that it did not export into page discussion that did. That this page is devoid of any suggests ours was a exchange off-the-page.

      • Right enough – might just have been emails between us.

        While I have your attention … another delete I need is to cleanup something RM is doing with FS that they say they might fix some day. When you create a person in RM by importing them from FS you very often end up with an empty death record because FS has that for almost everyone (unless there is specific death info). So I had been deleting those events in RM one by one as I ran into them, but then of course it occurred to me that the script was better so I wrote this and it appears to have worked:

        DELETE FROM EventTable WHERE
        EventType=2 and Date=”.” and length(Note)=0 and length(Details)=0 and OwnerType=0 and PlaceID=0

        Use it if it seems useful.

Leave a Reply

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