(System environment: Windows 10 using sqlite-tools-win32-x86-3290000.zip,
running SQLite3.exe as administrator)
I'm a newbie to SQLite but not to SQL. Apologies in advance if this has
been covered in the Archives and I just haven't found it after two days of
searching.
I'm trying to use SQLite3.exe (the command-line executable) to do some data
mining of a database generated by a commercial software product that
incorporates the .dll version (I can see sqlite3.dll in its Program Files
folder). That commercial software has a menu option to make a backup copy
of the database, and that's what I'm attempting to mine. I'm only using
SELECT against it.
Many of my attempted queries, especially those with JOINs on TEXT columns
fail with an "Error: no such collation sequence: LOCALE_NOCASE" message. A
specific example:
sqlite> select k.Keyword, count(ka.ImageID) from Keywords k
...> inner join KeywordAssignments ka on ka.KeywordID = k.KeywordID
...> group by k.Keyword;
Error: no such collation sequence: LOCALE_NOCASE
Using .schema to expose the structure of the Keywords table and the
KeywordAssignments table shows:
sqlite> .schema Keywords
CREATE TABLE IF NOT EXISTS 'Keywords'(KeywordID INTEGER PRIMARY KEY, Keyword
TEXT NOT NULL UNIQUE COLLATE LOCALE_NOCASE);
sqlite> .schema KeywordAssignments
CREATE TABLE IF NOT EXISTS 'KeywordAssignments'(ImageID NUMERIC NOT NULL,
KeywordID NUMERIC NOT NULL, PRIMARY KEY(ImageID, KeyWordID));
The "LOCALE_NOCASE" error message is obviously caused by the Keyword column
in the Keywords table that is declared as COLLATE LOCALE_NOCASE. Searching
the SQLite and third-party websites for "LOCALE_NOCASE" suggests that this
particular collation is not built-in to SQLite, but may have been developed
by the commercial software programmers. Since I am accessing the database
from the SQLite3 command line executable (and not the software's .dll), I
seem to have no access to collations that begin with "LOCALE_". QUESTION
ONE: Are these "LOCALE_..." collations custom collations developed by the
software programmers, or are they something I just haven't discovered yet in
the SQLite documentation?
I've developed a workaround. Starting with the Keywords table schema, I
CREATE TABLE a "myKeywords" table with the same columns but omitting the
COLLATE LOCALE_NOCASE declaration. Then I INSERT INTO the new "myKeywords"
table all of the data from the original Keywords table. My original query
then runs without error against the new "myKeywords" table. But this is a
pretty onerous approach.
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?
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!
TIA for any suggestions anyone can provide.
Jim
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
