Jump to content
FrancescoDiMuro

Multiple istance AutoIt from different computers

Recommended Posts

FrancescoDiMuro

Good morning :)
I'd like to know if someone else here has already tried to use an AutoIt script which work on the same DB ( i.e. SQLite ) on different Computers.
If yes, please answer here, because I'd like to develop it, and I don't know where to start ( i.e. , I don't know how the script would work if 2 or more users are writing/reading in the same moment... ) :) 
Thanks! 

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites
FrancescoDiMuro

Thanks for the reply @genius257!
I was thinking about that! I'm going to take a look! :) 
Thanks :) 

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites
jchd

The problem isn't multiple instances of AutoIt, not even multiple concurent connections to the same SQLite DB. The possible cause of issues is with the network file locking protocols which may fail in some more or less bscure situations. SQLite isn't a client/server engine and isn't the best choice where remote concurency occurs at significant rate.

That said, modern OSes (typically Win >= 7) seem to work flawlessly from this point of view AFAIK. Yet if you plan many users and significant concurency, your best choice will be a client/server engine (PostgeSQL for instance) and use of ODBC/ADO (lookup the ADO UDF by Mlipok).

Nonetheless if you can afford some network delay and don't plan high concurency, you still can use SQLite. There is an UDF here within the Examples section which uses file semaphores to avoid issues. Of course it's even slower than bare SQLite.

In low-concurency contexts and provided your application isn't mission- or life-critical, you can still use bare SQLite if you follow these guidelines (the same apply to multiple processes accessing a local SQLite DB, as well as using any client/server SQL engine with some changes to adapt to a given SQL dialect):

Once for all, connect to the DB and issue "pragma journal_mode = WAL" to set the DB in WAL mode, which allows one writer and multiple readers concurently. Do this once as the setting will be permanent until changed (don't change it!).

Right after creating any connection to the shared DB, issue _SQLite_SetTimeout with the maximum time that the longest possible transaction sequence might take. I could expand on why this pedantic phrasing, but let's say you set a 30 minutes timeout, or anything very long. Genuine client/server engines use more sophisticated locking and caching techniques, so this is merely a SQLite-specific thing.

Use "BEGIN IMMEDIATE" transactions around any sequence of SQL statements which need to be atomic (e.g. RMW = read, modify, write). Also use transaction around sequences of statements within _SQLite_Exec when the whole operation needs to be atomic.

If your application needs to read data, show it to a user, process the decision taken and then only update the DB, realize that when wrapped in an immediate transaction, the DB will get locked to other writers. If the user takes longer than the timeout value, other writers will receive SQLITE_BUSY error. The worst case is when data is read, awaiting user action and that the app crashes or PC is powered down or the user returns home or whatever situation where the transaction isn't terminated with either a COMMIT or a ROLLBACK. In this case, the best move is to have a dedicated column in shared tables which holds the user ID "owning" the row(s) and awaiting possible change. Only when a row holds NULL is it available for use in a RMW transaction.

Test the error value after all SQL function invokation and act accordingly.

 

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@jchd
Thank you for you detailed answer :)
I started using SQLite few months ago ( we talked in pm too ), but It was already familiar because I studied SQL ( just the basic ) more than 3 years ago :)
So, I'm not as acculturated as I would be to reply to you, and so, I'm sorry for that...

Maybe, explaining in my best way what I'd like to do, you can suggest more directly what could be the solution :)
With my DB, queries I do are:
- SELECT, just 1 SELECT with a LEFT JOIN between 2 tables;
- INSERT;
- UPDATE.

I don't do anything else with the DB.
Users would be 3.
If you can help me more "basic-like", I'd very appreciate your help ( that I can understand :) )
Thanks again for your detailed answer :) 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites
jchd

Lone or group inserts aren't a problem even if grouping bulk inserts in a transaction cuts wall clock by a large factor.

Lone selects aren't a problem either.

Where issues arise is when USER_A does:

1 - SELECT some_data
2 - let the user decide what to do, change, whatever, possibly after taking his/her lunch
3 - UPDATE whatever_needs_to_be_updated

If USER_B changes (UPDATE or DELETE) something in the resultset of step 1, or INSERT something that should have been part of that resultset, while USER_A is in step 2, then the DB might become inconsistent.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
FrancescoDiMuro
2 minutes ago, jchd said:

Lone or group inserts aren't a problem even if grouping bulk inserts in a transaction cuts wall clock by a large factor.

Lone selects aren't a problem either.

Where issues arise is when you do:

SELECT somedata

I do SELECT * and SELECT column_name(s).
 

 

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites
jchd

Sorry for hitting the wrong key, previous post completed now.

Think of a bank account with $1000 credit and no authorization to go in debt.

At some time the owner wants to get money from an ATM. He selects $800.  At the same time a $500 check is processed by the bank.

If the ATM checks the account, verifies that it allows a $800 withdrawal, updates the account to become $200 while it delivers the banknotes, AND if the bank proceeds to the payment of the $500 check after the ATM read the account and before it updates it to $200, the account will become -$300, which is forbidden.

The only way to guard against such inconsistency it to wrap both operations in a transaction, making each payment an atomic operation which either takes place completely as one uninterruptible process or fails (here due to insufficient credit in one of the cases).

You must do:

0 - Begin immediate                ; starts a transaction where writes will likely occur
1 - select ...
2 - process ...
3 - update ...
4 - if all OK then COMMIT (perform changes) else ROLLBACK (quit transaction without any change) and process confilcting situation.

Both the ATM and the check processing need to use a transaction, so only the first one to COMMIT will go to completion, the other will encounter an error when trying to open its own transaction, or rather will wait until timeout to have a chance to let the transaction already started to complete. If you set a large enough timeout value and no dramatic event occurs (app crash), then everything will take place serially, leaving the DB in a consistent state at every time.

SQLite implements IMMEDIATE and EXCLUSIVE transactions by using file locks. Network file locking is know to have been a big problem with all known OSes up to a relatively recent time. The problem is due to bugs and holes in the process of "simultaneously" broadcasting locking information over a network. Nothing is simultaneous with computing, especially in a networked environment. Situation seems to have improved by fixing myriad of bugs in protocols and discarding unreliable ones.

  • Like 2

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Skysnake

Beautiful explanation @jchd.

I had a similar little problem with an embedded SQLite app we used at the office to track paper files movement across the office.  In theory, only one person could handle a file at a time... but it turns out, that's not what happens in reality.

I also think that (a) a different database design or (b) different database solution warrants investigation.

SQLite is not designed for a multi-user environment. 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
FrancescoDiMuro
2 minutes ago, Skysnake said:

Beautiful explanation @jchd.

I had a similar little problem with an embedded SQLite app we used at the office to track paper files movement across the office.  In theory, only one person could handle a file at a time... but it turns out, that's not what happens in reality.

I also think that (a) a different database design or (b) different database solution warrants investigation.

SQLite is not designed for a multi-user environment. 

And then, how did you solve?
Thanks :)


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@jchd
Another great explanation, thank you :)
So, what I have to do, to use simultaneousely the application, is ( always? ), do a transaction and check the returns of that operation.
Am I on the correct path? :)
Thanks! 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites
jchd

Use IMMEDIATE transactions to make complex processing atomic (all or nothing), set a long timeout on each connection. Always checking return codes is a prerequisite in all situations.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Chimp

.... here there is a very instructive example of how to carry out concurrent accesses in read / write mode to an SQLite database (see Post #19).

I used these two scripts to run many instances of a parallel script, and each of them wrote on the same database without problems.

Many thanks again to @jchd!!

Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Chimp
Thanks for your help :)
Yeah, @jchd seems to be very very acculturated on DB :D
Good job buddy! :)


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • SC0U7
      By SC0U7
      Hello i have a text file which contain over 600KB of BASE64 strings like :
      TVqQAAMAAAAEAAAA//8AALgAAAAAAAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...
      Now what i need is divide this long string as autoit variable each max lenght (4000characters per line) example:
      Local $var
      $var &=  TVqQAAMAAAAEAAA..
      $var &=  VbAmejwqqqAACEE..
      and then how to i add encrypt function on every line ? how to i can xor it? example final will be :
      $var &=  XorEnc(TVqQAAMAAAAEAAA..)
      $var &=  XorEncVbAmejwqqqAACEE..)
       
      Thanks for any help and ideas 
    • JuanFelipe
      By JuanFelipe
      Hello friends, I would like to know how to open a GUI from another GUI, I did it the way I leave the attached code, but when closing the second GUI they all close, they could help me to solve this problem without compiling another script. Thank you #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> $Form1 = GUICreate("Form1", 173, 126, 192, 124) $Button1 = GUICtrlCreateButton("Button1", 16, 24, 75, 25) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _form2() EndSwitch WEnd ;================================================= GUI 2 Func _form2() $Form1 = GUICreate("Form2", 615, 437, 192, 124) GUISetState(@SW_SHOW) While 2 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit EndSwitch WEnd EndFunc  
    • bowker
      By bowker
      Hi! Is there a way for me to set the value for my slider? I am getting the value for my slider with
       
      Local $strText = _UIA_getPropertyValue($oUIElement, $UIA_LegacyIAccessibleValuePropertyId) MsgBox(0,"",$strText)  
    • nacerbaaziz
      By nacerbaaziz
      Hello
      Can we pause and resume the download in the InetGet function?
      If is possible, what is the solution please?
      I used this code To manage the download

      #include <INet.au3> func _downloader($name, $linc, $filepath, $RTLF = false, $link = false) global $downloader = GUICreate("downloader", 400, 200, -1, -1, $WS_CLIPCHILDREn, $RTLF, $link) global $path = $filePath $labelTxt = GUICtrlCreateLabel("downloading " & $name, 50, 10, 200, 20) global $labelTxt0 = GUICtrlCreateLabel("downloaded size 0 MB " & "OF 0 MB", 50, 60, 300, 20) global $Progress = "" global $sText = ""     For $i = 1 To Random(5, 20, 1) ; Return an integer between 5 and 20 to determine the length of the string.         $sText &= Chr(Random(65, 122, 1)) ; Return an integer between 65 and 122 which represent the ASCII characters between a (lower-case) to Z (upper-case). next global $labelTxt2 = GUICtrlCreateInput("0%", 50, 80, 50, 20) _GUICtrlEdit_SetReadOnly(-1, true) GUIStartGroup("") global $beep = GUICtrlCreateCheckBox("use the progress beep notification", 150, 120, 200, 20) GUIStartGroup("") $button = GUICtrlCreateButton("Cancel', 130, 150, 180, 25, 0x01) $iIndex = 0 global $Target global $url GUIStartGroup("") global $Progress = GUICtrlCreateProgress(50, 90, 150, 20) global $Target = $filepath global $url = $linc global $path = $filepath global $hDownloadNo = _RSMWare_GetData($url, $Target) global $status = false AdlibRegister("SetProgress") global $onprogress = false, $curent = false GUISetState(@sw_Show) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE, $button $asc = MsgBox(4132,"exit download?","if you click yes the downloading will be cancel, do you want to cancel it ?") if $asc = 6 then AdlibUnRegister("SetProgress") GUIDelete() If $hDownloadNo <> 0 Then InetClose($hDownloadNo) exitLoop endIf EndSwitch if $status = -1 then $status = 0 $hDownloadNo = _RSMWare_GetData($url, $Target) $onprogress = false $curent = false elseIf $Status = 1 then $status = $path GUIDelete() AdlibUnRegister("SetProgress") exitLoop endIf WEnd return $status endFunc Func _RSMWare_GetData($url, $Target) Local $hDownload = InetGet($url, $Target, 1, 1) Return $hDownload EndFunc ;==>_RSMWare_GetData Func SetProgress() Local $state If $hDownloadNo <> 0 Then $state = InetGetInfo($hDownloadNo) If @error = 0 Then $infor = "downloaded size " & Round(Execute(InetGetInfo($hDownloadNo, $INET_DOWNLOADREAD) / 1048576), 2) & " MB of " & Round(Execute(InetGetInfo($hDownloadNo, $INET_DOWNLOADSIZE) / 1048576), 2) & " MB " $onprogress = Round(Ceiling(($state[0] / $state[1]) * 100)) if not (InetGetInfo($hDownloadNo, $INET_DOWNLOADSIZE) = 0) then if $onProgress <= 0 then $onProgress = 0 GUICtrlSetData($Progress, $onProgress) GUICtrlSetData($labelTxt0, $infor) GUICtrlSetData($labelTxt2, $onProgress & "%") if _isChecked($beep) then if $onprogress > $curent then beep((100 + $onprogress * 20), 100) $curent = $onprogress endIf endIf endIf If $state[2] Then If $state[3] Then InetClose($hDownloadNo) $status = 1 else InetClose($hDownloadNo) $status = -1 endIf endIf EndIf endIf EndFunc ;==>SetProgress
    • VollachR
      By VollachR
      Hi,
      I'm looking for a way to take a number value from a Row2 of a 2D array and according to this check if files that appear in rows 3-11 in the array exists.
      For example, if the number in Row2 is 5 I need to check for the files in Row 3-6 only, if it is 6 than rows 3-7 and so on.
      I thought on using a FOR loop but I have very little experience with those.
      Can you suggest the best way to do what I need?
      BTW, the files in Rows 3-11 will usually have blank value for any row above the number in Row2 (e.g. Row2 = 5 so Rows3-6 will have values but 8-11 be empty), The values I need are in Column 1 of the array, the name of the key from the INI file that the array was created from is in Column 0.
      Full Example:
      Row2 of Array:
      Col0 = Games# - Col1 = 5
      Rows3-6
      Col0 = Exe2 - Col1 = Path To File
      Col0 = Exe3 - Col1 = Path To File
      Col0 = Exe4 - Col1 = Path To File
      Col0 = Exe5 - Col1 = Path To File
      I need that if Row2 is 5 to check these above for rows if the file exists, if it was 6 then the next row as well and so on up until number 10 in Row2 as it can't go above 10.
      So basically for whatever number in Row2 from 2-10 need to check 1-9 rows from 3-11 to see if the files in Col1 exists and if any of them don't exist it should call a function that shows an error message.
      I'm pretty sure I have the first line of the for look correct:
      For $i = 1 To $aAIO[2][1] Just not sure how to continue from there, also not sure if $i should be equal 1 or 2.
      Help will be appreciated.
×