Media.cpp 26.3 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 66
    row >> m_id
        >> m_type
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
67
        >> m_subType
68 69
        >> m_duration
        >> m_playCount
70
        >> m_lastPlayedDate
71
        >> m_insertionDate
72
        >> m_releaseDate
73 74
        >> m_thumbnailId
        >> m_thumbnailGenerated
75
        >> m_title
76
        >> m_filename
77 78
        >> m_isFavorite
        >> m_isPresent;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
79 80
}

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

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

109
    if ( insert( ml, self, req, type, self->m_insertionDate, self->m_title, self->m_filename ) == false )
110
        return nullptr;
111
    return self;
112 113
}

114
AlbumTrackPtr Media::albumTrack() const
115
{
116 117
    if ( m_subType != SubType::AlbumTrack )
        return nullptr;
118 119
    auto lock = m_albumTrack.lock();

120
    if ( m_albumTrack.isCached() == false )
121
        m_albumTrack = AlbumTrack::fromMedia( m_ml, m_id );
122
    return m_albumTrack.get();
123 124
}

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

133
int64_t Media::duration() const
134 135 136 137
{
    return m_duration;
}

138
void Media::setDuration( int64_t duration )
139
{
140 141
    if ( m_duration == duration )
        return;
142
    m_duration = duration;
143
    m_changed = true;
144 145
}

146
ShowEpisodePtr Media::showEpisode() const
147
{
148 149
    if ( m_subType != SubType::ShowEpisode )
        return nullptr;
150

151 152
    auto lock = m_showEpisode.lock();
    if ( m_showEpisode.isCached() == false )
153
        m_showEpisode = ShowEpisode::fromMedia( m_ml, m_id );
154
    return m_showEpisode.get();
155 156
}

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

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

173
int Media::playCount() const
174
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
175
    return m_playCount;
176 177
}

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

190 191 192 193 194
bool Media::isFavorite() const
{
    return m_isFavorite;
}

195
bool Media::setFavorite( bool favorite )
196
{
197
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET is_favorite = ? WHERE id_media = ?";
198
    if ( m_isFavorite == favorite )
199 200 201
        return true;
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, favorite, m_id ) == false )
        return false;
202
    m_isFavorite = favorite;
203
    return true;
204 205
}

206
const std::vector<FilePtr>& Media::files() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
207
{
208 209 210 211 212
    auto lock = m_files.lock();
    if ( m_files.isCached() == false )
    {
        static const std::string req = "SELECT * FROM " + policy::FileTable::Name
                + " WHERE media_id = ?";
213
        m_files = File::fetchAll<IFile>( m_ml, req, m_id );
214 215
    }
    return m_files;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
216 217
}

218 219 220 221 222
const std::string& Media::fileName() const
{
    return m_filename;
}

223
MoviePtr Media::movie() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
224
{
225 226 227
    if ( m_subType != SubType::Movie )
        return nullptr;

228 229
    auto lock = m_movie.lock();

230
    if ( m_movie.isCached() == false )
231
        m_movie = Movie::fromMedia( m_ml, m_id );
232
    return m_movie.get();
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
233 234
}

235
void Media::setMovie(MoviePtr movie)
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
236
{
237
    auto lock = m_movie.lock();
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
238
    m_movie = movie;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
239 240
    m_subType = SubType::Movie;
    m_changed = true;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
241 242
}

243 244
bool Media::addVideoTrack(const std::string& codec, unsigned int width, unsigned int height, float fps,
                          const std::string& language, const std::string& description )
245
{
246
    return VideoTrack::create( m_ml, codec, width, height, fps, m_id, language, description ) != nullptr;
247 248
}

249
Query<IVideoTrack> Media::videoTracks()
250
{
251
    static const std::string req = "FROM " + policy::VideoTrackTable::Name +
252
            " WHERE media_id = ?";
253
    return make_query<VideoTrack, IVideoTrack>( m_ml, "*", req, m_id );
254 255
}

256
bool Media::addAudioTrack( const std::string& codec, unsigned int bitrate,
257 258
                          unsigned int sampleRate, unsigned int nbChannels,
                          const std::string& language, const std::string& desc )
259
{
260
    return AudioTrack::create( m_ml, codec, bitrate, sampleRate, nbChannels, language, desc, m_id ) != nullptr;
261 262
}

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

270
const std::string& Media::thumbnail() const
271
{
272
    if ( m_thumbnailId == 0 || m_thumbnailGenerated == false )
273 274
        return Thumbnail::EmptyMrl;

275 276
    auto lock = m_thumbnail.lock();
    if ( m_thumbnail.isCached() == false )
277 278 279 280 281 282
    {
        auto thumbnail = Thumbnail::fetch( m_ml, m_thumbnailId );
        if ( thumbnail == nullptr )
            return Thumbnail::EmptyMrl;
        m_thumbnail = std::move( thumbnail );
    }
283 284 285 286 287 288
    return m_thumbnail.get()->mrl();
}

bool Media::isThumbnailGenerated() const
{
    return m_thumbnailGenerated;
289 290
}

291 292
unsigned int Media::insertionDate() const
{
293
    return static_cast<unsigned int>( m_insertionDate );
294 295
}

296 297 298 299 300
unsigned int Media::releaseDate() const
{
    return m_releaseDate;
}

301
const IMetadata& Media::metadata( IMedia::MetadataType type ) const
302
{
303
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
304 305
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
306
    return m_metadata.get( static_cast<MDType>( type ) );
307 308 309 310
}

bool Media::setMetadata( IMedia::MetadataType type, const std::string& value )
{
311
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
312 313
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
314
    return m_metadata.set( static_cast<MDType>( type ), value );
315 316 317 318
}

bool Media::setMetadata( IMedia::MetadataType type, int64_t value )
{
319
    using MDType = typename std::underlying_type<IMedia::MetadataType>::type;
320 321
    if ( m_metadata.isReady() == false )
        m_metadata.init( m_id, IMedia::NbMeta );
322
    return m_metadata.set( static_cast<MDType>( type ), value );
323 324
}

325 326 327 328 329 330 331 332
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 ) );
}

333 334 335 336 337 338 339 340
void Media::setReleaseDate( unsigned int date )
{
    if ( m_releaseDate == date )
        return;
    m_releaseDate = date;
    m_changed = true;
}

341
bool Media::setThumbnail( const std::string& thumbnailMrl, Thumbnail::Origin origin )
342
{
343
    if ( m_thumbnailId != 0 )
344
        return Thumbnail::setMrlFromPrimaryKey( m_ml, m_thumbnail, m_thumbnailId,
345
                                                thumbnailMrl, origin );
346

347 348 349 350
    std::unique_ptr<sqlite::Transaction> t;
    if ( sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();
    auto lock = m_thumbnail.lock();
351 352
    auto thumbnail = Thumbnail::create( m_ml, thumbnailMrl, origin );
    if ( thumbnail == nullptr )
353
        return false;
354

355
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
356
            "thumbnail_id = ?, thumbnail_generated = 1 WHERE id_media = ?";
357
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, thumbnail->id(), m_id ) == false )
358
        return false;
359
    m_thumbnailId = thumbnail->id();
360
    m_thumbnailGenerated = true;
361
    m_thumbnail = std::move( thumbnail );
362 363
    if ( t != nullptr )
        t->commit();
364 365 366
    return true;
}

367 368 369 370 371
bool Media::setThumbnail( const std::string& thumbnailMrl )
{
    return setThumbnail( thumbnailMrl, Thumbnail::Origin::UserProvided );
}

372 373 374
bool Media::save()
{
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
375
            "type = ?, subtype = ?, duration = ?, release_date = ?,"
376
            "title = ? WHERE id_media = ?";
377 378
    if ( m_changed == false )
        return true;
379
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_type, m_subType, m_duration,
380
                                       m_releaseDate, m_title, m_id ) == false )
381 382 383 384
    {
        return false;
    }
    m_changed = false;
385 386 387
    return true;
}

388 389
std::shared_ptr<File> Media::addFile( const fs::IFile& fileFs, int64_t parentFolderId,
                                      bool isFolderFsRemovable, IFile::Type type )
390
{
391
    auto file = File::createFromMedia( m_ml, m_id, type, fileFs, parentFolderId, isFolderFsRemovable);
392 393 394 395 396 397
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
398 399
}

400
FilePtr Media::addExternalMrl( const std::string& mrl , IFile::Type type )
401
{
402 403 404
    FilePtr file;
    try
    {
405
        file = File::createFromMedia( m_ml, m_id, type, mrl );
406 407 408 409 410 411 412
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add media external MRL: ", ex.what() );
        return nullptr;
    }

413 414 415 416 417 418 419 420
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
}

421
void Media::removeFile( File& file )
422
{
423 424 425
    file.destroy();
    auto lock = m_files.lock();
    if ( m_files.isCached() == false )
426
        return;
427
    m_files.get().erase( std::remove_if( begin( m_files.get() ), end( m_files.get() ), [&file]( const FilePtr& f ) {
428 429
        return f->id() == file.id();
    }));
430 431
}

432
std::string Media::sortRequest( const QueryParameters* params )
433
{
434
    std::string req = " ORDER BY ";
435

436 437
    auto sort = params != nullptr ? params->sort : SortingCriteria::Default;
    auto desc = params != nullptr ? params->desc : false;
438 439
    switch ( sort )
    {
440
    case SortingCriteria::Duration:
441
        req += "m.duration";
442
        break;
443
    case SortingCriteria::InsertionDate:
444
        req += "m.insertion_date";
445
        break;
446
    case SortingCriteria::ReleaseDate:
447
        req += "m.release_date";
448
        break;
449
    case SortingCriteria::PlayCount:
450
        req += "m.play_count";
451 452
        desc = !desc; // Make decreasing order default for play count sorting
        break;
453
    case SortingCriteria::Filename:
454 455 456 457 458 459 460
        req += "m.filename";
        break;
    case SortingCriteria::LastModificationDate:
        req += "f.last_modification_date";
        break;
    case SortingCriteria::FileSize:
        req += "f.size";
461
        break;
462
    default:
463
        req += "m.title";
464 465 466 467
        break;
    }
    if ( desc == true )
        req += " DESC";
468 469
    return req;
}
470

471
Query<IMedia> Media::listAll( MediaLibraryPtr ml, IMedia::Type type,
472
                              const QueryParameters* params )
473
{
474
    std::string req = "FROM " + policy::MediaTable::Name + " m INNER JOIN "
475 476 477 478
            + policy::FileTable::Name + " f ON m.id_media = f.media_id"
            " WHERE m.type = ?"
            " AND f.type = ?"
            " AND f.is_present != 0";
479

480
    req += sortRequest( params );
481 482
    return make_query<Media, IMedia>( ml, "m.*", std::move( req ),
                                      type, IFile::Type::Main );
483 484
}

485
int64_t Media::id() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
486 487 488 489
{
    return m_id;
}

490
IMedia::Type Media::type() const
491 492 493 494
{
    return m_type;
}

495
IMedia::SubType Media::subType() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
496 497 498 499
{
    return m_subType;
}

500
void Media::setType( Type type )
501
{
502
    if ( m_type == type )
503
        return;
504
    m_type = type;
505
    m_changed = true;
506 507
}

508
const std::string& Media::title() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
509
{
510
    return m_title;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
511 512
}

513 514 515 516 517
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;
518 519 520 521 522 523 524 525
    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() );
526
        return false;
527 528
    }

529 530 531 532
    m_title = title;
    return true;
}

533
void Media::setTitleBuffered( const std::string& title )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
534
{
535 536
    if ( m_title == title )
        return;
537
    m_title = title;
538
    m_changed = true;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
539 540
}

541
void Media::createTable( sqlite::Connection* connection )
542
{
543 544
    std::string req = "CREATE TABLE IF NOT EXISTS " + policy::MediaTable::Name + "("
            "id_media INTEGER PRIMARY KEY AUTOINCREMENT,"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
545
            "type INTEGER,"
546 547 548
            "subtype INTEGER NOT NULL DEFAULT " +
                std::to_string( static_cast<typename std::underlying_type<IMedia::SubType>::type>(
                                    IMedia::SubType::Unknown ) ) + ","
549
            "duration INTEGER DEFAULT -1,"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
550
            "play_count UNSIGNED INTEGER,"
551
            "last_played_date UNSIGNED INTEGER,"
552
            "insertion_date UNSIGNED INTEGER,"
553
            "release_date UNSIGNED INTEGER,"
554 555
            "thumbnail_id INTEGER,"
            "thumbnail_generated BOOLEAN NOT NULL DEFAULT 0,"
556
            "title TEXT COLLATE NOCASE,"
557
            "filename TEXT,"
558
            "is_favorite BOOLEAN NOT NULL DEFAULT 0,"
559 560 561
            "is_present BOOLEAN NOT NULL DEFAULT 1,"
            "FOREIGN KEY(thumbnail_id) REFERENCES " + policy::ThumbnailTable::Name
            + "(id_thumbnail)"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
562
            ")";
563

564
    const std::string vtableReq = "CREATE VIRTUAL TABLE IF NOT EXISTS "
565
                + policy::MediaTable::Name + "Fts USING FTS3("
566 567
                "title,"
                "labels"
568
            ")";
569 570
    sqlite::Tools::executeRequest( connection, req );
    sqlite::Tools::executeRequest( connection, vtableReq );
571 572
}

573
void Media::createTriggers( sqlite::Connection* connection )
574
{
575 576
    const std::string indexReq = "CREATE INDEX IF NOT EXISTS index_last_played_date ON "
            + policy::MediaTable::Name + "(last_played_date DESC)";
577 578 579 580
    static const std::string triggerReq = "CREATE TRIGGER IF NOT EXISTS has_files_present AFTER UPDATE OF "
            "is_present ON " + policy::FileTable::Name +
            " BEGIN "
            " UPDATE " + policy::MediaTable::Name + " SET is_present="
581 582
                "(SELECT EXISTS("
                    "SELECT id_file FROM " + policy::FileTable::Name +
583
                    " WHERE media_id=new.media_id AND is_present != 0 LIMIT 1"
584
                ") )"
585 586 587 588 589 590 591 592 593
                "WHERE id_media=new.media_id;"
            " END;";
    static const std::string triggerReq2 = "CREATE TRIGGER IF NOT EXISTS cascade_file_deletion AFTER DELETE ON "
            + policy::FileTable::Name +
            " BEGIN "
            " DELETE FROM " + policy::MediaTable::Name + " WHERE "
                "(SELECT COUNT(id_file) FROM " + policy::FileTable::Name + " WHERE media_id=old.media_id) = 0"
                " AND id_media=old.media_id;"
            " END;";
594 595 596 597

    static const std::string vtableInsertTrigger = "CREATE TRIGGER IF NOT EXISTS insert_media_fts"
            " AFTER INSERT ON " + policy::MediaTable::Name +
            " BEGIN"
598
            " INSERT INTO " + policy::MediaTable::Name + "Fts(rowid,title,labels) VALUES(new.id_media, new.title, '');"
599 600
            " END";
    static const std::string vtableDeleteTrigger = "CREATE TRIGGER IF NOT EXISTS delete_media_fts"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
601
            " BEFORE DELETE ON " + policy::MediaTable::Name +
602 603 604 605 606 607 608 609
            " BEGIN"
            " DELETE FROM " + policy::MediaTable::Name + "Fts WHERE rowid = old.id_media;"
            " END";
    static const std::string vtableUpdateTitleTrigger2 = "CREATE TRIGGER IF NOT EXISTS update_media_title_fts"
              " AFTER UPDATE OF title ON " + policy::MediaTable::Name +
              " BEGIN"
              " UPDATE " + policy::MediaTable::Name + "Fts SET title = new.title WHERE rowid = new.id_media;"
              " END";
610 611

    sqlite::Tools::executeRequest( connection, indexReq );
612 613 614 615 616
    sqlite::Tools::executeRequest( connection, triggerReq );
    sqlite::Tools::executeRequest( connection, triggerReq2 );
    sqlite::Tools::executeRequest( connection, vtableInsertTrigger );
    sqlite::Tools::executeRequest( connection, vtableDeleteTrigger );
    sqlite::Tools::executeRequest( connection, vtableUpdateTitleTrigger2 );
617
}
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
618

619
bool Media::addLabel( LabelPtr label )
620
{
621
    if ( m_id == 0 || label->id() == 0 )
622
    {
623
        LOG_ERROR( "Both file & label need to be inserted in database before being linked together" );
624 625
        return false;
    }
626 627
    try
    {
628 629 630 631 632 633 634 635 636 637 638 639 640
        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 ) );
641 642 643 644
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add label: ", ex.what() );
645
        return false;
646
    }
647 648
}

649
bool Media::removeLabel( LabelPtr label )
650
{
651
    if ( m_id == 0 || label->id() == 0 )
652
    {
653
        LOG_ERROR( "Can't unlink a label/file not inserted in database" );
654 655
        return false;
    }
656 657
    try
    {
658 659 660 661 662 663 664 665 666 667 668 669 670
        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 ) );
671 672 673 674
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to remove label: ", ex.what() );
675
        return false;
676
    }
677
}
678

679
Query<IMedia> Media::search( MediaLibraryPtr ml, const std::string& title,
680
                             const QueryParameters* params )
681
{
682
    std::string req = "FROM " + policy::MediaTable::Name + " m "
683 684 685
            " 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"
686
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
687
            " AND f.is_present = 1"
688 689
            " AND f.type = ?"
            " AND m.type != ?";
690
    req += sortRequest( params );
691 692
    return make_query<Media, IMedia>( ml, "m.*", req, title, File::Type::Main,
                                      Media::Type::External );
693
}
694

695 696 697 698 699 700 701 702 703 704 705 706 707 708 709
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 );
}

710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726
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 );
}

727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743
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 );
}

744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760
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 );
}

761
Query<IMedia> Media::fetchHistory( MediaLibraryPtr ml )
762
{
763
    static const std::string req = "FROM " + policy::MediaTable::Name + " WHERE last_played_date IS NOT NULL"
764
            " ORDER BY last_played_date DESC LIMIT 100";
765
    return make_query<Media, IMedia>( ml, "*", req );
766
}
767

768
void Media::clearHistory( MediaLibraryPtr ml )
769 770
{
    auto dbConn = ml->getConn();
771 772
    // There should already be an active transaction, from MediaLibrary::clearHistory
    assert( sqlite::Transaction::transactionInProgress() == true );
773 774
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
            "play_count = 0,"
775
            "last_played_date = NULL";
776 777
    // Clear the entire cache since quite a few items are now containing invalid info.
    clear();
778 779 780 781 782

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