Iczer

SQLite in AutoIt & multythreaded DLL

4 posts in this topic

#1 ·  Posted

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted

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?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#3 ·  Posted

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?

 

Share this post


Link to post
Share on other sites

#4 ·  Posted

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now