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?) - 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: <sqlite-users@sqlite.org>
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]
-----------------------------------------------------------------------------
