Quantcast

need help to implement a VTAB on a already sorted table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

need help to implement a VTAB on a already sorted table

Andreas Otto-5
Hi…

I'm new to this mailing-list because I have a problem to define a
"VIRTUAL TABLE"
sqlite3 extension. I already have a working implementation with close empty
*xBestIndex and *xFilter.

1) My data is already in a sorted list with ONE or MORE primary index
columns
2) Currently the VTAB is always doing a FULL TABLE SCAN even for trivial
search.
3) my schema define in sqlite3_declare_vtab is

"CREATE TABLE %s (            \
   rep   VARCHAR(3),           \
   par   VARCHAR(3),           \
   year  INTEGER,              \
   imp   DOUBLE,               \
   exp   DOUBLE,               \
   PRIMARY KEY(rep,par,year)   \
)"

4) it seems that sqlite does NOT use the "PRIMARY KEY(rep,par,year)"
information…
5) the key data is already sorted…
6) I expect for a primitive WHERE clause like "rep=XXX" some kind of access
   optimization…

I'm looking for a "starter" implementation with the conditions from above
with working *xBestIndex and *xFilter…

Thanks for your help.

mfg ao

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need help to implement a VTAB on a already sorted table

Richard Hipp-3
On 3/31/17, aotto <[hidden email]> wrote:
> I already have a working implementation with [a] close [to] empty
> *xBestIndex and *xFilter.
>
> 6) I expect for a primitive WHERE clause like "rep=XXX" some kind of access
>    optimization…

Your xBestIndex and xFilter functions must implement the desired optimization.

The xBestIndex and xFilter routines work as a team.  xBestIndex is
passed information about the WHERE clause.  xBestIndex uses that
information to realize that "rep=XXX" is present, sets fields of
sqlite3_index_info that cause the XXX value to be passed into xFilter,
then sets idxNum or idxStr to tell xFilter what algorithm to use.
xFilter will use the idxNum or idxStr values to recognize that it
should jump immediately to rep=XXX.

I'll see if I can work up some examples of this later today...

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need help to implement a VTAB on a already sorted table

jose isaias cabrera-3
Some examples of these would be great, Dr. Hipp.  Thanks.

On 2017-03-31 09:06, Richard Hipp wrote:

> On 3/31/17, aotto <[hidden email]> wrote:
>
>> I already have a working implementation with [a] close [to] empty
>> *xBestIndex and *xFilter.
>>
>> 6) I expect for a primitive WHERE clause like "rep=XXX" some kind of access
>> optimization...
>
> Your xBestIndex and xFilter functions must implement the desired optimization.
>
> The xBestIndex and xFilter routines work as a team.  xBestIndex is
> passed information about the WHERE clause.  xBestIndex uses that
> information to realize that "rep=XXX" is present, sets fields of
> sqlite3_index_info that cause the XXX value to be passed into xFilter,
> then sets idxNum or idxStr to tell xFilter what algorithm to use.
> xFilter will use the idxNum or idxStr values to recognize that it
> should jump immediately to rep=XXX.
>
> I'll see if I can work up some examples of this later today...
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...