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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
