CStdString CTextureDatabase::GetTextureForPath(const CStdString &url, const CStdString &type) { try { if (NULL == m_pDB.get()) return ""; if (NULL == m_pDS.get()) return ""; CStdString sql = PrepareSQL("select texture from path where url='%s' and type='%s'", url.c_str(), type.c_str()); m_pDS->query(sql.c_str()); if (!m_pDS->eof()) { // have some information CStdString texture = m_pDS->fv(0).get_asString(); m_pDS->close(); return texture; } m_pDS->close(); } catch (...) { CLog::Log(LOGERROR, "%s, failed on url '%s'", __FUNCTION__, url.c_str()); } return ""; }
bool CTextureDatabase::UpdateOldVersion(int version) { BeginTransaction(); try { if (version < 7) { // update all old thumb://foo urls to image://foo?size=thumb m_pDS->query("select id,texture from path where texture like 'thumb://%'"); while (!m_pDS->eof()) { unsigned int id = m_pDS->fv(0).get_asInt(); CURL url(m_pDS->fv(1).get_asString()); m_pDS2->exec(PrepareSQL("update path set texture='image://%s?size=thumb' where id=%u", url.GetHostName().c_str(), id)); m_pDS->next(); } m_pDS->query("select id, url from texture where url like 'thumb://%'"); while (!m_pDS->eof()) { unsigned int id = m_pDS->fv(0).get_asInt(); CURL url(m_pDS->fv(1).get_asString()); m_pDS2->exec(PrepareSQL("update texture set url='image://%s?size=thumb', urlhash=0 where id=%u", url.GetHostName().c_str(), id)); m_pDS->next(); } m_pDS->close(); } if (version < 8) { // get rid of old cached thumbs as they were previously set to the cached thumb name instead of the source thumb m_pDS->exec("delete from path"); } if (version < 9) { // get rid of the old path table and add the type column m_pDS->dropIndex("path", "idxPath"); m_pDS->exec("DROP TABLE path"); m_pDS->exec("CREATE TABLE path (id integer primary key, urlhash integer, url text, type text, texture text)\n"); m_pDS->exec("CREATE INDEX idxPath ON path(urlhash, type)"); } if (version < 10) { // get rid of urlhash in both tables... m_pDS->dropIndex("path", "idxPath"); m_pDS->exec("DROP TABLE path"); m_pDS->exec("CREATE TABLE path (id integer primary key, url text, type text, texture text)\n"); m_pDS->exec("CREATE INDEX idxPath ON path(url, type)"); m_pDS->dropIndex("texture", "idxTexture"); m_pDS->exec("CREATE TEMPORARY TABLE texture_backup(id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck)"); m_pDS->exec("INSERT INTO texture_backup SELECT id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck FROM texture"); m_pDS->exec("DROP TABLE texture"); m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, usecount integer, lastusetime text, imagehash text, lasthashcheck text)"); m_pDS->exec("CREATE INDEX idxTexture ON texture(url)"); m_pDS->exec("INSERT INTO texture SELECT * FROM texture_backup"); m_pDS->exec("DROP TABLE texture_backup"); } if (version < 11) { // get rid of cached URLs that don't have the correct extension m_pDS->exec("DELETE FROM texture WHERE SUBSTR(cachedUrl,-4,4) NOT IN ('.jpg', '.png')"); } } catch (...) { CLog::Log(LOGERROR, "%s(%d) failed", __FUNCTION__, version); RollbackTransaction(); return false; } CommitTransaction(); return true; }
bool CTextureDatabase::InvalidateCachedTexture(const CStdString &url) { CStdString date = (CDateTime::GetCurrentDateTime() - CDateTimeSpan(2, 0, 0, 0)).GetAsDBDateTime(); CStdString sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str()); return ExecuteQuery(sql); }
bool CTextureDatabase::UpdateOldVersion(int version) { if (version < 7) { // update all old thumb://foo urls to image://foo?size=thumb m_pDS->query("select id,texture from path where texture like 'thumb://%'"); while (!m_pDS->eof()) { unsigned int id = m_pDS->fv(0).get_asInt(); CURL url(m_pDS->fv(1).get_asString()); m_pDS2->exec(PrepareSQL("update path set texture='image://%s?size=thumb' where id=%u", url.GetHostName().c_str(), id)); m_pDS->next(); } m_pDS->query("select id, url from texture where url like 'thumb://%'"); while (!m_pDS->eof()) { unsigned int id = m_pDS->fv(0).get_asInt(); CURL url(m_pDS->fv(1).get_asString()); m_pDS2->exec(PrepareSQL("update texture set url='image://%s?size=thumb', urlhash=0 where id=%u", url.GetHostName().c_str(), id)); m_pDS->next(); } m_pDS->close(); } if (version < 8) { // get rid of old cached thumbs as they were previously set to the cached thumb name instead of the source thumb m_pDS->exec("delete from path"); } if (version < 9) { // get rid of the old path table and add the type column m_pDS->dropIndex("path", "idxPath"); m_pDS->exec("DROP TABLE path"); m_pDS->exec("CREATE TABLE path (id integer primary key, urlhash integer, url text, type text, texture text)\n"); m_pDS->exec("CREATE INDEX idxPath ON path(urlhash, type)"); } if (version < 10) { // get rid of urlhash in both tables... m_pDS->dropIndex("path", "idxPath"); m_pDS->exec("DROP TABLE path"); m_pDS->exec("CREATE TABLE path (id integer primary key, url text, type text, texture text)\n"); m_pDS->exec("CREATE INDEX idxPath ON path(url, type)"); m_pDS->dropIndex("texture", "idxTexture"); m_pDS->exec("CREATE TEMPORARY TABLE texture_backup(id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck)"); m_pDS->exec("INSERT INTO texture_backup SELECT id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck FROM texture"); m_pDS->exec("DROP TABLE texture"); m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, usecount integer, lastusetime text, imagehash text, lasthashcheck text)"); m_pDS->exec("CREATE INDEX idxTexture ON texture(url)"); m_pDS->exec("INSERT INTO texture SELECT * FROM texture_backup"); m_pDS->exec("DROP TABLE texture_backup"); } if (version < 11) { // get rid of cached URLs that don't have the correct extension m_pDS->exec("DELETE FROM texture WHERE SUBSTR(cachedUrl,-4,4) NOT IN ('.jpg', '.png')"); } if (version < 12) { // create new sizes table and move usecount info to it. m_pDS->exec("DROP TABLE texture"); m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, imagehash text, lasthashcheck text)"); m_pDS->exec("CREATE INDEX idxTexture ON texture(url)"); m_pDS->exec("CREATE TABLE sizes (idtexture integer, size integer, width integer, height integer, usecount integer, lastusetime text)"); m_pDS->exec("CREATE INDEX idxSize ON sizes(idtexture, size)"); m_pDS->exec("CREATE TRIGGER textureDelete AFTER delete ON texture FOR EACH ROW BEGIN delete from sizes where sizes.idtexture=old.id; END"); } if (version < 13) { // index for updateusecount m_pDS->exec("CREATE INDEX idxSize2 ON sizes(idtexture, width, height)"); } return true; }
bool CTextureDatabase::SetCachedTextureValid(const CStdString &url, bool updateable) { CStdString date = updateable ? CDateTime::GetCurrentDateTime().GetAsDBDateTime() : ""; CStdString sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str()); return ExecuteQuery(sql); }
bool CTextureDatabase::IncrementUseCount(const CTextureDetails &details) { CStdString sql = PrepareSQL("UPDATE sizes SET usecount=usecount+1, lastusetime=CURRENT_TIMESTAMP WHERE idtexture=%u AND width=%u AND height=%u", details.id, details.width, details.height); return ExecuteQuery(sql); }
wxString Model_Report::get_html(const Data* r) { mm_html_template report(r->TEMPLATECONTENT); r->to_template(report); loop_t contents; loop_t errors; row_t error; wxSQLite3ResultSet q; int columnCount = 0; wxString sql = r->SQLCONTENT; std::map <wxString, wxString> rep_params; try { PrepareSQL(sql, rep_params); wxSQLite3Statement stmt = this->db_->PrepareStatement(sql); if (!stmt.IsReadOnly()) { return wxString::Format(_("The SQL script:\n%s \nwill modify database! aborted!"), r->SQLCONTENT); } else { q = stmt.ExecuteQuery(); columnCount = q.GetColumnCount(); } } catch (const wxSQLite3Exception& e) { return e.GetMessage(); } std::map <std::wstring, int> colHeaders; loop_t columns; for (int i = 0; i < columnCount; ++i) { int col_type = q.GetColumnType(i); const std::wstring col_name = q.GetColumnName(i).ToStdWstring(); colHeaders[col_name] = col_type; row_t row; row(L"COLUMN") = col_name; columns += row; } report(L"COLUMNS") = columns; LuaGlue state; state. Class<Record>("Record"). ctor("new"). method("get", &Record::get). method("set", &Record::set). end().open().glue(); bool skip_lua = r->LUACONTENT.IsEmpty(); bool lua_status = state.doString(std::string(r->LUACONTENT.ToUTF8())); if (!skip_lua && !lua_status) { error(L"ERROR") = wxString("failed to doString : ") + r->LUACONTENT + wxString(" err: ") + wxString(state.lastError()); errors += error; } while (q.NextRow()) { Record rec; for (int i = 0; i < columnCount; ++i) { const wxString column_name = q.GetColumnName(i); rec[column_name.ToStdWstring()] = q.GetAsString(i); } if (lua_status && !skip_lua) { try { state.invokeVoidFunction("handle_record", &rec); } catch (const std::runtime_error& e) { error(L"ERROR") = wxString("failed to call handle_record : ") + wxString(e.what()); errors += error; } catch (const std::exception& e) { error(L"ERROR") = wxString("failed to call handle_record : ") + wxString(e.what()); errors += error; } catch (...) { error(L"ERROR") = L"failed to call handle_record "; errors += error; } } row_t row; for (const auto& item : rec) { row(item.first) = item.second; } contents += row; } q.Finalize(); Record result; if (lua_status && !skip_lua) { try { state.invokeVoidFunction("complete", &result); } catch (const std::runtime_error& e) { error(L"ERROR") = wxString("failed to call complete: ") + wxString(e.what()); errors += error; } catch (const std::exception& e) { error(L"ERROR") = wxString("failed to call complete: ") + wxString(e.what()); errors += error; } catch (...) { error(L"ERROR") = L"failed to call complete"; errors += error; } } for (const auto& item : result) report(item.first) = item.second; report(L"CONTENTS") = contents; { for (const auto& item : rep_params) { report(item.first.Upper().ToStdWstring()) = item.second; } auto p = mmex::getPathAttachment(mmAttachmentManage::InfotablePathSetting()); //javascript does not handle backslashs p.Replace("\\", "\\\\"); report(L"ATTACHMENTSFOLDER") = p; auto s = wxString(wxFileName::GetPathSeparator()); s.Replace("\\", "\\\\"); report(L"FILESEPARATOR") = s; report(L"LANGUAGE") = Option::instance().LanguageISO6391(); report(L"HTMLSCALE") = wxString::Format("%d", Option::instance().HtmlFontSize()); } report(L"ERRORS") = errors; wxString out = wxEmptyString; try { out = report.Process(); } catch (const syntax_ex& e) { return e.what(); } catch (...) { return _("Caught exception"); } return out; }
bool CViewDatabase::UpdateOldVersion(int version) { if (version < 4) m_pDS->exec("alter table view add skin text"); if (version < 5) { // translate legacy videodb:// and musicdb:// paths std::vector< std::pair<int, std::string> > paths; if (m_pDS->query("SELECT idView, path FROM view")) { while (!m_pDS->eof()) { std::string originalPath = m_pDS->fv(1).get_asString(); std::string path = originalPath; if (StringUtils::StartsWith(path, "musicdb://")) path = CLegacyPathTranslation::TranslateMusicDbPath(path); else if (StringUtils::StartsWith(path, "videodb://")) path = CLegacyPathTranslation::TranslateVideoDbPath(path); if (!StringUtils::EqualsNoCase(path, originalPath)) paths.push_back(std::make_pair(m_pDS->fv(0).get_asInt(), path)); m_pDS->next(); } m_pDS->close(); for (std::vector< std::pair<int, std::string> >::const_iterator it = paths.begin(); it != paths.end(); it++) m_pDS->exec(PrepareSQL("UPDATE view SET path='%s' WHERE idView=%d", it->second.c_str(), it->first).c_str()); } } if (version < 6) { // convert the "path" table m_pDS->exec("CREATE TABLE tmp_view AS SELECT * FROM view"); m_pDS->exec("DROP INDEX idxViews"); m_pDS->exec("DROP INDEX idxViewsWindow"); m_pDS->exec("DROP TABLE view"); m_pDS->exec("CREATE TABLE view (" "idView integer primary key," "window integer," "path text," "viewMode integer," "sortMethod integer," "sortOrder integer," "sortAttributes integer," "skin text)\n"); m_pDS->exec("CREATE INDEX idxViews ON view(path)"); m_pDS->exec("CREATE INDEX idxViewsWindow ON view(window)"); m_pDS->query("SELECT * FROM tmp_view"); while (!m_pDS->eof()) { SortDescription sorting = SortUtils::TranslateOldSortMethod((SORT_METHOD)m_pDS->fv(4).get_asInt()); CStdString sql = PrepareSQL("INSERT INTO view (idView, window, path, viewMode, sortMethod, sortOrder, sortAttributes, skin) VALUES (%i, %i, '%s', %i, %i, %i, %i, '%s')", m_pDS->fv(0).get_asInt(), m_pDS->fv(1).get_asInt(), m_pDS->fv(2).get_asString().c_str(), m_pDS->fv(3).get_asInt(), (int)sorting.sortBy, m_pDS->fv(5).get_asInt(), (int)sorting.sortAttributes, m_pDS->fv(6).get_asString().c_str()); m_pDS2->exec(sql); m_pDS->next(); } m_pDS->exec("DROP TABLE tmp_view"); } return true; }
bool CTextureDatabase::ClearCachedTexture(const std::string &url, std::string &cacheFile) { std::string id = GetSingleValue(PrepareSQL("select id from texture where url='%s'", url.c_str())); return !id.empty() ? ClearCachedTexture(strtol(id.c_str(), NULL, 10), cacheFile) : false; }