On 17 Mar 2017, at 10:12pm, Rob Willett <rob.sql...@robertwillett.com> wrote:
> 3. We have looked through all our indexes and can see that every index has a > COLLATE against it, even if the column is an integer. We have raised a > support call with Navicat. This might also be something that Navicat should look into. The most efficient way to implement COLLATE in SQLite is to do it in the column definition. At the moment, for example, the table is defined as > CREATE TABLE "Disruptions" ( > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > […] > "Direction" TEXT > ); . If instead it said CREATE TABLE "Disruptions" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, […] "Direction" TEXT COLLATE NOCASE ); then all comparisons and indexes which mentioned "Direction" would automatically use NOCASE without having to have COLLATE NOCASE mentioned in them. This is almost always The Right Thing, and what the programmer would want. It simplifies all the other SQL commands used by the program. And since the collation on the index then matches the collation for the column definition, SQLite has to do less work every time it deals with the index. Unfortunately, this change cannot be made with backward compatibility. Changing the table definition would involve remaking the table, probably by defining a new table with the new definition, copying the data across, deleting the original table, and renaming the new one. If backward compatibility is important in support of the program then this may be a deal-breaker and one could understand why the developer team won’t make the change. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users