2 * SomePlayer - An alternate music player for Maemo 5
3 * Copyright (C) 2010 Nikolay (somebody) Tischenko <niktischenko@gmail.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
20 #include "dbstorage.h"
25 using namespace SomePlayer::Storage;
26 using namespace SomePlayer::DataObjects;
28 DbStorage::DbStorage(QString path) {
29 QString dbname = path+_DATABASE_NAME_;
30 db = QSqlDatabase::addDatabase("QSQLITE");
31 db.setDatabaseName(dbname);
33 // throw big exception
35 // create database structure
36 _create_database_structure();
40 void DbStorage::_prepare_queries() {
41 _get_artists_query = new QSqlQuery(db);
42 _get_artists_query->prepare("SELECT name FROM artist ORDER BY name");
44 _get_albums_for_artist_query = new QSqlQuery(db);
45 _get_albums_for_artist_query->prepare("SELECT name FROM album WHERE artist_id in (SELECT id from artist WHERE UPPER(name) = UPPER(:name)) ORDER BY name;");
47 _get_tracks_for_album_query = new QSqlQuery(db);
48 _get_tracks_for_album_query->prepare("SELECT id, title, source, count, length FROM tracks WHERE artist_id IN "
49 "(SELECT id FROM artist WHERE UPPER(name) = UPPER(:artist_name)) AND album_id IN "
50 "(SELECT id FROM album WHERE UPPER(name) = UPPER(:album_name));");
52 _get_favorites_query = new QSqlQuery(db);
53 _get_favorites_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
54 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
55 "tracks JOIN artist ON tracks.artist_id = artist.id) "
56 "JOIN album ON album_id = album.id WHERE track_id IN "
57 "(SELECT track_id FROM favorites);");
59 _get_most_played_query = new QSqlQuery(db);
60 _get_most_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
61 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
62 "tracks JOIN artist ON tracks.artist_id = artist.id) "
63 "JOIN album ON album_id = album.id ORDER BY count DESC "
66 _get_never_played_query = new QSqlQuery(db);
67 _get_never_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
68 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
69 "tracks JOIN artist ON tracks.artist_id = artist.id) "
70 "JOIN album ON album_id = album.id "
73 _get_recently_added_query = new QSqlQuery(db);
74 _get_recently_added_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
75 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
76 "tracks JOIN artist ON tracks.artist_id = artist.id) "
77 "JOIN album ON album_id = album.id "
79 "(SELECT track_id FROM adding_date ORDER BY date DESC LIMIT 0, :max)");
81 _get_track_count = new QSqlQuery(db);
82 _get_track_count->prepare("SELECT count from tracks WHERE id = :id");
84 _get_track_by_source_query = new QSqlQuery(db);
85 _get_track_by_source_query->prepare("SELECT track_id AS id, title, artist, album.name AS album, source, count, length FROM "
86 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, tracks.album_id, length FROM "
87 "tracks JOIN artist ON tracks.artist_id = artist.id AND source = :source) "
88 "JOIN album ON album_id = album.id LIMIT 1");
90 _check_artist_query = new QSqlQuery(db);
91 _check_artist_query->prepare("SELECT id FROM artist WHERE UPPER(name) = UPPER(:name)");
93 _check_album_query = new QSqlQuery(db);
94 _check_album_query->prepare("SELECT id FROM album WHERE UPPER(name) = UPPER(:name) AND artist_id = :artist_id");
96 _check_track_query = new QSqlQuery(db);
97 _check_track_query->prepare("SELECT id FROM tracks WHERE source = :source");
99 _insert_artist_query = new QSqlQuery(db);
100 _insert_artist_query->prepare("INSERT INTO artist (name) values (:name)");
102 _insert_album_query = new QSqlQuery(db);
103 _insert_album_query->prepare("INSERT INTO album (name, artist_id) values (:name, :artist_id)");
105 _insert_track_query = new QSqlQuery(db);
106 _insert_track_query->prepare("INSERT INTO tracks (title, artist_id, album_id, source, length) values (:title, :artist_id, :album_id, :source, :length)");
108 _insert_date_query = new QSqlQuery(db);
109 _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))");
111 _insert_favorites_query = new QSqlQuery(db);
112 _insert_favorites_query->prepare("INSERT INTO favorites (track_id) values (:track_id)");
114 _update_track_count_query = new QSqlQuery(db);
115 _update_track_count_query->prepare("UPDATE tracks SET count = :count where id = :id");
117 _remove_track_query = new QSqlQuery(db);
118 _remove_track_query->prepare("DELETE FROM tracks WHERE id = :id");
121 void DbStorage::_create_database_structure() {
122 QSqlQuery *query = new QSqlQuery(db);
123 query->exec("create table artist (id integer primary key, "
126 query->exec("create table album (id integer primary key, "
127 "artist_id integer, "
129 "foreign key(artist_id) references arist(id) "
131 query->exec("create table tracks (id integer primary key, "
132 "artist_id integer, "
136 "count integer default 0, "
137 "length integer default 0, "
138 "foreign key(artist_id) references artist(id), "
139 "foreign key(album_id) references album(id) "
141 query->exec("create table favorites (track_id integer, "
142 "foreign key(track_id) references tracks(id) "
144 query->exec("create table adding_date (track_id integer, "
146 "foreign key(track_id) references tracks(id) "
150 DbStorage::~DbStorage() {
151 delete _get_albums_for_artist_query;
152 delete _get_artists_query;
153 delete _get_favorites_query;
154 delete _get_most_played_query;
155 delete _get_never_played_query;
156 delete _get_recently_added_query;
157 delete _get_tracks_for_album_query;
158 delete _get_track_by_source_query;
159 delete _check_album_query;
160 delete _check_artist_query;
161 delete _check_track_query;
162 delete _insert_album_query;
163 delete _insert_artist_query;
164 delete _insert_date_query;
165 delete _insert_track_query;
166 delete _insert_favorites_query;
167 delete _update_track_count_query;
168 delete _remove_track_query;
172 QList<QString> DbStorage::getArtists() {
173 QList<QString> artists;
174 QSqlQuery *query = _get_artists_query;
176 while (query->next()) {
177 QString name = query->value(0).toString();
178 artists.append(name);
183 QList<QString> DbStorage::getAlbumsForArtist(QString artist) {
184 QList<QString> albums;
185 QSqlQuery *query = _get_albums_for_artist_query;
186 query->bindValue(":name", artist);
188 while (query->next()) {
189 QString name = query->value(0).toString();
195 QList<Track> DbStorage::getTracksForAlbum(QString album, QString artist) {
197 QSqlQuery *query = _get_tracks_for_album_query;
198 query->bindValue(":artist_name", artist);
199 query->bindValue(":album_name", album);
202 while (query->next()) {
203 int id = query->value(0).toInt();
204 QString title = query->value(1).toString();
205 QString source = query->value(2).toString();
206 int count = query->value(3).toInt();
207 int length = query->value(4).toInt();
208 TrackMetadata meta (title, artist, album, length);
209 Track track(id, meta, source);
210 track.setCount(count);
211 tracks.append(track);
217 Playlist DbStorage::getFavorites() {
219 QSqlQuery *query = _get_favorites_query;
221 while(query->next()) {
222 int id = query->value(0).toInt();
223 QString title = query->value(1).toString();
224 QString artist = query->value(2).toString();
225 QString album = query->value(3).toString();
226 QString source = query->value(4).toString();
227 int count = query->value(5).toInt();
228 int length = query->value(6).toInt();
229 TrackMetadata meta(title, artist, album, length);
230 Track track(id, meta, source);
231 track.setCount(count);
232 playlist.addTrack(track);
233 playlist.setName("Favorites");
238 Playlist DbStorage::getMostPlayed() {
240 QSqlQuery *query = _get_most_played_query;
241 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
243 while (query->next()) {
244 int id = query->value(0).toInt();
245 QString title = query->value(1).toString();
246 QString artist = query->value(2).toString();
247 QString album = query->value(3).toString();
248 QString source = query->value(4).toString();
249 int count = query->value(5).toInt();
250 int length = query->value(6).toInt();
251 TrackMetadata meta(title, artist, album, length);
252 Track track(id, meta, source);
253 track.setCount(count);
254 playlist.addTrack(track);
255 playlist.setName("Most popular");
260 Playlist DbStorage::getNeverPlayed() {
262 QSqlQuery *query = _get_never_played_query;
263 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
265 while (query->next()) {
266 int id = query->value(0).toInt();
267 QString title = query->value(1).toString();
268 QString artist = query->value(2).toString();
269 QString album = query->value(3).toString();
270 QString source = query->value(4).toString();
271 int count = query->value(5).toInt();
272 int length = query->value(6).toInt();
273 TrackMetadata meta(title, artist, album, length);
274 Track track(id, meta, source);
275 track.setCount(count);
276 playlist.addTrack(track);
277 playlist.setName("Never played");
282 Playlist DbStorage::getRecentlyAdded() {
284 QSqlQuery *query = _get_recently_added_query;
285 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
287 while (query->next()) {
288 int id = query->value(0).toInt();
289 QString title = query->value(1).toString();
290 QString artist = query->value(2).toString();
291 QString album = query->value(3).toString();
292 QString source = query->value(4).toString();
293 int count = query->value(5).toInt();
294 int length = query->value(6).toInt();
295 TrackMetadata meta(title, artist, album, length);
296 Track track(id, meta, source);
297 track.setCount(count);
298 playlist.addTrack(track);
299 playlist.setName("Recently added");
304 void DbStorage::removeTrack(Track track) {
306 QSqlQuery *query = new QSqlQuery(db);
307 query->prepare("DELETE FROM tracks WHERE id = :id;");
308 query->bindValue(":id", id);
310 query->prepare("DELETE FROM favorites WHERE track_id = :id;");
311 query->bindValue(":id", id);
313 query->prepare("DELETE FROM adding_date WHERE id = :id;");
314 query->bindValue(":id", id);
318 void DbStorage::addTrack(Track track) {
319 QString title = track.metadata().title();
320 QString artist = track.metadata().artist();
321 QString album = track.metadata().album();
322 QString source = track.source();
323 int artist_id = _check_add_artist(artist);
324 int album_id = _check_add_album(album, artist_id);
325 if (artist_id == -1 || album_id == -1) {
330 QSqlQuery *query = _check_track_query;
331 query->bindValue(":source", source);
335 // already in datebase, skip
338 query = _insert_track_query;
339 query->bindValue(":title", title);
340 query->bindValue(":artist_id", artist_id);
341 query->bindValue(":album_id", album_id);
342 query->bindValue(":source", source);
343 query->bindValue(":length", track.metadata().length());
346 query = _check_track_query;
347 query->bindValue(":source", source);
350 int id = query->value(0).toInt();
351 query = _insert_date_query;
352 query->bindValue(":track_id", id);
356 qDebug () << "three";
369 void DbStorage::addToFavorites(Track track) {
370 QSqlQuery *query = _insert_favorites_query;
371 query->bindValue(":track_id", track.id());
375 void DbStorage::updateTrackCount(Track track) {
376 QSqlQuery *query = _get_track_count;
377 query->bindValue(":id", track.id());
380 int count = query->value(0).toInt();
381 query = _update_track_count_query;
382 query->bindValue(":count", count+1);
383 query->bindValue(":id", track.id());
388 Track DbStorage::updateTrack(Track track) {
389 QSqlQuery *query = _remove_track_query;
390 query->bindValue(":id", track.id());
391 if (!query->exec()) {
392 qDebug() << "Problem here";
395 query = _get_track_by_source_query;
396 query->bindValue(":source", track.source());
400 int id = query->value(0).toInt();
402 QString title = query->value(1).toString();
404 QString artist = query->value(2).toString();
406 QString album = query->value(3).toString();
408 QString source = query->value(4).toString();
410 int count = query->value(5).toInt();
411 int length = query->value(6).toInt();
412 TrackMetadata meta(title, artist, album, length);
413 Track ntrack(id, meta, source);
414 ntrack.setCount(count);
420 int DbStorage::_check_add_artist(QString artist) {
421 QSqlQuery *query = _check_artist_query;
422 query->bindValue(":name", artist);
425 int id = query->value(0).toInt();
428 query = _insert_artist_query;
429 query->bindValue(":name", artist);
431 return _check_add_artist(artist);
439 int DbStorage::_check_add_album(QString album, int artist_id) {
440 QSqlQuery *query = _check_album_query;
441 query->bindValue(":name", album);
442 query->bindValue(":artist_id", artist_id);
445 int id = query->value(0).toInt();
448 query = _insert_album_query;
449 query->bindValue(":name", album);
450 query->bindValue(":artist_id", artist_id);
452 return _check_add_album(album, artist_id);