B4A Library [B4X] SQLSearchView - SQLite based Search View

1628776189752.png


This library is based on B4XSearchTemplate. The difference is that instead of building an in-memory index, it uses a SQLite database. It is relevant when you want to search large collections.
I've tested it with a collection of almost 400k items. It starts immediately and the search is fast.

The idea is to build the database once with B4J and then add the database file to the project.
SearchView.BuildDatabase builds the database. Note that it uses MAX_LIMIT and MaxNumberOfItemsToShow while building the index. You cannot change them later.

Assuming that the database file is added to the assets folder, it needs to be copied to XUI.DefaultFolder. This happens, when needed, in SearchView.LoadDatabase. The file is copied asynchronously.

The database is not small. For this collection it is 30mb uncompressed and about 20mb when compressed inside the APK.

Usage example:
B4X:
Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private SearchView As SQLSearchView
    Private Dialog As B4XDialog
    Private Button1 As B4XView
End Sub

Public Sub Initialize
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    SearchView.Initialize
'    SearchView.BuildDatabase("C:\Users\H\Downloads", "search.db", File.ReadList("C:\Users\H\Downloads\words.txt", ""))
    xui.SetDataFolder("SQLSearchView")
    Wait For (SearchView.LoadDatabase(File.DirAssets, "search2.db")) Complete (Success As Boolean)
    Button1.Enabled = Success
    Dialog.Initialize(Root)
    Dialog.Title = "SQL Search View"
    SetDialogTheme
End Sub

Private Sub SetDialogTheme
    Dim TextColor As Int = 0xFF5B5B5B
    SearchView.SearchField.TextField.TextColor = TextColor
    SearchView.SearchField.NonFocusedHintColor = TextColor
    SearchView.CustomListView1.sv.ScrollViewInnerPanel.Color = 0xFFDFDFDF
    SearchView.CustomListView1.sv.Color = Dialog.BackgroundColor
    SearchView.CustomListView1.DefaultTextBackgroundColor = xui.Color_White
    SearchView.CustomListView1.DefaultTextColor = TextColor
    If SearchView.SearchField.lblV.IsInitialized Then SearchView.SearchField.lblV.TextColor = TextColor
    If SearchView.SearchField.lblClear.IsInitialized Then SearchView.SearchField.lblClear.TextColor = TextColor
    Dialog.BackgroundColor = xui.Color_White
    Dialog.ButtonsColor = xui.Color_White
    Dialog.BorderColor = xui.Color_Gray
    Dialog.ButtonsTextColor = 0xFF007DC3
    Dialog.OverlayColor = xui.Color_Transparent
End Sub

Private Sub Button1_Click
    Wait For (Dialog.ShowTemplate(SearchView, "", "", "Cancel")) Complete (Result As Int)
    If Result = xui.DialogResponse_Positive Then
        Log(SearchView.SelectedItem)
    End If
End Sub
 

Attachments

  • SQLSearchView.b4xlib
    3.6 KB · Views: 149
Last edited:

Peter Simpson

Expert
Licensed User

Peter Simpson

Expert
Licensed User
Okay @Erel you win, this is solution is extremely fast and efficient.
Hey, it even runs ridiculously quickly in debug mode via B4A, B4J and also B4i which I freely admit I was not expecting at all. I've been studying your Searchview class, well trying to anyway, still a bit lost in the code but I'll get there. Nevertheless filtering 386613 lines instantly in debug mode on the aforementioned platforms is a huge 👍 from me.

Thank you...
 
Last edited:

Mahares

Expert
Licensed User
Something may not be fully kosher about the search. Try to search for 'spectacular', it does not find it, although there are a few records in the database table with the word spectacular.
For those of you who want to make the search template bigger, just add this right after you load the layout:
B4X:
SearchView.Initialize
 SearchView.Resize(Root.Width/2,Root.Height/2)  'whatever dimensions you want
What database (for testing purposes only)???
I had the same confusion as Peter did about the link to the words.txt file, but finally figured it out. What confused me more was Mario's post #3 which seems to be unrelated.
 
Top