嵌入式Linux sqlite3移植与使用_嵌入式 linux 移植sqlite

概述

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

sqlite3 移植

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命令终端：#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
#
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

sqlite3 API使用

打开数据库

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 编程实例

实现思路：

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

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <time.h>
#include <strings.h>

#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("%s\n",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("%s\n",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:%.2f\n",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("%s\n",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("done\n");

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

	int i;
	for(i=0;i<f_num;i++)
	{
		if(i == 0)
			printf("\t%-19s ",f_value[i]);
		else
			printf("%-10s ",f_value[i]);
	}
	puts("");
	return 0;
}
void get_columns_name(sqlite3 * db)
{
	char **resultp;
	int nrow;
	int ncolumn;
	char *errmsg;
	int i;

	sqlite3_get_table(db,"select * from fru",&resultp,&nrow,&ncolumn,&errmsg);
	printf("\t%-19s ",resultp[0]);
	for(i=1;i<ncolumn;i++)
	{
		printf("%-10s ",resultp[i]);
	}
	puts("");
}
int do_delete_sqlite3(sqlite3 * db)
{
	fruit_t fruit;
	char sql[128];
	char *errmsg;
	char value[20];

	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)//系统中有这种水果
	{
		printf("input the out number of %s:",fruit.name);

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

		if(atof(value) > 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("%s\n",errmsg);
			return -1;
		}
		else
		{
			printf("delete done\n ");
			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("%s\n",errmsg);
		return -1;
	}
	else
	{
		printf("query done\n ");
		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:%.2f\n",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 cancelled\n");
			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("%s\n",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 done\n ");
			return 0;
		}
	}
	else//系统中没有的水果种类
	{
		printf("We don't have this fruit:%s. Please stock it.",fruit.name);
	}
		return 0;
}
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443

sqlite3 测试验证

交叉编译：
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 库路径，即libsqlite3.a所在目录。
-o 编译生成的文件名为sql ，testSql.c是源程序文件;
-lsqlite3指明要链接静态库文件libsqlite3.a。
将生成的可执行文件拷贝到开发板,运行测试：

友情链接，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