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