MediaGroup.cpp 35.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 23 24 25 26 27
/*****************************************************************************
 * Media Library
 *****************************************************************************
 * Copyright (C) 2015-2019 Hugo Beauzée-Luyssen, Videolabs, VideoLAN
 *
 * 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.
 *****************************************************************************/

#if HAVE_CONFIG_H
# include "config.h"
#endif

#include "MediaGroup.h"
28
#include "Media.h"
29 30
#include "database/SqliteQuery.h"
#include "medialibrary/IMediaLibrary.h"
31
#include "utils/ModificationsNotifier.h"
32

33 34
#include <cstring>

35 36 37 38 39 40 41
namespace medialibrary
{

const std::string MediaGroup::Table::Name = "MediaGroup";
const std::string MediaGroup::Table::PrimaryKeyColumn = "id_group";
int64_t MediaGroup::*const MediaGroup::Table::PrimaryKey = &MediaGroup::m_id;

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
42 43
const std::string MediaGroup::FtsTable::Name = "MediaGroupFts";

44 45 46 47 48 49 50
MediaGroup::MediaGroup( MediaLibraryPtr ml, sqlite::Row& row )
    : m_ml( ml )
    , m_id( row.extract<decltype(m_id)>() )
    , m_name( row.extract<decltype(m_name)>() )
    , m_nbVideo( row.extract<decltype(m_nbVideo)>() )
    , m_nbAudio( row.extract<decltype(m_nbAudio)>() )
    , m_nbUnknown( row.extract<decltype(m_nbUnknown)>() )
51
    , m_nbMedia( row.extract<decltype(m_nbMedia)>() )
52
    , m_duration( row.extract<decltype(m_duration)>() )
53 54
    , m_creationDate( row.extract<decltype(m_creationDate)>() )
    , m_lastModificationDate( row.extract<decltype(m_lastModificationDate)>() )
55
    , m_userInteracted( row.extract<decltype(m_userInteracted)>() )
56
    , m_forcedSingleton( row.extract<decltype(m_forcedSingleton)>() )
57 58 59 60
{
    assert( row.hasRemainingColumns() == false );
}

61 62
MediaGroup::MediaGroup( MediaLibraryPtr ml, std::string name, bool userInitiated,
                        bool isForcedSingleton )
63 64 65 66 67 68
    : m_ml( ml )
    , m_id( 0 )
    , m_name( std::move( name ) )
    , m_nbVideo( 0 )
    , m_nbAudio( 0 )
    , m_nbUnknown( 0 )
69
    , m_nbMedia( 0 )
70
    , m_duration( 0 )
71 72
    , m_creationDate( time( nullptr ) )
    , m_lastModificationDate( m_creationDate )
73
    , m_userInteracted( userInitiated )
74
    , m_forcedSingleton( isForcedSingleton )
75 76 77
{
}

78
MediaGroup::MediaGroup( MediaLibraryPtr ml , std::string name )
79 80
    : m_ml( ml )
    , m_id( 0 )
81
    , m_name( std::move( name ) )
82 83 84
    , m_nbVideo( 0 )
    , m_nbAudio( 0 )
    , m_nbUnknown( 0 )
85
    , m_nbMedia( 0 )
86
    , m_duration( 0 )
87 88
    , m_creationDate( time( nullptr ) )
    , m_lastModificationDate( m_creationDate )
89
    , m_userInteracted( true )
90
    , m_forcedSingleton( false )
91 92 93
{
}

94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
int64_t MediaGroup::id() const
{
    return m_id;
}

const std::string& MediaGroup::name() const
{
    return m_name;
}

uint32_t MediaGroup::nbMedia() const
{
    return m_nbVideo + m_nbAudio + m_nbUnknown;
}

uint32_t MediaGroup::nbVideo() const
{
    return m_nbVideo;
}

uint32_t MediaGroup::nbAudio() const
{
    return m_nbAudio;
}

uint32_t MediaGroup::nbUnknown() const
{
    return m_nbUnknown;
}

124 125 126 127 128
int64_t MediaGroup::duration() const
{
    return m_duration;
}

129 130 131 132 133 134 135 136 137 138
time_t MediaGroup::creationDate() const
{
    return m_creationDate;
}

time_t MediaGroup::lastModificationDate() const
{
    return m_lastModificationDate;
}

139
bool MediaGroup::userInteracted() const
140
{
141
    return m_userInteracted;
142 143
}

144 145
bool MediaGroup::add( IMedia& media )
{
146 147 148 149 150 151 152 153
    return add( media, false );
}

bool MediaGroup::add( int64_t mediaId )
{
    return add( mediaId, false );
}

154
bool MediaGroup::add( IMedia& media, bool initForceSingleton )
155
{
156
    if ( add( media.id(), initForceSingleton ) == false )
157 158 159 160 161 162 163 164 165 166 167 168 169
        return false;
    switch ( media.type() )
    {
        case IMedia::Type::Audio:
            ++m_nbAudio;
            break;
        case IMedia::Type::Video:
            ++m_nbVideo;
            break;
        case IMedia::Type::Unknown:
            ++m_nbUnknown;
            break;
    }
170 171
    if ( media.duration() > 0 )
        m_duration += media.duration();
172 173
    auto& m = static_cast<Media&>( media );
    m.setMediaGroupId( m_id );
174
    return true;
175 176
}

177
bool MediaGroup::add( int64_t mediaId, bool initForceSingleton )
178
{
179 180 181 182 183
    std::unique_ptr<sqlite::Transaction> t;
    if ( m_forcedSingleton == true && initForceSingleton == false &&
         sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();
    if ( Media::setMediaGroup( m_ml, mediaId, m_id ) == false )
184
        return false;
185 186 187 188 189 190 191 192 193 194
    if ( m_forcedSingleton == true && initForceSingleton == false )
    {
        const std::string req = "UPDATE " + Table::Name +
                " SET forced_singleton = 0 WHERE id_group = ?";
        if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, m_id ) == false )
            return false;
        m_forcedSingleton = false;
    }
    if ( t != nullptr )
        t->commit();
195
    m_lastModificationDate = time( nullptr );
196 197 198 199 200
    return Media::setMediaGroup( m_ml, mediaId, m_id );
}

bool MediaGroup::remove( IMedia& media )
{
201 202 203 204 205 206 207
    std::unique_ptr<sqlite::Transaction> t;
    if ( sqlite::Transaction::transactionInProgress() == false )
        t = m_ml->getConn()->newTransaction();

    auto group = MediaGroup::create( m_ml, media.title(), false, true );
    if ( group == nullptr )
        return false;
208
    auto res = group->add( media, true );
209
    if ( res == false )
210
        return false;
211 212 213 214 215 216 217

    if ( t != nullptr )
        t->commit();

    auto& m = static_cast<Media&>( media );
    m.setMediaGroupId( group->id() );

218 219 220 221 222 223 224 225 226 227 228 229
    switch ( media.type() )
    {
        case IMedia::Type::Audio:
            --m_nbAudio;
            break;
        case IMedia::Type::Video:
            --m_nbVideo;
            break;
        case IMedia::Type::Unknown:
            --m_nbUnknown;
            break;
    }
230 231
    if ( media.duration() > 0 )
        m_duration -= media.duration();
232
    return true;
233 234 235 236
}

bool MediaGroup::remove( int64_t mediaId )
{
237 238 239 240
    auto media = Media::fetch( m_ml, mediaId );
    if ( media == nullptr )
        return false;
    return remove( *media );
241 242 243 244 245 246 247 248 249 250 251 252 253
}

Query<IMedia> MediaGroup::media( IMedia::Type mediaType, const QueryParameters* params)
{
    return Media::fromMediaGroup( m_ml, m_id, mediaType, params );
}

Query<IMedia> MediaGroup::searchMedia(const std::string& pattern, IMedia::Type mediaType,
                                       const QueryParameters* params )
{
    return Media::searchFromMediaGroup( m_ml, m_id, mediaType, pattern, params );
}

254
bool MediaGroup::rename( std::string name )
255 256 257 258 259
{
    return rename( std::move( name ), true );
}

bool MediaGroup::rename( std::string name, bool userInitiated )
260
{
261 262
    if ( name.empty() == true )
        return false;
263 264
    if ( m_forcedSingleton == true )
        return false;
265 266
    if ( name == m_name )
        return true;
267 268
    /* No need to update the user_interacted column if it's already set to false */
    if ( userInitiated == false || m_userInteracted == true )
269 270
    {
        const std::string req = "UPDATE " + Table::Name +
271 272
                " SET name = ?, last_modification_date = strftime('%s')"
                " WHERE id_group = ?";
273 274 275 276 277 278
        if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, name, m_id ) == false )
            return false;
    }
    else
    {
        const std::string req = "UPDATE " + Table::Name +
279 280
                " SET name = ?, last_modification_date = strftime('%s'),"
                " user_interacted = true WHERE id_group = ?";
281 282
        if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, name, m_id ) == false )
            return false;
283
        m_userInteracted = true;
284
    }
285
    m_lastModificationDate = time( nullptr );
286 287 288 289
    m_name = std::move( name );
    return true;
}

290 291 292 293 294
bool MediaGroup::isForcedSingleton() const
{
    return m_forcedSingleton;
}

295 296
bool MediaGroup::destroy()
{
297 298
    if ( m_forcedSingleton == true )
        return false;
299 300 301 302 303 304 305 306 307 308
    auto t = m_ml->getConn()->newTransaction();
    auto content = media( IMedia::Type::Unknown, nullptr )->all();
    for ( auto& m : content )
    {
        if ( remove( *m ) == false )
            return false;
    }
    // Let the empty group be removed by the DeleteEmptyGroups trigger
    t->commit();
    return true;
309 310
}

311
std::shared_ptr<MediaGroup> MediaGroup::create( MediaLibraryPtr ml,
312
                                                std::string name,
313 314
                                                bool userInitiated,
                                                bool isForcedSingleton )
315 316
{
    static const std::string req = "INSERT INTO " + Table::Name +
317 318
            "(name, user_interacted, forced_singleton, creation_date, last_modification_date) "
            "VALUES(?, ?, ?, ?, ?)";
319
    auto self = std::make_shared<MediaGroup>( ml, std::move( name ),
320
                                              userInitiated, isForcedSingleton );
321
    if ( insert( ml, self, req, self->name(), userInitiated,
322 323
                 isForcedSingleton, self->creationDate(),
                 self->lastModificationDate() ) == false )
324
        return nullptr;
325 326 327
    auto notifier = ml->getNotifier();
    if ( notifier != nullptr )
        notifier->notifyMediaGroupCreation( self );
328 329 330
    return self;
}

331 332 333
std::shared_ptr<MediaGroup> MediaGroup::create( MediaLibraryPtr ml,
                                                const std::vector<int64_t>& mediaIds )
{
334
    std::vector<MediaPtr> media;
335
    std::string name;
336 337 338 339 340
    for ( const auto mId : mediaIds )
    {
        auto m = ml->media( mId );
        if ( m == nullptr )
            continue;
341 342 343 344 345 346 347 348 349 350 351 352 353
        if ( media.empty() == true )
        {
            /*
             * Only assign the media title for the first media. If at a later
             * point there is no match, we will empty 'name', and we'd end up
             * reseting it to an arbitrary media title if we'd only check if
             * 'name' was empty before assigning it
             */
            assert( name.empty() == true );
            name = m->title();
        }
        else
            name = commonPattern( name, m->title() );
354 355 356 357
        media.push_back( std::move( m ) );
    }
    if ( media.size() == 0 )
        return nullptr;
358
    static const std::string req = "INSERT INTO " + Table::Name +
359 360 361 362
            "(name, user_interacted, forced_singleton, creation_date, last_modification_date) "
            "VALUES(?, ?, ?, ?, ?)";
    auto self = std::make_shared<MediaGroup>( ml, std::move( name ) );
    if ( insert( ml, self, req, self->name(), true, false, self->creationDate(),
363
                 self->lastModificationDate() ) == false )
364 365 366 367
        return nullptr;
    auto notifier = ml->getNotifier();
    if ( notifier != nullptr )
        notifier->notifyMediaGroupCreation( self );
368
    for ( const auto& m : media )
369
    {
370
        self->add( *m );
371 372 373 374
    }
    return self;
}

375 376 377
std::vector<std::shared_ptr<MediaGroup>>
MediaGroup::fetchMatching( MediaLibraryPtr ml, const std::string& prefix )
{
378 379
    if ( prefix.length() < AutomaticGroupPrefixSize )
        return {};
380
    static const std::string req = "SELECT * FROM " + Table::Name +
381 382
            " WHERE forced_singleton = 0"
            " AND SUBSTR(name, 1, ?) = ? COLLATE NOCASE";
383 384 385
    return fetchAll<MediaGroup>( ml, req, prefix.length(), prefix );
}

386 387 388
Query<IMediaGroup> MediaGroup::listAll( MediaLibraryPtr ml,
                                        const QueryParameters* params )
{
389
    const std::string req = "FROM " + Table::Name + " mg";
390 391 392
    return make_query<MediaGroup, IMediaGroup>( ml, "mg.*", req, orderBy( params ) );
}

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
393 394 395 396 397 398 399 400 401 402
Query<IMediaGroup> MediaGroup::search( MediaLibraryPtr ml, const std::string& pattern,
                                       const QueryParameters* params )
{
    const std::string req = "FROM " + Table::Name + " mg"
            " WHERE id_group IN (SELECT rowid FROM " + FtsTable::Name +
                " WHERE " + FtsTable::Name + " MATCH ?)";
    return make_query<MediaGroup, IMediaGroup>( ml, "mg.*", req, orderBy( params ),
                                                sqlite::Tools::sanitizePattern( pattern ) );
}

403 404 405 406
void MediaGroup::createTable(sqlite::Connection* dbConnection )
{
    sqlite::Tools::executeRequest( dbConnection,
                                   schema( Table::Name, Settings::DbModelVersion ) );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
407 408 409 410 411 412 413 414 415 416
    sqlite::Tools::executeRequest( dbConnection,
                                   schema( FtsTable::Name, Settings::DbModelVersion ) );
}

void MediaGroup::createTriggers( sqlite::Connection* connection )
{
    sqlite::Tools::executeRequest( connection,
                                   trigger( Triggers::InsertFts, Settings::DbModelVersion ) );
    sqlite::Tools::executeRequest( connection,
                                   trigger( Triggers::DeleteFts, Settings::DbModelVersion ) );
417
    sqlite::Tools::executeRequest( connection,
418
                                   trigger( Triggers::UpdateNbMedia, Settings::DbModelVersion ) );
419 420
    sqlite::Tools::executeRequest( connection,
                                   trigger( Triggers::DecrementNbMediaOnDeletion, Settings::DbModelVersion ) );
421 422
    sqlite::Tools::executeRequest( connection,
                                   trigger( Triggers::DeleteEmptyGroups, Settings::DbModelVersion ) );
423 424
    sqlite::Tools::executeRequest( connection,
                                   trigger( Triggers::RenameForcedSingleton, Settings::DbModelVersion ) );
425 426 427 428
    sqlite::Tools::executeRequest( connection,
                                   trigger( Triggers::UpdateDurationOnMediaChange, Settings::DbModelVersion ) );
    sqlite::Tools::executeRequest( connection,
                                   trigger( Triggers::UpdateDurationOnMediaDeletion, Settings::DbModelVersion ) );
429 430
}

431
void MediaGroup::createIndexes( sqlite::Connection* connection )
432
{
433 434
    sqlite::Tools::executeRequest( connection,
                                   index( Indexes::ForcedSingleton, Settings::DbModelVersion ) );
435 436 437 438 439 440
    sqlite::Tools::executeRequest( connection,
                                   index( Indexes::Duration, Settings::DbModelVersion ) );
    sqlite::Tools::executeRequest( connection,
                                   index( Indexes::CreationDate, Settings::DbModelVersion ) );
    sqlite::Tools::executeRequest( connection,
                                   index( Indexes::LastModificationDate, Settings::DbModelVersion ) );
441 442
}

443 444 445
std::string MediaGroup::schema( const std::string& name, uint32_t dbModel )
{
    assert( dbModel >= 24 );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
446 447 448 449 450
    if ( name == FtsTable::Name )
    {
        return "CREATE VIRTUAL TABLE " + FtsTable::Name +
                   " USING FTS3(name)";
    }
451
    assert( name == Table::Name );
452 453 454 455 456 457 458 459 460 461 462 463 464 465 466
    if ( dbModel == 24 )
    {
        return "CREATE TABLE " + Table::Name +
        "("
            "id_group INTEGER PRIMARY KEY AUTOINCREMENT,"
            "parent_id INTEGER,"
            "name TEXT COLLATE NOCASE,"
            "nb_video UNSIGNED INTEGER DEFAULT 0,"
            "nb_audio UNSIGNED INTEGER DEFAULT 0,"
            "nb_unknown UNSIGNED INTEGER DEFAULT 0,"
            "FOREIGN KEY(parent_id) REFERENCES " + Table::Name +
                "(id_group) ON DELETE CASCADE,"
            "UNIQUE(parent_id, name) ON CONFLICT FAIL"
        ")";
    }
467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482
    if ( dbModel == 25 )
    {
        return "CREATE TABLE " + Table::Name +
        "("
            "id_group INTEGER PRIMARY KEY AUTOINCREMENT,"
            "name TEXT COLLATE NOCASE,"
            "nb_video UNSIGNED INTEGER DEFAULT 0,"
            "nb_audio UNSIGNED INTEGER DEFAULT 0,"
            "nb_unknown UNSIGNED INTEGER DEFAULT 0,"
            "duration INTEGER DEFAULT 0,"
            "creation_date INTEGER NOT NULL,"
            "last_modification_date INTEGER NOT NULL,"
            "user_interacted BOOLEAN,"
            "forced_singleton BOOLEAN"
        ")";
    }
483
    return "CREATE TABLE " + Table::Name +
484 485 486
    "("
        "id_group INTEGER PRIMARY KEY AUTOINCREMENT,"
        "name TEXT COLLATE NOCASE,"
487
        // Nb media per type, accounting for their presence.
488 489 490
        "nb_video UNSIGNED INTEGER DEFAULT 0,"
        "nb_audio UNSIGNED INTEGER DEFAULT 0,"
        "nb_unknown UNSIGNED INTEGER DEFAULT 0,"
491 492
        // Total number of media, regardless of presence
        "nb_media UNSIGNED INTEGER DEFAULT 0,"
493
        "duration INTEGER DEFAULT 0,"
494 495
        "creation_date INTEGER NOT NULL,"
        "last_modification_date INTEGER NOT NULL,"
496 497
        "user_interacted BOOLEAN,"
        "forced_singleton BOOLEAN"
498
    ")";
499 500
}

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
501 502 503 504 505 506
std::string MediaGroup::trigger( MediaGroup::Triggers t, uint32_t dbModel )
{
    assert( dbModel >= 24 );
    switch ( t )
    {
        case Triggers::InsertFts:
507
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
508 509 510 511 512 513
                    " AFTER INSERT ON " + Table::Name +
                    " BEGIN"
                    " INSERT INTO " + FtsTable::Name + "(rowid, name)"
                        " VALUES(new.rowid, new.name);"
                    " END";
        case Triggers::DeleteFts:
514
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
515 516 517 518 519
                   " AFTER DELETE ON " + Table::Name +
                   " BEGIN"
                   " DELETE FROM " + FtsTable::Name +
                       " WHERE rowid = old.id_group;"
                   " END";
520
        case Triggers::IncrementNbMediaOnGroupChange:
521 522
        {
            assert( dbModel < 26 );
523
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542
                    " AFTER UPDATE OF type, group_id ON " + Media::Table::Name +
                    " WHEN new.group_id IS NOT NULL AND"
                        " (old.type != new.type OR IFNULL(old.group_id, 0) != new.group_id)"
                    " BEGIN"
                    " UPDATE " + Table::Name + " SET"
                        " nb_video = nb_video + "
                            "(CASE new.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Video ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_audio = nb_audio + "
                            "(CASE new.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Audio ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_unknown = nb_unknown + "
                            "(CASE new.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Unknown ) ) +
543 544
                                " THEN 1 ELSE 0 END),"
                        " last_modification_date = strftime('%s')"
545 546
                    " WHERE id_group = new.group_id;"
                    " END";
547
        }
548
        case Triggers::DecrementNbMediaOnGroupChange:
549 550
        {
            assert( dbModel < 26 );
551
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570
                    " AFTER UPDATE OF type, group_id ON " + Media::Table::Name +
                    " WHEN old.group_id IS NOT NULL AND"
                        "(old.type != new.type OR old.group_id != IFNULL(new.group_id, 0))"
                    " BEGIN"
                    " UPDATE " + Table::Name + " SET"
                        " nb_video = nb_video - "
                            "(CASE old.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Video ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_audio = nb_audio - "
                            "(CASE old.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Audio ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_unknown = nb_unknown - "
                            "(CASE old.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Unknown ) ) +
571 572
                                " THEN 1 ELSE 0 END),"
                        " last_modification_date = strftime('%s')"
573 574
                    " WHERE id_group = old.group_id;"
                    " END";
575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623
        }
        case Triggers::UpdateNbMedia:
        {
            assert( dbModel >= 26 );
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
                    " AFTER UPDATE OF type, group_id ON " + Media::Table::Name +
                        " WHEN IFNULL(old.group_id, 0) != IFNULL(new.group_id, 0) OR"
                        " old.type != new.type"
                    " BEGIN"
                    // Handle increment
                    " UPDATE " + Table::Name + " SET"
                        " nb_video = nb_video + "
                            "(CASE new.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Video ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_audio = nb_audio + "
                            "(CASE new.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Audio ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_unknown = nb_unknown + "
                            "(CASE new.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Unknown ) ) +
                                " THEN 1 ELSE 0 END),"
                        " last_modification_date = strftime('%s')"
                    " WHERE new.group_id IS NOT NULL AND id_group = new.group_id;"
                    // Handle decrement
                    " UPDATE " + Table::Name + " SET"
                        " nb_video = nb_video - "
                            "(CASE old.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Video ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_audio = nb_audio - "
                            "(CASE old.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Audio ) ) +
                                " THEN 1 ELSE 0 END),"
                        " nb_unknown = nb_unknown - "
                            "(CASE old.type WHEN " +
                                std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                    IMedia::Type::Unknown ) ) +
                                " THEN 1 ELSE 0 END),"
                        " last_modification_date = strftime('%s')"
                    " WHERE old.group_id IS NOT NULL AND id_group = old.group_id;"
                    " END";
        }
624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643
        case Triggers::DecrementNbMediaOnDeletion:
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
                   " AFTER DELETE ON " + Media::Table::Name +
                   " WHEN old.group_id IS NOT NULL"
                   " BEGIN"
                   " UPDATE " + Table::Name + " SET"
                       " nb_video = nb_video - "
                           "(CASE old.type WHEN " +
                               std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                   IMedia::Type::Video ) ) +
                               " THEN 1 ELSE 0 END),"
                       " nb_audio = nb_audio - "
                           "(CASE old.type WHEN " +
                               std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                   IMedia::Type::Audio ) ) +
                               " THEN 1 ELSE 0 END),"
                       " nb_unknown = nb_unknown - "
                           "(CASE old.type WHEN " +
                               std::to_string( static_cast<std::underlying_type_t<IMedia::Type>>(
                                                   IMedia::Type::Unknown ) ) +
644 645
                               " THEN 1 ELSE 0 END),"
                       " last_modification_date = strftime('%s')"
646 647
                   " WHERE id_group = old.group_id;"
                   " END";
648 649 650 651 652 653 654 655 656
        case Triggers::DeleteEmptyGroups:
            assert( dbModel >= 25 );
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
                   " AFTER UPDATE OF nb_video, nb_audio, nb_unknown"
                       " ON " + Table::Name +
                   " WHEN new.nb_video = 0 AND new.nb_audio = 0 AND new.nb_unknown = 0"
                   " BEGIN"
                   " DELETE FROM " + Table::Name + " WHERE id_group = new.id_group;"
                   " END";
657 658 659 660 661 662 663 664 665 666
        case Triggers::RenameForcedSingleton:
            assert( dbModel >= 25 );
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
                   " AFTER UPDATE OF title ON " + Media::Table::Name +
                   " WHEN new.group_id IS NOT NULL"
                   " BEGIN"
                       " UPDATE " + Table::Name + " SET name = new.title"
                           " WHERE id_group = new.group_id"
                           " AND forced_singleton != 0;"
                   " END";
667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687
        case Triggers::UpdateDurationOnMediaChange:
            assert( dbModel >= 25 );
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
                   " AFTER UPDATE OF duration, group_id ON " + Media::Table::Name +
                   " BEGIN"
                       " UPDATE " + Table::Name +
                           " SET duration = duration - max(old.duration, 0)"
                           " WHERE id_group = old.group_id;"
                       " UPDATE " + Table::Name +
                           " SET duration = duration + max(new.duration, 0)"
                           " WHERE id_group = new.group_id;"
                   " END";
        case Triggers::UpdateDurationOnMediaDeletion:
            return "CREATE TRIGGER " + triggerName( t, dbModel ) +
                   " AFTER DELETE ON " + Media::Table::Name +
                   " WHEN old.group_id IS NOT NULL AND old.duration > 0"
                   " BEGIN"
                       " UPDATE " + Table::Name +
                           " SET duration = duration - old.duration"
                           " WHERE id_group = old.group_id;"
                   " END";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
688 689 690 691 692 693
        default:
            assert( !"Invalid trigger" );
    }
    return "<invalid request>";
}

694 695 696 697 698 699 700 701 702 703
std::string MediaGroup::triggerName(MediaGroup::Triggers t, uint32_t dbModel)
{
    assert( dbModel >= 24 );
    switch ( t )
    {
        case Triggers::InsertFts:
            return "media_group_insert_fts";
        case Triggers::DeleteFts:
            return "media_group_delete_fts";
        case Triggers::IncrementNbMediaOnGroupChange:
704
            assert( dbModel < 26 );
705 706
            return "media_group_increment_nb_media";
        case Triggers::DecrementNbMediaOnGroupChange:
707
            assert( dbModel < 26 );
708
            return "media_group_decrement_nb_media";
709 710
        case Triggers::DecrementNbMediaOnDeletion:
            return "media_group_decrement_nb_media_on_deletion";
711 712 713
        case Triggers::DeleteEmptyGroups:
            assert( dbModel >= 25 );
            return "media_group_delete_empty_group";
714 715 716
        case Triggers::RenameForcedSingleton:
            assert( dbModel >= 25 );
            return "media_group_rename_forced_singleton";
717 718 719 720 721 722
        case Triggers::UpdateDurationOnMediaChange:
            assert( dbModel >= 25 );
            return "media_group_update_duration_on_media_change";
        case Triggers::UpdateDurationOnMediaDeletion:
            assert( dbModel >= 25 );
            return "media_group_update_duration_on_media_deletion";
723 724 725
        case Triggers::UpdateNbMedia:
            assert( dbModel >= 26 );
            return "media_group_update_nb_media";
726 727 728 729 730 731
        default:
            assert( !"Invalid trigger" );
    }
    return "<invalid request>";
}

732 733
std::string MediaGroup::index( Indexes i, uint32_t dbModel )
{
734
    switch ( i )
735
    {
736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755
        case Indexes::ParentId:
            assert( dbModel == 24 );
            return "CREATE INDEX " + indexName( i, dbModel ) +
                   " ON " + Table::Name + "(parent_id)";
        case Indexes::ForcedSingleton:
            assert( dbModel >= 25 );
            return "CREATE INDEX " + indexName( i, dbModel ) +
                   " ON " + Table::Name + "(forced_singleton)";
        case Indexes::Duration:
            assert( dbModel >= 25 );
            return "CREATE INDEX " + indexName( i, dbModel ) +
                   " ON " + Table::Name + "(duration)";
        case Indexes::CreationDate:
            assert( dbModel >= 25 );
            return "CREATE INDEX " + indexName( i, dbModel ) +
                   " ON " + Table::Name + "(creation_date)";
        case Indexes::LastModificationDate:
            assert( dbModel >= 25 );
            return "CREATE INDEX " + indexName( i, dbModel ) +
                   " ON " + Table::Name + "(last_modification_date)";
756
    }
757
    return "<invalid request>";
758 759 760 761
}

std::string MediaGroup::indexName( Indexes i, uint32_t dbModel )
{
762
    switch ( i )
763
    {
764 765 766 767 768 769 770 771 772 773 774 775 776 777 778
        case Indexes::ParentId:
            assert( dbModel == 24 );
            return "media_group_parent_id_idx";
        case Indexes::ForcedSingleton:
            assert( dbModel >= 25 );
            return "media_group_forced_singleton";
        case Indexes::Duration:
            assert( dbModel >= 25 );
            return "media_group_duration";
        case Indexes::CreationDate:
            assert( dbModel >= 25 );
            return "media_group_creation_date";
        case Indexes::LastModificationDate:
            assert( dbModel >= 25 );
            return "media_group_last_modification_date";
779
    }
780
    return "<invalid request>";
781 782
}

783 784 785 786 787 788 789 790 791 792 793 794 795 796 797
bool MediaGroup::checkDbModel( MediaLibraryPtr ml )
{
    if ( sqlite::Tools::checkTableSchema( ml->getConn(),
                                       schema( Table::Name, Settings::DbModelVersion ),
                                       Table::Name ) == false ||
           sqlite::Tools::checkTableSchema( ml->getConn(),
                                       schema( FtsTable::Name, Settings::DbModelVersion ),
                                       FtsTable::Name ) == false )
        return false;

    auto check = []( sqlite::Connection* dbConn, Triggers t ) {
        return sqlite::Tools::checkTriggerStatement( dbConn,
                                    trigger( t, Settings::DbModelVersion ),
                                    triggerName( t, Settings::DbModelVersion ) );
    };
798 799 800 801 802 803
    auto checkIndex = []( sqlite::Connection* dbConn, Indexes i ) {
        return sqlite::Tools::checkIndexStatement( dbConn,
                                    index( i, Settings::DbModelVersion ),
                                    indexName( i, Settings::DbModelVersion ) );
    };

804 805
    return check( ml->getConn(), Triggers::InsertFts ) &&
            check( ml->getConn(), Triggers::DeleteFts ) &&
806
            check( ml->getConn(), Triggers::UpdateNbMedia ) &&
807 808 809 810 811
            check( ml->getConn(), Triggers::DecrementNbMediaOnDeletion ) &&
            check( ml->getConn(), Triggers::DeleteEmptyGroups ) &&
            check( ml->getConn(), Triggers::RenameForcedSingleton ) &&
            check( ml->getConn(), Triggers::UpdateDurationOnMediaChange ) &&
            check( ml->getConn(), Triggers::UpdateDurationOnMediaDeletion ) &&
812 813 814 815
            checkIndex( ml->getConn(), Indexes::ForcedSingleton ) &&
            checkIndex( ml->getConn(), Indexes::Duration ) &&
            checkIndex( ml->getConn(), Indexes::CreationDate ) &&
            checkIndex( ml->getConn(), Indexes::LastModificationDate );
816 817
}

818 819 820 821
bool MediaGroup::assignToGroup( MediaLibraryPtr ml, Media& m )
{
    assert( m.groupId() == 0 );
    auto title = m.title();
822 823 824
    auto p = prefix( title );
    auto groups = MediaGroup::fetchMatching( ml, p );
    if ( groups.empty() == true )
825
    {
826 827
        if ( strncasecmp( title.c_str(), "the ", 4 ) == 0 )
            title = title.substr( 4 );
828
        auto group = create( ml, std::move( title ), false, false );
829 830
        if ( group == nullptr )
            return false;
831 832 833 834 835 836 837 838 839 840 841 842 843
        return group->add( m );
    }
    std::string longestPattern;
    std::shared_ptr<MediaGroup> target;
    for ( const auto& group : groups )
    {
        auto match = commonPattern( group->name(), title );
        assert( match.empty() == false );
        if ( match.length() > longestPattern.length() )
        {
            longestPattern = match;
            target = group;
        }
844
    }
845 846 847 848 849
    if ( target == nullptr )
    {
        assert( !"There should have been a match" );
        return false;
    }
850
    if ( target->userInteracted() == false &&
851 852
         target->rename( longestPattern, false ) == false )
    {
853
        return false;
854
    }
855 856 857 858 859 860 861 862 863
    return target->add( m );
}

std::string MediaGroup::prefix( const std::string& title )
{
    auto offset = 0u;
    if ( strncasecmp( title.c_str(), "the ", 4 ) == 0 )
        offset = 4;
    return title.substr( offset, AutomaticGroupPrefixSize + offset );
864 865
}

866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886
std::string MediaGroup::commonPattern( const std::string& groupName,
                                       const std::string& newTitle )
{
    auto groupIdx = 0u;
    auto groupBegin = 0u;
    auto titleIdx = 0u;
    if ( strncasecmp( groupName.c_str(), "the ", 4 ) == 0 )
        groupBegin = groupIdx = 4u;
    if ( strncasecmp( newTitle.c_str(), "the ", 4 ) == 0 )
        titleIdx = 4;
    while ( groupIdx < groupName.size() && titleIdx < newTitle.size() &&
            tolower( groupName[groupIdx] ) == tolower( newTitle[titleIdx] ) )
    {
        ++groupIdx;
        ++titleIdx;
    }
    if ( groupIdx - groupBegin < AutomaticGroupPrefixSize )
        return {};
    return groupName.substr( groupBegin, groupIdx - groupBegin );
}

887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902
std::string MediaGroup::orderBy(const QueryParameters* params)
{
    std::string req = "ORDER BY ";
    auto sort = params != nullptr ? params->sort : SortingCriteria::Alpha;
    auto desc = params != nullptr ? params->desc : false;
    switch ( sort )
    {
        case SortingCriteria::NbAudio:
            req += "mg.nb_audio";
            break;
        case SortingCriteria::NbVideo:
            req += "mg.nb_video";
            break;
        case SortingCriteria::NbMedia:
            req += "mg.nb_audio + mg.nb_video + mg.nb_unknown";
            break;
903 904 905
        case SortingCriteria::Duration:
            req += "mg.duration";
            break;
906 907 908 909 910 911
        case SortingCriteria::InsertionDate:
            req += "mg.creation_date";
            break;
        case SortingCriteria::LastModificationDate:
            req += "mg.last_modification_date";
            break;
912 913 914 915 916
        default:
            LOG_WARN( "Unsupported sorting criteria for media groups: ",
                      static_cast<std::underlying_type_t<SortingCriteria>>( sort ),
                      ". Falling back to default (Alpha)" );
            /* fall-through */
917
        case SortingCriteria::Default:
918 919 920 921 922 923 924 925 926 927
        case SortingCriteria::Alpha:
            req += "mg.name";
            break;
    }
    if ( desc == true )
        req += " DESC";
    return req;
}

}