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[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[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[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
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?
Re[2]: [sqlite] sqlite3_compile missing?
> I think you are looking at the sqlite2 documentation, not sqlite3! > sqlite_compile was changed to sqlite3_prepare in sqlite 3 (with very > different schematics, so readily closely) > sqlite3_step still exists, but it takes different arguments Thanks. Have it now. I had downloaded the page for offline reading, and therefore hadnt opened it via the link for the start page, which was the only mention of version 2.
[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]
[sqlite] sqlite3_compile missing?
Hello, It seems that the functions required for reading data without a callback are mising in SQLite 3? I cant find "sqlite3_compile", or "sqlite3_step". Am I missing something? The documentation on the website describes "sqlite_compile" and "sqlite_step" which were presumably menat to be "upgraded" to "3" versions but are now just missing. The main.c file also contains the comments "..The following routine destroys a virtual machine that is created by ** the sqlite3_compile() routine.." -- Best regards, djm mailto:[EMAIL PROTECTED]
