Delete+Phantoms

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:
 * 2) Rebuild Indexes
 * 3) Compact database

Original query, fairly comprehensive and aggressive but overlooked unused Sources and Repositories. As original except less aggressive as it does not delete unused Media, Places... 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.

code format="sql" -- 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)

code