Sqlite + Dropbox

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Sqlite + Dropbox

Nigel Verity
Hi


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.


Thanks


Nige
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

Adam DeVita-2
I've been using Dropbox to synchronize sqlite files with no problems for a year, but only use the Dropbox directory as a synchronization buffer, not as a place to do work.

I usually
1) make a copy of the db in a non drop box directory,
2) complete all work on the copy,
3) replace the Dropbox copy after all connections are closed.

Perhaps I am overly cautious, but I had Dropbox delete an MS-Word file and synchronize the delete while I was saving my changes before. (Fortunately the Dropbox web site was able to restore an older copy, so Id didn't lose everything.)

regards,
Adam
________________________________________
From: sqlite-users <[hidden email]> on behalf of Nigel Verity <[hidden email]>
Sent: April 7, 2017 7:42:57 AM
To: [hidden email]
Subject: [sqlite] Sqlite + Dropbox

Hi


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.


Thanks


Nige
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
=============== CONFIDENTIALITY NOTICE: This e-mail and any attachments contain information from Lytx, Inc. and/or its affiliates, and are intended solely for the use of the named recipient(s). This e-mail may contain confidential information of Lytx and its customers. Any dissemination of this e-mail by anyone other than an intended recipient is strictly prohibited. If you are not a named recipient, you are prohibited from any further viewing of the e-mail or any attachments or from making any use of the e-mail or attachments. If you believe you have received this e-mail in error, notify the sender immediately and permanently delete the e-mail, any attachments, and all copies thereof from any drives or storage media and destroy any printouts of the e-mail or attachments.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

Simon Slavin-3
In reply to this post by Nigel Verity

On 7 Apr 2017, at 12:42pm, Nigel Verity <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

ajm
In reply to this post by Nigel Verity
> De: Nigel Verity <[hidden email]>
> Asunto:  [sqlite] Sqlite + Dropbox
>

> 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.

Besides the problems mentioned by Nigel and Simon, if you place a active SQLite database in a "live" Dropbox directory, the journaling mechanism involves a lot of Internet traffic, unless the database is in a distinct directory that the exejecutable who control the process.

HTH.

--
Adolfo J.M.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

Donald Griggs
In reply to this post by Simon Slavin-3
On Fri, Apr 7, 2017 at 10:18 AM, Simon Slavin <[hidden email]> wrote:

> Dropbox copies an entire file every time a part of it is updated.


Dropbox claims that it performs difference determination, even on binary
files.

Of course, that would not reduce the other problems listed in this thread
(atomicity, etc.)

From:   https://www.dropbox.com/en/help/8


*Before transferring a file, we compare the new file to the previous
version and only send the piece of the file that changed. This is called a
"binary diff" and works on any file type. Dropbox compresses files (without
any loss of data or quality) before transferring them as well. This way,
you also never have to worry about Dropbox re-uploading a file or wasting
bandwidth.*

Donald G.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

Simon Slavin-3

On 7 Apr 2017, at 5:55pm, Donald Griggs <[hidden email]> wrote:

> Dropbox claims that it performs difference determination, even on binary
> files.

Interesting.  That would definitely decrease bandwidth used by large SQLite databases.  Presumably it would transfer only those pages which had changed.  Probably three or four for a one-row change.  Thanks for the correction.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

Warren Young
On Apr 7, 2017, at 11:04 AM, Simon Slavin <[hidden email]> wrote:
>
> On 7 Apr 2017, at 5:55pm, Donald Griggs <[hidden email]> wrote:
>
>> Dropbox claims that it performs difference determination, even on binary
>> files.
>
> Interesting.  That would definitely decrease bandwidth used by large SQLite databases.

Dropbox is also smart enough to broadcast changes between machines on a LAN sharing the same Dropbox account, so the changes don’t have to go up to the cloud and then be sync’d back down to the other clients of that account on the same LAN.

None of that solve the core problems you identified which make Dropbox a poor choice for sharing a SQLite DB over the Internet.

These projects look like a better way to go:

    https://github.com/alixaxel/ArrestDB
    https://github.com/olsonpm/sqlite-to-rest
    https://www.dreamfactory.com/

All were found with a web search for “sqlite rest”.  I haven’t used any of them; they just look useful.

One could also just use one of the many client-server DBMSes.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

Chris Locke-3
Another 'sharing solution' which is just getting off the ground is dbhub.io,
(https://dbhub.io)
This is a 'github for sqlite databases' allowing you to share databases,
have version control, etc.


Thanks,
Chris


On Fri, Apr 7, 2017 at 6:22 PM, Warren Young <[hidden email]> wrote:

> On Apr 7, 2017, at 11:04 AM, Simon Slavin <[hidden email]> wrote:
> >
> > On 7 Apr 2017, at 5:55pm, Donald Griggs <[hidden email]> wrote:
> >
> >> Dropbox claims that it performs difference determination, even on binary
> >> files.
> >
> > Interesting.  That would definitely decrease bandwidth used by large
> SQLite databases.
>
> Dropbox is also smart enough to broadcast changes between machines on a
> LAN sharing the same Dropbox account, so the changes don’t have to go up to
> the cloud and then be sync’d back down to the other clients of that account
> on the same LAN.
>
> None of that solve the core problems you identified which make Dropbox a
> poor choice for sharing a SQLite DB over the Internet.
>
> These projects look like a better way to go:
>
>     https://github.com/alixaxel/ArrestDB
>     https://github.com/olsonpm/sqlite-to-rest
>     https://www.dreamfactory.com/
>
> All were found with a web search for “sqlite rest”.  I haven’t used any of
> them; they just look useful.
>
> One could also just use one of the many client-server DBMSes.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sqlite + Dropbox

Bob Friesenhahn
In reply to this post by Warren Young
On Fri, 7 Apr 2017, Warren Young wrote:
>
> None of that solve the core problems you identified which make Dropbox a poor choice for sharing a SQLite DB over the Internet.

It seems like there should not be a problem if sqlite's backup
mechanism is always used to produce the database shared with the
Internet rather than exposing the live database to Dropbox.  This
still assumes that the database is only updated at one point or there
is some way to assure that the database is only updated from one
computer at a time and the file is backed up to Dropbox before another
computer accesses it.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...