Possible RM9 Bug or RM9 Repurposing of MasterID Column of PlaceTable

#1 · 2023-05-27, 1:27 pm

Quote from thejerrybryan on 2023-05-27, 1:27 pm
I am working on a series of SQLite scripts that compare an RM7 database with an RM9 database. In this case, I was comparing RM7's PlaceTable with RM9's PlaceTable. I had just imported from RM7 into RM9 and my script reported that the PlaceTable's were identical.
I changed a Place in RM7's PlaceTable using the user interface. My script found the difference, which showed that my script was working correctly in this case. I changed the Place in RM7 back to it's original value and my script again reported that the Place Tables were identical.
I changed a Place in RM9's PlaceTable. My script found the difference, which again showed that my script was working correctly in this case. I changed the Place in RM9 back to its original value and my script still reported a difference between RM7 and RM9. I thought something was wrong with my script.
My script is not just testing the PlaceTable.Name column itself. It is testing all the columns in the PlaceTable that are common to RM7 and RM9. The script has to test only common columns because RM9 has new columns. Further investigation showed that the column which had become different was the MasterID column in RM9. That's most curious because that's the column in the PlaceTable that is used to link Place Details to the main Place value, and I don't use Place Details. It's further curious because the value that is being placed into the MasterID column is a number that does not correspond to any row in the PlaceTable. The number is always 1366 even though my PlaceTable has no row 1366.
My script is not causing the MasterID column to change. It's not an update script - just a reporting script. So I'm completely baffled. Either it's an out and out bug, or else they have repurposed the MasterID column a little bit in RM9. In RM7, it's an unused column for Place rows and it's only used for Place Details rows. It links the Place Detail rows to the owning Place Rows. But as an unused column in Place rows, I suppose they could have repurposed it for something.
I guess my two questions are: 1) does anybody know what's going on, and 2) does anybody else's RM9 PlaceTable have non-zero values in MasterID column for Place rows?

I am working on a series of SQLite scripts that compare an RM7 database with an RM9 database. In this case, I was comparing RM7's PlaceTable with RM9's PlaceTable. I had just imported from RM7 into RM9 and my script reported that the PlaceTable's were identical.

I changed a Place in RM7's PlaceTable using the user interface. My script found the difference, which showed that my script was working correctly in this case. I changed the Place in RM7 back to it's original value and my script again reported that the Place Tables were identical.

I changed a Place in RM9's PlaceTable. My script found the difference, which again showed that my script was working correctly in this case. I changed the Place in RM9 back to its original value and my script still reported a difference between RM7 and RM9. I thought something was wrong with my script.

My script is not just testing the PlaceTable.Name column itself. It is testing all the columns in the PlaceTable that are common to RM7 and RM9. The script has to test only common columns because RM9 has new columns. Further investigation showed that the column which had become different was the MasterID column in RM9. That's most curious because that's the column in the PlaceTable that is used to link Place Details to the main Place value, and I don't use Place Details. It's further curious because the value that is being placed into the MasterID column is a number that does not correspond to any row in the PlaceTable. The number is always 1366 even though my PlaceTable has no row 1366.

My script is not causing the MasterID column to change. It's not an update script - just a reporting script. So I'm completely baffled. Either it's an out and out bug, or else they have repurposed the MasterID column a little bit in RM9. In RM7, it's an unused column for Place rows and it's only used for Place Details rows. It links the Place Detail rows to the owning Place Rows. But as an unused column in Place rows, I suppose they could have repurposed it for something.

I guess my two questions are: 1) does anybody know what's going on, and 2) does anybody else's RM9 PlaceTable have non-zero values in MasterID column for Place rows?

#2 · 2023-05-27, 8:17 pm

I have a much simpler re-creation of the situation available. I made a new and empty RM9 database. I added one person with one fact with one place. Having done so the following columns for that place were zero: MasterID, fsID, and anID.

Then from the RM9 user interface (main Places tab) I changed the place name to something else. The results were as follows.

MasterID: 1366
fsID: -832685504
anID : 487

Changing PlaceTable.Name back to its original value leaves these strange values for MasterID, fsID, and anID in place.

Suspecting these might be bit flags, the hex values are as follows.

31333636
2D383332363835353034
343837

I find this not very helpful.

I think for the purposes of my script to compare RM7 to RM9, I need to ignore the MasterID column when PlaceTable.PlaceType = 0 indicating a standard place, and to process the MasterID column when PlaceTable.PlaceType = 2 indicating a Place Detail.

If anyone has puzzled out what MasterID, fsID, and anID mean when PlaceTable.PlaceType = 0, please post what you have learned. So far, I don't see any obvious clues in the RM9 user interface. The column names suggest we have a FamilySearch ID and an Ancestry ID and that this might have something to do with propagating place name changes to FamilySearch and to Ancestry. Except that my little one person database has nobody matched with FamilySearch and the database is not connected to Ancestry via TreeShare.

Ultimately, I'm going to need to confirm that when I run Tom's procedure to convert an RM9 database back to RM7 that the PlaceTable.MasterID column is set back to 0 when PlaceTable.PlaceType = 0,

#3 · 2023-05-28, 10:47 am

I've had a quick look at some databases and found examples similar to yours in a couple. One went from RM9 to RM7 to RM8 back to RM9 and the records were exactly preserved. I think the original was an upgrade from RM8 and that file has the same strange records. I don't have the original RM7 from which it was upgraded. Also found examples in another file from another user. The MasterID numbers are different from yours but are not all unique. Not had time to test further. My query was:

SELECT * FROM PlaceTable WHERE NOT PlaceType AND MasterID
;