Re: Re[10]: [sqlite] Accessing Database on Network
On Wed, 2005-08-10 at 11:30 +0200, djm wrote: > Hello, > > > >> My understanding, after all of your helpful comments, currently is: > >> > >> Simultaneous reading of a server file by different clients is safe, as > >> long as the file doesnt get changed at all (at least in a time scale > >> where the Client Os could cache it). > > Mrs.> Remove your parenthesized exception and this is correct. > > My current plan is: > > Store the (readonly) database on the server and include version info > in a table in the database. Each time the client app starts, it > accesses the server database and checks the version info. Based on > this it decides whether it needs to copy the database file locally > (the server version is newer or there is no local version) or can use > the current local version (the version of the server database is the > same as the local version). So the server database never changes? How do new versions get published? [[ This is why I suggested a protocol with well known semantics. ]] > Its possible that more than one client will be accessing this version > info simulataneously. Can there be any problem? Using sqlite to read > this version info via a SELECT should map cleanly to a read only > access to the disk file, right? This is the only point where imnot > fully sure. According to the above should then be fine? Read only access isn't always "safe" in that all of ACID can be satisfied. MS-DFS for example. > The server database will need to be updated at some stage. The sys > admin will haye to be responsible for seeing that no client is > accessing the file while its being updated (by disconecting the server > from the network or doing the job at niight when all clients are off, > or just making sure that none are running my app or whtever). What > should I advise him is a necessary/reasonable procedure, and what are > the worst case scenarios for each appraoch? I've already told you what's safe. SQLite has a broad range of requirements that if you cannot lock down your requirements any better than "the client knows more about engineering than I do" and "i have no idea what operating system, networked filesystems and platforms will ever be used for this application" you're either absurdly future-proofing yourself, or you only think you are. NFS has well known and well defined semantics. So does SMB/CIFS. Their semantics can be made compatible with SQLite (using -osync and the urls I posted last). DFS, AFS and Bullet cannot be made safe with SQLite unless the filename has the version encoded in it and you use purely atomic file creation (rename). HTTP is probably the simplest. > As far as I can understand, the only thing that absolutely must be > ensured is that no app starts after the sys admin begins replacing the > file, and before he's finished, because then the client would > possibly get corrupt data. This could be ensured eg by > temporarily disconnecting the server from the network. Are you trying to remove race conditions or not? > Given this worst that could happen (which is extremely unlikely, but not > completely impossible), due to client os caching, would be that > upon restarting the clients then dont know that the serever database > is newer, and dont copy it locally it, and instead continue to work with the > old data (local version). ... > Does this sound like a good approach? No. > >> The same applies to sqlite accesses since it just uses regular files. > >> And when you only querying the database (not changing the data), you > >> are just reading the database file .. ie sqlite doesnt do anything > >> which would break the clients only reading condition. > >> > >> Am I right so far? > > Mrs.> Almost. > > Mrs.> You make a distinction between Client and Server and I don't > Mrs.> think you know who they are. > > Whats that supposed to mean? > > Mrs.> It's helpful to understand that the server cannot update files. Ever. > Mrs.> Only clients can update files. The server coordinates these updates. In > Mrs.> reality, in many cases, the server coordinates fine. In others, it fails > Mrs.> miserably. > > You can work with the server just as a normal machine. It happens to > be functioning as a network server for clients on the network. Eg. the > system admin can copy a database file from a cd onto the harddisk of the > server. The server updates the database here, not the client. No you cannot. That's the point. You only think you can. When you update a file (say) on CIFS, this could invalidate oplocks and force clients to redownload. Those notifications can reach clients at a different rate. Are you trying to remove race conditions? Or are you simply trying to make them harder to find? > >> The size of a file depends for example on the cluster size of the > >> underlying partition. And regardless is of little value in comparing > >> two files; the same file size/name doesnt mean two files are > >> identical. > > Mrs.> The size of a file does not
Re[10]: [sqlite] Accessing Database on Network
Hello, >> My understanding, after all of your helpful comments, currently is: >> >> Simultaneous reading of a server file by different clients is safe, as >> long as the file doesnt get changed at all (at least in a time scale >> where the Client Os could cache it). Mrs.> Remove your parenthesized exception and this is correct. My current plan is: Store the (readonly) database on the server and include version info in a table in the database. Each time the client app starts, it accesses the server database and checks the version info. Based on this it decides whether it needs to copy the database file locally (the server version is newer or there is no local version) or can use the current local version (the version of the server database is the same as the local version). Its possible that more than one client will be accessing this version info simulataneously. Can there be any problem? Using sqlite to read this version info via a SELECT should map cleanly to a read only access to the disk file, right? This is the only point where imnot fully sure. According to the above should then be fine? The server database will need to be updated at some stage. The sys admin will haye to be responsible for seeing that no client is accessing the file while its being updated (by disconecting the server from the network or doing the job at niight when all clients are off, or just making sure that none are running my app or whtever). What should I advise him is a necessary/reasonable procedure, and what are the worst case scenarios for each appraoch? As far as I can understand, the only thing that absolutely must be ensured is that no app starts after the sys admin begins replacing the file, and before he's finished, because then the client would possibly get corrupt data. This could be ensured eg by temporarily disconnecting the server from the network. Given this worst that could happen (which is extremely unlikely, but not completely impossible), due to client os caching, would be that upon restarting the clients then dont know that the serever database is newer, and dont copy it locally it, and instead continue to work with the old data (local version). Does this sound like a good approach? >> The same applies to sqlite accesses since it just uses regular files. >> And when you only querying the database (not changing the data), you >> are just reading the database file .. ie sqlite doesnt do anything >> which would break the clients only reading condition. >> >> Am I right so far? Mrs.> Almost. Mrs.> You make a distinction between Client and Server and I don't Mrs.> think you know who they are. Whats that supposed to mean? Mrs.> It's helpful to understand that the server cannot update files. Ever. Mrs.> Only clients can update files. The server coordinates these updates. In Mrs.> reality, in many cases, the server coordinates fine. In others, it fails Mrs.> miserably. You can work with the server just as a normal machine. It happens to be functioning as a network server for clients on the network. Eg. the system admin can copy a database file from a cd onto the harddisk of the server. The server updates the database here, not the client. >> The size of a file depends for example on the cluster size of the >> underlying partition. And regardless is of little value in comparing >> two files; the same file size/name doesnt mean two files are >> identical. Mrs.> The size of a file does not depend on the cluster size of the underlying Mrs.> partition. That's how much SPACE the file takes up. Mrs.> Whether or not your filesystem records the size of a file isn't Mrs.> important. It is. Its all that matters here. Mrs.> The concept of file size exists, and it has nothing to do Mrs.> with clusters or blocks or extents... almost all Mrs.> filesystems...correct One wants to reliably determine this invariant size and not the space occupied on disk or some other measure reported which depends on cluster size or whatever. "Almost" or "roughhly" is not reliable. Mrs.> The test is not to determine whether or not the files are identical, It is. Mrs.> but if one has been changed. This method will certainly download Mrs.> files when they haven't been changed (although it's unlikely). Mrs.> This unusual case wastes some bandwidth. In contrast to Mrs.> downloading it all the time, where you always waste bandwidth. If you cant tell when files are identical, you cant tell when you dont need to download, so you need to download always, so you needent bother checking the filesize in the first place. Mrs.> If you're looking for cross-platform you need to select protocols that Mrs.> have well defined semantics. HTTP sounds like a good bet. Full file copy Mrs.> sounds like a better one. My app will access the files on the network (LAN) as if theyre local. -- Best regards, djmmailto:[EMAIL PROTECTED]
Re: Re[8]: [sqlite] Accessing Database on Network
On Wed, 2005-08-10 at 00:30 +0200, djm wrote: > Hello, > > Henry> No. It might be on your particular systems, but we cannot make > Henry> a general case. It is possible that it will always be safe on > Henry> (for example) Windows 2000, while not safe on NT or XP! It > Henry> depends on how the OS handles caching. They are supposed to > Henry> check on each file access to see if the file changed, but > Henry> networks are slow, so they might not. Thus you could start > Henry> reading data that is in the cache, which is out of date. > > My understanding, after all of your helpful comments, currently is: > > Simultaneous reading of a server file by different clients is safe, as > long as the file doesnt get changed at all (at least in a time scale > where the Client Os could cache it). Remove your parenthesized exception and this is correct. > Updaing the file might cause > problems becuase of caching on the Client Os side, no matter how > cleanly and isolated this update was done (eg all clients off, and > update on server machine). (Im not sure I fully understand what terms > like "atomic and "durable" mean in this context but I presume Ive > gotten the jist?) This is correct. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/857d145c-710d-4097-8ed6-df11e8d52228.asp You should really know what atomic and durable mean. > The same applies to sqlite accesses since it just uses regular files. > And when you only querying the database (not changing the data), you > are just reading the database file .. ie sqlite doesnt do anything > which would break the clients only reading condition. > > Am I right so far? Almost. You make a distinction between Client and Server and I don't think you know who they are. It's helpful to understand that the server cannot update files. Ever. Only clients can update files. The server coordinates these updates. In reality, in many cases, the server coordinates fine. In others, it fails miserably. The file cannot be updated by anything but a server. If you rename() it on the "local machine" you are doing that rename as a client- at least as far as all of these statements about networked/distributed filesystems being unsafe are. > >> 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. > > Mrs.> Wrong. The sqlite database is binary. Any size check is going to > Mrs.> be in 8-bit bytes and will always be the same- except for > Mrs.> platforms that SQLite doesn't run on- any comparison with a > Mrs.> system that doesn't have 8-bit bytes will have to be aware of > Mrs.> that fact. > > The size of a file depends for example on the cluster size of the > underlying partition. And regardless is of little value in comparing > two files; the same file size/name doesnt mean two files are > identical. The size of a file does not depend on the cluster size of the underlying partition. That's how much SPACE the file takes up. Whether or not your filesystem records the size of a file isn't important. The concept of file size exists, and it has nothing to do with clusters or blocks or extents. As a matter of fact, almost all filesystems record the correct file size. CP/M is a notable filesystem that does not. AFAIK, SQLite won't run in CP/M so this is moot. The test is not to determine whether or not the files are identical, but if one has been changed. This method will certainly download files when they haven't been changed (although it's unlikely). This unusual case wastes some bandwidth. In contrast to downloading it all the time, where you always waste bandwidth. > Mrs.> You still haven't said what platform you're developing for. > > Windown in particular, but also others. http://support.microsoft.com/kb/q163401/ http://support.microsoft.com/kb/q148367/ http://www.winguides.com/registry/display.php/1295/ WARNING: in this mode, SQLite will be much much slower than your earlier tests would indicate. others networked filesystems have other semantics. I'm not going to even try to list them all. > Mrs.> djm: You still haven't told us exactly what you're using. > > Im currently using C++ (Qt) but also plan to use php with sqlite for > other projjects. In either cases the app should be cross platform. If you're looking for cross-platform you need to select protocols that have well defined semantics. HTTP sounds like a good bet. Full file copy sounds like a better one.
Re[8]: [sqlite] Accessing Database on Network
Hello, Henry> No. It might be on your particular systems, but we cannot make Henry> a general case. It is possible that it will always be safe on Henry> (for example) Windows 2000, while not safe on NT or XP! It Henry> depends on how the OS handles caching. They are supposed to Henry> check on each file access to see if the file changed, but Henry> networks are slow, so they might not. Thus you could start Henry> reading data that is in the cache, which is out of date. My understanding, after all of your helpful comments, currently is: Simultaneous reading of a server file by different clients is safe, as long as the file doesnt get changed at all (at least in a time scale where the Client Os could cache it). Updaing the file might cause problems becuase of caching on the Client Os side, no matter how cleanly and isolated this update was done (eg all clients off, and update on server machine). (Im not sure I fully understand what terms like "atomic and "durable" mean in this context but I presume Ive gotten the jist?) The same applies to sqlite accesses since it just uses regular files. And when you only querying the database (not changing the data), you are just reading the database file .. ie sqlite doesnt do anything which would break the clients only reading condition. Am I right so far? >> 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. Mrs.> Wrong. The sqlite database is binary. Any size check is going to Mrs.> be in 8-bit bytes and will always be the same- except for Mrs.> platforms that SQLite doesn't run on- any comparison with a Mrs.> system that doesn't have 8-bit bytes will have to be aware of Mrs.> that fact. The size of a file depends for example on the cluster size of the underlying partition. And regardless is of little value in comparing two files; the same file size/name doesnt mean two files are identical. Mrs.> You still haven't said what platform you're developing for. Windown in particular, but also others. Mrs.> djm: You still haven't told us exactly what you're using. Im currently using C++ (Qt) but also plan to use php with sqlite for other projjects. In either cases the app should be cross platform. Best regards, djmmailto:[EMAIL PROTECTED]
Re: Re[6]: [sqlite] Accessing Database on Network
On Wed, 2005-08-03 at 08:46 -0500, Henry Miller wrote: > >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. NTP isn't relevant. Set the mtime to whatever you saw on the server using wstat() or utime() or what have you. Don't bother trying "to get close".
Re: Re[6]: [sqlite] Accessing Database on Network
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.
Re: Re[6]: [sqlite] Accessing Database on Network
On Wed, 2005-08-03 at 10:34 +0200, djm wrote: > Hello, > > 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? The answer is "sometimes not". Sometimes, it's even "often not". It really depends on your situation. It depends on what networked filesystem you're using, and what operating system you're using. In the case of UNIX and AFS- if you have all clients turned off - or you do it during AFS replication, then yes. > > 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? It depends on what you mean by "no client is reading." If you really mean the machines are turned off [and have no cache to upload], then maybe. If you mean that AND you're doing the update "on the server" (instead of by using another node/client/workstation) then the answer is "probably". > > If you're willing to do that, why not download the database > > periodically? Why not store the database via HTTP or otherwise check > > the mtime of it, and download it anew if the size/mtime doesn't > > match? > > > This'll be a lot safer AND provides a mechanism by which corrupt > > databases can otherwise be transparently updated. > > Downloading a copy of the database is indeed is indeed a good > suggestion, but it doesnt change my original question. > > 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. Wrong. The sqlite database is binary. Any size check is going to be in 8-bit bytes and will always be the same- except for platforms that SQLite doesn't run on- any comparison with a system that doesn't have 8-bit bytes will have to be aware of that fact. Changing the timestamp COULD be enough to have clients interrogate it- for example, perform some rsync operations on the file, or check in a prearranged place for a generation number. Either store the version/generation number in a separate file or hack the database to store it in the header. IIRC there are a few unused bytes there. Your OS might have other freshness checks (unix, for example, can use inodes if you always rename()), but even if they don't, there are other mechanisms: * Encoding the generation number/version into the filename * Update some EA (attribute) * Update the file's mtime to some value THAT IT WASN'T before (by recording all used mtimes) You still haven't said what platform you're developing for. > So the question is still is it always safe if 2 clients are > simultaneously reading this version info from the database (or for > that matter, but its not sqlite specific, if 2 clients are > accessing/running the executable file) ? The answer is still one of "usually", "probably" or "maybe". By using the methods I suggest the answer is "yes". > > 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. And as I said already I > wasnt trying to be rude, and Im sorry if it seemed so. But if you > insist on interpreting it as such I suppose I cant stop you. I'm showing you how I read your original post. Is this critically different than what you're saying? If so, how?
Re[6]: [sqlite] Accessing Database on Network
Hello, 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? > 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? > If you're willing to do that, why not download the database > periodically? Why not store the database via HTTP or otherwise check > the mtime of it, and download it anew if the size/mtime doesn't > match? > This'll be a lot safer AND provides a mechanism by which corrupt > databases can otherwise be transparently updated. Downloading a copy of the database is indeed is indeed a good suggestion, but it doesnt change my original question. 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. So the question is still is it always safe if 2 clients are simultaneously reading this version info from the database (or for that matter, but its not sqlite specific, if 2 clients are accessing/running the executable file) ? > 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. And as I said already I wasnt trying to be rude, and Im sorry if it seemed so. But if you insist on interpreting it as such I suppose I cant stop you.
Re: Re[4]: [sqlite] Accessing Database on Network
On Tue, 2005-08-02 at 17:36 +0200, djm wrote: > Hello, > > Thanks very much for the detailed and helpful response. Ill certainly > have a look at the book you receommended.. thanks for the tip. > > Sunday, July 31, 2005, 4:47:11 PM, you wrote: > > > As a result, any piece of software that relies on these semantics is > > bound to be surprised. > > But in this case the piece of sofware is sqlite, and Im not familiar > enough with its locking and other interanals to know exactly how safe > it is to do what. As far as I can tell its just reading and writing > sql commands that are of primary interest, and its up to sqlite to > make this as safe as it can, and to state under which conditions it > cant. The information on the website was pretty vague. It shouldnt > really be a prerequisite to be intimitely familiar with various > operating systems and/or filesystems to use sqlite or another > database, on a network to use sqlite effectively (not that such > knowledge wouldnt be very desirable, and I will read the book you > recommended). I disagree with that: see below. > > As a classical example: AFS deals with this problem by copying the file > > locally (completely!) before allowing the client to read it. Once the > > last client closes it, any changes are pushed up blindly. An AFS enabled > > SQLite is bound to produce corrupt databases if there is more than one > > writer (!) > > What do you mean "pushed up blindly". I mean that AFS will actually upload the file overwriting anything else there. > > NFS caches blocks together before sending them. This violates the > > single-byte atomicity of UNIX's own filesystem. As a result, no amount > > of journalling will help- but at least rename() is still atomic. So with > > NFS, one should write the new database - completely from scratch, and > > rename it over the original. Clients should NEVER update a shared file > > on NFS. Ever. > > How is that different to the above? Isnt the local copy above copied > over the original on the server? No. The cache isn't write through in the case of NFS (with -osync), and the lock rpc causes the read-cache to be invalidated anyway. > > .. you really shouldn't expect any writers to function on SQLite in > > a reliable manner... > > Fair enough. I had presumed this alerady. However is is always 100% > safe to do simultaneous reads (with all sqlite versions (3 and 2) and > on all os's it runs on?) And does what one reasonybly presumes is a > read (non write) in terms of sql commands (something that shouldnt > change the data in the database) always translate to a open file in > read_only mode for the os? 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). If the file is never written to, then why is it on the network (besides for distribution)? > >> > If there's never any writers, why bother keeping it on the network? > >> > >> I do have reasons, most of which are based on particular customer > >> requirenments. > > > Customers never say "the database has to be stored on the network so > > it's slower." > > > They might put something else there- and that's my question: what is the > > exact requirement that makes you want to keep it on the network _IF_ > > there aren't any writers? > > My customer wants the app instaled cleanly on the network (server) and > all clients to be oblivious to any updates app or database (presuming > theyre not unning the app while its being updated). The app should > also work for the other customers just running it locally. 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(). If you're willing to do that, why not download the database periodically? Why not store the database via HTTP or otherwise check the mtime of it, and download it anew if the size/mtime doesn't match? This'll be a lot safer AND provides a mechanism by which corrupt databases can otherwise be transparently updated. > > And don't just say "I do have reasons" when you're asking a question. > > It's really rude: > > > http://www.catb.org/~esr/faqs/smart-questions.html#id3002514 > > Sorry if I sounded so but I didnt mean to be rude. I just thought that > the motivation behind my question would be wasting bandwidth, since > the question was valid (and in my opinion interesting) whetever the > motivation. However I didnt ask "how to use x to do y". I asked "can I > do x" and you asked "why dont I do y instead". And I said that Id > still like to know even out of curiouity if its safe to do x ;-) you said "I need to access a database on the network (x), [how do I] do this safely (y)". The answer is "it's almost never safe" but nobody wants a short answer- they want to know why- with
Re: [sqlite] Accessing Database on Network
something like sql server is likely better for the task. sqlite can lag quite a bit when it needs to get file locks over the network to stay atomic. On 7/28/05, djm <[EMAIL PROTECTED]> wrote: > Hello, > > The documentation suggests that its unsafe to use SQLite when the > database file is on a windows network server and various other > machines may want to simultaneously access it. If however none of > these machines change the data in the databse (all accesses are just > queries) is it then completely safe? > > Presumably only querying the database (and not inserting/altering etc) > translates internally in sqlite to opening the database in read_only > mode, which is presumably safe to do in several clients concurrently? > If so from what version of windows/sqlite can I be confident? > > > -- > Best regards, > djm mailto:[EMAIL PROTECTED] > > > -- Cory Nelson http://www.int64.org
Re[2]: [sqlite] Accessing Database on Network
Hello, > Understanding what underlying operations aren't safe, is helpful. Im not sure what this should mean. Can you please elaborate. > If there's never any writers, why bother keeping it on the network? I do have reasons, most of which are based on particular customer requirenments. > Have your program "check the network server" for a newer version of the > file and copy it down if necessary. > This will reduce network traffic because you won't be checking for locks > every query... All good suggestions. Notwithstanding, Id still like very much to know the answer to the question if there's anybody reading who knows it? Thanks ion Advance. -- Best regards, djmmailto:[EMAIL PROTECTED]
Re: [sqlite] Accessing Database on Network
On Thu, 2005-07-28 at 11:28 +0200, djm wrote: > Hello, > > The documentation suggests that its unsafe to use SQLite when the > database file is on a windows network server and various other > machines may want to simultaneously access it. If however none of > these machines change the data in the databse (all accesses are just > queries) is it then completely safe? > > Presumably only querying the database (and not inserting/altering etc) > translates internally in sqlite to opening the database in read_only > mode, which is presumably safe to do in several clients concurrently? > If so from what version of windows/sqlite can I be confident? Understanding what underlying operations aren't safe, is helpful. If there's never any writers, why bother keeping it on the network? Have your program "check the network server" for a newer version of the file and copy it down if necessary. This will reduce network traffic because you won't be checking for locks every query...
Re: [sqlite] Accessing Database on Network
On Jul 30, 2005, at 8:22 AM, djm wrote: Nobody? Hello, The documentation suggests that its unsafe to use SQLite when the database file is on a windows network server and various other machines may want to simultaneously access it. If however none of these machines change the data in the databse (all accesses are just queries) is it then completely safe? Presumably only querying the database (and not inserting/altering etc) translates internally in sqlite to opening the database in read_only mode, which is presumably safe to do in several clients concurrently? If so from what version of windows/sqlite can I be confident? others may weigh in with a more knowledgeable response, however, as far as I can see, if you are only reading from a db, it should be safe no matter what conditions you use it in. -- Puneet Kishor
Re: [sqlite] Accessing Database on Network
Nobody? > Hello, > The documentation suggests that its unsafe to use SQLite when the > database file is on a windows network server and various other > machines may want to simultaneously access it. If however none of > these machines change the data in the databse (all accesses are just > queries) is it then completely safe? > Presumably only querying the database (and not inserting/altering etc) > translates internally in sqlite to opening the database in read_only > mode, which is presumably safe to do in several clients concurrently? > If so from what version of windows/sqlite can I be confident?
[sqlite] Accessing Database on Network
Hello, The documentation suggests that its unsafe to use SQLite when the database file is on a windows network server and various other machines may want to simultaneously access it. If however none of these machines change the data in the databse (all accesses are just queries) is it then completely safe? Presumably only querying the database (and not inserting/altering etc) translates internally in sqlite to opening the database in read_only mode, which is presumably safe to do in several clients concurrently? If so from what version of windows/sqlite can I be confident? -- Best regards, djm mailto:[EMAIL PROTECTED]
