RMGC_Properties+-+Query

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. 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
 * 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.**

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

=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 ||