Add FTS5 Support

[Thebas]

https://www.sqlite.org/fts5.html

[2shady4u]

I'm going to need some more information than just a link.

• Why would this be useful in your case?
• Why would this be useful for users in general?

[Thebas]

:D I believe it is useful because it allows the users to implement efficient "Full Text Search" functionality with very little effort. It is a default module that comes with sqlite (strange it is not available already).

For instance, I have a table of elements(Workout exercises) and I want to find all that work my "Chest".
With this module, I just have to create a virtual table using FTS5 and query with MATCH "Chest"

Good, simple example: https://www.sqlitetutorial.net/sqlite-full-text-search/

P.S.: I appreciate your work! Thank you very much.

[2shady4u]

Small test I was intending to do is not complying 🤔

According to the docs simply defining the pre-compiler symbol SQLITE_ENABLE_FTS5 should do the job, but that doesn't seem to be the case... 🤷‍♂️

[Thebas]

I think it requires recompilation, is that what you tried?

https://sqlite.org/amalgamation.html

Sorry if it's a stupid question.

[2shady4u]

After a little bit more messing around I was able to get the code to properly compile (was easy in hindsight)
I've pushed the changes to this branch:
https://github.com/2shady4u/godot-sqlite/tree/enable-fts5

You can download the DLL on that branch (demo/addons/godot-sqlite/bin/win64/libgdsqlite.dll) and try it out yourself.

However, this feature won't be merged into master since it is, in my opinion & at this very moment, a non-essential feature.

[Thebas]

Thank you!
I understand why you see it as non-essential.

Did you only update it for the windows platform?
I'm exclusively working on Linux and exporting to Android.

[2shady4u]

I've pushed the branch to Github Actions, so you can download the libraries there (once CI is finished) and replace them in your project:

MacOS:
https://github.com/2shady4u/godot-sqlite/actions/runs/545603563

Linux:
https://github.com/2shady4u/godot-sqlite/actions/runs/545603560

Windows:
https://github.com/2shady4u/godot-sqlite/actions/runs/545603565

Android:
https://github.com/2shady4u/godot-sqlite/actions/runs/545603562

iOS:
https://github.com/2shady4u/godot-sqlite/actions/runs/545603559

[Thebas]

Perfect! Thank you again!

[2shady4u]

I'm leaving this issue open (for now) for all future users that want to make use of FTS5

[hidemat]

Hey @2shady4u. If I were to try to use both this enable-fts5 feature and the encrypt/decrypt feature in issue #23 how would I do it? I'm extremely new to c++ and gdnative so I have no clue, but I need both of those features in my project. Thanks in advanced.

[2shady4u]

@hidemat This should be pretty easy as both the encrypt/decrypt and the fts5 feature are quite independent.
Unfortunately I currently don't have time to work on this due to other obligations...

Basically to enable FTS5 support in the latest master branch you should just add following line to the build script:

godot-sqlite/SConstruct

Line 330 in ed34475

env.Append(CPPDEFINES=['SQLITE_ENABLE_FTS5'])

I'll try to update both branches in a few weeks' time.
Apologies for not being able to help you at the moment 🙏

[hidemat]

That's ok. I'll see what I can do with this info. Thanks, and best of luck.

[hidemat]

I've tried the following command to build after adding the line you mentioned:
scons platform=windows

How do I build for android?

Edit: Although you know what, nevermind, I was able to get fts5 working on windows. This is enough for me to begin development. I'll be patient, and wait for you to get around to this. Best of luck.

[2shady4u]

@hidemat
I'm in the process of merging both the encrypt/decrypt and FTS5 support branches to master.
Here's some instructions on how to easily get binaries for all platforms:

1. Fork this repository
2. Update whatever you need on the master branch and push it to your fork
   (for example: add FTS5 support as discussed above)
3. Github will automatically build binaries for all platforms in the Actions tab.
   (more info: https://docs.github.com/en/actions)
4. Copy paste and replace the new binaries inside of the addons/godot-sqlite/bin/*-folder
5. All done!

In the future, both FTS5 and encrypt/decrypt will be moved to special build arguments, example:

scons platform=windows enable_fts5=true use_sqleet=true

In which you'll not need to update anything yourself anymore.
But this is currently NOT (yet) supported!

[hidemat]

Thanks so much! I'll look into this.

[hidemat]

Hello @2shady4u! Hope you're are doing well. I have a question. If I were to modify the android_builds.yml in my fork repository to include env.Append(CPPDEFINES=['SQLITE_ENABLE_FTS5']) how would I do that?

[2shady4u]

Hello @2shady4u! Hope you're are doing well. I have a question. If I were to modify the android_builds.yml in my fork repository to include env.Append(CPPDEFINES=['SQLITE_ENABLE_FTS5']) how would I do that?

Hi @hidemat !

I can't seem to find your fork 😅
I'll look into how to get FTS5 working on Android this weekend and I'll push it to the 'enable-fts5' branch

EDIT: It's working as seen here:

Now I just have to make it so that I can enable/disable the functionality from the workflow file. 🤔

EDIT2: Android support for FTS5 has now been merged into the 'enable-fts5'-branch

[hidemat]

Nice! Thanks so much.

I can't seem to find your fork 😅

Yeah I gave up on my fork, so I deleted it 😅. It was basically the sqleet branch with fts5 enabled, and I was trying to get github to enable fts5. This is not your fault, I make impulsive decisions sometimes :)

[Koyper]

I have been using a custom build of godot-sqlite (many thanks for creating this essential tool!) with the fts5 enabled for at least a year and it's perfectly stable. It's also enabled by default in the SQLite official amalgamation configure script, so I think it would be reasonble to enable fts5 by default in godot-sqlite in the master branch? I think the only downsize is the extra binary size but I believe it would be a small difference.

A branch using fts5 and sqleet enabled would also be great.

Thanks!

[Anutrix]

Was this merged to master? I need for my project.

[2shady4u]

Hello @Anutrix

This wasn't merged to master as it is not a core feature.
Modifications to the SConstruct could be easily made to enable it on master though.