OTS/OTSCPP/OTSSQLiteDll/InformationDB.cpp

#include "stdafx.h"
#include "InformationDB.h"
#include "InformationTable.h"
#include<stdlib.h>
namespace OTSSQLITE
{
	CInformationDB::CInformationDB(CDBStoreBasePtr a_datastore)
	{
		m_tableInfo.reset(new CInformationTable());
		myDB = CreateNewSQLiteDB(a_datastore,m_tableInfo);
	}

	CInformationDB::~CInformationDB(void)
	{
	}

	BOOL CInformationDB::DeleteRow(LPCTSTR a_sItemName)
	{
		ASSERT(a_sItemName);
		if (!a_sItemName)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("DeleteRow:invalid item name"));
			return FALSE;
		}

		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("DeleteRow:invalid data base store."));
			return FALSE;
		}

		auto tableInfoPtr = GetTableInfo();
		CString sNameColumnName = tableInfoPtr->GetColumnName((int)CInformationTable::ColumnID::ITEM - (int)CInformationTable::ColumnID::MIN);
		CString sSQLCommand;
		sSQLCommand.Format(_T("DELETE FROM \'%s\' WHERE %s = \'%s\'"), (LPCTSTR)tableInfoPtr->GetTableName(), (LPCTSTR)sNameColumnName, a_sItemName);
		return datastorePtr->RunCommand(sSQLCommand);
	}

	BOOL CInformationDB::InsertRow(LPCTSTR a_sItemName, LPCTSTR a_sItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		ASSERT(a_sItemName);
		if (!a_sItemName)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("InsertRow:invalid item name"));
			return FALSE;
		}
		ASSERT(a_sItemContent);
		if (!a_sItemContent)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("InsertRow:invalid item value"));
			return FALSE;
		}

		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("InsertRow:invalid data base store."));
			return FALSE;
		}

		CString sComment("NULL");
		if (a_sItemComment)
		{
			sComment.Format(_T("%s"), a_sItemComment);
		}

		CString sFormat = GetTableInfo()->GetInsertCommandFormatString();
		CString sSQLCommand;
		sSQLCommand.Format(sFormat,
			a_sItemName,
			a_sItemContent,
			sComment);

		return datastorePtr->RunCommand(sSQLCommand);
	}

	BOOL CInformationDB::UpdateRow(LPCTSTR a_sItemName, LPCTSTR a_sItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		ASSERT(a_sItemName);
		if (!a_sItemName)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("UpdateRow:invalid item name"));
			return FALSE;
		}
		ASSERT(a_sItemContent);
		if (!a_sItemContent)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("UpdateRow:invalid item value"));
			return FALSE;
		}

		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			LogErrorTrace(__FILE__, __LINE__, _T("UpdateRow:invalid data base store."));
			return FALSE;
		}

		CString sComment("NULL");
		if (a_sItemComment)
		{
			sComment.Format(_T("\'%s\'"), a_sItemComment);
		}

		std::vector<int> vColIndexes;
		int nItemIndex = (int)CInformationTable::ColumnID::ITEM - (int)CInformationTable::ColumnID::MIN;
		vColIndexes.push_back((int)CInformationTable::ColumnID::CONTENT - (int)CInformationTable::ColumnID::MIN);
		vColIndexes.push_back((int)CInformationTable::ColumnID::COMMENT - (int)CInformationTable::ColumnID::MIN);

		CString sFormat = GetTableInfo()->GetUpdateCommandFormatString(vColIndexes, nItemIndex);
		CString sSQLCommand;
		sSQLCommand.Format(sFormat,
			a_sItemContent,
			sComment,
			a_sItemName);

		return datastorePtr->RunCommand(sSQLCommand);
	}

	BOOL CInformationDB::InsertStringRow(LPCTSTR a_sItemName, LPCTSTR a_sItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		CString sContent;
		//sContent.Format(_T("\'%s\'"), a_sItemContent);
		sContent.Format(_T("%s"), a_sItemContent);

		if (!InsertRow(a_sItemName, sContent, a_sItemComment))
		{
			LogErrorTrace(__FILE__,__LINE__,_T("Insert string row failed: %s, %s"), a_sItemName, a_sItemContent);
			ASSERT(FALSE);
			return FALSE;
		}

		return TRUE;
	}

	BOOL CInformationDB::UpdateStringRow(LPCTSTR a_sItemName, LPCTSTR a_sItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		CString sContent;
		//sContent.Format(_T("\'%s\'"), a_sItemContent);
		sContent.Format(_T("%s"), a_sItemContent);

		if (!UpdateRow(a_sItemName, sContent, a_sItemComment))
		{
			LogErrorTrace(__FILE__,__LINE__,_T("Update string row failed: %s, %s"), a_sItemName, a_sItemContent);
			ASSERT(FALSE);
			return FALSE;
		}

		return TRUE;
	}

	BOOL CInformationDB::InsertIntegerRow(LPCTSTR a_sItemName, int a_nItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		CString sContent;
		sContent.Format(_T("%d"), a_nItemContent);

		return InsertRow(a_sItemName, sContent, a_sItemComment);
	}

	BOOL CInformationDB::UpdateIntegerRow(LPCTSTR a_sItemName, int a_nItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		CString sContent;
		sContent.Format(_T("%d"), a_nItemContent);

		return UpdateRow(a_sItemName, sContent, a_sItemComment);
	}

	BOOL CInformationDB::InsertDoubleRow(LPCTSTR a_sItemName, double a_dItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		CString sContent;
		sContent.Format(_T("%f"), a_dItemContent);

		return InsertRow(a_sItemName, sContent, a_sItemComment);
	}

	BOOL CInformationDB::UpdateDoubleRow(LPCTSTR a_sItemName, double a_dItemContent, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		CString sContent;
		sContent.Format(_T("%f"), a_dItemContent);

		return UpdateRow(a_sItemName, sContent, a_sItemComment);
	}

	BOOL CInformationDB::InsertTimeStampRow(LPCTSTR a_sItemName, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		SYSTEMTIME m_time;
		GetLocalTime(&m_time);
		char szDateTime[100] = { 0 };
		sprintf_s(szDateTime, "%02d-%02d-%02d %02d:%02d:%02d", m_time.wYear, m_time.wMonth,
			m_time.wDay, m_time.wHour, m_time.wMinute, m_time.wSecond);
		std::string time(szDateTime);

		return InsertRow(a_sItemName, CString(time.c_str()), a_sItemComment);
		//return InsertRow(a_sItemName, _T("CURRENT_TIMESTAMP"), a_sItemComment);
	}

	BOOL CInformationDB::UpdateTimeStampRow(LPCTSTR a_sItemName, LPCTSTR a_sItemComment /*= nullptr*/)
	{
		SYSTEMTIME m_time;
		GetLocalTime(&m_time);
		char szDateTime[100] = { 0 };
		sprintf_s(szDateTime, "%02d-%02d-%02d %02d:%02d:%02d", m_time.wYear, m_time.wMonth,
			m_time.wDay, m_time.wHour, m_time.wMinute, m_time.wSecond);
		std::string time(szDateTime);
		
		return UpdateRow(a_sItemName, CString(time.c_str()), a_sItemComment);
	}

	BOOL CInformationDB::GetStringValue(LPCTSTR a_sItemName, CString& a_sString)
	{
		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			return FALSE;
		}

		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return FALSE;
		}

		CString sNameColumnName = tableInfoPtr->GetColumnName((int)CInformationTable::ColumnID::ITEM - (int)CInformationTable::ColumnID::MIN);
		CString sSQLCommand;
		sSQLCommand.Format(_T("SELECT * FROM \'%s\' WHERE %s = \'%s\'"),
			(LPCTSTR)tableInfoPtr->GetTableName(),
			(LPCTSTR)sNameColumnName,
			a_sItemName);

		auto query = datastorePtr->QueryByCommand(sSQLCommand);
		ASSERT(query);
		if (!query)
		{
			return FALSE;
		}

		int nCol = (int)CInformationTable::ColumnID::CONTENT - (int)CInformationTable::ColumnID::MIN;
		a_sString = query->GetColStringValue(nCol);

		return TRUE;
	}

	BOOL CInformationDB::GetIntValue(LPCTSTR a_sItemName, int& a_nValue)
	{
		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			return FALSE;
		}

		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return FALSE;
		}

		CString sNameColumnName = tableInfoPtr->GetColumnName((int)CInformationTable::ColumnID::ITEM - (int)CInformationTable::ColumnID::MIN);
		CString sSQLCommand;
		sSQLCommand.Format(_T("SELECT * FROM \'%s\' WHERE %s = \'%s\'"),
			(LPCTSTR)tableInfoPtr->GetTableName(),
			(LPCTSTR)sNameColumnName,
			a_sItemName);

		auto query = datastorePtr->QueryByCommand(sSQLCommand);		
		ASSERT(query);
		if (!query)
		{
			return FALSE;
		}

		int nCol = (int)CInformationTable::ColumnID::CONTENT - (int)CInformationTable::ColumnID::MIN;
		a_nValue = query->GetColIntValue(nCol);

		return TRUE;
	}

	CDBTableBasePtr CInformationDB::GetTableInfo()
	{
		/*if (!m_tableInfo)
		{
			m_tableInfo.reset(new CInformationTable);
		}

		ASSERT(m_tableInfo);*/
		return m_tableInfo;
	}

	BOOL CInformationDB::Init(const BOOL a_bClean /*= FALSE*/)
	{
		return myDB->Init(a_bClean);
	}

	BOOL CInformationDB::CreateTable(const BOOL a_bForce /*= FALSE*/)
	{
		return myDB->CreateTable(a_bForce);
	}

	BOOL CInformationDB::DeleteTable()
	{
		return myDB->DeleteTable();
	}

	BOOL CInformationDB::RemoveAllRows()
	{
		return myDB->RemoveAllRows();
	}

	BOOL CInformationDB::IsDBExist()
	{
		return myDB->IsDBExist();
	}

	OTSSQLITE::CDBStoreBasePtr CInformationDB::GetDatastore()
	{
		return myDB->GetDatastore();
	}

	OTSSQLITE::CDBQueryBasePtr CInformationDB::GetTableQuery(LPCTSTR a_sOrderColumnName /*= nullptr*/)
	{
		return myDB->GetTableQuery(a_sOrderColumnName);
	}

}