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: <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] > ----------------------------------------------------------------------------- > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
