Re: [sqlite] Backing up SQLite file
Good point. Thanks. > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 09/10/2010 01:11 PM, Josh wrote: >> A saw the backup API's but they looked overly complicated for my situation. > > How so? > > There is sample code at this link (see the second example specifically): > > http://www.sqlite.org/backup.html > > It is at most 10 lines of code. > > The advantage of using the backup API is that it is guaranteed to be > correct. As your program grows over time, other things may access the > database, disk errors could occur, contention etc, it will always get things > right. > > Reinventing that wheel will take you more than 10 lines of code, and you are > unlikely to do as much testing as SQLite does. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkyKpAUACgkQmOOfHg372QSDigCg2MTTsstinndl+VnyeuXh38Mu > 0YcAnRQhuPq48yBoMoODYrv+JcgdghL9 > =M+nG > -END PGP SIGNATURE- > ___ > 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] Backing up SQLite file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/10/2010 01:11 PM, Josh wrote: > A saw the backup API's but they looked overly complicated for my situation. How so? There is sample code at this link (see the second example specifically): http://www.sqlite.org/backup.html It is at most 10 lines of code. The advantage of using the backup API is that it is guaranteed to be correct. As your program grows over time, other things may access the database, disk errors could occur, contention etc, it will always get things right. Reinventing that wheel will take you more than 10 lines of code, and you are unlikely to do as much testing as SQLite does. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyKpAUACgkQmOOfHg372QSDigCg2MTTsstinndl+VnyeuXh38Mu 0YcAnRQhuPq48yBoMoODYrv+JcgdghL9 =M+nG -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backing up SQLite file
On Fri, Sep 10, 2010 at 01:11:23PM -0700, Josh scratched on the wall:
> It should be noted that my program is a single thread, and I can assume no
> other programs or threads should be accessing my database file. Since
> SQLite auto commits transactions and I won't have any transactions open,
> I'm thinking there shouldn't be any reason I couldn't just copy the file
> without an exclusive lock, but it sounds like I may be missing something?
If you can really, truly, assume all those things, then yes.
> I'm assuming the BEGIN EXCLUSIVE will get a file lock. Will the BEGIN
> EXCLUSIVE statement block, waiting until it can get an exclusive lock, or
> if it fails to get an exclusive lock, will it immediately return to the
> caller (if called by sqlite3_get_table() for example)?
It will fail and return SQLITE_BUSY.
> runsql("sync database somehow?"); //do I need this?
No.
> runsql("BEGIN EXCLUSIVE");
> copydatabasefile();
> runsql("ROLLBACK");
Assuming runsql() will re-run a statement until it works (which is
normally a bad practice), then that's the general idea.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backing up SQLite file
Thanks so much, this helps. A saw the backup API's but they looked overly
complicated for my situation. A little clarification.
It should be noted that my program is a single thread, and I can assume no
other programs or threads should be accessing my database file. Since
SQLite auto commits transactions and I won't have any transactions open,
I'm thinking there shouldn't be any reason I couldn't just copy the file
without an exclusive lock, but it sounds like I may be missing something?
I'm assuming the BEGIN EXCLUSIVE will get a file lock. Will the BEGIN
EXCLUSIVE statement block, waiting until it can get an exclusive lock, or
if it fails to get an exclusive lock, will it immediately return to the
caller (if called by sqlite3_get_table() for example)?
Are there other ways to lock or sync the database than this?
How would this work?:
runsql("sync database somehow?"); //do I need this?
runsql("BEGIN EXCLUSIVE");
copydatabasefile();
runsql("ROLLBACK");
Thanks!
Josh
> On Fri, Sep 10, 2010 at 12:09:58PM -0700, Josh scratched on the wall:
>> Hello all,
>>
>> I think this is a simple question...
>>
>> I am using the C api to open and read/write a SQLite database (ie.
>> sqlite3_open_v2() etc.). I would like to have a function in my program to
>> backup the database file (using the OSes copy command).
>
> You might also be able to use the backup APIs.
>
> See: http://sqlite.org/c3ref/backup_finish.html
>
>> I can guarentee
>> that my program will not write to the database. Do I need to sync or lock
>> the database file before I do the copy command?
>
> That would be a good idea.
>
>> I believe that as long as
>> I have no open write transactions the file should be fine to copy, is this
>> correct?
>
> No, not exactly. Transactions are normally lazy about getting locks.
> To force the transaction to get the locks, issue the command:
>
>BEGIN EXCLUSIVE
>
> If that works, you know nobody else can touch the database. You're
> then free to copy it. Once the copy is done, you can rollback the
> transaction.
>
> -j
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backing up SQLite file
On Fri, Sep 10, 2010 at 12:09:58PM -0700, Josh scratched on the wall: > Hello all, > > I think this is a simple question... > > I am using the C api to open and read/write a SQLite database (ie. > sqlite3_open_v2() etc.). I would like to have a function in my program to > backup the database file (using the OSes copy command). You might also be able to use the backup APIs. See: http://sqlite.org/c3ref/backup_finish.html > I can guarentee > that my program will not write to the database. Do I need to sync or lock > the database file before I do the copy command? That would be a good idea. > I believe that as long as > I have no open write transactions the file should be fine to copy, is this > correct? No, not exactly. Transactions are normally lazy about getting locks. To force the transaction to get the locks, issue the command: BEGIN EXCLUSIVE If that works, you know nobody else can touch the database. You're then free to copy it. Once the copy is done, you can rollback the transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Backing up SQLite file
Hello all, I think this is a simple question... I am using the C api to open and read/write a SQLite database (ie. sqlite3_open_v2() etc.). I would like to have a function in my program to backup the database file (using the OSes copy command). I can guarentee that my program will not write to the database. Do I need to sync or lock the database file before I do the copy command? I believe that as long as I have no open write transactions the file should be fine to copy, is this correct? Is there a way to guarentee there are no open write transactions (I can guarentee there aren't any open transactions by going through my code, but I was just wondering if there is a way for SQLite to tell this as well)? Thanks for any thoughts. Josh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
