Android Question Increase search ability SQLite (Solved)

Shelby

Well-Known Member
Licensed User
Below is my b4xMainPage module for my SQLite app
My b4xmain module:
#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
. My search ability is limited. If I use more than one keyword I only get results if the exact occurrence is found in my db. I'd like for all and any records to be brought up no matter what arrangement the keywords might be typed into the search.
Thanks for any help
 
Last edited:
Solution
Try this...

B4X:
Private Sub FastSearch (what As Int)
    'Get the item to analyze from the List
    Dim myitem() As String = lstSearch.Get(what)
    'Just not to let the app to hang up
    Sleep(0)   

    'concat data want to search
    Dim mydata As String = myitem(0) & myitem(1) & myitem(2) & myitem(3)

    'prepare any word want to search, (assume single space for each word)
    Dim word() = Regex.Split(" ", SearchPattern.ToUpperCase)
    
    'search for any match word on the data
    Dim found As Boolean
    For y = 0 To word.Length - 1
        If mydata.ToUpperCase.Contains(word(y).ToUpperCase) Then
            found = True
            Exit
        End If
    Next
    
    'if founded, add to to table
    If found=True Then...

Shelby

Well-Known Member
Licensed User
Thanks Jahwant and Aeric and Mahares and Xfood; I'm off to work and will try to make some entries into my code from the suggestions you all made. Especially your last entry above Aeric. Logging out until 5 hours from now.
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Home for lunch: I have the following sub


Initialize Sub:
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

Aeric, Should I add your code above to it or start another sub? I only say that since that B4XPage_Created sub is the only place I can find the word created. From another perspective maybe I have to dim some additional variables to use your query suggestion. Going back to work around the block.
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
Your question is confusing. First you mention about "arrangement of the keywords" then you mentioned you have "400+ tables".

Please explain what tables are inside your db. What are the columns?
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
you have "400+ tables
Nooooo!. Shelby's database has only ONE SQLite table. His table has some 400 plus records whose data refer to some sort of construction tables that have nothing to do with SQLite tables. He just calls records tables for some reason. I think if Shelby clarifies things better when he post a thread, his threads will not accumulate an enormous amount of posts that tend to confuse him and those who want to help him.
I have seen his database in his attachment in the below thread.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
Nooooo!. Shelby's database has only ONE SQLite table. His table has some 400 plus records whose data refer to some sort of construction tables that have nothing to do with SQLite tables. He just calls records tables for some reason. I think if Shelby clarifies things better when he post a thread, his threads will not accumulate an enormous amount of posts that tend to confuse him and those who want to help him.
I have seen his database in his attachment in the below thread.
I see. The “Table” is the furniture of a building he must be referring to. Ops, I am start talking like a yoda.
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
Shelby, you must explain more precisely what exactly you want to do !
Filtering any given value or generic values would be much easier to do directly with SQLite instead of retrieving the data of the Table.
What kind of word or sentence do you want to filter ?
Give a concrete example !
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
The fact is that each of my records displays simple images of tables which are named tables by the company which offers the images that are actually the records. I realize that it's confusing and making you think I'm nuts. I may be, but each record is an image of a collection of text and, or an image.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
The words I want to filter are from my first column of the app entitled: DescriptionSubjectLookup. All the text in that column contains my abbreviated descriptions of each record. I now have a link to my app obtained from dropbox free for a week or two. If you would like to have that link, I'd be glad to email it if I recognize your name from the communications over the past four or so years I've been involved here.
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
Longtime User
I have played a bit with one of your previous projects.
I have added en EditText and two Buttons.
And the filter is done with SQLite.

1670431103431.png
1670431163459.png
1670431329815.png

The code:
B4X:
Private Sub btnCheck_Click
    xtblItems.LoadSQLiteDB2(sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%" & edtCheck.Text & "%'", False, Array As String("T", "T", "T", "I"))
    xtblItems.SetColumnsWidths(Array As Int(300dip, 150dip, 90dip, 75dip))
    IME1.HideKeyboard
End Sub

Private Sub btnUncheck_Click
    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"))
    xtblItems.SetColumnsWidths(Array As Int(300dip, 150dip, 90dip, 75dip))
    IME1.HideKeyboard
End Sub

It uses the IME library to hide the keyboard.
The EditText and the Buttons are on top of the screen to avoid that the keyboard covers the EditText and the Buttons.

PS:
As you see, when you give concrete information on your problem you get much quicker a solution.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Not everybody understands the makeup of concrete. Thanks so much for the never ending help and advice you give here.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
I get the error in my B4xMainPage: "Undeclared variable 'edtcheck' is used before it was assigned any value"
Will the following work? ' Public edtcheck As List ', or 'As image' or 'As B4XView' ?
 
Upvote 0

Shelby

Well-Known Member
Licensed User
also IME1 is said to be not assigned any value. I guess it's another variable for me to assign a value for, like 'Public IME1 As B4XView' ?
 
Upvote 0

Shelby

Well-Known Member
Licensed User
When I add those 2 variables in my Class_Globals, I see for those variables : not initialized. I better build a test app and try an initializing sub. Here's the lower end of my Class_Globals sub

Globals:
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 
    Private IME1 As IME 
    Private edtcheck As B4XView
 
Upvote 0

Shelby

Well-Known Member
Licensed User
My app compiled OK on my phone but still no search improvement. Have to go to work. I'll try adding some type of initializing of the variables later.
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
Not everybody understands the makeup of concrete.
Sorry, my native language is not English.
In this case 'concrete' should mean: tangible, practical (Deepl translator).

In the Designer you need to add an EditText View with the name of edtcheck !
Then, still in the Designer, you need to add two Buttons with the names btnCheck and btnUncheck !
Then, you need to check the IME library in the Libraries Manager Tab.
And it should work !
 
Upvote 0

emexes

Expert
Licensed User
What is the actual end goal here? For this section of your app, not for the entire all-encompassing everything-including-the-kitchen-sink ultimate app... that comprehensiveness aspect might be better handled by having separate apps specific to their own tasks, perhaps working from a shared set of common data that is entered once rather than multiple times ie for each app.

Anyway, my best guess so far is:

you have 433 scanned pages of building/construction code/regulation/standards, and you want to filter that down to just the pages relevant to a particular current project/job/contract,

for easy and quick access compared to having to continually thumb through hundreds of pages spread across multiple books/documents.

Is that in the ballpark?
 
Last edited:
Upvote 0
Top