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 || '%') "
92 "ORDER BY artist_uname, year");
94 _get_track_id_by_source_query = new QSqlQuery(db);
95 _get_track_id_by_source_query->prepare("SELECT id FROM tracks WHERE source = :source");
97 _get_directories_query = new QSqlQuery(db);
98 _get_directories_query->prepare("SELECT id, path FROM directories");
100 _get_artists_count_query = new QSqlQuery(db);
101 _get_artists_count_query->prepare("SELECT COUNT(id) AS cnt FROM artist");
103 _get_albums_count_query = new QSqlQuery(db);
104 _get_albums_count_query->prepare("SELECT COUNT(id) AS cnt FROM album");
106 _get_tracks_count_query = new QSqlQuery(db);
107 _get_tracks_count_query->prepare("SELECT COUNT(id) AS cnt FROM tracks");
109 _get_tracks_source_from_query = new QSqlQuery(db);
110 _get_tracks_source_from_query->prepare("SELECT source FROM tracks WHERE directory = :directory_id");
112 _check_artist_query = new QSqlQuery(db);
113 _check_artist_query->prepare("SELECT id FROM artist WHERE uname = :uname");
115 _check_album_query = new QSqlQuery(db);
116 _check_album_query->prepare("SELECT id FROM album WHERE uname = :uname AND artist_id = :artist_id");
118 _check_directory_query = new QSqlQuery(db);
119 _check_directory_query->prepare("SELECT id from directories WHERE path = :path");
121 _check_favorite_query = new QSqlQuery(db);
122 _check_favorite_query->prepare("SELECT 'yes' from favorites WHERE track_id in (SELECT id from tracks WHERE source = :source)");
124 _insert_artist_query = new QSqlQuery(db);
125 _insert_artist_query->prepare("INSERT INTO artist (name, uname) values (:name, :uname)");
127 _insert_album_query = new QSqlQuery(db);
128 _insert_album_query->prepare("INSERT INTO album (name, uname, artist_id, year) values (:name, :uname, :artist_id, :year)");
130 _insert_track_query = new QSqlQuery(db);
131 _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)");
133 _insert_date_query = new QSqlQuery(db);
134 _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))");
136 _insert_favorites_query = new QSqlQuery(db);
137 _insert_favorites_query->prepare("INSERT INTO favorites (track_id) values (:track_id)");
139 _insert_directory_query = new QSqlQuery(db);
140 _insert_directory_query->prepare("INSERT INTO directories (path) values (:path)");
142 _update_track_count_query = new QSqlQuery(db);
143 _update_track_count_query->prepare("UPDATE tracks SET count = :count where id = :id");
145 _remove_track_query = new QSqlQuery(db);
146 _remove_track_query->prepare("DELETE FROM tracks WHERE id = :id");
148 _remove_empty_albums_query = new QSqlQuery(db);
149 _remove_empty_albums_query->prepare("DELETE FROM album WHERE album.id IN "
151 "(SELECT COUNT(tracks.id) AS cnt, album.id FROM "
152 "album LEFT OUTER JOIN tracks ON album.id = tracks.album_id "
153 "GROUP BY album.id) WHERE cnt = 0)");
155 _remove_empty_artists_query = new QSqlQuery(db);
156 _remove_empty_artists_query->prepare("DELETE FROM artist WHERE artist.id IN "
158 "(SELECT COUNT(tracks.id) AS cnt, artist.id FROM "
159 "artist LEFT OUTER JOIN tracks ON artist.id = tracks.artist_id "
160 "GROUP BY artist.id) WHERE cnt = 0)");
162 _remove_tracks_from_query = new QSqlQuery(db);
163 _remove_tracks_from_query->prepare("DELETE FROM tracks WHERE directory = :directory_id");
165 _remove_directory_query = new QSqlQuery(db);
166 _remove_directory_query->prepare("DELETE FROM directories WHERE path = :path");
168 _remove_track_from_favorites_query = new QSqlQuery(db);
169 _remove_track_from_favorites_query->prepare("DELETE FROM favorites WHERE track_id in (SELECT id FROM tracks WHERE source = :source)");
172 void DbStorage::_create_database_structure() {
173 QSqlQuery *query = new QSqlQuery(db);
174 query->exec("create table artist (id integer primary key, "
178 query->exec("create table album (id integer primary key, "
179 "artist_id integer, "
183 "foreign key(artist_id) references arist(id) "
185 query->exec("create table tracks (id integer primary key, "
186 "artist_id integer, "
191 "directory integer, "
192 "count integer default 0, "
193 "length integer default 0, "
194 "foreign key(artist_id) references artist(id), "
195 "foreign key(album_id) references album(id) "
197 query->exec("create table favorites (track_id integer, "
198 "foreign key(track_id) references tracks(id) "
200 query->exec("create table adding_date (track_id integer, "
202 "foreign key(track_id) references tracks(id) "
204 query->exec("create view entire as "
205 "select id, title, artist_name as artist, album_name as album, source, count, length, album_year as year, utitle, artist_uname, album_uname from "
207 "(select artist.id as aartist_id, "
208 "artist.uname as artist_uname, "
209 "artist.name as artist_name, "
210 "album.uname as album_uname, "
211 "album.year as album_year, "
212 "album.id as aalbum_id, "
213 "album.name as album_name from "
216 "on album.artist_id = artist.id) "
217 "on aartist_id = tracks.artist_id "
218 "and aalbum_id = tracks.album_id");
220 query->exec("create table directories (id integer primary key, path text)");
223 DbStorage::~DbStorage() {
224 delete _get_albums_for_artist_sort_name_query;
225 delete _get_albums_for_artist_sort_year_query;
226 delete _get_artists_query;
227 delete _get_favorites_query;
228 delete _get_most_played_query;
229 delete _get_never_played_query;
230 delete _get_recently_added_query;
231 delete _get_tracks_for_album_query;
232 delete _get_tracks_by_pattern_query;
233 delete _get_directories_query;
234 delete _get_artists_count_query;
235 delete _get_albums_count_query;
236 delete _get_tracks_count_query;
237 delete _get_tracks_source_from_query;
238 delete _check_album_query;
239 delete _check_artist_query;
240 delete _check_directory_query;
241 delete _get_track_id_by_source_query;
242 delete _insert_album_query;
243 delete _insert_artist_query;
244 delete _insert_date_query;
245 delete _insert_track_query;
246 delete _insert_favorites_query;
247 delete _insert_directory_query;
248 delete _update_track_count_query;
249 delete _remove_track_query;
250 delete _remove_directory_query;
251 delete _remove_tracks_from_query;
252 delete _remove_track_from_favorites_query;
256 QList<QString> DbStorage::getArtists() {
257 QList<QString> artists;
258 QSqlQuery *query = _get_artists_query;
260 while (query->next()) {
261 QString name = query->value(0).toString();
262 artists.append(name);
267 QMap<QString, int> DbStorage::getAlbumsForArtist(QString artist) {
268 QMap<QString, int> albums;
270 QString sort = config.getValue("ui/albumsorting").toString();
271 QSqlQuery *query = NULL;
272 if (sort == "date") {
273 query = _get_albums_for_artist_sort_year_query;
275 query = _get_albums_for_artist_sort_name_query;
277 query->bindValue(":uname", artist.toUpper());
279 while (query->next()) {
280 QString name = query->value(0).toString();
281 albums[name] = query->value(1).toInt();
286 QList<Track> DbStorage::getTracksForAlbum(QString album, QString artist) {
288 QSqlQuery *query = _get_tracks_for_album_query;
289 query->bindValue(":artist_uname", artist.toUpper());
290 query->bindValue(":album_uname", album.toUpper());
293 while (query->next()) {
294 QString title = query->value(1).toString();
295 QString source = query->value(2).toString();
296 int count = query->value(3).toInt();
297 int length = query->value(4).toInt();
298 TrackMetadata meta (title, artist, album, length);
299 Track track(meta, source);
300 track.setCount(count);
301 tracks.append(track);
307 Playlist DbStorage::getFavorites() {
309 QSqlQuery *query = _get_favorites_query;
311 while(query->next()) {
312 QString title = query->value(1).toString();
313 QString artist = query->value(2).toString();
314 QString album = query->value(3).toString();
315 QString source = query->value(4).toString();
316 int count = query->value(5).toInt();
317 int length = query->value(6).toInt();
318 TrackMetadata meta(title, artist, album, length);
319 Track track(meta, source);
320 track.setCount(count);
321 playlist.addTrack(track);
322 playlist.setName("Favorites");
327 Playlist DbStorage::getMostPlayed() {
329 QSqlQuery *query = _get_most_played_query;
330 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
332 while (query->next()) {
333 QString title = query->value(1).toString();
334 QString artist = query->value(2).toString();
335 QString album = query->value(3).toString();
336 QString source = query->value(4).toString();
337 int count = query->value(5).toInt();
338 int length = query->value(6).toInt();
339 TrackMetadata meta(title, artist, album, length);
340 Track track(meta, source);
341 track.setCount(count);
342 playlist.addTrack(track);
343 playlist.setName("Most popular");
348 Playlist DbStorage::getNeverPlayed() {
350 QSqlQuery *query = _get_never_played_query;
351 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
353 while (query->next()) {
354 QString title = query->value(1).toString();
355 QString artist = query->value(2).toString();
356 QString album = query->value(3).toString();
357 QString source = query->value(4).toString();
358 int count = query->value(5).toInt();
359 int length = query->value(6).toInt();
360 TrackMetadata meta(title, artist, album, length);
361 Track track(meta, source);
362 track.setCount(count);
363 playlist.addTrack(track);
364 playlist.setName("Never played");
369 Playlist DbStorage::getRecentlyAdded() {
371 QSqlQuery *query = _get_recently_added_query;
372 query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
374 while (query->next()) {
375 QString title = query->value(1).toString();
376 QString artist = query->value(2).toString();
377 QString album = query->value(3).toString();
378 QString source = query->value(4).toString();
379 int count = query->value(5).toInt();
380 int length = query->value(6).toInt();
381 TrackMetadata meta(title, artist, album, length);
382 Track track(meta, source);
383 track.setCount(count);
384 playlist.addTrack(track);
385 playlist.setName("Recently added");
390 void DbStorage::removeTrack(Track track) {
391 QSqlQuery *query = _get_track_id_by_source_query;
392 query->bindValue(":source", track.source());
395 int id = query->value(0).toInt();
396 query = new QSqlQuery(db);
397 query->prepare("DELETE FROM tracks WHERE id = :id;");
398 query->bindValue(":id", id);
400 query->prepare("DELETE FROM favorites WHERE track_id = :id;");
401 query->bindValue(":id", id);
403 query->prepare("DELETE FROM adding_date WHERE id = :id;");
404 query->bindValue(":id", id);
409 void DbStorage::addTrack(Track track) {
410 QString title = track.metadata().title();
411 QString artist = track.metadata().artist();
412 QString album = track.metadata().album();
413 QString source = track.source();
414 QFileInfo info(source);
415 QString path = info.canonicalPath();
416 int year = track.metadata().year();
417 int artist_id = _check_add_artist(artist);
418 int album_id = _check_add_album(album, artist_id, year);
419 if (artist_id == -1 || album_id == -1) {
423 QSqlQuery *query = _get_track_id_by_source_query;
424 query->bindValue(":source", source);
427 // already in datebase, skip
430 query = _insert_track_query;
431 query->bindValue(":title", title);
432 query->bindValue(":utitle", title.toUpper());
433 query->bindValue(":artist_id", artist_id);
434 query->bindValue(":album_id", album_id);
435 query->bindValue(":source", source);
436 query->bindValue(":directory", _check_add_directory(path));
437 query->bindValue(":length", track.metadata().length());
440 query = _get_track_id_by_source_query;
441 query->bindValue(":source", source);
444 int id = query->value(0).toInt();
445 query = _insert_date_query;
446 query->bindValue(":track_id", id);
460 void DbStorage::addToFavorites(Track track) {
461 QSqlQuery *query = _get_track_id_by_source_query;
462 query->bindValue(":source", track.source());
465 int id = query->value(0).toInt();
466 query = _insert_favorites_query;
467 query->bindValue(":track_id", id);
472 void DbStorage::updateTrackCount(Track track) {
473 QSqlQuery *query = _get_track_id_by_source_query;
474 query->bindValue(":source", track.source());
477 int id = query->value(0).toInt();
478 query = _get_track_count_query;
479 query->bindValue(":id", id);
482 int count = query->value(0).toInt();
483 query = _update_track_count_query;
484 query->bindValue(":count", count+1);
485 query->bindValue(":id", id);
491 Track DbStorage::updateTrack(Track track) {
492 QSqlQuery *query = _get_track_id_by_source_query;
493 query->bindValue(":source", track.source());
495 if (query->next()) { // found track in library
496 int id = query->value(0).toInt();
497 query = _remove_track_query;
498 query->bindValue(":id", id);
506 int DbStorage::_check_add_artist(QString artist) {
507 QSqlQuery *query = _check_artist_query;
508 query->bindValue(":uname", artist.toUpper());
511 int id = query->value(0).toInt();
514 query = _insert_artist_query;
515 query->bindValue(":name", artist);
516 query->bindValue(":uname", artist.toUpper());
518 return _check_add_artist(artist);
526 int DbStorage::_check_add_album(QString album, int artist_id, int year) {
527 QSqlQuery *query = _check_album_query;
528 query->bindValue(":uname", album.toUpper());
529 query->bindValue(":artist_id", artist_id);
532 int id = query->value(0).toInt();
535 query = _insert_album_query;
536 query->bindValue(":name", album);
537 query->bindValue(":uname", album.toUpper());
538 query->bindValue(":artist_id", artist_id);
539 query->bindValue(":year", year);
541 return _check_add_album(album, artist_id, year);
549 QList<Track> DbStorage::searchTracks(QString pattern) {
551 QSqlQuery *query = _get_tracks_by_pattern_query;
552 query->bindValue(":ptitle", pattern.toUpper()); // with :pattern only doesn't work
554 // id, title, artist, album, source, count, length, year
555 while (query->next()) {
556 QString title = query->value(1).toString();
557 QString artist = query->value(2).toString();
558 QString album = query->value(3).toString();
559 QString source = query->value(4).toString();
560 int count = query->value(5).toInt();
561 int length = query->value(6).toInt();
562 int year = query->value(7).toInt();
563 TrackMetadata meta(title, artist, album, length);
565 Track track(meta, source);
566 track.setCount(count);
572 void DbStorage::_cleanup() {
573 _remove_empty_albums_query->exec();
574 _remove_empty_artists_query->exec();
577 int DbStorage::_check_add_directory(QString path) {
578 QSqlQuery *query = _check_directory_query;
579 query->bindValue(":path", path);
582 return query->value(0).toInt();
584 query = _insert_directory_query;
585 query->bindValue(":path", path);
587 return _check_add_directory(path);
591 QList<QString> DbStorage::getDirectories() {
592 QSqlQuery *query = _get_directories_query;
594 QList<QString> directories;
595 while (query->next()) {
596 directories.append(query->value(1).toString());
601 int DbStorage::getArtistsCount() {
602 QSqlQuery *query = _get_artists_count_query;
605 return query->value(0).toInt();
610 int DbStorage::getAlbumsCount() {
611 QSqlQuery *query = _get_albums_count_query;
614 return query->value(0).toInt();
619 int DbStorage::getTracksCount() {
620 QSqlQuery *query = _get_tracks_count_query;
623 return query->value(0).toInt();
628 void DbStorage::deleteTracksFrom(QString path) {
629 QSqlQuery *query = _check_directory_query;
630 query->bindValue(":path", path);
633 int id = query->value(0).toInt();
634 query = _remove_tracks_from_query;
635 query->bindValue(":directory_id", id);
637 query = _remove_directory_query;
638 query->bindValue(":path", path);
644 void DbStorage::checkTracksFrom(QString path) {
645 QSqlQuery *query = _check_directory_query;
646 query->bindValue(":path", path);
649 int id = query->value(0).toInt();
650 query = _get_tracks_source_from_query;
651 query->bindValue(":directory_id", id);
653 while (query->next()) {
654 QString source = query->value(0).toString();
655 if (!QFile::exists(source)) {
656 Track track(TrackMetadata(), source); // removeTrack uses only source field, so
657 removeTrack(track); // we can use this method
664 void DbStorage::removeFromFavorites(Track track) {
665 QSqlQuery *query = _remove_track_from_favorites_query;
666 query->bindValue(":source", track.source());
670 bool DbStorage::isFavorite(Track track) {
671 QSqlQuery *query = _check_favorite_query;
672 query->bindValue(":source", track.source());
675 QString ans = query->value(0).toString();