Sqlite 基本操作

Sqlite在常规操作中，经常会遇见SQLITE_BUSY错误，特别是存在大量写操作时。所以为了尽可能的减少SQLITE_BUSY，在实际过程中应该采取批操作。例如插入1W条数据，最好使用批量插入而不是单条插入。这样将会导致Sqlite效率大大降低。

执行Sql

sqlite3_exec如果接收了错误码，则一定要调用sqlite3_free，否则将会导致内存泄露

bool  CSqliteDb::execSql(const char *szSql, int(*callback)(void*, int, char**, char**),void *userParam)
{
	if (m_pdb == NULL)
	{
		DebugPrint("StCloudLog>>execSql:db = null\r\n");
		return false;
	}

	int ret = SQLITE_OK;

	char *szError = NULL;
	do
	{	
		ret = sqlite3_exec(m_pdb, szSql, callback, userParam, &szError);
		if (ret != SQLITE_OK && ret != SQLITE_BUSY)
		{
			DebugPrint("StCloudLog>>execSql:%s:ret = %d,error:%s\r\n", szSql, ret, szError == NULL ? "" : szError);
		}
		if (szError != nullptr)
		{
			sqlite3_free(szError);
			szError = NULL;
		}
		if(ret != SQLITE_OK)
		{
			Sleep(10);
		}
	}while (ret == SQLITE_BUSY);

	return ret == SQLITE_OK;
}

批量插入Sql

使用了sqlite3_prepare_xxx函数一定要记得sqlite3_finalize释放sqlite3_stmt ，否则会导致内存泄露。数据库数据往往是utf-8格式，所以存储之前最好转存utf-8之后再入库

bool CSqliteDb::Log(const std::vector<std::shared_ptr<Item>> &vec_logItem)
{
	if (m_pdb == NULL)
	{
		DebugPrint("StCloudLog>>Log:db = null\r\n");
		return false;
	}

	if (vec_logItem.empty())
	{
		return true;
	}

	//使用事务插入
	sqlite3_exec(m_pdb, "begin;", 0, 0, 0);
	sqlite3_stmt * st;
	const char * szSql = "insert into st_log(biz_id,modul,level,ext,message) values(?,?,?,?,?);";
	int ret = sqlite3_prepare_v2(m_pdb, szSql, -1, &st, NULL);
	while (ret == SQLITE_BUSY)
	{
		if (WaitForSingleObject(StLog::instance().getStopEvent(), 1) == WAIT_TIMEOUT)
		{
			ret = sqlite3_prepare_v2(m_pdb, szSql, -1, &st, NULL);
			if (ret != SQLITE_OK && ret != SQLITE_BUSY && ret != SQLITE_DONE)
			{
				DebugPrint("StCloudLog>>insert vec Sql,sqlite3_prepare_v2:%s:ret = %d\r\n", szSql, ret);
			}
			continue;
		}
		break;
	}

	if (ret == SQLITE_OK)
	{
		for (auto &logItem : vec_logItem)
		{
			sqlite3_reset(st);
			std::string biz_id = StringUtil::Easy_AnsiToUtf8(logItem->biz_id);
			sqlite3_bind_text(st, 1, biz_id.c_str(), biz_id.length(), SQLITE_TRANSIENT);

			std::string modul = StringUtil::Easy_AnsiToUtf8(logItem->modul);
			sqlite3_bind_text(st, 2, modul.c_str(), modul.length(), SQLITE_TRANSIENT);

			sqlite3_bind_int(st, 3, logItem->log_level);

			std::string ext = StringUtil::Easy_AnsiToUtf8(logItem->ext);
			sqlite3_bind_text(st, 4, ext.c_str(), ext.length(), SQLITE_TRANSIENT);

			std::string message = StringUtil::Easy_AnsiToUtf8(logItem->message);
			sqlite3_bind_text(st, 5, message.c_str(), message.length(), SQLITE_TRANSIENT);
			ret = sqlite3_step(st);
			//如果ret == SQLITE_BUSY 可以考虑重试sqlite3_step
			//由于是日志服务，暂时不考虑重试
			while (ret == SQLITE_BUSY)
			{
				if (WaitForSingleObject(StLog::instance().getStopEvent(), 1) == WAIT_TIMEOUT)
				{
					ret = sqlite3_step(st);
					if (ret != SQLITE_OK && ret != SQLITE_BUSY && ret != SQLITE_DONE)
					{
						DebugPrint("StCloudLog>>insert vec Sql:%s:ret = %d\r\n", szSql, ret);
					}
					continue;
				}
				break;
			}
		}
		
		sqlite3_finalize(st);
		ret = sqlite3_exec(m_pdb, "commit;", 0, 0, 0);;
		while (ret == SQLITE_BUSY)
		{
			if (WaitForSingleObject(StLog::instance().getStopEvent(), 1) == WAIT_TIMEOUT)
			{
				ret = sqlite3_exec(m_pdb, "commit;", 0, 0, 0);;
				if (ret != SQLITE_OK && ret != SQLITE_BUSY && ret != SQLITE_DONE)
				{
					DebugPrint("StCloudLog>>insert vec Sql:%s:ret = %d\r\n", szSql, ret);
				}
				continue;
			}
			break;
		}
		
	}
	if (ret != SQLITE_OK && ret != SQLITE_DONE)
	{
		DebugPrint("StCloudLog>>insert vec table log:ret = %d\r\n", ret);
	}
	return ret == SQLITE_DONE;

}

查询Sql

const char * szSql = “select * from xx”;
//使用查询回调
execSql(szSql, SelectCallback, NULL);

/*
@param userParam 用户自定义参数
@param colNumber 查询数据的列数量
@param val 查询数据的对应列的值，对应数据库存储格式，一般为utf-8
@param azColName查询数据的对应列的名称
*/
int SelectCallback(void *userParam, int colNumber, char **val, char **azColName)
{

}