1 /*
2 * Copyright (C) 2015 Emeric Poupon
3 *
4 * This file is part of LMS.
5 *
6 * LMS is free software: you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation, either version 3 of the License, or
9 * (at your option) any later version.
10 *
11 * LMS is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with LMS. If not, see <http://www.gnu.org/licenses/>.
18 */
19
20 #include "database/Release.hpp"
21
22 #include <Wt/Dbo/WtSqlTraits.h>
23
24 #include "database/Artist.hpp"
25 #include "database/Cluster.hpp"
26 #include "database/Session.hpp"
27 #include "database/Track.hpp"
28 #include "database/User.hpp"
29 #include "utils/Logger.hpp"
30 #include "SqlQuery.hpp"
31
32 namespace Database
33 {
34
35 template <typename T>
36 static
37 Wt::Dbo::Query<T>
createQuery(Session & session,const std::string & queryStr,const std::set<IdType> & clusterIds,const std::vector<std::string> & keywords)38 createQuery(Session& session,
39 const std::string& queryStr,
40 const std::set<IdType>& clusterIds,
41 const std::vector<std::string>& keywords)
42 {
43
44 auto query {session.getDboSession().query<T>(queryStr)};
45 query.join("track t ON t.release_id = r.id");
46
47 for (const std::string& keyword : keywords)
48 query.where("r.name LIKE ?").bind("%%" + keyword + "%%");
49
50 if (!clusterIds.empty())
51 {
52 std::ostringstream oss;
53 oss << "r.id IN (SELECT DISTINCT r.id FROM release r"
54 " INNER JOIN track t ON t.release_id = r.id"
55 " INNER JOIN cluster c ON c.id = t_c.cluster_id"
56 " INNER JOIN track_cluster t_c ON t_c.track_id = t.id";
57
58 WhereClause clusterClause;
59 for (const IdType clusterId : clusterIds)
60 {
61 clusterClause.Or(WhereClause("c.id = ?"));
62 query.bind(clusterId);
63 }
64
65 oss << " " << clusterClause.get();
66 oss << " GROUP BY t.id HAVING COUNT(*) = " << clusterIds.size() << ")";
67
68 query.where(oss.str());
69 }
70
71 return query;
72 }
73
Release(const std::string & name,const std::optional<UUID> & MBID)74 Release::Release(const std::string& name, const std::optional<UUID>& MBID)
75 : _name {std::string(name, 0 , _maxNameLength)},
76 _MBID {MBID ? MBID->getAsString() : ""}
77 {
78
79 }
80
81 std::vector<Release::pointer>
getByName(Session & session,const std::string & name)82 Release::getByName(Session& session, const std::string& name)
83 {
84 session.checkUniqueLocked();
85
86 Wt::Dbo::collection<Release::pointer> res = session.getDboSession().find<Release>().where("name = ?").bind( std::string(name, 0, _maxNameLength) );
87 return std::vector<Release::pointer>(res.begin(), res.end());
88 }
89
90 Release::pointer
getByMBID(Session & session,const UUID & mbid)91 Release::getByMBID(Session& session, const UUID& mbid)
92 {
93 session.checkSharedLocked();
94
95 return session.getDboSession().find<Release>().where("mbid = ?").bind(std::string {mbid.getAsString()});
96 }
97
98 Release::pointer
getById(Session & session,IdType id)99 Release::getById(Session& session, IdType id)
100 {
101 session.checkSharedLocked();
102
103 return session.getDboSession().find<Release>().where("id = ?").bind(id);
104 }
105
106 Release::pointer
create(Session & session,const std::string & name,const std::optional<UUID> & MBID)107 Release::create(Session& session, const std::string& name, const std::optional<UUID>& MBID)
108 {
109 session.checkSharedLocked();
110
111 Release::pointer res {session.getDboSession().add(std::make_unique<Release>(name, MBID))};
112 session.getDboSession().flush();
113
114 return res;
115 }
116
117 std::size_t
getCount(Session & session)118 Release::getCount(Session& session)
119 {
120 session.checkSharedLocked();
121
122 Wt::Dbo::collection<pointer> releases {session.getDboSession().find<Release>()};
123 return releases.size();
124 }
125
126 std::vector<Release::pointer>
getAll(Session & session,std::optional<Range> range)127 Release::getAll(Session& session, std::optional<Range> range)
128 {
129 session.checkSharedLocked();
130
131 Wt::Dbo::collection<pointer> res = session.getDboSession().find<Release>()
132 .offset(range ? static_cast<int>(range->offset) : -1)
133 .limit(range ? static_cast<int>(range->limit) : -1)
134 .orderBy("name COLLATE NOCASE");
135
136 return std::vector<pointer>(res.begin(), res.end());
137 }
138
139 std::vector<IdType>
getAllIds(Session & session)140 Release::getAllIds(Session& session)
141 {
142 session.checkSharedLocked();
143
144 Wt::Dbo::collection<IdType> res = session.getDboSession().query<IdType>("SELECT id FROM release");
145 return std::vector<IdType>(res.begin(), res.end());
146 }
147
148 std::vector<Release::pointer>
getAllOrderedByArtist(Session & session,std::optional<std::size_t> offset,std::optional<std::size_t> size)149 Release::getAllOrderedByArtist(Session& session, std::optional<std::size_t> offset, std::optional<std::size_t> size)
150 {
151 session.checkSharedLocked();
152
153 Wt::Dbo::collection<pointer> res = session.getDboSession().query<Wt::Dbo::ptr<Release>>(
154 "SELECT DISTINCT r FROM release r"
155 " INNER JOIN track t ON r.id = t.release_id"
156 " INNER JOIN track_artist_link t_a_l ON t_a_l.track_id = t.id"
157 " INNER JOIN artist a ON t_a_l.artist_id = a.id")
158 .offset(offset ? static_cast<int>(*offset) : -1)
159 .limit(size ? static_cast<int>(*size) : -1)
160 .orderBy("a.name COLLATE NOCASE, r.name COLLATE NOCASE");
161
162 return std::vector<pointer>(res.begin(), res.end());
163 }
164
165 std::vector<Release::pointer>
getAllRandom(Session & session,const std::set<IdType> & clusterIds,std::optional<std::size_t> size)166 Release::getAllRandom(Session& session, const std::set<IdType>& clusterIds, std::optional<std::size_t> size)
167 {
168 session.checkSharedLocked();
169
170 auto query {createQuery<Release::pointer>(session, "SELECT DISTINCT r from release r", clusterIds,{})};
171
172 Wt::Dbo::collection<pointer> res = query
173 .orderBy("RANDOM()")
174 .limit(size ? static_cast<int>(*size) : -1);
175
176 return std::vector<pointer>(res.begin(), res.end());
177 }
178
179 std::vector<IdType>
getAllIdsRandom(Session & session,const std::set<IdType> & clusterIds,std::optional<std::size_t> size)180 Release::getAllIdsRandom(Session& session, const std::set<IdType>& clusterIds, std::optional<std::size_t> size)
181 {
182 session.checkSharedLocked();
183
184 auto query {createQuery<IdType>(session, "SELECT DISTINCT r.id from release r", clusterIds,{})};
185
186 Wt::Dbo::collection<IdType> res = query
187 .orderBy("RANDOM()")
188 .limit(size ? static_cast<int>(*size) : -1);
189
190 return std::vector<IdType>(res.begin(), res.end());
191 }
192
193
194 std::vector<Release::pointer>
getAllOrphans(Session & session)195 Release::getAllOrphans(Session& session)
196 {
197 session.checkSharedLocked();
198
199 Wt::Dbo::collection<Release::pointer> res = session.getDboSession().query<Wt::Dbo::ptr<Release>>("select r from release r LEFT OUTER JOIN Track t ON r.id = t.release_id WHERE t.id IS NULL");
200
201 return std::vector<pointer>(res.begin(), res.end());
202 }
203
204 std::vector<Release::pointer>
getLastWritten(Session & session,std::optional<Wt::WDateTime> after,const std::set<IdType> & clusterIds,std::optional<Range> range,bool & moreResults)205 Release::getLastWritten(Session& session,
206 std::optional<Wt::WDateTime> after,
207 const std::set<IdType>& clusterIds,
208 std::optional<Range> range,
209 bool& moreResults)
210 {
211 session.checkSharedLocked();
212
213 auto query {createQuery<Release::pointer>(session, "SELECT r from release r", clusterIds, {})};
214 if (after)
215 query.where("t.file_last_write > ?").bind(after);
216
217 Wt::Dbo::collection<Release::pointer> collection = query
218 .orderBy("t.file_last_write DESC")
219 .groupBy("r.id")
220 .offset(range ? static_cast<int>(range->offset) : -1)
221 .limit(range ? static_cast<int>(range->limit) + 1: -1);
222
223 auto res {std::vector<pointer>(collection.begin(), collection.end())};
224 if (range && res.size() == static_cast<std::size_t>(range->limit) + 1)
225 {
226 moreResults = true;
227 res.pop_back();
228 }
229 else
230 moreResults = false;
231
232 return res;
233 }
234
235 std::vector<Release::pointer>
getByYear(Session & session,int yearFrom,int yearTo,std::optional<Range> range)236 Release::getByYear(Session& session, int yearFrom, int yearTo, std::optional<Range> range)
237 {
238 Wt::Dbo::collection<Release::pointer> res = session.getDboSession().query<Release::pointer>
239 ("SELECT DISTINCT r from release r INNER JOIN track t ON r.id = t.release_id")
240 .where("t.year >= ?").bind(yearFrom)
241 .where("t.year <= ?").bind(yearTo)
242 .orderBy("t.year, r.name COLLATE NOCASE")
243 .offset(range ? static_cast<int>(range->offset) : -1)
244 .limit(range ? static_cast<int>(range->limit) : -1);
245
246 return std::vector<pointer>(res.begin(), res.end());
247 }
248
249 std::vector<Release::pointer>
getStarred(Session & session,User::pointer user,const std::set<IdType> & clusterIds,std::optional<Range> range,bool & moreResults)250 Release::getStarred(Session& session,
251 User::pointer user,
252 const std::set<IdType>& clusterIds,
253 std::optional<Range> range,
254 bool& moreResults)
255 {
256 session.checkSharedLocked();
257
258 auto query {createQuery<Release::pointer>(session, "SELECT r from release r", clusterIds, {})};
259 {
260 std::ostringstream oss;
261 oss << "r.id IN (SELECT DISTINCT r.id FROM release r"
262 " INNER JOIN user_release_starred urs ON urs.release_id = r.id"
263 " INNER JOIN user u ON u.id = urs.user_id WHERE u.id = ?)";
264
265 query.bind(user.id());
266 query.where(oss.str());
267 }
268
269 Wt::Dbo::collection<Release::pointer> collection = query
270 .groupBy("r.id")
271 .orderBy("r.name COLLATE NOCASE")
272 .offset(range ? static_cast<int>(range->offset) : -1)
273 .limit(range ? static_cast<int>(range->limit) + 1: -1);
274
275 auto res {std::vector<pointer>(collection.begin(), collection.end())};
276 if (range && res.size() == static_cast<std::size_t>(range->limit) + 1)
277 {
278 moreResults = true;
279 res.pop_back();
280 }
281 else
282 moreResults = false;
283
284 return res;
285
286
287 }
288
289 std::vector<Release::pointer>
getByClusters(Session & session,const std::set<IdType> & clusters)290 Release::getByClusters(Session& session, const std::set<IdType>& clusters)
291 {
292 assert(!clusters.empty());
293
294 session.checkSharedLocked();
295
296 bool moreResults;
297 return getByFilter(session, clusters, {}, std::nullopt, moreResults);
298 }
299
300 std::vector<Release::pointer>
getByFilter(Session & session,const std::set<IdType> & clusterIds,const std::vector<std::string> & keywords,std::optional<Range> range,bool & moreResults)301 Release::getByFilter(Session& session,
302 const std::set<IdType>& clusterIds,
303 const std::vector<std::string>& keywords,
304 std::optional<Range> range,
305 bool& moreResults)
306 {
307 session.checkSharedLocked();
308
309 Wt::Dbo::collection<pointer> collection = createQuery<Release::pointer>(session, "SELECT r from release r", clusterIds, keywords)
310 .groupBy("r.id")
311 .orderBy("r.name COLLATE NOCASE")
312 .limit(range ? static_cast<int>(range->limit) + 1 : -1)
313 .offset(range ? static_cast<int>(range->offset) : -1);
314
315 auto res {std::vector<pointer>(collection.begin(), collection.end())};
316
317 if (range && res.size() == static_cast<std::size_t>(range->limit) + 1)
318 {
319 moreResults = true;
320 res.pop_back();
321 }
322 else
323 moreResults = false;
324
325 return res;
326 }
327
328 std::vector<IdType>
getAllIdsWithClusters(Session & session,std::optional<std::size_t> limit)329 Release::getAllIdsWithClusters(Session& session, std::optional<std::size_t> limit)
330 {
331 session.checkSharedLocked();
332
333 Wt::Dbo::collection<IdType> res = session.getDboSession().query<IdType>
334 ("SELECT DISTINCT r.id FROM release r"
335 " INNER JOIN track t ON t.release_id = r.id"
336 " INNER JOIN track_cluster t_c ON t_c.track_id = t.id")
337 .limit(limit ? static_cast<int>(*limit) : -1);
338
339 return std::vector<IdType>(res.begin(), res.end());
340 }
341
342
343 std::optional<std::size_t>
getTotalTrack(void) const344 Release::getTotalTrack(void) const
345 {
346 assert(session());
347 assert(IdIsValid(self()->id()));
348
349 int res = session()->query<int>("SELECT COALESCE(MAX(total_track),0) FROM track t INNER JOIN release r ON r.id = t.release_id")
350 .where("r.id = ?")
351 .bind(this->id());
352
353 return (res > 0) ? std::make_optional<std::size_t>(res) : std::nullopt;
354 }
355
356 std::optional<std::size_t>
getTotalDisc(void) const357 Release::getTotalDisc(void) const
358 {
359 assert(session());
360 assert(IdIsValid(self()->id()));
361
362 int res = session()->query<int>("SELECT COALESCE(MAX(total_disc),0) FROM track t INNER JOIN release r ON r.id = t.release_id")
363 .where("r.id = ?")
364 .bind(this->id());
365
366 return (res > 0) ? std::make_optional<std::size_t>(res) : std::nullopt;
367 }
368
369 std::optional<int>
getReleaseYear(bool original) const370 Release::getReleaseYear(bool original) const
371 {
372 assert(session());
373
374 const std::string field {original ? "original_year" : "year"};
375
376 Wt::Dbo::collection<int> dates = session()->query<int>(
377 std::string {"SELECT "} + "t." + field + " FROM track t INNER JOIN release r ON r.id = t.release_id")
378 .where("r.id = ?")
379 .groupBy(field)
380 .bind(this->id());
381
382 // various dates => no date
383 if (dates.empty() || dates.size() > 1)
384 return std::nullopt;
385
386 auto date {dates.front()};
387
388 if (date > 0)
389 return date;
390 else
391 return std::nullopt;
392 }
393
394 std::optional<std::string>
getCopyright() const395 Release::getCopyright() const
396 {
397 assert(session());
398
399 Wt::Dbo::collection<std::string> copyrights = session()->query<std::string>
400 ("SELECT copyright FROM track t INNER JOIN release r ON r.id = t.release_id")
401 .where("r.id = ?")
402 .groupBy("copyright")
403 .bind(this->id());
404
405 std::vector<std::string> values(copyrights.begin(), copyrights.end());
406
407 // various copyrights => no copyright
408 if (values.empty() || values.size() > 1 || values.front().empty())
409 return std::nullopt;
410
411 return values.front();
412 }
413
414 std::optional<std::string>
getCopyrightURL() const415 Release::getCopyrightURL() const
416 {
417 assert(session());
418
419 Wt::Dbo::collection<std::string> copyrights = session()->query<std::string>
420 ("SELECT copyright_url FROM track t INNER JOIN release r ON r.id = t.release_id")
421 .where("r.id = ?")
422 .groupBy("copyright_url")
423 .bind(this->id());
424
425 std::vector<std::string> values(copyrights.begin(), copyrights.end());
426
427 // various copyright URLs => no copyright URL
428 if (values.empty() || values.size() > 1 || values.front().empty())
429 return std::nullopt;
430
431 return values.front();
432 }
433
434 std::vector<Wt::Dbo::ptr<Artist>>
getArtists(TrackArtistLinkType linkType) const435 Release::getArtists(TrackArtistLinkType linkType) const
436 {
437 assert(self());
438 assert(IdIsValid(self()->id()));
439 assert(session());
440
441 Wt::Dbo::collection<Wt::Dbo::ptr<Artist>> res = session()->query<Wt::Dbo::ptr<Artist>>(
442 "SELECT DISTINCT a FROM artist a"
443 " INNER JOIN track_artist_link t_a_l ON t_a_l.artist_id = a.id"
444 " INNER JOIN track t ON t.id = t_a_l.track_id"
445 " INNER JOIN release r ON r.id = t.release_id")
446 .where("r.id = ?").bind(self()->id())
447 .where("t_a_l.type = ?").bind(linkType);
448
449 return std::vector<Wt::Dbo::ptr<Artist>>(res.begin(), res.end());
450 }
451
452 std::vector<Release::pointer>
getSimilarReleases(std::optional<std::size_t> offset,std::optional<std::size_t> count) const453 Release::getSimilarReleases(std::optional<std::size_t> offset, std::optional<std::size_t> count) const
454 {
455 assert(self());
456 assert(IdIsValid(self()->id()));
457 assert(session());
458
459 Wt::Dbo::Query<pointer> query {session()->query<pointer>(
460 "SELECT r FROM release r"
461 " INNER JOIN track t ON t.release_id = r.id"
462 " INNER JOIN track_cluster t_c ON t_c.track_id = t.id"
463 " WHERE "
464 " t_c.cluster_id IN (SELECT c.id from cluster c INNER JOIN track t ON c.id = t_c.cluster_id INNER JOIN track_cluster t_c ON t_c.track_id = t.id INNER JOIN release r ON r.id = t.release_id WHERE r.id = ?)"
465 " AND r.id <> ?"
466 )
467 .bind(self()->id())
468 .bind(self()->id())
469 .groupBy("r.id")
470 .orderBy("COUNT(*) DESC, RANDOM()")
471 .limit(count ? static_cast<int>(*count) : -1)
472 .offset(offset ? static_cast<int>(*offset) : -1)};
473
474 Wt::Dbo::collection<pointer> res = query;
475 return std::vector<pointer>(res.begin(), res.end());
476 }
477
478 bool
hasVariousArtists() const479 Release::hasVariousArtists() const
480 {
481 // TODO optimize
482 return getArtists().size() > 1;
483 }
484
485 std::vector<Wt::Dbo::ptr<Track>>
getTracks(const std::set<IdType> & clusterIds) const486 Release::getTracks(const std::set<IdType>& clusterIds) const
487 {
488 assert(self());
489 assert(self()->id() != Wt::Dbo::dbo_traits<Release>::invalidId() );
490 assert(session());
491
492 WhereClause where;
493
494 std::ostringstream oss;
495 oss << "SELECT t FROM track t INNER JOIN release r ON t.release_id = r.id";
496
497 if (!clusterIds.empty())
498 {
499 oss << " INNER JOIN cluster c ON c.id = t_c.cluster_id INNER JOIN track_cluster t_c ON t_c.track_id = t.id";
500
501 WhereClause clusterClause;
502
503 for (auto id : clusterIds)
504 clusterClause.Or(WhereClause("c.id = ?")).bind(std::to_string(id));
505
506 where.And(clusterClause);
507 }
508
509 where.And(WhereClause("r.id = ?")).bind(std::to_string(id()));
510
511 oss << " " << where.get();
512
513 if (!clusterIds.empty())
514 oss << " GROUP BY t.id HAVING COUNT(*) = " << clusterIds.size();
515
516 oss << " ORDER BY t.disc_number,t.track_number";
517
518 Wt::Dbo::Query<Track::pointer> query = session()->query<Track::pointer>( oss.str() );
519
520 for (const std::string& bindArg : where.getBindArgs())
521 {
522 query.bind(bindArg);
523 }
524
525 Wt::Dbo::collection< Wt::Dbo::ptr<Track> > res = query;
526
527 return std::vector< Wt::Dbo::ptr<Track> > (res.begin(), res.end());
528 }
529
530 std::size_t
getTracksCount() const531 Release::getTracksCount() const
532 {
533 return _tracks.size();
534 }
535
536 Wt::Dbo::ptr<Track>
getFirstTrack() const537 Release::getFirstTrack() const
538 {
539 assert(self());
540 assert(self()->id() != Wt::Dbo::dbo_traits<Artist>::invalidId());
541 assert(session());
542
543 return session()->query<Track::pointer>("SELECT t from track t")
544 .join("release r ON t.release_id = r.id")
545 .where("r.id = ?").bind(self()->id())
546 .orderBy("t.disc_number,t.track_number")
547 .limit(1);
548 }
549
550 std::chrono::milliseconds
getDuration() const551 Release::getDuration() const
552 {
553 assert(self());
554 assert(self()->id() != Wt::Dbo::dbo_traits<Artist>::invalidId());
555 assert(session());
556
557 using milli = std::chrono::duration<int, std::milli>;
558
559 Wt::Dbo::Query<milli> query {session()->query<milli>("SELECT COALESCE(SUM(duration), 0) FROM track t INNER JOIN release r ON t.release_id = r.id")
560 .where("r.id = ?").bind(self()->id())};
561
562 return query.resultValue();
563 }
564
565 Wt::WDateTime
getLastWritten() const566 Release::getLastWritten() const
567 {
568 assert(self());
569 assert(self()->id() != Wt::Dbo::dbo_traits<Artist>::invalidId());
570 assert(session());
571
572 Wt::Dbo::Query<Wt::WDateTime> query {session()->query<Wt::WDateTime>("SELECT COALESCE(MAX(file_last_write), '1970-01-01T00:00:00') FROM track t INNER JOIN release r ON t.release_id = r.id")
573 .where("r.id = ?").bind(self()->id())};
574
575 return query.resultValue();
576 }
577
578 std::vector<std::vector<Wt::Dbo::ptr<Cluster>>>
getClusterGroups(std::vector<ClusterType::pointer> clusterTypes,std::size_t size) const579 Release::getClusterGroups(std::vector<ClusterType::pointer> clusterTypes, std::size_t size) const
580 {
581 assert(self());
582 assert(self()->id() != Wt::Dbo::dbo_traits<Artist>::invalidId() );
583 assert(session());
584
585 WhereClause where;
586
587 std::ostringstream oss;
588
589 oss << "SELECT c from cluster c INNER JOIN track t ON c.id = t_c.cluster_id INNER JOIN track_cluster t_c ON t_c.track_id = t.id INNER JOIN cluster_type c_type ON c.cluster_type_id = c_type.id INNER JOIN release r ON t.release_id = r.id ";
590
591 where.And(WhereClause("r.id = ?")).bind(std::to_string(self()->id()));
592 {
593 WhereClause clusterClause;
594 for (auto clusterType : clusterTypes)
595 clusterClause.Or(WhereClause("c_type.id = ?")).bind(std::to_string(clusterType.id()));
596 where.And(clusterClause);
597 }
598 oss << " " << where.get();
599 oss << " GROUP BY c.id ORDER BY COUNT(c.id) DESC";
600
601 Wt::Dbo::Query<Cluster::pointer> query = session()->query<Cluster::pointer>( oss.str() );
602
603 for (const std::string& bindArg : where.getBindArgs())
604 query.bind(bindArg);
605
606 Wt::Dbo::collection<Cluster::pointer> queryRes = query;
607
608 std::map<IdType, std::vector<Cluster::pointer>> clusters;
609 for (auto cluster : queryRes)
610 {
611 if (clusters[cluster->getType().id()].size() < size)
612 clusters[cluster->getType().id()].push_back(cluster);
613 }
614
615 std::vector<std::vector<Cluster::pointer>> res;
616 for (auto cluster_list : clusters)
617 res.push_back(cluster_list.second);
618
619 return res;
620 }
621
622 } // namespace Database
623