Re: [sqlite] Handling move/rename of an SQLite database file
On 11/09/2013 06:47 PM, Simon Slavin wrote: On 9 Nov 2013, at 10:07am, Dan Kennedy wrote: On 11/09/2013 04:58 PM, L. Wood wrote: Richard Hipp wrote: Rollback journal files might be closed and reopened. [snip] [snip]* But what about the next open() of a new journal file? Will it use the new location of the database file or the old one? The old one. When it starts up, SQLite calculates the full paths to the database and journal files and stores them in memory. So if the journal file is opened, moved, closed and then reopened, it will be the original location that is opened both times. Given that the journal file is closed-and-reopened only between transactions, if SQLite is unable to reopen the journal file (because it was moved/deleted) then it will either create a new one or return an error, right ? No corruption of the database will result. The problem is that you end up with the database and journal files in separate directories. Which means that if an application or power failure occurs during a subsequent transaction, the next client to open the db will be unable to find the journal file to roll it back. If the failure occurred after the original process started writing to the database, this means the database file will likely be left in a corrupt state. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On 9 Nov 2013, at 10:07am, Dan Kennedy wrote: > On 11/09/2013 04:58 PM, L. Wood wrote: >>> Richard Hipp wrote: >>> Rollback journal files might be closed and reopened. [snip] >> >> [snip]* But what about the next open() of a new journal file? Will it use >> the new location of the database file or the old one? > > The old one. > > When it starts up, SQLite calculates the full paths > to the database and journal files and stores them > in memory. So if the journal file is opened, moved, > closed and then reopened, it will be the original > location that is opened both times. Given that the journal file is closed-and-reopened only between transactions, if SQLite is unable to reopen the journal file (because it was moved/deleted) then it will either create a new one or return an error, right ? No corruption of the database will result. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On 11/09/2013 04:58 PM, L. Wood wrote: Richard Hipp wrote: Rollback journal files might be closed and reopened. But the main database file is opened once and held open until sqlite3_close() (or DETACH if the file was originally opened using ATTACH). Thanks for this information. But suppose, while a journal file is open, it is moved (along with the main database file) to a new directory. * The next close() of the journal will work just fine, I assume? * But what about the next open() of a new journal file? Will it use the new location of the database file or the old one? The old one. When it starts up, SQLite calculates the full paths to the database and journal files and stores them in memory. So if the journal file is opened, moved, closed and then reopened, it will be the original location that is opened both times. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
> Richard Hipp wrote: > Rollback journal files might be closed and reopened. But the main database > file is opened once and held open until sqlite3_close() (or DETACH if the > file was originally opened using ATTACH). Thanks for this information. But suppose, while a journal file is open, it is moved (along with the main database file) to a new directory. * The next close() of the journal will work just fine, I assume? * But what about the next open() of a new journal file? Will it use the new location of the database file or the old one? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On 7 Nov 2013, at 6:03pm, L. Wood wrote: > I did a simple experiment and got an error from sqlite3_step() after renaming > the file. This is what I did: > > After the call to sqlite3_open(), I halt/sleep my program for 10 seconds to > give me ample time to rename the file. I rename the file. The subsequent > sqlite3_prepare_v2() works without errors. However, the sqlite3_step() *does* > give an error: Error code 10, "disk I/O error". This does not happen if I do > not rename the file. > > Am I missing something? SQLite's _open() function doesn't actually open the file. It sets things up so that the first proper operation on the file can open it, when it needs to. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
> D. Richard Hipp wrote: > No errors are returned. SQLite never notices that the database file has > been renamed. I did a simple experiment and got an error from sqlite3_step() after renaming the file. This is what I did: After the call to sqlite3_open(), I halt/sleep my program for 10 seconds to give me ample time to rename the file. I rename the file. The subsequent sqlite3_prepare_v2() works without errors. However, the sqlite3_step() *does* give an error: Error code 10, "disk I/O error". This does not happen if I do not rename the file. Am I missing something? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On 7 Nov 2013, at 5:38pm, Richard Hipp wrote: > Rollback journal files might be closed and reopened. Okay. That gives us more of the answer to the original question. Wood, this is the only file that SQLite would care about if moved. And technically you could use NSNotification to tell you if someone moved a journal file while SQLite was using it. But in practise, under both OS X and iOS, it doesn't matter. Losing that file between transactions isn't going to corrupt your database and if someone does move or delete it at that point the best thing your app could do is crash, so the user knows not to do that. So yes, OS X does give you a mechanism for keeping track of files being moved. But there's not much point in using it with a SQLite database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On Thu, Nov 7, 2013 at 12:04 PM, Simon Slavin wrote: > > Assuming that the user is using OS X, use of standard VFS, access on a > local disk, no ATTACH, no Jedi mind tricks ... > > Does SQLite ever close and reopen database or journal files between > _open() and _close() ? > Rollback journal files might be closed and reopened. But the main database file is opened once and held open until sqlite3_close() (or DETACH if the file was originally opened using ATTACH). -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On 7 Nov 2013, at 3:58pm, Richard Hipp wrote: > On Thu, Nov 7, 2013 at 10:31 AM, L. Wood wrote: > >> QUESTION: What error codes can each of the following functions possibly >> return while the database file is renamed/moved during the execution of the >> function? > > No errors are returned. SQLite never notices that the database file has > been renamed. > > In Unix, once a file is opened, the process only deals with the file > descriptor. The underlying filename can change or even deleted (unlinked) > and the application will never know. Richard (or any of the team), Assuming that the user is using OS X, use of standard VFS, access on a local disk, no ATTACH, no Jedi mind tricks ... Does SQLite ever close and reopen database or journal files between _open() and _close() ? An example of a time I might expect this would be during a VACUUM, or as a transaction finishes while journal_mode = DELETE. My guess is that if SQLite closes one of these files it creates a new one with the name and path it already knows, but I wonder if it might close and reopen one of these files. Of course, if it closes a file you've moved, then tries to delete it, it won't be able to delete it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On Thu, Nov 7, 2013 at 4:31 PM, L. Wood wrote: > Users could rename/move a database file while my (Mac OS X) program has made > an SQLite connection to the file. I want to handle this properly. One thing you have to be particularly careful about is the extra files sqlite creates next to the database file, such as the rollback journal files. The page http://sqlite.org/tempfiles.html documents how sqlite currently uses these. In particular, if sqlite finds the database file but does not file the associated journal file, your database can go corrupted. I don't know how you could assure that this does not happen. Instead of trying to handle errors from the frontend of sqlite, you might get a more robust solution if you wrote a custom VFS backend for sqlite, possibly by modifying the existing uniq VSF backend. The VFS is documented on http://sqlite.org/vfs.html . Sqlite performs all operating system dependent functions through the replacable VFS backend. These operations include opening a file, reading, writing, file locking. This way if, for example, if you detect that a user have moved the database file and sqlite wants to open the corresponding rollback journal file, you can make sure that it opens the correct rollback journal. Besides ensuring that there the extra files are found correctly, you will also have to make sure that file locking works correctly accross moves. All this gets quite complicated (maybe even impossible), especially if multiple programs want to open the same sqlite database concurrently. Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
On Thu, Nov 7, 2013 at 10:31 AM, L. Wood wrote: > > QUESTION: What error codes can each of the following functions possibly > return while the database file is renamed/moved during the execution of the > function? > No errors are returned. SQLite never notices that the database file has been renamed. In Unix, once a file is opened, the process only deals with the file descriptor. The underlying filename can change or even deleted (unlinked) and the application will never know. Note that renaming or unlinking a database file while it is being written, then killing the writer process or turning the power off, can corrupt the database file since it leave the associated journal file with a different name. See http://www.sqlite.org/howtocorrupt.html#unlink for additional information. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling move/rename of an SQLite database file
Users could rename/move a database file while my (Mac OS X) program has made an SQLite connection to the file. I want to handle this properly. Mac OS X (Unix) has ways to track a file that is renamed/moved, so if I can get enough information about how SQLite works in this regard, handling this should be possible. Below is a question that I believe I need answered to be able handle this properly, but feel free to add more thoughts of your own if you think I need more information. QUESTION: What error codes can each of the following functions possibly return while the database file is renamed/moved during the execution of the function? * sqlite3_open() * sqlite3_prepare_v2() * sqlite3_step() * The various "sqlite3_column()" functions - could they return something unexpected? * sqlite3_finalize() * sqlite3_close() My thinking is that if I knew all the possible error codes, I could - upon such an error - check if the file was renamed/moved. If it was, I could close and reopen the database connection on the new path and continue/restart execution as required. If it was not renamed/moved, it would be a genuine error to be handled like any other error. (And of course, I would perform the renamed/moved check in between the SQLite function calls, but that's not related to SQLite per se.) Again, you're welcome to add your own thoughts to the mix. Thank you. I'm using SQLite 3.8.1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
