B4A Library [B4X] SQLSearchView - SQLite based Search View

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:

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

 

[Peter Simpson]

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

What database (for testing purposes only)???

Thank you...

 

[LucaMs]

https://www.b4x.com/android/forum/threads/b4x-tool-source-lm-snippets-manager.127714/post-816400

?

 

[Erel]

What database (for testing purposes only)???

SQLite.

 

[Peter Simpson]

SQLite.

Thank you Erel for the response but I meant what database as in where is the database that you used (so that we can have a play around with it), I already knew that it was SQLite ?

Thank you...

 

[Erel]

Of course, download the words file: https://www.b4x.com/android/forum/t...tion-where-is-a-good-start.133326/post-842992
You will need to build the database in B4J with BuildDatabase and then add the database file to the project.

 

[Peter Simpson]

Of course, download the words file: https://www.b4x.com/android/forum/t...tion-where-is-a-good-start.133326/post-842992
You will need to build the database in B4J with BuildDatabase and then add the database file to the project.

Thank you Erel, when I fully wake up I'll give it a go. It's currently silly o'clock in the morning here in the UK

 

[Peter Simpson]

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

 

[thinktank]

more power for b4x...

zipped database can be downloaded from the link;

 

[Mahares]

SQLite

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:

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.

 

[LucaMs]

What confused me more was Mario's post #3 which seems to be unrelated.

That was just to say that I did it something similar (for myself), modifying Erel's SearchView.
I haven't written about databases or files to download.
(and it was not a reply to @Peter Simpson , I did not reply to his post)

 

[Mahares]

If you want to test Erel's project and are having a problem creating the database search.db that Erel uses or do not want to go through the process yourself, here is a link to my Dropbox from which you can download the database. I think the download is good for a week from this date:
https://www.dropbox.com/t/BYCf0TwSkYxUiG8h

 

[Erel]

V1.01 released, as a b4xlib. It fixes the issue @Mahares reported.

 

[yzhy-mail]

If you want to test Erel's project and are having a problem creating the database search.db that Erel uses or do not want to go through the process yourself, here is a link to my Dropbox from which you can download the database. I think the download is good for a week from this date:
https://www.dropbox.com/t/BYCf0TwSkYxUiG8h

HI ,Mahares
Could you share the database search.db again.
Thank you !

 

[Mahares]

Could you share the database search.db again

Dropbox

www.dropbox.com

 

[yzhy-mail]

Dropbox

www.dropbox.com

Great!
So kind you are !
Thank you Mahares !