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
