Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[dd]

>>The integrity check just takes too long so we don't.

pragma quick_check; doesn't take much tme.
http://www.sqlite.org/pragma.html#pragma_quick_check


On Sun, Mar 3, 2013 at 8:00 AM, Jason Dictos  wrote:

> >> .
> >
> > Thanks for the details.  What programming language do you use?  (Dropbox
> > uses Python.)
> >
>
> C++
>
> >> Early on we figured out quite quickly not leverage the read/write
> >> locking models of SQLite
> >
> > Do you use WAL mode?
> >
>
> I played with it early on but I didn't know how stable the feature was and
> didn't need any of its features at the time, so not at this time.
>
> > I'm also curious roughly what you do with your schema.  It looks like the
> > Dropbox on Android schema has elements of implementing a tree with
> > everything in one big table and no triggers.
> >
>
> We have a file table which is our representation of what the cloud
> currently thinks of things. Then we diff what the filesystem thinks and
> describe the differences to the cloud. Once the cloud accepts the event we
> update our file table. We are careful with triggers and foreign keys since
> SQLite slows to a crawl when we have those and decide to delete a few
> thousand rows.
>
> > Do you use any of SQLite's extensibilty such as adding your own
> collations
> > or user defined functions.
> >
>
> We did have some stored procedures early on but we soon found ways to use
> the sql language a bit better, which is always a faster option and
> guarantees use of indices in some cases. We still may have a couple I think
> but they are not used very much.
>
> >> As it stands now, our customers periodically have random I/O errors
> >> ...
> >
> > As you have large deployments, random stuff does happen.  We have a web
> > service with several requests per second from browsers all over the world
> > and sadly have to use HTTP (cough *IE* cough) rather than the SSL we
> > normally use.  Even though TCP/IP is checksummed there are sporadic
> > corruptions that come through (typically bit flips here and there).
> >
> > 8 years ago there were lots of CPU random errors:
> >
> >  http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx
> >
> > Do you run an integrity check at startup on the database?  I did so with
> > BitPim, but we didn't have analytics so there was no idea how often
> > corruption happened.
> >
>
> The integrity check just takes too long so we don't. We are currently
> brainstorming ways of detecting corruption quickly.
>
> > There has been an open feature request for a while to have data checksums
> > to deal with the case that what SQLite thinks it wrote is not what is
> > later returned:
> >
> >  http://www.sqlite.org/src/tktview?name=72b01a982a
> >
>
> Thanks for the info Roger
>
> Jason
>
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.11 (GNU/Linux)
> >
> > iEYEARECAAYFAlExZhYACgkQmOOfHg372QTLBgCgsbidy6oQfmAeS4OWq4OBSmFI
> > zxEAn04lneghgvr+ww76AQWzycZ3x+Q0
> > =eya6
> > -END PGP SIGNATURE-
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> Copy, by Barracuda, helps you store, protect, and share all your amazing
> things. Start today: www.copy.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[Jason Dictos]

>> .
> 
> Thanks for the details.  What programming language do you use?  (Dropbox
> uses Python.)
> 

C++

>> Early on we figured out quite quickly not leverage the read/write
>> locking models of SQLite
> 
> Do you use WAL mode?
> 

I played with it early on but I didn't know how stable the feature was and 
didn't need any of its features at the time, so not at this time.

> I'm also curious roughly what you do with your schema.  It looks like the
> Dropbox on Android schema has elements of implementing a tree with
> everything in one big table and no triggers.
> 

We have a file table which is our representation of what the cloud currently 
thinks of things. Then we diff what the filesystem thinks and describe the 
differences to the cloud. Once the cloud accepts the event we update our file 
table. We are careful with triggers and foreign keys since SQLite slows to a 
crawl when we have those and decide to delete a few thousand rows.

> Do you use any of SQLite's extensibilty such as adding your own collations
> or user defined functions.
> 

We did have some stored procedures early on but we soon found ways to use the 
sql language a bit better, which is always a faster option and guarantees use 
of indices in some cases. We still may have a couple I think but they are not 
used very much. 

>> As it stands now, our customers periodically have random I/O errors
>> ...
> 
> As you have large deployments, random stuff does happen.  We have a web
> service with several requests per second from browsers all over the world
> and sadly have to use HTTP (cough *IE* cough) rather than the SSL we
> normally use.  Even though TCP/IP is checksummed there are sporadic
> corruptions that come through (typically bit flips here and there).
> 
> 8 years ago there were lots of CPU random errors:
> 
>  http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx
> 
> Do you run an integrity check at startup on the database?  I did so with
> BitPim, but we didn't have analytics so there was no idea how often
> corruption happened.
> 

The integrity check just takes too long so we don't. We are currently 
brainstorming ways of detecting corruption quickly.

> There has been an open feature request for a while to have data checksums
> to deal with the case that what SQLite thinks it wrote is not what is
> later returned:
> 
>  http://www.sqlite.org/src/tktview?name=72b01a982a
> 

Thanks for the info Roger

Jason

> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
> 
> iEYEARECAAYFAlExZhYACgkQmOOfHg372QTLBgCgsbidy6oQfmAeS4OWq4OBSmFI
> zxEAn04lneghgvr+ww76AQWzycZ3x+Q0
> =eya6
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Copy, by Barracuda, helps you store, protect, and share all your amazing
things. Start today: www.copy.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/03/13 12:27, Jason Dictos wrote:
> Overall SQLite has been the perfect solution to our situation.

Thanks for the details.  What programming language do you use?  (Dropbox
uses Python.)

> Early on we figured out quite quickly not leverage the read/write
> locking models of SQLite

Do you use WAL mode?

I'm also curious roughly what you do with your schema.  It looks like the
Dropbox on Android schema has elements of implementing a tree with
everything in one big table and no triggers.

Do you use any of SQLite's extensibilty such as adding your own collations
or user defined functions.

> As it stands now, our customers periodically have random I/O errors
> ...

As you have large deployments, random stuff does happen.  We have a web
service with several requests per second from browsers all over the world
and sadly have to use HTTP (cough *IE* cough) rather than the SSL we
normally use.  Even though TCP/IP is checksummed there are sporadic
corruptions that come through (typically bit flips here and there).

8 years ago there were lots of CPU random errors:

  http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx

Do you run an integrity check at startup on the database?  I did so with
BitPim, but we didn't have analytics so there was no idea how often
corruption happened.

There has been an open feature request for a while to have data checksums
to deal with the case that what SQLite thinks it wrote is not what is
later returned:

  http://www.sqlite.org/src/tktview?name=72b01a982a

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlExZhYACgkQmOOfHg372QTLBgCgsbidy6oQfmAeS4OWq4OBSmFI
zxEAn04lneghgvr+ww76AQWzycZ3x+Q0
=eya6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[Jason Dictos]

Overall SQLite has been the perfect solution to our situation. We needed some 
relation but nothing too heavy weight to store our meta data about files and 
shares. Early on we figured out quite quickly not leverage the read/write 
locking models of SQLite (SQLITE_BUSY hell) but instead use a single monolithic 
lock for all our transactions into SQLite. Also we've struggled a bit with 
delete performance in SQLite and  quickly learned not to use too many foreign 
key constraints in these situations; in addition to ensuring our like/glob 
queries leveraged the index where appropriate.

As it stands now, our customers periodically have random I/O errors in sqlite, 
that we have no real explanation for aside from possibly a write i/o failure on 
the system, in addition to the occasional corrupt db. None of the issues with 
SQLite have been show stoppers though, and its worked out great for us.

-Jason

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Roger Binns
Sent: Friday, March 1, 2013 1:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Announcement of Copy - A cross platform syncing app, that 
uses SQLite as its backend

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/03/13 09:56, Jason Dictos wrote:
> This app we wrote couldn't have happened without SQLite,

I would be interested in hearing about the experience with SQLite, especially 
anything relevant to other developers.

One of your competitors is Dropbox who also use SQLite (I have no connection to 
them other than as a customer).  On desktop computers you can find the SQLite 
databases in a .dropbox subdirectory of your home directory (or equivalent).  
They were encrypted a while back because of various issues.

The Dropbox Android client also uses SQLite with a non-encrypted database.
 It has several tables related to camera images, with the core schema being 
these two tables:


CREATE TABLE dropbox (_id INTEGER PRIMARY KEY AUTOINCREMENT, _data TEXT, 
modified TEXT, bytes INTEGER, revision TEXT, hash TEXT, icon TEXT, is_dir 
INTEGER, path TEXT , canon_path TEXT, root TEXT, size TEXT, mime_type TEXT, 
thumb_exists INTEGER, parent_path TEXT, canon_parent_path TEXT, _display_name 
TEXT COLLATE NOCASE, is_favorite INTEGER, local_modified INTEGER, local_bytes 
INTEGER, local_revision TEXT, local_hash TEXT, accessed INTEGER, encoding TEXT, 
sync_status INTEGER, _natsort_name TEXT COLLATE NOCASE);

CREATE TABLE pending_uploads (_id INTEGER PRIMARY KEY AUTOINCREMENT, class 
TEXT, data TEXT);

dropbox._data is the path to a local copy of the file, not the file contents.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEw8/IACgkQmOOfHg372QSl3gCgnJzdP1aqglyaUkL2Dp2+nPIk
7j8AoK7sorrrzzh0z+jrxzmGjWqjx4AL
=zgYl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Copy, by Barracuda, helps you store, protect, and share all your amazing
things. Start today: www.copy.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/03/13 09:56, Jason Dictos wrote:
> This app we wrote couldn't have happened without SQLite,

I would be interested in hearing about the experience with SQLite,
especially anything relevant to other developers.

One of your competitors is Dropbox who also use SQLite (I have no
connection to them other than as a customer).  On desktop computers you
can find the SQLite databases in a .dropbox subdirectory of your home
directory (or equivalent).  They were encrypted a while back because of
various issues.

The Dropbox Android client also uses SQLite with a non-encrypted database.
 It has several tables related to camera images, with the core schema
being these two tables:


CREATE TABLE dropbox (_id INTEGER PRIMARY KEY AUTOINCREMENT, _data TEXT,
modified TEXT, bytes INTEGER, revision TEXT, hash TEXT, icon TEXT, is_dir
INTEGER, path TEXT , canon_path TEXT, root TEXT, size TEXT, mime_type
TEXT, thumb_exists INTEGER, parent_path TEXT, canon_parent_path TEXT,
_display_name TEXT COLLATE NOCASE, is_favorite INTEGER, local_modified
INTEGER, local_bytes INTEGER, local_revision TEXT, local_hash TEXT,
accessed INTEGER, encoding TEXT, sync_status INTEGER, _natsort_name TEXT
COLLATE NOCASE);

CREATE TABLE pending_uploads (_id INTEGER PRIMARY KEY AUTOINCREMENT, class
TEXT, data TEXT);

dropbox._data is the path to a local copy of the file, not the file contents.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEw8/IACgkQmOOfHg372QSl3gCgnJzdP1aqglyaUkL2Dp2+nPIk
7j8AoK7sorrrzzh0z+jrxzmGjWqjx4AL
=zgYl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend

[Jason Dictos]

Hi All,

I just wanted to let you all in on a deal we are offering to 
everyone on this mailing list, regarding Copy a new syncing app we have 
deployed here at barracuda networks (www.copy.com). This 
app we wrote couldn't have happened without SQLite, both on Android, and 
Windows/Mac/Linux, so to give back to the community, we are offering a free 
20GB of cloud storage to everyone who mentions this email and contacts 
supp...@copy.com.

Thanks to everyone on this list that has taught us a lot about how to make 
SQLite work great for our product!
-Jason


Copy, by Barracuda, helps you store, protect, and share all your amazing
things. Start today: www.copy.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users