xbmc/TextureDatabase.cpp

   1 /*
   2  *  Copyright (C) 2005-2018 Team Kodi
   3  *  This file is part of Kodi - https://kodi.tv
   4  *
   5  *  SPDX-License-Identifier: GPL-2.0-or-later
   6  *  See LICENSES/README.md for more information.
   7  */
   8
   9 #include "TextureDatabase.h"
  10
  11 #include "URL.h"
  12 #include "XBDateTime.h"
  13 #include "dbwrappers/dataset.h"
  14 #include "utils/DatabaseUtils.h"
  15 #include "utils/StringUtils.h"
  16 #include "utils/Variant.h"
  17 #include "utils/log.h"
  18
  19 enum TextureField
  20 {
  21   TF_None = 0,
  22   TF_Id,
  23   TF_Url,
  24   TF_CachedUrl,
  25   TF_LastHashCheck,
  26   TF_ImageHash,
  27   TF_Width,
  28   TF_Height,
  29   TF_UseCount,
  30   TF_LastUsed,
  31   TF_Max
  32 };
  33
  34 typedef struct
  35 {
  36   char string[14];
  37   TextureField field;
  38   CDatabaseQueryRule::FIELD_TYPE type;
  39 } translateField;
  40
  41 static const translateField fields[] = {
  42   { "none",          TF_None,          CDatabaseQueryRule::TEXT_FIELD    },
  43   { "textureid",     TF_Id,            CDatabaseQueryRule::REAL_FIELD    },
  44   { "url",           TF_Url,           CDatabaseQueryRule::TEXT_FIELD    },
  45   { "cachedurl",     TF_CachedUrl,     CDatabaseQueryRule::TEXT_FIELD    },
  46   { "lasthashcheck", TF_LastHashCheck, CDatabaseQueryRule::TEXT_FIELD    },
  47   { "imagehash",     TF_ImageHash,     CDatabaseQueryRule::TEXT_FIELD    },
  48   { "width",         TF_Width,         CDatabaseQueryRule::REAL_FIELD    },
  49   { "height",        TF_Height,        CDatabaseQueryRule::REAL_FIELD    },
  50   { "usecount",      TF_UseCount,      CDatabaseQueryRule::REAL_FIELD    },
  51   { "lastused",      TF_LastUsed,      CDatabaseQueryRule::TEXT_FIELD    }
  52 };
  53
  54 static const size_t NUM_FIELDS = sizeof(fields) / sizeof(translateField);
  55
  56 int CTextureRule::TranslateField(const char *field) const
  57 {
  58   for (const translateField& f : fields)
  59     if (StringUtils::EqualsNoCase(field, f.string)) return f.field;
  60   return FieldNone;
  61 }
  62
  63 std::string CTextureRule::TranslateField(int field) const
  64 {
  65   for (const translateField& f : fields)
  66     if (field == f.field) return f.string;
  67   return "none";
  68 }
  69
  70 std::string CTextureRule::GetField(int field, const std::string &type) const
  71 {
  72   if (field == TF_Id) return "texture.id";
  73   else if (field == TF_Url) return "texture.url";
  74   else if (field == TF_CachedUrl) return "texture.cachedurl";
  75   else if (field == TF_LastHashCheck) return "texture.lasthashcheck";
  76   else if (field == TF_ImageHash) return "texture.imagehash";
  77   else if (field == TF_Width) return "sizes.width";
  78   else if (field == TF_Height) return "sizes.height";
  79   else if (field == TF_UseCount) return "sizes.usecount";
  80   else if (field == TF_LastUsed) return "sizes.lastusetime";
  81   return "";
  82 }
  83
  84 CDatabaseQueryRule::FIELD_TYPE CTextureRule::GetFieldType(int field) const
  85 {
  86   for (const translateField& f : fields)
  87     if (field == f.field) return f.type;
  88   return TEXT_FIELD;
  89 }
  90
  91 std::string CTextureRule::FormatParameter(const std::string &operatorString,
  92                                           const std::string &param,
  93                                           const CDatabase &db,
  94                                           const std::string &strType) const
  95 {
  96   std::string parameter(param);
  97   if (m_field == TF_Url)
  98     parameter = CTextureUtils::UnwrapImageURL(param);
  99   return CDatabaseQueryRule::FormatParameter(operatorString, parameter, db, strType);
 100 }
 101
 102 void CTextureRule::GetAvailableFields(std::vector<std::string> &fieldList)
 103 {
 104   // start at 1 to skip TF_None
 105   for (unsigned int i = 1; i < NUM_FIELDS; i++)
 106     fieldList.emplace_back(fields[i].string);
 107 }
 108
 109 std::string CTextureUtils::GetWrappedImageURL(const std::string &image, const std::string &type, const std::string &options)
 110 {
 111   if (StringUtils::StartsWith(image, "image://"))
 112     return image; // already wrapped
 113
 114   CURL url;
 115   url.SetProtocol("image");
 116   url.SetUserName(type);
 117   url.SetHostName(image);
 118   if (!options.empty())
 119   {
 120     url.SetFileName("transform");
 121     url.SetOptions("?" + options);
 122   }
 123   return url.Get();
 124 }
 125
 126 std::string CTextureUtils::GetWrappedThumbURL(const std::string &image)
 127 {
 128   return GetWrappedImageURL(image, "", "size=thumb");
 129 }
 130
 131 std::string CTextureUtils::UnwrapImageURL(const std::string &image)
 132 {
 133   if (StringUtils::StartsWith(image, "image://"))
 134   {
 135     CURL url(image);
 136     if (url.GetUserName().empty() && url.GetOptions().empty())
 137       return url.GetHostName();
 138   }
 139   return image;
 140 }
 141
 142 CTextureDatabase::CTextureDatabase() = default;
 143
 144 CTextureDatabase::~CTextureDatabase() = default;
 145
 146 bool CTextureDatabase::Open()
 147 {
 148   return CDatabase::Open();
 149 }
 150
 151 void CTextureDatabase::CreateTables()
 152 {
 153   CLog::Log(LOGINFO, "create texture table");
 154   m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, imagehash text, lasthashcheck text)");
 155
 156   CLog::Log(LOGINFO, "create sizes table, index,  and trigger");
 157   m_pDS->exec("CREATE TABLE sizes (idtexture integer, size integer, width integer, height integer, usecount integer, lastusetime text)");
 158
 159   CLog::Log(LOGINFO, "create path table");
 160   m_pDS->exec("CREATE TABLE path (id integer primary key, url text, type text, texture text)\n");
 161 }
 162
 163 void CTextureDatabase::CreateAnalytics()
 164 {
 165   CLog::Log(LOGINFO, "{} creating indices", __FUNCTION__);
 166   m_pDS->exec("CREATE INDEX idxTexture ON texture(url)");
 167   m_pDS->exec("CREATE INDEX idxSize ON sizes(idtexture, size)");
 168   m_pDS->exec("CREATE INDEX idxSize2 ON sizes(idtexture, width, height)");
 169   //! @todo Should the path index be a covering index? (we need only retrieve texture)
 170   m_pDS->exec("CREATE INDEX idxPath ON path(url, type)");
 171
 172   CLog::Log(LOGINFO, "{} creating triggers", __FUNCTION__);
 173   m_pDS->exec("CREATE TRIGGER textureDelete AFTER delete ON texture FOR EACH ROW BEGIN delete from sizes where sizes.idtexture=old.id; END");
 174 }
 175
 176 void CTextureDatabase::UpdateTables(int version)
 177 {
 178   if (version < 7)
 179   { // update all old thumb://foo urls to image://foo?size=thumb
 180     m_pDS->query("select id,texture from path where texture like 'thumb://%'");
 181     while (!m_pDS->eof())
 182     {
 183       unsigned int id = m_pDS->fv(0).get_asInt();
 184       CURL url(m_pDS->fv(1).get_asString());
 185       m_pDS2->exec(PrepareSQL("update path set texture='image://%s?size=thumb' where id=%u", url.GetHostName().c_str(), id));
 186       m_pDS->next();
 187     }
 188     m_pDS->query("select id, url from texture where url like 'thumb://%'");
 189     while (!m_pDS->eof())
 190     {
 191       unsigned int id = m_pDS->fv(0).get_asInt();
 192       CURL url(m_pDS->fv(1).get_asString());
 193       m_pDS2->exec(PrepareSQL("update texture set url='image://%s?size=thumb', urlhash=0 where id=%u", url.GetHostName().c_str(), id));
 194       m_pDS->next();
 195     }
 196     m_pDS->close();
 197   }
 198   if (version < 8)
 199   { // get rid of old cached thumbs as they were previously set to the cached thumb name instead of the source thumb
 200     m_pDS->exec("delete from path");
 201   }
 202   if (version < 9)
 203   { // get rid of the old path table and add the type column
 204     m_pDS->exec("DROP TABLE IF EXISTS path");
 205     m_pDS->exec("CREATE TABLE path (id integer primary key, urlhash integer, url text, type text, texture text)\n");
 206   }
 207   if (version < 10)
 208   { // get rid of urlhash in both tables...
 209     m_pDS->exec("DROP TABLE IF EXISTS path");
 210     m_pDS->exec("CREATE TABLE path (id integer primary key, url text, type text, texture text)\n");
 211
 212     m_pDS->exec("CREATE TEMPORARY TABLE texture_backup(id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck)");
 213     m_pDS->exec("INSERT INTO texture_backup SELECT id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck FROM texture");
 214     m_pDS->exec("DROP TABLE texture");
 215     m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, usecount integer, lastusetime text, imagehash text, lasthashcheck text)");
 216     m_pDS->exec("INSERT INTO texture SELECT * FROM texture_backup");
 217     m_pDS->exec("DROP TABLE texture_backup");
 218   }
 219   if (version < 11)
 220   { // get rid of cached URLs that don't have the correct extension
 221     m_pDS->exec("DELETE FROM texture WHERE SUBSTR(cachedUrl,-4,4) NOT IN ('.jpg', '.png')");
 222   }
 223   if (version < 12)
 224   { // create new sizes table and move usecount info to it.
 225     m_pDS->exec("DROP TABLE IF EXISTS texture");
 226     m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, imagehash text, lasthashcheck text)");
 227     m_pDS->exec("CREATE TABLE sizes (idtexture integer, size integer, width integer, height integer, usecount integer, lastusetime text)");
 228   }
 229 }
 230
 231 bool CTextureDatabase::IncrementUseCount(const CTextureDetails &details)
 232 {
 233   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);
 234   return ExecuteQuery(sql);
 235 }
 236
 237 bool CTextureDatabase::GetCachedTexture(const std::string &url, CTextureDetails &details)
 238 {
 239   try
 240   {
 241     if (!m_pDB)
 242       return false;
 243     if (!m_pDS)
 244       return false;
 245
 246     std::string sql = PrepareSQL("SELECT id, cachedurl, lasthashcheck, imagehash, width, height FROM texture JOIN sizes ON (texture.id=sizes.idtexture AND sizes.size=1) WHERE url='%s'", url.c_str());
 247     m_pDS->query(sql);
 248     if (!m_pDS->eof())
 249     { // have some information
 250       details.id = m_pDS->fv(0).get_asInt();
 251       details.file  = m_pDS->fv(1).get_asString();
 252       CDateTime lastCheck;
 253       lastCheck.SetFromDBDateTime(m_pDS->fv(2).get_asString());
 254       if (lastCheck.IsValid() && lastCheck + CDateTimeSpan(1,0,0,0) < CDateTime::GetCurrentDateTime())
 255         details.hash = m_pDS->fv(3).get_asString();
 256       details.width = m_pDS->fv(4).get_asInt();
 257       details.height = m_pDS->fv(5).get_asInt();
 258       m_pDS->close();
 259       return true;
 260     }
 261     m_pDS->close();
 262   }
 263   catch (...)
 264   {
 265     CLog::Log(LOGERROR, "{}, failed on url '{}'", __FUNCTION__, url);
 266   }
 267   return false;
 268 }
 269
 270 bool CTextureDatabase::GetTextures(CVariant &items, const Filter &filter)
 271 {
 272   try
 273   {
 274     if (!m_pDB)
 275       return false;
 276     if (!m_pDS)
 277       return false;
 278
 279     std::string sql = "SELECT %s FROM texture JOIN sizes ON (texture.id=sizes.idtexture AND sizes.size=1)";
 280     std::string sqlFilter;
 281     if (!CDatabase::BuildSQL("", filter, sqlFilter))
 282       return false;
 283
 284     sql = PrepareSQL(sql, !filter.fields.empty() ? filter.fields.c_str() : "*") + sqlFilter;
 285     if (!m_pDS->query(sql))
 286       return false;
 287
 288     while (!m_pDS->eof())
 289     {
 290       CVariant texture;
 291       texture["textureid"] = m_pDS->fv(0).get_asInt();
 292       texture["url"] = m_pDS->fv(1).get_asString();
 293       texture["cachedurl"] = m_pDS->fv(2).get_asString();
 294       texture["imagehash"] = m_pDS->fv(3).get_asString();
 295       texture["lasthashcheck"] = m_pDS->fv(4).get_asString();
 296       CVariant size(CVariant::VariantTypeObject);
 297       // 5 is sizes.idtexture
 298       size["size"]  = m_pDS->fv(6).get_asInt();
 299       size["width"] = m_pDS->fv(7).get_asInt();
 300       size["height"] = m_pDS->fv(8).get_asInt();
 301       size["usecount"] = m_pDS->fv(9).get_asInt();
 302       size["lastused"] = m_pDS->fv(10).get_asString();
 303       texture["sizes"] = CVariant(CVariant::VariantTypeArray);
 304       texture["sizes"].push_back(size);
 305       items.push_back(texture);
 306       m_pDS->next();
 307     }
 308     m_pDS->close();
 309     return true;
 310   }
 311   catch (...)
 312   {
 313     CLog::Log(LOGERROR, "{}, failed", __FUNCTION__);
 314   }
 315   return false;
 316 }
 317
 318 bool CTextureDatabase::SetCachedTextureValid(const std::string &url, bool updateable)
 319 {
 320   std::string date = updateable ? CDateTime::GetCurrentDateTime().GetAsDBDateTime() : "";
 321   std::string sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str());
 322   return ExecuteQuery(sql);
 323 }
 324
 325 bool CTextureDatabase::AddCachedTexture(const std::string &url, const CTextureDetails &details)
 326 {
 327   try
 328   {
 329     if (!m_pDB)
 330       return false;
 331     if (!m_pDS)
 332       return false;
 333
 334     std::string sql = PrepareSQL("DELETE FROM texture WHERE url='%s'", url.c_str());
 335     m_pDS->exec(sql);
 336
 337     std::string date = details.updateable ? CDateTime::GetCurrentDateTime().GetAsDBDateTime() : "";
 338     sql = PrepareSQL("INSERT INTO texture (id, url, cachedurl, imagehash, lasthashcheck) VALUES(NULL, '%s', '%s', '%s', '%s')", url.c_str(), details.file.c_str(), details.hash.c_str(), date.c_str());
 339     m_pDS->exec(sql);
 340     int textureID = (int)m_pDS->lastinsertid();
 341
 342     // set the size information
 343     sql = PrepareSQL("INSERT INTO sizes (idtexture, size, usecount, lastusetime, width, height) VALUES(%u, 1, 1, CURRENT_TIMESTAMP, %u, %u)", textureID, details.width, details.height);
 344     m_pDS->exec(sql);
 345   }
 346   catch (...)
 347   {
 348     CLog::Log(LOGERROR, "{} failed on url '{}'", __FUNCTION__, url);
 349   }
 350   return true;
 351 }
 352
 353 bool CTextureDatabase::ClearCachedTexture(const std::string &url, std::string &cacheFile)
 354 {
 355   std::string id = GetSingleValue(PrepareSQL("select id from texture where url='%s'", url.c_str()));
 356   return !id.empty() ? ClearCachedTexture(strtol(id.c_str(), NULL, 10), cacheFile) : false;
 357 }
 358
 359 bool CTextureDatabase::ClearCachedTexture(int id, std::string &cacheFile)
 360 {
 361   try
 362   {
 363     if (!m_pDB)
 364       return false;
 365     if (!m_pDS)
 366       return false;
 367
 368     std::string sql = PrepareSQL("select cachedurl from texture where id=%u", id);
 369     m_pDS->query(sql);
 370
 371     if (!m_pDS->eof())
 372     { // have some information
 373       cacheFile = m_pDS->fv(0).get_asString();
 374       m_pDS->close();
 375       // remove it
 376       sql = PrepareSQL("delete from texture where id=%u", id);
 377       m_pDS->exec(sql);
 378       return true;
 379     }
 380     m_pDS->close();
 381   }
 382   catch (...)
 383   {
 384     CLog::Log(LOGERROR, "{}, failed on texture id {}", __FUNCTION__, id);
 385   }
 386   return false;
 387 }
 388
 389 bool CTextureDatabase::InvalidateCachedTexture(const std::string &url)
 390 {
 391   std::string date = (CDateTime::GetCurrentDateTime() - CDateTimeSpan(2, 0, 0, 0)).GetAsDBDateTime();
 392   std::string sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str());
 393   return ExecuteQuery(sql);
 394 }
 395
 396 std::string CTextureDatabase::GetTextureForPath(const std::string &url, const std::string &type)
 397 {
 398   try
 399   {
 400     if (!m_pDB)
 401       return "";
 402     if (!m_pDS)
 403       return "";
 404
 405     if (url.empty())
 406       return "";
 407
 408     std::string sql = PrepareSQL("select texture from path where url='%s' and type='%s'", url.c_str(), type.c_str());
 409     m_pDS->query(sql);
 410
 411     if (!m_pDS->eof())
 412     { // have some information
 413       std::string texture = m_pDS->fv(0).get_asString();
 414       m_pDS->close();
 415       return texture;
 416     }
 417     m_pDS->close();
 418   }
 419   catch (...)
 420   {
 421     CLog::Log(LOGERROR, "{}, failed on url '{}'", __FUNCTION__, url);
 422   }
 423   return "";
 424 }
 425
 426 void CTextureDatabase::SetTextureForPath(const std::string &url, const std::string &type, const std::string &texture)
 427 {
 428   try
 429   {
 430     if (!m_pDB)
 431       return;
 432     if (!m_pDS)
 433       return;
 434
 435     if (url.empty())
 436       return;
 437
 438     std::string sql = PrepareSQL("select id from path where url='%s' and type='%s'", url.c_str(), type.c_str());
 439     m_pDS->query(sql);
 440     if (!m_pDS->eof())
 441     { // update
 442       int pathID = m_pDS->fv(0).get_asInt();
 443       m_pDS->close();
 444       sql = PrepareSQL("update path set texture='%s' where id=%u", texture.c_str(), pathID);
 445       m_pDS->exec(sql);
 446     }
 447     else
 448     { // add the texture
 449       m_pDS->close();
 450       sql = PrepareSQL("insert into path (id, url, type, texture) values(NULL, '%s', '%s', '%s')", url.c_str(), type.c_str(), texture.c_str());
 451       m_pDS->exec(sql);
 452     }
 453   }
 454   catch (...)
 455   {
 456     CLog::Log(LOGERROR, "{} failed on url '{}'", __FUNCTION__, url);
 457   }
 458 }
 459
 460 void CTextureDatabase::ClearTextureForPath(const std::string &url, const std::string &type)
 461 {
 462   try
 463   {
 464     if (!m_pDB)
 465       return;
 466     if (!m_pDS)
 467       return;
 468
 469     std::string sql = PrepareSQL("DELETE FROM path WHERE url='%s' and type='%s'", url.c_str(), type.c_str());
 470     m_pDS->exec(sql);
 471   }
 472   catch (...)
 473   {
 474     CLog::Log(LOGERROR, "{} failed on url '{}'", __FUNCTION__, url);
 475   }
 476 }
 477
 478 CDatabaseQueryRule *CTextureDatabase::CreateRule() const
 479 {
 480   return new CTextureRule();
 481 }
 482
 483 CDatabaseQueryRuleCombination *CTextureDatabase::CreateCombination() const
 484 {
 485   return new CDatabaseQueryRuleCombination();
 486 }