Re: [sqlite] User-defined collation UNIQUE INDEX
> > That was it! It seems to work now. > > // new code > int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b, > ((alen < blen) ? alen : blen) / sizeof(wchar_t)); > > Is the fact that the callback strings are not NUL terminated and the > lengths are in bytes documented anywhere? If not, it might be a good > idea. If they are documented, can someone point me to the url? I would > like to see if there are any other gotchas before relying on this. > > csmith > I appreciate everyone's help with this issue. The below documentation is from here http://www.sqlite.org/c3ref/create_collation.html "The remaining arguments to the application-supplied routine are two strings, each represented by a (length, data) pair and encoded in the encoding that was passed as the third argument when the collation sequence was registered." May I suggest: "...are two strings, excluding NULs, each represented by a (length in bytes not chars, data) pair and encoded..." That would most likely clear up any confusion. When I read that paragraph initially, "two strings" made me think NUL terminated. When I think string in C I think NUL terminated. The "(length, data) pair encoded" part made me think that the length matched the encoding; meaning character count. Once again, thanks for helping me out. csmith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
>>> int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b, >>> (alen < blen) ? alen : blen); > > Maybe the length is still wrong. The lengths passed to an sqlite > collation sequence callback are in bytes. But _wcsnicmp() is > probably in characters, no? > > That was it! It seems to work now. // new code int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b, ((alen < blen) ? alen : blen) / sizeof(wchar_t)); Is the fact that the callback strings are not NUL terminated and the lengths are in bytes documented anywhere? If not, it might be a good idea. If they are documented, can someone point me to the url? I would like to see if there are any other gotchas before relying on this. csmith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
On Jul 21, 2008, at 8:10 PM, D. Richard Hipp wrote:
>
> On Jul 21, 2008, at 8:32 AM, C. Smith wrote:
>>
>> I didn't know the strings weren't nul terminated. I changed my
>> callback to:
>>
>> static int _cmp(void *pCtx, int alen, const void *a,
>> int blen, const void *b)
>> {
>> int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b,
>> (alen < blen) ? alen : blen);
Maybe the length is still wrong. The lengths passed to an sqlite
collation sequence callback are in bytes. But _wcsnicmp() is
probably in characters, no?
Dan.
>>
>> if(r == 0)
>> r = alen - blen;
>>
>> return r;
>> }
>>
>> Still doesn't work though :(
>>
>
> How did you register the collating sequence? Did you use the
> SQLITE_UTF16_ALIGNED argument on the 3rd parameter?
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] User-defined collation UNIQUE INDEX
>
> How did you register the collating sequence? Did you use the
> SQLITE_UTF16_ALIGNED argument on the 3rd parameter?
>
I am doing the below.
sqlite3_create_collation(db, "PATH", SQLITE_UTF16, NULL, _cmp);
I changed it to use "SQLITE_UTF16_ALIGNED" but it didn't load properly.
I modified my extension init func to print out the return code of
create collation:
int rc;
SQLITE_EXTENSION_INIT2(pApi)
rc = sqlite3_create_collation(db, "PATH", SQLITE_UTF16_ALIGNED,
NULL, _cmp);
fprintf(stderr, "collation create: %d\n", rc);
sqlite> pragma encoding = 'utf16le';
sqlite> select load_extension('path.dll');
collation create: 0
sqlite> create table test (str collate path);
SQL error: no such collation sequence: path
sqlite>
If I set it back to SQLITE_UTF16, it recognizes the "path" collation again.
csmith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
On Jul 21, 2008, at 8:32 AM, C. Smith wrote:
>
> I didn't know the strings weren't nul terminated. I changed my
> callback to:
>
> static int _cmp(void *pCtx, int alen, const void *a,
> int blen, const void *b)
> {
> int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b,
> (alen < blen) ? alen : blen);
>
> if(r == 0)
> r = alen - blen;
>
> return r;
> }
>
> Still doesn't work though :(
>
How did you register the collating sequence? Did you use the
SQLITE_UTF16_ALIGNED argument on the 3rd parameter?
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
D. Richard Hipp wrote:
> On Jul 21, 2008, at 12:05 AM, C. Smith wrote:
>
>> The collation is
>> a case-insensitive wchar compare for windows (using _wcsicmp).
>
> The strings passed to a collating function are not zero-terminated.
> Are you making a copy of both input strings and adding a zero
> terminator yourself, or are you really using _wcsnicmp()?
>
> You might want to have a look at how the "NOCASE" collation is
> implemented in the nocaseCollatingFunc() function in the main.c source
> file of SQLite.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
I didn't know the strings weren't nul terminated. I changed my callback to:
static int _cmp(void *pCtx, int alen, const void *a,
int blen, const void *b)
{
int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b,
(alen < blen) ? alen : blen);
if(r == 0)
r = alen - blen;
return r;
}
Still doesn't work though :(
sqlite> pragma encoding = 'utf16le';
sqlite> select load_extension('ext.dll');
sqlite> create table test (str collate path);
sqlite> create unique index myidx on test (str);
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'ABC';
sqlite> select * from test where str = 'abc';
sqlite> insert into test values ('abc');
sqlite> select 'abc' = 'ABC' collate path;
1
sqlite>
Still let me put two 'abc' records in and it still doesn't find any 'abc'
records. Yet, the collation is working if you look at the last select.
Andrew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
Robert Simpson wrote:
> I just tried the same steps on a memorydb using the NOCASE collation
> sequence, and it worked fine ...
>
>
> C:\Src>sqlite3 :memory:
> SQLite version 3.6.0
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .headers on
> sqlite> create table foo(myvalue text collate nocase);
> sqlite> create unique index fooidx on foo(myvalue);
> sqlite>
> sqlite> insert into foo(myvalue) values('abc');
> sqlite> insert into foo(myvalue) values('abc');
> SQL error: column myvalue is not unique
> sqlite> insert into foo(myvalue) values('Abc');
> SQL error: column myvalue is not unique
> sqlite> insert into foo(myvalue) values('ABC');
> SQL error: column myvalue is not unique
> sqlite> drop index fooidx;
> sqlite> select * from foo;
> myvalue
> abc
> sqlite> create unique index fooidx on foo(myvalue);
> sqlite> insert into foo(myvalue) values('ABC');
> SQL error: column myvalue is not unique
>
>
>
Okay. Two things:
1. NOCASE is a built-in collation. My issue is with a user-defined.
2. NOCASE only folds ASCII (26 chars), I need a more complete solution.
Try the below steps on windows. Not sure if you have microsoft's compiler
though, cl.exe. I am using version 7 (2003).
#include
#include
SQLITE_EXTENSION_INIT1
static int _cmp(void *pCtx, int alen, const void *a,
int blen, const void *b)
{
// for testing, not sure this crt func is the best solution
return _wcsicmp((const wchar_t *)a, (const wchar_t *)b);
}
__declspec(dllexport) int sqlite3_extension_init(sqlite3 *db,
char **pzErrMsg,const sqlite3_api_routines *pApi)
{
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_collation(db, "PATH", SQLITE_UTF16, NULL, _cmp);
return 0;
}
Compile the above: (sqlite3ext.h in current directory)
C:\test\sqlite> cl /nologo /W3 /LD /I. /Fepath.dll path.c sqlite3.lib
path.c
Creating library andy.lib and object andy.exp
C:\test\sqlite> sqlite testdb
sqlite> select load_extension('path.dll');
Now repeat your steps but use 'collate path'.
Andrew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
Is there a way to SELECT UNION all of the tables in a database -- that is to create a big table with rows from each of the individual tables? I understand I can do multiple selects, but the command gets very long if there are a lot of tables. Thanks - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Monday, July 21, 2008 7:18 AM Subject: Re: [sqlite] User-defined collation UNIQUE INDEX > > On Jul 21, 2008, at 12:05 AM, C. Smith wrote: > >> The collation is >> a case-insensitive wchar compare for windows (using _wcsicmp). > > The strings passed to a collating function are not zero-terminated. > Are you making a copy of both input strings and adding a zero > terminator yourself, or are you really using _wcsnicmp()? > > You might want to have a look at how the "NOCASE" collation is > implemented in the nocaseCollatingFunc() function in the main.c source > file of SQLite. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] User-defined collation UNIQUE INDEX
On Jul 21, 2008, at 12:05 AM, C. Smith wrote: > The collation is > a case-insensitive wchar compare for windows (using _wcsicmp). The strings passed to a collating function are not zero-terminated. Are you making a copy of both input strings and adding a zero terminator yourself, or are you really using _wcsnicmp()? You might want to have a look at how the "NOCASE" collation is implemented in the nocaseCollatingFunc() function in the main.c source file of SQLite. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
I just tried the same steps on a memorydb using the NOCASE collation
sequence, and it worked fine ...
C:\Src>sqlite3 :memory:
SQLite version 3.6.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .headers on
sqlite> create table foo(myvalue text collate nocase);
sqlite> create unique index fooidx on foo(myvalue);
sqlite>
sqlite> insert into foo(myvalue) values('abc');
sqlite> insert into foo(myvalue) values('abc');
SQL error: column myvalue is not unique
sqlite> insert into foo(myvalue) values('Abc');
SQL error: column myvalue is not unique
sqlite> insert into foo(myvalue) values('ABC');
SQL error: column myvalue is not unique
sqlite> drop index fooidx;
sqlite> select * from foo;
myvalue
abc
sqlite> create unique index fooidx on foo(myvalue);
sqlite> insert into foo(myvalue) values('ABC');
SQL error: column myvalue is not unique
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
Sent: Sunday, July 20, 2008 9:52 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined collation UNIQUE INDEX
No, assigning collation during index creation makes no difference. I also
tried
REINDEX with no luck.
I am using verison 3.5.9 on a winxp box (forgot to mention that).
csmith
Robert Simpson wrote:
> Does this work?
>
> CREATE UNIQUE INDEX myidx ON test(str COLLATE path);
>
> Robert
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
> Sent: Sunday, July 20, 2008 9:05 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] User-defined collation UNIQUE INDEX
>
> I am trying to add a very simple collation via load_extension. The
> collation is
> a case-insensitive wchar compare for windows (using _wcsicmp). It is
> designed
> for windows pathnames being stored in utf16. All works fine until I try
to
> add
> the final touch, a UNIQUE INDEX.
>
> Question: How do I get a UNIQUE INDEX to follow the collation assigned to
a
> column?
>
> NOTE: in the below examples, I also tried assigning the collation during
> index
> creation rather than table creation ... didn't help any.
>
> -- collation named PATH was loaded via load_extension
> sqlite> create table test (str text collate path);
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> abc
> sqlite> select * from test where str = 'abC';
> abc
> sqlite> select * from test where str = 'aBC';
> abc
>
> The above looks great. Now add the unique index...
>
> sqlite> create unique index myidx on test (str);
> sqlite> insert into test values ('abc');
>
> The above is the first problem. The unique index should of detected that
> the
> value 'abc' already existed in a 'str' column.
>
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> The next problem is that the above select does not find any matching
> records, as
> it did prior to the unique index being added. I thought it could be
because
> the
> table is messed up due to duplicate column values? So I did the below:
>
> sqlite> delete from test;
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> Still no result. I then removed the unique index:
>
> sqlite> drop index myidx;
> sqlite> select * from test where str = 'ABC';
> abc
> sqlite>
>
> Thanks,
> csmith
>
>
___
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] User-defined collation UNIQUE INDEX
No, assigning collation during index creation makes no difference. I also
tried
REINDEX with no luck.
I am using verison 3.5.9 on a winxp box (forgot to mention that).
csmith
Robert Simpson wrote:
> Does this work?
>
> CREATE UNIQUE INDEX myidx ON test(str COLLATE path);
>
> Robert
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
> Sent: Sunday, July 20, 2008 9:05 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] User-defined collation UNIQUE INDEX
>
> I am trying to add a very simple collation via load_extension. The
> collation is
> a case-insensitive wchar compare for windows (using _wcsicmp). It is
> designed
> for windows pathnames being stored in utf16. All works fine until I try to
> add
> the final touch, a UNIQUE INDEX.
>
> Question: How do I get a UNIQUE INDEX to follow the collation assigned to a
> column?
>
> NOTE: in the below examples, I also tried assigning the collation during
> index
> creation rather than table creation ... didn't help any.
>
> -- collation named PATH was loaded via load_extension
> sqlite> create table test (str text collate path);
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> abc
> sqlite> select * from test where str = 'abC';
> abc
> sqlite> select * from test where str = 'aBC';
> abc
>
> The above looks great. Now add the unique index...
>
> sqlite> create unique index myidx on test (str);
> sqlite> insert into test values ('abc');
>
> The above is the first problem. The unique index should of detected that
> the
> value 'abc' already existed in a 'str' column.
>
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> The next problem is that the above select does not find any matching
> records, as
> it did prior to the unique index being added. I thought it could be because
> the
> table is messed up due to duplicate column values? So I did the below:
>
> sqlite> delete from test;
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> Still no result. I then removed the unique index:
>
> sqlite> drop index myidx;
> sqlite> select * from test where str = 'ABC';
> abc
> sqlite>
>
> Thanks,
> csmith
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
Does this work?
CREATE UNIQUE INDEX myidx ON test(str COLLATE path);
Robert
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
Sent: Sunday, July 20, 2008 9:05 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] User-defined collation UNIQUE INDEX
I am trying to add a very simple collation via load_extension. The
collation is
a case-insensitive wchar compare for windows (using _wcsicmp). It is
designed
for windows pathnames being stored in utf16. All works fine until I try to
add
the final touch, a UNIQUE INDEX.
Question: How do I get a UNIQUE INDEX to follow the collation assigned to a
column?
NOTE: in the below examples, I also tried assigning the collation during
index
creation rather than table creation ... didn't help any.
-- collation named PATH was loaded via load_extension
sqlite> create table test (str text collate path);
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
abc
sqlite> select * from test where str = 'abC';
abc
sqlite> select * from test where str = 'aBC';
abc
The above looks great. Now add the unique index...
sqlite> create unique index myidx on test (str);
sqlite> insert into test values ('abc');
The above is the first problem. The unique index should of detected that
the
value 'abc' already existed in a 'str' column.
sqlite> select * from test where str = 'abc';
sqlite>
The next problem is that the above select does not find any matching
records, as
it did prior to the unique index being added. I thought it could be because
the
table is messed up due to duplicate column values? So I did the below:
sqlite> delete from test;
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
sqlite>
Still no result. I then removed the unique index:
sqlite> drop index myidx;
sqlite> select * from test where str = 'ABC';
abc
sqlite>
Thanks,
csmith
___
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
