sqlite c 语言客户端编程接口抽象

概述csdn lidp  转载注明出处 封装几个sqlite3 数据库接口，通过利用sqlite3提供的api实现数据库的打开，关闭，创建表，添加，删除字段,基于事务实现。 此数据库接口为我在google code上建立的的开源项目spider-tool的一部分， 关于spider-tool，欢迎访问google code.  https://spider-tool.googlecode.com sq

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" {#endifstruct 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] = '';		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] = '';		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;}

总结

以上是内存溢出为你收集整理的sqlite c 语言客户端编程接口抽象全部内容，希望文章能够帮你解决sqlite c 语言客户端编程接口抽象所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。