SQLite Forum

According to the [collation documentation](https://sqlite.org/c3ref/create_collation.html), collating functions must obey some important constraints for SQLite to work correctly.

    The collating function must obey the following properties for all strings A, B, and C:

    If A==B then B==A.
    If A==B and B==C then A==C.
    If A<B THEN B>A.
    If A<B and B<C then A<C.
    If a collating function fails any of the above constraints and that collating function is registered and used, then the behavior of SQLite is undefined.

Do these properties have to hold for the life of the persisted .db file, or only during the process's lifetime?

Specifically, what if a collation needs to release a new version? For example, suppose Unicode defines a new code point, and the collation needs to sort it in the new-correct position for that now-defined code point. Is there way the collation can go through an upgrade process that is guaranteed not to break anything? If some users had started using the code point immediately after it was defined by the Unicode folks but before the collation upgrade was published, might the database file become unusable when the collation is upgraded?

Or, to be more concrete, suppose a collation uses Windows APIs such as [CompareStringEx](https://docs.microsoft.com/en-us/windows/win32/api/stringapiset/nf-stringapiset-comparestringex), which very occasionally changes (for example, on some Windows major version upgrades; whenever [GetNLSVersionEx](https://docs.microsoft.com/en-us/windows/win32/api/winnls/nf-winnls-getnlsversionex) changes). [Related documentation](https://docs.microsoft.com/en-us/windows/win32/intl/handling-sorting-in-your-applications#index-the-database) suggests re-indexing database strings to handle any changes in the new localization version. Is there a good way to do that with SQLite? If it's just an index, I suspect dropping and re-creating the index would work, but what if it's part of the table definition, such as:

CREATE TABLE UsesWindowsLocale(Name TEXT PRIMARY KEY COLLATE Unicode_Windows_en_US);

Where the collation does something like:
```c
return CompareStringEx(
        L"en-US",
        0,
        (PCWCH)left,
        left_size / sizeof(wchar_t),
        (PCWCH)right,
        right_size / sizeof(wchar_t),
        NULL,
        NULL,
        0) - 2;
```

Is there a way to handle a Windows version upgrade that includes locale changes without dropping and re-creating the table?

If not, could/should there be? Perhaps some mechanism could allow telling "you had a valid collation before, and you have a new one now; please re-index" or even "here's the old and here's the new; please update the internal b-tree sorting accordingly."

Thanks,

David