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...

emexes

Expert
Licensed User
On a related note: is the app just for you, or for your subcontractors too, or for other contractors generally? Like, if it's just for your own use, then it doesn't matter if it the interface is quirky, ie that it prioritises functionality and speed over intuitiveness. But if it is for other people, then simplicity is paramount - anything complicated, that takes more effort to work out how to use it than it saves, is going to be ignored.

Lol if it's for other people, then we want them to think that the app is reading their mind. 🤔
 
Upvote 0

emexes

Expert
Licensed User
I realize that it's confusing and making you think I'm nuts.

Lol. Yes. I thought it was just me, but apparently not. 🙃

But I can see that you have a grand plan in your mind that is so crystal clear that you keep forgetting that other people aren't able to automagically see it too. 🔮
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
Lol. Yes. I thought it was just me, but apparently not. 🙃

But I can see that you have a grand plan in your mind that is so crystal clear that you keep forgetting that other people aren't able to automagically see it too. 🔮
Some programmers are not graduated from Computer Science and they learn programming by their own. Here is the different of mindset between them and someone who learn about System Analyst and Design. A programmer need to learn the skill to analyze a problem or requirements and able to translate it into technical solution. This is where software engineer comes in. One should able to talk Layman with users and also talk Jargons with other programmers. If words are not enough then drawing may help to visualize the whole idea.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
I tried to make a random guess of the requirement. Here is a demo using B4XTable and B4XFloatTextField. (B4J and B4A) It supports up to 2 search keywords.

1670475576975.png
 

Attachments

  • SearchItem.zip
    16.1 KB · Views: 89
Upvote 0

rraswisak

Active Member
Licensed User
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
        xTableSearch.AddRow(Array As String (myitem(0), myitem(1), myitem(2), myitem(3)))    'Add the found item
        Sleep(0)
    End If
End Sub
 
Upvote 0
Solution

Shelby

Well-Known Member
Licensed User
For emexes question:
The goal is to stop the locking up of the search function. When a user types in more than one word, at this point, unless the order of the words typed is exactly as is viewed in any record the search locks in a blank screen and displays no records, only the keywords typed. Even if a word doesn't occur in any record I want any and all other records which may have only one of perhaps many words typed is displayed.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
On a related note: is the app just for you, or for your subcontractors too, or for other contractors generally? Like, if it's just for your own use, then it doesn't matter if it the interface is quirky, ie that it prioritises functionality and speed over intuitiveness. But if it is for other people, then simplicity is paramount - anything complicated, that takes more effort to work out how to use it than it saves, is going to be ignored.
The app is to be used by any craftsman, inspector, contractor, and the like to find the record (so-called table) to view information relevant to installation of items, verify proper installation, etc. The records are composed of code requirements for building.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Some programmers are not graduated from Computer Science and they learn programming by their own. Here is the different of mindset between them and someone who learn about System Analyst and Design. A programmer need to learn the skill to analyze a problem or requirements and able to translate it into technical solution. This is where software engineer comes in. One should able to talk Layman with users and also talk Jargons with other programmers. If words are not enough then drawing may help to visualize the whole idea.
Aeric,
I appreciate your intuitive description here as I am a perfect example of the non programmer type you describe above.
Thank you
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
I've had no time to devote to any more work on it. I'm reluctant to use your additions since I like the search as it stands and I wonder why I would need two additional buttons when the single button seems sufficient. Must leave for a delivery.
Thanks for your assistance thus far. I'm hoping that some of the other members' suggestions might work sufficiently with the search I have now with additions of code to increase the requested flexibility of its function. All I need is for the present search to allow more keywords in any order to bring up multiple records as I've been requesting.
Thanks
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
I've had no time to devote to any more work on it.
Well, I am afraid that you did not take the time to really test it !?
It is very frustrating to help with almost no information on what you want to do !
When you get a possible solution with low information, just error reports because you did not do correctly what was suggested.
And then just 'will do later' and nothing more, this means I spend time for nothing.
Because you give no information on what does not work and what does not fit your expectations !?
 
Upvote 0

Shelby

Well-Known Member
Licensed User
I tried to make a random guess of the requirement. Here is a demo using B4XTable and B4XFloatTextField. (B4J and B4A) It supports up to 2 search keywords.
Hi Aeric,
I successfully ran your proposed solution with my phone. It doesn't quite serve my needs. I want the search function to ignore any words that may be used in a search. When I searched "Grade B" there is an appropriate result of only the second record "Grade B Egg"; then when I add an irrelevant word "Beach", and search "Grade Beach", the search function says "No matches". If a user tries a word which does not occur in my first column of descriptions, I want the search function to get matches on the occurring words and pay no attention to irrelevant words. If I search "Grade" I appropriately get all three results of "Grade A Eggs, Grade B Eggs, Grade C Eggs" of course. Lastly I would like to have more than only 2 words in the search query since the user might have many words entered for the search. Thanks for your efforts. I'll try some of the other suggestions in the meantime as I have a little more time this weekend to work on this.
 
Upvote 0

emexes

Expert
Licensed User
then when I add an irrelevant word "Beach", and search "Grade Beach", the search function says "No matches".

Are there any entries in your first column that have both words "Grade" and "Beach"?

If not, then "No matches" would be seem to be an accurate and reasonable result.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Are there any entries in your first column that have both words "Grade" and "Beach"?
If not, then "No matches" would be seem to be an accurate and reasonable result.
Still, I would think the operating system could ignore irrelevant words and give matches for the relevant words. Users will make mistakes or not know how my short descriptions are constructed. Thanks Emex (or whatever your name is). Kidding
Hey, you better get some sleep.
 
Upvote 0

emexes

Expert
Licensed User
the operating system

Operating system is not doing the searching - SQL and B4X are in charge of that.


could ignore irrelevant words and give matches for the relevant words

"irrelevant" is a subjective characteristic, eg, why wouldn't "Grade" be irrelevant, instead of "Beach", and we list entries containing just the word "Beach", rather than just the word "Grade" ?


Users will make mistakes or not know how my short descriptions are constructed.

If the search happens as each character is typed, and your users get at least the first couple of characters correct, then the reducing list will show samples of words that are in the ballpark.


Thanks Emex (or whatever your name is).

My initials are M X S so you're closer to my name than you think 🙃
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
B4X:
SELECT DescriptionSubjectLookup FROM TABLE WHERE DescriptionSubjectLookup LIKE %VALUE% OR DescriptionSubjectLookup LIKE %VALUE% OR DescriptionSubjectLookup LIKE %VALUE%
Hey Jahswant, I added the code you suggested and my B4A system says undeclared variable 'descriptionsubjectlookup' is used before it was assigned any value. I could dim it and so I'll try that. ( declare in memory)
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Operating system is not doing the searching - SQL and B4X are in charge of that.




"irrelevant" is a subjective characteristic, eg, why wouldn't "Grade" be irrelevant, instead of "Beach", and we list entries containing just the word "Beach", rather than just the word "Grade" ?




If the search happens as each character is typed, and your uses get at least the first couple of characters correct, then the reducing list will show samples of words that are in the ballpark.




My initials are M X S so you're closer to my name than you think 🙃
Maybe you forgot, I do know your first name. I'm just making fun of your code name here. Now I see the relevance of emexes.
Back to the information: The example of Graded eggs comes form Aeric's example which I ran. I'm analyzing your information above; thanks. Remember my actual app has actual building construction words, not hen laying words.
 
Upvote 0
Top