On 7 Apr 2017, at 12:42pm, Nigel Verity <nigelver...@hotmail.com> wrote:
> I have just started using Dropbox to share documents. If I place a Sqlite > database in my Dropbox folder I access it from a local application as a local > file. This is then automatically synced with the copy held in my Dropbox > cloud storage. > > > This strikes me as a possible way to share a database across the internet. Is > this a practical approach or does the time it takes to sync the files > introduce too much latency? I'd be interested in advice and/or experiences > from anybody who has tried this. tl;dr: Dropbox fails the ACID test for databases. So does its competitor Box, which works the same way. Use it for SQLite databases only if your transfer time exceeds its synch time. <https://en.wikipedia.org/wiki/ACID> Dropbox does not share files across the internet. It copies changed files from one computer to its server, then from that server to all the other computers which have access to that shared folder. Dropbox copies an entire file every time a part of it is updated. If you have a 200GB database and delete one row, it needs to copy the entire database file to all the other computers that can access it. While that works fine for small files, it will involve a lot of traffic as your files grow in size. If two copies of the file are updated at the same time on different computers, the changes made in one copy disappear. Your user-interface will ask you which one you want, but you may not have enough knowledge to pick the 'best' one. If Dropbox decides to take a copy while SQLite is in the middle of processing a transaction, you will temporarily have a copy of the database with a partially-processed transaction on all the computers which have access to that shared area. Dropbox doesn’t understand that the database file and the journal file go together, even if they’re in the same folder. And in order to stop one user from hogging its servers there’s sometimes a short delay between when it updates its copy of one file and when it updates its copy of another file. So it’s possible for one computer which has a copy of the database to have a newer database file than its journal file, or vice versa. SQLite autorepairs files when it finds a database file and a journal file which don’t match. I don’t know what it would do under the above two conditions. And what it would do would vary depending on which file Dropbox decided to copy first. Given all the above, I might use Dropbox or Box to promulgate copies of a SQLite database, but only if (A) I had an backup of a recent version and the backup system does not involve Dropbox/Box. (B) If I was fairly sure that if I used one computer to update the database, none of the other computers would try to open the file (even just for reading) until a couple of minutes after the updates were done and the service had had time to sync both database and journal file. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
