SQLite 使用简介

2016-01-19 language database sqlite

SQLite 是一个开源的嵌入式关系数据库，一个简单无需配置的小型数据库，支持事物，在嵌入式设备或者小型应用中使用较多，例如 Android、Chrome、WeChat 等系统。

它在 2000 年由 D. Richard Hipp 发布，用来减少应用程序管理数据的开销，可移植性好、很容易使用、高效而且可靠。

SQLite logo

简介

源码编译

最新版本的代码可以直接从 www.sqlite.org 上下载，在 README.md 中有相关的介绍，例如编译、源码概览等。

SQLite 使用了 Fossil 做版本管理，历史版本可以通过 taglist 中下载，选择对应的版本，进入 check-in 中的版本号，然后下载 ZIP 格式包即可。

----- 暂时关闭TCL的支持，否则需要提供tclsh命令
./configure --disable-tcl

如上是关闭了 tcl 支持，如果需要，那么在编译前需要安装 tcl 包，在 CentOS 中可以通过 yum install tcl 安装。

安装

在 CentOS 中可以可以直接通过如下方式安装。

# yum install sqlite

常见命令 

----- 直接新建一个文件名为foobar.db的数据库
$ sqlite foobar.db

----- 新建一个表
sqlite> create table foobar(id integer primary key, value text);

----- 直接插入部分数据
sqlite> insert into foobar(id, value) values(1, 'Micheal'), (2, 'Jenny'), (3, 'Francis');

----- 查看数据
sqlite> select * from foobar;
1|Micheal
2|Jenny
3|Francis

----- 设置查询返回的结果
sqlite> .mode column;                         # 按照列格式显示
sqlite> .header on;                           # 显示列名称
sqlite> select * from foobar;
id          value
----------- -------------
1           Micheal
2           Jenny
3           Francis

----- 添加列
sqlite> alter table foobar add column email text not null '' collate nocase;

----- 创建视图
sqlite> create view nameview as select value from foobar;
sqlite> select * from nameview;

----- 创建索引
sqlite> create index idx_value on foobar(value);

----- 查看帮助
sqlite> .help

----- 查看所有表，包括视图
sqlite> .tables

----- 显示表结构
sqlite> .schema [table|view]

----- 获取指定表的索引列表
sqlite > .indices [table]
idx_value

----- 导出数据库到SQL文件
sqlite > .output [filename ]
sqlite > .dump
sqlite > .output stdout

----- 备份、恢复数据库
$ sqlite foobar.db .dump > backup.sql
$ cat backup.sql | sqlite3 foobar-restore.db
$ sqlite3 foobar-restore.db < backup.sql

C 编程 

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

#define SQL_CREATE_TBL_DAEMON                                     \
	"CREATE TABLE IF NOT EXISTS daemon("                      \
		"name CHAR(64) PRIMARY KEY NOT NULL, "            \
		"version CHAR(64) NOT NULL, "                     \
		"gmt_modify NOT NULL DEFAULT CURRENT_TIMESTAMP, " \
		"gmt_create NOT NULL DEFAULT CURRENT_TIMESTAMP"   \
	");"

static int callback(void *non, int argc, char **argv, char **cols)
{
	(void) non;
	int i;

	for (i = 0; i < argc; i++)
		printf("%s = %s\n", cols[i], argv[i] ? argv[i] : "NULL");
	printf("\n");

	return 0;
}

int main(void)
{
	int rc;
	sqlite3 *db;
	sqlite3_stmt *res;
	char *sql, *errmsg;

	printf("Current SQLite version: %s\n", sqlite3_libversion());

	/* OR ":memory:" to create a memory database */
	rc = sqlite3_open("daemon.db", &db);
	if (rc != SQLITE_OK) {
		fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db); /* in case of resource leak */
		return 1;
	}
	printf("Opened SQLite handle successfully.\n");

	rc = sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &res, 0);
	if (rc != SQLITE_OK) {
		fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;
	}
	rc = sqlite3_step(res);
	if (rc == SQLITE_ROW)
		printf("Got version from SQL: %s\n", sqlite3_column_text(res, 0));
	sqlite3_finalize(res);

	/* "INSERT INTO XXXX VALUES(1, 'Audi', 52642);" */
	rc = sqlite3_exec(db, SQL_CREATE_TBL_DAEMON, NULL, NULL, &errmsg);
	if (rc != SQLITE_OK ) {
		fprintf(stderr, "SQL error: %s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(db);
		return 1;
	}
	printf("Create daemon table successfully.\n");

	sql = "INSERT INTO daemon(name, version) VALUES "
		"('MonitorAgent', 'V1.0.0'), ('SecurityAgent', 'V1.0.2');";
	rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if (rc != SQLITE_OK ) {
		fprintf(stderr, "SQL error: %s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(db);
		return 1;
	}
        printf("Insert data successfully\n");
	int lastid = sqlite3_last_insert_rowid(db);
	printf("The last Id of the inserted row is %d\n", lastid);


	/* SELECT DATETIME(gmt_modify, "localtime") FROM daemon; */
	sql = "SELECT * FROM daemon";
	rc = sqlite3_exec(db, sql, callback, NULL, &errmsg);
	if (rc != SQLITE_OK ) {
		fprintf(stderr, "SQL error: %s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(db);
		return 1;
	}

	char **result;
	int nrow, ncol;
	rc = sqlite3_get_table( db , sql , &result , &nrow , &ncol , &errmsg);
	if (rc != SQLITE_OK) {
		fprintf(stderr, "SQL error: %s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(db);
		return 1;
	}
	for(rc = 0; rc < (nrow + 1 ) * ncol ; rc++ )
		printf( "result[%d] = %s\n", rc, result[rc]);
	sqlite3_free_table(result);


	rc = sqlite3_prepare_v2(db, "SELECT * FROM daemon", -1, &res, 0);
	if (rc != SQLITE_OK) {
		fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;
	}
	while((rc = sqlite3_step(res)) == SQLITE_ROW)
		printf("Got version from SQL: %s\n", sqlite3_column_text(res, 0));
		//printf("Got version from SQL: %d\n", sqlite3_column_int(res, 0));
	sqlite3_finalize(res);

	sql = "UPDATE daemon SET version = 'V2.0.1', gmt_modify = CURRENT_TIMESTAMP WHERE name = ?1;";
	rc = sqlite3_prepare_v2(db, sql, -1, &res, 0);
	if (rc != SQLITE_OK) {
		fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;
	}
	sqlite3_bind_text(res, 1, "MonitorAgent", -1, SQLITE_STATIC);
        //sqlite3_bind_int(res, 1, 3);
	rc = sqlite3_step(res);
	if (rc == SQLITE_ROW)
		printf("%s: ", sqlite3_column_text(res, 0));
	sqlite3_finalize(res);

	sql = "UPDATE daemon SET version = 'V2.0.1', gmt_modify = CURRENT_TIMESTAMP WHERE name = 'MonitorAgent';";
	rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if (rc != SQLITE_OK ) {
		fprintf(stderr, "SQL error: %s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(db);
		return 1;
	}

	sql = "DELETE FROM daemon";
	rc = sqlite3_exec(db, sql, NULL, 0, &errmsg);
	if (rc != SQLITE_OK ) {
		fprintf(stderr, "SQL error: %s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(db);
		return 1;
	}
	sqlite3_close(db);

	return 0;
}

关闭

在通过 sqlite3_close() 关闭时，如果有 stmt 没有关闭则会返回报错，可以通过如下方式关闭。

void db_destroy(void)
{
    int rc = 0;
    sqlite3_stmt *stmt;

    if (db == NULL)
            return;

    rc = sqlite3_close(db);
    while(rc == SQLITE_BUSY) {
        stmt = sqlite3_next_stmt(db, NULL);
        if (stmt == NULL) {
            NEW_INFO(LOG_CATE_DAEMON, "Destroy SQLite error, unexpect NULL");
            sqlite3_close(db);
            break;
        }

        if (sqlite3_finalize(stmt) == SQLITE_OK)
            rc = sqlite3_close(db);
    }
    NEW_INFO(LOG_CATE_DAEMON, "Destroy SQLite(0x%lx) structure.", db);

    db = NULL;
}

并发访问控制

简单来说，在使用 SQLite 时，如果采用的是多线程或者多进程访问，一旦并发访问量过大，而又没有做并发控制，经常会遇到 database is locked SQLITE_BUSY(5) 的报错。

官网提供了 SQLite 并发模型的介绍，可以参考 Using SQLite In Multi-Threaded Applications 。

对于 SQLite 来说，只支持库级锁，也就是说，即使有两个事务分别对不同的表进行操作，那么这两个事物也是无法同时运行的，更不要说是元组级别的了。

当多个线程可以同时读数据库，但多个线程(多个链接)写入时就会发生冲突，也就是说 SQLite 实现的是多读单写。

重试机制

SQLite 提供了 Busy Retry 的方案，即发生阻塞时，会触发 Busy Handler，此时可以让线程休眠一段时间后，重新尝试操作，当重试一定次数依然失败后，则返回 SQLITE_BUSY 错误码。

提供两个 busy handle 函数 sqlite3_busy_timeout() sqlite3_busy_handle() 在并发访问失败时，可以进行重试，详细可以参考 Register A Callback To Handle SQLITE_BUSY Errors 。

也可以通过 PRAGMA 命令进行设置 sqlite3_exec(db, "PRAGMA busy_timeout=times", 0, 0, err); 等同于调用第一个 API 。

注意，对于同一个链接来说，只能有一个，两个会相互影响。

当然，这只能降低出现 SQLITE_BUSY 的概率，而不能彻底消除。

BusyHandler

实际上，SQLite 内部提供了默认的处理函数 sqliteDefaultBusyCallback ，这里简单介绍对用户提供的 API 接口。

int sqlite3_busy_handler(sqlite3 *, int (*)(void *, int), void *);

默认回调函数为 NULL ，此时会在申请不到锁时直接返回 BUSY。设置回调之后，当返回非 0 时会自动重试，否则返回 BUSY 。其中设置回调函数的第二个入参表示当前因 BUSY 事件调用该函数的次数。

int sqlite3_busy_timeout(sqlite3*, int ms);

上述函数用来设置等待 BUSY 的超时时间，SQLite 会 sleep 并重试当前操作，如果失败则返回 BUSY ，这里的时间必须要大于 1s ，否则无效。为了方便自己控制时间，最好的方式还是用第一种。

总结

在 Retry 过程中，休眠时间的长短和重试次数，是决定性能和操作成功率的关键。

根据不同的场景，其最优值也会有所区别，若休眠时间太短或重试次数太多，会空耗 CPU 的资源；若休眠时间过长，会造成等待的时间太长；若重试次数太少，则会降低操作的成功率。

参考