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)");
60 _check_artist_query = new QSqlQuery(db);
61 _check_artist_query->prepare("SELECT id FROM artist WHERE name = :name");
63 _check_album_query = new QSqlQuery(db);
64 _check_album_query->prepare("SELECT id FROM album WHERE name = :name AND artist_id = :artist_id");
66 _check_track_query = new QSqlQuery(db);
67 _check_track_query->prepare("SELECT id FROM tracks WHERE source = :source");
69 _insert_artist_query = new QSqlQuery(db);
70 _insert_artist_query->prepare("INSERT INTO artist (name) values (:name)");
72 _insert_album_query = new QSqlQuery(db);
73 _insert_album_query->prepare("INSERT INTO album (name, artist_id) values (:name, :artist_id)");
75 _insert_track_query = new QSqlQuery(db);
76 _insert_track_query->prepare("INSERT INTO tracks (title, artist_id, album_id, source, length) values (:title, :artist_id, :album_id, :source, :length)");
78 _insert_date_query = new QSqlQuery(db);
79 _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))");
82 void DbStorage::_create_database_structure() {
83 QSqlQuery *query = new QSqlQuery(db);
84 query->exec("create table artist (id integer primary key, "
87 query->exec("create table album (id integer primary key, "
90 "foreign key(artist_id) references arist(id) "
92 query->exec("create table tracks (id integer primary key, "
97 "count integer default 0, "
98 "length integer default 0, "
99 "foreign key(artist_id) references artist(id), "
100 "foreign key(album_id) references album(id) "
102 query->exec("create table favorites (track_id integer, "
103 "foreign key(track_id) references tracks(id) "
105 query->exec("create table adding_date (track_id integer, "
107 "foreign key(track_id) references tracks(id) "
111 DbStorage::~DbStorage() {
112 delete _get_albums_for_artist_query;
113 delete _get_artists_query;
114 delete _get_favorites_query;
115 delete _get_most_played_query;
116 delete _get_never_played_query;
117 delete _get_recently_added_query;
118 delete _get_tracks_for_album_query;
119 delete _check_album_query;
120 delete _check_artist_query;
121 delete _check_track_query;
122 delete _insert_album_query;
123 delete _insert_artist_query;
124 delete _insert_date_query;
125 delete _insert_track_query;
129 QList<QString> DbStorage::getArtists() {
130 QList<QString> artists;
131 QSqlQuery *query = _get_artists_query;
133 while (query->next()) {
134 QString name = query->value(0).toString();
135 artists.append(name);
140 QList<QString> DbStorage::getAlbumsForArtist(QString artist) {
141 QList<QString> albums;
142 QSqlQuery *query = _get_albums_for_artist_query;
143 query->bindValue(":name", artist);
145 while (query->next()) {
146 QString name = query->value(0).toString();
152 QList<Track> DbStorage::getTracksForAlbum(QString album, QString artist) {
154 QSqlQuery *query = _get_tracks_for_album_query;
155 query->bindValue(":artist_name", artist);
156 query->bindValue(":album_name", album);
159 while (query->next()) {
160 int id = query->value(0).toInt();
161 QString title = query->value(1).toString();
162 QString source = query->value(2).toString();
163 int count = query->value(3).toInt();
164 int length = query->value(4).toInt();
165 TrackMetadata meta (title, artist, album, length);
166 Track track(id, meta, source);
167 track.setCount(count);
168 tracks.append(track);
174 Playlist DbStorage::getFavorites() {
176 QSqlQuery *query = _get_favorites_query;
178 while(query->next()) {
179 int id = query->value(0).toInt();
180 QString title = query->value(1).toString();
181 QString artist = query->value(2).toString();
182 QString album = query->value(3).toString();
183 QString source = query->value(4).toString();
184 int count = query->value(5).toInt();
185 int length = query->value(6).toInt();
186 TrackMetadata meta(title, artist, album, length);
187 Track track(id, meta, source);
188 track.setCount(count);
189 playlist.addTrack(track);
190 playlist.setName("Favorites");
195 Playlist DbStorage::getMostPlayed() {
197 QSqlQuery *query = _get_most_played_query;
198 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
200 while (query->next()) {
201 int id = query->value(0).toInt();
202 QString title = query->value(1).toString();
203 QString artist = query->value(2).toString();
204 QString album = query->value(3).toString();
205 QString source = query->value(4).toString();
206 int count = query->value(5).toInt();
207 int length = query->value(6).toInt();
208 TrackMetadata meta(title, artist, album, length);
209 Track track(id, meta, source);
210 track.setCount(count);
211 playlist.addTrack(track);
212 playlist.setName("Most popular");
217 Playlist DbStorage::getNeverPlayed() {
219 QSqlQuery *query = _get_never_played_query;
220 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
222 while (query->next()) {
223 int id = query->value(0).toInt();
224 QString title = query->value(1).toString();
225 QString artist = query->value(2).toString();
226 QString album = query->value(3).toString();
227 QString source = query->value(4).toString();
228 int count = query->value(5).toInt();
229 int length = query->value(6).toInt();
230 TrackMetadata meta(title, artist, album, length);
231 Track track(id, meta, source);
232 track.setCount(count);
233 playlist.addTrack(track);
234 playlist.setName("Never played");
239 Playlist DbStorage::getRecentlyAdded() {
241 QSqlQuery *query = _get_recently_added_query;
242 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
244 while (query->next()) {
245 int id = query->value(0).toInt();
246 QString title = query->value(1).toString();
247 QString artist = query->value(2).toString();
248 QString album = query->value(3).toString();
249 QString source = query->value(4).toString();
250 int count = query->value(5).toInt();
251 int length = query->value(6).toInt();
252 TrackMetadata meta(title, artist, album, length);
253 Track track(id, meta, source);
254 track.setCount(count);
255 playlist.addTrack(track);
256 playlist.setName("Recently added");
261 void DbStorage::removeTrack(Track track) {
263 QSqlQuery *query = new QSqlQuery(db);
264 query->prepare("DELETE FROM tracks WHERE id = :id;");
265 query->bindValue(":id", id);
267 query->prepare("DELETE FROM favorites WHERE track_id = :id;");
268 query->bindValue(":id", id);
270 query->prepare("DELETE FROM adding_date WHERE id = :id;");
271 query->bindValue(":id", id);
275 void DbStorage::addTrack(Track track) {
276 QString title = track.metadata().title();
277 QString artist = track.metadata().artist();
278 QString album = track.metadata().album();
279 QString source = track.source();
280 int artist_id = _check_add_artist(artist);
281 int album_id = _check_add_album(album, artist_id);
282 if (artist_id == -1 || album_id == -1) {
286 QSqlQuery *query = _check_track_query;
287 query->bindValue(":source", source);
290 // already in datebase, skip
293 query = _insert_track_query;
294 query->bindValue(":title", title);
295 query->bindValue(":artist_id", artist_id);
296 query->bindValue(":album_id", album_id);
297 query->bindValue(":source", source);
298 query->bindValue(":length", track.metadata().length());
301 query = _check_track_query;
302 query->bindValue(":source", source);
305 int id = query->value(0).toInt();
306 query = _insert_date_query;
307 query->bindValue(":track_id", id);
321 void DbStorage::addToFavorites(Track) {
324 void DbStorage::updateTrack(Track) {
327 int DbStorage::_check_add_artist(QString artist) {
328 QSqlQuery *query = _check_artist_query;
329 query->bindValue(":name", artist);
332 int id = query->value(0).toInt();
335 query = _insert_artist_query;
336 query->bindValue(":name", artist);
338 return _check_add_artist(artist);
346 int DbStorage::_check_add_album(QString album, int artist_id) {
347 QSqlQuery *query = _check_album_query;
348 query->bindValue(":name", album);
349 query->bindValue(":artist_id", artist_id);
352 int id = query->value(0).toInt();
355 query = _insert_album_query;
356 query->bindValue(":name", album);
357 query->bindValue(":artist_id", artist_id);
359 return _check_add_album(album, artist_id);