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 <sqlite3ext.h>
#include <string.h>
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
