Playlist.cpp 9.36 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
23
24
25
26
27
28
29
30
/*****************************************************************************
 * 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.
 *****************************************************************************/

#include "Playlist.h"

#include "Media.h"

namespace policy
{
const std::string PlaylistTable::Name = "Playlist";
const std::string PlaylistTable::PrimaryKeyColumn = "id_playlist";
31
int64_t Playlist::* const PlaylistTable::PrimaryKey = &Playlist::m_id;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
32
33
}

34
Playlist::Playlist( MediaLibraryPtr ml, sqlite::Row& row )
35
    : m_ml( ml )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
36
37
{
    row >> m_id
38
39
        >> m_name
        >> m_creationDate;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
40
41
}

42
43
44
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
45
    , m_name( name )
46
    , m_creationDate( time( nullptr ) )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
47
48
49
{
}

50
std::shared_ptr<Playlist> Playlist::create( MediaLibraryPtr ml, const std::string& name )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
51
{
52
    auto self = std::make_shared<Playlist>( ml, name );
53
54
    static const std::string req = "INSERT INTO " + policy::PlaylistTable::Name + \
            "(name, creation_date) VALUES(?, ?)";
55
56
57
58
59
60
61
62
63
64
65
    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
66
67
}

68
int64_t Playlist::id() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
69
70
71
72
73
74
75
76
77
78
79
80
81
82
{
    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 = ?";
83
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, name, m_id ) == false )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
84
85
86
87
88
        return false;
    m_name = name;
    return true;
}

89
90
91
92
93
unsigned int Playlist::creationDate() const
{
    return m_creationDate;
}

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
94
95
96
97
98
99
std::vector<MediaPtr> Playlist::media() const
{
    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";
100
    return Media::fetchAll<IMedia>( m_ml, req, m_id );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
101
102
}

103
bool Playlist::append( int64_t mediaId )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
104
105
106
107
{
    return add( mediaId, 0 );
}

108
bool Playlist::add( int64_t mediaId, unsigned int position )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
109
110
111
112
{
    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.
113
114
115
116
117
118
119
120
121
    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
122
123
}

124
bool Playlist::move( int64_t mediaId, unsigned int position )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
125
126
127
128
129
{
    if ( position == 0 )
        return false;
    static const std::string req = "UPDATE PlaylistMediaRelation SET position = ? WHERE "
            "playlist_id = ? AND media_id = ?";
130
    return sqlite::Tools::executeUpdate( m_ml->getConn(), req, position, m_id, mediaId );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
131
132
}

133
bool Playlist::remove( int64_t mediaId )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
134
135
{
    static const std::string req = "DELETE FROM PlaylistMediaRelation WHERE playlist_id = ? AND media_id = ?";
136
    return sqlite::Tools::executeDelete( m_ml->getConn(), req, m_id, mediaId );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
137
138
139
140
141
142
}

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,"
143
            "name TEXT UNIQUE,"
144
            "creation_date UNSIGNED INT NOT NULL"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
145
146
147
148
149
150
151
152
153
154
155
        ")";
    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"
        ")";
156
157
    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
158
159
                "name"
            ")";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
160
161
    //FIXME Enforce (playlist_id,position) uniqueness
    return sqlite::Tools::executeRequest( dbConn, req ) &&
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
162
163
            sqlite::Tools::executeRequest( dbConn, relTableReq ) &&
            sqlite::Tools::executeRequest( dbConn, vtableReq );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
}

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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
    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
209
210
    return sqlite::Tools::executeRequest( dbConn, req ) &&
            sqlite::Tools::executeRequest( dbConn, autoAppendReq ) &&
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
211
212
213
214
215
216
            sqlite::Tools::executeRequest( dbConn, autoShiftPosReq ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerInsert ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerUpdate ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerDelete );
}

217
std::vector<PlaylistPtr> Playlist::search( MediaLibraryPtr ml, const std::string& name )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
218
219
220
{
    static const std::string req = "SELECT * FROM " + policy::PlaylistTable::Name + " WHERE id_playlist IN "
            "(SELECT rowid FROM " + policy::PlaylistTable::Name + "Fts WHERE name MATCH ?)";
221
    return fetchAll<IPlaylist>( ml, req, name + "*" );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
222
}
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239

std::vector<PlaylistPtr> Playlist::listAll( MediaLibraryPtr ml, medialibrary::SortingCriteria sort, bool desc )
{
    std::string req = "SELECT * FROM " + policy::PlaylistTable::Name + " ORDER BY ";
    switch ( sort )
    {
    case medialibrary::SortingCriteria::InsertionDate:
        req += "creation_date";
        break;
    default:
        req += "name";
        break;
    }
    if ( desc == true )
        req += " DESC";
    return fetchAll<IPlaylist>( ml, req );
}