Explorer like search in SQLite

[Iczer]

I want to make Explorer-like search in SQLite database, but... query come out wrong...

maybe someone can correct me...

#include-once
#include <Array.au3>

Local $sSearchString = 0, $aSearch = 0, $sSearchWord = 0, $aSearchWord = 0, $aSearchWordPermute = 0
$sSearchString = "aaa bb|ccc ddd|eeeeee|ff gg hhhhhh"
$aSearch = StringSplit($sSearchString,"|",1)
$sSearchWord = ""
For $i = 1 To $aSearch[0]
    $aSearchWord = StringSplit($aSearch[$i]," ",2)
    If UBound($aSearchWord) = 2 Then
        $aSearchWordPermute = _ArrayPermute($aSearchWord, "_")
        $sSearchWord &= _ArrayToString($aSearchWordPermute, "% OR %", 1) & "% OR %"
    Else
        $sSearchWord &= StringRegExpReplace($aSearch[$i],"\s","_") & "% OR %"
    EndIf
Next
$sSearchWord = StringTrimRight($sSearchWord,6)
ConsoleWrite($sSearchWord&@crlf)
$sSql_Query = "SELECT FilePath || '\' || FileName FROM IndexingDB WHERE FileName LIKE '%" & $sSearchWord & "%' OR FilePath LIKE '%" & $sSearchWord & "%' ORDER BY FileName"

[JohnOne]

What query do you expect?

[Iczer]

query should return combined string from two columns where some (any) number of patterns can be found. and if pattern consist of two words, then in any combination of this words.

[JohnOne]

I thought you meant the query string was incorrect.

I don't see any SQLite code in your script.

[Iczer]

query indid incorrect, since later in code i have:

If Not ((_SQLite_GetTable($DB, $sSql_Query, $aList, $iRows, $iColumns) = $SQLITE_OK) And (UBound($aList) > 2)) Then
    MsgBox($MB_SYSTEMMODAL, "SQLiteFileListLoader : SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg())
    Dim $aList[1] = [0]
    Return SetError(7)
EndIf

and msgbox popup - query cannot find anything what was present in database

[jchd]

Look at what the resulting query looks like (I added some single quotes but the logic used for building the query is flawed):

SELECT FilePath || '' || FileName FROM IndexingDB WHERE FileName LIKE '%aaa_bb%' OR '%bb_aaa%' OR '%ccc_ddd%' ...

This is incorrect SQL. To use LIKE, this should read:

SELECT FilePath || '' || FileName FROM IndexingDB WHERE FileName LIKE '%aaa_bb%' OR FileName LIKE '%bb_aaa%' OR FileName LIKE '%ccc_ddd%'...

You would benefit from using a FTS3/4 virtual table instead.

[Iczer]

thanks, i try to implement it

but - what is FTS3/4 virtual table?

[jchd]

https://www.sqlite.org/fts3.html

Support for FTS4 is build in sqlite3.dll distributed thru AutoIt.

[Iczer]

thanks again - seems promising

[jchd]

FTS tables work very well once setup correctly for a given application. There is nothing overly complex there, but there are many possibilities and options that may have to be considered. Given the volume of reading involved I prefer to refer you and future readers to the official doc, which is very precise, complete and illustrated with good examples. Paraphrasing it here would be a waste of time for everyone.

[jchd]

Current SQLite implements a better search, FTS5.