[Class] SQLiteDB - Update on 2022-10-04

[just me]

Code: Select all

;                   0.0.08.00/2019-03-09/just me   -  Added basic support for application-defined functions
...
   ; ===================================================================================================================
   ; METHOD CreateScalarFunc  Create a scalar application defined function
   ; Parameters:              Name  -  the name of the function
   ;                          Args  -  the number of arguments that the SQL function takes
   ;                          Func  -  a pointer to AHK functions that implement the SQL function
   ;                          Enc   -  specifies what text encoding this SQL function prefers for its parameters
   ;                          Param -  an arbitrary pointer accessible within the funtion with sqlite3_user_data()
   ; Return values:           On success  - True
   ;                          On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; Documentation:           www.sqlite.org/c3ref/create_function.html
   ; ===================================================================================================================
   ...
   ; ======================================================================================================================
; Exemplary custom callback function regexp()
; Parameters:        Context  -  handle to a sqlite3_context object
;                    ArgC     -  number of elements passed in Values (must be 2 for this function)
;                    Values   -  pointer to an array of pointers which can be passed to sqlite3_value_text():
;                                1. Needle
;                                2. Haystack
; Return values:     Call sqlite3_result_int() passing 1 (True) for a match, otherwise pass 0 (False).
; ======================================================================================================================

[jsong55]

Code: Select all

;                   0.0.08.00/2019-03-09/just me   -  Added basic support for application-defined functions
...
   ; ===================================================================================================================
   ; METHOD CreateScalarFunc  Create a scalar application defined function
   ; Parameters:              Name  -  the name of the function
   ;                          Args  -  the number of arguments that the SQL function takes
   ;                          Func  -  a pointer to AHK functions that implement the SQL function
   ;                          Enc   -  specifies what text encoding this SQL function prefers for its parameters
   ;                          Param -  an arbitrary pointer accessible within the funtion with sqlite3_user_data()
   ; Return values:           On success  - True
   ;                          On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; Documentation:           www.sqlite.org/c3ref/create_function.html
   ; ===================================================================================================================
   ...
   ; ======================================================================================================================
; Exemplary custom callback function regexp()
; Parameters:        Context  -  handle to a sqlite3_context object
;                    ArgC     -  number of elements passed in Values (must be 2 for this function)
;                    Values   -  pointer to an array of pointers which can be passed to sqlite3_value_text():
;                                1. Needle
;                                2. Haystack
; Return values:     Call sqlite3_result_int() passing 1 (True) for a match, otherwise pass 0 (False).
; ======================================================================================================================

Thanks for sharing. Would you mind providing an example of how to implement this in say

SELECT * FROM TABLE WHERE Email....

Where if we use ahk regex like below and return TRUE the query above would check

Regexmatch(email, ".+?\@gmail\.com")

[just me]

You might try (not tested):

Code: Select all

; Requires CLass_SQLiteDB v 0.0.08.00+
DB := New SQLiteDB
DB.OpenDB("Test.db")
DB.CreateScalarFunc("regexp", 2, RegisterCallback("SQLiteDB_RegExp", "CDecl"))
SQL := "SELECT * FROM TABLE WHERE Email REGEXP '.+?\@gmail\.com';"
DB.GetTable(SQL, Result)

Edit: Changed callback function name and added "CDecl" for AHK 32
Edit2: Added DB.OpenDB() because it's needed before you can call DB.CreateScalarFunc().

[jsong55]

Code: Select all

acDB := New SQLiteDB
acDB.CreateScalarFunc("regexp", 2, RegisterCallback("SQLiteDB_RegExp"))
acDB.OpenDB("accor.db","W",False)
SQL := "SELECT * FROM prices WHERE hotel regexp 'Swissotel'"
SQL2:="SELECT * FROM prices"
acDB.GetTable(SQL2, Result)

@just me - in the above, SQL2 works but SQL doesn't. I did also change the register Callback from SQLiteDB_RegExp to SQLite3_RegExp to no avail.

[safetycar]

@just me - in the above, SQL2 works but SQL doesn't. I did also change the register Callback from SQLiteDB_RegExp to SQLite3_RegExp to no avail.

Are you trying as win32? I noticed that I couldn't make it work with it, but switching to win64 it worked (both ahk-exe and the dll).

The win32 error I'm seeing for the regex query is:

Code: Select all

SQLite Error: GetTable
---------------------------
Msg:	DLLCall sqlite3_get_table failed!
Code:	3221225477

[just me]

Hi,

for AHK32 you need:

Code: Select all

RegisterCallback("SQLiteDB_RegExp", "CDecl") ; CDecl is required to work with AHK32

[safetycar]

Thanks, It worked. Casually I was looking at that part of the docs before reading your reply.

[jsong55]

@just me
For some reason I'm still not able to get it to work. I opened the message box on windows spy and it states it's AutohotkeyU64.exe

[just me]

@jsong55:
I used this script with the TEST.DB created by SQLiteDB_sample.ahk:

Code: Select all

#NoEnv
#SingleInstance force
SetWorkingDir, %A_ScriptDir%
SetBatchLines, -1
DBFileName := "TEST.DB"
DB := New SQLiteDB
If !DB.OpenDB(DBFileName) {
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
   ExitApp
}
DB.CreateScalarFunc("regexp", 2, RegisterCallback("SQLiteDB_RegExp", "CDecl"))
SQL := "SELECT * FROM test WHERE name REGEXP '\D\d\d$';"
Result := ""
If !DB.GetTable(SQL, Result)
   MsgBox, 16, SQLite Error: GetTable, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
Else
{
   Values := ""
   For Each, Row In Result.Rows {
      For Index, Col In Row
         Values .= Row[Index]  . "`t"
      Values .= "`n"
   }
   Gui, Add, Text, xm, % "RowCount: " . Result.RowCount
   Gui, Add, Text, xm, % "ColumnCount: " . Result.ColumnCount
   Gui, Add, Edit, xm w1000 r25 vED
   GuiControl, , ED, %Values%
   Gui, Show, , Result
}
DB.CloseDB()
Return
; ----------------------------------------------------------------------------------------------------------------------
GuiClose:
ExitApp
; ----------------------------------------------------------------------------------------------------------------------
#Include Class_SQLiteDB.ahk

Do you get a result?

[jsong55]

I finally got a result, but I found where the problem was, I need to open the DB before creating the scalar. Doing the reverse would not work.

Thanks for your patience and help tho! @just me

[just me]

Right, my bad. Sorry!

[jsong55]

Right, my bad. Sorry!

Not yours haha

[tester]

Hello,

What about implementing the ability to set a custom sqlite3.dll path from the constructor? Something like,

Code: Select all

db := new SQLiteDB( A_PtrSize == 4 ? A_ScriptDir "\bin\sqlite3.x86.dll" : A_ScriptDir "\bin\sqlite3.x64.dll" )

would be nice.

[just me]

Hi,

you can change the "private" property SQLiteDB._SQLiteDLL before you create instances:

Code: Select all

SQLiteDB._SQLiteDLL := A_PtrSize = 4 ? A_ScriptDir . "\bin\sqlite3.x86.dll" : A_ScriptDir . "\bin\sqlite3.x64.dll"

[tester]

I'd just do

Code: Select all

class SQLiteDB2 extends SQLiteDB {

    __New( sPathSQLite3DLL:="" ) {
        this.base._SQLiteDLL := sPathSQLite3DLL && FileExist( sPathSQLite3DLL )
            ? sPathSQLite3DLL
            : A_ScriptDir . "\SQLite3.dll"
        base.__New()
    }

It seems the file name sqlite3.dll must not be renamed. Renaing it to sqlite3.x86.dll causes an incompatible version error. So what I do now is to create a directory and put it in there like /bin/x86/sqlite3.dll and /bin/x64/sqlite3.dll.

It might be useful to have a clean-up (database close) method that doesn't return anything for a OnExit() callback as the script cannot exit if true is returned. So currently, this is what I do

Code: Select all

class SQLiteDB2 extends SQLiteDB {

    bCloseOnExit := true

    __New( sPathSQLite3DLL:="" ) {
        this.base._SQLiteDLL := sPathSQLite3DLL && FileExist( sPathSQLite3DLL )
            ? sPathSQLite3DLL
            : A_ScriptDir . "\SQLite3.dll"
        base.__New()

        If ( this.bCloseOnExit ) {
            OnExit( ObjBindMethod( this, "closeDatabase" ) )
        }
    }

    closeDatabase() {
        this.CloseDB()
    }

}

By the way, do you know a way to create an in-memory database that can be accessed from across separate processes?

[inseption86]

Good afternoon I use this tool to read and write from different devices, how can I do multithreading?

OpenDB(DBFileName_range, Access := "R" , "Select certif from range_certificate ORDER BY certif DESC LIMIT 1", get_rows)

Code: Select all

OpenDB(DBPath, Access, Arg,  ByRef get_rows)
{  
   DB := new SQLiteDB
   DB.OpenDB(DBPath)

   If !DB.OpenDB(DBPath, Access, 0) 
   {
      MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
      ExitApp
   }
   

   if(Access = "R")
   {
      If !DB.GetTable(Arg, Result)
         MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode

      get_rows := Result
   }
   else if(Access = "W")
   {
     
     DB.Exec("BEGIN TRANSACTION;")
     
     check_mydata:
      If !DB.Exec(Arg)
      {
         if Instr(DB.ErrorMsg, "database is locked")
         {
            ToolTip, % DB.ErrorMsg ".....One moment!!"
            sleep 1000
            goto check_mydata
         }
         else
            MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
      }

      DB.Exec("COMMIT TRANSACTION;")
   }

 
   DB.CloseDB()
}

[just me]

Hi, AHK v1 and also v2 are single-threaded.

[inseption86]

Hi, AHK v1 and also v2 are single-threaded.

this code - normal variant?

Code: Select all

     check_mydata:
      If !DB.Exec(Arg)
      {
         if Instr(DB.ErrorMsg, "database is locked")
         {
            ToolTip, % DB.ErrorMsg ".....One moment!!"
            sleep 1000
            goto check_mydata
         }
         else
            MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
      }

[just me]

Code: Select all

      While !DB.Exec(Arg)
      {
         If Instr(DB.ErrorMsg, "database is locked")
         {
            ToolTip, % DB.ErrorMsg ".....One moment!!"
            Sleep 1000
            Continue
         }
         MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
         Break
      }

?

[inseption86]

Code: Select all

      While !DB.Exec(Arg)
      {
         If Instr(DB.ErrorMsg, "database is locked")
         {
            ToolTip, % DB.ErrorMsg ".....One moment!!"
            Sleep 1000
            Continue
         }
         MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
         Break
      }

?

thanks