Post by Sören BrunkIn xBestIndex, each constraint contains the column on the left-hand side
(iColumn) and the operator (op). If I understood correctly, xFilter gets
the value on the right-hand side of each constraint you chose to use.
Correct.
Post by Sören BrunkBut I need to know not only that value in xFilter, but also the column
on the left-hand side and the operator.
Column indexes are not passed into xFilter(). From inside xFilter(),
there is no way to match a given value to a specific column. You
have to know the order that the column values that are passed in.
That ordering is determined by your code in xBestIndex(). Inside
xBestIndex(), the values you set in
idxinfo->aConstraintUsage[x].argvIndex will determine where the
idxinfo->aConstraint[x].iColumn value is passed into xFilter().
So the two functions must agree on how and where all those values are
setup, or you must somehow convey the information via idxNum/idxStr.
Post by Sören BrunkI'm wondering if there is any way to pass that additional information to
xFilter besides encoding it into idxNum/idxStr somehow.
Not easily.
There may be multiple tables in-flight at a given time, so any kind
of global storage outside the function parameters can get quite
tricky. By far, the easiest thing is to pack everything into those
two parameters.
xBestIndex() can be called multiple times to test multiple query
plans. This means anything you allocate inside of xBestIndex() may
get abandoned. Again, without some complex logic to deal with
external memory stores, the safest thing to do is single allocations
with sqlite3_malloc() that are passed via idxStr. If the memory is
allocated with sqlite3_malloc(), then SQLite will properly deallocate
any abandoned query plans. But the allocation must be one big chunk
(it cannot be a multi-level or "deep" data structure with pointers to
other data structures) because SQLite will only call sqlite3_free()
on the top level idxStr pointer.
As explained on page 267, the most typical usage is for "internal"
modules that are using normal SQL statements on "shadow tables" to
implement the virtual table. In that case, xBestIndex() can build
SQL statement strings, complete with indexed SQL statement
parameters, such as "?1", "?2", etc. These SQL command strings can
be passed via idxStr, and the parameter indexes can be matched to
table column indexes by setting the argvIndex numbers. Then,
xFilter() only need to prepare the given idxStr command strings and
bind argv[0] to parameter index 1, argv[1] to parameter index 2,
and so on (remember that parameter indexes start with 1), and run
the statements. In that situation, most of the actual logic happens
in xBestIndex().
This doesn't work so well with "external" modules, where you often
need to pass a great deal more state than a simple string. If you
need to pass a data structure, you can use the idxStr pointer. Just
remember that you should allocate the structure as a single call to
sqlite3_malloc(). That means keeping the data structure "flat",
either by using static sizes or hand-packing the memory.
You also can't bind every column value to its own column index via the
argvIndex (that is, make all the argv[] indexes match the column
indexes), because the argvIndex values are not allowed to have gaps.
I'd love to point you at some examples, but I'm not sure there are
any significant "external" style modules that provide solid examples
of xBestIndex() and xFilter(). External modules are typically fairly
custom in nature, and I'm not sure there is a very established design
pattern, as there is with "internal" style modules.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson