嵌入式数据库操作SQLite及FalshDB_嵌入式时序数据库

一、SQLite在嵌入式Linux及安卓上使用比较多，SQLite整个源文件20多万行，一个源文件就是一个工程，在安卓上不是什么事，主频足够，但在MCU就算在比较高端的mcu H7，配个4线QSIP flah，可能是文件系统原因（littlefs）跑起来也是相当卡，换到外部Class10 TF 卡（fat32）还算可以，主要优点使用SQL语句操作数据库，数据库导出到PC端可直接通过Navicat Premium 等的软件读取省了很多的数据转换操作。

1、数据插入

static int menu_insert_bind(sqlite3_stmt *stmt, int index, void *arg)
{
    int rc;
    rt_list_t *h = arg, *pos, *n;
    menu_t *s = RT_NULL;
    rt_list_for_each_safe(pos, n, h)
    {
        s = rt_list_entry(pos, menu_t, list);
        sqlite3_reset(stmt);                                        //reset the stmt
        sqlite3_bind_text(stmt,0, s->dishes_id,strlen(s->dishes_id),NULL);
        sqlite3_bind_text(stmt,1, s->main_class, 1, NULL);
        sqlite3_bind_text(stmt,2, s->sub_class, strlen(s->sub_class), NULL);
        sqlite3_bind_text(stmt,3, s->name, strlen(s->name), NULL); //bind the 1st data,is a string
        sqlite3_bind_int(stmt, 4, s->steps);                        //bind the 1st data,is a int
        sqlite3_bind_blob(stmt,5, s->cooking, sizeof(((menu_t*)0)->cooking), NULL);
        sqlite3_bind_blob(stmt,6, s->photo,   sizeof(((menu_t*)0)->photo), NULL);
        sqlite3_bind_text(stmt,7, s->link,    strlen(s->link), NULL);
        rc = sqlite3_step(stmt);                                    //execute the stmt by step
    }

    if (rc != SQLITE_DONE)
        return rc;
    return SQLITE_OK;
}

static int db_sql_menu_add(rt_list_t *h,char *dishes_id)
{
    char sql[128];
    rt_snprintf(sql, 128, "insert into menu(dishes_id,main_class,sub_class,name,steps,cooking,photo,link) values ('%s',?,?,?,?,?,?,?);",dishes_id);
    
    return db_nonquery_operator(sql, menu_insert_bind, h);
}

static rt_err_t db_menu_add(menu_t *menu_ptr)
{
    rt_list_t *h = (rt_list_t *)rt_calloc(1, sizeof(rt_list_t));
    rt_list_init(h);
    
    menu_t *s = (menu_t *)rt_calloc(1, sizeof(menu_t));
    
    memcpy(s,menu_ptr,sizeof(menu_t)-sizeof(((menu_t*)0)->list));
    
    rt_list_insert_before(h, &(s->list));

    int res = db_sql_menu_add(h,menu_ptr->dishes_id);
    db_menu_free_list(h);
    
    if (res != SQLITE_OK){
        
        LOG_E("menu add id:%s failed!",menu_ptr->dishes_id);
        return RT_ERROR;
    }
    else{
        
        return RT_EOK;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

这三个函数实现SQL数据库插入操作，一层层往上调用,db_menu_add(menu_t *menu_ptr),menu_ptr是通过结构指针获取数据入口

2、通过ID更新数据

static int menu_update_bind(sqlite3_stmt *stmt, int index, void *arg)
{
    int rc;
    menu_t *s = arg;
    sqlite3_bind_text(stmt,0, s->dishes_id,strlen(s->dishes_id),NULL);
    sqlite3_bind_text(stmt,1, s->main_class, 1, NULL);
    sqlite3_bind_text(stmt,2, s->sub_class, strlen(s->sub_class), NULL);
    sqlite3_bind_text(stmt,3, s->name, strlen(s->name), NULL); //bind the 1st data,is a string
    sqlite3_bind_int(stmt, 4, s->steps);                        //bind the 1st data,is a int
    sqlite3_bind_blob(stmt,5, s->cooking, sizeof(((menu_t*)0)->cooking), NULL);
    sqlite3_bind_blob(stmt,6, s->photo,   sizeof(((menu_t*)0)->photo), NULL);
    sqlite3_bind_text(stmt,7, s->link,    strlen(s->link), NULL);
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
        return rc;
    return SQLITE_OK;
}

static int db_sql_menu_update(menu_t *menu_ptr)
{
    char sql[128];
    rt_snprintf(sql, 128, "update menu set main_class=?,sub_class=?,name=?,steps=?,cooking=?,photo=?,link=? where dishes_id='%s';",menu_ptr->dishes_id);
    return db_nonquery_operator(sql, menu_update_bind, menu_ptr);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3、删除指定ID数据

static int db_sql_menu_del(char *id)
{
    return db_nonquery_by_varpara("delete from menu where dishes_id=?;", "%s", id);
}
1
2
3
4

4、通过ID获取数据

static int db_sql_get_menu_by_id(char *id, void *buf)
{
    char sql[128];

    rt_snprintf(sql, 128, "SELECT * FROM menu WHERE dishes_id='%s';",id);
    return db_query_by_varpara(sql, menu_create_buf, buf, RT_NULL);
}
1
2
3
4
5
6
7

除了插入及更新有点繁琐，其它操作都是发送SQL语句即可。

二、FlashDB，网上资料也是挺多，支持键值数据库与时序数据库，适合小型单线及多线SPI flash，100ms左右保存一条8个浮点类型温度加上时间戳也没见发生丢失，对于记录物联网数据还是相当适用，1秒保存一次2M flash可以循环覆盖记录8小时数据，只适合顺序保存数据不能插入删除。键值数据用于保存配置参数。

键值数据读写

struct fdb_blob blob;

    fdb_kv_get_blob(&_global_kvdb, "cooking", fdb_blob_make(&blob, (uint8_t*)usSRegHoldBuf+DB_START_ADDR, DB_SAVE_LEN));
	default_kv.kvs = cooking_kv_table;
    default_kv.num = sizeof(cooking_kv_table) / sizeof(cooking_kv_table[0]);
    result = fdb_kvdb_init(&_global_kvdb, "cook", "flashdb_kv", &default_kv, NULL);

    if (result != FDB_NO_ERR){
        return RT_ERROR;
    }
    else{
        flash_db_read_cooking();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

时序数据写入

    result = fdb_tsdb_init(&work_tsdb, "work", "flashdb_ts", time, sizeof(flashdb_work_t), NULL);
    if (result != FDB_NO_ERR){
        return RT_ERROR;
    }

	void flash_tsdb_append(flashdb_work_t *work_ptr)
	{
    	struct fdb_blob blob;
    
	    fdb_tsl_append(&work_tsdb, fdb_blob_make(&blob, work_ptr, sizeof(flashdb_work_t)));
	}
1
2
3
4
5
6
7
8
9
10
11

work_ptr 数据入口指针

数据导出
如需将flashDB数据导出，可能过查询打印数据，利用Xshell终端录屏功能将数据保存成文本文件

static uint8_t  query_side = 0;
static uint32_t query_cnt = 0;
static bool query_by_time_cb(fdb_tsl_t tsl, void *arg)
{
    struct fdb_blob blob;
    flashdb_work_t work;
    fdb_tsdb_t db = arg;
    const char *slot_str[]={"L","R"};
    const char *mode_str[]={"SHUTDOWN","  MELT  ","  ECO   ","PREHEAT ","COOKING "," FILTER "};
    const char *run_str[]={"  STOP  "," START  ","COMPLETE"};

    fdb_blob_read((fdb_db_t) db, fdb_tsl_to_blob(tsl, fdb_blob_make(&blob, &work, sizeof(flashdb_work_t))));
    
    if( query_side == 0 || query_side==work.slot ){
        struct tm * tptr = gmtime(&tsl->time);
        rt_kprintf("%4d-%02d-%02d %02d:%02d:%02d VOL:%3d.%-2d CUR:%3d.%-2d BT:%3d.%-2d %s CT:%3d.%-2d TT:%3d.%-2d %s M:%2d %s SEG:%d P:%3d.%-2d I:%3d.%-2d D:%3d.%-2d\n", \
            tptr->tm_year+1900,tptr->tm_mon+1,tptr->tm_mday,tptr->tm_hour+8,tptr->tm_min,tptr->tm_sec,\
            (int)work.voltage,(int)((work.voltage-(int)work.voltage)*100),\
            (int)work.current,(int)((work.current-(int)work.current)*100),\
            (int)work.temperature,(int)((work.temperature-(int)work.temperature)*100),\
            slot_str[work.slot-1],\
            (int)work.curr_temp,(int)((work.curr_temp-(int)work.curr_temp)*100),\
            (int)work.target_temp,(int)((work.target_temp-(int)work.target_temp)*100),\
            mode_str[work.mode],\
            work.menu,\
            run_str[work.run],\
            work.seg,\
            (int)work.p_value,(int)((work.p_value-(int)work.p_value)*100),\
            (int)work.i_value,(int)((work.i_value-(int)work.i_value)*100),\
            (int)work.d_value,(int)((work.d_value-(int)work.d_value)*100)\
            );
        
        query_cnt++;
    }
    rt_thread_delay(1);

    return false;
}

void flash_tsdb_query(int argc, char *argv[])
{
    if( argc==6 ){
        
        struct tm tm_from,tm_to;
        
        sscanf( argv[1],"%d-%d-%d",&tm_from.tm_year, &tm_from.tm_mon, &tm_from.tm_mday );
        sscanf( argv[2],"%d:%d:%d",&tm_from.tm_hour, &tm_from.tm_min, &tm_from.tm_sec );
        sscanf( argv[3],"%d-%d-%d",&tm_to.tm_year, &tm_to.tm_mon, &tm_to.tm_mday );
        sscanf( argv[4],"%d:%d:%d",&tm_to.tm_hour, &tm_to.tm_min, &tm_to.tm_sec );
        if( rt_strcmp(argv[5],"left") == 0 ){
            query_side = 1;
        }
        else if( rt_strcmp(argv[5],"right") == 0 ){
            query_side = 2;
        }
        else if( rt_strcmp(argv[5],"both") == 0 ){
            query_side = 0;
        }
        
        tm_from.tm_year -= 1900;
        tm_to.tm_year -= 1900;
        tm_from.tm_mon--;
        tm_to.tm_mon--;
                
        time_t from_time = mktime(&tm_from), to_time = mktime(&tm_to);
        /* query all TSL in TSDB by time */
        query_cnt = 0;
        fdb_tsl_iter_by_time(&work_tsdb, from_time, to_time, query_by_time_cb, &work_tsdb);
        
//        uint32_t count;
//        count = fdb_tsl_query_count(&work_tsdb, from_time, to_time, FDB_TSL_WRITE);
        rt_kprintf("query count is: %u\n", query_cnt);
    }
    else{
        rt_kprintf("error parameter\n");
        rt_kprintf("flash_tsdb_query y-m-d h:m:s y-m-d h:m:s [left|right]\n");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

也只能导出指定时间段的数据，但对于开发调试阶段很适用。