全志H616开发学习笔记（七）：轻量数据库SQLite

轻量数据库SQLite

基本概念介绍

SQLite是一款轻量化、易用的数据库。一般用于设备端的数据管理，可以理解成单点的数据库。相比于传统服务器型数据库用于管理多端设备，十分复杂，SQLite的数据库无服务器，是自包含的。这也称为嵌入式数据库，这意味着数据库引擎作为应用程序的一部分运行。

下面以MySQL为例对比一下SQLite和传统服务型数据库的优缺点：

SQLite的优点	SQLite的缺点	MySQL的优点	MySQL的缺点
基于文件，易于设置 和使用 适合基础开发和测试 轻松携带 使用标准SQL语法进 行微小更改 使用方便	缺乏用户管理和 安全功能 不容易扩展 不适合大数据库 无法定制	使用方便 提供了许多与数据库相 关的功能 良好的安全功能 易于扩展，适用于大型 数据库 提供良好的速度和性能 提供良好的用户管理和 多种访问控制	需要一些技术专业知 识来设置 与传统SQL相比，语 法略有不同

基于嵌入式的数据库主要有：SQLite，Firebird，Berkeley DB，eXtremeDB

• Firebird 是关系型数据库，功能强大，支持存储过程，SQL兼容等
• SQLite 关系型数据库，体积小，支持ACID事务（Atomicity、Consistency、Isolation、Durability）
• Berkeley DB 并没有数据库服务器的概念，他的程序直接链接到应用程序中
• eXtremeDB 是内存数据库，运行效率高

SQLite安装

SQLite的安装有两种方式，一种是通过apt install sqlite，从apt源获取官方编译好的版本（但目前只能获得2.x版本的）；另一种方式是我们手动去下载编译源码，然后自行编译。这里详细介绍一下第二种方式。

• 进入SQLite官网，选择合适版本的tar.gz格式源码包点击下载

• 然后将下载的文件通过Mobaxterm或Ftp上传至开发板，tar xvf 解压

• 使用./configure --prefix=/usr/local命令，配置安装路径为/usr/local

• make命令编译，耗时较长约15分钟

• make install ，执行安装

• 安装后使用sqlite 3命令即可进入sqlite控制台。

自行编译的方式比较适合上位机，特别是在没有较为完整系统环境的时候（实际应用中会根据具体产品使用场景对完整的系统进行精简，只保留需要的模块），可能会用到交叉编译，速度也更快

SQLite基本操作练习

• 数据库的创建和打开

  有两种方式，第一种是sqlite3进入控制台后使用.open database.db命令，若存在该文件则打开否则创建；另一种是直接在命令行使用sqlite3 database.db进入控制台再输入.database命令，作用同上。

• 数据表的创建与删除

  create table report(id Integer,name char,score Integer);

  .database查询所有数据库，.tables查询当前数据库所有数据表

  drop table report

• 数据插入，查询，更新

  insert into report values(001,'张三',80);

  insert into report(name,score) values('李四',88)

  select * from report;

  update report set score=95 where id=001;

• 表结构的更改

  alter table report add column class char;

  alter table report drop column class char;

关于SQLite更多命令及其语法，可以参考SQLite 语法-菜鸟教程

C语言SQLite编程操作相关API

打开/创建数据库的接口

• sqlite3_open(const char *filename, sqlite3 **ppDb)
  该例程打开一个指向 SQLite 数据库文件的连接，返回一个用于其他 SQLite 程序的数据库连接对象。

• sqlite3_close(sqlite3*);
  该例程关闭之前调用 sqlite3_open() 打开的数据库连接。所有与连接相关的语句都应在连接关闭之前完
  成。如果还有查询没有完成，sqlite3_close() 将返回 SQLITE_BUSY 禁止关闭的错误消息。

• const char *sqlite3_errmsg(sqlite3*);
sqlite3_errcode() 通常用来获取最近调用的API接口返回的错误代码.

测试代码：

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
int main(char argc, char** argv){
	sqlite3 *db;
	int ret;
	if(argc < 2){
		printf("Usage: %s xxx.db\n",argv[0]);
		return -1;
	} 
	if( (ret = sqlite3_open(argv[1],&db)) == SQLITE_OK){
		printf("open %s success\n",argv[1]);
	}else{
		printf("error:%s,%d\n",sqlite3_errmsg(db),ret);
		if(ret == 14){
			printf("permission denied\n");
		} 
		return -1;
	}
	sqlite3_close(db);
	printf("done\n");
	return 0;
	return 0;
}

编写好后使用gcc编译时注意使用-lsqlite3参数链接sqlite3库（在/usr/local/lib下）

执行sql语句的C接口

这一小节主要学习用于执行SQL语句的一个C语言API：sqlite3_exec

原型：

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

该例程提供了一个执行SQL 语句的快捷方式，SQL 语句具体内容由 sql 参数提供，可以由多个 SQL 语句组合而成，通过英文分号分隔。
在这里，第一个参数 sqlite3 是打开的数据库对象，sqlite_callback 是一个回调，data 作为其第一
个参数，errmsg 将被返回用来获取程序生成的任何错误。
该接口将解析并执行由 sql 参数提供的每条语句，直到字符串结束或者遇到错误为止

下面以一个小demo为例来学习其基本使用方法：

#include <stdio.h>
#include <sqlite3.h>
int callback(void *arg, int column_size, char *column_value[], char *column_name[])
{
	int i;
	printf("arg=%s\n",(char *)arg);
	for(i=0;i<column_size;i++){
		printf("%s = %s\n", column_name[i], column_value[i]);
	} 
	printf("=======================\n");
	return 0;//必须返回0，这样数据库中有多少条数据,这个回调函数就会被调用多少次，否则只会回调一次
} 
int main(char argc, char **argv)
{
	sqlite3 *db;
	char *errorMsg = NULL;
	int ret;
	if(argc < 2){
		printf("Usage: %s xxx.db\n",argv[0]);
		return -1;
	} 
	if( (ret = sqlite3_open(argv[1],&db)) == SQLITE_OK){
		printf("open %s success\n",argv[1]);
	}else{
		printf("error:%s,%d\n",sqlite3_errmsg(db),ret);
		if(ret == 14){
			printf("permission denied\n");
		} 
		return -1;
	} 
	sqlite3_exec(db, "select * from stu2;",callback, "exec result:",&errorMsg);
	sqlite3_close(db);
	printf("done\n");
	return 0;
}

可以看到，sql_exec函数能够为我们执行sql语句，但不直接输出执行结果，而是调用回调函数callback去处理。

关于callback，有四个参数，分别的含义和作用：

• void *arg是sqlite3_exec函数的第四个参数，用于错误消息的传递
• column_size：数据库的字段数
• column_value[]：列的值， column_name：字段名字

上方的demo首先是执行了select语句，然后将执行结果传递给回调函数处理。当且仅当callback函数的返回值设为0，才会处理sql_exec结果的每一行。

执行结果：

在上面的代码中存在一个隐藏问题：errorMsg一开始是一个野指针，并没有开辟空间，如果直接打印输出的话照理说会报错Segment fault，但经过测试证明该操作sqlite3_exec函数本身会为该指针开辟空间

使用sqlite3_exec创建表并插入数据

下面两个例子参照菜鸟教程编写得来，首先是创建了一个表格（若已存在，则会给出提示信息，并不覆盖原有表），然后向其中插入了一些记录

点击展开

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
	int i;
	for(i=0; i<argc; i++){
		printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
	}
	printf("\n");
	return 0;
} 
int main(int argc, char* argv[])
{
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	char *sql;
	/* Open database */
	rc = sqlite3_open("test.db", &db);
	if( rc ){
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		exit(0);
	}else{
		fprintf(stdout, "Opened database successfully\n");
	}
    
	/* Prepare SQL statement */
    sql-create = "CREATE TABLE COMPANY(" \
        "ID INT PRIMARY KEY NOT NULL," \
        "NAME TEXT NOT NULL," \
        "AGE INT NOT NULL," \
        "ADDRESS CHAR(50)," \
        "SALARY REAL );";
    sql-insert = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
       "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
       "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \
       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
       "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
       "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
    
	/* Execute SQL statement */
	rc = sqlite3_exec(db, sql-create, callback, 0, &zErrMsg);
	if( rc != SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}else{
		fprintf(stdout, "Table created successfully\n");
	}
    
    rc = sqlite3_exec(db, sql-insert, callback, 0, &zErrMsg);
	if( rc != SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}else{
		fprintf(stdout, "Table created successfully\n");
	}
    
	sqlite3_close(db);
	return 0;
}

小挑战：将SQLite中的数据转换成链表