C/C++ 透過SQLiteSDK增刪改查

微軟技術分享發表於2023-11-25

SQLite,作為一款嵌入式關係型資料庫管理系統,一直以其輕量級、零配置以及跨平臺等特性而備受青睞。不同於傳統的資料庫系統,SQLite是一個庫,直接與應用程式一同編譯和連結,無需單獨的資料庫伺服器程式,實現了資料庫的零配置管理。這種設計理念使得SQLite成為許多嵌入式系統、移動應用和小型專案中的首選資料庫引擎。

SQLite的特點包括:

  1. 嵌入式資料庫引擎: SQLite 是一個嵌入式資料庫引擎,意味著它是一個庫,與應用程式一同編譯和連結,而不是作為獨立的程式執行。
  2. 零配置: 無需配置或管理。SQLite 不需要一個獨立的資料庫伺服器程式,所有的操作都是直接在儲存在檔案中的資料庫上執行。
  3. 輕量級: SQLite 是一個輕量級的資料庫,相對於一些其他資料庫管理系統來說,它的記憶體佔用和資源消耗相對較小。
  4. 支援 SQL: SQLite 支援大部分標準的 SQL 語法,並提供了事務支援,包括隔離級別和回滾。
  5. 跨平臺: SQLite 可以在各種作業系統上執行,包括 Windows、Linux、macOS 和其他嵌入式系統。
  6. 自給自足: SQLite 資料庫是一個單一的磁碟檔案,整個資料庫被儲存在一個檔案中,這使得備份、複製或傳輸資料庫變得非常容易。
  7. 開源: SQLite 是一個開源專案,採用公共領域授權(Public Domain License),可以在商業和非商業專案中免費使用。

SQLite 資料庫以其獨特的自給自足特性脫穎而出,整個資料庫被儲存在一個單一的磁碟檔案中,使得備份、複製或傳輸資料庫變得異常簡單。而作為一款開源專案,SQLite採用了公共領域授權,可以在商業和非商業專案中免費使用。

由於該資料庫的小巧和簡潔所以在使用上也非常容易,當讀者下載好附件以後會看到如下圖所示的檔案;

使用時只需要將sqlite3.hsqlite3.c檔案匯入到專案中並使用#include "sqlite3.h"即可,無需做其他配置,圖中的sqlite3.dll是動態庫,sqlite3.exe則是一個命令列版本的資料庫可在測試時使用它。

開啟與關閉庫

sqlite3_open 用於開啟或建立一個 SQLite 資料庫檔案。該函式的原型如下:

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
  • filename: 要開啟或建立的 SQLite 資料庫檔案的路徑。如果檔案不存在,將會建立一個新的資料庫檔案。
  • ppDb: 用於儲存 SQLite 資料庫控制程式碼(handle)的指標。SQLite 資料庫控制程式碼是與一個開啟的資料庫關聯的結構,它在後續的 SQLite 操作中用作標識。

該函式返回一個整數值,代表函式的執行狀態。如果函式成功執行,返回 SQLITE_OK。如果有錯誤發生,返回一個表示錯誤程式碼的整數值。可以透過 sqlite3_errmsg 函式獲取更詳細的錯誤資訊。

sqlite3_close 用於關閉資料庫連線的函式。其原型如下:

int sqlite3_close(sqlite3*);
  • sqlite3: 要關閉的 SQLite 資料庫連線的控制程式碼。

該函式返回一個整數值,用於表示函式的執行狀態。如果函式成功執行,返回 SQLITE_OK。如果有錯誤發生,返回一個表示錯誤程式碼的整數值。

使用 sqlite3_close 函式可以釋放與資料庫連線相關的資源,並確保資料庫檔案被正確關閉。在關閉資料庫連線之前,應該確保已經完成了所有需要執行的 SQL 語句,並在需要的情況下檢查執行結果。

// 開啟資料庫並返回控制程式碼
sqlite3* open_database(std::string database_name)
{
  int ref =-1;
  sqlite3 *db = 0;

  ref = sqlite3_open(database_name.c_str(), &db);
  if (ref == SQLITE_OK)
    return db;
  return false;
}

// 關閉資料庫
bool close_database(sqlite3 *db)
{
  int ref = sqlite3_close(db);
  if (ref == SQLITE_OK)
    return true;
  return false;
}

執行查詢語句

sqlite3_exec 用於執行 SQL 語句的高階介面函式。它的原型如下:

int sqlite3_exec(
  sqlite3* db,                    /* Database handle */
  const char* sql,                /* SQL statement, UTF-8 encoded */
  int (*callback)(                /* Callback function */
    void*,                        /* Callback parameter */
    int,                          /* Number of columns in the result set */
    char**,                       /* Array of column values */
    char**                        /* Array of column names */
  ),
  void* callback_param,           /* 1st argument to callback function */
  char** errmsg                   /* Error msg written here */
);
  • db: SQLite 資料庫連線的控制程式碼。
  • sql: 要執行的 SQL 語句,以 UTF-8 編碼。
  • callback: 回撥函式,用於處理查詢結果的每一行資料。
  • callback_param: 傳遞給回撥函式的引數。
  • errmsg: 用於儲存錯誤訊息的指標。

sqlite3_exec 函式執行一個或多個 SQL 語句,並對每一條語句的執行結果呼叫指定的回撥函式。回撥函式的原型如下:

int callback(
  void* callback_param, /* 引數,由 sqlite3_exec 傳遞給回撥函式 */
  int num_columns,      /* 結果集中的列數 */
  char** column_values,  /* 指向結果集中當前行的列值的陣列 */
  char** column_names    /* 指向結果集中列名的陣列 */
);
  • callback_param: 回撥函式的引數,由 sqlite3_exec 傳遞給回撥函式。
  • num_columns: 結果集中的列數。
  • column_values: 指向結果集中當前行的列值的陣列。
  • column_names: 指向結果集中列名的陣列。

回撥函式返回一個整數,用於指示是否繼續執行後續的 SQL 語句。如果回撥函式返回非零值,sqlite3_exec 將停止執行 SQL,並立即返回。

sqlite3_prepare_v2 用於準備 SQL 語句的介面函式。它的原型如下:

int sqlite3_prepare_v2(
  sqlite3* db,            /* Database handle */
  const char* sql,        /* SQL statement, UTF-8 encoded */
  int sql_len,            /* Length of SQL statement in bytes, or -1 for zero-terminated */
  sqlite3_stmt** stmt,    /* OUT: Statement handle */
  const char** tail       /* OUT: Pointer to unused portion of SQL statement */
);
  • db: SQLite 資料庫連線的控制程式碼。
  • sql: 要準備的 SQL 語句,以 UTF-8 編碼。
  • sql_len: SQL 語句的長度,如果為 -1,則表示 SQL 語句以 null 結尾。
  • stmt: 用於儲存準備好的語句控制程式碼的指標。
  • tail: 用於儲存未使用的 SQL 語句的指標。

sqlite3_prepare_v2 函式用於將 SQL 語句編譯成一個 SQLite 語句物件(prepared statement)。這個物件可以被多次執行,每次執行時可以繫結不同的引數。stmt 引數將用於儲存編譯後的語句的控制程式碼,以供後續的操作。

sqlite3_step 執行預編譯 SQL 語句的介面函式。它的原型如下:

int sqlite3_step(sqlite3_stmt*);
  • sqlite3_stmt*: 由 sqlite3_prepare_v2 預編譯的 SQL 語句的控制程式碼。

sqlite3_step 函式用於執行由 sqlite3_prepare_v2 預編譯的 SQL 語句。在執行過程中,可以透過不斷呼叫 sqlite3_step 來逐行獲取查詢結果,直到結果集結束。對於非查詢語句(如 INSERTUPDATEDELETE),sqlite3_step 函式執行一次即可完成操作。

該函式的返回值表示執行的結果,可能的返回值包括:

  • SQLITE_ROW: 成功獲取一行資料。
  • SQLITE_DONE: 執行完成,沒有更多的資料可用(用於非查詢語句)。
  • 其他錯誤碼,表示執行過程中出現了錯誤。

sqlite3_column_text 用於獲取查詢結果集中某一列的文字值。其原型為:

const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
  • sqlite3_stmt*: 由 sqlite3_prepare_v2 預編譯的 SQL 語句的控制程式碼。
  • int iCol: 列的索引,從0開始。

該函式返回指向字串值的指標,該字串值是查詢結果集中指定列的文字表示。需要注意的是,返回的指標指向 SQLite 內部的儲存區,應該在使用完之後儘早釋放資源。

sqlite3_column_int 用於獲取查詢結果集中某一列的整數值。其原型為:

int sqlite3_column_int(sqlite3_stmt*, int iCol);
  • sqlite3_stmt*: 由 sqlite3_prepare_v2 預編譯的 SQL 語句的控制程式碼。
  • int iCol: 列的索引,從0開始。

該函式返回查詢結果集中指定列的整數表示。需要注意的是,如果該列不是整數型別,或者包含的資料無法轉換為整數,那麼返回的結果可能不是有效的整數值。

sqlite3_finalize 用於釋放一個預備語句物件(prepared statement)。在使用 sqlite3_prepare_v2 函式準備 SQL 語句後,需要使用 sqlite3_finalize 來釋放相應的語句物件。

該函式的原型為:

int sqlite3_finalize(sqlite3_stmt *pStmt);
  • sqlite3_stmt *pStmt: 指向要釋放的語句物件的指標。

該函式返回 SQLITE_OK 表示成功,返回其他錯誤碼錶示失敗。

// 執行SQL語句
bool exec_sql(sqlite3 *db, char *sql)
{
  char *error_code = 0;
  int ref = sqlite3_exec(db, sql, 0, 0, &error_code);
  if (ref == SQLITE_OK)
  {
    return true;
  }
  return false;
}

// 插入資料
bool insert_data(sqlite3 *db, char *sql)
{
  sqlite3_stmt *stmt = 0;

  // 插入前檢查語句合法性, -1自動計算SQL長度
  int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
  if (ref == SQLITE_OK)
  {
    sqlite3_step(stmt);       // 執行語句
    sqlite3_finalize(stmt);   // 清理語句控制程式碼
    return true;
  }
  sqlite3_finalize(stmt);
  return false;
}

// 查詢資料集
bool select_data(sqlite3 *db, char *sql)
{
  sqlite3_stmt *stmt = 0;

  int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
  if (ref == SQLITE_OK)
  {
    // 每調一次sqlite3_step()函式,stmt就會指向下一條記錄
    while (sqlite3_step(stmt) == SQLITE_ROW)
    {
      // 取出第1列欄位的值
      const unsigned char *name = sqlite3_column_text(stmt, 1);
      
      // 取出第2列欄位的值
      int age = sqlite3_column_int(stmt, 2);
      std::cout << "姓名: " << name << " 年齡: " << age << std::endl;
    }
  }
  else
  {
    sqlite3_finalize(stmt);
    return false;
  }
  sqlite3_finalize(stmt);
  return true;
}

呼叫查詢語句

建立資料庫

首先開啟了名為 "database.db" 的 SQLite 資料庫,並建立了一個名為 "LySharkDB" 的表,該表格包含了id、name、agemsg四個欄位。隨後,透過執行 SQL 語句建立了這個表格。最後,關閉了資料庫連線。這段程式碼主要用於資料庫初始化操作,確保了資料庫中包含了指定的表格結構。

int main(int argc, char *argv[])
{
  sqlite3* open_db = open_database("database.db");
  if (open_db != false)
  {
    bool create_table_ref;

    std::string sql = 
      "create table LySharkDB("
      "id int auto_increment primary key,"
      "name char(30) not null," 
      "age int not null,"
      "msg text default null"
      ")";

    // 執行建立表操作
    char run_sql[1024] = { 0 };
    strcpy(run_sql, sql.c_str());
    create_table_ref = exec_sql(open_db, run_sql);
  }

  close_database(open_db);
  std::system("pause");
  return 0;
}

上述程式碼執行後則可以建立一個資料庫database.db表名為LySharkDB讀者可以使用資料庫工具開啟該表,其結構如下所示;

插入資料測試

建立資料庫後,接著就是插入資料測試,插入時可以使用insert_data,如下程式碼項資料庫中插入5條記錄;

int main(int argc, char *argv[])
{
  sqlite3* open_db = open_database("./database.db");
  if (open_db != false)
  {
    bool create_table_ref;

    // 執行插入記錄
    if (create_table_ref == true)
    {
      bool insert_ref = 0;
      insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(1,'lyshark',1,'hello lyshark');");
      insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(2,'guest',2,'hello guest');");
      insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(3,'admin',3,'hello admin');");
      insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(4,'wang',4,'hello wang');");
      insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(5,'sqlite',5,'hello sql');");
      if (insert_ref == true)
      {
        std::cout << "插入完成" << std::endl;
      }
    }
  }
  close_database(open_db);
  std::system("pause");
  return 0;
}

插入後,開啟資料庫管理軟體,可看到插入後的記錄;

查詢與刪除資料

而查詢刪除與增加記錄,我們這裡直接使用exec_sql()函式,透過傳入不同的SQL語句實現。

int main(int argc, char *argv[])
{
  sqlite3* open_db = open_database("./database.db");
  if (open_db != false)
  {
    // 刪除記錄
    bool delete_ref = exec_sql(open_db, "delete from LySharkDB where id = 5;");
    if (delete_ref == true)
    {
      std::cout << "刪除完成." << std::endl;
    }

    // 更新記錄
    bool update_ref = exec_sql(open_db, "update LySharkDB set name='lyshark' where id = 4;");
    if (update_ref == true)
    {
      std::cout << "更新完成." << std::endl;
    }

    // 查詢資料
    bool select_ref = select_data(open_db, "select * from LySharkDB;");
    if (select_ref == true)
    {
      std::cout << "查詢完畢." << std::endl;
    }
  }
  close_database(open_db);
  std::system("pause");
  return 0;
}

執行更新後的表記錄如下所示;

查詢區間資料

首先建立一些資料集,這裡透過迴圈生成並插入資料,如下程式碼中新建一個TimeDB資料表,其中有三個欄位uid,host_time,cpu_value

#include <iostream>
#include <string>
#include <map>
#include <vector>
#include <time.h>
#include "sqlite3.h"

#include <boost/lexical_cast.hpp>
#include <boost/format.hpp>

using namespace std;
using namespace boost;

// 獲取本地時間日期
std::string get_local_datetime()
{
	char ct[80];
	time_t  tt;
	struct tm *tblock;

	tt = time(NULL);
	tblock = localtime(&tt);

	strftime(ct, sizeof(ct), "%Y-%m-%d %H:%M:%S", tblock);
	return ct;
}

// 初始化建立表結構
void Init_Database()
{
	sqlite3* open_db = open_database("./database.db");
	if (open_db != false)
	{
		bool create_table_ref;
		std::string sql =
			"create table TimeDB("
			"uid primary key,"
			"host_time char(128) not null,"
			"cpu_value int not null"
			");";
		char run_sql[1024] = { 0 };
		strcpy(run_sql, sql.c_str());
		exec_sql(open_db, run_sql);
	}
	close_database(open_db);
}

// 批次生成時間字串並插入資料表
void Insert_Test()
{
	sqlite3* open_db = open_database("./database.db");
	for (int x = 0; x < 1000; x++)
	{
		// 獲取本地日期時間
		std::string local_times = get_local_datetime();
		std::string format_string = boost::str(boost::format("insert into TimeDB(uid,host_time,cpu_value) values(%d,'%s',%d);") % x %local_times %x);

		std::cout << "執行SQL: " << format_string << std::endl;

		char run_sql[1024] = { 0 };
		strcpy(run_sql, format_string.c_str());
		insert_data(open_db, run_sql);
		_sleep(1000);
	}
	close_database(open_db);
}

int main(int argc, char *argv[])
{
	sqlite3* open_db = open_database("./database.db");
	Init_Database();
	Insert_Test();

	std::system("pause");
	return 0;
}

如下是五分鐘的模擬資料;

當有了資料則再查詢,程式碼中Select_Time_List函式演示瞭如何透過時間查詢一個區間的資料,並返回一個容器列表給被呼叫者使用,查詢程式碼如下所示;

#include <iostream>
#include <string>
#include <map>
#include <vector>
#include <time.h>
#include "sqlite3.h"

#include <boost/lexical_cast.hpp>
#include <boost/format.hpp>

using namespace std;
using namespace boost;

// 開啟資料庫並返回控制程式碼
sqlite3* open_database(std::string database_name)
{
	int ref = -1;
	sqlite3 *db = 0;

	ref = sqlite3_open(database_name.c_str(), &db);
	if (ref == SQLITE_OK)
		return db;
	return false;
}

// 關閉資料庫
bool close_database(sqlite3 *db)
{
	int ref = sqlite3_close(db);
	if (ref == SQLITE_OK)
		return true;
	return false;
}

// 執行SQL語句
bool exec_sql(sqlite3 *db, char *sql)
{
	char *error_code = 0;
	int ref = sqlite3_exec(db, sql, 0, 0, &error_code);
	if (ref == SQLITE_OK)
	{
		return true;
	}
	return false;
}

// 插入資料
bool insert_data(sqlite3 *db, char *sql)
{
	sqlite3_stmt *stmt = 0;

	// 插入前檢查語句合法性, -1自動計算SQL長度
	int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
	if (ref == SQLITE_OK)
	{
		sqlite3_step(stmt);       // 執行語句
		sqlite3_finalize(stmt);   // 清理語句控制程式碼
		return true;
	}
	sqlite3_finalize(stmt);
	return false;
}

// 查詢資料集
bool select_data(sqlite3 *db, char *sql)
{
	sqlite3_stmt *stmt = 0;

	int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
	if (ref == SQLITE_OK)
	{
		// 每調一次sqlite3_step()函式,stmt就會指向下一條記錄
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			// 取出第1列欄位的值
			const unsigned char *name = sqlite3_column_text(stmt, 1);

			// 取出第2列欄位的值
			int age = sqlite3_column_int(stmt, 2);
			std::cout << "姓名: " << name << " 年齡: " << age << std::endl;
		}
	}
	else
	{
		sqlite3_finalize(stmt);
		return false;
	}
	sqlite3_finalize(stmt);
	return true;
}

// 獲取本地時間日期
std::string get_local_datetime()
{
	char ct[80];
	time_t  tt;
	struct tm *tblock;

	tt = time(NULL);
	tblock = localtime(&tt);

	strftime(ct, sizeof(ct), "%Y-%m-%d %H:%M:%S", tblock);
	return ct;
}

// 初始化建立表結構
void Init_Database()
{
	sqlite3* open_db = open_database("./database.db");
	if (open_db != false)
	{
		bool create_table_ref;
		std::string sql =
			"create table TimeDB("
			"uid primary key,"
			"host_time char(128) not null,"
			"cpu_value int not null"
			");";
		char run_sql[1024] = { 0 };
		strcpy(run_sql, sql.c_str());
		exec_sql(open_db, run_sql);
	}
	close_database(open_db);
}

// 批次生成時間字串並插入資料表
void Insert_Test()
{
	sqlite3* open_db = open_database("./database.db");
	for (int x = 0; x < 1000; x++)
	{
		// 獲取本地日期時間
		std::string local_times = get_local_datetime();
		std::string format_string = boost::str(boost::format("insert into TimeDB(uid,host_time,cpu_value) values(%d,'%s',%d);") % x %local_times %x);

		std::cout << "執行SQL: " << format_string << std::endl;

		char run_sql[1024] = { 0 };
		strcpy(run_sql, format_string.c_str());
		insert_data(open_db, run_sql);
		_sleep(1000);
	}
	close_database(open_db);
}

// 查詢時間區間並返回 傳入開始時間與結束時間,過濾出特定的記錄
bool Select_Time_List(sqlite3 *db, std::vector<std::map<std::string, int>> &time_ref, std::string start_time, std::string end_time)
{
	sqlite3_stmt *stmt = 0;
	std::string format_string = boost::str(boost::format("select * from TimeDB where host_time >= '%s' and host_time <= '%s';") % start_time %end_time);

	char run_sql[1024] = { 0 };
	strcpy(run_sql, format_string.c_str());

	int ref = sqlite3_prepare_v2(db, run_sql, -1, &stmt, 0);
	if (ref == SQLITE_OK)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			std::map < std::string, int > ptr;

			// 取出第一個和第二個欄位
			const unsigned char *time_text = sqlite3_column_text(stmt, 1);
			const int cpu_value = sqlite3_column_int(stmt, 2);

			// 放入一個map容器中
			ptr[boost::lexical_cast<std::string>(time_text)] = cpu_value;
			time_ref.push_back(ptr);
		}
		sqlite3_finalize(stmt);
		return true;
	}
	sqlite3_finalize(stmt);
	return false;
}

int main(int argc, char *argv[])
{
	sqlite3* open_db = open_database("./database.db");
	//Init_Database();
	//Insert_Test();

	// 查詢 2023-11-25 19:52:31 - 2023-11-25 19:53:35 區間內的所有的負載情況
	std::vector<std::map<std::string, int>> db_time;
	bool is_true = Select_Time_List(open_db, db_time, "2023-11-25 19:52:31", "2023-11-25 19:53:35");
	if (is_true == true)
	{
		for (int x = 0; x < db_time.size(); x++)
		{
			// 輸出該區間內的資料
			std::map < std::string, int>::iterator ptr;
			for (ptr = db_time[x].begin(); ptr != db_time[x].end(); ptr++)
			{
				std::cout << "時間區間: " << ptr->first << " CPU利用率: " << ptr->second << std::endl;
			}
		}
	}
	std::system("pause");
	return 0;
}

例如程式碼中我們查詢2023-11-25 19:52:31 - 2023-11-25 19:53:35這個區間內的資料資訊,並返回一個map容器給被呼叫者,執行效果如下所示;