git.maemo.org Git - someplayer/blob - src/dbstorage.cpp

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