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