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; }