videodb: add tag and taglinks tables for tagging support

4 files changed, 191 insertions, 8 deletions

diff --git a/xbmc/video/VideoDatabase.cpp b/xbmc/video/VideoDatabase.cpp
index c4e895e9cc..61ee78c131 100644
--- a/xbmc/video/VideoDatabase.cpp
+++ b/xbmc/video/VideoDatabase.cpp
@@ -321,6 +321,16 @@ bool CVideoDatabase::CreateTables()
     m_pDS->exec("CREATE TRIGGER delete_set AFTER DELETE ON sets FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idSet AND media_type='set'; END");
     m_pDS->exec("CREATE TRIGGER delete_person AFTER DELETE ON actors FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idActor AND media_type IN ('actor','artist','writer','director'); END");
 
+    CLog::Log(LOGINFO, "create tag table");
+    m_pDS->exec("CREATE TABLE tag (idTag integer primary key, strTag text)");
+    m_pDS->exec("CREATE UNIQUE INDEX ix_tag_1 ON tag (strTag(256))");
+
+    CLog::Log(LOGINFO, "create taglinks table");
+    m_pDS->exec("CREATE TABLE taglinks (idTag integer, idMedia integer, media_type TEXT)");
+    m_pDS->exec("CREATE UNIQUE INDEX ix_taglinks_1 ON taglinks (idTag, media_type(20), idMedia)");
+    m_pDS->exec("CREATE UNIQUE INDEX ix_taglinks_2 ON taglinks (idMedia, media_type(20), idTag)");
+    m_pDS->exec("CREATE INDEX ix_taglinks_3 ON taglinks (media_type(20))");
+
     // we create views last to ensure all indexes are rolled in
     CreateViews();
   }
@@ -1240,7 +1250,7 @@ int CVideoDatabase::AddToTable(const CStdString& table, const CStdString& firstF
     {
       m_pDS->close();
       // doesnt exists, add it
-      strSQL = PrepareSQL("insert into %s (%s, %s) values( NULL, '%s')", table.c_str(), firstField.c_str(), secondField.c_str(), value.c_str());
+      strSQL = PrepareSQL("insert into %s (%s, %s) values(NULL, '%s')", table.c_str(), firstField.c_str(), secondField.c_str(), value.c_str());      
       m_pDS->exec(strSQL.c_str());
       int id = (int)m_pDS->lastinsertid();
       return id;
@@ -1265,6 +1275,14 @@ int CVideoDatabase::AddSet(const CStdString& strSet)
   return AddToTable("sets", "idSet", "strSet", strSet);
 }
 
+int CVideoDatabase::AddTag(const std::string& tag)
+{
+  if (tag.empty())
+    return -1;
+
+  return AddToTable("tag", "idTag", "strTag", tag);
+}
+
 int CVideoDatabase::AddGenre(const CStdString& strGenre)
 {
   return AddToTable("genre", "idGenre", "strGenre", strGenre);
@@ -1346,19 +1364,24 @@ void CVideoDatabase::AddLinkToActor(const char *table, int actorID, const char *
   }
 }
 
-void CVideoDatabase::AddToLinkTable(const char *table, const char *firstField, int firstID, const char *secondField, int secondID)
+void CVideoDatabase::AddToLinkTable(const char *table, const char *firstField, int firstID, const char *secondField, int secondID, const char *typeField /* = NULL */, const char *type /* = NULL */)
 {
   try
   {
     if (NULL == m_pDB.get()) return ;
     if (NULL == m_pDS.get()) return ;
 
-    CStdString strSQL=PrepareSQL("select * from %s where %s=%i and %s=%i", table, firstField, firstID, secondField, secondID);
+    CStdString strSQL = PrepareSQL("select * from %s where %s=%i and %s=%i", table, firstField, firstID, secondField, secondID);
+    if (typeField != NULL && type != NULL)
+      strSQL += PrepareSQL(" and %s='%s'", typeField, type);
     m_pDS->query(strSQL.c_str());
     if (m_pDS->num_rows() == 0)
     {
       // doesnt exists, add it
-      strSQL=PrepareSQL("insert into %s (%s,%s) values(%i,%i)", table, firstField, secondField, firstID, secondID);
+      if (typeField == NULL || type == NULL)
+        strSQL = PrepareSQL("insert into %s (%s,%s) values(%i,%i)", table, firstField, secondField, firstID, secondID);
+      else
+        strSQL = PrepareSQL("insert into %s (%s,%s,%s) values(%i,%i,'%s')", table, firstField, secondField, typeField, firstID, secondID, type);
       m_pDS->exec(strSQL.c_str());
     }
     m_pDS->close();
@@ -1369,11 +1392,45 @@ void CVideoDatabase::AddToLinkTable(const char *table, const char *firstField, i
   }
 }
 
+void CVideoDatabase::RemoveFromLinkTable(const char *table, const char *firstField, int firstID, const char *secondField, int secondID, const char *typeField /* = NULL */, const char *type /* = NULL */)
+{
+  try
+  {
+    if (NULL == m_pDB.get()) return ;
+    if (NULL == m_pDS.get()) return ;
+
+    CStdString strSQL = PrepareSQL("DELETE FROM %s WHERE %s = %i AND %s = %i", table, firstField, firstID, secondField, secondID);
+    if (typeField != NULL && type != NULL)
+      strSQL += PrepareSQL(" AND %s='%s'", typeField, type);
+    m_pDS->exec(strSQL.c_str());
+  }
+  catch (...)
+  {
+    CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
+  }
+}
+
 //****Sets****
 void CVideoDatabase::AddSetToMovie(int idMovie, int idSet)
 {
   AddToLinkTable("setlinkmovie", "idSet", idSet, "idMovie", idMovie);
 }
+//****Tags****
+void CVideoDatabase::AddTagToItem(int idMovie, int idTag, const std::string &type)
+{
+  if (type.empty())
+    return;
+
+  AddToLinkTable("taglinks", "idTag", idTag, "idMedia", idMovie, "media_type", type.c_str());
+}
+
+void CVideoDatabase::RemoveTagFromItem(int idMovie, int idTag, const std::string &type)
+{
+  if (type.empty())
+    return;
+
+  RemoveFromLinkTable("taglinks", "idTag", idTag, "idMedia", idMovie, "media_type", type.c_str());
+}
 
 //****Actors****
 void CVideoDatabase::AddActorToMovie(int idMovie, int idActor, const CStdString& strRole, int order)
@@ -1898,6 +1955,13 @@ int CVideoDatabase::SetDetailsForMovie(const CStdString& strFilenameAndPath, con
       AddSetToMovie(idMovie, idSet);
     }
 
+    // add tags...
+    for (unsigned int i = 0; i < details.m_tags.size(); i++)
+    {
+      int idTag = AddTag(details.m_tags[i]);
+      AddTagToItem(idMovie, idTag, "movie");
+    }
+
     // add countries...
     for (unsigned int i = 0; i < details.m_country.size(); i++)
       AddCountryToMovie(idMovie, AddCountry(details.m_country[i]));
@@ -2838,6 +2902,31 @@ void CVideoDatabase::DeleteSet(int idSet)
   }
 }
 
+void CVideoDatabase::DeleteTag(int idTag, const std::string &mediaType)
+{
+  try
+  {
+    if (m_pDB.get() == NULL || m_pDS.get() == NULL)
+      return;
+
+    CStdString strSQL;
+    strSQL = PrepareSQL("DELETE FROM taglinks WHERE idTag = %i AND media_type = '%s'", idTag, mediaType.c_str());
+    m_pDS->exec(strSQL.c_str());
+
+    // check if the tag is used for another media type as well before deleting it completely
+    strSQL = PrepareSQL("SELECT 1 FROM taglinks WHERE idTag = %i", idTag);
+    if (RunQuery(strSQL) <= 0)
+    {
+      strSQL = PrepareSQL("DELETE FROM tag WHERE idTag = %i", idTag);
+      m_pDS->exec(strSQL.c_str());
+    }
+  }
+  catch (...)
+  {
+    CLog::Log(LOGERROR, "%s (%i) failed", __FUNCTION__, idTag);
+  }
+}
+
 void CVideoDatabase::GetDetailsFromDB(auto_ptr<Dataset> &pDS, int min, int max, const SDbTableOffsets *offsets, CVideoInfoTag &details, int idxOffset)
 {
   GetDetailsFromDB(pDS->get_sql_record(), min, max, offsets, details, idxOffset);
@@ -3058,6 +3147,15 @@ CVideoInfoTag CVideoDatabase::GetDetailsForMovie(const dbiplus::sql_record* cons
       m_pDS2->next();
     }
 
+    // get tags
+    strSQL = PrepareSQL("SELECT tag.strTag FROM tag, taglinks WHERE taglinks.idMedia = %i AND taglinks.media_type = 'movie' AND taglinks.idTag = tag.idTag ORDER BY tag.idTag", idMovie);
+    m_pDS2->query(strSQL.c_str());
+    while (!m_pDS2->eof())
+    {
+      details.m_tags.push_back(m_pDS2->fv("tag.strTag").get_asString());
+      m_pDS2->next();
+    }
+
     // create tvshowlink string
     vector<int> links;
     GetLinksToTvShow(idMovie,links);
@@ -3900,6 +3998,16 @@ bool CVideoDatabase::UpdateOldVersion(int iVersion)
     m_pDS->exec("CREATE INDEX ix_episode_show1 on episode(idEpisode,idShow)");
     m_pDS->exec("CREATE INDEX ix_episode_show2 on episode(idShow,idEpisode)");
   }
+  if (iVersion < 67)
+  {
+    m_pDS->exec("CREATE TABLE tag (idTag integer primary key, strTag text)");
+    m_pDS->exec("CREATE UNIQUE INDEX ix_tag_1 ON tag (strTag(256))");
+
+    m_pDS->exec("CREATE TABLE taglinks (idTag integer, idMedia integer, media_type TEXT)");
+    m_pDS->exec("CREATE UNIQUE INDEX ix_taglinks_1 ON taglinks (idTag, media_type(20), idMedia)");
+    m_pDS->exec("CREATE UNIQUE INDEX ix_taglinks_2 ON taglinks (idMedia, media_type(20), idTag)");
+    m_pDS->exec("CREATE INDEX ix_taglinks_3 ON taglinks (media_type(20))");
+  }
   // always recreate the view after any table change
   CreateViews();
   return true;
@@ -4351,6 +4459,54 @@ bool CVideoDatabase::GetNavCommon(const CStdString& strBaseDir, CFileItemList& i
   return false;
 }
 
+bool CVideoDatabase::GetTagsNav(const CStdString& strBaseDir, CFileItemList& items, int idContent)
+{
+  CStdString mediaType;
+  if (idContent == VIDEODB_CONTENT_MOVIES)
+    mediaType = "movie";
+  else
+    return false;
+
+  try
+  {
+    if (NULL == m_pDB.get()) return false;
+    if (NULL == m_pDS.get()) return false;
+
+    int iRowsFound = RunQuery(PrepareSQL("SELECT tag.idTag, tag.strTag FROM taglinks JOIN tag ON tag.idTag = taglinks.idTag WHERE taglinks.media_type = '%s' GROUP BY taglinks.idTag", mediaType.c_str()));
+    if (iRowsFound <= 0)
+      return iRowsFound == 0;
+
+    while (!m_pDS->eof())
+    {
+      int idTag = m_pDS->fv(0).get_asInt();
+
+      CFileItemPtr pItem(new CFileItem(m_pDS->fv(1).get_asString()));
+      pItem->m_bIsFolder = true;
+      pItem->GetVideoInfoTag()->m_iDbId = idTag;
+      pItem->GetVideoInfoTag()->m_type = "tag";
+
+      CStdString strDir; strDir.Format("%ld/", idTag);
+      pItem->SetPath(strBaseDir + strDir);
+
+      if (!items.Contains(pItem->GetPath()))
+      {
+        pItem->SetLabelPreformated(true);
+        items.Add(pItem);
+      }
+
+      m_pDS->next();
+    }
+    m_pDS->close();
+
+    return true;
+  }
+  catch (...)
+  {
+    CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
+  }
+  return false;
+}
+
 bool CVideoDatabase::GetSetsNav(const CStdString& strBaseDir, CFileItemList& items, int idContent)
 {
   if (idContent != VIDEODB_CONTENT_MOVIES)
@@ -5149,7 +5305,9 @@ bool CVideoDatabase::GetSortedVideos(MediaType mediaType, const CStdString& strB
   return success;
 }
 
-bool CVideoDatabase::GetMoviesNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre, int idYear, int idActor, int idDirector, int idStudio, int idCountry, int idSet)
+bool CVideoDatabase::GetMoviesNav(const CStdString& strBaseDir, CFileItemList& items,
+                                  int idGenre /* = -1 */, int idYear /* = -1 */, int idActor /* = -1 */, int idDirector /* = -1 */,
+                                  int idStudio /* = -1 */, int idCountry /* = -1 */, int idSet /* = -1 */, int idTag /* = -1 */)
 {
   Filter filter;
   if (idGenre != -1)
@@ -5184,6 +5342,11 @@ bool CVideoDatabase::GetMoviesNav(const CStdString& strBaseDir, CFileItemList& i
     filter.join  = PrepareSQL("join setlinkmovie on setlinkmovie.idMovie=movieview.idMovie");
     filter.where = PrepareSQL("setlinkmovie.idSet=%u",idSet);
   }
+  else if (idTag != -1)
+  {
+    filter.join  = PrepareSQL("join taglinks on taglinks.idMedia = movieview.idMovie AND taglinks.media_type = 'movie'");
+    filter.where = PrepareSQL("taglinks.idTag = %u", idTag);
+  }
 
   return GetMoviesByWhere(strBaseDir, filter, items, idSet == -1);
 }
@@ -5842,6 +6005,11 @@ CStdString CVideoDatabase::GetSetById(int id)
   return GetSingleValue("sets", "strSet", PrepareSQL("idSet=%i", id));
 }
 
+CStdString CVideoDatabase::GetTagById(int id)
+{
+  return GetSingleValue("tag", "strTag", PrepareSQL("idTag = %i", id));
+}
+
 CStdString CVideoDatabase::GetPersonById(int id)
 {
   return GetSingleValue("actors", "strActor", PrepareSQL("idActor=%i", id));
diff --git a/xbmc/video/VideoDatabase.h b/xbmc/video/VideoDatabase.h
index 06331af52f..9cb16e0ad0 100644
--- a/xbmc/video/VideoDatabase.h
+++ b/xbmc/video/VideoDatabase.h
@@ -419,6 +419,7 @@ public:
   CStdString GetGenreById(int id);
   CStdString GetCountryById(int id);
   CStdString GetSetById(int id);
+  CStdString GetTagById(int id);
   CStdString GetPersonById(int id);
   CStdString GetStudioById(int id);
   CStdString GetTvShowTitleById(int id);
@@ -459,6 +460,7 @@ public:
   void RemoveContentForPath(const CStdString& strPath,CGUIDialogProgress *progress = NULL);
   void UpdateFanart(const CFileItem &item, VIDEODB_CONTENT_TYPE type);
   void DeleteSet(int idSet);
+  void DeleteTag(int idTag, const std::string &mediaType);
 
   // per-file video settings
   bool GetVideoSettings(const CStdString &strFilenameAndPath, CVideoSettings &settings);
@@ -584,9 +586,10 @@ public:
   bool GetDirectorsNav(const CStdString& strBaseDir, CFileItemList& items, int idContent=-1);
   bool GetWritersNav(const CStdString& strBaseDir, CFileItemList& items, int idContent=-1);
   bool GetSetsNav(const CStdString& strBaseDir, CFileItemList& items, int idContent=-1);
+  bool GetTagsNav(const CStdString& strBaseDir, CFileItemList& items, int idContent=-1);
   bool GetMusicVideoAlbumsNav(const CStdString& strBaseDir, CFileItemList& items, int idArtist);
 
-  bool GetMoviesNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre=-1, int idYear=-1, int idActor=-1, int idDirector=-1, int idStudio=-1, int idCountry=-1, int idSet=-1);
+  bool GetMoviesNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre=-1, int idYear=-1, int idActor=-1, int idDirector=-1, int idStudio=-1, int idCountry=-1, int idSet=-1, int idTag=-1);
   bool GetTvShowsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre=-1, int idYear=-1, int idActor=-1, int idDirector=-1, int idStudio=-1);
   bool GetSeasonsNav(const CStdString& strBaseDir, CFileItemList& items, int idActor=-1, int idDirector=-1, int idGenre=-1, int idYear=-1, int idShow=-1);
   bool GetEpisodesNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre=-1, int idYear=-1, int idActor=-1, int idDirector=-1, int idShow=-1, int idSeason=-1, const SortDescription &sortDescription = SortDescription());
@@ -680,6 +683,10 @@ public:
   std::string GetArtForItem(int mediaId, const std::string &mediaType, const std::string &artType);
   bool GetTvShowSeasonArt(int mediaId, std::map<int, std::string> &seasonArt);
 
+  int AddTag(const std::string &tag);
+  void AddTagToItem(int idItem, int idTag, const std::string &type);
+  void RemoveTagFromItem(int idItem, int idTag, const std::string &type);
+
 protected:
   int GetMovieId(const CStdString& strFilenameAndPath);
   int GetMusicVideoId(const CStdString& strFilenameAndPath);
@@ -710,7 +717,8 @@ protected:
 
   // link functions - these two do all the work
   void AddLinkToActor(const char *table, int actorID, const char *secondField, int secondID, const CStdString &role, int order);
-  void AddToLinkTable(const char *table, const char *firstField, int firstID, const char *secondField, int secondID);
+  void AddToLinkTable(const char *table, const char *firstField, int firstID, const char *secondField, int secondID, const char *typeField = NULL, const char *type = NULL);
+  void RemoveFromLinkTable(const char *table, const char *firstField, int firstID, const char *secondField, int secondID, const char *typeField = NULL, const char *type = NULL);
 
   void AddSetToMovie(int idMovie, int idSet);
 
@@ -799,7 +807,7 @@ private:
    */
   bool LookupByFolders(const CStdString &path, bool shows = false);
 
-  virtual int GetMinVersion() const { return 66; };
+  virtual int GetMinVersion() const { return 67; };
   virtual int GetExportVersion() const { return 1; };
   const char *GetBaseDBName() const { return "MyVideos"; };
 
diff --git a/xbmc/video/VideoInfoTag.cpp b/xbmc/video/VideoInfoTag.cpp
index d5913deb1b..bb352dbcf0 100644
--- a/xbmc/video/VideoInfoTag.cpp
+++ b/xbmc/video/VideoInfoTag.cpp
@@ -53,6 +53,7 @@ void CVideoInfoTag::Reset()
   m_cast.clear();
   m_set.clear();
   m_setId.clear();
+  m_tags.clear();
   m_strFile.clear();
   m_strPath.clear();
   m_strIMDBNumber.clear();
@@ -174,6 +175,7 @@ bool CVideoInfoTag::Save(TiXmlNode *node, const CStdString &tag, bool savePathIn
   XMLUtils::SetStringArray(movie, "genre", m_genre);
   XMLUtils::SetStringArray(movie, "country", m_country);
   XMLUtils::SetStringArray(movie, "set", m_set);
+  XMLUtils::SetStringArray(movie, "tag", m_tags);
   XMLUtils::SetStringArray(movie, "credits", m_writingCredits);
   XMLUtils::SetStringArray(movie, "director", m_director);
   XMLUtils::SetDate(movie, "premiered", m_premiered);
@@ -291,6 +293,7 @@ void CVideoInfoTag::Archive(CArchive& ar)
 
     ar << m_set;
     ar << m_setId;
+    ar << m_tags;
     ar << m_strRuntime;
     ar << m_strFile;
     ar << m_strPath;
@@ -367,6 +370,7 @@ void CVideoInfoTag::Archive(CArchive& ar)
 
     ar >> m_set;
     ar >> m_setId;
+    ar >> m_tags;
     ar >> m_strRuntime;
     ar >> m_strFile;
     ar >> m_strPath;
@@ -443,6 +447,7 @@ void CVideoInfoTag::Serialize(CVariant& value)
   value["setid"] = CVariant(CVariant::VariantTypeArray);
   for (unsigned int i = 0; i < m_setId.size(); i++)
     value["setid"].push_back(m_setId[i]);
+  value["tags"] = m_tags;
   value["runtime"] = m_strRuntime;
   value["file"] = m_strFile;
   value["path"] = m_strPath;
@@ -656,6 +661,7 @@ void CVideoInfoTag::ParseNative(const TiXmlElement* movie, bool prioritise)
   }
 
   XMLUtils::GetStringArray(movie, "set", m_set, prioritise, g_advancedSettings.m_videoItemSeparator);
+  XMLUtils::GetStringArray(movie, "tag", m_tags, prioritise, g_advancedSettings.m_videoItemSeparator);
   XMLUtils::GetStringArray(movie, "studio", m_studio, prioritise, g_advancedSettings.m_videoItemSeparator);
   // artists
   node = movie->FirstChildElement("artist");
diff --git a/xbmc/video/VideoInfoTag.h b/xbmc/video/VideoInfoTag.h
index 7db64ad42a..06c99b6e15 100644
--- a/xbmc/video/VideoInfoTag.h
+++ b/xbmc/video/VideoInfoTag.h
@@ -97,6 +97,7 @@ public:
   typedef std::vector< SActorInfo >::const_iterator iCast;
   std::vector<std::string> m_set;
   std::vector<int> m_setId;
+  std::vector<std::string> m_tags;
   CStdString m_strRuntime;
   CStdString m_strFile;
   CStdString m_strPath;