-- FindAlmostEverywhere.sql

/*
2014-10-16 Tom Holden ve3meo
2014-10-23 rev make Full Text Search table temp so it is dropped when SQLite manager
 closes.
2014-10-24 rev expanded "Various" in FieldName to comma-separated list of fields

Most of this script builds a virtual table using the SQLite FTS4 extension from 
fields of RootsMagic tables containing user-entered text. Date fields are excluded.

The last statement runs the search query which pops up 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.

The creation of the virtual table can take considerable time with a large database
so it is preferable to run it once and then repeat the search query on its own as
often as needed until changes in the database warrant the rebuilding of the FTS 
table. 

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

*/

DROP TABLE IF EXISTS xFTStable
;
-- Create a Full Text Search table with four columns
CREATE VIRTUAL TABLE temp.xFTStable USING fts4(Content, FieldName, TableName, Rownum)
;

INSERT INTO xFTStable
SELECT * FROM
(
------------------AddressTable
SELECT Name ||', '||
Street1 ||', '||
Street2 ||', '||
City ||', '||
State ||', '||
Zip ||', '||
Country ||', '||
Phone1 ||', '||
Phone2 ||', '||
Fax ||', '||
Email ||', '||
URL AS "Content"
, 'Name, 
Street1, 
Street2, 
City, 
State, 
Zip, 
Country, 
Phone1, 
Phone2, 
Fax, 
Email, 
URL' AS "FieldName"
, 'AddressTable' AS "TableName"
, ROWID AS "Rownum" 
FROM AddressTable
UNION ALL
SELECT CAST(Note AS TEXT) AS "Content", 'Note' AS "FieldName", 'AddressTable' AS "TableName", ROWID AS "Rownum" FROM AddressTable
WHERE "Value" NOT LIKE ''
UNION ALL
------------------ChildTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'ChildTable', ROWID FROM ChildTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
-------------------CitationTable

SELECT CAST(Comments AS TEXT) AS CommentsTxt, 'Comments', 'CitationTable', ROWID FROM CitationTable
WHERE CommentsTxt NOT LIKE ''
UNION ALL

SELECT CAST(ActualText AS TEXT) AS ActualTextTxt, 'ActualText', 'CitationTable', ROWID FROM CitationTable
WHERE ActualTextTxt NOT LIKE ''
UNION ALL

SELECT CAST(Fields AS TEXT) AS FieldsTxt, 'Fields', 'CitationTable', ROWID FROM CitationTable
WHERE FieldsTxt NOT LIKE ''
UNION ALL
------------------EventTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'EventTable', ROWID FROM EventTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Details AS TEXT) AS NoteTxt, 'Details', 'EventTable', ROWID FROM EventTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'EventTable', ROWID FROM EventTable
WHERE SentenceTxt NOT LIKE ''    -- custom ones only
UNION ALL

-------------------FactTypeTable

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'FactTypeTable', ROWID FROM FactTypeTable
WHERE SentenceTxt NOT LIKE ''
AND FactTypeID > 999    -- custom ones only
UNION ALL
--------------------FamilyTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'FamilyTable', ROWID FROM FamilyTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
------------------LabelTable

SELECT LabelName ||': '|| Description AS NoteTxt, 'LabelName', 'LabelTable', ROWID FROM LabelTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
------------------LinkTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'LinkTable', ROWID FROM LinkTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
------------------MediaLinkTable

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'MediaLinkTable', ROWID FROM MediaLinkTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Caption AS TEXT) AS CaptionTxt, 'Caption', 'MediaLinkTable', ROWID FROM MediaLinkTable
WHERE CaptionTxt NOT LIKE ''
UNION ALL

SELECT CAST(Description AS TEXT) AS DescriptionTxt, 'Description', 'MediaLinkTable', ROWID FROM MediaLinkTable
WHERE DescriptionTxt NOT LIKE ''
UNION ALL
----------------------MultiMediaTable

SELECT CAST(Caption AS TEXT) AS CaptionTxt, 'Caption', 'MultiMediaTable', ROWID FROM MultiMediaTable
WHERE CaptionTxt NOT LIKE ''
UNION ALL

SELECT CAST(Description AS TEXT) AS DescriptionTxt, 'Description', 'MultiMediaTable', ROWID FROM MultiMediaTable
WHERE DescriptionTxt NOT LIKE ''
UNION ALL
--------------------NameTable

SELECT Prefix ||
' '|| Given ||
' '|| '"'||Nickname||'"' ||
' '|| Surname ||
' '|| Suffix AS NoteTxt
, 'Prefix 
, Given
, Nickname
, Surname
, Suffix' AS FieldName
, 'NameTable'
, ROWID FROM NameTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'NameTable', ROWID FROM NameTable
WHERE NoteTxt NOT LIKE ''
UNION ALL

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'NameTable', ROWID FROM NameTable
WHERE SentenceTxt NOT LIKE ''
UNION ALL
---------------------PersonTable

SELECT CAST(Note AS TEXT) NoteTxt, 'Note', 'PersonTable', ROWID FROM PersonTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
---------------------PlaceTable

SELECT Name || ' | ' || Abbrev || ' | ' || Normalized  AS NoteTxt
, 'Name, Abbrev, Normalized'
, 'PlaceTable'
, ROWID 
FROM PlaceTable
WHERE NoteTxt NOT LIKE '' 
UNION ALL

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'PlaceTable', ROWID FROM PlaceTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
---------------------ResearchItemTable
SELECT 
  RefNumber ||' | '||
  Repository ||' | '||
  Goal ||' | '||
  Source ||' | '||
  Result AS NoteTxt
  , 'RefNumber
     , Repository
     , Goal
     , Source
     , Result'
  , 'ResearchItemTable', ROWID FROM ResearchItemTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
---------------------ResearchTable

SELECT RefNumber ||' | '||
Name ||' | '||
Filename ||' | '||
CAST(Details AS TEXT) AS NoteTxt
, 'RefNumber
   , Name
   , Filename
   , Details'
, 'ResearchTable', ROWID FROM ResearchTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
-------------------RoleTable

SELECT RoleName ||': '|| CAST(Sentence AS TEXT) AS SentenceTxt, 'RoleName, Sentence', 'RoleTable', ROWID FROM RoleTable
WHERE SentenceTxt NOT LIKE ''
AND RoleID > 58    -- custom ones only

UNION ALL
---------------------SourceTable--------------------
SELECT Name ||' | '|| RefNumber AS NameTxt, 'Name, RefNumber', 'SourceTable', ROWID FROM SourceTable
WHERE NameTxt NOT LIKE ''
UNION ALL

SELECT CAST(ActualText AS TEXT) AS ActualTextTxt, 'ActualText', 'SourceTable', ROWID FROM SourceTable
WHERE ActualTextTxt NOT LIKE ''
UNION ALL

SELECT CAST(Comments AS TEXT) AS CommentsTxt, 'Comments', 'SourceTable', ROWID FROM SourceTable
WHERE CommentsTxt NOT LIKE ''
UNION ALL

SELECT CAST(Fields AS TEXT) AS FieldsTxt, 'Fields', 'SourceTable', ROWID FROM SourceTable
WHERE FieldsTxt NOT LIKE ''
UNION ALL
------------------SourceTemplateTable
SELECT Name ||' | '|| Description ||' | '|| Category AS VariousTxt
, 'Name, Description, Category'
, 'SourceTemplateTable', ROWID FROM SourceTemplateTable
WHERE TemplateID > 9999    -- custom ones only
UNION ALL  
SELECT Footnote, 'Footnote', 'SourceTemplateTable', ROWID FROM SourceTemplateTable
WHERE TemplateID > 9999    -- custom ones only
UNION ALL
SELECT ShortFootnote, 'ShortFootnote', 'SourceTemplateTable', ROWID FROM SourceTemplateTable
WHERE TemplateID > 9999    -- custom ones only
UNION ALL
SELECT Bibliography, 'Bibliography', 'SourceTemplateTable', ROWID FROM SourceTemplateTable
WHERE TemplateID > 9999    -- custom ones only
UNION ALL
---------------------URLTable

SELECT Name ||' | '|| URL ||' | '|| CAST(Note AS TEXT) AS NoteTxt
, 'name, URL, Note', 'URLTable', ROWID FROM URLTable
WHERE NoteTxt NOT LIKE ''
UNION ALL
-------------------WitnessTable

SELECT Prefix ||
' '|| Given ||
' '|| Surname ||
' '|| Suffix AS NoteTxt
, 'Prefix
   , Given
   , Surname
   , Suffix' AS extWitness, 'WitnessTable', ROWID FROM WitnessTable
WHERE Prefix||Given||Surname||Suffix NOT LIKE ''
UNION ALL

SELECT CAST(Sentence AS TEXT) AS SentenceTxt, 'Sentence', 'WitnessTable', ROWID FROM WitnessTable
WHERE SentenceTxt NOT LIKE ''
UNION ALL

SELECT CAST(Note AS TEXT) AS NoteTxt, 'Note', 'WitnessTable', ROWID FROM WitnessTable
WHERE NoteTxt NOT LIKE ''
)
;
-----END of Script that builds the virtual FTS table-----

-----repeat the following query as often as you want-----
---------------- Full Text Search query -----------------
SELECT * FROM xFTStable WHERE xFTStable MATCH $SearchTerm
;


/*

Alternative search query with snippet from all fields in virtual table 
with match highlighted either by <b>...</b> or by [...] or by **...**.

SELECT snippet(xFTStable) AS [snippet], * FROM xFTStable WHERE xFTStable MATCH $SearchTerm
;
SELECT snippet(xFTStable, '[', ']', '...') AS [snippet], * FROM xFTStable WHERE xFTStable MATCH $SearchTerm
;
SELECT snippet(xFTStable, '**', '**', '...') AS [snippet], * FROM xFTStable WHERE xFTStable MATCH $SearchTerm
;
*/

------------END OF SCRIPT--------------