Re: [sqlite] Sqlite Secure backup
David Costa <[EMAIL PROTECTED]> 03/06/2004 09:26 AM To: [EMAIL PROTECTED] cc: Subject: [sqlite] Sqlite Secure backup > I am in the process of writing a PEAR package to handle Sqlite backups > (both local backups and remote backups) and I am looking for some > suggestions. > Is the right way to proceed the db dump ? I assume that copying the db > will result in a corrupted file if the db is used at the time of > backup. Using .dump in the sqlite command-line utility is always going to be the simplest, lowest risk solution. I recommend it, especially for small databases. Using .dump, you don't have to worry about other readers and writers. You don't have to worry about stray -journal files. All of this is taken care of by the engine itself. You end up with a sequence of sql commands that are suitable for rebuilding the sqlite database. This list has a number of advantages over the basic file format: 1) Although sqlite database changes have in the past been forward compatible, it is more likely that the .dump format of an ancient version will be restorable on modern sqlite versions than that the old database files is restorable. 2) .dump output is more likely to be backwards compatible, in case you decide you have to downgrade your sqlite version 3) The dump file is human readible and is more likely to be able to be applied to other database software if the need arises 4) The .dump output is more compressable than the original file format. In gzip form it will usually take up less space than the gzip of the original database file. The only significant downside to the .dump format in my experience is that it takes longer to restore from your media than a straight ungzip would take. this is particularly true if you have large tables with indices. On some (old) hardware I use a 400meg file takes only a few seconds to restore from gzip on backup media, but the equivalent .dump format takes in the order of half an hour to rebuild its tables and indices. If you have large tables and restoration time is critial, the original sqlite files are probably the best thing to backup. If you're more interested in the security of your data, I recommend backing up the .dump output instead. Benjamin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Sqlite Secure backup
On Jun 3, 2004, at 2:17 AM, Darren Duncan wrote: At 2:04 AM +0200 6/3/04, David Costa wrote: so in this case you just check if a file .journal exist and if it does you keep that file right ? Yes. But check the sqlite.org website documentation to make sure that is the correct file name. from here http://www.sqlite.org/fileformat.html it looks like is a file with -journal appended. Now (hope will be my last question and sorry again for bothering) am I right to assume that if a -journal file exist something has gone wrong and that file should be part of the backup for the relevant rollback ? Secondly, for testing purposes, how can I create the situation where something doesn't go as planned and the -journal file appears ? thanks David Costa It would be interesting to see how you did it in Perl The function I describe isn't implemented yet, but should be around release 5 or 6 of my Rosetta-Extensions distribution on CPAN ( http://search.cpan.org , that Perl repository which PEAR emulates for PHP ). Meanwhile, the (pre-alpha) API is already documented in the SQL-SyntaxModel distribution under "commands". -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Sqlite Secure backup
David, please send all replies, like the original questions, to the list ([EMAIL PROTECTED]), not directly to me. You would get a quicker response that way, and I don't feel soley responsible for answering. At 2:49 AM +0200 6/3/04, David Costa wrote: On Jun 3, 2004, at 2:17 AM, Darren Duncan wrote: At 2:04 AM +0200 6/3/04, David Costa wrote: so in this case you just check if a file .journal exist and if it does you keep that file right ? Yes. But check the sqlite.org website documentation to make sure that is the correct file name. from here http://www.sqlite.org/fileformat.html it looks like is a file with -journal appended. Now (hope will be my last question and sorry again for bothering) am I right to assume that if a -journal file exist something has gone wrong and that file should be part of the backup for the relevant rollback ? Yes. If the -journal file exists, then backup that file also and keep it with the actual database file; the two files should stay in the same folder and have the same names, as they originally do. Secondly, for testing purposes, how can I create the situation where something doesn't go as planned and the -journal file appears ? In a program of yours that uses SQLite, make some change to a database, such as an INSERT, and then make your program exit without issuing a commit or a close. Then a journal file should be left behind. thanks David Costa -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Sqlite Secure backup
At 9:42 AM +1000 6/3/04, Greg Obleshchuk wrote: Hi David, I have been thinking about developing a backup/restore as part of my wrapper. It would be great to have all of these features compatible. I really wanted to backup the database and then have the ability to restore just one table or the entire DB. What do you think? Maybe we should create a wiki page on sqlite.org? Greg As a partial coincidence, I am doing something similar but in Perl. My 'Rosetta' database abstraction layer has a "clone database" function in its API (which can be used for backups); when implemented against SQLite, this function will do a standard file copy such as I described. (Note that the "normal" access to SQLite is implemented using DBD::SQLite, such as open/close, queries, etc.) -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Sqlite Secure backup
On Jun 3, 2004, at 1:38 AM, Darren Duncan wrote: At 1:26 AM +0200 6/3/04, David Costa wrote: I am in the process of writing a PEAR package to handle Sqlite backups (both local backups and remote backups) and I am looking for some suggestions. Is the right way to proceed the db dump ? I assume that copying the db will result in a corrupted file if the db is used at the time of backup. The aim would be to dump or duplicate a whole database for a local or remote backup with the relevant integrity checks. You can do a file backup reliably, like this: thanks for your reply, few more questions 1. Obtain a file system read lock on the SQLite database file. this shouldn't be a problem but I will need to do it from php. How would you do it in console ? 2. Check if a corresponding rollback log exists. how ? :D If it does not, then the database should be internally consistant. If it does, then you need to copy that file too and keep it together with the SQLite database file as if they were one unit. Copy the database file, and the rollback file if it exists. 3. Release the file system lock. 4. Both the original and the copy will be in an identical and either correct or recoverable state. Thanks in advance for the help Regards David Costa -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Sqlite Secure backup
Hi David, I have been thinking about developing a backup/restore as part of my wrapper. It would be great to have all of these features compatible. I really wanted to backup the database and then have the ability to restore just one table or the entire DB. What do you think? Maybe we should create a wiki page on sqlite.org? Greg -Original Message- From: David Costa [mailto:[EMAIL PROTECTED] Sent: Thursday, 3 June 2004 9:26 AM To: [EMAIL PROTECTED] Subject: [sqlite] Sqlite Secure backup Hello Everyone, I am a contributor on the php project ( my profile is available at http://pear.php.net/user/gurugeek ) where I maintain several extensions. I am in the process of writing a PEAR package to handle Sqlite backups (both local backups and remote backups) and I am looking for some suggestions. Is the right way to proceed the db dump ? I assume that copying the db will result in a corrupted file if the db is used at the time of backup. The aim would be to dump or duplicate a whole database for a local or remote backup with the relevant integrity checks. Thanks in advance for your time and attention I remain, Very much obliged, Regards, David Costa PEAR- PHP Extensions and Application Repository Developer http://pear.php.net/user/gurugeek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Sqlite Secure backup
At 1:26 AM +0200 6/3/04, David Costa wrote: I am in the process of writing a PEAR package to handle Sqlite backups (both local backups and remote backups) and I am looking for some suggestions. Is the right way to proceed the db dump ? I assume that copying the db will result in a corrupted file if the db is used at the time of backup. The aim would be to dump or duplicate a whole database for a local or remote backup with the relevant integrity checks. You can do a file backup reliably, like this: 1. Obtain a file system read lock on the SQLite database file. 2. Check if a corresponding rollback log exists. If it does not, then the database should be internally consistant. If it does, then you need to copy that file too and keep it together with the SQLite database file as if they were one unit. Copy the database file, and the rollback file if it exists. 3. Release the file system lock. 4. Both the original and the copy will be in an identical and either correct or recoverable state. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Sqlite Secure backup
Hello Everyone, I am a contributor on the php project ( my profile is available at http://pear.php.net/user/gurugeek ) where I maintain several extensions. I am in the process of writing a PEAR package to handle Sqlite backups (both local backups and remote backups) and I am looking for some suggestions. Is the right way to proceed the db dump ? I assume that copying the db will result in a corrupted file if the db is used at the time of backup. The aim would be to dump or duplicate a whole database for a local or remote backup with the relevant integrity checks. Thanks in advance for your time and attention I remain, Very much obliged, Regards, David Costa PEAR- PHP Extensions and Application Repository Developer http://pear.php.net/user/gurugeek - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
