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