When and how to use COLLATE NOCASE in queries

[mooredan]

In https://github.com/RichardOtter/Genealogy-scripts/tree/main/RM/Run%20SQL#readme , it is recommended that "SQLite built-in NOCASE collation" be used in queries in which the column is "TEXT COLLATE RMNOCASE" type. How would these two queries be changed?

SELECT MediaID, MediaPath, MediaFile, MediaType from MultimediaTable;

UPDATE MultimediaTable SET MediaFile='$MediaFile' WHERE MediaID=$MediaID ;

MediaPath is TEXT type
MediaFile is TEXT COLLATE RMNOCASE type

[RichardOtter]

Hi,
Thanks for the discussion!

I probably need to rework a doc I've written on the topic
https://github.com/RichardOtter/Genealogy-scripts/blob/main/RM/doc/Notes%20on%20collation%20RMNOCASE.md
to include more info on how and when to use COLLATE NOCASE overrides in SQL.

The first SQl in your post-
SELECT MediaID, MediaPath, MediaFile, MediaType from MultimediaTable;
runs as-is.
MediaFile is indeed collated with RMNOCASE, but since the SQL just retrieves
data as it is stored in the tables, the collation is not relevant.

If you were to change the SQL to-

SELECT MediaID, MediaPath, MediaFile, MediaType
FROM MultimediaTable
ORDER BY MediaFile;

then the collation sequence is required since the request is to order the output
and the default ordering in the RM schema is the order specified by RMNOCASE.

To get this new query to work without RMNOCASE, one must override its use by-

SELECT MediaID, MediaPath, MediaFile, MediaType
FROM MultimediaTable
ORDER BY MediaFile COLLATE nocase;

the "collate nocase' clause is placed to the right of the column name in every
situation where the collation is used. Ordering, comparing etc.

another example-

SELECT MediaID, MediaPath, MediaFile, MediaType
FROM MultimediaTable
WHERE MediaFile collate nocase LIKE '%ANC%'
ORDER BY MediaFile COLLATE nocase;

Depending on the data in the tables, the results may differ between collation
sequences, but in a way that is usually not important.

The second SQL in the post is different since it modifies the data.
UPDATE MultimediaTable SET MediaFile='$MediaFile' WHERE MediaID=$MediaID ;

Whenever SQLite modifies data, it updates any indexes associated with that data.
In this case, changing MediaFile will change the index of MediaFile. The first
index I notice is the index created by
CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile);

and since the MultimediaTable was defined as-
CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER,
MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB,
Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT,
SortDate BIGINT, Description TEXT, UTCModDate FLOAT );

the MediaFile column's default collation is RMNOCASE, so the index will also use
RMNOCASE.

Therefore any change in MediaFile will update the index which uses RMNOCASE.

I don't think there is a way to override the update of an index.
I have a number of workarounds that I've never gotten around to testing, but
they all rely on how RM is coded (black box).
For instance, one might just drop all of the indexes that use RMNOCASE in your
session that does the update data and then hope that RM will recreate them when
the Rebuild indexes database tool is run.
Or one could simply change the RM schema to use nocase instead of rmnocase and
then hope that RM will still function as expected.

Let me stop here to see if this is clear so far.
Richard

[mooredan]

Thank you, I understand. In general, if sorting, doing a specific select upon, or updating a COLLATE RMNOCASE column, then qualify the column name with COLLATE NOCASE.

I did not do this recently when changing the value of MediaFile for many rows in the MultimediaTable. It appears that nothing bad happened. But I'll play it safe in the future.

Yes, I do have backups.

[RichardOtter]

Not quite...
When updating data, one must use the "fake" RMNOCASE collation loaded in the DB.
(I can't say that I've tried to adding COLLATE NOCASE to an update..)

complete safe procedure for updating data outside of RM

1 load the "fake" RMNOCASE collation sqlite extension
2 run the "reindex RMNOCASE;" statement so database is consistent with the actual collation in the "fake" RMNOCASE collation sqlite extension
3 make to data updates
4 open the database in RM and then the first thing you do is run the RM database tool- Rebuild Indexes.
(this makes the database consistent with the collation in the "real" RMNOCASE collation used by RM within the app.

[mooredan]

Understood. Thank you.