Page 12 of 12

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

Posted: 21 Nov 2020, 14:58
by robodesign
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.

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

Posted: 22 Nov 2020, 05:13
by just me
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?

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

Posted: 22 Nov 2020, 08:45
by robodesign
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.

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

Posted: 22 Nov 2020, 10:13
by kczx3
Now you are saying threads... which is it? Separate processes or threads?

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

Posted: 22 Nov 2020, 10:27
by robodesign
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.

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

Posted: 22 Nov 2020, 11:59
by just me
I cannot see any apparent error in your script. Are you sure that the call of RecordSet.Free() within retrieveSQLdbEntryImgHisto() is causing the crash?

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

Posted: 22 Nov 2020, 12:37
by robodesign
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.

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

Posted: 23 Nov 2020, 04:15
by just me
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?

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

Posted: 23 Nov 2020, 05:24
by robodesign
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.

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

Posted: 19 Dec 2020, 07:05
by kintar0e
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 ?

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

Posted: 19 Dec 2020, 08:15
by just me
SQLite databases don't have built-in password protection. The same is true for the official SQLite dll files.

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

Posted: 20 Dec 2020, 02:45
by Qriist
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.

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

Posted: 20 Dec 2020, 04:30
by just me
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.

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

Posted: 20 Dec 2020, 04:43
by Qriist
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.

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

Posted: 20 Dec 2020, 05:11
by just me
SQL syntax related questions aren't my playground. ;)