void CFileMgrDB::RemoveFile(CFileTaskItem * item, CONST BYTE * lpHash) { CStringA strCmd, strCmd2; if(lpHash != NULL && !isnulmd4(lpHash)) { CHAR strHash[33]; md4strA(lpHash, strHash); strCmd.Format("SELECT id FROM files WHERE hash='%s'", strHash); strCmd2.Format("DELETE FROM files WHERE hash='%s'", strHash); } else { CStringA strUrl = StrToUtf8(item->m_strUrl); strUrl.Replace("'", "''"); strCmd.Format("SELECT id FROM files WHERE url='%s'", strUrl); strCmd2.Format("DELETE FROM files WHERE url='%s'", strUrl); } if(!m_db->Prepare(strCmd)) return; if(!m_db->Step()) { m_db->Finalize(); return; } UINT id = m_db->GetColInt(0); strCmd.Format("DELETE FROM urls WHERE fid=%d", id); SQL_EXEC(strCmd); SQL_EXEC(strCmd2); }
void CFileMgrDB::UpdateFiles(CRBMap<FILEKEY,CFileTaskItem*> & FileList) { CStringA strCmd, strCmd2; CHAR strHash[33]; m_db->Exec("begin transaction"); POSITION pos = FileList.GetHeadPosition(); while (pos) { FILEKEY fk = FileList.GetKeyAt(pos); CFileTaskItem *item = FileList.GetNextValue(pos); md4strA(fk.key, strHash); CStringA strPath = StrToUtf8(item->m_strFilePath), strUrl = StrToUtf8(item->m_strUrl), strName = StrToUtf8(item->m_FileName), strLink = StrToUtf8(item->m_strEd2kLink); strPath.Replace("'", "''"); strUrl.Replace("'", "''"); strName.Replace("'", "''"); strLink.Replace("'", "''"); strCmd2.Format("INSERT INTO files (hash, state, path, url, size, name, ed2klink, filetime, metbakid) " "VALUES ('%s',%u,'%s','%s',%I64u,'%s','%s',%I64u,%u)", strHash, item->m_nFileState, strPath, strUrl, item->m_FileSize, strName, strLink, item->m_tFiletime.GetTime(), item->m_metBakId); SQL_EXEC(strCmd2); } m_db->Exec("end transaction"); }
void CFileMgrDB::RemoveHashing(CString strPath) { CStringA strCmd, strFPath = StrToUtf8(strPath); strFPath.Replace("'", "''"); strCmd.Format("DELETE FROM hashing WHERE path='%s'", strFPath); SQL_EXEC(strCmd); }
void CFileMgrDB::UpdateHashing(CString strPath, INT state,BOOL bNew) { CStringA strCmd; strPath.Replace( _T("'"), _T("''") ); if(bNew) strCmd.Format("INSERT INTO hashing (path, state) VALUES ('%s', %u)", StrToUtf8(strPath), state); else strCmd.Format("REPLACE INTO hashing (path, state) VALUES ('%s', %u)", StrToUtf8(strPath), state); SQL_EXEC(strCmd); }
void CFileMgrDB::UpdateUrl(CFileTaskItem * item, CUrlSite * lpSite, CONST BYTE * lpHash) { CStringA strCmd, strUrl; if(lpHash != NULL && !isnulmd4(lpHash)) { CHAR strHash[33]; md4strA(lpHash, strHash); strCmd.Format("SELECT id FROM files WHERE hash='%s'", strHash); } else if(item->m_strUrl != "") { strUrl = StrToUtf8(item->m_strUrl); strUrl.Replace("'", "''"); strCmd.Format("SELECT id FROM files WHERE url='%s'", StrToUtf8(item->m_strUrl)); } else return; if(!m_db->Prepare(strCmd)) return; if(!m_db->Step()) { m_db->Finalize(); return; } UINT id = m_db->GetColInt(0); strUrl = StrToUtf8(lpSite->m_strUrl); strUrl.Replace("'", "''"); strCmd.Format("SELECT id FROM urls WHERE fid=%u AND url='%s'", id, strUrl); BOOL bReplace = FALSE; UINT uid = 0; if(m_db->Prepare(strCmd)) { if(m_db->Step()) { bReplace = TRUE; uid = m_db->GetColInt(0); } m_db->Finalize(); } if(bReplace) strCmd.Format("UPDATE urls SET fid=%u, url='%s', fromwhere=%u, transnopay=%I64u, transpay=%I64u, badsite=%d, pref=%d, needci=%u WHERE id=%u", id, strUrl, lpSite->m_dwFromWhere, lpSite->m_dwDataTransferedWithoutPayload, lpSite->m_dwDataTransferedWithPayload, lpSite->m_bBadSite ? 1 : 0, lpSite->m_dwInitPreference, lpSite->m_bNeedCommitted ? 1 : 0, uid); else strCmd.Format("INSERT INTO urls (fid, url, fromwhere, transnopay, transpay, badsite, pref, needci) VALUES (%u,'%s',%u,%I64u,%I64u,%d,%d,%u)", id, strUrl, lpSite->m_dwFromWhere, lpSite->m_dwDataTransferedWithoutPayload, lpSite->m_dwDataTransferedWithPayload, lpSite->m_bBadSite ? 1 : 0, lpSite->m_dwInitPreference, lpSite->m_bNeedCommitted ? 1 : 0); SQL_EXEC(strCmd); }
void CFileMgrDB::RemoveAll() { SQL_EXEC("DELETE FROM files"); SQL_EXEC("DELETE FROM urls"); SQL_EXEC("DELETE FROM hashing"); }
void CFileMgrDB::UpdateFile(CFileTaskItem * item, CONST BYTE * lpHash, BOOL bNew, BOOL bUpdateUrl) { CStringA strCmd, strCmd2; CHAR strHash[33]; UINT id = 0; if(lpHash != NULL && !isnulmd4(lpHash)) { md4strA(lpHash, strHash); strCmd.Format("SELECT id FROM files WHERE hash='%s'", strHash); if(m_db->Prepare(strCmd)) { if(m_db->Step()) { bNew = FALSE; id = m_db->GetColInt(0); } m_db->Finalize(); } } else if(item->m_strUrl != "") { strHash[0] = 0; strCmd.Format("SELECT id FROM files WHERE url='%s'", StrToUtf8(item->m_strUrl)); if(m_db->Prepare(strCmd)) { if(m_db->Step()) { bNew = FALSE; id = m_db->GetColInt(0); } m_db->Finalize(); } } else return; CStringA strPath = StrToUtf8(item->m_strFilePath), strUrl = StrToUtf8(item->m_strUrl), strName = StrToUtf8(item->m_FileName), strLink = StrToUtf8(item->m_strEd2kLink); strPath.Replace("'", "''"); strUrl.Replace("'", "''"); strName.Replace("'", "''"); strLink.Replace("'", "''"); if(bNew) strCmd2.Format("INSERT INTO files (hash, state, path, url, size, name, ed2klink, filetime, metbakid) " "VALUES ('%s',%u,'%s','%s',%I64u,'%s','%s',%I64u,%u)", strHash, item->m_nFileState, strPath, strUrl, item->m_FileSize, strName, strLink, item->m_tFiletime.GetTime(), item->m_metBakId); else strCmd2.Format("UPDATE files SET hash='%s', state=%u, path='%s', url='%s', size=%I64u, name='%s', ed2klink='%s', " "filetime=%I64u, metbakid=%u WHERE id=%u", strHash, item->m_nFileState, strPath, strUrl, item->m_FileSize,strName, strLink, item->m_tFiletime.GetTime(), item->m_metBakId, id); SQL_EXEC(strCmd2); if(bNew) { if(!m_db->Prepare(strCmd)) return; if(!m_db->Step()) { m_db->Finalize(); return; } id = m_db->GetColInt(0); } if(!bUpdateUrl) return; strCmd.Format("DELETE FROM urls WHERE fid=%d", id); SQL_EXEC(strCmd); POSITION pos = item->m_lMetaLinkURLList.GetHeadPosition(); while (pos) { CUrlSite pSite = item->m_lMetaLinkURLList.GetNext(pos); strUrl = StrToUtf8(pSite.m_strUrl); strUrl.Replace("'", "''"); strCmd.Format("INSERT INTO urls (fid, url, fromwhere, transnopay, transpay, badsite, pref, needci) VALUES (%u,'%s',%u,%I64u,%I64u,%d,%d,%u)", id, strUrl, pSite.m_dwFromWhere, pSite.m_dwDataTransferedWithoutPayload, pSite.m_dwDataTransferedWithPayload, pSite.m_bBadSite ? 1 : 0, pSite.m_dwInitPreference, pSite.m_bNeedCommitted ? 1 : 0); SQL_EXEC(strCmd); } }
void StorageAccess::checkDatabaseStructure() { QStringList lTables = mStorageDatabase.tables(); QSqlQuery q; if (!lTables.contains("profile")) { SQL_PREPARE(q, "create table profile (" "id INTEGER PRIMARY KEY AUTOINCREMENT," "service VARCHAR(16)," "name VARCHAR(32)," "level INTEGER," "token VARCHAR(64)," "last_access INTEGER," "UNIQUE (service,name)" ")" ); SQL_EXEC(q); } if (!lTables.contains("sessions")) { SQL_PREPARE(q, "create table sessions (" "id INTEGER PRIMARY KEY AUTOINCREMENT," "profile_id INTEGER REFERENCES profile (id) ON DELETE CASCADE ON UPDATE CASCADE," "started INTEGER," "finished INTEGER," "stream_title VARCHAR(200)," "UNIQUE (profile_id, started)" ")" ); SQL_EXEC(q); } if (!lTables.contains("statistics")) { SQL_PREPARE(q, "create table statistics (" "session_id INTEGER REFERENCES sessions (id) ON DELETE CASCADE ON UPDATE CASCADE," "curr_time INTEGER," "total_subscribers INTEGER," "total_followers INTEGER," "total_viewers INTEGER," "total_chatters INTEGER," "num_lines_chatted INTEGER," "num_words_chatted INTEGER," "PRIMARY KEY (session_id, curr_time)" ")" ); SQL_EXEC(q); } if (!lTables.contains("followers")) { SQL_PREPARE(q, "create table followers (" "profile_id INTEGER REFERENCES profile (id) ON DELETE CASCADE ON UPDATE CASCADE," "created INTEGER," "name VARCHAR(32)" ")" ); SQL_EXEC(q); SQL_PREPARE(q, "create index followers_idx1 on followers (profile_id, created)"); SQL_EXEC(q); SQL_PREPARE(q, "create index followers_idx2 on followers (profile_id, name, created)"); SQL_EXEC(q); } if (!lTables.contains("subscribers")) { SQL_PREPARE(q, "create table subscribers (" "profile_id INTEGER REFERENCES profile (id) ON DELETE CASCADE ON UPDATE CASCADE," "created INTEGER," "name VARCHAR(32)" ")" ); SQL_EXEC(q); SQL_PREPARE(q, "create index subscribers_idx1 on subscribers (profile_id, created)"); SQL_EXEC(q); SQL_PREPARE(q, "create index subscribers_idx2 on subscribers (profile_id, name, created)"); SQL_EXEC(q); } if (!lTables.contains("chatters")) { SQL_PREPARE(q, "create table chatters (" "session_id INTEGER REFERENCES sessions (id) ON DELETE CASCADE ON UPDATE CASCADE," "name VARCHAR(32)," "joined INTEGER," "parted INTEGER," "num_lines INTEGER," "num_highlights INTEGER" ")" ); SQL_EXEC(q); SQL_PREPARE(q, "create index chatters_idx1 on chatters (session_id, name, joined)"); SQL_EXEC(q); } }