Re: [sqlite] BLOB question
A couple of comments.
On Feb 23, 2007, at 3:03 PM, Cesar Rodas wrote:
while ( (n=fread(buff,Buffsize,1,file)) > 0)
{
if (i>0)
*value = realloc(*value, (i+1) * Buffsize);
memcpy(*value + (i * Buffsize), buff, Buffsize);
*len += n;
i++;
}
You are growing the array in size at each append. This is usually a
bad idea; it leads to n^2 complexity. The standard approach is to
double the buffer each time it fills up. This is 2*n complexity.
However, it's not your bug.
query = sqlite3_mprintf("INSERT INTO blob VALUES('%Q') ", value);
This segfaults because there is no null terminator on the value
string. The way SQLite3 reads 'value' is by scanning it until it
finds a '\0'. If there is no such character, it scans past the end of
the array, leading to the crash you are seeing.
A simple solution is to append this character to the end of the
string. However, it the file itself contains a '\0' character, this
will lead to the file being truncated in the database. A better
solution would be:
file_content = ... your loading code, allocated by malloc ...
file_length = ... length of the file ...;
query = "INSERT INTO blob VALUES(?);";
sqlite3_prepare_v2(db, query, -1, , );
sqlite3_bind_block(qhandle, 1, file_content, file_length, free);
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Announcing SQLite3/SML binding v0.1
I've finished writing a binding for SQLite3 to SML. It can be found at http://terpstra.ca/sqlite3-sml/sqlite3-sml-0.1.tar.gz svn://mlton.org/mltonlib/trunk/ca/terpstra/sqlite3-sml It covers the entire non-experimental SQLite3 interface and I find it convenient to use. I've put the glue code (about 90k) into the public domain to match SQLite3. The included README, sql.sig, and demo.sml roughly document the API. I hope this is enough to get people started. The only caveat is that there is a bug in the current SQLite3 making custom authorizers unreliable. Either don't use this feature or help me campaign to get the bug fixed. :-) Please let me know if you encounter any bugs specific to the binding. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Bug: sqlite3_finalize in authorizer breaks step of another query
Compile the attached C program and run it in a directory with the attached test.db. kiwiw:~/sqlite3/x terpstra$ ls -l bug.c test.db -rw-r--r--1 terpstra terpstra 976 Feb 18 00:09 bug.c -rw-r--r--1 terpstra terpstra 6144 Feb 18 00:09 test.db kiwiw:~/sqlite3/x terpstra$ gcc -Wall -O0 -g bug.c -o bug -lsqlite3 kiwiw:~/sqlite3/x terpstra$ ./bug Failing step: 17 - library routine called out of sequence Invoking the finalizer for query 1 inside the authorization function leads the step of query 2 failing. As SQLite3 is supposed to be re- entrant, recursively altering q1 should not affect q2. I found this bug when running an SML program using the SQLite3 binding I'm working on. In that binding, queries are garbage collected. During the authorizer callback into SML, the runtime decided to recover memory. This led to finalizing a no-longer-used query. In case this mailing list drops my attachments again, find them also here: http://terpstra.ca/sqlite3/bug.c http://terpstra.ca/sqlite3/test.db - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
On Feb 15, 2007, at 4:01 PM, [EMAIL PROTECTED] wrote: "Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote: The approach I'd much prefer is to register a trigger to update the GUI... Note that the trigger runs on the client-side of the process that makes the change - not on the client side of the process that is implementing the GUI. Yes, I'm aware of this limitation. There will only be one database user: the application itself. You might implement something like [UndoRedo] then have your GUI process poll only changes table. The changes table will normally be empty, except after some other process makes a change. So when the changes table is non-empty, that is the GUI process's cue to redraw its screen based on the latest database content. You are assuming I intend to track changes from another application. I do not. I already know when a change happens; I made it. So, I don't need a log to tell me when to refresh. My goals are: 1. simplify reasoning about the application (this program will be bloated and grotesque in short order, so I want to be responsible for as little cross-component interaction as possible) 2. avoid the cost of refreshing big tree widgets with thousands of entries (polling doesn't give me a 'diff') You suggestion helps with #2, but so would putting triggers on the base tables as Michael suggested. His suggestion doesn't completely address concern #1, but it is certainly a step in the right direction (it's not too hard to reason about which base tables can affect the view if you have the query). As an example: in an instant messenger a new text message arrives. The application puts it into the message log. The chat window automatically updates with the new text. The statistics window about total # of messages updates. Possibly other things happen. The point is I don't want to have to think about the 'possible other things'. If the program gets fat, I'll screw this up. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
On Feb 15, 2007, at 3:46 PM, Michael Schlenker wrote: Wesley W. Terpstra schrieb: I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view changes. [snip] Thus, a window simply provides the VIEW definition and insert/ update/delete callbacks. Some support code creates the view and insert()s the current contents. Then it hooks the triggers invoking the methods to catch future updates. On widget death, the view and triggers are dropped. Triggers only react on update/delete/insert operations, which are not generally available for a arbitrary view. Databases that implement immediate materialized views propagate the changes from the base tables to the view. I would have expected an update/delete/insert trigger on a view to trigger under the same criteria as when a materialized view would be updated to reflect changes in the base tables. I'm positive that before/after triggers work on a materialized view in this way under Oracle. But if you slightly change your api, to provide a view definition and a list of tables/columns to watch you can do it, just create the triggers on the tables not on the view and take the appropriate action in your callbacks. You mean to basically create the hooks manually that would've been created for a materialized view? This is certainly possible, just error prone. I wonder how hard it would be to automatically translate a trigger on a view into triggers over the base tables. I was hoping for something of this nature to relieve me of responsibility for thinking of all the ways base tables could change the view. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Triggers+callbacks = GUI?
I intend to write a GUI application backed by SQL. Several of the
windows display status that would best be represented as a database
view. What I've been thinking about is how to update the GUI when the
view changes.
First the obvious approach: polling. Every X seconds re-execute the
query and redraw the GUI. Certainly this will work, and I might still
do this. For windows with very large state, however, this is not very
desirable.
The approach I'd much prefer is to register a trigger to update the
GUI as follows:
create temp view StatusWidget as select some query ...;
create temp trigger StatusWidgetUpdate after update on StatusWidget
for each row
begin
select statusWidgetUpdateFn(OLD.key, NEW.key, NEW.value1,
NEW.value2, ...);
end;
ditto for Add/Delete
Then I create custom functions 'statusWidget{Add,Update,Delete}Fn'
that take the values and update the GUI.
Thus, a window simply provides the VIEW definition and insert/update/
delete callbacks. Some support code creates the view and insert()s
the current contents. Then it hooks the triggers invoking the methods
to catch future updates. On widget death, the view and triggers are
dropped.
It seems to me this would be a very bug-free way to design even
complicated applications. Whenever you create a window, back its
state with SQLite. Actions taken by the network or the user simply
modify state in the database. This in turn updates all the relevant
GUI windows automatically. You don't need to track the changes; you
let the database do it. This is only possible since callback
functions can be bound to triggers. AFAIK, no other database can do
this, since the triggers exist on the server-side.
However, the problem I'm running into is that I can't create before/
after triggers on a view:
SQL error: cannot create BEFORE trigger on view: main.popup
SQL error: cannot create AFTER trigger on view: main.popup
This isn't documented as an unimplemented SQL feature, so is this a bug?
If SQLite doesn't support triggers on views, does anyone know of a
database which does AND allows triggers to invoke a client-side
callback? Is supporting triggers on views planned for the future? Can
I help?
Thanks.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Patch: sqlite3_query_string
On Feb 14, 2007, at 4:29 PM, Wesley W. Terpstra wrote: Please consider applying the attached patch It seems this mailing list strips file attachments. The patch is now available at <http://terpstra.ca/sqlite3_query_string.patch> As it's so short, you probably don't need a copyright disclaimer, but just in case: The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights this code under copyright law. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Patch: sqlite3_query_string
Please consider applying the attached patch to SQLite3. It adds support for a method sqlite3_query_string(sqlite3_stmt*); This is helpful for error reporting when query execution fails. It's also helpful if an application needs to clone an existing query. It is the logical analogue of sqlite3_db_handle(sqlite3_stmt*); As it's so short, you probably don't need a copyright disclaimer, but just in case: The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights this code under copyright law. - To unsubscribe, send email to [EMAIL PROTECTED] -
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 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
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
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
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
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 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] -
[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] -
