运行环境

正点原子STM32F429阿波罗开发板
CPU: 180M
SDRAM:32M
NAND:512M
NAND+UFFS挂载到根目录”/“
SD+FATFS挂载到”/sd”

兼容性问题修复

SQLite数据库RT-Thread的3.1.3以上及其以上的版本出现了些兼容性问题。

初始化报错

使用ENV配置并编译运行，在调用sqlite3_initialize时报错:
(obj != object) assertion failed at function:rt_object_init, line number:267
根据报错定位至object.c文件rt_object_init方法中

  1. /* try to find object */
  2.     for (node  = information->object_list.next;
  3.             node != &(information->object_list);
  4.             node  = node->next)
  5.     {
  6.         struct rt_object *obj;
  8.         obj = rt_list_entry(node, struct rt_object, list);
  9.         if (obj) /* skip warning when disable debug */
  10.         {
  11.             RT_ASSERT(obj != object);
  12.         }
  13.     }
  14.     /* leave critical */

这段代码是为了放防止对象重复插入链表，意味着系统在初始化sqlite时有对象重复初始化了。通过单步调试以及查看已被压栈的方法及变量，发现是重复初始化导致，在头文件sqlite_config_rtthread.h中增加宏定义#define SQLITE_OMIT_AUTOINIT得以解决。

插入报错

初始化问题解决后，成功创建了数据库文件m.db。但是在执行插入操作是再次出现
(obj != object) assertion failed at function:rt_object_init, line number:267
还是通过查看栈内方法及变量，配合单步调试定位到问题，在rtthread_io_methods.c和rtthread_vfs.c中，每次打开操作时都会调用rt_sem_init来初始化信号量”vfssem”作为文件描述符的锁，rt_sem_init会将信号量添加到对象列表，但是在关闭操作时未将”vfssem”从对象列表中剔除，导致了这个报错。所以在rtthread_io_methods.c文件_rtthread_io_close方法中增加rt_sem_detach操作：

  1. static int _rtthread_io_close(sqlite3_file *file_id)
  2. {
  3.     int rc = 0;
  4.     RTTHREAD_SQLITE_FILE_T *file = (RTTHREAD_SQLITE_FILE_T*)file_id;
  6.     if (file->fd >= 0)
  7.     {
  8.         _rtthread_io_unlock(file_id, NO_LOCK);
  9.         rt_sem_detach(&file->sem);
  10.         rc = close(file->fd);
  11.         file->fd = -1;
  12.     }
  14.     return rc;
  15. }

插入性能测试

创建表：
CREATE TABLE userinfo(id INTEGER PRIMARY KEY AUTOINCREMENT,userid INT NOT NULL,username varchar(32) NOT NULL);

id userid username
1 1 username1
2 2 username2

DAO：dbhelper+userinfo

普通插入

伪代码：

  1. sqlite3_open(DB_NAME,&db);
  2. for(i=0;i<n;i++)
  3. {
  4.     sqlite3_exec(db,"insert into userinfo(userid,username) values (1001,\"username1001\");"
  5.              ,0,0,NULL);
  6. }
  7. sqlite3_close(db);

UFFS:

  1. Directory /:
  2. sd                  <DIR>
  3. udisk               <DIR>
  4. m.db                12288
  5. msh />
  6. msh />user add
  7. [I/user_info] operat record(s): 226 ms
  8. Add 1 record(s): 232ms, speed: 232ms/record
  9. msh />user add 10
  10. [I/user_info] operat record(s): 2312 ms
  11. Add 10 record(s): 2318ms, speed: 231ms/record

显式开启事务

伪代码：

  1. sqlite3_open(DB_NAME,&db);
  2. sqlite3_exec(db,"begin transaction;",0,0,0); 
  3. for(i=0;i<n;i++)
  4. {
  5.     sqlite3_exec(db,"insert into userinfo(userid,username) values (1001,\"username1001\");"
  6.              ,0,0,NULL);
  7. }
  8. sqlite3_exec(db,"commit transaction;",0,0,0);
  9. sqlite3_close(db);

UFFS:

  1. msh />user add 10
  2. [I/user_info] operat record(s): 237 ms
  3. Add 10 record(s): 243ms, speed: 24ms/record
  4. msh />user add 1000
  5. [I/user_info] operat record(s): 1360 ms
  6. Add 1000 record(s): 1365ms, speed: 1ms/record
  7. msh />user add 10000
  8. [I/user_info] operat record(s): 12452 ms
  9. Add 10000 record(s): 12459ms, speed: 1ms/record
  10. msh />ls
  11. Directory /:
  12. sd                  <DIR>
  13. udisk               <DIR>
  14. m.db                270336
  15. msh />

SD+FATFS

  1. Directory /sd:
  2. m.db                12288
  3. System Volume Information<DIR>
  4. msh /sd>user add
  5. [I/user_info] operat record(s): 245 ms
  6. Add 1 record(s): 256ms, speed: 256ms/record
  7. msh /sd>user add 10
  8. [I/user_info] operat record(s): 252 ms
  9. Add 10 record(s): 266ms, speed: 26ms/record
  10. msh /sd>user add 100
  11. [I/user_info] operat record(s): 341 ms
  12. Add 100 record(s): 355ms, speed: 3ms/record
  13. msh /sd>user add 1000
  14. [I/user_info] operat record(s): 1385 ms
  15. Add 1000 record(s): 1399ms, speed: 1ms/record
  16. msh /sd>user add 10000
  17. [I/user_info] operat record(s): 11314 ms
  18. Add 10000 record(s): 11329ms, speed: 1ms/record
  19. msh /sd>

显式开启事务+执行准备

伪代码：

  1. sqlite3_open(DB_NAME, &db);
  2. sqlite3_exec(db, "begin transaction;", 0, 0, 0); 
  3. sqlite3_prepare(db, sql, -1, &stmt, NULL);
  4. for(i = 0; i < n; i++)
  5. {
  6.     sqlite3_bind_int(stmt, 1, e->userid);
  7.     sqlite3_bind_text(stmt, 2, e->username, strlen(e->username), NULL);
  8.     rc = sqlite3_step(stmt);
  9. }
  10. sqlite3_finalize(stmt);
  11. sqlite3_exec(db, "commit transaction;", 0, 0, 0);
  12. sqlite3_close(db);

UFFS:

  1. msh />ls
  2. Directory /:
  3. sd                  <DIR>
  4. udisk               <DIR>
  5. m.db                12288
  6. msh />user add 1000
  7. [I/dbhelper] insert record(s): 668 ms
  8. Add 1000 record(s): 674ms, speed: 0.7ms/record
  9. msh />user
  10. test get all userinfo
  11. id:101  userid:3425     username:UserName3425
  12. id:102  userid:3425     username:UserName3425
  13. id:103  userid:3425     username:UserName3425
  15. ...
  17. id:1098 userid:3425     username:UserName3425
  18. id:1099 userid:3425     username:UserName3425
  19. id:1100 userid:3425     username:UserName3425
  20. record(s):1000
  21. msh />ls
  22. Directory /:
  23. sd                  <DIR>
  24. udisk               <DIR>
  25. m.db                36864
  26. msh />user add
  27. [I/dbhelper] operat record(s): 227 ms
  28. Add 1 record(s): 234ms, speed: 234ms/record
  29. msh />user add 10000
  30. [I/dbhelper] operat record(s): 6666 ms
  31. Add 10000 record(s): 6673ms, speed: 0.7ms/record
  32. msh />
  33. msh />user del
  34. [I/dbhelper] operat record(s): 1174 ms
  35. Del all record success!
  36. msh />

SD+FATFS

msh /sd>user add
[I/dbhelper] operat record(s): 244 ms
Add 1 record(s): 256ms, speed: 256ms/record
msh /sd>user add 100
[I/dbhelper] operat record(s): 283 ms
Add 100 record(s): 296ms, speed: 2.8ms/record
msh /sd>user add 1000
[I/dbhelper] operat record(s): 776 ms
Add 1000 record(s): 789ms, speed: 0.7ms/record
msh /sd>user add 10000
[I/dbhelper] operat record(s): 5415 ms
Add 10000 record(s): 5431ms, speed: 0.5ms/record
msh /sd>

开启事务

存储 1条(ms) 100条(ms) 1000条(ms) 10000条(ms)
uffs 227 323 1378 12452
sd+fatfs 245 341 1385 11314

开启事务+执行准备

存储 1条(ms) 100条(ms) 1000条(ms) 10000条(ms)
uffs 227 265 669 6670
sd+fatfs 244 283 776 5415

当单表数据量达到6W至30W条数据时，单条插入或100条批量插入时间会达到700ms以上，1000条批量插入时间1500-2000ms。根据id单条查询时间未收影响。