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

jahswant

Well-Known Member
Licensed User
Longtime User
if the exact occurrence is found in my db
USE
B4X:
SELECT DescriptionSubjectLookup FROM TABLE WHERE DescriptionSubjectLookup  LIKE %VALUE% OR DescriptionSubjectLookup  LIKE %VALUE% OR DescriptionSubjectLookup  LIKE %VALUE%
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Thanks, I'll try the suggestion and run to donate if it's a success. I'm not sure which line I should put it in, however. Maybe line 93?
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
Private xTableSearch As xTableLite
Sorry for asking. What is xTableLite?

EDIT: seems it is a custom view created by klaus

 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Sorry for asking. What is xTableLite?

EDIT: seems it is a custom view created by klaus

Oh, yes, I think as Aeric said, it's an entry of construction by Klaus months ago.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Create a small project with sample date and let me help you.
I don't think I'm capable to extract a small amount of my db so I'll just include my entire code zipped. Whoops, I couldn't get it in; too large. I'll have to come back later. Work calls. Still, do you have an idea of which of my lines I should try entering your earlier (one of three) suggestions? Maybe I should try multiple lines where in my code is the line beginning with "SELECT DescriptionSubjectLookup...." I'll check back here as I prepare for leaving.
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
Sorry for asking. What is xTableLite?
xTableLite is a not published CustomView (b4xlib).
It is a lite version of the xTable CustomView, also not published, which is a B4X version of the B4A Table CustomView (Flexible Table).
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
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 of the entries in the db to be brought up no matter what arrangement the keywords my be displayed in my db elements.
You mean when you search for "yoda speak b4x" then the search results or highlights show "b4x speaks yoda", "yoda speaks", "b4x yoda"?
 
Upvote 0

Shelby

Well-Known Member
Licensed User
You mean when you search for "yoda speak b4x" then the search results or highlights show "b4x speaks yoda", "yoda speaks", "b4x yoda"?
Funny Aeric, I mean if normally occurring keywords in my db of 433 tables is entered into the search field, the correct wording does not have to be obeyed. No Yoda's in my database so far.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Jahswant, I could email my zipped app to you if you private message an email address to me.
 
Upvote 0

Xfood

Expert
Licensed User
Funny Aeric, I mean if normally occurring keywords in my db of 433 tables is entered into the search field, the correct wording does not have to be obeyed. No Yoda's in my database so far.
could you share a dropbox, googledrive link here
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
Funny Aeric, I mean if normally occurring keywords in my db of 433 tables is entered into the search field, the correct wording does not have to be obeyed. No Yoda's in my database so far.
of course I know no yoda in your db but I am trying to understand about your question to give a hand with my example, since you didn't provide your own example.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
For Aeric: What I expect is for the search to give results of any of my 430 tables in my db to show up if any word or more that one word is within any of the tables. Primarily I want to search the column: "DescriptionSubjectLookup"
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
What I expect is for the search to give results of any of my 430 tables in my db to show up if any word or more that one word is within any of the tables.
SELECT DescriptionSubjectLookup, TableNumber as
, PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221
This SQL query only search for ONE table. If you want to search for more than one table then you can use UNION
e.g.
SELECT Col1, Col2 FROM Table001 WHERE Col1 LIKE ? OR Col2 LIKE ?
UNION
SELECT Col1, Col2 FROM Table002 WHERE Col1 LIKE ? OR Col2 LIKE ?
and so on

BUT this is very cumbersome!
Why not create a VIEW in the db and then use SELECT Col1, Col2 FROM View001 WHERE Col1 LIKE ? OR Col2 LIKE ?
 
Upvote 0

Shelby

Well-Known Member
Licensed User
This SQL query only search for ONE table. If you want to search for more than one table then you can use UNION
e.g.
SELECT Col1, Col2 FROM Table001 WHERE Col1 LIKE ? OR Col2 LIKE ?
UNION
SELECT Col1, Col2 FROM Table002 WHERE Col1 LIKE ? OR Col2 LIKE ?
and so on

BUT this is very cumbersome!
Why not create a VIEW in the db and then use SELECT Col1, Col2 FROM View001 WHERE Col1 LIKE ? OR Col2 LIKE ?
It's still too advanced for my abilities. I'm not sure how to write a view in the db as you mentioned. Maybe I'd start a new sub?
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Maybe Jahwant's idea above is giving me the same result but I'm confused as usual as to where I might interject the lines. I'm vaguely familiar with Union so that makes sense.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
You may need to execute a SQL query like:
B4X:
CREATE VIEW vw_AllTables (
    Col01,
    Col02
)
AS
    SELECT Col01, Col02 FROM Table01
    UNION
    SELECT Col01, Col02 FROM Table02
    UNION
    SELECT Col01, Col02 FROM Table03

1670351442975.png
 
Upvote 0
Top