Re: [sqlite] Re: Request for comment: Proposed SQLite API changes
Eduardo <[EMAIL PROTECTED]> wrote: Isn't better lock the database while a transaction that can make a SQLITE_SCHEMA error, as is done with writes? A change in database is first sqlite3_step succeeds, an implicit transaction is started (I assume there are no explicit transactions in effect), so the schema can no longer change unexpectedly. Well, the write was an example. So, a lock_schema wouldn't do the work at the prepare phase? The schema begins locked and when a transaction needs to do a change, sends a signal to gain exclusive, unlock, make the changes and lock it again. Don't know how many cpu cycles can this take but in a heavy scenario it may be less than re-prepare, in some cases reparse, the other threaded transactions. Must add that doing this way you don't need to modify the API. #The Unix Guru's View of Sex unzip ; strip ; touch ; grep ; finger ; mount ; fsck ; more ; yes ; umount ; sleep
Re: [sqlite] Re: Request for comment: Proposed SQLite API changes
At 14:27 06/11/2005, you wrote: Eduardo <[EMAIL PROTECTED]> wrote: Isn't better lock the database while a transaction that can make a SQLITE_SCHEMA error, as is done with writes? A change in database is always a change. Also that way you don't waste time in rerunning the affected transactions. It is indeed locked as you describe. The problem is as follows: 1. One db handle performs sqlite3_prepare. This does not in itself start a transaction, but the resulting data structure (the prepared statement) relies on details of the schema at the time of prepare. E.g. "select * " query captures the list of columns when the statement is prepared. 2. Another db handle performs a transaction that modifies the schema. 3. sqlite3_step is called on the statement prepared at #1. It is at this point that the schema modification is discovered. Currently, SQLite reports SQLITE_SCHEMA error in this situation. The proprosal is for the statement to keep the text of the query, so the engine can re-prepare the satement and try to step again, transparently to the caller. 4. Once the first sqlite3_step succeeds, an implicit transaction is started (I assume there are no explicit transactions in effect), so the schema can no longer change unexpectedly. Well, the write was an example. So, a lock_schema wouldn't do the work at the prepare phase? The schema begins locked and when a transaction needs to do a change, sends a signal to gain exclusive, unlock, make the changes and lock it again. Don't know how many cpu cycles can this take but in a heavy scenario it may be less than re-prepare, in some cases reparse, the other threaded transactions. Antivirus. Warning: User detected. Please, move away from computer or you will be eliminated. Thanks
[sqlite] Re: Request for comment: Proposed SQLite API changes
Eduardo <[EMAIL PROTECTED]> wrote: Isn't better lock the database while a transaction that can make a SQLITE_SCHEMA error, as is done with writes? A change in database is always a change. Also that way you don't waste time in rerunning the affected transactions. It is indeed locked as you describe. The problem is as follows: 1. One db handle performs sqlite3_prepare. This does not in itself start a transaction, but the resulting data structure (the prepared statement) relies on details of the schema at the time of prepare. E.g. "select * " query captures the list of columns when the statement is prepared. 2. Another db handle performs a transaction that modifies the schema. 3. sqlite3_step is called on the statement prepared at #1. It is at this point that the schema modification is discovered. Currently, SQLite reports SQLITE_SCHEMA error in this situation. The proprosal is for the statement to keep the text of the query, so the engine can re-prepare the satement and try to step again, transparently to the caller. 4. Once the first sqlite3_step succeeds, an implicit transaction is started (I assume there are no explicit transactions in effect), so the schema can no longer change unexpectedly. Igor Tandetnik
[sqlite] Re: Request for comment: Proposed SQLite API changes
D. Richard Hipp wrote: > "Ulrich Telle" wrote: >> Sure, it would be nice if SQLITE_SCHEMA errors could be handled >> internally by SQLite, but I think it's a non-trivial task to handle >> this kind of error. >> >> When I created my SQLite C++ wrapper wxSQLite3 I thought about handling >> SCHEMA errors, but decided against it due to the complexities involved. > > Isn't that really the whole point of a wrapper - to deal with > complexities so that the user doesn't have to. Of course a wrapper should hide as much of the complexities as possible. And be assured my wrapper wxSQLite3 hides a lot of them. > If you are passing all of the complexities up to the user, > why use you wrapper at all? Just call SQLite directly. Have I written somewhere my wrapper passes *all* complexities up to the user??? Definitely not! If I understood it right each of sqlite3_prepare/sqlite3_step/sqlite3_finalize may return a SQLITE_SCHEMA error. At least for a SELECT statement sqlite3_prepare is called *once* _before_ the data of *each row* are fetched using sqlite3_step and sqlite3_finalize is called *once* after all rows have been processed. A wrapper will seldom hide this kind of processing. So the wrapper can not know whether the application is processing the data of each row or collecting the data for later processing elsewhere. If the SQLITE_SCHEMA error occurs for example after already 10 rows were processed. What should the wrapper - or SQLite (if you decide to handle SCHEMA errors internally) - do? If you recompile the statement calling sqlite3_prepare wouldn't you refetch *all* rows again (unless you have counted the number of rows already fetched and now skip this number of rows before returning)? And what does it mean if the SQLITE_SCHEMA error occurs for the first time when calling sqlite3_finalize? Then you have already processed all rows. How on earth a wrapper could hide this from the user? If it is trivial to handle SQLITE_SCHEMA errors then SQLIte should do it. If not, why and how should a wrapper do it? >> For INSERT, UPDATE or DELETE a simple retry might be a good choice. But >> how often should the retry take place? The SQLite FAQ code example >> contains an endless loop! > > Not. OK, I guess in theory, if another process were updating the > schema at just the right rate so that the schema was different every > times you retried, you could get an infinite loop. But in practice, > the loop never runs more than twice - 3 times in the extreme. Coding infinite loops should be avoided even if there is only a theoretical possibility it will ever loop forever. >> In case of a SELECT statement the situation is still more complex. The >> SCHEMA error could happen after reading several result rows. > > No. SCHEMA errors happen prior to reading any data. You mean a SQLITE_SCHEMA error can only occur when you try to read the data of the *first* row of a SELECT query? And if you were able to read the first row you will be able to read *all* rows? Hard to believe but if that is truly the case then definitely SQLite should handle this error internally. At least the documentation should be more explicit about when a SQLITE_SCHEMA error may occur. >> When retrying a query another problem arises if the SQL statement >> contains bind variables. You would have to rebind the variables. To >> handle this automatically would induce a lot of extra house keeping, >> wouldn't it? > > See the sqlite3_transfer_bindings() API. If SQLite already keeps track of all bindings it should keep a copy of the SQL statement string, too. Regards, Ulrich Telle
[sqlite] Re: Request for comment: Proposed SQLite API changes
Ulrich Telle <[EMAIL PROTECTED]> wrote: Sure, it would be nice if SQLITE_SCHEMA errors could be handled internally by SQLite, but I think it's a non-trivial task to handle this kind of error. For INSERT, UPDATE or DELETE a simple retry might be a good choice. But how often should the retry take place? The SQLite FAQ code example contains an endless loop! I didn't want to introduce such kind of code into wxSQLite3. - Additionally the number of columns in a table used in these statements might have changed, that is the statement would probably fail again. Only if the schema change affects the table you are manipulating. Even if this is the case, the statement might indeed fail again - but with a _different_ error (probably a syntax error), at which point you drop out of the loop and handle this error as you normally would. The only way for you to stay in that "endless" loop for a long time is if some other thread keeps changing the schema on you, in which case your application has worse problems to worry about. In case of a SELECT statement the situation is still more complex. The SCHEMA error could happen after reading several result rows. No it can't. A schema error can only occur between sqlite3_prepare or sqlite3_reset, and the first sqlite3_step. When retrying a query another problem arises if the SQL statement contains bind variables. You would have to rebind the variables. To handle this automatically would induce a lot of extra house keeping, wouldn't it? SQLite maintains sufficient information already. See sqlite3_transfer_bindings - it is intended specifically to allow a wrapper to transparently recover from SQLITE_SCHEMA error. Igor Tandetnik
Re: [sqlite] Re: Request for comment: Proposed SQLite API changes
Ulrich Telle wrote: In case of a SELECT statement the situation is still more complex. The SCHEMA error could happen after reading several result rows. If you would then redo the query automatically it would start from scratch delivering the already read rows again. If your application code gathers the result set in an array for example you would get duplicate rows. I think that a DB schema change can't happend while a transaction (explicit or implicit) is being executed. So it's no possible that a "select * from table" fails for a shema error while you didn't closed the query.. Paolo
[sqlite] Re: Request for comment: Proposed SQLite API changes
> > Suppose this where to change in version 3.3.0 so that the > > actual error code was returned by sqlite3_step(). That would be a good thing, since it would allow appropriate reaction at the right time and the right place. > > Then when a schema change occurred, the statement was automatically > > recompiled and rebound. There would no more SQLITE_SCHEMA errors. > > This change should be done. SQLITE_SCHEMA is all about > an internal implementation detail in SQLite and shouldn't > really be exposed to the users of SQLite. There is only > action that people take on getting it - rerun the query. > Pretty much every wrapper does that anyway so it makes > even more sense to make that the standard code in SQLite. Sure, it would be nice if SQLITE_SCHEMA errors could be handled internally by SQLite, but I think it's a non-trivial task to handle this kind of error. When I created my SQLite C++ wrapper wxSQLite3 I thought about handling SCHEMA errors, but decided against it due to the complexities involved. For example which reaction is appropriate depends on the kind of the SQL statement: For INSERT, UPDATE or DELETE a simple retry might be a good choice. But how often should the retry take place? The SQLite FAQ code example contains an endless loop! I didn't want to introduce such kind of code into wxSQLite3. - Additionally the number of columns in a table used in these statements might have changed, that is the statement would probably fail again. In case of a SELECT statement the situation is still more complex. The SCHEMA error could happen after reading several result rows. If you would then redo the query automatically it would start from scratch delivering the already read rows again. If your application code gathers the result set in an array for example you would get duplicate rows. When retrying a query another problem arises if the SQL statement contains bind variables. You would have to rebind the variables. To handle this automatically would induce a lot of extra house keeping, wouldn't it? If SQLite would be able to handle all these issues transparently, it would be ok for me. > If you are looking at API changes, the most beneficial to > me would be a unification of sqlite3_value_TYPE and > sqlite3_column_TYPE. I have to write identical code to > do my own type conversion when calling these function > and duplicate it. Similar story with sqlite3_result_TYPE > and sqlite3_bind_TYPE. Since C++ variables are typed my wrapper wxSQLite3 needs no type conversion. It's the user's responsibility to use variables of the right type. There is only one place where I would appreciate to have type information at hand. sqlite3_get_table returns all values as character strings and there is no way to find out the original value types. Regards, Ulrich Telle -- Ulrich Telle E-Mail privat: mailto:[EMAIL PROTECTED] E-Mail Studium: mailto:[EMAIL PROTECTED] Homepage: http://www.stud.fernuni-hagen.de/q1471341 Fax: +49-(0)89-1488-203070
RE: [sqlite] Re: Request for comment: Proposed SQLite API changes
I guess I have the same attitude as an old Main Framer I tried to sell a Datapoint Arc Net long, long ago. His summation of the presentation was, " I have enough damn trouble with one CPU. Why would I want a whole network full of them!" I can barely keep one release of a database running, why would I want two! :-) I have yet to have worked with any database engine that I could not migrate to a new release external to any code I have written. Including Paradox and XBase if those qualify as engines. To each his own. Fred > -Original Message- > From: Igor Tandetnik [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 12:01 PM > To: SQLite > Subject: [sqlite] Re: Request for comment: Proposed SQLite API changes > > > Fred Williams wrote: > > Thanks. I guess I never considered using two different releases of > > any product within the same executable. Wonder how many use this > > feature and why? > > SQLite2 and SQLite3 have incompatible database formats. > Neither can read > the databases produced by the other. One might want to write a > conversion utility that reads data with sqlite2_* API and > writes it to a > new database with sqlite3_*. Or say you cannot migrate your > legacy data > for some reason so you need your software to be able to work > with both > formats. > > Igor Tandetnik >
[sqlite] Re: Request for comment: Proposed SQLite API changes
Fred Williams wrote: Thanks. I guess I never considered using two different releases of any product within the same executable. Wonder how many use this feature and why? SQLite2 and SQLite3 have incompatible database formats. Neither can read the databases produced by the other. One might want to write a conversion utility that reads data with sqlite2_* API and writes it to a new database with sqlite3_*. Or say you cannot migrate your legacy data for some reason so you need your software to be able to work with both formats. Igor Tandetnik
