sqlite嵌入式数据库C语言基本操作(2)

Reading time ~5 minutes

事实上对于操作sqlite的其他语言，写一个统一的数据库操作模型是非常容易的，比如java，c#，这些语言支持垃圾回收，支持异常捕获，支持泛型，写起来就很容易。但是对于C语言，就得另当别论了，就拿查询操作来说，c语言没有泛型，不能返回统一的泛型列表，只能返回数据模型的链表结构。

但是得益于前面讲过的通用链表结构,我们可以尽可能的像其他语言一样封装一个通用的数据库操作模型。

回顾前面讲到的sqlite操作基本流程，查询流程是 1.打开数据库 2.准备好SQL语句。 3.绑定SQL语句参数 4.执行SQL语句 5.返回结果集，执行串链操作。 6.释放资源 7.关闭数据库句柄. 整体的流程不变，把不一样的部分抽象出来 —— 绑定参数，执行串链的过程抽象出来。

绑定参数，如


int bind_userinfo_t(sqlite3_stmt * stmt,int index, void * arg )

执行查询建链过程，如


int create_userinfo_T(sqlite3_stmt,void * arg)

然后在通用的数据库操作函数中传入函数指针，尽量的精简代码。 下面是的dbhelper.h 和 dbhelper.c 抽象的一般数据库操作函数。


#ifndef _DBHELPER_H_
#define _DBHELPER_H_
#include <sqlite3.h> 

#define DB_SQL_MAX_LEN 1024
//执行没有返回的SQL语句
int db_nonquery_operator(const char *sqlstr,int (*bind)(sqlite3_stmt *,int index,void * arg),void *param);
//执行没有返回的SQL语句的多值传参
int db_nonquery_by_varpara(const char *sql,const char *fmt,...);
//执行没有返回的SQL语句
int db_nonquery_transaction(int (*exec_sqls)(sqlite3 *db,void * arg),void *arg);
//执行多值传参的查询语句
int db_query_by_varpara(const char *sql,int (*create)(sqlite3_stmt *stmt,void *arg),void *arg,const char *fmt,...);
//执行查询并返回查询结果集的条数
int db_query_count_result(const char *sql);
//对sqlite3_column_blob的二次封装
int db_stmt_get_blob(sqlite3_stmt *stmt,int index,unsigned char *out);
//对sqlite3_column_text的二次封装
int db_stmt_get_text(sqlite3_stmt *stmt,int index,char *out);
//对sqlite3_column_int的二次封装
int db_stmt_get_int(sqlite3_stmt *stmt,int index);
//对sqlite3_column_double的二次封装
double db_stmt_get_double(sqlite3_stmt *stmt,int index);

#endif

dbhelper.c:


#include <stdio.h>
#include <string.h>
#include <pthread.h>
#include "na_queue.h"
#include "dbhelper.h"

#define DB_NAME "test.db"

static pthread_mutex_t db_mutex_lock = PTHREAD_MUTEX_INITIALIZER;


static int db_bind_by_var(sqlite3_stmt *stmt,const char *fmt,va_list args)
{
	int len,npara=1;
	int ret = SQLITE_OK;
	if(fmt == NULL){
		return ret;
	}

   	for(;*fmt;++fmt){
		if(*fmt != '%')
			continue;
		++fmt;
		//get length
		len = 0;
		while(isdigit(*fmt)){
			len = len * 10 + (*fmt - '0');
			++fmt;
		}
		switch(*fmt){
		case 'd':
			ret = sqlite3_bind_int(stmt,npara,va_arg(args,int));
			break;
		case 's':
			{
			char *str = va_arg(args,char *);
			ret = sqlite3_bind_text(stmt,npara,str,strlen(str),NULL);
			}
			break;
		case 'x':
			{
			unsigned char *pdata;
			pdata = va_arg(args,char *);
			ret = sqlite3_bind_blob(stmt,npara,pdata,len,NULL);
		//	printf_phex("blob",pdata,len);
			}
			break;
		default:
			ret = SQLITE_ERROR;
			break;
		}
		++npara;
		if(ret)
			return ret;
	}	
	return ret;
}

/* 
 * ===  FUNCTION  ======================================================================
 *         Name:  db_query_by_varpara
 *  Description:  数据库查询操作，可以带变参绑定
 *  @sql       :  sql 
 *  @create    :  取得数据并创建节点
 *  @arg       :  用户用于create的参数
 *  @fmt       :  格式字符串，%s string,%d int,%nx 长度为N的二进制串
 *  ...        :  变参
 *  Return     :  查询到数据的条数 
 * =====================================================================================
 */
int db_query_by_varpara(const char *sql,int (*create)(sqlite3_stmt *stmt,void *arg),void *arg,const char *fmt,...)
{
	sqlite3 *db = NULL;
	sqlite3_stmt *stmt = NULL;
	if(sql == NULL){
//		return SQLITE_ERROR;
		return 0;
	}
	pthread_mutex_lock(&db_mutex_lock);
	int rc = sqlite3_open(DB_NAME,&db);
	if(rc != SQLITE_OK){
		printf("open database failed,rc=%d",rc);
		pthread_mutex_unlock(&db_mutex_lock);
		return 0;
	}

	rc = sqlite3_prepare(db,sql,-1,&stmt,NULL);
	if(rc != SQLITE_OK){
		printf("database prepare fail,rc=%d",rc);
		goto DB_EXEC_FAIL;
	}

	if(fmt){
		va_list args;
		va_start(args,fmt);
		rc = db_bind_by_var(stmt,fmt,args);
		va_end(args);
		if(rc){
			printf("database bind fail,rc=%d",rc);
			goto DB_EXEC_FAIL;
		}
	}

	if(create){
		rc = (*create)(stmt,arg);
	}else{
		rc = (sqlite3_step(stmt),0);
	}
	sqlite3_finalize(stmt);
	goto DB_EXEC_OK;
DB_EXEC_FAIL:
	printf("db operator failed,rc=%d",rc);
	rc = 0;
DB_EXEC_OK:
	sqlite3_close(db);
	pthread_mutex_unlock(&db_mutex_lock);
	return rc;
}




/* 
 * ===  FUNCTION  ======================================================================
 *         Name:  db_exec_noquery
 *  Description:  执行非查询操作 
 *  @sqlstr    :  sql,多条语句由';'分开
 *  @bind      :  绑定sql中的未知变量操作 
 *  @param     :  绑定中的参数
 *  @bind.index:  sql语句序号 
 *  Return     :  0 or error
 * =====================================================================================
 */
int db_nonquery_operator(const char *sqlstr,int (*bind)(sqlite3_stmt *,int index,void * arg),void *param)
{
	sqlite3 *db = NULL;
	sqlite3_stmt *stmt = NULL;
//	char *emsg = NULL;
	if(sqlstr == NULL){
		return SQLITE_ERROR;
	}
	pthread_mutex_lock(&db_mutex_lock);
	int rc = sqlite3_open(DB_NAME,&db);
	if(rc != SQLITE_OK){
		printf("open database failed,rc=%d",rc);
		pthread_mutex_unlock(&db_mutex_lock);
		return rc;
	}
	rc = sqlite3_exec(db,"begin transaction",0,0,NULL);
	if(rc != SQLITE_OK){
		printf("begin transaction:ret=%d",rc);
		goto DB_BEGIN_FAIL;
	}
	char sql[DB_SQL_MAX_LEN];
	int index = 0,offset=0,n=0;
	while(sqlstr[index] != 0){
		offset = 0;
		do{
			if(offset >= DB_SQL_MAX_LEN){
				printf("sql is too long,(%d)",offset);
				rc = SQLITE_ERROR;
				goto DB_EXEC_FAIL;
			}
			if((sqlstr[index] != ';') && (sqlstr[index] != 0)){
				sql[offset++] = sqlstr[index++];
			}else{
				sql[offset] = '\0';
				if(sqlstr[index] == ';') index++;
				n++;
				break;
			}
		}while(1);
		printf("sql:%s",sql);
		rc = sqlite3_prepare(db,sql,-1,&stmt,NULL);
		if(rc != SQLITE_OK){
			printf("prepare,error,%d",rc);
			goto DB_EXEC_FAIL;
		}
		if(bind){
			rc = (*bind)(stmt,n,param);
		}else{
			rc = sqlite3_step(stmt);
		}
		sqlite3_finalize(stmt);
		if((rc != SQLITE_OK) && (rc != SQLITE_DONE)){
			printf("bind");
			goto DB_EXEC_FAIL;
		}
	}
	rc = sqlite3_exec(db,"commit transaction",0,0,NULL);
	if(rc){
		printf("commit transaction:%d",rc);
		goto DB_EXEC_FAIL;
	}
	goto DB_EXEC_OK;
DB_EXEC_FAIL:
	if(sqlite3_exec(db,"rollback transaction",0,0,NULL)){
		printf("rollback transaction error");
	}
DB_BEGIN_FAIL:
//	sqlite3_free(emsg);
	printf("db operator failed,rc=%d",rc);
DB_EXEC_OK:
	sqlite3_close(db);
	pthread_mutex_unlock(&db_mutex_lock);
	return rc;
}

int db_nonquery_by_varpara(const char *sql,const char *fmt,...)
{
	sqlite3 *db = NULL;
	sqlite3_stmt *stmt = NULL;
	if(sql == NULL){
		return SQLITE_ERROR;
	}
	pthread_mutex_lock(&db_mutex_lock);
	int rc = sqlite3_open(DB_NAME,&db);
	if(rc != SQLITE_OK){
		printf("open database failed,rc=%d",rc);
		pthread_mutex_unlock(&db_mutex_lock);
		return rc;
	}
	printf("sql:%s",sql);
	rc = sqlite3_prepare(db,sql,-1,&stmt,NULL);
	if(rc != SQLITE_OK){
		printf("prepare,");
		goto DB_EXEC_FAIL;
	}
	if(fmt){
		va_list args;
		va_start(args,fmt);
		rc = db_bind_by_var(stmt,fmt,args);
		va_end(args);
		if(rc){
			goto DB_EXEC_FAIL;
		}
	}
	rc = sqlite3_step(stmt);
	sqlite3_finalize(stmt);
	if((rc != SQLITE_OK) && (rc != SQLITE_DONE)){
		printf("bind");
		goto DB_EXEC_FAIL;
	}
	rc = 0;
	goto DB_EXEC_OK;
DB_EXEC_FAIL:
DB_BEGIN_FAIL:
	printf("db operator failed,rc=%d",rc);
DB_EXEC_OK:
	sqlite3_close(db);
	pthread_mutex_unlock(&db_mutex_lock);
	return rc;
}


int db_nonquery_transaction(int (*exec_sqls)(sqlite3 *db,void * arg),void *arg)
{
	sqlite3 *db = NULL;
	sqlite3_stmt *stmt = NULL;
//	char *emsg = NULL;
	pthread_mutex_lock(&db_mutex_lock);
	int rc = sqlite3_open(DB_NAME,&db);
	if(rc != SQLITE_OK){
		printf("open database failed,rc=%d",rc);
		pthread_mutex_unlock(&db_mutex_lock);
		return rc;
	}
	rc = sqlite3_exec(db,"begin transaction",0,0,NULL);
	if(rc != SQLITE_OK){
		printf("begin transaction:%d",rc);
		goto DB_BEGIN_FAIL;
	}
	
	if(exec_sqls){
		rc = (*exec_sqls)(db,arg);
	}else{
		rc = SQLITE_ERROR;
	}
	if((rc != SQLITE_OK) && (rc != SQLITE_DONE)){
		printf("prepare,error,%d",rc);
		goto DB_EXEC_FAIL;
	}
	
	rc = sqlite3_exec(db,"commit transaction",0,0,NULL);
	if(rc){
		printf("commit transaction:%d",rc);
		goto DB_EXEC_FAIL;
	}
	goto DB_EXEC_OK;
DB_EXEC_FAIL:
	if(sqlite3_exec(db,"rollback transaction",0,0,NULL)){
		printf("rollback transaction:error");
	}
DB_BEGIN_FAIL:
//	sqlite3_free(emsg);
	printf("db operator failed,rc=%d",rc);
DB_EXEC_OK:
	sqlite3_close(db);
	pthread_mutex_unlock(&db_mutex_lock);
	return rc;
}



static int db_get_count(sqlite3_stmt *stmt,void *arg)
{
	int ret,*count=arg;
	ret = sqlite3_step(stmt);
	if(ret != SQLITE_ROW)
		return SQLITE_EMPTY;
	*count = db_stmt_get_int(stmt,0);
	return SQLITE_OK;
}


/* 
 * ===  FUNCTION  ======================================================================
 *         Name:  db_query_count_result
 *  Description:  查询计数结果的第一列第一行，其它忽略
 *  @sql       :  查询计数的SQL语句
 *  Return     :  查询到计数返回计数，否则一律返回0 
 * =====================================================================================
 */
int db_query_count_result(const char *sql)
{
	int ret,count=0;
	ret = db_query_by_varpara(sql,db_get_count,&count,NULL);
	if(ret == SQLITE_OK)
		return count;
	return 0;
}


int db_stmt_get_blob(sqlite3_stmt *stmt,int index,unsigned char *out)
{
	const char *pdata = sqlite3_column_blob(stmt,index);
	int len = sqlite3_column_bytes(stmt,index);
	if(pdata){
		memcpy(out,pdata,len);
		return len;
	}
	return 0;
}

int db_stmt_get_text(sqlite3_stmt *stmt,int index,char *out)
{
	const unsigned char *pdata = sqlite3_column_text(stmt,index);
	if(pdata){
		int len = strlen(pdata);
		strncpy(out,pdata,len);
		return len;
	}
	return 0;
}

int db_stmt_get_int(sqlite3_stmt *stmt,int index)
{
	return sqlite3_column_int(stmt,index);
}

double db_stmt_get_double(sqlite3_stmt *stmt,int index)
{
	return sqlite3_column_double(stmt,index);
}

对以上的代码做一下说明: test.db为数据库文件 为了处理多线程，在对数据库进行操作的时候加了锁。

现在按照通用数据库操作方式重写上次的代码，查询和增加操作。

首先重写数据模型：


typedef struct userinfo_s{
	int userid;
    char username[32];
    na_queue_t queue;
}userinfo_t;

释放函数链表函数：


 void free_userinfo_t(na_queue_t *h){
    na_queue_t *head = h,*pos,*n;
    userinfo_t *p = NULL;
    na_queue_for_each_safe(pos,n,head){
        p = na_queue_data(pos,userinfo_t,queue);
        free(p);
    }
    na_queue_init(head);
 }

绑定函数，用于绑定insert的插入函数


int bind_userinfo_t(sqlite3_stmt * stmt,int index,void * arg){
        int rc;
        userinfo_t * param = arg;
        sqlite3_bind_int(stmt,1,param->userid);
        sqlite3_bind_text(stmt,2,param->username,strlen(param->username),NULL);
        rc = sqlite3_step(stmt);
        if (rc != SQLITE_DONE)
                return rc;
        return SQLITE_OK;
}

查询操作需要的串链函数：


int create_userinfo_t(sqlite3_stmt * stmt,void * arg){
        na_queue_t * h = arg;
        na_queue_init(h);
        userinfo_t * u;
        int ret,count = 0;
        ret = sqlite3_step(stmt);
        if(ret != SQLITE_ROW){
                return 0;
        }
        do
        {
                u = calloc(sizeof(userinfo_t),1);
                if(!u){
                        goto CREATE_USERINFO_FAIL;
                }
                u->userid = db_stmt_get_int(stmt,0);
                db_stmt_get_text(stmt,1,u->username);
                na_queue_insert_tail(h,&(u->queue));
                count ++;
        } while ((ret = sqlite3_step(stmt)) == SQLITE_ROW);
        return count;
CREATE_USERINFO_FAIL:
        free_userinfo_t(h);
        return 0;
}

基本的对数据表的操作就是按照这种方式来抽象代码,现在我们来按照这种方式来写一段测试代码:


//打印用户信息
void printusers(na_queue_t *h){
        userinfo_t * q=NULL;
        na_queue_foreach(q,h,userinfo_t,queue){
                printf("userid:%d username:%s\n",q->userid,q->username);
        }
}

//得到所以用户信息
int get_all_userinfo(na_queue_t * h){
        return db_query_by_varpara("select * from userinfo;",create_userinfo_t,h,NULL);
}

//添加一个用户信息
int add_a_userinfo(userinfo_t * u){
        return db_nonquery_operator("insert into userinfo(userid,username) values (?,?)",bind_userinfo_t,u);
}

int main(int argc, char *argv[])
{
        printf("test get all userinfo\n");
        na_queue_t h;
        int ret = get_all_userinfo(&h);
        printusers(&h);
        free_userinfo_t(&h);
        printf("test add userinfo");
        userinfo_t newuser;
        newuser.userid = 7;
        strncpy(newuser.username,"White",10);
        add_a_userinfo(&newuser);
        get_all_userinfo(&h);
        printusers(&h);
        free_userinfo_t(&h);
        return 0;
}

我们还是上一次的数据库来接着运行，输出：

test get all userinfo userid:1 username:Alex userid:2 username:Neo userid:3 username:Allan userid:4 username:coby userid:5 username:micheal test add userinfo userid:1 username:Alex userid:2 username:Neo userid:3 username:Allan userid:4 username:coby userid:5 username:micheal userid:7 username:White

以上就是一个简单的C语言对SQLITE数据库操作的封装，事实上我们已经把它用在了我们的生产环境，不过生产环境版本相对于这个版本要复杂一点，加入的日志模块，线程模块，而且现在还在继续进化中，但是原理就是这样了，实现上大同小异而已。实例也同样只是写了查询和插入，至于其他操作就有读者自己在这个实例上去添加了。SQLITE小型数据库相对与其他Oracle等其他大型数据库而言，他对SQL的支持没有他们强大，但是对于小型嵌入式系统已经足够了，他们之间的差异只有改天再补充了。今天就到这了。

Read More

排序(2)冒泡排序

# 排序(2)### 冒泡排序##冒泡排序实际上是一个非常简单的排序算法，非常容易实现----遍历文件，如果近邻两个元素大小顺序不对，就将两者交换，重复这样的操作直到整个文件排好序。如下用TypeScript做了一个简单的演示:[代码地址](https://github.c...… Continue reading

排序(1)

Published on May 01, 2016

sqlite嵌入式数据库C语言基本操作(1)

Published on March 02, 2016