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