[Completed] Volunteers wanted 👀 to test an AutoIt + SQLite project

[SOLVE-SMART]

Hi everyone 👋 ,

thank you in advance for looking at the little project and for thinking about supporting it (me) 😀 .

Introduction
I have a small AutoIt + SQLite GitHub project (sqlite-showcase). This serves at 1️⃣. glance to demonstrate how to use SQLite using concrete examples. If questions arise in the English or German forum about SQLite, I can provide direct assistance using the project and its example data. As 2️⃣. perspective, this project is intended as a module for a subsequent project that deals with something more than "just" AutoIt + SQLite - but more on that another time.

I deliberately refrain from writing and explaining too many details here, because it is important to me, among other things, that the explanation on GitHub, in the form of a README file, can be handled or whether I need to make improvements there.

Testing
So far I have been able to test the project under Windows 10 (x64), with AutoIt v3.3.16.1 and with SQLite v3.49.0. Due to the support of the german AutoIt forum member(s), I could also test with Windows 11. Now I would be very happy if someone among you (several people are welcome) 🤗 would test other system configurations. See the following table:

The test consists of the following parts:

• Read the README, understand what to do ==> If there are already problems understanding this, then I have to make improvements.
• Setting up the project (getting started) ==> Is the explanation in the README sufficient?
  • You can test it on Windows 10 x86? Then please, great.
  • Do you have an old version of AutoIt that you can test with? That's exactly what I need ==> Thanks.
  • You want to try a different SQLite version? Why not, I appreciate it.
• Are the few SQLite functions running correctly or are there any errors? They shouldn't, but if they do, please let me know.

Preconditions
You don't need Git or GitHub, although Git is an advantage. A simple download of the project (zip) is completely sufficient (see README).
Just a little time, curiosity, interest in learning something new or putting my approach to the test 😅 .

🔥 CHANGELOG can be found here.

---------------------------------

Thank you very much for your interest and for your support. If you have any questions, please let me know. Thanks.

Best regards
Sven

 

Update: Testing complete.

Edited Wednesday at 08:20 PM by SOLVE-SMART

[argumentum]

59 minutes ago, SOLVE-SMART said:

Do you have an old version of AutoIt that you can test with? That's exactly what I need ==> Thanks.

$mSqlite.Path    = _PathFull('../lib/sqlite/') ; based from the location of main.au3

it uses maps and that is newer. Forward slash that:

Func _PathFull($sRelativePath, $sBasePath = @WorkingDir)
    If Not $sRelativePath Or $sRelativePath = "." Then Return $sBasePath

    ; Normalize slash direction.
    Local $sFullPath = StringReplace($sRelativePath, "/", "\") ; Holds the full path (later, minus the root)
...

has to correct.

As a project to show how to use obscure things ( for the uninitiated ) is a good idea as a teaching tool. But it looks more like a framework, and with that I have issue, given that "it leads the subject", as they say in court/lawyer/judge type of TV drama, to use the framework's workflow more than SQLing**  with SQLite.
** SQL-ing [Verb]: To do SQL stuff. [ Encyclopedia Britannica, 2045 ] ( yes, one day in the future, people will say sqling, and you saw it here first )

My 2 cents. Glad to see that someone (you) is putting together a teaching tool. Nice

[SOLVE-SMART]

Thanks @argumentum for statement and review 😀 .

Function _PathFull() have to correct my slashes, so do you recommend to change it directly? Hmm 🤔 , I will think about it - because there is no real issue with that an I enjoy the VSCode extension "Path Intellisense" which auto-completes paths while I write them - but only in case of forward slashes. That's why I use them.

21 minutes ago, argumentum said:

But it looks more like a framework, and with that I have issue, given that "it leads the subject", as they say in court/lawyer/judge type of TV drama, to use the framework's workflow more than SQLing**  with SQLite.
** SQL-ing [Verb]: To do SQL stuff. [ Encyclopedia Britannica, 2045 ] ( yes, one day in the future, people will say sqling, and you saw it here first )

Interesting and funny. I am absolutely not finished with the project and I don't know how far this could be more framework'ish or more simple ... time will show I guess.

21 minutes ago, argumentum said:

My 2 cents. Glad to see that someone (you) is putting together a teaching tool. Nice

Thanks.

Best regards
Sven

Edited February 25 by SOLVE-SMART

[argumentum]

2 minutes ago, SOLVE-SMART said:

... so do you recommend to change it directly? Hmm 🤔 , I will think about it -  ...  the VSCode extension "Path Intellisense" which auto-completes paths while I write them ...

In my view, this is for Windows OS and it does not use forward slashes. Giving that most users copy and paste ( ..well, that's what I do ), setting an example..., sets an example.
Therefore: as a teaching tool, we do it as correct as possible and comment as much as possible because we ( well, you ) are guiding young minds and everything should be impeccable. You are now to be known as the "sql sensei"

Think of it as writing a book. A good example is better than a book, in my book.

[genius257]

Hi @SOLVE-SMART,

The main purpose of this is to show best practices for error handling and logging?
What ideal level of AutoIt experience is this for?

I feel like this wraps too much of the original SQLite UDF, without extending it much.
The issue i see is that this seems most like a implementation template. Meaning currently if followed, potential bug fixes and improvements won't be easy to add later, for a bigger project.
Better error handling and logging is great, but should be it's own UDF/Project.
This will reduce the code needed and allow upgrading the dependency later on, by replacing the dependency with the new version and following a simple laid out upgrade guide, if necessary.

Maybe I'm in the wrong, but it just seems like too much code spread out, for demonstrating simple concepts?

Not trying to be a douche, just my critique 😅

But really great idea, wanting to have a good introduction to usage and best practices!

[genius257]

2 minutes ago, argumentum said:

In my view, this is for Windows OS and it does not use forward slashes.

From my usage windows does not care if you use forwards, backwards or a mix of slashes?

If functionally it makes no difference, the only thing that matters is that a standard for the project is chosen and consistently upheld throughout the project?

[argumentum]

1 minute ago, genius257 said:

From my usage windows does not care if you use forwards, backwards or a mix of slashes?
If functionally it makes no difference, the only thing that matters ...

It does not mean that is a Windows standard.

2 minutes ago, genius257 said:

...the only thing that matters is that a standard for the project is chosen and consistently upheld throughout the project?

8 minutes ago, genius257 said:

Maybe I'm in the wrong, but it just seems like too much code spread out, for demonstrating simple concepts?

I agree with that. But like he said, "will see".
Is a project in this "AutoIt Projects and Collaboration " for now. Once settle will move to Examples I guess.

[SOLVE-SMART]

@argumentum Understood, well you're right and I will change the slashes. Important: of course I am the "sql sensei" - everybody knows that 🤣 - irony mode off.

The project was not planned as a teaching tool. I wanted to have an easy starting point when it came to questions about SQLite usage. Then I could add concrete examples. The current ones are just tiny showcases, not more. That's why I have to think about how this can and should develop.

Thank you too @genius257 for your statement🤝 .

7 minutes ago, genius257 said:

What ideal level of AutoIt experience is this for?

I am not sure yet. But as you can read few lines above, I try to figure out how this can and should develop.

10 minutes ago, genius257 said:

I feel like this wraps too much of the original SQLite UDF, without extending it much.

Exactly, this is intended for now. Please see this litte paragraph - I don't try to create another SQL(ite) UDF.

12 minutes ago, genius257 said:

The issue i see is that this seems most like a implementation template. Meaning currently if followed, potential bug fixes and improvements won't be easy to add later, for a bigger project.
Better error handling and logging is great, but should be it's own UDF/Project.
This will reduce the code needed and allow upgrading the dependency later on, by replacing the dependency with the new version and following a simple laid out upgrade guide, if necessary.

I like that summary very much, thanks. I also have to think about it - but sounds very reasonable.

13 minutes ago, genius257 said:

Maybe I'm in the wrong, but it just seems like too much code spread out, for demonstrating simple concepts?

Not trying to be a douche, just my critique 😅

All fine genius257, I appreciate every ideas, suggestions etc. so thanks again.

11 minutes ago, genius257 said:

From my usage windows does not care if you use forwards, backwards or a mix of slashes?

If functionally it makes no difference, the only thing that matters is that a standard for the project is chosen and consistently upheld throughout the project?

Agreed, I will use "\" slashes and will be consistent throughout the project.

 

💡 At the moment, my intention was to test the current state. Test by different situations/systems/circumstances. It's a pretty early version of the project, so there will probably more changes soon. And I am very curious what you guys will say about the upcoming (bigger projects). Stay tuned 😀 .

Best regards
Sven

[jchd]

I"ve currently very  little free time yet I just had a quick look at the SQLite part.

Setting PRAGMA busy_timeout = 2000; may be too short in practice. In multi-user context, this parameter should be way larger than the longest possible sequence of SQL statements transactions issued by the maximum concurent users, all of this as worst case. I can expand on this if needed. I set that to 10 minutes.

Why do you make the IDs autoincrement? Don't, unless you really have a compelling reason to do so.

Edited February 26 by jchd

[SOLVE-SMART]

Hi thanks @jchd ,

ten minutes for PRAGMA busy_timeout? Wow, I didn't expect this much. To be honest, default is PRAGMA busy_timeout = 0; in SQLite and I set it "just in case" to 2 seconds (for a multi user and multi transaction scenario). But I am very grateful for your statement coming from the practice 🤝 . I will increase it to 30 seconds (maybe more in the future) instead. At the moment it's not in a production ready phase, but hints like yours are very welcome, thanks.

1 hour ago, jchd said:

Why do you make the IDs autoincrement? Don't, unless you really have a compelling reason to do so.

I have to think about it? I never had a problem before with autoincrements. I will do a research or/and you can give me the short summary why not?

Best regards
Sven

Edited February 26 by SOLVE-SMART

[argumentum]

I'd love to see an example to show these values tested.
2000 ms. is an eternity. 60000 ms. is forever ( give or take an eternity ).
Muti-user of 2 or 200 users ?, because 2000 users may need an hour of timeout ?
Is it in a HDD or NVMe or RAM ? Is it 1 thousand records or 1 trillion ?

@jchd, if you could elaborate in the form of running code ( my preference ) or experience(s) that leads to the warning, I'd like to gain some understanding.

..off topic but, I remember reading about using SQLite for IPC. If you have a way to do that, I've been curious about that since forever.

 

Edited February 26 by argumentum
added "off topic"

[TheXman]

1 hour ago, SOLVE-SMART said:

I have to think about it? I never had a problem before with autoincrements. I will do a research or/and you can give me the short summary why not?

The information in the second bullet point of this post is probably what he's referring to.

[jchd]

First, SQLite is a simple library, not a client-server design. SQLite can only proceed with ONE SQL transaction at any given time. Also SQLite locks the entire DB file, not a specific table or row.

When SQLite detects a BUSY situation when transaction A enters execution (another SQL transaction B in being processed, plus possible other transactions C, D, ... in BUSY state as well) and a non-zero timeout is in place for A, it continues processing B and repeatedly checks if A, C, D, ... are still in BUSY state until their respective timeout value exipres or B terminates and clears the way for others.

SQLite doesn't queue BUSY transactions waiting for clearance in a FIFO or like. It randomly selects one of the waiting transaction and gives it a chance to process.

That means that with the previous enumeration, when B terminates, SQLite is free to randomly select D to run, then F, then C, then E, until it selects A.

That's why I mean "the longest possible sequence of transactions issued by all users of the library", because you may be unlucky and have your thing clear to run way after what you'd expect.

That's also why it should be forbiden to group in a single transaction something like: select for change a number of rows, wait for user to chose the ones they need change, take the lunch break, update some data and finish the transaction. Some client-server designs can cope with this but at the expense of serious internal complexity to deal with parallel updates.

 

About autoincrement: you ever need this except if your application can't cope with duplicate use of rowid over time. Example without autoincrement:

create table T (id integer primary key, name text);
insert into T (name) values ('Alice'), ('Bob'), ('Charlie'), ('Dom'), ('Elon');
delete from T where name glob '*o*';
insert into T (name) values ('Eve');

You obtain:

id    name
1    Alice
3    Charlie
4    Eve

Instead, with this:

create table T (id integer primary key autoincrement, name text);
insert into T (name) values ('Alice'), ('Bob'), ('Charlie'), ('Dom'), ('Elon');
delete from T where name glob '*o*';
insert into T (name) values ('Eve');

You get:

id    name
1    Alice
3    Charlie
6    Eve

With autoincrement, once a given rowid has been used, you're sure it won't be reused ever. This a very rare use case and forces SQLite to maintain sqlite_sequence for this table.

Edited February 26 by jchd

[SOLVE-SMART]

Thanks to both of you @TheXman and @jchd 🤝 , I learned a lot, great 😀 .

Best regards
Sven

[SOLVE-SMART]

On 2/25/2025 at 9:27 PM, argumentum said:

it uses maps and that is newer

In which AutoIt version was "maps" introduced? I will not support versions without maps support 🤔 .

---------------------------------

Besides all your good inputs and helpful statements which I really grateful for, back to the main question in post #1 😅 :

Testing ==> who could support by testing in one of the open situations/systems/circumstances (see table)?

Best regards
Sven

Edited February 26 by SOLVE-SMART

[argumentum]

36 minutes ago, SOLVE-SMART said:

In which AutoIt version was "maps" introduced? I will not support versions without maps support 🤔 .

Looked in "history of the changes to AutoIt v3" but there is no reference. Found it in my portable(s) v3.3.15.4 (Beta) but not in v3.3.14.5.
Those versions are quite old. Should always focus in the current version. That been said, the use of maps has nothing to do with SQLite.

Your "Func _Query()" in my view could be:

Func _Query($sSQL, $hDB = -1)
    Local $aResult, $iRows, $iColumns
    _SQLite_GetTable2D($hDB, $sSQL, $aResult, $iRows, $iColumns)
    If @error Then
        _Log('_SQLite_GetTable2D() error')
        Return SetError(1)
    EndIf
    Return $aResult
EndFunc

and is more along the lines of what was ( in standard AutoIt3 ), to give an example.
And is easy to use more than 1 DB in the script.

But, if you need to use maps, then that's that I guess.

---

"Warning: This feature is experimental. It may not work, may contain bugs or may be changed or removed without notice.
DO NOT REPORT BUGS OR REQUEST NEW FEATURES FOR THIS FEATURE."
"Remarks: Almost uniquely in AutoIt, keys are case sensitive - "MyKey" is not the same as "mykey" "

That leads me to believe that if the one who coded that warns about "not too sure about this" and "Almost uniquely in AutoIt, keys are case sensitive", is that something in that library is questionable and/or conflict with the existing base of the AutoIt3 product.

..just as a side note. Most people in the forums are begging to remove the warning because it looks stable. 

Edited February 26 by argumentum
more

[SOLVE-SMART]

Thank you @argumentum 👌 . Then I will set the focus to current version v3.3.16.1 or newer. I will adjust the "test table" in the post #1 and in the README file.

16 minutes ago, argumentum said:

and is more along the lines of what was ( in standard AutoIt3 ), to give an example.
And is easy to use more than 1 DB in the script.

But, if you need to use maps, then that's that I guess.

I don't have to use maps, no. But I really want to.
The point about more then one database is a good one. At the moment it was not intended that way, but why not - thanks.

Best regards
Sven

[genius257]

34 minutes ago, argumentum said:

Looked in "history of the changes to AutoIt v3" but there is no reference. Found it in my portable(s) v3.3.15.4 (Beta) but not in v3.3.14.5.

A lot of changes are missing from that version of the history, you need to check autoit_changelog_complete.txt. Earliest reference to Maps i can find is 3.3.13.6 (18th, 2014) (Beta), where it changed from being called Tables to Maps, but i cannot find any references to Tables earlier at a glance.

Edited February 26 by genius257
Fixed spelling

[SOLVE-SMART]

Thank you very much, thanks in part to your help I was able to successfully complete the tests in the most important configurations. This means that the search for volunteer testers is now over. Thank you to all supporters and contributors.

Best regards
Sven