[Library] Lite sqlite3 wrapper library

[wbm1113]

https://github.com/wbm1113/Class_sqlite3.ahk/blob/master/Class_sqlite3.ahk

I needed to incorporate a database in a project I was working on. The only other AHK libraries I could find were very large and very involved. If you have a large-scale application in mind, you'll probably want to go with those as they offer more efficient querying. If you're working on something smaller scale and you don't mind sacrificing efficiency for convenience, or you just want to learn SQL through AHK, this will be helpful for you.

Make sure you have sqlite.dll from sqlite.org.

To use it, make a new instance of the SQLiteObj:

Code: Select all

SQL := new SQLiteObj(Path_To_Your_sqlite3DLL_File, Path_To_Database_You_Want_To_Work_With)

Create and populate tables within your database:

Code: Select all

SQL.Query("CREATE TABLE SampleTable(Items, Stuff);")
SQL.Query("INSERT INTO SampleTable VALUES(1, 'a');")
SQL.Query("INSERT INTO SampleTable VALUES(2, 'b');")
SQL.Query("INSERT INTO SampleTable VALUES(3, 'c');")

Get values from your database:

Code: Select all

Values := SQL.Query("SELECT Items, Stuff FROM SampleTable WHERE Items > 1;")

The output format is a simple array:

Code: Select all

[column1_value1, column1_value2, ...] ; selecting from 1 column
[column1_value1, column2_value1, column1_value2, column2_value2, ...] ; selecting from 2 columns, and so on

And that's it.

Code: Select all

#SingleInstance, Force



;======================================================================
;======================================================================
;======================================================================

; You must download sqlite3.dll from sqlite.org to use this library

;======================================================================
;======================================================================
;======================================================================




; RegisterCallback does not support ObjBindMethod(), so we need
; a global variable to pass between the SQLiteObj class instance
; and the callback function.  if you are using AHK v2 you can
; change this
global Query := []



; create a test database file 
FileAppend, % "", % A_Desktop "\Sample.db"



; construct SQLiteObj
; param1 = sqlite3.dll file path
; param2 = target database file path
SQL := new SQLiteObj(A_Desktop "\sqlite3.dll", A_Desktop "\Sample.db")
if SQL=0
    return



; queries to populate db with sample data
SQL.Query("CREATE TABLE SampleTable(Items, Stuff);")
SQL.Query("INSERT INTO SampleTable VALUES(1, 'a');")
SQL.Query("INSERT INTO SampleTable VALUES(2, 'b');")
SQL.Query("INSERT INTO SampleTable VALUES(3, 'c');")



; basic SELECT FROM WHERE query
; return value is always a simple array, in this case:
; [item1, stuff1, item2, stuff2, ...]
Values := []
Values := SQL.Query("SELECT Items, Stuff FROM SampleTable WHERE Items > 1;")
MsgBox % Values[1] " | " Values[2] " | " Values[3] " | " Values[4]



; supports dynamic queries
Values := []
Cols := "Items, Stuff"
Table := "SampleTable"
Condition := "Items > 0"
Values := SQL.Query("SELECT " Cols " FROM " Table " WHERE " Condition ";")



; placing values into an associative array
SampleTask := Object()
Loop 3 {
    i := A_Index * 2 - 1
    SampleTask[Values[i]] := Values[i+1]
}

for key, val in SampleTask
    MsgBox % key ":" val

return


class SQLiteObj
{
    __new(Path_SQLDLL, Path_DB) {
        if FileExist(Path_SQLDLL)="" { 
            msgbox sqlite3.dll not found!  Go get sqlite3.dll from sqlite.org.
            return 0
        }
        This.Library := DllCall("LoadLibrary", "Str", Path_SQLDLL, "Ptr")
        This.Callback := RegisterCallback("QueryCallback", "F C", 4)
        This.Open(Path_DB)
    }

    Open(Path_DB) {
        DB := 0
        VarSetCapacity(Address, StrPut(Path_DB, "UTF-8"), 0)
        StrPut(Path_DB, &Address, "UTF-8")
        DllCall("sqlite3.dll\sqlite3_open_v2" ; https www.sqlite.org /c3ref/open.html  Broken Link for safety
                , "Ptr", &Address
                , "PtrP", DB
                , "Int", 2
                , "Ptr", 0
                , "CDecl Int")
        This.Database := DB
    }

    Query(Q) {
        Query := []
        VarSetCapacity(Address, StrPut(Q, "UTF-8"), 0)
        StrPut(Q, &Address, "UTF-8")        
        DllCall("sqlite3.dll\sqlite3_exec" ; https www.sqlite.org /c3ref/exec.html  Broken Link for safety
                , "Ptr", This.Database
                , "Ptr", &Address
                , "Ptr", This.Callback)
        return Query
    }
}

QueryCallback(ParamFromCaller, Columns, Values, Names) {
    Loop %Columns%
        Query.Push(StrGet(NumGet(Values+0, (A_Index-1)*8, "uInt"), "UTF-8"))
    return 0
}

[Relayer]

Hi,

It appears the caller can pass a value to sqlite3_exec in the 4th parameter position that will then be returned as the 1st parameter of the callback. I think we can use this to eliminate the need for a global.

Relayer

Code: Select all

class SQLiteObj
{
	__New(Path_SQLDLL, Path_DB) {
		if FileExist(Path_SQLDLL)="" { 
			msgbox sqlite3.dll not found!  Go get sqlite3.dll from sqlite.org.
			return 0
		}
		this.Library := DllCall("LoadLibrary", "Str", Path_SQLDLL, "Ptr")
		this.Callback := RegisterCallback("QueryCallback", "F C", 4)
		this.Open(Path_DB)
		this.Reply := []
	}

	Open(Path_DB) {
		DB := 0
		VarSetCapacity(Address, StrPut(Path_DB, "UTF-8"), 0)
		StrPut(Path_DB, &Address, "UTF-8")
		DllCall("sqlite3.dll\sqlite3_open_v2" ; https://www.sqlite.org/c3ref/open.html
				, "Ptr", &Address
				, "PtrP", DB
				, "Int", 2
				, "Ptr", 0
				, "CDecl Int")
		this.Database := DB
	}

	Query(Q) {
		this.Reply := []
		VarSetCapacity(Address, StrPut(Q, "UTF-8"), 0)
		StrPut(Q, &Address, "UTF-8")
		DllCall("sqlite3.dll\sqlite3_exec" ; https://www.sqlite.org/c3ref/exec.html
				, "Ptr", this.Database
				, "Ptr", &Address
				, "Ptr", this.Callback
				, "Int", Object(this))
		return this.Reply
	}
}

QueryCallback(ParamFromCaller, Columns, Values, Names) {
	Loop %Columns%
		Object(ParamFromCaller).Reply.Push(StrGet(NumGet(Values+0, (A_Index-1)*8, "uInt"), "UTF-8"))
	return 0
}

[aifritz]

Hi,
can someone tell me, how I can get the values here: 'a', 'b', 'c'?
Running the example it shows only the indexes