SQLite 使用简介

2016-01-19 Tuesday     misc

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));
		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 的资源;若休眠时间过长,会造成等待的时间太长;若重试次数太少,则会降低操作的成功率。

参考

SQLite C tutorial

Command Line Shell For SQLite



如果喜欢这里的文章,而且又不差钱的话,欢迎打赏个早餐 ^_^


About This Blog

Recent Posts

Categories

Related Links

  • RTEMS
    RTEMS
  • GNU
  • Linux Kernel
  • Arduino

Search


This Site was built by Jin Yang, generated with Jekyll, and hosted on GitHub Pages
©2013-2019 – Jin Yang