How to write sqlites extension

android / sqlite3 / sqlite3 extension / latitude and longitude distance

0x00 SQLite

sqlite是一个使用c语音编写的库，其实现了一个的小巧、快速、自含、高可靠性、全功能的SQL数据库引擎。它仅依赖少数的几个系统调用。它是一个非常流行的数据库，几乎打包到了所有的移动设备中，同时有无数的应用程序内嵌了它。

0x01 SQLite扩展

sqlite extension是可以在运行时被sqlite加载的库，其主要有以下作用：

  • 扩展sqlite的功能，如提供sqlite本身不支持的函数。
  • 运行时加载扩展，可以动态更新，也可以保证只在需要时加载扩展，减少内存消耗。
  • 扩展可以独立于sqlite本身单独开发，测试。

0x02 编写扩展

编写扩展时，我们可以从sqlite的官网复制一份模板代码，整个模板代码如下所示：

/* Add your header comment here */
#include <sqlite3ext.h> /* Do not use <sqlite3.h>! */
SQLITE_EXTENSION_INIT1

/* Insert your extension code here */

#ifdef _WIN32
__declspec(dllexport)
#endif
/* TODO: Change the entry point name so that "extension" is replaced by
** text derived from the shared library filename as follows:  Copy every
** ASCII alphabetic character from the filename after the last "/" through
** the next following ".", converting each character to lowercase, and
** discarding the first three characters if they are "lib".
*/
int sqlite3_extension_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  /* Insert here calls to
  **     sqlite3_create_function(),
  **     sqlite3_create_function_v2(),
  **     sqlite3_create_collation_v2(),
  **     sqlite3_create_module_v2(), and/or
  **     sqlite3_vfs_register()
  ** to register the new features that your extension adds.
  */
  return rc;
}
  • sqlite3ext.h

对于sqlite3的头文件，我们可以从sqlite3 download下载其对应版本的源码获取对应的头文件。也可以使用Fossil工具clone源代码。

  • 编写扩展函数实现

下面distance()函数用于计算两个经纬度坐标之间的距离，distanceFunc()用于定义扩展函数可以参考sqlite3 create_function进行定义。

#include <sqlite3ext.h>
#include <math.h>
#include <assert.h>
SQLITE_EXTENSION_INIT1

// degrees * pi over 180
#define DEG2RAD(degrees) (degrees * 0.01745329251994329) // degrees * pi over 180
 
// same to AMapUtils.calculateLineDistance
static int distance(double lat1, double lng1, double lat2, double lng2) {
    double lng1rad = DEG2RAD(lng1);
    double lat1rad = DEG2RAD(lat1);
    double lng2rad = DEG2RAD(lng2);
    double lat2rad = DEG2RAD(lat2);
 
    double sinLng1Rad = sin(lng1rad);
    double sinLat1Rad = sin(lat1rad);
    double cosLng1Rad = cos(lng1rad);
    double cosLat1Rad = cos(lat1rad);
 
    double sinLng2Rad = sin(lng2rad);
    double sinLat2Rad = sin(lat2rad);
    double cosLng2Rad = cos(lng2rad);
    double cosLat2Rad = cos(lat2rad);
 
    double v1 = cosLat1Rad * cosLng1Rad;
    double v2 = cosLat2Rad * cosLng2Rad;
    double v3 = cosLat1Rad * sinLng1Rad;
    double v4 = cosLat2Rad * sinLng2Rad;
    double v =  sqrt((v1 - v2) * (v1 - v2) +
        (v3 - v4) * (v3 - v4) +
        (sinLat1Rad - sinLat2Rad) * (sinLat1Rad - sinLat2Rad)
    );
 
    return asin(v / 2.0) * 12742001.5798544;
}

static void distanceFunc(sqlite3_context *context, int argc, sqlite3_value **argv) {
    // check that we have four arguments (lat1, lon1, lat2, lon2)
    assert(argc == 4);
    // check that all four arguments are non-null
    if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) {
        sqlite3_result_null(context);
        return;
    }
    // get the four argument values
    double lat1 = sqlite3_value_double(argv[0]);
    double lng1 = sqlite3_value_double(argv[1]);
    double lat2 = sqlite3_value_double(argv[2]);
    double lng2 = sqlite3_value_double(argv[3]);
    sqlite3_result_int(context, distance(lat1, lng1, lat2, lng2));
}
  • 修改entry point（入口函数名）与注册扩展函数

从模板代码和文档中我们可以知道，自定义扩展的入口函数默认为sqlite3_extension_init，不过为了防止冲突，一般使用自己库的名字作为入口函数名。以扩展库libdistance.so为例，它对应的入口函数名就是sqlite3_distance_init。

注意：entry point扩展函数入口名只能是小写，即使扩展库名称包含大写。

定义好入口函数之后，我们在入口函数中通过sqlite3_create_function()来注册刚才编写的扩展函数。

#ifdef _WIN32
__declspec(dllexport)
#endif

int sqlite3_distance_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  (void)pzErrMsg;  /* Unused parameter */
  rc = sqlite3_create_function(db, "distance", 4, SQLITE_UTF8, 0, distanceFunc, 0, 0);
  return rc;
}

至此，扩展已经编写完毕了，代码详见: SqliteDistanceExtension。

0x03 编译扩展

因为sqlite是跨平台的，对于不同的平台编译方式不同，具体可以参考sqlite3 compile_extension。

下面我们主要说明一下在android端如何编译扩展？

首先需要安装android nkd套件

  • 编写makefile文件

jni整体目录结构如下:

$ tree
jni
├── Android.mk
├── Application.mk
├── distance.c
├── sqlite3.h
└── sqlite3ext.h

Android.mk文件如下：

LOCAL_PATH:= $(call my-dir)

include $(CLEAR_VARS)

LOCAL_MODULE:= distance

LOCAL_SRC_FILES += distance.c

LOCAL_LDLIBS += -ldl -llog -latomic

include $(BUILD_SHARED_LIBRARY)

Application.mk文件内容如下：

APP_STL:=c++_static
APP_OPTIM := release
APP_ABI := armeabi-v7a,arm64-v8a,x86,x86_64
NDK_TOOLCHAIN_VERSION := clang
NDK_APP_LIBS_OUT=../jniLibs

  • 执行ndk-build生成扩展库

    $ cd jni
$ ndk-build

0x04 使用扩展

1. 启用扩展

由于一些安全性的问题，sqlite默认禁用扩展，当用户使用扩展时，首先需要启用扩展功能，开启的方式有以下两种：

  • 编译sqlite时在编译选项中添加LOCAL_CFLAGS += -DSQLITE_ENABLE_LOAD_EXTENSION=1标志，直接启用扩展功能。
  • 使用sqlite提供的接口sqlite3_enable_load_extension(db, 1);，直接开启扩展功能，其中1表示启用扩展，0表示禁用扩展。

2. 加载扩展

加载扩展有两种方式，

  • 使用sqlite内建的load_extension(libFileName, entryPoint)sql语句加载扩展，若加载失败会直接抛出异常，否则加载成功，如：

    select load_extension(distance, sqlite3_distance_init)

    注意: 在java中使用sql语句加载扩展时，需要先使用System.loadLibrary("distance")加载动态库。

  • 使用sqlite提供的api函数sqlite3_load_extension(db, libFilePath, entryPoint, pzErrMsg)加载扩展，当函数返回SQLITE_OK也就是0时表示扩展加载成功。

    注意：在java中使用函数加载扩展时，需要通过jni方法来调用c接口，如：sqlite3-android-bindings库。

1. android sdk不支持自定义扩展方法

我们知道android系统对外没有提供加载sqlite3扩展的java api。尽管在android的源代码中包含扩展函数相关代码，但是其提供的扩展函数没有返回值，同时其又是隐藏api，所以也不可用。具体相关代码如下：

// android.database.sqlite.SQLiteDatabase

/**
 * 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 void callback(String[] args);
}

/**
 * Registers a CustomFunction callback as a function that can be called from
 * SQLite database triggers.
 *
 * @param name the name of the sqlite3 function
 * @param numArgs the number of arguments for the function
 * @param function callback to call when the function is executed
 * @hide
 */
public void addCustomFunction(String name, int numArgs, CustomFunction function) {
    // Create wrapper (also validates arguments).
    SQLiteCustomFunction wrapper = new SQLiteCustomFunction(name, numArgs, function);

    synchronized (mLock) {
        throwIfNotOpenLocked();

        mConfigurationLocked.customFunctions.add(wrapper);
        try {
            mConnectionPoolLocked.reconfigure(mConfigurationLocked);
        } catch (RuntimeException ex) {
            mConfigurationLocked.customFunctions.remove(wrapper);
            throw ex;
        }
    }
}

2. 使用sqlite-android添加自定义扩展方法

所以为了使用sqlite扩展函数，必须自己内嵌一个的sqlite库，同时暴露出加载扩展方法接口，在此我们选用了sqlite-android库。

添加扩展时，可以在SQLiteOpenHelper的createConfiguration()方法中添加扩展库即可，代码大致如下：

override fun createConfiguration(path: String?, openFlags: Int): SQLiteDatabaseConfiguration {
    val configuration = super.createConfiguration(path, openFlags)
    val nativeLibraryDir = context.applicationInfo.nativeLibraryDir
    TLog.d(TAG, "nativeLibraryDir=$nativeLibraryDir")
    configuration.customExtensions.add(SQLiteCustomExtension("$nativeLibraryDir/libdistance.so", "sqlite3_distance_init"))
    return configuration
}

3. 调用扩展方法

在sql中使用扩展函数:

select * from location order by distance(lat, lng, 39.904211, 116.407394) limit 100;

0x05 参考