In the case outlined it is easy. If idxNum == 0 then there are no arguments. If
idxNum == -1 then the only argument is the ID value of the single row to be
retrieved.
Lets assume you are implementing a virtual table person (name text, age int,
...) that stores the rows in a flat file (the offset of the record being the
rowid) and also has an index on "name".
When preparing a query, SQLite will ask a series of questions via calls to
vt_best_index. Your function answers these questions by filling in certain
fields and also recording any additional information required for the vt_filter
function (passed as idxStr). The vt_filter function retrieves the idxStr and
knows what argv to expect (and what to do with it), if any.
"SELECT * FROM person;" will have SQLite asking "what is the cost of a full
table scan" (no constraints) and the answer should be the number of rows in the
table (n).
"SELECT * FROM person WHERE name = 'fred';" will have SQLite asking "what is
the cost of a partial table scan on field name" ( {name, '='} ) and the answer
should be the average number of entries that must be read to locate all
matching entries ( e.g. the cost of finding the first one plus the average
number of duplicates: ld n + (n / distinct names) ). You need to record that
the first argv is the name.
More complex queries will involve SQLite asking more than one question and then
choosing the answer it thinks will offer the best performance.
"... join person on (person.id = ...) join ... on (... = person.name)..." will
have SQLite asking the cost of a full table scan (n), a rowid access (1) and a
name access (ld n + n/d).
Think of the questions as envelopes. SQLite writes the constraints on the
outside, you write the cost on the outside, mark the constraints used, write
down how to answer the question on a piece of paper and seal it inside the
envelope. SQLite will then pick one of the envelopes, pull out the paper, and
hand it to your v_filter function along with the requested arguments.
-----Ursprüngliche Nachricht-----
Von: Micka [mailto:mickamus...@gmail.com]
Gesendet: Freitag, 04. Juli 2014 13:49
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan
thx but how do you know in your vt_filter function :
int vt_filter( sqlite3_vtab_cursor *cur, int idxNum, const char *idxStr, int
argc, sqlite3_value **argv ){
that argv[0] is column index 4 by example ?
Micka,
On Fri, Jul 4, 2014 at 12:17 PM, Hick Gunter <h...@scigames.at> wrote:
> AFAIKT you currently have two supported operation modes:
>
> a) full table scan
> b) access via exact ID
>
> To implement a) you should make p_info->estimatedCost be the number of
> rows in your table (I suggest leaving p_info->idxNum as 0).
>
> To implement b) you need to check for a constraint with
> - iColumn == index of your ID column
> - op == SQLITE_INDEX_CONSTRAINT_EQ
> - usable != 0
> In that case you need to set p_info->estimatedCost to 1 (you will be
> retrieving only this row), argvIndex to 1, omit to 1 and
> p_info->idxNum to something different (I suggest -1).
>
> You can begin thinking about range scans and index scans later.
>
>
> -----Ursprüngliche Nachricht-----
> Von: Micka [mailto:mickamus...@gmail.com]
> Gesendet: Freitag, 04. Juli 2014 11:54
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan Hi,
>
> Thx for this constructive message ;)
>
> I don't have a lot of experience with the this part of Sqlite which is
> very powerful !
>
> In the xbestIndex function i'm using only this line :
>
> p_info->idxNum=p_info->aConstraint[0].iColumn;
>
> Because in my xfilter function I needed to know the index of the first
> column. ( do you know a better way to do that ? )
>
>
> and
>
> p_info->idxStr=p_vt->acNameTab;
>
>
> This part was in case I needed to know the name of my table ... But I
> don't really need it, because I'm using sqlite3_vtab *p_svt to store
> the extra data that I need.
>
>
> That part :
> for(i=0;i<p_info->nConstraint;i++){
> if(p_info->aConstraint[i].usable){
> p_info->aConstraintUsage[i].argvIndex=j++;
> }
> }
>
> is to give the order of index . Because the role of this function is
> to give the order of index right ?
>
>
> In my xFilter function I'm detecting if the column index giving by
> idxNum is the ID of my table. If it is, I'm positioning the cursor at
> the correct position. IF the index of the column is different of ID,
> I'm positioning the cursor at the beginning of my table.
>
> YES, it's not perfect, but most of the time it works ^^ .
>
> you said :
>
> But how are you telling your vt_filter function which fields the
> passed values belong to?
>
>
> for the moment I'm only using the first column. most of the time it's
> the ID.
>
>
> I don't see how the column number of the first constraint - usable or
> not- is going to be sufficient information. Neither is there a check
> for the type of operation requested taken into account.
>
>
> Yes I will have to improve it .
>
>
> Since your filter/next functions are probably going to return
> arbitrary records, the fact that you are not setting any "omit" flags
> allows SQLite to recheck the conditions and return something vaguely
> resembling the correct result set. Just with a lot of work filtering
> out superfluous records and missing any records that should have been
> returned by filter/next (but were not).
>
> Yes I have to improve that too ^^
> I don't know why I should use omit ? When do you need it ? Sorry ...
> I'm a beginner ..
>
>
> Also, you are writing the field aOrderBy, which is documented to be an
> input field.
>
> thx, I've deleted it !
>
>
> Micka,
>
>
> On Fri, Jul 4, 2014 at 11:20 AM, Hick Gunter <h...@scigames.at> wrote:
>
> > As you noticed, you were asking to have the values of unusable
> > constraints passed to your vt_filter function.
> >
> > But how are you telling your vt_filter function which fields the
> > passed values belong to? I don't see how the column number of the
> > first constraint
> > - usable or not- is going to be sufficient information. Neither is
> > there a check for the type of operation requested taken into account.
> >
> > Since your filter/next functions are probably going to return
> > arbitrary records, the fact that you are not setting any "omit"
> > flags allows SQLite to recheck the conditions and return something
> > vaguely resembling the correct result set. Just with a lot of work
> > filtering out superfluous records and missing any records that
> > should have been returned by filter/next (but were not).
> >
> > Also, you are writing the field aOrderBy, which is documented to be
> > an input field.
> >
> > -----Ursprüngliche Nachricht-----
> > Von: Micka [mailto:mickamus...@gmail.com]
> > Gesendet: Freitag, 04. Juli 2014 09:57
> > An: General Discussion of SQLite Database
> > Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan
> >
> > Ok I looked at the source sqlite3.c and saw that :
> >
> >
> >
> > This is my correction :
> >
> >
> > int vt_best_index(sqlite3_vtab *p_svt, sqlite3_index_info *p_info){
> > sVTAB* p_vt = (sVTAB*)p_svt;
> > int i;
> > int j=1;
> > printf("vt_best_index %d\n", p_info->nConstraint);
> >
> > p_info->idxNum=0;
> >
> > if(p_info->nConstraint!=0){
> > p_info->idxNum=p_info->aConstraint[0].iColumn;
> > p_info->idxStr=p_vt->acNameTab;
> > printf("best index constraint column %d\n",
> > p_info->aConstraint[0].iColumn); for(i=0;i<p_info->nConstraint;i++){
> > *if(p_info->aConstraint[i].usable){*
> > p_info->aConstraintUsage[i].argvIndex=j++;
> > }
> > }
> > }
> > if(p_info->nOrderBy==0){
> > p_info->aOrderBy=NULL;
> > }
> > return SQLITE_OK;
> > }
> >
> > Thx every one ! And Keith Medcalf ... you should use your time better !
> >
> >
> > Micka,
> >
> >
> > On Fri, Jul 4, 2014 at 9:49 AM, Micka <mickamus...@gmail.com> wrote:
> >
> > > I've no word for this ! .....
> > >
> > >
> > > On Fri, Jul 4, 2014 at 9:46 AM, Keith Medcalf
> > > <kmedc...@dessus.com>
> > wrote:
> > >
> > >>
> > >> The spacing and indentation are atrocious?
> > >>
> > >> >-----Original Message-----
> > >> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > >> >boun...@sqlite.org] On Behalf Of Micka
> > >> >Sent: Friday, 4 July, 2014 01:29
> > >> >To: General Discussion of SQLite Database
> > >> >Subject: [sqlite] Error xBestIndex returned an invalid plan
> > >> >
> > >> >Hi, I wanted to know what could be wrong with :
> > >> >
> > >> >int vt_best_index(sqlite3_vtab *p_svt, sqlite3_index_info *p_info){
> > >> >sVTAB* p_vt = (sVTAB*)p_svt;
> > >> >int i;
> > >> >printf("vt_best_index %d\n", p_info->nConstraint);
> > >> >
> > >> >p_info->idxNum=0;
> > >> >
> > >> >if(p_info->nConstraint!=0){
> > >> >p_info->idxNum=p_info->aConstraint[0].iColumn;
> > >> >p_info->idxStr=p_vt->acNameTab;
> > >> >printf("best index constraint column %d\n", p_info-
> > >> >>aConstraint[0].iColumn);
> > >> >for(i=0;i<p_info->nConstraint;i++){
> > >> >p_info->aConstraintUsage[i].argvIndex=(1+i);
> > >> >}
> > >> >}
> > >> >if(p_info->nOrderBy==0){
> > >> >p_info->aOrderBy=NULL;
> > >> >}
> > >> >return SQLITE_OK;
> > >> >}
> > >> >
> > >> >thx you very much ^^
> > >> >_______________________________________________
> > >> >sqlite-users mailing list
> > >> >sqlite-users@sqlite.org
> > >> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >>
> > >>
> > >> _______________________________________________
> > >> sqlite-users mailing list
> > >> sqlite-users@sqlite.org
> > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > --------------------------------------------------------------------
> > --
> > -
> > Gunter Hick
> > Software Engineer
> >
> > Scientific Games International GmbH
> > Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien
> > Tel: +43 1 80100 0
> > E-Mail: h...@scigames.at
> >
> > This e-mail is confidential and may well also be legally privileged.
> > If you have received it in error, you are on notice as to its status
> > and accordingly please notify us immediately by reply e-mail and
> > then delete this message from your system. Please do not copy it or
> > use it for any purposes, or disclose its contents to any person as
> > to do so could be a breach of confidence. Thank you for your cooperation.
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
