Media.cpp 22.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
#include "database/SqliteTools.h"
45
#include "VideoTrack.h"
46
#include "filesystem/IFile.h"
47 48
#include "filesystem/IDirectory.h"
#include "filesystem/IDevice.h"
49
#include "utils/Filename.h"
50

51 52 53
namespace medialibrary
{

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

58 59
const std::string policy::MediaMetadataTable::Name = "MediaMetadata";

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

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

98
std::shared_ptr<Media> Media::create( MediaLibraryPtr ml, Type type, const std::string& fileName )
99
{
100
    auto self = std::make_shared<Media>( ml, fileName, type );
101
    static const std::string req = "INSERT INTO " + policy::MediaTable::Name +
102
            "(type, insertion_date, title, filename) VALUES(?, ?, ?, ?)";
103

104
    if ( insert( ml, self, req, type, self->m_insertionDate, self->m_title, self->m_filename ) == false )
105
        return nullptr;
106
    return self;
107 108
}

109
AlbumTrackPtr Media::albumTrack() const
110
{
111 112
    if ( m_subType != SubType::AlbumTrack )
        return nullptr;
113 114
    auto lock = m_albumTrack.lock();

115
    if ( m_albumTrack.isCached() == false )
116
        m_albumTrack = AlbumTrack::fromMedia( m_ml, m_id );
117
    return m_albumTrack.get();
118 119
}

120
void Media::setAlbumTrack( AlbumTrackPtr albumTrack )
121
{
122
    auto lock = m_albumTrack.lock();
123
    m_albumTrack = albumTrack;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
124 125
    m_subType = SubType::AlbumTrack;
    m_changed = true;
126 127
}

128
int64_t Media::duration() const
129 130 131 132
{
    return m_duration;
}

133
void Media::setDuration( int64_t duration )
134
{
135 136
    if ( m_duration == duration )
        return;
137
    m_duration = duration;
138
    m_changed = true;
139 140
}

141
ShowEpisodePtr Media::showEpisode() const
142
{
143 144
    if ( m_subType != SubType::ShowEpisode )
        return nullptr;
145

146 147
    auto lock = m_showEpisode.lock();
    if ( m_showEpisode.isCached() == false )
148
        m_showEpisode = ShowEpisode::fromMedia( m_ml, m_id );
149
    return m_showEpisode.get();
150 151
}

152
void Media::setShowEpisode( ShowEpisodePtr episode )
153
{
154 155
    auto lock = m_showEpisode.lock();
    m_showEpisode = episode;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
156 157
    m_subType = SubType::ShowEpisode;
    m_changed = true;
158 159
}

160
std::vector<LabelPtr> Media::labels()
161
{
162
    static const std::string req = "SELECT l.* FROM " + policy::LabelTable::Name + " l "
163
            "INNER JOIN LabelFileRelation lfr ON lfr.label_id = l.id_label "
164
            "WHERE lfr.media_id = ?";
165
    return Label::fetchAll<ILabel>( m_ml, req, m_id );
166 167
}

168
int Media::playCount() const
169
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
170
    return m_playCount;
171 172
}

173
bool Media::increasePlayCount()
174
{
175 176 177 178 179
    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;
180
    m_playCount++;
181 182
    m_lastPlayedDate = lastPlayedDate;
    return true;
183 184
}

185 186 187 188 189
bool Media::isFavorite() const
{
    return m_isFavorite;
}

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

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

213
MoviePtr Media::movie() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
214
{
215 216 217
    if ( m_subType != SubType::Movie )
        return nullptr;

218 219
    auto lock = m_movie.lock();

220
    if ( m_movie.isCached() == false )
221
        m_movie = Movie::fromMedia( m_ml, m_id );
222
    return m_movie.get();
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
223 224
}

225
void Media::setMovie(MoviePtr movie)
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
226
{
227
    auto lock = m_movie.lock();
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
228
    m_movie = movie;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
229 230
    m_subType = SubType::Movie;
    m_changed = true;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
231 232
}

233 234
bool Media::addVideoTrack(const std::string& codec, unsigned int width, unsigned int height, float fps,
                          const std::string& language, const std::string& description )
235
{
236
    return VideoTrack::create( m_ml, codec, width, height, fps, m_id, language, description ) != nullptr;
237 238
}

239
std::vector<VideoTrackPtr> Media::videoTracks()
240
{
241 242
    static const std::string req = "SELECT * FROM " + policy::VideoTrackTable::Name +
            " WHERE media_id = ?";
243
    return VideoTrack::fetchAll<IVideoTrack>( m_ml, req, m_id );
244 245
}

246
bool Media::addAudioTrack( const std::string& codec, unsigned int bitrate,
247 248
                          unsigned int sampleRate, unsigned int nbChannels,
                          const std::string& language, const std::string& desc )
249
{
250
    return AudioTrack::create( m_ml, codec, bitrate, sampleRate, nbChannels, language, desc, m_id ) != nullptr;
251 252
}

253
std::vector<AudioTrackPtr> Media::audioTracks()
254
{
255 256
    static const std::string req = "SELECT * FROM " + policy::AudioTrackTable::Name +
            " WHERE media_id = ?";
257
    return AudioTrack::fetchAll<IAudioTrack>( m_ml, req, m_id );
258 259
}

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
260
const std::string &Media::thumbnail()
261
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
262
    return m_thumbnail;
263 264
}

265 266
unsigned int Media::insertionDate() const
{
267
    return static_cast<unsigned int>( m_insertionDate );
268 269
}

270 271 272 273 274
unsigned int Media::releaseDate() const
{
    return m_releaseDate;
}

275 276 277 278 279 280
const IMediaMetadata& Media::metadata( IMedia::MetadataType type ) const
{
    auto lock = m_metadata.lock();
    if ( m_metadata.isCached() == false )
    {
        std::vector<MediaMetadata> res;
281 282 283 284 285
        // Reserve the space for all meta to avoid a race condition where 2 threads
        // would cache different meta, invalidating the potential reference
        // to another IMediaMetadata held by another thread.
        // This guarantees the vector will not grow afterward.
        res.reserve( IMedia::NbMeta );
286 287
        static const std::string req = "SELECT * FROM " + policy::MediaMetadataTable::Name +
                " WHERE id_media = ?";
288 289
        auto conn = m_ml->getConn();
        auto ctx = conn->acquireReadContext();
290
        sqlite::Statement stmt( conn->handle(), req );
291 292 293 294 295 296 297
        stmt.execute( m_id );
        for ( sqlite::Row row = stmt.row(); row != nullptr; row = stmt.row() )
        {
            assert( row.load<int64_t>( 0 ) == m_id );
            res.emplace_back( row.load<decltype(MediaMetadata::m_type)>( 1 ),
                              row.load<decltype(MediaMetadata::m_value)>( 2 ) );
        }
298
        m_metadata = std::move( res );
299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320
    }
    auto it = std::find_if( begin( m_metadata.get() ), end( m_metadata.get() ), [type](const MediaMetadata& m ) {
        return m.m_type == type;
    });
    if ( it == end( m_metadata.get() ) )
    {
        m_metadata.get().emplace_back( type );
        return *m_metadata.get().rbegin();
    }
    return (*it);
}

bool Media::setMetadata( IMedia::MetadataType type, const std::string& value )
{
    {
        auto lock = m_metadata.lock();
        if ( m_metadata.isCached() == true )
        {
            auto it = std::find_if( begin( m_metadata.get() ), end( m_metadata.get() ), [type](const MediaMetadata& m ) {
                return m.m_type == type;
            });
            if ( it != end( m_metadata.get() ) )
321
                (*it).set( value );
322 323 324 325
            else
                m_metadata.get().emplace_back( type, value );
        }
    }
326 327 328 329 330 331 332 333 334 335 336
    try
    {
        static const std::string req = "INSERT OR REPLACE INTO " + policy::MediaMetadataTable::Name +
                "(id_media, type, value) VALUES(?, ?, ?)";
        return sqlite::Tools::executeInsert( m_ml->getConn(), req, m_id, type, value );
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to update media metadata: ", ex.what() );
        return false;
    }
337 338 339 340 341 342 343 344
}

bool Media::setMetadata( IMedia::MetadataType type, int64_t value )
{
    auto str = std::to_string( value );
    return setMetadata( type, str );
}

345 346 347 348 349 350 351 352
void Media::setReleaseDate( unsigned int date )
{
    if ( m_releaseDate == date )
        return;
    m_releaseDate = date;
    m_changed = true;
}

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
353
void Media::setThumbnail(const std::string& thumbnail )
354
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
355
    if ( m_thumbnail == thumbnail )
356
        return;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
357
    m_thumbnail = thumbnail;
358 359 360 361 362 363
    m_changed = true;
}

bool Media::save()
{
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
364
            "type = ?, subtype = ?, duration = ?, release_date = ?,"
365
            "thumbnail = ?, title = ? WHERE id_media = ?";
366 367
    if ( m_changed == false )
        return true;
368
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_type, m_subType, m_duration,
369
                                       m_releaseDate, m_thumbnail, m_title, m_id ) == false )
370 371 372 373
    {
        return false;
    }
    m_changed = false;
374 375 376
    return true;
}

377 378
std::shared_ptr<File> Media::addFile( const fs::IFile& fileFs, int64_t parentFolderId,
                                      bool isFolderFsRemovable, IFile::Type type )
379
{
380
    auto file = File::createFromMedia( m_ml, m_id, type, fileFs, parentFolderId, isFolderFsRemovable);
381 382 383 384 385 386
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
387 388
}

389
FilePtr Media::addExternalMrl( const std::string& mrl , IFile::Type type )
390
{
391 392 393
    FilePtr file;
    try
    {
394
        file = File::createFromMedia( m_ml, m_id, type, mrl );
395 396 397 398 399 400 401
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add media external MRL: ", ex.what() );
        return nullptr;
    }

402 403 404 405 406 407 408 409
    if ( file == nullptr )
        return nullptr;
    auto lock = m_files.lock();
    if ( m_files.isCached() )
        m_files.get().push_back( file );
    return file;
}

410
void Media::removeFile( File& file )
411
{
412 413 414
    file.destroy();
    auto lock = m_files.lock();
    if ( m_files.isCached() == false )
415
        return;
416
    m_files.get().erase( std::remove_if( begin( m_files.get() ), end( m_files.get() ), [&file]( const FilePtr& f ) {
417 418
        return f->id() == file.id();
    }));
419 420
}

421
std::vector<MediaPtr> Media::listAll( MediaLibraryPtr ml, IMedia::Type type, SortingCriteria sort, bool desc )
422
{
423
    std::string req;
424
    if ( sort == SortingCriteria::LastModificationDate || sort == SortingCriteria::FileSize )
425 426 427 428
    {
        req = "SELECT m.* FROM " + policy::MediaTable::Name + " m INNER JOIN "
                + policy::FileTable::Name + " f ON m.id_media = f.media_id"
                " WHERE m.type = ?"
429
                " AND f.type = ?";
430 431 432 433
        if ( sort == SortingCriteria::LastModificationDate )
            req += " ORDER BY f.last_modification_date";
        else
            req += " ORDER BY f.size";
434 435
        if ( desc == true )
            req += " DESC";
436
        return fetchAll<IMedia>( ml, req, type, File::Type::Main );
437 438 439 440
    }
    req = "SELECT * FROM " + policy::MediaTable::Name + " WHERE type = ? AND is_present = 1 ORDER BY ";
    switch ( sort )
    {
441
    case SortingCriteria::Duration:
442 443
        req += "duration";
        break;
444
    case SortingCriteria::InsertionDate:
445 446
        req += "insertion_date";
        break;
447
    case SortingCriteria::ReleaseDate:
448 449 450
        req += "release_date";
        break;
    default:
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
451
        req += "title";
452 453 454 455 456 457
        break;
    }
    if ( desc == true )
        req += " DESC";

    return fetchAll<IMedia>( ml, req, type );
458 459
}

460
int64_t Media::id() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
461 462 463 464
{
    return m_id;
}

465
IMedia::Type Media::type()
466 467 468 469
{
    return m_type;
}

470
IMedia::SubType Media::subType() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
471 472 473 474
{
    return m_subType;
}

475
void Media::setType( Type type )
476
{
477
    if ( m_type == type )
478
        return;
479
    m_type = type;
480
    m_changed = true;
481 482
}

483
const std::string &Media::title() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
484
{
485
    return m_title;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
486 487
}

488 489 490 491 492
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;
493 494 495 496 497 498 499 500
    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() );
501
        return false;
502 503
    }

504 505 506 507
    m_title = title;
    return true;
}

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
508
void Media::setTitleBuffered( const std::string &title )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
509
{
510 511
    if ( m_title == title )
        return;
512
    m_title = title;
513
    m_changed = true;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
514 515
}

516
bool Media::createTable( sqlite::Connection* connection )
517
{
518 519
    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
520
            "type INTEGER,"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
521
            "subtype INTEGER,"
522
            "duration INTEGER DEFAULT -1,"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
523
            "play_count UNSIGNED INTEGER,"
524
            "last_played_date UNSIGNED INTEGER,"
525
            "insertion_date UNSIGNED INTEGER,"
526
            "release_date UNSIGNED INTEGER,"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
527
            "thumbnail TEXT,"
528
            "title TEXT COLLATE NOCASE,"
529
            "filename TEXT,"
530
            "is_favorite BOOLEAN NOT NULL DEFAULT 0,"
531
            "is_present BOOLEAN NOT NULL DEFAULT 1"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
532
            ")";
533
    const std::string indexReq = "CREATE INDEX IF NOT EXISTS index_last_played_date ON "
534
            + policy::MediaTable::Name + "(last_played_date DESC)";
535
    const std::string vtableReq = "CREATE VIRTUAL TABLE IF NOT EXISTS "
536
                + policy::MediaTable::Name + "Fts USING FTS3("
537 538
                "title,"
                "labels"
539
            ")";
540 541 542 543 544 545
    const std::string metadataReq = "CREATE TABLE IF NOT EXISTS " + policy::MediaMetadataTable::Name + "("
            "id_media INTEGER,"
            "type INTEGER,"
            "value TEXT,"
            "PRIMARY KEY (id_media, type)"
            ")";
546
    return sqlite::Tools::executeRequest( connection, req ) &&
547
            sqlite::Tools::executeRequest( connection, indexReq ) &&
548 549
            sqlite::Tools::executeRequest( connection, vtableReq ) &&
            sqlite::Tools::executeRequest( connection, metadataReq );
550 551
}

552
bool Media::createTriggers( sqlite::Connection* connection )
553 554 555 556 557
{
    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="
558 559
                "(SELECT COUNT(id_file) FROM " + policy::FileTable::Name +
                    " WHERE media_id=new.media_id AND is_present=1 LIMIT 1) "
560 561 562 563 564 565 566 567 568
                "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;";
569 570 571 572

    static const std::string vtableInsertTrigger = "CREATE TRIGGER IF NOT EXISTS insert_media_fts"
            " AFTER INSERT ON " + policy::MediaTable::Name +
            " BEGIN"
573
            " INSERT INTO " + policy::MediaTable::Name + "Fts(rowid,title,labels) VALUES(new.id_media, new.title, '');"
574 575
            " 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
576
            " BEFORE DELETE ON " + policy::MediaTable::Name +
577 578 579 580 581 582 583 584
            " 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";
585
    return sqlite::Tools::executeRequest( connection, triggerReq ) &&
586 587 588 589
            sqlite::Tools::executeRequest( connection, triggerReq2 ) &&
            sqlite::Tools::executeRequest( connection, vtableInsertTrigger ) &&
            sqlite::Tools::executeRequest( connection, vtableDeleteTrigger ) &&
            sqlite::Tools::executeRequest( connection, vtableUpdateTitleTrigger2 );
590
}
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
591

592
bool Media::addLabel( LabelPtr label )
593
{
594
    if ( m_id == 0 || label->id() == 0 )
595
    {
596
        LOG_ERROR( "Both file & label need to be inserted in database before being linked together" );
597 598
        return false;
    }
599 600
    try
    {
601 602 603 604 605 606 607 608 609 610 611 612 613
        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 ) );
614 615 616 617
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to add label: ", ex.what() );
618
        return false;
619
    }
620 621
}

622
bool Media::removeLabel( LabelPtr label )
623
{
624
    if ( m_id == 0 || label->id() == 0 )
625
    {
626
        LOG_ERROR( "Can't unlink a label/file not inserted in database" );
627 628
        return false;
    }
629 630
    try
    {
631 632 633 634 635 636 637 638 639 640 641 642 643
        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 ) );
644 645 646 647
    }
    catch ( const sqlite::errors::Generic& ex )
    {
        LOG_ERROR( "Failed to remove label: ", ex.what() );
648
        return false;
649
    }
650
}
651 652


653
std::vector<MediaPtr> Media::search( MediaLibraryPtr ml, const std::string& title )
654 655 656
{
    static const std::string req = "SELECT * FROM " + policy::MediaTable::Name + " WHERE"
            " id_media IN (SELECT rowid FROM " + policy::MediaTable::Name + "Fts"
657
            " WHERE " + policy::MediaTable::Name + "Fts MATCH '*' || ? || '*')"
658
            "AND is_present = 1";
659
    return Media::fetchAll<IMedia>( ml, req, title );
660
}
661

662
std::vector<MediaPtr> Media::fetchHistory( MediaLibraryPtr ml )
663 664 665
{
    static const std::string req = "SELECT * FROM " + policy::MediaTable::Name + " WHERE last_played_date IS NOT NULL"
            " ORDER BY last_played_date DESC LIMIT 100";
666
    return fetchAll<IMedia>( ml, req );
667
}
668

669
void Media::clearHistory( MediaLibraryPtr ml )
670 671
{
    auto dbConn = ml->getConn();
672 673
    // There should already be an active transaction, from MediaLibrary::clearHistory
    assert( sqlite::Transaction::transactionInProgress() == true );
674 675
    static const std::string req = "UPDATE " + policy::MediaTable::Name + " SET "
            "play_count = 0,"
676 677 678
            "last_played_date = NULL";
    static const std::string flushProgress = "DELETE FROM " + policy::MediaMetadataTable::Name +
            " WHERE type = ?";
679 680
    // Clear the entire cache since quite a few items are now containing invalid info.
    clear();
681
    sqlite::Tools::executeUpdate( dbConn, req );
682
    sqlite::Tools::executeDelete( dbConn, flushProgress, IMedia::MetadataType::Progress );
683 684
}

685 686 687 688 689 690 691 692 693 694 695 696 697 698 699
bool Media::MediaMetadata::isSet() const
{
    return m_isSet;
}

int64_t Media::MediaMetadata::integer() const
{
    return atoll( m_value.c_str() );
}

const std::string& Media::MediaMetadata::str() const
{
    return m_value;
}

700 701 702 703 704 705
void Media::MediaMetadata::set( const std::string& value )
{
    m_value = value;
    m_isSet = true;
}

706
}