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