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"
26 using namespace SomePlayer::Storage;
27 using namespace SomePlayer::DataObjects;
29 DbStorage::DbStorage(QString path) {
30 QString dbname = path + _DATABASE_NAME_;
31 db = QSqlDatabase::addDatabase("QSQLITE");
32 db.setDatabaseName(dbname);
34 // throw big exception
36 // create database structure
37 _create_database_structure();
41 void DbStorage::_prepare_queries() {
42 _get_artists_query = new QSqlQuery(db);
43 _get_artists_query->prepare("SELECT name FROM artist ORDER BY uname");
45 _get_albums_for_artist_sort_name_query = new QSqlQuery(db);
46 _get_albums_for_artist_sort_name_query->prepare("SELECT name, year FROM album WHERE artist_id in (SELECT id from artist WHERE uname = :uname) ORDER BY uname;");
48 _get_albums_for_artist_sort_year_query = new QSqlQuery(db);
49 _get_albums_for_artist_sort_year_query->prepare("SELECT name, year FROM album WHERE artist_id in (SELECT id from artist WHERE uname = :uname) ORDER BY year;");
51 _get_tracks_for_album_query = new QSqlQuery(db);
52 _get_tracks_for_album_query->prepare("SELECT id, title, source, count, length FROM tracks WHERE artist_id IN "
53 "(SELECT id FROM artist WHERE uname = :artist_uname) AND album_id IN "
54 "(SELECT id FROM album WHERE uname = :album_uname);");
56 _get_favorites_query = new QSqlQuery(db);
57 _get_favorites_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
58 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
59 "tracks JOIN artist ON tracks.artist_id = artist.id) "
60 "JOIN album ON album_id = album.id WHERE track_id IN "
61 "(SELECT track_id FROM favorites);");
63 _get_most_played_query = new QSqlQuery(db);
64 _get_most_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
65 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
66 "tracks JOIN artist ON tracks.artist_id = artist.id) "
67 "JOIN album ON album_id = album.id ORDER BY count DESC "
70 _get_never_played_query = new QSqlQuery(db);
71 _get_never_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
72 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
73 "tracks JOIN artist ON tracks.artist_id = artist.id) "
74 "JOIN album ON album_id = album.id "
77 _get_recently_added_query = new QSqlQuery(db);
78 _get_recently_added_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
79 "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
80 "tracks JOIN artist ON tracks.artist_id = artist.id) "
81 "JOIN album ON album_id = album.id "
83 "(SELECT track_id FROM adding_date ORDER BY date DESC LIMIT 0, :max)");
85 _get_track_count_query = new QSqlQuery(db);
86 _get_track_count_query->prepare("SELECT count from tracks WHERE id = :id");
88 _get_tracks_by_pattern_query = new QSqlQuery(db);
89 _get_tracks_by_pattern_query->prepare("SELECT id, title, artist, album, source, count, length, year FROM "
91 "utitle LIKE (SELECT '%' || :ptitle || '%') OR "
92 "album_uname LIKE (SELECT '%' || :palbum || '%') OR "
93 "artist_uname LIKE (SELECT '%' || :partist || '%') "
94 "ORDER BY artist_uname, year");
96 _get_track_id_by_source_query = new QSqlQuery(db);
97 _get_track_id_by_source_query->prepare("SELECT id FROM tracks WHERE source = :source");
99 _get_directories_query = new QSqlQuery(db);
100 _get_directories_query->prepare("SELECT id, path FROM directories");
102 _get_artists_count_query = new QSqlQuery(db);
103 _get_artists_count_query->prepare("SELECT COUNT(id) AS cnt FROM artist");
105 _get_albums_count_query = new QSqlQuery(db);
106 _get_albums_count_query->prepare("SELECT COUNT(id) AS cnt FROM album");
108 _get_tracks_count_query = new QSqlQuery(db);
109 _get_tracks_count_query->prepare("SELECT COUNT(id) AS cnt FROM tracks");
111 _get_tracks_source_from_query = new QSqlQuery(db);
112 _get_tracks_source_from_query->prepare("SELECT source FROM tracks WHERE directory = :directory_id");
114 _check_artist_query = new QSqlQuery(db);
115 _check_artist_query->prepare("SELECT id FROM artist WHERE uname = :uname");
117 _check_album_query = new QSqlQuery(db);
118 _check_album_query->prepare("SELECT id FROM album WHERE uname = :uname AND artist_id = :artist_id");
120 _check_directory_query = new QSqlQuery(db);
121 _check_directory_query->prepare("SELECT id from directories WHERE path = :path");
123 _insert_artist_query = new QSqlQuery(db);
124 _insert_artist_query->prepare("INSERT INTO artist (name, uname) values (:name, :uname)");
126 _insert_album_query = new QSqlQuery(db);
127 _insert_album_query->prepare("INSERT INTO album (name, uname, artist_id, year) values (:name, :uname, :artist_id, :year)");
129 _insert_track_query = new QSqlQuery(db);
130 _insert_track_query->prepare("INSERT INTO tracks (title, utitle, artist_id, album_id, source, directory, length) values (:title, :utitle, :artist_id, :album_id, :source, :directory_id, :length)");
132 _insert_date_query = new QSqlQuery(db);
133 _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))");
135 _insert_favorites_query = new QSqlQuery(db);
136 _insert_favorites_query->prepare("INSERT INTO favorites (track_id) values (:track_id)");
138 _insert_directory_query = new QSqlQuery(db);
139 _insert_directory_query->prepare("INSERT INTO directories (path) values (:path)");
141 _update_track_count_query = new QSqlQuery(db);
142 _update_track_count_query->prepare("UPDATE tracks SET count = :count where id = :id");
144 _remove_track_query = new QSqlQuery(db);
145 _remove_track_query->prepare("DELETE FROM tracks WHERE id = :id");
147 _remove_empty_albums_query = new QSqlQuery(db);
148 _remove_empty_albums_query->prepare("DELETE FROM album WHERE album.id IN "
150 "(SELECT COUNT(tracks.id) AS cnt, album.id FROM "
151 "album LEFT OUTER JOIN tracks ON album.id = tracks.album_id "
152 "GROUP BY album.id) WHERE cnt = 0)");
154 _remove_empty_artists_query = new QSqlQuery(db);
155 _remove_empty_artists_query->prepare("DELETE FROM artist WHERE artist.id IN "
157 "(SELECT COUNT(tracks.id) AS cnt, artist.id FROM "
158 "artist LEFT OUTER JOIN tracks ON artist.id = tracks.artist_id "
159 "GROUP BY artist.id) WHERE cnt = 0)");
161 _remove_tracks_from_query = new QSqlQuery(db);
162 _remove_tracks_from_query->prepare("DELETE FROM tracks WHERE directory = :directory_id");
164 _remove_directory_query = new QSqlQuery(db);
165 _remove_directory_query->prepare("DELETE FROM directories WHERE path = :path");
168 void DbStorage::_create_database_structure() {
169 QSqlQuery *query = new QSqlQuery(db);
170 query->exec("create table artist (id integer primary key, "
174 query->exec("create table album (id integer primary key, "
175 "artist_id integer, "
179 "foreign key(artist_id) references arist(id) "
181 query->exec("create table tracks (id integer primary key, "
182 "artist_id integer, "
187 "directory integer, "
188 "count integer default 0, "
189 "length integer default 0, "
190 "foreign key(artist_id) references artist(id), "
191 "foreign key(album_id) references album(id) "
193 query->exec("create table favorites (track_id integer, "
194 "foreign key(track_id) references tracks(id) "
196 query->exec("create table adding_date (track_id integer, "
198 "foreign key(track_id) references tracks(id) "
200 query->exec("create view entire as "
201 "select id, title, artist_name as artist, album_name as album, source, count, length, album_year as year, utitle, artist_uname, album_uname from "
203 "(select artist.id as aartist_id, "
204 "artist.uname as artist_uname, "
205 "artist.name as artist_name, "
206 "album.uname as album_uname, "
207 "album.year as album_year, "
208 "album.id as aalbum_id, "
209 "album.name as album_name from "
212 "on album.artist_id = artist.id) "
213 "on aartist_id = tracks.artist_id "
214 "and aalbum_id = tracks.album_id");
216 query->exec("create table directories (id integer primary key, path text)");
219 DbStorage::~DbStorage() {
220 delete _get_albums_for_artist_sort_name_query;
221 delete _get_albums_for_artist_sort_year_query;
222 delete _get_artists_query;
223 delete _get_favorites_query;
224 delete _get_most_played_query;
225 delete _get_never_played_query;
226 delete _get_recently_added_query;
227 delete _get_tracks_for_album_query;
228 delete _get_tracks_by_pattern_query;
229 delete _get_directories_query;
230 delete _get_artists_count_query;
231 delete _get_albums_count_query;
232 delete _get_tracks_count_query;
233 delete _get_tracks_source_from_query;
234 delete _check_album_query;
235 delete _check_artist_query;
236 delete _check_directory_query;
237 delete _get_track_id_by_source_query;
238 delete _insert_album_query;
239 delete _insert_artist_query;
240 delete _insert_date_query;
241 delete _insert_track_query;
242 delete _insert_favorites_query;
243 delete _insert_directory_query;
244 delete _update_track_count_query;
245 delete _remove_track_query;
246 delete _remove_directory_query;
247 delete _remove_tracks_from_query;
251 QList<QString> DbStorage::getArtists() {
252 QList<QString> artists;
253 QSqlQuery *query = _get_artists_query;
255 while (query->next()) {
256 QString name = query->value(0).toString();
257 artists.append(name);
262 QMap<QString, int> DbStorage::getAlbumsForArtist(QString artist) {
263 QMap<QString, int> albums;
265 QString sort = config.getValue("ui/albumsorting").toString();
266 QSqlQuery *query = NULL;
267 if (sort == "date") {
268 query = _get_albums_for_artist_sort_year_query;
270 query = _get_albums_for_artist_sort_name_query;
272 query->bindValue(":uname", artist.toUpper());
274 while (query->next()) {
275 QString name = query->value(0).toString();
276 albums[name] = query->value(1).toInt();
281 QList<Track> DbStorage::getTracksForAlbum(QString album, QString artist) {
283 QSqlQuery *query = _get_tracks_for_album_query;
284 query->bindValue(":artist_uname", artist.toUpper());
285 query->bindValue(":album_uname", album.toUpper());
288 while (query->next()) {
289 QString title = query->value(1).toString();
290 QString source = query->value(2).toString();
291 int count = query->value(3).toInt();
292 int length = query->value(4).toInt();
293 TrackMetadata meta (title, artist, album, length);
294 Track track(meta, source);
295 track.setCount(count);
296 tracks.append(track);
302 Playlist DbStorage::getFavorites() {
304 QSqlQuery *query = _get_favorites_query;
306 while(query->next()) {
307 QString title = query->value(1).toString();
308 QString artist = query->value(2).toString();
309 QString album = query->value(3).toString();
310 QString source = query->value(4).toString();
311 int count = query->value(5).toInt();
312 int length = query->value(6).toInt();
313 TrackMetadata meta(title, artist, album, length);
314 Track track(meta, source);
315 track.setCount(count);
316 playlist.addTrack(track);
317 playlist.setName("Favorites");
322 Playlist DbStorage::getMostPlayed() {
324 QSqlQuery *query = _get_most_played_query;
325 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
327 while (query->next()) {
328 QString title = query->value(1).toString();
329 QString artist = query->value(2).toString();
330 QString album = query->value(3).toString();
331 QString source = query->value(4).toString();
332 int count = query->value(5).toInt();
333 int length = query->value(6).toInt();
334 TrackMetadata meta(title, artist, album, length);
335 Track track(meta, source);
336 track.setCount(count);
337 playlist.addTrack(track);
338 playlist.setName("Most popular");
343 Playlist DbStorage::getNeverPlayed() {
345 QSqlQuery *query = _get_never_played_query;
346 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
348 while (query->next()) {
349 QString title = query->value(1).toString();
350 QString artist = query->value(2).toString();
351 QString album = query->value(3).toString();
352 QString source = query->value(4).toString();
353 int count = query->value(5).toInt();
354 int length = query->value(6).toInt();
355 TrackMetadata meta(title, artist, album, length);
356 Track track(meta, source);
357 track.setCount(count);
358 playlist.addTrack(track);
359 playlist.setName("Never played");
364 Playlist DbStorage::getRecentlyAdded() {
366 QSqlQuery *query = _get_recently_added_query;
367 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
369 while (query->next()) {
370 QString title = query->value(1).toString();
371 QString artist = query->value(2).toString();
372 QString album = query->value(3).toString();
373 QString source = query->value(4).toString();
374 int count = query->value(5).toInt();
375 int length = query->value(6).toInt();
376 TrackMetadata meta(title, artist, album, length);
377 Track track(meta, source);
378 track.setCount(count);
379 playlist.addTrack(track);
380 playlist.setName("Recently added");
385 void DbStorage::removeTrack(Track track) {
386 QSqlQuery *query = _get_track_id_by_source_query;
387 query->bindValue(":source", track.source());
390 int id = query->value(0).toInt();
391 query = new QSqlQuery(db);
392 query->prepare("DELETE FROM tracks WHERE id = :id;");
393 query->bindValue(":id", id);
395 query->prepare("DELETE FROM favorites WHERE track_id = :id;");
396 query->bindValue(":id", id);
398 query->prepare("DELETE FROM adding_date WHERE id = :id;");
399 query->bindValue(":id", id);
404 void DbStorage::addTrack(Track track) {
405 QString title = track.metadata().title();
406 QString artist = track.metadata().artist();
407 QString album = track.metadata().album();
408 QString source = track.source();
409 QFileInfo info(source);
410 QString path = info.canonicalPath();
411 int year = track.metadata().year();
412 int artist_id = _check_add_artist(artist);
413 int album_id = _check_add_album(album, artist_id, year);
414 if (artist_id == -1 || album_id == -1) {
418 QSqlQuery *query = _get_track_id_by_source_query;
419 query->bindValue(":source", source);
422 // already in datebase, skip
425 query = _insert_track_query;
426 query->bindValue(":title", title);
427 query->bindValue(":utitle", title.toUpper());
428 query->bindValue(":artist_id", artist_id);
429 query->bindValue(":album_id", album_id);
430 query->bindValue(":source", source);
431 query->bindValue(":directory", _check_add_directory(path));
432 query->bindValue(":length", track.metadata().length());
435 query = _get_track_id_by_source_query;
436 query->bindValue(":source", source);
439 int id = query->value(0).toInt();
440 query = _insert_date_query;
441 query->bindValue(":track_id", id);
455 void DbStorage::addToFavorites(Track track) {
456 QSqlQuery *query = _get_track_id_by_source_query;
457 query->bindValue(":source", track.source());
460 int id = query->value(0).toInt();
461 query = _insert_favorites_query;
462 query->bindValue(":track_id", id);
467 void DbStorage::updateTrackCount(Track track) {
468 QSqlQuery *query = _get_track_id_by_source_query;
469 query->bindValue(":source", track.source());
472 int id = query->value(0).toInt();
473 query = _get_track_count_query;
474 query->bindValue(":id", id);
477 int count = query->value(0).toInt();
478 query = _update_track_count_query;
479 query->bindValue(":count", count+1);
480 query->bindValue(":id", id);
486 Track DbStorage::updateTrack(Track track) {
487 QSqlQuery *query = _get_track_id_by_source_query;
488 query->bindValue(":source", track.source());
490 if (query->next()) { // found track in library
491 int id = query->value(0).toInt();
492 query = _remove_track_query;
493 query->bindValue(":id", id);
501 int DbStorage::_check_add_artist(QString artist) {
502 QSqlQuery *query = _check_artist_query;
503 query->bindValue(":uname", artist.toUpper());
506 int id = query->value(0).toInt();
509 query = _insert_artist_query;
510 query->bindValue(":name", artist);
511 query->bindValue(":uname", artist.toUpper());
513 return _check_add_artist(artist);
521 int DbStorage::_check_add_album(QString album, int artist_id, int year) {
522 QSqlQuery *query = _check_album_query;
523 query->bindValue(":uname", album.toUpper());
524 query->bindValue(":artist_id", artist_id);
527 int id = query->value(0).toInt();
530 query = _insert_album_query;
531 query->bindValue(":name", album);
532 query->bindValue(":uname", album.toUpper());
533 query->bindValue(":artist_id", artist_id);
534 query->bindValue(":year", year);
536 return _check_add_album(album, artist_id, year);
544 QList<Track> DbStorage::search(QString pattern) {
546 QSqlQuery *query = _get_tracks_by_pattern_query;
547 query->bindValue(":ptitle", pattern.toUpper()); // with :pattern only doesn't work
548 query->bindValue(":palbum", pattern.toUpper());
549 query->bindValue(":partist", pattern.toUpper());
551 // id, title, artist, album, source, count, length, year
552 while (query->next()) {
553 QString title = query->value(1).toString();
554 QString artist = query->value(2).toString();
555 QString album = query->value(3).toString();
556 QString source = query->value(4).toString();
557 int count = query->value(5).toInt();
558 int length = query->value(6).toInt();
559 int year = query->value(7).toInt();
560 TrackMetadata meta(title, artist, album, length);
562 Track track(meta, source);
563 track.setCount(count);
569 void DbStorage::_cleanup() {
570 _remove_empty_albums_query->exec();
571 _remove_empty_artists_query->exec();
574 int DbStorage::_check_add_directory(QString path) {
575 QSqlQuery *query = _check_directory_query;
576 query->bindValue(":path", path);
579 return query->value(0).toInt();
581 query = _insert_directory_query;
582 query->bindValue(":path", path);
584 return _check_add_directory(path);
588 QList<QString> DbStorage::getDirectories() {
589 QSqlQuery *query = _get_directories_query;
591 QList<QString> directories;
592 while (query->next()) {
593 directories.append(query->value(1).toString());
598 int DbStorage::getArtistsCount() {
599 QSqlQuery *query = _get_artists_count_query;
602 return query->value(0).toInt();
607 int DbStorage::getAlbumsCount() {
608 QSqlQuery *query = _get_albums_count_query;
611 return query->value(0).toInt();
616 int DbStorage::getTracksCount() {
617 QSqlQuery *query = _get_tracks_count_query;
620 return query->value(0).toInt();
625 void DbStorage::deleteTracksFrom(QString path) {
626 QSqlQuery *query = _check_directory_query;
627 query->bindValue(":path", path);
630 int id = query->value(0).toInt();
631 query = _remove_tracks_from_query;
632 query->bindValue(":directory_id", id);
634 query = _remove_directory_query;
635 query->bindValue(":path", path);
641 void DbStorage::checkTracksFrom(QString path) {
642 QSqlQuery *query = _check_directory_query;
643 query->bindValue(":path", path);
646 int id = query->value(0).toInt();
647 query = _get_tracks_source_from_query;
648 query->bindValue(":directory_id", id);
650 while (query->next()) {
651 QString source = query->value(0).toString();
652 if (!QFile::exists(source)) {
653 Track track(TrackMetadata(), source); // removeTrack uses only source field, so
654 removeTrack(track); // we can use this method