Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

OK, I have looked over all possible solutions for this issue and the best I
could find is to extend sqlite3_index_info structure as follows:
struct sqlite3_index_info {

  ...

  /* Extra info */
  struct sqlite3_index_extras {
int iVersion;
sqlite3_collseq **coll_seq;  /* Collation sequences (same size as
aConstraints) */
  } *extras;
};

this would be binary compatible and also extendable for needs in future.

How about this?


2013/7/17 Dan Kennedy 

> On 07/17/2013 03:46 PM, Dušan Paulovič wrote:
>
>> Hello,
>> in virtual table mechanism is missing a way to correctly handle following
>> queries:
>>
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;
>>
>> To xBestIndex function is passed only constraint field = 'abc', but there
>> is no way to correctly compare (by correctly I mean accordingly to passed
>> collation) those strings because of missing collation functions in
>> constraint definition.
>>
>> Patch for this issue exists for a long time:
>> http://osdir.com/ml/sqlite-**users/2011-09/msg00152.html
>>
>> Is there any chance that it could get merged?
>>
>
> I think the main problem is that it is not binary compatible.
>
> Dan.
>
>
>
> __**_
> 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

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

Yes, xRename was a part of the v1 module interface, but v1 module interface
is not the first verson of it.
Old sqlite extensions does not even contain xRename in their VT
implementations and xFindFunction was the last one considered.

I understand what you mean by binary incompatibility, but it is not a
reason to keep this issue unresolved.
Without possibility to custom handling of collation in xBestIndex function
is this great idea limited to be used only in case of numeric queries.
Now it is no way to return correct results from VT using xBestIndex. Yes, I
can keep these constraints on SQLite, but then perhaps these
constraints should be filtered out from xBestIndex consideration.

Perhaps new function returning CollSeq array for constraint structure in
API could resolve it - as I suggested in previous post.
It can be resolved even with binary compatibility.

Dusan


2013/7/17 Jay A. Kreibich 

> On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the
> wall:
> > But it is the same as with new functions in sqlite3_module. Old SQLite
> > extensions does not implement xRename function which is now needed. Also,
> > new feature could be made optional using macro switch like some other
> > features in SQLite.
>
>   Actually, xRename() was part of the v1 module interface.  xSavepoint(),
>   xRelease(), and xRollbackTo() were added in the v2 interface.  All
>   three of these functions are optional, and the fact they were added
>   at the end of the data structure means a v1 module will run under a
>   modern version of SQLite just fine.
>
>   http://www.sqlite.org/c3ref/module.html
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> 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

Re: [sqlite] Passing collation to xBestIndex

[Jay A. Kreibich]

On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the wall:
> But it is the same as with new functions in sqlite3_module. Old SQLite
> extensions does not implement xRename function which is now needed. Also,
> new feature could be made optional using macro switch like some other
> features in SQLite.

  Actually, xRename() was part of the v1 module interface.  xSavepoint(),
  xRelease(), and xRollbackTo() were added in the v2 interface.  All
  three of these functions are optional, and the fact they were added
  at the end of the data structure means a v1 module will run under a
  modern version of SQLite just fine.

  http://www.sqlite.org/c3ref/module.html

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

Well, to make it binary compatible with existing virtual table
implementations, there could be added new function returning const pointer
to CollSeq structure from passed index contraint.
This function should be callable only from xBestIndex function.

Something like:
const CollSeq * sqlite3_vtab_constraint_coll_seq(const
sqlite3_index_constraint* pConstraint);

sqlite3_index_constraint contains iTermOffset, so to find a collation
sequence for it should not be so big problem.

How about it?


2013/7/17 Dušan Paulovič 

> But it is the same as with new functions in sqlite3_module. Old SQLite
> extensions does not implement xRename function which is now needed. Also,
> new feature could be made optional using macro switch like some other
> features in SQLite.
>
>
> 2013/7/17 Stephan Beal 
>
>> On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič 
>> wrote:
>>
>> > What do you mean? I use it for a while on Windows and all works.
>> > Binary not compatible with what?
>> >
>>
>> What Dan means is that libraries built with and without this feature might
>> not be binary compatible. i compile against an sqlite3 DLL without this
>> feature, then my sysadmin updates libsqlite3 to one with this feature, and
>> my binary will then likely segfault at some point (or otherwise invoke
>> undefined behaviour) because the binary signatures of the sqlite
>> structures
>> in my app no longer match those in the library. A recompile of my app
>> (with
>> no code changes) would be necessary to fix this.
>>
>> --
>> - stephan beal
>> http://wanderinghorse.net/home/stephan/
>> http://gplus.to/sgbeal
>> ___
>> 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

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

But it is the same as with new functions in sqlite3_module. Old SQLite
extensions does not implement xRename function which is now needed. Also,
new feature could be made optional using macro switch like some other
features in SQLite.


2013/7/17 Stephan Beal 

> On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič 
> wrote:
>
> > What do you mean? I use it for a while on Windows and all works.
> > Binary not compatible with what?
> >
>
> What Dan means is that libraries built with and without this feature might
> not be binary compatible. i compile against an sqlite3 DLL without this
> feature, then my sysadmin updates libsqlite3 to one with this feature, and
> my binary will then likely segfault at some point (or otherwise invoke
> undefined behaviour) because the binary signatures of the sqlite structures
> in my app no longer match those in the library. A recompile of my app (with
> no code changes) would be necessary to fix this.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> 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

Re: [sqlite] Passing collation to xBestIndex

[Stephan Beal]

On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič  wrote:

> What do you mean? I use it for a while on Windows and all works.
> Binary not compatible with what?
>

What Dan means is that libraries built with and without this feature might
not be binary compatible. i compile against an sqlite3 DLL without this
feature, then my sysadmin updates libsqlite3 to one with this feature, and
my binary will then likely segfault at some point (or otherwise invoke
undefined behaviour) because the binary signatures of the sqlite structures
in my app no longer match those in the library. A recompile of my app (with
no code changes) would be necessary to fix this.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

What do you mean? I use it for a while on Windows and all works.
Binary not compatible with what?

Dusan


2013/7/17 Dan Kennedy 

> On 07/17/2013 03:46 PM, Dušan Paulovič wrote:
>
>> Hello,
>> in virtual table mechanism is missing a way to correctly handle following
>> queries:
>>
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;
>>
>> To xBestIndex function is passed only constraint field = 'abc', but there
>> is no way to correctly compare (by correctly I mean accordingly to passed
>> collation) those strings because of missing collation functions in
>> constraint definition.
>>
>> Patch for this issue exists for a long time:
>> http://osdir.com/ml/sqlite-**users/2011-09/msg00152.html
>>
>> Is there any chance that it could get merged?
>>
>
> I think the main problem is that it is not binary compatible.
>
> Dan.
>
>
>
> __**_
> 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

Re: [sqlite] Passing collation to xBestIndex

[Dan Kennedy]

On 07/17/2013 03:46 PM, Dušan Paulovič wrote:

Hello,
in virtual table mechanism is missing a way to correctly handle following
queries:

SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;

To xBestIndex function is passed only constraint field = 'abc', but there
is no way to correctly compare (by correctly I mean accordingly to passed
collation) those strings because of missing collation functions in
constraint definition.

Patch for this issue exists for a long time:
http://osdir.com/ml/sqlite-users/2011-09/msg00152.html

Is there any chance that it could get merged?


I think the main problem is that it is not binary compatible.

Dan.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

Hello,
in virtual table mechanism is missing a way to correctly handle following
queries:

SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;

To xBestIndex function is passed only constraint field = 'abc', but there
is no way to correctly compare (by correctly I mean accordingly to passed
collation) those strings because of missing collation functions in
constraint definition.

Patch for this issue exists for a long time:
http://osdir.com/ml/sqlite-users/2011-09/msg00152.html

Is there any chance that it could get merged?

Dusan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users