repos_util/SQLite3/testsqlite3/DataImpl.cpp

#include "DataImpl.h"
#include <string>
using namespace std;

CDataImpl::CDataImpl(void):m_psqlite3(NULL),m_pszErrmsg(NULL)
{
}

CDataImpl::~CDataImpl(void)
{
}

BOOL CDataImpl::Open()
{
	Close();
	CHAR szpath[MAX_PATH] = {0};
	sprintf(szpath, "E:\\repos_other\\satd.db");
	string strPath = szpath;
	INT nResult = sqlite3_open(strPath.c_str(), &m_psqlite3);
	if ( nResult != SQLITE_OK )
		return FALSE;

	return TRUE;
}

void CDataImpl::Close()
{
	if ( m_psqlite3 )
		sqlite3_close(m_psqlite3);

	m_psqlite3 = NULL;
}

BOOL CDataImpl::ExecteSQL(IN LPCSTR lpSQL)
{
	if ( lpSQL == NULL || lpSQL[0] == '\0' )
	{
#ifdef _DEBUG
		cout << "ExecteSQL：执行语句空！" << endl;
#endif
		return FALSE;
	}

	if(m_psqlite3 == NULL)
		return FALSE;

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, lpSQL, NULL, 0, &psqlite_error);
	if(SQLITE_OK != sqlite_error)
	{
#ifdef _DEBUG
		cout << "ExecteSQL:" << psqlite_error << endl;
#endif
		return FALSE;
	}

	return TRUE;
}

INT CDataImpl::QueryComplieServer(satdDatabase::listServer & list)
{
	if (m_psqlite3 == NULL)
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	int sqlite_error = sqlite3_get_table(m_psqlite3, "select server, user, password from complie_server", &pazResult, &nRow, &nCol, &m_pszErrmsg);
	if (sqlite_error != SQLITE_OK)
	{
#ifdef _DEBUG
		cout << "QueryComplieServer:" << m_pszErrmsg << endl;
#endif
		return -1;
	}

	for (int i = 1; i <= nRow; i++)
	{
		satdDatabase::complieServer *pObj = list.add_servers();
#ifndef USE_UTF8
		pObj->set_server(pazResult[i*nCol + 0]);
		pObj->set_user(pazResult[i*nCol + 1]);
		pObj->set_password(pazResult[i*nCol + 2]);
#else
		// 由Native for SQLite3插入的数据，都是utf8格式;
		pObj->set_server(pazResult[i*nCol + 0]);
		pObj->set_user(pazResult[i*nCol + 1]);
		pObj->set_password(pazResult[i*nCol + 2]);
#endif
	}

	sqlite3_free_table(pazResult);

	return nRow;
}

INT CDataImpl::QueryAutoComplieModel(satdDatabase::listModel & list)
{
	if (m_psqlite3 == NULL)
		return -1;

	INT nRow = 0;
	INT nCol = 0;

	char** pazResult = NULL;
	string strQuery = "select name, soft_suffix, flags,content from model";

	sqlite3_stmt *pStmt = NULL;
	int sqlite_error = sqlite3_prepare_v2(m_psqlite3, strQuery.c_str(), strQuery.size(), &pStmt, NULL);
	if (sqlite_error != SQLITE_OK)
	{
		OutputDebugString(sqlite3_errmsg(m_psqlite3));
		return -1;
	}

	int nTotal = 0;
	int nStrLen = 0;
	const int intSize = sizeof(int);
	string strInfo;
	for (;;)
	{
		sqlite_error = sqlite3_step(pStmt);
		if (sqlite_error == SQLITE_ROW)
		{
			satdDatabase::model *pObj = list.add_models();
			pObj->set_name((const char*)sqlite3_column_text(pStmt, 0));
			pObj->set_softsuffix((const char*)sqlite3_column_text(pStmt, 1));

			const void *pdat = (const char*)sqlite3_column_blob(pStmt, 2);
			nTotal = sqlite3_column_bytes(pStmt, 2);
			pObj->mutable_flags()->ParseFromArray(pdat, nTotal);

			pdat = sqlite3_column_blob(pStmt, 3);
			nTotal = sqlite3_column_bytes(pStmt, 3);
			pObj->mutable_content()->ParseFromArray(pdat, nTotal);
		}
		else if (sqlite_error == SQLITE_DONE)
		{
			break;
		}
		else
		{
			break;
		}
	}

	if (pStmt)
		sqlite3_finalize(pStmt);

	return nRow;
}

BOOL CDataImpl::InsertComplieServer(IN satdDatabase::complieServer & server)
{
	if (m_psqlite3 == NULL)
		return FALSE;

	string strInsert = "INSERT INTO complie_server(server, user, password) VALUES ('";
#ifndef USE_UTF8
	strInsert.append(server.server());
	strInsert.append("','");
	strInsert.append(server.user());
	strInsert.append("','");
	strInsert.append(server.password());
#else
	string str;
	strInsert.append(server.server());
	strInsert.append("','");
	strInsert.append(server.user());
	strInsert.append("','");
	strInsert.append(server.password());
#endif
	strInsert.append("');");

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if (SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("InsertContactsType:%s"), psqlite_error);
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::InsertAutoComplieModel(IN satdDatabase::model & model)
{
	if (m_psqlite3 == NULL)
		return FALSE;

	sqlite3_stmt *pStmt = NULL;
	int nRet = 0;
	string strSql = "insert into model(name,soft_suffix,flags,content) values(?,?,?,?)";
	nRet = sqlite3_prepare_v2(m_psqlite3, strSql.c_str(), strSql.size(), &pStmt, NULL);
	if (nRet != SQLITE_OK)
	{
#ifdef _DEBUG
		OutputDebugString(sqlite3_errmsg(m_psqlite3));
#endif
		return FALSE;
	}

	unsigned char *pfData = NULL;
	unsigned char *pmData = NULL;
	// 插入时，索引是从1开始。查询时，索引是从0开始;
	nRet = sqlite3_bind_text(pStmt, 1, model.name().c_str(), model.name().size(), NULL);
	if (nRet != SQLITE_OK)
	{
#ifdef _DEBUG
		OutputDebugString(sqlite3_errmsg(m_psqlite3));
#endif
		goto clear;
		return FALSE;
	}

	nRet = sqlite3_bind_text(pStmt, 2, model.softsuffix().c_str(), model.softsuffix().size(), NULL);
	if (nRet != SQLITE_OK)
	{
#ifdef _DEBUG
		OutputDebugString(sqlite3_errmsg(m_psqlite3));
#endif
		goto clear;
		return FALSE;
	}

	satdDatabase::listEndFlag *pflags = model.mutable_flags();
	if (pflags != NULL)
	{
		int nSize = pflags->ByteSize();
		pfData = new unsigned char[nSize];
		memset(pfData, 0, nSize);
		pflags->SerializeToArray(pfData, nSize);
		nRet = sqlite3_bind_blob(pStmt, 3, pfData, nSize, SQLITE_STATIC);

		if (nRet != SQLITE_OK)
		{
			goto clear;
			return FALSE;
		}
	}

	satdDatabase::modelContent *pMc = model.mutable_content();
	if (pMc)
	{
		int nSize = pMc->ByteSize();
		pmData = new unsigned char[nSize];
		memset(pmData, 0, nSize);
		pMc->SerializeToArray(pmData, nSize);
		nRet = sqlite3_bind_blob(pStmt, 4, pmData, nSize, SQLITE_STATIC);

		if (nRet != SQLITE_OK)
		{
			goto clear;
			return FALSE;
		}
	}

	nRet = sqlite3_step(pStmt);
	if (nRet != SQLITE_DONE)
	{
		goto clear;
		return FALSE;
	}

clear:
	if (pStmt)
		sqlite3_finalize(pStmt);

	if (pfData)
		delete[]pfData;

	if (pmData)
		delete[]pmData;

	return nRet == SQLITE_DONE;
}

BOOL CDataImpl::UpdateComplieServer(IN satdDatabase::complieServer & server)
{
	if (m_psqlite3 == NULL)
		return FALSE;

	string strInsert = "UPDATE complie_server SET server = '";
#ifndef USE_UTF8
	strInsert.append(server.server());
	strInsert.append("', user = '");
	strInsert.append(server.user());
	strInsert.append("', password = '");
	strInsert.append(server.password());
	strInsert.append("' WHERE server = ");
	strInsert.append(server.server());
	strInsert.append(";");
#else
	string str;
	strInsert.append(server.server());
	strInsert.append("', user = '");
	strInsert.append(server.user());
	strInsert.append("', password = '");
	strInsert.append(server.password());
	strInsert.append("' WHERE server = ");
	strInsert.append(server.server());
	strInsert.append(";");
#endif

	char* psqlite_error = NULL;
	int sqlite_error = sqlite3_exec(m_psqlite3, strInsert.c_str(), NULL, 0, &psqlite_error);
	if (SQLITE_OK != sqlite_error)
	{
		//Global::WriteTextLog(_T("UpdateContactsType:%s"), psqlite_error);
		return FALSE;
	}

	return TRUE;
}

BOOL CDataImpl::UpdateAutoCompliteModel(IN satdDatabase::model & model)
{
	if (m_psqlite3 == NULL)
		return FALSE;

	sqlite3_stmt *pStmt = NULL;
	int nRet = 0;
	char szSql[500] = {0};
	sprintf(szSql, "update model set soft_suffix=?, flags=?, content=? where name = '%s';", model.name().c_str());
	nRet = sqlite3_prepare_v2(m_psqlite3, szSql, strlen(szSql), &pStmt, NULL);
	if (nRet != SQLITE_OK)
	{
#ifdef _DEBUG
		OutputDebugString(sqlite3_errmsg(m_psqlite3));
#endif
		return FALSE;
	}

	char *pData1 = NULL, *pData2 = NULL;
	nRet = sqlite3_bind_text(pStmt, 1, model.softsuffix().c_str(), model.softsuffix().size(), NULL);
	if (nRet != SQLITE_OK)
	{
#ifdef _DEBUG
		OutputDebugString(sqlite3_errmsg(m_psqlite3));
#endif
		goto clear;
		return FALSE;
	}

	satdDatabase::listEndFlag *pflags = model.mutable_flags();
	if (pflags != NULL)
	{
		int nSize = pflags->ByteSize();
		pData1 = new char[nSize];
		memset(pData1, 0, nSize);
		pflags->SerializeToArray(pData1, nSize);
		nRet = sqlite3_bind_blob(pStmt, 2, pData1, nSize, NULL);

		if (nRet != SQLITE_OK)
		{
#ifdef _DEBUG
			OutputDebugString(sqlite3_errmsg(m_psqlite3));
#endif
			goto clear;
			return FALSE;
		}
	}

	satdDatabase::modelContent *pMc = model.mutable_content();
	if (pMc)
	{
		int nSize = pMc->ByteSize();
		pData2 = new char[nSize];
		memset(pData2, 0, nSize);
		pMc->SerializeToArray(pData2, nSize);
		nRet = sqlite3_bind_blob(pStmt, 3, pData2, nSize, NULL);

		if (nRet != SQLITE_OK)
		{
#ifdef _DEBUG
			OutputDebugString(sqlite3_errmsg(m_psqlite3));
#endif
			goto clear;
			return FALSE;
		}
	}

	if (nRet != SQLITE_OK)
	{
		goto clear;
		return FALSE;
	}

	nRet = sqlite3_step(pStmt);
	if (nRet != SQLITE_DONE)
	{
		goto clear;
		return FALSE;
	}

clear:
	if (pStmt)
		sqlite3_finalize(pStmt);

	if (pData1) delete[]pData1;
	if (pData2) delete[]pData2;

	return nRet == SQLITE_OK;
}