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