Jump to content


Photo

Cleaning up FS data

SQLite FamilySearch

  • Please log in to reply
9 replies to this topic

#1 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 102 posts

Posted 15 October 2018 - 02:07 PM

This is mainly an SQLite question (TomH are you here?).

 

I had downloaded a lot of info from FS into RM. Then I developed a query to identify the ones that I did NOT need to keep and gave them all a particular colour coding. Then I used an SQLite query (Delete Many) that deleted every person with that colour coding and cleaned up all the unused fact types and places and sources etc etc etc (Delete Phantoms3). After all that, run all the database tools and RM is happy with the results, and it looks like what I was intending.

 

The SQLite script was developed before the FS integration and so it does not know how to cleanup the references in the tables that were added when FS integration was added. In particular, there are some entries in some table that indicates that a PersonID (which no longer exists) is matched to a particular FS ID. So I'm looking for a script that can clean up those references.  I'll go poke around in SQLite Spy and see if I can figure anything out but, if you know how to do this, please respond here!



#2 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 102 posts

Posted 15 October 2018 - 02:26 PM

I think this lists all the FS links that are broken:

        select extID, rmID from LinkTable where rmID not in (select OwnerID from NameTable)

I checked a couple of them and it looks right.

 

So I should be able to delete all those records from the LinkTable with

        delete from LinkTable where rmID not in (select OwnerID from NameTable)

 

But is there anything ELSE I need to do or is just deleting these records enough?



#3 John_of_Ross_County

John_of_Ross_County

    Advanced Member

  • Members
  • PipPipPip
  • 636 posts

Posted 15 October 2018 - 03:36 PM

I don't use SQLite, but I assume that you do a backup before any database modification.



#4 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 102 posts

Posted 15 October 2018 - 04:04 PM

I don't use SQLite, but I assume that you do a backup before any database modification.

 

Seriously? If you used SQLite you'd see that as a FREQUENT AND COMMON warning with all the Scripts that have been published for use with RM. And I have way too many years of technical computing experience to miss that in any case.

 

But .... you don't know me so, thanks.



#5 John_of_Ross_County

John_of_Ross_County

    Advanced Member

  • Members
  • PipPipPip
  • 636 posts

Posted 15 October 2018 - 06:05 PM

And all the way back to DEC PDP8, PDP11, and VAX computers on my part.



#6 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3299 posts

Posted 15 October 2018 - 06:08 PM

DELETE FROM LinkTable WHERE rmID NOT IN (SELECT PersonID FROM PersonTable);

I haven't tested it, but I'm about 99% sure the above is what you need. The LinkTable contains the FS ID's and the above statement should delete from the LinkTable those rows that don't have a corresponding row in the PersonTable because you have deleted that person from the PersonTable.

 

Jerry



#7 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 102 posts

Posted 15 October 2018 - 06:12 PM

DELETE FROM LinkTable WHERE rmID NOT IN (SELECT OwnerID FROM NameTable);

DELETE FROM LinkTable WHERE rmID NOT IN (SELECT PersonID FROM PersonTable);

 

These SHOULD be equivalent if the database has integrity since there's a 1-to-1 correspondence between NameTable and PersonTable as far as I can tell, but I think I like yours better anyway! Thanks.

 

I'm going to wait a little longer before trying it (and looking for unintended consequences) - just in case someone else rolls in with a suggestion.



#8 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3299 posts

Posted 15 October 2018 - 06:18 PM

DELETE FROM LinkTable WHERE rmID NOT IN (SELECT OwnerID FROM NameTable);

DELETE FROM LinkTable WHERE rmID NOT IN (SELECT PersonID FROM PersonTable);

 

These SHOULD be equivalent if the database has integrity since there's a 1-to-1 correspondence between NameTable and PersonTable as far as I can tell, but I think I like yours better anyway! Thanks.

 

The two should produce an equivalent result if the database has referential integrity. However, there actually is not usually a 1-to-1 correspondence between NameTable and PersonTable. Rather, if you have any Alternate Names then there is a many-to-one correspondence because multiple rows of the Name Table will correspond to a single row of the PersonTable.

 

Jerry



#9 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6086 posts

Posted 16 October 2018 - 07:07 AM

I agree that you can safely delete those records from LinkTable that have no match in PersonTable.PersonID or in NameTable.OwnerID. The same would be true for AncestryLinkTable.

When I get unstalled with the SQLiteTools migration, I will update the script. Of course, it's possible RM8 will overtake that process and many other changes will be needed.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#10 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 102 posts

Posted 16 October 2018 - 03:26 PM

Deleted the records in LinkTable as described and then ran the Database Tools in RM. Everything seems fine, and the info presented on Family Search Central makes a LOT more sense. I'm declaring success. Thanks to all for your help.