Media.cpp 27.1 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 <algorithm>
28
#include <cassert>
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
29 30
#include <cstdlib>
#include <cstring>
31
#include <ctime>
32

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
33
#include "Album.h"
34
#include "AlbumTrack.h"
35
#include "Artist.h"
36
#include "AudioTrack.h"
37
#include "Media.h"
38
#include "File.h"
39
#include "Folder.h"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
40
#include "Label.h"
41
#include "logging/Logger.h"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
42
#include "Movie.h"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
43
#include "ShowEpisode.h"
44

45
#include "database/SqliteTools.h"
46
#include "database/SqliteQuery.h"
47
#include "VideoTrack.h"
48 49 50
#include "medialibrary/filesystem/IFile.h"
#include "medialibrary/filesystem/IDirectory.h"
#include "medialibrary/filesystem/IDevice.h"
51
#include "utils/Filename.h"
52

53 54 55
namespace medialibrary
{

56
const std::string policy::MediaTable::Name = "Media";
57
const std::string policy::MediaTable::PrimaryKeyColumn = "id_media";
58
int64_t Media::* const policy::MediaTable::PrimaryKey = &Media::m_id;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
59

60 61
Media::Media( MediaLibraryPtr ml, sqlite::Row& row )
    : m_ml( ml )
62
    , m_metadata( m_ml, IMetadata::EntityType::Media )
63
    , m_changed( false )
64
{
65
    time_t dummy;
66 67
    row >> m_id
        >> m_type
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
68
        >> m_subType
69 70
        >> m_duration
        >> m_playCount
71
        >> m_lastPlayedDate
72
        >> dummy
73
        >> m_insertionDate
74
        >> m_releaseDate
75 76
        >> m_thumbnailId
        >> m_thumbnailGenerated
77
        >> m_title
78
        >> m_filename
79
        >> m_isFavorite
80 81
        >> m_isPresent
        >> m_nbPlaylists;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
82 83
}

84 85 86
Media::Media( MediaLibraryPtr ml, const std::string& title, Type type )
    : m_ml( ml )
    , m_id( 0 )
87
    , m_type( type )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
88
    , m_subType( SubType::Unknown )
89
    , m_duration( -1 )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
90
    , m_playCount( 0 )
91
    , m_lastPlayedDate( 0 )
92
    , m_insertionDate( time( nullptr ) )
93
    , m_releaseDate( 0 )
94 95
    , m_thumbnailId( 0 )
    , m_thumbnailGenerated( false )
96
    , m_title( title )
97 98
    // When creating a Media, meta aren't parsed, and therefor, is the filename
    , m_filename( title )
99
    , m_isFavorite( false )
100
    , m_isPresent( true )
101
    , m_nbPlaylists( 0 )
102
    , m_metadata( m_ml, IMetadata::EntityType::Media )
103
    , m_changed( false )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
104
{
105 106
}

107
std::shared_ptr<Media> Media::create( MediaLibraryPtr ml, Type type, const std::string& fileName )
108
{
109
    auto self = std::make_shared<Media>( ml, fileName, type );
110
    static const std::string req = "INSERT INTO " + policy::MediaTable::Name +
111
            "(type, insertion_date, title, filename) VALUES(?, ?, ?, ?)";
112

113
    if ( insert( ml, self, req, type, self->m_insertionDate, self->m_title, self->m_filename ) == false )
114
        return nullptr;
115
    return self;
116 117
}

118
AlbumTrackPtr Media::albumTrack() const
119
{
120 121
    if ( m_subType != SubType::AlbumTrack )
        return nullptr;
122 123
    auto lock = m_albumTrack.lock();

124
    if ( m_albumTrack.isCached() == false )
125
        m_albumTrack = AlbumTrack::fromMedia( m_ml, m_id );
126
    return m_albumTrack.get();
127 128
}

129
void Media::setAlbumTrack( AlbumTrackPtr albumTrack )
130
{
131
    auto lock = m_albumTrack.lock();
132
    m_albumTrack = albumTrack;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
133 134
    m_subType = SubType::AlbumTrack;
    m_changed = true;
135 136
}

137
int64_t Media::duration() const
138 139 140 141
{
    return m_duration;
}

142
void Media::setDuration( int64_t duration )
143
{
144 145
    if ( m_duration == duration )
        return;
146
    m_duration = duration;
147
    m_changed = true;
148 149
}

150
ShowEpisodePtr Media::showEpisode() const
151
{
152 153
    if ( m_subType != SubType::ShowEpisode )
        return nullptr;
154

155 156
    auto lock = m_showEpisode.lock();
    if ( m_showEpisode.isCached() == false )
157
        m_showEpisode = ShowEpisode::fromMedia( m_ml, m_id );
158
    return m_showEpisode.get();
159 160
}

161
void Media::setShowEpisode( ShowEpisodePtr episode )
162
{
163 164
    auto lock = m_showEpisode.lock();
    m_showEpisode = episode;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
165 166
    m_subType = SubType::ShowEpisode;
    m_changed = true;
167 168
}

169
Query<ILabel> Media::labels() const
170
{
171
    static const std::string req = "FROM " + policy::LabelTable::Name + " l "
172
            "INNER JOIN LabelFileRelation lfr ON lfr.label_id = l.id_label "
173
            "WHERE lfr.media_id = ?";
174
    return make_query<Label, ILabel>( m_ml, "l.*", req, m_id );
175 176
}

177
int Media::playCount() const
178
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
179
    return m_playCount;
180 181
}

182
bool Media::increasePlayCount()
183
{
184
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
185 186
            "play_count = ?, last_played_date = ?, real_last_played_date = ? "
            "WHERE id_media = ?";
187
    auto lastPlayedDate = time( nullptr );
188 189
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_playCount + 1,
                                       lastPlayedDate, lastPlayedDate, m_id ) == false )
190
        return false;
191
    m_playCount++;
192 193
    m_lastPlayedDate = lastPlayedDate;
    return true;
194 195
}

196 197 198 199 200
time_t Media::lastPlayedDate() const
{
    return m_lastPlayedDate;
}

201 202 203 204 205
bool Media::isFavorite() const
{
    return m_isFavorite;
}

206
bool Media::setFavorite( bool favorite )
207
{
208
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET is_favorite = ? WHERE id_media = ?";
209
    if ( m_isFavorite == favorite )
210 211 212
        return true;
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, favorite, m_id ) == false )
        return false;
213
    m_isFavorite = favorite;
214
    return true;
215 216
}

217
const std::vector<FilePtr>& Media::files() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
218
{
219 220 221 222 223
    auto lock = m_files.lock();
    if ( m_files.isCached() == false )
    {
        static const std::string req = "SELECT * FROM " + policy::FileTable::Name
                + " WHERE media_id = ?";
224
        m_files = File::fetchAll<IFile>( m_ml, req, m_id );
225 226
    }
    return m_files;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
227 228
}

229 230 231 232 233
const std::string& Media::fileName() const
{
    return m_filename;
}

234
MoviePtr Media::movie() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
235
{
236 237 238
    if ( m_subType != SubType::Movie )
        return nullptr;

239 240
    auto lock = m_movie.lock();

241
    if ( m_movie.isCached() == false )
242
        m_movie = Movie::fromMedia( m_ml, m_id );
243
    return m_movie.get();
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
244 245
}

246
void Media::setMovie(MoviePtr movie)
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
247
{
248
    auto lock = m_movie.lock();
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
249
    m_movie = movie;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
250 251
    m_subType = SubType::Movie;
    m_changed = true;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
252 253
}

254
bool Media::addVideoTrack( const std::string& codec, unsigned int width, unsigned int height,
255
                           uint32_t fpsNum, uint32_t fpsDen, uint32_t bitrate,
256 257
                           uint32_t sarNum, uint32_t sarDen, const std::string& language,
                           const std::string& description )
258
{
259
    return VideoTrack::create( m_ml, codec, width, height, fpsNum, fpsDen,
260
                               bitrate, sarNum, sarDen, m_id, language, description ) != nullptr;
261 262
}

263
Query<IVideoTrack> Media::videoTracks() const
264
{
265
    static const std::string req = "FROM " + policy::VideoTrackTable::Name +
266
            " WHERE media_id = ?";
267
    return make_query<VideoTrack, IVideoTrack>( m_ml, "*", req, m_id );
268 269
}

270
bool Media::addAudioTrack( const std::string& codec, unsigned int bitrate,
271 272
                          unsigned int sampleRate, unsigned int nbChannels,
                          const std::string& language, const std::string& desc )
273
{
274
    return AudioTrack::create( m_ml, codec, bitrate, sampleRate, nbChannels, language, desc, m_id ) != nullptr;
275 276
}

277
Query<IAudioTrack> Media::audioTracks() const
278
{
279
    static const std::string req = "FROM " + policy::AudioTrackTable::Name +
280
            " WHERE media_id = ?";
281
    return make_query<AudioTrack, IAudioTrack>( m_ml, "*", req, m_id );
282 283
}

284
const std::string& Media::thumbnail() const
285
{
286
    if ( m_thumbnailId == 0 || m_thumbnailGenerated == false )
287 288
        return Thumbnail::EmptyMrl;

289 290
    auto lock = m_thumbnail.lock();
    if ( m_thumbnail.isCached() == false )
291 292 293 294 295 296
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return Thumbnail::EmptyMrl;
        m_thumbnail = std::move( thumbnail );
    }
297 298 299 300 301 302
    return m_thumbnail.get()->mrl();
}

bool Media::isThumbnailGenerated() const
{
    return m_thumbnailGenerated;
303 304
}

305 306
unsigned int Media::insertionDate() const
{
307
    return static_cast<unsigned int>( m_insertionDate );
308 309
}

310 311 312 313 314
unsigned int Media::releaseDate() const
{
    return m_releaseDate;
}

315 316 317 318 319 320 321 322 323 324 325
uint32_t Media::nbPlaylists() const
{
    return m_nbPlaylists.load( std::memory_order_relaxed );
}

void Media::udpateNbPlaylist(int32_t increment) const
{
    // Only update the cached representation, let the triggers handle the DB values
    m_nbPlaylists.fetch_add( increment, std::memory_order_relaxed );
}

326
const IMetadata& Media::metadata( IMedia::MetadataType type ) const
327
{
328
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
329 330
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
331
    return m_metadata.get( static_cast<MDType>( type ) );
332 333 334 335
}

bool Media::setMetadata( IMedia::MetadataType type, const std::string& value )
{
336
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
337 338
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
339
    return m_metadata.set( static_cast<MDType>( type ), value );
340 341 342 343
}

bool Media::setMetadata( IMedia::MetadataType type, int64_t value )
{
344
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
345 346
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
347
    return m_metadata.set( static_cast<MDType>( type ), value );
348 349
}

350 351 352 353 354 355 356 357
bool Media::unsetMetadata(IMedia::MetadataType type)
{
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
    return m_metadata.unset( static_cast<MDType>( type ) );
}

358 359 360 361 362 363 364 365
void Media::setReleaseDate( unsigned int date )
{
    if ( m_releaseDate == date )
        return;
    m_releaseDate = date;
    m_changed = true;
}

366
bool Media::setThumbnail( const std::string& thumbnailMrl, Thumbnail::Origin origin )
367
{
368
    if ( m_thumbnailId != 0 )
369
        return Thumbnail::setMrlFromPrimaryKey( m_ml, m_thumbnail, m_thumbnailId,
370
                                                thumbnailMrl, origin );
371

372 373 374 375
    std::unique_ptr<sqlite::Transaction> t;
    if ( sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();
    auto lock = m_thumbnail.lock();
376 377
    auto thumbnail = Thumbnail::create( m_ml, thumbnailMrl, origin );
    if ( thumbnail == nullptr )
378
        return false;
379

380
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
381
            "thumbnail_id = ?, thumbnail_generated = 1 WHERE id_media = ?";
382
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, thumbnail->id(), m_id ) == false )
383
        return false;
384
    m_thumbnailId = thumbnail->id();
385
    m_thumbnailGenerated = true;
386
    m_thumbnail = std::move( thumbnail );
387 388
    if ( t != nullptr )
        t->commit();
389 390 391
    return true;
}

392 393 394 395 396
bool Media::setThumbnail( const std::string& thumbnailMrl )
{
    return setThumbnail( thumbnailMrl, Thumbnail::Origin::UserProvided );
}

397 398 399
bool Media::save()
{
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
400
            "type = ?, subtype = ?, duration = ?, release_date = ?,"
401
            "title = ? WHERE id_media = ?";
402 403
    if ( m_changed == false )
        return true;
404
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_type, m_subType, m_duration,
405
                                       m_releaseDate, m_title, m_id ) == false )
406 407 408 409
    {
        return false;
    }
    m_changed = false;
410 411 412
    return true;
}

413 414
std::shared_ptr<File> Media::addFile( const fs::IFile& fileFs, int64_t parentFolderId,
                                      bool isFolderFsRemovable, IFile::Type type )
415
{
416
    auto file = File::createFromMedia( m_ml, m_id, type, fileFs, parentFolderId, isFolderFsRemovable);
417 418 419 420 421 422
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
423 424
}

425
FilePtr Media::addExternalMrl( const std::string& mrl , IFile::Type type )
426
{
427 428 429
    FilePtr file;
    try
    {
430
        file = File::createFromMedia( m_ml, m_id, type, mrl );
431 432 433 434 435 436 437
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add media external MRL: ", ex.what() );
        return nullptr;
    }

438 439 440 441 442 443 444 445
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
}

446
void Media::removeFile( File& file )
447
{
448 449 450
    file.destroy();
    auto lock = m_files.lock();
    if ( m_files.isCached() == false )
451
        return;
452
    m_files.get().erase( std::remove_if( begin( m_files.get() ), end( m_files.get() ), [&file]( const FilePtr& f ) {
453 454
        return f->id() == file.id();
    }));
455 456
}

457
std::string Media::sortRequest( const QueryParameters* params )
458
{
459
    std::string req = " ORDER BY ";
460

461 462
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
463 464
    switch ( sort )
    {
465
    case SortingCriteria::Duration:
466
        req += "m.duration";
467
        break;
468
    case SortingCriteria::InsertionDate:
469
        req += "m.insertion_date";
470
        break;
471
    case SortingCriteria::ReleaseDate:
472
        req += "m.release_date";
473
        break;
474
    case SortingCriteria::PlayCount:
475
        req += "m.play_count";
476 477
        desc = !desc; // Make decreasing order default for play count sorting
        break;
478
    case SortingCriteria::Filename:
479
        req += "m.filename COLLATE NOCASE";
480 481 482 483 484 485
        break;
    case SortingCriteria::LastModificationDate:
        req += "f.last_modification_date";
        break;
    case SortingCriteria::FileSize:
        req += "f.size";
486
        break;
487
    default:
488
        req += "m.title";
489 490 491 492
        break;
    }
    if ( desc == true )
        req += " DESC";
493 494
    return req;
}
495

496
Query<IMedia> Media::listAll( MediaLibraryPtr ml, IMedia::Type type,
497
                              const QueryParameters* params )
498
{
499
    std::string req = "FROM " + policy::MediaTable::Name + " m INNER JOIN "
500 501 502 503
            + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " WHERE m.type = ?"
            " AND f.type = ?"
            " AND f.is_present != 0";
504

505
    req += sortRequest( params );
506 507
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
                                      type, IFile::Type::Main );
508 509
}

510
int64_t Media::id() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
511 512 513 514
{
    return m_id;
}

515
IMedia::Type Media::type() const
516 517 518 519
{
    return m_type;
}

520
IMedia::SubType Media::subType() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
521 522 523 524
{
    return m_subType;
}

525
void Media::setType( Type type )
526
{
527
    if ( m_type == type )
528
        return;
529
    m_type = type;
530
    m_changed = true;
531 532
}

533
const std::string& Media::title() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
534
{
535
    return m_title;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
536 537
}

538 539 540 541 542
bool Media::setTitle( const std::string& title )
{
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET title = ? WHERE id_media = ?";
    if ( m_title == title )
        return true;
543 544 545 546 547 548 549 550
    try
    {
        if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, title, m_id ) == false )
            return false;
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to set media title: ", ex.what() );
551
        return false;
552 553
    }

554 555 556 557
    m_title = title;
    return true;
}

558
void Media::setTitleBuffered( const std::string& title )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
559
{
560 561
    if ( m_title == title )
        return;
562
    m_title = title;
563
    m_changed = true;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
564 565
}

566
void Media::createTable( sqlite::Connection* connection )
567
{
568 569 570 571 572
    std::string reqs[] = {
        #include "database/tables/Media_v14.sql"
    };
    for ( const auto& req : reqs )
        sqlite::Tools::executeRequest( connection, req );
573 574
}

575
void Media::createTriggers( sqlite::Connection* connection, uint32_t modelVersion )
576
{
577 578 579 580 581 582
    const std::string reqs[] = {
        #include "database/tables/Media_triggers_v14.sql"
    };

    for ( const auto& req : reqs )
        sqlite::Tools::executeRequest( connection, req );
583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603

    if ( modelVersion >= 14 )
    {
        sqlite::Tools::executeRequest( connection,
            "CREATE TRIGGER IF NOT EXISTS increment_media_nb_playlist AFTER INSERT ON "
            " PlaylistMediaRelation "
            " BEGIN "
                " UPDATE " + policy::MediaTable::Name + " SET nb_playlists = nb_playlists + 1 "
                    " WHERE id_media = new.media_id;"
            " END;"
        );

        sqlite::Tools::executeRequest( connection,
            "CREATE TRIGGER IF NOT EXISTS decrement_media_nb_playlist AFTER DELETE ON "
            " PlaylistMediaRelation "
            " BEGIN "
                " UPDATE " + policy::MediaTable::Name + " SET nb_playlists = nb_playlists - 1 "
                    " WHERE id_media = old.media_id;"
            " END;"
        );
    }
604
}
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
605

606
bool Media::addLabel( LabelPtr label )
607
{
608
    if ( m_id == 0 || label->id() == 0 )
609
    {
610
        LOG_ERROR( "Both file & label need to be inserted in database before being linked together" );
611 612
        return false;
    }
613 614
    try
    {
615 616 617 618 619 620 621 622 623 624 625 626 627
        return sqlite::Tools::withRetries( 3, [this]( LabelPtr label ) {
            auto t = m_ml->getConn()->newTransaction();

            const char* req = "INSERT INTO LabelFileRelation VALUES(?, ?)";
            if ( sqlite::Tools::executeInsert( m_ml->getConn(), req, label->id(), m_id ) == 0 )
                return false;
            const std::string reqFts = "UPDATE " + policy::MediaTable::Name + "Fts "
                "SET labels = labels || ' ' || ? WHERE rowid = ?";
            if ( sqlite::Tools::executeUpdate( m_ml->getConn(), reqFts, label->name(), m_id ) == false )
                return false;
            t->commit();
            return true;
        }, std::move( label ) );
628 629 630 631
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add label: ", ex.what() );
632
        return false;
633
    }
634 635
}

636
bool Media::removeLabel( LabelPtr label )
637
{
638
    if ( m_id == 0 || label->id() == 0 )
639
    {
640
        LOG_ERROR( "Can't unlink a label/file not inserted in database" );
641 642
        return false;
    }
643 644
    try
    {
645 646 647 648 649 650 651 652 653 654 655 656 657
        return sqlite::Tools::withRetries( 3, [this]( LabelPtr label ) {
            auto t = m_ml->getConn()->newTransaction();

            const char* req = "DELETE FROM LabelFileRelation WHERE label_id = ? AND media_id = ?";
            if ( sqlite::Tools::executeDelete( m_ml->getConn(), req, label->id(), m_id ) == false )
                return false;
            const std::string reqFts = "UPDATE " + policy::MediaTable::Name + "Fts "
                    "SET labels = TRIM(REPLACE(labels, ?, '')) WHERE rowid = ?";
            if ( sqlite::Tools::executeUpdate( m_ml->getConn(), reqFts, label->name(), m_id ) == false )
                return false;
            t->commit();
            return true;
        }, std::move( label ) );
658 659 660 661
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to remove label: ", ex.what() );
662
        return false;
663
    }
664
}
665

666
Query<IMedia> Media::search( MediaLibraryPtr ml, const std::string& title,
667
                             const QueryParameters* params )
668
{
669
    std::string req = "FROM " + policy::MediaTable::Name + " m "
670 671 672
            " INNER JOIN " + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " WHERE"
            " m.id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts"
673
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
674
            " AND f.is_present = 1"
675
            " AND f.type = ?"
676
            " AND m.type != ? AND m.type != ?";
677
    req += sortRequest( params );
678
    return make_query<Media, IMedia>( ml, "m.*", req, title, File::Type::Main,
679
                                      Media::Type::External, Media::Type::Stream );
680
}
681

682 683 684 685 686 687 688 689 690 691 692 693 694 695 696
Query<IMedia> Media::search( MediaLibraryPtr ml, const std::string& title,
                             Media::Type type, const QueryParameters* params )
{
    std::string req = "FROM " + policy::MediaTable::Name + " m "
            " INNER JOIN " + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " WHERE"
            " m.id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts"
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
            " AND f.is_present = 1"
            " AND f.type = ?"
            " AND m.type = ?";
    req += sortRequest( params );
    return make_query<Media, IMedia>( ml, "m.*", req, title, File::Type::Main, type );
}

697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713
Query<IMedia> Media::searchAlbumTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t albumId, const QueryParameters* params)
{
    std::string req = "FROM " + policy::MediaTable::Name + " m "
            " INNER JOIN " + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " INNER JOIN " + policy::AlbumTrackTable::Name + " tra ON tra.media_id = m.id_media "
            " WHERE"
            " m.id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts"
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
            " AND tra.album_id = ?"
            " AND f.is_present = 1"
            " AND f.type = ?"
            " AND m.subtype = ?";
    req += sortRequest( params );
    return make_query<Media, IMedia>( ml, "m.*", req, pattern, albumId,
                                      File::Type::Main, Media::SubType::AlbumTrack );
}

714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730
Query<IMedia> Media::searchArtistTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t artistId, const QueryParameters* params)
{
    std::string req = "FROM " + policy::MediaTable::Name + " m "
            " INNER JOIN " + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " INNER JOIN " + policy::AlbumTrackTable::Name + " tra ON tra.media_id = m.id_media "
            " WHERE"
            " m.id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts"
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
            " AND tra.artist_id = ?"
            " AND f.is_present = 1"
            " AND f.type = ?"
            " AND m.subtype = ?";
    req += sortRequest( params );
    return make_query<Media, IMedia>( ml, "m.*", req, pattern, artistId,
                                      File::Type::Main, Media::SubType::AlbumTrack );
}

731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747
Query<IMedia> Media::searchGenreTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t genreId, const QueryParameters* params)
{
    std::string req = "FROM " + policy::MediaTable::Name + " m "
            " INNER JOIN " + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " INNER JOIN " + policy::AlbumTrackTable::Name + " tra ON tra.media_id = m.id_media "
            " WHERE"
            " m.id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts"
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
            " AND tra.genre_id = ?"
            " AND f.is_present = 1"
            " AND f.type = ?"
            " AND m.subtype = ?";
    req += sortRequest( params );
    return make_query<Media, IMedia>( ml, "m.*", req, pattern, genreId,
                                      File::Type::Main, Media::SubType::AlbumTrack );
}

748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765
Query<IMedia> Media::searchShowEpisodes(MediaLibraryPtr ml, const std::string& pattern,
                                        int64_t showId, const QueryParameters* params)
{
    std::string req = "FROM " + policy::MediaTable::Name + " m "
            " INNER JOIN " + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " INNER JOIN " + policy::ShowEpisodeTable::Name + " ep ON ep.media_id = m.id_media "
            " WHERE"
            " m.id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts"
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
            " AND ep.show_id = ?"
            " AND f.is_present = 1"
            " AND f.type = ?"
            " AND m.subtype = ?";
    req += sortRequest( params );
    return make_query<Media, IMedia>( ml, "m.*", req, pattern, showId,
                                      File::Type::Main, Media::SubType::ShowEpisode );
}

766 767 768 769 770 771 772 773 774 775 776 777 778
Query<IMedia> Media::searchInPlaylist( MediaLibraryPtr ml, const std::string& pattern,
                                       int64_t playlistId, const QueryParameters* params )
{
    std::string req = "FROM " + policy::MediaTable::Name + " m "
           "INNER JOIN " + policy::FileTable::Name + " f ON m.id_media = f.media_id "
           "LEFT JOIN PlaylistMediaRelation pmr ON pmr.media_id = m.id_media "
           "WHERE pmr.playlist_id = ? AND m.is_present != 0 AND "
           "m.id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts "
           "WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')";
    req += sortRequest( params );
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ), playlistId, pattern );
}

779
Query<IMedia> Media::fetchHistory( MediaLibraryPtr ml )
780
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
781 782 783
    static const std::string req = "FROM " + policy::MediaTable::Name +
            " WHERE last_played_date IS NOT NULL"
            " AND type != ?"
784
            " ORDER BY last_played_date DESC";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
785 786 787 788 789 790 791 792
    return make_query<Media, IMedia>( ml, "*", req, IMedia::Type::Stream );
}

Query<IMedia> Media::fetchStreamHistory(MediaLibraryPtr ml)
{
    static const std::string req = "FROM " + policy::MediaTable::Name +
            " WHERE last_played_date IS NOT NULL"
            " AND type = ?"
793
            " ORDER BY last_played_date DESC";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
794
    return make_query<Media, IMedia>( ml, "*", req, IMedia::Type::Stream );
795
}
796

797
void Media::clearHistory( MediaLibraryPtr ml )
798 799
{
    auto dbConn = ml->getConn();
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
800
    auto t = dbConn->newTransaction();
801 802
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
            "play_count = 0,"
803
            "last_played_date = NULL";
804 805
    // Clear the entire cache since quite a few items are now containing invalid info.
    clear();
806 807 808 809 810

    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
    Metadata::unset( dbConn, IMetadata::EntityType::Media,
                     static_cast<MDType>( IMedia::MetadataType::Progress ) );

811
    sqlite::Tools::executeUpdate( dbConn, req );