Qt之内存数据库

  内存数据库，顾名思义就是将数据放在内存中直接操作的数据库。相对于磁盘，内存的数据读写速度要高出几个数量级，将数据保存在内存中相比从磁盘上访问能够极大地提高应用的性能。所以在有大量数据交互时使用内存数据库，等到数据交互量降下来以后同步至本地数据库也是个不错的选择。

  下段代码是我事先将部分数据插入内存数据库，便于后续数据交互时查表使用。

/*
* @brief 将自检信息表存入内存数据库中
* @return &MemoryQuery
*/
QSqlQuery* CreateMemoryDBAndStoreSCInfo()
{
    typedef struct 
    {
        int Type;      //类型
        int MainItem;  //主项
        int SubItem;   //子项
    }StSelfCheckInfo;
    /*
    *  在各枚举下不同索引对应的类型、主项、子项的值
    *  在查表时找到对应位置下的索引值即可知道是哪个子项
    */
    StSelfCheckInfo stSCInfoArr[32] = 
    {
        {0,0,0},{0,0,1},{0,0,2},{0,0,3},{0,0,4},   //4
        {0,2,0},{0,2,1},{0,2,2},{0,2,3},{0,2,4},   //9
        {0,0,5},{0,1,0},{1,4,0},{1,4,1},{1,0,0},   //14
        {1,0,1},{1,0,2},{1,0,3},{1,1,0},{1,1,1},   //19
        {1,1,1},{1,1,4},{1,1,3},{1,2,0},{1,3,0},   //24
        {1,6,0},{1,6,1},{1,7,0},{1,7,1},{1,5,0},   //29
        {2,0,0},{2,1,0}                            //31
    };
    QVariantList IdList,TypeList,MainItemList,SubItemList;
    for(int i = 0;i < 32;i++)
    {
        IdList << i;
        TypeList << stSCInfoArr[i].Type;
        MainItemList << stSCInfoArr[i].MainItem;
        SubItemList << stSCInfoArr[i].SubItem;
    }
    MemoryDb = QSqlDatabase::addDatabase("QSQLITE","Memory");
    MemoryDb.setDatabaseName(":memory:");/// 内存数据库，掉电会丢失
    if(!MemoryDb.open())
    {
        return NULL;
    }
    MemoryQuery = QSqlQuery::QSqlQuery(MemoryDb);
    /*
    *   自检枚举表
    */  
    QString StrMemoryQuery = "CREATE TABLE SCItemRetrievalTable(id INT8 NOT NULL primary key,Type INT8 NOT NULL,MainItem INT8 NOT NULL, SubItem INT8 NOT NULL)";
    MemoryQuery.exec(StrMemoryQuery);
    /*
    *   自检信息表
    */
    QString StrSCinfoQuery = "create table SCInfoTable (id INTEGER PRIMARY KEY, Datetime varchar(20),System varchar(10), TestType varchar(10), TestIndex varchar(20), TestResult varchar(20))";
    MemoryQuery.exec(StrSCinfoQuery);

    MemoryQuery.prepare("INSERT INTO SCItemRetrievalTable VALUES(?,?,?,?)");   
    MemoryQuery.addBindValue(IdList);
    MemoryQuery.addBindValue(TypeList);
    MemoryQuery.addBindValue(MainItemList);
    MemoryQuery.addBindValue(SubItemList);
    MemoryQuery.execBatch();    ///批量写入
    return &MemoryQuery;
}

在空闲时同步数据：

   /// 设定定时器开始同步内存数据库至本地数据库
   QTimer::singleShot(1000,this,SLOT(SyncSCInfoToLocalDb()));

不过现在同步的方法还有待改进...

​
// @brief 同步SCInfo表至本地事件处理函数
void SyncSCInfoToLocalDb()
{
    int Num = 0;
    typedef struct  
    {
        QString id;
        QString DateTime;
        QString System;
        QString TestType;
        QString TestIndex;
        QString TestResult;
    }StSCInfo;
    MemoryQuery = QSqlQuery::QSqlQuery(MemoryDb);
    MemoryQuery.exec("select count(*) from SCInfoTable");
    bool ret = MemoryQuery.next();
    if(ret)
    {
        Num = MemoryQuery.value(0).toInt();
        StSCInfo *stScInfoArr = new StSCInfo[Num];
        QString str = "";
        for(int i = 0;i < Num;i++)
        {
            str = QString("select DateTime,System,TestType,TestIndex,TestResult from SCInfoTable where id = %1").arg(i+1);
            MemoryQuery.exec(str);
            ret &= MemoryQuery.next();
            if(ret)
            {
                stScInfoArr[i].id = QString::number(i);
                stScInfoArr[i].DateTime = MemoryQuery.value(0).toString();
                stScInfoArr[i].System = MemoryQuery.value(1).toString();
                stScInfoArr[i].TestType = MemoryQuery.value(2).toString();
                stScInfoArr[i].TestIndex = MemoryQuery.value(3).toString();
                stScInfoArr[i].TestResult = MemoryQuery.value(4).toString();
            }
        }
        QString StrSyncQuery = "INSERT INTO TestTable SELECT '" + stScInfoArr[0].id  + "'AS 'id' ,'" + stScInfoArr[0].DateTime  + "'AS 'Datetime' ,'" + stScInfoArr[0].System + "'AS 'System', '" + stScInfoArr[0].TestType + "'AS 'TestType' ,'"+ stScInfoArr[0].TestIndex + "'AS 'TestIndex' ,'" + stScInfoArr[0].TestResult + "'AS 'TestResult'";
        QString StrSyncUnionQuery= "";
        for(int j = 1;j < Num;j++)
        {
            StrSyncUnionQuery = "UNION ALL SELECT '" + stScInfoArr[j].id + "' ,'"  + stScInfoArr[j].DateTime + "' ,'" + stScInfoArr[j].System + "' ,'" + stScInfoArr[j].TestType + "','"  + stScInfoArr[j].TestIndex + "' ,'"+ stScInfoArr[j].TestResult + "' ";
            StrSyncQuery = StrSyncQuery+ StrSyncUnionQuery;
        }
        QSqlQuery query;
        query.setForwardOnly(true);
        query.exec(StrSyncQuery);
        delete[] stScInfoArr;
        stScInfoArr = NULL;
    }
}

​

参考资料：

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

https://blog.csdn.net/qq_24127015/article/details/100510848

https://www.cnblogs.com/soqu36/p/9153855.html