嵌入式环境下使用SQLite

2018-10-23

转载请注明出处。https://rhirufxmbcyj.gitlab.io

环境搭建

下载SQLite的源码，https://www.sqlite.org/download.html，下载名为sqlite-autoconf的包，在交叉编译环境下（我的是Ubuntu系统）使用arm编译器进行编译。

# 将包内的文件提取出来，假定提取后的文件夹名为sqlite-autoconf-3250200

# cd到此目录，使用./configure --host=arm-arago-linux-gnueabi --prefix=/home/root/Desktop/sqlite/build
PS：我的交叉编译工具链中gcc全名为arm-arago-linux-gnueabi-gcc，所以host就填写为arm-arago-linux-gnueabi，configure用到的gcc、g++、strip等都会以arm-arago-linux-gnueabi为前缀自动引用到。prefix就是生成的so和头文件的路径，可以自己定义

# 等待configure完成后，执行make && make install

# make完成后将build目录下，lib文件夹中的文件拷贝到开发板系统中的/lib目录下，将bin中的文件拷贝到开发板系统的/bin目录下

# 在开发板系统中执行sqlite3 test.db命令，如果可以执行成功，则说明移植成功，执行成功出现

SQLite version 3.25.2 2018-09-25 15:08:22
Enter ".help" for usage hints.
sqlite> 

我第一次执行并没有成功，因为依赖libreadline.so.5，既然能编译成功，说明交叉编译环境中是有这个库的，使用locate libreadline查找库的位置，并拷贝到开发板系统的/lib目录下。
注意：如果出现了多个libreadline库的位置，需要拷贝交叉编译器目录下的库，不能使用当前系统中的libreadline库。我的libreadline库位置在/usr/local/arm/cross/am335xt3/devkit/arm-arago-linux-gnueabi/usr/lib/下，大家根据自己的环境查找。

# 开发板的环境搭建好了，还需要将生成的build/lib目录下的文件拷贝一份到交叉编译器的lib下，使之可以编译c/c++代码成功，并且将build/include目录下的头文件拷贝到交叉编译器的include目录下，我的include目录在/usr/local/arm/cross/am335xt3/devkit/arm-arago-linux-gnueabi/usr/include

# 这时，交叉编译环境下的sqlite也搭建好了，注意编译时需要链接sqlite3库，例如arm-arago-linux-gnueabi-gcc test.c -lsqlite3

示例代码

创建表、插入表项

#include <stdio.h>
#include <sqlite3.h>
int main( void )
{
	sqlite3 *db=NULL;
	char *error_msg = NULL;
	int ret = 0;
	char *sql;
	//打开数据库文件,如果不存在将创建一个数据库文件
	sqlite3_initialize();
	ret = sqlite3_open_v2("test.db", &db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NULL); 
	if( ret )
	{
		sqlite3_close_v2(db);
		sqlite3_shutdown();
		return 0;
	}
	//创建表,如果表存在，则不创建，并给出提示信息，存储在 error_msg 中
	//表中只有一个自增键 每次插入自动+1
	sql = "CREATE TABLE test_table(\
		ID INTEGER PRIMARY KEY, \
		);";
	sqlite3_exec( db, sql, 0, 0, &zErrMsg );
 	sqlite3_free(zErrMsg);
	//插入数据 
	sql = "INSERT INTO \"test_table\" VALUES(null);" ;
	//执行sql语句  每次传出zErrMsg都应该调用sqlite3_free(zErrMsg)释放内存
	sqlite3_exec( db, sql, 0, 0, &zErrMsg );
	sqlite3_exec( db, sql, 0, 0, &zErrMsg );
 	sqlite3_exec( db, sql, 0, 0, &zErrMsg );
	sqlite3_exec( db, sql, 0, 0, &zErrMsg );
	sqlite3_exec( db, sql, 0, 0, &zErrMsg );
	sqlite3_exec( db, sql, 0, 0, &zErrMsg );
	
	sqlite3_close_v2(db); //关闭数据库
	sqlite3_shutdown();
	return 0;
}

查询表项

接上边的插入好的数据库文件

#include <stdio.h>
#include <sqlite3.h>
int main()
{
	sqlite3 *db = NULL;
	char *error_msg = NULL;
	int ret = 0;
	sqlite3_initialize();
	ret = sqlite3_open_v2("test.db", &db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NULL); 
	if( ret )
	{
		sqlite3_close_v2(db);
		sqlite3_shutdown();
		return 0;
	}
	char **table_array = NULL;
	int nrow = 0,ncolumn = 0;
	int i = 0,j = 0,index = 0;
	ret = sqlite3_get_table(db,"SELECT * FROM test_table",&table_array,&nrow,&ncolumn,&error_msg);
	if(ret == SQLITE_OK)
	{
		index = 0;
		//打印列名 此例只有一项“ID”
		printf("%s\n",table_array[index++]);
		for(i=0;i<nrow;i++)
		{
			for(j=0;j<ncolumn;j++)
			{
				printf("%s\n",table_array[index++]);
			}
			printf("\n");
		}
		sqlite3_free_table(table_array);
	}
    if(table_array)
        sqlite3_free_table(table_array);
    sqlite3_free(error_msg);
	sqlite3_close_v2(db);
	sqlite3_shutdown();
	return 0;
}

删除表项

#include <stdio.h>
#include <sqlite3.h>
int main()
{
	sqlite3 *db = NULL;
	char *sql = NULL;
	char *error_msg = NULL;
	int ret = 0;
	sqlite3_initialize();
	ret = sqlite3_open_v2("test.db", &db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NULL); 
	if( ret )
	{
		sqlite3_close_v2(db);
		sqlite3_shutdown();
		return 0;
	}
	//删除ID为5的表项
	sql = "DELETE FROM test_table where ID = 5";
	//删除按ID排序的前三项
	//sql = "DELETE FROM test_table where ID in(SELECT * FROM test_table order by ID limit 3)";
	//删除表中全部内容
	//sql = "DELETE FROM test_table;"
	int ret = sqlite3_exec(db,sql,0,0,&error_msg);
	if(ret == SQLITE_OK)
	{
		//作用是重新构建数据库文件，回收空白空间，减小数据库文件的大小。
		sqlite3_exec(db,"VACUUM;",0,0,&error_msg);
	}
	sqlite3_close_v2(db);
	sqlite3_shutdown();
	return 0;
}

工作需求

在开发板中插入SD卡存放数据库文件来保存数据，以实现方便快捷的增删改查，要求SD卡满了将早期数据删除再存放新插入的数据。但是经过测试，SD卡满了以后再删除数据就会报错，会提示database or disk is full。

解决方案

每次插入数据时检查一次SD卡空间，当SD卡占用达到一定程度（暂定80%）时，进行一次早期数据删除。

#include <stdio.h>
#include <sys/vfs.h>
#include <sqlite3.h>

int check_space();
int free_some_space();

int main(void)
{
	sqlite3 *db = NULL;
	char *error_msg = NULL;
	int ret = 0;
	char *sql;
	//打开数据库文件,如果不存在将创建一个数据库文件
	sqlite3_initialize();
	ret = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
	if (ret)
	{
		sqlite3_close_v2(db);
		sqlite3_shutdown();
		return 0;
	}
	//创建表,如果表存在，则不创建，并给出提示信息，存储在 error_msg 中
	//表中只有一个自增键 每次插入自动+1
	sql = "CREATE TABLE test_table(\
		ID INTEGER PRIMARY KEY, \
		);";
	sqlite3_exec(db, sql, 0, 0, &zErrMsg);

	//插入数据 
	sql = "INSERT INTO \"test_table\" VALUES(null);";
	//执行sql语句
	while (1)
	{
		//SD卡占用空间达到80%时 删除一些早期的数据 释放空间
		if (check_space() == -1)
		{
			ret = free_some_space(db);
			if (ret != SQLITE_OK)
			{
				printf("error code is %d\n", ret);
				goto End;
			}
		}
		ret = sqlite3_exec(db, sql, 0, 0, &error_msg);
		printf("ret: %d  msg: %s\n", ret, error_msg);
	}

End:
	sqlite3_close_v2(db); //关闭数据库
	sqlite3_shutdown();
	return 0;
}

int check_space()
{
	struct statfs sfs;
	statfs("/media/mmcblk0p1", &sfs);
	int percent = (sfs.f_blocks - sfs.f_bfree) * 100 / (sfs.f_blocks - sfs.f_bfree + sfs.f_bavail) + 1;
	//SD卡占用空间达到80%时 返回-1
	if (percent > 80)
		return -1;
	return 0;
}

int free_some_space()
{
	char *sql = ;
	char *error_msg = "DELETE FROM test_table where ID in(SELECT * FROM test_table order by ID limit 20);";
	int ret = sqlite3_exec(db, sql, 0, 0, &error_msg);
	if (ret == SQLITE_OK)
	{
		//重新构建数据库文件，回收空白空间，减小数据库文件的大小。
		ret = sqlite3_exec(db, "VACUUM;", 0, 0, &error_msg);
	}
	return ret;
}

参考资料：

An Introduction To The SQLite C/C++ Interface

Result and Error Codes