Re: [sqlite] Proper way to transfer a live sqlite database
On Tue, 2007-06-19 at 11:51 -0700, Gerry Snyder wrote: > Michael Hooker wrote: > > Many thanks for the explanation Dan. > Ditto the thanks. > > I suspected the purpose of ROLLBACK was as you say, but couldn't see > > why it was used here. You point out the "under the hood" difference > > between ROLLBACK and COMMIT, but what about END? My main (third-party, > > commercial) application may well have some data stored waiting to be > > fed into the database after the file has been copied, and if it is > > forced to discard its cache that presumably means these are lost, > > which wouldn't be a good idea. END is the same as COMMIT in sqlite. > It shouldn't have to. The cache Dan was referring to was an internal > copy of (part of) what is already in the data base. If the data base > file has been updated, that copy has to be discarded, since it may not > be valid--of course, it may be valid, but figuring out whether it is > would be a lot more work than just rereading it. Anyhow, this is all > happening at a much lower level than the application data you are > referring to, which is still valid and should be entered into the file. Right. If another app has some "writes" (dirty pages) in it's cache, then it will already have at least a RESERVED lock on the database file. If this is the case the "BEGIN IMMEDIATE" statement executed by the copy-file process will fail to obtain it's EXCLUSIVE database lock. So the only logic the file-copy process needs is "Do not do the file copy until after the BEGIN IMMEDIATE succeeds". Dan. > HTH, > > Gerry > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proper way to transfer a live sqlite database
Thank you, Gerry. So basically, it seems, ROLLBACK, COMMIT or END would all do nicely in this particular context. Hopefully I'll never need to worry about locking etc because I have no intention of changing the data in the database and far> I've successfully managed to extract what I want from the live database using a SELECT statement with only the occasional "busy" message. However, the prospect of what amounts to an automatic daily back-up and then working on the backup is much more comfortable. I think I'm going to have to find some excuse for closing the data-provider down (next time Windows Update insists I have to re-boot, probably), make a quick backup copy of the inactive database in the normal way, turn the data-provider back on again and run an experimental program on the new live database. If it doesn't work, switch off again, quickly restore the backup, get going again and come back to you kind folk. All in the wee small hours when the data-flow is at its lowest. Just for interest, this is what it's all about: http://www.kinetic-avionics.co.uk/sbs-1.php The software that processes the data from this clever piece of kit uses Sqlite3, but it has to be said that it's missing some vital features, one of them being the ability to produce a report of what happened between time A and time B. It will produce a backup but this takes an age and is done by exporting each table in the database as a separate .csv file - and last time I tried it on a live database the reporting application just went into "not responding" mode. So we have to do it ourselves. Thanks again Michael Hooker http://www.AvPhotosOnline.org.uk - Original Message - From: "Gerry Snyder" <[EMAIL PROTECTED]> To: Sent: Tuesday, June 19, 2007 7:51 PM Subject: Re: [sqlite] Proper way to transfer a live sqlite database Michael Hooker wrote: Many thanks for the explanation Dan. Ditto the thanks. I suspected the purpose of ROLLBACK was as you say, but couldn't see why it was used here. You point out the "under the hood" difference between ROLLBACK and COMMIT, but what about END? My main (third-party, commercial) application may well have some data stored waiting to be fed into the database after the file has been copied, and if it is forced to discard its cache that presumably means these are lost, which wouldn't be a good idea. It shouldn't have to. The cache Dan was referring to was an internal copy of (part of) what is already in the data base. If the data base file has been updated, that copy has to be discarded, since it may not be valid--of course, it may be valid, but figuring out whether it is would be a lot more work than just rereading it. Anyhow, this is all happening at a much lower level than the application data you are referring to, which is still valid and should be entered into the file. HTH, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proper way to transfer a live sqlite database
Michael Hooker wrote: Many thanks for the explanation Dan. Ditto the thanks. I suspected the purpose of ROLLBACK was as you say, but couldn't see why it was used here. You point out the "under the hood" difference between ROLLBACK and COMMIT, but what about END? My main (third-party, commercial) application may well have some data stored waiting to be fed into the database after the file has been copied, and if it is forced to discard its cache that presumably means these are lost, which wouldn't be a good idea. It shouldn't have to. The cache Dan was referring to was an internal copy of (part of) what is already in the data base. If the data base file has been updated, that copy has to be discarded, since it may not be valid--of course, it may be valid, but figuring out whether it is would be a lot more work than just rereading it. Anyhow, this is all happening at a much lower level than the application data you are referring to, which is still valid and should be entered into the file. HTH, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proper way to transfer a live sqlite database
Many thanks for the explanation Dan. I suspected the purpose of ROLLBACK was as you say, but couldn't see why it was used here. You point out the "under the hood" difference between ROLLBACK and COMMIT, but what about END? My main (third-party, commercial) application may well have some data stored waiting to be fed into the database after the file has been copied, and if it is forced to discard its cache that presumably means these are lost, which wouldn't be a good idea. I'm not entirely sure that the application goes about storing data in a sensible fashion anyway, as sometimes the snapshot it is supposed to give does not include items I know were received into the system hours ago - while at other times things appear instantly. So, calling the Windows CopyFile function from Delphi as I proposed counts as copying "at the OS level", does it? Thanks Michael Hooker co-list-owner Original Gatwick Spotters List & Gatwick AvPhotos http://www.AvPhotosOnline.org.uk - Original Message - From: "Dan Kennedy" <[EMAIL PROTECTED]> To: Sent: Tuesday, June 19, 2007 4:55 AM Subject: Re: [sqlite] Proper way to transfer a live sqlite database 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: 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
Re: [sqlite] Proper way to transfer a live sqlite database
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: > 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'
Re: [sqlite] Proper way to transfer a live sqlite database
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: 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] -
Re: [sqlite] Proper way to transfer a live sqlite database
One of the most endearing features of Sqlite is that it is a single file. You can copy it with impunity. If it is in use while you are copying you can launch an exclusive transaction to block other users and copy it and be assured of its state. Rich Rattanni wrote: 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. On 6/18/07, Fred Williams <[EMAIL PROTECTED]> wrote: It would most likely be much quicker (and simpler) just to utilize the OS's file coping feature to copy the table. What would be gained with the attaching databases approach over just a straight file copy? Fred > -Original Message- > From: Rich Rattanni [mailto:[EMAIL PROTECTED] > Sent: Monday, June 18, 2007 10:20 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Proper way to transfer a live sqlite database > > > I was writing to ask some opinions on how to perform a download of a > live sqlite database. Basically I have a device, which stores all > manner of data in a sqlite database. Periodically I want to download > the data to a central server for viewing. I discussed it with my > colleagues, and they felt that I should just copy the file to the > server. However I was thinking of having a blank database with > identical schema to the database I am copying. Then when the download > occurs, I would ATTACH the live database to the blank database, and > query the data from one to the other. Then I would close the cloned > version and offload that to the server. > > The standard questions now follow... > Is this an acceptable way? > Is there a better/best way? > > Thanks for any input, > Rich Rattanni > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proper way to transfer a live sqlite database
Thank you Igor and Christian. I appreciate your help. On 6/18/07, Christian Smith <[EMAIL PROTECTED]> wrote: 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] -
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] -
Re: [sqlite] Proper way to transfer a live sqlite database
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. On 6/18/07, Fred Williams <[EMAIL PROTECTED]> wrote: It would most likely be much quicker (and simpler) just to utilize the OS's file coping feature to copy the table. What would be gained with the attaching databases approach over just a straight file copy? Fred > -Original Message- > From: Rich Rattanni [mailto:[EMAIL PROTECTED] > Sent: Monday, June 18, 2007 10:20 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Proper way to transfer a live sqlite database > > > I was writing to ask some opinions on how to perform a download of a > live sqlite database. Basically I have a device, which stores all > manner of data in a sqlite database. Periodically I want to download > the data to a central server for viewing. I discussed it with my > colleagues, and they felt that I should just copy the file to the > server. However I was thinking of having a blank database with > identical schema to the database I am copying. Then when the download > occurs, I would ATTACH the live database to the blank database, and > query the data from one to the other. Then I would close the cloned > version and offload that to the server. > > The standard questions now follow... > Is this an acceptable way? > Is there a better/best way? > > Thanks for any input, > Rich Rattanni > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Proper way to transfer a live sqlite database
It would most likely be much quicker (and simpler) just to utilize the OS's file coping feature to copy the table. What would be gained with the attaching databases approach over just a straight file copy? Fred > -Original Message- > From: Rich Rattanni [mailto:[EMAIL PROTECTED] > Sent: Monday, June 18, 2007 10:20 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Proper way to transfer a live sqlite database > > > I was writing to ask some opinions on how to perform a download of a > live sqlite database. Basically I have a device, which stores all > manner of data in a sqlite database. Periodically I want to download > the data to a central server for viewing. I discussed it with my > colleagues, and they felt that I should just copy the file to the > server. However I was thinking of having a blank database with > identical schema to the database I am copying. Then when the download > occurs, I would ATTACH the live database to the blank database, and > query the data from one to the other. Then I would close the cloned > version and offload that to the server. > > The standard questions now follow... > Is this an acceptable way? > Is there a better/best way? > > Thanks for any input, > Rich Rattanni > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Proper way to transfer a live sqlite database
I was writing to ask some opinions on how to perform a download of a live sqlite database. Basically I have a device, which stores all manner of data in a sqlite database. Periodically I want to download the data to a central server for viewing. I discussed it with my colleagues, and they felt that I should just copy the file to the server. However I was thinking of having a blank database with identical schema to the database I am copying. Then when the download occurs, I would ATTACH the live database to the blank database, and query the data from one to the other. Then I would close the cloned version and offload that to the server. The standard questions now follow... Is this an acceptable way? Is there a better/best way? Thanks for any input, Rich Rattanni - To unsubscribe, send email to [EMAIL PROTECTED] -
