[sqlite] Newbie --question about multiple PCs accessing sqlite
I'm contemplating using the TCL binding of sqlite, but the database is to be located in a shared folder and about 100 users will be accessing it for read/write purposes. I would like to take some time to learn as much as possible to safeguard the database from getting corrupt due to several users accessing it at the same time. However, the sqlite website "http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite" says: “A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.“ I have 3 questions: 1.From the above statement, Am I right to assume that sqlite is not for me? 2.If sqlite is feasible for my purpose, where can I find good information to avoid ending up with a corrupt database 3.The TCL binding I downloaded says version 2.0 and I do not have a C++ compiler, where can I get the most recent binary of TCL sqlite? Thanks. The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of , or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] runtime detect of THREADSAFE
Stefano Barbato wrote: is there a way to detect at runtime if the sqlite library has been compiled with the THREADSAFE=1 flag or not? No, or not that I know of. It would be nice for ver 3 to have a simple function which returns a structure of the database library capabilities. The obvious ones which come to mind: ThreadSafe, MaxRowSize, Encoding, Version. Reason for the latter two is cos not all dev environments (Delphi) support "extern" data in a dll. Regards P. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Newbie --question about multiple PCs accessing sqlite
> I'm contemplating using the TCL binding of sqlite, but the database is to > be located in a shared folder and about 100 users will be accessing it for > read/write purposes. I would like to take some time to learn as much as > possible to safeguard the database from getting corrupt due to several > users accessing it at the same time. However, the sqlite website > "http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite" says: > > “A good rule of thumb is that you should avoid using SQLite in situations > where the same database will be accessed simultaneously from many computers > over a network filesystem.“ > > > I have 3 questions: > > 1.From the above statement, Am I right to assume that sqlite is not for > me? Probably not unless you intend on implementing some application level write locking on the SQLite database. I've done this with one application but there probably aren't ever anywhere near 200 people accessing it at the same time. If someone is writing the database it waits a few milliseconds and tries again (up to a finite number of times, at which time it reports a failure).. With that many possible concurrent users you might look into a RDBMS server like PostgreSQL or MySQL. My personal preference is PostgreSQL, but many use MySQL too.. > 2.If sqlite is feasible for my purpose, where can I find good > information to avoid ending up with a corrupt database SQLite locks the *whole* database when writing. You simply cannot write more than one thing at a time to the database (INSERT, UPDATE, DELETE etc). I don't think there is any documentation that says anything more than that. I'm not sure if doing so will corrupt the data or just plain fail, but either way you can't do it :-) > 3.The TCL binding I downloaded says version 2.0 and I do not have a C++ > compiler, where can I get the most recent binary of TCL sqlite? > Thanks. No idea on this one, check the SQLite webpage.. -- - Mitchell Vincent - kBilling - http://www.k-billing.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] runtime detect of THREADSAFE
Hi, I already tried to find the aswer on the faq/wiki/mailing list archive/google with no luck so here is my question: is there a way to detect at runtime if the sqlite library has been compiled with the THREADSAFE=1 flag or not? I need thread-safeness turned on and I would like to test for it during ./configure checks. thanks a lot. stefano - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Re: Life of a cursor
Hello again, All these answers are client/server related and very informative, but the original question is still in the open. In SQLite's case, let's assume I want to make a wrapper which add some functionality and simplifies some features. I create a cursor which contains a copy of the data. If I close the database, is it the norm to expect the results to be still available? Some developers may want the cursor to be available, other may want to invalidate them... Should I make it optional? Thanks again, -- Tito On 19 may 2004, at 10:12, Fred Williams wrote: What George described is exactly how client-server is implemented using Borland's development products. The result set is "cached" locally on the client. Any updates performed on the client data set must be "applied" to the server. All aspects of the entire process are controlled by the developer. Normally, an update cycle would be: Select the data set, close the database, update the local data set, open the database, apply updates with error handling, close the database. All opening and closing of the database is optional based on the environment and design constraints. As far as I know that is the basic "industry standard" design for Client-Server. Fred -Original Message- From: Tito Ciuro [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 19, 2004 1:35 AM To: George Ionescu Cc: Sqlite Forum Subject: Re: [sqlite] Re: Life of a cursor Hello George, After that, it's of no use to keep the database open, so it closes it. And sends the results obtained back to the client. Wow! This is the first I hear that. Wouldn't that be an expensive operation? (opening, closing, reopening...) ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Re: Life of a cursor
What George described is exactly how client-server is implemented using Borland's development products. The result set is "cached" locally on the client. Any updates performed on the client data set must be "applied" to the server. All aspects of the entire process are controlled by the developer. Normally, an update cycle would be: Select the data set, close the database, update the local data set, open the database, apply updates with error handling, close the database. All opening and closing of the database is optional based on the environment and design constraints. As far as I know that is the basic "industry standard" design for Client-Server. Fred > -Original Message- > From: Tito Ciuro [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 19, 2004 1:35 AM > To: George Ionescu > Cc: Sqlite Forum > Subject: Re: [sqlite] Re: Life of a cursor > > > Hello George, > > > After that, it's of no use to keep the database open, so it closes it. > > And sends the results obtained back to the client. > > Wow! This is the first I hear that. Wouldn't that be an expensive > operation? (opening, closing, reopening...) > ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Re: Life of a cursor
> > After that, it's of no use to keep the database open, so it closes it. > > And sends the results obtained back to the client. > > Wow! This is the first I hear that. Wouldn't that be an expensive > operation? (opening, closing, reopening...) For an embedded database, such as SQLite, yes it would be an expensive, and unnecessary, operation. The open/use/close method is well suited for a client/server engine, however. Many client/server RDBMS engines, such as MS SQL, and I'm pretty sure Oracle, use so-called "connection pooling", where the connections on the server aren't actually deallocated when the client "closes" them, but are instead maintained in a pool on the server. The next client that opens a connection may get a new one, or it may get a reference to a previously used one. If your application holds a connection open even if it has no further use for it, or won't need it for some extended period of time, then that connection's resources aren't available for some other application, which means the server has to allocate more resources for a new one, using up time and memory on the server. The server side resources for the connection being held open aren't actually being used for anything, so they are wasted. Imagine every application that connects to the server doing that. The theory is that allocating and deallocating the resources used by a connection object on the server are actually more time consuming than simply reinitializing the resources associated with an existing object, so not having to create a new one *every* time a client connects saves time on the server and makes the applications that much scaleable. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]