Thank you Simon for a very full and informative reply.
Howard
Date: Sat, 1 Dec 2012 19:37:45 -0800
From: [email protected]
To: [email protected]
Subject: Re: How to restore from backup or alter db in multi-user envionment?
On 1 Dec 2012, at 3:22pm, dochsm <[hidden email]> wrote:
> My database is shared by many users on a network. As part of auto-updating my
> client applications via the web it might be necessary to alter some table
> structures / view sql or put extra data into tables inside the shared sqlite
> database. Problem is that the db might be in use at the time.
>
> Qn1) Is it safe simply to wrap up everthing I want to do in a transaction
> and then commit the transaction?
As far as SQLite is concerned, yes. SQLite will lock the schema against
changes while SELECT UPDATE INSERT commands are executing, and will lock the
database against those commands while the database schema is being changed.
The effect is that locking is used in the same way it would be if two users
tried to do UPDATE commands at the same time.
> Q2) If I back up the database first using the online backup to safely back
> it up, how do I safely restore it again when the db might be in use at the
> time?
I assume you're talking about the online backup API:
<http://www.sqlite.org/c3ref/backup_finish.html>
Under those circumstances I think you would use the same backup API in the
other direction to restore the database again. It will have a lock on that
destination database the whole time it's working, of course. So if you have
implemented a timeout correctly your users will just get a long pause or a
"database busy" message during access.
If you have been writing your code correctly you will have used transactions to
group all related accesses /including related SELECTs/ together. So any
particular piece of code in your app should notice only consistency.
> I'm using DISQLite and the online api in delphi, not the command line thing.
I have seen some bad drivers which assume that underlying schema will not be
changed by another user while they have a database connection open. I have no
reason to believe that this is one of them, but it might be worth reading the
documentation.
Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
If you reply to this email, your message will be added to the
discussion below:
http://sqlite.1065341.n5.nabble.com/How-to-restore-from-backup-or-alter-db-in-multi-user-envionment-tp65812p65817.html
To unsubscribe from How to restore from backup or alter db in
multi-user envionment?, click here.
NAML
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/How-to-restore-from-backup-or-alter-db-in-multi-user-envionment-tp65812p65818.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
