xbmc/TextureDatabase.cpp

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