FrancescoDiMuro

Multiple istance AutoIt from different computers

14 posts in this topic

#1 ·  Posted (edited)

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



#3 ·  Posted (edited)

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

#4 ·  Posted

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.

 

1 person likes this

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

#5 ·  Posted

@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

#6 ·  Posted (edited)

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

#7 ·  Posted (edited)

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

#8 ·  Posted

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.

2 people like this

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

#9 ·  Posted

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

#10 ·  Posted

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

#11 ·  Posted

@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

#12 ·  Posted

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

#13 ·  Posted (edited)

.... 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

#14 ·  Posted

@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

    • X_xkijux_x
      By X_xkijux_x
      Ok so im using this program. clarify for school. I would like to have a program that takes every word i type in microsoft word and searches on it in claryfi. is this possible. I would like to have the program not stopping me from typing more after that word. Like when i type a word it auto search it on clarify without making me stop typing and if i want to change i can do that and if im fine that word i can just keep typing and it will search after the next word. 
    • Miliardsto
      By Miliardsto
      Is this possible to make program works like in diagram? There is so much encryption methods and UDFs are they give security? If the $Address will be crypted with some encryption algorithm could be possible to get the value of $Address in easy way? Of course We know its easy too look into autoit code and get value of variable and so We dont want to anyone see $Address value - there are functions Could func1.au3, func2.au3.. be for example func1.php or other type of file? As we know .php files are unnable to see. For example func1 would be read by FileRead() and then Execute() Is this generally possible to do?
      Will it give secure?
      What do you think about? Maybe there is something wrong in this concept or missed.

    • JaredStroeb
      By JaredStroeb
      I need to open an application and send it keys in Session 0.  I have exe's working locally, I can see the application open in session 0 through interactive services, but my send commands do not go to the application.  The following article running-autoit-session-0, directs me to use ControlSend() however my application has no controls that can be identified by AutoIt v3 Window Info.  Get All Windows Controls also returned nothing for the application's handle.  
      What are my options?  
      Can this be done with AutoIt?
      Is there a different "relatively simple" library/tool to use? (I am familiar with C# if there is a viable library there )

       
    • nassausky
      By nassausky
      Hey all,
      Has anyone created any queries for youtube? I was thinking it might be something like this but can't quite figure it out:
      $oIE = _IECreateEmbedded() _IENavigate($oIE,"https://www.youtube.com") Local $oForms = _IETagNameGetCollection($oIE, "form") Local $sTxt = "" For $oForm In $oForms If $oForm.id = "masthead-search" Then $oInputs = _IETagNameGetCollection($oIE, "input") For $oInput In $oInputs If $oInput.id = "masthead-search-term" Then _IEFormElementSetValue($oInput, "cards") _IEFormSubmit($oForm) EndIf Next EndIf Next  
    • Skysnake
      By Skysnake
      Scite comes up occasional, almost incidentally, in some support topics.  I have not found a "Scite" topic in the forums, so this is where I will start.  I have recently been using Scite intensively, specifically with a large project and I have certain issues.  I know that Scite is not an AutoIt product, but it is so closely integrated to the core release that it makes sense to raise issues and request support here.  If this is not the right please refer me to the right forum.
      Thx
       
      Skysnake