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
