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