[sqlite] API enhancement proposal
I'd like to propose the following simple piece of code be added to sqlite. I
believe it has some benefits for those who've wrapped the sqlite api's keeping
copies in memory of the sql statement being executed.
Add an api call that will return the saved SQL if using sqlite3_prepare_v2 or
NULL if using sqlite3_prepare:
char *
sqlite3_stmt_sql(sqlite3_stmt* pStmt ) {
return( sqlite3VdbeGetSql( (Vdbe *) pStmt) );
}
I Kenneth Long,
Contribute the above to the public domain.
Re: [sqlite] API enhancement
Partitioning an API at the wrong level punishes users who have carefully
structured interfaces by needlessly bloating their code. It is hard to
have a "Lite" embedded application when code bloat swells the library
routines.
Ken wrote:
It should save some time. How much is questionable.
Why would sqlite have to bind the Pointer bound variables? Isn't the strategy of binding to associate a variable with a statment? Why should I have to continually re-associate the bindings with a statement thats allready been prepared and bound, just to execute it again after a reset ?
I guess I'm a bit confused, I'll look at the bind code in sqlite some more.
Scott Hess <[EMAIL PROTECTED]> wrote: I don't see how your modified version is
any better than just putting
the sqlite3_bind_int() inside the loop. You've superficially lifted
some code out of the loop, but sqlite3_step() is going to have to go
through and bind all of the "pointer bound" variables in your
suggested API, so it won't save you anything in the end.
-scott
On 3/19/07, ken-33 wrote:
Anyone thoughts?
ken-33 wrote:
Question for the list,
I'd like to optimize my code, using the following pseudo code as an
example.
===
int i = 0 ;
char str[20];
sqlite3_prepare_v2( "insert into t1 values (?,?)" )
sqlite3_bind_int ( i )
sqlite3_bind_text(str)
BEGIN TRANSACTION
For (i = 0; i < 10; i++) {
sqlite3_step ( );
sqlite3_reset( )
}
COMMIT TRANSACTION
==
However, the above code will fail to insert the values for i in the loop.
It will only insert the value 0, since that was the binding value...
An enhancement request would be to allow the user to bind the address to
the statement objects. This would be a huge benefit from the standpoint
of fewer function calls to sqlite3_bind in the inside loop.
So maybe the following API:
sqlite3_pbind_int(sqlite3_stmt *, int, int * );
sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
void(*)(void*));
notice the text takes a pointer to the length...
sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
void(*)(void*));
Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
SQLITE_STATIC.
Regards,
Ken
--
View this message in context:
http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] API enhancement
Denis, Thanks for the great explanation !!! Regards, Ken Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > It should save some time. How much is questionable. > > Why would sqlite have to bind the Pointer bound variables? Isn't the strategy > of binding to associate a variable with a statment? Why should I have to > continually re-associate the bindings with a statement thats allready been > prepared and bound, just to execute it again after a reset ? > > I guess I'm a bit confused, I'll look at the bind code in sqlite some more. > > > > > Ken, Your idea could save some time but it would require adding a new class of indirect variables (references) to sqlite. The VDBE opcode that loads the variable values would have to be changed to recognize the indirect variables and then create an internal sqlite variable that can be pushed onto the VDBE stack from the external variable. The last part is the same function that the bind routines perform. The bind APIs are fairly lightweight functions, basically just saving the value passed into an internal array. Your scheme would only be saving the overhead of the internal copy operation (from the variable to the stack during the op_variable opcode) and the call to the bind function itself. This scheme would also be adding the cost of the variable type check to every variable lookup. There is also the distinct possibility that a variable may be dereferenced more than once while executing a statement, and this would involve duplicating the work of creating the internal variable from the external memory. There is also the possibility of some nasty SQL bugs due to the value off a variable being changed during the execution of a statement. All in all I don't think the payback is large enough to justify the extra complexity and increased code size in the general case. If you have an application where the overhead of the bind functions calls are a real issue, you could of course create a custom version of sqlite that implements your idea. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] API enhancement
Ken wrote: It should save some time. How much is questionable. Why would sqlite have to bind the Pointer bound variables? Isn't the strategy of binding to associate a variable with a statment? Why should I have to continually re-associate the bindings with a statement thats allready been prepared and bound, just to execute it again after a reset ? I guess I'm a bit confused, I'll look at the bind code in sqlite some more. Ken, Your idea could save some time but it would require adding a new class of indirect variables (references) to sqlite. The VDBE opcode that loads the variable values would have to be changed to recognize the indirect variables and then create an internal sqlite variable that can be pushed onto the VDBE stack from the external variable. The last part is the same function that the bind routines perform. The bind APIs are fairly lightweight functions, basically just saving the value passed into an internal array. Your scheme would only be saving the overhead of the internal copy operation (from the variable to the stack during the op_variable opcode) and the call to the bind function itself. This scheme would also be adding the cost of the variable type check to every variable lookup. There is also the distinct possibility that a variable may be dereferenced more than once while executing a statement, and this would involve duplicating the work of creating the internal variable from the external memory. There is also the possibility of some nasty SQL bugs due to the value off a variable being changed during the execution of a statement. All in all I don't think the payback is large enough to justify the extra complexity and increased code size in the general case. If you have an application where the overhead of the bind functions calls are a real issue, you could of course create a custom version of sqlite that implements your idea. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] API enhancement
It should save some time. How much is questionable.
Why would sqlite have to bind the Pointer bound variables? Isn't the strategy
of binding to associate a variable with a statment? Why should I have to
continually re-associate the bindings with a statement thats allready been
prepared and bound, just to execute it again after a reset ?
I guess I'm a bit confused, I'll look at the bind code in sqlite some more.
Scott Hess <[EMAIL PROTECTED]> wrote: I don't see how your modified version is
any better than just putting
the sqlite3_bind_int() inside the loop. You've superficially lifted
some code out of the loop, but sqlite3_step() is going to have to go
through and bind all of the "pointer bound" variables in your
suggested API, so it won't save you anything in the end.
-scott
On 3/19/07, ken-33 wrote:
>
> Anyone thoughts?
>
>
> ken-33 wrote:
> >
> > Question for the list,
> >
> > I'd like to optimize my code, using the following pseudo code as an
> > example.
> >
> > ===
> > int i = 0 ;
> > char str[20];
> >
> > sqlite3_prepare_v2( "insert into t1 values (?,?)" )
> > sqlite3_bind_int ( i )
> > sqlite3_bind_text(str)
> >
> > BEGIN TRANSACTION
> > For (i = 0; i < 10; i++) {
> >sqlite3_step ( );
> >sqlite3_reset( )
> > }
> > COMMIT TRANSACTION
> > ==
> >
> > However, the above code will fail to insert the values for i in the loop.
> > It will only insert the value 0, since that was the binding value...
> >
> > An enhancement request would be to allow the user to bind the address to
> > the statement objects. This would be a huge benefit from the standpoint
> > of fewer function calls to sqlite3_bind in the inside loop.
> >
> > So maybe the following API:
> >
> > sqlite3_pbind_int(sqlite3_stmt *, int, int * );
> > sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
> > sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
> > sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
> > void(*)(void*));
> > notice the text takes a pointer to the length...
> > sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
> > void(*)(void*));
> >
> > Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
> > SQLITE_STATIC.
> >
> > Regards,
> > Ken
> >
> >
> >
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
> Sent from the SQLite mailing list archive at Nabble.com.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] API enhancement
ken-33 <[EMAIL PROTECTED]> wrote:
> Anyone thoughts?
>
I would rather not complicate the existing API
unnecessarily by add bells and whistles that can
be easily implemented using simple by wrappers.
>
> ken-33 wrote:
> >
> > Question for the list,
> >
> > I'd like to optimize my code, using the following pseudo code as an
> > example.
> >
> > ===
> > int i = 0 ;
> > char str[20];
> >
> > sqlite3_prepare_v2( "insert into t1 values (?,?)" )
> > sqlite3_bind_int ( i )
> > sqlite3_bind_text(str)
> >
> > BEGIN TRANSACTION
> > For (i = 0; i < 10; i++) {
> >sqlite3_step ( );
> >sqlite3_reset( )
> > }
> > COMMIT TRANSACTION
> > ==
> >
> > However, the above code will fail to insert the values for i in the loop.
> > It will only insert the value 0, since that was the binding value...
> >
> > An enhancement request would be to allow the user to bind the address to
> > the statement objects. This would be a huge benefit from the standpoint
> > of fewer function calls to sqlite3_bind in the inside loop.
> >
> > So maybe the following API:
> >
> > sqlite3_pbind_int(sqlite3_stmt *, int, int * );
> > sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
> > sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
> > sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
> > void(*)(void*));
> > notice the text takes a pointer to the length...
> > sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
> > void(*)(void*));
> >
> > Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
> > SQLITE_STATIC.
> >
> > Regards,
> > Ken
> >
> >
> >
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
> Sent from the SQLite mailing list archive at Nabble.com.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
> .
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] API enhancement
Anyone thoughts?
ken-33 wrote:
>
> Question for the list,
>
> I'd like to optimize my code, using the following pseudo code as an
> example.
>
> ===
> int i = 0 ;
> char str[20];
>
> sqlite3_prepare_v2( "insert into t1 values (?,?)" )
> sqlite3_bind_int ( i )
> sqlite3_bind_text(str)
>
> BEGIN TRANSACTION
> For (i = 0; i < 10; i++) {
>sqlite3_step ( );
>sqlite3_reset( )
> }
> COMMIT TRANSACTION
> ==
>
> However, the above code will fail to insert the values for i in the loop.
> It will only insert the value 0, since that was the binding value...
>
> An enhancement request would be to allow the user to bind the address to
> the statement objects. This would be a huge benefit from the standpoint
> of fewer function calls to sqlite3_bind in the inside loop.
>
> So maybe the following API:
>
> sqlite3_pbind_int(sqlite3_stmt *, int, int * );
> sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
> sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
> sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
> void(*)(void*));
> notice the text takes a pointer to the length...
> sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
> void(*)(void*));
>
> Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
> SQLITE_STATIC.
>
> Regards,
> Ken
>
>
>
>
>
--
View this message in context:
http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] API enhancement
I don't see how your modified version is any better than just putting
the sqlite3_bind_int() inside the loop. You've superficially lifted
some code out of the loop, but sqlite3_step() is going to have to go
through and bind all of the "pointer bound" variables in your
suggested API, so it won't save you anything in the end.
-scott
On 3/19/07, ken-33 <[EMAIL PROTECTED]> wrote:
Anyone thoughts?
ken-33 wrote:
>
> Question for the list,
>
> I'd like to optimize my code, using the following pseudo code as an
> example.
>
> ===
> int i = 0 ;
> char str[20];
>
> sqlite3_prepare_v2( "insert into t1 values (?,?)" )
> sqlite3_bind_int ( i )
> sqlite3_bind_text(str)
>
> BEGIN TRANSACTION
> For (i = 0; i < 10; i++) {
>sqlite3_step ( );
>sqlite3_reset( )
> }
> COMMIT TRANSACTION
> ==
>
> However, the above code will fail to insert the values for i in the loop.
> It will only insert the value 0, since that was the binding value...
>
> An enhancement request would be to allow the user to bind the address to
> the statement objects. This would be a huge benefit from the standpoint
> of fewer function calls to sqlite3_bind in the inside loop.
>
> So maybe the following API:
>
> sqlite3_pbind_int(sqlite3_stmt *, int, int * );
> sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
> sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
> sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
> void(*)(void*));
> notice the text takes a pointer to the length...
> sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
> void(*)(void*));
>
> Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
> SQLITE_STATIC.
>
> Regards,
> Ken
>
>
>
>
>
--
View this message in context:
http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] API enhancement
Anyone
Ken <[EMAIL PROTECTED]> wrote: Question for the list,
I'd like to optimize my code, using the following pseudo code as an example.
===
int i = 0 ;
char str[20];
sqlite3_prepare_v2( "insert into t1 values (?,?)" )
sqlite3_bind_int ( i )
sqlite3_bind_text(str)
BEGIN TRANSACTION
For (i = 0; i < 10; i++) {
sqlite3_step ( );
sqlite3_reset( )
}
COMMIT TRANSACTION
==
However, the above code will fail to insert the values for i in the loop. It
will only insert the value 0, since that was the binding value...
An enhancement request would be to allow the user to bind the address to the
statement objects. This would be a huge benefit from the standpoint of fewer
function calls to sqlite3_bind in the inside loop.
So maybe the following API:
sqlite3_pbind_int(sqlite3_stmt *, int, int * );
sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *, void(*)(void*));
notice the text takes a pointer to the length...
sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *, void(*)(void*));
Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
SQLITE_STATIC.
Regards,
Ken
[sqlite] API enhancement
Question for the list,
I'd like to optimize my code, using the following pseudo code as an example.
===
int i = 0 ;
char str[20];
sqlite3_prepare_v2( "insert into t1 values (?,?)" )
sqlite3_bind_int ( i )
sqlite3_bind_text(str)
BEGIN TRANSACTION
For (i = 0; i < 10; i++) {
sqlite3_step ( );
sqlite3_reset( )
}
COMMIT TRANSACTION
==
However, the above code will fail to insert the values for i in the loop. It
will only insert the value 0, since that was the binding value...
An enhancement request would be to allow the user to bind the address to the
statement objects. This would be a huge benefit from the standpoint of fewer
function calls to sqlite3_bind in the inside loop.
So maybe the following API:
sqlite3_pbind_int(sqlite3_stmt *, int, int * );
sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *, void(*)(void*));
notice the text takes a pointer to the length...
sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *, void(*)(void*));
Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
SQLITE_STATIC.
Regards,
Ken
