1 /* DatabaseAlbums.cpp */
2
3 /* Copyright (C) 2011-2020 Michael Lugmair (Lucio Carreras)
4 *
5 * This file is part of sayonara player
6 *
7 * This program is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <http://www.gnu.org/licenses/>.
19 */
20
21 #include "Albums.h"
22 #include "Module.h"
23
24 #include "Utils/MetaData/Album.h"
25 #include "Utils/Library/Filter.h"
26 #include "Utils/Utils.h"
27 #include "Utils/Algorithm.h"
28
29 using DB::Albums;
30 using DB::Query;
31 using ::Library::Filter;
32
33 static QString getFilterClause(const Filter& filter, QString cisPlaceholder);
34
dropAlbumView(DB::Module * module,const QString & albumView)35 static void dropAlbumView(DB::Module* module, const QString& albumView)
36 {
37 module->runQuery
38 (
39 "DROP VIEW IF EXISTS " + albumView + "; ",
40 "Cannot drop album view"
41 );
42 }
43
createAlbumView(DB::Module * module,const QString & trackView,const QString & albumView)44 static void createAlbumView(DB::Module* module, const QString& trackView, const QString& albumView)
45 {
46 const QStringList fields
47 {
48 "albums.albumID AS albumID", // 0
49 "albums.name AS albumName", // 1
50 "albums.rating AS albumRating", // 2
51 "GROUP_CONCAT(DISTINCT artists.name) AS artistNames", // 3
52 "GROUP_CONCAT(DISTINCT albumArtists.name) AS albumArtistName", // 4
53 "SUM(%1.length) / 1000 AS albumLength", // 5
54 "COUNT(DISTINCT %1.trackID) AS trackCount", // 6
55 "MAX(%1.year) AS albumYear", // 7
56 "GROUP_CONCAT(DISTINCT %1.discnumber) AS discnumbers", // 8
57 "GROUP_CONCAT(%1.filename, '#') AS filenames" // 9
58 };
59
60 const QString query = QString
61 (
62 "CREATE VIEW " + albumView + " AS "
63 "SELECT " + fields.join(", ") + " FROM albums "
64 "LEFT OUTER JOIN %1 ON %1.albumID = albums.albumID " // leave out empty albums
65 "LEFT OUTER JOIN artists ON %1.artistID = artists.artistID "
66 "LEFT OUTER JOIN artists albumArtists ON %1.albumArtistID = albumArtists.artistID "
67 "GROUP BY albums.albumID; "
68 ).arg(trackView);
69
70 module->runQuery(query, "Cannot create album view");
71 }
72
73 Albums::Albums() = default;
74 Albums::~Albums() = default;
75
albumViewName(LibraryId libraryId)76 static QString albumViewName(LibraryId libraryId)
77 {
78 if(libraryId < 0){
79 return "album_view";
80 }
81
82 return QString("album_view_%1").arg(QString::number(libraryId));
83 }
84
initViews()85 void Albums::initViews()
86 {
87 const QString& viewName = albumViewName(libraryId());
88
89 dropAlbumView(module(), viewName);
90 createAlbumView(module(), trackView(), viewName);
91 }
92
fetchQueryAlbums(bool alsoEmpty) const93 QString Albums::fetchQueryAlbums(bool alsoEmpty) const
94 {
95 const QStringList fields
96 {
97 "albumID", // 0
98 "albumName", // 1
99 "albumRating", // 2
100 "artistNames", // 3
101 "albumArtistName", // 4
102 "albumLength", // 5
103 "trackCount", // 6
104 "albumYear", // 7
105 "discnumbers", // 8
106 "filenames" // 9
107 };
108
109 QString query = "SELECT " + fields.join(", ") + " FROM " + albumViewName(libraryId());
110
111 if(alsoEmpty) {
112 query += " WHERE 1 ";
113 }
114 else {
115 query += " WHERE trackCount > 0 ";
116 }
117
118 return query;
119 }
120
dbFetchAlbums(Query & q,AlbumList & result) const121 bool Albums::dbFetchAlbums(Query& q, AlbumList& result) const
122 {
123 result.clear();
124
125 if (!q.exec()) {
126 q.showError("Could not get all albums from database");
127 return false;
128 }
129
130 while(q.next())
131 {
132 Album album;
133
134 album.setId(q.value(0).value<AlbumId>());
135 album.setName(q.value(1).toString());
136 album.setRating(q.value(2).value<Rating>());
137 album.setArtists(q.value(3).toString().split(','));
138 album.setAlbumArtist(q.value(4).toString());
139 album.setDurationSec(q.value(5).value<Seconds>());
140 album.setSongcount(q.value(6).value<TrackNum>());
141 album.setYear(q.value(7).value<Year>());
142
143 QStringList discs = q.value(8).toString().split(',');
144 auto discnumbers = album.discnumbers();
145 if(discs.isEmpty()){
146 discnumbers << 1;
147 }
148
149 discs.removeDuplicates();
150 for(const QString& disc : discs) {
151 discnumbers << Disc(disc.toInt());
152 }
153
154 album.setDiscnumbers(discnumbers);
155 album.setDatabaseId(module()->databaseId());
156 album.setPathHint(q.value(9).toString().split("#"));
157
158 result.push_back(std::move(album));
159 }
160
161 return true;
162 }
163
getAlbumID(const QString & album) const164 AlbumId Albums::getAlbumID(const QString& album) const
165 {
166 Query q(module());
167 int albumID = -1;
168
169 q.prepare("SELECT albumID FROM albums WHERE name = ?;");
170 q.addBindValue(Util::convertNotNull(album));
171
172 if(!q.exec()) {
173 q.showError("Cannot fetch albumID");
174 return -1;
175 }
176
177 if (q.next()) {
178 albumID = q.value(0).toInt();
179 }
180
181 return albumID;
182 }
183
getAlbumByID(AlbumId id,Album & album) const184 bool Albums::getAlbumByID(AlbumId id, Album& album) const
185 {
186 return getAlbumByID(id, album, false);
187 }
188
getAlbumByID(AlbumId id,Album & album,bool alsoEmpty) const189 bool Albums::getAlbumByID(AlbumId id, Album& album, bool alsoEmpty) const
190 {
191 if(id == -1) {
192 return false;
193 }
194
195 const QString query =
196 fetchQueryAlbums(alsoEmpty) +
197 " AND albumID = :id " +
198 " GROUP BY albumID, albumName, albumRating;";
199
200 Query q(module());
201 q.prepare(query);
202 q.bindValue(":id", id);
203
204 AlbumList albums;
205 dbFetchAlbums(q, albums);
206
207 if(!albums.empty()) {
208 album = albums.first();
209 }
210
211 return (!albums.empty());
212 }
213
getAllAlbums(AlbumList & result,bool alsoEmpty) const214 bool Albums::getAllAlbums(AlbumList& result, bool alsoEmpty) const
215 {
216 const QString query =
217 fetchQueryAlbums(alsoEmpty) +
218 " GROUP BY albumID, albumName, albumRating;";
219
220 Query q(module());
221 q.prepare(query);
222
223 return dbFetchAlbums(q, result);
224 }
225
226
getAllAlbumsByArtist(const IdList & artists,AlbumList & result,const Library::Filter & filter) const227 bool Albums::getAllAlbumsByArtist(const IdList& artists, AlbumList& result, const Library::Filter& filter) const
228 {
229 if(artists.isEmpty()) {
230 return false;
231 }
232
233 const QStringList fields
234 {
235 "albumID",
236 "albumName",
237 "albumRating",
238 "GROUP_CONCAT(DISTINCT artistName)",
239 "GROUP_CONCAT(DISTINCT albumArtistName)",
240 "SUM(length) / 1000 AS albumLength",
241 "COUNT(DISTINCT trackID) AS trackCount",
242 "MAX(year) AS albumYear",
243 "GROUP_CONCAT(DISTINCT discnumber)",
244 "GROUP_CONCAT(DISTINCT filename)"
245 };
246
247 const QString joinedFields = fields.join(", ");
248 const QString searchView = trackSearchView();
249
250 const QStringList searchFilters = filter.searchModeFiltertext(true);
251 for(const QString& searchFilter : searchFilters)
252 {
253 QString query = "SELECT " + joinedFields + " FROM " + searchView + " WHERE ";
254
255 if( !filter.cleared() )
256 {
257 query += getFilterClause(filter, ":cissearch") + " AND ";
258 }
259
260 { // artist conditions
261 const QString aidf = searchView + "." + artistIdField();
262
263 QStringList orClauses;
264 Util::Algorithm::transform(artists, orClauses, [aidf](ArtistId artistId)
265 {
266 return QString("%1 = :artistId%2").arg(aidf).arg(artistId);
267 });
268
269 query += " (" + orClauses.join(" OR ") + ") ";
270 }
271
272 query += " GROUP BY albumID, albumName; ";
273
274 { // prepare and run
275 Query q(module());
276 q.prepare(query);
277 q.bindValue(":cissearch", searchFilter);
278
279 for(ArtistId artistId : artists)
280 {
281 q.bindValue(QString(":artistId%1").arg(artistId), artistId);
282 }
283
284 AlbumList tmpAlbums;
285 dbFetchAlbums(q, tmpAlbums);
286 result.appendUnique(tmpAlbums);
287 }
288 }
289
290 return true;
291 }
292
293
getAllAlbumsBySearchString(const Library::Filter & filter,AlbumList & result) const294 bool Albums::getAllAlbumsBySearchString(const Library::Filter& filter, AlbumList& result) const
295 {
296 const QStringList fields
297 {
298 "albumID",
299 "albumName",
300 "albumRating",
301 "GROUP_CONCAT(DISTINCT artistName)",
302 "GROUP_CONCAT(DISTINCT albumArtistName)",
303 "SUM(length) / 1000 AS albumLength",
304 "COUNT(DISTINCT trackID) AS trackCount",
305 "MAX(year) AS albumYear",
306 "GROUP_CONCAT(DISTINCT discnumber)",
307 "GROUP_CONCAT(DISTINCT filename)"
308 };
309
310 const QString joinedFields = fields.join(", ");
311
312 const QStringList searchFilters = filter.searchModeFiltertext(true);
313 for(const QString& searchFilter : searchFilters)
314 {
315 QString query = "SELECT " + joinedFields + " FROM " + trackSearchView() + " WHERE ";
316 query += getFilterClause(filter, ":cissearch");
317 query += " GROUP BY albumID, albumName;";
318
319 Query q(module());
320 q.prepare(query);
321 q.bindValue(":cissearch", searchFilter);
322
323 AlbumList tmpList;
324 dbFetchAlbums(q, tmpList);
325 result.appendUnique(tmpList);
326 }
327
328 return true;
329 }
330
updateAlbumRating(AlbumId id,Rating rating)331 AlbumId Albums::updateAlbumRating(AlbumId id, Rating rating)
332 {
333 QMap<QString, QVariant> bindings
334 {
335 {"rating", QVariant::fromValue(int(rating))}
336 };
337
338 Query q = module()->update("albums", bindings, {"albumID", id}, QString("Cannot set album rating for id %1").arg(id));
339 if (q.hasError()) {
340 return -1;
341 }
342
343 return id;
344 }
345
updateAlbumCissearch()346 void Albums::updateAlbumCissearch()
347 {
348 AlbumList albums;
349 getAllAlbums(albums, true);
350
351 module()->db().transaction();
352
353 for(const Album& album : albums)
354 {
355 const QString cissearch = Library::Utils::convertSearchstring(album.name());
356
357 module()->update
358 (
359 "albums",
360 {{"cissearch", cissearch}},
361 {"albumID", album.id()},
362 "Cannot update album cissearch"
363 );
364 }
365
366 module()->db().commit();
367 }
368
insertAlbumIntoDatabase(const QString & name)369 AlbumId Albums::insertAlbumIntoDatabase(const QString& name)
370 {
371 const QString cissearch = Library::Utils::convertSearchstring(name);
372
373 QMap<QString, QVariant> bindings
374 {
375 {"name", Util::convertNotNull(name)},
376 {"cissearch", cissearch},
377 {"rating", QVariant::fromValue(int(Rating::Zero))}
378 };
379
380 Query q = module()->insert("albums", bindings, QString("2. Cannot insert album %1").arg(name));
381 if (q.hasError()) {
382 return -1;
383 }
384
385 return q.lastInsertId().toInt();
386 }
387
insertAlbumIntoDatabase(const Album & album)388 AlbumId Albums::insertAlbumIntoDatabase(const Album& album)
389 {
390 return insertAlbumIntoDatabase(album.name());
391 }
392
deleteAllAlbums()393 void Albums::deleteAllAlbums()
394 {
395 module()->runQuery("DELETE FROM albums;", "Could not delete all albums");
396 }
397
getFilterClause(const Filter & filter,QString placeholder)398 static QString getFilterClause(const Filter& filter, QString placeholder)
399 {
400 placeholder.remove(":");
401
402 switch(filter.mode())
403 {
404 case Library::Filter::Genre:
405 if(filter.isInvalidGenre()){
406 return " genre = '' ";
407 }
408 else {
409 return " genreCissearch LIKE :" + placeholder + " ";
410 }
411
412 case Library::Filter::Filename:
413 return " fileCissearch LIKE :" + placeholder + " ";
414
415 case Library::Filter::Fulltext:
416 default:
417 return " allCissearch LIKE :" + placeholder + " ";
418 }
419 }
420