Workaround for using Android's LOCALIZED collator after 3.5.0 update

SQLCipher Issues
14

@un1v3rse - a collation can be defined via loadable extension. You basically just need to call sqlite3_create_collation_v2 from within sqlite3_extension_init. That function is called automatically by when the extension is loaded, thus registering the collation.

It is true that collations may be used in schema. In practice that is not a problem as long as you load the extension prior to executing any statements that will use the collations. For example, you can simply open your database, then call SELECT load_extension('<your extension here>'); to register the collation, and the go on as normal. If you try to execute a statement that uses the collation without the extension being loaded, you’ll just get an error (e.g. no such collation sequence).

Here is a simple reference that implements a collation named RANDOM that is runtime-loadable via the extension mechanism:

#include <stdlib.h>
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

static int collate_random(void *ctx, int n0, const void *v0, int n1, const void *v1) {
  return (rand() % 3) - 1;
}

int sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_collation_v2(db, "RANDOM", SQLITE_UTF8, NULL, collate_random, NULL);
  return 0;
}

Obviously a random collation sequence as little practical value; this simply serves as a demonstration of the technique. Expanding upon this and building it as an android .so is left as a further exercise.