database.c

简单操作Sqlite3的API封装

#include "database.h"

//把数组的长度设为2048，使sql语句的长度有更大的灵活性
char tmp[2048];  //因为sqlite3的参数为char*型所以我们需要一个数组来存放string型的数据
static void info_db_result(struct sqlite_db_t *db);//打印查询的结果

int sqlite_db_open(struct sqlite_db_t *db,const char *fname)
{
	db->fname = fname;
	
	if(sqlite3_open(fname,&db->db) != SQLITE_OK){		
		printf("db: database open error,'%s'\n",fname);		
		return -1;
	}
	
	printf("db: success open database '%s'\n",fname);	
	return 0;
}

int sqlite_db_close(struct sqlite_db_t *db)
{
	sqlite3_close(db->db);
	
	printf("db: close database.\n");	
	return 0;
}


int sqlite_db_exec(struct sqlite_db_t *db,char *sql)
{
	char *errmsg = NULL;
	
	memset(tmp,0,sizeof(tmp));
	strcpy(tmp,sql);
	
	printf("db: exec '%s'\n",sql);
	
	sqlite3_exec(db->db,tmp,0,0,&errmsg);
	if(errmsg){
		printf("db:%s\n",errmsg);
		sqlite3_free(errmsg);
		return -1;
	}else 
		return 0;
}

int sqlite_db_select(struct sqlite_db_t *db,char *sql)
{
	char *errmsg = NULL;
	
	memset(tmp,0,sizeof(tmp));
	strcpy(tmp,sql);
	
	sqlite3_get_table(db->db,tmp,&db->result,&db->row,&db->col,&errmsg);
	if(errmsg){
		printf("db:%s\n",errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	
	//printf("db: select query result,row=%d,col=%d\n",db->row,db->col);
	//info_db_result(db);
	
	return 0;
}

//取得字段名称
//FIXME:number存放字段个数,fields存放取得的字段名称,之后需要一层一层的释放掉
int sqlite_db_get_fileds(struct sqlite_db_t *db,const char *table,char ***fields,int *number)
{
	int i;
	char sql[100];
	int count = 0;
	sqlite3_stmt *stmt;
	
	sprintf(sql,"select * from %s",table);
	sqlite3_prepare_v2(db->db,sql,-1,&stmt,0);
	
	//执行这个语句
	if(sqlite3_step(stmt) == SQLITE_DONE){
		count = sqlite3_column_count(stmt);		
		if(count <= 0){
			sqlite3_finalize(stmt);
			return -1;	
		}
		
		//printf("count: fields=%d\n",count);
		*fields = (char**)malloc(count*sizeof(char*));
		for(i = 0; i < count; i++){
			char *name;
			
			name = strdup(sqlite3_column_name(stmt,i));
			//printf("column: index=%d,name='%s'\n",i,name);
			
			(*fields)[i] = name;
		}
		
		*number = count;
	}
	
	sqlite3_finalize(stmt);
	
	return 0;
}

//FIXME:取得字段名称,column从0开始计算
const char* sqlite_db_column_name(struct sqlite_db_t *db,const char *table,int column)
{
	char sql[100];
	int count = 0;
	const char* name = NULL;
	sqlite3_stmt *stmt;
	
	sprintf(sql,"select * from %s",table);
	sqlite3_prepare_v2(db->db,sql,-1,&stmt,0);
	sqlite3_step(stmt);//执行这个语句	

	count = sqlite3_column_count(stmt);
	//printf("count:=%d\n",count);
	if(count <= 0 || column >= count){
		sqlite3_finalize(stmt);
		return NULL;
	}
	
	name = sqlite3_column_name(stmt,column);
	//printf("column: index=%d,name='%s'\n",column,name);

	return name;	
}

//返回查询后的结果个数
int sqlite_db_get_rownum(struct sqlite_db_t *db)
{
	return db->row + 1;
}

//结果的列数
int sqlite_db_get_colnum(struct sqlite_db_t *db)
{
	return db->col;
}

//返回查询的结果
char* sqlite_db_get_result(struct sqlite_db_t *db,int row,int col)
{
	//FIXME:空的时候一定要返回NULL，要不然后面访问内容的时候就会异常了
	if(db->row == 0){
		return NULL;
	}
	
	if(row <= db->row+1 && col <= db->col){
		return db->result[row*db->col+col];
	}
	
	return NULL;	
}

//打印查询的结果
void info_db_result(struct sqlite_db_t *db)
{
	int i,j;
	
	//FIXME:第0行是这个表的头
	printf("db: select query result,rows=%d,cols=%d\n",db->row+1,db->col);
	
	for(i = 0; i < db->row+1; i++){
		printf("%d:",i);//行索引
		
		for(j = 0; j < db->col; j++){
			printf("\t%s",db->result[i*db->col+j]);
		}
		
		printf("\n");
	}
}