I'm guessing that LOCALE_NOCASE will probably be causing things that collate distinct in NOCASE to collate equal, so the risk of breaking UNIQUE constraints seems rather small
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Richard Hipp Gesendet: Dienstag, 01. Oktober 2019 19:41 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] Newbie Issues with COLLATE 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
