On 10/1/19, Jim and Carol Ingram <ingram-ho...@mysteriousgrace.org> wrote: > QUESTION > ONE: Are these "LOCALE_..." collations custom collations developed by the > software programmers,
Yes. The application is using the sqlite3_create_collation() API (https://www.sqlite.org/c3ref/create_collation.html) to create a new collating sequence that they are calling "LOCALE_NOCASE". We can guess at what that collating sequence does from its name, but without seeing the code, we don't really know. > > I have noted by trial and error that modifying the original query's last > line to include COLLATE NOCASE ("group by k.Keyword COLLATE NOCASE;") makes > the query work against the original Keywords table without error. I suspect > this is the correct way to address the error message rather than my > admittedly messy and time-consuming workaround. QUESTION TWO: Can anyone > verify that this is indeed the acceptable way to address the error message, > or provide a more acceptable way to do it? That seems like a reasonable approach to me! Another thing to consider, depending on how much data there is, is to run the ".dump" command to convert the whole database into a big pile of SQL. Then edit the SQL to change LOCALE_NOCASE into just NOCASE, and reimport it into a new SQLite database. Then all of your queries will work correctly. Or, you could set "PRAGMA writable_schema=ON" and then do an UPDATE statement on the sqlite_master table to actually change the text of the CREATE TABLE statement: PRAGMA writable_schema=on; UPDATE sqlite_master SET sql=replace(sql,'LOCALE_NOCASE','NOCASE') WHERE name LIKE 'keywords'; Then exit the command-line tool and reopen and type "REINDEX". Then you should be good to go. Warning: Make a backup copy first, as things might to wrong. In particular, the change from LOCALE_NOCASE to just NOCASE might possibly cause the UNIQUE constraint to start failing in one or more cases. (Unlikely, but possible.) So be prepared to work around such difficulties. > And finally, QUESTION THREE: Can anyone point me to a thorough and detailed > tutorial of the whole SQLite COLLATE subject, including creation of custom > collations and loading them into the SQLite command-line executable if > possible? The documentation seems really thin in this area! > The https://www.sqlite.org/c3ref/create_collation.html document is about all we have. There might be more information in some of the books about SQLite. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
