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 ofSystem.Data.SQLite
:Microsoft.Data.Sqlite.SqliteException: ‘SQLite Error 1: ‘no such collation sequence: unicase‘.’
![](https://taurit.pl/wp-content/uploads/2024/07/obraz-6.png)
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:
![](https://taurit.pl/wp-content/uploads/2024/07/obraz-7.png)
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!
No comments yet, you can leave the first one!