Media.cpp 33.4 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>
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 "Chapter.h"
38
#include "Device.h"
39
#include "Media.h"
40
#include "File.h"
41
#include "Folder.h"
42
#include "Label.h"
43
#include "logging/Logger.h"
44
#include "Movie.h"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
45
#include "ShowEpisode.h"
46
#include "SubtitleTrack.h"
47

48
#include "database/SqliteTools.h"
49
#include "database/SqliteQuery.h"
50
#include "VideoTrack.h"
51 52 53
#include "medialibrary/filesystem/IFile.h"
#include "medialibrary/filesystem/IDirectory.h"
#include "medialibrary/filesystem/IDevice.h"
54
#include "utils/Filename.h"
55

56 57 58
namespace medialibrary
{

59 60 61
const std::string Media::Table::Name = "Media";
const std::string Media::Table::PrimaryKeyColumn = "id_media";
int64_t Media::* const Media::Table::PrimaryKey = &Media::m_id;
62

63 64
Media::Media( MediaLibraryPtr ml, sqlite::Row& row )
    : m_ml( ml )
65 66 67 68 69 70 71 72 73 74 75
    // DB field extraction:
    , m_id( row.load<decltype(m_id)>( 0 ) )
    , m_type( row.load<decltype(m_type)>( 1 ) )
    , m_subType( row.load<decltype(m_subType)>( 2 ) )
    , m_duration( row.load<decltype(m_duration)>( 3 ) )
    , m_playCount( row.load<decltype(m_playCount)>( 4 ) )
    , m_lastPlayedDate( row.load<decltype(m_lastPlayedDate)>( 5 ) )
    // skip real_last_played_date as we don't need it in memory
    , m_insertionDate( row.load<decltype(m_insertionDate)>( 7 ) )
    , m_releaseDate( row.load<decltype(m_releaseDate)>( 8 ) )
    , m_thumbnailId( row.load<decltype(m_thumbnailId)>( 9 ) )
76 77 78
    , m_title( row.load<decltype(m_title)>( 10 ) )
    , m_filename( row.load<decltype(m_filename)>( 11 ) )
    , m_isFavorite( row.load<decltype(m_isFavorite)>( 12 ) )
79 80
    // Skip is_present
    // Skip device_id
81
    , m_nbPlaylists( row.load<unsigned int>( 15 ) )
82 83
    // Skip folder_id if any field gets added afterward

84
    // End of DB fields extraction
85
    , m_metadata( m_ml, IMetadata::EntityType::Media )
86
    , m_changed( false )
87
{
88 89
}

90 91 92
Media::Media( MediaLibraryPtr ml, const std::string& title, Type type )
    : m_ml( ml )
    , m_id( 0 )
93
    , m_type( type )
94
    , m_subType( SubType::Unknown )
95
    , m_duration( -1 )
96
    , m_playCount( 0 )
97
    , m_lastPlayedDate( 0 )
98
    , m_insertionDate( time( nullptr ) )
99
    , m_releaseDate( 0 )
100
    , m_thumbnailId( 0 )
101
    , m_title( title )
102
    // When creating a Media, meta aren't parsed, and therefor, the title is the filename
103
    , m_filename( title )
104
    , m_isFavorite( false )
105
    , m_nbPlaylists( 0 )
106
    , m_metadata( m_ml, IMetadata::EntityType::Media )
107
    , m_changed( false )
108
{
109 110
}

111
std::shared_ptr<Media> Media::create( MediaLibraryPtr ml, Type type,
112 113
                                      int64_t deviceId, int64_t folderId,
                                      const std::string& fileName )
114
{
115
    auto self = std::make_shared<Media>( ml, fileName, type );
116
    static const std::string req = "INSERT INTO " + Media::Table::Name +
117 118
            "(type, insertion_date, title, filename, device_id, folder_id) "
            "VALUES(?, ?, ?, ?, ?, ?)";
119

120
    if ( insert( ml, self, req, type, self->m_insertionDate, self->m_title,
121 122
                 self->m_filename, sqlite::ForeignKey{ deviceId },
                 sqlite::ForeignKey{ folderId } ) == false )
123
        return nullptr;
124
    return self;
125 126
}

127
AlbumTrackPtr Media::albumTrack() const
128
{
129 130
    if ( m_subType != SubType::AlbumTrack )
        return nullptr;
131
    if ( m_albumTrack == nullptr )
132
        m_albumTrack = AlbumTrack::fromMedia( m_ml, m_id );
133
    return m_albumTrack;
134 135
}

136
void Media::setAlbumTrack( AlbumTrackPtr albumTrack )
137 138
{
    m_albumTrack = albumTrack;
139 140
    m_subType = SubType::AlbumTrack;
    m_changed = true;
141 142
}

143
int64_t Media::duration() const
144 145 146 147
{
    return m_duration;
}

148
void Media::setDuration( int64_t duration )
149
{
150 151
    if ( m_duration == duration )
        return;
152
    m_duration = duration;
153
    m_changed = true;
154 155
}

156
ShowEpisodePtr Media::showEpisode() const
157
{
158 159
    if ( m_subType != SubType::ShowEpisode )
        return nullptr;
160

161
    if ( m_showEpisode == nullptr )
162
        m_showEpisode = ShowEpisode::fromMedia( m_ml, m_id );
163
    return m_showEpisode;
164 165
}

166
void Media::setShowEpisode( ShowEpisodePtr episode )
167
{
168
    m_showEpisode = episode;
169 170
    m_subType = SubType::ShowEpisode;
    m_changed = true;
171 172
}

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

181
uint32_t Media::playCount() const
182
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
183
    return m_playCount;
184 185
}

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

200 201 202 203 204 205 206 207 208 209
bool Media::setPlayCount( uint32_t playCount )
{
    static const std::string req = "UPDATE " + Media::Table::Name + " SET "
            "play_count = ? WHERE id_media = ?";
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, playCount, m_id ) == false )
        return false;
    m_playCount = playCount;
    return true;
}

210 211 212 213 214
time_t Media::lastPlayedDate() const
{
    return m_lastPlayedDate;
}

215 216 217 218 219
bool Media::isFavorite() const
{
    return m_isFavorite;
}

220
bool Media::setFavorite( bool favorite )
221
{
222
    static const std::string req = "UPDATE " + Media::Table::Name + " SET is_favorite = ? WHERE id_media = ?";
223
    if ( m_isFavorite == favorite )
224 225 226
        return true;
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, favorite, m_id ) == false )
        return false;
227
    m_isFavorite = favorite;
228
    return true;
229 230
}

231
const std::vector<FilePtr>& Media::files() const
232
{
233
    if ( m_files.empty() == true )
234
    {
235
        static const std::string req = "SELECT * FROM " + File::Table::Name
236
                + " WHERE media_id = ?";
237
        m_files = File::fetchAll<IFile>( m_ml, req, m_id );
238 239
    }
    return m_files;
240 241
}

242 243 244 245 246
const std::string& Media::fileName() const
{
    return m_filename;
}

247
MoviePtr Media::movie() const
248
{
249 250 251
    if ( m_subType != SubType::Movie )
        return nullptr;

252
    if ( m_movie == nullptr )
253
        m_movie = Movie::fromMedia( m_ml, m_id );
254
    return m_movie;
255 256
}

257
void Media::setMovie(MoviePtr movie)
258 259
{
    m_movie = movie;
260 261
    m_subType = SubType::Movie;
    m_changed = true;
262 263
}

264
bool Media::addVideoTrack( const std::string& codec, unsigned int width, unsigned int height,
265
                           uint32_t fpsNum, uint32_t fpsDen, uint32_t bitrate,
266 267
                           uint32_t sarNum, uint32_t sarDen, const std::string& language,
                           const std::string& description )
268
{
269
    return VideoTrack::create( m_ml, codec, width, height, fpsNum, fpsDen,
270
                               bitrate, sarNum, sarDen, m_id, language, description ) != nullptr;
271 272
}

273
Query<IVideoTrack> Media::videoTracks() const
274
{
275
    static const std::string req = "FROM " + VideoTrack::Table::Name +
276
            " WHERE media_id = ?";
277
    return make_query<VideoTrack, IVideoTrack>( m_ml, "*", req, "", m_id );
278 279
}

280
bool Media::addAudioTrack( const std::string& codec, unsigned int bitrate,
281 282
                          unsigned int sampleRate, unsigned int nbChannels,
                          const std::string& language, const std::string& desc )
283
{
284
    return AudioTrack::create( m_ml, codec, bitrate, sampleRate, nbChannels, language, desc, m_id ) != nullptr;
285 286
}

287 288 289 290 291 292 293 294
bool Media::addSubtitleTrack( std::string codec, std::string language,
                              std::string description, std::string encoding )
{
    return SubtitleTrack::create( m_ml, std::move( codec ), std::move( language ),
                                  std::move( description ), std::move( encoding ),
                                  m_id ) != nullptr;
}

295
Query<IAudioTrack> Media::audioTracks() const
296
{
297
    static const std::string req = "FROM " + AudioTrack::Table::Name +
298
            " WHERE media_id = ?";
299
    return make_query<AudioTrack, IAudioTrack>( m_ml, "*", req, "", m_id );
300 301
}

302 303 304 305 306 307 308
Query<ISubtitleTrack> Media::subtitleTracks() const
{
    static const std::string req = "FROM " + SubtitleTrack::Table::Name +
            " WHERE media_id = ?";
    return make_query<SubtitleTrack, ISubtitleTrack>( m_ml, "*", req, "", m_id );
}

309 310 311 312 313 314 315 316 317 318 319
Query<IChapter> Media::chapters( const QueryParameters* params ) const
{
    return Chapter::fromMedia( m_ml, m_id, params );
}

bool Media::addChapter(int64_t offset, int64_t duration, std::string name)
{
    return Chapter::create( m_ml, offset, duration, std::move( name ),
                            m_id ) != nullptr;
}

320
const std::string& Media::thumbnail() const
321
{
322
    if ( isThumbnailGenerated() == false )
323 324
        return Thumbnail::EmptyMrl;

325
    if ( m_thumbnail == nullptr )
326 327 328 329 330 331
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return Thumbnail::EmptyMrl;
        m_thumbnail = std::move( thumbnail );
    }
332 333
    if ( m_thumbnail->isValid() == false )
        return Thumbnail::EmptyMrl;
334
    return m_thumbnail->mrl();
335 336 337 338
}

bool Media::isThumbnailGenerated() const
{
339
    return m_thumbnailId != 0;
340 341
}

342 343
unsigned int Media::insertionDate() const
{
344
    return static_cast<unsigned int>( m_insertionDate );
345 346
}

347 348 349 350 351
unsigned int Media::releaseDate() const
{
    return m_releaseDate;
}

352 353 354 355 356 357 358 359 360 361 362
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 );
}

363
const IMetadata& Media::metadata( IMedia::MetadataType type ) const
364
{
365
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
366 367
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
368
    return m_metadata.get( static_cast<MDType>( type ) );
369 370 371 372
}

bool Media::setMetadata( IMedia::MetadataType type, const std::string& value )
{
373
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
374 375
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
376
    return m_metadata.set( static_cast<MDType>( type ), value );
377 378 379 380
}

bool Media::setMetadata( IMedia::MetadataType type, int64_t value )
{
381
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
382 383
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
384
    return m_metadata.set( static_cast<MDType>( type ), value );
385 386
}

387 388 389 390 391 392 393 394
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 ) );
}

395 396 397 398 399 400 401 402
void Media::setReleaseDate( unsigned int date )
{
    if ( m_releaseDate == date )
        return;
    m_releaseDate = date;
    m_changed = true;
}

403 404
bool Media::setThumbnail( const std::string& thumbnailMrl, Thumbnail::Origin origin,
                          bool isGenerated )
405
{
406
    if ( m_thumbnailId != 0 )
407
        return Thumbnail::setMrlFromPrimaryKey( m_ml, m_thumbnail, m_thumbnailId,
408
                                                thumbnailMrl, origin );
409

410 411 412
    std::unique_ptr<sqlite::Transaction> t;
    if ( sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();
413 414 415 416 417 418 419 420 421
    std::shared_ptr<Thumbnail> thumbnail;
    if ( thumbnailMrl.empty() )
    {
        assert( origin == Thumbnail::Origin::Media );
        assert( isGenerated == true );
        thumbnail = Thumbnail::createForFailure( m_ml );
    }
    else
        thumbnail = Thumbnail::create( m_ml, thumbnailMrl, origin, isGenerated );
422
    if ( thumbnail == nullptr )
423
        return false;
424

425
    static const std::string req = "UPDATE " + Media::Table::Name + " SET "
426
            "thumbnail_id = ? WHERE id_media = ?";
427
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, thumbnail->id(), m_id ) == false )
428
        return false;
429 430
    m_thumbnailId = thumbnail->id();
    m_thumbnail = std::move( thumbnail );
431 432
    if ( t != nullptr )
        t->commit();
433 434 435
    return true;
}

436 437
bool Media::setThumbnail( const std::string& thumbnailMrl )
{
438
    return setThumbnail( thumbnailMrl, Thumbnail::Origin::UserProvided, false );
439 440
}

441 442
bool Media::save()
{
443
    static const std::string req = "UPDATE " + Media::Table::Name + " SET "
444
            "type = ?, subtype = ?, duration = ?, release_date = ?,"
445
            "title = ? WHERE id_media = ?";
446 447
    if ( m_changed == false )
        return true;
448
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_type, m_subType, m_duration,
449
                                       m_releaseDate, m_title, m_id ) == false )
450 451 452 453
    {
        return false;
    }
    m_changed = false;
454 455 456
    return true;
}

457 458
std::shared_ptr<File> Media::addFile( const fs::IFile& fileFs, int64_t parentFolderId,
                                      bool isFolderFsRemovable, IFile::Type type )
459
{
460
    return File::createFromMedia( m_ml, m_id, type, fileFs, parentFolderId, isFolderFsRemovable);
461 462
}

463
FilePtr Media::addExternalMrl( const std::string& mrl , IFile::Type type )
464
{
465 466
    try
    {
467
        return File::createFromMedia( m_ml, m_id, type, mrl );
468 469 470 471 472 473
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add media external MRL: ", ex.what() );
        return nullptr;
    }
474 475
}

476
void Media::removeFile( File& file )
477
{
478
    file.destroy();
479
    auto it = std::remove_if( begin( m_files ), end( m_files ), [&file]( const FilePtr& f ) {
480
        return f->id() == file.id();
481 482
    });
    if ( it != end( m_files ) )
483
        m_files.erase( it );
484 485
}

486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521
std::string Media::addRequestJoin( const QueryParameters* params, bool forceFile,
                                    bool forceAlbumTrack )
{
    bool albumTrack = forceAlbumTrack;
    bool artist = false;
    bool album = false;
    bool file = forceFile;
    auto sort = params != nullptr ? params->sort : SortingCriteria::Alpha;

    switch( sort )
    {
        case SortingCriteria::Default:
        case SortingCriteria::Alpha:
        case SortingCriteria::PlayCount:
        case SortingCriteria::Duration:
        case SortingCriteria::InsertionDate:
        case SortingCriteria::ReleaseDate:
        case SortingCriteria::Filename:
            /* All those are stored in the media itself */
            break;
        case SortingCriteria::LastModificationDate:
        case SortingCriteria::FileSize:
            file = true;
            break;
        case SortingCriteria::Artist:
            artist = true;
            albumTrack = true;
            break;
        case SortingCriteria::Album:
            /* We need the album track to get the album id & the album for its title */
            albumTrack = true;
            album = true;
            break;
        case SortingCriteria::TrackNumber:
            albumTrack = true;
            break;
522 523
        case SortingCriteria::NbAudio:
        case SortingCriteria::NbVideo:
524 525 526
        case SortingCriteria::NbMedia:
            // Unrelated to media requests
            break;
527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550
    }
    std::string req;
    // Use "LEFT JOIN to allow for ordering different media type
    // For instance ordering by albums on all media would not fetch the video if
    // we were using INNER JOIN
    if ( albumTrack == true )
        req += " LEFT JOIN " + AlbumTrack::Table::Name + " att ON m.id_media = att.media_id ";
    if ( album == true )
    {
        assert( albumTrack == true );
        req += " LEFT JOIN " + Album::Table::Name + " alb ON att.album_id = alb.id_album ";
    }
    if ( artist == true )
    {
        assert( albumTrack == true );
        req += " LEFT JOIN " + Artist::Table::Name + " art ON att.artist_id = art.id_artist ";
    }
    if ( file == true )
        req += " LEFT JOIN " + File::Table::Name + " f ON m.id_media = f.media_id ";

    return req;
}

std::string Media::sortRequest( const QueryParameters* params )
551
{
552
    std::string req = " ORDER BY ";
553

554 555
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
556 557
    switch ( sort )
    {
558
    case SortingCriteria::Duration:
559
        req += "m.duration";
560
        break;
561
    case SortingCriteria::InsertionDate:
562
        req += "m.insertion_date";
563
        break;
564
    case SortingCriteria::ReleaseDate:
565
        req += "m.release_date";
566
        break;
567
    case SortingCriteria::PlayCount:
568
        req += "m.play_count";
569 570
        desc = !desc; // Make decreasing order default for play count sorting
        break;
571
    case SortingCriteria::Filename:
572
        req += "m.filename";
573 574 575 576 577 578
        break;
    case SortingCriteria::LastModificationDate:
        req += "f.last_modification_date";
        break;
    case SortingCriteria::FileSize:
        req += "f.size";
579
        break;
580 581
    case SortingCriteria::Album:
        if ( desc == true )
582
            req += "alb.title DESC, att.track_number";
583
        else
584 585 586 587 588 589 590 591 592 593
            req += "alb.title, att.track_number";
        break;
    case SortingCriteria::Artist:
        req += "art.name";
        break;
    case SortingCriteria::TrackNumber:
        if ( desc == true )
            req += "att.track_number DESC, att.disc_number";
        else
            req += "att.track_number, att.disc_number";
594
        break;
595
    default:
596 597 598 599
        LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Default (Alpha)" );
        /* fall-through */
    case SortingCriteria::Default:
    case SortingCriteria::Alpha:
600
        req += "m.title";
601 602
        break;
    }
603
    if ( desc == true && sort != SortingCriteria::Album )
604
        req += " DESC";
605 606
    return req;
}
607

608
Query<IMedia> Media::listAll( MediaLibraryPtr ml, IMedia::Type type,
609
                              const QueryParameters* params )
610
{
611
    std::string req = "FROM " + Media::Table::Name + " m ";
612

613
    req += addRequestJoin( params, true, false );
614
    req +=  " WHERE m.type = ?"
615
            " AND (f.type = ? OR f.type = ?)"
616
            " AND m.is_present != 0";
617 618

    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
619 620
                                      sortRequest( params ), type,
                                      IFile::Type::Main, IFile::Type::Disc );
621 622
}

623
int64_t Media::id() const
624 625 626 627
{
    return m_id;
}

628
IMedia::Type Media::type() const
629 630 631 632
{
    return m_type;
}

633
IMedia::SubType Media::subType() const
634 635 636 637
{
    return m_subType;
}

638 639 640 641 642 643 644 645
void Media::setSubType( IMedia::SubType subType )
{
    if ( subType == m_subType )
        return;
    m_subType = subType;
    m_changed = true;
}

646 647 648 649 650 651 652 653
void Media::setType( Type type )
{
    if ( m_type == type )
        return;
    m_type = type;
    m_changed = true;
}

654
const std::string& Media::title() const
655
{
656
    return m_title;
657 658
}

659 660
bool Media::setTitle( const std::string& title )
{
661
    static const std::string req = "UPDATE " + Media::Table::Name + " SET title = ? WHERE id_media = ?";
662 663
    if ( m_title == title )
        return true;
664 665 666 667 668 669 670 671
    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() );
672
        return false;
673 674
    }

675 676 677 678
    m_title = title;
    return true;
}

679
void Media::setTitleBuffered( const std::string& title )
680
{
681 682
    if ( m_title == title )
        return;
683
    m_title = title;
684
    m_changed = true;
685 686
}

687 688 689 690 691 692 693 694 695
void Media::setFileName( std::string fileName )
{
    if ( fileName == m_filename )
        return;
    static const std::string req = "UPDATE " + Media::Table::Name + " SET filename = ? WHERE id_media = ?";
    sqlite::Tools::executeUpdate( m_ml->getConn(), req, fileName, m_id );
    m_filename = std::move( fileName );
}

696
void Media::createTable( sqlite::Connection* connection, uint32_t modelVersion )
697
{
698 699 700 701 702
    std::string reqs[] = {
        #include "database/tables/Media_v14.sql"
    };
    for ( const auto& req : reqs )
        sqlite::Tools::executeRequest( connection, req );
703 704 705 706 707 708 709 710
    if ( modelVersion >= 14 )
    {
        // Don't create this index before model 14, as the real_last_played_date
        // was introduced in model version 14
        const auto req = "CREATE INDEX IF NOT EXISTS media_last_usage_dates_idx ON " + Media::Table::Name +
                "(last_played_date, real_last_played_date, insertion_date)";
        sqlite::Tools::executeRequest( connection, req );
    }
711 712
}

713
void Media::createTriggers( sqlite::Connection* connection, uint32_t modelVersion )
714
{
715 716 717 718 719 720
    const std::string reqs[] = {
        #include "database/tables/Media_triggers_v14.sql"
    };

    for ( const auto& req : reqs )
        sqlite::Tools::executeRequest( connection, req );
721 722 723 724 725 726 727

    if ( modelVersion >= 14 )
    {
        sqlite::Tools::executeRequest( connection,
            "CREATE TRIGGER IF NOT EXISTS increment_media_nb_playlist AFTER INSERT ON "
            " PlaylistMediaRelation "
            " BEGIN "
728
                " UPDATE " + Media::Table::Name + " SET nb_playlists = nb_playlists + 1 "
729 730 731 732 733 734 735 736
                    " 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 "
737
                " UPDATE " + Media::Table::Name + " SET nb_playlists = nb_playlists - 1 "
738 739 740 741
                    " WHERE id_media = old.media_id;"
            " END;"
        );
    }
742
}
743

744
bool Media::addLabel( LabelPtr label )
745
{
746
    if ( m_id == 0 || label->id() == 0 )
747
    {
748
        LOG_ERROR( "Both file & label need to be inserted in database before being linked together" );
749 750
        return false;
    }
751 752
    try
    {
753 754 755 756 757 758
        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;
759
            const std::string reqFts = "UPDATE " + Media::Table::Name + "Fts "
760 761 762 763 764 765
                "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 ) );
766 767 768 769
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add label: ", ex.what() );
770
        return false;
771
    }
772 773
}

774
bool Media::removeLabel( LabelPtr label )
775
{
776
    if ( m_id == 0 || label->id() == 0 )
777
    {
778
        LOG_ERROR( "Can't unlink a label/file not inserted in database" );
779 780
        return false;
    }
781 782
    try
    {
783 784 785 786 787 788
        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;
789
            const std::string reqFts = "UPDATE " + Media::Table::Name + "Fts "
790 791 792 793 794 795
                    "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 ) );
796 797 798 799
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to remove label: ", ex.what() );
800
        return false;
801
    }
802
}
803

804
Query<IMedia> Media::search( MediaLibraryPtr ml, const std::string& title,
805
                             const QueryParameters* params )
806
{
807 808 809 810 811
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, false );

    req +=  " WHERE"
812 813
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
814
            " AND m.is_present = 1"
815
            " AND (f.type = ? OR f.type = ?)"
816
            " AND m.type != ? AND m.type != ?";
817
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
818
                                      sortRequest( params ), title,
819
                                      File::Type::Main, File::Type::Disc,
820
                                      Media::Type::External, Media::Type::Stream );
821
}
822

823 824 825
Query<IMedia> Media::search( MediaLibraryPtr ml, const std::string& title,
                             Media::Type type, const QueryParameters* params )
{
826 827 828 829
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, false );
    req +=  " WHERE"
830 831
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
832
            " AND m.is_present = 1"
833
            " AND (f.type = ? OR f.type = ?)"
834
            " AND m.type = ?";
835
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
836
                                      sortRequest( params ), title,
837 838
                                      File::Type::Main, File::Type::Disc,
                                      type );
839 840
}

841 842
Query<IMedia> Media::searchAlbumTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t albumId, const QueryParameters* params)
{
843 844 845 846
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, true );
    req +=  " WHERE"
847 848
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
849
            " AND att.album_id = ?"
850
            " AND m.is_present = 1"
851 852
            " AND f.type = ?"
            " AND m.subtype = ?";
853
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
854
                                      sortRequest( params ), pattern, albumId,
855 856 857
                                      File::Type::Main, Media::SubType::AlbumTrack );
}

858 859
Query<IMedia> Media::searchArtistTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t artistId, const QueryParameters* params)
{
860 861 862 863 864
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, true );

    req +=  " WHERE"
865 866
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
867
            " AND att.artist_id = ?"
868
            " AND m.is_present = 1"
869 870
            " AND f.type = ?"
            " AND m.subtype = ?";
871
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
872
                                      sortRequest( params ), pattern, artistId,
873 874 875
                                      File::Type::Main, Media::SubType::AlbumTrack );
}

876 877
Query<IMedia> Media::searchGenreTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t genreId, const QueryParameters* params)
{
878 879 880 881 882
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, true );

    req +=  " WHERE"
883 884
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
885
            " AND att.genre_id = ?"
886
            " AND m.is_present = 1"
887 888
            " AND f.type = ?"
            " AND m.subtype = ?";
889
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
890
                                      sortRequest( params ), pattern, genreId,
891 892 893
                                      File::Type::Main, Media::SubType::AlbumTrack );
}

894 895 896
Query<IMedia> Media::searchShowEpisodes(MediaLibraryPtr ml, const std::string& pattern,
                                        int64_t showId, const QueryParameters* params)
{
897 898 899 900 901
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, false );

    req +=  " INNER JOIN " + ShowEpisode::Table::Name + " ep ON ep.media_id = m.id_media "
902
            " WHERE"
903 904
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
905
            " AND ep.show_id = ?"
906
            " AND m.is_present = 1"
907 908
            " AND f.type = ?"
            " AND m.subtype = ?";
909
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
910
                                      sortRequest( params ), pattern, showId,
911 912 913
                                      File::Type::Main, Media::SubType::ShowEpisode );
}

914 915 916
Query<IMedia> Media::searchInPlaylist( MediaLibraryPtr ml, const std::string& pattern,
                                       int64_t playlistId, const QueryParameters* params )
{
917 918 919 920 921
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, false );

    req += "LEFT JOIN PlaylistMediaRelation pmr ON pmr.media_id = m.id_media "
922
           "WHERE pmr.playlist_id = ? AND m.is_present != 0 AND "
923 924
           "m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts "
           "WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')";
925
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
926
                                      sortRequest( params ), playlistId, pattern );
927 928
}

929
Query<IMedia> Media::fetchHistory( MediaLibraryPtr ml )
930
{
931
    static const std::string req = "FROM " + Media::Table::Name +
932
            " WHERE last_played_date IS NOT NULL"
933 934 935 936
            " AND type != ?";
    return make_query<Media, IMedia>( ml, "*", req,
                                      "ORDER BY last_played_date DESC",
                                      IMedia::Type::Stream );
937 938 939 940
}

Query<IMedia> Media::fetchStreamHistory(MediaLibraryPtr ml)
{
941
    static const std::string req = "FROM " + Media::Table::Name +
942
            " WHERE last_played_date IS NOT NULL"
943 944 945 946
            " AND type = ?";
    return make_query<Media, IMedia>( ml, "*", req,
                                      "ORDER BY last_played_date DESC",
                                      IMedia::Type::Stream );
947
}
948

949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968
Query<IMedia> Media::fromFolderId( MediaLibraryPtr ml, IMedia::Type type,
                                   int64_t folderId, const QueryParameters* params )
{
    // This assumes the folder is present, as folders are not expected to be
    // manipulated when the device is not present
    std::string req = "FROM " + Table::Name +  " m WHERE folder_id = ?";
    if ( type != Type::Unknown )
    {
        req += " AND type = ?";
        req += addRequestJoin( params, false, false );
        return make_query<Media, IMedia>( ml, "*", req, sortRequest( params ),
                                          folderId, type );
    }
    // Don't explicitely filter by type since only video/audio media have a
    // non NULL folder_id
    req += addRequestJoin( params, false, false );
    return make_query<Media, IMedia>( ml, "*", req, sortRequest( params ),
                                      folderId );
}

969
void Media::clearHistory( MediaLibraryPtr ml )
970 971
{
    auto dbConn = ml->getConn();
972
    auto t = dbConn->newTransaction();
973
    static const std::string req = "UPDATE " + Media::Table::Name + " SET "
974
            "play_count = 0,"
975
            "last_played_date = NULL";
976 977 978 979 980

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

981
    sqlite::Tools::executeUpdate( dbConn, req );
982
    t->commit();
983 984
}

985 986
void Media::removeOldMedia( MediaLibraryPtr ml, std::chrono::seconds maxLifeTime )
{
987 988 989
    // Media that were never played have a real_last_played_date = NULL, so they
    // won't match for real_last_played_date < X
    // However we need to take care about media that were inserted but never played
990
    const std::string req = "DELETE FROM " + Media::Table::Name + " "
991 992 993
            "WHERE ( real_last_played_date < ? OR "
                "( real_last_played_date IS NULL AND insertion_date < ? ) )"
            "AND ( type = ? OR type = ? ) "
994
            "AND nb_playlists = 0";
995 996 997
    auto deadline = std::chrono::duration_cast<std::chrono::seconds>(
                (std::chrono::system_clock::now() - maxLifeTime).time_since_epoch() );
    sqlite::Tools::executeDelete( ml->getConn(), req, deadline.count(),
998
                                  deadline.count(),
999 1000 1001
                                  IMedia::Type::External, IMedia::Type::Stream );
}

1002
}