git.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
  24 using namespace SomePlayer::Storage;
  25 using namespace SomePlayer::DataObjects;
  26
  27 DbStorage::DbStorage(QString path) {
  28         QString dbname = path+_DATABASE_NAME_;
  29         db = QSqlDatabase::addDatabase("QSQLITE");
  30         db.setDatabaseName(dbname);
  31         if (!db.open()) {
  32                 // throw big exception
  33         }
  34         // create database structure
  35         _create_database_structure();
  36         _prepare_queries();
  37 }
  38
  39 void DbStorage::_prepare_queries() {
  40         _get_artists_query = new QSqlQuery(db);
  41         _get_artists_query->prepare("SELECT name FROM artist");
  42
  43         _get_albums_for_artist_query = new QSqlQuery(db);
  44         _get_albums_for_artist_query->prepare("SELECT name FROM album WHERE artist_id in (SELECT id from artist WHERE name = :name);");
  45
  46         _get_tracks_for_album_query = new QSqlQuery(db);
  47         _get_tracks_for_album_query->prepare("SELECT id, title, source, count, length FROM tracks WHERE artist_id IN "
  48                                                                                  "(SELECT id FROM artist WHERE name = :artist_name) AND album_id IN "
  49                                                                                  "(SELECT id FROM album WHERE name = :album_name);");
  50
  51         _get_favorites_query = new QSqlQuery(db);
  52         _get_favorites_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
  53                                                                   "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
  54                                                                   "tracks JOIN artist ON tracks.artist_id = artist.id) "
  55                                                                   "JOIN album ON album_id = album.id WHERE track_id IN "
  56                                                                   "(SELECT track_id FROM favorites);");
  57
  58         _get_most_played_query = new QSqlQuery(db);
  59         _get_most_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
  60                                                                         "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
  61                                                                         "tracks JOIN artist ON tracks.artist_id = artist.id) "
  62                                                                         "JOIN album ON album_id = album.id ORDER BY count DESC "
  63                                                                         "LIMIT 0, :max");
  64
  65         _get_never_played_query = new QSqlQuery(db);
  66         _get_never_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
  67                                                                          "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
  68                                                                          "tracks JOIN artist ON tracks.artist_id = artist.id) "
  69                                                                          "JOIN album ON album_id = album.id "
  70                                                                          "WHERE count = 0");
  71
  72         _get_recently_added_query = new QSqlQuery(db);
  73         _get_recently_added_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count, length FROM "
  74                                                                            "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id, length FROM "
  75                                                                            "tracks JOIN artist ON tracks.artist_id = artist.id) "
  76                                                                            "JOIN album ON album_id = album.id "
  77                                                                            "WHERE track_id IN "
  78                                                                            "(SELECT track_id FROM adding_date ORDER BY date DESC LIMIT 0, :max)");
  79
  80         _get_track_count = new QSqlQuery(db);
  81         _get_track_count->prepare("SELECT count from tracks WHERE id = :id");
  82
  83         _check_artist_query = new QSqlQuery(db);
  84         _check_artist_query->prepare("SELECT id FROM artist WHERE name = :name");
  85
  86         _check_album_query = new QSqlQuery(db);
  87         _check_album_query->prepare("SELECT id FROM album WHERE name = :name AND artist_id = :artist_id");
  88
  89         _check_track_query = new QSqlQuery(db);
  90         _check_track_query->prepare("SELECT id FROM tracks WHERE source = :source");
  91
  92         _insert_artist_query = new QSqlQuery(db);
  93         _insert_artist_query->prepare("INSERT INTO artist (name) values (:name)");
  94
  95         _insert_album_query = new QSqlQuery(db);
  96         _insert_album_query->prepare("INSERT INTO album (name, artist_id) values (:name, :artist_id)");
  97
  98         _insert_track_query = new QSqlQuery(db);
  99         _insert_track_query->prepare("INSERT INTO tracks (title, artist_id, album_id, source, length) values (:title, :artist_id, :album_id, :source, :length)");
 100
 101         _insert_date_query = new QSqlQuery(db);
 102         _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))");
 103
 104         _insert_favorites_query = new QSqlQuery(db);
 105         _insert_favorites_query->prepare("INSERT INTO favorites (track_id) values (:track_id)");
 106
 107         _update_track_count_query = new QSqlQuery(db);
 108         _update_track_count_query->prepare("UPDATE tracks SET count = :count where id = :id");
 109 }
 110
 111 void DbStorage::_create_database_structure() {
 112         QSqlQuery *query = new QSqlQuery(db);
 113         query->exec("create table artist (id integer primary key, "
 114                                                                                 "name text "
 115                                                                                 ");");
 116         query->exec("create table album (id integer primary key, "
 117                                                                                 "artist_id integer, "
 118                                                                                 "name text, "
 119                                                                                 "foreign key(artist_id) references arist(id) "
 120                                                                                 ");");
 121         query->exec("create table tracks (id integer primary key, "
 122                                                                                 "artist_id integer, "
 123                                                                                 "album_id integer, "
 124                                                                                 "title text, "
 125                                                                                 "source text, "
 126                                                                                 "count integer default 0, "
 127                                                                                 "length integer default 0, "
 128                                                                                 "foreign key(artist_id) references artist(id), "
 129                                                                                 "foreign key(album_id) references album(id) "
 130                                                                                 ");");
 131         query->exec("create table favorites (track_id integer, "
 132                                                                                 "foreign key(track_id) references tracks(id) "
 133                                                                                 ");");
 134         query->exec("create table adding_date (track_id integer, "
 135                                                                                 "date integer, "
 136                                                                                 "foreign key(track_id) references tracks(id) "
 137                                                                                 ");");
 138 }
 139
 140 DbStorage::~DbStorage() {
 141         delete _get_albums_for_artist_query;
 142         delete _get_artists_query;
 143         delete _get_favorites_query;
 144         delete _get_most_played_query;
 145         delete _get_never_played_query;
 146         delete _get_recently_added_query;
 147         delete _get_tracks_for_album_query;
 148         delete _check_album_query;
 149         delete _check_artist_query;
 150         delete _check_track_query;
 151         delete _insert_album_query;
 152         delete _insert_artist_query;
 153         delete _insert_date_query;
 154         delete _insert_track_query;
 155         delete _insert_favorites_query;
 156         delete _update_track_count_query;
 157         db.close();
 158 }
 159
 160 QList<QString> DbStorage::getArtists() {
 161         QList<QString> artists;
 162         QSqlQuery *query = _get_artists_query;
 163         query->exec();
 164         while (query->next()) {
 165                 QString name = query->value(0).toString();
 166                 artists.append(name);
 167         }
 168         return artists;
 169 }
 170
 171 QList<QString> DbStorage::getAlbumsForArtist(QString artist) {
 172         QList<QString> albums;
 173         QSqlQuery *query = _get_albums_for_artist_query;
 174         query->bindValue(":name", artist);
 175         query->exec();
 176         while (query->next()) {
 177                 QString name = query->value(0).toString();
 178                 albums.append(name);
 179         }
 180         return albums;
 181 }
 182
 183 QList<Track> DbStorage::getTracksForAlbum(QString album, QString artist) {
 184         QList<Track> tracks;
 185         QSqlQuery *query = _get_tracks_for_album_query;
 186         query->bindValue(":artist_name", artist);
 187         query->bindValue(":album_name", album);
 188         query->exec();
 189
 190         while (query->next()) {
 191                 int id = query->value(0).toInt();
 192                 QString title = query->value(1).toString();
 193                 QString source = query->value(2).toString();
 194                 int count = query->value(3).toInt();
 195                 int length = query->value(4).toInt();
 196                 TrackMetadata meta (title, artist, album, length);
 197                 Track track(id, meta, source);
 198                 track.setCount(count);
 199                 tracks.append(track);
 200         }
 201
 202         return tracks;
 203 }
 204
 205 Playlist DbStorage::getFavorites() {
 206         Playlist playlist;
 207         QSqlQuery *query = _get_favorites_query;
 208         query->exec();
 209         while(query->next()) {
 210                 int id = query->value(0).toInt();
 211                 QString title = query->value(1).toString();
 212                 QString artist = query->value(2).toString();
 213                 QString album = query->value(3).toString();
 214                 QString source = query->value(4).toString();
 215                 int count = query->value(5).toInt();
 216                 int length = query->value(6).toInt();
 217                 TrackMetadata meta(title, artist, album, length);
 218                 Track track(id, meta, source);
 219                 track.setCount(count);
 220                 playlist.addTrack(track);
 221                 playlist.setName("Favorites");
 222         }
 223         return playlist;
 224 }
 225
 226 Playlist DbStorage::getMostPlayed() {
 227         Playlist playlist;
 228         QSqlQuery *query = _get_most_played_query;
 229         query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
 230         query->exec();
 231         while (query->next()) {
 232                 int id = query->value(0).toInt();
 233                 QString title = query->value(1).toString();
 234                 QString artist = query->value(2).toString();
 235                 QString album = query->value(3).toString();
 236                 QString source = query->value(4).toString();
 237                 int count = query->value(5).toInt();
 238                 int length = query->value(6).toInt();
 239                 TrackMetadata meta(title, artist, album, length);
 240                 Track track(id, meta, source);
 241                 track.setCount(count);
 242                 playlist.addTrack(track);
 243                 playlist.setName("Most popular");
 244         }
 245         return playlist;
 246 }
 247
 248 Playlist DbStorage::getNeverPlayed() {
 249         Playlist playlist;
 250         QSqlQuery *query = _get_never_played_query;
 251         query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
 252         query->exec();
 253         while (query->next()) {
 254                 int id = query->value(0).toInt();
 255                 QString title = query->value(1).toString();
 256                 QString artist = query->value(2).toString();
 257                 QString album = query->value(3).toString();
 258                 QString source = query->value(4).toString();
 259                 int count = query->value(5).toInt();
 260                 int length = query->value(6).toInt();
 261                 TrackMetadata meta(title, artist, album, length);
 262                 Track track(id, meta, source);
 263                 track.setCount(count);
 264                 playlist.addTrack(track);
 265                 playlist.setName("Never played");
 266         }
 267         return playlist;
 268 }
 269
 270 Playlist DbStorage::getRecentlyAdded() {
 271         Playlist playlist;
 272         QSqlQuery *query = _get_recently_added_query;
 273         query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_);
 274         query->exec();
 275         while (query->next()) {
 276                 int id = query->value(0).toInt();
 277                 QString title = query->value(1).toString();
 278                 QString artist = query->value(2).toString();
 279                 QString album = query->value(3).toString();
 280                 QString source = query->value(4).toString();
 281                 int count = query->value(5).toInt();
 282                 int length = query->value(6).toInt();
 283                 TrackMetadata meta(title, artist, album, length);
 284                 Track track(id, meta, source);
 285                 track.setCount(count);
 286                 playlist.addTrack(track);
 287                 playlist.setName("Recently added");
 288         }
 289         return playlist;
 290 }
 291
 292 void DbStorage::removeTrack(Track track) {
 293         int id = track.id();
 294         QSqlQuery *query = new QSqlQuery(db);
 295         query->prepare("DELETE FROM tracks WHERE id = :id;");
 296         query->bindValue(":id", id);
 297         query->exec();
 298         query->prepare("DELETE FROM favorites WHERE track_id = :id;");
 299         query->bindValue(":id", id);
 300         query->exec();
 301         query->prepare("DELETE FROM adding_date WHERE id = :id;");
 302         query->bindValue(":id", id);
 303         query->exec();
 304 }
 305
 306 void DbStorage::addTrack(Track track) {
 307         QString title = track.metadata().title();
 308         QString artist = track.metadata().artist();
 309         QString album = track.metadata().album();
 310         QString source = track.source();
 311         int artist_id = _check_add_artist(artist);
 312         int album_id = _check_add_album(album, artist_id);
 313         if (artist_id == -1 || album_id == -1) {
 314                 //big bang
 315                 return;
 316         }
 317         QSqlQuery *query = _check_track_query;
 318         query->bindValue(":source", source);
 319         query->exec();
 320         if (query->next()) {
 321                 // already in datebase, skip
 322                 return;
 323         }
 324         query = _insert_track_query;
 325         query->bindValue(":title", title);
 326         query->bindValue(":artist_id", artist_id);
 327         query->bindValue(":album_id", album_id);
 328         query->bindValue(":source", source);
 329         query->bindValue(":length", track.metadata().length());
 330         if (query->exec()) {
 331                 //ok
 332                 query = _check_track_query;
 333                 query->bindValue(":source", source);
 334                 query->exec();
 335                 if (query->next()) {
 336                         int id = query->value(0).toInt();
 337                         query = _insert_date_query;
 338                         query->bindValue(":track_id", id);
 339                         if (query->exec()) {
 340                                 // ok
 341                         } else {
 342                                 // big bang
 343                         }
 344                 } else {
 345                         // big bang
 346                 }
 347         } else {
 348                 // big bang
 349         }
 350 }
 351
 352 void DbStorage::addToFavorites(Track track) {
 353         QSqlQuery *query = _insert_favorites_query;
 354         query->bindValue(":track_id", track.id());
 355         query->exec();
 356 }
 357
 358 void DbStorage::updateTrackCount(Track track) {
 359         QSqlQuery *query = _get_track_count;
 360         query->bindValue(":id", track.id());
 361         query->exec();
 362         if (query->next()) {
 363                 int count = query->value(0).toInt();
 364                 query = _update_track_count_query;
 365                 query->bindValue(":count", count+1);
 366                 query->bindValue(":id", track.id());
 367                 query->exec();
 368         }
 369 }
 370
 371 int DbStorage::_check_add_artist(QString artist) {
 372         QSqlQuery *query = _check_artist_query;
 373         query->bindValue(":name", artist);
 374         query->exec();
 375         if (query->next()) {
 376                 int id = query->value(0).toInt();
 377                 return id;
 378         } else {
 379                 query = _insert_artist_query;
 380                 query->bindValue(":name", artist);
 381                 if (query->exec()) {
 382                         return _check_add_artist(artist);
 383                 } else {
 384                         // big bang
 385                         return -1;
 386                 }
 387         }
 388 }
 389
 390 int DbStorage::_check_add_album(QString album, int artist_id) {
 391         QSqlQuery *query = _check_album_query;
 392         query->bindValue(":name", album);
 393         query->bindValue(":artist_id", artist_id);
 394         query->exec();
 395         if (query->next()) {
 396                 int id = query->value(0).toInt();
 397                 return id;
 398         } else {
 399                 query = _insert_album_query;
 400                 query->bindValue(":name", album);
 401                 query->bindValue(":artist_id", artist_id);
 402                 if (query->exec()) {
 403                         return _check_add_album(album, artist_id);
 404                 } else {
 405                         // big bang
 406                         return -1;
 407                 }
 408         }
 409 }