嵌入式数据库SQLite 3配置使用讲解_MsSqlserver

0、惨痛教训

随着管理开发的项目体积越来越庞大，产品系统涉及的数据量也越来越多，并且伴随着项目不久就要交付给甲方了。如果项目的数据信息没有被妥善管理，后期设备的运行状态、操作状况等数据流信息不能被溯源，当出现了一些特殊意外时，就会导致对于故障信息不能迅速准确的追踪，甚至会被甩锅、推卸责任，白白当了冤大头。因此对于嵌入式项目中，其产品运行时的数据库建立非常有必要，且是迫在眉睫！！！

目前常用的数据库系统有：mysql、postgresql、oracle database、microsoft sql server、sqlite等。在嵌入式项目中，前面几个数据库显然是不合适的，而sqlite是一个轻量级的数据库管理系统，它包含在一个c库中，提供了零配置、无服务器、事务性的sql数据库引擎。所以sqlite的特点使其非常适合嵌入式系统、移动设备、小型项目或者作为应用程序的本地数据库使用。本文选用了嵌入式数据库sqlite3进行配置和讲解。

1、sqlite3环境配置

（1）、下载安装sqlite库

根据目标系统平台，下载sqlite源码，或下载官方提供的已经编译好的库。本文目标平台是windows11 64位平台，进入sqlite download page的主页，选择需要的库版本（windows）。

下载的压缩包一共有如下所示的三个：

sqlite-dll-win-x64-3450300.zip

sqlite-dll-win-x86-3450300.zip

sqlite-tools-win-x64-3450300.zip

（2）、解压下载的文件

本文中将对应的sqlite库文件解压到了，c:\program files\sqlite路径下。

（3）、添加库路径到环境变量

根据下图所示的步骤，进入系统属性-->环境变量-->系统变量-->编辑环境变量，将路径加入到环境变量中。

（4）、检查数据库安装状态

打开windows的命令行，输入sqlite3，有类似如下的数据信息说明库安装成功，后续只需在程序代码中，将库加入到工程代码中即可。

（5）、sqlitestudio工具

如果有可视化分析数据需求、推荐使用下载：sqlitestudio

https://sqlitestudio.pl/

2、sqlite3基础

sql（structured query language）是一种结构化查询语言，sql 是一种专门用来与数据库通信的语言。

不同的数据库管理系统在其实践过程中都对 sol 规范作了某些改编和扩充。故不同数据库管理系统之间的 sol语言不能完全相互通用。

以下是sqlite的一些关键特点：

零配置: sqlite不需要安装或者管理服务器进程。启动一个使用sqlite的应用程序时，数据库文件会自动创建（如果尚不存在），并且直接通过程序访问。
轻量级: sqlite的代码量小，资源消耗少，对硬件要求很低。这使得它非常适合资源有限的环境，如手机、平板电脑或微型设备。
跨平台: sqlite兼容几乎所有主流的操作系统，包括windows、linux、unix、android、ios等。
服务器less: 由于sqlite是嵌入式的，没有单独运行的数据库服务器进程，数据直接存储在文件中。这简化了部署和维护过程。
事务处理: sqlite支持acid（原子性、一致性、隔离性、持久性）事务，确保数据的完整性。
sql标准兼容: 虽然sqlite有自己的sql方言，但它大体上遵循ansi sql标准，支持大多数标准sql语句。
单一文件存储: sqlite数据库完全存储在一个磁盘文件中，这使得备份和迁移数据库变得非常简单，只需复制该文件即可。
动态类型: sqlite具有弱类型特性，允许更灵活的数据存储，但也可能需要开发者更加注意数据类型的处理。
广泛使用: sqlite被许多应用程序和操作系统采用，包括浏览器（如firefox）、操作系统组件、手机应用等，是世界上最广泛部署的数据库引擎之一。

有个重要的点值得注意，sqlite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 glob 和 glob 在 sqlite 的语句中有不同的含义。一般数据采用固定的静态数据类型，而 solite 采用的是动态数据类型，会根据存入值自动判断。

sqlite 存储类：solite 具有以下五种基本数据类型

（1）integer：带符号的整型(最多64位)。

（2）real：8字节表示的浮点类型。

（3）text：字符类型，支持多种编码(如 utf-8、utf-16)，大小无限制。

（4）blob：任意类型的数据，大小无限制。 blob(binary large obiect)二进制大对象，使用二进制保存数据。

（5）null：表示空值

sqlite 亲和类型(affinity)及类型名称

下表列出了当创建 sqlite3 表时可使用的各种数据类型名称，同时也显示了相应的亲和类型：

数据类型	亲和类型
int integer tinyint smallint mediumint bigint unsigned big int int2 int8	integer
character(20) varchar(255) varying character(255) nchar(55) native character(70) nvarchar(100) text clob	text
blob 未指定类型	blob
real double double precision float	real
numeric decimal(10,5) boolean date datetime	numeric

sqlite 语句：所有的 sqlite 语句可以以任何关键字开始，如 select、insert、update、delete、alter、drop 等，所有的语句以分号 ; 结束。

3、sqlite3基本语法

（1）、创建数据库

//打开数据库,如不存在则会创建数据库
int ret = sqlite3_open("project_data.db", &db);
if( ret )
{
    fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
    exit(-1);
}
fprintf(stderr, "opened database successfully\n");

（2）、创建表

//create table 告诉数据库系统创建一个新表的关键字。create table 语句后跟着表的唯一的名称或标识。
create table database_name.table_name(
   column1 datatype  primary key(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnn datatype,
);
char table_name[200] = {0};
char *err_msg = null;
snprintf(table_name, sizeof(table_name), "create table if not exists camera(time_stamp integer primary key, action text, x integer, y integer, z integer, vx integer, vy integer, vz integer, time integer);");
int ret = sqlite3_exec(db, table_name, null, null, &err_msg);
if(ret){
    fprintf(stderr, "create table err:%s\n", err_msg);
    return -1;
}
fprintf(stderr, "create table successfully\n");

（3）、删除表

//sqlite 的 drop table 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
//drop table 语句的基本语法如下。
drop table database_name.table_name;
char table_name[200] = {0};
char *err_msg = null;
snprintf(table_name, sizeof(table_name), "drop table database_name.table_name;");
int ret = sqlite3_exec(db, table_name, null, null, &err_msg);
if(ret){
    fprintf(stderr, "delete table err:%s\n", err_msg);
    return -1;
}
fprintf(stderr, "delete table successfully\n");

（4）、插入数据

insert into 语句有两种基本语法，如下所示：
insert into table_name [(column1, column2, column3,...columnn)]  values (value1, value2, value3,...valuen);//在这里，column1, column2,...columnn 是要插入数据的表中的列的名称
或
insert into table_name values (value1,value2,value3,...valuen);
//确保值的顺序与列在表中的顺序一致。
char table_value[200] = {0};
int ret = 0;
char *err_msg = null;
snprintf(table_value, sizeof(table_value),"insert into camera values(%lld, '%c', %d, %d, %d, %d, %d, %d, %d);", get_current_timestamp_ms(), action, x, y, z, vx, vy, vz, time);
ret = sqlite3_exec(db, table_value, null, null, &err_msg);
if(ret)
{
    fprintf(stderr, "insert value to table err:%s\n", err_msg);
    return -1;
}
fprintf(stderr, "insert value to table successfully\n");

（5）、查询数据

//sqlite 的 select 语句用于从 sqlite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。
//sqlite 的 select 语句的基本语法如下：
select column1, column2, columnn from table_name;//在这里，column1, column2...是表的字段，他们的值即是您要获取的。
select * from table_name;    //获取所有可用的字段
char *err_msg = null;
sprintf(sql, "select * from table_value;");
ret = sqlite3_exec(db, sql, null, null, &err_msg);   //执行 sql 命令的快捷方式
if(ret)
{
    fprintf(stderr, "can't select sqlite value: %s\n", sqlite3_errmsg(db));
    return -1;
}

（6）、删除数据

//sqlite 的 delete 查询用于删除表中已有的记录。可以使用带有 where 子句的 delete 查询来删除选定行，否则所有的记录都会被删除。
//带有 where 子句的 delete 查询的基本语法如下：
delete from table_name
where [condition];
//可以使用 and 或 or 运算符来结合 n 个数量的条件。
char *err_msg = null;
sprintf(sql, "delete from camera where time_stamp = 123456789;");
ret = sqlite3_exec(db, sql, null, null, &err_msg);   //执行 sql 命令的快捷方式
if(ret)
{
    fprintf(stderr, "can't delete sqlite data: %s\n", sqlite3_errmsg(db));
    return -1;
}

（7）、修改数据

//sqlite 的 update 查询用于修改表中已有的记录。可以使用带有 where 子句的 update 查询来更新选定行，否则所有的行都会被更新。
//带有 where 子句的 update 查询的基本语法如下：
update table_name
set column1 = value1, column2 = value2...., columnn = valuen
where [condition];
char *err_msg = null;
sprintf(sql, "update camera set action = 't' where time_stamp = 123456789;");
ret = sqlite3_exec(db, sql, null, null, &err_msg);   //执行 sql 命令的快捷方式
if(ret)
{
    fprintf(stderr, "can't delete sqlite data: %s\n", sqlite3_errmsg(db));
    return -1;
}

4、sqlite3代码

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlite3.h>
int database_init();
int write_motor_info_to_database(sqlite3 *db, int motor_id, double target_pos, double real_pos, double real_speed, double real_current);
int write_camera_info_to_database(sqlite3 *db, char action, int x, int y, int z, int vx, int vy, int vz, int time);
long long get_current_timestamp_ms(void); 
int main(void)
{
    printf("sqlite3 database test!\n");    
    database_init();
    return 0;
}
/**
  * @brief  数据库初始化
  * @param  none
  * @retval 成功返回0， 失败返回-1
  */
int database_init(void)  
{
    int ret = -1;
    sqlite3 *db;
    char *err_msg = null;
    char database_name[128] = {0};
    //获取当前时间
    struct tm t; 
    time_t now;
    time(&now);
    localtime_s(&t, &now);
    snprintf(database_name, sizeof(database_name),"%02d%02d%02d.db", t.tm_year + 1900, t.tm_mon + 1, t.tm_mday);
    printf("date:%s\n", database_name);
   //打开数据库
    ret = sqlite3_open(database_name, &db);
    if( ret )
    {
        fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    fprintf(stderr, "opened database successfully\n");
    char table_name[200] = {0};
    //时间戳 目标位置 实际位置 实际速度 实际电流
    //create table if not exists motor0 (time_stamp integer primary key, target_pos real, real_pos real, real_speed real, real_current, real);
    for(int motor_id = 0; motor_id < 6; motor_id++)
    {
        snprintf(table_name, sizeof(table_name),"create table if not exists motor%d (time_stamp integer primary key, target_pos real, real_pos real, real_speed real, real_current real);", motor_id);
        ret = sqlite3_exec(db, table_name, null, null, &err_msg);
        if(ret)
        {
            fprintf(stderr, "create table err:%s\n", err_msg);
            return -1;
        }
        fprintf(stderr, "create table successfully\n");
    }
    memset(table_name, 0, sizeof(table_name));
    snprintf(table_name, sizeof(table_name), "create table if not exists camera(time_stamp integer primary key, action text, x integer, y integer, z integer, vx integer, vy integer, vz integer, time integer);");
    ret = sqlite3_exec(db, table_name, null, null, &err_msg);
    if(ret)
    {
        fprintf(stderr, "create table err:%s\n", err_msg);
        return -1;
    }
    fprintf(stderr, "create table successfully\n");
    write_motor_info_to_database(db, 0, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 1, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 2, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 3, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 4, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 5, 90.0, 87.2, 5.0, 0.85);
    write_camera_info_to_database(db, 't', 100,200,150,160,130,110,1000);
    return 0;
}
/**
  * @brief  写入电机数据到数据库中
  * @param  db：数据库文件描述符
  * @param  target_pos：目标位置
  * @param  real_pos：实际位置
  * @param  real_speed：实际速度
  * @param  real_current：实际电流
  * @retval 写入成功返回0，失败-1
  */
int write_motor_info_to_database(sqlite3 *db, int motor_id, double target_pos, double real_pos, double real_speed, double real_current)
{
    char table_value[200] = {0};
    int ret = 0;
    char *err_msg = null;
    //insert into motor0 values(1798345, 90.0, 88.66, 45.1, 0.97);
    snprintf(table_value, sizeof(table_value),"insert into motor%d values(%lld, %.2f, %.2f, %.2f, %.2f);", motor_id, get_current_timestamp_ms(), target_pos, real_pos, real_speed, real_current);
    ret = sqlite3_exec(db, table_value, null, null, &err_msg);
    if(ret)
    {
        fprintf(stderr, "insert value to table err:%s\n", err_msg);
        return -1;
    }
    fprintf(stderr, "insert value to table successfully\n");
    return 0;
}
/**
  * @brief  写入相机数据到数据库中
  * @param  db：数据库文件描述符
  * @param  action:动作
  * @param  x：
  * @param  y：
  * @param  z:
  * @param  vx：
  * @param  vy：
  * @param  vz:
  * @retval 写入成功返回0，失败-1
  */
int write_camera_info_to_database(sqlite3 *db, char action, int x, int y, int z, int vx, int vy, int vz, int time)
{
    char table_value[200] = {0};
    int ret = 0;
    char *err_msg = null;
    //insert into motor0 values(1798345, 90.0, 88.66, 45.1, 0.97);
    snprintf(table_value, sizeof(table_value),"insert into camera values(%lld, '%c', %d, %d, %d, %d, %d, %d, %d);", get_current_timestamp_ms(), action, x, y, z, vx, vy, vz, time);
    ret = sqlite3_exec(db, table_value, null, null, &err_msg);
    if(ret)
    {
        fprintf(stderr, "insert value to table err:%s\n", err_msg);
        return -1;
    }
    fprintf(stderr, "insert value to table successfully\n");
    return 0;
}
/**
  * @brief  获取毫秒级时间戳
  * @param  none
  * @retval 成功返回时间戳值，失败返回-1
  */
long long get_current_timestamp_ms(void)
{
#if defined(_win32) || defined(_win64)
    struct _timeb timebuffer;
    _ftime64_s(&timebuffer);
    return (long long)timebuffer.time * 1000 + timebuffer.millitm;
#elif defined(__unix__) || defined(__unix) || defined(unix)
    struct timeval tv;
    gettimeofday(&tv, null);
    return (long long)tv.tv_sec * 1000 + tv.tv_usec / 1000;
#endif
}

参考代码运行结果