Playlist.cpp 9.39 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
}

bool Playlist::createTable( DBConnection dbConn )
{
148
    const std::string req = "CREATE TABLE IF NOT EXISTS " + policy::PlaylistTable::Name + "("
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
149
            + 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
    const std::string relTableReq = "CREATE TABLE IF NOT EXISTS PlaylistMediaRelation("
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
154
155
156
157
158
159
160
161
162
            "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
    const std::string vtableReq = "CREATE VIRTUAL TABLE IF NOT EXISTS "
164
                + 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
{
    static const std::string req = "SELECT * FROM " + policy::PlaylistTable::Name + " WHERE id_playlist IN "
227
228
            "(SELECT rowid FROM " + policy::PlaylistTable::Name + "Fts WHERE name MATCH '*' || ? || '*')";
    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

}