From f57b6fbfead058f3d1d738720573d17be738ef2b Mon Sep 17 00:00:00 2001 From: Ryan Rector Date: Wed, 29 May 2024 22:54:34 -0600 Subject: add database support for image cache cleaner --- xbmc/TextureDatabase.cpp | 65 ++++++++++++++++++++- xbmc/TextureDatabase.h | 16 +++++- xbmc/addons/AddonDatabase.cpp | 42 ++++++++++++++ xbmc/addons/AddonDatabase.h | 7 +++ xbmc/music/MusicDatabase.cpp | 53 +++++++++++++++++ xbmc/music/MusicDatabase.h | 6 ++ xbmc/video/VideoDatabase.cpp | 129 ++++++++++++++++++++++++++++++++++++++++++ xbmc/video/VideoDatabase.h | 7 +++ 8 files changed, 323 insertions(+), 2 deletions(-) diff --git a/xbmc/TextureDatabase.cpp b/xbmc/TextureDatabase.cpp index 8356c4be6b..aa851c1301 100644 --- a/xbmc/TextureDatabase.cpp +++ b/xbmc/TextureDatabase.cpp @@ -119,7 +119,8 @@ bool CTextureDatabase::Open() void CTextureDatabase::CreateTables() { CLog::Log(LOGINFO, "create texture table"); - m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, imagehash text, lasthashcheck text)"); + m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, " + "imagehash text, lasthashcheck text, lastlibrarycheck text)"); CLog::Log(LOGINFO, "create sizes table, index, and trigger"); m_pDS->exec("CREATE TABLE sizes (idtexture integer, size integer, width integer, height integer, usecount integer, lastusetime text)"); @@ -194,11 +195,18 @@ void CTextureDatabase::UpdateTables(int version) m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, imagehash text, lasthashcheck text)"); m_pDS->exec("CREATE TABLE sizes (idtexture integer, size integer, width integer, height integer, usecount integer, lastusetime text)"); } + if (version < 14) + { + m_pDS->exec("ALTER TABLE texture ADD lastlibrarycheck text"); + } } bool CTextureDatabase::IncrementUseCount(const CTextureDetails &details) { std::string 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); + if (!ExecuteQuery(sql)) + return false; + sql = PrepareSQL("UPDATE texture SET lastlibrarycheck=NULL WHERE id=%u", details.id); return ExecuteQuery(sql); } @@ -283,6 +291,61 @@ bool CTextureDatabase::GetTextures(CVariant &items, const Filter &filter) return false; } +std::vector CTextureDatabase::GetOldestCachedImages(unsigned int maxImages) const +{ + try + { + if (!m_pDB || !m_pDS) + return {}; + + // PVR manages own image cache, so exclude from here: + // `WHERE url NOT LIKE 'image://pvr%%' AND url NOT LIKE 'image://epg%%'` + // "re-check" between minimum of 30 days and maximum of total time required to check all + // current images by maxImages 4 times per day, in case of very many images in library. + std::string sql = PrepareSQL( + "SELECT url FROM texture JOIN sizes ON (texture.id=sizes.idtexture AND sizes.size=1) WHERE " + "url NOT LIKE 'image://pvr%%' AND url NOT LIKE 'image://epg%%' AND lastusetime < " + "datetime('now', '-30 days') AND (lastlibrarycheck IS NULL OR lastlibrarycheck < " + "datetime('now', '-'||min((select (count(*) / %u / 4) + 1 from texture WHERE url NOT LIKE " + "'image://pvr%%' AND url NOT LIKE 'image://epg%%'), max(30, (julianday(lastlibrarycheck) - " + "julianday(sizes.lastusetime)) / 2))||' days')) ORDER BY COALESCE(lastlibrarycheck, " + "lastusetime) ASC LIMIT %u", + maxImages, maxImages); + + if (!m_pDS->query(sql)) + return {}; + + std::vector result; + while (!m_pDS->eof()) + { + result.push_back(m_pDS->fv(0).get_asString()); + m_pDS->next(); + } + m_pDS->close(); + return result; + } + catch (...) + { + CLog::Log(LOGERROR, "{}, failed", __FUNCTION__); + } + return {}; +} + +bool CTextureDatabase::SetKeepCachedImages(const std::vector& imagesToKeep) +{ + if (!imagesToKeep.size()) + return true; + + std::string sql = "UPDATE texture SET lastlibrarycheck=CURRENT_TIMESTAMP WHERE url IN ("; + for (const auto& image : imagesToKeep) + { + sql += PrepareSQL("'%s',", image.c_str()); + } + sql.pop_back(); // remove last ',' + sql += ")"; + return ExecuteQuery(sql); +} + bool CTextureDatabase::SetCachedTextureValid(const std::string &url, bool updateable) { std::string date = updateable ? CDateTime::GetCurrentDateTime().GetAsDBDateTime() : ""; diff --git a/xbmc/TextureDatabase.h b/xbmc/TextureDatabase.h index 341e94ede2..a0430020ce 100644 --- a/xbmc/TextureDatabase.h +++ b/xbmc/TextureDatabase.h @@ -86,6 +86,20 @@ public: bool GetTextures(CVariant &items, const Filter &filter); + /*! + * @brief Get a list of the oldest cached images eligible for cleaning. + * @param maxImages the maximum number of images to return + * @return + */ + std::vector GetOldestCachedImages(unsigned int maxImages) const; + + /*! + * @brief Set a list of images to be kept. Used to clean the image cache. + * @param imagesToKeep + * @return + */ + bool SetKeepCachedImages(const std::vector& imagesToKeep); + // rule creation CDatabaseQueryRule *CreateRule() const override; CDatabaseQueryRuleCombination *CreateCombination() const override; @@ -100,6 +114,6 @@ protected: void CreateTables() override; void CreateAnalytics() override; void UpdateTables(int version) override; - int GetSchemaVersion() const override { return 13; } + int GetSchemaVersion() const override { return 14; } const char* GetBaseDBName() const override { return "Textures"; } }; diff --git a/xbmc/addons/AddonDatabase.cpp b/xbmc/addons/AddonDatabase.cpp index 29837be2f6..20cddcd362 100644 --- a/xbmc/addons/AddonDatabase.cpp +++ b/xbmc/addons/AddonDatabase.cpp @@ -1252,3 +1252,45 @@ bool CAddonDatabase::AddInstalledAddon(const std::shared_ptr& addon, return true; } + +namespace +{ +bool IsAddonImageChecked(const std::string& addonImage, + const std::vector& imagesToCheck) +{ + if (addonImage.empty()) + return false; + + auto found = std::find(imagesToCheck.begin(), imagesToCheck.end(), addonImage); + return found != imagesToCheck.end(); +} +} // namespace + +std::vector CAddonDatabase::GetUsedImages( + const std::vector& imagesToCheck) const +{ + if (!imagesToCheck.size()) + return {}; + + VECADDONS allAddons; + if (!GetRepositoryContent(allAddons)) + return imagesToCheck; + + std::vector result; + for (const auto& addon : allAddons) + { + if (IsAddonImageChecked(addon->Icon(), imagesToCheck)) + result.push_back(addon->Icon()); + for (const auto& screenshot : addon->Screenshots()) + { + if (IsAddonImageChecked(screenshot, imagesToCheck)) + result.push_back(screenshot); + } + for (const auto& artPair : addon->Art()) + { + if (IsAddonImageChecked(artPair.second, imagesToCheck)) + result.push_back(artPair.second); + } + } + return result; +} diff --git a/xbmc/addons/AddonDatabase.h b/xbmc/addons/AddonDatabase.h index 72caa8564b..5a19a2d4e5 100644 --- a/xbmc/addons/AddonDatabase.h +++ b/xbmc/addons/AddonDatabase.h @@ -230,6 +230,13 @@ public: */ bool AddInstalledAddon(const std::shared_ptr& addon, const std::string& origin); + /*! + * @brief Check the passed in list of images if used in this database. Used to clean the image cache. + * @param imagesToCheck + * @return a list of the passed in images used by this database. + */ + std::vector GetUsedImages(const std::vector& imagesToCheck) const; + protected: void CreateTables() override; void CreateAnalytics() override; diff --git a/xbmc/music/MusicDatabase.cpp b/xbmc/music/MusicDatabase.cpp index 1e5ba67fd3..33896766ba 100644 --- a/xbmc/music/MusicDatabase.cpp +++ b/xbmc/music/MusicDatabase.cpp @@ -13953,3 +13953,56 @@ bool CMusicDatabase::GetResumeBookmarkForAudioBook(const CFileItem& item, int& b bookmark = m_pDS->fv(0).get_asInt(); return true; } + +std::vector CMusicDatabase::GetUsedImages( + const std::vector& imagesToCheck) const +{ + try + { + if (!m_pDB || !m_pDS) + return imagesToCheck; + + if (!imagesToCheck.size()) + return {}; + + int artworkLevel = CServiceBroker::GetSettingsComponent()->GetSettings()->GetInt( + CSettings::SETTING_MUSICLIBRARY_ARTWORKLEVEL); + if (artworkLevel == CSettings::MUSICLIBRARY_ARTWORK_LEVEL_NONE) + { + return {}; + } + + std::string sql = "SELECT DISTINCT url FROM art WHERE url IN ("; + for (const auto& image : imagesToCheck) + { + sql += PrepareSQL("'%s',", image.c_str()); + } + sql.pop_back(); // remove last ',' + sql += ")"; + + // add arttype filters if set to "Basic" + if (artworkLevel == CSettings::MUSICLIBRARY_ARTWORK_LEVEL_BASIC) + { + sql += PrepareSQL(" AND (media_type = 'album' AND type = 'thumb' OR media_type = 'artist' " + "AND type IN ('thumb', 'fanart'))"); + } + + if (!m_pDS->query(sql)) + return {}; + + std::vector result; + while (!m_pDS->eof()) + { + result.push_back(m_pDS->fv(0).get_asString()); + m_pDS->next(); + } + m_pDS->close(); + + return result; + } + catch (...) + { + CLog::Log(LOGERROR, "{}, failed", __FUNCTION__); + } + return {}; +} diff --git a/xbmc/music/MusicDatabase.h b/xbmc/music/MusicDatabase.h index 20022f85a2..f2aca6258e 100644 --- a/xbmc/music/MusicDatabase.h +++ b/xbmc/music/MusicDatabase.h @@ -882,6 +882,12 @@ public: std::string GetAlbumsLastModified(); std::string GetArtistsLastModified(); + /*! + * @brief Check the passed in list of images if used in this database. Used to clean the image cache. + * @param imagesToCheck + * @return a list of the passed in images used by this database. + */ + std::vector GetUsedImages(const std::vector& imagesToCheck) const; protected: std::map m_genreCache; diff --git a/xbmc/video/VideoDatabase.cpp b/xbmc/video/VideoDatabase.cpp index cc7bda1fd7..7a837b6f11 100644 --- a/xbmc/video/VideoDatabase.cpp +++ b/xbmc/video/VideoDatabase.cpp @@ -33,6 +33,7 @@ #include "guilib/GUIWindowManager.h" #include "guilib/LocalizeStrings.h" #include "guilib/guiinfo/GUIInfoLabels.h" +#include "imagefiles/ImageFileURL.h" #include "interfaces/AnnouncementManager.h" #include "messaging/helpers/DialogOKHelper.h" #include "music/Artist.h" @@ -12750,3 +12751,131 @@ void CVideoDatabase::SetVideoVersionDefaultArt(int dbId, int idFrom, VideoDbCont SetArtForItem(dbId, MediaTypeVideoVersion, it.first, it.second); } } + +std::vector CVideoDatabase::GetUsedImages( + const std::vector& imagesToCheck) +{ + try + { + if (!m_pDB || !m_pDS) + return imagesToCheck; + + if (!imagesToCheck.size()) + return {}; + + int artworkLevel = CServiceBroker::GetSettingsComponent()->GetSettings()->GetInt( + CSettings::SETTING_VIDEOLIBRARY_ARTWORK_LEVEL); + if (artworkLevel == CSettings::VIDEOLIBRARY_ARTWORK_LEVEL_NONE) + { + return {}; + } + + // first check the art table + + std::string sql = "SELECT DISTINCT url FROM art WHERE url IN ("; + for (const auto& image : imagesToCheck) + { + sql += PrepareSQL("'%s',", image.c_str()); + } + sql.pop_back(); // remove last ',' + sql += ")"; + + // add arttype filters if not set to "Maximum" + if (artworkLevel != CSettings::VIDEOLIBRARY_ARTWORK_LEVEL_ALL) + { + static std::array mediatypes = { + MediaTypeEpisode, MediaTypeTvShow, MediaTypeSeason, MediaTypeMovie, + MediaTypeVideoCollection, MediaTypeMusicVideo, MediaTypeVideoVersion}; + + std::string arttypeSQL; + for (const auto& mediatype : mediatypes) + { + const auto& arttypes = CVideoThumbLoader::GetArtTypes(mediatype); + if (arttypes.empty()) + continue; + + if (!arttypeSQL.empty()) + arttypeSQL += ") OR "; + arttypeSQL += PrepareSQL("media_type = '%s' AND (", mediatype.c_str()); + bool workingNext = false; + for (const auto& arttype : arttypes) + { + if (workingNext) + arttypeSQL += " OR "; + workingNext = true; + if (artworkLevel == CSettings::VIDEOLIBRARY_ARTWORK_LEVEL_BASIC) + { + // for basic match exact artwork type + arttypeSQL += PrepareSQL("type = '%s'", arttype.c_str()); + } + else + { + // otherwise check for arttype 'families', like fanart, fanart1, fanart13; + // still avoid most "happens to start with" like fanartstuff + arttypeSQL += + PrepareSQL("type BETWEEN '%s' AND '%s999'", arttype.c_str(), arttype.c_str()); + } + } + } + + if (CServiceBroker::GetSettingsComponent()->GetSettings()->GetBool( + CSettings::SETTING_VIDEOLIBRARY_ACTORTHUMBS)) + { + if (!arttypeSQL.empty()) + arttypeSQL += ") OR "; + arttypeSQL += "media_type = 'actor'"; + } + + if (!arttypeSQL.empty()) + sql += " AND (" + arttypeSQL + ")"; + } + + std::vector result; + if (m_pDS->query(sql)) + { + while (!m_pDS->eof()) + { + result.push_back(m_pDS->fv(0).get_asString()); + m_pDS->next(); + } + m_pDS->close(); + } + + // then check any chapter thumbnails against path and file tables + + if (CServiceBroker::GetSettingsComponent()->GetSettings()->GetBool( + CSettings::SETTING_MYVIDEOS_EXTRACTCHAPTERTHUMBS)) + { + std::vector foundVideoFiles; + for (const auto& image : imagesToCheck) + { + auto imageFile = IMAGE_FILES::CImageFileURL(image); + if (imageFile.GetSpecialType() == "video" && !imageFile.GetOption("chapter").empty()) + { + auto target = imageFile.GetTargetFile(); + auto quickFind = std::find(foundVideoFiles.begin(), foundVideoFiles.end(), target); + if (quickFind != foundVideoFiles.end()) + { + result.push_back(image); + } + else + { + int fileId = GetFileId(target); + if (fileId != -1) + { + result.push_back(image); + foundVideoFiles.push_back(target); + } + } + } + } + } + + return result; + } + catch (...) + { + CLog::LogF(LOGERROR, "failed"); + } + return {}; +} diff --git a/xbmc/video/VideoDatabase.h b/xbmc/video/VideoDatabase.h index 50b7feffde..e8de69b88c 100644 --- a/xbmc/video/VideoDatabase.h +++ b/xbmc/video/VideoDatabase.h @@ -1113,6 +1113,13 @@ public: int GetFileIdByMovie(int idMovie); std::string GetFileBasePathById(int idFile); + /*! + * @brief Check the passed in list of images if used in this database. Used to clean the image cache. + * @param imagesToCheck + * @return a list of the passed in images used by this database. + */ + std::vector GetUsedImages(const std::vector& imagesToCheck); + protected: int AddNewMovie(CVideoInfoTag& details); int AddNewMusicVideo(CVideoInfoTag& details); -- cgit v1.2.3