Sqlite存储和读取人脸数据

简介

在linux嵌入式设备上，进行人脸识别后，会生成人脸特征数据，和faceid绑定。需要利用sqlite，实现添加、删除、查询人脸的功能。通过官网进行下载：

https://www.sqlite.org/download.html

交叉编译

• 解压

tar -xvf sqlite-autoconf-3380200.tar.gz

• 进入该路径，添加build.sh

#!/bin/bash

ROOT_PATH=`pwd`
TARGET_PATH=$ROOT_PATH/output
CROSS_COMPILE_HOST="arm-linux-gnueabihf"

rm -rf output
rm -rf build
mkdir build
cd build

../configure --prefix=$TARGET_PATH \
             --host=$CROSS_COMPILE_HOST \
             CC=$CROSS_COMPILE_HOST-gcc \
             CXX=$CROSS_COMPILE_HOST-g++

make
make install

• 执行脚本

sh build.sh

存储类型介绍

对于人脸特征值，需要存储为blob类型，其他描述性文字可以存为text格式

sqlite编程

• 创建表，包含faceid，feature，附加的描述文字

int crate_table(sqlite3 *db)
{
    int result = 0;
    int ret;
    char *errmsg = NULL;
    char sql[128];
    snprintf(sql, 128, "create table " FACEID_TABLE_NAME " (faceid int, face_name text, feature blob)");
    ret = sqlite3_exec(db, sql, NULL,NULL,&errmsg);
    if(ret != SQLITE_OK) {
        if(0 != strcmp("table " FACEID_TABLE_NAME " already exists", errmsg)) {
            printf("fail to exec:%s\n",errmsg);
            result = -1;
        }
        sqlite3_free(errmsg);
    }
    return result;
}

• 删除表

int drop_table(sqlite3 *db)
{
    int result = 0;
    int ret;
    char *errmsg;
    char sql[128];
    snprintf(sql, 128, "drop table " FACEID_TABLE_NAME);
    printf("%s\n", sql);

    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK) {
        result = -1;
        printf("fail to exec:%s\n", errmsg);
        sqlite3_free(errmsg);
    }
    return result;
}

• 插入数据，注意数据查重，同样的faceid会导致出现多条记录

int insert_faceid(sqlite3 *db, int faceid, const char* face_name, const void* feature, int feature_len)
{
    int result = -1;
    int ret;
    sqlite3_stmt *stmt = NULL;
    char sql[128];
    snprintf(sql, 128, "insert into "FACEID_TABLE_NAME" values(%d, '%s', ?)", faceid, face_name);
    printf("%s\n", sql);
    ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, 0);
    if (ret == SQLITE_OK && stmt) {
        ret = sqlite3_bind_blob(stmt, 1, feature, feature_len, NULL);
        if (ret == SQLITE_OK) {
            ret = sqlite3_step(stmt);
        }
    }

    if (stmt != NULL) {
        sqlite3_finalize(stmt);
        if (ret == SQLITE_OK) {
            result = 0;
        }
    }

    return result;
}

• 读取数据，查询表中的所有记录

int query_faceid(sqlite3 *db)
{
    int result = -1;
    int ret;
    sqlite3_stmt *stmt = NULL;

    ret = sqlite3_prepare(db, "select * from " FACEID_TABLE_NAME, -1, &stmt, 0);
    if (ret == SQLITE_OK && stmt) {
        result = 0;
        int n = sqlite3_column_count(stmt);
        printf("column count  = %d\n", n);
        ret = sqlite3_step(stmt);
        int id;
        int face_name_len;
        char face_name[49];
        int feature_len;
        short feature[64];
        while (ret == SQLITE_ROW) {
            id = sqlite3_column_int(stmt, 0);
            face_name_len = sqlite3_column_bytes(stmt, 1);
            feature_len = sqlite3_column_bytes(stmt, 2);

            const unsigned char * p = sqlite3_column_text(stmt, 1);
            memset(face_name, 0, sizeof(face_name));
            memcpy(face_name, p, face_name_len);
            printf("face_name:%s\n", face_name);

            const void * pfeature_data= sqlite3_column_blob(stmt, 2);
            memset(feature, 0, sizeof(feature));
            memcpy(feature, pfeature_data, feature_len);

            ret = sqlite3_step(stmt);
        }
    }

    if (stmt != NULL) {
        sqlite3_finalize(stmt);
    }
    return result;
}

• 查询faceid对应的rowid，用于更新对应的rowid的数据使用

// 查询到一次回调一次
int select_rowid_cb(void *arg, int argc, char** values, char** colname)
{
	int *rowid = (int*)arg;
    *rowid = atoi(values[0]);
    return 0;
}

int select_rowid_with_faceid(sqlite3 *db, int faceid, int* rowid)
{
    int result = 0;
    int ret;
    char *errmsg;
    char sql[128];
    snprintf(sql, 128, "select rowid from " FACEID_TABLE_NAME " where faceid=%d", faceid);
    //printf("%s\n", sql);
    ret = sqlite3_exec(db, sql, select_rowid_cb, rowid, &errmsg);
    if (ret != SQLITE_OK) {
        result = -1;
        printf("select_rowid_with_faceid fail to exec:%s\n", errmsg);
        sqlite3_free(errmsg);
    }
    
    return result;
}

• 根据rowid更新数据，faceid可选择是否更新

int update_faceid(sqlite3 *db, int rowid, int new_faceid)
{
    int result = 0;
    int ret;
    char *errmsg;
    char sql[128];
    snprintf(sql, 128, "update "FACEID_TABLE_NAME" set faceid=%d where rowid=%d", new_faceid, rowid);
    printf("%s\n", sql);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK) {
        result = -1;
        printf("update_faceid fail to exec:%s\n", errmsg);
        sqlite3_free(errmsg);
    }

    return result;
}

int update_faceid_and_feature(sqlite3 *db, int faceid, int new_faceid, const void* feature, int feature_len)
{
    int result = 0;
    int ret;

    int rowid = 0;
    ret = select_rowid_with_faceid(db, faceid, &rowid);
    if (ret != 0) {
        printf("select rowid failed.\n");
        return -1;
    }

    if (new_faceid != faceid) {
        ret = update_faceid(db, rowid, new_faceid);
        if (ret != 0) {
            printf("select rowid failed.\n");
            return -1;
        }
    }

    printf("rowid= %d\n", rowid);
    sqlite3_blob *pblob;
    ret = sqlite3_blob_open(db, "main", FACEID_TABLE_NAME, "feature", rowid, 1, &pblob);
    if (ret != SQLITE_OK) {
        result = -1;
        printf("fail to sqlite3_blob_open\n");
    } else {
        ret = sqlite3_blob_write(pblob, feature, feature_len, 0);
        if (ret != SQLITE_OK) {
            result = -1;
            printf("fail to sqlite3_blob_write\n");
        }

        printf("sqlite3_blob_close\n");
        sqlite3_blob_close(pblob);
    }

    return result;
}