[VideoDB][video versions] Restore movie list performance

Video versions merge added an additional query per item to retrieve the existence of additional
versions, with a significant impact on large remote databases.

This version replaces the query with an additional column hasVideoVersions of movie_view for efficiency.
Values: true when there is any record in videoversion for the same movie, with an idFile different
from the default version of the movie (stored as movie.idFile). False otherwise.

2 files changed, 15 insertions, 35 deletions

diff --git a/xbmc/video/VideoDatabase.cpp b/xbmc/video/VideoDatabase.cpp
index cc872df413..e56ff201d6 100644
--- a/xbmc/video/VideoDatabase.cpp
+++ b/xbmc/video/VideoDatabase.cpp
@@ -287,6 +287,8 @@ void CVideoDatabase::CreateAnalytics()
               "actor_link (media_id, media_type(20), actor_id)");
   m_pDS->exec("CREATE INDEX ix_actor_link_3 ON actor_link (media_type(20))");
 
+  m_pDS->exec("CREATE INDEX ix_videoversion ON videoversion (idMedia, mediaType(20))");
+
   CreateLinkIndex("tag");
   CreateForeignLinkIndex("director", "actor");
   CreateForeignLinkIndex("writer", "actor");
@@ -562,7 +564,14 @@ void CVideoDatabase::CreateViews()
                                       "  rating.votes AS votes, "
                                       "  rating.rating_type AS rating_type, "
                                       "  uniqueid.value AS uniqueid_value, "
-                                      "  uniqueid.type AS uniqueid_type "
+                                      "  uniqueid.type AS uniqueid_type, "
+                                      "  EXISTS( "
+                                      "    SELECT 1 "
+                                      "    FROM  videoversion vv "
+                                      "    WHERE vv.idMedia = movie.idMovie "
+                                      "    AND   vv.mediaType = 'movie' "
+                                      "    AND   vv.idFile <> movie.idFile "
+                                      "  ) AS hasVideoVersions "
                                       "FROM movie"
                                       "  LEFT JOIN sets ON"
                                       "    sets.idSet = movie.idSet"
@@ -4263,7 +4272,7 @@ CVideoInfoTag CVideoDatabase::GetDetailsForMovie(const dbiplus::sql_record* cons
   details.m_iDbId = idMovie;
   details.m_type = MediaTypeMovie;
 
-  details.m_hasVideoVersions = HasVideoVersions(VideoDbContentType::MOVIES, idMovie);
+  details.m_hasVideoVersions = record->at(VIDEODB_DETAILS_MOVIE_HASVERSIONS).get_asBool();
   details.m_set.id = record->at(VIDEODB_DETAILS_MOVIE_SET_ID).get_asInt();
   details.m_set.title = record->at(VIDEODB_DETAILS_MOVIE_SET_NAME).get_asString();
   details.m_set.overview = record->at(VIDEODB_DETAILS_MOVIE_SET_OVERVIEW).get_asString();
@@ -6149,11 +6158,13 @@ void CVideoDatabase::UpdateTables(int iVersion)
         "INSERT INTO videoversion SELECT idFile, idMovie, 'movie', '%i', '%i' FROM movie",
         VideoVersionItemType::PRIMARY, VIDEO_VERSION_ID_DEFAULT));
   }
+
+  // Version 124: add index to videoversion
 }
 
 int CVideoDatabase::GetSchemaVersion() const
 {
-  return 123;
+  return 124;
 }
 
 bool CVideoDatabase::LookupByFolders(const std::string &path, bool shows)
@@ -11752,37 +11763,6 @@ int CVideoDatabase::AddVideoVersionType(const std::string& typeVideoVersion,
   return id;
 }
 
-bool CVideoDatabase::HasVideoVersions(VideoDbContentType itemType, int dbId)
-{
-  if (!m_pDB || !m_pDS2)
-    return false;
-
-  MediaType mediaType;
-
-  if (itemType == VideoDbContentType::MOVIES)
-    mediaType = MediaTypeMovie;
-  else
-    return false;
-
-  try
-  {
-    m_pDS2->query(
-        PrepareSQL("SELECT idFile FROM videoversion WHERE idMedia = %i AND mediaType = '%s'", dbId,
-                   mediaType.c_str()));
-
-    int count = m_pDS2->num_rows();
-    m_pDS2->close();
-
-    return count > 1;
-  }
-  catch (...)
-  {
-    CLog::Log(LOGERROR, "{} failed for {} {}", __FUNCTION__, mediaType, dbId);
-  }
-
-  return false;
-}
-
 bool CVideoDatabase::IsVideoExtras(int dbId)
 {
   if (!m_pDB || !m_pDS)
diff --git a/xbmc/video/VideoDatabase.h b/xbmc/video/VideoDatabase.h
index 7234767090..a498dc7760 100644
--- a/xbmc/video/VideoDatabase.h
+++ b/xbmc/video/VideoDatabase.h
@@ -93,6 +93,7 @@ enum VideoDbDetails
 #define VIDEODB_DETAILS_MOVIE_RATING_TYPE       VIDEODB_MAX_COLUMNS + 17
 #define VIDEODB_DETAILS_MOVIE_UNIQUEID_VALUE    VIDEODB_MAX_COLUMNS + 18
 #define VIDEODB_DETAILS_MOVIE_UNIQUEID_TYPE     VIDEODB_MAX_COLUMNS + 19
+#define VIDEODB_DETAILS_MOVIE_HASVERSIONS       VIDEODB_MAX_COLUMNS + 20
 
 #define VIDEODB_DETAILS_EPISODE_TVSHOW_ID       VIDEODB_MAX_COLUMNS + 2
 #define VIDEODB_DETAILS_EPISODE_USER_RATING     VIDEODB_MAX_COLUMNS + 3
@@ -1013,7 +1014,6 @@ public:
   std::string GetVideoVersionById(int id);
   bool GetVideoItemByVideoVersion(int dbId, CFileItem& item);
   int GetVideoVersionFile(VideoDbContentType itemType, int dbId, int idVideoVersion);
-  bool HasVideoVersions(VideoDbContentType itemType, int dbId);
   bool IsVideoExtras(int dbId);
   void GetVideoVersions(VideoDbContentType itemType, int dbId, CFileItemList& items);
   void GetVideoVersions(VideoDbContentType itemType,