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:[email protected]]
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 <[email protected]> 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:[email protected]]
> 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 <[email protected]> wrote:
>
> > I've no word for this ! .....
> >
> >
> > On Fri, Jul 4, 2014 at 9:46 AM, Keith Medcalf <[email protected]>
> wrote:
> >
> >>
> >> The spacing and indentation are atrocious?
> >>
> >> >-----Original Message-----
> >> >From: [email protected] [mailto:sqlite-users-
> >> >[email protected]] 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
> >> >[email protected]
> >> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [email protected]
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> 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: [email protected]
>
> 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
