Sqldiff no such collation sequence

(1) By anonymous on 2022-11-13 15:41:47 [source]

Hi. I'm currently trying to compute the diff (using sqldiff) between two versions of a database with a custom collation. However, I don't know how to define the collation for sqldiff so that it can make sense of it. Is there a way to ignore this error or something?

The internet suggests it should be possible to make clones of each table using this unicase collation, which instead will use NOCASE, and then replace the originals with the clones. Can anyone give a nudge in the right direction of how to do this, if it's the right approach? I'm pretty new to SQLite, so any help is much appreciated!

This is the error I'm seeing:

sqldiff: SQL statement error: no such collation sequence: unicase
"SELECT B.id, 1, -- changed row
       A.name IS NOT B.name, B.name,
       A.mtime_secs IS NOT B.mtime_secs, B.mtime_secs,
       A.usn IS NOT B.usn, B.usn,
       A.config IS NOT B.config, B.config
  FROM main.deck_config A, aux.deck_config B
 WHERE A.id=B.id
   AND (A.name IS NOT B.name
        OR A.mtime_secs IS NOT B.mtime_secs
        OR A.usn IS NOT B.usn
        OR A.config IS NOT B.config)
 UNION ALL
SELECT A.id, 2, -- deleted row
       NULL, NULL,
       NULL, NULL,
       NULL, NULL,
       NULL, NULL
  FROM main.deck_config A
 WHERE NOT EXISTS(SELECT 1 FROM aux.deck_config B
                   WHERE A.id=B.id)
 UNION ALL
SELECT B.id, 3, -- inserted row
       1, B.name,
       1, B.mtime_secs,
       1, B.usn,
       1, B.config
  FROM aux.deck_config B
 WHERE NOT EXISTS(SELECT 1 FROM main.deck_config A
                   WHERE A.id=B.id)
 ORDER BY 1;
"

(2) By Larry Brasfield (larrybr) on 2022-11-25 18:21:03 in reply to 1 [link] [source]

That unknown collation sequence, "unicase", was undoubtedly provided by a SQLite extension. The simplest approach to using sqldiff with your DBs that rely upon that collation sequence (and hence upon that extension) is to load the extension when running sqldiff.

If you run "sqldiff --help", it will say how to load extensions. And, provided that their entry points are named in the strongly suggested way¹, you can use a sqlfiff option to get them loaded. With the needed extension(s) loaded, the error and complaint about a strange collation will not occur.

If that extension entry point is goofily named, you are out of luck with sqldiff unless you can persuade the extension's author to follow the programming guidance², or you can do so yourself and rebuild the extension.

^{^} Per section 4, "Programming ...", of Runtime-Loadable Extensions
^{^} It should be possible to provide an entry point that adheres to the guidance even if the goofy one must be retained for backwards compatibility.