RMGC_Properties – Query #database


Lists the properties of a RootsMagic 4 or 5 database except for dates, plus reports extra detail characterizing the database and identifying possible problems in it or in transfers to another other RootsMagic database via GEDCOM or drag’n’drop. Most SQLite managers can export the results to a .csv spreadsheet for tracking and comparison. Could be useful to compare before and after any major operations on the database.

Now has a parameter input dialog to select Summary level (matching RM’s File > Properties report) or default to the Detail level as in the sample below. Summary level works (again and only) with SQLite Developer except for some late (but not the latest) upgrades which require running the last Select after the query first runs. All others skip the parameter input and produce the Detail report. Does not yet report on the RM5 Research Manager feature.

New 2012-03-01: discovered and added class of Headless Citations for Alternate Names mispointing to a Primary Name
New 2012-02-20: corrected Orphaned Alternate Names; adapted Media Date+Description test for RM4/5 differences
Rev 2010-02-13: – Duplicate Citations
Rev 2010-07-05: – Duplicate Citations for families overstated due non-discrimination between different events – corrected.
Rev 2010-07-06: – Duplicate Citations for Individuals erroneously pointed to some family facts – corrected.
Rev 2010-07-14 – added Witness & Role Properties, using a new, unmodified blank database for detection of changes to role names and sentences.
N.B.: note the comment in the sql file about the requirement for a new, empty, unmodified database file for comparison to the file under test.
Rev 2010-08-09 – added Places and Place Details: Used, having Geo-coordinates; added FactType and SourceTemplate properties
Rev 2010-08-10 – added Version property as recorded in ConfigTable
Rev 2010-12-13 – added Multimedia duplicate filenames and Multimedia duplicate links properties

Also download or create a reference empty database and revise the path in the script to match its location on your system: Empty5021.rmgc

Sample Output

5008Versionvs Control version 5008
1157Peopleall records in PersonTable
0– Nameless Peopleno record in NameTable for that RIN
97– Unresolved Duplicate Name Pairspairs of Given and Surnames, not flagged as “Not a Problem”
16– Resolved* Duplicate Name Pairsflagged as “Not a Problem” – flags lost on transfer
2– Unresolved Duplicates with Media Linkssecondary persons’ links lost on merge
39Alternate namesall records in NameTable where IsPrimary=0
0– Orphaned Alternate names*no Primary name record found
389Familiesall records in FamilyTable
66Fact Typesno. of records from FactTypeTable
2– Custom Fact Typesno. of custom Fact Types
9– Customised Built-in Fact Typesno. of built-in Fact Types modified
35– Unused Fact Typesno. of Fact Types not used
0– Blank Fact Type NamesFactTypes must be named
0– Blank FactType SentencesFactType needing definition
2831Eventsall records of EventTable
0– Orphaned Eventsevents for which no person or family match in respective tables
135– Event WitnessesAll records in WitnessTable of persons sharing events
17— Nominal Witnessesnot Persons from database, but named in WitnessTable as sharing an event
0— Headless WitnessesPersonID (RIN) in WitnessTable missing from PersonTable
0— Witnesses to Lost EventsEventID in WitnessTable cannot be found in EventTable
0— Witnesses with blank Roleno role has been assigned from RoleTable or the RoleTable role is empty
3— Witnesses with Custom Sentencea custom sentence has been assigned, unique to this witness
27— Witnesses with Notea note has been entered for this witness to an event
81— Rolesno. of records from RoleTable
23– Custom Rolesno. of custom roles
1– Customised Built-in Rolesno. of built-in roles modified
54– Unused Rolesno. of roles not used
0– Blank Role NamesRoles needing definition
2– Blank Role SentencesRoles needing definition
775Total Placesall records in PlaceTable incl Places and Place Details (Sites)
146– System Placessystem supplied Places: LDS Temples
470– User Placesuser defined Places excl Sites
296— Used, having Geo-coordinatesnon-empty Lat or Long
27— Unused User Places*not used by EventTable, will be dropped in a transfer
159— User Place Detailsuser defined Sites
9– Used, having Place Detail Notes*Site Notes will be lost in a transfer
30– Used, having Geo-coordinatesnon-empty Lat or Long
1– Unused Place Details*Sites will be lost in a transfer
829Source Templates
  1. of records from SourceTemplateTable
416– Custom Source Templates
  1. of custom Source Templates
0– Unupdated Built-in SourceTemplates
  1. not matching reference database
393– Unused Custom SourceTemplates*lost on transfer
0– Incomplete Source Templatesmissing part of definition
139Total Sourcesall records from SourceTable
2– Unused Sources*SourceTable records unused by CitationTable
1697Total Citationsall records from CitationTable
0– Duplicate Citationsidentical in most respects, cluttering reports
0– Sourceless Citations*no SourceTable record for this CitationTable record
10– Headless Citations*CitationTable records for which no Person, Event, Family, AltName found; cleaned on transfer
12Repositoriesall records from AddressTable of type Repository
36To- do tasksall records from ResearchTable
123Multimedia itemsall records from MultimediaTable
12– lacking thumbnailprobably an imported reference to an image file that has yet to be found
0– duplicate multimedia filenamesprobably having different paths
158Multimedia linksall records from MediaLinkTable
0– with Date & Description*(RM4) if a record has both, the Description is lost in a transfer
10– with Date & Description*(RM5) if a record has both, the Description is lost in a transfer
5– duplicate multimedia linksimage appears multiple times for person, fact
20Addressesall records from AddressTable of type Address
0– blank namesName field of AddressTable record is blank
0Correspondenceall records from ResearchTable of type Correspondence
via GEDCOM or Drag&Drop to another RM database

Discussions & comments from Wikispaces site


Tracing the “Headless Witnesses”

07 December 2011 17:14:19

Following Don Newcomb’s post at RootsMagic Forum, let’s look for a solution.

RMGC_Properties query reports a number of Headless citations. The core of that query gives a list of the RINs that were once witness to some event but are no longer persons in the database. Reviewing an earlier backup of the database may reveal these persons.
-- List of RINs of Headless Witnesses
SELECT PersonID FROM WitnessTable WHERE WitnessTable.PersonID > 0

A more complex query could look up the persons in the database currently owning (the Principal role) or sharing the event.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.