SQLite in AutoIt & multythreaded DLL

[Iczer]

I' using  SQLite in AutoIt script and want to shift some DB operations to multithreaded DLL.

How I should plan DB/SQLite Initialisation/Open/.../Close/Shutdown correctly in main AutoIt script and threads?

Main AutoIt script startup:

;------------------------------------------------------------------------------------------
_SQLite_StartupEX($sPathTo_SQLiteDLL)
If @error Then Exit MsgBox(16,"SharpReaderEX_Init()", "_SQLite_Startup() Error = " & @error & ", Exiting...")
;------------------------------------------------------------------------------------------
Local Const $SQLITE_CONFIG_SERIALIZED = 3
Local $iRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_config", "int", $SQLITE_CONFIG_SERIALIZED)
ConsoleWrite("sqlite3_config(SQLITE_CONFIG_SERIALIZED) Error = " & @error & ",  Return Value = " & $iRval[0] & @LF)
;------------------------------------------------------------------------------------------
Global $hDB = _SQLite_Open($sPathTo_DB)
ConsoleWrite("_SQLite_Open status: @error = " & @error & '; @extended = ' & @extended & @LF)
;------------------------------------------------------------------------------------------
_SQLite_Exec($hDB, "PRAGMA page_size = " & $iSQLiteDBPageSize & ";")
_SQLite_Exec($hDB, "PRAGMA cache_size = " & $DBCacheSize & ";" )
_SQLite_Exec($hDB, "PRAGMA synchronous = NORMAL;" )
;------------------------------------------------------------------------------------------
_SQLite_SetTimeout($hDB, 888888)
;------------------------------------------------------------------------------------------

I pass only Query string and path to DB to the thread function in DLL.

SQLite Part of Thread in DLL

'------------------------------------------------------------------------------------------
sqlite3_initialize()
'------------------------------------------------------------------------------------------
Dim As sqlite3 Ptr hDB_Thread

RetValue = sqlite3_open(tData.PathToDB, @hDB_Thread)

If (RetValue = SQLITE_OK) then
    
    sqlite3_busy_timeout(hDB_Thread, 88888)
    
    RetValue = SQLite3_GetTable_1d(hDB_Thread, tData.Query, aRezult(), iRowNumber, iColumnNumber)

    If (RetValue = SQLITE_OK) Then
        For i As Integer = 1 To iRowNumber
            tData.aItems(i) = *WinAPI_MultiByteToWideChar(CP_UTF8, StrPtr(aRezult(i)))
        Next i
    EndIf
Else
    Print " Cannot open database:" + *sqlite3_errstr(RetValue) + " "
    Sleep(333)
    Exit Sub
EndIf
'------------------------------------------------------------------------------------------
sqlite3_close(hDB_Thread)
sqlite3_shutdown()
'------------------------------------------------------------------------------------------

Is it correct way of use SQLite dll in multiheaded dll/script?

[jchd]

First and most importantly, which issue(s) do you expect to solve by devoting some DB operations to a multithreaded library?

Will threads be all readers, some writers, which mix?

Will everythread have its own connection?

[Iczer]

foremost i wanted to make main .au3 script responsive at any time - it has fancy gui and based on DB implementation of huge-ListView by LarsJ. As AutoIt is single threaded, any time-consuming or frequently repeated db operation or calculations make gui lag . So i wanted to move all tasks beside light and gui-related to threads.

Threads can only read, read and write to db, update db, export some data, etc.

For now main script and any thread make its own connection to db.

 

Would it be better to use single handle for all threads?

About setting  $SQLITE_CONFIG_SERIALIZED - when and where i should set it? Currently i'm doing it after sqlite startup in main thread only. As I make new db connection in every thread, should I repeat this dll-call also?

Should I init/shutdown sqlite in every thread or only in main?

 

[jchd]

I'm unsure that multi-threading things can gain anything. A lengthy DB operation will take more time done by a thread than "inline" (or so) code. The issue with GUI being locked can be solved by the OnEvent mode. Besides it merely boils down to optimizing the DB schema for the lengthy operations. Of course using the Wal mode will help by allowing many readers and one writer.

Anyway, you may invoke Init/Shutdown in the main code (subsequent calls are NoOps) but this is definitely optional.

The default configuration (ThreadSafe=1 and threading mode = Serialized) makes it possible to share the same DB connection between threads, unless the library has been compiled with unusual options.

Now realize that when in Serialized mode, threads will block each other thanks to ... serialization!

If you're doing disk-intensive requests, the main bottleneck will be disk I/O and making the OS and hardware dance from A to B to C to A to D will only waste more time and flush disk cache faster than doing otherwise.

Ask yourself first which operations are actually snails and why.