-- FindAlmostEverywhere-Snippet_Waymarks_LocateQuery.sql
/*
2014-10-27 Tom Holden ve3meo

Full text search query on the FTS table "xFTStable" built by the script
 FindAlmostEverywhere-Build_FTS_table.sql from most user-editable text fields
 in the current RootsMagic database. 

It pops up a window for the search term to be entered. This term can be simple 
 or complicated with options described at
 http://www.sqlite.org/fts3.html#section_3. 
It returns the content of the field matching the search which cannot be edited 
so it also returns a SQL query that can be copied into the SQL editor and
executed. That result can be edited. Note that the FTS table will not reflect 
changes until it is rebuilt. AND it returns a trail to follow in RootsMagic
to inspect and edit it there.

Different search queries can be run without rebuilding the FTS table.

Requires SQLite Expert Personal with unifuzz.dll extension for fake RMNOCASE collation
 or equivalent SQLite manager with support for runtime parameters. 

Usage:
  1. Open database with SQLite Expert Personal and ensure extension unifuzz.dll is
  loaded (right-click on database name and select Unload Extension to see all loaded)
  2. Load/execute SQL script RM6_WaymarksViews.sql (needed only once as long as 
  database remains open in SQLite Expert)
  3. Load/execute SQL script FindAlmostEverywhere-Build_FTS_table.sql (re-execute  
  after changes to data)  
  4. Load/execute this script to carry out a search (repeat as needed)  
  5. To edit data, follow the Waymarks through RootsMagic. OR, for the adventuresome,  
  copy the LocateQuery to a SQL editor page and execute; use the SQLite Expert  
  editing capability - the first field(s) to the left of the Primary Key field 
  contain a match to the search term. Fields named with the "_1" suffix are  
  duplicates and cannot be edited.

*/

DROP TABLE IF EXISTS xSearchTerm
;

CREATE TEMP TABLE xSearchTerm
AS 
SELECT $SearchTerm AS SearchTerm
;

DROP VIEW IF EXISTS xSearchResults
;

CREATE TEMP VIEW xSearchResults
AS
SELECT *,  snippet(xFTStable, '**', '**', '...') AS [snippet]
 , 'SELECT '||FieldName||', * FROM '||TableName||' WHERE ROWID = '||Rownum||';' AS "LocateQuery" 
 
FROM xFTStable 
WHERE xFTStable MATCH (SELECT SearchTerm FROM xSearchTerm)
;

SELECT
 snippet 
 , CASE TableName 
-- WHEN 'AddressLinkTable' THEN -- Unsearched; is Details field used? Yes, Call Number from Repositories screen, OwnerType 3
   WHEN 'AddressTable' THEN   
     'Lists > ' || (SELECT Waymarks FROM temp.AddressWay WHERE Rownum = AddressID)   
-- WHEN 'ChildTable' THEN -- Note is unused so nothing searched.
   WHEN 'CitationTable' THEN   
     (SELECT Waymarks FROM temp.CitationWay WHERE Rownum = CitationID)   
   WHEN 'EventTable' THEN
     (SELECT Waymarks FROM temp.EventWay WHERE Rownum = EventID)     
   WHEN 'FactTypeTable' THEN   
      'Lists > ' || (SELECT Waymarks FROM FactTypeWay WHERE Rownum = FactTypeID)      
   WHEN 'FamilyTable' THEN   
     (SELECT Waymarks FROM temp.FamilyWay WHERE Rownum = FamilyID)
-- WHEN 'GroupTable' THEN  -- unsearched so far
   WHEN 'LabelTable' THEN   
     'Sidebar > Groups' || CHAR(13) || (SELECT Waymarks FROM temp.[LabelWay] WHERE Rownum = LabelID)
   WHEN 'LinkTable' THEN    
     (SELECT extSysNam || CHAR(13) || Waymarks FROM temp.LinkWay WHERE Rownum = LinkID) 
   WHEN 'MediaLinkTable' THEN -- MediaTags
     'Lists > '  || (SELECT List || CHAR(13) || Tag FROM MediaLinkWay WHERE Rownum = LinkID)  
   WHEN 'MultiMediaTable' THEN    
     'Lists > '  || (SELECT Waymarks FROM MultimediaWay WHERE Rownum = MediaID)
   WHEN 'NameTable' THEN    
     (SELECT CASE WHEN IsPrimary THEN 'Name: ' ELSE 'Alt Name: ' END || Person FROM temp.NameWay WHERE Rownum = NameID)  
   WHEN 'PersonTable' THEN   
     (SELECT Person FROM temp.NameWay NW WHERE Rownum = NW.OwnerID)
   WHEN 'PlaceTable' THEN   
     'Lists > Place List' || CHAR(13) || ' ' || (SELECT Place FROM temp.PlaceWay WHERE Rownum = PlaceID)
   WHEN 'ResearchItemTable' THEN -- TBD Research Logs
     'Lists > Research Manager' || CHAR(13) || (SELECT Waymarks FROM temp.ResearchItemWay WHERE Rownum = ItemID) 
   WHEN 'ResearchTable' THEN -- TO DO and Correspondence
     'Lists > ' || (SELECT Waymarks FROM temp.ResearchWay WHERE Rownum = TaskID) 
   WHEN 'RoleTable' THEN   
     (SELECT 'Lists > ' || Waymarks FROM temp.RoleWay WHERE Rownum = RoleID)
   WHEN 'SourceTable' THEN   
      'Lists > Source List' || CHAR(13) || ' ' || (SELECT Name FROM SourceTable WHERE Rownum = SourceID)      
   WHEN 'SourceTemplateTable' THEN   
      'Lists > Source Template List' || CHAR(13) || ' ' || (SELECT Name FROM SourceTemplateTable WHERE Rownum = TemplateID)      
   WHEN 'URLTable' THEN    
     (SELECT Waymarks FROM temp.URLWay WHERE Rownum = LinkID)
   WHEN 'WitnessTable' THEN
     (SELECT Waymarks FROM temp.WitnessWay WHERE Rownum = WitnessID)  
   ELSE 'TBD - ' || TableName   
   END AS [Waymarks]   
 , FieldName
 , LocateQuery 
FROM xSearchResults 
;
-- Re-execute as often as needed. SQLite Expert remembers the last $SearchTerm