On Feb 12, 2008, at 7:05 AM, Evans, Mark (Tandem) wrote:
> SQLite experts:
>
> The xBestIndex method of the SQLite virtual table interface
> implemented by the VT module returns an output to the core by
> setting idxNum member var of struct sqlite3_index_info to a value
> that is meaningful to the VT module. Assume that a memory resource
> was created in conjunction with the chosen index that will hold
> information passed by xFilter.
>
> The question is: How can VT module tell when it is safe to release
> that resource? I'm thinking, it's when the associated statement is
> finalized. But how does the VT module know that? I have found
> that xClose() call is not the answer because I have stumbled on a
> test sequence that shows this to be unsafe:
>
>
> do_test update-1.0 {
> execsql {DELETE FROM t1}
> execsql {insert into t1 values(1,2,3)}
> execsql {SELECT * FROM t1 }
> execsql {UPDATE t1 SET y=3 WHERE x=1}
> execsql {SELECT * FROM t1 }
> } {1 2 3 1 3 3}
>
> After execution of the UPDATE, the VT module call sequence for the
> next SELECT does not include xBestIndex as I was expecting. It
> calls xFilter with the idxNum that the previous SELECT created (I
> think). I crash and burn because I released the index resource in
> the xClose call for the first SELECT.
>
> I'd be most appreciative if an expert could steer me in the right
> direction.
Executing an SQL statement is broken into two parts: compilation
(sqlite3_prepare()) to virtual machine code and execution of that
virtual machine code (sqlite3_step()). The xBestIndex() method is
called as part of compilation, xFilter() is called as part of
execution. As is xClose().
The first time your SELECT statement is run the Tcl interface
calls sqlite3_prepare() to compile it, then
sqlite3_step()/sqlite3_reset() to execute it. The second time,
it is able to re-use the compiled statement. That is why xBestIndex
is not called for the second SELECT.
For passing context, you can also use the sqlite3_index_info.idxStr
variable. Set this to point at a string allocated by sqlite3_malloc()
and sqlite will automatically free it when it is no longer required.
This allows you to store a blob of context data instead of a single
integer.
If you need some resource that really does require a destructor (a
connection handle to some other database etc.), do not open it in
xBestIndex(). Open it in xFilter() and close it in xClose(). Each
xFilter() call should be matched by exactly one xClose().
Regards,
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
