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