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