Android Question Increase search ability SQLite (Solved)

#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=The_Shelby_Code.zip

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
 
    Public sql As SQL
 
    Public DBFileDir As String
    Public DBFileName = "dbircV11.db" As String
    Public Shared As String
    Public ImageFileName As String
 
    Private xtblItems As xTableLite
    Private ZoomImageView1 As ZoomImageView
    Private btnQuit As Button
#If B4A
    Public rp As RuntimePermissions
#End If
    Private lblTitle As B4XView
    Private lblSearch As B4XView           'first use of word: 'search'
    Private lblCancel As B4XView
    Private edtSearch As B4XView
    Private xTableSearch As xTableLite
    Private writing As Boolean            'Boolean to wait for search while writing
    Private timerSearch As Timer    'The timer that will check when is possible to start Search
    Private SearchPattern As String        'Variable to Hold the pattern to search for
    Private lstSearch As List            'List contains copy of DB to fasten the search (I suppose)
    Private ime As IME                    'Library to handle the keyboard and hide it when no longer needed
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("Layout")
 
#If B4A
    Shared = rp.GetSafeDirDefaultExternal("")
#Else If B4J
    xui.SetDataFolder("SS2021B4X")
    Shared = xui.DefaultFolder
#Else
    Shared = xui.DefaultFolder
#End If
    File.MakeDir(Shared, "/Dbs/")
'    File.MakeDir(Shared, "/Images")
    DBFileDir = Shared & "/Dbs/"

'    ImagesDir = Shared & "/Images/"
'    ImagesDir = File.DirAssets    'you could eventually leave the images in the File.DirAssets folder.

    File.Delete(DBFileDir, DBFileName)    'can be used to reset the default database.
 
    Log(DBFileDir)
'    Log(ImagesDir)
    If File.Exists(DBFileDir, DBFileName) = False Then 'SSS When False:Lets browser update
        Log("db file does not exist")
        File.Copy(File.DirAssets, DBFileName, DBFileDir, DBFileName)
        Log("db copied")
    Else
        Log("db file does exist")
    End If
 
'    If File.Exists(ImagesDir, "R702.4.2.png") = False Then
'        File.Copy(File.DirAssets, "R702.4.2.png", ImagesDir, "R702.4.2.png")    'ignore
'    End If                         (SSS all green here by Klaus)                   
 
    B4XPages.SetTitle(Me, "SS2021B4X")
    If sql.IsInitialized = False Then
        Try
            #If B4J
            sql.InitializeSQLite(DBFileDir, DBFileName,True)
            #Else
            sql.Initialize(DBFileDir, DBFileName,True)
            #End If
        Catch
            Log("DB Not Initialized")
            'CkUser
        End Try
    Else
    
    End If
 
    Dim resultset As ResultSet = sql.ExecQuery("SELECT name from sqlite_master where type='table'")
    Do While resultset.NextRow
        Log(resultset.Getstring("name"))
    Loop
    resultset.Close
 
    'xtblItems.LoadSQLiteDB(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221",True)
    'Above edited out with Klaus recommendation in forum 5.20.21 SSS
    xtblItems.SingleLine = False
'    xtblItems.LoadSQLiteDB2(sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221", False, Array As String("T", "T", "T", "I"))


    'I made a small change to the xTableLite class of Klaus, just to have the app wait for the table to be fully loaded before doing other things
    'Because of this I call the Table loading with a Wait For
    Wait For (xtblItems.LoadSQLiteDB2(sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221", False, Array As String("T", "T", "T", "I"))) complete (Comp As Object)
 
    'Here we populate the List that will help us for the Search Function
    lstSearch.Initialize
    For x = 0 To xtblItems.Size - 1
        Dim item() As String = xtblItems.GetValues(x)
        lstSearch.Add(item)
    Next
 
 


    xtblItems.SetColumnsWidths(Array As Int(250dip, 150dip, 90dip, 75dip))
 
    'Setting the Table for Search Result to appear as the original one
    xTableSearch.SingleLine = False
    xTableSearch.SetHeader(Array As String ("DescriptionSubjectLookup", "Table", "IRC Page", "PDF Page"))
    xTableSearch.SetColumnsWidths(Array As Int(250dip, 150dip, 90dip, 75dip))
 
    timerSearch.Initialize("Search", 1000)    'Timer set to wait for 1 second after  writing before start Search.
    ime.Initialize("")                        'Initialize the library to hide the keyboard later
End Sub

Sub btnQuit_Click
    If btnQuit.Text = "Back" Then
        ZoomImageView1.mBase.Visible = False
        btnQuit.Text = "Exit"
    Else
        sql.Close
#If B4i
        xui.MsgboxAsync("You cannot exit with this button", "Warning")
#Else
        ExitApplication
#End If
    End If
End Sub

Private Sub xtblItems_CellClick(col As Int, row As Int)
    ImageFileName = xtblItems.GetValue(1, row) & ".png"
    If File.Exists(File.DirAssets, ImageFileName) Then
        ShowImage
    Else
        xui.MsgboxAsync("Image  " & ImageFileName & "  does not exist.", "Warning")
    End If
End Sub

Private Sub ShowImage
    btnQuit.Text = "Back"
    ZoomImageView1.SetBitmap(xui.LoadBitmap(File.DirAssets, ImageFileName))
    ZoomImageView1.mBase.Visible = True
End Sub


Private Sub lblSearch_Click
    'Hide the Title bar and make available the EditText for Search, and the icon to close the search function.
    'It even hide the original table and show the one for search results.
    lblTitle.Visible = False
    lblSearch.Visible = False
    lblCancel.Visible = True
    edtSearch.Visible = True
    edtSearch.Text = ""
    xtblItems.Visible = False
    xTableSearch.Visible = True
    xTableSearch.ClearAll
End Sub

Private Sub lblCancel_Click
    'Hide everything related to the Search function and show again the Title Bar and the icon for Search
    edtSearch.Visible = False
    lblCancel.Visible = False
    lblSearch.Visible = True
    lblTitle.Visible = True
    xTableSearch.Visible = False
    xTableSearch.ClearAll
    xtblItems.Visible = True
    ime.HideKeyboard
End Sub

Private Sub edtSearch_TextChanged (Old As String, New As String)
    If New = "" Then Return            'If EditText is empty no search will be done
    SearchPattern = New                'Copy the editext content as the pattern to search for
    writing = True                    'Variable to set that we are writing something and to avoid immediate unneeded search
    xTableSearch.ClearAll            'Clear the search results table
    timerSearch.Enabled = True        'Start the timer to check when possible to begin the search
End Sub

Private Sub Search_Tick
    If writing Then            'If we are writing no search will start
        writing = False
        Return
    End If
    timerSearch.Enabled = False                    'Stop the timer because a search will start
    For x = 0 To lstSearch.Size - 1                'Call the check of every single item simultaneously using a ResumableSub
        FastSearch(x)        'Just call a single item check
        Sleep(0)
    Next
End Sub

Private Sub FastSearch (what As Int)
    Dim myitem() As String = lstSearch.Get(what)                'Get the item to analyze from the List
    Sleep(0)        'Just not to let the app to hang up
    For y = 0 To 3
        If myitem(y).ToUpperCase.Contains(SearchPattern.ToUpperCase) Then        'Check all of the four Table fields. If the pattern is found then add it to the search table results
            xTableSearch.AddRow(Array As String (myitem(0), myitem(1), myitem(2), myitem(3)))    'Add the found item
            Sleep(0)
        End If
    Next
End Sub

Private Sub xTableSearch_CellClick(col As Int, row As Int)
    ime.HideKeyboard
    ImageFileName = xTableSearch.GetValue(1, row) & ".png"
    If File.Exists(File.DirAssets, ImageFileName) Then
        ShowImage
    Else
        xui.MsgboxAsync("Image  " & ImageFileName & "  does not exist.", "Warning")
    End If
End Sub