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