sqliteUtils

最新推荐文章于 2024-03-26 09:32:12 发布

lxzlife

最新推荐文章于 2024-03-26 09:32:12 发布

阅读量85

点赞数

分类专栏： sqlite 文章标签： c++ sql

本文链接：https://blog.csdn.net/weixin_45050132/article/details/132364966

版权

sqlite 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

int sqliteUtils_checkAvaliable(sqlite3 *pDb) {
	if (!pDb) {
		return SQLITE_ERROR;
	}

	char sql[256] = { 0 };
	sprintf(sql, "PRAGMA integrity_check;");

	return sqlite3_exec(pDb, sql, nullptr, nullptr, nullptr);
}


#define SQLITE_SYNC_MODE_DATA_ONLY 0
#define SQLITE_SYNC_MODE_NORMAL 1
#define SQLITE_SYNC_MODE_FULL 2
int sqliteUtils_setSyncMode(sqlite3 *pDb, int mode)
{
	if (!pDb) 
	{
		return SQLITE_ERROR;
	}
	if ((mode != SQLITE_SYNC_MODE_DATA_ONLY) && (mode != SQLITE_SYNC_MODE_NORMAL) && (mode != SQLITE_SYNC_MODE_FULL))
	{
		return SQLITE_ERROR;
	}
	char sql[1024] = {0};
	int ret = SQLITE_ERROR;
	if (mode == SQLITE_SYNC_MODE_DATA_ONLY)
	{
		sprintf(sql, "PRAGMA synchronous = OFF;");
	}
	else if (mode == SQLITE_SYNC_MODE_NORMAL)
	{
		sprintf(sql, "PRAGMA synchronous = NORMAL;");
	}
	else if (mode == SQLITE_SYNC_MODE_FULL)
	{
		sprintf(sql, "PRAGMA synchronous = FULL;");
	}
	
	ret = sqlite3_exec(pDb, sql, nullptr, nullptr, nullptr);
	if (SQLITE_OK != ret) {
		fprintf(stderr, "sqlite3_exec sync mode error\n");
		return SQLITE_ERROR;
	}
	fprintf(stdout, "sqlite3_exec sync mode success\n");

	return SQLITE_OK;
}


#define SQLITE_BACKUP 0
#define SQLITE_RECOVERY 1
/*
\brief 数据库文件备份或恢复
\param pDb为已打开的数据库链接
\param zFilename为数据库副本
\param xProgress为进度条回调函数
\param mode==SQLITE_RECOVERY(zFilename->pDb);mode==SQLITE_BACKUP(pDb->zFilename)
*/
int sqliteUtils_backupDb(sqlite3 *pDb, const char *zFilename, void(*xProgress)(int, int), int mode)
{
	if ((!pDb) || (!zFilename)) {
		return SQLITE_ERROR;
	}
	if ((mode != SQLITE_BACKUP) && (mode != SQLITE_RECOVERY)) {
		return SQLITE_ERROR;
	}

	int rc = 0;
	sqlite3 *pFile = 0;
	sqlite3_backup *pBackup = 0;

	rc = sqlite3_open(zFilename, &pFile);
	if (rc != SQLITE_OK) {
		return SQLITE_ERROR;
	}

	rc = sqliteUtils_setSyncMode(pFile, SQLITE_SYNC_MODE_FULL);
	if (rc != SQLITE_OK) {
		(void)sqlite3_close(pFile);
		return SQLITE_ERROR;
	}
    
	
	if (mode == SQLITE_RECOVERY) {
		pBackup = sqlite3_backup_init(pDb, "main", pFile, "main");
	}
	else if (mode == SQLITE_BACKUP) {
		pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");
	}

	if (pBackup)
	{
		do
		{
			rc = sqlite3_backup_step(pBackup, 5);

			if (xProgress != NULL)
			{
				/* Completion = 100% * (pagecount() - remaining()) / pagecount() */
				xProgress(sqlite3_backup_remaining(pBackup), sqlite3_backup_pagecount(pBackup));
			}
				
			if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED)
			{
				sqlite3_sleep(250);
			}
		} while (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED);
		
		(void)sqlite3_backup_finish(pBackup);
	}

	rc = sqlite3_errcode(pFile);
	
	(void)sqlite3_close(pFile);

	return rc;
}

int sqliteUtils_getTables(sqlite3 *pDb, std::vector<std::string>& vec) {
	if (!pDb) {
		return SQLITE_ERROR;
	}
	vec.resize(0);
	char* sql = "SELECT name FROM sqlite_master WHERE type='table' order by name;";
	auto callback = [](void * para,int columnCount,char** columnValue,char** columnName) -> int {
		if(columnCount == 0)	//查询语句在未查到记录
    	{
        	return 0;
    	}

		std::vector<std::string>* vec = (std::vector<std::string>*)para;

		for(int i = 0;i < columnCount; i++)
    	{
			vec->push_back(std::string(columnValue[i]));    // 存储表名
    	}

		return 0;
	};

	return sqlite3_exec(pDb, sql, callback, (void*)&vec, nullptr);
}

/*
 \brief 获取sqlite数据库指定表的行数
 \param pDb:数据库
 \param table:表名
 \retval 非SQLITE_OK失败
*/
int sqliteUtils_getRowOfTable(sqlite3* pDb, std::string& table, int* rows)
{
	if ((!pDb) || (!rows)){
		return SQLITE_ERROR;
	}
	*rows = 0;
	char sql[256] = {0};
	sprintf(sql, "SELECT count(*) FROM %s;", table.c_str());
	std::string cnt;
	int ret = SQLITE_ERROR;

	auto callback = [](void * para,int columnCount,char** columnValue,char** columnName) -> int {
		if(columnCount == 0)	//查询语句在未查到记录
    	{
        	return 0;
    	}

		std::string* row = (std::string*)para;

		*row = columnValue[0];

		return 0;
	};

	ret = sqlite3_exec(pDb, sql, callback, (void*)&cnt, nullptr);
	if (ret != SQLITE_OK) {
		return SQLITE_ERROR;
	}

	*rows = atoi(cnt.c_str());

	return SQLITE_OK;
}


int sqliteUtils_getRowOfDB(sqlite3 *pDb, int* rows)
{
	if ((!pDb) || (!rows)){
		return SQLITE_ERROR;
	}

	*rows = 0;
	int ret = -1;
	int row = 0;
	std::vector<std::string> tables;

	ret = sqliteUtils_getTables(pDb, tables);

	for (int i = 0; i < tables.size(); ++i) {
		if (sqliteUtils_getRowOfTable(pDb, tables[i], &row) != SQLITE_OK) {
			return SQLITE_ERROR;
		}
		*rows += row;
	}

	return SQLITE_OK;
}