OTS/OTSCPP/OTSSQLiteDll/SQLiteStore.cpp

#include "stdafx.h"
#include "SQLiteStore.h"
#include "SQLiteQuery.h"
#include "DBStoreFile.h"

namespace OTSSQLITE
{

	CSQLiteStore::CSQLiteStore()
	{
		/*m_pDatastoreFile = a_pDatastoreFile;*/
	}

	CSQLiteStore::~CSQLiteStore(void)
	{
	}

	BOOL CSQLiteStore::Open(LPCTSTR a_sFileName, const BOOL a_bForce /*= TRUE*/)
	{
		Close();

		ASSERT(a_sFileName);
		if (!a_sFileName)
		{
			return FALSE;
		}

		if (!a_bForce)
		{
			if (!Exists(a_sFileName))
			{
				LogErrorTrace(__FILE__,__LINE__,_T("Open SQLite datastore failed: %s not exists."), a_sFileName);
				return FALSE;
			}
		}

		return OpenOrCreate(a_sFileName);
	}

	BOOL CSQLiteStore::Create(LPCTSTR a_sFileName, const BOOL a_bOverwrite /*= FALSE*/)
	{
		Close();

		ASSERT(a_sFileName);
		if (!a_sFileName)
		{
			return FALSE;
		}

		if (!a_bOverwrite)
		{
			if (Exists(a_sFileName))
			{
				LogErrorTrace(__FILE__,__LINE__,_T("Create SQLite datastore failed: %s exists."), a_sFileName);
				return FALSE;
			}
		}

		if (!DeleteAFile(a_sFileName))
		{
			return FALSE;
		}

		return OpenOrCreate(a_sFileName);
	}

	BOOL CSQLiteStore::Save(LPCTSTR a_sFileName, const BOOL a_bOverwrite /*= FALSE*/)
	{
		ASSERT(a_sFileName);
		if (!a_sFileName)
		{
			return FALSE;
		}
		UNREFERENCED_PARAMETER(a_bOverwrite);

		ASSERT(FALSE);
		return FALSE;
	}

	BOOL CSQLiteStore::OpenOrCreate(LPCTSTR a_sFileName)
	{
		ASSERT(a_sFileName);
		if (!a_sFileName)
		{
			return FALSE;
		}

		auto dbPtr = GetDBPtr();
		ASSERT(dbPtr);
		if (!dbPtr)
		{
			return FALSE;
		}

		try
		{
			dbPtr->open(a_sFileName);
		}
		catch (CppSQLite3Exception& ex)
		{
			LogErrorTrace(__FILE__,__LINE__,GetExceptionErrorString(ex));

			LogErrorTrace(__FILE__, __LINE__, _T("Can't open data base."));

			ASSERT(FALSE);
			return FALSE;
		}

		if (IsOpened())
		{
			m_sDatastorePathName = a_sFileName;

			return TRUE;
		}

		ASSERT(FALSE);
		return FALSE;
	}

	std::vector<CString> CSQLiteStore::GetTableList()
	{
		std::vector<CString> tableList;

		CString sSQLCommand("SELECT name FROM sqlite_master WHERE type = \'table\';");
		auto query = QueryByCommand(sSQLCommand);
		if (!query || !query->IsValid())
		{
			LogErrorTrace(__FILE__,__LINE__,_T("Invalid table query command: %s"), (LPCTSTR)sSQLCommand);
			ASSERT(FALSE);

			return tableList;
		}

		while (!query->IsEOF())
		{
			CString sValue = query->GetColStringValue(0);
			tableList.push_back(sValue);
			query->NextRow();
		}

		LogTrace(_T("There are total %d tables inside %s"), (int)tableList.size(), (LPCTSTR)GetFileName());

		return tableList;
	}

	BOOL CSQLiteStore::IsTableExists(LPCTSTR a_sTableName)
	{
		ASSERT(a_sTableName);
		if (!a_sTableName)
		{
			return FALSE;
		}

		return GetDBPtr()->tableExists(a_sTableName) ? TRUE : FALSE;
	}

	CDBQueryBasePtr CSQLiteStore::QueryByTableName(LPCTSTR a_sTableName, LPCTSTR a_sOrderColumnName )
	{
		/*if (!IsTableExists(a_sTableName))
		{
			ASSERT(FALSE);
			return (CDBQueryBasePtr());
		}*/

		CString sSQLCommand;
		if (a_sOrderColumnName)
		{
			sSQLCommand.Format(_T("SELECT * FROM \'%s\' ORDER BY \'%s\'"), a_sTableName, a_sOrderColumnName);
		}
		else
		{
			sSQLCommand.Format(_T("SELECT * FROM \'%s\'"), a_sTableName);
		}

		return QueryByCommand(sSQLCommand);
	}

	CDBQueryBasePtr CSQLiteStore::QueryByCommand(LPCTSTR a_sQueryString)
	{
		if (!IsOpened() || !a_sQueryString)
		{
			ASSERT(FALSE);
			return (CDBQueryBasePtr());
		}

		CDBQueryBasePtr retPtr;

		try
		{
			retPtr = CDBQueryBasePtr(new CSQLiteQuery(GetDBPtr()->execQuery(a_sQueryString)));
		}
		catch (CppSQLite3Exception& ex)
		{
			LogErrorTrace(__FILE__,__LINE__,GetExceptionErrorString(ex));
			LogErrorTrace(__FILE__,__LINE__,_T("Query command: %s"), a_sQueryString);
			ASSERT(FALSE);
			return (CDBQueryBasePtr());
		}

		return retPtr;
	}

	BOOL CSQLiteStore::DeleteTable(LPCTSTR a_sTableName)
	{
		if (!IsTableExists(a_sTableName))
		{
			return TRUE;
		}

		CString sSQLCommand;
		sSQLCommand.Format(_T("DROP TABLE \'%s\'"), a_sTableName);

		return RunCommand(sSQLCommand);
	}

	BOOL CSQLiteStore::RunCommand(LPCTSTR a_sCommandString, const BOOL a_bIgnoreExist /*= FALSE*/)
	{
		if (!IsOpened())
		{
			ASSERT(FALSE);
			return FALSE;
		}

		int nRet = SQLITE_OK;
		try
		{
			nRet = GetDBPtr()->execDML(a_sCommandString);
			
		}
		catch (CppSQLite3Exception& ex)
		{
			if (a_bIgnoreExist)
			{
				if (ex.GetErrorCode() == SQLITE_ERROR)
				{
					CString sErrorMessage = ex.GetErrorMessage();

					if (sErrorMessage.MakeUpper().Find(_T("UNIQUE")) >= 0)
					{
						return TRUE;
					}
				}
			}

			nRet = ex.GetErrorCode();

			LogErrorTrace(__FILE__,__LINE__,GetExceptionErrorString(ex));
		}

		BOOL bRet = (nRet == SQLITE_OK);
		ASSERT(bRet);
		return bRet;


	}
	//函数功能：向表中插入BLOB数据
//参数：    sqlite3 *db[IN]                               -- 数据库操作指针
//          const TestInfoStruct* info[IN]                -- 要插入的信息
//          const int recordId[IN]                        -- 记录ID
//返回值：  bool                                          -- 函数执行成功，则返回true；否则返回false
	BOOL CSQLiteStore:: InsertBlobData( CString sqlStr, const DWORD* xrayData)
	{
		/*char *zErrMsg = 0;
		char sql[1000];*/
		//char * xrayD = xrayData;
	/*	int rc;*/

		sqlite3_stmt *stmt;
		//sprintf_s(sql, "insert into Student_Blob values('%d',?);", recordId);
		auto db1 = this->GetDBPtr();
		sqlite3_prepare(db1->m_pDB, sqlStr, (int)strlen(sqlStr), &stmt, 0);
		{
			sqlite3_bind_blob(stmt, 1, xrayData, sizeof(xrayData), NULL);
			sqlite3_step(stmt);
		}

		sqlite3_finalize(stmt);
		return true;
	}
	

	BOOL CSQLiteStore::Commit()
	{
		if (RunCommand(_T("COMMIT;")))
		{
			return TRUE;
		}

		ASSERT(FALSE);
		return FALSE;
	}

	BOOL CSQLiteStore::BeginTransaction()
	{
		if (RunCommand(_T("begin transaction;")))
		{
			return TRUE;
		}

		ASSERT(FALSE);
		return FALSE;
	}
	BOOL CSQLiteStore::CloseSynchronous()
	{
		CString sCommand;
		sCommand.Format(_T("PRAGMA synchronous = OFF; "), 0, 0, 0);
		if (RunCommand(sCommand))
		{
			return TRUE;
		}

		ASSERT(FALSE);
		return FALSE;
	}



	BOOL CSQLiteStore::CommitTransaction()
	{
		if (RunCommand(_T("commit transaction;")))
		{
			return TRUE;
		}

		ASSERT(FALSE);
		return FALSE;
	}

	long CSQLiteStore::GetLastRowId(LPCTSTR a_sTableName /*= nullptr*/)
	{
		if (!IsOpened())
		{
			ASSERT(FALSE);
			return -1;
		}

		long nRet = -1;
		if (!a_sTableName)
		{
			nRet = (long)GetDBPtr()->lastRowId();
		}
		else
		{
			CString sSQLCommand;
			sSQLCommand.Format(_T("SELECT max(ROWID) FROM %s"), a_sTableName);
			nRet = GetDBPtr()->execScalar(sSQLCommand);
		}

		return nRet;
	}

	void CSQLiteStore::Close()
	{
		m_sDatastorePathName = _T("");

		try
		{
			if (m_dbPtr)
			{
				m_dbPtr->close();
				m_dbPtr.reset();
			}
		}
		catch (CppSQLite3Exception& ex)
		{
			LogErrorTrace(__FILE__,__LINE__,GetExceptionErrorString(ex));
			ASSERT(FALSE);
		}
	}

	CString CSQLiteStore::GetExceptionErrorString(CppSQLite3Exception& a_ex)
	{
		auto nErrorCode = a_ex.GetErrorCode();

		CString sErrMessage;
		sErrMessage.Format(_T("Database Exception: return = %d(%s), error message is %s"),
			nErrorCode,
			a_ex.GetErrorCodeAsString(nErrorCode),
			a_ex.GetErrorMessage());

		return sErrMessage;
	}

	BOOL CSQLiteStore::InsertBlobData(char* szSql, const void* pBlobData, int iBlobDataLen)
	{
		sqlite3_stmt *pStmt;

		pStmt = NULL;

		if (sqlite3_prepare_v2(m_dbPtr->m_pDB, szSql, -1, &pStmt, NULL) != SQLITE_OK)
		{
			ASSERT(FALSE);
			return FALSE;
		}

		if (NULL != pStmt)
		{
			if (sqlite3_bind_blob(pStmt, 1, pBlobData, iBlobDataLen, SQLITE_STATIC) != SQLITE_OK)
			{
				sqlite3_finalize(pStmt);
				ASSERT(FALSE);
				return FALSE;
			}
		}

		if (sqlite3_step(pStmt) != SQLITE_DONE)
		{
			sqlite3_finalize(pStmt);
			ASSERT(FALSE);
			return FALSE;
		}
		sqlite3_finalize(pStmt);

		//sqlite3_exec(m_dbPtr->m_pDB, "COMMIT", NULL, NULL, NULL);

		return TRUE;
	}
	CDBStoreBasePtr CreateNewSQLiteStore()
	{
		return CDBStoreBasePtr(new CSQLiteStore());

	}
}