使用C++将sqlite3数据库访问封装成dsn类

使用sqlite3数据库在嵌入式系统中作为本地状态机，替代以往使用一系列配置文件作为本地状态存储是一种结构化程度更强的方式。

要访问sqlite3数据库，首先要建立本地状态机对应的多个表，然后构造sql语句，调用sqlite_exec进行Insert/Update/Delete操作（DML），得到所需的结果或者结果集。不论是Insert/Update/Delete任何一种DML的操作，sqlite_open, sqlite_exec, sqlite_close的模式总是相同的，因此可以将这个模式抽象成一个类。

以下是我在一个项目中对DML进行抽象，得到的一个dsn类。这个类包括头文件tvn_dsn.h和实现体tvn_dsn.cpp，代码如下所示。

tvn_dsn.h：其中将具体的文件名称路径用xxx替代了。

/**
 * =============================================================
 * @file     .../app/statemachine/dml/tvn_dsn.h
 * @author   Luoyuan (15904113750@163.com)
 * @brief    Topview Networks SQLite3 Common header file
 * @version  1.0.0
 * @date     2021-12-16
 *
 * @copyright Copyright (c) 2020-2022, Topview Networks
 * @remark    
 * =============================================================
 */

#ifndef _TVN_SQLITE3_DSN_H_
#define _TVN_SQLITE3_DSN_H_

#define TVN_MAX_EM_LEN 64
#define TVN_MAX_SQL_LEN 4096
#define TVN_MAX_TXT_LEN 512
#define TVN_DML_RETRY 32
#define TVN_DML_RETRY_INTERVAL 50057

#define TVN_DSN_OK 0
#define TVN_DSN_ERROR 1
#define TVN_DSN_MTX_FAILED 5

#include "./sqlite3.h"
#include <string>
#include "../../utils/tvn_g.h"

#ifdef _AMD64_
#define NVRAM_DB_FILE "/home/xxx/nvram"
#define STMACH_DB_SOURCE_FILE "/home/xxx/stmach"
#else
#define NVRAM_DB_FILE "/usr/bin/myapp/nvram"
#define STMACH_DB_SOURCE_FILE "/usr/bin/myapp/stmach"
#endif
#define STMACH_DB_FILE "/tmp/stmach"

typedef SQLITE_API int DBRES;
typedef sqlite3 *DB;
typedef char EM[TVN_MAX_EM_LEN + 1];

using namespace std;

class TVN_DSN
{
private:
    string dbFileName;
    DB db;
    int isOpen;
    int nvrIsOpen, stmIsOpen;

    int ac;
    string err;

public:
    TVN_DSN();
    TVN_DSN(const TVN_DSN &v);
    ~TVN_DSN();

    void Open(const char *vDBFileName);
    int OpenNvram();
    int OpenStatemachine();

    int IsOpen() const { return isOpen; }
    int NvramIsOpen() const { return nvrIsOpen; }
    int StatemachineIsOpen() const { return stmIsOpen; }

    void Close();

    int Read(const char *sql,
             int (*callback)(void *, int, char **, char **),
             void *res,
             char **errmsg);

    int Write(const char *sql,
              char **errmsg);

    void Debug(const string who,
               const char *sql,
               const int ac,
               const string err);

    int ExRead(const char *sql,
               int (*callback)(void *data, int argc, char **argv, char **azColName),
               void *dc,
               char **errMsg);

    int ExWrite(const char *sql,
                char **errMsg);

    int ForceWrite(const char *sql, char **errMsg);

    DB Me() const { return db; }

    int ActionCode() const { return ac; }
    string Error() const { return err; }
};

#endif // _TVN_SQLITE3_DSN_H_

tvn_dsn.cpp：

/**
 * =============================================================
 * @file     .../app/statemachine/dml/tvn_dsn.cpp
 * @author   Luoyuan (15904113750@163.com)
 * @brief    Topview Networks SQLite3 Implementation
 * @version  1.0.1
 * @date     2021-12-16
 *
 * @copyright Copyright (c) 2020-2022, Topview Networks
 * @remark    
 * =============================================================
 */

#include <unistd.h>
#include <pthread.h>
#include "./tvn_dsn.h"
#include "../../utils/tvn_g.h"
#include "../../utils/tvn_color.h"

pthread_mutex_t mtxDbWrite;

TVN_DSN::TVN_DSN()
{
    db = 0;
    isOpen = 0;
    nvrIsOpen = 0;
    stmIsOpen = 0;

    ac = TVN_DSN_OK;
    err = "";
}

TVN_DSN::TVN_DSN(const TVN_DSN &v)
{
    db = v.Me();
    isOpen = v.IsOpen();
    nvrIsOpen = v.NvramIsOpen();
    stmIsOpen = v.StatemachineIsOpen();

    ac = v.ActionCode();
    err = v.Error();
}

TVN_DSN::~TVN_DSN()
{
    if (isOpen)
        Close();
}

void TVN_DSN::Open(const char *vDBFileName)
{
    isOpen = 0;
    // ac = sqlite3_open_v2(vDBFileName, &db, SQLITE_OPEN_READWRITE, NULL);
    ac = sqlite3_open_v2(vDBFileName, &db, SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READWRITE, NULL);
    // ac = sqlite3_open(vDBFileName, &db);

    if (SQLITE_OK != ac)
    {
        err = sqlite3_errmsg(db);
        return;
    }

    dbFileName = vDBFileName;
    isOpen = 1;
}

int TVN_DSN::OpenNvram()
{
    Open(NVRAM_DB_FILE);
    isOpen = 1;
    nvrIsOpen = 1;

    return ac;
}

int TVN_DSN::OpenStatemachine()
{
    Open(STMACH_DB_FILE);
    isOpen = 1;
    stmIsOpen = 1;

    return ac;
}

void TVN_DSN::Close()
{
    if (db)
    {
        sqlite3_close_v2(db);
        db = 0;

#if (_DBG_B0_)
        printf("[TVN_DSN::Close()] Database %s closed accordingly.\r\n", dbFileName.c_str());
#endif
    }

    isOpen = 0;
    nvrIsOpen = 0;
    stmIsOpen = 0;
}

int TVN_DSN::Read(const char *sql,
                  int (*callback)(void *, int, char **, char **),
                  void *res,
                  char **errmsg)
{
    int rc = SQLITE_ABORT;
    err = "";
    pthread_mutex_lock(&mtxDbWrite);
    rc = sqlite3_exec(db, sql, callback, res, errmsg);
    pthread_mutex_unlock(&mtxDbWrite);

    if (*errmsg)
        err = *errmsg;

    return rc;
}

int TVN_DSN::Write(const char *sql,
                   char **errmsg)
{
    int rc = SQLITE_ABORT;
    err = "";

    pthread_mutex_lock(&mtxDbWrite);
    rc = sqlite3_exec(db, sql, NULL, NULL, errmsg);
    pthread_mutex_unlock(&mtxDbWrite);
    if (*errmsg)
        err = *errmsg;
    return rc;
}

int TVN_DSN::ExRead(const char *sql,
                    int (*callback)(void *data, int argc, char **argv, char **azColName),
                    void *dc,
                    char **errMsg)
{
    int ac = SQLITE_EMPTY;

    int retries = TVN_DML_RETRY;
    do
    {
        ac = Read((char *)sql, callback, dc, errMsg);

        if (ac != SQLITE_OK)
        {
            retries--;
            usleep(TVN_DML_RETRY_INTERVAL);
        }
        else
        {
            break;
        }
#if (_DBG_B1_)
        printf(WHITE
               "[TVN_DSN::ExRead]\r\n"
               "    sql= %s\r\n"
               "retries= %d\r\n"
               "     ac= %d\r\n"
               "    err= %s" NONE "\r\n",
               sql, retries, ac, *errMsg);
#endif
    } while (SQLITE_OK != ac && retries > 0);

    return ac;
}

int TVN_DSN::ExWrite(const char *sql,
                     char **errMsg)
{
    int ac = SQLITE_EMPTY;

    int retries = TVN_DML_RETRY;
    do
    {
        ac = Write(sql, errMsg);
        if (SQLITE_OK != ac)
        {
            retries--;
            usleep(TVN_DML_RETRY_INTERVAL);
        }
        else
        {
            break;
        }

#if (_DBG_B1_)
        printf(WHITE
               "[TVN_DSN::ExWrite]\r\n"
               "    sql= %s\r\n"
               "retries= %d\r\n"
               "     ac= %d\r\n"
               "    err= %s" NONE "\r\n",
               sql, retries, ac, *errMsg);
#endif

    } while (SQLITE_OK != ac && retries > 0);

    return ac;
}

int TVN_DSN::ForceWrite(const char *sql, char **errMsg)
{
    int ac = SQLITE_EMPTY;

    do
    {
        ac = Write(sql, errMsg);
        if (SQLITE_OK != ac)
        {
            usleep(TVN_DML_RETRY_INTERVAL);
        }
    } while (SQLITE_OK != ac);

    return ac;
}

void TVN_DSN::Debug(const string who,
                    const char *sql,
                    const int ac,
                    const string err)
{
    printf(L_RED
           "%s:\r\n"
           "  SQL = %s\r\n"
           "   ac = %d\r\n"
           "  Err = %s" NONE "\r\n",
           who.c_str(), sql, ac, err.c_str());
}

说明：

1. tvn_dsn.h定义了默认构造函数和拷贝构造函数，在默认构造函数中对其私有变量进行了初始化。
2. Open，OpenNvram和OpenStatemachine三个方法用于封装sqlite3_open_v2函数，而sqlite3_close_v2()在类的析构函数中执行。OpenNvram()方法和OpenStatemachine()方法都调用Open()方法，只不过这两个方法固定了dbFileName，这样做，使得外部调用时就不必每一次都显示地携带dbFileName参数，上层调用得到了简化。
3. Read和Write方法是基本的实现，封装了sqlite3_exec。
4. ExRead和ExWrite循环调用Read/Write方法，当存在多线程访问时，提升容错性。
5. ForceWrite确保某些关键信息必须写入成功。
6. mtxDbWrite锁用于应用层的读写，在多线程访问时，保证写的独占性。简单起见，读和写共用了一个锁。

调用方法较为简单，结合代码说明。

调用1：单记录读

int TopoInfoBean::Get(string vMAC)
{
    if (vMAC.empty())
    {
        ac = SQLITE_NULL;
        err = "[TopoInfoBean].Get(string)  Parameter MAC empty.";
        return ac;
    }

    char *zErrMsg = 0;
    char sql[TVN_MAX_SQL_LEN] = "";
    sprintf(sql, "SELECT "
                 " mac"
                 ",tei"
                 ",proxyTEI"
                 ",level"
                 ",ability"
                 ",snr"
                 ",attenuation"
                 ",phase "
                 "FROM TopoInfo"
                 " WHERE mac='%s'",
            vMAC.c_str());

    dsn.OpenStatemachine();
    ac = dsn.ExRead(sql, selProcessor, (void *)(this), &zErrMsg);
    dsn.Close();
    err = (SQLITE_OK == ac) ? "" : zErrMsg;
    sqlite3_free(zErrMsg);

    if (SQLITE_OK != ac)
    {
        dsn.Debug("[TopoInfoBean].Get(vMAC)", sql, ac, err.c_str());
    }

    return ac;
}

说明：上述方法按照特定的MAC地址查询TopoInfo，首先进行了一个空值判断，然后构造sql语句，使用dsn打开数据库，调用Read/ExRead方法，通过sqlProcessor回调函数得到结果记录。后面是调试语句了。

调用2：多记录读

vector<TopoInfoBean> TopoInfoBean::All()
{
    vector<TopoInfoBean> res;

    char *zErrMsg = 0;
    char sql[] = "SELECT"
                 " mac"
                 ",tei"
                 ",proxyTEI"
                 ",level"
                 ",ability"
                 ",snr"
                 ",attenuation"
                 ",phase"
                 " FROM TopoInfo"
                 " ORDER BY tei ASC";

    dsn.OpenStatemachine();
    ac = dsn.ExRead(sql, selProcMulti, (void *)(&res), &zErrMsg);
    dsn.Close();
    err = (SQLITE_OK == ac) ? "" : zErrMsg;
    sqlite3_free(zErrMsg);

    if (SQLITE_OK != ac)
    {
        dsn.Debug("[TopoInfoBean].GetAll()", sql, ac, err.c_str());
        res.clear();
    }

    return res;
}

说明：和单记录读略有不同的是，多记录读在Read方法中使用的回调函数的第三个参数传递的是结果集的地址，而单记录读的对应参数是TopoInfoBean本身（this）。回调函数也将不同。

调用3：写 

int TopoInfoBean::Insert()
{
    char *zErrMsg = 0;
    char sql[TVN_MAX_SQL_LEN] = "";
    sprintf(sql, "INSERT INTO TopoInfo "
                 "(mac"
                 ",tei"
                 ",proxyTEI"
                 ",level"
                 ",ability"
                 ",snr"
                 ",attenuation"
                 ",phase) VALUES "
                 "('%s'"
                 ",%d"
                 ",%d"
                 ",%d"
                 ",'%s'"
                 ",%d"
                 ",%d"
                 ",%d);",
            mac.c_str(),
            tei,
            proxyTEI,
            level,
            ability.c_str(),
            snr,
            attenuation,
            phase);

    dsn.OpenStatemachine();
    ac = dsn.ExWrite(sql, &zErrMsg);
    dsn.Close();
    err = (SQLITE_OK == ac) ? "" : zErrMsg;
    sqlite3_free(zErrMsg);

    if (SQLITE_OK != ac)
        dsn.Debug("[TopoInfoBean].Insert()", sql, ac, err.c_str());

    return ac;
}

说明：Write/ExWrite方法的参数只有两个，不需要调用回调函数。