Introduction:

SQLite built-in function is limited, sometimes by adding custom function (User-Defined Fuctions) method can achieve some through ordinary SQL operation is not implemented or very troublesome function; can also be replaced the original SQLite built-in function, make it accord with our demand. This paper focuses on the Android environment in the practice.

Now suppose we want to add a in accordance with the file name extension sorting function in Android system video player now, if not with custom functions, you need to query path from a multimedia database out video, and then remove the extension of video files and the sort, because the query the Cursor object can not write, so the need to generate a MatrixCursor, the sorted data writing, finally returned to the MatrixCursor. Pseudo code as follows:

1 public Cursor getSortedByFileExtensionCursor() {
2     Cursor rawCursor = queryVideoFileNameFromDb(); // Check out the ID and path from the database
3     HashMap<int,String> idAndExtensionMap = getVideoFileExtension(rawCursor); // Access to ID and the HashMap extension
4     Cursor result = sortAndCreateNewMatrixCursor(idAndExtensionMap); // To sort the extension, MatrixCursor generated as a result of
5     return result;
6 }

And if we can register a custom function to the SQLite, many similar problems will be easier.

1 through the C language interface to add a custom function

If you are using the Android SQLite program source code, need to modify the Android directory of the external/sqlite/android/sqlite3_android.cpp file. Register a custom function, the function name is get_file_ext, in the SQL statement function name is "_GET_FILE_EXT", this code is as follows:

 1 extern "C" int register_android_functions(sqlite3 * handle, int utf16Storage)
 2 {
 3     // ...
 4     
 5     // Register a custom function
 6     err = sqlite3_create_function(handle, "_GET_FILE_EXT", 1, SQLITE_UTF8, NULL, get_file_ext, NULL, NULL);
 7     if (err != SQLITE_OK) {
 8         return err;
 9     }
10     
11     // ...
12 }
13 
14 // The specific implementation returns the file name extension function
15 static void get_file_ext(sqlite3_context * context, int argc, sqlite3_value ** argv) 
16 {
17     // ...
18 }

Then external/sqlite compiler, the compiler replaces /system/lib/ libsqlite.so mobile phone the library. The use of the user-defined function is very simple:

1 public Cursor getSortedByFileExtensionCursor() {
2     // Use the SQLite function in sort by where we
3     Cursor result = query("SELECT * FROM video SORT BY _GET_FILE_EXT(_data)");
4 }

Note:If it is not in the Android environment, or can not modify the Android related code, you need to interface to operate the database by C language, and then use the same sqlite3_create_function this function registers a custom function. If you are using the C#/Python/PHP language, the language has a corresponding SQLite wrapper, can be similar to the operation of the database and register a custom function.

2 through the Java language interface to add a custom function

Android SQLiteDatabase there is a hidden interface to add custom function (@hide), in the Android compiler source code you can use the interface, the use of methods are as follows:

 1 public class DatabaseHelper extends SQLiteOpenHelper {
 2     public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
 3         super(context, name, factory, version);
 4     }
 5     
 6     @Override
 7     public void onOpen(SQLiteDatabase db) {
 8         db.addCustomFunction("_GET_FILE_EXT", 1, mGetFileExtension);
 9     }
10     
11     private final SQLiteDatabase.CustomFunction mGetFileExtension =
12             new SQLiteDatabase.CustomFunction() {
13         @Override
14         public void callback(String[] args) {
15             String file = args[0];
16             int index = file.lastIndexOf(".");
17             if (index != -1 && file.length() > index + 1) {
18                 return file.substring(index);
19             }
20             return null;
21         }
22     };
23 }

Then the database queries, you can use the _GET_FILE_EXT function we added. Android source code directory pacakges/providers/MediaProvider this procedure using this interface, can refer to.

Note:Interface of Java release program is best not to rely on the hidden, it is difficult to guarantee the compatibility.

Extended reading:

• Create Or Redefine SQL Functions: Create a custom function explanation.
• Quickstart: Open the SQLite database using C language interface simple example code.

Reprint please indicate the source:

In this paper, the external mirror

Posted by Samuel at November 15, 2013 - 10:54 PM