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.
<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.
----
http://sqlite.1065341.n5.nabble.com/Sqlite-Dropbox-tp95173p95177.html
> 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:
https://www.maxqda.com/faq/a-13-can-i-store-my-maxqda-projects-in-cloud-based-services-like-dropbox
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
____________________________________________________________________
ambassa...@fourthworld.com http://www.FourthWorld.com
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
