SQLite Forum

Lua db:close deletes WAL file
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. 
[![The process flow][1]][1]

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

print("start lua")

local sqlite3 = prequire("lsqlite3complete")
if sqlite3 == nil then
    print('No lsqlite3')

local db = sqlite3.open('/data/data/ru.chromesearcher.sqlitewal/files/database.sqlite3')
if db == nil then
    print("db open failed")

print("query start")

local stmt = db:prepare('SELECT * FROM paymentevents where pe_amount=?')
local amount = 3000


  [1]: https://i.stack.imgur.com/8rFsj.jpg