Knapp Martin wrote:

> Richard, could you elaborate on the issues with Dropbox?

I first came across it in a search at Google for "dropbox sqlite", looking for tips on making the most of that relationship. What I found was a long series of support forum and blog posts filled with horror stories of out of sync or corrupted DB files.

Some merely lose a record. Others lose the DB. And so many have no problem at all that it seems like something we should all be doing, until you look into it far enough to find those who've lost records or the entire DB.

Of all the things I've read, this forum post covered more ground more succinctly than most:

   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.


   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.

> Is there a recommended procedure for dealing with it?

This vendor who uses SQLite for their data storage has the simplest solution for sharing their files via Dropbox: Don't do it. ;) At least, not directly, but this isn't going to work for so many users who don't spend much time monkeying with files directly:

If the SQlite + Dropbox combo were super-robust we wouldn't see so many purpose-built sync systems out there. Sync systems aren't fun to write and eat a lot of time, so if devs could avoid it so simply they would.

But they don't. So many proprietary syncs out there, almost a new one for every app that syncs.

And if you look into sync, you find just about every app that came up with a syncing method has a very long blog post about their sync 2.0, needed after whatever they came up with when they got started didn't work out.

Every OS vendor has a sync system in their APIs, but for obvious lock-in reasons none of them are compatible with any other.

So it's left up to us to figure this stuff out, a million developers all repeating the same wheel-building exercises of Evernote and every other sync-based system....

 Richard Gaskin
 Fourth World Systems
 Software Design and Development for the Desktop, Mobile, and the Web

use-livecode mailing list
Please visit this url to subscribe, unsubscribe and manage your subscription 

Reply via email to