Search – Find Almost Everywhere #search #fts

RootsMagic 6 has a very powerful search tool under the main menu item Search > Find Everywhere… However, it really falls short of everywhere, omitting a number of tables and fields within the tables it does search. Notably, it omits the:

  • AddressTable (Repositories, Contacts, Correspondence)
  • FactTypeTable (Fact Types)
  • SourceTemplateTable (Source Templates)
  • URLTable (WebTags)
  • WitnessTable (non-Principals of shared events)

This family of scripts query searches these tables in addition to all other tables which have fields into which the user may enter text. RootsMagic’s Find Everywhere has the advantage of searching within expanded sentence templates for events and footnotes and hyperlinking to the editable field in which the search term found a match. These Find Almost Everywhere queries cannot expand sentence templates and hyperlink but have wider scope; their results give guidance to the power SQLite user to locate the table and record within which its search term found a match. Moreover, their search expressions can be more complex and powerful. However, for user-friendliness, RootsMagic’s implementation is very superior. Also see Search – wayfinding from data tables to RootsMagic screens.

Requirements

SQLite Expert Personal with unifuzz.dll extension for fake RMNOCASE or an equivalent which also supports entry of the search term at run-time and the FTS4 extension.

Usage

  1. Open the RootsMagic database in the SQLite manager.
  2. Load the SQL script into a SQL tab and execute (F5 in SQLite Expert).
  3. Enter your search term(s) in the Missing Parameter window.
    Find_Almost_Everywhere_Parameter.png
    Entry of search term for Find Almost Everywhere query.
  4. Search results will be displayed in the results pane.
  5. If you find that re-executing the script is time-consuming, for further queries, select/highlight the SELECT statement near the bottom of the script and execute it alone. What takes time in large databases is the building of the virtual table of texts that is required for full text searches.
  6. If you edit a cell using the SQLite Manager, be sure to use RootsMagic’s Rebuild Indexes tool when you return to working with it.
FindAlmostEverywhereResults.PNG
Sample result from Find Almost Everywhere Simple Search query. One can go directly to the identified rows in the MultiMediaTable to examine further or edit directly using the SQLite manager OR use the clues to find the item in RootsMagic’s Media Gallery.

Downloads

FindAlmostEverywhere.sqlBuilds Full Text Search table and index and provides a simple search.
rev 2014-10-23 made FTS table temp for duration of SQLite session
rev 2014-10-24 replaced collective name like “Various” with explicit field names in FieldName column
FindAlmostEverywhere-Build_FTS_table.sql2014-10-23 Split from above to Build Full Text Search table and index only. Use one of the following separate scripts to Search so table is not rebuilt every time.
FindAlmostEverywhere-Search_Simple.sql2014-10-23 Simple Search split out from FindAlmostEveryWhere.
FindAlmostEverywhere-Search_LocateQuery.sql2014-10-23 Search and Locate returns the content having a match along with a SQLite statement which can be copied to the SQLite editor and executed to open and edit the actual cell.
rev 2014-10-24 ROWID added to the generated SELECT; highlighted snippet instead of full content.
FindAlmostEverywhere-Search_Locate-Results.PNG
A result from FindAlmostEverywhere-Search_LocateQuery.sql on the search term “marr”. Copy the SELECT statement from the LocateQuery column to the SQLite manager and execute to open and edit that cell directly.
FindAlmostEverywhere-Search_LocateQuery-result of LocateQuery.PNG
Result of 2nd LocateQuery from above. As this query presents the raw fields from but one table, the fields can be edited. Select a cell and press F2. However, be very careful; it is safer to edit from within RootsMagic.

Search Terms Syntax

Find Almost Everywhere uses the SQLite 3 FTS4 extension for Full Text Search with its MATCH operator working through the index of the virtual table the script creates from the database. The syntax of the <full text search expression> you may enter as the value for the run-time parameter $SearchTerm is described in the section Full text Index Queries at http://www.sqlite.org/fts3.html but excluding §3.2. Set Operations Using The Standard Query Syntax.
In summary (the <…> brackets are not to be entered):

  • case insensitive except operators MUST be capitalised, else they are just terms
  • wildcard character: * , e.g., <joan*> matches Joan, Joanne, Joanie, Joannie
  • phrase: enclose in double quotes, e.g., <“Alexander Barnes”>
  • NEAR, e.g., <sql* NEAR query> matches “SQLite database query”, “sql is a powerful query”… The distance between the matching terms is by default a maximum of 10 intervening terms but can be overridden as NEAR/n where the integer n sets the maximum distance, e.g., NEAR/2 in the foregoing expression matches only the first of the two examples.
  • NOT, AND, OR, operators in the foregoing default order of precedence which can be overriden by grouping in parentheses, e.g., <(carrie OR lula) AND barnes>
  • implicit AND: <carrie barnes> is the same as <carrie AND barnes>
  • narrowed scope: prefixing the term with the name of the virtual table column restricts the match for that term to that column; normally, the search traverses all columns. <FieldName:description> restricts matches for the term “description” to the column named “FieldName”.

Alternative Structures for the Virtual Table

The FTS MATCH operator looks across all columns of the virtual table for a match to its expression, i.e., one term can match in one column, another term can match in another column; the expression need not be matched by any one column unless prefixing with the column name forces it to do so. Any combination of sub-matches in different columns can result in a full match. Given a source table having multiple text columns, one can choose a vertical or horizontal layout of the virtual table or some hybrid of the two. For example, the WitnessTable:

Vertical layout

ContentFieldNameTableNameRownum
ghjgSentenceCitationTable7
yyoyuNoteCitationTable7
dfhdGivenCitationTable7
tyitSurnameCitationTable7
dfghdPrefixCitationTable7
yuoySuffixCitationTable7

Horizontal layout

SentenceNoteGivenSurnamePrefixSuffixTableNameRownum
ghjgyyoyudfhdtyitdfghdyuoyCitationTable7

Hybrid layout

This is the hybrid layout used by the Find Almost Everywhere script because it started from the script in TMG-RootsMagic Sentence Variables & Format Codes.

ContentFieldNameTableNameRownum
ghjgSentenceCitationTable7
yyoyuNoteCitationTable7
dfghd dfhd tyit yuoyextWitnessCitationTable7

“extWitness” = Prefix Given Surname Suffix)

Comparisons

The vertical layout cannot make a combination match among the fields from the WitnessTable because they are in separate rows in the virtual table. It is inherently a narrow search. The horizontal layout is the widest potential search for a match from the WitnessTable because sub-matches can be made among all its text fields. The hybrid was built on the vertical layout by concatenating the name fields for the external Witness (person not in the database) so it can match in the Sentence, Note OR extWitness content but not in a combination of these.

Further Considerations

This script populates the Content column with values from one table at a time. Search scope could be widened by bringing in content from a JOINed table, e.g., CitationTable and SourceTable.

Another possibility is to link the search results via the TableName and Rownum values to more helpful guides to the Person, Event, Citation etc. That is where the RootsMagic implementation shines with its hyperlinking straight from the result to the corresponding edit screen. See Search – wayfinding from data tables to RootsMagic screens for a FTS query that provides waymarks to help navigate to the RootsMagic screen that controls the data in the matching fields.

Discussions & comments from Wikispaces site


Geraniums

RMNOCASE question

Geraniums
23 October 2014 12:13:46

Hi, I was going to try the FindAlmostEverywhere search tool and installed SQLite Expert Personal 3.5.60.2480. I loaded a RootsMagic 6 database, then the FindAlmostEverwhere.sql. After clicking the Execute SQL button in the SQL tab, a message came up that said, “An error occurred. no such collation sequence: RMNOCASE” etc.

I have downloaded the file RMNOCASE_fake-SQLiteSpy.dll
and it’s in the same folder as the SQLiteExpertPersSetup.exe file.

In SQLite Expert Personal, under Tools > Options, I don’t see where I can register the extension RMNOCASE_fake-SQLiteSpy.dll

Hoping someone can help. Thanks


ve3meo

ve3meo
23 October 2014 12:19:22

The extension you want is unifuzz.dll, see RMNOCASE – faking it in SQLite Expert, command-line shell et al

Tom


Geraniums

Search questions

Geraniums
23 October 2014 15:39:46

1) After getting search results, is there a way to determine which person in RM6 that the record refers to?

2) In the directions for using the search tool it says, “If you find that re-executing the script is time-consuming, for further queries, select/highlight the SELECT statement near the bottom of the script and execute it alone.” I can’t see the SELECT statement on the SQLite Expert Personal screen.

3) When a record is selected, if I type into the Content field, will this change the content in RootsMagic6?

Thanks


ve3meo

ve3meo
23 October 2014 16:46:51

1) Yes, if you know your way around the database. For example (and perhaps a trivial one), the search result indicates a match in the Note field of EventTable at Rownum 1234. Click on EventTable in the sidebar and the Data tab in the main view. Scroll down to RecNo/EventID 1234 OR click on the row “Click here to define filter” and enter “1234” under either the RecNo or EventID heading. Look under OwnerType and OwnerID. If OwnerType=0 then OwnerID is the Person with that RIN and you can look them up in RootsMagic Explorer (Ctrl-F, Alt-R). If OwnerType=1, then the OwnerID is the FamilyID in FamilyTable and you will need to look in it to get the RINs of the couple.

I have some intention of providing a more user-friendly result to save much of that manual navigation…

2) Click on the SQL tab. Pull down the divider between the SQL pane and the results pane to expose more of the script and use the scroll slider to get to the bottom of the script (or use other Windows techniques). Look for:
—–repeat the following query as often as you want—–
—————- Full Text Search query —————–
SELECT * FROM xFTStable WHERE xFTStable MATCH $SearchTerm
;

3) No, you cannot edit the results of a query. You can edit a cell displayed under the Data tab. In our example above, having found EventID 1234 in EventTable, you can double-click on any cell in that row to open the Record Editor. Scroll down to the Note heading which will have 4 buttons Auto|Blob|Image|Text. Click on Text to edit the Note.

Tom


ringozmy

Person Search According to the Place of Residence

ringozmy
28 June 2017 23:59:22

Hello,

I have a list of 10K individuals and their place of residence, and I would like to retrieve their personal information (gender, race, job, age, etc.) from 19-century US censuses. Can I do it through SQLite for RM? Could you give some suggestions? Thanks!


ve3meo

ve3meo
29 June 2017 01:31:34

Do you have in your database Residence, Race, Occupation and any other facts you wish to retrieve for these people?Or are they just in the Citation text for the person and the citation is tagged to a Census event?

Search – wayfinding from data tables to RootsMagic screens #database #fts #search #rm8

Update 2021-03-11: added version by Pat Jones for RM8.

This page brings together a set of scripts that:

  • adds ‘friendly’ Waymarks in views of each database table to aid in navigating through RootsMagic to the responsible screen and to help understand the role of each table
  • builds a Full Text Search table, first described on the page Search – Find Almost Everywhere
  • launches a Full Text Search with results combining the ‘friendly’ Waymarks and a SQLite statement that opens the table row with the matching cell for possible editing
Waymarks.PNG
Sample of WitnessWay, the temporary view of the WitnessTable with Waymarks added to facilitate navigation to the sharee in RootsMagic.
Each RootsMagic table is replicated in a temporary View named the same except ending with “Way” instead of “Table”. Hence, “WitnessWay” is the wayfinding view of “WitnessTable”. To the right, in this screenshot snippet of the SQLite Expert sidebar, we see:

  • the last three RootsMagic tables, below which are
  • the temporary virtual table for Full Text Search and
  • a one record temporary table that stores the Search Term you enter. Below these are
  • the first few wayfinding views, which, when opened, will show the Waymarks as the first column, followed by all the columns of the table being viewed, as in the WitnessWay screenshot above.

The Full Text Search query returns:

  • a snippet of the text with the match to the search term(s) highlighted by the double-asterisks (**),
  • the Waymarks to help find the snippet in RootsMagic,
  • the field names included in the block of text in which the match was found plus
  • a simple SQL query.

This query can be copied to the SQL editor and executed; then the SQLite Expert editing tool can be used to edit these fields and others in the database table it opens.

WayfindViews.PNG
Snippet from SQLite Expert sidebar showing the Wayfinder views.
Wayfinder_FTS_LocateQuery.PNG
Sample result from the Full Text Search on the term “clerk”.
WaymarksFollowed.PNG
Waymarks for result #3 in the search were followed to this screen snip from the RootsMagic Place List.

Hopefully, the Waymarks list is self-explanatory and can be easily followed through RootsMagic. Editing the database is more safely done in RootsMagic but there are circumstances which can only be addressed through SQLite or the user may prefer doing through SQLite. You are reading this because there is something you want to do with your RootsMagic database that you cannot do with RootsMagic itself!

LocateQuery.PNG
The LocateQuery for search result #3 was copied and executed giving this screen. The fields without the “_1” suffix can be edited (take all necessary precautions). With SQLite Expert, right-click on the field and select Text Editor.
EditTextBlob.PNG
Note and other fields designed for large amounts of text are stored as binary BLOBs and are so indicated by the … icon. The SQLite Expert text editor works with them, too.
SQLiteExpertEditingControls.PNG
The Post Edit button is key to affecting the database.

If you do want to edit your RootsMagic database with SQLite Expert, please read its Help pages under the topics:

  • Editing table data
  • Editing live queries

Always run the SQLite Database Tools before and after you make changes externally and make a backup before, too.

While there are several ways to start an edit, making the changes stick in the database requires a click of the Post edit button, enabled when a change has been made to an onscreen field.

Downloads

ScriptHistorySQLite Requirements
1aRM8_WaymarksViews.sql
Creates the Wayfinding views of the RM8 database tables
2021-03-11 added by Pat Jones for #RM8RMNOCASE
1bRM7_5_WaymarksViews.sql
Creates the Wayfinding views of the RM7.5 database tables.
2017-07-01 extended for RM7.5 and corrected bug for null spouse
2019-02-12 corrected omission of famiies from EventWay and dependent views
RMNOCASE
2FindAlmostEverywhere-Build_FTS_table.sql
Creates the FTS4 virtual table xFTStable for full-text searches.
RMNOCASE, FTS4
3FindAlmostEverywhere-Snippet_Waymarks_LocateQuery.sql
Stores user inputted search terms in xSearchTerm,
searches xFTStable and returns snippets, Waymarks,
field names and the SQL queries to examine the matching
records.
RMNOCASE, FTS4, runtime-parameters
SQLite Expert Personal qualifies

Usage

Script #3 is dependent on the execution of both #1 and #2 preceding its running. #1 and #2 are independent of any other script.

On very large databases, scripts 2 and 3 may consume tens of seconds. In one example 40 and 30 seconds, respectively. However, RootsMagic’s Find EveryWhere took over 180 seconds for the same search and does so for each new search. Script 2 need not be re-executed for each new search by script 3 so there is a distinct time advantage.

All the views and tables created by these scripts are temporary, in-memory and are destroyed when the SQLite manager is closed.