2 * Copyright (C) 2005-2013 Team XBMC
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)
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.
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/>.
21 #include "TextureDatabase.h"
22 #include "utils/log.h"
23 #include "XBDateTime.h"
24 #include "dbwrappers/dataset.h"
26 #include "utils/StringUtils.h"
27 #include "utils/Variant.h"
48 CDatabaseQueryRule::FIELD_TYPE type;
53 static const translateField fields[] = {
54 { "none", TF_None, CDatabaseQueryRule::TEXT_FIELD },
55 { "textureid", TF_Id, CDatabaseQueryRule::NUMERIC_FIELD },
56 { "url", TF_Url, CDatabaseQueryRule::TEXT_FIELD },
57 { "cachedurl", TF_CachedUrl, CDatabaseQueryRule::TEXT_FIELD },
58 { "lasthashcheck", TF_LastHashCheck, CDatabaseQueryRule::TEXT_FIELD },
59 { "imagehash", TF_ImageHash, CDatabaseQueryRule::TEXT_FIELD },
60 { "width", TF_Width, CDatabaseQueryRule::NUMERIC_FIELD },
61 { "height", TF_Height, CDatabaseQueryRule::NUMERIC_FIELD },
62 { "usecount", TF_UseCount, CDatabaseQueryRule::NUMERIC_FIELD },
63 { "lastused", TF_LastUsed, CDatabaseQueryRule::TEXT_FIELD }
66 static const size_t NUM_FIELDS = sizeof(fields) / sizeof(translateField);
68 int CTextureRule::TranslateField(const char *field) const
70 for (unsigned int i = 0; i < NUM_FIELDS; i++)
71 if (StringUtils::EqualsNoCase(field, fields[i].string)) return fields[i].field;
75 CStdString CTextureRule::TranslateField(int field) const
77 for (unsigned int i = 0; i < NUM_FIELDS; i++)
78 if (field == fields[i].field) return fields[i].string;
82 CStdString CTextureRule::GetField(int field, const CStdString &type) const
84 if (field == TF_Id) return "texture.id";
85 else if (field == TF_Url) return "texture.url";
86 else if (field == TF_CachedUrl) return "texture.cachedurl";
87 else if (field == TF_LastHashCheck) return "texture.lasthashcheck";
88 else if (field == TF_ImageHash) return "texture.imagehash";
89 else if (field == TF_Width) return "sizes.width";
90 else if (field == TF_Height) return "sizes.height";
91 else if (field == TF_UseCount) return "sizes.usecount";
92 else if (field == TF_LastUsed) return "sizes.lastusetime";
96 CDatabaseQueryRule::FIELD_TYPE CTextureRule::GetFieldType(int field) const
98 for (unsigned int i = 0; i < NUM_FIELDS; i++)
99 if (field == fields[i].field) return fields[i].type;
103 CStdString CTextureRule::FormatParameter(const CStdString &operatorString, const CStdString ¶m, const CDatabase &db, const CStdString &strType) const
105 CStdString parameter(param);
106 if (m_field == TF_Url)
107 parameter = CTextureUtils::UnwrapImageURL(param);
108 return CDatabaseQueryRule::FormatParameter(operatorString, parameter, db, strType);
111 void CTextureRule::GetAvailableFields(std::vector<std::string> &fieldList)
113 // start at 1 to skip TF_None
114 for (unsigned int i = 1; i < NUM_FIELDS; i++)
115 fieldList.push_back(fields[i].string);
118 CStdString CTextureUtils::GetWrappedImageURL(const CStdString &image, const CStdString &type, const CStdString &options)
120 if (StringUtils::StartsWith(image, "image://"))
121 return image; // already wrapped
124 url.SetProtocol("image");
125 url.SetUserName(type);
126 url.SetHostName(image);
127 if (!options.IsEmpty())
129 url.SetFileName("transform");
130 url.SetOptions("?" + options);
135 CStdString CTextureUtils::GetWrappedThumbURL(const CStdString &image)
137 return GetWrappedImageURL(image, "", "size=thumb");
140 CStdString CTextureUtils::UnwrapImageURL(const CStdString &image)
142 if (StringUtils::StartsWith(image, "image://"))
145 if (url.GetUserName().IsEmpty() && url.GetOptions().IsEmpty())
146 return url.GetHostName();
151 CTextureDatabase::CTextureDatabase()
155 CTextureDatabase::~CTextureDatabase()
159 bool CTextureDatabase::Open()
161 return CDatabase::Open();
164 bool CTextureDatabase::CreateTables()
168 CDatabase::CreateTables();
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)");
173 CLog::Log(LOGINFO, "create textures index");
174 m_pDS->exec("CREATE INDEX idxTexture ON texture(url)");
176 CLog::Log(LOGINFO, "create sizes table, index, and trigger");
177 m_pDS->exec("CREATE TABLE sizes (idtexture integer, size integer, width integer, height integer, usecount integer, lastusetime text)");
178 m_pDS->exec("CREATE INDEX idxSize ON sizes(idtexture, size)");
179 m_pDS->exec("CREATE INDEX idxSize2 ON sizes(idtexture, width, height)");
180 m_pDS->exec("CREATE TRIGGER textureDelete AFTER delete ON texture FOR EACH ROW BEGIN delete from sizes where sizes.idtexture=old.id; END");
182 CLog::Log(LOGINFO, "create path table");
183 m_pDS->exec("CREATE TABLE path (id integer primary key, url text, type text, texture text)\n");
185 // TODO: Should the path index be a covering index? (we need only retrieve texture)
186 CLog::Log(LOGINFO, "create path index");
187 m_pDS->exec("CREATE INDEX idxPath ON path(url, type)");
191 CLog::Log(LOGERROR, "%s unable to create tables", __FUNCTION__);
198 bool CTextureDatabase::UpdateOldVersion(int version)
201 { // update all old thumb://foo urls to image://foo?size=thumb
202 m_pDS->query("select id,texture from path where texture like 'thumb://%'");
203 while (!m_pDS->eof())
205 unsigned int id = m_pDS->fv(0).get_asInt();
206 CURL url(m_pDS->fv(1).get_asString());
207 m_pDS2->exec(PrepareSQL("update path set texture='image://%s?size=thumb' where id=%u", url.GetHostName().c_str(), id));
210 m_pDS->query("select id, url from texture where url like 'thumb://%'");
211 while (!m_pDS->eof())
213 unsigned int id = m_pDS->fv(0).get_asInt();
214 CURL url(m_pDS->fv(1).get_asString());
215 m_pDS2->exec(PrepareSQL("update texture set url='image://%s?size=thumb', urlhash=0 where id=%u", url.GetHostName().c_str(), id));
221 { // get rid of old cached thumbs as they were previously set to the cached thumb name instead of the source thumb
222 m_pDS->exec("delete from path");
225 { // get rid of the old path table and add the type column
226 m_pDS->dropIndex("path", "idxPath");
227 m_pDS->exec("DROP TABLE path");
228 m_pDS->exec("CREATE TABLE path (id integer primary key, urlhash integer, url text, type text, texture text)\n");
229 m_pDS->exec("CREATE INDEX idxPath ON path(urlhash, type)");
232 { // get rid of urlhash in both tables...
233 m_pDS->dropIndex("path", "idxPath");
234 m_pDS->exec("DROP TABLE path");
235 m_pDS->exec("CREATE TABLE path (id integer primary key, url text, type text, texture text)\n");
236 m_pDS->exec("CREATE INDEX idxPath ON path(url, type)");
238 m_pDS->dropIndex("texture", "idxTexture");
239 m_pDS->exec("CREATE TEMPORARY TABLE texture_backup(id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck)");
240 m_pDS->exec("INSERT INTO texture_backup SELECT id,url,cachedurl,usecount,lastusetime,imagehash,lasthashcheck FROM texture");
241 m_pDS->exec("DROP TABLE texture");
242 m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, usecount integer, lastusetime text, imagehash text, lasthashcheck text)");
243 m_pDS->exec("CREATE INDEX idxTexture ON texture(url)");
244 m_pDS->exec("INSERT INTO texture SELECT * FROM texture_backup");
245 m_pDS->exec("DROP TABLE texture_backup");
248 { // get rid of cached URLs that don't have the correct extension
249 m_pDS->exec("DELETE FROM texture WHERE SUBSTR(cachedUrl,-4,4) NOT IN ('.jpg', '.png')");
252 { // create new sizes table and move usecount info to it.
253 m_pDS->exec("DROP TABLE texture");
254 m_pDS->exec("CREATE TABLE texture (id integer primary key, url text, cachedurl text, imagehash text, lasthashcheck text)");
255 m_pDS->exec("CREATE INDEX idxTexture ON texture(url)");
256 m_pDS->exec("CREATE TABLE sizes (idtexture integer, size integer, width integer, height integer, usecount integer, lastusetime text)");
257 m_pDS->exec("CREATE INDEX idxSize ON sizes(idtexture, size)");
258 m_pDS->exec("CREATE TRIGGER textureDelete AFTER delete ON texture FOR EACH ROW BEGIN delete from sizes where sizes.idtexture=old.id; END");
261 { // index for updateusecount
262 m_pDS->exec("CREATE INDEX idxSize2 ON sizes(idtexture, width, height)");
267 bool CTextureDatabase::IncrementUseCount(const CTextureDetails &details)
269 CStdString 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);
270 return ExecuteQuery(sql);
273 bool CTextureDatabase::GetCachedTexture(const CStdString &url, CTextureDetails &details)
277 if (NULL == m_pDB.get()) return false;
278 if (NULL == m_pDS.get()) return false;
280 CStdString 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());
281 m_pDS->query(sql.c_str());
283 { // have some information
284 details.id = m_pDS->fv(0).get_asInt();
285 details.file = m_pDS->fv(1).get_asString();
287 lastCheck.SetFromDBDateTime(m_pDS->fv(2).get_asString());
288 if (lastCheck.IsValid() && lastCheck + CDateTimeSpan(1,0,0,0) < CDateTime::GetCurrentDateTime())
289 details.hash = m_pDS->fv(3).get_asString();
290 details.width = m_pDS->fv(4).get_asInt();
291 details.height = m_pDS->fv(5).get_asInt();
299 CLog::Log(LOGERROR, "%s, failed on url '%s'", __FUNCTION__, url.c_str());
304 bool CTextureDatabase::GetTextures(CVariant &items, const Filter &filter)
308 if (NULL == m_pDB.get()) return false;
309 if (NULL == m_pDS.get()) return false;
311 CStdString sql = "SELECT %s FROM texture JOIN sizes ON (texture.id=sizes.idtexture AND sizes.size=1)";
312 CStdString sqlFilter;
313 if (!CDatabase::BuildSQL("", filter, sqlFilter))
316 sql = PrepareSQL(sql, !filter.fields.empty() ? filter.fields.c_str() : "*") + sqlFilter;
317 if (!m_pDS->query(sql.c_str()))
320 while (!m_pDS->eof())
323 texture["textureid"] = m_pDS->fv(0).get_asInt();
324 texture["url"] = m_pDS->fv(1).get_asString();
325 texture["cachedurl"] = m_pDS->fv(2).get_asString();
326 texture["imagehash"] = m_pDS->fv(3).get_asString();
327 texture["lasthashcheck"] = m_pDS->fv(4).get_asString();
328 CVariant size(CVariant::VariantTypeObject);
329 // 5 is sizes.idtexture
330 size["size"] = m_pDS->fv(6).get_asInt();
331 size["width"] = m_pDS->fv(7).get_asInt();
332 size["height"] = m_pDS->fv(8).get_asInt();
333 size["usecount"] = m_pDS->fv(9).get_asInt();
334 size["lastused"] = m_pDS->fv(10).get_asString();
335 texture["sizes"] = CVariant(CVariant::VariantTypeArray);
336 texture["sizes"].push_back(size);
337 items.push_back(texture);
345 CLog::Log(LOGERROR, "%s, failed", __FUNCTION__);
350 bool CTextureDatabase::SetCachedTextureValid(const CStdString &url, bool updateable)
352 CStdString date = updateable ? CDateTime::GetCurrentDateTime().GetAsDBDateTime() : "";
353 CStdString sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str());
354 return ExecuteQuery(sql);
357 bool CTextureDatabase::AddCachedTexture(const CStdString &url, const CTextureDetails &details)
361 if (NULL == m_pDB.get()) return false;
362 if (NULL == m_pDS.get()) return false;
364 CStdString sql = PrepareSQL("DELETE FROM texture WHERE url='%s'", url.c_str());
365 m_pDS->exec(sql.c_str());
367 CStdString date = details.updateable ? CDateTime::GetCurrentDateTime().GetAsDBDateTime() : "";
368 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());
369 m_pDS->exec(sql.c_str());
370 int textureID = (int)m_pDS->lastinsertid();
372 // set the size information
373 sql = PrepareSQL("INSERT INTO sizes (idtexture, size, usecount, lastusetime, width, height) VALUES(%u, 1, 1, CURRENT_TIMESTAMP, %u, %u)", textureID, details.width, details.height);
374 m_pDS->exec(sql.c_str());
378 CLog::Log(LOGERROR, "%s failed on url '%s'", __FUNCTION__, url.c_str());
383 bool CTextureDatabase::ClearCachedTexture(const CStdString &url, CStdString &cacheFile)
385 std::string id = GetSingleValue(PrepareSQL("select id from texture where url='%s'", url.c_str()));
386 return !id.empty() ? ClearCachedTexture(strtol(id.c_str(), NULL, 10), cacheFile) : false;
389 bool CTextureDatabase::ClearCachedTexture(int id, CStdString &cacheFile)
393 if (NULL == m_pDB.get()) return false;
394 if (NULL == m_pDS.get()) return false;
396 CStdString sql = PrepareSQL("select cachedurl from texture where id=%u", id);
397 m_pDS->query(sql.c_str());
400 { // have some information
401 cacheFile = m_pDS->fv(0).get_asString();
404 sql = PrepareSQL("delete from texture where id=%u", id);
405 m_pDS->exec(sql.c_str());
412 CLog::Log(LOGERROR, "%s, failed on texture id %u", __FUNCTION__, id);
417 bool CTextureDatabase::InvalidateCachedTexture(const CStdString &url)
419 CStdString date = (CDateTime::GetCurrentDateTime() - CDateTimeSpan(2, 0, 0, 0)).GetAsDBDateTime();
420 CStdString sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str());
421 return ExecuteQuery(sql);
424 CStdString CTextureDatabase::GetTextureForPath(const CStdString &url, const CStdString &type)
428 if (NULL == m_pDB.get()) return "";
429 if (NULL == m_pDS.get()) return "";
434 CStdString sql = PrepareSQL("select texture from path where url='%s' and type='%s'", url.c_str(), type.c_str());
435 m_pDS->query(sql.c_str());
438 { // have some information
439 CStdString texture = m_pDS->fv(0).get_asString();
447 CLog::Log(LOGERROR, "%s, failed on url '%s'", __FUNCTION__, url.c_str());
452 void CTextureDatabase::SetTextureForPath(const CStdString &url, const CStdString &type, const CStdString &texture)
456 if (NULL == m_pDB.get()) return;
457 if (NULL == m_pDS.get()) return;
462 CStdString sql = PrepareSQL("select id from path where url='%s' and type='%s'", url.c_str(), type.c_str());
463 m_pDS->query(sql.c_str());
466 int pathID = m_pDS->fv(0).get_asInt();
468 sql = PrepareSQL("update path set texture='%s' where id=%u", texture.c_str(), pathID);
469 m_pDS->exec(sql.c_str());
474 sql = PrepareSQL("insert into path (id, url, type, texture) values(NULL, '%s', '%s', '%s')", url.c_str(), type.c_str(), texture.c_str());
475 m_pDS->exec(sql.c_str());
480 CLog::Log(LOGERROR, "%s failed on url '%s'", __FUNCTION__, url.c_str());
485 void CTextureDatabase::ClearTextureForPath(const CStdString &url, const CStdString &type)
489 if (NULL == m_pDB.get()) return;
490 if (NULL == m_pDS.get()) return;
492 CStdString sql = PrepareSQL("DELETE FROM path WHERE url='%s' and type='%s'", url.c_str(), type.c_str());
493 m_pDS->exec(sql.c_str());
497 CLog::Log(LOGERROR, "%s failed on url '%s'", __FUNCTION__, url.c_str());
502 CDatabaseQueryRule *CTextureDatabase::CreateRule() const
504 return new CTextureRule();
507 CDatabaseQueryRuleCombination *CTextureDatabase::CreateCombination() const
509 return new CDatabaseQueryRuleCombination();