Re: [sqlite] Multiple prepared queries
For anyone who has the same questions I had, I've found most of the answers. PS. Excellent internal documentation of the VDBE (though a bit out-of- date)! It made it really easy to navigate the source. On Feb 12, 2007, at 1:51 PM, Wesley W. Terpstra wrote: What is the defined behaviour of interleaved inserts and selects? Any open select statements must have executed a 'Callback' opcode. An insert at this point will complete in one step (unless there's an error). Therefore, when the select resumes, any open cursors will simply walk on to the next value (possibly the new one) with no problems. Drop table fails with SQLITE_LOCKED if your try to use it with an in- progress query. Will an UPDATE ... WHERE ... also *always* complete in a single step? Yes. there some sort of 'open transaction counter'? ie: if I begin&commit within the scope of an 'automatic' transaction, it will not commit until the automatic transaction completes? There is no counter. Instead, each query locks as it needs to and releases on Halt. Begin/Commit simply toggle the autocommit flag to off and on. If running statements have not completed, they still have their locks. So a begin statement makes the locks retained until the commit/rollback unset the flag. Suppose I ran: begin, select *, step1, commit, step2. Does the commit fail because there is still a nested, active query? Yes. The commit will fail with cannot commit/rollback transaction - SQL statement in progress and SQLITE_ERROR. if I am running a "select * from giant_table;" and find on row 10/800 the data I was looking for, I might want to just finalize the select statement. My question is what happens to an explicitly opened transaction that included this select statement? The documentation implies that the transaction will be aborted, which is not very desirable. Stopping the query will not affect the autocommit flag. So the containing transaction is not aborted. It is impossible to stop an update/insert as they never invoke Callback. Therefore, there is no problem. Stopping a query is harmless to the forward progress of a transaction. Now that I'm working on wrapping bind_text/bind_text16, I have another question: what is the native on-disk format of the strings? The documentation suggests that open/open16 determine the encoding. db_enc determines the encoding of the values used on the stack. Conversion does happen, so just use UTF-8 everywhere and there will be none. It's ackward that there is no separation between a compiled VM and a compiled VM's state. ie: If I want to run the same precompiled query twice in parallel (since this is apparently well defined), then I would need two separate instances of the state. There is this separation within sqlite3. It's just not exposed to the user. However, nearly all of a Vdbe structure has to do with state information. While it would be possible to cache the parsed query, the cost of creating a Vdbe structure is probably high enough that the savings are not worthwhile. TBH, it would be more helpful if there was an intermediate form between prepare and the stmt. This looks quite easy to implement. However, it's probably a red herring. It seems the right solution to my problem is to keep a pool of prepared queries in the 'factory'. When all of them are in use, create a new one from the query string, and add it to the pool. This allows re-entrant use of the same query, and both can iterate correctly and independently. Sometimes silence is the right answer. Thanks drh! The well thought out concurrency makes me feel warm and fuzzy inside. sqlite3 will look after my bits. :-) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple prepared queries
The basis of our business has been writing compilers and run time packages to integrate legacy and newer software. We discovered that basic computer science provides the answers and looking beyond the paradigm of the legacy language or system is essential if a complexity chain reaction is to be avoided. Good luck with your project. Wesley W. Terpstra wrote: On Feb 12, 2007, at 7:32 PM, John Stanton wrote: I suggest that you also look carefully at the manifest typing implemented in Sqlite. If your language is strongly typed you will have some design issues to address. I am aware of this issue and already have a solution. It's part of why I wanted precompiled queries to be distinct from their execution state: val AgeQuery = query db "select name, age from users where age="iI";" oS oI $ will bind AgeQuery to a query factory that takes an integer as input (the iI) and outputs a (string, integer) pair (from the oS oI). The '$' is a terminating function. This already works fine. Using a technique called functional unparsing, the above line constructs a function (hidden behind the query type) that converts+binds arguments and fetchs+converts result columms. However, for some compiler implementations (not MLton), this dynamic creation of a function is costly, so it should be done once per 'query template'. This binding should be good for more than just my favourite of the many SML compilers. sqlite3_exec ... implements callbacks and that you implement a callback to handle each row. Then the logic of your interface is elegant. I already can do the functional equivalents. There is no real need for a 'callback function' in a functional language. Functions can be passed around like any other argument. In the following code snippet, the function f takes the (string, integer) pairs output by the query and creates a new string. The map function takes the query result set and feeds it through this function. The number '6' is the parameter which is bound to the query's input. fun f (s & i) = "User " ^ s ^ " has age: " ^ Int.toString i val results = SQL.map f AgeQuery 6 The value results will now contain a 'string vector' with entries like "User Tom has age: 6", "User Joe has age: 6", ... The problem I have stems from sqlite3_stmt containing both the parsed query and the query state. In a binding like the above, these would ideally happen at two distinct points. 1. query parsing happens in the 'val AgeQuery = query ...' line 2. query state is created in the 'SQL.map f AgeQuery' line Then the user function 'f' can quite happily re-use AgeQuery to create a recursive use of the query, but with different state (arguments and stack). Without this separation, I am forced to choose to do both at step 1 or both at step 2: 1. both query state creation and parsing happen at 'val AgeQuery = query ...'. This means that an attempt by 'f' to reuse AgeQuery must result in an exception, as it would require two instances of the AgeQuery state. 2. both query state creation and parsing happen at 'SQL.map f AgeQuery'. This would allow 'f' to reuse AgeQuery, but it would mean that every execution reparsed the query string. The documentation implies this is unacceptably slow in some situations. As MLton- compiled executables are about as fast as C [1], too slow for C is too slow for SML. If there were a separation between the execution state of a query, and the prepared form of a query, then this would be a perfect fit. At the moment, I take option #1. Now that I know SQLite can handle subqueries, I would like to support this. It would be a shame to prohibit recursive use of the same query. I could also try hacking a hybrid approach. In this case, I mark the AgeQuery as 'in-use' during the SQL.map. If a nested use of the query occurs, I could clone the query, and use this clone for the nested execution. From looking at the SQLite3 source code, it seems a clone method would be relatively easy to add. I could also implement the clone functionality in SML by re-executing prepare_v2 with a saved copy of the query string. You will find on reflection that the Sqlite API is simple and elegant I didn't argue that it's inelegant. I *do* think it's a tad too simple. The state of a query and the bytecode are combined into one concept. It might be convenient most of the time, but it is a confusion of two distinct things. You can run the same program more than once in Unix. You can call the same method twice (even recursively) in every programming language. I can't think of many places where code must be one-to-one with data. and will let you craft your interface provided you design it to suit Sqlite, not have a preconceived notion and then be unable to make the connection. That's a valid point. I do have a preconceived notion of how this should work. However, it comes from the standard idioms used in
Re: [sqlite] Multiple prepared queries
On Feb 12, 2007, at 7:32 PM, John Stanton wrote: I suggest that you also look carefully at the manifest typing implemented in Sqlite. If your language is strongly typed you will have some design issues to address. I am aware of this issue and already have a solution. It's part of why I wanted precompiled queries to be distinct from their execution state: val AgeQuery = query db "select name, age from users where age="iI";" oS oI $ will bind AgeQuery to a query factory that takes an integer as input (the iI) and outputs a (string, integer) pair (from the oS oI). The '$' is a terminating function. This already works fine. Using a technique called functional unparsing, the above line constructs a function (hidden behind the query type) that converts+binds arguments and fetchs+converts result columms. However, for some compiler implementations (not MLton), this dynamic creation of a function is costly, so it should be done once per 'query template'. This binding should be good for more than just my favourite of the many SML compilers. sqlite3_exec ... implements callbacks and that you implement a callback to handle each row. Then the logic of your interface is elegant. I already can do the functional equivalents. There is no real need for a 'callback function' in a functional language. Functions can be passed around like any other argument. In the following code snippet, the function f takes the (string, integer) pairs output by the query and creates a new string. The map function takes the query result set and feeds it through this function. The number '6' is the parameter which is bound to the query's input. fun f (s & i) = "User " ^ s ^ " has age: " ^ Int.toString i val results = SQL.map f AgeQuery 6 The value results will now contain a 'string vector' with entries like "User Tom has age: 6", "User Joe has age: 6", ... The problem I have stems from sqlite3_stmt containing both the parsed query and the query state. In a binding like the above, these would ideally happen at two distinct points. 1. query parsing happens in the 'val AgeQuery = query ...' line 2. query state is created in the 'SQL.map f AgeQuery' line Then the user function 'f' can quite happily re-use AgeQuery to create a recursive use of the query, but with different state (arguments and stack). Without this separation, I am forced to choose to do both at step 1 or both at step 2: 1. both query state creation and parsing happen at 'val AgeQuery = query ...'. This means that an attempt by 'f' to reuse AgeQuery must result in an exception, as it would require two instances of the AgeQuery state. 2. both query state creation and parsing happen at 'SQL.map f AgeQuery'. This would allow 'f' to reuse AgeQuery, but it would mean that every execution reparsed the query string. The documentation implies this is unacceptably slow in some situations. As MLton- compiled executables are about as fast as C [1], too slow for C is too slow for SML. If there were a separation between the execution state of a query, and the prepared form of a query, then this would be a perfect fit. At the moment, I take option #1. Now that I know SQLite can handle subqueries, I would like to support this. It would be a shame to prohibit recursive use of the same query. I could also try hacking a hybrid approach. In this case, I mark the AgeQuery as 'in-use' during the SQL.map. If a nested use of the query occurs, I could clone the query, and use this clone for the nested execution. From looking at the SQLite3 source code, it seems a clone method would be relatively easy to add. I could also implement the clone functionality in SML by re-executing prepare_v2 with a saved copy of the query string. You will find on reflection that the Sqlite API is simple and elegant I didn't argue that it's inelegant. I *do* think it's a tad too simple. The state of a query and the bytecode are combined into one concept. It might be convenient most of the time, but it is a confusion of two distinct things. You can run the same program more than once in Unix. You can call the same method twice (even recursively) in every programming language. I can't think of many places where code must be one-to-one with data. and will let you craft your interface provided you design it to suit Sqlite, not have a preconceived notion and then be unable to make the connection. That's a valid point. I do have a preconceived notion of how this should work. However, it comes from the standard idioms used in this language, which (if at all possible) i would like to preserve. [1] http://shootout.alioth.debian.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple prepared queries
I wasn't suggesting that you use sqlite3_exec. On the contrary I would counsel you not to. I just suggested that you look to see how it implements callbacks and that you implement a callback to handle each row. Then the logic of your interface is elegant. If you are creating a language interface, you have assumed responsibility to make it work seamlessly and flawlessly. You will find on reflection that the Sqlite API is simple and elegant and will let you craft your interface provided you design it to suit Sqlite, not have a preconceived notion and then be unable to make the connection. I understood quite well what you are trying to achieve and suggested that you rethink your strategy. I suggest that you also look carefully at the manifest typing implemented in Sqlite. If your language is strongly typed you will have some design issues to address. Wesley W. Terpstra wrote: On Feb 12, 2007, at 3:22 PM, John Stanton wrote: Look at how Sqlite implements callbacks in sqlite3_exec to discover a way to implement your language. I had already looked at it. I will be using the prepare_v2/bind/step/ reset/.../finalize interface instead. The exec interface is too simplified. Incidentally, SML is not my language. It has been standardized since 1997. Doing that will ensure that the transactions are atomic and that database integrity is maintained. How are ACID properties in any way my responsibility? They are SQLite's. No offence, but I think you've completely missed my point. My questions had to do with how the SQLite interface behaves in cases that are undocumented in the C API (which I have read completely, several times). I need to know exactly which sequence of method calls are allowed, in order to bind the library in such a way that the type checker in SML will ensure only correct use. SML type-safety is much stronger than in C. If a program compiles, it is guaranteed not to segfault/etc. Therefore, binding a C library must be done carefully to preserve this guarantee. The problem I was talking about comes from (what I see as) a deficiency in the SQLite3 API. That is, the compiled query and it's state are associated with the same opaque data structure. Switching to using exec will not solve this. It looks like I'll have to do a SQLite3 source dive to see how hard it would be to separate state from statements. If I can't do this, then I will have to settle for a less elegant binding, without precompiled queries. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple prepared queries
Wesley W. Terpstra wrote:
val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
val Iterator1b = SQL.execute Query1 ("foobar" & 3)
val Iterator2 = SQL.execute Query2 4
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1b () of
NONE => print "End of the table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
The point is that here I use the same prepared query twice, with two
different associated states and bindings. It seems this sort of
interface is impossible at the moment without reparsing the SQL
statement again. Even if I choose not to expose the 'step' method as
an iterator, during the processing of a query's results there's
nothing I can do to prevent a user from executing the query again.
Wesley,
You will have to explain how SML works.
In your example, what exactly does this statement Do?
val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
Does it execute the query to completion, does it only bind the
parameters, or does it also take the first step? If the first, then you
are simply iterating through the results and your queries are in fact
not executing at the same time. If the second or third are true it seem
to me you have a mismatch between your execute semantics and sqlite's
prepare, bind, and step semantics.
I am assuming that the Iterator1a() type "calls" are stepping the query
and returning the next result row using the sqlite_column_* API functions.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Multiple prepared queries
On Feb 12, 2007, at 3:22 PM, John Stanton wrote: Look at how Sqlite implements callbacks in sqlite3_exec to discover a way to implement your language. I had already looked at it. I will be using the prepare_v2/bind/step/ reset/.../finalize interface instead. The exec interface is too simplified. Incidentally, SML is not my language. It has been standardized since 1997. Doing that will ensure that the transactions are atomic and that database integrity is maintained. How are ACID properties in any way my responsibility? They are SQLite's. No offence, but I think you've completely missed my point. My questions had to do with how the SQLite interface behaves in cases that are undocumented in the C API (which I have read completely, several times). I need to know exactly which sequence of method calls are allowed, in order to bind the library in such a way that the type checker in SML will ensure only correct use. SML type-safety is much stronger than in C. If a program compiles, it is guaranteed not to segfault/etc. Therefore, binding a C library must be done carefully to preserve this guarantee. The problem I was talking about comes from (what I see as) a deficiency in the SQLite3 API. That is, the compiled query and it's state are associated with the same opaque data structure. Switching to using exec will not solve this. It looks like I'll have to do a SQLite3 source dive to see how hard it would be to separate state from statements. If I can't do this, then I will have to settle for a less elegant binding, without precompiled queries. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple prepared queries
Wesley W. Terpstra wrote:
On Feb 12, 2007, at 1:21 AM, John Stanton wrote:
Prepared queries are actually the query compiled into the
metalanguage, VDBE. You can have many statements in one transaction,
and each of those statements can be reusing a VDBE virtual machine
instance with a current set of data bound to it.
It is a good idea to store and resuse compiled SQL because you avoid
the tedious and unecessary process of recompiling the same SQL over
and over during the life of an application invocation.
Yes, I've understood this.
Unfortunately, an sqlite3_stmt is not just the VDBE, but also the
current execution state and bindings of that VDBE.
|deally, what I'd like is something where a user can prepare a bunch of
SQL statements, creating query objects, like this:
local
open SQL.Template
in
val Query1 = query db "select * from table where x="iS" and y="iI";"
oS oI $
val Query2 = query db "select * from table2 where x="iS";" oS oI $
end
Here the iS and iI refer to input string and input integer
respectively. The oS and oI refer to output string and integer.
Then, later the user might do the following:
val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
val Iterator1b = SQL.execute Query1 ("foobar" & 3)
val Iterator2 = SQL.execute Query2 4
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1b () of
NONE => print "End of the table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
The point is that here I use the same prepared query twice, with two
different associated states and bindings. It seems this sort of
interface is impossible at the moment without reparsing the SQL
statement again. Even if I choose not to expose the 'step' method as an
iterator, during the processing of a query's results there's nothing I
can do to prevent a user from executing the query again.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Look at how Sqlite implements callbacks in sqlite3_exec to discover
a way to implement your language. It looks like it requires a little
extra in your language compiler to resolve the forward references.
Doing that will ensure that the transactions are atomic and that
database integrity is maintained.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Multiple prepared queries
On Feb 12, 2007, at 1:21 AM, John Stanton wrote:
Prepared queries are actually the query compiled into the
metalanguage, VDBE. You can have many statements in one
transaction, and each of those statements can be reusing a VDBE
virtual machine instance with a current set of data bound to it.
It is a good idea to store and resuse compiled SQL because you
avoid the tedious and unecessary process of recompiling the same
SQL over and over during the life of an application invocation.
Yes, I've understood this.
Unfortunately, an sqlite3_stmt is not just the VDBE, but also the
current execution state and bindings of that VDBE.
|deally, what I'd like is something where a user can prepare a bunch
of SQL statements, creating query objects, like this:
local
open SQL.Template
in
val Query1 = query db "select * from table where x="iS" and
y="iI";" oS oI $
val Query2 = query db "select * from table2 where x="iS";" oS oI $
end
Here the iS and iI refer to input string and input integer
respectively. The oS and oI refer to output string and integer.
Then, later the user might do the following:
val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
val Iterator1b = SQL.execute Query1 ("foobar" & 3)
val Iterator2 = SQL.execute Query2 4
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1b () of
NONE => print "End of the table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
The point is that here I use the same prepared query twice, with two
different associated states and bindings. It seems this sort of
interface is impossible at the moment without reparsing the SQL
statement again. Even if I choose not to expose the 'step' method as
an iterator, during the processing of a query's results there's
nothing I can do to prevent a user from executing the query again.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Multiple prepared queries
Prepared queries are actually the query compiled into the metalanguage, VDBE. You can have many statements in one transaction, and each of those statements can be reusing a VDBE virtual machine instance with a current set of data bound to it. When you reset the compiled statement you make it ready for a new set of bindings and when you finalize it you destroy it. As far as I can see there is no simple way in Sqlite to use cursors and have multiple transactions current. I am sure that with ingenuity you could implement some cursor-like functionality. It is a good idea to store and resuse compiled SQL because you avoid the tedious and unecessary process of recompiling the same SQL over and over during the life of an application invocation. Wesley W. Terpstra wrote: Good evening. I've been working on an SQLite3 binding for Standard ML (SML) targeting the MLton compiler. I've read all the material on the C API I've found, but I have some questions about the VM prepare/step methods. Specifically, I am confused about the lifetime of non- finalized queries. It seems that there can be several prepared queries at once, and the documentation implies that this represents a savings: the queries need not be reparsed. However, it also means it's possible to interleave steps to prepared queries. Here are the things I believe to be true, but I would like confirmation / denial, as it affects the interface I expose in SML: Suppose you have two prepared queries: q1 and q2. I currently believe that it is an error to execute: step q1, step q2, step q1. In fact, I believe that after step q1, you must either call finalize or reset on q1 before you can call step q2. If I'm wrong with the above, what happens if I step q1, start a transaction, then step q1 again? What happens to an open transaction if you reset a query? I imagine that a user might be running a select statement, have found the row they were interested in, and then called reset rather than read the rest of the result set. In this case, I would like to be able to continue running new statements within the query. Similarly, I might cancel the query with finalize. The documentation seems to imply that this will abort a transaction? I ask all this, because I think it would be convenient to allow uses to create query objects that they can call / reuse at a later point. However, it's not clear to me if I should expose the 'step' interface to a user. If interleaving steps is bad, it is possible to design the interface to allow stopping a query prematurely, without exposing 'step'. If this premature cancelation breaks an open transaction, then perhaps I should forbid it as well. Thanks for any clarification on these points. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple prepared queries
Thanks for your answers! On Feb 12, 2007, at 1:58 AM, [EMAIL PROTECTED] wrote: "Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote: Suppose you have two prepared queries: q1 and q2. I currently believe that it is an error to execute: step q1, step q2, step q1. No. Queries can be interleaved this way. Ok. What is the defined behaviour of interleaved inserts and selects? I gather since an insert completes in a single step there is no problem? Active queries now return the result if they have not been stepped past this point? Will an UPDATE ... WHERE ... also *always* complete in a single step? For that matter SQLite is reentrant, and this fact is used within the implementation. So, I can create subqueries within user-code in response to some of the rows I've retrieved? That's pretty cool An implicit transaction was already started when you started stepping q1. That implicit transaction will not end until all active queries have finished. If you run the BEGIN command, all that does is set a flag that tells SQLite not to commit once all queries have finished but rather to keep the transaction open. Is there some sort of 'open transaction counter'? ie: if I begin&commit within the scope of an 'automatic' transaction, it will not commit until the automatic transaction completes? Suppose I ran: begin, select *, step1, commit, step2. Does the commit fail because there is still a nested, active query? If yes, then great. If not, then what is the behaviour of step2? What happens to an open transaction if you reset a query? If you have not run BEGIN, then the transaction will end once *all* active queries have completed. While most helpful in understanding the mechanism (is this documented somewhere?), that didn't actually answer this question: What I meant is that if I am running a "select * from giant_table;" and find on row 10/800 the data I was looking for, I might want to just finalize the select statement. My question is what happens to an explicitly opened transaction that included this select statement? The documentation implies that the transaction will be aborted, which is not very desirable. Here's a concrete example: I need to compute some statistic and write it into a table. I start a transaction and then begin processing some accounting records. Part-way through reading those records, I realize I have enough information, so I stop the select with finalize/reset, and then run an update, then commit. Is this ok? Now that I'm working on wrapping bind_text/bind_text16, I have another question: what is the native on-disk format of the strings? The documentation suggests that open/open16 determine the encoding. Does this mean that if I created the database with open, then a future bind_text16 will do an automatic charset conversion? ie: should I pick one encoding and stick with it, or allow users to mix them? (SML has its own Unicode character classes, so I have to convert to UTF-8/16 anyway. It would be a shame to do it twice.) It's ackward that there is no separation between a compiled VM and a compiled VM's state. ie: If I want to run the same precompiled query twice in parallel (since this is apparently well defined), then I would need two separate instances of the state. How hard would it be to create a method that cloned only the byte- code: stmt* sqlite3_clone(stmt*)? If I wrote this, would it be accepted? TBH, it would be more helpful if there was an intermediate form between prepare and the stmt. Again, thanks for your answers. I just want to be sure that I don't allow breaking SQLite's invariants. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple prepared queries
"Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote: > > Suppose you have two prepared queries: q1 and q2. I currently believe > that it is an error to execute: step q1, step q2, step q1. No. Queries can be interleaved this way. > > In fact, I believe that after step q1, you must either call finalize > or reset on q1 before you can call step q2. Not true. Two or more queries can be running at the same time. For that matter SQLite is reentrant, and this fact is used within the implementation. Some queries will automatically invoke other queries. Examples of this occur when you run VACUUM or when you make any kind of queries against fts1 or fts2 virtual tables. > > If I'm wrong with the above, what happens if I step q1, start a > transaction, then step q1 again? An implicit transaction was already started when you started stepping q1. That implicit transaction will not end until all active queries have finished. If you run the BEGIN command, all that does is set a flag that tells SQLite not to commit once all queries have finished but rather to keep the transaction open. > > What happens to an open transaction if you reset a query? I imagine > that a user might be running a select statement, have found the row > they were interested in, and then called reset rather than read the > rest of the result set. In this case, I would like to be able to > continue running new statements within the query. Similarly, I might > cancel the query with finalize. The documentation seems to imply that > this will abort a transaction? If you have not run BEGIN, then the transaction will end once *all* active queries have completed. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Multiple prepared queries
Good evening. I've been working on an SQLite3 binding for Standard ML (SML) targeting the MLton compiler. I've read all the material on the C API I've found, but I have some questions about the VM prepare/step methods. Specifically, I am confused about the lifetime of non- finalized queries. It seems that there can be several prepared queries at once, and the documentation implies that this represents a savings: the queries need not be reparsed. However, it also means it's possible to interleave steps to prepared queries. Here are the things I believe to be true, but I would like confirmation / denial, as it affects the interface I expose in SML: Suppose you have two prepared queries: q1 and q2. I currently believe that it is an error to execute: step q1, step q2, step q1. In fact, I believe that after step q1, you must either call finalize or reset on q1 before you can call step q2. If I'm wrong with the above, what happens if I step q1, start a transaction, then step q1 again? What happens to an open transaction if you reset a query? I imagine that a user might be running a select statement, have found the row they were interested in, and then called reset rather than read the rest of the result set. In this case, I would like to be able to continue running new statements within the query. Similarly, I might cancel the query with finalize. The documentation seems to imply that this will abort a transaction? I ask all this, because I think it would be convenient to allow uses to create query objects that they can call / reuse at a later point. However, it's not clear to me if I should expose the 'step' interface to a user. If interleaving steps is bad, it is possible to design the interface to allow stopping a query prematurely, without exposing 'step'. If this premature cancelation breaks an open transaction, then perhaps I should forbid it as well. Thanks for any clarification on these points. - To unsubscribe, send email to [EMAIL PROTECTED] -
