Album.cpp 23.7 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
/*****************************************************************************
 * Media Library
 *****************************************************************************
 * Copyright (C) 2015 Hugo Beauzée-Luyssen, Videolabs
 *
 * Authors: Hugo Beauzée-Luyssen<hugo@beauzee.fr>
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation; either version 2.1 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this program; if not, write to the Free Software Foundation,
 * Inc., 51 Franklin Street, Fifth Floor, Boston MA 02110-1301, USA.
 *****************************************************************************/

23 24 25 26
#if HAVE_CONFIG_H
# include "config.h"
#endif

27 28
#include <algorithm>

29
#include "Album.h"
30
#include "AlbumTrack.h"
31
#include "Artist.h"
32
#include "Genre.h"
33
#include "Media.h"
34
#include "Thumbnail.h"
35

36
#include "database/SqliteTools.h"
37
#include "database/SqliteQuery.h"
38

39 40 41
namespace medialibrary
{

42 43 44
const std::string Album::Table::Name = "Album";
const std::string Album::Table::PrimaryKeyColumn = "id_album";
int64_t Album::* const Album::Table::PrimaryKey = &Album::m_id;
45

46 47
Album::Album(MediaLibraryPtr ml, sqlite::Row& row)
    : m_ml( ml )
48 49 50 51 52 53 54 55
    , m_id( row.load<decltype(m_id)>( 0 ) )
    , m_title( row.load<decltype(m_title)>( 1 ) )
    , m_artistId( row.load<decltype(m_artistId)>( 2 ) )
    , m_releaseYear( row.load<decltype(m_releaseYear)>( 3 ) )
    , m_shortSummary( row.load<decltype(m_shortSummary)>( 4 ) )
    , m_thumbnailId( row.load<decltype(m_thumbnailId)>( 5 ) )
    , m_nbTracks( row.load<decltype(m_nbTracks)>( 6 ) )
    , m_duration( row.load<decltype(m_duration)>( 7 ) )
56 57
    , m_nbDiscs( row.load<decltype(m_nbDiscs)>( 8 ) )
    , m_isPresent( row.load<decltype(m_isPresent)>( 9 ) )
58
{
59 60
}

61
Album::Album( MediaLibraryPtr ml, const std::string& title, int64_t thumbnailId )
62 63
    : m_ml( ml )
    , m_id( 0 )
64
    , m_title( title )
65
    , m_artistId( 0 )
66
    , m_releaseYear( ~0u )
67
    , m_thumbnailId( thumbnailId )
68
    , m_nbTracks( 0 )
69
    , m_duration( 0 )
70
    , m_nbDiscs( 1 )
71
    , m_isPresent( true )
72 73 74
{
}

75 76 77
Album::Album( MediaLibraryPtr ml, const Artist* artist )
    : m_ml( ml )
    , m_id( 0 )
78 79
    , m_artistId( artist->id() )
    , m_releaseYear( ~0u )
80
    , m_thumbnailId( 0 )
81
    , m_nbTracks( 0 )
82
    , m_duration( 0 )
83
    , m_nbDiscs( 1 )
84
    , m_isPresent( true )
85 86 87
{
}

88
int64_t Album::id() const
89 90
{
    return m_id;
91 92
}

93
const std::string& Album::title() const
94
{
95
    return m_title;
96 97
}

98
unsigned int Album::releaseYear() const
99
{
100 101
    if ( m_releaseYear == ~0U )
        return 0;
102 103 104
    return m_releaseYear;
}

105
bool Album::setReleaseYear( unsigned int date, bool force )
106
{
107 108 109 110 111 112 113 114 115 116 117 118
    if ( date == m_releaseYear )
        return true;
    if ( force == false )
    {
        if ( m_releaseYear != ~0u && date != m_releaseYear )
        {
            // If we already have set the date back to 0, don't do it again.
            if ( m_releaseYear == 0 )
                return true;
            date = 0;
        }
    }
119
    static const std::string req = "UPDATE " + Album::Table::Name
120
            + " SET release_year = ? WHERE id_album = ?";
121
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, date, m_id ) == false )
122 123 124 125 126
        return false;
    m_releaseYear = date;
    return true;
}

127
const std::string& Album::shortSummary() const
128 129 130 131
{
    return m_shortSummary;
}

132 133
bool Album::setShortSummary( const std::string& summary )
{
134
    static const std::string req = "UPDATE " + Album::Table::Name
135
            + " SET short_summary = ? WHERE id_album = ?";
136
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, summary, m_id ) == false )
137 138 139 140 141
        return false;
    m_shortSummary = summary;
    return true;
}

142
const std::string& Album::artworkMrl() const
143
{
144 145 146
    if ( m_thumbnailId == 0 )
        return Thumbnail::EmptyMrl;

147
    if ( m_thumbnail == nullptr )
148 149 150 151 152 153
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return Thumbnail::EmptyMrl;
        m_thumbnail = std::move( thumbnail );
    }
154
    return m_thumbnail->mrl();
155 156
}

157 158 159 160
std::shared_ptr<Thumbnail> Album::thumbnail()
{
    if ( m_thumbnailId == 0 )
        return nullptr;
161
    if ( m_thumbnail == nullptr )
162 163 164 165 166 167
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return nullptr;
        m_thumbnail = std::move( thumbnail );
    }
168
    return m_thumbnail;
169 170
}

171 172
bool Album::setArtworkMrl( const std::string& artworkMrl, Thumbnail::Origin origin,
                           bool isGenerated )
173
{
174 175
    if ( m_thumbnailId != 0 )
        return Thumbnail::setMrlFromPrimaryKey( m_ml, m_thumbnail, m_thumbnailId,
176
                                                artworkMrl, origin );
177 178 179 180

    std::unique_ptr<sqlite::Transaction> t;
    if ( sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();
181
    m_thumbnail = Thumbnail::create( m_ml, artworkMrl, Thumbnail::Origin::Album, isGenerated );
182
    if ( m_thumbnail == nullptr )
183
        return false;
184
    static const std::string req = "UPDATE " + Album::Table::Name
185
            + " SET thumbnail_id = ? WHERE id_album = ?";
186
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_thumbnail->id(), m_id ) == false )
187
        return false;
188
    m_thumbnailId = m_thumbnail->id();
189 190
    if ( t != nullptr )
        t->commit();
191 192 193
    return true;
}

194
std::string Album::orderTracksBy( const QueryParameters* params = nullptr )
195 196
{
    std::string req = " ORDER BY ";
197 198
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
199 200
    switch ( sort )
    {
201
    case SortingCriteria::Alpha:
202 203
        req += "med.title";
        break;
204
    case SortingCriteria::Duration:
205 206
        req += "med.duration";
        break;
207
    case SortingCriteria::ReleaseDate:
208
        req += "med.release_date";
209 210
        break;
    default:
211 212 213
        LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Default" );
        /* fall-through */
    case SortingCriteria::Default:
214 215 216 217
        if ( desc == true )
            req += "att.disc_number DESC, att.track_number DESC, med.filename";
        else
            req += "att.disc_number, att.track_number, med.filename";
218 219 220 221 222 223 224 225
        break;
    }

    if ( desc == true )
        req += " DESC";
    return req;
}

226
std::string Album::orderBy( const QueryParameters* params )
227 228
{
    std::string req = " ORDER BY ";
229 230
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
231 232
    switch ( sort )
    {
233
    case SortingCriteria::ReleaseDate:
234 235 236 237 238
        if ( desc == true )
            req += "release_year DESC, title";
        else
            req += "release_year, title";
        break;
239
    case SortingCriteria::Duration:
240 241 242 243
        req += "duration";
        if ( desc == true )
            req += " DESC";
        break;
244 245 246 247 248
    case SortingCriteria::TrackNumber:
        req += "nb_tracks";
        if ( desc == false )
            req += " DESC";
        break;
249
    default:
250 251 252 253
        LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Default (Alpha)" );
        /* fall-through */
    case SortingCriteria::Default:
    case SortingCriteria::Alpha:
254 255 256 257 258 259 260 261
        req += "title";
        if ( desc == true )
            req += " DESC";
        break;
    }
    return req;
}

262
Query<IMedia> Album::tracks( const QueryParameters* params ) const
263
{
264 265
    // This doesn't return the cached version, because it would be fairly complicated, if not impossible or
    // counter productive, to maintain a cache that respects all orderings.
266 267
    std::string req = "FROM " + Media::Table::Name + " med "
        " INNER JOIN " + AlbumTrack::Table::Name + " att ON att.media_id = med.id_media "
268
        " WHERE att.album_id = ? AND med.is_present != 0";
269 270
    return make_query<Media, IMedia>( m_ml, "med.*", std::move( req ),
                                      orderTracksBy( params ), m_id );
271 272
}

273
Query<IMedia> Album::tracks( GenrePtr genre, const QueryParameters* params ) const
274 275 276
{
    if ( genre == nullptr )
        return {};
277 278
    std::string req = "FROM " + Media::Table::Name + " med "
            " INNER JOIN " + AlbumTrack::Table::Name + " att ON att.media_id = med.id_media "
279
            " WHERE att.album_id = ? AND med.is_present != 0"
280
            " AND genre_id = ?";
281 282
    return make_query<Media, IMedia>( m_ml, "med.*", std::move( req ),
                                      orderTracksBy( params ), m_id, genre->id() );
283 284
}

285
std::vector<MediaPtr> Album::cachedTracks() const
286
{
287
    if ( m_tracks.size() == 0 )
288
        m_tracks = tracks( nullptr )->all();
289
    return m_tracks;
290 291
}

292 293 294 295 296 297
Query<IMedia> Album::searchTracks( const std::string& pattern,
                                   const QueryParameters* params ) const
{
    return Media::searchAlbumTracks( m_ml, pattern, m_id, params );
}

298
std::shared_ptr<AlbumTrack> Album::addTrack( std::shared_ptr<Media> media, unsigned int trackNb,
299
                                             unsigned int discNumber, int64_t artistId, Genre* genre )
300
{
301 302
    auto track = AlbumTrack::create( m_ml, m_id, media, trackNb, discNumber, artistId,
                                     genre != nullptr ? genre->id() : 0, media->duration() );
303 304
    if ( track == nullptr )
        return nullptr;
305
    media->setAlbumTrack( track );
306 307
    if ( genre != nullptr )
        genre->updateCachedNbTracks( 1 );
308
    // Assume the media will be saved by the caller
309
    m_nbTracks++;
310 311
    if ( media->duration() > 0 )
        m_duration += media->duration();
312 313 314 315 316
    // Don't assume we have always have a valid value in m_tracks.
    // While it's ok to assume that if we are currently parsing the album, we
    // have a valid cache tracks, this isn't true when restarting an interrupted parsing.
    // The nbTracks value will be correct however. If it's equal to one, it means we're inserting
    // the first track in this album
317 318 319
    if ( ( m_tracks.empty() == true && m_nbTracks == 1 ) ||
         ( m_tracks.empty() == false && m_nbTracks > 1 ) )
        m_tracks.push_back( media );
320
    return track;
321 322
}

323 324 325 326 327 328 329
bool Album::removeTrack( Media& media, AlbumTrack& track )
{
    m_duration -= media.duration();
    m_nbTracks--;
    auto genre = std::static_pointer_cast<Genre>( track.genre() );
    if ( genre != nullptr )
        genre->updateCachedNbTracks( -1 );
330 331 332 333 334
    auto it = std::find_if( begin( m_tracks ), end( m_tracks ), [&media]( MediaPtr m ) {
        return m->id() == media.id();
    });
    if ( it != end( m_tracks ) )
        m_tracks.erase( it );
335 336 337 338

    return true;
}

339 340 341 342 343
unsigned int Album::nbTracks() const
{
    return m_nbTracks;
}

344 345 346 347 348 349 350 351 352 353 354 355 356 357 358
uint32_t Album::nbDiscs() const
{
    return m_nbDiscs;
}

bool Album::setNbDiscs( uint32_t nbDiscs )
{
    static const std::string req = "UPDATE " + Album::Table::Name
            + " SET nb_discs = ? WHERE id_album = ?";
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, nbDiscs, m_id ) == false )
        return false;
    m_nbDiscs = nbDiscs;
    return true;
}

359 360 361 362 363
unsigned int Album::duration() const
{
    return m_duration;
}

364 365 366 367 368
bool Album::isUnknownAlbum() const
{
    return m_title.empty();
}

369
ArtistPtr Album::albumArtist() const
370 371 372
{
    if ( m_artistId == 0 )
        return nullptr;
373
    if ( m_albumArtist == nullptr )
374
        m_albumArtist = Artist::fetch( m_ml, m_artistId );
375
    return m_albumArtist;
376 377
}

378
bool Album::setAlbumArtist( std::shared_ptr<Artist> artist )
379 380 381 382 383
{
    if ( m_artistId == artist->id() )
        return true;
    if ( artist->id() == 0 )
        return false;
384
    static const std::string req = "UPDATE " + Table::Name + " SET "
385
            "artist_id = ? WHERE id_album = ?";
386
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, artist->id(), m_id ) == false )
387
        return false;
388 389
    if ( m_artistId != 0 )
    {
390
        if ( m_albumArtist == nullptr )
391
            albumArtist();
392
        m_albumArtist->updateNbAlbum( -1 );
393
    }
394
    m_artistId = artist->id();
395
    m_albumArtist = artist;
396
    artist->updateNbAlbum( 1 );
397
    static const std::string ftsReq = "UPDATE " + Table::Name + "Fts SET "
398
            " artist = ? WHERE rowid = ?";
399
    sqlite::Tools::executeUpdate( m_ml->getConn(), ftsReq, artist->name(), m_id );
400 401 402
    return true;
}

403
Query<IArtist> Album::artists( const QueryParameters* params ) const
404
{
405
    std::string req = "FROM " + Artist::Table::Name + " art "
406 407 408
            "INNER JOIN " + AlbumTrack::Table::Name + " att "
                "ON att.artist_id = art.id_artist "
            "WHERE att.album_id = ?";
409 410
    if ( params != nullptr && ( params->sort != SortingCriteria::Alpha &&
                                params->sort != SortingCriteria::Default ) )
411 412
        LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Alpha" );
    std::string orderBy = "GROUP BY art.id_artist ORDER BY art.name";
413
    if ( params != nullptr && params->desc == true )
414 415 416
        orderBy += " DESC";
    return make_query<Artist, IArtist>( m_ml, "art.*", std::move( req ),
                                        std::move( orderBy ), m_id );
417 418
}

419
void Album::createTable( sqlite::Connection* dbConnection )
420
{
421
    const std::string req = "CREATE TABLE IF NOT EXISTS " +
422
            Table::Name +
423
            "("
424
                "id_album INTEGER PRIMARY KEY AUTOINCREMENT,"
425
                "title TEXT COLLATE NOCASE,"
426
                "artist_id UNSIGNED INTEGER,"
427
                "release_year UNSIGNED INTEGER,"
428
                "short_summary TEXT,"
429
                "thumbnail_id UNSIGNED INT,"
430
                "nb_tracks UNSIGNED INTEGER DEFAULT 0,"
431
                "duration UNSIGNED INTEGER NOT NULL DEFAULT 0,"
432
                "nb_discs UNSIGNED INTEGER NOT NULL DEFAULT 1,"
433
                "is_present UNSIGNED INTEGER NOT NULL DEFAULT 0,"
434
                "FOREIGN KEY( artist_id ) REFERENCES " + Artist::Table::Name
435
                + "(id_artist) ON DELETE CASCADE,"
436
                "FOREIGN KEY(thumbnail_id) REFERENCES " + Thumbnail::Table::Name
437
                + "(id_thumbnail)"
438
            ")";
439
    const std::string vtableReq = "CREATE VIRTUAL TABLE IF NOT EXISTS "
440
                + Table::Name + "Fts USING FTS3("
441 442 443
                "title,"
                "artist"
            ")";
444

445 446
    sqlite::Tools::executeRequest( dbConnection, req );
    sqlite::Tools::executeRequest( dbConnection, vtableReq );
447 448
}

449
void Album::createTriggers( sqlite::Connection* dbConnection )
450
{
451
    const std::string indexReq = "CREATE INDEX IF NOT EXISTS album_artist_id_idx ON " +
452
            Table::Name + "(artist_id)";
453
    static const std::string triggerReq = "CREATE TRIGGER IF NOT EXISTS is_album_present AFTER UPDATE OF "
454 455 456 457
            "is_present ON " + Media::Table::Name +
            " WHEN new.subtype = " +
                std::to_string( static_cast<typename std::underlying_type<IMedia::SubType>::type>(
                                    IMedia::SubType::AlbumTrack ) ) +
458
            " BEGIN "
459 460 461 462 463
            " UPDATE " + Table::Name + " SET is_present=is_present + "
                "(CASE new.is_present WHEN 0 THEN -1 ELSE 1 END)"
                "WHERE id_album = (SELECT album_id FROM " + AlbumTrack::Table::Name + " "
                    "WHERE media_id = new.id_media"
                ");"
464
            " END";
465
    static const std::string deleteTriggerReq = "CREATE TRIGGER IF NOT EXISTS delete_album_track AFTER DELETE ON "
466
             + AlbumTrack::Table::Name +
467
            " BEGIN "
468
            " UPDATE " + Table::Name +
469 470
            " SET"
                " nb_tracks = nb_tracks - 1,"
471
                " is_present = is_present - 1,"
472
                " duration = duration - old.duration"
473
                " WHERE id_album = old.album_id;"
474
            " DELETE FROM " + Table::Name +
475
                " WHERE id_album=old.album_id AND nb_tracks = 0;"
476
            " END";
477
    static const std::string updateAddTrackTriggerReq = "CREATE TRIGGER IF NOT EXISTS add_album_track"
478
            " AFTER INSERT ON " + AlbumTrack::Table::Name +
479
            " BEGIN"
480
            " UPDATE " + Table::Name +
481
            " SET duration = duration + new.duration,"
482 483
            " nb_tracks = nb_tracks + 1,"
            " is_present = is_present + 1"
484 485
            " WHERE id_album = new.album_id;"
            " END";
486
    static const std::string vtriggerInsert = "CREATE TRIGGER IF NOT EXISTS insert_album_fts AFTER INSERT ON "
487
            + Table::Name +
488 489 490
            // Skip unknown albums
            " WHEN new.title IS NOT NULL"
            " BEGIN"
491
            " INSERT INTO " + Table::Name + "Fts(rowid, title) VALUES(new.id_album, new.title);"
492 493
            " END";
    static const std::string vtriggerDelete = "CREATE TRIGGER IF NOT EXISTS delete_album_fts BEFORE DELETE ON "
494
            + Table::Name +
495 496 497
            // Unknown album probably won't be deleted, but better safe than sorry
            " WHEN old.title IS NOT NULL"
            " BEGIN"
498
            " DELETE FROM " + Table::Name + "Fts WHERE rowid = old.id_album;"
499
            " END";
500
    sqlite::Tools::executeRequest( dbConnection, indexReq );
501 502 503 504 505
    sqlite::Tools::executeRequest( dbConnection, triggerReq );
    sqlite::Tools::executeRequest( dbConnection, deleteTriggerReq );
    sqlite::Tools::executeRequest( dbConnection, updateAddTrackTriggerReq );
    sqlite::Tools::executeRequest( dbConnection, vtriggerInsert );
    sqlite::Tools::executeRequest( dbConnection, vtriggerDelete );
506 507
}

508
std::shared_ptr<Album> Album::create( MediaLibraryPtr ml, const std::string& title, int64_t thumbnailId )
509
{
510
    auto album = std::make_shared<Album>( ml, title, thumbnailId );
511
    static const std::string req = "INSERT INTO " + Table::Name +
512 513
            "(id_album, title, thumbnail_id) VALUES(NULL, ?, ?)";
    if ( insert( ml, album, req, title, sqlite::ForeignKey( thumbnailId ) ) == false )
514 515
        return nullptr;
    return album;
516
}
517

518
std::shared_ptr<Album> Album::createUnknownAlbum( MediaLibraryPtr ml, const Artist* artist )
519
{
520
    auto album = std::make_shared<Album>( ml, artist );
521
    static const std::string req = "INSERT INTO " + Table::Name +
522
            "(id_album, artist_id) VALUES(NULL, ?)";
523
    if ( insert( ml, album, req, artist->id() ) == false )
524 525 526
        return nullptr;
    return album;
}
527

528
Query<IAlbum> Album::search( MediaLibraryPtr ml, const std::string& pattern,
529
                             const QueryParameters* params )
530
{
531
    std::string req = "FROM " + Table::Name + " alb "
532
            "WHERE id_album IN "
533 534
            "(SELECT rowid FROM " + Table::Name + "Fts WHERE " +
            Table::Name + "Fts MATCH '*' || ? || '*')"
535
            "AND is_present != 0";
536 537
    return make_query<Album, IAlbum>( ml, "*", std::move( req ),
                                      orderBy( params ), pattern );
538
}
539

540 541 542
Query<IAlbum> Album::searchFromArtist( MediaLibraryPtr ml, const std::string& pattern,
                                       int64_t artistId, const QueryParameters* params )
{
543
    std::string req = "FROM " + Table::Name + " alb "
544
            "WHERE id_album IN "
545 546
            "(SELECT rowid FROM " + Table::Name + "Fts WHERE " +
            Table::Name + "Fts MATCH '*' || ? || '*')"
547 548
            "AND is_present != 0 "
            "AND artist_id = ?";
549 550
    return make_query<Album, IAlbum>( ml, "*", std::move( req ),
                                      orderBy( params ), pattern, artistId );
551 552
}

553
Query<IAlbum> Album::fromArtist( MediaLibraryPtr ml, int64_t artistId, const QueryParameters* params )
554
{
555 556
    std::string req = "FROM " + Table::Name + " alb "
                    "INNER JOIN " + AlbumTrack::Table::Name + " att "
557
                        "ON att.album_id = alb.id_album "
558 559
                    "INNER JOIN " + Media::Table::Name + " m "
                        "ON att.media_id = m.id_media "
560
                    "WHERE (att.artist_id = ? OR alb.artist_id = ?) "
561
                        "AND m.is_present != 0 ";
562
    std::string groupAndOrder = "GROUP BY att.album_id ORDER BY ";
563 564
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
565 566
    switch ( sort )
    {
567
    case SortingCriteria::Alpha:
568
        groupAndOrder += "title";
569
        if ( desc == true )
570
            groupAndOrder += " DESC";
571
        break;
572

573
    default:
574 575 576 577
        LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Default (ReleaseDate)" );
        /* fall-through */
    case SortingCriteria::Default:
    case SortingCriteria::ReleaseDate:
578 579 580 581
        // When listing albums of an artist, default order is by descending year (with album title
        // discrimination in case 2+ albums went out the same year)
        // This leads to DESC being used for "non-desc" case
        if ( desc == true )
582
            groupAndOrder += "release_year, title";
583
        else
584
            groupAndOrder += "release_year DESC, title";
585 586 587
        break;
    }

588 589
    return make_query<Album, IAlbum>( ml, "*", std::move( req ),
                                      std::move( groupAndOrder ),
590
                                      artistId, artistId );
591 592
}

593
Query<IAlbum> Album::fromGenre( MediaLibraryPtr ml, int64_t genreId, const QueryParameters* params )
594
{
595 596
    std::string req = "FROM " + Table::Name + " alb "
            "INNER JOIN " + AlbumTrack::Table::Name + " att ON att.album_id = alb.id_album "
597 598
            "WHERE att.genre_id = ?";
    std::string groupAndOrderBy = "GROUP BY att.album_id" + orderBy( params );
599
    return make_query<Album, IAlbum>( ml, "alb.*", std::move( req ),
600
                                      std::move( groupAndOrderBy ), genreId );
601 602
}

603 604 605
Query<IAlbum> Album::searchFromGenre( MediaLibraryPtr ml, const std::string& pattern,
                                      int64_t genreId, const QueryParameters* params )
{
606 607
    std::string req = "FROM " + Table::Name + " alb "
            "INNER JOIN " + AlbumTrack::Table::Name + " att ON att.album_id = alb.id_album "
608
            "WHERE id_album IN "
609 610
            "(SELECT rowid FROM " + Table::Name + "Fts WHERE " +
            Table::Name + "Fts MATCH '*' || ? || '*')"
611 612
            "AND att.genre_id = ?";
    std::string groupAndOrderBy = "GROUP BY att.album_id" + orderBy( params );
613
    return make_query<Album, IAlbum>( ml, "alb.*", std::move( req ),
614 615
                                      std::move( groupAndOrderBy ), pattern,
                                      genreId );
616 617
}

618
Query<IAlbum> Album::listAll( MediaLibraryPtr ml, const QueryParameters* params )
619
{
620 621
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
622 623 624
    std::string countReq = "SELECT COUNT(*) FROM " + Table::Name + " WHERE "
            "is_present != 0";
    std::string req = "SELECT alb.* FROM " + Table::Name + " alb ";
625 626
    if ( sort == SortingCriteria::Artist )
    {
627 628 629
        req += "INNER JOIN " + Artist::Table::Name + " art ON alb.artist_id = art.id_artist "
               "WHERE alb.is_present != 0 ";
        req += "ORDER BY art.name ";
630
        if ( desc == true )
631 632
            req += "DESC ";
        req += ", alb.title";
633
    }
634
    else if ( sort == SortingCriteria::PlayCount )
635
    {
636 637 638 639 640
        req += "INNER JOIN " + AlbumTrack::Table::Name + " t ON alb.id_album = t.album_id "
               "INNER JOIN " + Media::Table::Name + " m ON t.media_id = m.id_media "
               "WHERE alb.is_present != 0 ";
        req += "GROUP BY id_album "
               "ORDER BY SUM(m.play_count) ";
641
        if ( desc == false )
642 643
            req += "DESC "; // Most played first by default
        req += ", alb.title";
644
    }
645 646 647 648 649 650 651
    else
    {
        req += " WHERE is_present != 0";
        req += orderBy( params );
    }
    return make_query_with_count<Album, IAlbum>( ml, std::move( countReq ),
                                                 std::move( req ) );
652
}
653 654

}