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

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

54 55 56
namespace medialibrary
{

57 58 59
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;
60

61 62
Media::Media( MediaLibraryPtr ml, sqlite::Row& row )
    : m_ml( ml )
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
    // 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 ) )
    , m_thumbnailGenerated( row.load<decltype(m_thumbnailGenerated)>( 10 ) )
    , m_title( row.load<decltype(m_title)>( 11 ) )
    , m_filename( row.load<decltype(m_filename)>( 12 ) )
    , m_isFavorite( row.load<decltype(m_isFavorite)>( 13 ) )
78 79 80
    // Skip is_present
    // Skip device_id
    , m_nbPlaylists( row.load<unsigned int>( 16 ) )
81
    // End of DB fields extraction
82
    , m_metadata( m_ml, IMetadata::EntityType::Media )
83
    , m_changed( false )
84
{
85 86
}

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

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

117 118
    if ( insert( ml, self, req, type, self->m_insertionDate, self->m_title,
                 self->m_filename, sqlite::ForeignKey{ deviceId } ) == false )
119
        return nullptr;
120
    return self;
121 122
}

123
AlbumTrackPtr Media::albumTrack() const
124
{
125 126
    if ( m_subType != SubType::AlbumTrack )
        return nullptr;
127 128
    auto lock = m_albumTrack.lock();

129
    if ( m_albumTrack.isCached() == false )
130
        m_albumTrack = AlbumTrack::fromMedia( m_ml, m_id );
131
    return m_albumTrack.get();
132 133
}

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

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

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

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

160 161
    auto lock = m_showEpisode.lock();
    if ( m_showEpisode.isCached() == false )
162
        m_showEpisode = ShowEpisode::fromMedia( m_ml, m_id );
163
    return m_showEpisode.get();
164 165
}

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

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

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

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

201 202 203 204 205 206 207 208 209 210
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;
}

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

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

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

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

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

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

254 255
    auto lock = m_movie.lock();

256
    if ( m_movie.isCached() == false )
257
        m_movie = Movie::fromMedia( m_ml, m_id );
258
    return m_movie.get();
259 260
}

261
void Media::setMovie(MoviePtr movie)
262
{
263
    auto lock = m_movie.lock();
264
    m_movie = movie;
265 266
    m_subType = SubType::Movie;
    m_changed = true;
267 268
}

269
bool Media::addVideoTrack( const std::string& codec, unsigned int width, unsigned int height,
270
                           uint32_t fpsNum, uint32_t fpsDen, uint32_t bitrate,
271 272
                           uint32_t sarNum, uint32_t sarDen, const std::string& language,
                           const std::string& description )
273
{
274
    return VideoTrack::create( m_ml, codec, width, height, fpsNum, fpsDen,
275
                               bitrate, sarNum, sarDen, m_id, language, description ) != nullptr;
276 277
}

278
Query<IVideoTrack> Media::videoTracks() const
279
{
280
    static const std::string req = "FROM " + VideoTrack::Table::Name +
281
            " WHERE media_id = ?";
282
    return make_query<VideoTrack, IVideoTrack>( m_ml, "*", req, "", m_id );
283 284
}

285
bool Media::addAudioTrack( const std::string& codec, unsigned int bitrate,
286 287
                          unsigned int sampleRate, unsigned int nbChannels,
                          const std::string& language, const std::string& desc )
288
{
289
    return AudioTrack::create( m_ml, codec, bitrate, sampleRate, nbChannels, language, desc, m_id ) != nullptr;
290 291
}

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

299
const std::string& Media::thumbnail() const
300
{
301
    if ( m_thumbnailId == 0 || m_thumbnailGenerated == false )
302 303
        return Thumbnail::EmptyMrl;

304 305
    auto lock = m_thumbnail.lock();
    if ( m_thumbnail.isCached() == false )
306 307 308 309 310 311
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return Thumbnail::EmptyMrl;
        m_thumbnail = std::move( thumbnail );
    }
312 313 314 315 316 317
    return m_thumbnail.get()->mrl();
}

bool Media::isThumbnailGenerated() const
{
    return m_thumbnailGenerated;
318 319
}

320 321
unsigned int Media::insertionDate() const
{
322
    return static_cast<unsigned int>( m_insertionDate );
323 324
}

325 326 327 328 329
unsigned int Media::releaseDate() const
{
    return m_releaseDate;
}

330 331 332 333 334 335 336 337 338 339 340
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 );
}

341
const IMetadata& Media::metadata( IMedia::MetadataType type ) const
342
{
343
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
344 345
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
346
    return m_metadata.get( static_cast<MDType>( type ) );
347 348 349 350
}

bool Media::setMetadata( IMedia::MetadataType type, const std::string& value )
{
351
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
352 353
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
354
    return m_metadata.set( static_cast<MDType>( type ), value );
355 356 357 358
}

bool Media::setMetadata( IMedia::MetadataType type, int64_t value )
{
359
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
360 361
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
362
    return m_metadata.set( static_cast<MDType>( type ), value );
363 364
}

365 366 367 368 369 370 371 372
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 ) );
}

373 374 375 376 377 378 379 380
void Media::setReleaseDate( unsigned int date )
{
    if ( m_releaseDate == date )
        return;
    m_releaseDate = date;
    m_changed = true;
}

381
bool Media::setThumbnail( const std::string& thumbnailMrl, Thumbnail::Origin origin )
382
{
383
    if ( m_thumbnailId != 0 )
384
        return Thumbnail::setMrlFromPrimaryKey( m_ml, m_thumbnail, m_thumbnailId,
385
                                                thumbnailMrl, origin );
386

387 388 389 390
    std::unique_ptr<sqlite::Transaction> t;
    if ( sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();
    auto lock = m_thumbnail.lock();
391 392
    auto thumbnail = Thumbnail::create( m_ml, thumbnailMrl, origin );
    if ( thumbnail == nullptr )
393
        return false;
394

395
    static const std::string req = "UPDATE " + Media::Table::Name + " SET "
396
            "thumbnail_id = ?, thumbnail_generated = 1 WHERE id_media = ?";
397
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, thumbnail->id(), m_id ) == false )
398
        return false;
399
    m_thumbnailId = thumbnail->id();
400
    m_thumbnailGenerated = true;
401
    m_thumbnail = std::move( thumbnail );
402 403
    if ( t != nullptr )
        t->commit();
404 405 406
    return true;
}

407 408 409 410 411
bool Media::setThumbnail( const std::string& thumbnailMrl )
{
    return setThumbnail( thumbnailMrl, Thumbnail::Origin::UserProvided );
}

412 413
bool Media::save()
{
414
    static const std::string req = "UPDATE " + Media::Table::Name + " SET "
415
            "type = ?, subtype = ?, duration = ?, release_date = ?,"
416
            "title = ? WHERE id_media = ?";
417 418
    if ( m_changed == false )
        return true;
419
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_type, m_subType, m_duration,
420
                                       m_releaseDate, m_title, m_id ) == false )
421 422 423 424
    {
        return false;
    }
    m_changed = false;
425 426 427
    return true;
}

428 429
std::shared_ptr<File> Media::addFile( const fs::IFile& fileFs, int64_t parentFolderId,
                                      bool isFolderFsRemovable, IFile::Type type )
430
{
431
    auto file = File::createFromMedia( m_ml, m_id, type, fileFs, parentFolderId, isFolderFsRemovable);
432 433 434 435 436 437
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
438 439
}

440
FilePtr Media::addExternalMrl( const std::string& mrl , IFile::Type type )
441
{
442 443 444
    FilePtr file;
    try
    {
445
        file = File::createFromMedia( m_ml, m_id, type, mrl );
446 447 448 449 450 451 452
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add media external MRL: ", ex.what() );
        return nullptr;
    }

453 454 455 456 457 458 459 460
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
}

461
void Media::removeFile( File& file )
462
{
463 464 465
    file.destroy();
    auto lock = m_files.lock();
    if ( m_files.isCached() == false )
466
        return;
467
    m_files.get().erase( std::remove_if( begin( m_files.get() ), end( m_files.get() ), [&file]( const FilePtr& f ) {
468 469
        return f->id() == file.id();
    }));
470 471
}

472 473 474 475 476 477 478 479 480 481 482 483 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 522 523 524 525 526 527 528 529 530 531
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;
    }
    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 )
532
{
533
    std::string req = " ORDER BY ";
534

535 536
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
537 538
    switch ( sort )
    {
539
    case SortingCriteria::Duration:
540
        req += "m.duration";
541
        break;
542
    case SortingCriteria::InsertionDate:
543
        req += "m.insertion_date";
544
        break;
545
    case SortingCriteria::ReleaseDate:
546
        req += "m.release_date";
547
        break;
548
    case SortingCriteria::PlayCount:
549
        req += "m.play_count";
550 551
        desc = !desc; // Make decreasing order default for play count sorting
        break;
552
    case SortingCriteria::Filename:
553
        req += "m.filename";
554 555 556 557 558 559
        break;
    case SortingCriteria::LastModificationDate:
        req += "f.last_modification_date";
        break;
    case SortingCriteria::FileSize:
        req += "f.size";
560
        break;
561 562
    case SortingCriteria::Album:
        if ( desc == true )
563
            req += "alb.title DESC, att.track_number";
564
        else
565 566 567 568 569 570 571 572 573 574
            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";
575
        break;
576
    default:
577 578 579 580
        LOG_WARN( "Unsupported sorting criteria, falling back to SortingCriteria::Default (Alpha)" );
        /* fall-through */
    case SortingCriteria::Default:
    case SortingCriteria::Alpha:
581
        req += "m.title";
582 583
        break;
    }
584
    if ( desc == true && sort != SortingCriteria::Album )
585
        req += " DESC";
586 587
    return req;
}
588

589
Query<IMedia> Media::listAll( MediaLibraryPtr ml, IMedia::Type type,
590
                              const QueryParameters* params )
591
{
592
    std::string req = "FROM " + Media::Table::Name + " m ";
593

594
    req += addRequestJoin( params, true, false );
595
    req +=  " WHERE m.type = ?"
596
            " AND f.type = ?"
597
            " AND m.is_present != 0";
598 599

    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
600
                                      sortRequest( params ),
601
                                      type, IFile::Type::Main );
602 603
}

604
int64_t Media::id() const
605 606 607 608
{
    return m_id;
}

609
IMedia::Type Media::type() const
610 611 612 613
{
    return m_type;
}

614
IMedia::SubType Media::subType() const
615 616 617 618
{
    return m_subType;
}

619 620 621 622 623 624 625 626
void Media::setSubType( IMedia::SubType subType )
{
    if ( subType == m_subType )
        return;
    m_subType = subType;
    m_changed = true;
}

627 628 629 630 631 632 633 634
void Media::setType( Type type )
{
    if ( m_type == type )
        return;
    m_type = type;
    m_changed = true;
}

635
const std::string& Media::title() const
636
{
637
    return m_title;
638 639
}

640 641
bool Media::setTitle( const std::string& title )
{
642
    static const std::string req = "UPDATE " + Media::Table::Name + " SET title = ? WHERE id_media = ?";
643 644
    if ( m_title == title )
        return true;
645 646 647 648 649 650 651 652
    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() );
653
        return false;
654 655
    }

656 657 658 659
    m_title = title;
    return true;
}

660
void Media::setTitleBuffered( const std::string& title )
661
{
662 663
    if ( m_title == title )
        return;
664
    m_title = title;
665
    m_changed = true;
666 667
}

668 669 670 671 672 673 674 675 676
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 );
}

677
void Media::createTable( sqlite::Connection* connection, uint32_t modelVersion )
678
{
679 680 681 682 683
    std::string reqs[] = {
        #include "database/tables/Media_v14.sql"
    };
    for ( const auto& req : reqs )
        sqlite::Tools::executeRequest( connection, req );
684 685 686 687 688 689 690 691
    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 );
    }
692 693
}

694
void Media::createTriggers( sqlite::Connection* connection, uint32_t modelVersion )
695
{
696 697 698 699 700 701
    const std::string reqs[] = {
        #include "database/tables/Media_triggers_v14.sql"
    };

    for ( const auto& req : reqs )
        sqlite::Tools::executeRequest( connection, req );
702 703 704 705 706 707 708

    if ( modelVersion >= 14 )
    {
        sqlite::Tools::executeRequest( connection,
            "CREATE TRIGGER IF NOT EXISTS increment_media_nb_playlist AFTER INSERT ON "
            " PlaylistMediaRelation "
            " BEGIN "
709
                " UPDATE " + Media::Table::Name + " SET nb_playlists = nb_playlists + 1 "
710 711 712 713 714 715 716 717
                    " 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 "
718
                " UPDATE " + Media::Table::Name + " SET nb_playlists = nb_playlists - 1 "
719 720 721 722
                    " WHERE id_media = old.media_id;"
            " END;"
        );
    }
723
}
724

725
bool Media::addLabel( LabelPtr label )
726
{
727
    if ( m_id == 0 || label->id() == 0 )
728
    {
729
        LOG_ERROR( "Both file & label need to be inserted in database before being linked together" );
730 731
        return false;
    }
732 733
    try
    {
734 735 736 737 738 739
        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;
740
            const std::string reqFts = "UPDATE " + Media::Table::Name + "Fts "
741 742 743 744 745 746
                "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 ) );
747 748 749 750
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add label: ", ex.what() );
751
        return false;
752
    }
753 754
}

755
bool Media::removeLabel( LabelPtr label )
756
{
757
    if ( m_id == 0 || label->id() == 0 )
758
    {
759
        LOG_ERROR( "Can't unlink a label/file not inserted in database" );
760 761
        return false;
    }
762 763
    try
    {
764 765 766 767 768 769
        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;
770
            const std::string reqFts = "UPDATE " + Media::Table::Name + "Fts "
771 772 773 774 775 776
                    "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 ) );
777 778 779 780
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to remove label: ", ex.what() );
781
        return false;
782
    }
783
}
784

785
Query<IMedia> Media::search( MediaLibraryPtr ml, const std::string& title,
786
                             const QueryParameters* params )
787
{
788 789 790 791 792
    std::string req = "FROM " + Media::Table::Name + " m ";

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

    req +=  " WHERE"
793 794
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
795
            " AND m.is_present = 1"
796
            " AND f.type = ?"
797
            " AND m.type != ? AND m.type != ?";
798
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
799
                                      sortRequest( params ), title,
800
                                      File::Type::Main,
801
                                      Media::Type::External, Media::Type::Stream );
802
}
803

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

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

821 822
Query<IMedia> Media::searchAlbumTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t albumId, const QueryParameters* params)
{
823 824 825 826
    std::string req = "FROM " + Media::Table::Name + " m ";

    req += addRequestJoin( params, true, true );
    req +=  " WHERE"
827 828
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
829
            " AND att.album_id = ?"
830
            " AND m.is_present = 1"
831 832
            " AND f.type = ?"
            " AND m.subtype = ?";
833
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
834
                                      sortRequest( params ), pattern, albumId,
835 836 837
                                      File::Type::Main, Media::SubType::AlbumTrack );
}

838 839
Query<IMedia> Media::searchArtistTracks(MediaLibraryPtr ml, const std::string& pattern, int64_t artistId, const QueryParameters* params)
{
840 841 842 843 844
    std::string req = "FROM " + Media::Table::Name + " m ";

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

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

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

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

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

874 875 876
Query<IMedia> Media::searchShowEpisodes(MediaLibraryPtr ml, const std::string& pattern,
                                        int64_t showId, const QueryParameters* params)
{
877 878 879 880 881
    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 "
882
            " WHERE"
883 884
            " m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts"
            " WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')"
885
            " AND ep.show_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, showId,
891 892 893
                                      File::Type::Main, Media::SubType::ShowEpisode );
}

894 895 896
Query<IMedia> Media::searchInPlaylist( MediaLibraryPtr ml, const std::string& pattern,
                                       int64_t playlistId, const QueryParameters* params )
{
897 898 899 900 901
    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 "
902
           "WHERE pmr.playlist_id = ? AND m.is_present != 0 AND "
903 904
           "m.id_media IN (SELECT rowid FROM " + Media::Table::Name + "Fts "
           "WHERE " + Media::Table::Name + "Fts MATCH '*' || ? || '*')";
905
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
906
                                      sortRequest( params ), playlistId, pattern );
907 908
}

909
Query<IMedia> Media::fetchHistory( MediaLibraryPtr ml )
910
{
911
    static const std::string req = "FROM " + Media::Table::Name +
912
            " WHERE last_played_date IS NOT NULL"
913 914 915 916
            " AND type != ?";
    return make_query<Media, IMedia>( ml, "*", req,
                                      "ORDER BY last_played_date DESC",
                                      IMedia::Type::Stream );
917 918 919 920
}

Query<IMedia> Media::fetchStreamHistory(MediaLibraryPtr ml)
{
921
    static const std::string req = "FROM " + Media::Table::Name +
922
            " WHERE last_played_date IS NOT NULL"
923 924 925 926
            " AND type = ?";
    return make_query<Media, IMedia>( ml, "*", req,
                                      "ORDER BY last_played_date DESC",
                                      IMedia::Type::Stream );
927
}
928

929
void Media::clearHistory( MediaLibraryPtr ml )
930 931
{
    auto dbConn = ml->getConn();
932
    auto t = dbConn->newTransaction();
933
    static const std::string req = "UPDATE " + Media::Table::Name + " SET "
934
            "play_count = 0,"
935
            "last_played_date = NULL";
936 937
    // Clear the entire cache since quite a few items are now containing invalid info.
    clear();
938 939 940 941 942

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

943
    sqlite::Tools::executeUpdate( dbConn, req );
944
    t->commit();
945 946
}

947 948
void Media::removeOldMedia( MediaLibraryPtr ml, std::chrono::seconds maxLifeTime )
{
949 950 951
    // 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
952
    const std::string req = "DELETE FROM " + Media::Table::Name + " "
953 954 955
            "WHERE ( real_last_played_date < ? OR "
                "( real_last_played_date IS NULL AND insertion_date < ? ) )"
            "AND ( type = ? OR type = ? ) "
956
            "AND nb_playlists = 0";
957 958 959
    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(),
960
                                  deadline.count(),
961 962 963
                                  IMedia::Type::External, IMedia::Type::Stream );
}

964
}