RMGC_Properties – Query #database

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.sql
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	of records from SourceTemplateTable
416	– Custom Source Templates	of custom Source Templates
0	– Unupdated Built-in SourceTemplates	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