5 using namespace SomePlayer::Storage;
6 using namespace SomePlayer::DataObjects;
8 DbStorage::DbStorage(QString path) {
9 QString dbname = path+_DATABASE_NAME_;
10 db = QSqlDatabase::addDatabase("QSQLITE");
11 db.setDatabaseName(dbname);
13 // throw big exception
15 // create database structure
16 _create_database_structure();
20 void DbStorage::_prepare_queries() {
21 _get_artists_query = new QSqlQuery(db);
22 _get_artists_query->prepare("SELECT name FROM artist");
24 _get_albums_for_artist_query = new QSqlQuery(db);
25 _get_albums_for_artist_query->prepare("SELECT name FROM album WHERE artist_id in (SELECT id from artist WHERE name = :name);");
27 _get_tracks_for_album_query = new QSqlQuery(db);
28 _get_tracks_for_album_query->prepare("SELECT id, title, source, count, length FROM tracks WHERE artist_id IN "
29 "(SELECT id FROM artist WHERE name = :artist_name) AND album_id IN "
30 "(SELECT id FROM album WHERE name = :album_name);");
32 _get_favorites_query = new QSqlQuery(db);
33 _get_favorites_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
34 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
35 "tracks JOIN artist ON tracks.artist_id = artist.id) "
36 "JOIN album ON album_id = album.id WHERE track_id IN "
37 "(SELECT track_id FROM favorites);");
39 _get_most_played_query = new QSqlQuery(db);
40 _get_most_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
41 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
42 "tracks JOIN artist ON tracks.artist_id = artist.id) "
43 "JOIN album ON album_id = album.id ORDER BY count DESC "
46 _get_never_played_query = new QSqlQuery(db);
47 _get_never_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
48 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
49 "tracks JOIN artist ON tracks.artist_id = artist.id) "
50 "JOIN album ON album_id = album.id "
53 _get_recently_added_query = new QSqlQuery(db);
54 _get_recently_added_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
55 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
56 "tracks JOIN artist ON tracks.artist_id = artist.id) "
57 "JOIN album ON album_id = album.id "
59 "(SELECT track_id FROM adding_date ORDER BY date DESC LIMIT 0, :max)");
61 _get_track_count = new QSqlQuery(db);
62 _get_track_count->prepare("SELECT count from tracks WHERE id = :id");
64 _check_artist_query = new QSqlQuery(db);
65 _check_artist_query->prepare("SELECT id FROM artist WHERE name = :name");
67 _check_album_query = new QSqlQuery(db);
68 _check_album_query->prepare("SELECT id FROM album WHERE name = :name AND artist_id = :artist_id");
70 _check_track_query = new QSqlQuery(db);
71 _check_track_query->prepare("SELECT id FROM tracks WHERE source = :source");
73 _insert_artist_query = new QSqlQuery(db);
74 _insert_artist_query->prepare("INSERT INTO artist (name) values (:name)");
76 _insert_album_query = new QSqlQuery(db);
77 _insert_album_query->prepare("INSERT INTO album (name, artist_id) values (:name, :artist_id)");
79 _insert_track_query = new QSqlQuery(db);
80 _insert_track_query->prepare("INSERT INTO tracks (title, artist_id, album_id, source, length) values (:title, :artist_id, :album_id, :source, :length)");
82 _insert_date_query = new QSqlQuery(db);
83 _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))");
85 _insert_favorites_query = new QSqlQuery(db);
86 _insert_favorites_query->prepare("INSERT INTO favorites (track_id) values (:track_id)");
88 _update_track_count_query = new QSqlQuery(db);
89 _update_track_count_query->prepare("UPDATE tracks SET count = :count where id = :id");
92 void DbStorage::_create_database_structure() {
93 QSqlQuery *query = new QSqlQuery(db);
94 query->exec("create table artist (id integer primary key, "
97 query->exec("create table album (id integer primary key, "
100 "foreign key(artist_id) references arist(id) "
102 query->exec("create table tracks (id integer primary key, "
103 "artist_id integer, "
107 "count integer default 0, "
108 "length integer default 0, "
109 "foreign key(artist_id) references artist(id), "
110 "foreign key(album_id) references album(id) "
112 query->exec("create table favorites (track_id integer, "
113 "foreign key(track_id) references tracks(id) "
115 query->exec("create table adding_date (track_id integer, "
117 "foreign key(track_id) references tracks(id) "
121 DbStorage::~DbStorage() {
122 delete _get_albums_for_artist_query;
123 delete _get_artists_query;
124 delete _get_favorites_query;
125 delete _get_most_played_query;
126 delete _get_never_played_query;
127 delete _get_recently_added_query;
128 delete _get_tracks_for_album_query;
129 delete _check_album_query;
130 delete _check_artist_query;
131 delete _check_track_query;
132 delete _insert_album_query;
133 delete _insert_artist_query;
134 delete _insert_date_query;
135 delete _insert_track_query;
136 delete _insert_favorites_query;
137 delete _update_track_count_query;
141 QList<QString> DbStorage::getArtists() {
142 QList<QString> artists;
143 QSqlQuery *query = _get_artists_query;
145 while (query->next()) {
146 QString name = query->value(0).toString();
147 artists.append(name);
152 QList<QString> DbStorage::getAlbumsForArtist(QString artist) {
153 QList<QString> albums;
154 QSqlQuery *query = _get_albums_for_artist_query;
155 query->bindValue(":name", artist);
157 while (query->next()) {
158 QString name = query->value(0).toString();
164 QList<Track> DbStorage::getTracksForAlbum(QString album, QString artist) {
166 QSqlQuery *query = _get_tracks_for_album_query;
167 query->bindValue(":artist_name", artist);
168 query->bindValue(":album_name", album);
171 while (query->next()) {
172 int id = query->value(0).toInt();
173 QString title = query->value(1).toString();
174 QString source = query->value(2).toString();
175 int count = query->value(3).toInt();
176 int length = query->value(4).toInt();
177 TrackMetadata meta (title, artist, album, length);
178 Track track(id, meta, source);
179 track.setCount(count);
180 tracks.append(track);
186 Playlist DbStorage::getFavorites() {
188 QSqlQuery *query = _get_favorites_query;
190 while(query->next()) {
191 int id = query->value(0).toInt();
192 QString title = query->value(1).toString();
193 QString artist = query->value(2).toString();
194 QString album = query->value(3).toString();
195 QString source = query->value(4).toString();
196 int count = query->value(5).toInt();
197 int length = query->value(6).toInt();
198 TrackMetadata meta(title, artist, album, length);
199 Track track(id, meta, source);
200 track.setCount(count);
201 playlist.addTrack(track);
202 playlist.setName("Favorites");
207 Playlist DbStorage::getMostPlayed() {
209 QSqlQuery *query = _get_most_played_query;
210 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
212 while (query->next()) {
213 int id = query->value(0).toInt();
214 QString title = query->value(1).toString();
215 QString artist = query->value(2).toString();
216 QString album = query->value(3).toString();
217 QString source = query->value(4).toString();
218 int count = query->value(5).toInt();
219 int length = query->value(6).toInt();
220 TrackMetadata meta(title, artist, album, length);
221 Track track(id, meta, source);
222 track.setCount(count);
223 playlist.addTrack(track);
224 playlist.setName("Most popular");
229 Playlist DbStorage::getNeverPlayed() {
231 QSqlQuery *query = _get_never_played_query;
232 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
234 while (query->next()) {
235 int id = query->value(0).toInt();
236 QString title = query->value(1).toString();
237 QString artist = query->value(2).toString();
238 QString album = query->value(3).toString();
239 QString source = query->value(4).toString();
240 int count = query->value(5).toInt();
241 int length = query->value(6).toInt();
242 TrackMetadata meta(title, artist, album, length);
243 Track track(id, meta, source);
244 track.setCount(count);
245 playlist.addTrack(track);
246 playlist.setName("Never played");
251 Playlist DbStorage::getRecentlyAdded() {
253 QSqlQuery *query = _get_recently_added_query;
254 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
256 while (query->next()) {
257 int id = query->value(0).toInt();
258 QString title = query->value(1).toString();
259 QString artist = query->value(2).toString();
260 QString album = query->value(3).toString();
261 QString source = query->value(4).toString();
262 int count = query->value(5).toInt();
263 int length = query->value(6).toInt();
264 TrackMetadata meta(title, artist, album, length);
265 Track track(id, meta, source);
266 track.setCount(count);
267 playlist.addTrack(track);
268 playlist.setName("Recently added");
273 void DbStorage::removeTrack(Track track) {
275 QSqlQuery *query = new QSqlQuery(db);
276 query->prepare("DELETE FROM tracks WHERE id = :id;");
277 query->bindValue(":id", id);
279 query->prepare("DELETE FROM favorites WHERE track_id = :id;");
280 query->bindValue(":id", id);
282 query->prepare("DELETE FROM adding_date WHERE id = :id;");
283 query->bindValue(":id", id);
287 void DbStorage::addTrack(Track track) {
288 QString title = track.metadata().title();
289 QString artist = track.metadata().artist();
290 QString album = track.metadata().album();
291 QString source = track.source();
292 int artist_id = _check_add_artist(artist);
293 int album_id = _check_add_album(album, artist_id);
294 if (artist_id == -1 || album_id == -1) {
298 QSqlQuery *query = _check_track_query;
299 query->bindValue(":source", source);
302 // already in datebase, skip
305 query = _insert_track_query;
306 query->bindValue(":title", title);
307 query->bindValue(":artist_id", artist_id);
308 query->bindValue(":album_id", album_id);
309 query->bindValue(":source", source);
310 query->bindValue(":length", track.metadata().length());
313 query = _check_track_query;
314 query->bindValue(":source", source);
317 int id = query->value(0).toInt();
318 query = _insert_date_query;
319 query->bindValue(":track_id", id);
333 void DbStorage::addToFavorites(Track track) {
334 QSqlQuery *query = _insert_favorites_query;
335 query->bindValue(":track_id", track.id());
339 void DbStorage::updateTrackCount(Track track) {
340 QSqlQuery *query = _get_track_count;
341 query->bindValue(":id", track.id());
344 int count = query->value(0).toInt();
345 query = _update_track_count_query;
346 query->bindValue(":count", count+1);
347 query->bindValue(":id", track.id());
352 int DbStorage::_check_add_artist(QString artist) {
353 QSqlQuery *query = _check_artist_query;
354 query->bindValue(":name", artist);
357 int id = query->value(0).toInt();
360 query = _insert_artist_query;
361 query->bindValue(":name", artist);
363 return _check_add_artist(artist);
371 int DbStorage::_check_add_album(QString album, int artist_id) {
372 QSqlQuery *query = _check_album_query;
373 query->bindValue(":name", album);
374 query->bindValue(":artist_id", artist_id);
377 int id = query->value(0).toInt();
380 query = _insert_album_query;
381 query->bindValue(":name", album);
382 query->bindValue(":artist_id", artist_id);
384 return _check_add_album(album, artist_id);