sqlite C接口增删改查，数据库优化

 

3 FM001.03模块日志信息数据库设计

1. 功能描述

数据库存储日志数有限，当数据库写满，数据库从头开始覆盖写。

模块日志在数据库中以表的形式存储，定义数据库表的格式。

数据写入数据库的接口和从数据库查询数据的查询接口定义。

 

1. 设计意图和实现方法说明

 

• 数据写入策略

• 数据库数据的存储格式和写Flash 的数据格式保持一致，采用二进制方式存储
• 数据库相比于Flash，无法判断当前写到位置（当超过M条），所以设计一个WritePost表存储当前所有表中数据写到位置WriteRowid
• 由于Flash空间大小受限制，数据库最大只能写到M（M值待定）条，超过M条后，数据库从第一条开始用Update方式更新数据   （rowid 不可自增，手动填入）
• WriteRowid>MaxRowid,数据库未写满，采用写的方式写入，WriteRowid<=MaxRowid,数据库已写满，采用Update方式写入

 

模块日志数据库最大条数：100000条

 

 

• 数据库表设计

创建两个表，一个用于存储数据，一个用于存储当前数据库写的位置。

1. 不采用自增，头部删除方案读取数据会很方便：尾部插入需要 vacuum去释放空间，但是VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件会占用双倍的空间，就适用于嵌入式数据库了。
2. 插入时，数据库ID自增，当超过最大条数，用新的ID替换最小的ID，保持数据库中最大的ID永远是最新插入的记录。
3. 按照ID顺序排序查询，最多查500条，select  * from RECTLOG_TAB order by ID limit 0,11

           

模块信息表：

Rowid：作为主键INTEGER PRIMARY KEY

    Data：  Recordid+Alarm1+Alarm2+Temp1+Temp3+Temp4+Temp5+Volt+Curr+LimitCurr+

CRC16

    数据库表写位置表：

Rowid：作为主键INTEGER PRIMARY KEY

Data：Recordid +WriteIndex+CRC16

• 数据库操作接口设计

写/更新接口：

当前写的Rowid>MaxRow,表明数据库还未写满，直接写入。

当前写的Rowid<=MaxRow,表明数据库还已写满，采用Update方式写入。

采用insert命令写入，写入后需要读出进行校验才认为写成功。

Data_Sqlite3WriteOneRecord

{

        RecordId+RectInfoBuf+CheckNO

IF（CurrWriteRowid<=MaxRow）

{

            /* Update Table Set RectInfo=‘%s’, Where Rowid=%d */

    Sqlite3_Update(Dest);

}

Else

{

            /* Insert in to Table(Rowid,RectInfo) valuese (‘%ld’,Bindata) */

                Sqlite3_Write(Dest);

}

//LOG_ReadOneRecord()

//(Read==Write?)  //success

//{

        //CurrWriteRowid++;

//ss}

}

查询接口：

Data_Sqlite3ReadOneRecord

{

    /* Insert in to Table(Rowid,RectInfo) valuese (‘%ld’,Bindata) */

    Sqlite3_Read(Dest);

    （ldCheck==alCheck?）

    {

 

    }

    If(iRequest<iRead)

    {

        FillEmpty();//取到的长度不足，剩下为空，兼容数据库结构发生改变。

}

   

}

删除接口：

暂时不做删除，Flash分区也不。

 

• 数据库性能优化设计

经试验发现，多条Insert操作提交和Prepare IO消耗非常严重

1. 显式开启事务  （实测cpu占用减少20%）

 

所谓”事务“就是指一组SQL命令，这些命令要么一起执行，要么都不被执行。在SQLite中，每调用一次sqlite3_exec()函数，就会隐式地开启了一个事务，如果插入一条数据，就调用该函数一次，事务就会被反复地开启、关闭，会增大IO量。如果在插入数据前显式开启事务，插入后再一起提交，则会大大提高IO效率，进而加数据快插入速度。

开启事务只需在上述代码的前后各加一句开启与提交事务的命令即可：

[cpp] view plaincopy

sqlite3_exec(db,"begin;",0,0,0);  

for(int i=0;i<nCount;++i)  

{  

    std::stringstream ssm;  

    ssm<<"insert into t1 values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";  

    sqlite3_exec(db,ssm.str().c_str(),0,0,0);  

}  

sqlite3_exec(db,"commit;",0,0,0);  

1. 执行准备 （实测cpu占用减少20%）

SQLite执行SQL语句的时候，有两种方式：一种是使用前文提到的函数sqlite3_exec()，该函数直接调用包含SQL语句的字符串；另一种方法就是“执行准备”（类似于存储过程）操作，即先将SQL语句编译好，然后再一步一步（或一行一行）地执行。如果采用前者的话，就算开起了事务，SQLite仍然要对循环中每一句SQL语句进行“词法分析”和“语法分析”，这对于同时插入大量数据的操作来说，简直就是浪费时间。因此，要进一步提高插入效率的话，就应该使用后者

sqlite3_exec(db,"begin;",0,0,0);  

    sqlite3_stmt *stmt;  

    const char* sql = "insert into t1 values(?,?,?,?)";  

    sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0);  

      

    for(int i=0;i<nCount;++i)  

    {         

        sqlite3_reset(stmt);  

        sqlite3_bind_int(stmt,1,i);  

        sqlite3_bind_int(stmt,1,i*2);  

        sqlite3_bind_int(stmt,1,i/2);  

        sqlite3_bind_double(stmt,1,i*i);  

    }  

    sqlite3_finalize(stmt);  

    sqlite3_exec(db,"commit;",0,0,0);

 

 

 

 
#include    "sqlite_data_test.h"

//Write data  queue number     
#define SQL_MAX_DATA_TYPES        3

/*Global patameters */
static SQLITEDATA_QUEUE    g_SQLITEDATA_QUEUE[SQL_MAX_DATA_TYPES];      //queue for write data 
SQLITEGLOBAL        g_SqliteGlobal;
//为两个全局变量加锁

// sqlite tables 
/*Table 为什么不用多个列    ?
    1.如果分成多个列，那么 以后每个不同的表，读写接口都会不一样，设计会很复杂
    2.对于查询， 即使 填入多个列，如果按列查询，但是我们实际取还是要一条一条的取，循环处理条记录会需要新的接口去处理实现
    尽量保持接口能够兼容

    按照时间查询可以考虑，以后将时间作为一个item分开       时间作为结构体第一个参数传递：     但是取出来不好取
    数据库不可以同时读写，所以每次写读需要加锁，每次读也要加锁（读在外面加锁，不单独对读一条加锁）
    可以支持多线程写，但是必须采用等待方式写。    https://blog.csdn.net/u011726005/article/details/77754949
    数据库锁机制：https://www.cnblogs.com/lijingcheng/p/4454884.html

    数据库可以一个线程写，多个线程读，加读写锁：https://blog.csdn.net/lovecodeless/article/details/24968369
*/
SQLITETABLE g_SqliteTable[]=
{
        {WRITERECORD_TABLEID,    WRITERECORD_TAB,    SQL_TABLE_NUM,            "TAB_ID ",    "INTEGER PRIMARY KEY",    "RECORDID",    "INTEGER NOT NULL"},
        {RECTLOG_TABLEID,        RECT_DATA_LOG,        60,        "ID",            "INTEGER PRIMARY KEY",    "DATA ",            "BLOB NOT NULL"},
        {RECTLOGTEST_TABLEID,    RECT_DATATEST_LOG,    RECT_TABLE_MAXSIZE,        "ID",            "INTEGER PRIMARY KEY",    "DATA ",            "BLOB NOT NULL"}
        
        //if add one table ,must change the   SQL_TABLE_NUM value;
};

/*==========================================================================*
 * FUNCTION : Sqlite_CreateDataBase
 * PURPOSE  : create a database  
 * CALLS    :    sqlite3_open    : 
 * CALLED BY: DAT_InitSqlite 
 * RETURN   : 
 * COMMENTS : 
 * CREATOR  : Lemon               DATE: 2018-08-16 16:50
 *==========================================================================*/

static BOOL  Sqlite_CloseDataBase()
{
    sqlite3_close(g_SqliteGlobal.pSqlite3DB);
    //AppLogOut(SQLITE_TASK,APP_LOG_ERROR,"Database Closed at %s\n",time(NULL));         //str format 
}
static BOOL  Sqlite_OpenDataBase()
{

    int iSqlRtn;
    /*create a data base  */
    iSqlRtn=sqlite3_open(SQLITE_DATABASE,&g_SqliteGlobal.pSqlite3DB);
    if(iSqlRtn!= SQLITE_OK
        && g_SqliteGlobal.pSqlite3DB!=NULL)
    {
        //AppLogOut(SQLITE_TASK,APP_LOG_ERROR,"Create database Fail:%s \n",sqlite3_errmsg(g_SqliteGlobal.pSqlite3DB));
        return FALSE;
    }
    //AppLogOut(SQLITE_TASK,APP_LOG_ERROR,"Database Opened at %s\n",time(NULL));         //str format 
    return TRUE;

}
static BOOL Sqlite_CreateTables()
{
    char szSqlTableCmd[SQLITE_CMD_LENTH_MAX]={0};
    int iSqlRtn=0,i=0;
    char *pszErrMsg = 0;