chatlogdb.cpp

SQlite 的使用 读库 写库

// ChatLogDB.cpp: implementation of the CChatLogDB class.
//
//////////////////////////////////////////////////////////////////////
//#include "stdafx.h"
#include "ChatLogDB.h"
#include "direct.h"
#include  <io.h>
#include <time.h>
const char STR_SYS_TABLE_CREATE[]		= "create table msgsystem(id integer primary key, msg blob, time integer);";
const char STR_NOM_TABLE_CREATE[]		= "create table msgnormal(id integer primary key, msg blob, time integer, channel integer, sender varchar(16), receiver varchar(16));";
const char STR_CONTACT_QUERY[]			= "select sender, receiver, time from msgnormal";
const char SQL_INSERT_SYSTEM_BIN[]		= "insert into msgsystem values(NULL, ?, %d)";
const char SQL_INSERT_NORMAL_BIN[]		= "insert into msgnormal values(NULL, ?, %d, %d, '%s', '%s')";
const char STR_DELETE_SYSTEM[]			= "delete from msgsystem where id = %d";
const char STR_DELETE_NORMAL[]			= "delete from msgnormal where id = %d";
const char SQL_SYSTEM_COUNT[]			= "select COUNT(id) from msgsystem";
const char SQL_NORMAL_COUNT[]			= "select COUNT(id) from msgnormal";
const char SQL_DELETE_NORMAL_BY_TIME[]	= "delete from msgnormal where time = %d";
const char SQL_DELETE_SYSTEM_BY_TIME[]	= "delete from msgsystem where time = %d";

const int ID_KEY = 0;
const int MSG_KEY = 1;
const int TIME_KEY = 2;
const int CHANNEL_KEY = 3;
const int SENDER_KEY = 4;
const int RECEIVER_KEY = 5;

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CChatLogDB::CChatLogDB()
{
	m_bConnected	= false;
	m_nAddLine		= 0;
	m_bLog			= true;
	m_lCurTime		= 0;
	m_bHeroLogin	= false;
	m_strCurPath	= "";
}	

CChatLogDB::~CChatLogDB()
{
	this->Close();
}

CChatLogDB* g_pInstance = NULL;

TQCHAT_RECORD_API IChatLogDB* CreateChatLogDB()
{
	if (NULL == g_pInstance)
	{
		g_pInstance = new CChatLogDB;
	}
	return g_pInstance;
}

TQCHAT_RECORD_API void DestoryChatLogDB()
{
	SAFE_DELETE( g_pInstance );
}

void CChatLogDB::Connect(const char* pszFullPath, const char* pszKey)
{
	if(pszFullPath == NULL)
	{
		return;
	}

	if (pszKey != NULL)
	{
		m_strDbKey = pszKey;
	}
	else
	{
		m_strDbKey = "";
	}
	
	this->Close();
	this->CreateDir(pszFullPath);
	
	if (m_objDB.OpenDB(pszFullPath, m_strDbKey.c_str()))
	{
		m_bConnected = true;
		m_objDB.Execute(STR_SYS_TABLE_CREATE);
		m_objDB.Execute(STR_NOM_TABLE_CREATE);
	}
	else
	{
		m_bConnected = false;
	}
}

void CChatLogDB::Commit()
{
	if (m_nAddLine != 0)
	{
		m_objDB.Execute("COMMIT");
		m_nAddLine = 0;
	}
}

void CChatLogDB::Close()
{
	if (m_nAddLine != 0)
	{
		m_objDB.Execute("COMMIT");
		m_nAddLine = 0;
	}

	if (m_bConnected)
	{
		m_objDB.CloseDB();
	}
}

void CChatLogDB::Reset()
{ 
	this->SetHeroLogin(false); 
}

int CChatLogDB::QuerySystemCount()
{
	if (!m_bConnected)
	{
		return 0;		
	}

	CResult* pResult = m_objDB.ExecuteSelect(SQL_SYSTEM_COUNT);	
	if (pResult != NULL)
	{
		const char* pszRet = pResult->GetAt(1, 0);
		if (pszRet != NULL)
		{
			return atoi(pszRet);
		}
	}

	return 0;
}

int CChatLogDB::QueryNormalCountByCondition(const char* pszSql)
{
	if(!m_bConnected || pszSql == NULL)
	{
		return 0;
	}

	CResult* pResult = m_objDB.ExecuteSelect(pszSql);	
	if (pResult != NULL)
	{
		const char* pszRet = pResult->GetAt(1, 0);
		if (pszRet != NULL)
		{
			return atoi(pszRet);
		}
	}

	return 0;
}

int CChatLogDB::QueryNormalCount()
{
	if (!m_bConnected)
	{
		return 0;		
	}

	CResult* pResult = m_objDB.ExecuteSelect(SQL_NORMAL_COUNT);	
	if (pResult != NULL)
	{
		const char* pszRet = pResult->GetAt(1, 0);
		if (pszRet != NULL)
		{
			return atoi(pszRet);
		}
	}

	return 0;
}

const VEC_CHAT_CONTACT& CChatLogDB::QueryContactList()
{
	m_vecChatContact.clear();

	if (m_bConnected)
	{
		MAP_CHAT_CONTACT mapContact;
		CResult* pResult = m_objDB.ExecuteSelect(STR_CONTACT_QUERY);
		if (NULL != pResult)
		{
			for (int i = 1; i < pResult->GetRowNum(); ++i)
			{
				string strSender	= pResult->GetAt(i, 0);
				string strReciver	= pResult->GetAt(i, 1); 
				long lTime			= atoi(pResult->GetAt(i, 2));

				if (!strSender.empty())
				{
					if (mapContact[strSender] < lTime)
					{
						mapContact[strSender] = lTime;
					}
				}

				if (!strReciver.empty())
				{
					if (mapContact[strReciver] < lTime)
					{
						mapContact[strReciver] = lTime;
					}
				}
			}
		}

		ITER_MAP_CHAT_CONTACT iter = mapContact.begin();
		for(; iter!=mapContact.end(); ++iter)
		{
			CHAT_CONTACT_INFO info;
			info.strName = iter->first;
			info.lTime = iter->second;
			m_vecChatContact.push_back(info);
		}
	}

	return m_vecChatContact;
}

void CChatLogDB::AddValueUsingBinaryMode(const CHAT_DB_ROW_INFO* pRowInfo, bool bIsSystem)
{
	if (!m_bConnected || pRowInfo == NULL || !m_bLog)
	{
		return;
	}
	
	//////////////////////////////////////////////////////////////////////////
	//判断时间是否相同，不同的话换一个数据库文件
	struct tm *newtime;
	time_t long_time;
    time( &long_time );                /* Get time as long integer. */
    newtime = localtime( &long_time ); /* Convert to local time. */
	if (newtime == NULL)
	{
		return;
	}

	int nYear	= newtime->tm_year + 1900;
	int nMonth	= newtime->tm_mon + 1;
	int nGetTime = nYear * 10000 + nMonth * 100 + newtime->tm_mday;
	
	//如果时间不相等
	if (m_lCurTime != nGetTime)
	{
		m_lCurTime = nGetTime;

		char szFullPath[256] = {0};
		sprintf(szFullPath, "%schatlog_%d_%d.db", m_strCurPath.c_str(), nMonth, newtime->tm_mday);
		this->Connect(szFullPath, m_strDbKey.c_str());
	}
	//////////////////////////////////////////////////////////////////////////
	if (0 == m_nAddLine)
	{
		m_objDB.Execute("BEGIN");
	}

	char szTemp[512];
	if (bIsSystem)
	{	
		sprintf(szTemp, SQL_INSERT_SYSTEM_BIN, pRowInfo->lTime);
	}
	else
	{
		sprintf(szTemp, SQL_INSERT_NORMAL_BIN, pRowInfo->lTime, pRowInfo->nChannel, 
			    pRowInfo->strSender.c_str(), pRowInfo->strReceiver.c_str());
	}

	char szTimeString[40] = "";
	_strtime(szTimeString);
	string strContent = pRowInfo->strText;
	strContent += "#G【";
	strContent += szTimeString;
	strContent += "】";

	string strTmp = szTemp;
	m_objDB.AddInfoContainBinary(strTmp, strContent);

	++m_nAddLine;
	if (m_nAddLine > 20)
	{
		m_nAddLine = 0;
		m_objDB.Execute("COMMIT");
	}				
}

const ROW_INFO_VEC&	CChatLogDB::QueryValueUsingBinaryMode(const char* pszSql, bool bSys)
{
	m_vecResultSet.clear();
	if(pszSql == NULL)
	{
		return m_vecResultSet;
	}
	
	if (m_bConnected)
	{
		string strSql = pszSql;
		m_objDB.GetInfoContainBinary(strSql, m_vecResultSet, bSys);
	}

	return m_vecResultSet;
}

void CChatLogDB::DelLogByGivenTimeSet(const VEC_CHAT_TIME& vecTime, bool bIsSystem)
{
	if (!m_bConnected || vecTime.empty())
	{
		return;
	}
	
	//事物处理
	m_objDB.Execute("BEGIN");
	for (int i=0; i<vecTime.size(); ++i)
	{
		char szTemp[256];
		if (bIsSystem)
		{
			sprintf(szTemp, SQL_DELETE_SYSTEM_BY_TIME, vecTime[i]);
		}
		else
		{
			sprintf(szTemp, SQL_DELETE_NORMAL_BY_TIME, vecTime[i]);
		}

		m_objDB.Execute(szTemp);
	}
	m_objDB.Execute("COMMIT");
}

void CChatLogDB::DelLogByTime(long lTime, bool bIsSystem)
{
	if (!m_bConnected)
	{
		return;
	}

	char szTemp[256];
	if (bIsSystem)
	{
		sprintf(szTemp, SQL_DELETE_SYSTEM_BY_TIME, lTime);
	}
	else
	{
		sprintf(szTemp, SQL_DELETE_NORMAL_BY_TIME, lTime);
	}

	m_objDB.Execute(szTemp);
}

void CChatLogDB::DelLogByID(DWORD id, bool bIsSystem)
{
	if (!m_bConnected)
	{
		return;
	}

	char szTemp[256];
	if (bIsSystem)
	{
		sprintf(szTemp, STR_DELETE_SYSTEM, id);
	}
	else
	{
		sprintf(szTemp, STR_DELETE_NORMAL, id);
	}

	m_objDB.Execute(szTemp);
}

void CChatLogDB::DelCurrentLog(bool bIsSystem)
{
	if (!m_bConnected)
	{
		return;
	}
	
	m_objDB.Execute("BEGIN");
	for (int i = 0; i < m_vecResultSet.size(); ++i)
	{
		this->DelLogByID(m_vecResultSet[i].id, bIsSystem);
	}
	m_objDB.Execute("COMMIT");
}

void CChatLogDB::DelBySql(const char* pszSql)
{
	if (pszSql == NULL)
	{
		return;
	}

	m_objDB.Execute(pszSql);
}

void CChatLogDB::SetCurPath(const char* pszText)
{
	if (pszText != NULL)
	{
		m_strCurPath = pszText;
	}
}

bool CChatLogDB::CreateDir(const char *pszPath)   
{   
	if (pszPath == NULL || pszPath[0] == '\0')
	{
		return false;
	}

	char DirName[256];   
	strcpy(DirName, pszPath);   

	int len   =  strlen(DirName); 
	for(int i=1; i<len; i++)   
	{   
		if(DirName[i] == '\\')   
		{   
			DirName[i]   =   0;   
			if(_access(DirName,   0) != 0)   
			{   
				int nRet = _mkdir(DirName);
				if (nRet == -1 && errno != EEXIST)
				{
					return false;	 
				}
			}   
			DirName[i]   =   '\\';   
		}   
	}   
    
	return   true;   
}