数据库文件模板

头文件：

/******************************************************************************** @file        * @version     v1.0.0* @copyright   COPYRIGHT ; 2022 CSG* @author      Kaguya* @date        2022-5-7* @brief* @attention* Modification History* DATE         DESCRIPTION* ------------------------* - 2022-5-7  Ywy Created
*******************************************************************************/
#include 
#include 
#include "sqlite3.h"
#include 
#include 
#define SCREEN_DB_NAME ("SimpleCustumConf.db")typedef struct S_SCREENDBINFO{int m_enable;       //使能int m_id; 			//IDint m_defaultLight; //默认亮度int m_type;
}s_screenDbInfo;
struct UNCFG
{s_screenDbInfo m_screenInfo;
};
typedef struct S_DB
{int iId;struct UNCFG unCfg;
}SDB;#define DB_INVALID_OPERATION (-1)
#define DB_OPEN_OK (0)
#define DB_OK (0)
#define DB_RET_DEF_UNKOWN (-2)
#define DB_RET_VECTOR_EMPTY (1)
//事务操作类型
class C_SimpleDbObj
{
public://构造函数C_SimpleDbObj(const char* path) : m_dbHandle(NULL){m_dbPath = path;printf("Path[%s]\n", m_dbPath.c_str());}//析构函数~C_SimpleDbObj(){CloseDb();}//初始化数据库int OnInit();//关闭数据库void CloseDb();//删除所有的站点变量int DelAllSiteVarConf();//删除一条站点int DelOneSiteVarConf(int id);//添加一条站点int AddOneConf(S_DB*);//修改一条站点int SetOneConf(int iId, S_DB* pCfg);//获取所有的站点配置int GetAllConf(int* cnt,S_DB** pSiteCfgs);//事务开启void TransActionBegin();//事务提交void TransActionCommit();//事务回滚void TransActionRollback();
private:sqlite3* m_dbHandle;    //数据库操作句柄std::mutex m_dbOpMtx;   //数据库句柄操作锁std::string m_dbPath;   //数据库文件所在的绝对路径
};

源文件

/******************************************************************************** @file        * @version     v1.0.0* @copyright   COPYRIGHT ; 2022 CSG* @author      Kaguya* @date        2022-5-7* @brief* @attention* Modification History* DATE         DESCRIPTION* ------------------------* - 2022-5-7  Ywy Created
*******************************************************************************/
#include "SimpleDb.h"//初始化数据库
int C_SimpleDbObj::OnInit()
{std::string strDbNm,strTbNm;std::lock_guard<std::mutex> lck(m_dbOpMtx);//打开数据库strDbNm.append(m_dbPath);strDbNm.append(SCREEN_DB_NAME);//参数表strTbNm.append("CREATE TABLE IF NOT EXISTS T_SITEPARA""(iId,""CfgBin BLOB);");//打开数据库文件if(SQLITE_OK != sqlite3_open(strDbNm.c_str(), &m_dbHandle)){VM_ERROR("db[%s] open failed", strDbNm.c_str());return DB_INVALID_OPERATION;}VM_INFO("Db[%s] Opened", strDbNm.c_str());//创建表if(SQLITE_OK != sqlite3_exec(m_dbHandle,strTbNm.c_str(),\nullptr,nullptr,nullptr)){VM_ERROR("sqlite3_exec failed, errmsg[%s]!",\sqlite3_errmsg(m_dbHandle));(void)sqlite3_close(m_dbHandle);m_dbHandle = NULL;return DB_INVALID_OPERATION;}VM_INFO("Tb[T_SITEPARA] Inited");return DB_OPEN_OK;
}
//关闭数据库
void C_SimpleDbObj::CloseDb()
{std::lock_guard<std::mutex> lck(m_dbOpMtx);if(m_dbHandle){sqlite3_close(m_dbHandle);m_dbHandle = NULL;}
}
//删除所有的数据
int C_SimpleDbObj::DelAllSiteVarConf()
{std::lock_guard<std::mutex> lck(m_dbOpMtx);if(!m_dbHandle){printf("ColPluginDb Not Open\n");return DB_INVALID_OPERATION;}if(SQLITE_OK != sqlite3_exec(m_dbHandle, "DELETE FROM T_SITEPARA;",nullptr,nullptr,nullptr)){printf("sqlite3_exec failed, errmsg[%s]!\n",  sqlite3_errmsg(m_dbHandle));return DB_INVALID_OPERATION;}return DB_OK;
}
//删除一条的数据
int C_SimpleDbObj::DelOneSiteVarConf(int id)
{int ret = OSI_OK, rc = 0;char sql[256] = {0};snprintf(sql, sizeof(sql), "delete from T_SITEPARA where iId = %d;",id);rc = sqlite3_exec(m_dbHandle, sql, nullptr, nullptr, nullptr);if (rc != SQLITE_OK){printf("sqlite3_exec failed, errmsg[%s]!\n",  sqlite3_errmsg(m_dbHandle));ret = OSI_ERROR;}return ret;
}//添加一条数据
int C_SimpleDbObj::AddOneConf(S_DB* pCfg)
{int iRet = DB_RET_DEF_UNKOWN;std::string strSql;sqlite3_stmt *pStmt = nullptr;std::lock_guard<std::mutex> lck(m_dbOpMtx);if(!m_dbHandle){printf("ColPluginDb Not Open\n");return DB_INVALID_OPERATION;}strSql.append("INSERT INTO T_SITEPARA (iId,CfgBin) VALUES(?,?);");if (SQLITE_OK != sqlite3_prepare(m_dbHandle,strSql.c_str(),-1,&pStmt,NULL)){printf("sqlite3_prepare(%s) failed, errmsg[%s]!\n", strSql.c_str(), \sqlite3_errmsg(m_dbHandle));iRet = DB_INVALID_OPERATION;goto done;}sqlite3_bind_int(pStmt,1,pCfg->iId);sqlite3_bind_blob(pStmt,2,&pCfg->unCfg,\sizeof(pCfg->unCfg),NULL);if(SQLITE_DONE != sqlite3_step(pStmt)){printf("(%s) failed, errmsg[%s]! \n ",  \strSql.c_str(), sqlite3_errmsg(m_dbHandle));iRet = DB_INVALID_OPERATION;goto done;}iRet = DB_OK;
done:if(pStmt){sqlite3_finalize(pStmt);}return iRet;
}int C_SimpleDbObj::SetOneConf(int iId, S_DB* pCfg)
{int iRet = DB_RET_DEF_UNKOWN;std::string strSql;sqlite3_stmt *pStmt = nullptr;std::lock_guard<std::mutex> lck(m_dbOpMtx);if(!m_dbHandle){printf("ColPluginDb Not Open\n");return DB_INVALID_OPERATION;}strSql.append("REPLACE INTO T_SITEPARA (iId,CfgBin) VALUES(?,?);");if (SQLITE_OK != sqlite3_prepare(m_dbHandle,strSql.c_str(),-1,&pStmt,NULL)){printf("sqlite3_prepare(%s) failed, errmsg[%s]!\n", strSql.c_str(), \sqlite3_errmsg(m_dbHandle));iRet = DB_INVALID_OPERATION;goto done;}sqlite3_bind_int(pStmt,1,pCfg->iId);sqlite3_bind_blob(pStmt,2,&pCfg->unCfg,\sizeof(pCfg->unCfg),NULL);if(SQLITE_DONE != sqlite3_step(pStmt)){printf("(%s) failed, errmsg[%s]!\n	",	\strSql.c_str(), sqlite3_errmsg(m_dbHandle));iRet = DB_INVALID_OPERATION;goto done;}iRet = DB_OK;
done:if(pStmt){sqlite3_finalize(pStmt);}return iRet;
}//获取所有的配置
int C_SimpleDbObj::GetAllConf(int* cnt,S_DB** pCfgs)
{char **presult = nullptr;int iRet = DB_RET_DEF_UNKOWN;int rown = 0, num = 0;char  *zErr = nullptr;std::string strSql;sqlite3_stmt *pStmt = nullptr;S_DB* pSCfg = nullptr;std::lock_guard<std::mutex> lck(m_dbOpMtx);if(!m_dbHandle){printf("Db Not Open\n");return DB_INVALID_OPERATION;}strSql.append("select * from T_SITEPARA;");iRet = sqlite3_get_table(m_dbHandle,strSql.c_str(), &presult, \&rown, NULL, &zErr);sqlite3_free_table(presult);if(SQLITE_OK != iRet){if (NULL != zErr){printf("SQL err :%s\n",zErr);sqlite3_free(zErr);}iRet = DB_INVALID_OPERATION;goto done;}*cnt = rown;if(rown <= 0){printf("conf is empty:%d\n",rown);iRet = DB_RET_VECTOR_EMPTY;goto done;}if (SQLITE_OK != sqlite3_prepare(m_dbHandle,strSql.c_str(),-1,&pStmt,NULL)){printf("sqlite3_prepare(%s) failed, errmsg[%s]!\n", strSql.c_str(), \sqlite3_errmsg(m_dbHandle));iRet = DB_INVALID_OPERATION;goto done;}pSCfg = (S_DB*)malloc(rown*sizeof(S_DB));if(!pSCfg){printf("new failed, errno:%d\n",errno);iRet = DB_INVALID_OPERATION;goto done;}memset_s((char*)pSCfg,rown*sizeof(S_DB),0);iRet = sqlite3_step(pStmt);while(SQLITE_ROW == iRet){pSCfg[num].iId    =  sqlite3_column_int(pStmt,0);if(sizeof(pSCfg[num].unCfg) == sqlite3_column_bytes(pStmt,1)){memcpy_s(&pSCfg[num].unCfg,sizeof(S_DB),sqlite3_column_blob(pStmt,1),sizeof(S_DB));}++num;iRet = sqlite3_step(pStmt);}printf("site cnt:%d\n",num);iRet = DB_OK;done:if(pStmt){sqlite3_finalize(pStmt);pStmt = nullptr;}if(DB_OK == iRet){*pCfgs= pSCfg;}else{printf("*pCfgs = nullptr\n");*pCfgs = nullptr;if(pSCfg){free(pSCfg);}}return iRet;
}//事务开启
void C_SimpleDbObj::TransActionBegin()
{std::lock_guard<std::mutex> lck(m_dbOpMtx);if(!m_dbHandle){printf("ColPluginDb Not Open\n");return;}if (SQLITE_OK != sqlite3_exec(m_dbHandle, "BEGIN TRANSACTION", nullptr, nullptr, nullptr)){printf("sqlite3_exec failed, errmsg[%s]!\n",sqlite3_errmsg(m_dbHandle));return ;}return;
}
//事务提交
void C_SimpleDbObj::TransActionCommit()
{std::lock_guard<std::mutex> lck(m_dbOpMtx);if(!m_dbHandle){printf("ColPluginDb Not Open\n");return;}if (SQLITE_OK != sqlite3_exec(m_dbHandle, "COMMIT TRANSACTION", nullptr, nullptr, nullptr)){printf("sqlite3_exec failed, errmsg[%s]!\n",sqlite3_errmsg(m_dbHandle));return ;}return;
}
//事务回滚
void C_SimpleDbObj::TransActionRollback()
{std::lock_guard<std::mutex> lck(m_dbOpMtx);if(!m_dbHandle){printf("ColPluginDb Not Open\n);return;}if (SQLITE_OK != sqlite3_exec(m_dbHandle, "ROLLBACK TRANSACTION", nullptr, nullptr, nullptr)){printf("sqlite3_exec failed, errmsg[%s]!\n",sqlite3_errmsg(m_dbHandle));return ;}return;
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！