[大数据]C语言使用 sqlite数据库

sqlite 导入 使用conan 一行代码就可以

sqlite3/3.32.3

具体使用
创建基本的增删改查

#ifndef BASICC_DOWNLOADER_SQLITE_MANAGER_H_
#define BASICC_DOWNLOADER_SQLITE_MANAGER_H_

#include "basics.h"
#include <sqlite3.h>
#include <glib.h>

#define MAX_SQL_LENGTH
// 用来解析数据的  做一些赋值工作   具体操作由具体  具体数据库操作决定
typedef void (*DataRetriever)(void *, sqlite3_stmt *);

typedef struct QuerySingleResult {
  size_t element_size;
  DataRetriever data_retriever;
  void *data;
} QuerySingleResult;

typedef struct QueryManyResult {
  size_t element_size;
  DataRetriever data_retriever;
  GPtrArray *array;
} QueryManyResult;

int OpenDataBase(char *path);

/**
 * @param sql_format update task_info set status = $d where id = $d
 * @param ...  status, id
 * @return
 */
int ExecuteSql(char *sql_format, ...);

/**
 * @param sql_format select * from task_info where id = $d and status = $d
 * @param ...  id, status
 * @return
 */
int QuerySingle(QuerySingleResult *query_single_result, char *sql_format, ...);

int QueryMany(QueryManyResult *query_result, char *sql_format, ...);

sqlite3_int64 GetLastInsertRowId();

void CloseDataBase();

#endif //BASICC_DOWNLOADER_SQLITE_MANAGER_H_

每个数据库有每个数据库不同的信息 不同的常用操作

#ifndef BASICC_DOWNLOADER_TASK_INFO_H_
#define BASICC_DOWNLOADER_TASK_INFO_H_

#include <glib.h>
#include <sqlite3.h>

typedef struct sqlite3_stmt sqlite3_stmt;

#define STATUS_REMOVING -1
#define STATUS_ERROR 0
#define STATUS_READY  1
#define STATUS_PAUSED 2
#define STATUS_DOWNLOADING 3
#define STATUS_COMPLETED 4

#define STATUS_READY_TEXT "Ready" //0
#define STATUS_PAUSED_TEXT "Paused" //1
#define STATUS_DOWNLOADING_TEXT "Downloading" //2
#define STATUS_COMPLETED_TEXT "Completed" //3
#define STATUS_ERROR_TEXT "Error" //4

#define INVALID_ID -1
// 数据库内结构体的映射
typedef struct {
  gint64 id;
  char *filename;
  char *directory;
  char *url;
  gint64 size;
  guint64 progress;
  guint status;
  char *create_time;
  gboolean resume_support;
} TaskInfo;

void TaskInfoDump(TaskInfo *task_info);

void DestroyTaskInfoContent(TaskInfo *task_info);

void DestroyTaskInfo(TaskInfo **task_info);

int InsertTaskInfo(TaskInfo *task_info);

void UpdateTaskInfo(TaskInfo *task_info);

void DeleteTaskInfo(TaskInfo *task_info);

TaskInfo *FindTaskInfoById(sqlite3_int64 id);

GPtrArray *ListTaskInfos();

#endif //BASICC_DOWNLOADER_TASK_INFO_H_

具体操作的实现

#include "common/sqlite_manager.h"
#include "utils/io_utils.h"
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>

static sqlite3 *db;

static int Sqlite3PrepareWithParameters(sqlite3_stmt **p_stmt, char const *sql_format, va_list args) {
  char *sql = strdup(sql_format);
  int sql_length = strlen(sql);
  //获取参数的类型
  char parameter_types[20];
  int parameter_type_count = 0;

  for (int i = 0; i < sql_length; ++i) {
    if (sql[i] == '$') {
      if(sql[i + 1] != '$') {
        parameter_types[parameter_type_count++] = sql[i + 1];
        sql[i] = '?';
      }
      sql[i + 1] = ' ';
    }
  }

  PRINTLNF("DB Execute: %s", sql);
  //解析第二步
  int err = sqlite3_prepare_v2(db, sql, -1, p_stmt, NULL);

  free(sql);

  if (err != SQLITE_OK) {
    PRINTLNF("prepare failed: %s", sqlite3_errmsg(db));
    return RESULT_FAILURE;
  }
    //读取参数 绑定进去
  for (int i = 0; i < parameter_type_count; ++i) {
    switch (parameter_types[i]) {
      case 'c': {
        char arg = va_arg(args, int);
        char parameter_holder[2] = {arg};
        sqlite3_bind_text(*p_stmt, i + 1, parameter_holder, -1, NULL);
        break;
      }
      case 's': {
        sqlite3_bind_text(*p_stmt, i + 1, va_arg(args, char *), -1, NULL);
        break;
      }
      case 'd': {
        int arg = va_arg(args, int);
        sqlite3_bind_int64(*p_stmt, i + 1, arg);
        break;
      }
      case 'f': {
        sqlite3_bind_double(*p_stmt, i + 1, va_arg(args, double));
        break;
      }
      default: {
        PRINTLNF("Unsupported parameter type: %c.", parameter_types[i]);
        exit(-1);
      }
    }
  }

  return RESULT_OK;
}

int OpenDataBase(char *path) {
  if (sqlite3_open(path, &db) != SQLITE_OK) {
    PRINTLNF("Can't open database: %s", sqlite3_errmsg(db));
    CloseDataBase();
    return RESULT_FAILURE;
  }
  return RESULT_OK;
}

int ExecuteSql(char *sql_format, ...) {
  if (db) {
    int result;
    sqlite3_stmt *stmt;

    va_list args;
    va_start(args, sql_format);
    if (Sqlite3PrepareWithParameters(&stmt, sql_format, args) == RESULT_OK) {
      if (sqlite3_step(stmt) != SQLITE_DONE) {
        PRINTLNF("execution failed: %s", sqlite3_errmsg(db));
        result = RESULT_FAILURE;
      } else {
        result = RESULT_OK;
      }
      sqlite3_finalize(stmt);
    } else {
      result = RESULT_FAILURE;
    }
    va_end(args);

    return result;
  } else {
    return RESULT_FAILURE;
  }
}

int QuerySingle(QuerySingleResult *query_single_result, char *sql_format, ...) {
  if (db) {
    int result;
    sqlite3_stmt *stmt;

    va_list args;
    va_start(args, sql_format);
    if (Sqlite3PrepareWithParameters(&stmt, sql_format, args) == RESULT_OK) {
      if (sqlite3_step(stmt) == SQLITE_ROW) {
          //查询的时候需要绑定
          // 在C语言查询的时候 不知道返回类型多大  因为要开辟空间 所以这里也是要传值 回调后开辟空间的
        query_single_result->data = malloc(query_single_result->element_size);
        query_single_result->data_retriever(query_single_result->data, stmt);
        result = RESULT_OK;
      } else {
        PRINTLNF("execution failed: %s", sqlite3_errmsg(db));
        result = RESULT_FAILURE;
      }
      sqlite3_finalize(stmt);
    } else {
      result = RESULT_FAILURE;
    }
    va_end(args);

    return result;
  } else {
    return RESULT_FAILURE;
  }
}

int QueryMany(QueryManyResult *query_result, char *sql_format, ...) {
  if (db) {
    int result;
    sqlite3_stmt *stmt;
    va_list args;
    va_start(args, sql_format);
    if (Sqlite3PrepareWithParameters(&stmt, sql_format, args) == RESULT_OK) {
      if (!query_result->array) {
        query_result->array = g_ptr_array_new_with_free_func(free);
      }

      while (1) {
        int sql_result = sqlite3_step(stmt);
        if (sql_result == SQLITE_ROW) {
          void *data = malloc(query_result->element_size);
          g_ptr_array_add(query_result->array, data);
          query_result->data_retriever(data, stmt);
        } else if(sql_result == SQLITE_ERROR){
          PRINTLNF("execution failed: %s", sqlite3_errmsg(db));
          result = RESULT_FAILURE;
          break;
        } else {
          result = RESULT_OK;
          break;
        }
      }
      sqlite3_finalize(stmt);
    } else {
      result  = RESULT_FAILURE;
    }
    va_end(args);
    return result;
  }
  return RESULT_FAILURE;
}

sqlite3_int64 GetLastInsertRowId() {
  return sqlite3_last_insert_rowid(db);
}

void CloseDataBase() {
  if (db) {
    sqlite3_close(db);
    db = NULL;
  }
}

调用复现

#include "model/task_info.h"
#include "common/sqlite_manager.h"
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#include "utils/io_utils.h"
//DataRetriever 对于TaskInfo 数据库中得出的信息进行映射  也就是指向吧  （指针函数 和接口 ）
static void TaskInfoRetriever(TaskInfo *task_info, sqlite3_stmt *result) {
  task_info->id = sqlite3_column_int64(result, 0);
  task_info->url = strdup((char *) sqlite3_column_text(result, 1));
  task_info->filename = strdup((char *) sqlite3_column_text(result, 2));
  task_info->directory = strdup((char *) sqlite3_column_text(result, 3));
  task_info->size = sqlite3_column_int64(result, 4);
  task_info->progress = sqlite3_column_int64(result, 5);
  task_info->status = sqlite3_column_int(result, 6);
  task_info->resume_support = sqlite3_column_int(result, 7);
  task_info->create_time = strdup((char *) sqlite3_column_text(result, 8));
}

int InsertTaskInfo(TaskInfo *task_info) {
    //这里还得传类型 不能直接问好所以传递 $+类型 以供后面判断读取可变长参数
  int result = ExecuteSql("insert into task_info(filename, directory, url, size, progress, status, resume_support) "
                          "values($s, $s, $s, $d, $d, $d, $d)",
                          task_info->filename,
                          task_info->directory,
                          task_info->url,
                          task_info->size,
                          task_info->progress,
                          task_info->status,
                          task_info->resume_support);

  if (result == RESULT_OK) {
      //对于默认值进行操作
    TaskInfo *task_info_from_db = FindTaskInfoById(GetLastInsertRowId());
    //返回数据库生成的默认值
    //复制了一份放在返回的结构体中
    task_info->create_time = strdup(task_info_from_db->create_time);
    task_info->id = task_info_from_db->id;

    DestroyTaskInfo(&task_info_from_db);
  }

  return result;
}

void UpdateTaskInfo(TaskInfo *task_info) {
  ExecuteSql("update task_info set progress=$d, status=$d where id=$d",
             task_info->progress,
             task_info->status,
             task_info->id);
}

void DeleteTaskInfo(TaskInfo *task_info) {
  ExecuteSql("delete from task_info where id=$d", task_info->id);
  task_info->id = INVALID_ID;
}

TaskInfo *FindTaskInfoById(sqlite3_int64 id) {
  QuerySingleResult query_single_result = {
          // 在调用的时候传入 要开辟空间的大小
      .element_size = sizeof(TaskInfo),
      .data_retriever = (DataRetriever)TaskInfoRetriever
  };

  if (QuerySingle(&query_single_result, "select * from task_info where id=$d", id) == RESULT_OK) {
    return query_single_result.data;
  }
  return NULL;
}

GPtrArray *ListTaskInfos() {
  QueryManyResult query_many_result = {
      .element_size = sizeof(TaskInfo),
      .data_retriever = (DataRetriever)TaskInfoRetriever
  };
  QueryMany(&query_many_result, "select * from task_info");
  return query_many_result.array;
}

void DestroyTaskInfoContent(TaskInfo *task_info) {
  if (task_info) {
    free(task_info->filename);
    free(task_info->directory);
    free(task_info->url);
    free(task_info->create_time);
    memset(task_info, 0, sizeof(TaskInfo));
  }
}


void DestroyTaskInfo(TaskInfo **task_info) {
  if (task_info && *task_info) {
    free((*task_info)->filename);
    free((*task_info)->directory);
    free((*task_info)->url);
    free((*task_info)->create_time);

    free(*task_info);
    *task_info = NULL;
  }
}

void TaskInfoDump(TaskInfo *task_info) {
  PRINTLNF("TaskInfo(\n"
           "            id=%lld,\n"
           "            filename=%s,\n"
           "            directory=%s,\n"
           "            url=%s,\n"
           "            size=%lld,\n"
           "            progress=%lld,\n"
           "            status=%d,\n"
           "            resume_support=%d,\n"
           "            create_time=%s\n"
           "         )",
           task_info->id,
           task_info->filename,
           task_info->directory,
           task_info->url,
           task_info->size,
           task_info->progress,
           task_info->status,
           task_info->resume_support,
           task_info->create_time);
}

学习到点 或者说技术点
1 因为C语言开辟空间需要具体的malloc sizeof 不同数据库 查询结果所需要内存的大小不一样 所以在设计 查询结果的时候 对于结构体设定 给定一个element_size = sizeof(TaskInfo), 谁调用 那就开辟谁的大小。 同样不同数据库操作不一样 故而也使用了回调 谁调用传入 谁的操作一样 具体操作由对应数据库控制

代码如图

typedef struct QuerySingleResult {
  size_t element_size;
  DataRetriever data_retriever;
  void *data;
} QuerySingleResult;

QuerySingleResult query_single_result = {
          // 在调用的时候传入 要开辟空间的大小
      .element_size = sizeof(TaskInfo),
      .data_retriever = (DataRetriever)TaskInfoRetriever
  };

static void TaskInfoRetriever(TaskInfo *task_info, sqlite3_stmt *result) {
  task_info->id = sqlite3_column_int64(result, 0);
  task_info->url = strdup((char *) sqlite3_column_text(result, 1));
  task_info->filename = strdup((char *) sqlite3_column_text(result, 2));
  task_info->directory = strdup((char *) sqlite3_column_text(result, 3));
  task_info->size = sqlite3_column_int64(result, 4);
  task_info->progress = sqlite3_column_int64(result, 5);
  task_info->status = sqlite3_column_int(result, 6);
  task_info->resume_support = sqlite3_column_int(result, 7);
  task_info->create_time = strdup((char *) sqlite3_column_text(result, 8));
}

具体调用事宜由通用封装决定

完美的函数式编程思路

就像java function对函数进行定义一样

但是java 对象形式的编程 直接这样函数式编程就很绕

2 关于数据库查询 传递可变长参数的时候 由于C的可变长参数对于类型没有通用所以在 传递类型 后获取类型 然后赋值 在吧传递参数地方的符号改为？

  int result = ExecuteSql("insert into task_info(filename, directory, url, size, progress, status, resume_support) "
                          "values($s, $s, $s, $d, $d, $d, $d)",
                          task_info->filename,
                          task_info->directory,
                          task_info->url,
                          task_info->size,
                          task_info->progress,
                          task_info->status,
                          task_info->resume_support);