Two questions; - Outside of SQLite, how would you handle "better checking" if a file randomly moves stuff around when a transaction is happening? - Why would a user move a database file while the program is in use?
You can plan for every kind of problem, but you can't prevent every problem. Some even subscribe to "You can't plan for every problem because some dumb user will think of something new we smart developers haven't thought of yet". There is a cost/benefit ratio you have to ponder. The cost portion is consider to how much bloat your program has to deal with protecting itself from the user and your time invested to code the bloat, and then compare that value to "for what gain"? If the value of the data the user is working on is THAT precious to them, they shouldn't be playing with the file structure to begin with. I'm on the side of "You move files around, you deal with the consequences. The program and data lives where it is for a reason, so paws off.". The thing I've not had to consider in my coding life is the movement of files during program operations, and that reason is in the next paragraph. With any program I've written with SQLite, the VERY FIRST THING my code does is open a file handle to the main database file, and the VERY LAST THING my code does is close the database and release the file handle. I have a unit written SPECIFICALLY for handling SQLite file access. I open the database connection even before the GUI forms are even considered. Linux/EXT3+ handles file handles differently compared to Windows. I'm not a Linux developer (Unless you count BASH scripting) but I deal with the OS enough (My job for 6 years) to know enough low level stuff that at the file system level, programs attach to files at the INODE level, so files can be renamed, or moved, with a file handle open against it on the same device. Details such as file name, path, size, attributes, permissions, etc, etc, are all stored somewhere else but references the INODE. Giving a program a path to a file is only a "reference" and not an absolute as far as the OS/File System is concerned. Since the INODE never changes on a move or rename, file operations continue to happen against the contents of that file. You can even DELETE a file and file operations will continue usually uninterrupted while that file handle is open. Windows handles things so that if ANYTHING has their hand on it, NOTHING can be done to the "external container" itself, meaning, the file cannot be renamed and cannot be moved. I'm not sure if its the file system that blocks this kind of behavior and tells the OS "Nope, not gonna happen", or if its the OS that is checking if any handles are attached to the file in question, but this kind of blocking behavior has been around since early MS DOS days. Basically, with SQLite anyways, I think that if you have a file that is moved during a transaction, your results are going to fall under the "UNDEFINED" category. I've not looked at the code to see how the creation of the "-journal" files are done but I would suspect that SQLite internally holds the old path when the initial database file was created/opened, and just creates work files there. If that directory structure no longer exists and the -journal file has to be recreated, the program may fault out, or, the directory structure may be recreated on the fly. Lets consider the weights on your program and this particular situation; - the length of time a transaction is actively running - the time a user has to move a file during the transaction - the volume of the data - user expertise If the transaction time is short, then a user moving files during a transaction shouldn't be a concern. If the transaction is long, the volume of data is probably high, so users are probably busy waiting for things to happen or have other pressing concerns rather than random file management. If user expertise is high, user is probably not going to be moving files around anyways since they know the application is running and corruption could happen If user expertise is low, they shouldn't know how to move files around, or don't care to move files around, or wouldn't know what to do with said files. If you want a suggestion, before EVERY action on the database, validate that the database physically exists in the directory required. If it doesn't, "abort, fail, don't retry" as your user is monkeying with things they shouldn't be and its on them for the data corruption. On Mon, Nov 11, 2013 at 9:01 AM, L. Wood <lwoo...@live.com> wrote: > Here is my situation and why SQLite doesn't seem to satisfy it. > It would be great to hear what some of the SQLite developers think about > this. > > * My Mac OS X program has a document file format of packages (which are > nothing but glorified directories). My database file is always inside of a > package. So, it can be assumed that all the extra files (such as -journal) > will always move with the main database file if the user moves the document > (the package). > > * The program runs in sandbox mode (a requirement for all programs on > Apple's Mac App Store), meaning that if a user moves a package, the program > will gain read/write access to the new path but LOSE read/write access to > the old path. > > This poses a problem: > > * A user could move the file during a transaction, i.e., between > _prepare_v2(); and _finalize(); > > It seems that SQLite needs support for this situation. Would you agree? > > If so, the question is how. Maybe through callback functions requesting an > updated directory when needed? Or by internally following the changed path > of the main database file instead of storing it in memory at the outset? > > Until then, I guess I just have to live with the risk of either an error > or data corruption when a user moves a document (a package). > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
