Personally, I don't buy that DropBox is the culprit as I've done this kind of thing a few times in a few applications of my own, however, I'm the single user that works on that single account, and any app that uses DB is usually under development and "closed" on any other geographical site. However, with the chance that the WAL file makes it down the wire, I could see that as being a problem if your application spits out small bits of data periodically and is being run in multiple places. DropBox, for me at least, realizes that another application has a handle on the database file and won't touch it, or overwrite anything. But the WAL file might make it through.
I'd suggest looking into opening the database with an exclusive lock, or look into using the Backup API that SQLite uses. Using the SQLite Backup API, when your program starts, do a flat-file standard file copy from DropBox to a different location (%USERPATH% or something similar) and wait for the file to finish to copy. Then open that backed up file and work on it. When your user saves, or closes your application, use the backup API to put the file back to DropBox directory. This isolates WAL file writes to the local system and not to DB. On Mon, Feb 10, 2014 at 4:20 PM, C M <cmpyt...@gmail.com> wrote: > On Mon, Feb 10, 2014 at 2:54 PM, RSmith <rsm...@rsweb.co.za> wrote: > > > How to go from the error codes to the diagnosis? I think the logic is as > > follows: > > > > We can see an error occurs when trying to access the file, or more > > specifically, trying to obtain a shared lock on it. This means it is > locked > > by another application (as opposed to another SQLite thread). Now the > > question remains which other application? We would usually simply suggest > > to look in your system, but you already provided a log of the error, and > it > > is clear from the error that a file you are trying to access is in > > "Documents\My Dropbox\myapp\" which, as everyone knows, is a dropbox > > folder, which means likely you have dropbox installed. Secondly, Dropbox > is > > a known culprit in this regard, because it syncs files with the cloud (it > > is not the only one, Skydrive, Google drive etc all do this), which means > > it will have to lock a file while either uploading or download-syncing it > > for consistency and concurrency reasons. Put these three pieces of > > evidence together, and the answer is inevitable - you probably have > dropbox > > problems. > > > > The remedy is not easy - same as when dealing with Excel exports or some > > other system that will lock files of it's own volition if it is opened > with > > that system - simply making a byte-copy of the file, changing it and > > replacing it afterwards, with a possible replace-queue facility which > will > > wait till a lock is released. Problem is, what if the other app made > > changes that you actually mean to keep? > > > > To put this into your perspective, what if the file was dropboxed, > altered > > on another machine of the user's, or by another user (through a dropbox > > share), and is now updated in the cloud and due to sync back? Whatever > > solution, versioning-control or other system you come up with to handle > > this, it has to be full of user-informative messages and you can never > keep > > an editable file where locking might be a problem inside a dropbox (or > > other locking+syncing) folder. > > > > It is better to have a DB file (meaning a file that gets small > incremental > > changes over time as opposed to a load-once, save-once methodology) in a > > place that is not affected by locks, and sometimes exporting (using the > > SQLIte backup facility maybe) to the dropbox or shared folder so it gets > > propagated to the cloud... Using it within that folder is just not > > feasible. > > > > Thanks for this insight. > > I purposefully put the SQlite database file in the Dropbox folder because > it was my intention, with this app, to allow a user to use the app on more > than one computer and "sync" the database via Dropbox. E.g., s/he could > make changes to the db at home and then also from his/her office computer, > get home, and the database would be synced. I tried this out on two > computers at home and it seemed to be sort of working, but I occasionally > got conflicted copies and yet never pursued the right way to do it. > > But this must be a fairly commonly sought need. The solution you propose > where I occasionally export a copy of the db to Dropbox is great *for > backup purposes* but seems to exclude the possibility of syncing across > multiple computers. So what would you recommend? > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
