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