嵌入式Linux sqlite3移植与使用

• 概述
• sqlite3 移植
• sqlite3 常用命令
• sqlite3 API使用
• sqlite3 编程实例
• sqlite3 测试验证

sqlite3是一个小型数据库，代码量少，运行占内存也比较少，采用C 编写，所以天生适合嵌入式系统中，尤其是嵌入式linux，相当支持，sqlite3可以直接通过shell运行，不过这个也只限于测试使用，在实际的项目编程中，我们还是要使用sqlite3提供的C/C++接口函数，也就是API接口。

sqlite官网下载C源码：https://www.sqlite.org/download.html

解压压缩包：tar -zxvf sqlite-autoconf-3360000.tar.gz
运行配置文件：./configure --host=arm-linux --prefix=/work/src_packages/sqlite-autoconf-3071401/dist-sqlite3
–host：指定交叉编译工具， 和编译 Linux 的编译器一致
–prefix： 指定安装目录， 编译后生成的文件放在此目录， 必须是绝对路径
编译：执行make，再执行make install
编译结束过后，在源码目录下生成dist-sqlite3文件夹

将/dist-sqlite3/bin/sqlite3拷贝到开发板的/bin目录下，并执行chmod 777 sqlite3。
将/dist-sqlite3/lib/所有文件拷贝到开发板的/lib目录下。
终端输入”sqlite3”，进入sqlite3。

进入sqlite3命令终端：#sqlite3
获取点命令清单：sqlite>.help
退出sqlite3操作：sqlite>.exit 或sqlite>.quit
打开或创建数据库：sqlite>.open test.db
查看数据库文件信息：sqlite>.database
创建表：CREATE TABLE语句；
查看所有表：sqlite>.tables
查看所有表的完整信息：sqlite>.schema
删除表：DROp TABLE语句
某个表中添加新的数据行：INSERT INTO语句
数据库表中获取数据：SELECT语句
列出当前显示格式的配置：sqlite>.show
设置显示格式之打开表头：sqlite>.header on
设置显示格式之列款：sqlite>.width 2
示例：

# sqlite3 
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db
sqlite> .databases
main: /backup0/test.db r/w
sqlite> create table demo (id int,name char,age int);
sqlite> .tables                                  
demo
sqlite> .schema
CREATE TABLE demo (id int,name char,age int);
sqlite> insert into demo values(100,'WangWu',23);
sqlite> insert into demo values(101,'Tommm',25);
sqlite> .header on
sqlite> .mode column
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: column
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "n"
       stats: off
       width: 
    filename: test.db
sqlite> select * from demo;
id   name    age
---  ------  ---
100  WangWu  23 
101  Tommm   25 
sqlite> .exit
#

打开数据库

int sqlite3_open( 文件名, sqlite3 ** );

关闭数据库

int sqlite3_close(sqlite3 *);

执行sql语句

int sqlite3_exec(sqlite3*,const char sql,sqlite3_callback,void,char **errmsg );

查询数据库

int sqlite3_get_table(sqlite3*,const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg);

实现思路：

• 打开数据库，执行sqlite3_open；
• 创建数据库表格，利用sqlite3_exec执行create table语句；
• 根据输入指令，执行插入、删除、查询、退出操作；

#include 
#include 
#include 
#include 
#include 
#include 

#define DATAbase "fru.db"
typedef struct{
	char time[20];
	char name[16];
	float cost;//成本
	float price;//售价
	char in_out[5];//进货in,出货out
	float number;//交易量
	float money;//交易金额（成本或售价*交易量）
	float total;//库存总量
}fruit_t;

void operate_table_cmd_menu()
{
	puts("");
	puts("*operate table cmd menu:");
	printf("*1:insert 2:delete 3:query 4:update 5:quite n");
	puts("");
	printf("Please input cmd:");
}

const char *get_local_time(char *time_str);
fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit);
int query_callback(void *para, int f_num, char **f_value, char **f_name);
int do_insert_sqlite3(sqlite3 * db);
int do_delete_sqlite3(sqlite3 * db);
int do_query_sqlite3(sqlite3 * db);
void get_columns_name(sqlite3 * db);//获取字段名称
int do_update_sqlite3(sqlite3 * db);

int main(int argc, const char *argv[])
{
	sqlite3 * db;
	char *errmsg;
	int cmd;
	char sql[128]={};

	//打开sqlite3数据库
	if(sqlite3_open(DATAbase,&db) != SQLITE_OK)
	{
		printf("%sn",sqlite3_errmsg(db));
		exit(-1);
	}
	else
	{
		printf("open %s success.n",DATAbase);
	}

	//创建一张数据库的表格   将ID设为主键值，并设为自增字段(保证字段唯一性)
	sprintf(sql,"create table fru(time char,name char,cost Integer,price Integer,in_out char,number Integer,money Integer,total Integer);");
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("%sn",errmsg);
	}
	else
	{
		printf("create or open table success.n");
	}

	while(1)
	{
		operate_table_cmd_menu();
		while(scanf("%d",&cmd) == 0)
		{
			getchar();
			operate_table_cmd_menu();
		}
		getchar();

		switch(cmd)
		{
			case 1:
				do_insert_sqlite3(db);
				break;
			case 2:
				do_delete_sqlite3(db);
				break;
			case 3:
				do_query_sqlite3(db);
				break;
			case 4:
				do_update_sqlite3(db);
				break;
			case 5:
				sqlite3_close(db);
				exit(0);
			default:
				printf("Error cmd.n");
		}
	
	}
	return 0;
}

const char *get_local_time(char *time_str)
{
	time_t tim_t;
	struct tm loc_t;

	time(&tim_t);

	loc_t = *localtime(&tim_t);

	sprintf(time_str,"%04d-%d-%02d %02d:%02d:%02d",loc_t.tm_year+1900,loc_t.tm_mon+1,loc_t.tm_mday,loc_t.tm_hour,loc_t.tm_min,loc_t.tm_sec);

	return time_str;
}

fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit)
{
	char **resultp;
	int nrow;
	int ncolumn;
	char sql[128];
	int index_base;
	char *errmsg;

	sprintf(sql,"select * from fru where name='%s';",fruit->name);

	sqlite3_get_table(db,sql,&resultp,&nrow,&ncolumn,&errmsg);
	
	if(nrow == 0)
	{
		return NULL;
	}
	else
	{
		index_base = (nrow)*(ncolumn);
		fruit->cost = atof(resultp[index_base+2]);
		fruit->price = atof(resultp[index_base+3]);
		fruit->total = atof(resultp[index_base+7]);
	}
	return fruit;
}
int do_insert_sqlite3(sqlite3 * db)
{
	fruit_t fruit;
	char sql[128];
	char *errmsg;
	char type[10];

	bzero(&fruit,sizeof(fruit));

	get_local_time(fruit.time);

	printf("input fruit name:");
	scanf("%s",fruit.name);
	getchar();


	if(get_last_row(db,&fruit) != NULL)//系统中已经有这种水果的记录
	{
		puts("*************************************");	
		printf("%s cost:%.2f price:%.2fn",fruit.name,fruit.cost,fruit.price);
		puts("*************************************");	
		printf("if you change it[y|n]:");

		scanf("%s",type);
		getchar();

		if(strncasecmp(type,"y",strlen("y")) == 0)
		{
		char value[20];
			printf("change cost:%.2f of %s [cost|n]:",fruit.cost,fruit.name);
			scanf("%s",value);//输入成本修改成本，输入n放弃修改
			getchar();
			if(strncasecmp(value,"n",strlen("n")) != 0)
				fruit.cost = atof(value);
		
			printf("change price:%.2f of %s [price|n]:",fruit.price,fruit.name);
			scanf("%s",value);//输入单价修单价，输入n放弃修改
			getchar();
			if(strncasecmp(value,"n",strlen("n")) != 0)
				fruit.price = atof(value);
		}
		printf("Please input the number of %s:",fruit.name);
		scanf("%f",&fruit.number);
		getchar();

		fruit.total = fruit.total +fruit.number;
	}
	else//系统中没有的水果种类
	{
		printf("Please input the cost of %s [cost]:",fruit.name);
		while(scanf("%f",&fruit.cost) == 0)
		{
			getchar();
			printf("Please input the cost of %s [cost]:",fruit.name);
		}
		getchar();
	
		printf("Please input the price of %s [price]:",fruit.name);
		while(scanf("%f",&fruit.price) == 0)
		{
			getchar();
			printf("Please input the price of %s [price]:",fruit.name);
		}
		getchar();
			
		printf("Please input the number of %s:",fruit.name);
		scanf("%f",&fruit.number);
		getchar();

		fruit.total = fruit.number;
	}
	
	sprintf(fruit.in_out,"in");

	fruit.money = fruit.cost * fruit.number;


	sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//自增字段的值传NULL

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
	{
		printf("%sn",errmsg);
		return -1;
	}
	else
	{
		printf("insert:n ");

		sprintf(sql,"select * from fru where time='%s';",fruit.time);
		get_columns_name(db);//获取字段名称
		sqlite3_exec(db,sql,query_callback,NULL,&errmsg);
		printf("donen");

		return 0;
	}
}
int query_callback(void *para, int f_num, char **f_value, char **f_name)
{

	int i;
	for(i=0;i fruit.total)//剩余的数量不够
		{
			printf("The total number:%.4f of %s is not enough, please stock it.n",fruit.total,fruit.name);
			return -1;
		}
		else
		{
			fruit.number = atof(value);
			fruit.money = fruit.price * fruit.number;
			fruit.total = fruit.total - fruit.number;
		}
	}
	else//系统中没有这种水果
	{
		printf("We don't have this fruit:%s. Please stock it.",fruit.name);
		return -1;
	}
	
	sprintf(fruit.in_out,"out");

	printf("are you sure delete number:%.4f of %s[y|n]:",fruit.number,fruit.name);
	scanf("%s",value);
	getchar();

	if(strncasecmp(value,"y",strlen("y")) == 0)
	{
		sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//自增字段的值传NULL

		if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
		{
			printf("%sn",errmsg);
			return -1;
		}
		else
		{
			printf("delete donen ");
			return 0;
		}
	}
	else
	{
		printf("delete cancelled.n");
		return 0;
	}
}
int do_query_sqlite3(sqlite3 * db)
{
	fruit_t fruit;
	char sql[128];
	char *errmsg;
	char value[96];

	bzero(&fruit,sizeof(fruit));

	puts("*************************************");	
	printf("*Examples of query types:n");
	puts("*[all | name='apple' | in_out='in']");
	//puts("*[all total | apple total | banana total]");
	puts("*[time<'2018-12-5 17:08:08' | total>90 and total<200]");
	puts("*[name='apple' and in_out='in']");
	puts("*************************************");	

	printf("Pease input query type:");
	scanf("%s",value);
	getchar();

	if(strncasecmp("all",value,strlen(value)) == 0)
		sprintf(sql,"select * from fru;");
	else
		sprintf(sql,"select * from fru where %s;",value);//自增字段的值传NULL

	get_columns_name(db);//获取字段名称
	if(sqlite3_exec(db,sql,query_callback,NULL,&errmsg) != SQLITE_OK )
	{
		printf("%sn",errmsg);
		return -1;
	}
	else
	{
		printf("query donen ");
		return 0;
	}
}
int do_update_sqlite3(sqlite3 * db)
{
	fruit_t fruit;
	char sql[128];
	char *errmsg;
	int type;

	bzero(&fruit,sizeof(fruit));

	get_local_time(fruit.time);

	printf("input fruit name:");
	scanf("%s",fruit.name);
	getchar();


	if(get_last_row(db,&fruit) != NULL)//系统中已经有这种水果的记录
	{
		puts("*************************************");	
		printf("%s cost:%.2f price:%.2fn",fruit.name,fruit.cost,fruit.price);
		puts("*************************************");	
		printf("if you update it[1:yes 2:cancel]:");

		scanf("%d",&type);
		getchar();

		char value[20];
		if(type == 1)
		{
			printf("update cost:%.2f of %s [cost|n]:",fruit.cost,fruit.name);
			scanf("%s",value);//输入成本修改成本，输入n放弃修改
			getchar();
			if(strncasecmp(value,"n",strlen("n")) != 0)
				fruit.cost = atof(value);
			
			printf("update price:%.2f of %s [price|n]:",fruit.price,fruit.name);
			scanf("%s",value);//输入单价修单价，输入n放弃修改
			getchar();
			if(strncasecmp(value,"n",strlen("n")) != 0)
				fruit.price = atof(value);
		}
		else
		{
			printf("update cancelledn");
			return 0;
		}
		sprintf(fruit.in_out,"update");

		sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//自增字段的值传NULL
		if(sqlite3_exec(db,sql,query_callback,NULL,&errmsg) != SQLITE_OK )
		{
			printf("%sn",errmsg);
			return -1;
		}
		else
		{
			printf("update:n");
			get_columns_name(db);//获取字段名称
			sprintf(sql,"select * from fru where time='%s';",fruit.time);
			sqlite3_exec(db,sql,query_callback,NULL,&errmsg);
			printf("update donen ");
			return 0;
		}
	}
	else//系统中没有的水果种类
	{
		printf("We don't have this fruit:%s. Please stock it.",fruit.name);
	}
		return 0;
}

交叉编译：
arm-linux-gcc -I /home/ubuntu/sqlite3/install/include/ -L /home/ubuntu/sqlite3/install/lib/ -o sql testSql.c -lsqlite3 –ldl
-I 指定了 sqlite3.h 所在路径，
-L 指定 sqlite3 的 lib 库路径。
将生成的可执行文件拷贝到开发板,运行测试：

友情链接，sqlite3菜鸟教程：https://www.runoob.com/sqlite/sqlite-create-table.html
友情链接，sqlite3 C代码示例：https://blog.csdn.net/m0_37542524/article/details/84842154
友情链接，sqlite3 基础概念：https://www.cnblogs.com/xishuichangliu/p/6270291.html