Class_SQLiteDB v2.0.6 - 2023-11-28

[bonobo]

Nice. I load the .dll from here as an extension for regex queries. It's faster than using a custom callback function via RegisterCallback.
https://github.com/asg017/sqlite-regex/releases

Code: Select all

db := SQLiteDB()
db.OpenDB("dbfile.db", "R", "F")
db.EnableLoadExtension()
db.LoadExtension("Lib\SQLite\regex0.dll")
db.GetTable("SELECT name, rowid FROM table WHERE name IS NOT NULL AND regexp '\bword\b'", results)

[valuex]

@bonobo
Thanks for sharing. But it seems the regex0.dll is not compatible with current Class_SQLiteDB.ahk.
I've tried to replace SQLite3 with regex0.dll. There are some errors happen.
Could you help to see how to fix it? Thanks.

Code: Select all

#Requires AutoHotkey v2.0.0
#Warn
; ======================================================================================================================
; Function:       Sample script for Class_SQLiteDB.ahk
; AHK version:    AHK 2.0.6
; Tested on:      Win 10 Pro (x64)
; Author:         just me
; Version:        2.0.4 - 20230831
; ======================================================================================================================
; AHK Settings
; ======================================================================================================================
#SingleInstance Force
SetWorkingDir(A_ScriptDir)
OnExit((*) => Main_Close())
; ======================================================================================================================
; Includes
; ======================================================================================================================
#Include Class_SQLiteDB.ahk
; ======================================================================================================================
; Start & GUI
; ======================================================================================================================
CBBSQL := ["SELECT * FROM Test"]
DBFileName := A_ScriptDir . "\TEST.DB"
Title := "SQL Query/Command ListView Function GUI"
If FileExist(DBFileName)
   Try FileDelete(DBFileName)
Main := Gui("+Disabled +LastFound +OwnDialogs", Title)
Main.MarginX := 10
Main.MarginY := 10
Main.OnEvent("Close", Main_Close)
Main.OnEvent("Escape", Main_Close)
Main.AddText("w100 h20 0x200 vTX", "SQL statement:")
Main.AddComboBox("x+0 ym w590 Choose1 Sort vSQL", CBBSQL)
Main["SQL"].GetPos(&X, &Y, &W, &H)
Main["TX"].Move( , , , H)
Main.AddButton("ym w80 hp Default", "Run").OnEvent("Click", RunSQL)
Main.AddText("xm h20 w100 0x200", "Table name:")
Main.AddEdit("x+0 yp w150 hp vTable", "Test")
Main.AddButton("Section x+10 yp wp hp", "Get Table").OnEvent("Click", GetTable)
Main.AddButton("x+10 yp wp hp", "Get Result").OnEvent("Click" , GetResult)
Main.AddGroupBox("xm w780 h330", "Results")
LV := Main.AddListView("xp+10 yp+18 w760 h300 vResultsLV +LV0x00010000")
SB:= Main.AddStatusBar()
Main.Show()
; ======================================================================================================================
; Use Class SQLiteDB : Initialize and get lib version
; ======================================================================================================================
SB.SetText("SQLiteDB new instance")
DB := SQLiteDB()
DB.EnableLoadExtension()
DB.LoadExtension("SQLite3.dll")
; Sleep(1000)
SB.SetText("Version")
; Main.Title := Title . " - SQLite3.dll v " . SQLiteDB.Version
; Sleep(1000)
; ======================================================================================================================
; Use Class SQLiteDB : Open/Create database and table
; ======================================================================================================================
SB.SetText("OpenDB - " . DBFileName)
If !DB.OpenDB(DBFileName) {
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ExitApp
}
Sleep(1000)
SB.SetText("Exec: CREATE TABLE")
SQL := "CREATE TABLE Test (FullName, ShortName, PinYin, ETimes, LastTime,GroupN,PRIMARY KEY(FullName ASC));"
; SQL := "CREATE TABLE Test (Name, Fname, Phone, Room, PRIMARY KEY(Name ASC, FName ASC));"

If !DB.Exec(SQL)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
; Sleep(1000)
SB.SetText("Exec: INSERT 1000 rows")
Start := A_TickCount
DB.Exec("BEGIN TRANSACTION;")
SQLStr := ""


_SQL := "INSERT OR IGNORE INTO Test VALUES('FullName', 'ShortName', 'PinYin','ETimes', 'LastTime','Group');"
; Loop 1000 {
;    SQL := StrReplace(_SQL, "#", A_Index)
;    SQLStr .= SQL
; }

Folders:=["C:\ProgramData\Microsoft\Windows\Start Menu\Programs\*.lnk",
"C:\Windows\System32\*.exe"]
loop  Folders.Length
{
    Loop Files,Folders[A_Index], "R"  ; Recurse into subfolders.
    {
        OriFullName:=A_LoopFilePath
        SplitPath A_LoopFilePath, &ShortName, &OutDir, &OutExtension, &OutNameNoExt
        if(InStr(OutExtension,"lnk"))
        {
            FileGetShortcut A_LoopFilePath, &OriFullName
        }
        if(InStr(OriFullName,"unins"))
            continue
        SQL_p1 := StrReplace(_SQL, "FullName", OriFullName)
        SQL_p2 := StrReplace(SQL_p1, "ShortName", OutNameNoExt)
        SQL_p3 := StrReplace(SQL_p2, "ETimes", 1)
        SQLStr .= SQL_p3
    }
}
If !DB.Exec(SQLStr)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
DB.Exec("COMMIT TRANSACTION;")
SQLStr := ""
SB.SetText("Exec: INSERT 1000 rows done in " . (A_TickCount - Start) . " ms")
Sleep(1000)
; ======================================================================================================================
; Use Class SQLiteDB : Using Exec() with callback function
; ======================================================================================================================
SB.SetText("Exec: Using a callback function")
SQL := "SELECT COUNT(*) FROM Test;"
DB.Exec(SQL, SQLiteExecCallBack)
; ======================================================================================================================
; Use Class SQLiteDB : Get some informations
; ======================================================================================================================
SB.SetText("LastInsertRowID")
RowID := ""
If !DB.LastInsertRowID(&RowID)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
LV.Opt("-Redraw")
LV.Delete()
Loop LV.GetCount("Col")
   LV.DeleteCol(1)
LV.InsertCol(1,"", "LastInsertedRowID")
LV.Add("", RowID)
LV.Opt("+Redraw")
Sleep(1000)
; ======================================================================================================================
; Start of query using GetTable() : Get the first 10 rows of table Test
; ======================================================================================================================
SQL := "SELECT * FROM Test;"
SB.SetText("SQLite_GetTable : " . SQL)
Result := ""
If !DB.GetTable(SQL, &Result, 10)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
ShowTable(LV, Result)
Sleep(1000)
; ======================================================================================================================
; Start of query using Prepare() : Get the column names for table Test
; ======================================================================================================================
SQL := "SELECT * FROM Test;"
SB.SetText("Prepare : " . SQL)
Prepared := ""
If !DB.Prepare(SQL, &Prepared)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
LV.Opt("-ReDraw")
LV.Delete()
ColCount := LV.GetCount("Col")
Loop ColCount
   LV.DeleteCol(1)
LV.InsertCol(1,"", "Column names")
Loop Prepared.ColumnCount
   LV.Add("", Prepared.ColumnNames[A_Index])
LV.ModifyCol(1, "AutoHdr")
Prepared.Free()
LV.Opt("+ReDraw")
; ======================================================================================================================
; End of query using Prepare()
; ======================================================================================================================
Main.Opt("-Disabled")
Return
; ======================================================================================================================
; Gui Subs
; ======================================================================================================================
Main_Close(*) {
   If !DB.CloseDB()
      MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ExitApp
}
; ======================================================================================================================
; Other Subs
; ======================================================================================================================
; "One step" query using GetTable()
; ======================================================================================================================
GetTable(GuiCtrl, Info) {
   Local Result, SQL, Start, Table
   Table := Main["Table"].Text
   SQL := "SELECT * FROM " . Table . ";"
   SB.SetText("GetTable: " . SQL)
   Start := A_TickCount
   Result := ""
   If !DB.GetTable(SQL, &Result)
      MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ShowTable(LV, Result)
   SB.SetText("GetTable: " . SQL . " done (including ListView) in " . (A_TickCount - Start) . " ms")
}
; ======================================================================================================================
; Show results for prepared query using Prepare()
; ======================================================================================================================
GetResult(GuiCtrl, Info) {
   Local Prepared, SQL, Start, Table
   Table := Main["Table"].Text
   SQL := "SELECT * FROM " . Table . ";"
   SB.SetText("Query: " . SQL)
   Start := A_TickCount
   Prepared := ""
   If !DB.Prepare(SQL, &Prepared)
      MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ShowResult(LV, Prepared)
   SB.SetText("Prepare: " . SQL . " done (including ListView) in " . (A_TickCount - Start) . " ms")
}
; ======================================================================================================================
; Execute SQL statement using Exec() / GetTable()
; ======================================================================================================================
RunSQL(CtrlObj, Info) {
   Local SQL, Result
   SQL := Trim(Main["SQL"].Text)
   If (SQL = "") {
      SB.SetText("No SQL statement entered!")
      Return
   }
   If (Main["SQL"].Value = 0)
      Main["SQL"].Add([SQL])
   If (SubStr(SQL, -1) != ";")
      SQL .= ";"
   Result := ""
   If RegExMatch(SQL, "i)^\s*SELECT\s") {
      SB.SetText("GetTable: " . SQL)
      If !DB.GetTable(SQL, &Result)
         MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
      Else
         ShowTable(LV, Result)
      SB.SetText("GetTable: " . SQL . " done!")
   }
   Else {
      SB.SetText("Exec: " . SQL)
      If !DB.Exec(SQL)
         MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
      Else
         SB.SetText("Exec: " . SQL . " done!")
   }
}
; ======================================================================================================================
; Exec() callback function sample
; ======================================================================================================================
SQLiteExecCallBack(DB, ColumnCount, ColumnValues, ColumnNames) {
   This := ObjFromPtrAddRef(DB)
   Main.Opt("+OwnDialogs") ; required for the MsgBox
   MsgBox("SQLite version: " . SQLiteDB.Version . "`n" .
          "SQL statement: " . This.SQL . "`n" .
          "Number of columns: " . ColumnCount . "`n" .
          "Name of first column: " . StrGet(NumGet(ColumnNames, "Ptr"), "UTF-8") . "`n" .
          "Value of first column: " . StrGet(NumGet(ColumnValues, "Ptr"), "UTF-8"),
          A_ThisFunc, 1)
   Return 0
}
; ======================================================================================================================
; Show results
; ======================================================================================================================
ShowTable(LV, Table) {
   LV.Opt("-Redraw")
   LV.Delete()
   Loop LV.GetCount("Col")
      LV.DeleteCol(1)
   If (Table.HasNames) {
      Loop Table.ColumnCount
         LV.InsertCol(A_Index, "", Table.ColumnNames[A_Index])
      If (Table.HasRows) {
         Loop Table.Rows.Length
            LV.Add("", Table.Rows[A_Index]*)
      }
      Loop Table.ColumnCount
         LV.ModifyCol(A_Index, "AutoHdr")
   }
   LV.Opt("+Redraw")
}
; ----------------------------------------------------------------------------------------------------------------------
ShowResult(LV, Prepared) {
   LV.Opt("-Redraw")
   LV.Delete()
   Loop LV.GetCount("Col")
      LV.DeleteCol(1)
   If (Prepared.ColumnCount > 0) {
      Loop Prepared.ColumnCount
         LV.InsertCol(A_Index, "", Prepared.ColumnNames[A_Index])
      Row := ""
      RC := Prepared.Step(&Row)
      While (RC > 0) {
         LV.Add("", Row*)
         RC := Prepared.Step(&Row)
      }
      If (RC = 0)
         MsgBox("Msg:`t" . Prepared.ErrorMsg . "`nCode:`t" . Prepared.ErrorCode, A_ThisFunc, 16)
      Loop Prepared.ColumnCount
         LV.ModifyCol(A_Index, "AutoHdr")
   }
   LV.Opt("+Redraw")
}

[bonobo]

@bonobo
Thanks for sharing. But it seems the regex0.dll is not compatible with current Class_SQLiteDB.ahk.
I've tried to replace SQLite3 with regex0.dll. There are some errors happen.
Could you help to see how to fix it? Thanks.

Hi @valuex, the regex0.dll is just an sqlite extension, and is to be loaded *separately* from sqlite3.dll, not as a replacement of it.
You want to load the official sqlite3.dll in the normal way[1], *then* create a new instance by calling SQLiteDB(), and then load the extension dll.




[1] From @justme's code:

Code: Select all

; SQLite3.dll file is assumed to be in the script's folder, otherwise you have to
; provide an INI-File SQLiteDB.ini in the script's folder containing the path:
; [Main]
; DllPath=Path to SQLite3.dll

[william_ahk]

Can you show us an example of using prepared statements to insert values?

[william_ahk]

Hey @just me, I was wondering why my prepared statements didn't work, then I found there is an error at _Prepared.Bind:

Code: Select all

For ParamType, Value In Param {
    If !Types.HasOwnProp(Type)

I assume you forgot to change Type to ParamType when converting from v1 where Type was allowed as a variable name.

[just me]

Hey @william_ahk,
you're right. Thanks for reporting. Should be fixed!

[Qriist]

Heya @just me, I'm sharing a small QOL method that I added to the _Table subclass. This returns the row as a Map with associated column names for easier direct referencing.

Code: Select all

; ----------------------------------------------------------------------------------------------------------------
		; METHOD NextNamed   Get next row depending on _CurrentRow; same as Next() but returns a Map with column names
		; Parameters:        ByRef Row   - Variable to pass out the row Map 
		; Return values:     On failure  - False, -1 for EOR (end of rows)
		;                    On success  - True, Row contains a valid Map 
		; ----------------------------------------------------------------------------------------------------------------
		NextNamed(&Row) {
			Row := Map()
			If (This._CurrentRow >= This.RowCount)
				Return -1
			This._CurrentRow += 1
			If !This.Rows.Has(This._CurrentRow)
				Return False
			for k,v in This.Rows[This._CurrentRow]{
				Row[This["ColumnNames"][k]] := v
			}
			Return True
		}

Also, do you have a github account? I would have just pull requested this but I couldn't find your profile.

Thanks for the awesome class!

[just me]

Heya @Qriist,

I still don't see the advantage of accessing columns of a result row by name. What you do in your NextNamed() method can also be done outside of the class if the user wants to do it. It's really simple:

Code: Select all

; We do it once after we got the table:
CN := Map() ; C(olumn)N(ames)
For Index, Name In Table.ColumnNames
	CN[Name] := Index
...
...
; Get the colum content
Value := Table.Rows[RowIndex][CN["ColName"]]

[Qriist]

advantage of accessing columns of a result row by name

The whole point is that it's a Quality of Life (QOL) method specifically designed to avoid the boiler plate you mentioned. More precisely, it moves the boiler plate to where it belongs: inside the class.

It is true that there's no real programmatic advantage to directly referencing the column names. However there's huge programmer advantages in that a person authoring a script using your class can use the returned information without needing to track exactly which column number contains which data - they can instead use the more human-readable column name that they would probably assign anyways. This frees up mental load better used for writing code actually using the returned row data.

At any rate, you also didn't answer if you had a github account.

[just me]

@Qriist -> https://github.com/AHK-just-me

The whole point is that it's a Quality of Life (QOL) method ...

... LOL, we are programmers, the tough guys.

[xMaxrayx]

hi Op thanks can you list a license?

[just me]

@xMaxrayx, The Unlicense

[xMaxrayx]

@xMaxrayx, The Unlicense

I see many thanks <3

[pho7271]

@just me
I have just started using SQLite database and am currently using Class_SQLiteDB v2.0.6. I have a question. If I want to get the results of a query statement,
For example: select count (*), sum (je1), sum (xm), sum (ap) from test;
How can I get the correct values for count (*), sum (je1), sum (xm), and sum (ap)? thanks

[just me]

For select count (*), sum (je1), sum (xm), sum (ap) from test; GetTable() should return an object TB with one result row containing 4 columns. You should get the values using

Code: Select all

Count := TB.Rows[1][1]
SumJE1 := TB.Rows[1][2]
SumXM := TB.Rows[1][3]
SumAP := TB.Rows[1][4]

[pho7271]

@just me Thank you very much