9 replies to this topic

[GlenB]

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!

[GlenB]

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?

[John_of_Ross_County]

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

[GlenB]

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.

[John_of_Ross_County]

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

[Jerry Bryan]

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

[GlenB]

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.

[Jerry Bryan]

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

[TomH]

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.

[GlenB]

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.