SQLite added a custom function
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