Search+-+Find+Almost+Everywhere

toc 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= =Downloads= 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 || rev 2014-10-24 ROWID added to the generated SELECT; highlighted snippet instead of full content. ||
 * 1) Open the RootsMagic database in the SQLite manager.
 * 2) [[image:Find_Almost_Everywhere_Parameter.png align="right" caption="Entry of search term for Find Almost Everywhere query. "]]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.
 * 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.
 * [[file:FindAlmostEverywhere.sql]] || Builds Full Text Search table and index and provides a simple search.
 * [[file:FindAlmostEverywhere-Build_FTS_table.sql]] || 2014-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. ||
 * [[file:FindAlmostEverywhere-Search_Simple.sql]] || 2014-10-23 Simple Search split out from FindAlmostEveryWhere. ||
 * [[file:FindAlmostEverywhere-Search_LocateQuery.sql]] || 2014-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.

=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  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): =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:
 * case insensitive except operators MUST be capitalised, else they are just terms
 * wildcard character: *, e.g.,  matches Joan, Joanne, Joanie, Joannie
 * phrase: enclose in double quotes, e.g., <"Alexander Barnes">
 * NEAR, e.g.,  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: is the same as 
 * 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.  restricts matches for the term "description" to the column named "FieldName".

Vertical layout

 * ~ Content ||~ FieldName ||~ TableName ||~ Rownum ||
 * ghjg || Sentence || CitationTable ||> 7 ||
 * yyoyu || Note || CitationTable ||> 7 ||
 * dfhd || Given || CitationTable ||> 7 ||
 * tyit || Surname || CitationTable ||> 7 ||
 * dfghd || Prefix || CitationTable ||> 7 ||
 * yuoy || Suffix || CitationTable ||> 7 ||

Horizontal layout

 * ~ Sentence ||~ Note ||~ Given ||~ Surname ||~ Prefix ||~ Suffix ||~ TableName ||~ Rownum ||
 * ghjg || yyoyu || dfhd || tyit || dfghd || yuoy || CitationTable ||> 7 ||

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. "extWitness" = Prefix Given Surname Suffix)
 * ~ Content ||~ FieldName ||~ TableName ||~ Rownum ||
 * ghjg || Sentence || CitationTable ||> 7 ||
 * yyoyu || Note || CitationTable ||> 7 ||
 * dfghd dfhd tyit yuoy || extWitness || CitationTable ||> 7 ||

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.