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