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
