RMGC_Properties – Query #databaseproperties #subqueries #integrity

Description

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

RMGC_Properties.sqlRMtrix_tiny_check.png
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

Value Variable Remark
5008 Version vs Control version 5008
1157 People all records in PersonTable
0 – Nameless People no record in NameTable for that RIN
97 – Unresolved Duplicate Name Pairs pairs of Given and Surnames, not flagged as “Not a Problem”
16 – Resolved* Duplicate Name Pairs flagged as “Not a Problem” – flags lost on transfer
2 – Unresolved Duplicates with Media Links secondary persons’ links lost on merge
39 Alternate names all records in NameTable where IsPrimary=0
0 – Orphaned Alternate names* no Primary name record found
389 Families all records in FamilyTable
66 Fact Types no. of records from FactTypeTable
2 – Custom Fact Types no. of custom Fact Types
9 – Customised Built-in Fact Types no. of built-in Fact Types modified
35 – Unused Fact Types no. of Fact Types not used
0 – Blank Fact Type Names FactTypes must be named
0 – Blank FactType Sentences FactType needing definition
2831 Events all records of EventTable
0 – Orphaned Events events for which no person or family match in respective tables
135 – Event Witnesses All records in WitnessTable of persons sharing events
17 — Nominal Witnesses not Persons from database, but named in WitnessTable as sharing an event
0 — Headless Witnesses PersonID (RIN) in WitnessTable missing from PersonTable
0 — Witnesses to Lost Events EventID in WitnessTable cannot be found in EventTable
0 — Witnesses with blank Role no role has been assigned from RoleTable or the RoleTable role is empty
3 — Witnesses with Custom Sentence a custom sentence has been assigned, unique to this witness
27 — Witnesses with Note a note has been entered for this witness to an event
81 — Roles no. of records from RoleTable
23 – Custom Roles no. of custom roles
1 – Customised Built-in Roles no. of built-in roles modified
54 – Unused Roles no. of roles not used
0 – Blank Role Names Roles needing definition
2 – Blank Role Sentences Roles needing definition
775 Total Places all records in PlaceTable incl Places and Place Details (Sites)
146 – System Places system supplied Places: LDS Temples
470 – User Places user defined Places excl Sites
296 — Used, having Geo-coordinates non-empty Lat or Long
27 — Unused User Places* not used by EventTable, will be dropped in a transfer
159 — User Place Details user defined Sites
9 – Used, having Place Detail Notes* Site Notes will be lost in a transfer
30 – Used, having Geo-coordinates non-empty Lat or Long
1 – Unused Place Details* Sites will be lost in a transfer
829 Source 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 Templates missing part of definition
139 Total Sources all records from SourceTable
2 – Unused Sources* SourceTable records unused by CitationTable
1697 Total Citations all records from CitationTable
0 – Duplicate Citations identical 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
12 Repositories all records from AddressTable of type Repository
36 To- do tasks all records from ResearchTable
123 Multimedia items all records from MultimediaTable
12 – lacking thumbnail probably an imported reference to an image file that has yet to be found
0 – duplicate multimedia filenames probably having different paths
158 Multimedia links all 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 links image appears multiple times for person, fact
20 Addresses all records from AddressTable of type Address
0 – blank names Name field of AddressTable record is blank
0 Correspondence all records from ResearchTable of type Correspondence
  • NOT TRANSFERABLE
via GEDCOM or Drag&Drop to another RM database

Discussions & comments from Wikispaces site


ve3meo

Tracing the “Headless Witnesses”

ve3meo
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
EXCEPT SELECT PersonID FROM PersonTable
;

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


ve3meo

Leave a Reply

Your email address will not be published. Required fields are marked *