[bitm]

I am running Kodi 15.2 using the MySQL setup for shared video database, and I have a MythTV Backend setup with my recordings. Whenever a new recording is added or after watching shows and leaving the show group (episode view) it frequently will pop "Working" in the bottom right corner.

My MySQL database is upstairs, and my Kodi machine is connected through wireless N. Running wireshark shows Kodi is doing a query for each show group and I have about 40 - 50 "shows", this takes somewhere around 30-60 seconds each time and sometimes it as soon as it has refreshed it will pop up Working again sometimes 2-3 times.

I have two questions:
1. Any way these queries can be changed to do a single query to collect all the information and/or bulk fetch (not sure if MySQL has this)?
2. Is there any way to control/limit how often it refreshes the information? I don't know if it is stored in memory, or what is happening there.
3. Is there a bug or issue why it might refresh 2-3 times consecutively? It doesn't happen all the time.
4. Is there anything else I can do to make this quicker? I do not have this problem with the non-PVR TV/Movie views in Kodi. It seems like the PVR stores bits of the information inside the same video database that regular Kodi uses, so I guess splitting it is not an option (use sqlite locally for PVR, and MySQL for TV/Movies).


Thank you in advance for any insight you guys might have.

[bitm]

So an update, I was able to track this down to CPVRRecordings::GetSubDirectories which in turn calls CPVRRecording::UpdateMetadata. I have group items enabled, so what it starts out doing is firing this SQL:


select idPath from path where strPath='pvr://recordings/active/Default/Title/'
select idFile from files where strFileName='Title - Subtitle, Channel, Starttime.pvr' and idPath=225
select playCount from files WHERE idFile=4133
select * from bookmark where idFile=4133 and type=1 order by timeInSeconds


Then it repeats about 9000 sql's similar to this, which takes up about 20 seconds. The average rtt is <= 0.6ms. What's obvious is it clearly knows what shows I have, and it seems like it is updating play counts and bookmark positions.

I think a fair tradeoff would to update this only when I drilled into a group item, it would mean if someone watched on another computer that the group itself may show as not watched. Another option perhaps is updating this in the background periodically?

[bitm]

OK, This is a bit of a hack but it illustrates the concept and it changes the playcount and bookmark metadata update to about 4 seconds for 938 entries (out of 2300 recordings). It appears the PVR will get the recordings from the PVR client and only watched videos are stored in the VideoDatabase.

Basically CPVRRecording::UpdateMetadata is replaced with a bulk method in CPVRRecordings that queries the database in one batch and then updates the playcounts/bookmarks. There is a lot of room for improvement.

Code:

diff --git a/xbmc/pvr/recordings/PVRRecording.cpp b/xbmc/pvr/recordings/PVRRecording.cpp
index b25da21..5e68bb8 100644
--- a/xbmc/pvr/recordings/PVRRecording.cpp
+++ b/xbmc/pvr/recordings/PVRRecording.cpp
@@ -484,3 +484,27 @@ bool CPVRRecording::IsBeingRecorded(void) const
   }
   return false;
}
+
+void CPVRRecording::SetMetadata(int playCount, double timeInSeconds, double totalTimeInSeconds)
+{
+  if (m_bGotMetaData)
+    return;
+
+  bool supportsPlayCount  = g_PVRClients->SupportsRecordingPlayCount(m_iClientId);
+  bool supportsLastPlayed = g_PVRClients->SupportsLastPlayedPosition(m_iClientId);
+
+  if (!supportsPlayCount || !supportsLastPlayed)
+  {
+    if (!supportsPlayCount)
+      m_playCount = playCount;
+
+    if (!supportsLastPlayed)
+    {
+      m_resumePoint.timeInSeconds = timeInSeconds;
+      m_resumePoint.totalTimeInSeconds = totalTimeInSeconds;
+    }
+  }
+
+  m_bGotMetaData = true;
+}
+
diff --git a/xbmc/pvr/recordings/PVRRecording.h b/xbmc/pvr/recordings/PVRRecording.h
index 6045d66..32eb846 100644
--- a/xbmc/pvr/recordings/PVRRecording.h
+++ b/xbmc/pvr/recordings/PVRRecording.h
@@ -229,6 +229,8 @@ namespace PVR
      */
     std::string EpisodeName(void) const { return m_strShowTitle; };

+    void SetMetadata(int playCount, double timeInSeconds, double totalTimeInSeconds);
+
   private:
     CDateTime    m_recordingTime; /*!< start time of the recording */
     bool         m_bGotMetaData;
diff --git a/xbmc/pvr/recordings/PVRRecordings.cpp b/xbmc/pvr/recordings/PVRRecordings.cpp
index 5288a34..2817c28 100644
--- a/xbmc/pvr/recordings/PVRRecordings.cpp
+++ b/xbmc/pvr/recordings/PVRRecordings.cpp
@@ -38,7 +38,8 @@ CPVRRecordings::CPVRRecordings(void) :
     m_bIsUpdating(false),
     m_iLastId(0),
     m_bGroupItems(true),
-    m_bHasDeleted(false)
+    m_bHasDeleted(false),
+    m_bHasMetadata(false)
{
   m_database.Open();
}
@@ -348,6 +349,7 @@ bool CPVRRecordings::GetDirectory(const std::string& strPath, CFileItemList &ite

   if (StringUtils::StartsWith(strDirectoryPath, PVR_RECORDING_BASE_PATH))
   {
+    LoadMetadata();
     strDirectoryPath.erase(0, sizeof(PVR_RECORDING_BASE_PATH) - 1);

     // Check directory name is for deleted recordings
@@ -481,6 +483,7 @@ void CPVRRecordings::Clear()
{
   CSingleLock lock(m_critSection);
   m_bHasDeleted = false;
+  m_bHasMetadata = false;
   m_recordings.clear();
}

@@ -526,3 +529,32 @@ void CPVRRecordings::UpdateEpgTags(void)
     }
   }
}
+
+void CPVRRecordings::LoadMetadata(void)
+{
+  if (m_bHasMetadata)
+    return;
+
+  CSingleLock lock(m_critSection);
+
+  if (m_database.IsOpen())
+  {
+    CFileItemList videoItems;
+    m_database.GetRecordings(videoItems);
+
+    for (int index = 0; index < videoItems.Size(); index++)
+    {
+      CFileItemPtr videoItemPtr = videoItems.Get(index);
+      CVideoInfoTag video = *(videoItemPtr->GetVideoInfoTag());
+
+      CFileItem recordingItem = *(GetByPath(video.m_strFileNameAndPath));
+      if (recordingItem.HasPVRRecordingInfoTag())
+      {
+        CPVRRecordingPtr recording = recordingItem.GetPVRRecordingInfoTag();
+        recording->SetMetadata(video.m_playCount, video.m_resumePoint.timeInSeconds, video.m_resumePoint.totalTimeInSeconds);
+      }
+    }
+
+    m_bHasMetadata = true;
+  }
+}
diff --git a/xbmc/pvr/recordings/PVRRecordings.h b/xbmc/pvr/recordings/PVRRecordings.h
index ced6854..17180aa 100644
--- a/xbmc/pvr/recordings/PVRRecordings.h
+++ b/xbmc/pvr/recordings/PVRRecordings.h
@@ -43,12 +43,14 @@ namespace PVR
     bool                         m_bGroupItems;
     CVideoDatabase               m_database;
     bool                         m_bHasDeleted;
+    bool                         m_bHasMetadata;

     virtual void UpdateFromClients(void);
     virtual std::string TrimSlashes(const std::string &strOrig) const;
     virtual const std::string GetDirectoryFromPath(const std::string &strPath, const std::string &strBase) const;
     virtual bool IsDirectoryMember(const std::string &strDirectory, const std::string &strEntryDirectory) const;
     virtual void GetSubDirectories(const std::string &strBase, CFileItemList *results);
+    void LoadMetadata(void);

     /**
      * @brief recursively deletes all recordings in the specified directory
diff --git a/xbmc/video/VideoDatabase.cpp b/xbmc/video/VideoDatabase.cpp
index b56e2e8..aa3ce01 100644
--- a/xbmc/video/VideoDatabase.cpp
+++ b/xbmc/video/VideoDatabase.cpp
@@ -9433,3 +9433,44 @@ bool CVideoDatabase::SetVideoUserRating(int dbId, int rating, const MediaType& m
   }
   return false;
}
+
+bool CVideoDatabase::GetRecordings(CFileItemList& items)
+{
+  try
+  {
+    std::string sql = PrepareSQL("SELECT * FROM files "
+                                "JOIN path ON path.idPath = files.idPath "
+                                "LEFT JOIN bookmark ON bookmark.idFile = files.idFile AND bookmark.type = %i "
+                                "WHERE path.strPath LIKE 'pvr://recordings/active/%%'", CBookmark::RESUME);
+    if (!m_pDS->query(sql))
+      return false;
+
+    while (!m_pDS->eof())
+    {
+      CVideoInfoTag details;
+
+      details.m_iFileId = m_pDS->fv("files.idFile").get_asInt();
+      details.m_strPath = m_pDS->fv("path.strPath").get_asString();
+      std::string strFileName = m_pDS->fv("files.strFilename").get_asString();
+      ConstructPath(details.m_strFileNameAndPath, details.m_strPath, strFileName);
+      details.m_playCount = std::max(details.m_playCount, m_pDS->fv("files.playCount").get_asInt());
+      details.m_lastPlayed.SetFromDBDateTime(m_pDS->fv("files.lastPlayed").get_asString());
+      details.m_dateAdded.SetFromDBDateTime(m_pDS->fv("files.dateAdded").get_asString());
+      details.m_resumePoint.timeInSeconds = m_pDS->fv("bookmark.timeInSeconds").get_asInt();
+      details.m_resumePoint.totalTimeInSeconds = m_pDS->fv("bookmark.totalTimeInSeconds").get_asInt();
+      details.m_resumePoint.type = CBookmark::RESUME;
+
+      CFileItemPtr pItem(new CFileItem(details));
+      items.Add(pItem);
+      m_pDS->next();
+    }
+
+    m_pDS->close();
+    return true;
+  }
+  catch (...)
+  {
+    CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
+  }
+  return false;
+}
diff --git a/xbmc/video/VideoDatabase.h b/xbmc/video/VideoDatabase.h
index 2021dd9..5062399 100644
--- a/xbmc/video/VideoDatabase.h
+++ b/xbmc/video/VideoDatabase.h
@@ -809,6 +809,8 @@ public:
   void SetMovieSet(int idMovie, int idSet);
   bool SetVideoUserRating(int dbId, int rating, const MediaType& mediaType);

+  bool GetRecordings(CFileItemList& items);
+
protected:
   int GetMovieId(const std::string& strFilenameAndPath);
   int GetMusicVideoId(const std::string& strFilenameAndPath);