Place+Details+without+a+Place

=Orphaned Place Details= toc On integrating these queries into the RMtrix bundled utilities, I found it necessary to revise them extensively, partly because they do not safely or efficiently address those orphaned Place Details that are actually used by a fact/event in the database. Moreover, without the foreknowledge of the names of the Place Details, it is impossible to find them using RootsMagic tools. My revised queries provide both an efficient way of finding where the orphaned sites are used, so that we can get at them with the Edit Person screen, and a speedy deletion of only those that are unused.

FAQ

 * 1) What is an Orphaned Place Detail? It's a Place Detail for which there is no parent Place in the PlaceTable.
 * 2) What's the problem with them? Unused ones merely clutter up one table. While ones that are used do appear to come out alright in narrative reports, they do not show in the Place List, they cannot be: edited, have images tagged to them, geotagged nor commented.
 * 3) How do they arise in the first place? One way for certain is that a Place Detail can be added through the Edit Person screen with the Place field left empty (as of RM5021). Other possibilities are from past deletion or merging of the parent.
 * 4) How do I fix the ones I want to keep? Use the OrphanedPlaceDetails query in RMtrix or download and run it in SQLiteSpy to get the report. Using RootsMagic, select the persons listed and find the fact in their Edit Person screen; assign the Place and then add the Place Detail.
 * 5) How do I get rid of the unused ones? Orphaned Place Details - Delete Unused in RMtrix or download and run it in SQliteSpy. WARNING - there is risk of database corruption of the sort that RM5 can repair but RM4 cannot. After the deletion, use RM5's Database Tools > Rebuild Indexes. Best to leave the unused Place Details alone in RM4.

Download


=Original Post from Jerry Bryan= I've discovered that I have four Place Details in my database without a Place.RMGC_Properties - Query The situation can be identified with the following extremely trivial query. code format="sql" SELECT * FROM PlaceTable WHERE PlaceType = 2 AND MasterID = 0 code PlaceType = 2 identifies PlaceTable entries that are for Place Details, and if PlaceType = 2 then MasterID identifies the associated Place with which the Place Details are associated. MasterID=0 indicates that there is no Place associated with the Place Details.

For each of the four Place Details in question, I did a search within RM5 itself for "Any Fact Place Details contains (the Place Details in question)". In three cases, nothing was found. In the fourth case, RM5 itself found the Place Details in question associated with a blank Place (basically, an impossible situation).

I have not run any of Tom's Place/Place Details queries against my database. Whatever strange thing happened to my database just happened between me doing data entry with the keyboard and mouse and then with whatever processing RM4/RM5 did behind the scenes. I include both RM4 and RM5 in this scenario because I don't know when this little glitch in my database happened - before RM5 or after RM5.

I decided to fix my database with the following and equally trivial query.

code format="sql" DELETE FROM PlaceTable WHERE PlaceType = 2 AND MasterID = 0 code But the DELETE query will not run. It gets the infamous error message: SQLite Error 1 - no such collation sequence: RMNOCASE. I don't understand why there is an RMNOCASE error when both of the data elements I'm testing are numeric.

Jerry

Added by Jerry Bryan 3/6/2012

I have concluded that my original description of orphaned Place Details was just a symptom of a larger problem. Namely, somewhere in the process of using the RM 5.0.2.1 feature to split a Place into Place + Place Details and merging any resultant duplicate places, there appears to be a bug whereby one of the duplicate places being merged is deleted without the all the pointers in the EventsTable for that place being adjusted to point to the entry in the PlaceTable that is being kept. The following query will identify all such orphaned place pointers in the Events Table.

code format="sql" /*    This query identifies all events in the EventTable which contain a PlaceID value that does not appear as a PlaceID value in the PlaceTable. This is an "impossible" situation which should never occur. However, due to a possible bug in RM 5.0.2.1 or other unknown cause, this "impossible" situation has occurred one time in my database.

This "impossible" situation occurred after numerous repetitions of splitting a Place into Place + Place Details and subseqently merging the resultant duplicate Place values.

If the database against which this query is run does not have the problem, then the query will return no results. So "no results" is the sign of success.

SELECT L.EventID, L.EventType, L.OwnerType, L.OwnerID, L.FamilyID, L.PlaceID, P.PlaceID FROM (       SELECT EventID, EventType, OwnerType, OwnerID, FamilyID, PlaceID        FROM EventTable        WHERE PlaceID != 0          /* Eliminate events without places in the sub-query  */        ORDER BY PlaceID, EventID       ) AS L

LEFT JOIN

PlaceTable AS P ON L.PlaceID = P.PlaceID

WHERE P.PlaceID IS NULL;    /* Events with a place, but the place is not in the PlaceTable  */ code