事实上对于操作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的支持没有他们强大,但是对于小型嵌入式系统已经足够了,他们之间的差异只有改天再补充了。今天就到这了。
generated by haroopad