void CVoxSQLite::InsertUrl ( int nId, const std::string& type, const std::string& url, EnumVisibility::Visibility vis )
{
if ( IsValidUrl( url.c_str() ) )
{
std::string strSql = "INSERT INTO Url "
"( [profile_id], [type], [url], [visibility] ) "
"VALUES( ?, ?, ?, ? ); ";
try
{
//TODO: compile as a member var?
CppSQLite3Statement stmt = m_db.compileStatement( strSql.c_str() );
stmt.bind( 1, nId );
stmt.bind( 2, type.c_str() );
stmt.bind( 3, url.c_str() );
stmt.bind( 4, (int)vis );
stmt.execDML(); //We expect a return value == 1 (Number of rows changed);
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
}
void BaseDatabaseUnitImpl::runCreateStatements()
{
loadDatabaseInfo();
if (database_)
{
try
{
uint32_t version = databaseInfo_->latest_.version_;
for (auto it = databaseInfo_->latest_.tableList_.begin(); it != databaseInfo_->latest_.tableList_.end(); ++it)
{
CppSQLite3Statement statement = database_->compileStatement(it->statement_.c_str());
runTransaction(statement);
}
CppSQLite3Statement statement = database_->compileStatement(SQL_VERSION_TABLE_CREATE);
runTransaction(statement);
statement = database_->compileStatement(SQL_VERSION_TABLE_SET_VERSION);
statement.bind(1, VERSION_KEY);
statement.bind(2, (int)version);
runTransaction(statement);
runUpdateStatements();
}
catch (CppSQLite3Exception e)
{
LOG_ERR_R(DATABASE_MANAGER_LOG_TAG, _T("Failed to run create statements, error: %u"), e.errorCode());
LOG_ERR_D_A(DATABASE_MANAGER_LOG_TAG_A, "Message: %s", e.errorMessage());
}
}
}
void CVoxSQLite::InsertGroup( const char* username, Group& rGroup )
{
//TODO: compile as a member var?
std::string strSql = "INSERT INTO [Group] "
"( [username], [name] ) "
"VALUES( ?, ? ); ";
try
{
CppSQLite3Statement stmt = m_db.compileStatement( strSql.c_str() );
stmt.bind( 1, username );
stmt.bind( 2, rGroup.getName() );
stmt.execDML(); //We expect a return value == 1 (Number of rows changed);
stmt.reset();
int nId = (int)m_db.lastRowId();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
BOOL DatabaseModule_Impl::sqlInsertRecentSessionInfoEntity(IN const module::SessionEntity& sessionInfo)
{
try
{
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(insertRecentSessionSql.c_str());
stmt.bind(1, sessionInfo.sessionID.c_str());
stmt.bind(2, int(sessionInfo.sessionType));
stmt.bind(3, int(sessionInfo.updatedTime));
stmt.bind(4, int(sessionInfo.latestmsgId));
stmt.bind(5, sessionInfo.latestMsgContent.c_str());
stmt.bind(6, sessionInfo.latestMsgFromId.c_str());
if (0 == stmt.execDML())
{
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("insert failed,error msg:%s"), csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
return FALSE;
}
return TRUE;
}
void CVoxSQLite::InsertMergedContact( const char* strContactUsername, const char* strUsername )
{
//TODO: compile as a member var?
std::string strSql = "INSERT INTO [MergedContact] "
"( [parent_username], [username] ) "
"VALUES( ?, ? ); ";
try
{
CppSQLite3Statement stmt = m_db.compileStatement( strSql.c_str() );
stmt.bind( 1, strContactUsername );
stmt.bind( 2, strUsername );
stmt.execDML(); //We expect a return value == 1 (Number of rows changed);
stmt.reset();
int nId = (int)m_db.lastRowId();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
//##ModelId=474D30760272
bool CClip_ImportExport::ExportToSqliteDB(CppSQLite3DB &db)
{
bool bRet = false;
try
{
//Add to Main Table
m_Desc.Replace(_T("'"), _T("''"));
db.execDMLEx(_T("insert into Main values(NULL, %d, '%s');"), CURRENT_EXPORT_VERSION, m_Desc);
long lId = (long)db.lastRowId();
//Add to Data table
CClipFormat* pCF;
CppSQLite3Statement stmt = db.compileStatement(_T("insert into Data values (NULL, ?, ?, ?, ?);"));
for(int i = m_Formats.GetSize()-1; i >= 0 ; i--)
{
pCF = & m_Formats.ElementAt(i);
stmt.bind(1, lId);
stmt.bind(2, GetFormatName(pCF->m_cfType));
long lOriginalSize = GlobalSize(pCF->m_hgData);
stmt.bind(3, lOriginalSize);
const unsigned char *Data = (const unsigned char *)GlobalLock(pCF->m_hgData);
if(Data)
{
//First compress the data
long lZippedSize = compressBound(lOriginalSize);
Bytef *pZipped = new Bytef[lZippedSize];
if(pZipped)
{
int nZipReturn = compress(pZipped, (uLongf *)&lZippedSize, (const Bytef *)Data, lOriginalSize);
if(nZipReturn == Z_OK)
{
stmt.bind(4, pZipped, lZippedSize);
}
delete []pZipped;
pZipped = NULL;
}
}
GlobalUnlock(pCF->m_hgData);
stmt.execDML();
stmt.reset();
m_Formats.RemoveAt(i);
}
bRet = true;
}
CATCH_SQLITE_EXCEPTION_AND_RETURN(false)
return bRet;
}
BOOL DatabaseModule_Impl::sqlInsertOrReplaceGroupInfoEntity(IN const module::GroupInfoEntity& groupInfo)
{
try
{
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(insertGroupInfoSql.c_str());
stmt.bind(1, groupInfo.gId.c_str());
stmt.bind(2, util::cStringToString(groupInfo.csName).c_str());
stmt.bind(3, util::cStringToString(groupInfo.desc).c_str());
stmt.bind(4, groupInfo.avatarUrl.c_str());
stmt.bind(5, groupInfo.creatorId.c_str());
stmt.bind(6, int(groupInfo.type));
stmt.bind(7, int(groupInfo.version));
stmt.bind(8, int(groupInfo.groupUpdated));
stmt.bind(9, int(groupInfo.shieldStatus));
std::string& strJson = _makeJsonForGroupMembers(groupInfo.groupMemeberList);
stmt.bind(10, strJson.c_str());
stmt.execDML();
}
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("insert failed,error msg:%s"), csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
return FALSE;
}
return TRUE;
}
void CBrainMemory::SetSystemItem(int64 Item,AnsiString Info){
CppSQLite3Buffer SQL;
CppSQLite3Query Result;
char a[30],b[30];
int64toa(ROOM_SYSTEM,a);
int64toa(Item,b);
SQL.format("select b from \"%s\" where a = \"%s\";",a,b);
Result = BrainDB.execQuery(SQL);
bool Find = !Result.eof();
Result.finalize();
if(!Find){
SQL.format("insert into \"%s\" values (\"%s\", ?)",
a,
b);
}else{
SQL.format("update \"%s\" set b = ? where a = \"%s\";",
a,
b
);
}
CppSQLite3Statement State = BrainDB.compileStatement(SQL);
State.bind(1,Info.c_str()); //替换第一个问号
State.execDML();
}
BOOL DatabaseModule_Impl::sqlDeleteGroupInfoEntity(IN const std::string& groupId)
{
try
{
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(deleteGroupInfoSql.c_str());
stmt.bind(1, groupId.c_str());
stmt.execDML();
}
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("delete failed,error msg:%s"), csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
return FALSE;
}
return TRUE;
}
int CppSQLite3DB::insertBlob(const CString& szSQL, const unsigned char *data, int dataLength)
{
CppSQLite3Statement statement = compileStatement(szSQL);
statement.bind(1, data, dataLength);
return statement.execDML();
}
void BaseDatabaseUnitImpl::runUpdateStatements()
{
loadDatabaseInfo();
if (database_)
{
uint32_t version = getDatabaseVersion();
if (version > 0)
{
auto it = databaseInfo_->updateMap_.find(boost::lexical_cast<std::string>(version + 1));
if (it != databaseInfo_->updateMap_.end())
{
try
{
for (it; it != databaseInfo_->updateMap_.end(); ++it)
{
for (auto it2 = it->second.tableList_.begin(); it2 != it->second.tableList_.end(); ++it2)
{
CppSQLite3Statement statement = database_->compileStatement(it2->statement_.c_str());
runTransaction(statement);
}
try
{
version = boost::lexical_cast<uint32_t>(it->first);
}
catch (...)
{
}
}
CppSQLite3Statement statement = database_->compileStatement(SQL_VERSION_TABLE_SET_VERSION);
statement.bind(1, VERSION_KEY);
statement.bind(2, (int)version);
runTransaction(statement);
}
catch (CppSQLite3Exception e)
{
LOG_ERR_R(DATABASE_MANAGER_LOG_TAG, _T("Failed to run update statements, error: %u"), e.errorCode());
LOG_ERR_D_A(DATABASE_MANAGER_LOG_TAG_A, "Message: %s", e.errorMessage());
}
}
}
}
}
void CVoxSQLite::InsertStreetAddress( int nId, StreetAddress& addr )
{
std::string strSql = "INSERT INTO StreetAddress "
"( [profile_id], [type], [street], [locality], [region], [postcode], [country], [visibility] ) "
"VALUES( ?, ?, ?, ?, ?, ?, ?, ? ); ";
try
{
//TODO: compile as a member var?
CppSQLite3Statement stmt = m_db.compileStatement( strSql.c_str() );
stmt.bind( 1, nId );
stmt.bind( 2, addr.getType().c_str() );
stmt.bind( 3, addr.getStreet1().c_str() );
stmt.bind( 4, addr.getCity().c_str() );
stmt.bind( 5, addr.getStateProvince().c_str());
stmt.bind( 6, addr.getPostalCode().c_str() );
stmt.bind( 7, addr.getCountry().c_str() );
stmt.bind( 8, (int) addr.getVisibility() );
stmt.execDML(); //We expect a return value == 1 (Number of rows changed);
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
int CppSQLite3DB::updateBlob(const CString& szTableName, const CString& szFieldName, const unsigned char* data, int dataLength, const CString& szWhere)
{
CString sql = CString("update ") + szTableName + " set " + szFieldName + "=? where " + szWhere;
CppSQLite3Statement statement = compileStatement(sql);
statement.bind(1, data, dataLength);
return statement.execDML();
}
BOOL DatabaseModule_Impl::sqlInsertFileTransferHistory(IN TransferFileEntity& fileInfo)
{
if (fileInfo.nClientMode == IM::BaseDefine::ClientFileRole::CLIENT_OFFLINE_UPLOAD
|| fileInfo.nClientMode == IM::BaseDefine::ClientFileRole::CLIENT_REALTIME_SENDER)
{
LOG__(DEBG, _T("fileInfo.nClientMode not fixed"));
return FALSE;
}
try
{
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(insertFileTransferHistorySql.c_str());
stmt.bind(1, fileInfo.sTaskID.c_str());
stmt.bind(2, fileInfo.sFromID.c_str());
std::string filename = util::cStringToString(fileInfo.getRealFileName());
stmt.bind(3, filename.c_str());
std::string savePath = util::cStringToString(fileInfo.getSaveFilePath());
stmt.bind(7, savePath.c_str());
stmt.bind(8, (Int32)fileInfo.nFileSize);
stmt.bind(9, time(0));
stmt.execDML();
}
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("failed,error msg:%s"), csErrMsg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("unknown exception"));
return FALSE;
}
return TRUE;
}
/** 해당 ID에 대한 전체 Message 삭제
*/
BOOL CMessageHelper::Delete(const char *szId, BYTE nMessageType)
{
if(!CheckDB()) return FALSE;
if(!Open()) return FALSE;
try
{
CppSQLite3Statement stmt;
if(nMessageType > 0)
{
stmt = m_SqliteHelper.compileStatement(
"DELETE FROM MessageTbl "
"WHERE targetId = ? AND messageType = ? ; ");
stmt.bind(1, szId);
stmt.bind(2, nMessageType);
} else {
stmt = m_SqliteHelper.compileStatement(
"DELETE FROM MessageTbl "
"WHERE targetId = ?; ");
stmt.bind(1, szId);
}
stmt.execDML();
stmt.finalize();
Close();
}
catch ( CppSQLite3Exception& e )
{
Close();
XDEBUG(ANSI_COLOR_RED "DB ERROR DELETE: %d %s\r\n" ANSI_NORMAL, e.errorCode(), e.errorMessage());
return FALSE;
}
return TRUE;
}
BOOL DatabaseModule_Impl::sqlBatchInsertRecentSessionInfos(IN std::vector<module::SessionEntity>& sessionList)
{
try
{
CppSQLite3Statement stmtBegin = m_pSqliteDB->compileStatement(BeginInsert.c_str());
stmtBegin.execDML();
for (module::SessionEntity sessionInfo : sessionList)
{
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(insertRecentSessionSql.c_str());
stmt.bind(1, sessionInfo.sessionID.c_str());
stmt.bind(2, int(sessionInfo.sessionType));
stmt.bind(3, int(sessionInfo.updatedTime));
stmt.bind(4, int(sessionInfo.latestmsgId));
stmt.bind(5, sessionInfo.latestMsgContent.c_str());
stmt.bind(6, sessionInfo.latestMsgFromId.c_str());
stmt.execDML();
}
CppSQLite3Statement stmtEnd = m_pSqliteDB->compileStatement(EndInsert.c_str());
stmtEnd.execDML();
}
catch (CppSQLite3Exception& e)
{
CString csErrMsg = util::stringToCString(e.errorMessage());
LOG__(ERR, _T("batch insert failed,error msg:%s"), csErrMsg);
CppSQLite3Statement stmtRollback = m_pSqliteDB->compileStatement(RollBack.c_str());
stmtRollback.execDML();
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("batch insert unknown exception"));
return FALSE;
}
return TRUE;
}
BOOL DatabaseModule_Impl::sqlBatchInsertGroupInfos(IN module::GroupInfoMap& mapGroupInfos)
{
try
{
CppSQLite3Statement stmtBegin = m_pSqliteDB->compileStatement(BeginInsert.c_str());
stmtBegin.execDML();
for (auto kv : mapGroupInfos)
{
module::GroupInfoEntity& groupInfo = kv.second;
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(insertGroupInfoSql.c_str());
stmt.bind(1, groupInfo.gId.c_str());
stmt.bind(2, util::cStringToString(groupInfo.csName).c_str());
stmt.bind(3, util::cStringToString(groupInfo.desc).c_str());
stmt.bind(4, groupInfo.avatarUrl.c_str());
stmt.bind(5, groupInfo.creatorId.c_str());
stmt.bind(6, int(groupInfo.type));
stmt.bind(7, int(groupInfo.version));
stmt.bind(8, int(groupInfo.groupUpdated));
stmt.bind(9, int(groupInfo.shieldStatus));
std::string& strJson = _makeJsonForGroupMembers(groupInfo.groupMemeberList);
stmt.bind(10, strJson.c_str());
stmt.execDML();
}
CppSQLite3Statement stmtEnd = m_pSqliteDB->compileStatement(EndInsert.c_str());
stmtEnd.execDML();
}
catch (CppSQLite3Exception& e)
{
CString csErrMsg = util::stringToCString(e.errorMessage());
LOG__(ERR, _T("batch insert failed,error msg:%s"), csErrMsg);
CppSQLite3Statement stmtRollback = m_pSqliteDB->compileStatement(RollBack.c_str());
stmtRollback.execDML();
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("batch insert unknown exception"));
return FALSE;
}
return TRUE;
}
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;
}
/** Message Queuing.
*
* @param szId Sensor ID
* @param nMessageId 사용자 정의 Message ID
* @param nMessageType Message Type (0x01 Immediately, 0x02 Lazy, 0x03 Passive)
* @param nDuration Lazy, Passive 일 경우 유지 시간(sec)
* @param nErrorHandler Error Handler
* @param nPreHandler Pre-Action Handler
* @param nPostHandler Post-Action Handler
* @param nUserData User Data
* @param nDataLength Message length
* @param pszData Message
*
*/
BOOL CMessageHelper::Add(const char *szId, UINT nMessageId, BYTE nMessageType, UINT nDuration,
UINT nErrorHandler, UINT nPreHandler, UINT nPostHandler, UINT nUserData, int nDataLength, const BYTE *pszData)
{
int i=1;
if(!CheckDB()) return FALSE;
if(!Open()) return FALSE;
try
{
CppSQLite3Statement stmt = m_SqliteHelper.compileStatement(
"INSERT INTO MessageTbl "
"( targetId, messageId, messageType, duration, "
" errorHandler, preHandler, postHandler, userData, payload ) "
"VALUES "
"( ?, ?, ?, ?, "
" ?, ?, ?, ?, ?);");
stmt.bind(i, szId); i++;
stmt.bind(i, (const int)nMessageId); i++;
stmt.bind(i, nMessageType); i++;
stmt.bind(i, (const int)nDuration); i++;
stmt.bind(i, (const int)nErrorHandler); i++;
stmt.bind(i, (const int)nPreHandler); i++;
stmt.bind(i, (const int)nPostHandler); i++;
stmt.bind(i, (const int)nUserData); i++;
stmt.bind(i, pszData, nDataLength); i++;
stmt.execDML();
stmt.finalize();
Close();
return TRUE;
}
catch ( CppSQLite3Exception& e )
{
Close();
XDEBUG(ANSI_COLOR_RED "DB ERROR: %d %s\r\n" ANSI_NORMAL, e.errorCode(), e.errorMessage());
}
return FALSE;
}
BOOL DatabaseModule_Impl::sqlUpdateGroupInfoEntity(std::string& sId, IN const module::GroupInfoEntity& groupInfo)
{
try
{
//"update groupinfo set name=?,desc=?,avatarUrl=?,creatorId=?,type=?,version=?,lastUpdateTime=?,shieldStatus=?,memberlist=? where groupId=?";
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(updateGroupInfoBySIdSql.c_str());
stmt.bind(1, util::cStringToString(groupInfo.csName).c_str());
stmt.bind(2, util::cStringToString(groupInfo.desc).c_str());
stmt.bind(3, groupInfo.avatarUrl.c_str());
stmt.bind(4, groupInfo.creatorId.c_str());
stmt.bind(5, int(groupInfo.type));
stmt.bind(6, int(groupInfo.version));
stmt.bind(7, int(groupInfo.groupUpdated));
stmt.bind(8, int(groupInfo.shieldStatus));
std::string& strJson = _makeJsonForGroupMembers(groupInfo.groupMemeberList);
stmt.bind(9, strJson.c_str());
stmt.bind(9, groupInfo.gId.c_str());
int countUpdate = stmt.execDML();
if (0 == countUpdate)
{
LOG__(ERR, _T("db update failed:%s"), util::stringToCString(groupInfo.gId));
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 (...)
{
return FALSE;
}
return TRUE;
}
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;
}
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::InsertProfile( const char* username, Profile& rProfile )
{
//TODO: compile as a member var?
std::string strSql = "INSERT INTO Profile "
"( [username], [first_name], [last_name], [alias], [sms_signature], [company], [notes], [sex] ) "
// "[birthday], [photo] ) " //TODO
"VALUES( ?, ?, ?, ?, ?, ?, ?, ? ); ";
try
{
CppSQLite3Statement stmt = m_db.compileStatement( strSql.c_str() );
stmt.bind( 1, username );
stmt.bind( 2, rProfile.getFirstName().c_str() );
stmt.bind( 3, rProfile.getLastName().c_str() );
stmt.bind( 4, rProfile.getAlias().c_str() );
stmt.bind( 5, rProfile.getSmsSignature().c_str());
stmt.bind( 6, rProfile.getCompany().c_str() );
stmt.bind( 7, rProfile.getNotes().c_str() );
stmt.bind( 8, rProfile.getSex() );
// stmt.bind( 10, rProfile.getBirthday() );
// stmt.bind( 11, rProfile.getPhoto() );
stmt.execDML(); //We expect a return value == 1 (Number of rows changed);
stmt.reset();
int nId = (int)m_db.lastRowId();
InsertUrls ( nId, rProfile.getUrls() );
InsertStreetAddresses( nId, rProfile.getStreetAddresses() );
InsertEmailAddresses ( nId, rProfile.getEmailAddresses() );
InsertTelephones ( nId, rProfile.getTelephones() );
}
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;
}
BOOL DatabaseModule_Impl::sqlBatchInsertMessage(IN std::list<MessageEntity>& msgList)
{
if (module::TCPCLIENT_STATE_DISCONNECT == module::getTcpClientModule()->getTcpClientNetState())
{
LOG__(ERR, _T("TCPCLIENT_STATE_DISCONNECT"));
return FALSE;
}
if (msgList.empty())
{
LOG__(ERR, _T("msgList is empty!"));
return FALSE;
}
MessageEntity msg;
try
{
CppSQLite3Statement stmtBegin = m_pSqliteDB->compileStatement(BeginInsert.c_str());
stmtBegin.execDML();
std::list<MessageEntity>::iterator iter = msgList.begin();
for (; iter != msgList.end(); ++iter)
{
MessageEntity msg = *iter;
if (msg.msgId <= 0)//非法的msg消息不用存储
{
std::string msgDecrptyCnt;
DECRYPT_MSG(msg.content,msgDecrptyCnt);
LOG__(ERR, _T("msgid <= 0, msgid:%d msg_content:%s Don't save to DB!")
, msg.msgId, util::stringToCString(msgDecrptyCnt));
continue;
}
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(insertMessageSql.c_str());
stmt.bind(1, (Int32)msg.msgId);
stmt.bind(2, msg.sessionId.c_str());
stmt.bind(3, msg.talkerSid.c_str());
//对语音消息做个特殊处理，content存储的是json格式字符串
if (MESSAGE_RENDERTYPE_AUDIO == msg.msgRenderType)
{
Json::Value root;
root["msgAudioTime"] = msg.msgAudioTime;
root["msgAudioId"] = msg.content;
Json::FastWriter fstWrite;
std::string audioContent = fstWrite.write(root);
stmt.bind(4, audioContent.c_str());
}
else
{
stmt.bind(4, msg.content.c_str());
}
stmt.bind(5, msg.msgRenderType);
stmt.bind(6, msg.msgSessionType);
stmt.bind(7, (Int32)msg.msgTime);
stmt.bind(8, time(0));
stmt.execDML();
}
CppSQLite3Statement stmtEnd = m_pSqliteDB->compileStatement(EndInsert.c_str());
stmtEnd.execDML();
}
catch (CppSQLite3Exception& e)
{
CString csErrMsg = util::stringToCString(e.errorMessage());
LOG__(ERR, _T("db failed,error msg:%s"), csErrMsg);
CppSQLite3Statement stmtRollback = m_pSqliteDB->compileStatement(RollBack.c_str());
stmtRollback.execDML();
_msgToTrace(msg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
_msgToTrace(msg);
return FALSE;
}
return TRUE;
}
BOOL DatabaseModule_Impl::sqlInsertMessage(IN MessageEntity& msg)
{
if (module::TCPCLIENT_STATE_DISCONNECT == module::getTcpClientModule()->getTcpClientNetState()
|| MESSAGE_RENDERTYPE_SYSTEMTIPS == msg.msgRenderType)
{
return FALSE;
}
if (msg.msgId <= 0)
{
std::string msgDecrptyCnt;
DECRYPT_MSG(msg.content, msgDecrptyCnt);
LOG__(ERR, _T("msgid <= 0, msgid:%d msg_content:%s Don't save to DB!")
, msg.msgId, util::stringToCString(msgDecrptyCnt));
return FALSE;
}
try
{
CppSQLite3Statement stmt = m_pSqliteDB->compileStatement(insertMessageSql.c_str());
stmt.bind(1, (Int32)msg.msgId);
stmt.bind(2, msg.sessionId.c_str());
stmt.bind(3, msg.talkerSid.c_str());
//对语音消息做个特殊处理，content存储的是json格式字符串
if (MESSAGE_RENDERTYPE_AUDIO == msg.msgRenderType)
{
Json::Value root;
root["msgAudioTime"] = msg.msgAudioTime;
root["msgAudioId"] = msg.content;
Json::FastWriter fstWrite;
std::string audioContent = fstWrite.write(root);
stmt.bind(4, audioContent.c_str());
}
else
{
stmt.bind(4, msg.content.c_str());
}
stmt.bind(5, msg.msgRenderType);
stmt.bind(6, msg.msgSessionType);
stmt.bind(7, (Int32)msg.msgTime);
stmt.bind(8, time(0));
stmt.execDML();
}
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);
_msgToTrace(msg);
return FALSE;
}
catch (...)
{
LOG__(ERR, _T("db unknown exception"));
_msgToTrace(msg);
return FALSE;
}
return TRUE;
}
EXPORT_C gint32 CContactDb::UpdateEntity(CDbEntity * pEntity)
{
int i;
char sql[256] = {0};
OpenDatabase();
try
{
m_dbBeluga.execDML("begin transaction;");
/* update contact entity */
strcpy(sql, "update contact set ");
for (i=1; i<ContactField_EndFlag; i++)
{
GString * fieldName = (GString*)g_ptr_array_index(m_pFieldsName, i);
strcat(sql, fieldName->str);
strcat(sql, " = ?");
if (i != ContactField_EndFlag - 1)
strcat(sql, ", ");
}
strcat(sql, "where cid = ?;");
CppSQLite3Statement statement = m_dbBeluga.compileStatement(sql);
GString * idValue = NULL;
if (ECode_No_Error == pEntity->GetFieldValue(0, &idValue))
{
statement.bind(ContactField_EndFlag, idValue->str);
g_string_free(idValue, TRUE);
}
else
statement.bindNull(ContactField_EndFlag);
for (i=1; i<ContactField_EndFlag; i++)
{
GString * fieldValue = NULL;
if (ECode_No_Error == pEntity->GetFieldValue(i, &fieldValue))
{
statement.bind(i, fieldValue->str);
g_string_free(fieldValue, TRUE);
}
else
statement.bindNull(i);
}
statement.execDML();
statement.reset();
/* update contact_ext entity */
CContact * contact = (CContact*)pEntity;
GString * fieldValue = NULL;
contact->GetFieldValue(ContactField_Type, &fieldValue);
if (ContactType_Phone == atoi(fieldValue->str))
{
CPhoneContact * phonecontact = (CPhoneContact*)pEntity;
GString * fieldId = NULL;
phonecontact->GetFieldValue(ContactField_Id, &fieldId);
memset(sql, 0, sizeof(sql));
sprintf(sql, "delete from contact_ext where cid = %d;", atoi(fieldId->str));
m_dbBeluga.execDML(sql);
/* insert phones */
GHashTable * phones = NULL;
phonecontact->GetAllPhones(&phones);
g_hash_table_foreach(phones, update_contact_ext_row, phonecontact);
g_hash_table_destroy(phones);
/* insert emails */
GHashTable * emails = NULL;
phonecontact->GetAllEmails(&emails);
g_hash_table_foreach(emails, update_contact_ext_row, phonecontact);
g_hash_table_destroy(emails);
/* insert ims */
GHashTable * ims = NULL;
phonecontact->GetAllIMs(&ims);
g_hash_table_foreach(ims, update_contact_ext_row, phonecontact);
g_hash_table_destroy(ims);
/* insert addresses */
GPtrArray * addresses = NULL;
phonecontact->GetAllAddresses(&addresses);
for (guint32 j=0; j<addresses->len; j++)
{
memset(sql, 0, sizeof(sql));
stAddress * addr = (stAddress*)g_ptr_array_index(addresses, j);
sprintf(sql, "insert into address values(NULL, %d, '%s', '%s', '%s', '%s', '%s', '%s');",
addr->atype, addr->block, addr->street, addr->district,
addr->city, addr->state, addr->country, addr->postcode);
guint32 nAddrId = m_dbBeluga.execScalar("select max(aid) from address;");
memset(sql, 0, sizeof(sql));
sprintf(sql, "insert into contact_ext values(NULL, %d, %d, %d);",
atoi(fieldId->str), addr->atype, nAddrId);
m_dbBeluga.execDML(sql);
}
freeAddressArray(addresses);
g_string_free(fieldId, TRUE);
}
g_string_free(fieldValue, TRUE);
m_dbBeluga.execDML("commit transaction;");
delete pEntity;
pEntity = NULL;
CloseDatabase();
return 0;
}
catch(CppSQLite3Exception& e)
{
m_dbBeluga.execDML("rollback transaction;");
delete pEntity;
pEntity = NULL;
CloseDatabase();
return ERROR(ESide_Client, EModule_Db, ECode_Update_Failed);
}
return 0;
}
/** 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;
}
void testCppSQLite()
{
try
{
int i, fld;
time_t tmStart, tmEnd;
remove(gszFile);
CppSQLite3DB* db = getSQLiteDB();
cout << "SQLite Version: " << db->SQLiteVersion() << endl;
cout << endl << "Creating emp table" << endl;
db->execDML("create table emp(empno int, empname char(20));");
///////////////////////////////////////////////////////////////
// Execute some DML, and print number of rows affected by each one
///////////////////////////////////////////////////////////////
cout << endl << "DML tests" << endl;
int nRows = db->execDML("insert into emp values (7, 'David Beckham');");
cout << nRows << " rows inserted" << endl;
nRows = db->execDML(
"update emp set empname = 'Christiano Ronaldo' where empno = 7;");
cout << nRows << " rows updated" << endl;
nRows = db->execDML("delete from emp where empno = 7;");
cout << nRows << " rows deleted" << endl;
/////////////////////////////////////////////////////////////////
// Transaction Demo
// The transaction could just as easily have been rolled back
/////////////////////////////////////////////////////////////////
int nRowsToCreate(50000);
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
tmStart = time(0);
db->execDML("begin transaction;");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[128];
sprintf(buf, "insert into emp values (%d, 'Empname%06d');", i, i);
db->execDML(buf);
}
db->execDML("commit transaction;");
tmEnd = time(0);
////////////////////////////////////////////////////////////////
// Demonstrate CppSQLiteDB::execScalar()
////////////////////////////////////////////////////////////////
cout << db->execScalar("select count(*) from emp;")
<< " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was fast!)" << endl;
////////////////////////////////////////////////////////////////
// Re-create emp table with auto-increment field
////////////////////////////////////////////////////////////////
cout << endl << "Auto increment test" << endl;
db->execDML("drop table emp;");
db->execDML(
"create table emp(empno integer primary key, empname char(20));");
cout << nRows << " rows deleted" << endl;
for (i = 0; i < 5; i++)
{
char buf[128];
sprintf(buf,
"insert into emp (empname) values ('Empname%06d');", i+1);
db->execDML(buf);
cout << " primary key: " << db->lastRowId() << endl;
}
///////////////////////////////////////////////////////////////////
// Query data and also show results of inserts into auto-increment field
//////////////////////////////////////////////////////////////////
cout << endl << "Select statement test" << endl;
CppSQLite3Query q = db->execQuery("select * from emp order by 1;");
for (fld = 0; fld < q.numFields(); fld++)
{
cout << q.fieldName(fld) << "(" << q.fieldDeclType(fld) << ")|";
}
cout << endl;
while (!q.eof())
{
cout << q.fieldValue(0) << "|";
cout << q.fieldValue(1) << "|" << endl;
q.nextRow();
}
///////////////////////////////////////////////////////////////
// SQLite's printf() functionality. Handles embedded quotes and NULLs
////////////////////////////////////////////////////////////////
cout << endl << "SQLite sprintf test" << endl;
CppSQLite3Buffer bufSQL;
bufSQL.format("insert into emp (empname) values (%Q);", "He's bad");
cout << (const char*)bufSQL << endl;
db->execDML(bufSQL);
bufSQL.format("insert into emp (empname) values (%Q);", NULL);
cout << (const char*)bufSQL << endl;
db->execDML(bufSQL);
////////////////////////////////////////////////////////////////////
// Fetch table at once, and also show how to
// use CppSQLiteTable::setRow() method
//////////////////////////////////////////////////////////////////
cout << endl << "getTable() test" << endl;
CppSQLite3Table t = db->getTable("select * from emp order by 1;");
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldName(fld) << "|";
}
cout << endl;
for (int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
for (int fld = 0; fld < t.numFields(); fld++)
{
if (!t.fieldIsNull(fld))
cout << t.fieldValue(fld) << "|";
else
cout << "NULL" << "|";
}
cout << endl;
}
////////////////////////////////////////////////////////////////////
// Test CppSQLiteBinary by storing/retrieving some binary data, checking
// it afterwards to make sure it is the same
//////////////////////////////////////////////////////////////////
cout << endl << "Binary data test" << endl;
db->execDML("create table bindata(desc char(10), data blob);");
unsigned char bin[256];
CppSQLite3Binary blob;
for (i = 0; i < sizeof bin; i++)
{
bin[i] = i;
}
blob.setBinary(bin, sizeof bin);
bufSQL.format("insert into bindata values ('testing', %Q);",
blob.getEncoded());
db->execDML(bufSQL);
cout << "Stored binary Length: " << sizeof bin << endl;
q = db->execQuery("select data from bindata where desc = 'testing';");
if (!q.eof())
{
blob.setEncoded((unsigned char*)q.fieldValue("data"));
cout << "Retrieved binary Length: "
<< blob.getBinaryLength() << endl;
}
const unsigned char* pbin = blob.getBinary();
for (i = 0; i < sizeof bin; i++)
{
if (pbin[i] != i)
{
cout << "Problem: i: ," << i << " bin[i]: "
<< pbin[i] << endl;
}
}
/////////////////////////////////////////////////////////
// Pre-compiled Statements Demo
/////////////////////////////////////////////////////////////
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
db->execDML("drop table emp;");
db->execDML("create table emp(empno int, empname char(20));");
tmStart = time(0);
db->execDML("begin transaction;");
CppSQLite3Statement stmt = db->compileStatement(
"insert into emp values (?, ?);");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[16];
sprintf(buf, "EmpName%06d", i);
stmt.bind(1, i);
stmt.bind(2, buf);
stmt.execDML();
stmt.reset();
}
db->execDML("commit transaction;");
tmEnd = time(0);
cout << db->execScalar("select count(*) from emp;")
<< " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was even faster!)" << endl;
cout << endl << "End of tests" << endl;
}
catch (CppSQLite3Exception& e)
{
cerr << e.errorCode() << ":" << e.errorMessage() << endl;
}
}
