Re: [sqlite] Request for comment: Proposed SQLite API changes
Sorry for being dense, but I don't understand what you are saying. What do you mean by "prepare a transaction"? Transactions are not prepared, statements are. sqlite3_prepare is not part of a transaction, does not initiate a transaction, and is not affected by any ongoing transaction in any way, shape or form. The first call to sqlite3_step on a prepared statement initiates the transaction (assuming there is no explicitly started transaction in place on the database handle). SQLITE_SCHEMA error arises when another process or thread changes the schema between sqlite3_prepare (or sqlite3_reset) and sqlite3_step. If your usage pattern is something like begin transaction prepare step step ... finalize commit then you don't need to worry about SQLITE_SCHEMA under the existing engine. The whole issue becomes moot. Of course you are losing one of the benefits of prepared statements - time saving due to elimination of unnecessary parsing and execution planning. Igor Tandetnik Well, i use sqlite without threads, the os where it's used hasn't thread support and there is one app with sqlite only, so only one process and only one transaction is on the way. That's why i say my point of view maybe a bit obfuscated. I only tried to look for other ways to avoid SQLITE_SCHEMA errors, re-prepare other threads is a bit expensive in cpu cycles, at least for me. Thanks for your time ;) - La diferencia entre la teoria y la practica es que en teoria no hay, pero en la practica si
Re: [sqlite] Request for comment: Proposed SQLite API changes
At 14:18 03/11/2005, you wrote: As currently implemented, when an error occurs during sqlite3_step(), the function returns SQLITE_ERROR. Then you have to call either sqlite3_reset() or sqlite3_finalize() to find the actual error code. Suppose this where to change in version 3.3.0 so that the actual error code was returned by sqlite3_step(). That would mean that moving from version 3.2.7 to 3.3.0 might involve some minor code changes. The API would not be 100% backwards compatible. But the API would be cleaner. Yes for it. Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? That i don't understand. So, there are 2 scenarios a) sqlite3.2 process a SQL transaction while a potentially SQLITE_SCHEMA error transaction is on the road. b) sqlite3.2 begins a potentially SQLITE_SCHEMA error transaction while others transactions are on the road. 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. - Useful Acronymous : FAQ = Frequently 'Answered' Questions
RE: [sqlite] Request for comment: Proposed SQLite API changes
> -Original Message- > From: Cory Nelson [mailto:[EMAIL PROTECTED] > Sent: Saturday, November 05, 2005 11:40 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > *I* know that, and *you* know that, but the idea is to > minimize the impact > > of inefficient code. This is somewhat akin to the ol' += > string concat > > operator ... so many people use it that you can't throw it > out the window as > > inefficient -- but you CAN change your underpinnings to > minimize the impact. > > As a wrapper writer I can't control how people code -- in > my case I am bound > > by the ADO.NET 2.0 architecture and have a standard that I > cannot deviate > > from. > > Hmm... no. It is not the job of the author to optimize for improper > use of something. If you have so many ignorant users, perhaps you > need to re-think your documentation. First, there's a difference between improper and inefficient. String concatenation using a += operator is not improper, but it can be inefficient if the string class doesn't use its buffer wisely. Likewise building a SQL string each time and executing it isn't improper, but depending on what it is, it can be inefficient. As for the documentation, tell that to Microsoft. I didn't write ADO.NET. I have an entire section of my helpfile dedicated to educating users about the wonders of paramerized queries and transactions, but I can only lead the horse to water. Robert
Re: [sqlite] Request for comment: Proposed SQLite API changes
On 11/4/05, Robert Simpson <[EMAIL PROTECTED]> wrote: > - Original Message - > From: "Dennis Cote" <[EMAIL PROTECTED]> > To: > Sent: Friday, November 04, 2005 8:48 AM > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > > > Robert Simpson wrote: > > > >> It's 100,000 more memory allocations and about 4.6mb of string copies > >> SQLite is doing behind the scenes. Every time SQLite prepares a > >> statement it allocates and copies the string. If you're inserting a > >> million rows into a database using the above "cheese" method (which I > >> hate but a lot of folks do anyway) then that's a pretty serious > >> performance hit IMO. > > [snip] > > > Doing things this way is a bad idea because you are doing most of the work > > over repeatedly when you don't need to. The proposed change would add a > > trivial amount of extra work for each iteration. Improving performance in > > this situation is a classic example of where changing your algorithm will > > provide a much greater return than micro optimization to eliminate one > > malloc and one strcpy. You could optimize this loop all you want, it will > > never be fast. > > *I* know that, and *you* know that, but the idea is to minimize the impact > of inefficient code. This is somewhat akin to the ol' += string concat > operator ... so many people use it that you can't throw it out the window as > inefficient -- but you CAN change your underpinnings to minimize the impact. > As a wrapper writer I can't control how people code -- in my case I am bound > by the ADO.NET 2.0 architecture and have a standard that I cannot deviate > from. Hmm... no. It is not the job of the author to optimize for improper use of something. If you have so many ignorant users, perhaps you need to re-think your documentation. > > On the other hand, the extra allocation and copy that concerns you could > > often be eliminated. If a new prepare API function was created to go along > > with the enhanced step function, it could use the same type of string > > destructor argument that the bind text call uses. This would allow the > > caller to pass in an SQLITE_STATIC string and avoid the need for SQLite to > > copy the SQL statement in many (if not most) cases. If it ever needed to > > recompile the statement, it would be able to use the string that the > > caller already had. SQLite would then only make its own copy if it was > > passed SQLITE_TRANSIENT. > > Yes, if one could specify the string was static or transient and if the > function were separate, then I'd be happy. As it is, I have inefficient > users of my wrapper, making trouble for themselves relying on .NET's garbage > collector and many developing on the Pocket PC where any extra memory > allocations and copies are bound to have a much higher performance impact > than the desktop. > > Robert > > > -- Cory Nelson http://www.int64.org
Re: [sqlite] Request for comment: Proposed SQLite API changes
- Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: Sent: Friday, November 04, 2005 8:48 AM Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes Robert Simpson wrote: It's 100,000 more memory allocations and about 4.6mb of string copies SQLite is doing behind the scenes. Every time SQLite prepares a statement it allocates and copies the string. If you're inserting a million rows into a database using the above "cheese" method (which I hate but a lot of folks do anyway) then that's a pretty serious performance hit IMO. [snip] Doing things this way is a bad idea because you are doing most of the work over repeatedly when you don't need to. The proposed change would add a trivial amount of extra work for each iteration. Improving performance in this situation is a classic example of where changing your algorithm will provide a much greater return than micro optimization to eliminate one malloc and one strcpy. You could optimize this loop all you want, it will never be fast. *I* know that, and *you* know that, but the idea is to minimize the impact of inefficient code. This is somewhat akin to the ol' += string concat operator ... so many people use it that you can't throw it out the window as inefficient -- but you CAN change your underpinnings to minimize the impact. As a wrapper writer I can't control how people code -- in my case I am bound by the ADO.NET 2.0 architecture and have a standard that I cannot deviate from. On the other hand, the extra allocation and copy that concerns you could often be eliminated. If a new prepare API function was created to go along with the enhanced step function, it could use the same type of string destructor argument that the bind text call uses. This would allow the caller to pass in an SQLITE_STATIC string and avoid the need for SQLite to copy the SQL statement in many (if not most) cases. If it ever needed to recompile the statement, it would be able to use the string that the caller already had. SQLite would then only make its own copy if it was passed SQLITE_TRANSIENT. Yes, if one could specify the string was static or transient and if the function were separate, then I'd be happy. As it is, I have inefficient users of my wrapper, making trouble for themselves relying on .NET's garbage collector and many developing on the Pocket PC where any extra memory allocations and copies are bound to have a much higher performance impact than the desktop. Robert
Re: [sqlite] Request for comment: Proposed SQLite API changes
Dennis Cote <[EMAIL PROTECTED]> writes: > Furthermore, I don't believe that most users are using an authorizer anyway > (but I could definitely be wrong about that). In that case all the authorizer > callbacks become no-ops don't they? PHP's use of sqlite uses an authorizer callback in the original (sqlite2) functionalilty and I expect does so in the current PDO implementation for sqltie3. That means that all apps using sqlite i PHP have an authorizer callback. Derrell
Re: [sqlite] Request for comment: Proposed SQLite API changes
Rob Lohman wrote: This all starts to feel a bit "unsafe". What would happen if a program would load a library that houses the callback and it gets unloaded after the first prepare? Or is it a rule you need to have the callback available for the entire run of the virtual machine? It would have to be a "rule" as you say. It is really no different than what we have now. You can't unload an authorizer between the calls to sqlite3_set_authorizer(func) and sqlite3_set_authorizer(NULL). This will be basically the same except the calls are sqlite3_prepare_v2() and sqlite3_finalize(). Dennis Cote
Re: [sqlite] Request for comment: Proposed SQLite API changes
The authorizer is used to protect against SQL injection attaches when the SQL text originates from user input. Typically an application will turn the authorizer on when preparing user-supplied SQL then turn it right back off again so that its own internal SQL can run unfiltered. Example: sqlite3_set_authorizer(db, ignore_passwd_column); stmt1 = sqlite3_prepare(db, zSqlFromUser) sqlite3_set_authorizer(db, 0); stmt2 = sqlite3_prepare(db, zInternalSql); sqlite3_step(stmt1); -- Oops! Might try to recompile! Note also that the authorizer will not necessary throw an error the first time. It might just cause certain columns in a table to be ignored. For example, in the CVSTrac system (used for bug tracking on SQLite as well as elsewhere) user-generated ticket reports can query any table in the database. But if the report requests the USER.PASSWD field, the authorizer causes that field to return a NULL rather than the actual user password. No error is generated so there is nothing to signal a problem the first time the authorizer is run. But if the statement is then recompiled automatically with the authorizer turned off, then the PASSWD information might leak through. I see. So in order to re-prepare an SQL statement you would need to keep a copy of the authorizer callback pointer that was used when it was originally prepared. Couldn't this be done automatically as well? Perhaps it could be made more explicit by adding an authorizer callback parameter to a new sqlite3_prepare_v2() function. This function could also include an SQL string destructor argument that could be used to eliminate most SQL string copies as well. This all starts to feel a bit "unsafe". What would happen if a program would load a library that houses the callback and it gets unloaded after the first prepare? Or is it a rule you need to have the callback available for the entire run of the virtual machine? Rob
Re: [sqlite] Request for comment: Proposed SQLite API changes
On 11/4/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > The authorizer is used to protect against SQL injection attaches > when the SQL text originates from user input. Typically an > application will turn the authorizer on when preparing user-supplied > SQL then turn it right back off again so that its own internal > SQL can run unfiltered. Example: > > sqlite3_set_authorizer(db, ignore_passwd_column); > stmt1 = sqlite3_prepare(db, zSqlFromUser) > sqlite3_set_authorizer(db, 0); > stmt2 = sqlite3_prepare(db, zInternalSql); > sqlite3_step(stmt1); -- Oops! Might try to recompile! Well, obviously, part of the sqlite3_stmt structure, then, is the authorizer that is used on that particular statement ;) So change it so that *if* you decide to do this, sqlite3_stmt also has its authorizer as well as the original SQL statement. Better might be to have an alternative data structure (sqlite3_stmt2) with calls as appropriate. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] Request for comment: Proposed SQLite API changes
[EMAIL PROTECTED] wrote: The authorizer is used to protect against SQL injection attaches when the SQL text originates from user input. Typically an application will turn the authorizer on when preparing user-supplied SQL then turn it right back off again so that its own internal SQL can run unfiltered. Example: sqlite3_set_authorizer(db, ignore_passwd_column); stmt1 = sqlite3_prepare(db, zSqlFromUser) sqlite3_set_authorizer(db, 0); stmt2 = sqlite3_prepare(db, zInternalSql); sqlite3_step(stmt1); -- Oops! Might try to recompile! Note also that the authorizer will not necessary throw an error the first time. It might just cause certain columns in a table to be ignored. For example, in the CVSTrac system (used for bug tracking on SQLite as well as elsewhere) user-generated ticket reports can query any table in the database. But if the report requests the USER.PASSWD field, the authorizer causes that field to return a NULL rather than the actual user password. No error is generated so there is nothing to signal a problem the first time the authorizer is run. But if the statement is then recompiled automatically with the authorizer turned off, then the PASSWD information might leak through. I see. So in order to re-prepare an SQL statement you would need to keep a copy of the authorizer callback pointer that was used when it was originally prepared. Couldn't this be done automatically as well? Perhaps it could be made more explicit by adding an authorizer callback parameter to a new sqlite3_prepare_v2() function. This function could also include an SQL string destructor argument that could be used to eliminate most SQL string copies as well. Dennis Cote
Re: [sqlite] Request for comment: Proposed SQLite API changes
Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > >Dan Kennedy <[EMAIL PROTECTED]> wrote: > >>> > >>The authorization callback > >>would have to be reinvoked from within sqlite3_step() too. > >> > >> > > > >Yikes! I didn't think of that. > > > > > > I don't see why this should have any impact. If you pass the > SQLITE_SCHEMA error back to the user, they will have to re-prepare the > statement, which will also re-invoke the authorizer. Aren't the > authorizer callbacks an automatic part of the compilation process? If > the authorizer allowed you to compile the statement successfully the > first time, isn't it almost certain that it would allow you to compile > the statement again? > > Furthermore, I don't believe that most users are using an authorizer > anyway (but I could definitely be wrong about that). In that case all > the authorizer callbacks become no-ops don't they? > The authorizer is used to protect against SQL injection attaches when the SQL text originates from user input. Typically an application will turn the authorizer on when preparing user-supplied SQL then turn it right back off again so that its own internal SQL can run unfiltered. Example: sqlite3_set_authorizer(db, ignore_passwd_column); stmt1 = sqlite3_prepare(db, zSqlFromUser) sqlite3_set_authorizer(db, 0); stmt2 = sqlite3_prepare(db, zInternalSql); sqlite3_step(stmt1); -- Oops! Might try to recompile! Note also that the authorizer will not necessary throw an error the first time. It might just cause certain columns in a table to be ignored. For example, in the CVSTrac system (used for bug tracking on SQLite as well as elsewhere) user-generated ticket reports can query any table in the database. But if the report requests the USER.PASSWD field, the authorizer causes that field to return a NULL rather than the actual user password. No error is generated so there is nothing to signal a problem the first time the authorizer is run. But if the statement is then recompiled automatically with the authorizer turned off, then the PASSWD information might leak through. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Request for comment: Proposed SQLite API changes
[EMAIL PROTECTED] wrote: Dan Kennedy <[EMAIL PROTECTED]> wrote: Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. The authorization callback would have to be reinvoked from within sqlite3_step() too. Yikes! I didn't think of that. This is pretty big negative and will likely scuttle plans to do automatic re-prepare. -- D. Richard Hipp <[EMAIL PROTECTED]> Richard, Why? I don't see why this should have any impact. If you pass the SQLITE_SCHEMA error back to the user, they will have to re-prepare the statement, which will also re-invoke the authorizer. Aren't the authorizer callbacks an automatic part of the compilation process? If the authorizer allowed you to compile the statement successfully the first time, isn't it almost certain that it would allow you to compile the statement again? Furthermore, I don't believe that most users are using an authorizer anyway (but I could definitely be wrong about that). In that case all the authorizer callbacks become no-ops don't they? Dennis Cote
Re: [sqlite] Request for comment: Proposed SQLite API changes
On 3 Nov 2005, at 13:18, [EMAIL PROTECTED] wrote: Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. I'm in favour of this change. I think any code that wraps around prepared/reusable queries will have to do it anyway (I've written code to do exactly this). Based on some of the previous comments (and my limited understanding of SQLite internals), it would seem to make sense to limit this functionality to cases when the developer specifically is preparing a query for reuse. Perhaps a new prepare function, where the only difference between it and the current one is that the original statement is stored and re-preapared as needed. Wrappers creating reusable queries would just call the new function and be guaranteed not to get a SQLITE_SCHEMA error, whereas existing code will work as before. This originally came up for me when ATTACHing and detaching a database changed the schema. In my application this is not a "rare case". Cheers, Demitri
Re: [sqlite] Request for comment: Proposed SQLite API changes
Robert Simpson wrote: It's 100,000 more memory allocations and about 4.6mb of string copies SQLite is doing behind the scenes. Every time SQLite prepares a statement it allocates and copies the string. If you're inserting a million rows into a database using the above "cheese" method (which I hate but a lot of folks do anyway) then that's a pretty serious performance hit IMO. Robert, When you are doing things this way the additional cost of the memory allocations and string copies is much much smaller than the cost of parsing the SQL and generating the VM code over and over. I suspect it would be barley noticeable even if you actually measure it. Doing things this way is a bad idea because you are doing most of the work over repeatedly when you don't need to. The proposed change would add a trivial amount of extra work for each iteration. Improving performance in this situation is a classic example of where changing your algorithm will provide a much greater return than micro optimization to eliminate one malloc and one strcpy. You could optimize this loop all you want, it will never be fast. On the other hand, the extra allocation and copy that concerns you could often be eliminated. If a new prepare API function was created to go along with the enhanced step function, it could use the same type of string destructor argument that the bind text call uses. This would allow the caller to pass in an SQLITE_STATIC string and avoid the need for SQLite to copy the SQL statement in many (if not most) cases. If it ever needed to recompile the statement, it would be able to use the string that the caller already had. SQLite would then only make its own copy if it was passed SQLITE_TRANSIENT. Dennis Cote
Re: [sqlite] Request for comment: Proposed SQLite API changes
Rob Lohman wrote: Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? I'm a big fan of this change. We do quite a lot of bookkeeping in our own code to do exactly the same thing at the moment. It would make me very happy to delete that code. This is not directly a question for you, Eric, but does anyone actually have schema changes on a working database? I've never seen any schema changes on my databases except for a new version, bugfixes etc. Does anyone runs code that actually changes the schema of your database as the normal process in an application? If so, for what reason? Rob This is to the point. Why carry an extra load for something which may never happen. In the very special case where it might, then the Sqlite API can be extended to add the capability. JS
Re: [sqlite] Request for comment: Proposed SQLite API changes
Robert Simpson wrote:
- Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]>
To:
Sent: Thursday, November 03, 2005 4:14 PM
Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes
Robert Simpson wrote:
This is one gives me pause. I see lots of people doing things like
this in their code (Using C# since I address this in my wrapper's
helpfile and its convenient):
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
int n;
for (n = 0; n < 10; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable]
([MyId]) VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
}
I try to discourage this sort of thing, but so many people do this
and they'll be hit with a stiffer penalty under the proposed change.
IMO schema change errors are an outside case, and preventing them by
introducing this change would only penalize users that have already
either coded around this issue or don't experience it at all.
Robert,
I don't understand why you think your users will pay a stiffer penalty
under the proposed change. In your case the database schema isn't
changing, so there will be no SQLITE_SCHEMA errors to handle. The only
cost is the cost of storing a copy of the SQL string for the lifetime
of the prepared statement. In your case I assume that the statement is
prepared, stepped, and finalized in your ExecuteNonQuery function. So
it would only require additional memory for one copy of the SQL string
for the length of time that that function takes to execute. That
storage would be released when the prepared statement is finalized. I
don't think this is a severe penalty for any user.
It's 100,000 more memory allocations and about 4.6mb of string copies
SQLite is doing behind the scenes. Every time SQLite prepares a
statement it allocates and copies the string. If you're inserting a
million rows into a database using the above "cheese" method (which I
hate but a lot of folks do anyway) then that's a pretty serious
performance hit IMO.
Robert
A suggestion to go with that proposed change. Rename the product
"SQLHEAVY".
Re: [sqlite] Request for comment: Proposed SQLite API changes
> That would compel me to change *all* of the API to use a > "sqlite4_" prefix instead of "sqlite3_". I'm guessing that eventually backwards compatibility will be broken in such a way that there will be an sqlite4 (and perhaps an sqlite5 down the line). When that happens you might consider using macro slight-of-hand for major version number symbol prefixing: #define SQLITE(b) SQLITE1(sqlite4_,b) #define SQLITE1(a,b) a##b This way redefining the macro SQLITE() need only change in one spot. For example: SQLITE(open) would expand to sqlite4_open SQLITE(exec) would expand to sqlite4_exec It's ugly, but at least the number of characters in the identifiers remain the same. An external header file would still have to be created with the new name "sqlite4.h" (or whatever) so the users would pick up the correct sqlite header, but all the internal sqlite source code using the new macro scheme need not change in any subsequent major version upgrade. __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs
Re: [sqlite] 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.
>
> 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. If you are passing all of the
complexities up to the user, why use you wrapper at all? Just call
SQLite directly.
>
> 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.
> Additionally the number of columns in a table used in these statements might
> have changed, that is the statement would probably fail again.
If the number of columns changes, you will get a syntax error, not
a schema error. At that point you exit the loop.
>
> 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.
>
> 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.
A Digression --
To all writers of wrappers, my I please call your attention to
the TCL wrapper for SQLite. In that wrapper I have attempted
to hide as many details of the interface as possible from the user and
to make everything automatic. This allows the user to focus on their
application and not worry so much about the details of the interface
to the database. I and everybody else I have talked to find this to
be a very refreshing approach.
In the TCL wrapper, you run SQL statements using this template:
DBOBJECT eval SQL-STATEMENT CODE-TO-HANDLE-EACH-RESULT-ROW
The DBOBJECT is the object that is created when you open the database.
On this object you call the "eval" method with two arguments, the
text of the SQL you want to process and a lambda procedure that runs
once for each row of output. (The lambda is optional and is usually
omitted for non-query statements.) The bindings automatically process
named parameters within the SQL statement by binding them with the
value of TCL variables of the same name. So for example, if you
say:
db eval {UPDATE table1 SET x=$var1 WHERE y=$rownum}
The $var1 and $rownum named parameters are bound to the values of
the var1 and rownum variables in TCL. This is all automatic.
Within the lambda procedure that runs once for each row of the result,
local variables are created to contain the value of each column -
the names of the local variables are chosen to match the column names.
That way the user does not have to remember any method calls or
other such complication to get at the results.
Note the complete absence of prepared statements. Prepared statements
are handled automatically by the wrapper. The wrapper keep a cache
of recently used SQL statements and reuses them if the reappear.
Statements are cleared from the cache using the LRU algorithm. There
are methods on the database object to changes the size of the prepared
cache or flush the cache. But those methods never get used in practice
because the cache just seems to work without any problems.
Note also that the user never needs to worry about how to bind
values. Bindings all happens automatically and transparently.
SQLITE_SCHEMA errors are also handled automatically.
The end results it that the user of the TCL binds does not need to
remember much about how the wrapper work. If they can just remember
how to create a database object (hint: use the sqlite3 constructor)
and how to run the eval method, then they have access to the full
power of the SQLite interface without having to know or remember
ny of the details.
There is an important principle at work here: keep it simple.
An interface with less complication, with fewer choices and options,
with less to remember and think about, is a better interface. I
want to encourage all authors of wrappers and bindings for SQLite
and for other libraries to keep this principle in mind.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] 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(). Would it still be necessary to call sqlite3_finalize? If so then I don't see the point of the API change. 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. 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. The full gory details are in my original post with feedback from a wrapper author: http://article.gmane.org/gmane.comp.db.sqlite.general/9222/ It would be useful if other wrapper authors also gave their feedback. Roger
Re: [sqlite] Request for comment: Proposed SQLite API changes
API changes for SQLite are fine with me as long as the PHP folks keep up with SQLite in terms of implementing SQLite hooks. I'm not real experienced with SQLite, but I'm starting to learn a lot. I use it with PHP 5.1 and PDOs. I find myself compiling the latest SQLite CVS and the latest snapshot of PHP 5.1 about once a week. Can't live without 'em. Bob Cochran Greenbelt, Maryland, USA [EMAIL PROTECTED] wrote: As currently implemented, when an error occurs during sqlite3_step(), the function returns SQLITE_ERROR. Then you have to call either sqlite3_reset() or sqlite3_finalize() to find the actual error code. Suppose this where to change in version 3.3.0 so that the actual error code was returned by sqlite3_step(). That would mean that moving from version 3.2.7 to 3.3.0 might involve some minor code changes. The API would not be 100% backwards compatible. But the API would be cleaner. What does the community think about such a change? Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Request for comment: Proposed SQLite API changes
> -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 6:28 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > At 4:22 PM -0700 11/3/05, Robert Simpson wrote: > >It's 100,000 more memory allocations and about 4.6mb of string > >copies SQLite is doing behind the scenes. Every time SQLite > >prepares a statement it allocates and copies the string. If you're > >inserting a million rows into a database using the above "cheese" > >method (which I hate but a lot of folks do anyway) then that's a > >pretty serious performance hit IMO. > > This only happens if you are not reusing prepared statements for each > row going into the same table. I know, that's just what I said. I also said I don't do this, but many people do. > And if you're not reusing prepared statements, you're already doing > 100,000 memory allocations that you shouldn't be doing, Not necessarily. One could have a static fixed-size buffer that is constantly changing and re-preparing -- but that's not the point. > for the statement handle itself, and have already > hit yourself. So since they've already hit themselves once, why not hit them again? > Use prepared statements and both hits are brought down > to something negligible. And just like telling people smoking is bad for them, they will continue to do it. Anyway, the point is moot since DRH said the authentication callback problem would be a showstopper. Robert
Re: [sqlite] Request for comment: Proposed SQLite API changes
At 4:22 PM -0700 11/3/05, Robert Simpson wrote: It's 100,000 more memory allocations and about 4.6mb of string copies SQLite is doing behind the scenes. Every time SQLite prepares a statement it allocates and copies the string. If you're inserting a million rows into a database using the above "cheese" method (which I hate but a lot of folks do anyway) then that's a pretty serious performance hit IMO. This only happens if you are not reusing prepared statements for each row going into the same table. And if you're not reusing prepared statements, you're already doing 100,000 memory allocations that you shouldn't be doing, for the statement handle itself, and have already hit yourself. Use prepared statements and both hits are brought down to something negligible. -- Darren Duncan
Re: [sqlite] Request for comment: Proposed SQLite API changes
- Original Message -
From: "Dennis Cote" <[EMAIL PROTECTED]>
To:
Sent: Thursday, November 03, 2005 4:14 PM
Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes
Robert Simpson wrote:
This is one gives me pause. I see lots of people doing things like this
in their code (Using C# since I address this in my wrapper's helpfile and
its convenient):
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
int n;
for (n = 0; n < 10; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId])
VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
}
I try to discourage this sort of thing, but so many people do this and
they'll be hit with a stiffer penalty under the proposed change.
IMO schema change errors are an outside case, and preventing them by
introducing this change would only penalize users that have already
either coded around this issue or don't experience it at all.
Robert,
I don't understand why you think your users will pay a stiffer penalty
under the proposed change. In your case the database schema isn't
changing, so there will be no SQLITE_SCHEMA errors to handle. The only
cost is the cost of storing a copy of the SQL string for the lifetime of
the prepared statement. In your case I assume that the statement is
prepared, stepped, and finalized in your ExecuteNonQuery function. So it
would only require additional memory for one copy of the SQL string for
the length of time that that function takes to execute. That storage would
be released when the prepared statement is finalized. I don't think this
is a severe penalty for any user.
It's 100,000 more memory allocations and about 4.6mb of string copies SQLite
is doing behind the scenes. Every time SQLite prepares a statement it
allocates and copies the string. If you're inserting a million rows into a
database using the above "cheese" method (which I hate but a lot of folks do
anyway) then that's a pretty serious performance hit IMO.
Robert
Re: [sqlite] Request for comment: Proposed SQLite API changes
Robert Simpson wrote:
This is one gives me pause. I see lots of people doing things like
this in their code (Using C# since I address this in my wrapper's
helpfile and its convenient):
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
int n;
for (n = 0; n < 10; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable]
([MyId]) VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
}
I try to discourage this sort of thing, but so many people do this and
they'll be hit with a stiffer penalty under the proposed change.
IMO schema change errors are an outside case, and preventing them by
introducing this change would only penalize users that have already
either coded around this issue or don't experience it at all.
Robert,
I don't understand why you think your users will pay a stiffer penalty
under the proposed change. In your case the database schema isn't
changing, so there will be no SQLITE_SCHEMA errors to handle. The only
cost is the cost of storing a copy of the SQL string for the lifetime of
the prepared statement. In your case I assume that the statement is
prepared, stepped, and finalized in your ExecuteNonQuery function. So it
would only require additional memory for one copy of the SQL string for
the length of time that that function takes to execute. That storage
would be released when the prepared statement is finalized. I don't
think this is a severe penalty for any user.
Dennis Cote
Re: [sqlite] Request for comment: Proposed SQLite API changes
- Original Message -
From: <[EMAIL PROTECTED]>
As currently implemented, when an error occurs during
sqlite3_step(), the function returns SQLITE_ERROR. Then
you have to call either sqlite3_reset() or sqlite3_finalize()
to find the actual error code. Suppose this where to
change in version 3.3.0 so that the actual error code
was returned by sqlite3_step(). That would mean that
moving from version 3.2.7 to 3.3.0 might involve some
minor code changes. The API would not be 100% backwards
compatible. But the API would be cleaner.
This is a good change and though it'll require some code changes on my part,
I'm all for it.
Another proposal: Suppose that when creating an
sqlite3_stmt using sqlite3_prepare, the original SQL
text was stored in the sqlite3_stmt. Then when a
schema change occurred, the statement was automatically
recompiled and rebound. There would no more SQLITE_SCHEMA
errors. But sqlite3_stmts would use a little more
memory. And sqlite3_step might take a little longer
to initialize sometimes if it found it needed to rerun
the parser.
This is one gives me pause. I see lots of people doing things like this in
their code (Using C# since I address this in my wrapper's helpfile and its
convenient):
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
int n;
for (n = 0; n < 10; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId])
VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
}
I try to discourage this sort of thing, but so many people do this and
they'll be hit with a stiffer penalty under the proposed change.
IMO schema change errors are an outside case, and preventing them by
introducing this change would only penalize users that have already either
coded around this issue or don't experience it at all.
Robert
Re: [sqlite] Request for comment: Proposed SQLite API changes
To summarize my thoughts on this thread so far: 1. None of the stuff being discussed should cause a major version change, such as to 4.x.y; a big leap like that should only be taken for large and sweeping changes, such as incompatible file formats or paradigm shifts. Mainly, they should be given a lot of time to think through and bundle a number of issues, such as 3.x.y did, and not just be done at the drop of a hat like doing it now would be. 2. Any changes being discussed should just raise the version to 3.3.0; really, it is only changes to the third digit which shouldn't break anything, while changing the second that could reasonably happen. 3. Having sqlite3_step() return the actual error is indeed an improvement, assuming that the error had always been detected at that time, deferring its report is counterintuitive. 4. A rebinding change on SQLITE_SCHEMA errors should be done with a new function name since it is significantly different behaviour and effectively a wrapper over several other function calls. Let users choose what they want to use. 5. That said, its fine to store the original SQL text in sqlite3_stmt regardless of whether users use the new function or not, for simplicity, and its storage may carry other uses later as well. 6. I suggest having a compile time pragma regarding SQL text storage so that people wanting to save the most RAM can have it not stored and lose the new function, while it is available by default otherwise. -- Darren Duncan
Re: [sqlite] Request for comment: Proposed SQLite API changes
Dennis Cote wrote: I think both of these proposed changes are useful enhancements to SQLite. I also think it would be better to add a new sqlite3_step_v2() API function that does this. This will eliminate the need to change the base version number, since existing code can continue to use the existing step function. New code, or those who want to modify their existing code, could use the new function. The new step function should be a wrapper around the existing step function in much the same way as sqlite3_exec() wraps the entire prepare, step, finalize process. It would do the enhanced error checks and return the real error, or automatically re-prepare the SQL if the error was SQLITE_SCHEMA. This would give you the best of both worlds, backwards compatibility and a cleaner API for new code. I have to agree here; simply changing sqlite3_step() could cause problems for code that links to sqlite as a shared library/dll and can't be sure which minor version is installed. Currently it's usually possible to simply drop in the latest version of the library to get the latest features/optimizations/bug fixes; I think that behavior is important to preserve. The auto-compilation change is potentially a can of worms, because while it's not likely in a sane environment, it's possible that a schema change could render the original query uncompilable, particularly if SQLite ever added support for dropping or renaming columns. Or the authorizer callback could fail upon recompilation. The upshot is that there are still going to be cases where a schema change could cause an sqlite3_step() to fail so it would still be necessary to write defensive code; the change would simply cause that code to be executed less frequently.
Re: [sqlite] Request for comment: Proposed SQLite API changes
On 3 Nov 2005, at 08:18, [EMAIL PROTECTED] wrote: As currently implemented, when an error occurs during sqlite3_step(), the function returns SQLITE_ERROR. Then you have to call either sqlite3_reset() or sqlite3_finalize() to find the actual error code. Suppose this where to change in version 3.3.0 so that the actual error code was returned by sqlite3_step(). That would mean that moving from version 3.2.7 to 3.3.0 might involve some minor code changes. The API would not be 100% backwards compatible. But the API would be cleaner. What does the community think about such a change? Changing backwards compatibility considered very bad by me. What a mess that would be for DBD::SQLite if/when someone updates sqlite.so and everything stops working in very subtle ways. Yuck! Wrap it in a new function please. Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? Judging by the followup you posted this can't be done easily, but I'd be happy if you can find a way. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
RE: [sqlite] Request for comment: Proposed SQLite API changes -- [OT] GCC
Joe Wilson <[EMAIL PROTECTED]> wrote: > :-) A very nice description of C. Unfortunately, it's the > best cross-platform/cross-language tool available for such > a library. Java and C++ have a whole other mess of > compatibility problems - even on the same platform. Try > using a C++ shared library compiled in GCC 2.9.x with code > from GCC 3.x or GCC 4.x - there are not enough > curse words available in the english language. But post-GCC 3 C++ developments are far more compatible -- even ABI to a point. ANSI C++ compliance and other, major changes thanx to the Cygnus team (now Red Hat) are in order. There was a reason why the GNU project gave Cygnus the reins on GCC 3. Before GCC 3, there was a major lack of compatibility between GCC 2.x releases -- so much so that Red Hat did not bother to adopt GCC 2.8 after 2.7, and went directly to EGCS instead. Now understand that the entire GCC 2.9.x was never supposed to be adopted -- sans maybe 2.91.66 (which was essentially EGCS 1.1.2). It was a radical change that broke GCC 2 compatibility -- especially for C++. Although GCC 2.95.x tried to maintain some GCC 2 compatibility, the 2.96 branch finally forced the ANSI C++ issue. At one point, for its distribution end, Red Hat finally decided that there was no sense in sticking with non-ANSI C++ compliant code for its new distro series, and incremented the finalized work for GCC 3 to a new 2.97 branch -- releasing 2.96. A lot of people bitched and moaned, but it was actually a very good thing they did IMHO. [ Yes, it would have been more ideal to wait on GCC 3, but the full GCC 3 release was nowhere near finished, and Red Hat wouldn't wait another year before working on a new distro series. ] GCC 2 was _not_ ANSI C++ compliant. GCC 3+ is (GCC 2.96 is too, but it's not fully GCC 3+ ABI/API compatible). Although GCC 3/4 allow you to use some GCC 2 directives and allow for some GCC 2 compatibility with a few options, they are not recommended. -- Bryan J. Smith| Sent from Yahoo Mail mailto:[EMAIL PROTECTED] | (please excuse any http://thebs413.blogspot.com/ | missing headers)
RE: [sqlite] Request for comment: Proposed SQLite API changes
:-) A very nice description of C. Unfortunately, it's the best cross-platform/cross-language tool available for such a library. Java and C++ have a whole other mess of compatibility problems - even on the same platform. Try using a C++ shared library compiled in GCC 2.9.x with code from GCC 3.x or GCC 4.x - there are not enough curse words available in the english language. --- Cariotoglou Mike <[EMAIL PROTECTED]> wrote: > ...and the only > tool to structure your code and isolate interface from implementation is > a crappy .H file. these people (c programmers) live in wasteland, I > really admire them for the constructive way they use header files, > include files, defines, make files, configure files and what not,and > still manage to write code that is write-once, compile everywhere. this > is something not easily achieved, given the tools they have to work > with. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com
RE: [sqlite] Request for comment: Proposed SQLite API changes
Try using the ANALYZE command prior to querying under the latest version of Sqlite. If that does not work, please consider writing up a small test case and posting it to the mailing list. --- Preston Z <[EMAIL PROTECTED]> wrote: > Any other hints about what is in the works for 3.3.0? I haven't upgraded > past 3.2.2 because the optimizer changes made my application hang and i > haven't had a chance to re-write & test all of my queries. Any changes in > 3.3.0 that would allow me to upgrade to it without re-writing queries? (I > can hope can't I?) __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
Re: [sqlite] Request for comment: Proposed SQLite API changes
Rob Lohman wrote: Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? I'm a big fan of this change. We do quite a lot of bookkeeping in our own code to do exactly the same thing at the moment. It would make me very happy to delete that code. This is not directly a question for you, Eric, but does anyone actually have schema changes on a working database? I've never seen any schema changes on my databases except for a new version, bugfixes etc. Does anyone runs code that actually changes the schema of your database as the normal process in an application? If so, for what reason? In our application, the schema design is not centralized, so portions of the schema (i.e. new table definitions) can be "registered" at different times. We also support schema evolution, which means we must use temporary tables during data migration. So, yes, schema changes are indeed a normal part of our application's running process. -Eric
Re: [sqlite] Request for comment: Proposed SQLite API changes
On 11/3/05, Rob Lohman <[EMAIL PROTECTED]> wrote: > > Does anyone runs code that actually changes the schema of > your database as the normal process in an application? If so, > for what reason? I believe the automatic vacuum can cause a schema change event.
Re: [sqlite] Request for comment: Proposed SQLite API changes
[EMAIL PROTECTED] wrote: What about this change? Is it a worth-while tradeoff? Personally, I would like to see both of these changes.
Re: [sqlite] Request for comment: Proposed SQLite API changes
On Thu, 3 Nov 2005, Rob Lohman wrote: >> >> I'm a big fan of this change. We do quite a lot of bookkeeping in our >> own code to do exactly the same thing at the moment. It would make me >> very happy to delete that code. > >This is not directly a question for you, Eric, but does anyone >actually have schema changes on a working database? I've >never seen any schema changes on my databases except for >a new version, bugfixes etc. > >Does anyone runs code that actually changes the schema of >your database as the normal process in an application? If so, >for what reason? Creating views results in a schema change, I believe. Also, dropping an index to speed up a bulk insert will also change the schema version. > >Rob > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] Request for comment: Proposed SQLite API changes
Yes I use SQLite for replicating a main oracle database. Occasionally (and I mean occasionally) a schema change in Oracle needs to be propagated to the SQLite database. So this situation does arise for me. -Original Message- From: Rob Lohman [mailto:[EMAIL PROTECTED] Sent: 03 November 2005 17:11 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes >> Another proposal: Suppose that when creating an sqlite3_stmt using >> sqlite3_prepare, the original SQL text was stored in the >> sqlite3_stmt. Then when a schema change occurred, the statement was >> automatically recompiled and rebound. There would no more >> SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more >> memory. And sqlite3_step might take a little longer to initialize >> sometimes if it found it needed to rerun the parser. >> >> What about this change? Is it a worth-while tradeoff? > > I'm a big fan of this change. We do quite a lot of bookkeeping in our > own code to do exactly the same thing at the moment. It would make me > very happy to delete that code. This is not directly a question for you, Eric, but does anyone actually have schema changes on a working database? I've never seen any schema changes on my databases except for a new version, bugfixes etc. Does anyone runs code that actually changes the schema of your database as the normal process in an application? If so, for what reason? Rob
Re: [sqlite] Request for comment: Proposed SQLite API changes
Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? I'm a big fan of this change. We do quite a lot of bookkeeping in our own code to do exactly the same thing at the moment. It would make me very happy to delete that code. This is not directly a question for you, Eric, but does anyone actually have schema changes on a working database? I've never seen any schema changes on my databases except for a new version, bugfixes etc. Does anyone runs code that actually changes the schema of your database as the normal process in an application? If so, for what reason? Rob
Re: [sqlite] Request for comment: Proposed SQLite API changes
On Thu, 3 Nov 2005 [EMAIL PROTECTED] wrote: >As currently implemented, when an error occurs during >sqlite3_step(), the function returns SQLITE_ERROR. Then >you have to call either sqlite3_reset() or sqlite3_finalize() >to find the actual error code. Suppose this where to >change in version 3.3.0 so that the actual error code >was returned by sqlite3_step(). That would mean that >moving from version 3.2.7 to 3.3.0 might involve some >minor code changes. The API would not be 100% backwards >compatible. But the API would be cleaner. > >What does the community think about such a change? Not very valuable, IMHO. The current API has a known behaviour, and code either side of the API change is not strictly compatible with the other side. > >Another proposal: Suppose that when creating an >sqlite3_stmt using sqlite3_prepare, the original SQL >text was stored in the sqlite3_stmt. Then when a >schema change occurred, the statement was automatically >recompiled and rebound. There would no more SQLITE_SCHEMA >errors. But sqlite3_stmts would use a little more >memory. And sqlite3_step might take a little longer >to initialize sometimes if it found it needed to rerun >the parser. > >What about this change? Is it a worth-while tradeoff? Elsewhere, it was indicated that the authorizer callback would be called upon recompile. Why would this be a problem? If the statement is recompiled anyway, you'll already get the callback as well. Perhaps a new API function to validate a sqlite3_stmt against the current schema, and recompile if necessary, if recompile should only be done under user code control. > >-- D. Richard Hipp <[EMAIL PROTECTED]> > > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] Request for comment: Proposed SQLite API changes
I would be in favor of both changes. Any other hints about what is in the works for 3.3.0? I haven't upgraded past 3.2.2 because the optimizer changes made my application hang and i haven't had a chance to re-write & test all of my queries. Any changes in 3.3.0 that would allow me to upgrade to it without re-writing queries? (I can hope can't I?) Thanks Preston As currently implemented, when an error occurs during sqlite3_step(), the function returns SQLITE_ERROR. Then you have to call either sqlite3_reset() or sqlite3_finalize() to find the actual error code. Suppose this where to change in version 3.3.0 so that the actual error code was returned by sqlite3_step(). That would mean that moving from version 3.2.7 to 3.3.0 might involve some minor code changes. The API would not be 100% backwards compatible. But the API would be cleaner. What does the community think about such a change? Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser.
RE: [sqlite] Request for comment: Proposed SQLite API changes
Clay, I like stick-shifts, I drive motorcycles *and* stick-shifts, and I was about to buy a Z4, but the wife stopped me (judging, correctly, that it is a girl-trap). I even write assembly now and then, when absolutely needed. I cant say it is fun, though. the thing is, I never have seen a single task implemented in C (not C++ mind you), that can not be implemented in Delphi, with the SAME or better performance, in a way that is 10 times more elegant, much better for maintainance and clarity, and less effort. afaik, the only down-side is that you don't get the delphi compiler in anything else than windows (ok, and linux). other than that, I see absolutely no advantage in the C language, and certainly no "fun" in it... for me, it is as fun as trying to start a fire by rubbing two sticks together. it can be done, and people have been doing it for thousands of years. but that was only because they did not have matches, not because they liked it. so, assuming that you had an efficient portable implementation of object pascal, do you really think *new* developers would opt to use C anyway ? > -Original Message- > From: Clay Dowling [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 7:53 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Request for comment: Proposed SQLite API changes > > > Cariotoglou Mike said: > > > a crappy .H file. these people (c programmers) live in wasteland, I > > really admire them for the constructive way they use header files, > > include files, defines, make files, configure files and > what not,and > > still manage to write code that is write-once, compile everywhere. > > this is something not easily achieved, given the tools they have to > > work with. otoh, I live in delphi land from day 1, and I > *know* what > > they are missing... > > As somebody who lives in both worlds, C land isn't such a bad > place. It's a little like the difference between driving a > nice comfortable Ford Taurus (Delphi) and a BMW Z3 with a > stick shift (if you haven't done it, > do: you probably never realized that driving could be so > fun). I like driving both. But there are certain situations > where one is a lot better than the other, as I'm sure you're aware. > > Clay Dowling > -- > Simple Content Management > http://www.ceamus.com > > > >
Re: [sqlite] Request for comment: Proposed SQLite API changes
[EMAIL PROTECTED] wrote: Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? I'm a big fan of this change. We do quite a lot of bookkeeping in our own code to do exactly the same thing at the moment. It would make me very happy to delete that code. -Eric -- Eric Scouten | [EMAIL PROTECTED] | Photography: www.ericscouten.com
RE: [sqlite] Request for comment: Proposed SQLite API changes
Cariotoglou Mike said: > a crappy .H file. these people (c programmers) live in wasteland, I > really admire them for the constructive way they use header files, > include files, defines, make files, configure files and what not,and > still manage to write code that is write-once, compile everywhere. this > is something not easily achieved, given the tools they have to work > with. otoh, I live in delphi land from day 1, and I *know* what they are > missing... As somebody who lives in both worlds, C land isn't such a bad place. It's a little like the difference between driving a nice comfortable Ford Taurus (Delphi) and a BMW Z3 with a stick shift (if you haven't done it, do: you probably never realized that driving could be so fun). I like driving both. But there are certain situations where one is a lot better than the other, as I'm sure you're aware. Clay Dowling -- Simple Content Management http://www.ceamus.com
RE: [sqlite] Request for comment: Proposed SQLite API changes
since you work in D7, as I do, you already have namespaces (in the form of units), so this was never an issue, even if you wanted to have two versions of the same code built-in. as to why, well, consider a database managent tool that has to open both 2.x and 3.x databases, and the only tool to structure your code and isolate interface from implementation is a crappy .H file. these people (c programmers) live in wasteland, I really admire them for the constructive way they use header files, include files, defines, make files, configure files and what not,and still manage to write code that is write-once, compile everywhere. this is something not easily achieved, given the tools they have to work with. otoh, I live in delphi land from day 1, and I *know* what they are missing... > -Original Message- > From: Fred Williams [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 5:10 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Request for comment: Proposed SQLite API changes > > 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? > > > -Original Message- > > From: Joe Wilson [mailto:[EMAIL PROTECTED] > > Sent: Thursday, November 03, 2005 8:54 AM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] Request for comment: Proposed SQLite > API changes > > > > > > It's a primitive form of namespaces in C. > > Renaming the function calls allows Sqlite2 and Sqlite3 to coexist > > within the same executable/binary. > > > > Mind you, if the functionality of a documented function changes (as > > opposed to merely extended) I would think it would warrent a major > > revision number increase. Sqlite versions 3.1.0 and 3.2.0 did not > > change as radically as is planned for this upcoming release. > > > > --- Fred Williams <[EMAIL PROTECTED]> wrote: > > > > > Just curious, why is this being done anyway? I think this > > is the only > > > software product I have used that has this "feature." I > > fail to see the > > > usefulness from way up here above the source code, and I > ... > > > >
RE: [sqlite] Request for comment: Proposed SQLite API changes
ok, the sqlite_Schema thing can (and has) been wrapped. however, the error code issue is there, I believe that we have agreed in the past that it was bad design, but it could not be changed because of compatibility issues. I,for one, am willing to comb my code and re-code for this, so yes, please do it. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 4:05 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > Dan Kennedy <[EMAIL PROTECTED]> wrote: > > > Another proposal: Suppose that when creating an > sqlite3_stmt using > > > sqlite3_prepare, the original SQL text was stored in the > > > sqlite3_stmt. Then when a schema change occurred, the > statement was > > > automatically recompiled and rebound. > > > > The authorization callback > > would have to be reinvoked from within sqlite3_step() too. > > Yikes! I didn't think of that. This is pretty big negative > and will likely scuttle plans to do automatic re-prepare. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > >
Re: [sqlite] Request for comment: Proposed SQLite API changes
[EMAIL PROTECTED] wrote: As currently implemented, when an error occurs during sqlite3_step(), the function returns SQLITE_ERROR. Then you have to call either sqlite3_reset() or sqlite3_finalize() to find the actual error code. Suppose this where to change in version 3.3.0 so that the actual error code was returned by sqlite3_step(). That would mean that moving from version 3.2.7 to 3.3.0 might involve some minor code changes. The API would not be 100% backwards compatible. But the API would be cleaner. What does the community think about such a change? Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? -- D. Richard Hipp <[EMAIL PROTECTED]> I think both of these proposed changes are useful enhancements to SQLite. I also think it would be better to add a new sqlite3_step_v2() API function that does this. This will eliminate the need to change the base version number, since existing code can continue to use the existing step function. New code, or those who want to modify their existing code, could use the new function. The new step function should be a wrapper around the existing step function in much the same way as sqlite3_exec() wraps the entire prepare, step, finalize process. It would do the enhanced error checks and return the real error, or automatically re-prepare the SQL if the error was SQLITE_SCHEMA. This would give you the best of both worlds, backwards compatibility and a cleaner API for new code. Dennis Cote
RE: [sqlite] Request for comment: Proposed SQLite API changes
"The first proposal seems to me a very good one and contributes to clarity in programs. The second one is of dubious value and a poor tradeoff in my opinion." Hmm. Perhaps. I currently do this in my wrapper anyway and it's annoying, so it's not completely devoid of value. -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 November 2005 15:25 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes The first proposal seems to me a very good one and contributes to clarity in programs. The second one is of dubious value and a poor tradeoff in my opinion. JS [EMAIL PROTECTED] wrote: > As currently implemented, when an error occurs during sqlite3_step(), > the function returns SQLITE_ERROR. Then you have to call either > sqlite3_reset() or sqlite3_finalize() to find the actual error code. > Suppose this where to change in version 3.3.0 so that the actual error > code was returned by sqlite3_step(). That would mean that moving from > version 3.2.7 to 3.3.0 might involve some minor code changes. The API > would not be 100% backwards compatible. But the API would be cleaner. > > What does the community think about such a change? > > Another proposal: Suppose that when creating an sqlite3_stmt using > sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. > Then when a schema change occurred, the statement was automatically > recompiled and rebound. There would no more SQLITE_SCHEMA errors. > But sqlite3_stmts would use a little more memory. And sqlite3_step > might take a little longer to initialize sometimes if it found it > needed to rerun the parser. > > What about this change? Is it a worth-while tradeoff? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] Request for comment: Proposed SQLite API changes
The first proposal seems to me a very good one and contributes to clarity in programs. The second one is of dubious value and a poor tradeoff in my opinion. JS [EMAIL PROTECTED] wrote: As currently implemented, when an error occurs during sqlite3_step(), the function returns SQLITE_ERROR. Then you have to call either sqlite3_reset() or sqlite3_finalize() to find the actual error code. Suppose this where to change in version 3.3.0 so that the actual error code was returned by sqlite3_step(). That would mean that moving from version 3.2.7 to 3.3.0 might involve some minor code changes. The API would not be 100% backwards compatible. But the API would be cleaner. What does the community think about such a change? Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Request for comment: Proposed SQLite API changes
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? > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 8:54 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Request for comment: Proposed SQLite API changes > > > It's a primitive form of namespaces in C. > Renaming the function calls allows Sqlite2 and Sqlite3 to coexist > within the same executable/binary. > > Mind you, if the functionality of a documented function changes > (as opposed to merely extended) I would think it would warrent > a major revision number increase. Sqlite versions 3.1.0 and 3.2.0 > did not change as radically as is planned for this upcoming release. > > --- Fred Williams <[EMAIL PROTECTED]> wrote: > > > Just curious, why is this being done anyway? I think this > is the only > > software product I have used that has this "feature." I > fail to see the > > usefulness from way up here above the source code, and I ...
RE: [sqlite] Request for comment: Proposed SQLite API changes
Ah, that explains why I get all those bugs in each new release of Oracle :-) > -Original Message- > From: Jay Sprenkle [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 8:44 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > > > Just curious, why is this being done anyway? I think this > is the only > > software product I have used that has this "feature." I > fail to see the > > usefulness from way up here above the source code, and I > don't have time > > to look deeper. But still curious as to why everybody has > to rename all > > their function calls between major releases. > > > > I'm on Delphi 7.0 and have never been forced to change > anything because > > of a new release, since Delphi 1.0. And closer to home, From PC DOS > > Oracle to 8.0i Oracle. Seems like a lot of additional > work, and would > > like to understand why. > > This prevents existing code from being broken in subtle ways. > If you get the new version it won't compile, rather than giving you an > application that seems to work without very in depth testing.
RE: [sqlite] Request for comment: Proposed SQLite API changes
It's a primitive form of namespaces in C. Renaming the function calls allows Sqlite2 and Sqlite3 to coexist within the same executable/binary. Mind you, if the functionality of a documented function changes (as opposed to merely extended) I would think it would warrent a major revision number increase. Sqlite versions 3.1.0 and 3.2.0 did not change as radically as is planned for this upcoming release. --- Fred Williams <[EMAIL PROTECTED]> wrote: > Just curious, why is this being done anyway? I think this is the only > software product I have used that has this "feature." I fail to see the > usefulness from way up here above the source code, and I don't have time > to look deeper. But still curious as to why everybody has to rename all > their function calls between major releases. > > I'm on Delphi 7.0 and have never been forced to change anything because > of a new release, since Delphi 1.0. And closer to home, From PC DOS > Oracle to 8.0i Oracle. Seems like a lot of additional work, and would > like to understand why. > > Fred > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Thursday, November 03, 2005 7:56 AM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > > > > > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > > > With the recent numeric/integer/division change, the working > > > check clause and this proposed API changed shouldn't the version > > > number should be bumped to 4.0.0 to indicate incompatibility with > > > past versions? > > > > > > > That would compel me to change *all* of the API to use a > > "sqlite4_" prefix instead of "sqlite3_". > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com
Re: [sqlite] Request for comment: Proposed SQLite API changes
> Just curious, why is this being done anyway? I think this is the only > software product I have used that has this "feature." I fail to see the > usefulness from way up here above the source code, and I don't have time > to look deeper. But still curious as to why everybody has to rename all > their function calls between major releases. > > I'm on Delphi 7.0 and have never been forced to change anything because > of a new release, since Delphi 1.0. And closer to home, From PC DOS > Oracle to 8.0i Oracle. Seems like a lot of additional work, and would > like to understand why. This prevents existing code from being broken in subtle ways. If you get the new version it won't compile, rather than giving you an application that seems to work without very in depth testing.
RE: [sqlite] Request for comment: Proposed SQLite API changes
Just curious, why is this being done anyway? I think this is the only software product I have used that has this "feature." I fail to see the usefulness from way up here above the source code, and I don't have time to look deeper. But still curious as to why everybody has to rename all their function calls between major releases. I'm on Delphi 7.0 and have never been forced to change anything because of a new release, since Delphi 1.0. And closer to home, From PC DOS Oracle to 8.0i Oracle. Seems like a lot of additional work, and would like to understand why. Fred > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 7:56 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > With the recent numeric/integer/division change, the working > > check clause and this proposed API changed shouldn't the version > > number should be bumped to 4.0.0 to indicate incompatibility with > > past versions? > > > > That would compel me to change *all* of the API to use a > "sqlite4_" prefix instead of "sqlite3_". > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] Request for comment: Proposed SQLite API changes
On 11/3/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > With the recent numeric/integer/division change, the working > > check clause and this proposed API changed shouldn't the version > > number should be bumped to 4.0.0 to indicate incompatibility with > > past versions? > > > > That would compel me to change *all* of the API to use a > "sqlite4_" prefix instead of "sqlite3_". Two thumbs up! Is the old sqlite3 going to be removed?
Re: [sqlite] Request for comment: Proposed SQLite API changes
Dan Kennedy <[EMAIL PROTECTED]> wrote: > > Another proposal: Suppose that when creating an > > sqlite3_stmt using sqlite3_prepare, the original SQL > > text was stored in the sqlite3_stmt. Then when a > > schema change occurred, the statement was automatically > > recompiled and rebound. > > The authorization callback > would have to be reinvoked from within sqlite3_step() too. Yikes! I didn't think of that. This is pretty big negative and will likely scuttle plans to do automatic re-prepare. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Request for comment: Proposed SQLite API changes
Joe Wilson <[EMAIL PROTECTED]> wrote: > > With the recent numeric/integer/division change, the working > check clause and this proposed API changed shouldn't the version > number should be bumped to 4.0.0 to indicate incompatibility with > past versions? > That would compel me to change *all* of the API to use a "sqlite4_" prefix instead of "sqlite3_". -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Request for comment: Proposed SQLite API changes
> Another proposal: Suppose that when creating an > sqlite3_stmt using sqlite3_prepare, the original SQL > text was stored in the sqlite3_stmt. Then when a > schema change occurred, the statement was automatically > recompiled and rebound. This seems pretty handy, but it can be done in a wrapper. It would eliminate SQLITE_SCHEMA, but the idea that the structure of the query can change between compilation and execution, or between execution runs is a bit scary. If it's a "SELECT * FROM" query the number of columns returned might change. The authorization callback would have to be reinvoked from within sqlite3_step() too. I think this is one that requires pretty careful consideration. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
RE: [sqlite] Request for comment: Proposed SQLite API changes
I'm for both changes... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 03 November 2005 13:18 To: sqlite-users@sqlite.org Subject: [sqlite] Request for comment: Proposed SQLite API changes As currently implemented, when an error occurs during sqlite3_step(), the function returns SQLITE_ERROR. Then you have to call either sqlite3_reset() or sqlite3_finalize() to find the actual error code. Suppose this where to change in version 3.3.0 so that the actual error code was returned by sqlite3_step(). That would mean that moving from version 3.2.7 to 3.3.0 might involve some minor code changes. The API would not be 100% backwards compatible. But the API would be cleaner. What does the community think about such a change? Another proposal: Suppose that when creating an sqlite3_stmt using sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt. Then when a schema change occurred, the statement was automatically recompiled and rebound. There would no more SQLITE_SCHEMA errors. But sqlite3_stmts would use a little more memory. And sqlite3_step might take a little longer to initialize sometimes if it found it needed to rerun the parser. What about this change? Is it a worth-while tradeoff? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Request for comment: Proposed SQLite API changes
I vote for the cleaner API changes even though they are not 100% backwards compatible. Alternatively you could have a brand new step function, leaving the old versions with the same functionality. With the recent numeric/integer/division change, the working check clause and this proposed API changed shouldn't the version number should be bumped to 4.0.0 to indicate incompatibility with past versions? --- [EMAIL PROTECTED] wrote: > As currently implemented, when an error occurs during > sqlite3_step(), the function returns SQLITE_ERROR. Then > you have to call either sqlite3_reset() or sqlite3_finalize() > to find the actual error code. Suppose this where to > change in version 3.3.0 so that the actual error code > was returned by sqlite3_step(). That would mean that > moving from version 3.2.7 to 3.3.0 might involve some > minor code changes. The API would not be 100% backwards > compatible. But the API would be cleaner. > > What does the community think about such a change? > > Another proposal: Suppose that when creating an > sqlite3_stmt using sqlite3_prepare, the original SQL > text was stored in the sqlite3_stmt. Then when a > schema change occurred, the statement was automatically > recompiled and rebound. There would no more SQLITE_SCHEMA > errors. But sqlite3_stmts would use a little more > memory. And sqlite3_step might take a little longer > to initialize sometimes if it found it needed to rerun > the parser. > > What about this change? Is it a worth-while tradeoff? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com
