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

Post your working scripts, libraries and tools for AHK v1.1 and older
robodesign
Posts: 932
Joined: 30 Sep 2017, 03:59
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by robodesign » 21 Nov 2020, 14:58

kczx3 wrote:
21 Nov 2020, 12:03
I don’t think that applies. First, connections to the same database by different AHK processes is not the same as multiple connections across threads. Second, it looks as though SQLite is compiled in a thread safe manner by default.
True. I have different AHK processes. And, yes - it is compiled to be thread safe. So, in theory, it should work. And I commented RecordSet.Free(), everything works fine. It is just that line that seems to cause issues. I also updated to the latest SqlLite3.dll, and it is all the same. It crashes. Any suggestions? Based on my understanding, one should serialize the database connection[s).

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by just me » 22 Nov 2020, 05:13

Hi,

would you please provide a code sample showing how you process the record sets?

Just out of curiosity, why do you need multipe scripts working on the same database file simultaneously?

robodesign
Posts: 932
Joined: 30 Sep 2017, 03:59
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by robodesign » 22 Nov 2020, 08:45

Hello

Code: Select all

; main thread opens the database like this:
  activeSQLdb := new SQLiteDB
  If !activeSQLdb.OpenDB(fileName)
     Return -1


; each thread opens the database in this manner:
    activeSQLdb := new SQLiteDB
    If activeSQLdb.OpenDB(sqlDBfile, "R", 0)
       ExitApp

; each thread executes this loop:

Loop, % count
    retrieveSQLdbEntryImgHisto(filesArray[A_Index, 1])


; ===================
retrieveSQLdbEntryImgHisto(fullPath, imgIndex) {
  RecordSet := ""
  SplitPath, fullPath, fileNamu, imgPath
  activeSQLdb.EscapeStr(imgPath)
  activeSQLdb.EscapeStr(fileNamu)
  SQL := "SELECT imgmedian, imgavg, imghpeak, imghlow FROM images WHERE (imgfile=" fileNamu " AND imgfolder=" imgPath ");"
  If !activeSQLdb.Query(SQL, RecordSet)
     Return 0

  obju := []
  Loop
  {
     If (Row[1] || Row[2] || Row[3])
     {
        obju.median := Row[1]
        obju.avg := Row[2]
        obju.peak := Row[3]
        obju.low := Row[4]
     }
     RC := RecordSet.Next(Row)
  } Until (RC<1)

  RecordSet.Free()
  if (obju.median>0 || obju.avg>0 || obju.peak>0)
  {
     filesArray[imgIndex, 2] := obju
  } Else
  {
    ; load image file and blah blah 
  }
}

Please note, single threaded, i never experienced crashes.

The database is created in a different session with this structure:

Code: Select all

   activeSQLdb := new SQLiteDB
   activeSQLdb.OpenDB(newFileNamu)

   SQL := "CREATE TABLE images (imgfile TEXT COLLATE NOCASE, imgfolder TEXT COLLATE NOCASE, fsize NUMERIC, fmodified NUMERIC, fcreated NUMERIC, imgwidth NUMERIC, imgheight NUMERIC, imgframes NUMERIC, imgpixfmt TEXT, imgmedian NUMERIC, imgavg NUMERIC, imghpeak NUMERIC, imghlow NUMERIC, PRIMARY KEY(imgfolder ASC, imgfile ASC));"
   SQL .= "CREATE TABLE imagesData (imgfile TEXT COLLATE NOCASE, imgCaption TEXT, imgAudio TEXT COLLATE NOCASE, PRIMARY KEY(imgfile ASC));"
   SQL .= "CREATE TABLE dynamicfolders (imgfolder TEXT COLLATE NOCASE, fmodified NUMERIC, PRIMARY KEY(imgfolder ASC));"
   SQL .= "CREATE TABLE staticfolders (imgfolder TEXT COLLATE NOCASE, fmodified NUMERIC, PRIMARY KEY(imgfolder ASC));"
   SQL .= "CREATE TABLE settings (paramz TEXT COLLATE NOCASE, valuez TEXT COLLATE NOCASE, PRIMARY KEY(paramz ASC, valuez ASC));"
   If !activeSQLdb.Exec(SQL)
      Return activeSQLdb.ErrorMsg
The main database for tests contains 350 000 records, but i can create others smaller or bigger...

The purpose is: cache data collected from images [done, works fine] and then when needed, reuse it such that the files are not reloaded again - to have everything go much faster. The issue arrises when I spawn new instances to ping the database to see if the infos are cached or not.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by kczx3 » 22 Nov 2020, 10:13

Now you are saying threads... which is it? Separate processes or threads?

robodesign
Posts: 932
Joined: 30 Sep 2017, 03:59
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by robodesign » 22 Nov 2020, 10:27

kczx3 wrote:
22 Nov 2020, 10:13
Now you are saying threads... which is it? Separate processes or threads?
Apologies for the misuse of the word(s). What I mean is new AHK_L or AHK_H processes. That is what I spawn.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by just me » 22 Nov 2020, 11:59

I cannot see any apparent error in your script. Are you sure that the call of RecordSet.Free() within retrieveSQLdbEntryImgHisto() is causing the crash?

robodesign
Posts: 932
Joined: 30 Sep 2017, 03:59
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by robodesign » 22 Nov 2020, 12:37

just me wrote:
22 Nov 2020, 11:59
I cannot see any apparent error in your script. Are you sure that the call of RecordSet.Free() within retrieveSQLdbEntryImgHisto() is causing the crash?
Yes, i get continuable exception error msgbox that indicates the line mentioned previously with the dllcall pertaining to that function... I even tried adding the flag for fullnomutex in the openv2 function, but it acts the same (it crashes). If i comment RecordSet.Free(), I get no crashes.

Anyways, i have abandoned this approach. I will try another approach to my endeavour.

Thank you for your interest in helping me.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by just me » 23 Nov 2020, 04:15

robodesign wrote:Yes, i get continuable exception error msgbox ...
That's an AHK_H error, isn't it? If so, what happens if you run AHK_L?

robodesign
Posts: 932
Joined: 30 Sep 2017, 03:59
Location: Romania
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by robodesign » 23 Nov 2020, 05:24

just me wrote:
23 Nov 2020, 04:15
robodesign wrote:Yes, i get continuable exception error msgbox ...
That's an AHK_H error, isn't it? If so, what happens if you run AHK_L?
It just crashes. You can try it on your end as well ... Make a huge database of files on your system and then try run multiple instances of ahk_l on the same database / table to read randomly some records from it. Let me know if it works.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.

kintar0e
Posts: 41
Joined: 05 Mar 2019, 07:32

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by kintar0e » 19 Dec 2020, 07:05

Hi, is there a way to connect database with password?
I created database.db with password when I connect the msgbox shows that: file is not a database.
I created the same db without password it works, I can get the information.

How can get working the connection db with password ?

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by just me » 19 Dec 2020, 08:15

SQLite databases don't have built-in password protection. The same is true for the official SQLite dll files.

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by Qriist » 20 Dec 2020, 02:45

just me wrote:
19 Dec 2020, 08:15
SQLite databases don't have built-in password protection. The same is true for the official SQLite dll files.
That's actually incorrect. There's a licensed builtin encryption but it has to be enabled via a compile option.
https://www.sqlite.org/see/doc/trunk/www/readme.wiki


A number of other options exist that make use of the disabled hooks in the free official sourcecode. (list stolen from elsewhere)
wxSQLite - A wxWidgets style C++ wrapper that also implements SQLite's encryption.
SQLCipher - Uses openSSL's libcrypto to implement.
SQLiteCrypt - Custom implementation, modified API. (not freeware
botansqlite3 - botansqlite3 is an encryption codec for SQLite3 that can use any algorithms in Botan for encryption.
sqleet - another encryption implementation, using ChaCha20/Poly1305 primitives. Note that wxSQLite mentioned above can use this as a crypto provider.

Without knowing more about the method in use there isn't really much to do.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by just me » 20 Dec 2020, 04:30

Qriist wrote:
20 Dec 2020, 02:45
just me wrote:
19 Dec 2020, 08:15
SQLite databases don't have built-in password protection. The same is true for the official SQLite dll files.
That's actually incorrect. There's a licensed builtin encryption but it has to be enabled via a compile option.
https://www.sqlite.org/see/doc/trunk/www/readme.wiki
As said, you need special 'extended' DLL files. The 'extensions' are not supported by this class.

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by Qriist » 20 Dec 2020, 04:43

just me wrote:
20 Dec 2020, 04:30
Qriist wrote:
20 Dec 2020, 02:45
just me wrote:
19 Dec 2020, 08:15
SQLite databases don't have built-in password protection. The same is true for the official SQLite dll files.
That's actually incorrect. There's a licensed builtin encryption but it has to be enabled via a compile option.
https://www.sqlite.org/see/doc/trunk/www/readme.wiki
As said, you need special 'extended' DLL files. The 'extensions' are not supported by this class.
Sure they are, or at least wxsqlite3 is. It's the DLL I use with your class. Most (all?) extended functionality is accessed via SQLite statements like you would with anything else.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by just me » 20 Dec 2020, 05:11

SQL syntax related questions aren't my playground. ;)

jsong55
Posts: 219
Joined: 30 Mar 2021, 22:02

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by jsong55 » 23 Apr 2021, 23:32

need some help. I'm trying to get msgbox to display status message like "1 row deleted" or "rowid #1 deleted"

Code: Select all

MyDB := New SQLiteDB
DBFileName:="E:\masterDB.db"
MyDB.OpenDB(DBFileName)

arg:="" 
. "`n" "DELETE FROM test"
. "`n" "WHERE rowid = 1"
. "`n" "ORDER BY rowid"
. "`n" "LIMIT 1 OFFSET offset; "
Msgbox, % arg
clipboard:=arg
MyDB.Exec(arg, "SQLiteExecCallBack")
Return

SQLiteExecCallBack(MyDB, ColumnCount, ColumnValues, ColumnNames) {
   This := Object(MyDB)
   MsgBox, 0, %A_ThisFunc%
      , % "SQLite version: " . This.Version . "`n"
      . "SQL statement: " . StrGet(A_EventInfo) . "`n"
      . "Number of columns: " . ColumnCount . "`n" 
      . "Name of first column: " . StrGet(NumGet(ColumnNames + 0, "UInt"), "UTF-8") . "`n" 
      . "Value of first column: " . StrGet(NumGet(ColumnValues + 0, "UInt"), "UTF-8")
   Return 0
}


just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by just me » 24 Apr 2021, 05:25

Maybe?

Code: Select all

MyDB.Exec(arg)
MsgBox, % MyDB.Changes

jsong55
Posts: 219
Joined: 30 Mar 2021, 22:02

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by jsong55 » 24 Apr 2021, 13:06

just me wrote:
24 Apr 2021, 05:25
Maybe?

Code: Select all

MyDB.Exec(arg)
MsgBox, % MyDB.Changes
Thank you! Didn't realize it was that simple haa

pmobin
Posts: 17
Joined: 14 Apr 2016, 12:50

Newbie SQLite Installation Problem. Please help

Post by pmobin » 02 May 2021, 17:14

I am trying to get sqlite3 to work on my machine. I can get it working with command line. However for some reason the Class_SQLIteDB.ahk does not find the sqlite3.dll although I am positive it exists. I think it is some other error masked as that.

My test code

Code: Select all

SQLiteDLL := "C:\Google Drive\55-Coding\Autohotkey\Etc\Sqlite\sqlite3\sqlite3.dll"
If !(DLL := DllCall("LoadLibrary", "Str",SQLiteDll, "UPtr")) 
{
  MsgBox, 16, SQLiteDB Error, % "DLL " . SQLiteDLL . " does not exist. Return Value=" . DLL
}
ExitApp
image.png
image.png (165.58 KiB) Viewed 3905 times

User avatar
hoppfrosch
Posts: 443
Joined: 07 Oct 2013, 04:05
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by hoppfrosch » 03 May 2021, 06:13

Are you sure using the correct 32-/64-bit combination? (AHK 32bit <=> sqlite3.dll 32bit, AHK 64bit <=> sqlite3.dll 64bit)

Post Reply

Return to “Scripts and Functions (v1)”