Have you considered using sockets or simple ajax to connect to a main server app?
In my experience (regardless of platforms like AIR or otherwise) mapped drives are almost never the way to go. Adds setup time to each pc among other problems etc.
Personally I like AIR for the type of app you are describing. It allows for an offline version (in case everything freaks or a salesman is out of the office, he has everything that was the latest cache). It also lets your client easily upgrade / change versions in case they want to take their service out of the intranet.
I would likely accomplish what you're trying to do using a single master web service that connects to the master database, then just use urlrequests to get that info through a web service / update the local database. The master database would never have a lock issue with this architecture as only one user (the master itself) is connecting. (You also have the option of using a more feature rich database like mysql etc)
It's probably not the absolute *best* tool for what you're asking. Syncing data and schema in AIR apps is pretty basic and you'll wind up writing a lot of your own to accomplish those tasks. That said it's lightweight, portable and relatively easy. Biggest headache for me would be caching / syncing schema, but that's a pretty minor headache all things considered.
Just my 2 cents
Thanks so much for the thoughts and reply.
I agree that mapped drives are a headache.
The problem I encounter is that a customer may not have an internet connection, ever. All data is stored locally on the master installation's sqlite instance and the client simply opens up a connection to that db. All I really need is a failsafe way to prevent table locks from affecting the user.
I really don't like the idea of setting up mapped drive either. If there was a way for me to scan their network for the master installation's path, that might be a better approach. Do you know of a way to do this with actionscript?
Thanks for the interest.
In my opinion you're probably never going to get SQLite to perform well in a multi user environment. You could try to catch the errors (probably not that bad) and wait / retry but it's not as robust as just writing basic intranet service and mirroring content to local (user specific) sqlite databases in case of local connection disruption. (You could even have the local service actually build the air app / distribute it with content loaded.)
On the other hand, if you used mySQL as a master your issue could be completely resolved. Here's a good actionscript library for connecting to mySQL databases: http://code.google.com/p/assql/
Pros: instant multi user support, no need for mapped drives
Cons: Static internal IP address, static local domain, or some other polling method needed.
On the polling method: possibly just simple socket polling (using AIR's built in socket class) where you try to detect sql's open port then pass that to the actionscript driver. (Would have to toy with that a bit to see if it's even possible, no experience there myself.) In the meantime you could just assume that it's possible to have a static internal IP at your customer's location. That said, if your users are already having to map drives, a single static IP is probably not too much to ask as the configuration time is far faster.
Good Luck, hope my rambling discourse helps somewhat.
Thanks for spending the time to work with me on this. You're thoughts are very appreciated. This is the last hurdle that I need to overcome before I can release my application. I didn't think about having a mysql instance on the intranet and then using assql to connect to it. That's a decent idea.
One thing I don't like is the complexity of the install. I'd rather not write an platform specific installer that installs a local mysql instance, AIR runtime (if not installed) and then my application. Is that what you'd do in this case?
If I read your post right, and I like to think I did, the solution you're after is... None of the above! "The main requirement is that all of this has to work "offline"". If this is the case, then you'll need to provide all the necessary data on first run. Am I miss-understanding your problem completely?
Sort of. Here is how the application works:
1) User installs application using the air installer.
2) Application detects that it's the first time run and builds the db schema and populates it with a small set of initial data as well as some of the data tha the user entered during the install wizard.
3) From then on, the user can install "client" instances that don't install a db, but instead point to the "master" db that was installed in step #2. They do this by using a mapped drive.
4) Everything works perfectly, until 2 users want to execute a statement against the DB at the same time. When this happens SQLite throws an exception, which is caught by the application and a few retries are performed. If the retries all fail, the user is shown a message "The DB is busy, please try again".
This hasn't been much of a problem for our store. We've got 4 PCs (1 server, 3 clients) running find for almost 1 year. Before I can sell this application, I want to make sure I'm doing all of this the right way (easy on install, easy on maintenance, easy on users). The simplicity of the run time is what attracted me to this platform.
I come from the J2EE world where DB connections/pooling are something we sort of take for granted.
On the complexity of the install: Other than providing detailed setup instructions for mySQL and then installing the platform independent AIR components I don't see a way around it. If you wanted a fairly unattended setup of mySQL and your AIR frontend you'd likely have to go with at least two platform independent installers.
If you plan on expanding your offering to other services using your existing "server" architecture then I'd likely go with a linux based server as it's cheaper to implement. If you're going for ease of install, then one installer for Windows and possibly one for Mac (although they still are on the low end of adoption in businesses).
So yeah I'd envision two disks (client / server) or two web based installs. Server Install (mySQL install with optionfile (for standardized setup and possibly a sql dump to be ran after install) and Client install (AIR runtime and AIR applications).
You *could* bundle the mySQL and Client applications into one installer, however I think that's a bad idea because in the case of 5 PCs in one location only 1 out of 5 PCs will need the server components.
More info on unattended mySQL installs: http://dev.mysql.com/doc/refman/5.1/en/mem-unattended-windows.html (process is similar on mac/linux)
Well, barring some method of direct peer to peer communication (maybe this is possible with Merapi, or some other way, but that sounds unduly complicated), the only real connection between the clients is the mapped drive. So perhaps the clients can communicate through the hard drive.
When each client is installed, have it write a queue file that indicates whether or not it is in line to use the database, and its number in line. Each client should be configured to be able to write only to its own queue file, and read all of the other queue files. The main client will write the first queue file and be client 0, and each subsequent client will write a new queue file, and assign itself the next client number.
Before executing a sql statement, the client should read all of the queue files, and then write to its own file, indicating that it is now in line, and giving itself a queue number 1 higher than the highest existing queue number, or 0 if none of the other clients are in line.
Then, the client should read all of the queue files again, and keep checking periodically until it has the lowest queue number. Once the client has the lowest queue number, it can execute its sql statement. (even if no other clients are in line when the client checks the first time, it should always write to its own file first, then check the other files, then execute the sql statement, just in case two clients check the queue files at the same time.) If two clients check the queue files at the same time and give themselves the same queue number, they should proceed in the order of their client numbers.
Once the statement is executed, the client should reset its queue file to show that it is no longer in line.
Possible problems: if one client in line freezes for some reason or dies, it could freeze the entire network. You might put a time limit so that a client looses its place in line if it doesn't complete its transaction in a reasonable amount of time, but you would have to make sure the time period is longer than the client could possibly take to complete a transaction.