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