Logo Home    Page list

RMGC_Properties - Query #databaseproperties #subqueries #integrity

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

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.
[[code format="SQL"]] -- List of RINs of Headless Witnesses
SELECT PersonID FROM WitnessTable WHERE WitnessTable.PersonID > 0
EXCEPT SELECT PersonID FROM PersonTable
;code
A more complex query could look up the persons in the database currently owning (the Principal role) or sharing the event.

ve3meo
ve3meo 07 December 2011 17:17:24

Sorry, misformatted...
[[code format="SQL"]]
-- List of RINs of Headless Witnesses
SELECT PersonID FROM WitnessTable WHERE WitnessTable.PersonID > 0
EXCEPT SELECT PersonID FROM PersonTable
;
code

www.000webhost.com