本模块采用面向对象的编程思想，将sqlite3的繁琐语法转换成类似printf的读写方式，使操作更简单方便。
使用时注意key值的唯一性。
lib_sqlite3.c
/*************************************************************************
> File Name: lib_sqlite3.c
> Author: Femx
> Mail: 617490935@qq.com
> Created Time: Thu 26 May 2016 10:56:44 PM CST
************************************************************************/
#include<stdio.h>
#include<stdarg.h>
#include<unistd.h>
#include<stdlib.h>
#include<string.h>
#include"sqlite3.h"
#include"lib_sqlite3.h"
/*
int (*open_table)(Sts_p sqlite_table);
int (*insert_record)(Sts_p sqlite_table, int key, const char* format, ... );
int (*read_record)(Sts_p sqlite_table, int row, int *key, const char* format, ... );
int (*delete_record)(Sts_p sqlite_table, int row);
int (*modify_record)(Sts_p sqlite_table, int row,int key, const char* format, ... );
int (*read_record_key)(Sts_p sqlite_table, int key, const char* format, ... );
int (*delete_record_key)(Sts_p sqlite_table, int key);
int (*modify_record_key)(Sts_p sqlite_table, int key, const char* format, ... );
int (*transform_key_and_row)(Sts_p sqlite_table, int input, int is_key);
int (*get_table_size)(Sts_p sqlite_table);
*/
int lib_sqlite3_open_table(Sts_p sqlite_table)
{
sqlite3 * sqlite_p = NULL;
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"table init error!!");
return SQ3_RF;
}
#if 1
if(sqlite_table->sqlite_p != NULL)
{
//SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
// "sqlite_p init exist!!");
return SQ3_ROK;
}
#endif
SQ3_LOG_PRINT(EVENT, __FUNCTION__, __LINE__,
"open table[%s]", sqlite_table->file_name);
if(sqlite3_open(sqlite_table->file_name, &sqlite_p) == SQ3_RF)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite3_open error!! ");
return SQ3_RF;
}
sqlite_table->sqlite_p = sqlite_p;
return SQ3_ROK;
}
int lib_sqlite3_insert_record(Sts_p sqlite_table, int key, const char* format, ... )
{
char *errmsg;
char insert_buf[SQLITE_DATA_STR_LEN];
char buf[SQLITE_DATA_STR_LEN];
va_list ap;
int d;
int index = 0;
int i ;
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
for(i = 0; i < sqlite_table->size; i++)
{
memset(buf, 0, SQLITE_DATA_STR_LEN);
sqlite_table->read_record(sqlite_table, i, &index, "%s", buf);
if(index == key)
{
#if 0
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"key is exist!!");
#endif
return SQ3_RF;
}
}
memset(insert_buf, 0, SQLITE_DATA_STR_LEN);
memset(buf, 0, SQLITE_DATA_STR_LEN);
va_start(ap, format);
d = vsprintf(buf, format, ap);
va_end(ap);
sprintf(insert_buf,
"insert into %s values ('%d', '%s')",
sqlite_table->table_name, key, buf);
if (sqlite3_exec(sqlite_table->sqlite_p, insert_buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite3_exec error!! error: %s ", errmsg);
return SQ3_RF;
}
sqlite_table->index++;
sqlite_table->size++;
return SQ3_ROK;
}
int lib_sqlite3_read_record(Sts_p sqlite_table, int row, int *key, const char* format, ... )
{
char **db_result;
int nrow, ncolumn;
int i, j, index;
char *errmsg;
char databuf[10];
va_list ap;
int retval;
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
if(sqlite_table->size < row)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p size error!");
return SQ3_RF;
}
//sqlite_table->open_table(sqlite_table);
if (sqlite3_get_table(sqlite_table->sqlite_p, sqlite_table->cmd_open,
&db_result, &nrow,&ncolumn, &errmsg) != SQLITE_OK)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite3_get_table error!! errmsg: %s ", errmsg);
//sqlite3_free_table(db_result);
return SQ3_RF;
}
else
{
index = ncolumn;
index = index + SQLITE3_TABLE_COL_NUM * row;
if (!!nrow)
{
*key = atoi(db_result[index++]);
va_start(ap, format);
retval = vsscanf(db_result[index++], format, ap);
va_end(ap);
}
}
sqlite3_free_table(db_result);
return nrow;
}
int lib_sqlite3_delete_record(Sts_p sqlite_table, int row)
{
int key = 0;
char argv[SQLITE_DATA_STR_LEN];
char delete_cmd[SQLITE_TAB_STR_LEN];
char *errmsg;
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
memset(argv, 0, SQLITE_DATA_STR_LEN);
memset(delete_cmd, 0, SQLITE_TAB_STR_LEN);
sqlite_table->read_record(sqlite_table, row, &key, "%s", argv);
sprintf(delete_cmd, "delete from %s where Id = %d;",
sqlite_table->table_name, key);
if (sqlite3_exec(sqlite_table->sqlite_p, delete_cmd, NULL, NULL,
&errmsg) != SQLITE_OK)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite3_exec error: %s!!", errmsg);
return SQ3_RF;
}
sqlite_table->index--;
sqlite_table->size--;
return SQ3_ROK;
}
int lib_sqlite3_modify_record(Sts_p sqlite_table, int row,int key, const char* format, ... )
{
char argv[SQLITE_DATA_STR_LEN];
char buf[SQLITE_DATA_STR_LEN];
va_list ap;
int d;
int old_key = 0;
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
memset(argv, 0, SQLITE_DATA_STR_LEN);
memset(buf, 0, SQLITE_DATA_STR_LEN);
va_start(ap, format);
d = vsprintf(buf, format, ap);
va_end(ap);
sqlite_table->read_record(sqlite_table, row, &old_key, "%s", argv);
sqlite_table->delete_record(sqlite_table, row);
sqlite_table->insert_record(sqlite_table, key, "%s" , buf);
return SQ3_ROK;
}
int lib_sqlite3_read_record_key(Sts_p sqlite_table, int key, const char* format, ... )
{
int index, i;
char get_buff[SQLITE_DATA_STR_LEN];
va_list ap;
int retval;
int row = 0;
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
for(i = 0; i < sqlite_table->size; i++)
{
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
row = sqlite_table->read_record(sqlite_table, i, &index, "%s", get_buff);
if(index != 0 && index == key)
{
va_start(ap, format);
retval = vsscanf(get_buff, format, ap);
va_end(ap);
return row;
}
}
return row;
}
int lib_sqlite3_delete_record_key(Sts_p sqlite_table, int key)
{
int index, i;
char get_buff[SQLITE_DATA_STR_LEN];
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
for(i = 0; i < sqlite_table->size; i++)
{
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
sqlite_table->read_record(sqlite_table, i, &index, "%s", get_buff);
if(index == key)
{
sqlite_table->delete_record(sqlite_table, i);
}
}
return SQ3_ROK;
}
int lib_sqlite3_modify_record_key(Sts_p sqlite_table, int key, const char* format, ... )
{
int index, i;
char get_buff[SQLITE_DATA_STR_LEN];
char argv[SQLITE_DATA_STR_LEN];
char buf[SQLITE_DATA_STR_LEN];
va_list ap;
int d;
int old_key = 0;
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
for(i = 0; i < sqlite_table->size; i++)
{
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
sqlite_table->read_record(sqlite_table, i, &index, "%s", get_buff);
if(index == key)
{
memset(argv, 0, SQLITE_DATA_STR_LEN);
memset(buf, 0, SQLITE_DATA_STR_LEN);
va_start(ap, format);
d = vsprintf(buf, format, ap);
va_end(ap);
sqlite_table->read_record(sqlite_table, i, &old_key, "%s", argv);
sqlite_table->delete_record(sqlite_table, i);
sqlite_table->insert_record(sqlite_table, key, "%s", buf);
}
}
return SQ3_ROK;
}
int lib_sqlite3_transform_key_and_row(Sts_p sqlite_table, int input, int is_key)
{
int index, i;
char get_buff[SQLITE_DATA_STR_LEN];
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
if(sqlite_table->sqlite_p == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_p is null!!");
return SQ3_RF;
}
if(!!is_key)
{
for(i = 0; i < sqlite_table->size; i++)
{
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
sqlite_table->read_record(sqlite_table, i, &index, "%s", get_buff);
if(index == input)
{
return i;
}
}
}
else
{
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
sqlite_table->read_record(sqlite_table, i, &index, "%s", get_buff);
return index;
}
return SQ3_RF;
}
int lib_sqlite3_get_table_size(Sts_p sqlite_table)
{
if(sqlite_table == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"sqlite_table is null!!");
return SQ3_RF;
}
return sqlite_table->size;
}
int lib_sqlite3_init(Sts_p* sqlite_table_p, char* file_name, char* tabele_name)
{
char get_buff[SQLITE_DATA_STR_LEN];
int ret = 0;
char *errmsg;
int index = 0;
if(*sqlite_table_p != NULL ||
file_name == NULL ||
tabele_name == NULL)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"table init error!! file_name[%s] tabele_name[%s]",
file_name, tabele_name);
return SQ3_RF;
}
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
*sqlite_table_p = (Sts_p)malloc(sizeof(Sts));
memset(*sqlite_table_p, 0, sizeof(Sts));
sprintf((*sqlite_table_p)->file_name, "%s", file_name);
sprintf((*sqlite_table_p)->table_name, "%s", tabele_name);
sprintf((*sqlite_table_p)->cmd_creat, "create table %s %s", tabele_name, SQLITE_TAB_HEAD);
sprintf((*sqlite_table_p)->cmd_open, "select *from %s", tabele_name);
#if 0
SQ3_LOG_PRINT(EVENT, __FUNCTION__, __LINE__,
"creat tables: file_name[%s] table_name[%s] cmd_creat[%s] cmd_open[%s]",
(*sqlite_table_p)->file_name, (*sqlite_table_p)->table_name,
(*sqlite_table_p)->cmd_creat, (*sqlite_table_p)->cmd_open);
#endif
(*sqlite_table_p)->open_table = lib_sqlite3_open_table;
(*sqlite_table_p)->insert_record = lib_sqlite3_insert_record;
(*sqlite_table_p)->delete_record = lib_sqlite3_delete_record;
(*sqlite_table_p)->read_record = lib_sqlite3_read_record;
(*sqlite_table_p)->modify_record = lib_sqlite3_modify_record;
(*sqlite_table_p)->delete_record_key = lib_sqlite3_delete_record_key;
(*sqlite_table_p)->read_record_key = lib_sqlite3_read_record_key;
(*sqlite_table_p)->modify_record_key = lib_sqlite3_modify_record_key;
(*sqlite_table_p)->transform_key_and_row = lib_sqlite3_transform_key_and_row;
(*sqlite_table_p)->get_table_size = lib_sqlite3_get_table_size;
(*sqlite_table_p)->open_table(*sqlite_table_p);
ret = (*sqlite_table_p)->read_record(*sqlite_table_p, 0, &index, "%s", get_buff);
if(ret == SQ3_RF)
{
if (sqlite3_exec((*sqlite_table_p)->sqlite_p, (*sqlite_table_p)->cmd_creat,
NULL, NULL, &errmsg) != SQLITE_OK)
{
SQ3_LOG_PRINT(ERROR, __FUNCTION__, __LINE__,
"creat tables error: %s", errmsg);
return SQ3_RF;
}
(*sqlite_table_p)->index = 0;
ret = (*sqlite_table_p)->open_table(*sqlite_table_p);
}
else
{
(*sqlite_table_p)->size = ret;
int i;
for(i = 0; i < (*sqlite_table_p)->size - 1; i++)
{
memset(get_buff, 0, SQLITE_DATA_STR_LEN);
index = 0;
(*sqlite_table_p)->read_record(*sqlite_table_p, 0, &index, "%s", get_buff);
if(index > (*sqlite_table_p)->index)
(*sqlite_table_p)->index = index;
}
}
return SQ3_ROK;
}
#ifndef OUT_CONFIG
int msg_index;
int __lsqlite3_log_print(int info ,const char * fun, int line, const char* format, ... )
{
va_list ap;
int d;
char buff[LOG_BUFF_LEN];
//int num = 0;
char state[6];
time_t timep;
time(&timep);
va_start(ap, format);
d = vsprintf(buff, format, ap);
va_end(ap);
/*
#define DEBUG 4
#define EVENT 3
#define WARIN 2
#define ERROR 1
*/
memset(state, 0, 6);
switch(info)
switch(info)
{
case 1:{memcpy(state, "ERROR", 5);break;}
case 2:{memcpy(state, "WARIN", 5);break;}
case 3:{memcpy(state, "EVENT", 5);break;}
case 4:{memcpy(state, "DEBUG", 5);break;}
}
printf("\n\
INDEX:[%5d] TIME:%s\
STATE:[%s] LINE:[%5d] FUN:%s \n\
INFOR:%s\n\n",
msg_index++,
asctime(gmtime(&timep)),
state, line, fun,buff);
return 0;
}
#endiflib_sqlite3.h
/*************************************************************************
> File Name: lib_sqlite3.h
> Author: Femx
> Mail: 617490935@qq.com
> Created Time: Thu 26 May 2016 10:56:44 PM CST
************************************************************************/
#ifndef _LIB_SQLIE3_H_
#define _LIB_SQLIE3_H_
#include<stdlib.h>
#include<unistd.h>
#include"sqlite3.h"
typedef struct sqlite3_tables_struct Sts, *Sts_p;
#define SQLITE_TAB_STR_LEN 100
#define SQLITE_DATA_STR_LEN 1024
#define SQLITE3_TABLE_COL_NUM 2
#define SQLITE_TAB_HEAD "(id text, argv text)"
struct sqlite3_tables_struct
{
int index;
int size;
// int col_num;
sqlite3 * sqlite_p;
char file_name[SQLITE_TAB_STR_LEN];
char table_name[SQLITE_TAB_STR_LEN];
char cmd_open[SQLITE_TAB_STR_LEN];
char cmd_creat[SQLITE_TAB_STR_LEN];
int (*open_table)(Sts_p sqlite_table);
int (*insert_record)(Sts_p sqlite_table, int key, const char* format, ... );
int (*read_record)(Sts_p sqlite_table, int row, int *key, const char* format, ... );
int (*delete_record)(Sts_p sqlite_table, int row);
int (*modify_record)(Sts_p sqlite_table, int row,int key, const char* format, ... );
int (*read_record_key)(Sts_p sqlite_table, int key, const char* format, ... );
int (*delete_record_key)(Sts_p sqlite_table, int key);
int (*modify_record_key)(Sts_p sqlite_table, int key, const char* format, ... );
int (*transform_key_and_row)(Sts_p sqlite_table, int input, int is_key);
int (*get_table_size)(Sts_p sqlite_table);
};
#ifdef OUT_CONFIG
//define by user
#define SQ3_ROK
#define SQ3_RF
#define SQ3_LOG_PRINT()
#else
#define SQ3_ROK 0
#define SQ3_RF -1
#define DEBUG 4
#define EVENT 3
#define WARIN 2
#define ERROR 1
#define LOG_BUFF_LEN 1204
extern int __lsqlite3_log_print(int info ,const char * fun, int line, const char* format, ... );
#define SQ3_LOG_PRINT(info ,fun, line, format, ... ) __lsqlite3_log_print(info ,fun, line, format, ##__VA_ARGS__)
#endif
extern int lib_sqlite3_init(Sts_p* sqlite_table_p, char* file_name, char* tabele_name);
#endif
sqlite3_test.c
/*************************************************************************
> File Name: sqlite3_test.c
> Author: Femx
> Mail: 617490935@qq.com
> Created Time: Thu 26 May 2016 10:57:26 PM CST
************************************************************************/
//gcc lib_sqlite3.c sqlite3_test.c -o sqlite3_test -lsqlite3
#include<stdio.h>
#include"lib_sqlite3.h"
int main(int argc, char **argv)
{
Sts_p sqlite_table = NULL;
int key;
char read_name[10] = "\0";
int age = 0;
int w = 0;
lib_sqlite3_init(&sqlite_table, "./sq_table.db", "test_table");
//sqlite_table->open_table(sqlite_table);
sqlite_table->insert_record(sqlite_table,
1, "%s %d %d", "Femx", 189, 72);
sqlite_table->insert_record(sqlite_table,
2, "%s %d %d", "yo", 164, 48);
sqlite_table->insert_record(sqlite_table,
3, "%s %d %d", "yun", 164, 48);
sqlite_table->insert_record(sqlite_table,
4, "%s %d %d", "yun", 164, 48);
sqlite_table->insert_record(sqlite_table,
5, "%s %d %d", "yun", 164, 48);
sqlite_table->insert_record(sqlite_table,
6, "%s %d %d", "yun", 164, 48);
sqlite_table->insert_record(sqlite_table,
7, "%s %d %d", "yun", 164, 48);
sqlite_table->read_record(sqlite_table,
1,&key, "%s %d %d", read_name, &age, &w);
sqlite_table->read_record(sqlite_table,
2, &key,"%s %d %d", read_name, &age, &w);
sqlite_table->read_record_key(sqlite_table,
2, "%s %d %d", read_name, &age, &w);
sqlite_table->read_record_key(sqlite_table,
3, "%s %d %d", read_name, &age, &w);
sqlite_table->delete_record(sqlite_table, 0);
sqlite_table->delete_record_key(sqlite_table, 3);
sqlite_table->insert_record(sqlite_table,
8, "%s %d %d", "yun1", 164, 48);
sqlite_table->modify_record(sqlite_table,
4, 12, "%s %d %d", "yun12", 164, 48);
sqlite_table->modify_record_key(sqlite_table,
6, "%s %d %d", "yun12", 164, 48);
int index;
for(index = 0;
index < sqlite_table->get_table_size(sqlite_table); index++)
{
sqlite_table->read_record(sqlite_table,
index,&key, "%s %d %d", read_name, &age, &w);
printf("\nkey:%d read_name:%s age:%d w:%d\n",
key, read_name, age, w);
}
return 0;
}