csdn lidp 转载注明出处

封装几个sqlite3 数据库接口，通过利用sqlite3提供的api实现数据库的打开，关闭，创建表，添加，删除字段,基于事务实现。

此数据库接口为我在google code上建立的的开源项目spider-tool的一部分，

关于spider-tool，欢迎访问google code.

https://spider-tool.googlecode.com

sqlite3 C语言api接口参照这里；

http://www.sqlite.org/c3ref/intro.html

http://wenku.baidu.com/view/9b0c2619964bcf84b9d57b46.html

http://wenku.baidu.com/view/65005486b9d528ea81c7794b.html

调用sqlite3_prepare()将sql语句编译为sqlite内部一个结构体(sqlite3_stmt). 该结构体中包含了将要执行的的 sql语句的信息.
** 如果需要传入参数,在sql语句中用'?'作为占位符,再调用sqlite3_bind_XXX()函数将对应的参数传入.
** 调用sqlite3_step(),这时候sql语句才真正执行.注意该函数的返回值,sqlITE_DONE和sqlITE_ROW都是表示执行成功,不同的是sqlITE_DONE表示没有查询结果,象UPDATE,INSERT这些sql语句都是返回sqlITE_DONE,SELECT查询语句在 查询结果不为空的时候返回sqlITE_ROW,在查询结果为空的时候返回sqlITE_DONE.
** 每次调用sqlite3_step()的时候,只返回一行数据,使用sqlite3_column_XXX()函数来取出这些数据.要取出全部的数据需要 反复调用sqlite3_step(). (注意,在bind参数的时候,参数列表的index从1开始,而取出数据的时候,列的index是从0开始).
** 在sql语句使用完了之后要调用sqlite3_finalize()来释放stmt占用的内存.该内存是在sqlite3_prepare()时分配的.
** 如果sql语句要重复使用,可以调用sqlite3_reset()来清楚已经绑定的参数.

db.h

/*
 * Spider -- An open source C language toolkit.
 *
 * Copyright (C) 2011,Inc.
 *
 * lidp <openser@yeah.net>
 *
 * This program is free software,distributed under the terms of
 * the GNU General Public License Version 2. See the LICENSE file
 * at the top of the source tree.
 */
#ifndef _SPIDER_INTERNAL_DB_H
#define _SPIDER_INTERNAL_DB_H

#if defined(__cplusplus) || defined(c_plusplus)
extern "C" {
#endif

struct spd_db_entry {
	struct spd_db_entry *next;
	char *key;
	char data[0];
};

/*!\brief Get key value specified by family/key */
int spd_db_get(const char *table,const char *key,char *buf,int len);

/*!\brief Store value addressed by family/key */
int spd_db_put(const char * table,const char *buf);

/*!\brief Delete entry in db */
int spd_db_del(const char *table,const char *key);

/*!\brief Delete one or more entries in spddb
 * If both parameters are NULL,the entire database will be purged.  If
 * only keytree is NULL,all entries within the family will be purged.
 * It is an error for keytree to have a value when family is NULL.
 *
 * \retval -1 An error occurred
 * \retval >= 0 Number of records deleted
 */
int spd_db_deltree(const char *table,const char *key);

/*!\brief Get a list of values within the spddb tree
 * If family is specified,only those keys will be returned.  If keytree
 * is specified,subkeys are expected to exist (separated from the key with
 * a slash).  If subkeys do not exist and keytree is specified,the tree will
 * consist of either a single entry or NULL will be returned.
 *
 * Resulting tree should be freed by passing the return value to spd_db_freetree()
 * when usage is concluded.
 */
struct spd_db_entry *spd_db_gettree(const char *table,const char *key);

/*!\brief Free structure created by spd_db_gettree() */
void spd_db_freetree(struct spd_db_entry *entry);

/* init db engine at start time */
int spddb_init(void);
void spddb_uninit(void);
	
#if defined(__cplusplus) || defined(c_plusplus)
}
#endif
#endif

db.c

/*
 * Spider -- An open source C language toolkit.
 *
 * Copyright (C) 2011,distributed under the terms of
 * the GNU General Public License Version 2. See the LICENSE file
 * at the top of the source tree.
 */

 #include <sys/time.h>
 #include <sys/types.h>
 #include <sys/stat.h>
 #include <unistd.h>
 #include <signal.h>
 #include <dirent.h>
 #include <sqlite3.h>

 #include "logger.h"
 #include "utils.h"
 #include "thread.h"
 #include "lock.h"
 #include "internal_db.h"
 #include "strings.h"
 #include "test_engine.h"
 
/*
  * sqlite c interface api
  * http://www.sqlite.org/c3ref/open.html
  */

SPD_MUTEX_DEFINE_STATIC(dblock);
static spd_cond_t dbcond;
static pthread_t  dbsync_thread;
static int doexit;
static sqlite3 *spd_db;
const char* spddb_dir = "/tmp/spddb";


#define SPD_DB_STATEMENT_DEFINE(stmt,sql) static sqlite3_stmt *stmt; \
	const char stmt##_sql[] = sql;

SPD_DB_STATEMENT_DEFINE(put_stmt,"INSERT OR REPLACE INTO spd_db (key,value) VALUES (?,?)")
SPD_DB_STATEMENT_DEFINE(get_stmt,"SELECT value FROM spd_db WHERE key =?")
SPD_DB_STATEMENT_DEFINE(del_stmt,"DELETE FROM spd_db WHERE key=?")
SPD_DB_STATEMENT_DEFINE(deltree_stmt,"DELETE FROM spd_db WHERE key || '/' LIKE ? || '/' || '%'")
SPD_DB_STATEMENT_DEFINE(deltree_all_stmt,"DELETE FROM spd_db")
SPD_DB_STATEMENT_DEFINE(gettree_stmt,"SELECT key,value FROM spd_db WHERE key || '/' LIKE ? || '/' || '%' ORDER BY key")
SPD_DB_STATEMENT_DEFINE(gettree_all_stmt,value FROM spd_db ORDER BY key")
SPD_DB_STATEMENT_DEFINE(create_spd_db_stmt,"CREATE TABLE IF NOT EXISTS spd_db(key VARCHAR(256),value VARCHAR(256),PRIMARY KEY(key))");

static int db_open()
{
	char *dbname;
	if(!(dbname = alloca(strlen(spddb_dir) + sizeof(".db")))) {
		spd_log(LOG_ERROR,"alloca Failed\n");
		return -1;
	}	

	strcpy(dbname,spddb_dir);
	strcat(dbname,".db");

	spd_mutex_lock(&dblock);
	if(sqlite3_open(dbname,&spd_db) != sqlITE_OK) {
		spd_log(LOG_ERROR,"Failed open db '%s' %s\n",dbname,sqlite3_errmsg(spd_db));
		spd_mutex_unlock(&dblock);
		return -1;
	}
	spd_mutex_unlock(&dblock);

}

/*
  * sync db signal,must lock db first.
  */
static void do_dbsync()
{
	spd_cond_signal(&dbcond);
}

static int init_stmt(sqlite3_stmt **stmt,const char *sql,size_t len)
{
	spd_mutex_lock(&dblock);
	if(sqlite3_prepare(spd_db,sql,len,stmt,NULL) != sqlITE_OK) {
		spd_log(LOG_WARNING,"could not prepare statement '%s': %s\n",sqlite3_errmsg(spd_db));
		spd_mutex_unlock(&dblock);
		return -1;
	}
	spd_mutex_unlock(&dblock);

	return 0;
}

static int db_create()
{
	int ret = 0;
	
	if(!create_spd_db_stmt) {
		init_stmt(&create_spd_db_stmt,create_spd_db_stmt_sql,sizeof(create_spd_db_stmt_sql));
	}

	spd_mutex_lock(&dblock);
	if(sqlite3_step(create_spd_db_stmt) != sqlITE_DONE) {
		spd_log(LOG_WARNING,"Couldnot create spd db table: %s\n",sqlite3_errmsg(spd_db));
		ret = -1;
	}

	sqlite3_reset(create_spd_db_stmt);
	do_dbsync();
	spd_mutex_unlock(&dblock);

	return ret;
}

/*
 * init stmt on start up for performance.
 */
static int db_init_statements()
{
	return init_stmt(&get_stmt,get_stmt_sql,sizeof(get_stmt_sql))
		|| init_stmt(&del_stmt,del_stmt_sql,sizeof(del_stmt_sql))
		|| init_stmt(&deltree_stmt,deltree_stmt_sql,sizeof(deltree_stmt_sql))
		|| init_stmt(&deltree_all_stmt,deltree_all_stmt_sql,sizeof(deltree_all_stmt_sql))
		|| init_stmt(&gettree_stmt,gettree_stmt_sql,sizeof(gettree_stmt_sql))
		|| init_stmt(&gettree_all_stmt,gettree_all_stmt_sql,sizeof(gettree_all_stmt_sql))
		|| init_stmt(&put_stmt,put_stmt_sql,sizeof(put_stmt_sql));
}



/* init db engine,open,create and init stmt */
static int db_init()
{
	if(spd_db)
		return 0;

	if(db_open() || db_create() || db_init_statements())
		return -1;

	return 0;
}

static void spd_db_close(sqlite3 *db)
{
	sqlite3_close(db);
}

/*
  * perform clean up resource.
  */
void spddb_uninit(void)
{
	spd_log(LOG_NOTICE,"uninit db \n");
	doexit = 1;
	spd_mutex_lock(&dblock);
	do_dbsync();
	spd_mutex_unlock(&dblock);
	
	pthread_join(dbsync_thread,NULL);
	spd_mutex_lock(&dblock);
	spd_db_close(spd_db);
	spd_mutex_unlock(&dblock);

	spd_mutex_destroy(&dblock);
	spd_cond_destroy(&dbcond);
	spd_log(LOG_NOTICE,"uninit db end .\n");
}

int spd_db_get(const char * table,const char * key,char * buf,int len)
{
	const unsigned char *result;
	char fullkey[256];
	size_t fullkey_len;
	int res = 0;

	if(strlen(table) + strlen(key) + 2 > sizeof(fullkey) -1) {
		spd_log(LOG_WARNING,"table and key too large lengh\n");
		return -1;
	}

	fullkey_len = snprintf(fullkey,sizeof(fullkey),"/%s/%s",table,key);

	spd_mutex_lock(&dblock);

	if(sqlite3_bind_text(get_stmt,1,fullkey,fullkey_len,sqlITE_STATIC) != sqlITE_OK) {
		spd_log(LOG_WARNING,"Couldnot bind key to stmt:%s\n",sqlite3_errmsg(spd_db));
		res = -1;
	} else if(sqlite3_step(get_stmt) != sqlITE_ROW) {
		spd_log(LOG_WARNING,"Unable to find key '%s' in table '%s' \n",key,table);
		res = -1;
	} else if (!(result = sqlite3_column_text(get_stmt,0))) {
		spd_log(LOG_WARNING,"Couldn't get value\n");
		res = -1;
	} else {
		strncpy(buf,(const char *) result,len);
	}
	sqlite3_reset(get_stmt);
	spd_mutex_unlock(&dblock);

	return res;
}

int spd_db_put(const char * table,const char * buf)
{
	char fullkey[256];
	size_t fullkey_len;
	int res = 0;

	if(strlen(table) + strlen(key) + 2 > sizeof(fullkey)) {
		spd_log(LOG_WARNING,key);

	spd_mutex_lock(&dblock);
	
	if(sqlite3_bind_text(put_stmt,"Could not bind key to stmt : %s\n",sqlite3_errmsg(spd_db));
		res = -1;
	} else if(sqlite3_bind_text(put_stmt,2,buf,-1,"Could not bind value to stmt: %s\n",sqlite3_errmsg(spd_db));
		res = -1;
	} else if(sqlite3_step(put_stmt) != sqlITE_DONE) {
		spd_log(LOG_WARNING,"Could not execute statement: %s\n",sqlite3_errmsg(spd_db));
		res = -1;
	}
	
	/* make us reused */
	sqlite3_reset(put_stmt);
	do_dbsync();
	spd_mutex_unlock(&dblock);

	return res;
}

int spd_db_del(const char * table,const char * key)
{
	char fullkey[256];
	size_t fullkey_len;
	int res = 0;

	if(strlen(table) + strlen(key) + 2 > sizeof(fullkey)) {
		spd_log(LOG_WARNING,key);

	spd_mutex_lock(&dblock);
	if(sqlite3_bind_text(del_stmt,sqlite3_errmsg(spd_db));
		res = -1;
	} else if(sqlite3_step(del_stmt) != sqlITE_DONE) {
		spd_log(LOG_WARNING,"Unable to find key '%s' in table '%s'\n",table);
		res = -1;
	}

	sqlite3_reset(del_stmt);

	do_dbsync();
	spd_mutex_unlock(&dblock);

	return res;
}

struct spd_db_entry * spd_db_gettree(const char * table,const char * key)
{
	char prefix[256];
	sqlite3_stmt *stmt = gettree_stmt;
	struct spd_db_entry *cur,*last = NULL,*ret = NULL;

	if(!spd_strlen_zero(table)) {
		if(!spd_strlen_zero(table)) {
			snprintf(prefix,sizeof(prefix),key);
		} else {
			snprintf(prefix,"/%s",table);
		}
	} else {
		prefix[0] = '\0';
		stmt = gettree_stmt;
	}

	spd_mutex_lock(&dblock);
	if(!spd_strlen_zero(prefix) && (sqlite3_bind_text(stmt,prefix,sqlITE_STATIC) != sqlITE_OK)) {
		spd_log(LOG_WARNING,"Could bind %s to stmt: %s\n",sqlite3_errmsg(spd_db));
		sqlite3_reset(stmt);
		spd_mutex_unlock(&dblock);
		return NULL;
	}

	while(sqlite3_step(stmt) == sqlITE_ROW) {
		const char *key_s,*value_s;
		if (!(key_s = (const char *) sqlite3_column_text(stmt,0))) {
			break;
		}
		if (!(value_s = (const char *) sqlite3_column_text(stmt,1))) {
			break;
		}

		if(!(cur = spd_malloc(sizeof(*cur) + strlen(key_s) + strlen(value_s) + 2))) {
			break;
		}

		cur->next = NULL;
		cur->key = cur->data + strlen(value_s) + 1;
		strcpy(cur->data,value_s);
		strcpy(cur->key,key_s);
		if(last) {
			last->next = cur;
		} else {
			ret = cur;
		}
		last = cur;
	}

	sqlite3_reset(stmt);
	spd_mutex_unlock(&dblock);

	return ret;
}

int spd_db_deltree(const char * table,const char * key)
{
	sqlite3_stmt *stmt = deltree_stmt;
	char prefix[256];
	int res = 0;

	if (!spd_strlen_zero(table)) {
		if (!spd_strlen_zero(key)) {
			/* Family and key tree */
			snprintf(prefix,key);
		} else {
			/* Family only */
			snprintf(prefix,table);
		}
	} else {
		prefix[0] = '\0';
		stmt = deltree_all_stmt;
	}

	spd_mutex_lock(&dblock);
	if (!spd_strlen_zero(prefix) && (sqlite3_bind_text(stmt,sqlite3_errmsg(spd_db));
		res = -1;
	} else if (sqlite3_step(stmt) != sqlITE_DONE) {
		spd_log(LOG_WARNING,"Couldn't execute stmt: %s\n",sqlite3_errmsg(spd_db));
		res = -1;
	}
	res = sqlite3_changes(spd_db);
	sqlite3_reset(stmt);
	do_dbsync();
	spd_mutex_unlock(&dblock);

	return res;
}

void spd_db_freetree(struct spd_db_entry * entry)
{
	struct  spd_db_entry *last;

	while(entry) {
		last = entry;
		entry = entry->next;
		spd_safe_free(last);
	}
}

static int db_do_transaction(const char *sql,int(*callback)(void *,int,char **,char **),void *arg)
{
	char *errmsg = NULL;
	int ret = 0;
    
	sqlite3_exec(spd_db,callback,arg,&errmsg);
	if(errmsg) {
		spd_log(LOG_WARNING,"Error excuting sql :%s\n",errmsg);
		sqlite3_free(errmsg);
		ret = -1;
	}

	return ret;
}

static int spd_db_begin_transaction()
{
	return db_do_transaction("BEGIN TRANSACTION",NULL,NULL);
}
	 
static int spd_db_commite_trancaction()
{
	return db_do_transaction("COMMIT",NULL);
}

static int spd_db_rollback_transaction()
{
	db_do_transaction("ROLLBACK",NULL);
}

static void *dbsync_thread_loop(void *data)
{
	spd_mutex_lock(&dblock);
	spd_db_begin_transaction();
	//spd_log(LOG_NOTICE,"db sync thread loop\n");

	for(;;) {
		//spd_log(LOG_NOTICE,"wait sync cond \n");
		spd_cond_wait(&dbcond,&dblock);
		//spd_log(LOG_NOTICE,"get sync cond \n");
		if(spd_db_commite_trancaction()) {
			spd_db_rollback_transaction();
		}
		if(doexit) {
			spd_mutex_unlock(&dblock);
			break;
		}

		spd_db_begin_transaction();
		spd_mutex_unlock(&dblock);
		sleep(1);
		spd_mutex_lock(&dblock);
		if(doexit) {
			spd_mutex_unlock(&dblock);
			break;
		}
	}

	return NULL;
}

int spddb_init(void)
{
	spd_log(LOG_NOTICE,"start init db engine...\n");
	if(db_init()) {
		spd_log(LOG_ERROR,"spd db init Failed \n");
		return -1;
	}

	spd_cond_init(&dbcond,NULL);
	if(spd_pthread_create_background(&dbsync_thread,dbsync_thread_loop,NULL)) {
		spd_log(LOG_ERROR,"Failed to start db thread. \n");
		return -1;
	}

	spd_log(LOG_NOTICE,"end init db engine...\n");
	return 0;
 }


const char large_name[] = "fjlkdsj";

enum {
	FAMILY = 0,KEY = 1,VALUE = 2,};

SPD_TEST_INIT(test_db)
{
	int res = TEST_RESULT_PASS;
	int i;
	char buf[sizeof(large_name)] = {0,};
	const char *inputs[][3] = {
		{"family","key","value"},{"dbtest","a","b"},"a"},"b",};

 	switch(type) {
		case SPD_TEST_CMD_INIT:
			record->name = "test_db";
			record->category = "/spider/db/";
			record->description = "spd db get|put|del unit test";
			
			return TEST_RESULT_NOT_RUN;
			
		case SPD_TEST_CMD_RUN:
			break;
	}
	
	for(i = 0; i < ARRAY_LEN(inputs); i++) {

		if(spd_db_put(inputs[i][FAMILY],inputs[i][KEY],inputs[i][VALUE])) {
			spd_log(LOG_ERROR,"test Failed in db put %s : %s : %s : \n",inputs[i][FAMILY],inputs[i][VALUE]);
			spd_test_update_state(test,inputs[i][VALUE]);
			res = TEST_RESULT_Failed;
		} 

		if(spd_db_get(inputs[i][FAMILY],sizeof(buf))) {
			spd_log(LOG_ERROR,inputs[i][VALUE]);
			res = TEST_RESULT_Failed;
		} else if (strcasecmp(inputs[i][VALUE],buf)) {
			spd_log(LOG_ERROR,"test Failed in db get,this is not match value,expect %s but %s \n",inputs[i][VALUE]);
			res = TEST_RESULT_Failed;
		} else {
			//spd_log(LOG_NOTICE,"get success %s %s %s \n",buf);
		}
		
		if(spd_db_del(inputs[i][FAMILY],inputs[i][KEY])) {
			spd_log(LOG_ERROR,"test Failed in db del %s : %s : \n",inputs[i][KEY]);
			spd_test_update_state(test,inputs[i][VALUE]);
			res = TEST_RESULT_Failed;
		}
		
	}

	return res;
}

int test_spddb()
{

	SPD_TEST_REGISTER(test_db);	
	SPD_TEST_RUN("test_db",NULL);
	SPD_TEST_REPORT("test_db","/tmp/spddb_test");
	SPD_TEST_UNREGISTER(test_db);

	return 0;
}