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