Custom SQLite Android functions or your own LOWER_FNC ()
- Tutorial
SELECT * WHERE LOWER_FNC (name) like '% "+ filterText +"%' "
When developing an Android application, I encountered a problem in the SQLite filter query with Russian letters. For English localization, there are no problems. For other international layouts, the capital letters in the query were not processed correctly.
Having a little understanding, 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. 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.
Probably the current implementation of SQLite Android is
I saw the solution through CursorWrapper but still decided to collect my version of SQLite and use addCustomFunction.
What came of it, read under the cat
The idea is to use direct and reverse data exchange with the SQLite library of your own (custom) assembly.
First, we look at the SQLite Android Bindings instruction.
I used the Android API version 15 (Android 4.0.3). A slight difference in the additional folder or package package org.sqlite.os;
Then we collect the standard library through the NDK sqliteX library. We connect to the project. And ship our library
Now we define our custom function that 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 actual function handler itself.
Please note that you need to open the database along the full path. Option to get the full path:
In the logs, we see the call to our function LOWER_FNC and the line from the request. Fine!
What's next? How to use these lines and return them back in processed form?
We look at the source code of SQLite:
We see CallVoidMethod and further TODO: Support functions that return values
Great. The authors did not finish. Will have to myself ...
I will say that the approach was not found right away. Spent two days, but the result was achieved. And this is the main thing
Instead of CallVoidMethod we make CallObjectMethod in which we take away a line from Android
There is one more point. Need to change in register_android_database_SQLiteConnection:
By adding Ljava / lang / String; as a parameter. This is the string that will return from the Android application. Otherwise, Android OS will not find our new implementation.
The final stage. We change callback and interface so that it returns String
Thus, you can override any function, add on or make your own with unique functionality. All this found application in the Air Tickets project. Feed
Aviasales is used, but this is a completely different story.
I hope the article will be useful. Write SQLite queries with your own functionality!
Article materials:
SQLite Android Bindings
Android NDK
When developing an Android application, I encountered a problem in the SQLite filter query with Russian letters. For English localization, there are no problems. For other international layouts, the capital letters in the query were not processed correctly.
Having a little understanding, 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. 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.
Probably the current implementation of SQLite Android is
only supports case-insensitive comparisons of ASCII characters
I saw the solution through CursorWrapper but still decided to collect my version of SQLite and use addCustomFunction.
What came of it, read under the cat
The idea is to use direct and reverse data exchange with the SQLite library of your own (custom) assembly.
First, we look at the SQLite Android Bindings instruction.
I used the Android API version 15 (Android 4.0.3). A slight difference in the additional folder or package package org.sqlite.os;
Then we collect the standard library through the NDK sqliteX library. We connect to the project. And ship our library
System.loadLibrary("sqliteX");
Now we define our custom function that will be called from the SQL query
privatefinal SQLiteDatabase.CustomFunction mLowerFnc =
new SQLiteDatabase.CustomFunction() {
@Overridepublicvoidcallback(String[] args){
String text = args[0];
text = text.toLowerCase();
Log.d(LOG, "LOWER_FNC : " + text);
return;
}
};
The function itself is connected as follows
publicclassDataBaseextendsSQLiteOpenHelper{
...
publicDataBase(Context context){
super(context, context.getDatabasePath(DATABASE_NAME).getPath(), null, DATABASE_VERSION);
context.openOrCreateDatabase(context.getDatabasePath(DATABASE_NAME).getPath(), context.MODE_PRIVATE, null);
}
publicvoidopen()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 actual function handler itself.
Please note that you need to open the database 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 line from the request. Fine!
What's next? How to use these lines and return them back in processed form?
We look at the source code of SQLite:
// Called each time a custom function is evaluated.staticvoidsqliteCustomFunctionCallback(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
Great. The authors did not finish. Will have to myself ...
I will say that the approach was not found right away. 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 make CallObjectMethod in which we take away a line from Android
Full Version Feature
// Called each time a custom function is evaluated.staticvoidsqliteCustomFunctionCallback(sqlite3_context *context,
int argc, sqlite3_value **argv){
jobject result;
JNIEnv* env = 0;
constchar* 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 point. Need to change in register_android_database_SQLiteConnection:
GET_METHOD_ID(gSQLiteCustomFunctionClassInfo.dispatchCallback,
clazz, "dispatchCallback", "([Ljava/lang/String;)Ljava/lang/String;");
By adding Ljava / lang / String; as a parameter. This is the string that will return from the Android application. Otherwise, Android OS will not find our new implementation.
register_android_database_SQLiteConnection (JNIEnv * env)
intregister_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. We change callback and interface so that it returns String
Hidden text
privatefinal SQLiteDatabase.CustomFunction mLowerFnc =
new SQLiteDatabase.CustomFunction() {
@Overridepublic 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
*/publicinterfaceCustomFunction{
public String callback(String[] args);
}
Thus, you can override any function, add on or make your own with unique functionality. All this found application in the Air Tickets project. Feed
Aviasales is used, but this is a completely different story.
I hope the article will be useful. Write SQLite queries with your own functionality!
Article materials:
SQLite Android Bindings
Android NDK