On Tue, 2007-06-19 at 00:46 +0100, Michael Hooker wrote:
Christian wrote:
>>Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the
database file, then executing a 'ROLLBACK' to end the transaction.<<
>>and can be safely copied at the OS level<<
I also have a need to copy a live database which is constantly being
updated
24/7 by software which decodes radio signals. I need to safely
interrogate
and process a static version of the data without risk of messing up the
original. Once midnight has passed, the copy can be taken and the
previous
day's data extracted from the copy. But as a raw beginner I don't
clearly
understand what is being said here.
When you say >>and can be safely copied at the OS level<<, I guess you
must
mean something more than right-click the file icon and select "Copy" ;)
In
any case I would much prefer the copy to be taken programmatically, which
would be neater and faster, and could be done automatically in the middle
of
the night when the data flow is much less intense. I use, as best I
can,
Delphi 7 and Ralf Junker's DisqLite3, so can I safely:-
(1) send a BEGIN IMMEDIATE command,
(2) issue Delphi Windows API command "CopyFile(PChar(SourceFileName),
PChar(DestFileName), FALSE);
(3) send a ROLLBACK command.
The destination would be the same folder as the source, so no network
delay.
The file is about 55 Megabytes.
My data is of no commercial value, but I have a few hundred people
looking
forward to my reports every day and don't want to mess it up; I have no
other SQLite3 databases to experiment with, so please forgive me for
asking
you experts what is probably a very basic question. It would also be
very
helpful if someone could explain in jargon-free terms what ROLLBACK means
in
this context and why it apparently serves the purpose of finishing the
transaction which has not attempted to change anything(why not END?)
"ROLLBACK" means abandon the current transaction, and put the database
back the way it was before the transaction started. To "roll back" all
changes so far.
In this specific context, the important part is that the "BEGIN
IMMEDIATE" locks the database file and the "ROLLBACK" releases the
lock. A "COMMIT" or "END" would be logically identical - it releases
the lock, and since there were no database changes made in this
transaction, it doesn't matter if they are rolled back or not.
Under the hood, there is a minor difference - a COMMIT will update
the database change-counter, meaning that all other connections
will need to discard their caches. A ROLLBACK does not update the
change-counter, so caches held by other connections will remain
valid.
Dan.
- I
keep coming across the word and I'm sure it means something fairly
simple,
but I have not encountered it until I started looking at SQLite. I've
let
Delphi and VisualDB handle all my database work through the BDE until now
and never had any need to worry about locking or contentions.
Thanks
Michael Hooker
----- Original Message -----
From: "Christian Smith" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Monday, June 18, 2007 6:39 PM
Subject: Re: [sqlite] Proper way to transfer a live sqlite database
> Rich Rattanni uttered:
>
>> The databases will be in flux, and I didnt necessairly want to suspend
>> the application that is performs reads and writes into the database.
>> A simple copy worries me because it seems like messing with SQLITE on
>> the file level is dangerous since you circumvent all the protection
>> mechanisms that provide fault tolerance. I didnt want to have to
>> worry about if the database has a journal file that needs copied, or
>> any other situation like that. I figured using the SQLITE API to do
>> the copy would award me some protection against corruption.
>
>
> You're right to be cautious. Never copy an in use database if that
> database could possibly be updated.
>
> If you open the database, and obtain a SQLite read lock on it, you can
> be
> sure it is not going to be modified, and can be safely copied at the OS
> level.
>
> Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the
> database file, then executing a 'ROLLBACK' to end the transaction.
>
> To limit the time the database is locked, I suggest copying the file to
> a
> local filesystem first, then transferring across the network after the
> lock is released.
>
> Christian
>
>
>
> --
> /"\
> \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
> X - AGAINST MS ATTACHMENTS
> / \
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
