dcapp/datacenter/inc/Database.h

/**
 * @文件路径: \dcapp\datacenter\inc\Database.h
 * @作者名称: zjp
 * @文件版本: V1.0.0
 * @创建日期: 2025-02-21 15:16:45
 * @简要说明:
 * @编辑时间: 2025-02-28 10:01:15
 * @Copyright: 2025 Shenzhen ShenBao Electrical instruments CO.,LTD. All Rights Reserved.
 */

#include <iostream>
#include <common/inc/sqlite3.h>
#include <iostream>
#include <string.h>
#include <string>
#include <map>
#include <vector>
#include <sstream>
#include <sys/stat.h>

using namespace std;

#define DBNAME "test.db"
#define FILEBACKUP "/opt/bak/bak.sql"
#define CMDBACKUP_HIS "mv /opt/bak/bak.sql /opt/bak/bak.sql.bak"

class Database
{
public:
    Database()
    {
        if (sqlite3_open(DBNAME, &db) != SQLITE_OK)
        {
            std::cout << "Error opening database: " << sqlite3_errmsg(db) << std::endl;
        }
    }

    ~Database()
    {
        if (db)
        {
            sqlite3_close(db);
        }
    }

    int ExecSQL(std::string sql)
    {
        char *errmsg;
        int nResult = sqlite3_exec(db, sql.c_str(), 0, 0, &errmsg);
        if (nResult != SQLITE_OK)
        {
            // sqlite3_close(db);
            cout << errmsg << " code:" << nResult << endl;
            sqlite3_free(errmsg);
            return (-1);
        }
        return 0;
    }
    int Query(string sql, int *nRow, int *nCol, char ***pResult)
    {
        char *errmsg;
        int nResult = sqlite3_get_table(db, sql.c_str(), pResult, nRow, nCol, &errmsg);
        if (nResult != SQLITE_OK)
        {
            // sqlite3_close(db);
            cout << errmsg << " code:" << nResult << endl;
            sqlite3_free(errmsg);
            return (-1);
        }
        return 0;
    }

    int DbOpen(const string dbFilePath)
    {
        if (sqlite3_open(dbFilePath.c_str(), &db) != SQLITE_OK)
        {
            std::cout << "Error opening database." << std::endl;
            sqlite3_close(db);
            return (-1);
        }
        return 0;
    }

    sqlite3 *GetHandle(void)
    {
        return db;
    }

    int DbClose(void)
    {
        if (sqlite3_close(db) != SQLITE_OK)
        {
            std::cout << "Error closing database." << std::endl;
            return (-1);
        }
        return (0);
    }

    int BackupParam(void) // 备份参数
    {
        int rtn = -1;
        std::cout << "start backup database..." << std::endl;

        system(CMDBACKUP_HIS);

        // system("./sqlite shb3104.db '.dump f_param' > /opt/bak.sql");
        // system("./sqlite shb3104.db '.dump f_param_def' >> /opt/bak.sql");

        struct stat statbuf;
        if (stat(FILEBACKUP, &statbuf) == 0)
        {
            if (statbuf.st_size > 0) // 备份参数失败
            {
                std::cout << "backup param ok" << std::endl;
                rtn = 0;
            }
        }
        return rtn;
    }

    int CheckTablesCount(void) // 检查表的总数量
    {
        int ret = 0;
        char sql[512];
        sqlite3_stmt *stmt;

        snprintf(sql, sizeof(sql), "SELECT count(*) FROM sqlite_master WHERE type='table' AND name in ('Users','tbl_data');");

        if (sqlite3_prepare(db, sql, -1, &stmt, NULL) == SQLITE_OK)
        {
            if (sqlite3_step(stmt) == SQLITE_ROW)
            {
                const unsigned char *result = sqlite3_column_text(stmt, 0);
                if (!result)
                    ret = atoi((const char *)result);
            }
        }
        sqlite3_finalize(stmt);
        return ret;
    }

    int CheckDatabase(void) // 执行整个库的完全性检查
    {
        int rtn = -1;
        sqlite3_stmt *integrity = NULL;

        if (sqlite3_prepare_v2(db, "PRAGMA quick_check;", -1, &integrity, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(integrity) == SQLITE_ROW)
            {
                const unsigned char *result = sqlite3_column_text(integrity, 0);
                if (result && strcmp((const char *)result, (const char *)"ok") == 0)
                {
                    rtn = 0;
                    std::cout << __func__ << " ok" << std::endl;
                    break;
                }
            }
            sqlite3_finalize(integrity);
        }
        return rtn;
    }

    void ReCreate(void) // 重建数据
    {
        int flag_bak = 0;
        struct stat statbuf;
        char *zErrMsg = 0;
        int rc;

        if (stat(FILEBACKUP, &statbuf) == 0)
        {
            if (statbuf.st_size > 0) // 备份参数失败
            {
                flag_bak = 1;
            }
        }

        DbClose(); // 关闭现有连接
        system("sync");
        DbOpen(DBNAME);

        // 从备份文件中读取SQL命令并执行
        std::ifstream backupFile(FILEBACKUP);
        std::stringstream buffer;
        buffer << backupFile.rdbuf();
        std::string sqlCommands = buffer.str();
        rc = sqlite3_exec(db, sqlCommands.c_str(), NULL, 0, &zErrMsg);
        if (rc != SQLITE_OK)
        {
            std::cerr << "SQL错误: " << zErrMsg << std::endl;
            sqlite3_free(zErrMsg);
        }
        else
        {
            std::cout << "数据库恢复成功" << std::endl;
        }

        DbClose(); // 关闭现有连接
        system("sync");

        // system("reboot");
        return;
    }

private:
    sqlite3 *db;
};