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