SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Bundled Utilities – Groupings #application

Posted on by Tom Holden

This page is intended to group existing SQLite queries and future functions
under a menu structure that parallels that of RootsMagic itself, with the
idea that similar naming, order and, possibly, locations on screen may
facilitate user navigation to the utilities most relevant to their area of
interest or concern. This is a first draft.
RMutil-Menu.xlsx

Rmutils (working name) January 10, 2012
Main Menu 5:55 PM
SQLiteToolsForRootsMagic query or other description
Sort Main Sub1 Sub2 Query Name Description Page Comment
1

File
1.1 File Open Open Windows Explorer browser on *.rmgc files. Get database version, possibly do SQLite Quick Integrity Check
“PRAGMA quick_check(integer);”
1.2 File Open Recent
1.3 File Search for Files
1.4 File Close
1.5 File Copy
1.5.1 File Copy All
1.5.2 File Copy Less People CustomDatabaseShell Makes an empty Master from current database, preserving custom
fact types and source templates, Places, Master Sources and
associated Repositories and Addresses, and Place/Source Media		 Depopulate
but keep Customs, Places, Sources
1.5.3 File Copy To vanilla DB3 RM#_CREATE_as_DB3_NOCASE | RM#_Copy_Data_to_DB3_NOCASE Clones a RM4/5 database without the RMNOCASE collation so that
we’re free to do what we want to the data. Even works with RM but it
might trip up on non-English alphabets.		 Convert
Database to NOCASE
1.6 File Database tools…
1.6.1 File Integrity check Check RootsMagic Database Integrity Explains why an integrity check is needed and how, using PRAGMA
quick_check.		 Check
RootsMagic Database Integrity
1.6.1 File Integrity check PRAGMA integrity_check(integer);
1.6.2 File Reindex REINDEX
1.6.3 File Clean TBD Deletes unused rows from tables as reported by RMGC_Properties.
May need checkbox options to select what tables are cleaned. RM4
& 5 are different		 Conceivably, Properties & Clean could be one form with
checkboxes for tables that have unused rows & a Clean button
1.6.3a File Clean Delete Phantom Citations Deletes citations of non-existent Sources from database that
manifest themselves in the Edit Persons screen as a citation
(counted and checkmarked) but return nothing when opened.		 Delete
Phantom Citations – Query
1.6.4 File Compact VACUUM
1.7 File Export TBD possible route to a control of Fact settings for GEDCOM export
1.8 File Backup…
1.8.1 File Database file only
1.8.2 File Include media RMfullbackup.bat Backs up the database file along with all the media files
referenced by it to one file. Uses the command versions of SQLite3
(free), WinRAR ($fee) and 7-Zip (free). The 7-Zip version produces a
.rmgb file that RootsMagic can restore, media and database.		 Backup
Media with Database – RAR
1.8.2 File Include media RMfullbackup.bat Backs up the database file along with all the media files
referenced by it to one file. Uses the command versions of SQLite3
(free), WinRAR ($fee) and 7-Zip (free). The 7-Zip version produces a
.rmgb file that RootsMagic can restore, media and database.		 Backup
Media with Database – 7Zip		 If there is Backup, should there be Restore from within Rmpotions?
1.9 File Properties Database Properties Reports in a list most of the values found in RM4’s File >
Properties report; results can be exported to a file.		 Database
Properties List – Query
1.9 File Properties RMGC_Properties As ‘Database Properties’ but with more detail and pointing out
possible problem areas		 RMGC_Properties
– Query		 Variant for RM5 TBD
1.9 File Properties RMGC_TablesRowCount Lists count of rows in each table — If not 22 tables listed,
database has corruption		 RMGC Tables
Row Count
1.A File Exit
2

Edit
2.1 Edit Person
2.1.1 Edit Person Birth Year BirthYearMisMatch Lists Individuals whose Birth Year from NameTable does not match
that of the date for their Birth fact.		 Birth Year
Mis-Match
2.1.2 Edit Person Death Year DeathYearMismatch Lists Individuals whose Death Year from NameTable does not match
that of the date for their Death fact.		 Death Year
Mismatch
2.1.3a Edit Person Set Living SetLivingFlag Another version of a Set Living query. Another
version of a Set Living query
2.1.3b Edit Person Set Living SetLivingFlag A discussion of queries that can modify the Living flag. Set Living Flag
2.1.4 Edit Person Same day sort order SortDateSameDayOrder | SortDateSameDayOrderCustom Re-orders all same day events of limited types to a natural order,
e.g., Birth before Death		 Dates –
Same Day Sort Order
2.1.5 Edit Person Copy fact to group CopyFact2Group Copies a fact/event for a person to a Named Group of persons,
along with the Sources but not the Media for the Fact.		 Copy Fact to
Group
2.1.6 Edit Person Copy RIN to REFN CopyRINtoREFN Variant of CopyFact2Group that copies a REFN fact to a group,
substituting the target persons’ PersonID’s or RIN for that of the
source person.		 Copy RIN to REFN
2.1.7 Edit Person Convert sharable facts SharableFacts2 | SharableEvents-Convert List and convert events having key properties in common to shared
events. Especially useful on imports from Legacy Family Tree in
which events were copied to multiple persons.		 Sharable
Events – Find and Convert to Shared
3

Lists
3.1 Lists Source List
3.1.1 Lists Source List Source List Source List About as close as we can get to a RM4 Source List Report presented
in tabular form with which the results can be sorted and filtered
with relative ease.		 Source List Query
3.1.2 Lists Source List Source Details Source Detail View (Parsing XML) Lists Source Names along with Source Detail field names;
illustrates principles applicable to Master Source fields and other
XML-like columns.		 Source
Detail View (Parsing XML)
3.1.3 Lists Source List Citations AllCitations Lists all citations in the database from which citations of
non-existent sources (‘phantoms’) and citations for non-existent
events or persons (‘headless’) can be found, along with other useful
information such as all citations per source.		 All Citations –
Query
3.1.3 Lists Source List Citations AllCitations+Dupes Lists all source names cited for a person in descending order of
the count of duplicate citations – a help in finding and resolving
duplicate citations after merging.		 All
Citations & Dupes Count – Query
3.2 Lists To-Do List
3.2 Lists To-Do List To-Do (was To-Do4Persons) Lists uncompleted To Do tasks for Individuals, Families and
General; can be readily modified to filter or sort by surname,
priority, repository, etc.		 RM4 To Do List
3.3 Lists Research Manager
3.4 Lists Media Gallery
3.4.1 Lists Media Gallery Duplicates MediaRepair Series of queries to list duplicate media file names in the Media
Gallery and list duplicate links to items in the Gallery. Repairs a
specific case of duplicate file names as an example; repairs all
cases of duplicate links.		 Media Repair
Queries		 Needs rev for RM5
3.4.2 Lists Media Gallery List uses Media Users List Complete listing of all users of media in the Gallery, more
complete and navigable than what is provided in RM 4.1.2.1		 Media Users
List Query		 Needs rev for RM5
3.4.3 Lists Media Gallery Files usage ScrapBookFilesStatus Lists files under the RM Multimedia default folder and flags those
used by the RM database; helps to ensure that files are used.		 Scrapbook
Files Status
3.5 Lists Address List
3.5.1 Lists Address List Blank Names blankname_in_addresslist List Persons with Blank Names in the Address List — (a fault that
may occur in a GEDCOM import).		 Four Little
Queries
3.6 Lists Repository Manager
3.7 Lists Correspondence List
3.8 Lists Place List
3.8.1 Lists Place List Unused Places UnusedPlaces List of unused Places Four Little
Queries
3.8.2 Lists Place List Place Details PlacesDetails Lists Places having Place Details Four Little
Queries
3.8.3 Lists Place List Convert to Place Details Places to Place Details Conversion Combo of SQLite queries and RM4 edits of Place List beats having
to edit every fact/event to split a Place into Place and Place
detail; otherwise, wait for the RootsMagician!		 Places
to Place Details Conversion
3.8.4 Lists Place List Place Details with Facts FactsHavingPlaceDetails3 Returns all Places with Place Details and the Persons or Families
(couples) and their Facts, including Shared events, using the Place
Detail.		 Facts
Having Place Details – Query
3.9 Lists Fact Type List Manage, save, apply Fact settings for Export, Reports, Privacy
3.A Lists Source Templates
3.A.1 Lists Source Templates Sources by Template SourceTemplateListWithCitationDetails2.sql Lists Sources associated with Source Templates, as well as an
extension to Citation details. Free-Form templates are excluded
since they’re not included in SourceTemplateTable.		 Source
Template List – Query
3.A.2 Lists Source Templates Make Editable SrcTmpltsConvert Converts Sources using uneditable, built-in templates to using
editable copies.		 Source Templates
3.A.3 Lists Source Templates Revert to Built-in SrcTmpltsRevert Reverts Sources modified by SrcTmpltsConvert to using uneditable,
built-in templates.		 “
6

Search
6.1 Search Name NameFind Produces a list of names that match or sound like specified names,
similar to RM4’s NameFind.		 Name Find query
6.2 Search Surname selected_surnames List Persons with specified Surnames. — Example of creating a SQL
View or Virtual Table and the explicit use of COLLATE NOCASE to
override the RMNOCASE collation defined for certain fields and
embedded in the RootsMagic application.		 Four Little
Queries
6.3 Search Search & Replace Search&Replace Discussion and examples of how you can search (filter) using any
SQLite manager and replace found values with revised ones using
SQLiteSpy.		 Search &
Replace
7

Reports
7.1 Reports Format
7.1.1 Reports Format Paragraphing Paragraph-Strip | Paragraph-Add Removes CR/LF characters around custom fact sentences and
person/family/alt name/fact notes and adds CR/LF pairs to ends of
notes except the last, for first cut batch paragraphing.		 Paragraphing
7.1.2 Reports Format Strip footnote line feeds Fix Extra Line Feeds in Footnote This query strips extraneous Carriage Returns from the end of the
Footnote sentence template for the Source Template “Vital Records
(state-level, online derivatives)” which cause unwanted white space
in reports.		 Source Templates
7.2 Reports My Lists
7.2 Reports My Lists All facts AllFacts4Persons List all the Individual, Family and Shared Facts/Events for all
persons in a database. A complex query using UNION ALL, COUNT() and
GROUP BY, constants and NULL to assemble multiple SELECTs in one big
result.		 Pulling
Together All the Events for An Individual
7.2 Reports My Lists Begatters PersonsBegatChildren An optional extension to AllFacts4Persons or standalone. Having a
Child as an event for the Father and the Mother.		 Births
of children as facts
7.2 Reports My Lists Direct Ancestors Ancestors Lists all the ancestral lines for a given RIN Ancestors Query
7.2 Reports My Lists DNA DNA_mtDNA_locationslist | DNA_Y-STR_markerslist Two queries that list the test results in a format suitable for
easy review and copying and pasting into other applications.
RM4.1.1.4 provides no DNA report.		 DNA Test
results list
7.2 Reports My Lists Facts shared with persons in database People Who Share a Fact with a Principal Lists people who share a fact with a Principal, as well as
relevant fact and Principal information.		 People
Who Share a Fact with a Principal List – Query
7.2 Reports My Lists Facts shared with persons NOT in database People Who Share a Fact with a Principal, But Who Are Not in a
Tree in the File		 Lists people who share a fact with a Principal, but who aren’t in
a tree in the database file, as well as relevant fact and Principal
information.		 People
Who Share a Fact with a Principal, But Who Are Not in a Tree in
the File List – Query
7.2 Reports My Lists Geo-Lifelines Geo-Lifelines Have you ever wished to be able to look at all the facts in your
family tree database that happened within a day’s horseride of a
certain location? This adaptation of the LifeLines query helps you
view your events for any geographic area in addition to looking at
the lifeline of any person in your database.		 Geo-Lifelines
Query
7.2 Reports My Lists LifeLines LifeLines Lists all events for all persons whether in a database tree or
not, including shared facts, date, fact detail, site and place,
MRIN, other parties, and duplication indicator. Sorted by RIN and
Sort Date. With the right SQLite manager, can filter results for one
person.		 LifeLines – Query
7.2 Reports My Lists Map Events (KML) MapEvents-KML This query helps you plot events from your RootsMagic database on
Google Maps, Google Earth, and Bing Maps, provided there are
geo-coded Places and Place Details (sites) in your database.		 MapEvents-KML
query
7.2 Reports My Lists Multiple spouses Multi-spouses Lists persons with multiple spouses in descending number – may
flag a data problem		 Multiple
Spouses query
7.2 Reports My Lists Nameless witnesses to shared facts TraceHeadlessWitnesses Report on the role, event and owner or principal of the shared
event for which a witness or sharee is no longer a person in the
database.		 Shared
Events With Missing Witnesses
7.2 Reports My Lists To-Do Grid MyToDoList A Query for a To Do List in a Grid Format – using the REFN to
assign status for paragraphing and census. Versions for both SQLite
directly and MS Access via SQLODBC.		 A
Query for a To Do List in a Grid Format
8

Tools

 Hmm Tools within a Tool
8.1.1 Tools Merge Fast Duplicate Name Search DuplicateNameSearch Lists duplicate name pairs with a weighted score indicating degree
of match. On a large database, produces results in 4.5 minutes
similar to what RootsMagic 4’s Duplicate Search Merge tool does in
45 min.		 Duplicate
Name Search – query
8.1.2 Tools Merge Mark unmerged not a problem MarkNotProblem Copies all unmerged pairs from the results of RM4’s Duplicate
Search Merge Tool to RM4’s ExclusionTable so that they will not
reappear in subsequent runs of Duplicate Search Merge.		 Duplicate
Search Merge Database
G

Groups

 Refresh – various Top page for a collection of queries for refreshing Named Groups Named Group
Refresh
G.1 Groups Ancestors AncestorsGroup Provides a manual refresh for a specific Named Group, i.e., the
ancestors of a specified person.		 Ancestors
Named Group
G.2 Groups Census needed CensusNeededGroup | CensusNeededGroup2 Builds or refreshes a group of persons whose lifetime probably
spanned a user-defined Census Year and who had some event in the
user-defined jurisdiction but not a Census fact for that year.		 Census
Needed – Named Group
G.3 Groups Mark/Unmark Lists Group Unmark List Refresh | Group Mark List Refresh Marks or Unmarks members of a group according to a list;
equivalent to memorising the checkboxes in RootsMagic Explorer for
re-use.		 Named
Group – Mark or Unmark List refresh
zFn

functions

 DateDecoder Decodes most of the possible formats found in RM4 Date fields of
the form Da+nnnnnnnn.x+nnnnnnnn.x		 Date Decoder encode as SQLite function
zFn functions SortDateEncodeDecode for encoding single, pure dates and decoding corresponding
SortDates (no modifiers yet like bef, aft)		 Dates –
SortDate Algorithm		 encode as SQLite function

Discussions & comments from Wikispaces site


LessTX

Functions by Table modified

LessTX
11
January 2012 15:37:45
Warning: I don’t know what all of the current queries are, I
haven’t taken the time to look at the long list of stuff, it’s
currently too long and unstructure to make me believe that
it’s worth my time. In that, I’m probably like most folks who
come here, and am the kind of person this project is intended
to help.
Just be aware, my suggestions have NO relationship to whatever
work has already been done, just a relationship to my prior
program (TMG) and what I needed from a utility program, and
seem to still need from an RM utils program.
The utility program itself should have the standard
File/Edit/View/Tools/Help structure.
Users will come to the RMUtils program why? To either GET INFO
from their database or to CHANGE their database.
The pertinent areas are:
names, events, places, sources, repositories, media, others?
I absolutely see a structure similar to that of TMG being the
most intuitive for users.

ve3meo

ve3meo
21
January 2012 03:40:31

I have activated a Query Menu in the sidebar linking to a
new Query Menu page providing something of a structure that
hopefully helps newcomers navigate to a query of interest.

Leave a Reply

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