Workaround for using Android's LOCALIZED collator after 3.5.0 update

SQLCipher Issues
1

Hi,

We are using ORDER BY col_name COLLATE LOCALIZED ASC in our query strings of our Android app. And as we updated to 3.5.2, the queries started to fail.

Following the discussions on here and here, I see that the reason is stated in the release notes:

So, SqlCipher has removed support for using this collation starting from 3.5.0. However, no workaround is given for this problem. Our app uses this a lot and sure, removing this from our query strings is a solution but we do not want to break an existing functionality of the app. What is the official recommendation for this?

2

Hello @kayaatakan

Thanks for getting in touch. As you’ve noted it was unfortunately necessary to remove the built in customized collations in the new version. There isn’t a direct workaround in SQLCipher itself at this time, though we might explore other options in the future. For now:

  1. The most straightforward approach would be modification to the application to remove the dependencies on the LOCALIZED collation. You could consider replacing LOCALIZED with the built in NOCASE collation, which should provide similar sort order at least within the standard ASCII character set.
  2. An alternate option would be to implement a new custom LOCALIZED collation, outside of SQLCipher, and then load it as an extension and register it as a new collation with the same name. This would be logically separated from SQLCipher.
3

Thank you for your answer @sjlombardo.

Unfortunately 1 is not an option for us. It would be if were shipping our app to US. However, our customers are European and sorting with standard ASCII set is just wrong.

And indeed 2 could be the solution; however, methods to register a collation is not exposed through the Java Api of Android. It could be done through the NDK but the workload is too much.

You could still think other higher level workarounds like saving a sort key to a db column but still we doubt that it would worth the effort.

Unfortunately due to this, we are aiming to not to target 24 anymore and we will not update SqlCipher. I guess previous versions of SqlCipher for Android will still work since the private libraries are temporarily accessible until a future Android platform update according to here. And we hope you guys make SqlCipher compatible with Android’s Sqlite until then.

4

Please note that older versions of SQLCipher for Android will crash on Android N (API 24). You should upgrade to the latest library (currently 3.5.2) if your application needs to run on Android N.

You might consider upgrading to the latest SQLCipher for Android, then move sorting of the data internal to your application in Java.

5

Are you sure about this? Let me be more clear:
We will set compileSdkVersion to 24; however, we will set targetSdkVersion to 23. So the app will not target 24 as I said above.

See the row Private (temporarily accessible private libraries) here
N Developer Preview behavior for apps targetting 23 or lower is defined as:

Works as expected, but you receive a logcat warning and a message on the target device.

And for Final N Release behavior:

Works as expected, but you receive a logcat warning.

And for Future Android platform behavior:

Runtime error

And I tested this, and I see that old versions of SqlCipher works perfectly on the N emulator. And a logcat message and a dialog message is displayed as promised above. I don’t see any crash. What makes you say that it will crash? Does older versions of SqlCipher for Android use private libraries that are not made temporarily accessible?

6

Hello @kayaatakan

Yes, the conflict is not with the Java API, but the native libraries and dependencies found the older versions of SQLCipher for Android, they will not operate on Android N. There was an issue opened in March following the initial developer release of Android N showing the deficiencies. We have addressed those issues in our subsequent releases.

7

So, SQLCipher is not going to support LOCALIZED collation anymore? I am using 3.5.3 and ordering is not working with european accents. Its a big ordering problem for languages that uses accents, in Europe we have many.

8

Hello @adso - yes, it was necessary to remove the customized LOCALIZED collations in the new version for N compatibility. We may revisit this in the future, but for the time being the two options mentioned earlier in the thread are the available workarounds at this time.

9

A quick workaround:

  1. Create additional fields in database for those fields to order by
  2. Update the new fields with the original content but normilized. Ex: áéò -> aeo
  3. Order by the new normalized fields (now they are in ASCII and can be used with NOCASE built collation)

How to normalize:
// name is the original name
name = Normalizer.normalize(name, Normalizer.Form.NFD);
name = name.replaceAll("[^\p{ASCII}]", “”);
// Now name is normalized

10

Hello @adso - that is an excellent suggestion!

11

From my perspective, the suggested approaches are hacks:

  • sort in the app (requires replicating database logic in code, no thank you)
  • sort by COLLATE NOCASE (doesn’t work right for international)
  • add a sort column (requires precognition of sorted data, else sweeping text duplication throughout the db, effectively doubling size)

Are there any plans to fix this properly, either by:

  • restoring the Android-style collation support via COLLATE UNICODE
  • adding facility to define collations to sqlite’s engine, i.e. exposing sqlite3_create_collation_v2

Thanks !

12

Hello @un1v3rse - SQLCipher does allow loadable extensions. Therefore, you should be able to create an extension for android with a custom collator and load it dynamically.

13

Thanks for getting back to me @sjlombardo.

To my understanding a function extension isn’t the same as a collation, because the latter is allowed to be specified as a schema property, just like

my_field TEXT COLLATE NOCASE

This is why I suggested exposing sqlite3_create_collation_v2.

I may be mistaken, because I’m having a really hard time finding anything relevant when I google “sqlcipher android loadable extensions” Do you have a link to recent documentation for defining Android extensions in SqlCipher?

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.