Feature Request: Encryption and Fts5

[hidemat]

Hi. I don't know if these features have been implemented or not, but these are really useful to have on database related libraries:

1. FTS5 enabled: useful for glossary type applications where there is a huge dataset to search though to get a particular term from the database.

2. Encryption/decryption: useful for protecting sensitive information stored in a database. I believe godot-sqlite gdnative implemented this with sqleet

[fire]

I'll look into this.

1. https://github.com/resilar/sqleet
2. FTS5 enabled

[hidemat]

Hello @fire! I hope your doing ok. So I've been playing around with this module. I was able to get both FTS5 and Sqleet working, but I'm no expert, and I'm extremely new to c++, so I'm pretty sure I broke something. But here is what I did in case you every wanted to implement this in a way that doesn't break things:

For FTS5 It's pretty straight forward, just add the module_env.Append(CPPDEFINES=["SQLITE_ENABLE_FTS5"]) line to your SCsub file

For Sqleet Sqleet is pretty much a wrapper for sqlite3, and it implements most if not all of the sqlite3 methods (not sure on this). So:

1. Import sqleet into the sqlite module folder
2. replace #include <sqlite3.h> with #include <sqleet.h>
3. add a password parameter to the SQLite::open() or where ever the sqlite3_open / sqlite3_open_v2, possibly in spmemvfs_open_db
4. make a call similar to this after the db succesfully opens to authenticate the password:

if (!password.empty())
{
	const char* char_key = password.utf8().get_data();
	result = sqlite3_key(db, char_key, strlen(char_key));
	if (result != SQLITE_OK)
	{
		/* Returned error is always SQLITE_AUTH (23) which is caused by either: */
		/* - Supplying a wrong password */
		/* - Attempting to open an unencrypted database using a password */
		print_error("Authorization denied due to incorrect password! Was the database previously encrypted?");
		/* Note for self: Unsure if the database should automatically be closed here or not... */
		return false;
	}
}

Note: I'm not saying this is the best way to do it. I'm just presenting it to you guys as a starting point. :)

[fire]

Do you want me to implement this into a branch and maybe merge?

I'm not able to dedicate as much effort :(. I recommend you pr this.

[fire]

Noticed that sqleet -DSKIP_HEADER_BYTES=24 allows unencrypted headers so that that page size and other settings can be directly read from encrypted databases.

[hidemat]

Do you want me to implement this into a branch and maybe merge?

I'm not able to dedicate as much effort :(. I recommend you pr this.

I'll try implementing it and doing a pull request. I've never contributed to a project before, so this should be fun.

Noticed that sqleet -DSKIP_HEADER_BYTES=24 allows unencrypted headers so that that page size and other settings can be directly read from encrypted databases.

Sorry I don't know what this means. I'm mostly interested in preventing people from seeing the actual data.

[fire]

This means increased reliability through showing the file is a sqlite database, but the contents is still encrypted.

[hidemat]

Hi I forked this repository to make a pull request with the changes I made. I made a branch called sqleet-fts. How do I make a pull request for that branch? It's only letting me PR to the master branch.

[fire]

It should be possible to change the fork to be https://github.com/godot-extended-libraries/godot-sqlite.

Reach out if you're still stuck.

[hidemat]

Should I do it like this?

[fire]

I forgot about this. Do you want me to pull from your repo?

[fire]

I've been migrating to libsql which has FTS5 integrated.

https://github.com/V-Sekai/godot-libsql.

I'm going to archive this repo in a few days.