Playlist.cpp 9 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
55
    static const std::string req = "INSERT INTO " + policy::PlaylistTable::Name + \
            "(name, creation_date) VALUES(?, ?)";
    if ( insert( ml, self, req, name, self->m_creationDate ) == false )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
56
57
58
59
        return nullptr;
    return self;
}

60
int64_t Playlist::id() const
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
61
62
63
64
65
66
67
68
69
70
71
72
73
74
{
    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 = ?";
75
    if ( sqlite::Tools::executeUpdate( m_ml->getConn(), req, name, m_id ) == false )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
76
77
78
79
80
        return false;
    m_name = name;
    return true;
}

81
82
83
84
85
unsigned int Playlist::creationDate() const
{
    return m_creationDate;
}

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
86
87
88
89
90
91
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";
92
    return Media::fetchAll<IMedia>( m_ml, req, m_id );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
93
94
}

95
bool Playlist::append( int64_t mediaId )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
96
97
98
99
{
    return add( mediaId, 0 );
}

100
bool Playlist::add( int64_t mediaId, unsigned int position )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
101
102
103
104
{
    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.
105
    return sqlite::Tools::insert( m_ml->getConn(), req, mediaId, m_id, sqlite::ForeignKey{ position } );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
106
107
}

108
bool Playlist::move( int64_t mediaId, unsigned int position )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
109
110
111
112
113
{
    if ( position == 0 )
        return false;
    static const std::string req = "UPDATE PlaylistMediaRelation SET position = ? WHERE "
            "playlist_id = ? AND media_id = ?";
114
    return sqlite::Tools::executeUpdate( m_ml->getConn(), req, position, m_id, mediaId );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
115
116
}

117
bool Playlist::remove( int64_t mediaId )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
118
119
{
    static const std::string req = "DELETE FROM PlaylistMediaRelation WHERE playlist_id = ? AND media_id = ?";
120
    return sqlite::Tools::executeDelete( m_ml->getConn(), req, m_id, mediaId );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
121
122
123
124
125
126
}

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,"
127
128
            "name TEXT,"
            "creation_date UNSIGNED INT NOT NULL"
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
129
130
131
132
133
134
135
136
137
138
139
        ")";
    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"
        ")";
140
141
    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
142
143
                "name"
            ")";
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
144
145
    //FIXME Enforce (playlist_id,position) uniqueness
    return sqlite::Tools::executeRequest( dbConn, req ) &&
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
146
147
            sqlite::Tools::executeRequest( dbConn, relTableReq ) &&
            sqlite::Tools::executeRequest( dbConn, vtableReq );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
}

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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
    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
193
194
    return sqlite::Tools::executeRequest( dbConn, req ) &&
            sqlite::Tools::executeRequest( dbConn, autoAppendReq ) &&
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
195
196
197
198
199
200
            sqlite::Tools::executeRequest( dbConn, autoShiftPosReq ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerInsert ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerUpdate ) &&
            sqlite::Tools::executeRequest( dbConn, vtriggerDelete );
}

201
std::vector<PlaylistPtr> Playlist::search( MediaLibraryPtr ml, const std::string& name )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
202
203
204
{
    static const std::string req = "SELECT * FROM " + policy::PlaylistTable::Name + " WHERE id_playlist IN "
            "(SELECT rowid FROM " + policy::PlaylistTable::Name + "Fts WHERE name MATCH ?)";
205
    return fetchAll<IPlaylist>( ml, req, name + "*" );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
206
}
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

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 );
}