On 8/3/2005 at 10:34 djm wrote:
>Wednesday, August 3, 2005, 4:41:24 AM, you wrote: > >> No, none of those things are guaranteed. If there's even a single >> writer it can be unsafe (consider AFS putting a corrupt journal up >> that clients notice download, and thrash their local copy). > >But Im saying there -wont- be a single writer (except the rare update >process on the server, during which all other clients wont be >reading), and the question is it it always safe then? No. It might be on your particular systems, but we cannot make a general case. It is possible that it will always be safe on (for example) Windows 2000, while not safe on NT or XP! It depends on how the OS handles caching. They are supposed to check on each file access to see if the file changed, but networks are slow, so they might not. Thus you could start reading data that is in the cache, which is out of date. You may get different results from AFS, NFS, Coda, Samba, or Windows networking. Not to mention different versions of each protocol, and implementation. (Windows file sharing uses the same protocol as Samba, but that doesn't mean they handle caching the same!) >> Okay. that's what's important. That update procedure is completely >> unsafe UNLESS you can guarantee that the sqlite database will be >> overwritten atomically. Most operating systems don't have such an >> operation- the closest thing being rename(). > >And this is presumably only important if another client is reading the >file while its being updated. If no client is reading the file on the >server during update (and none are writing anyways), then the os and >the filesystem should be irrelevant, right? No, because we don't know what caching the OS is doing. It is possible for the remote OS to not look at the server at all if it still remembers the part of the file you are asking for after the write! Even writing your own file system may not work if the OS is caching things above the file system. That said, most OSes get this right these days. So if you correctly shut down all your readers, then to a write, then start them back up again, you might consider the problems rare enough to ignore. In fact they may never happen in practice, but in theory they could. What will work is to replace all the places where sqlite reads the file to instead talk to some server on the network you write. This is the same as writing a network file system, but you control ALL the layers, so you know what caching is going on. This is a lot of work to get right, and generally a poor use of time, but it is an option. >In my opinion system time stamps etc are not a reliable means of >comparing 2 files. Many things can change the timestamp of a file, >without changing the contents, and one (server) os/filesystem can >report a different file size to another (local) for the same file >(contents). As I said already, I think having a version number >embedded in the databse itself is much more relible. You should be running NTP (network time protocol) on all computers. This will keep all your system times to within milliseconds. Unix systems keep the last modified times tamp separately. Microsoft Windows sets (resets? I can never remember) the archive bit, which could be abused to tell you when a file is modified - at the cost of breaking backups so I can't recommend it. >> you said "I need to access a database on the network (x), [how do I] >> do this safely (y)". > > I didnt. Youre rephrasing my post, to suit your means, and I think > the point is degenerating in the process. Not really. We are just reading between the lines. Your questions look suspiciously like his rephrasing. Don't be offended if we are answering the question we think you should have asked, not the one you did. The correct answer to your question you should ask is to use something like postgresql/Oracle/(I can think of half a dozen other choices). Sqlite was not designed for what you want to do. Now there may be good reasons you cannot use a different database. That is a whole different issue. We cannot make decisions for you, but we can point out what looks like a bad decision from the parts of the problem we know (we know that we don't know all your issues). Bottom line: what you want to do will probably work without problem. However if you want a strong statement you need to have your lawyers contact your OS vender(s) and get a contract in writing that they will guarantee that this will work. Salesmen will lie, and the documentation may not account for the latest changes, but when you have a contract in writing you can at least get relief if things do not work. Asking management for this forces them to place a value on data if nothing else, which may help drive the decision on what to do.
