I have a question concerning SQLite & Lua. Basically i'm trying to use SQLite in WAL mode in my Android app. However if i perform a particular set of operations with database from my Lua script, it deletes `-wal` file. It leads to the problem: some of my transactions could be lost if the device loses power, and i don't have opportunity to close DB connection properly. The flow is attached in picture below.
Database opened this way (c++):
`int32 rcOpen = sqlite3_open(filename.c_str(), &handle);`
Also, after connection opened, i use this `pragma`'s
```
PRAGMA journal_mode=WAL;
PRAGMA synchronous=FULL;
```
More details:
- I do manually commit transactions by `BEGIN TRANSACTION;` + `END TRANSACTION;`. I check if transactions have been commited via registered `sqlite_wal_hook`, which shows that number of pages in WAL file increases after INSERT. However i am not sure what happens to transactions that are transferred from WAL file to main DB file during checkpoint.
- After Lua DB connection closed, there is STILL one c++ connection that is opened, so there is no reason to delete WAL file.
- If i do db:close straight after sqlite3.open, without extra action between them - WAL file is not deleted
- Also tried `PRAGMA synchronous=EXTRA + PRAGMA locking_mode=EXCLUSIVE` - didn't make any difference
- Also tried using file controle mode `SQLITE_FCNTL_PERSIST_WAL` - didn't make any difference either
```
int enabled = 1;
int rcControl = sqlite3_file_control(handle, "main", SQLITE_FCNTL_PERSIST_WAL, &enabled);
```
Stack: android 7 (fs ext4) - JNI (NDK 21.4.7075529) - c++ 11 - sqlite 3.27.2 - lua 5.3.4 - lsqlite3complete 0.9.5
The lua script i use for reproducing the problem:
```
function prequire(name)
local status, lib = pcall(require, name)
if(status) then return lib end
--Library failed to load
return nil
end
print("start lua")
local sqlite3 = prequire("lsqlite3complete")
if sqlite3 == nil then
print('No lsqlite3')
return
end
local db = sqlite3.open('/data/data/ru.chromesearcher.sqlitewal/files/database.sqlite3')
if db == nil then
print("db open failed")
return
end
print("query start")
local stmt = db:prepare('SELECT * FROM paymentevents where pe_amount=?')
local amount = 3000
stmt:bind_values(amount)
stmt:finalize()
db:close()
```
