Playlist.cpp 9.41 KB
Newer Older
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
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

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
27 28 29 30
#include "Playlist.h"

#include "Media.h"

31 32 33
namespace medialibrary
{

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
34 35 36 37
namespace policy
{
const std::string PlaylistTable::Name = "Playlist";
const std::string PlaylistTable::PrimaryKeyColumn = "id_playlist";
38
int64_t Playlist::* const PlaylistTable::PrimaryKey = &Playlist::m_id;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
39 40
}

41
Playlist::Playlist( MediaLibraryPtr ml, sqlite::Row& row )
42
    : m_ml( ml )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
43 44
{
    row >> m_id
45 46
        >> m_name
        >> m_creationDate;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
47 48
}

49 50 51
Playlist::Playlist( MediaLibraryPtr ml, const std::string& name )
    : m_ml( ml )
    , m_id( 0 )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
52
    , m_name( name )
53
    , m_creationDate( time( nullptr ) )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
54 55 56
{
}

57
std::shared_ptr<Playlist> Playlist::create( MediaLibraryPtr ml, const std::string& name )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
58
{
59
    auto self = std::make_shared<Playlist>( ml, name );
60 61
    static const std::string req = "INSERT INTO " + policy::PlaylistTable::Name + \
            "(name, creation_date) VALUES(?, ?)";
62 63 64 65 66 67 68 69 70 71 72
    try
    {
        if ( insert( ml, self, req, name, self->m_creationDate ) == false )
            return nullptr;
        return self;
    }
    catch( sqlite::errors::ConstraintViolation& ex )
    {
        LOG_WARN( "Failed to create playlist: ", ex.what() );
    }
    return nullptr;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
73 74
}

75
int64_t Playlist::id() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
76 77 78 79 80 81 82 83 84 85 86 87 88 89
{
    return m_id;
}

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

bool Playlist::setName( const std::string& name )
{
    if ( name == m_name )
        return true;
    static const std::string req = "UPDATE " + policy::PlaylistTable::Name + " SET name = ? WHERE id_playlist = ?";
90
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, name, m_id ) == false )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
91 92 93 94 95
        return false;
    m_name = name;
    return true;
}

96 97 98 99 100
unsigned int Playlist::creationDate() const
{
    return m_creationDate;
}

101
std::vector<MediaPtr> Playlist::media() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
102 103 104 105 106
{
    static const std::string req = "SELECT m.* FROM " + policy::MediaTable::Name + " m "
            "LEFT JOIN PlaylistMediaRelation pmr ON pmr.media_id = m.id_media "
            "WHERE pmr.playlist_id = ? AND m.is_present = 1 "
            "ORDER BY pmr.position";
107
    return Media::fetchAll<IMedia>( m_ml, req, m_id );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
108 109
}

110
bool Playlist::append( int64_t mediaId )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
111 112 113 114
{
    return add( mediaId, 0 );
}

115
bool Playlist::add( int64_t mediaId, unsigned int position )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
116 117 118 119
{
    static const std::string req = "INSERT INTO PlaylistMediaRelation(media_id, playlist_id, position) VALUES(?, ?, ?)";
    // position isn't a foreign key, but we want it to be passed as NULL if it equals to 0
    // When the position is NULL, the insertion triggers takes care of counting the number of records to auto append.
120 121 122 123 124 125 126 127 128
    try
    {
        return sqlite::Tools::executeInsert( m_ml->getConn(), req, mediaId, m_id, sqlite::ForeignKey{ position } );
    }
    catch (const sqlite::errors::ConstraintViolation& ex)
    {
        LOG_WARN( "Rejected playlist insertion: ", ex.what() );
        return false;
    }
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
129 130
}

131
bool Playlist::move( int64_t mediaId, unsigned int position )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
132 133 134 135 136
{
    if ( position == 0 )
        return false;
    static const std::string req = "UPDATE PlaylistMediaRelation SET position = ? WHERE "
            "playlist_id = ? AND media_id = ?";
137
    return sqlite::Tools::executeUpdate( m_ml->getConn(), req, position, m_id, mediaId );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
138 139
}

140
bool Playlist::remove( int64_t mediaId )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
141 142
{
    static const std::string req = "DELETE FROM PlaylistMediaRelation WHERE playlist_id = ? AND media_id = ?";
143
    return sqlite::Tools::executeDelete( m_ml->getConn(), req, m_id, mediaId );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
144 145 146 147 148 149
}

bool Playlist::createTable( DBConnection dbConn )
{
    static const std::string req = "CREATE TABLE IF NOT EXISTS " + policy::PlaylistTable::Name + "("
            + policy::PlaylistTable::PrimaryKeyColumn + " INTEGER PRIMARY KEY AUTOINCREMENT,"
150
            "name TEXT UNIQUE,"
151
            "creation_date UNSIGNED INT NOT NULL"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
152 153 154 155 156 157 158 159 160 161 162
        ")";
    static const std::string relTableReq = "CREATE TABLE IF NOT EXISTS PlaylistMediaRelation("
            "media_id INTEGER,"
            "playlist_id INTEGER,"
            "position INTEGER,"
            "PRIMARY KEY(media_id, playlist_id),"
            "FOREIGN KEY(media_id) REFERENCES " + policy::MediaTable::Name + "("
                + policy::MediaTable::PrimaryKeyColumn + ") ON DELETE CASCADE,"
            "FOREIGN KEY(playlist_id) REFERENCES " + policy::PlaylistTable::Name + "("
                + policy::PlaylistTable::PrimaryKeyColumn + ") ON DELETE CASCADE"
        ")";
163 164
    static const std::string vtableReq = "CREATE VIRTUAL TABLE IF NOT EXISTS "
                + policy::PlaylistTable::Name + "Fts USING FTS3("
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
165 166
                "name"
            ")";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
167 168
    //FIXME Enforce (playlist_id,position) uniqueness
    return sqlite::Tools::executeRequest( dbConn, req ) &&
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
169 170
            sqlite::Tools::executeRequest( dbConn, relTableReq ) &&
            sqlite::Tools::executeRequest( dbConn, vtableReq );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200
}

bool Playlist::createTriggers( DBConnection dbConn )
{
    static const std::string req = "CREATE TRIGGER IF NOT EXISTS update_playlist_order AFTER UPDATE OF position"
            " ON PlaylistMediaRelation"
            " BEGIN "
                "UPDATE PlaylistMediaRelation SET position = position + 1"
                " WHERE playlist_id = new.playlist_id"
                " AND position = new.position"
                // We don't to trigger a self-update when the insert trigger fires.
                " AND media_id != new.media_id;"
            " END";
    static const std::string autoAppendReq = "CREATE TRIGGER IF NOT EXISTS append_new_playlist_record AFTER INSERT"
            " ON PlaylistMediaRelation"
            " WHEN new.position IS NULL"
            " BEGIN "
                " UPDATE PlaylistMediaRelation SET position = ("
                    "SELECT COUNT(media_id) FROM PlaylistMediaRelation WHERE playlist_id = new.playlist_id"
                ") WHERE playlist_id=new.playlist_id AND media_id = new.media_id;"
            " END";
    static const std::string autoShiftPosReq = "CREATE TRIGGER IF NOT EXISTS update_playlist_order_on_insert AFTER INSERT"
            " ON PlaylistMediaRelation"
            " WHEN new.position IS NOT NULL"
            " BEGIN "
                "UPDATE PlaylistMediaRelation SET position = position + 1"
                " WHERE playlist_id = new.playlist_id"
                " AND position = new.position"
                " AND media_id != new.media_id;"
            " END";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
    static const std::string vtriggerInsert = "CREATE TRIGGER IF NOT EXISTS insert_playlist_fts AFTER INSERT ON "
            + policy::PlaylistTable::Name +
            " BEGIN"
            " INSERT INTO " + policy::PlaylistTable::Name + "Fts(rowid, name) VALUES(new.id_playlist, new.name);"
            " END";
    static const std::string vtriggerUpdate = "CREATE TRIGGER IF NOT EXISTS update_playlist_fts AFTER UPDATE OF name"
            " ON " + policy::PlaylistTable::Name +
            " BEGIN"
            " UPDATE " + policy::PlaylistTable::Name + "Fts SET name = new.name WHERE rowid = new.id_playlist;"
            " END";
    static const std::string vtriggerDelete = "CREATE TRIGGER IF NOT EXISTS delete_playlist_fts BEFORE DELETE ON "
            + policy::PlaylistTable::Name +
            " BEGIN"
            " DELETE FROM " + policy::PlaylistTable::Name + "Fts WHERE rowid = old.id_playlist;"
            " END";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
216 217
    return sqlite::Tools::executeRequest( dbConn, req ) &&
            sqlite::Tools::executeRequest( dbConn, autoAppendReq ) &&
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
218 219 220 221 222 223
            sqlite::Tools::executeRequest( dbConn, autoShiftPosReq ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerInsert ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerUpdate ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerDelete );
}

224
std::vector<PlaylistPtr> Playlist::search( MediaLibraryPtr ml, const std::string& name )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
225 226 227
{
    static const std::string req = "SELECT * FROM " + policy::PlaylistTable::Name + " WHERE id_playlist IN "
            "(SELECT rowid FROM " + policy::PlaylistTable::Name + "Fts WHERE name MATCH ?)";
228
    return fetchAll<IPlaylist>( ml, req, name + "*" );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
229
}
230

231
std::vector<PlaylistPtr> Playlist::listAll( MediaLibraryPtr ml, SortingCriteria sort, bool desc )
232 233 234 235
{
    std::string req = "SELECT * FROM " + policy::PlaylistTable::Name + " ORDER BY ";
    switch ( sort )
    {
236
    case SortingCriteria::InsertionDate:
237 238 239 240 241 242 243 244 245 246
        req += "creation_date";
        break;
    default:
        req += "name";
        break;
    }
    if ( desc == true )
        req += " DESC";
    return fetchAll<IPlaylist>( ml, req );
}
247 248

}