Add UDF to SQLite

Introduction:

SQLite built-in functions are limited. Sometimes User-Defined functions (User-Defined Functions) can be added to implement some functions that cannot be implemented or are difficult to implement through common SQL operations; you can also replace the built-in functions of SQLite to meet our requirements. This article focuses on the practices in the Android environment.

Now, we want to add a sort by File Extension function in the Video Player of the Android system. If you do not use a custom function, you need to first query the video path from the multimedia database, then, extract the video file extension and sort it. Because the queried Cursor object cannot be written, you need to generate a MatrixCursor, write the sorted data, and finally return the MatrixCursor. The pseudocode is as follows:

      Cursor rawCursor = queryVideoFileNameFromDb();      HashMap<,String> idAndExtensionMap = getVideoFileExtension(rawCursor);      Cursor result = sortAndCreateNewMatrixCursor(idAndExtensionMap);       }

If we can register a UDF with SQLite, many similar problems will be much simpler.

1. Add User-defined functions through the C language interface

If you are using the sqlite program provided by Android, You need to modify the external/sqlite/Android/sqlite3_android.cpp file in the android source code directory. Register a user-defined function. The function name is get_file_ext and the function name in the SQL statement is "_ GET_FILE_EXT". The brief code is as follows:

    register_android_functions(sqlite3 * handle,                      err = sqlite3_create_function(handle, ,       (err !=                          get_file_ext(sqlite3_context * context,  argc, sqlite3_value **       }

Compile external/sqlite and replace the compiled libsqlite. so with the library under the Mobile Phone/system/lib. Using this UDF is also simple:

           Cursor result = query("SELECT * FROM video SORT BY _GET_FILE_EXT(_data)" }

Note:If it is not in the Android environment or the Android-related Code cannot be modified, You need to operate the database through the c language interface, and then use the sqlite3_create_function function to register the custom function. If you are using C #, Python, PHP, and other languages, these languages all have the corresponding SQLite wrapper. You can perform similar operations on the database and register custom functions.

2. Add a UDF through the Java Interface

The Android SQLiteDatabase class contains a hidden interface (@ hide) for adding user-defined functions. You can use this interface for compiling in the Android source code. The usage is roughly as follows:

   DatabaseHelper       DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,                                db.addCustomFunction("_GET_FILE_EXT", 1             SQLiteDatabase.CustomFunction mGetFileExtension =                                     String file = args[0              index = file.lastIndexOf("."              (index != -1 && file.length() > index + 1                                   }

Then, when querying this database, you can use the _ GET_FILE_EXT function we added. This interface is used by the pacakges/providers/MediaProvider program in the Android source code directory. For details, refer.

Note:It is best not to rely on hidden Java interfaces for programs released externally, which is difficult to guarantee compatibility.

Additional reading:

• Create Or Redefine SQL Functions: explains how to Create a UDF.
• Quickstart: Use the c language interface to open the simple sample code of the SQLite database.

Reprinted please indicate the source: http://www.cnblogs.com/imouto/archive/2013/06/14/how-to-add-user-defined-functions-for-sqlite.html

External Image: http://oteku.blogspot.com/2013/06/how-to-add-user-defined-functions-for-sqlite.html