need help to implement a VTAB on a already sorted table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

petern
In reply to this post by Richard Hipp-3
Richard.  Further to your reply on Mar 31 2017, were those newly worked
code exemplars demonstrating non-trivial sqlite3_index_info from xBestIndex
interaction for a SQLite table backed vtable published?

I am sure there is still great interest for this information considering
the lack of even one expertly worked table backed vtable example to study.
I routinely search this forum's traffic for anything related.

Thanks in advance for your reply.



On Fri, Mar 31, 2017 at 6:06 AM, Richard Hipp <[hidden email]> 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...
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...