sqlite3函数的封装

该文件包含了基本的sqlite3的增删改查，由于sqlite3在保存数据时是不区分数据类型的所以所有的接口都是基于字符串传参的。
下面的代码有需要的可以直接拿过去用，如有发现不足的可提出宝贵的意见。

sqlite3官网：https://www.sqlite.org/index.html

sqlite3命令行的基本用法：
sqlite3 table -cmd “select *from table；” “.exit”
.table:查看当前数据库的表
.database：选择数据库（应该是，不太记得了）
.help:命令帮助

注意：在sliqte3中的外键功能没有启用如果想要使用则使用命令PRAGMA FOREIGN_KEYS=ON；启用，PRAGMA 特殊sqlite3命令

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include <stdint.h>
#include <sqlite3.h>

//由于sqlite3中保存的数据不区分数据类型，为了统一接口使用统一的字符串返回数据
//查询时的内容全部放到qurey_value_t中

typedef struct _qurey_value{
  char field[8][32];
} qurey_value_t;

#if 0
#define SQLITE_OK           0   /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* NOT USED. Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* NOT USED. Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */
#endif

sqlite3 *open_system_sqlite(char *sqlname)
{
  int rc;
	
#if 0
  sqlite3 *db = (sqlite3 *)malloc(sizeof(sqlite3 *));
  if (db == NULL) return db;
#endif
  sqlite3 *db;
  //rc = sqlite3_open_v2(sqlname, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
  rc = sqlite3_open_v2(sqlname, &db, SQLITE_OPEN_READWRITE, NULL);
  if(rc != SQLITE_OK) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
	  return NULL;
  }

  return db;
}

sqlite3 *open_or_create_system_sqlite(char *sqlname)
{
  int rc;
	
#if 0
  sqlite3 *db = (sqlite3 *)malloc(sizeof(sqlite3 *));
  if (db == NULL) return db;
#endif
  sqlite3 *db;
  rc = sqlite3_open_v2(sqlname, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
  if(rc != SQLITE_OK) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
	  return NULL;
  }

  return db;
}

void close_system_sqlite(sqlite3 *db)
{
  if (db != NULL)
    sqlite3_close_v2(db);
  //free(db);
}

int check_system_sqlite_is_exist(sqlite3 *db, char *table) 
{
  char sql[256] = "";
  int nRet;
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  int ret = 0;

  sprintf(sql, "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s';", table);

  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "check sqlite table error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }

/*SQLITE_DONE表示该语句已成功完成执行， SQLITE_ROW指示另一行输出可用*/
  while(sqlite3_step(ppStmt) == SQLITE_ROW) {
    if (0 == sqlite3_column_int(ppStmt, 0)) {
      ret = -2;
    }
  }
  //sqlite3_step(ppStmt);
  sqlite3_finalize(ppStmt);

  return ret;
}

//使用该接口创建表
int sqilte_create_table(sqlite3 *db, char *table)
{
  char sql[256] = "";
  int nRet;
  sqlite3_stmt *ppStmt;
  const char *pzTail;

  sprintf(sql, "CREATE TABLE %s (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, type VARCHAR, time TIME DEFAULT (datetime('now', 'localtime')));", table);

  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite create table error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }

  sqlite3_step(ppStmt);
  sqlite3_finalize(ppStmt);  

  return 0;
}

//重新定义结构里面表名是否需要默认值，唯一性，键值，非空等
int alter_systtem_table_column()
{
//alter table basic_para add SW_Hea int default 34 not null;数据库字段添加,由于字段只能一个一个的加，需要处理一下使其一次性加入多个

  return 0;
}

int sqlite3_delete_table(sqlite3 *db, char *table)
{
  char sql[256] = "";
  int nRet;
  sqlite3_stmt *ppStmt;
  const char *pzTail;

  sprintf(sql, "drop table %s;", table);
  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite delete table error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }

  sqlite3_step(ppStmt);
  sqlite3_finalize(ppStmt);  

  return 0;
}


//从数据库中查询数据
int sqlite3_query(sqlite3 *db, char *table, char *field, uint8_t field_num, qurey_value_t *qvt, char *is_where)
{
  char sql[1024] = "";
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  const unsigned char *rval;
  int iCol, nRet, record = 0;

  if (is_where == NULL) {
    sprintf(sql, "SELECT %s FROM %s;", field, table);
  }
  else {
    sprintf(sql, "SELECT %s FROM %s where %s;", field, table, is_where);
  }
  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite query error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }
  //开启事务
  //sqlite3_exec(db,"begin;",0,0,0);

/*SQLITE_DONE表示该语句已成功完成执行， SQLITE_ROW指示另一行输出可用*/
  while(sqlite3_step(ppStmt) == SQLITE_ROW) {
    for (iCol = 0; iCol < field_num; iCol++) {
      rval = sqlite3_column_text(ppStmt, iCol);
      if (rval != NULL) strcpy(qvt->field[iCol], (char *)rval);
      else qvt->field[iCol][0] = 0;
    }
    qvt++;
    record++;
  }
  //关闭事务
  //sqlite3_exec(db,"commit;",0,0,0);  
  sqlite3_finalize(ppStmt);
  return record;
}

int sqlite3_query_warning_log(sqlite3 *db, char *table, char *field, uint8_t field_num, qurey_value_t *qvt, int limit, int offset)
{
  char sql[1024] = "";
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  const unsigned char *rval;
  int iCol, nRet, record = 0;

  sprintf(sql, "select %s from %s order by id desc limit %d offset %d;", field, table, limit, offset);
  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite query error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }
  //开启事务
  //sqlite3_exec(db,"begin;",0,0,0);

/*SQLITE_DONE表示该语句已成功完成执行， SQLITE_ROW指示另一行输出可用*/
  while(sqlite3_step(ppStmt) == SQLITE_ROW) {
    for (iCol = 0; iCol < field_num; iCol++) {
      rval = sqlite3_column_text(ppStmt, iCol);
      if (rval != NULL) strcpy(qvt->field[iCol], (char *)rval);
      else qvt->field[iCol][0] = 0;
    }
    qvt++;
    record++;
  }
  //关闭事务
  //sqlite3_exec(db,"commit;",0,0,0);  
  sqlite3_finalize(ppStmt);
  return record;
}

//支持一次性查询多条sql语句，但是sql语句要自己写
int sqlite3_query_for_duplicate(sqlite3 *db, char *sql)
{
  char *pzTail;

  sqlite3_exec(db, "begin;", 0, 0, 0);
  if(SQLITE_OK != sqlite3_exec(db, sql, 0, 0, &pzTail)){
    fprintf(stderr, "SQL error: %s line: %d\n", pzTail, __LINE__);
    sqlite3_free(pzTail);
    return -1;
  }
  sqlite3_exec(db,"commit;",0,0,0);  

  return 0;
}

//该函数一次只能更新一条记录的一个字段
int sqlite3_upate(sqlite3 *db, char *table, char *field, char *value, char *is_where)
{
  char sql[256] = "";
  int nRet;
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  int ret = 0;

  if (is_where == NULL) {
    sprintf(sql, "update %s set %s=%s;", table, field, value);
  }
  else {
    sprintf(sql, "update %s set %s=%s where %s;", table, field, value, is_where);
  }
  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite update error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }

  sqlite3_step(ppStmt);
  sqlite3_finalize(ppStmt);
  return ret;
}

//多字段更新，但是只能更新一条记录
//field_val = "val=1, val2=2, val3=3"
int sqlite3_upate_for_fields(sqlite3 *db, char *table, char *field_val, char *is_where)
{
  char sql[256] = "";
  int nRet;
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  int ret = 0;

  if (is_where == NULL) {
    sprintf(sql, "update %s set %s;", table, field_val);
  }
  else {
    sprintf(sql, "update %s set %s where %s;", table, field_val, is_where);
  }
  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite query for fields error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }

  sqlite3_step(ppStmt);
  sqlite3_finalize(ppStmt);

  return ret;
}

//数据删除
int sqlite3_delete(sqlite3 *db, char *table, char *is_where) 
{
  char sql[256] = "";
  int nRet;
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  int ret = 0;

  if (is_where == NULL) {
    sprintf(sql, "delete from %s;", table);
  }
  else {
    sprintf(sql, "delete from %s where %s;", table, is_where);
  }

  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite query for fields error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }

  sqlite3_step(ppStmt);
  sqlite3_finalize(ppStmt);

  return ret;
}

int sqlite3_insert(sqlite3 *db, char *table, char *field, char *value)
{
  char sql[256] = "";
  int nRet;
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  int ret = 0;

  sprintf(sql, "insert into %s(%s) values(%s);", table, field, value);

  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite insert error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }

  sqlite3_step(ppStmt);
  sqlite3_finalize(ppStmt);
  return ret;
}

//求和
int sqlite3_query_max(sqlite3 *db, char *table, char *field)
{
  char sql[1024] = "";
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  int nRet, val = 0;
  
  sprintf(sql, "SELECT max(%s) FROM %s;", field, table);
  
  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite query error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }
  //开启事务
  //sqlite3_exec(db,"begin;",0,0,0);

  while(sqlite3_step(ppStmt) == SQLITE_ROW) {
    val = sqlite3_column_int(ppStmt, 0);
  }
  //关闭事务
  //sqlite3_exec(db,"commit;",0,0,0);  
  sqlite3_finalize(ppStmt);

  return val;
}

int sqlite3_query_from_order_by(sqlite3 *db, char *table, char *field, uint8_t field_num, qurey_value_t *qvt, 
  char *order, int limit, int offset)
{
  char sql[1024] = "";
  sqlite3_stmt *ppStmt;
  const char *pzTail;
  const unsigned char *rval;
  int iCol, nRet, record = 0;

  sprintf(sql, "select %s from %s order by %s desc limit %d offset %d;", field, table, order, limit, offset);
  nRet = sqlite3_prepare_v2(db, sql, strlen(sql), &ppStmt, &pzTail);
  if (nRet != SQLITE_OK) {
    fprintf(stderr, "sqlite query error: %s sql: %s\n", sqlite3_errmsg(db), sql);
    return -1;
  }
  //开启事务
  //sqlite3_exec(db,"begin;",0,0,0);

/*SQLITE_DONE表示该语句已成功完成执行， SQLITE_ROW指示另一行输出可用*/
  while(sqlite3_step(ppStmt) == SQLITE_ROW) {
    for (iCol = 0; iCol < field_num; iCol++) {
      rval = sqlite3_column_text(ppStmt, iCol);
      if (rval != NULL) strcpy(qvt->field[iCol], (char *)rval);
      else qvt->field[iCol][0] = 0;
    }
    qvt++;
    record++;
  }
  //关闭事务
  //sqlite3_exec(db,"commit;",0,0,0);  
  sqlite3_finalize(ppStmt);
  return record;
}


//禁止使用
int sqlite3_attach_database(sqlite3 *db, char *tmp_db_path)
{
  char sql[128] = "";
  char *pzTail;

  sprintf(sql, "ATTACH DATABASE '%s' as 'upgrade';", tmp_db_path);

  if(SQLITE_OK != sqlite3_exec(db, sql, 0, 0, &pzTail)){
    fprintf(stderr, "SQL error: %s line: %d\n", pzTail, __LINE__);
    sqlite3_free(pzTail);
    return -1;
  }

  return 0;
}

//禁止使用
int sqlite3_detach_database(sqlite3 *db)
{
  char sql[128] = "";
  char *pzTail;

  sprintf(sql, "DETACH DATABASE 'upgrade';");

  if(SQLITE_OK != sqlite3_exec(db, sql, 0, 0, &pzTail)){
    fprintf(stderr, "SQL error: %s line: %d\n", pzTail, __LINE__);
    sqlite3_free(pzTail);
    return -1;
  }

  return 0;
}

/*
ATTACH DATABASE 'origin.db' as 'origin';
UPDATE table SET 
value=(SELECT value FROM origin.table as uw WHERE uw.id=table.id) 
WHERE id=(SELECT id FROM origin.table as uw WHERE uw.id=table.id);
DETACH DATABASE 'upgrade';", tmp_db_path);

WHERE exists(SELECT id FROM origin.table as uw WHERE uw.id=table.id);

ATTACH DATABASE:附加数据库指令
*/

/*
 *db：新的数据库句柄
 *table：要更新的表名
 *origin: 原始数据库名
 *参数可变，参数为需要更新的字段名；以NULL为结尾
**/
int sqlite3_upgrade_data(sqlite3 *db, char *table, char *origin, ...)
{
  char sql[1024] = "";
  int len = 0;
  char *pzTail;

  va_list pArgs;
  va_start(pArgs, origin);

  len += sprintf(sql, "ATTACH DATABASE '%s' as 'origin';UPDATE %s SET ", origin, table);
  char *argument = va_arg(pArgs, char *);
  while (argument != NULL) {
    len += sprintf(sql+len, "%s=(SELECT %s FROM origin.%s as uw WHERE uw.id=%s.id),", argument, argument, table, table);
    argument = va_arg(pArgs, char *);
  }
  va_end(pArgs);

#if 0
  sql[len - 1] = ' ';
  len += srpintf(sql+len, "WHERE id=(SELECT id FROM origin.%s as uw WHERE uw.id=%s.id);", table, table);
#else
  sql[len - 1] = ';';
#endif
  len += sprintf(sql+len, "DETACH DATABASE 'origin';");

  if(SQLITE_OK != sqlite3_exec(db, sql, 0, 0, &pzTail)){
    fprintf(stderr, "SQL error: %s line: %d\n", pzTail, __LINE__);
    sqlite3_free(pzTail);
    return -1;
  }

  return 0;
}

例子：
/*
UPDATE table1 SET "
               "value=(SELECT value FROM table2 as uw WHERE uw.id=table1.id), "
               "value2=(SELECT value2 FROM table2 as uw WHERE uw.id=table1.id), "
               "value3=(SELECT value3 FROM table2 as uw WHERE uw.id=table1.id) "
               "WHERE table1.id=(SELECT id FROM table2 as uw WHERE uw.id=table1.id);
*/

/*
 *db：数据库句柄
 *table：要恢复的表名
 *origin: 作为备份存在的表表名
 *参数可变，参数为需要更新的字段名；以NULL为结尾
**/
int sqlite3_restore_data(sqlite3 *db, char *table, char *restore, ...)
{
  char sql[1024] = "";
  int len = 0;
  char *pzTail;

  va_list pArgs;
  va_start(pArgs, restore);

  len += sprintf(sql, "UPDATE %s SET ", table);
  char *argument = va_arg(pArgs, char *);
  while (argument != NULL) {
    len += sprintf(sql + len, "%s=(SELECT %s FROM %s as uw WHERE uw.id=%s.id),", argument, argument, restore, table);
    argument = va_arg(pArgs, char *);
  }
  va_end(pArgs);
  sql[len - 1] = ';';

  if(SQLITE_OK != sqlite3_exec(db, sql, 0, 0, &pzTail)){
    fprintf(stderr, "SQL error: %s line: %d\n", pzTail, __LINE__);
    sqlite3_free(pzTail);
    return -1;
  }
  return 0;
}

/*
** This function is used to load the contents of a database file on disk 
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database, 
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are 
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
int loadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, int isSave){
  int rc;                   /* Function return code */
  sqlite3 *pFile;           /* Database connection opened on zFilename */
  sqlite3_backup *pBackup;  /* Backup object used to copy data */
  sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
  sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

  /* Open the database file identified by zFilename. Exit early if this fails
  ** for any reason. */
  rc = sqlite3_open(zFilename, &pFile);
  if( rc==SQLITE_OK ){

    /* If this is a 'load' operation (isSave==0), then data is copied
    ** from the database file just opened to database pInMemory. 
    ** Otherwise, if this is a 'save' operation (isSave==1), then data
    ** is copied from pInMemory to pFile.  Set the variables pFrom and
    ** pTo accordingly. */
    pFrom = (isSave ? pInMemory : pFile);
    pTo   = (isSave ? pFile     : pInMemory);

    /* Set up the backup procedure to copy from the "main" database of 
    ** connection pFile to the main database of connection pInMemory.
    ** If something goes wrong, pBackup will be set to NULL and an error
    ** code and message left in connection pTo.
    **
    ** If the backup object is successfully created, call backup_step()
    ** to copy data from pFile to pInMemory. Then call backup_finish()
    ** to release resources associated with the pBackup object.  If an
    ** error occurred, then an error code and message will be left in
    ** connection pTo. If no error occurred, then the error code belonging
    ** to pTo is set to SQLITE_OK.
    */
    pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
    if( pBackup ){
      (void)sqlite3_backup_step(pBackup, -1);
      (void)sqlite3_backup_finish(pBackup);
    }
    rc = sqlite3_errcode(pTo);
  }

  /* Close the database connection opened on database file zFilename
  ** and return the result of this function. */
  (void)sqlite3_close(pFile);
  return rc;
}