SQLiteException: ‘no such collation sequence: unicase’

Recently, I’ve been prototyping various plugins and tools for Anki (flashcard management system) in C#. So far, the biggest technical obstacle I encountered was the following exception thrown when I tried to query data from the database:

System.Data.SQLite.SQLiteException: ‘SQL logic error, no such collation sequence: unicase‘

or, when I used Microsoft.Data.Sqlite NuGet package instead of System.Data.SQLite:

Microsoft.Data.Sqlite.SqliteException: ‘SQLite Error 1: ‘no such collation sequence: unicase‘.’

Understanding the issue

It seems that Anki decided to use a non-standard collation named unicase for some columns.

Collation is a declaration of what algorithm the database engine should use to compare data in the column (to determine whether values are equal or one is greater than another). For string columns, collation is relevant e.g., in the case of SQL operations like DISTINCT or ORDER BY.

I tracked that the unicase collation Anki uses has its implementation hosted in GitHub (see the seanmonstar/unicase project). The value added (as compared to the standard “NOCASE” collation) seems to be that it considers strings with international characters and their transcriptions equal (like “MASSE” and “Maße”).

Here’s a demonstration showing that the collation is set for one of the columns in the database:

Making SQL queries work in .NET

To get this to work in .NET, we need to provide a .NET implementation for the “unicase” comparison to the SQLite connection.

Firstly, if you use System.Data.SQLite NuGet package, you might need to migrate to Microsoft.Data.Sqlite which provides the SqliteConnection.CreateCollation(...) method. I could not find an easy way to achieve it with the System.Data.SQLite library, so I’m not sure it’s possible – that would require further research if you don’t wish to migrate.

Then, you need to register a collation named unicase:

var ankiDatabasePath = "c:\Users\...\AppData\Roaming\Anki2\...\collection.anki2"

using var connection = new Microsoft.Data.Sqlite.SqliteConnection($"Data Source={ankiDatabaseFilePath};");
connection.Open();

connection.CreateCollation("unicase", (x, y) => String.Compare(x, y, StringComparison.OrdinalIgnoreCase));

var query = $@"SELECT DISTINCT notetypes.name FROM notetypes";
using var command = new Microsoft.Data.Sqlite.SqliteCommand(query, connection);
using var reader = command.ExecuteReader(); // this one should now work without exception
Code language: JavaScript (javascript)

For my use cases, the simple case-ignoring collation StringComparison.OrdinalIgnoreCase is enough. If you want to mimic unicase‘s library way of sorting and comparing international strings on the database side, you might need to find or implement similar logic in C#.

At this point, queries should work. I assembled a small project on GitHub to show how to reproduce the issue and how to fix it.

Have fun hacking Anki and creating cool things!