Artist.cpp 18.2 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
#include "Artist.h"
28
#include "Album.h"
29
#include "AlbumTrack.h"
30
#include "Media.h"
31 32

#include "database/SqliteTools.h"
33
#include "database/SqliteQuery.h"
34

35 36 37
namespace medialibrary
{

38 39 40
const std::string Artist::Table::Name = "Artist";
const std::string Artist::Table::PrimaryKeyColumn = "id_artist";
int64_t Artist::*const Artist::Table::PrimaryKey = &Artist::m_id;
41

42 43
Artist::Artist( MediaLibraryPtr ml, sqlite::Row& row )
    : m_ml( ml )
44 45 46 47 48 49 50 51
    , m_id( row.extract<decltype(m_id)>() )
    , m_name( row.extract<decltype(m_name)>() )
    , m_shortBio( row.extract<decltype(m_shortBio)>() )
    , m_thumbnailId( row.extract<decltype(m_thumbnailId)>() )
    , m_nbAlbums( row.extract<decltype(m_nbAlbums)>() )
    , m_nbTracks( row.extract<decltype(m_nbTracks)>() )
    , m_mbId( row.extract<decltype(m_mbId)>() )
    , m_isPresent( row.extract<decltype(m_isPresent)>() )
52 53 54
{
}

55 56 57
Artist::Artist( MediaLibraryPtr ml, const std::string& name )
    : m_ml( ml )
    , m_id( 0 )
58
    , m_name( name )
59
    , m_thumbnailId( 0 )
60
    , m_nbAlbums( 0 )
61
    , m_nbTracks( 0 )
62
    , m_isPresent( true )
63 64 65
{
}

66
int64_t Artist::id() const
67 68 69 70
{
    return m_id;
}

71
const std::string& Artist::name() const
72 73 74 75
{
    return m_name;
}

76
const std::string& Artist::shortBio() const
77 78 79 80
{
    return m_shortBio;
}

81
bool Artist::setShortBio(const std::string& shortBio)
82
{
83
    static const std::string req = "UPDATE " + Artist::Table::Name
84
            + " SET shortbio = ? WHERE id_artist = ?";
85
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, shortBio, m_id ) == false )
86 87 88 89 90
        return false;
    m_shortBio = shortBio;
    return true;
}

91
Query<IAlbum> Artist::albums( const QueryParameters* params ) const
92
{
93
    return Album::fromArtist( m_ml, m_id, params );
94 95
}

96 97 98 99 100 101
Query<IAlbum> Artist::searchAlbums( const std::string& pattern,
                                    const QueryParameters* params ) const
{
    return Album::searchFromArtist( m_ml, pattern, m_id, params );
}

102
Query<IMedia> Artist::tracks( const QueryParameters* params ) const
103
{
104
    std::string req = "FROM " + Media::Table::Name + " med ";
105

106 107
    SortingCriteria sort = params != nullptr ? params->sort : SortingCriteria::Default;
    bool desc = params != nullptr ? params->desc : false;
108 109 110 111 112 113
    // Various artist is a special artist that doesn't have tracks per-se.
    // Rather, it's a virtual artist for albums with many artist but no declared
    // album artist. When listing its tracks, we need to list those by albums
    // instead of listing all tracks by this artist, as there will be none.
    if ( m_id != VariousArtistID )
    {
114 115
        req += "INNER JOIN MediaArtistRelation mar ON mar.media_id = med.id_media ";
        if ( sort == SortingCriteria::Album )
116 117 118 119
        {
            req += "INNER JOIN Album alb ON alb.id_album = atr.album_id "
                   "INNER JOIN AlbumTrack atr ON atr.media_id = med.id_media ";
        }
120
        req += "WHERE mar.artist_id = ? ";
121 122 123 124 125 126 127 128
    }
    else
    {
        req += "INNER JOIN AlbumTrack atr ON atr.media_id = med.id_media "
               "INNER JOIN Album alb ON alb.id_album = atr.album_id "
               "WHERE alb.artist_id = ? ";
    }

129 130
    req += "AND med.is_present != 0";
    std::string orderBy = "ORDER BY ";
131 132
    switch ( sort )
    {
133
    case SortingCriteria::Duration:
134
        orderBy += "med.duration";
135
        break;
136
    case SortingCriteria::InsertionDate:
137
        orderBy += "med.insertion_date";
138
        break;
139
    case SortingCriteria::ReleaseDate:
140
        orderBy += "med.release_date";
141
        break;
142 143
    case SortingCriteria::Album:
        if ( desc == true )
144
            orderBy += "alb.title DESC, atr.disc_number, atr.track_number";
145
        else
146
            orderBy += "alb.title, atr.disc_number, atr.track_number";
147
        break;
148
    default:
149 150 151 152
        LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Default (Alpha)" );
        /* fall-through */
    case SortingCriteria::Default:
    case SortingCriteria::Alpha:
153
        orderBy += "med.title";
154 155 156
        break;
    }

157
    if ( desc == true && sort != SortingCriteria::Album )
158 159 160
        orderBy += " DESC";
    return make_query<Media, IMedia>( m_ml, "med.*", std::move( req ),
                                      std::move( orderBy ), m_id );
161 162
}

163 164 165 166 167
Query<IMedia> Artist::searchTracks( const std::string& pattern, const QueryParameters* params ) const
{
    return Media::searchArtistTracks( m_ml, pattern, m_id, params );
}

168
bool Artist::addMedia( Media& media )
169 170 171 172
{
    static const std::string req = "INSERT INTO MediaArtistRelation VALUES(?, ?)";
    // If track's ID is 0, the request will fail due to table constraints
    sqlite::ForeignKey artistForeignKey( m_id );
173
    return sqlite::Tools::executeInsert( m_ml->getConn(), req, media.id(), artistForeignKey ) != 0;
174 175
}

176
const std::string& Artist::artworkMrl() const
177
{
178 179 180 181 182 183 184 185 186 187 188 189
    if ( m_thumbnailId == 0 )
        return Thumbnail::EmptyMrl;

    auto lock = m_thumbnail.lock();
    if ( m_thumbnail.isCached() == false )
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return Thumbnail::EmptyMrl;
        m_thumbnail = std::move( thumbnail );
    }
    return m_thumbnail.get()->mrl();
190 191
}

192 193 194 195 196 197 198 199 200 201 202 203 204 205 206
std::shared_ptr<Thumbnail> Artist::thumbnail()
{
    if ( m_thumbnailId == 0 )
        return nullptr;
    auto lock = m_thumbnail.lock();
    if ( m_thumbnail.isCached() == false )
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return nullptr;
        m_thumbnail = std::move( thumbnail );
    }
    return m_thumbnail.get();
}

207
bool Artist::setArtworkMrl( const std::string& artworkMrl, Thumbnail::Origin origin )
208
{
209 210
    if ( m_thumbnailId != 0 )
        return Thumbnail::setMrlFromPrimaryKey( m_ml, m_thumbnail, m_thumbnailId,
211
                                                artworkMrl, origin );
212 213 214 215 216 217 218 219

    std::unique_ptr<sqlite::Transaction> t;
    if ( sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();
    auto lock = m_thumbnail.lock();
    m_thumbnail = Thumbnail::create( m_ml, artworkMrl, Thumbnail::Origin::Artist );
    if ( m_thumbnail.get() == nullptr )
        return false;
220
    static const std::string req = "UPDATE " + Artist::Table::Name +
221 222
            " SET thumbnail_id = ? WHERE id_artist = ?";
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_thumbnail.get()->id(), m_id ) == false )
223
        return false;
224 225 226
    m_thumbnailId = m_thumbnail.get()->id();
    if ( t != nullptr )
        t->commit();
227 228 229
    return true;
}

230
bool Artist::updateNbAlbum( int increment )
231
{
232 233 234
    assert( increment != 0 );
    assert( increment > 0 || ( increment < 0 && m_nbAlbums >= 1 ) );

235
    static const std::string req = "UPDATE " + Artist::Table::Name +
236
            " SET nb_albums = nb_albums + ? WHERE id_artist = ?";
237
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, increment, m_id ) == false )
238
        return false;
239
    m_nbAlbums += increment;
240 241 242
    return true;
}

243 244 245 246
bool Artist::updateNbTrack(int increment)
{
    assert( increment != 0 );
    assert( increment > 0 || ( increment < 0 && m_nbTracks >= 1 ) );
247
    static const std::string req = "UPDATE " + Artist::Table::Name +
248 249 250 251 252 253 254
            " SET nb_tracks = nb_tracks + ? WHERE id_artist = ?";
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, increment, m_id ) == false )
        return false;
    m_nbTracks += increment;
    return true;
}

255 256
std::shared_ptr<Album> Artist::unknownAlbum()
{
257
    static const std::string req = "SELECT * FROM " + Album::Table::Name +
258
                        " WHERE artist_id = ? AND title IS NULL";
259
    auto album = Album::fetch( m_ml, req, m_id );
260 261
    if ( album == nullptr )
    {
262
        album = Album::createUnknownAlbum( m_ml, this );
263 264 265 266
        if ( album == nullptr )
            return nullptr;
        if ( updateNbAlbum( 1 ) == false )
        {
267
            Album::destroy( m_ml, album->id() );
268 269
            return nullptr;
        }
270 271 272 273
    }
    return album;
}

274 275 276 277 278 279 280
const std::string& Artist::musicBrainzId() const
{
    return m_mbId;
}

bool Artist::setMusicBrainzId( const std::string& mbId )
{
281
    static const std::string req = "UPDATE " + Artist::Table::Name
282 283 284
            + " SET mb_id = ? WHERE id_artist = ?";
    if ( mbId == m_mbId )
        return true;
285
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, mbId, m_id ) == false )
286 287 288 289 290
        return false;
    m_mbId = mbId;
    return true;
}

291 292 293 294 295
unsigned int Artist::nbAlbums() const
{
    return m_nbAlbums;
}

296 297 298 299 300
unsigned int Artist::nbTracks() const
{
    return m_nbTracks;
}

301
void Artist::createTable( sqlite::Connection* dbConnection )
302
{
303
    const std::string req = "CREATE TABLE IF NOT EXISTS " +
304
            Artist::Table::Name +
305 306
            "("
                "id_artist INTEGER PRIMARY KEY AUTOINCREMENT,"
307
                "name TEXT COLLATE NOCASE UNIQUE ON CONFLICT FAIL,"
308
                "shortbio TEXT,"
309
                "thumbnail_id TEXT,"
310
                "nb_albums UNSIGNED INT DEFAULT 0,"
311
                "nb_tracks UNSIGNED INT DEFAULT 0,"
312
                "mb_id TEXT,"
313
                "is_present BOOLEAN NOT NULL DEFAULT 1,"
314
                "FOREIGN KEY(thumbnail_id) REFERENCES " + Thumbnail::Table::Name
315
                + "(id_thumbnail)"
316
            ")";
317
    const std::string reqRel = "CREATE TABLE IF NOT EXISTS MediaArtistRelation("
318 319 320
                "media_id INTEGER NOT NULL,"
                "artist_id INTEGER,"
                "PRIMARY KEY (media_id, artist_id),"
321
                "FOREIGN KEY(media_id) REFERENCES " + Media::Table::Name +
322
                "(id_media) ON DELETE CASCADE,"
323 324
                "FOREIGN KEY(artist_id) REFERENCES " + Artist::Table::Name + "("
                    + Artist::Table::PrimaryKeyColumn + ") ON DELETE CASCADE"
325
            ")";
326
    const std::string reqFts = "CREATE VIRTUAL TABLE IF NOT EXISTS " +
327
                Artist::Table::Name + "Fts USING FTS3("
328 329
                "name"
            ")";
330 331 332
    sqlite::Tools::executeRequest( dbConnection, req );
    sqlite::Tools::executeRequest( dbConnection, reqRel );
    sqlite::Tools::executeRequest( dbConnection, reqFts );
333 334
}

335
void Artist::createTriggers( sqlite::Connection* dbConnection, uint32_t dbModelVersion )
336 337
{
    static const std::string triggerReq = "CREATE TRIGGER IF NOT EXISTS has_album_present AFTER UPDATE OF "
338
            "is_present ON " + Album::Table::Name +
339
            " BEGIN "
340
            " UPDATE " + Artist::Table::Name + " SET is_present="
341
                "(SELECT EXISTS("
342
                    "SELECT id_album FROM " + Album::Table::Name +
343
                    " WHERE artist_id=new.artist_id AND is_present != 0 LIMIT 1"
344
                ") )"
345 346
                "WHERE id_artist=new.artist_id;"
            " END";
347 348 349 350 351
    // Automatically delete the artists that don't have any albums left, except the 2 special artists.
    // Those are assumed to always exist, and deleting them would cause a constaint violation error
    // when inserting an album with unknown/various artist(s).
    // The alternative would be to always check the special artists for existence, which would be much
    // slower when inserting an unknown artist album
352
    static const std::string autoDeleteAlbumTriggerReq = "CREATE TRIGGER IF NOT EXISTS has_album_remaining"
353
            " AFTER DELETE ON " + Album::Table::Name +
354
            " BEGIN"
355 356
            " UPDATE " + Artist::Table::Name + " SET nb_albums = nb_albums - 1 WHERE id_artist = old.artist_id;"
            " DELETE FROM " + Artist::Table::Name + " WHERE id_artist = old.artist_id "
357 358 359 360 361 362 363
            " AND nb_albums = 0 "
            " AND nb_tracks = 0 "
            " AND old.artist_id != " + std::to_string( UnknownArtistID ) +
            " AND old.artist_id != " + std::to_string( VariousArtistID ) + ";"
            " END";

    static const std::string autoDeleteTrackTriggerReq = "CREATE TRIGGER IF NOT EXISTS has_track_remaining"
364
            " AFTER DELETE ON " + AlbumTrack::Table::Name +
365
            " BEGIN"
366 367
            " UPDATE " + Artist::Table::Name + " SET nb_tracks = nb_tracks - 1 WHERE id_artist = old.artist_id;"
            " DELETE FROM " + Artist::Table::Name + " WHERE id_artist = old.artist_id "
368 369 370
            " AND nb_albums = 0 "
            " AND nb_tracks = 0 "
            " AND old.artist_id != " + std::to_string( UnknownArtistID ) +
371
            " AND old.artist_id != " + std::to_string( VariousArtistID ) + ";"
372 373
            " END";

374
    static const std::string ftsInsertTrigger = "CREATE TRIGGER IF NOT EXISTS insert_artist_fts"
375
            " AFTER INSERT ON " + Artist::Table::Name +
376
            " WHEN new.name IS NOT NULL"
377
            " BEGIN"
378
            " INSERT INTO " + Artist::Table::Name + "Fts(rowid,name) VALUES(new.id_artist, new.name);"
379 380
            " END";
    static const std::string ftsDeleteTrigger = "CREATE TRIGGER IF NOT EXISTS delete_artist_fts"
381
            " BEFORE DELETE ON " + Artist::Table::Name +
382
            " WHEN old.name IS NOT NULL"
383
            " BEGIN"
384
            " DELETE FROM " + Artist::Table::Name + "Fts WHERE rowid=old.id_artist;"
385
            " END";
386
    sqlite::Tools::executeRequest( dbConnection, triggerReq );
387
    sqlite::Tools::executeRequest( dbConnection, autoDeleteAlbumTriggerReq );
388 389 390 391 392 393 394 395 396 397 398
    // Don't create this trigger if the database is about to be migrated.
    // This could make earlier migration fail, and needs to be done when
    // migrating to v7 to v8.
    // While the has_album_remaining trigger now also references the nb_tracks
    // field, it was present from before version 3, so it wouldn't be recreated.
    // As we don't support any model before 3 (or rather we just recreate
    // everything), we don't have to bother here.
    if ( dbModelVersion >= 8 )
    {
        sqlite::Tools::executeRequest( dbConnection, autoDeleteTrackTriggerReq );
    }
399 400
    sqlite::Tools::executeRequest( dbConnection, ftsInsertTrigger );
    sqlite::Tools::executeRequest( dbConnection, ftsDeleteTrigger );
401 402
}

403
bool Artist::createDefaultArtists( sqlite::Connection* dbConnection )
404 405 406
{
    // Don't rely on Artist::create, since we want to insert or do nothing here.
    // This will skip the cache for those new entities, but they will be inserted soon enough anyway.
407
    static const std::string req = "INSERT OR IGNORE INTO " + Artist::Table::Name +
408
            "(id_artist) VALUES(?),(?)";
409 410
    sqlite::Tools::executeInsert( dbConnection, req, UnknownArtistID,
                                          VariousArtistID );
411 412 413
    // Always return true. The insertion might succeed, but we consider it a failure when 0 row
    // gets inserted, while we are explicitely specifying "OR IGNORE" here.
    return true;
414 415
}

416
std::shared_ptr<Artist> Artist::create( MediaLibraryPtr ml, const std::string& name )
417
{
418
    auto artist = std::make_shared<Artist>( ml, name );
419
    static const std::string req = "INSERT INTO " + Artist::Table::Name +
420
            "(id_artist, name) VALUES(NULL, ?)";
421
    if ( insert( ml, artist, req, name ) == false )
422 423 424 425
        return nullptr;
    return artist;
}

426
Query<IArtist> Artist::search( MediaLibraryPtr ml, const std::string& name,
427
                               const QueryParameters* params )
428
{
429 430
    std::string req = "FROM " + Artist::Table::Name + " WHERE id_artist IN "
            "(SELECT rowid FROM " + Artist::Table::Name + "Fts WHERE name MATCH '*' || ? || '*')"
431
            "AND is_present != 0";
432 433
    return make_query<Artist, IArtist>( ml, "*", std::move( req ),
                                        sortRequest( params ), name );
434 435
}

436
Query<IArtist> Artist::listAll( MediaLibraryPtr ml, bool includeAll,
437
                                const QueryParameters* params )
438
{
439
    std::string req = "FROM " + Artist::Table::Name + " WHERE ";
440 441 442 443 444
    if ( includeAll == true )
        req += "( nb_albums > 0 OR nb_tracks > 0 )";
    else
        req += "nb_albums > 0";

445
    req += " AND is_present != 0";
446 447
    return make_query<Artist, IArtist>( ml, "*", std::move( req ),
                                        sortRequest( params ) );
448 449
}

450 451 452
Query<IArtist> Artist::searchByGenre( MediaLibraryPtr ml, const std::string& pattern,
                                      const QueryParameters* params, int64_t genreId )
{
453 454
    std::string req = "FROM " + Artist::Table::Name + " a "
                "INNER JOIN " + AlbumTrack::Table::Name + " att ON att.artist_id = a.id_artist "
455
                "WHERE id_artist IN "
456
                    "(SELECT rowid FROM " + Artist::Table::Name + "Fts WHERE name MATCH '*' || ? || '*')"
457 458 459 460
                "AND att.genre_id = ? ";

    std::string groupBy = "GROUP BY att.artist_id "
                          "ORDER BY a.name";
461 462 463 464 465 466 467
    if ( params != nullptr )
    {
        if ( params->sort != SortingCriteria::Default && params->sort != SortingCriteria::Alpha )
            LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Alpha" );
        if ( params->desc == true )
            groupBy += " DESC";
    }
468 469
    return make_query<Artist, IArtist>( ml, "a.*", std::move( req ),
                                        std::move( groupBy ), pattern, genreId );
470 471
}

472
std::string Artist::sortRequest( const QueryParameters* params )
473
{
474
    std::string req = " ORDER BY name";
475 476 477 478 479 480 481
    if ( params != nullptr )
    {
        if ( params->sort != SortingCriteria::Default && params->sort != SortingCriteria::Alpha )
            LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Alpha" );
        if ( params->desc == true )
            req +=  " DESC";
    }
482
    return req;
483 484
}

485
}