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
