void CVoxSQLite::GetGroups( const char* username, Groups& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from [Group] WHERE [username] = %Q;", username );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nContactId = 0;
std::string type = "";
CppSQLite3Query q = stmt.execQuery();
Group grp;
//Process record set.
while (!q.eof())
{
grp.setUsername( q.getStringField( 0 ) );
grp.setName ( q.getStringField( 1 ) );
rList.Add( &grp );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
void CVoxSQLite::GetMergedContacts( const char* parent_username, MergedContacts& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from [MergedContact] WHERE [parent_username] = %Q;", parent_username );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nContactId = 0;
std::string type = "";
CppSQLite3Query q = stmt.execQuery();
MergedContact mc;
//Process record set.
while (!q.eof())
{
mc.setParentUsername( q.getStringField( 0 ) );
mc.setUsername ( q.getStringField( 1 ) );
rList.Add( &mc );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
BOOL DatabaseModule_Impl::sqlGetGroupInfoByGId(IN std::string& gId, OUT module::GroupInfoEntity& groupInfo)
{
try
{
CppSQLite3Statement stmt;
stmt = m_pSqliteDB->compileStatement(getGroupInfoByGIdSql.c_str());
stmt.bind(1, gId.c_str());
CppSQLite3Query query = stmt.execQuery();
if (!query.eof())
{
groupInfo.gId = gId;
groupInfo.csName = util::stringToCString(query.getStringField(2));
groupInfo.desc = util::stringToCString(query.getStringField(3));
groupInfo.avatarUrl = query.getStringField(4);
groupInfo.creatorId = query.getStringField(5);
groupInfo.type = query.getIntField(6);
groupInfo.version = query.getIntField(7);
groupInfo.groupUpdated = query.getIntField(8);
groupInfo.shieldStatus = query.getIntField(9);
_parseJsonForGroupMembers(query.getStringField(10), groupInfo.groupMemeberList);
}
else
{
return FALSE;
}
}
catch (CppSQLite3Exception& sqliteException)
{
#ifdef _DEBUG
MessageBoxA(0, sqliteException.errorMessage(), "BD ERROR", MB_OK | MB_ICONHAND);
#endif
CString csErrMsg = util::stringToCString(sqliteException.errorMessage(), CP_UTF8);
LOG__(ERR, _T("db failed,error msg:%s"),
csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
return FALSE;
}
return TRUE;
}
void CVoxSQLite::GetProfile( const char* username, Profile& rProfile )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Profile WHERE username = %Q;", username );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
CppSQLite3Query q = stmt.execQuery();
//Process record set.
while (!q.eof())
{
nProfileId = q.getIntField(0);
rProfile.setFirstName ( q.getStringField(2) );
rProfile.setLastName ( q.getStringField(3) );
rProfile.setAlias ( q.getStringField(4) );
rProfile.setSmsSignature( q.getStringField(5) );
rProfile.setCompany ( q.getStringField(6) );
rProfile.setSex ( (EnumSex::Sex)q.getIntField(7) );
rProfile.setNotes ( q.getStringField(10) );
// rProfile.setBirthday( q.getStringField(8) );
// photo (filename)
//TODO rProfile.setPhoto( q.getBlobField(9) );
//Address, email, phone numbers, and URLs will be in separate tables to allow for easy
GetStreetAddresses( nProfileId, rProfile.getStreetAddresses() );
GetEmailAddresses ( nProfileId, rProfile.getEmailAddresses() );
GetTelephones ( nProfileId, rProfile.getTelephones() );
GetUrls ( nProfileId, rProfile.getUrls() );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
void CVoxSQLite::GetStreetAddresses( int nProfileId, StreetAddresses& rAddrs )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from StreetAddress WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nId = 0;
CppSQLite3Query q = stmt.execQuery();
StreetAddress addr;
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [street], [locality], [region], [postcode], [country], [visibility] ) "
nId = q.getIntField(0); //Not part of Profile.
addr.setType ( q.getStringField(1) );
addr.setStreet1 ( q.getStringField(2) );
addr.setCity ( q.getStringField(3) );
addr.setStateProvince( q.getStringField(4) );
addr.setPostalCode ( q.getStringField(5) );
addr.setCountry ( q.getStringField(6) );
addr.setVisibility ( (EnumVisibility::Visibility)q.getIntField(7) );
rAddrs.Add( &addr );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
BOOL DatabaseModule_Impl::sqlGetFileTransferHistory(OUT std::vector<TransferFileEntity>& fileList)
{
try
{
CppSQLite3Statement stmt;
stmt = m_pSqliteDB->compileStatement(getFileTransferHistoryBySIdSql.c_str());
stmt.bind(1, 20);
CppSQLite3Query query = stmt.execQuery();
while (!query.eof())
{
TransferFileEntity fileInfo;
fileInfo.sTaskID = query.getStringField(1);
fileInfo.sFromID = query.getStringField(2);
fileInfo.sFileName = query.getStringField(3);
CString strSavePath = util::stringToCString(query.getStringField(7));
fileInfo.setSaveFilePath(strSavePath);
fileInfo.nFileSize = query.getIntField(8);
fileInfo.time = query.getIntField(9);
fileList.push_back(fileInfo);
query.nextRow();
}
}
catch (CppSQLite3Exception& sqliteException)
{
#ifdef _DEBUG
MessageBoxA(0, sqliteException.errorMessage(), "BD ERROR", MB_OK | MB_ICONHAND);
#endif
CString csErrMsg = util::stringToCString(sqliteException.errorMessage(), CP_UTF8);
LOG__(ERR, _T("db failed,error msg:%s"),
csErrMsg);
return FALSE;
}
catch (...)
{
return FALSE;
}
return TRUE;
}
BOOL DatabaseModule_Impl::sqlGetRecentSessionInfoByGId(IN std::string& sId, OUT module::SessionEntity& sessionInfo)
{
try
{
CppSQLite3Statement stmt;
stmt = m_pSqliteDB->compileStatement(getRecentSessionByIdSql.c_str());
stmt.bind(1, sId.c_str());
CppSQLite3Query query = stmt.execQuery();
if (!query.eof())
{
sessionInfo.sessionID = sId;
sessionInfo.sessionType = query.getIntField(2);
sessionInfo.updatedTime = query.getIntField(3);
sessionInfo.latestmsgId = query.getIntField(4);
sessionInfo.latestMsgContent = query.getStringField(5);
sessionInfo.latestMsgFromId = query.getStringField(6);
}
else
{
return FALSE;
}
}
catch (CppSQLite3Exception& sqliteException)
{
#ifdef _DEBUG
MessageBoxA(0, sqliteException.errorMessage(), "BD ERROR", MB_OK | MB_ICONHAND);
#endif
CString csErrMsg = util::stringToCString(sqliteException.errorMessage(), CP_UTF8);
LOG__(ERR, _T("db failed,error msg:%s"),
csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
return FALSE;
}
return TRUE;
}
BOOL DatabaseModule_Impl::sqlGetAllRecentSessionInfo(OUT std::vector<module::SessionEntity>& sessionList)
{
try
{
CppSQLite3Statement stmt;
stmt = m_pSqliteDB->compileStatement(getAllRecentSessionSql.c_str());
CppSQLite3Query query = stmt.execQuery();
while (!query.eof())
{
module::SessionEntity sessionInfo;
sessionInfo.sessionID = query.getStringField(1);;
sessionInfo.sessionType = query.getIntField(2);
sessionInfo.updatedTime = query.getIntField(3);
sessionInfo.latestmsgId = query.getIntField(4);
sessionInfo.latestMsgContent = query.getStringField(5);
sessionInfo.latestMsgFromId = query.getStringField(6);
sessionList.push_back(sessionInfo);
query.nextRow();
}
}
catch (CppSQLite3Exception& sqliteException)
{
#ifdef _DEBUG
MessageBoxA(0, sqliteException.errorMessage(), "BD ERROR", MB_OK | MB_ICONHAND);
#endif
CString csErrMsg = util::stringToCString(sqliteException.errorMessage(), CP_UTF8);
LOG__(ERR, _T("select failed,error msg:%s"),
csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
return FALSE;
}
return TRUE;
}
void CVoxSQLite::GetEmailAddresses( int nProfileId, EmailAddresses& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from EmailAddress WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
std::string type = "";
EmailAddress addr;
CppSQLite3Query q = stmt.execQuery();
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [email], [visiblity]
nProfileId = q.getIntField(0); //Not part of Profile.
type = q.getStringField( 1 ); //Use to determine which address to update.
addr.setType ( q.getStringField(1) );
addr.setAddress ( q.getStringField(2) );
addr.setVisibility( (EnumVisibility::Visibility) q.getIntField(3) );
rList.Add( &addr );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
uint32_t BaseDatabaseUnitImpl::getDatabaseVersion()
{
if (database_)
{
try
{
CppSQLite3Statement statement = database_->compileStatement(SQL_VERSION_TABLE_GET_VERSION);
statement.bind(1, VERSION_KEY);
CppSQLite3Query result = statement.execQuery();
if (!result.eof())
{
return (uint32_t)result.getIntField("version");
}
}
catch (CppSQLite3Exception e)
{
LOG_ERR_R(DATABASE_MANAGER_LOG_TAG, _T("Failed to get database version, error: %u"), e.errorCode());
LOG_ERR_D_A(DATABASE_MANAGER_LOG_TAG_A, "Message: %s", e.errorMessage());
}
}
return 0;
}
void CVoxSQLite::GetUrls( int nProfileId, Urls& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Url WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
std::string type = "";
CppSQLite3Query q = stmt.execQuery();
Url url;
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [address]
nProfileId = q.getIntField(0); //Not part of Profile.
url.setType ( q.getStringField( 1 ) );
url.setUrl ( q.getStringField( 2 ) );
url.setVisibility( (EnumVisibility::Visibility)q.getIntField(3) );
rList.Add( &url );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
void CVoxSQLite::GetContact( const char* username, Contact& c )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Contact WHERE username = %Q;", username );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nContactId = 0;
CppSQLite3Query q = stmt.execQuery();
//Process record set.
while (!q.eof())
{
nContactId = q.getIntField(0);
// c.setName ( q.getStringField(1) );
// c.setNickname ( q.getStringField(2) );
// c.setBirthday ( q.getStringField(3) );
// c.setMergedContact( q.getStringField(3) );
GetMergedContacts( username, c.getMergedContacts() );
GetGroups ( username, c.getGroups() );
GetProfile ( username, c );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
void CVoxSQLite::GetTelephones( int nProfileId, Telephones& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Telephone WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
std::string type = "";
CppSQLite3Query q = stmt.execQuery();
Telephone tel;
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [number]
nProfileId = q.getIntField(0); //Not part of Profile.
tel.setType ( q.getStringField( 1 ) );
tel.setNumber( q.getStringField( 2 ) );
rList.Add( &tel );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
BOOL DatabaseModule_Impl::sqlGetHistoryMessage(IN const std::string& sId, IN const UInt32 msgId
, IN UInt32 nMsgCount, OUT std::vector<MessageEntity>& msgList)
{
try
{
CppSQLite3Statement stmt;
stmt = m_pSqliteDB->compileStatement(getMessageBySId_Msg_Sql.c_str());
stmt.bind(1, sId.c_str());
stmt.bind(2, (int)msgId);
stmt.bind(3, (int)nMsgCount);
CppSQLite3Query query = stmt.execQuery();
while (!query.eof())
{
MessageEntity msg;
msg.msgType = MESSAGE_TYPE_HISTORY;
msg.msgId = query.getIntField(1);
msg.msgRenderType = query.getIntField(5);
//对语音消息做个特殊处理，content存储的是json格式字符串
if (MESSAGE_RENDERTYPE_AUDIO == msg.msgRenderType)
{
std::string jsonAudioContent = query.getStringField(4);
Json::Reader reader;
Json::Value root;
if (reader.parse(jsonAudioContent, root))
{
msg.msgAudioTime = (root.get("msgAudioTime", "")).asUInt();
msg.content = (root.get("msgAudioId", "")).asString();
msg.msgAudioReaded = 1;//历史语音消息默认为已读
}
}
else
{
msg.content = query.getStringField(4);
}
msg.msgSessionType = query.getIntField(6);
msg.msgTime = query.getIntField(7);
msg.talkerSid = query.getStringField(3);
msg.msgAudioReaded = TRUE;
msgList.push_back(msg);
//msgList.insert(msgList.begin(), msg);//需要
query.nextRow();
}
}
catch (CppSQLite3Exception& sqliteException)
{
#ifdef _DEBUG
MessageBoxA(0, sqliteException.errorMessage(), "BD ERROR", MB_OK | MB_ICONHAND);
#endif
CString csErrMsg = util::stringToCString(sqliteException.errorMessage(), CP_UTF8);
LOG__(ERR, _T("get history message failed,error msg:%s"),
csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("get history message unknown exception"));
return FALSE;
}
return TRUE;
}
/** Message 조회
*
* @param szId target device id
* @param nMessageType 0 이라면 Message 전체
* @param pWrapper 응답을 전해 줄 IF4Wrapper
*/
BOOL CMessageHelper::Select(const char *szId, BYTE nMessageType, IF4Wrapper *pWrapper)
{
if(!CheckDB()) return FALSE;
CppSQLite3Query result;
char timeString[32];
TIMESTAMP issueTime;
unsigned char * pPayload;
int idx=0, nPayloadLen;
int year, mon, day, hour, min, sec;
if(pWrapper == NULL) return FALSE;
if(!Open()) return FALSE;
try
{
CppSQLite3Statement stmt;
if (nMessageType > 0) {
stmt = m_SqliteHelper.compileStatement(
"SELECT "
"messageId, issueTime, userData, payload, "
"duration, errorHandler, preHandler, postHandler "
"FROM MessageTbl "
"WHERE targetId = ? and messageType = ? ; ");
stmt.bind(1, szId);
stmt.bind(2, nMessageType);
} else {
stmt = m_SqliteHelper.compileStatement(
"SELECT "
"messageId, issueTime, userData, payload, "
"duration, errorHandler, preHandler, postHandler "
"FROM MessageTbl "
"WHERE targetId = ? ; ");
stmt.bind(1, szId);
}
result = stmt.execQuery();
while(!result.eof())
{
memset(timeString, 0, sizeof(timeString));
memset(&issueTime, 0, sizeof(TIMESTAMP));
pPayload = NULL; nPayloadLen = 0; idx = 0;
IF4API_AddResultNumber(pWrapper, "1.6", VARSMI_UINT, result.getIntField(idx)); idx++;
strcat(timeString, result.getStringField(idx)); idx++;
sscanf(timeString,"%04d-%02d-%02d %02d:%02d:%02d",
&year, &mon, &day, &hour, &min, &sec);
issueTime.year = year; issueTime.mon = mon;
issueTime.day = day; issueTime.hour = hour;
issueTime.min = min; issueTime.sec = sec;
/*
XDEBUG(" %04d/%02d/%02d %02d:%02d:%02d\r\n",
issueTime.year, issueTime.mon, issueTime.day, issueTime.hour, issueTime.min, issueTime.sec);
*/
IF4API_AddResultFormat(pWrapper, "1.16", VARSMI_TIMESTAMP, &issueTime, sizeof(TIMESTAMP));
IF4API_AddResultNumber(pWrapper, "1.6", VARSMI_UINT, result.getIntField(idx)); idx++;
pPayload = const_cast<unsigned char *>(result.getBlobField(idx, nPayloadLen)); idx++;
IF4API_AddResultFormat(pWrapper, "1.12", VARSMI_STREAM, pPayload, nPayloadLen);
result.nextRow();
}
stmt.finalize();
Close();
}
catch ( CppSQLite3Exception& e )
{
Close();
XDEBUG(ANSI_COLOR_RED "MessageTbl DB ERROR SELECT: %d %s\r\n" ANSI_NORMAL, e.errorCode(), e.errorMessage());
return FALSE;
}
return TRUE;
}
