int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

int sqlite3_close(sqlite3*);

// 打開(kāi)數(shù)據(jù)庫(kù)并返回句柄
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;
}

// 關(guān)閉數(shù)據(jù)庫(kù)
bool close_database(sqlite3 *db)
{
  int ref = sqlite3_close(db);
  if (ref == SQLITE_OK)
    return true;
  return false;
}

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 */
);

int callback(
  void* callback_param, /* 參數(shù)，由 sqlite3_exec 傳遞給回調(diào)函數(shù) */
  int num_columns,      /* 結(jié)果集中的列數(shù) */
  char** column_values,  /* 指向結(jié)果集中當(dāng)前行的列值的數(shù)組 */
  char** column_names    /* 指向結(jié)果集中列名的數(shù)組 */
);

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 */
);

int sqlite3_step(sqlite3_stmt*);

const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);

int sqlite3_column_int(sqlite3_stmt*, int iCol);

int sqlite3_finalize(sqlite3_stmt *pStmt);

// 執(zhí)行SQL語(yǔ)句
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;
}

// 插入數(shù)據(jù)
bool insert_data(sqlite3 *db, char *sql)
{
  sqlite3_stmt *stmt = 0;

  // 插入前檢查語(yǔ)句合法性, -1自動(dòng)計(jì)算SQL長(zhǎng)度
  int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
  if (ref == SQLITE_OK)
  {
    sqlite3_step(stmt);       // 執(zhí)行語(yǔ)句
    sqlite3_finalize(stmt);   // 清理語(yǔ)句句柄
    return true;
  }
  sqlite3_finalize(stmt);
  return false;
}

// 查詢數(shù)據(jù)集
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)
  {
    // 每調(diào)一次sqlite3_step()函數(shù)，stmt就會(huì)指向下一條記錄
    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;
}

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"
      ")";

    // 運(yùn)行創(chuàng)建表操作
    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;
}

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

    // 運(yùn)行插入記錄
    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;
}

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;
    }

    // 查詢數(shù)據(jù)
    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;
}

#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;

// 獲取本地時(shí)間日期
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;
}

// 初始化創(chuàng)建表結(jié)構(gòu)
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);
}

// 批量生成時(shí)間字符串并插入數(shù)據(jù)表
void Insert_Test()
{
	sqlite3* open_db = open_database("./database.db");
	for (int x = 0; x < 1000; x++)
	{
		// 獲取本地日期時(shí)間
		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 << "執(zhí)行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;
}

#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;

// 打開(kāi)數(shù)據(jù)庫(kù)并返回句柄
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;
}

// 關(guān)閉數(shù)據(jù)庫(kù)
bool close_database(sqlite3 *db)
{
	int ref = sqlite3_close(db);
	if (ref == SQLITE_OK)
		return true;
	return false;
}

// 執(zhí)行SQL語(yǔ)句
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;
}

// 插入數(shù)據(jù)
bool insert_data(sqlite3 *db, char *sql)
{
	sqlite3_stmt *stmt = 0;

	// 插入前檢查語(yǔ)句合法性, -1自動(dòng)計(jì)算SQL長(zhǎng)度
	int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
	if (ref == SQLITE_OK)
	{
		sqlite3_step(stmt);       // 執(zhí)行語(yǔ)句
		sqlite3_finalize(stmt);   // 清理語(yǔ)句句柄
		return true;
	}
	sqlite3_finalize(stmt);
	return false;
}

// 查詢數(shù)據(jù)集
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)
	{
		// 每調(diào)一次sqlite3_step()函數(shù)，stmt就會(huì)指向下一條記錄
		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;
}

// 獲取本地時(shí)間日期
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;
}

// 初始化創(chuàng)建表結(jié)構(gòu)
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);
}

// 批量生成時(shí)間字符串并插入數(shù)據(jù)表
void Insert_Test()
{
	sqlite3* open_db = open_database("./database.db");
	for (int x = 0; x < 1000; x++)
	{
		// 獲取本地日期時(shí)間
		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 << "執(zhí)行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);
}

// 查詢時(shí)間區(qū)間并返回 傳入開(kāi)始時(shí)間與結(jié)束時(shí)間,過(guò)濾出特定的記錄
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;

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

			// 放入一個(gè)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 區(qū)間內(nèi)的所有的負(fù)載情況
	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++)
		{
			// 輸出該區(qū)間內(nèi)的數(shù)據(jù)
			std::map < std::string, int>::iterator ptr;
			for (ptr = db_time[x].begin(); ptr != db_time[x].end(); ptr++)
			{
				std::cout << "時(shí)間區(qū)間: " << ptr->first << " CPU利用率: " << ptr->second << std::endl;
			}
		}
	}
	std::system("pause");
	return 0;
}