SQLite and multiple-access databases (locking etc)

[kinch]

Hi,

I suspect this is really more of an SQL question than an AutoIT question, but since I'm writing and using AutoIT, I wondered if someone might have come up with a neat little solution (as so often happens around here!)

I have a basic DB admin program - it loads the data into a ListView for easy viewing and editing.  This allows me to fix typos, make updates, add rows, delete erroneous rows, etc.

The DB is stored on a network server and potentially could be opened (and updated) by another user while I'm working on it.  Due to the nature of the program, I'm technically working on a copy in the listview, rather than on the DB itself (ie, I load the DB into the listview contents, and then have to save it back to the DB when I'm ready to commit my changes).  So my question is, is there a way to detect if someone else has updated the DB while I'm working on it, so I don't commit changes that might overwrite or undo someone else's work?

I suspect not based on what I've read in the SQLite AutoIT helpfiles, but hopefully someone with more knowledge than me can help.

If not, is there any workaround?  I'm looking into the "begin exclusive" SQL function to lock the DB while I'm working on it, so that other updates will have to wait until I've finished - this allows me to make my updates, reload the DB, and check that it's all good.  Then I release the exclusive lock and the other updates can read the updated data and go about their business.

Am I doing this wrong?

 

Also, slightly off-topic but I'm curious... when I was using Perl, there was this really nifty trick (module) where they linked a perl array variable to a SQL database using some funky object code and a Database Abstraction Layer, so that queries to the "array" were channeled through to the DB itself.  i.e., "$array[0]" would actually run a "select * from db where index=0" type of query and "$array[0] = 1" would run an "update table" query and effectively give you a live interface to the database.  Probably quite problematic in a large environment with lots of updates, but none-the-less, a very cool functionality.  Is such a thing possible in AutoIT?

Cheers.

[jchd]

Raw SQLite is not advisable for running over a network. This isn't SQLite fault: all and every file locking protocols available on every OS you can think of are buggy as hell when pushed to their limits (and even well before getting close to said limits). You might think it's dumb and, yes, it is.

There exist network-shielded version of the library including proper wrappers but they are not managed by SQLite development team.

On the contrary, SQLite is sure to work as advertized as a local DB hit by multiple processes or threads (read official doc to learn how to and not to).

The easiest way to track changes to a DB made by concurrent processes is to wrap changes in transactions and include an update statement to an ad-hoc table (1 column and 1 row) with an incremental integer, or simply query/increment user_version using pragma user_version.

php uses a good deal of OO encapsulation, so it's easy for this language to offer sugar like the one you mention. AutoIt doesn't enjoy the same design so, no, we don't have such a feature built in the Array stuff.

Setup a large enough timeout when you don't want a second or more writer to error out while the first isn't done.

[kinch]

Thanks jchd.  I figured right on most of it, but it's good to have it confirmed by an expert.

As for running SQLite over the network... I understand it's not the most desirable solution, but in a worst case scenario I'm envisaging 20 added rows per transaction, 20 times a work day (spread over the course of a few hours).  I'm hoping that this isn't too much for the (network) SQLite DB to handle, because if it is, then my entire project goes down the drain.  I'd have to switch over to using just a textfile as database storage with no easy way to check for data integrity or modifications.  It'd be doable, but messy.  Anyway, I'll see how it goes in the live environment first.  Initial reports from my alpha testers are ok.

It's partly why I'm even writing my admin application - to open the DB and compare the DB data with the logfiles to ensure the correct data has been written to the DB.  If something goes wrong, I should be able to manually fix it with the admin tool.

The user_version pragma is a nifty little feature, could be useful for all sorts of things.  I'll have a think as to how best to incorporate it.

I imagine the array<->DB functionality could be done with a messy bunch of functions, such as "SArrayInsert" and "SArrayGet" which would just be wrappers around the sql queries... and you'd have to make it robust enough to handle all the array queries that people would ordinarily throw at it... again, probably doable but very messy and not worth it.  Oh well, as I said, I was just curious.

Cheers.