Custom SQLite Android functions or its own LOWER_FNC ()
SELECT * WHERE LOWER_FNC (name) like '% "+ filterText +"%' "
When developing an Android application, I ran into a problem in a SQLite filter query with Russian letters. There are no problems with English localization. For other international layouts, capitalized letters in the request were incorrectly processed.
Having a little understood I came across the following description:
(18) Case-insensitive matching of Unicode characters does not work.
')
The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. It would be a little bit different for the SQLite library. It’s not worth it.
Instead of providing full Unicode case support by default, SQLite provides a linking and conversion routines.
Probably the current implementation of SQLite Android is
I saw the solution through CursorWrapper but decided to build my version of SQLite and use addCustomFunction
What came out of this read under the cut
The idea of using direct and reverse data exchange with the SQLite library of your own (custom) assembly
To start, look at the SQLite Android Bindings instruction.
I used the Android API levels 15 (Android 4.0.3) version. There is a slight difference in the additional folder or package package org.sqlite.os;
Further standardly we collect library through NDK sqliteX. We connect to the project. And ship our library
Now we define our user function, which will be called from the SQL query.
The function itself is connected as follows.
Parameters: The name of the function by which it will be called from the SQLite query string. The number of arguments, in this case, the input string and the handler function itself
Please note that you need to open the base along the full path. Option to get the full path:
In the logs we see the call to our function LOWER_FNC and the lines from the request. Fine!
What's next? How to use these lines and return them back in processed form?
We watch source codes of SQLite:
We see CallVoidMethod and further TODO: Support functions that return values
Wonderful. The authors did not finish. I'll have to myself ...
I will say that the approach was not found immediately. Spent two days, but the result was achieved. And this is the main thing
Instead of CallVoidMethod, we do a CallObjectMethod in which we pick up a string from Android
There is one more thing. You need to change in register_android_database_SQLiteConnection:
Adding Ljava / lang / String; as a parameter. This is a string that will be returned from the Android application. Otherwise, Android OS will not find our new implementation.
The final stage. Change the callback and interface so that it returns a String.
Thus, you can redefine any function, build on it or make your own with unique functionality. The application is all found in the project Air Tickets
Used feed Aviasales, but that's another story
I hope the article will be useful. Write SQLite queries with your functionality!
Article materials:
SQLite Android Bindings
Android NDK
When developing an Android application, I ran into a problem in a SQLite filter query with Russian letters. There are no problems with English localization. For other international layouts, capitalized letters in the request were incorrectly processed.
Having a little understood I came across the following description:
(18) Case-insensitive matching of Unicode characters does not work.
')
The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. It would be a little bit different for the SQLite library. It’s not worth it.
Instead of providing full Unicode case support by default, SQLite provides a linking and conversion routines.
Probably the current implementation of SQLite Android is
only supports case-insensitive comparisons of ASCII characters
I saw the solution through CursorWrapper but decided to build my version of SQLite and use addCustomFunction
What came out of this read under the cut
The idea of using direct and reverse data exchange with the SQLite library of your own (custom) assembly
To start, look at the SQLite Android Bindings instruction.
I used the Android API levels 15 (Android 4.0.3) version. There is a slight difference in the additional folder or package package org.sqlite.os;
Further standardly we collect library through NDK sqliteX. We connect to the project. And ship our library
System.loadLibrary("sqliteX"); Now we define our user function, which will be called from the SQL query.
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; } }; The function itself is connected as follows.
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); } Parameters: The name of the function by which it will be called from the SQLite query string. The number of arguments, in this case, the input string and the handler function itself
Please note that you need to open the base along the full path. Option to get the full path:
DB_PATH = getApplicationContext().getDatabasePath("test.db"); DB_PATH.mkdirs(); In the logs we see the call to our function LOWER_FNC and the lines from the request. Fine!
What's next? How to use these lines and return them back in processed form?
We watch source codes of 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); ... We see CallVoidMethod and further TODO: Support functions that return values
Wonderful. The authors did not finish. I'll have to myself ...
I will say that the approach was not found immediately. Spent two days, but the result was achieved. And this is the main thing
result = env->CallObjectMethod( functionObj, gSQLiteCustomFunctionClassInfo.dispatchCallback, argsArray); char_result = env->GetStringUTFChars( (jstring) result, NULL); sqlite3_result_text(context, char_result, -1, SQLITE_TRANSIENT); Instead of CallVoidMethod, we do a CallObjectMethod in which we pick up a string from Android
Full version of the function
// 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(); } } There is one more thing. You need to change in register_android_database_SQLiteConnection:
GET_METHOD_ID(gSQLiteCustomFunctionClassInfo.dispatchCallback, clazz, "dispatchCallback", "([Ljava/lang/String;)Ljava/lang/String;"); Adding Ljava / lang / String; as a parameter. This is a string that will be returned from the Android application. Otherwise, Android OS will not find our new implementation.
register_android_database_SQLiteConnection (JNIEnv * env)
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) ); } The final stage. Change the callback and interface so that it returns a String.
Hidden text
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); } Thus, you can redefine any function, build on it or make your own with unique functionality. The application is all found in the project Air Tickets
Used feed Aviasales, but that's another story
I hope the article will be useful. Write SQLite queries with your functionality!
Article materials:
SQLite Android Bindings
Android NDK
Source: https://habr.com/ru/post/252823/
All Articles