vcs.maemo.org Git - someplayer/blob - src/dbstorage.cpp

   1 /*
   2  * SomePlayer - An alternate music player for Maemo 5
   3  * Copyright (C) 2010 Nikolay (somebody) Tischenko <niktischenko@gmail.com>
   4  *
   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.
   9  *
  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.
  14  *
  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.
  18  */
  19
  20 #include "dbstorage.h"
  21 #include <QSqlQuery>
  22 #include <QSqlResult>
  23 #include <QDebug>
  24 #include <QFileInfo>
  25
  26 using namespace SomePlayer::Storage;
  27 using namespace SomePlayer::DataObjects;
  28
  29 DbStorage::DbStorage(QString path) {
  30         QString dbname = path + _DATABASE_NAME_;
  31         db = QSqlDatabase::addDatabase("QSQLITE");
  32         db.setDatabaseName(dbname);
  33         if (!db.open()) {
  34                 // throw big exception
  35         }
  36         // create database structure
  37         _create_database_structure();
  38         _prepare_queries();
  39 }
  40
  41 void DbStorage::_prepare_queries() {
  42         _get_artists_query = new QSqlQuery(db);
  43         _get_artists_query->prepare("SELECT name FROM artist ORDER BY uname");
  44
  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;");
  47
  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;");
  50
  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);");
  55
  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);");
  62
  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 "
  68                                                                 "LIMIT 0, :max");
  69
  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 "
  75                                                                 "WHERE count = 0");
  76
  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 "
  82                                                                 "WHERE track_id IN "
  83                                                                 "(SELECT track_id FROM adding_date ORDER BY date DESC LIMIT 0, :max)");
  84
  85         _get_track_count_query = new QSqlQuery(db);
  86         _get_track_count_query->prepare("SELECT count from tracks WHERE id = :id");
  87
  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 "
  90                                               "entire WHERE "
  91                                               "utitle LIKE (SELECT '%' || :ptitle || '%') "
  92                                               "ORDER BY artist_uname, year");
  93
  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");
  96
  97         _get_directories_query = new QSqlQuery(db);
  98         _get_directories_query->prepare("SELECT id, path FROM directories");
  99
 100         _get_artists_count_query = new QSqlQuery(db);
 101         _get_artists_count_query->prepare("SELECT COUNT(id) AS cnt FROM artist");
 102
 103         _get_albums_count_query = new QSqlQuery(db);
 104         _get_albums_count_query->prepare("SELECT COUNT(id) AS cnt FROM album");
 105
 106         _get_tracks_count_query = new QSqlQuery(db);
 107         _get_tracks_count_query->prepare("SELECT COUNT(id) AS cnt FROM tracks");
 108
 109         _get_tracks_source_from_query = new QSqlQuery(db);
 110         _get_tracks_source_from_query->prepare("SELECT source FROM tracks WHERE directory = :directory_id");
 111
 112         _check_artist_query = new QSqlQuery(db);
 113         _check_artist_query->prepare("SELECT id FROM artist WHERE uname = :uname");
 114
 115         _check_album_query = new QSqlQuery(db);
 116         _check_album_query->prepare("SELECT id FROM album WHERE uname = :uname AND artist_id = :artist_id");
 117
 118         _check_directory_query = new QSqlQuery(db);
 119         _check_directory_query->prepare("SELECT id from directories WHERE path = :path");
 120
 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)");
 123
 124         _insert_artist_query = new QSqlQuery(db);
 125         _insert_artist_query->prepare("INSERT INTO artist (name, uname) values (:name, :uname)");
 126
 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)");
 129
 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)");
 132
 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'))");
 135
 136         _insert_favorites_query = new QSqlQuery(db);
 137         _insert_favorites_query->prepare("INSERT INTO favorites (track_id) values (:track_id)");
 138
 139         _insert_directory_query = new QSqlQuery(db);
 140         _insert_directory_query->prepare("INSERT INTO directories (path) values (:path)");
 141
 142         _update_track_count_query = new QSqlQuery(db);
 143         _update_track_count_query->prepare("UPDATE tracks SET count = :count where id = :id");
 144
 145         _remove_track_query = new QSqlQuery(db);
 146         _remove_track_query->prepare("DELETE FROM tracks WHERE id = :id");
 147
 148         _remove_empty_albums_query = new QSqlQuery(db);
 149         _remove_empty_albums_query->prepare("DELETE FROM album WHERE album.id IN "
 150                                             "(SELECT id FROM "
 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)");
 154
 155         _remove_empty_artists_query = new QSqlQuery(db);
 156         _remove_empty_artists_query->prepare("DELETE FROM artist WHERE artist.id IN "
 157                                              "(SELECT id FROM "
 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)");
 161
 162         _remove_tracks_from_query = new QSqlQuery(db);
 163         _remove_tracks_from_query->prepare("DELETE FROM tracks WHERE directory = :directory_id");
 164
 165         _remove_directory_query = new QSqlQuery(db);
 166         _remove_directory_query->prepare("DELETE FROM directories WHERE path = :path");
 167
 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)");
 170 }
 171
 172 void DbStorage::_create_database_structure() {
 173         QSqlQuery *query = new QSqlQuery(db);
 174         query->exec("create table artist (id integer primary key, "
 175                                                                 "name text, "
 176                                                                 "uname text "
 177                                                                 ");");
 178         query->exec("create table album (id integer primary key, "
 179                                                                 "artist_id integer, "
 180                                                                 "name text, "
 181                                                                 "uname text, "
 182                                                                 "year int, "
 183                                                                 "foreign key(artist_id) references arist(id) "
 184                                                                 ");");
 185         query->exec("create table tracks (id integer primary key, "
 186                                                                 "artist_id integer, "
 187                                                                 "album_id integer, "
 188                                                                 "title text, "
 189                                                                 "utitle text, "
 190                                                                 "source text, "
 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) "
 196                                                                 ");");
 197         query->exec("create table favorites (track_id integer, "
 198                                                                 "foreign key(track_id) references tracks(id) "
 199                                                                 ");");
 200         query->exec("create table adding_date (track_id integer, "
 201                                                                 "date integer, "
 202                                                                 "foreign key(track_id) references tracks(id) "
 203                                                                 ");");
 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 "
 206                         "tracks left join "
 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 "
 214                                         "artist "
 215                                         "join album "
 216                                         "on album.artist_id = artist.id) "
 217                                 "on aartist_id = tracks.artist_id "
 218                                 "and aalbum_id = tracks.album_id");
 219
 220         query->exec("create table directories (id integer primary key, path text)");
 221 }
 222
 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;
 253         db.close();
 254 }
 255
 256 QList<QString> DbStorage::getArtists() {
 257         QList<QString> artists;
 258         QSqlQuery *query = _get_artists_query;
 259         query->exec();
 260         while (query->next()) {
 261                 QString name = query->value(0).toString();
 262                 artists.append(name);
 263         }
 264         return artists;
 265 }
 266
 267 QMap<QString, int> DbStorage::getAlbumsForArtist(QString artist) {
 268         QMap<QString, int> albums;
 269         Config config;
 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;
 274         } else {
 275                 query = _get_albums_for_artist_sort_name_query;
 276         }
 277         query->bindValue(":uname", artist.toUpper());
 278         query->exec();
 279         while (query->next()) {
 280                 QString name = query->value(0).toString();
 281                 albums[name] = query->value(1).toInt();
 282         }
 283         return albums;
 284 }
 285
 286 QList<Track> DbStorage::getTracksForAlbum(QString album, QString artist) {
 287         QList<Track> tracks;
 288         QSqlQuery *query = _get_tracks_for_album_query;
 289         query->bindValue(":artist_uname", artist.toUpper());
 290         query->bindValue(":album_uname", album.toUpper());
 291         query->exec();
 292
 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);
 302         }
 303
 304         return tracks;
 305 }
 306
 307 Playlist DbStorage::getFavorites() {
 308         Playlist playlist;
 309         QSqlQuery *query = _get_favorites_query;
 310         query->exec();
 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");
 323         }
 324         return playlist;
 325 }
 326
 327 Playlist DbStorage::getMostPlayed() {
 328         Playlist playlist;
 329         QSqlQuery *query = _get_most_played_query;
 330         query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
 331         query->exec();
 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");
 344         }
 345         return playlist;
 346 }
 347
 348 Playlist DbStorage::getNeverPlayed() {
 349         Playlist playlist;
 350         QSqlQuery *query = _get_never_played_query;
 351         query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
 352         query->exec();
 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");
 365         }
 366         return playlist;
 367 }
 368
 369 Playlist DbStorage::getRecentlyAdded() {
 370         Playlist playlist;
 371         QSqlQuery *query = _get_recently_added_query;
 372         query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
 373         query->exec();
 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");
 386         }
 387         return playlist;
 388 }
 389
 390 void DbStorage::removeTrack(Track track) {
 391         QSqlQuery *query = _get_track_id_by_source_query;
 392         query->bindValue(":source", track.source());
 393         query->exec();
 394         if (query->next()) {
 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);
 399                 query->exec();
 400                 query->prepare("DELETE FROM favorites WHERE track_id = :id;");
 401                 query->bindValue(":id", id);
 402                 query->exec();
 403                 query->prepare("DELETE FROM adding_date WHERE id = :id;");
 404                 query->bindValue(":id", id);
 405                 query->exec();
 406         }
 407 }
 408
 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) {
 420                 //big bang
 421                 return;
 422         }
 423         QSqlQuery *query = _get_track_id_by_source_query;
 424         query->bindValue(":source", source);
 425         query->exec();
 426         if (query->next()) {
 427                 // already in datebase, skip
 428                 return;
 429         }
 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());
 438         if (query->exec()) {
 439                 //ok
 440                 query = _get_track_id_by_source_query;
 441                 query->bindValue(":source", source);
 442                 query->exec();
 443                 if (query->next()) {
 444                         int id = query->value(0).toInt();
 445                         query = _insert_date_query;
 446                         query->bindValue(":track_id", id);
 447                         if (query->exec()) {
 448                                 // ok
 449                         } else {
 450                                 // big bang
 451                         }
 452                 } else {
 453                         // big bang
 454                 }
 455         } else {
 456                 // big bang
 457         }
 458 }
 459
 460 void DbStorage::addToFavorites(Track track) {
 461         QSqlQuery *query = _get_track_id_by_source_query;
 462         query->bindValue(":source", track.source());
 463         query->exec();
 464         if (query->next()) {
 465                 int id = query->value(0).toInt();
 466                 query = _insert_favorites_query;
 467                 query->bindValue(":track_id", id);
 468                 query->exec();
 469         }
 470 }
 471
 472 void DbStorage::updateTrackCount(Track track) {
 473         QSqlQuery *query = _get_track_id_by_source_query;
 474         query->bindValue(":source", track.source());
 475         query->exec();
 476         if (query->next()) {
 477                 int id = query->value(0).toInt();
 478                 query = _get_track_count_query;
 479                 query->bindValue(":id", id);
 480                 query->exec();
 481                 if (query->next()) {
 482                         int count = query->value(0).toInt();
 483                         query = _update_track_count_query;
 484                         query->bindValue(":count", count+1);
 485                         query->bindValue(":id", id);
 486                         query->exec();
 487                 }
 488         }
 489 }
 490
 491 Track DbStorage::updateTrack(Track track) {
 492         QSqlQuery *query = _get_track_id_by_source_query;
 493         query->bindValue(":source", track.source());
 494         query->exec();
 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);
 499                 query->exec();
 500                 addTrack(track);
 501                 _cleanup();
 502         }
 503         return track;
 504 }
 505
 506 int DbStorage::_check_add_artist(QString artist) {
 507         QSqlQuery *query = _check_artist_query;
 508         query->bindValue(":uname", artist.toUpper());
 509         query->exec();
 510         if (query->next()) {
 511                 int id = query->value(0).toInt();
 512                 return id;
 513         } else {
 514                 query = _insert_artist_query;
 515                 query->bindValue(":name", artist);
 516                 query->bindValue(":uname", artist.toUpper());
 517                 if (query->exec()) {
 518                         return _check_add_artist(artist);
 519                 } else {
 520                         // big bang
 521                         return -1;
 522                 }
 523         }
 524 }
 525
 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);
 530         query->exec();
 531         if (query->next()) {
 532                 int id = query->value(0).toInt();
 533                 return id;
 534         } else {
 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);
 540                 if (query->exec()) {
 541                         return _check_add_album(album, artist_id, year);
 542                 } else {
 543                         // big bang
 544                         return -1;
 545                 }
 546         }
 547 }
 548
 549 QList<Track> DbStorage::searchTracks(QString pattern) {
 550         QList <Track> found;
 551         QSqlQuery *query = _get_tracks_by_pattern_query;
 552         query->bindValue(":ptitle", pattern.toUpper()); // with :pattern only doesn't work
 553         query->exec();
 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);
 564                 meta.setYear(year);
 565                 Track track(meta, source);
 566                 track.setCount(count);
 567                 found.append(track);
 568         }
 569         return found;
 570 }
 571
 572 void DbStorage::_cleanup() {
 573         _remove_empty_albums_query->exec();
 574         _remove_empty_artists_query->exec();
 575 }
 576
 577 int DbStorage::_check_add_directory(QString path) {
 578         QSqlQuery *query = _check_directory_query;
 579         query->bindValue(":path", path);
 580         query->exec();
 581         if (query->next()) {
 582                 return query->value(0).toInt();
 583         } else {
 584                 query = _insert_directory_query;
 585                 query->bindValue(":path", path);
 586                 query->exec();
 587                 return _check_add_directory(path);
 588         }
 589 }
 590
 591 QList<QString> DbStorage::getDirectories() {
 592         QSqlQuery *query = _get_directories_query;
 593         query->exec();
 594         QList<QString> directories;
 595         while (query->next()) {
 596                 directories.append(query->value(1).toString());
 597         }
 598         return directories;
 599 }
 600
 601 int DbStorage::getArtistsCount() {
 602         QSqlQuery *query = _get_artists_count_query;
 603         query->exec();
 604         if (query->next()) {
 605                 return query->value(0).toInt();
 606         }
 607         return 0;
 608 }
 609
 610 int DbStorage::getAlbumsCount() {
 611         QSqlQuery *query = _get_albums_count_query;
 612         query->exec();
 613         if (query->next()) {
 614                 return query->value(0).toInt();
 615         }
 616         return 0;
 617 }
 618
 619 int DbStorage::getTracksCount() {
 620         QSqlQuery *query = _get_tracks_count_query;
 621         query->exec();
 622         if (query->next()) {
 623                 return query->value(0).toInt();
 624         }
 625         return 0;
 626 }
 627
 628 void DbStorage::deleteTracksFrom(QString path) {
 629         QSqlQuery *query = _check_directory_query;
 630         query->bindValue(":path", path);
 631         query->exec();
 632         if (query->next()) {
 633                 int id = query->value(0).toInt();
 634                 query = _remove_tracks_from_query;
 635                 query->bindValue(":directory_id", id);
 636                 query->exec();
 637                 query = _remove_directory_query;
 638                 query->bindValue(":path", path);
 639                 query->exec();
 640                 _cleanup();
 641         }
 642 }
 643
 644 void DbStorage::checkTracksFrom(QString path) {
 645         QSqlQuery *query = _check_directory_query;
 646         query->bindValue(":path", path);
 647         query->exec();
 648         if (query->next()) {
 649                 int id = query->value(0).toInt();
 650                 query = _get_tracks_source_from_query;
 651                 query->bindValue(":directory_id", id);
 652                 query->exec();
 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
 658                         }
 659                 }
 660                 _cleanup();
 661         }
 662 }
 663
 664 void DbStorage::removeFromFavorites(Track track) {
 665         QSqlQuery *query = _remove_track_from_favorites_query;
 666         query->bindValue(":source", track.source());
 667         query->exec();
 668 }
 669
 670 bool DbStorage::isFavorite(Track track) {
 671         QSqlQuery *query = _check_favorite_query;
 672         query->bindValue(":source", track.source());
 673         query->exec();
 674         if (query->next()) {
 675                 QString ans = query->value(0).toString();
 676                 return ans == "yes";
 677         }
 678         return false;
 679 }