Sqlite3的JSON1扩展具体使用案例--基于C语言的C-API

在ROS系统中，因为消息种类太多，要对这些消息进行统一存储并且允许检索，就面临一个问题：如何存储，用什么存储，如何检索？

鉴于我们第一阶段使用了统一数据模型对各种消息进行了统一封装：将所有消息共有的部分提取出来生成各个字段，而将差异部分作为blob进行存储。这个模型存在的一个重要问题就是不能对差异部分进行检索。

为了解决或者部分解决上述问题，我们进行了调研，发现sqlite3存在json扩展，这个是从sliqte3.09版本之后就自带的。所以我们开始考虑进一步扩展统一数据封装模型，将差异性的部分放进去以支持检索。

由于目前sqlite3支持的数据格式还是传统的5种，json还没有独立升格为一种数据类型，所以目前仍然以text类型存储json数据，如{"key1":"1", "key2":"2"}这种样式存储。我们在原来的表设计基础上增加一个text字段来存储json数据。而ros中的各种msg使用我们前面的博客中介绍的ROS的msssage的Printer类和我们开发的msg2Json方法来实现ros消息到json的格式转换（里面的数组我们暂时不处理）。

后面的借助json1的函数实现json部分数据的检索，同时支持传统的sql检索和json检索混合模式检索。下面是我们使用json扩展的过程（摸索中出现了很多错误，很多所谓的介绍只是说了sqlite扩展是啥，怎么写一个扩展，怎么用并不清楚，官网给出的说明以及网上的使用教程基本没有）：

1. 编译json库生成libjson.so

2. 打开数据库，获取数据库的句柄

3. 开启load_extension功能

4. 加载json库和入口函数

5. 使用json扩展

上述顺序中，2和3的顺序不能错，也就是说load_extension是要指定为哪个db加载扩展，这时候db不能是NULL，所以要先打开数据库获得句柄。这个是我不断调试摸索出来的（sqlite的扩展使用错误的话，只会报段错误，没有其他任何有用的提示信息）。

下面分别按上述步骤介绍：

1. 编译json扩展得到库文件

在Ubuntu16.04里面使用apt-get install sqlite3 libsqlite3的时候安装的是sqlite3 3.11.0-1ubuntu1.2这一版的二进制包。我调研后发现这里面也是开启了json功能的，至于怎么验证，请参考我的另一个博客。因为要加载扩展，所以首先要存在json库文件，所以我先编译它。那么第一步，我们要去下载对应版本的sqlite3的源码。具体地址： https://launchpad.net/ubuntu/+source/sqlite3/3.11.0-1ubuntu1.2 从下面的介绍我们还可以发现，我们使用apt-get 安装sqlite3中其实并没有json扩展的库文件，但是我们可以在sqlite3命令行中使用json扩展的相关函数。经过调研发现这其实是2套东西，一套sqlite3的sql扩展，一套是sqlite3的C-API扩展。sql扩展默认安装了，所以可以直接在命令行中使用json函数。但C-API扩展并没有安装，所以我们要手动编译得到库文件。

我们下载sqlite3_3.11.0.orig.tar.xz文件（sqlite3_3.11.0.orig-www.tar.xz是html版的api说明）。源码包里面有ext目录，里面是各种扩展，我们要使用的是misc下面的json1.c这个文件。进入到这个目录，执行如下命令

cp ../../sqlite3ext.h ./
gcc -fPIC -shared json1.c sqlite3ext.h -o libjson.so

注意：这里有的教程说不用sqlite3ext.h编译进去，这个为了安全考虑，我还是编译进入了，因为打开json1.c看一下，发现它上来就加载了sqlite3exe.h这个文件。

这样就可以得到json扩展的库文件了，接下来把它复制到/usr/lib/下面（也可以把它放到某个具体路径下）。

2. 打开数据库，得到数据库句柄

这里使用了spatialite插件，其作用是让sqlite3支持地理信息系统，支持二维空间索引，对应的需要安装spatialite和libspatialite包。

3. 启用json扩展

4. 加载json扩展

这里sqlite3_json_init是库文件的入口，可以打开json1.c来查看它的入口（这个是在很多教程里面介绍了的，默认的是sqlite3_extension_init)。根据说明文件来看，如果前面提供的库文件名字以lib开头和.so结尾，那么也可以不写入口函数名，它会先找sqlite3_extension_init，如果不存在，则会找库文件名lib和.so之间的名字x对应的入口sqlite3_x_init。如果库文件不是以lib或so结尾，也它考虑先去掉lib头和.so尾，然后把剩余部分当作x，找sqlite3_x_init作为入口函数。

5. 使用json扩展进行查询：

对于整个json扩展的使用过程中，最混乱的地方有2个：1）先打开数据库，然后启用扩展，然后加载扩展这个顺序（没有人说明白过）；2）json扩展的使用，官网说了怎么制作一个扩展和链接一个扩展，但是没有一个例子。导致我费了很多功夫去按照教程制作了一个扩展，但是怎么都链不到json，后来好好研究了一下json1.c和其他几个类似vfs的扩展，发现json1.c文件的组成就是一个扩展的样子，然后开发思考，最后认为json1.c就是一个扩展，所以我们并不需要去写一个扩展，而是要直接使用这个扩展。后面就是探索使用扩展的方法（去读了load_extension()的源代码，发现关于扩展这部分的函数就那么多个，慢慢自己推测使用的组合方式，不断尝试，最后得到上述结果）。

最后，由于我中间不断的卸载/删除sqlite3和spatialite，导致最后虽然可以加载和使用，但是无法插入数据到sqlite3的问题，报错显示no such module： rtree。然后我就认为是sqlite3按照的时候没有把rtree加上去（我把apt-get安装的sqlite3卸载了，又用源码安装了一遍）。后来我又使用CFLAG和-D参数开启了一堆编译选项，发现还是没有rtree。后来发现rtree跟sqlite3没啥关系，是和spatialite有关，然后各种删和重装，还是不行。

最后把最初始的虚拟机解压缩，使用apt-get install 安装了sqlite3和spatialite及其lib库。然后又下载了sqlite3的源码，编译了json库文件，然后直接按照上述几个步骤使用json扩展就没有问题了。这说明我原来瞎折腾一通slqite3和spatialite是无用功。直接使用apt安装的sqlite3和spatialite就可以，不用自己编译，而libjson.so是需要从源码编译的。整个测试文件代码如下：

#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <ros/ros.h>
#include "abstract_msgs/UnifiedData.h"
#include <spatialite/sqlite3.h>
#include <spatialite/gaiageo.h>
#include <spatialite.h>


using namespace std;


//创建"表"函数

int32_t createDB(string aDataBaseName, string aCreateTableSentence, string anAddGeoColSentence, string aCreateSpaIndexSentence)
{
  ROS_INFO("SqliteDao creating DB, please wait some minutes.\n");
  int32_t retCode;
  char sql[512];
  char* errMsg = NULL;
  sqlite3* handle;

  retCode = sqlite3_open_v2(aDataBaseName.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
  if(retCode != SQLITE_OK)
  {
    ROS_INFO("cannot open '%s': %s\n", aDataBaseName.c_str(), sqlite3_errmsg(handle));
    sqlite3_close_v2(handle);
    return -1;
  }

  void* cache;
  cache = spatialite_alloc_connection();
  spatialite_init_ex(handle, cache, 0);

  // InitSpatialMetadata
  strcpy(sql, "SELECT InitSpatialMetadata()");
  retCode = sqlite3_exec(handle, sql, NULL, NULL, &errMsg);
  if(retCode != SQLITE_OK)
  {
    ROS_INFO("InitSpatialMetadata() error: %s\n", errMsg);
    goto abort;
  }

  // create ordinary part of table
  strcpy(sql, aCreateTableSentence.c_str());
  retCode = sqlite3_exec(handle, sql, NULL, NULL, &errMsg);
  if(retCode != SQLITE_OK)
  {
    ROS_INFO("CREATE TABLE  error: %s\n", errMsg);
    goto abort;
  }

  // add geo column uint32_to table
  strcpy(sql, anAddGeoColSentence.c_str());
  retCode = sqlite3_exec(handle, sql, NULL, NULL, &errMsg);
  if(retCode != SQLITE_OK)
  {
    ROS_INFO("AddGeometryColumn() error: %s\n", errMsg);
    goto abort;
  }

  // create spa index of geo column
  strcpy(sql, aCreateSpaIndexSentence.c_str());
  retCode = sqlite3_exec(handle, sql, NULL, NULL, &errMsg);
  if(retCode != SQLITE_OK)
  {
    ROS_INFO("CreateSpatialIndex() error: %s\n", errMsg);
    goto abort;
  }

  retCode = sqlite3_close_v2(handle);
  if(retCode != SQLITE_OK)
    ROS_INFO("close() error: %s\n", sqlite3_errmsg(handle));

  spatialite_cleanup_ex(cache);
  spatialite_shutdown();

  return 0;

abort:
  sqlite3_free(errMsg);

  sqlite3_close_v2(handle);
  if(retCode != SQLITE_OK)
    ROS_INFO("close() error: %s\n", sqlite3_errmsg(handle));

  spatialite_cleanup_ex(cache);
  spatialite_shutdown();
  return -1;
}

int32_t insertDB(uint64_t pOffset, uint64_t pSize, string aFileName, abstract_msgs::UnifiedData ud)
{
  sqlite3_stmt *stmt;
  char sql[512];
  char *errMsg = NULL;

  sqlite3 *db;
  char * dbName="/home/ok/storage_ws_jin/test.sql3";
  int rc = sqlite3_open(dbName,&db);
  if (rc != SQLITE_OK)
     {
       cout<<"打开数据库失败："<<sqlite3_errmsg(db)<<endl;
       return 0;
     }
   else
     {
      cout<<"数据库打开成功"<<endl;
   }

  gaiaGeomCollPtr geo = NULL;
  unsigned char* blob;
  int32_t blobSize;
  int32_t retCode;
string content ;
  void* cache;
  cache = spatialite_alloc_connection();
  spatialite_init_ex(db, cache, 0);

  // prepare data, insert batch data to database

  strcpy(sql, "BEGIN");
  retCode = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
  if (retCode != SQLITE_OK) {
    ROS_INFO("BEGIN error: %s\n", errMsg);
    goto abort;
  }
    std::cout<<"begin to insert record into observe!"<<std::endl;

  strcpy(sql,"INSERT INTO observe(swarmID, taskName, actorName, robotID, timeStamp, dataType, offset, size, fileName, content, positionZ, positionXY) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
  retCode = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
  if (retCode != SQLITE_OK)
  {
    ROS_INFO("INSERT SQL error: %s\n", sqlite3_errmsg(db));
    goto abort;
  }


    geo = gaiaAllocGeomColl();
    geo->Srid = 3003;
    gaiaAddPointToGeomColl(geo, ud.robotPose.position.x, ud.robotPose.position.y);

    gaiaToSpatiaLiteBlobWkb(geo, &blob, &blobSize);
    gaiaFreeGeomColl(geo);

    sqlite3_reset(stmt);
    sqlite3_clear_bindings(stmt);

    sqlite3_bind_int(stmt, 1, ud.swarmID);
    sqlite3_bind_text(stmt, 2, ud.taskName.c_str(), std::strlen(ud.taskName.c_str()), SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, ud.actorName.c_str(), std::strlen(ud.actorName.c_str()), SQLITE_TRANSIENT);
    //sqlite3_bind_text(stmt, 3, ud.robotID.c_str(), std::strlen(ud.robotID.c_str()), SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 4, ud.robotID);
    sqlite3_bind_text(stmt, 5, ud.timeStamp.c_str(), std::strlen(ud.timeStamp.c_str()), SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, ud.dataType.c_str(), std::strlen(ud.dataType.c_str()), SQLITE_TRANSIENT);
    sqlite3_bind_int64(stmt, 7, pOffset);
    sqlite3_bind_int64(stmt, 8, pSize);
    sqlite3_bind_text(stmt, 9, aFileName.c_str(), std::strlen(aFileName.c_str()), SQLITE_TRANSIENT);
    content = "{\"c\":\"3\",\"d\":\"4\"}";
    sqlite3_bind_text(stmt, 10, content.c_str(), std::strlen(content.c_str()), SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt, 11, ud.robotPose.position.z);

    sqlite3_bind_blob(stmt, 12, blob, blobSize, free);

    cout<<"binding finished!"<<endl;


    retCode = sqlite3_step(stmt);
    if(retCode == SQLITE_DONE || retCode == SQLITE_ROW)
      ;
    else
    {
      ROS_INFO("sqlite3_step() error: %s\n", sqlite3_errmsg(db));
      sqlite3_finalize(stmt);
      goto abort;
    }


  sqlite3_finalize(stmt);

  strcpy(sql, "COMMIT");
  retCode = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
  if (retCode != SQLITE_OK) {

    ROS_INFO("COMMIT error: %s\n", errMsg);
    goto abort;
  }

  sqlite3_close_v2(db);
  spatialite_cleanup_ex(cache);
  spatialite_shutdown();

  return 0;

abort:
   sqlite3_close_v2(db);
  sqlite3_free(errMsg);
  spatialite_cleanup_ex(cache);
  spatialite_shutdown();
  return -1;
}


void callback(const abstract_msgs::UnifiedData::ConstPtr& msg)
{

    insertDB(random(), random(), "/home/ok/data_store/file/1.txt",  *msg);
}

int main(int argc, char ** argv)
{
    ros::init(argc, argv, "sqlite3Test");
    ros::NodeHandle nh;
    ros::Subscriber sub;

    //sqlite3_db_config(db,SQLITE_)
    sqlite3_os_init();
    string dbname="/home/ok/storage_ws_jin/test.sql3";
    sqlite3 *db ;//=NULL;
    sqlite3_stmt *stmt=0;
    char *errMsg = NULL;
    //string createTableSentence = "CREATE TABLE IF NOT EXISTS observe(id INTEGER PRIMARY KEY AUTOINCREMENT, swarmID INTERGER NOT NULL, taskName TEXT NOT NULL, actorName TEXT NOT NULL, robotID INTEGER NOT NULL, timeStamp TEXT NOT NULL, dataType TEXT NOT NULL, offset INTERGER NOT NULL, size INTERGER NOT NULL, fileName TEXT NOT NULL, positionZ REAL NOT NULL)";

    string createTableSentence= " CREATE TABLE IF NOT EXISTS observe(id INTEGER PRIMARY KEY AUTOINCREMENT, \
            swarmID INTERGER NOT NULL, taskName TEXT NOT NULL, actorName TEXT NOT NULL, \
            robotID INTEGER NOT NULL, timeStamp TEXT NOT NULL, dataType TEXT NOT NULL, \
            offset INTERGER NOT NULL, size INTERGER NOT NULL, fileName TEXT NOT NULL, content TEXT, \
            positionZ REAL NOT NULL);";
    string addGeometryColumnSentence = "SELECT AddGeometryColumn('observe', 'positionXY', 3003, 'POINT', 'XY')";
    string createSpatialIndexSentence = "SELECT CreateSpatialIndex('observe', 'positionXY')";

    int retCode = sqlite3_open(dbname.c_str(),&db);
   if(access(dbname.c_str(), F_OK) == -1)
  {
       cout<<"create a new table!"<<endl;
       createDB(dbname, createTableSentence, addGeometryColumnSentence, createSpatialIndexSentence);

   }else{
       cout<<"table exists!"<<endl;
   }
   cout<<"here 0"<<endl;

    sqlite3_enable_load_extension(db,true);
   // sqlite3_enable_load_extension(db,1);
    cout<<" here 1 "<<endl;
    sqlite3_load_extension(db,"/usr/local/lib/libjson.so","sqlite3_json_init",0);
    cout<<"here 2 "<<endl;
    //sqlite3_auto_extension((void(*)(void))sqlite3_json_init);
    //sqlite3Json1Init(db);


   sqlite3_close(db);
    cout<<"I am Here 4!"<<endl;

    //select data from database

    stmt = NULL;
    const char* zTail;
    int ret = sqlite3_open(dbname.c_str(),&db);
    ret = sqlite3_prepare_v2(db,"select id, json_extract(observe.content,'$.c'), timeStamp from observe where id>10000;",-1,&stmt,&zTail);
    cout<<"retcode is "<< ret<<endl;
    if(ret ==SQLITE_OK){
        int num =0;
        while(sqlite3_step(stmt)==SQLITE_ROW){
            num++;
            int id = sqlite3_column_int(stmt,0);
            const unsigned char* value = sqlite3_column_text(stmt,1);
            const unsigned char* timestamp = sqlite3_column_text(stmt,2);
            cout<<num<<", row id: "<<id<<", timestamp: "<<timestamp<<", value :"<<value<<endl;

        }
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);


    sub = nh.subscribe("/data", 1000, &callback);

    ros::spin();

    return 1;
}

对应的CMakeList.txt文件如下（其实上述代码是可以独立运行的，不需要编译成ros包或者可运行程序）：

cmake_minimum_required(VERSION 2.8.3)
project(storage)

## Compile as C++11, supported in ROS Kinetic and newer
 add_compile_options(-std=c++11)

## Find catkin macros and libraries
## if COMPONENTS list like find_package(catkin REQUIRED COMPONENTS xyz)
## is used, also find other catkin packages
find_package(catkin REQUIRED COMPONENTS
  roscpp
  rospy
  std_msgs
  fastrtps_pubsub
)


catkin_package(
  INCLUDE_DIRS include
#  LIBRARIES storage
  CATKIN_DEPENDS roscpp rospy std_msgs fastrtps_pubsub
#  DEPENDS system_lib
)

include_directories(
  include
  ${catkin_INCLUDE_DIRS}
  /usr/include/
  /usr/local/include
  /usr/local/fastdb/include
  /usr/local/sqlite3/include
)

link_directories(
   /usr/lib/x86_64-linux-gnu/
   /usr/local/lib/
   /usr/local/fastdb/lib/
   /usr/local/sqlite3/lib/
)

## Declare a C++ library
 add_library(${PROJECT_NAME}
   src/fdbDao.cpp
   src/GUDC.cpp
   src/RUDC.cpp
   src/sqliteDao.cpp
   src/RingBuffer.cpp
   src/storage.cpp
 )

## Add cmake target dependencies of the library
## as an example, code may need to be generated before libraries
## either from message generation or dynamic reconfigure
 add_dependencies(${PROJECT_NAME}
     ${${PROJECT_NAME}_EXPORTED_TARGETS}
     ${catkin_EXPORTED_TARGETS}
     fastdb
     )

## Declare a C++ executable
## With catkin_make all packages are built within a single CMake context
## The recommended prefix ensures that target names across packages don't collide
 add_executable(fastDBTest
     test/fdbDaoTest.cpp
     src/fdbDao.cpp
     src/RUDC.cpp
     src/GUDC.cpp
     )

 add_executable(sqliteDaoTest
     test/sqliteDaoTest.cpp
     src/sqliteDao.cpp
     )

 add_executable(storageNodeTest
     src/StorageNode.cpp
     src/sqliteDao.cpp
     src/storage.cpp
     src/RUDC.cpp
     src/RingBuffer.cpp
     src/fdbDao.cpp
     src/GUDC.cpp
     )
 add_executable(UnifiedDataPub
     test/UnifiedDataPub.cpp
     )
 add_executable(storageTest
     test/storageTest.cpp
     src/sqliteDao.cpp
     src/storage.cpp
     src/RUDC.cpp
     src/RingBuffer.cpp
     src/fdbDao.cpp
     src/GUDC.cpp
     )

 add_executable(sqlite3Test
     test/sqlite3test.cpp
     )
## Rename C++ executable without prefix
## The above recommended prefix causes long target names, the following renames the
## target back to the shorter version for ease of user use
## e.g. "rosrun someones_pkg node" instead of "rosrun someones_pkg someones_pkg_node"
# set_target_properties(${PROJECT_NAME}_node PROPERTIES OUTPUT_NAME node PREFIX "")

## Add cmake target dependencies of the executable
## same as for the library above
 
 add_dependencies(sqlite3Test
     ${${PROJECT_NAME}_EXPORTED_TARGETS}
     ${catkin_EXPORTED_TARGETS}
     )


## Specify libraries to link a library or executable target against
target_link_libraries(sqlite3Test
  ${catkin_LIBRARIES}
  sqlite3
  spatialite
  #json
)