Кастомные функции SQLite Android или своя LOWER_FNC()

SELECT * WHERE LOWER_FNC(name) like '%" + filterText + "%'"

При разработке Android приложения столкнулся с проблемой в запросе SQLite фильтра с русскими буквами. Для английской локализации проблем нет. Для других интернациональных раскладок некорректно обрабатывались заглавные буквы в запросе.
Немного разобравшись я наткнулся на следующее описание:

(18) Case-insensitive matching of Unicode characters does not work.

The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.

Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines.

Вероятно текущая реализация SQLite Android и есть

only supports case-insensitive comparisons of ASCII characters

Видел решение через CursorWrapper но решил все таки собрать свою версию SQLite и использовать addCustomFunction

Что из этого получилось читайте под катом

Идея использовать прямой и обратный обмен данными с SQLite библиотекой собственной (custom) сборки
Для начала смотрим инструкцию SQLite Android Bindings
Я использовал версию Android API levels 15 (Android 4.0.3). Небольшое отличие в дополнительной папке или пакете package org.sqlite.os;

Дальше стандартно собираем через NDK sqliteX библиотеку. Подключаем к проекту. И грузим нашу библиотеку

System.loadLibrary("sqliteX");

Теперь определяем нашу пользовательскую функцию, которая будет вызываться из SQL запроса

    private final SQLiteDatabase.CustomFunction mLowerFnc =
                         new SQLiteDatabase.CustomFunction() {
                 @Override
                 public void callback(String[] args) {
                     String text = args[0];
                     text = text.toLowerCase();
                     Log.d(LOG, "LOWER_FNC : " + text);
                     return;
                }
        };

Сама функция подключается следующим образом

public class DataBase extends SQLiteOpenHelper {
...
    public DataBase(Context context) {
        super(context, context.getDatabasePath(DATABASE_NAME).getPath(), null, DATABASE_VERSION);
        context.openOrCreateDatabase(context.getDatabasePath(DATABASE_NAME).getPath(), context.MODE_PRIVATE, null);
    }

    public void open() throws SQLException {
        database = getWritableDatabase();
        database.addCustomFunction("LOWER_FNC", 1, mLowerFnc);
    }

Параметры: Название функции, по которой она будет вызвана из SQLite строки запроса. Количество аргументов, в данном случае входная строка и собственно сама функция-обработчик

Обратите внимание, что открывать базу надо по полному пути. Вариант получения полного пути:

DB_PATH = getApplicationContext().getDatabasePath("test.db");
DB_PATH.mkdirs();

В логах видим вызов нашей функции LOWER_FNC и строки из запроса. Отлично!
А что дальше? Как использовать эти строки и вернуть их обратно в обработанном виде?

Смотрим исходники SQLite:

// Called each time a custom function is evaluated.
static void sqliteCustomFunctionCallback(sqlite3_context *context,
        int argc, sqlite3_value **argv) {
...
        // TODO: Support functions that return values.
        env->CallVoidMethod(functionObj,
                gSQLiteCustomFunctionClassInfo.dispatchCallback, argsArray);
...

Видим CallVoidMethod и далее TODO: Support functions that return values
Замечательно. Авторы не допилили. Придется самому…
Скажу что подход был найден не сразу. Потрачено два дня, но результат был достигнут. А это главное

	result = env->CallObjectMethod( functionObj, gSQLiteCustomFunctionClassInfo.dispatchCallback, argsArray);
	char_result = env->GetStringUTFChars( (jstring) result, NULL);
	sqlite3_result_text(context, char_result, -1, SQLITE_TRANSIENT);

Вместо CallVoidMethod делаем CallObjectMethod в которой забираем у Android строку

// Called each time a custom function is evaluated.
static void sqliteCustomFunctionCallback(sqlite3_context *context,
        int argc, sqlite3_value **argv) {
    jobject result;
    JNIEnv* env = 0;
    const char* char_result;

    gpJavaVM->GetEnv((void**)&env, JNI_VERSION_1_4);

    // Get the callback function object.
    // Create a new local reference to it in case the callback tries to do something
    // dumb like unregister the function (thereby destroying the global ref) while it is running.
    jobject functionObjGlobal = reinterpret_cast<jobject>(sqlite3_user_data(context));
    jobject functionObj = env->NewLocalRef(functionObjGlobal);

    jobjectArray argsArray = env->NewObjectArray(argc, gStringClassInfo.clazz, NULL);
    if (argsArray) {
        for (int i = 0; i < argc; i++) {
            const jchar* arg = static_cast<const jchar*>(sqlite3_value_text16(argv[i]));
            if (!arg) {
                ALOGW("NULL argument in custom_function_callback.  This should not happen.");
            } else {
                size_t argLen = sqlite3_value_bytes16(argv[i]) / sizeof(jchar);
                jstring argStr = env->NewString(arg, argLen);
                if (!argStr) {
                    goto error; // out of memory error
                }
                env->SetObjectArrayElement(argsArray, i, argStr);
                env->DeleteLocalRef(argStr);
            }
        }

        // TODO: Support functions that return values.
        //env->CallVoidMethod(functionObj,
        //        gSQLiteCustomFunctionClassInfo.dispatchCallback, argsArray);

	result = env->CallObjectMethod( functionObj, gSQLiteCustomFunctionClassInfo.dispatchCallback, argsArray);
	char_result = env->GetStringUTFChars( (jstring) result, NULL);
	sqlite3_result_text(context, char_result, -1, SQLITE_TRANSIENT);

error:
        env->DeleteLocalRef(argsArray);
    }

    env->DeleteLocalRef(functionObj);
    env->DeleteLocalRef(result);

    if (env->ExceptionCheck()) {
        ALOGE("An exception was thrown by custom SQLite function.");
        /* LOGE_EX(env); */
        env->ExceptionClear();
    }
}

Есть еще один момент. Надо изменить register_android_database_SQLiteConnection добавив Ljava/lang/String; Это строка, которая вернется из Android приложения. Иначе Android OS не найдет нашу новую реализацию

int register_android_database_SQLiteConnection(JNIEnv *env)
{
    jclass clazz;
    FIND_CLASS(clazz, "org/sqlite/database/sqlite/SQLiteCustomFunction");

    GET_FIELD_ID(gSQLiteCustomFunctionClassInfo.name, clazz,
            "name", "Ljava/lang/String;");
    GET_FIELD_ID(gSQLiteCustomFunctionClassInfo.numArgs, clazz,
            "numArgs", "I");
    GET_METHOD_ID(gSQLiteCustomFunctionClassInfo.dispatchCallback,
            clazz, "dispatchCallback", "([Ljava/lang/String;)Ljava/lang/String;");

    FIND_CLASS(clazz, "java/lang/String");
    gStringClassInfo.clazz = jclass(env->NewGlobalRef(clazz));

    return jniRegisterNativeMethods(env, 
        "org/sqlite/database/sqlite/SQLiteConnection",
        sMethods, NELEM(sMethods)
    );
}

Заключительный этап. Изменяем callback и interface, так чтобы он возвращал String

    private final SQLiteDatabase.CustomFunction mLowerFnc =
                         new SQLiteDatabase.CustomFunction() {
                 @Override
                 public String callback(String[] args) {
                     String text = args[0];
                     text = text.toLowerCase();
                     Log.d(LOG, "LOWER_FNC : " + text);
                     return text;
                }
        };

...

    /**
     * A callback interface for a custom sqlite3 function.
     * This can be used to create a function that can be called from
     * sqlite3 database triggers.
     * @hide
     */
    public interface CustomFunction {
        public String callback(String[] args);
    }

Таким образом можно переопределить любую функцию, надстроить или сделать свою с уникальной функциональностью. Применение все это нашло в проекте Air Tickets
Используется feed Aviasales, но это уже совсем другая история

Надеюсь статья будет полезна. Пишите SQLite запросы со своей функциональностью!

Материалы статьи:
SQLite Android Bindings
Android NDK

Автор: app-z

Источник