summaryrefslogtreecommitdiffstats
path: root/examples/mediaplayer/spmodel.cpp
blob: 399c4cf0545171aed37153f65b8ba01e8d9eb37a (plain)
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
/****************************************************************************
**
** Copyright (C) 2008-2009 Nokia Corporation and/or its subsidiary(-ies).
** Contact: Qt Software Information (qt-info@nokia.com)
**
** This file is part of the SCXML project on Trolltech Labs.
**
** This file may be used under the terms of the GNU General Public
** License version 2.0 or 3.0 as published by the Free Software Foundation
** and appearing in the file LICENSE.GPL included in the packaging of
** this file.  Please review the following information to ensure GNU
** General Public Licensing requirements will be met:
** http://www.fsf.org/licensing/licenses/info/GPLv2.html and
** http://www.gnu.org/copyleft/gpl.html.
**
** If you are unsure which license is appropriate for your use, please
** contact the sales department at qt-sales@nokia.com.
**
** This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE
** WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
**
****************************************************************************/
#include "spmodel.h"
#include <QSqlQueryModel>
#include <QtSql>
#include <QStandardItemModel>
#include <QMessageBox>
// an SQL query model that always as column (0) as uid and column (1) as display
class SPSqlQueryModel : public QSqlQueryModel
{
    Q_OBJECT
    public:
    SPSqlQueryModel (QObject* o = NULL): QSqlQueryModel (o) {}
    virtual QVariant data(const QModelIndex & index, int role) const
    {
        QModelIndex idx(index);
        if (role == Qt::DisplayRole && query().record().count() > 1) {
            idx = idx.sibling(idx.row(),1);
        } else if (role == Qt::UserRole)
            role = Qt::DisplayRole;
        return QSqlQueryModel::data(idx,role);
    }
};


class SPModelPvt
{
    public:
    SPSqlQueryModel 
                    artistModel, 
                    albumModel, 
                    songModel,
                    playlistModel, genreModel;

    QSqlQuery artistQuery, albumQuery, songQuery, playlistQuery, genreQuery, playingQuery;

};

SPModel::SPModel(QObject* o)
    :QObject(o)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("sqlite.db");
    if (!db.open()) {
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
            qApp->tr("Unable to establish a database connection.\n"
                     "This example needs SQLite support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit."), QMessageBox::Cancel);
    }

    db.exec("CREATE TABLE IF NOT EXISTS songs (song_url VARCHAR(1024) PRIMARY KEY, song_title VARCHAR(1024), song_artist VARCHAR(1024), song_album VARCHAR(1024), song_track_index SMALLINT)");
    db.exec("DROP TABLE genres");
    db.exec("CREATE TABLE IF NOT EXISTS genres (genre_title VARCHAR(64), genre_song_url VARCHAR(1024))");
//    db.exec("CREATE TABLE IF NOT EXISTS playlists (playlist_id VARACHAR(256) PRIMARY KEY, playlist_title VARCHAR(1024))");
//    db.exec("CREATE TABLE IF NOT EXISTS playlist_songs (playlist_song_id BIGINT PRIMARY KEY, playlist_song_playlist

    pvt = new SPModelPvt;
    pvt->artistQuery = QSqlQuery("SELECT DISTINCT song_artist FROM songs");
    pvt->albumQuery = QSqlQuery("SELECT DISTINCT song_album FROM songs");
    pvt->playlistQuery = QSqlQuery("SELECT playlist_url, playlist_title FROM playlists");
    pvt->songQuery = QSqlQuery("SELECT song_url, song_title from songs");
    pvt->genreQuery = QSqlQuery("SELECT DISTINCT genre_title from genres");
    QSqlQuery q;
//    q.exec("SELECT DISTINCT song_url,song_title FROM songs, genres WHERE genre_song_url=song_url AND genre_title='All'");
    q.exec("SELECT * from genres");
    while (q.next()) {
        qDebug () << q.value(0);
    }
}

void SPModel::addSong ( const SongData & data)
{

    QSqlQuery q;
    q.prepare("SELECT count(*) FROM songs WHERE song_url=:url");
    q.bindValue(":url",data.url);
    bool inserting = true;
    if (q.exec()) {
        q.next();
        inserting = q.value(0).toInt() == 0;
    }
    if (inserting) {
        q.prepare ("INSERT INTO songs (song_url, song_title, song_artist, song_album, song_track_index) VALUES (:url, :title, :artist, :album, :track)");
    } else {
        q.prepare("UPDATE songs SET song_title=:title, song_album=:album, song_track_index=:track WHERE song_url=:url ");
    }
    q.bindValue(":url",data.url);
    q.bindValue(":title",data.title);
    q.bindValue(":artist",data.artist);
    q.bindValue(":album",data.album);
    q.bindValue(":track",data.trackNumber);
    q.exec();

    q.prepare ("DELETE FROM genres WHERE genre_song_url=:url");
    q.bindValue(":url",data.url);
    q.exec();

    q.prepare ("INSERT INTO genres (genre_song_url, genre_title) VALUES(:url, :genre)");
    q.bindValue(":url",data.url);
    QStringList gn = data.genres;
    gn << "All";
    foreach (QString g, gn) {
        q.bindValue(":genre",g);
        q.exec ();
    }

    if (inserting) {
        emit albumChanged(data.album);
        emit songListChanged();
        emit artistChanged(data.artist);
        foreach (QString g, data.genres) {
            emit genreChanged(g);
        }
    }
    

}
  

SPModel::~SPModel()
{
    delete pvt;
}

int SPModel::albumCount() const
{
    return pvt->albumModel.rowCount();
}

void SPModel::clearAlbumFilter ()
{
    pvt->albumQuery = QSqlQuery ("SELECT DISTINCT song_album FROM songs ");
}
void SPModel::clearSongFilter ()
{
    pvt->songQuery = QSqlQuery ("SELECT song_url, song_title FROM songs");
}
void SPModel::loadArtists ()
{
    pvt->artistQuery.exec ();
    pvt->artistModel.setQuery(pvt->artistQuery);
}
void SPModel::filterAlbumsByArtist(const QString & artist)
{
    pvt->albumQuery.prepare("SELECT DISTINCT song_album FROM songs WHERE song_artist=:artist");
    pvt->albumQuery.bindValue(":artist",artist);
}
void SPModel::filterSongsByAlbum(const QString & album)
{
    pvt->albumQuery.prepare("SELECT song_url,song_title, song_track_index FROM songs WHERE song_album=:album ORDER BY song_track_index");
    pvt->albumQuery.bindValue(":album",album);
}
void SPModel::loadGenres ()
{
    pvt->genreQuery.exec();
    pvt->genreModel.setQuery(pvt->genreQuery);
}

void SPModel::filterSongsByGenre(const QString & genre)
{
    pvt->songQuery.prepare ("SELECT DISTINCT song_url,song_title FROM songs, genres WHERE genre_song_url=song_url AND genre_title=:genre");
    pvt->songQuery.bindValue(":genre",genre);
}
void SPModel::loadPlaylists()
{
    pvt->playlistQuery.exec ();
    pvt->playlistModel.setQuery(pvt->playlistQuery);
}
void SPModel::loadAlbums()
{
    pvt->albumQuery.exec ();
    pvt->albumModel.setQuery(pvt->albumQuery);
}
void SPModel::filterSongsByPlaylist(const QString & uid)
{
    pvt->songQuery.prepare("SELECT DISTINCT song_url, song_title, playlist_song_index FROM playlist_songs INNER JOIN songs ON playlist_song_url=song_url WHERE playlist_id=:playlist ORDER BY playlist_song_index");
    pvt->songQuery.bindValue(":playlist",uid);
}
void SPModel::loadSongs ()
{
    pvt->songQuery.exec ();
    pvt->songModel.setQuery(pvt->songQuery);
}
QUrl SPModel::currentSong()
{
    if (pvt->playingQuery.isValid())
        return QUrl(pvt->playingQuery.value(0).toString());
    else
        return QUrl();
}
QString SPModel::currentSongTitle()
{
    if (pvt->playingQuery.isValid())
        return pvt->playingQuery.value(1).toString();
    else
        return QString();
}
QString SPModel::currentSongArtist()
{
    QSqlQuery q;
    q.prepare("SELECT song_artist FROM songs WHERE song_url=:url");
    q.bindValue(":url",currentSong().toString());
    q.exec();
    q.next();
    return q.value(0).toString();
}
QString SPModel::currentSongAlbum()
{
    QSqlQuery q;
    q.prepare("SELECT song_album FROM songs WHERE song_url=:url");
    q.bindValue(":url",currentSong().toString());
    q.exec();
    q.next();
    return q.value(0).toString();
}

void SPModel::selectSong (const QString & s)
{
    pvt->playingQuery = QSqlQuery(pvt->songQuery.executedQuery ());

    while (pvt->playingQuery.next()) {
        if (pvt->playingQuery.value(0).toString() == s) {
            emit songChanged ();
            return;
        }
    }
    emit endOfList ();
}

void SPModel::reset ()
{
    pvt->playingQuery = QSqlQuery(pvt->songQuery.executedQuery ());
    pvt->playingQuery.exec();
}

void SPModel::gotoNext()
{
    if (pvt->playingQuery.next()) {
        emit songChanged ();
    }else
        emit endOfList ();
}
void SPModel::gotoPrev()
{
    if (pvt->playingQuery.previous())
        emit songChanged ();
    else
        emit endOfList ();
}



QAbstractItemModel* SPModel::albumsItemModel() const
{
    return &pvt->albumModel;
}
QAbstractItemModel* SPModel::genresItemModel() const
{
    return &pvt->genreModel;
}
QAbstractItemModel* SPModel::songsItemModel() const
{
    return &pvt->songModel;
}
QAbstractItemModel* SPModel::playlistsItemModel() const
{
    return &pvt->playlistModel;
}
QAbstractItemModel* SPModel::artistsItemModel() const
{
    return &pvt->artistModel;
}

#include <spmodel.moc>