Re: [sqlite] Sqlite + Dropbox

[Bob Friesenhahn]

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
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite + Dropbox

[Chris Locke]

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  wrote:

> On Apr 7, 2017, at 11:04 AM, Simon Slavin  wrote:
> >
> > On 7 Apr 2017, at 5:55pm, Donald Griggs  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite + Dropbox

[Warren Young]

On Apr 7, 2017, at 11:04 AM, Simon Slavin  wrote:
> 
> On 7 Apr 2017, at 5:55pm, Donald Griggs  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite + Dropbox

[Simon Slavin]

On 7 Apr 2017, at 5:55pm, Donald Griggs  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite + Dropbox

[Donald Griggs]

On Fri, Apr 7, 2017 at 10:18 AM, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite + Dropbox

[ajm]

> De: Nigel Verity <nigelver...@hotmail.com>
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite + Dropbox

[Simon Slavin]

On 7 Apr 2017, at 12:42pm, Nigel Verity  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.



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

Re: [sqlite] Sqlite + Dropbox

[Adam DeVita]

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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Nigel Verity <nigelver...@hotmail.com>
Sent: April 7, 2017 7:42:57 AM
To: sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users