1 /*
2  *  Copyright (C) 2012-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 "DatabaseUtils.h"
10 
11 #include "dbwrappers/dataset.h"
12 #include "music/MusicDatabase.h"
13 #include "utils/StringUtils.h"
14 #include "utils/Variant.h"
15 #include "utils/log.h"
16 #include "video/VideoDatabase.h"
17 
18 #include <sstream>
19 
MediaTypeFromVideoContentType(int videoContentType)20 MediaType DatabaseUtils::MediaTypeFromVideoContentType(int videoContentType)
21 {
22   VIDEODB_CONTENT_TYPE type = (VIDEODB_CONTENT_TYPE)videoContentType;
23   switch (type)
24   {
25     case VIDEODB_CONTENT_MOVIES:
26       return MediaTypeMovie;
27 
28     case VIDEODB_CONTENT_MOVIE_SETS:
29       return MediaTypeVideoCollection;
30 
31     case VIDEODB_CONTENT_TVSHOWS:
32       return MediaTypeTvShow;
33 
34     case VIDEODB_CONTENT_EPISODES:
35       return MediaTypeEpisode;
36 
37     case VIDEODB_CONTENT_MUSICVIDEOS:
38       return MediaTypeMusicVideo;
39 
40     default:
41       break;
42   }
43 
44   return MediaTypeNone;
45 }
46 
GetField(Field field,const MediaType & mediaType,DatabaseQueryPart queryPart)47 std::string DatabaseUtils::GetField(Field field, const MediaType &mediaType, DatabaseQueryPart queryPart)
48 {
49   if (field == FieldNone || mediaType == MediaTypeNone)
50     return "";
51 
52   if (mediaType == MediaTypeAlbum)
53   {
54     if (field == FieldId) return "albumview.idAlbum";
55     else if (field == FieldAlbum) return "albumview.strAlbum";
56     else if (field == FieldArtist || field == FieldAlbumArtist) return "albumview.strArtists";
57     else if (field == FieldGenre)
58       return "albumview.strGenres";
59     else if (field == FieldYear)
60       return "albumview.strReleaseDate";
61     else if (field == FieldOrigYear || field == FieldOrigDate)
62       return "albumview.strOrigReleaseDate";
63     else if (field == FieldMoods) return "albumview.strMoods";
64     else if (field == FieldStyles) return "albumview.strStyles";
65     else if (field == FieldThemes) return "albumview.strThemes";
66     else if (field == FieldReview) return "albumview.strReview";
67     else if (field == FieldMusicLabel) return "albumview.strLabel";
68     else if (field == FieldAlbumType) return "albumview.strType";
69     else if (field == FieldCompilation) return "albumview.bCompilation";
70     else if (field == FieldRating) return "albumview.fRating";
71     else if (field == FieldVotes) return "albumview.iVotes";
72     else if (field == FieldUserRating) return "albumview.iUserrating";
73     else if (field == FieldDateAdded) return "albumview.dateAdded";
74     else if (field == FieldDateNew) return "albumview.dateNew";
75     else if (field == FieldDateModified) return "albumview.dateModified";
76     else if (field == FieldPlaycount) return "albumview.iTimesPlayed";
77     else if (field == FieldLastPlayed) return "albumview.lastPlayed";
78     else if (field == FieldTotalDiscs)
79       return "albumview.iDiscTotal";
80     else if (field == FieldAlbumStatus)
81         return "albumview.strReleaseStatus";
82     else if (field == FieldAlbumDuration)
83       return "albumview.iAlbumDuration";
84   }
85   else if (mediaType == MediaTypeSong)
86   {
87     if (field == FieldId) return "songview.idSong";
88     else if (field == FieldTitle) return "songview.strTitle";
89     else if (field == FieldTrackNumber) return "songview.iTrack";
90     else if (field == FieldTime) return "songview.iDuration";
91     else if (field == FieldYear)
92       return "songview.strReleaseDate";
93     else if (field == FieldOrigYear || field == FieldOrigDate)
94       return "songview.strOrigReleaseDate";
95     else if (field == FieldFilename) return "songview.strFilename";
96     else if (field == FieldPlaycount) return "songview.iTimesPlayed";
97     else if (field == FieldStartOffset) return "songview.iStartOffset";
98     else if (field == FieldEndOffset) return "songview.iEndOffset";
99     else if (field == FieldLastPlayed) return "songview.lastPlayed";
100     else if (field == FieldRating) return "songview.rating";
101     else if (field == FieldVotes) return "songview.votes";
102     else if (field == FieldUserRating) return "songview.userrating";
103     else if (field == FieldComment) return "songview.comment";
104     else if (field == FieldMoods) return "songview.mood";
105     else if (field == FieldAlbum) return "songview.strAlbum";
106     else if (field == FieldPath) return "songview.strPath";
107     else if (field == FieldArtist || field == FieldAlbumArtist) return "songview.strArtists";
108     else if (field == FieldGenre)
109       return "songview.strGenres";
110     else if (field == FieldDateAdded) return "songview.dateAdded";
111     else if (field == FieldDateNew) return "songview.dateNew";
112     else if (field == FieldDateModified) return "songview.dateModified";
113 
114     else if (field == FieldDiscTitle)
115       return "songview.strDiscSubtitle";
116     else if (field == FieldBPM)
117         return "songview.iBPM";
118     else if (field == FieldMusicBitRate)
119         return "songview.iBitRate";
120     else if (field == FieldSampleRate)
121         return "songview.iSampleRate";
122     else if (field == FieldNoOfChannels)
123         return "songview.iChannels";
124   }
125   else if (mediaType == MediaTypeArtist)
126   {
127     if (field == FieldId) return "artistview.idArtist";
128     else if (field == FieldArtistSort) return "artistview.strSortName";
129     else if (field == FieldArtist) return "artistview.strArtist";
130     else if (field == FieldArtistType) return "artistview.strType";
131     else if (field == FieldGender) return "artistview.strGender";
132     else if (field == FieldDisambiguation) return "artistview.strDisambiguation";
133     else if (field == FieldGenre) return "artistview.strGenres";
134     else if (field == FieldMoods) return "artistview.strMoods";
135     else if (field == FieldStyles) return "artistview.strStyles";
136     else if (field == FieldInstruments) return "artistview.strInstruments";
137     else if (field == FieldBiography) return "artistview.strBiography";
138     else if (field == FieldBorn) return "artistview.strBorn";
139     else if (field == FieldBandFormed) return "artistview.strFormed";
140     else if (field == FieldDisbanded) return "artistview.strDisbanded";
141     else if (field == FieldDied) return "artistview.strDied";
142     else if (field == FieldDateAdded) return "artistview.dateAdded";
143     else if (field == FieldDateNew) return "artistview.dateNew";
144     else if (field == FieldDateModified) return "artistview.dateModified";
145   }
146   else if (mediaType == MediaTypeMusicVideo)
147   {
148     std::string result;
149     if (field == FieldId) return "musicvideo_view.idMVideo";
150     else if (field == FieldTitle) result = StringUtils::Format("musicvideo_view.c%02d",VIDEODB_ID_MUSICVIDEO_TITLE);
151     else if (field == FieldTime) result = StringUtils::Format("musicvideo_view.c%02d", VIDEODB_ID_MUSICVIDEO_RUNTIME);
152     else if (field == FieldDirector) result = StringUtils::Format("musicvideo_view.c%02d", VIDEODB_ID_MUSICVIDEO_DIRECTOR);
153     else if (field == FieldStudio) result = StringUtils::Format("musicvideo_view.c%02d", VIDEODB_ID_MUSICVIDEO_STUDIOS);
154     else if (field == FieldYear) return "musicvideo_view.premiered";
155     else if (field == FieldPlot) result = StringUtils::Format("musicvideo_view.c%02d", VIDEODB_ID_MUSICVIDEO_PLOT);
156     else if (field == FieldAlbum) result = StringUtils::Format("musicvideo_view.c%02d",VIDEODB_ID_MUSICVIDEO_ALBUM);
157     else if (field == FieldArtist) result = StringUtils::Format("musicvideo_view.c%02d", VIDEODB_ID_MUSICVIDEO_ARTIST);
158     else if (field == FieldGenre) result = StringUtils::Format("musicvideo_view.c%02d", VIDEODB_ID_MUSICVIDEO_GENRE);
159     else if (field == FieldTrackNumber) result = StringUtils::Format("musicvideo_view.c%02d", VIDEODB_ID_MUSICVIDEO_TRACK);
160     else if (field == FieldFilename) return "musicvideo_view.strFilename";
161     else if (field == FieldPath) return "musicvideo_view.strPath";
162     else if (field == FieldPlaycount) return "musicvideo_view.playCount";
163     else if (field == FieldLastPlayed) return "musicvideo_view.lastPlayed";
164     else if (field == FieldDateAdded) return "musicvideo_view.dateAdded";
165     else if (field == FieldUserRating) return "musicvideo_view.userrating";
166 
167     if (!result.empty())
168       return result;
169   }
170   else if (mediaType == MediaTypeMovie)
171   {
172     std::string result;
173     if (field == FieldId) return "movie_view.idMovie";
174     else if (field == FieldTitle)
175     {
176       // We need some extra logic to get the title value if sorttitle isn't set
177       if (queryPart == DatabaseQueryPartOrderBy)
178         result = StringUtils::Format("CASE WHEN length(movie_view.c%02d) > 0 THEN movie_view.c%02d ELSE movie_view.c%02d END", VIDEODB_ID_SORTTITLE, VIDEODB_ID_SORTTITLE, VIDEODB_ID_TITLE);
179       else
180         result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_TITLE);
181     }
182     else if (field == FieldPlot) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_PLOT);
183     else if (field == FieldPlotOutline) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_PLOTOUTLINE);
184     else if (field == FieldTagline) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_TAGLINE);
185     else if (field == FieldVotes) return "movie_view.votes";
186     else if (field == FieldRating) return "movie_view.rating";
187     else if (field == FieldWriter) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_CREDITS);
188     else if (field == FieldYear) return "movie_view.premiered";
189     else if (field == FieldSortTitle) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_SORTTITLE);
190     else if (field == FieldOriginalTitle) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_ORIGINALTITLE);
191     else if (field == FieldTime) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_RUNTIME);
192     else if (field == FieldMPAA) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_MPAA);
193     else if (field == FieldTop250) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_TOP250);
194     else if (field == FieldSet) return "movie_view.strSet";
195     else if (field == FieldGenre) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_GENRE);
196     else if (field == FieldDirector) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_DIRECTOR);
197     else if (field == FieldStudio) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_STUDIOS);
198     else if (field == FieldTrailer) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_TRAILER);
199     else if (field == FieldCountry) result = StringUtils::Format("movie_view.c%02d", VIDEODB_ID_COUNTRY);
200     else if (field == FieldFilename) return "movie_view.strFilename";
201     else if (field == FieldPath) return "movie_view.strPath";
202     else if (field == FieldPlaycount) return "movie_view.playCount";
203     else if (field == FieldLastPlayed) return "movie_view.lastPlayed";
204     else if (field == FieldDateAdded) return "movie_view.dateAdded";
205     else if (field == FieldUserRating) return "movie_view.userrating";
206 
207     if (!result.empty())
208       return result;
209   }
210   else if (mediaType == MediaTypeTvShow)
211   {
212     std::string result;
213     if (field == FieldId) return "tvshow_view.idShow";
214     else if (field == FieldTitle)
215     {
216       // We need some extra logic to get the title value if sorttitle isn't set
217       if (queryPart == DatabaseQueryPartOrderBy)
218         result = StringUtils::Format("CASE WHEN length(tvshow_view.c%02d) > 0 THEN tvshow_view.c%02d ELSE tvshow_view.c%02d END", VIDEODB_ID_TV_SORTTITLE, VIDEODB_ID_TV_SORTTITLE, VIDEODB_ID_TV_TITLE);
219       else
220         result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_TITLE);
221     }
222     else if (field == FieldPlot) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_PLOT);
223     else if (field == FieldTvShowStatus) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_STATUS);
224     else if (field == FieldVotes) return "tvshow_view.votes";
225     else if (field == FieldRating) return "tvshow_view.rating";
226     else if (field == FieldYear) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_PREMIERED);
227     else if (field == FieldGenre) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_GENRE);
228     else if (field == FieldMPAA) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_MPAA);
229     else if (field == FieldStudio) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_STUDIOS);
230     else if (field == FieldSortTitle) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_SORTTITLE);
231     else if (field == FieldOriginalTitle) result = StringUtils::Format("tvshow_view.c%02d", VIDEODB_ID_TV_ORIGINALTITLE);
232     else if (field == FieldPath) return "tvshow_view.strPath";
233     else if (field == FieldDateAdded) return "tvshow_view.dateAdded";
234     else if (field == FieldLastPlayed) return "tvshow_view.lastPlayed";
235     else if (field == FieldSeason) return "tvshow_view.totalSeasons";
236     else if (field == FieldNumberOfEpisodes) return "tvshow_view.totalCount";
237     else if (field == FieldNumberOfWatchedEpisodes) return "tvshow_view.watchedcount";
238     else if (field == FieldUserRating) return "tvshow_view.userrating";
239 
240     if (!result.empty())
241       return result;
242   }
243   else if (mediaType == MediaTypeEpisode)
244   {
245     std::string result;
246     if (field == FieldId) return "episode_view.idEpisode";
247     else if (field == FieldTitle) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_TITLE);
248     else if (field == FieldPlot) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_PLOT);
249     else if (field == FieldVotes) return "episode_view.votes";
250     else if (field == FieldRating) return "episode_view.rating";
251     else if (field == FieldWriter) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_CREDITS);
252     else if (field == FieldAirDate) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_AIRED);
253     else if (field == FieldTime) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_RUNTIME);
254     else if (field == FieldDirector) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_DIRECTOR);
255     else if (field == FieldSeason) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_SEASON);
256     else if (field == FieldEpisodeNumber) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_EPISODE);
257     else if (field == FieldUniqueId) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_IDENT_ID);
258     else if (field == FieldEpisodeNumberSpecialSort) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_SORTEPISODE);
259     else if (field == FieldSeasonSpecialSort) result = StringUtils::Format("episode_view.c%02d", VIDEODB_ID_EPISODE_SORTSEASON);
260     else if (field == FieldFilename) return "episode_view.strFilename";
261     else if (field == FieldPath) return "episode_view.strPath";
262     else if (field == FieldPlaycount) return "episode_view.playCount";
263     else if (field == FieldLastPlayed) return "episode_view.lastPlayed";
264     else if (field == FieldDateAdded) return "episode_view.dateAdded";
265     else if (field == FieldTvShowTitle) return "episode_view.strTitle";
266     else if (field == FieldYear) return "episode_view.premiered";
267     else if (field == FieldMPAA) return "episode_view.mpaa";
268     else if (field == FieldStudio) return "episode_view.strStudio";
269     else if (field == FieldUserRating) return "episode_view.userrating";
270 
271     if (!result.empty())
272       return result;
273   }
274 
275   if (field == FieldRandom && queryPart == DatabaseQueryPartOrderBy)
276     return "RANDOM()";
277 
278   return "";
279 }
280 
GetField(Field field,const MediaType & mediaType)281 int DatabaseUtils::GetField(Field field, const MediaType &mediaType)
282 {
283   if (field == FieldNone || mediaType == MediaTypeNone)
284     return -1;
285 
286   return GetField(field, mediaType, false);
287 }
288 
GetFieldIndex(Field field,const MediaType & mediaType)289 int DatabaseUtils::GetFieldIndex(Field field, const MediaType &mediaType)
290 {
291   if (field == FieldNone || mediaType == MediaTypeNone)
292     return -1;
293 
294   return GetField(field, mediaType, true);
295 }
296 
GetSelectFields(const Fields & fields,const MediaType & mediaType,FieldList & selectFields)297 bool DatabaseUtils::GetSelectFields(const Fields &fields, const MediaType &mediaType, FieldList &selectFields)
298 {
299   if (mediaType == MediaTypeNone || fields.empty())
300     return false;
301 
302   Fields sortFields = fields;
303 
304   // add necessary fields to create the label
305   if (mediaType == MediaTypeSong || mediaType == MediaTypeVideo || mediaType == MediaTypeVideoCollection ||
306       mediaType == MediaTypeMusicVideo || mediaType == MediaTypeMovie || mediaType == MediaTypeTvShow || mediaType == MediaTypeEpisode)
307     sortFields.insert(FieldTitle);
308   if (mediaType == MediaTypeEpisode)
309   {
310     sortFields.insert(FieldSeason);
311     sortFields.insert(FieldEpisodeNumber);
312   }
313   else if (mediaType == MediaTypeAlbum)
314     sortFields.insert(FieldAlbum);
315   else if (mediaType == MediaTypeSong)
316     sortFields.insert(FieldTrackNumber);
317   else if (mediaType == MediaTypeArtist)
318     sortFields.insert(FieldArtist);
319 
320   selectFields.clear();
321   for (Fields::const_iterator it = sortFields.begin(); it != sortFields.end(); ++it)
322   {
323     // ignore FieldLabel because it needs special handling (see further up)
324     if (*it == FieldLabel)
325       continue;
326 
327     if (GetField(*it, mediaType, DatabaseQueryPartSelect).empty())
328     {
329       CLog::Log(LOGDEBUG, "DatabaseUtils::GetSortFieldList: unknown field %d", *it);
330       continue;
331     }
332     selectFields.push_back(*it);
333   }
334 
335   return !selectFields.empty();
336 }
337 
GetFieldValue(const dbiplus::field_value & fieldValue,CVariant & variantValue)338 bool DatabaseUtils::GetFieldValue(const dbiplus::field_value &fieldValue, CVariant &variantValue)
339 {
340   if (fieldValue.get_isNull())
341   {
342     variantValue = CVariant::ConstNullVariant;
343     return true;
344   }
345 
346   switch (fieldValue.get_fType())
347   {
348   case dbiplus::ft_String:
349   case dbiplus::ft_WideString:
350   case dbiplus::ft_Object:
351     variantValue = fieldValue.get_asString();
352     return true;
353   case dbiplus::ft_Char:
354   case dbiplus::ft_WChar:
355     variantValue = fieldValue.get_asChar();
356     return true;
357   case dbiplus::ft_Boolean:
358     variantValue = fieldValue.get_asBool();
359     return true;
360   case dbiplus::ft_Short:
361     variantValue = fieldValue.get_asShort();
362     return true;
363   case dbiplus::ft_UShort:
364     variantValue = fieldValue.get_asShort();
365     return true;
366   case dbiplus::ft_Int:
367     variantValue = fieldValue.get_asInt();
368     return true;
369   case dbiplus::ft_UInt:
370     variantValue = fieldValue.get_asUInt();
371     return true;
372   case dbiplus::ft_Float:
373     variantValue = fieldValue.get_asFloat();
374     return true;
375   case dbiplus::ft_Double:
376   case dbiplus::ft_LongDouble:
377     variantValue = fieldValue.get_asDouble();
378     return true;
379   case dbiplus::ft_Int64:
380     variantValue = fieldValue.get_asInt64();
381     return true;
382   }
383 
384   return false;
385 }
386 
GetDatabaseResults(const MediaType & mediaType,const FieldList & fields,const std::unique_ptr<dbiplus::Dataset> & dataset,DatabaseResults & results)387 bool DatabaseUtils::GetDatabaseResults(const MediaType &mediaType, const FieldList &fields, const std::unique_ptr<dbiplus::Dataset> &dataset, DatabaseResults &results)
388 {
389   if (dataset->num_rows() == 0)
390     return true;
391 
392   const dbiplus::result_set &resultSet = dataset->get_result_set();
393   unsigned int offset = results.size();
394 
395   if (fields.empty())
396   {
397     DatabaseResult result;
398     for (unsigned int index = 0; index < resultSet.records.size(); index++)
399     {
400       result[FieldRow] = index + offset;
401       results.push_back(result);
402     }
403 
404     return true;
405   }
406 
407   if (resultSet.record_header.size() < fields.size())
408     return false;
409 
410   std::vector<int> fieldIndexLookup;
411   fieldIndexLookup.reserve(fields.size());
412   for (FieldList::const_iterator it = fields.begin(); it != fields.end(); ++it)
413     fieldIndexLookup.push_back(GetFieldIndex(*it, mediaType));
414 
415   results.reserve(resultSet.records.size() + offset);
416   for (unsigned int index = 0; index < resultSet.records.size(); index++)
417   {
418     DatabaseResult result;
419     result[FieldRow] = index + offset;
420 
421     unsigned int lookupIndex = 0;
422     for (FieldList::const_iterator it = fields.begin(); it != fields.end(); ++it)
423     {
424       int fieldIndex = fieldIndexLookup[lookupIndex++];
425       if (fieldIndex < 0)
426         return false;
427 
428       std::pair<Field, CVariant> value;
429       value.first = *it;
430       if (!GetFieldValue(resultSet.records[index]->at(fieldIndex), value.second))
431         CLog::Log(LOGWARNING, "GetDatabaseResults: unable to retrieve value of field %s", resultSet.record_header[fieldIndex].name.c_str());
432 
433       if (value.first == FieldYear &&
434          (mediaType == MediaTypeTvShow || mediaType == MediaTypeEpisode))
435       {
436         CDateTime dateTime;
437         dateTime.SetFromDBDate(value.second.asString());
438         if (dateTime.IsValid())
439         {
440           value.second.clear();
441           value.second = dateTime.GetYear();
442         }
443       }
444 
445       result.insert(value);
446     }
447 
448     result[FieldMediaType] = mediaType;
449     if (mediaType == MediaTypeMovie || mediaType == MediaTypeVideoCollection ||
450         mediaType == MediaTypeTvShow || mediaType == MediaTypeMusicVideo)
451       result[FieldLabel] = result.at(FieldTitle).asString();
452     else if (mediaType == MediaTypeEpisode)
453     {
454       std::ostringstream label;
455       label << (int)(result.at(FieldSeason).asInteger() * 100 + result.at(FieldEpisodeNumber).asInteger());
456       label << ". ";
457       label << result.at(FieldTitle).asString();
458       result[FieldLabel] = label.str();
459     }
460     else if (mediaType == MediaTypeAlbum)
461       result[FieldLabel] = result.at(FieldAlbum).asString();
462     else if (mediaType == MediaTypeSong)
463     {
464       std::ostringstream label;
465       label << (int)result.at(FieldTrackNumber).asInteger();
466       label << ". ";
467       label << result.at(FieldTitle).asString();
468       result[FieldLabel] = label.str();
469     }
470     else if (mediaType == MediaTypeArtist)
471       result[FieldLabel] = result.at(FieldArtist).asString();
472 
473     results.push_back(result);
474   }
475 
476   return true;
477 }
478 
BuildLimitClause(int end,int start)479 std::string DatabaseUtils::BuildLimitClause(int end, int start /* = 0 */)
480 {
481   return " LIMIT " + BuildLimitClauseOnly(end, start);
482 }
483 
BuildLimitClauseOnly(int end,int start)484 std::string DatabaseUtils::BuildLimitClauseOnly(int end, int start /* = 0 */)
485 {
486   std::ostringstream sql;
487   if (start > 0)
488   {
489     if (end > 0)
490     {
491       end = end - start;
492       if (end < 0)
493         end = 0;
494     }
495 
496     sql << start << "," << end;
497   }
498   else
499     sql << end;
500 
501   return sql.str();
502 }
503 
GetLimitCount(int end,int start)504 size_t DatabaseUtils::GetLimitCount(int end, int start)
505 {
506   if (start > 0)
507   {
508     if (end - start < 0)
509       return 0;
510     else
511       return static_cast<size_t>(end - start);
512   }
513   else if (end > 0)
514     return static_cast<size_t>(end);
515   return 0;
516 }
517 
GetField(Field field,const MediaType & mediaType,bool asIndex)518 int DatabaseUtils::GetField(Field field, const MediaType &mediaType, bool asIndex)
519 {
520   if (field == FieldNone || mediaType == MediaTypeNone)
521     return -1;
522 
523   int index = -1;
524 
525   if (mediaType == MediaTypeAlbum)
526   {
527     if (field == FieldId) return CMusicDatabase::album_idAlbum;
528     else if (field == FieldAlbum) return CMusicDatabase::album_strAlbum;
529     else if (field == FieldArtist || field == FieldAlbumArtist) return CMusicDatabase::album_strArtists;
530     else if (field == FieldGenre) return CMusicDatabase::album_strGenres;
531     else if (field == FieldYear) return CMusicDatabase::album_strReleaseDate;
532     else if (field == FieldMoods) return CMusicDatabase::album_strMoods;
533     else if (field == FieldStyles) return CMusicDatabase::album_strStyles;
534     else if (field == FieldThemes) return CMusicDatabase::album_strThemes;
535     else if (field == FieldReview) return CMusicDatabase::album_strReview;
536     else if (field == FieldMusicLabel) return CMusicDatabase::album_strLabel;
537     else if (field == FieldAlbumType) return CMusicDatabase::album_strType;
538     else if (field == FieldRating) return CMusicDatabase::album_fRating;
539     else if (field == FieldVotes) return CMusicDatabase::album_iVotes;
540     else if (field == FieldUserRating) return CMusicDatabase::album_iUserrating;
541     else if (field == FieldPlaycount) return CMusicDatabase::album_iTimesPlayed;
542     else if (field == FieldLastPlayed) return CMusicDatabase::album_dtLastPlayed;
543     else if (field == FieldDateAdded) return CMusicDatabase::album_dateAdded;
544     else if (field == FieldDateNew) return CMusicDatabase::album_dateNew;
545     else if (field == FieldDateModified) return CMusicDatabase::album_dateModified;
546     else if (field == FieldTotalDiscs)
547       return CMusicDatabase::album_iTotalDiscs;
548     else if (field == FieldOrigYear || field == FieldOrigDate)
549       return CMusicDatabase::album_strOrigReleaseDate;
550     else if (field == FieldAlbumStatus)
551       return CMusicDatabase::album_strReleaseStatus;
552     else if (field == FieldAlbumDuration)
553       return CMusicDatabase::album_iAlbumDuration;
554   }
555   else if (mediaType == MediaTypeSong)
556   {
557     if (field == FieldId) return CMusicDatabase::song_idSong;
558     else if (field == FieldTitle) return CMusicDatabase::song_strTitle;
559     else if (field == FieldTrackNumber) return CMusicDatabase::song_iTrack;
560     else if (field == FieldTime) return CMusicDatabase::song_iDuration;
561     else if (field == FieldYear) return CMusicDatabase::song_strReleaseDate;
562     else if (field == FieldFilename) return CMusicDatabase::song_strFileName;
563     else if (field == FieldPlaycount) return CMusicDatabase::song_iTimesPlayed;
564     else if (field == FieldStartOffset) return CMusicDatabase::song_iStartOffset;
565     else if (field == FieldEndOffset) return CMusicDatabase::song_iEndOffset;
566     else if (field == FieldLastPlayed) return CMusicDatabase::song_lastplayed;
567     else if (field == FieldRating) return CMusicDatabase::song_rating;
568     else if (field == FieldUserRating) return CMusicDatabase::song_userrating;
569     else if (field == FieldVotes) return CMusicDatabase::song_votes;
570     else if (field == FieldComment) return CMusicDatabase::song_comment;
571     else if (field == FieldMoods) return CMusicDatabase::song_mood;
572     else if (field == FieldAlbum) return CMusicDatabase::song_strAlbum;
573     else if (field == FieldPath) return CMusicDatabase::song_strPath;
574     else if (field == FieldGenre) return CMusicDatabase::song_strGenres;
575     else if (field == FieldArtist || field == FieldAlbumArtist) return CMusicDatabase::song_strArtists;
576     else if (field == FieldDateAdded) return CMusicDatabase::song_dateAdded;
577     else if (field == FieldDateNew) return CMusicDatabase::song_dateNew;
578     else if (field == FieldDateModified) return CMusicDatabase::song_dateModified;
579     else if (field == FieldBPM)
580       return CMusicDatabase::song_iBPM;
581     else if (field == FieldMusicBitRate)
582         return CMusicDatabase::song_iBitRate;
583     else if (field == FieldSampleRate)
584         return CMusicDatabase::song_iSampleRate;
585     else if (field == FieldNoOfChannels)
586         return CMusicDatabase::song_iChannels;
587   }
588   else if (mediaType == MediaTypeArtist)
589   {
590     if (field == FieldId) return CMusicDatabase::artist_idArtist;
591     else if (field == FieldArtist) return CMusicDatabase::artist_strArtist;
592     else if (field == FieldArtistSort) return CMusicDatabase::artist_strSortName;
593     else if (field == FieldArtistType) return CMusicDatabase::artist_strType;
594     else if (field == FieldGender) return CMusicDatabase::artist_strGender;
595     else if (field == FieldDisambiguation) return CMusicDatabase::artist_strDisambiguation;
596     else if (field == FieldGenre) return CMusicDatabase::artist_strGenres;
597     else if (field == FieldMoods) return CMusicDatabase::artist_strMoods;
598     else if (field == FieldStyles) return CMusicDatabase::artist_strStyles;
599     else if (field == FieldInstruments) return CMusicDatabase::artist_strInstruments;
600     else if (field == FieldBiography) return CMusicDatabase::artist_strBiography;
601     else if (field == FieldBorn) return CMusicDatabase::artist_strBorn;
602     else if (field == FieldBandFormed) return CMusicDatabase::artist_strFormed;
603     else if (field == FieldDisbanded) return CMusicDatabase::artist_strDisbanded;
604     else if (field == FieldDied) return CMusicDatabase::artist_strDied;
605     else if (field == FieldDateAdded) return CMusicDatabase::artist_dateAdded;
606     else if (field == FieldDateNew) return CMusicDatabase::artist_dateNew;
607     else if (field == FieldDateModified) return CMusicDatabase::artist_dateModified;
608   }
609   else if (mediaType == MediaTypeMusicVideo)
610   {
611     if (field == FieldId) return 0;
612     else if (field == FieldTitle) index = VIDEODB_ID_MUSICVIDEO_TITLE;
613     else if (field == FieldTime) index =  VIDEODB_ID_MUSICVIDEO_RUNTIME;
614     else if (field == FieldDirector) index =  VIDEODB_ID_MUSICVIDEO_DIRECTOR;
615     else if (field == FieldStudio) index =  VIDEODB_ID_MUSICVIDEO_STUDIOS;
616     else if (field == FieldYear) return VIDEODB_DETAILS_MUSICVIDEO_PREMIERED;
617     else if (field == FieldPlot) index =  VIDEODB_ID_MUSICVIDEO_PLOT;
618     else if (field == FieldAlbum) index = VIDEODB_ID_MUSICVIDEO_ALBUM;
619     else if (field == FieldArtist) index =  VIDEODB_ID_MUSICVIDEO_ARTIST;
620     else if (field == FieldGenre) index =  VIDEODB_ID_MUSICVIDEO_GENRE;
621     else if (field == FieldTrackNumber) index =  VIDEODB_ID_MUSICVIDEO_TRACK;
622     else if (field == FieldFilename) return VIDEODB_DETAILS_MUSICVIDEO_FILE;
623     else if (field == FieldPath) return VIDEODB_DETAILS_MUSICVIDEO_PATH;
624     else if (field == FieldPlaycount) return VIDEODB_DETAILS_MUSICVIDEO_PLAYCOUNT;
625     else if (field == FieldLastPlayed) return VIDEODB_DETAILS_MUSICVIDEO_LASTPLAYED;
626     else if (field == FieldDateAdded) return VIDEODB_DETAILS_MUSICVIDEO_DATEADDED;
627     else if (field == FieldUserRating) return VIDEODB_DETAILS_MUSICVIDEO_USER_RATING;
628 
629     if (index < 0)
630       return index;
631 
632     if (asIndex)
633     {
634       // see VideoDatabase.h
635       // the first field is the item's ID and the second is the item's file ID
636       index += 2;
637     }
638   }
639   else if (mediaType == MediaTypeMovie)
640   {
641     if (field == FieldId) return 0;
642     else if (field == FieldTitle) index = VIDEODB_ID_TITLE;
643     else if (field == FieldSortTitle) index = VIDEODB_ID_SORTTITLE;
644     else if (field == FieldOriginalTitle) index = VIDEODB_ID_ORIGINALTITLE;
645     else if (field == FieldPlot) index = VIDEODB_ID_PLOT;
646     else if (field == FieldPlotOutline) index = VIDEODB_ID_PLOTOUTLINE;
647     else if (field == FieldTagline) index = VIDEODB_ID_TAGLINE;
648     else if (field == FieldVotes) return VIDEODB_DETAILS_MOVIE_VOTES;
649     else if (field == FieldRating) return VIDEODB_DETAILS_MOVIE_RATING;
650     else if (field == FieldWriter) index = VIDEODB_ID_CREDITS;
651     else if (field == FieldYear) return VIDEODB_DETAILS_MOVIE_PREMIERED;
652     else if (field == FieldTime) index = VIDEODB_ID_RUNTIME;
653     else if (field == FieldMPAA) index = VIDEODB_ID_MPAA;
654     else if (field == FieldTop250) index = VIDEODB_ID_TOP250;
655     else if (field == FieldSet) return VIDEODB_DETAILS_MOVIE_SET_NAME;
656     else if (field == FieldGenre) index = VIDEODB_ID_GENRE;
657     else if (field == FieldDirector) index = VIDEODB_ID_DIRECTOR;
658     else if (field == FieldStudio) index = VIDEODB_ID_STUDIOS;
659     else if (field == FieldTrailer) index = VIDEODB_ID_TRAILER;
660     else if (field == FieldCountry) index = VIDEODB_ID_COUNTRY;
661     else if (field == FieldFilename) index = VIDEODB_DETAILS_MOVIE_FILE;
662     else if (field == FieldPath) return VIDEODB_DETAILS_MOVIE_PATH;
663     else if (field == FieldPlaycount) return VIDEODB_DETAILS_MOVIE_PLAYCOUNT;
664     else if (field == FieldLastPlayed) return VIDEODB_DETAILS_MOVIE_LASTPLAYED;
665     else if (field == FieldDateAdded) return VIDEODB_DETAILS_MOVIE_DATEADDED;
666     else if (field == FieldUserRating) return VIDEODB_DETAILS_MOVIE_USER_RATING;
667 
668     if (index < 0)
669       return index;
670 
671     if (asIndex)
672     {
673       // see VideoDatabase.h
674       // the first field is the item's ID and the second is the item's file ID
675       index += 2;
676     }
677   }
678   else if (mediaType == MediaTypeTvShow)
679   {
680     if (field == FieldId) return 0;
681     else if (field == FieldTitle) index = VIDEODB_ID_TV_TITLE;
682     else if (field == FieldSortTitle) index = VIDEODB_ID_TV_SORTTITLE;
683     else if (field == FieldOriginalTitle) index = VIDEODB_ID_TV_ORIGINALTITLE;
684     else if (field == FieldPlot) index = VIDEODB_ID_TV_PLOT;
685     else if (field == FieldTvShowStatus) index = VIDEODB_ID_TV_STATUS;
686     else if (field == FieldVotes) return VIDEODB_DETAILS_TVSHOW_VOTES;
687     else if (field == FieldRating) return VIDEODB_DETAILS_TVSHOW_RATING;
688     else if (field == FieldYear) index = VIDEODB_ID_TV_PREMIERED;
689     else if (field == FieldGenre) index = VIDEODB_ID_TV_GENRE;
690     else if (field == FieldMPAA) index = VIDEODB_ID_TV_MPAA;
691     else if (field == FieldStudio) index = VIDEODB_ID_TV_STUDIOS;
692     else if (field == FieldPath) return VIDEODB_DETAILS_TVSHOW_PATH;
693     else if (field == FieldDateAdded) return VIDEODB_DETAILS_TVSHOW_DATEADDED;
694     else if (field == FieldLastPlayed) return VIDEODB_DETAILS_TVSHOW_LASTPLAYED;
695     else if (field == FieldNumberOfEpisodes) return VIDEODB_DETAILS_TVSHOW_NUM_EPISODES;
696     else if (field == FieldNumberOfWatchedEpisodes) return VIDEODB_DETAILS_TVSHOW_NUM_WATCHED;
697     else if (field == FieldSeason) return VIDEODB_DETAILS_TVSHOW_NUM_SEASONS;
698     else if (field == FieldUserRating) return VIDEODB_DETAILS_TVSHOW_USER_RATING;
699 
700     if (index < 0)
701       return index;
702 
703     if (asIndex)
704     {
705       // see VideoDatabase.h
706       // the first field is the item's ID
707       index += 1;
708     }
709   }
710   else if (mediaType == MediaTypeEpisode)
711   {
712     if (field == FieldId) return 0;
713     else if (field == FieldTitle) index = VIDEODB_ID_EPISODE_TITLE;
714     else if (field == FieldPlot) index = VIDEODB_ID_EPISODE_PLOT;
715     else if (field == FieldVotes) return VIDEODB_DETAILS_EPISODE_VOTES;
716     else if (field == FieldRating) return VIDEODB_DETAILS_EPISODE_RATING;
717     else if (field == FieldWriter) index = VIDEODB_ID_EPISODE_CREDITS;
718     else if (field == FieldAirDate) index = VIDEODB_ID_EPISODE_AIRED;
719     else if (field == FieldTime) index = VIDEODB_ID_EPISODE_RUNTIME;
720     else if (field == FieldDirector) index = VIDEODB_ID_EPISODE_DIRECTOR;
721     else if (field == FieldSeason) index = VIDEODB_ID_EPISODE_SEASON;
722     else if (field == FieldEpisodeNumber) index = VIDEODB_ID_EPISODE_EPISODE;
723     else if (field == FieldUniqueId) index = VIDEODB_ID_EPISODE_IDENT_ID;
724     else if (field == FieldEpisodeNumberSpecialSort) index = VIDEODB_ID_EPISODE_SORTEPISODE;
725     else if (field == FieldSeasonSpecialSort) index = VIDEODB_ID_EPISODE_SORTSEASON;
726     else if (field == FieldFilename) return VIDEODB_DETAILS_EPISODE_FILE;
727     else if (field == FieldPath) return VIDEODB_DETAILS_EPISODE_PATH;
728     else if (field == FieldPlaycount) return VIDEODB_DETAILS_EPISODE_PLAYCOUNT;
729     else if (field == FieldLastPlayed) return VIDEODB_DETAILS_EPISODE_LASTPLAYED;
730     else if (field == FieldDateAdded) return VIDEODB_DETAILS_EPISODE_DATEADDED;
731     else if (field == FieldTvShowTitle) return VIDEODB_DETAILS_EPISODE_TVSHOW_NAME;
732     else if (field == FieldStudio) return VIDEODB_DETAILS_EPISODE_TVSHOW_STUDIO;
733     else if (field == FieldYear) return VIDEODB_DETAILS_EPISODE_TVSHOW_AIRED;
734     else if (field == FieldMPAA) return VIDEODB_DETAILS_EPISODE_TVSHOW_MPAA;
735     else if (field == FieldUserRating) return VIDEODB_DETAILS_EPISODE_USER_RATING;
736 
737     if (index < 0)
738       return index;
739 
740     if (asIndex)
741     {
742       // see VideoDatabase.h
743       // the first field is the item's ID and the second is the item's file ID
744       index += 2;
745     }
746   }
747 
748   return index;
749 }
750