From: Nikolay Tischenko Date: Sat, 4 Sep 2010 20:15:35 +0000 (+0700) Subject: Implemented Database storage for library. X-Git-Tag: release-1.0~22 X-Git-Url: http://git.maemo.org/git/?p=someplayer;a=commitdiff_plain;h=4e6c2c79b2071319631846dd9459234a27f700de Implemented Database storage for library. Temporary removed genre support for library. --- diff --git a/someplayer.pro b/someplayer.pro index 2a3ceb9..09682c6 100644 --- a/someplayer.pro +++ b/someplayer.pro @@ -4,7 +4,7 @@ # #------------------------------------------------- -QT += core gui phonon +QT += core gui phonon sql TARGET = someplayer TEMPLATE = app diff --git a/someplayer.pro.user b/someplayer.pro.user index ff27c4c..5ed6616 100644 --- a/someplayer.pro.user +++ b/someplayer.pro.user @@ -202,7 +202,7 @@ 1 - 2010-09-04T19:46:35 + 2010-09-05T03:10:24 1 diff --git a/src/dbstorage.cpp b/src/dbstorage.cpp index 764f616..95cc980 100644 --- a/src/dbstorage.cpp +++ b/src/dbstorage.cpp @@ -1 +1,344 @@ #include "dbstorage.h" +#include +#include + +using namespace SomePlayer::Storage; +using namespace SomePlayer::DataObjects; + +DbStorage::DbStorage(QString path) { + QString dbname = path+_DATABASE_NAME_; + db = QSqlDatabase::addDatabase("QSQLITE"); + db.setDatabaseName(dbname); + if (!db.open()) { + // throw big exception + } + // create database structure + _create_database_structure(); + _prepare_queries(); +} + +void DbStorage::_prepare_queries() { + _get_artists_query = new QSqlQuery(db); + _get_artists_query->prepare("SELECT name FROM artist"); + + _get_albums_for_artist_query = new QSqlQuery(db); + _get_albums_for_artist_query->prepare("SELECT name FROM album WHERE artist_id in (SELECT id from artist WHERE name = :name);"); + + _get_tracks_for_album_query = new QSqlQuery(db); + _get_tracks_for_album_query->prepare("SELECT id, title, source, count FROM tracks WHERE artist_id IN " + "(SELECT id FROM artist WHERE name = :artist_name) AND album_id IN " + "(SELECT id FROM album WHERE name =: album_name);"); + + _get_favorites_query = new QSqlQuery(db); + _get_favorites_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count FROM " + "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id FROM " + "tracks JOIN artist ON tracks.artist_id = artist.id) " + "JOIN album ON album_id = album.id WHERE track_id IN " + "(SELECT track_id FROM favorites);"); + + _get_most_played_query = new QSqlQuery(db); + _get_most_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count FROM " + "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id FROM " + "tracks JOIN artist ON tracks.artist_id = artist.id) " + "JOIN album ON album_id = album.id ORDER BY count DESC " + "LIMIT 0, :max"); + + _get_never_played_query = new QSqlQuery(db); + _get_never_played_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count FROM " + "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id FROM " + "tracks JOIN artist ON tracks.artist_id = artist.id) " + "JOIN album ON album_id = album.id " + "WHERE count = 0"); + + _get_recently_added_query = new QSqlQuery(db); + _get_recently_added_query->prepare("SELECT track_id as id, title, artist, album.name as album, source, count FROM " + "(SELECT tracks.id AS track_id, artist.name AS artist, title, count, source, tracks.album_id FROM " + "tracks JOIN artist ON tracks.artist_id = artist.id) " + "JOIN album ON album_id = album.id " + "WHERE track_id IN " + "(SELECT track_id FROM adding_date ORDER BY date DESC LIMIT 0, :max)"); + _check_artist_query = new QSqlQuery(db); + _check_artist_query->prepare("SELECT id FROM artist WHERE name = :name"); + + _check_album_query = new QSqlQuery(db); + _check_album_query->prepare("SELECT id FROM album WHERE name = :name AND artist_id = :artist_id"); + + _check_track_query = new QSqlQuery(db); + _check_track_query->prepare("SELECT id FROM tracks WHERE source = :source"); + + _insert_artist_query = new QSqlQuery(db); + _insert_artist_query->prepare("INSERT INTO artist (name) values (:name)"); + + _insert_album_query = new QSqlQuery(db); + _insert_album_query->prepare("INSERT INTO album (name, artist_id) values (:name, :artist_id)"); + + _insert_track_query = new QSqlQuery(db); + _insert_track_query->prepare("INSERT INTO tracks (title, artist_id, album_id, source) values (:title, :artist_id, :album_id, :source)"); + + _insert_date_query = new QSqlQuery(db); + _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))"); +} + +void DbStorage::_create_database_structure() { + QSqlQuery *query = new QSqlQuery(db); + query->exec("create table artist (id integer primary key, " + "name text " + ");"); + query->exec("create table album (id integer primary key, " + "artist_id integer, " + "name text, " + "foreign key(artist_id) references arist(id) " + ");"); + query->exec("create table tracks (id integer primary key, " + "artist_id integer, " + "album_id integer, " + "title text, " + "source text, " + "count integer default 0, " + "foreign key(artist_id) references artist(id), " + "foreign key(album_id) references album(id) " + ");"); + query->exec("create table favorites (track_id integer, " + "foreign key(track_id) references tracks(id) " + ");"); + query->exec("create table adding_date (track_id integer, " + "date integer, " + "foreign key(track_id) references tracks(id) " + ");"); +} + +DbStorage::~DbStorage() { + delete _get_albums_for_artist_query; + delete _get_artists_query; + delete _get_favorites_query; + delete _get_most_played_query; + delete _get_never_played_query; + delete _get_recently_added_query; + delete _get_tracks_for_album_query; + db.close(); +} + +QList DbStorage::getArtists() { + QList artists; + QSqlQuery *query = _get_artists_query; + query->exec(); + while (query->next()) { + QString name = query->value(0).toString(); + artists.append(name); + } + return artists; +} + +QList DbStorage::getAlbumsForArtist(QString artist) { + QList albums; + QSqlQuery *query = _get_albums_for_artist_query; + query->bindValue(":name", artist); + query->exec(); + while (query->next()) { + QString name = query->value(0).toString(); + albums.append(name); + } + return albums; +} + +QList DbStorage::getTracksForAlbum(QString album, QString artist) { + QList tracks; + QSqlQuery *query = _get_tracks_for_album_query; + query->bindValue(":artist_name", artist); + query->bindValue(":album_name", album); + query->exec(); + + while (query->next()) { + int id = query->value(0).toInt(); + QString title = query->value(1).toString(); + QString source = query->value(2).toString(); + int count = query->value(3).toInt(); + TrackMetadata meta (title, artist, album); + Track track(id, meta, source); + track.setCount(count); + tracks.append(track); + } + + return tracks; +} + +Playlist DbStorage::getFavorites() { + Playlist playlist; + QSqlQuery *query = _get_favorites_query; + query->exec(); + while(query->next()) { + int id = query->value(0).toInt(); + QString title = query->value(1).toString(); + QString artist = query->value(2).toString(); + QString album = query->value(3).toString(); + QString source = query->value(4).toString(); + int count = query->value(5).toInt(); + TrackMetadata meta(title, artist, album); + Track track(id, meta, source); + track.setCount(count); + playlist.addTrack(track); + playlist.setName("Favorites"); + } + return playlist; +} + +Playlist DbStorage::getMostPlayed() { + Playlist playlist; + QSqlQuery *query = _get_most_played_query; + query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_); + query->exec(); + while (query->next()) { + int id = query->value(0).toInt(); + QString title = query->value(1).toString(); + QString artist = query->value(2).toString(); + QString album = query->value(3).toString(); + QString source = query->value(4).toString(); + int count = query->value(5).toInt(); + TrackMetadata meta(title, artist, album); + Track track(id, meta, source); + track.setCount(count); + playlist.addTrack(track); + playlist.setName("Most popular"); + } + return playlist; +} + +Playlist DbStorage::getNeverPlayed() { + Playlist playlist; + QSqlQuery *query = _get_never_played_query; + query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_); + query->exec(); + while (query->next()) { + int id = query->value(0).toInt(); + QString title = query->value(1).toString(); + QString artist = query->value(2).toString(); + QString album = query->value(3).toString(); + QString source = query->value(4).toString(); + int count = query->value(5).toInt(); + TrackMetadata meta(title, artist, album); + Track track(id, meta, source); + track.setCount(count); + playlist.addTrack(track); + playlist.setName("Never played"); + } + return playlist; +} + +Playlist DbStorage::getRecentlyAdded() { + Playlist playlist; + QSqlQuery *query = _get_recently_added_query; + query->bindValue(":max", _DYNAMIC_PLAYLIST_MAX_COUNT_); + query->exec(); + while (query->next()) { + int id = query->value(0).toInt(); + QString title = query->value(1).toString(); + QString artist = query->value(2).toString(); + QString album = query->value(3).toString(); + QString source = query->value(4).toString(); + int count = query->value(5).toInt(); + TrackMetadata meta(title, artist, album); + Track track(id, meta, source); + track.setCount(count); + playlist.addTrack(track); + playlist.setName("Recently added"); + } + return playlist; +} + +void DbStorage::removeTrack(Track track) { + int id = track.id(); + QSqlQuery *query = new QSqlQuery(db); + query->prepare("DELETE FROM tracks WHERE id = :id;"); + query->bindValue(":id", id); + query->exec(); + query->prepare("DELETE FROM favorites WHERE track_id = :id;"); + query->bindValue(":id", id); + query->exec(); + query->prepare("DELETE FROM adding_date WHERE id = :id;"); + query->bindValue(":id", id); + query->exec(); +} + +void DbStorage::addTrack(Track track) { + QString title = track.metadata().title(); + QString artist = track.metadata().artist(); + QString album = track.metadata().album(); + QString source = track.source(); + int artist_id = _check_add_artist(artist); + int album_id = _check_add_album(album, artist_id); + if (artist_id == -1 || album_id == -1) { + //big bang + return; + } + QSqlQuery* query = _insert_track_query; + query->bindValue(":title", title); + query->bindValue(":artist_id", artist_id); + query->bindValue(":album_id", album_id); + query->bindValue(":source", source); + if (query->exec()) { + //ok + query = _check_track_query; + query->bindValue(":source", source); + query->exec(); + if (query->next()) { + int id = query->value(0).toInt(); + query = _insert_date_query; + query->bindValue(":track_id", id); + if (query->exec()) { + // ok + } else { + // big bang + } + } else { + // big bang + } + } else { + // big bang + } +} + +void DbStorage::addToFavorites(Track) { +} + +void DbStorage::updateTrack(Track) { +} + +int DbStorage::_check_add_artist(QString artist) { + QSqlQuery *query = _check_artist_query; + query->bindValue(":name", artist); + query->exec(); + if (query->next()) { + int id = query->value(0).toInt(); + return id; + } else { + query = _insert_artist_query; + query->bindValue(":name", artist); + if (query->exec()) { + return _check_add_artist(artist); + } else { + // big bang + return -1; + } + } +} + +int DbStorage::_check_add_album(QString album, int artist_id) { + QSqlQuery *query = _check_album_query; + query->bindValue(":name", album); + query->bindValue(":artist_id", artist_id); + query->exec(); + if (query->next()) { + int id = query->value(0).toInt(); + return id; + } else { + query = _insert_album_query; + query->bindValue(":name", album); + query->bindValue(":artist_id", artist_id); + if (query->exec()) { + return _check_add_album(album, artist_id); + } else { + // big bang + return -1; + } + } +} diff --git a/src/dbstorage.h b/src/dbstorage.h index 21f0572..76041d6 100644 --- a/src/dbstorage.h +++ b/src/dbstorage.h @@ -3,14 +3,67 @@ #include "someplayer.h" #include "storage.h" +#include +#include +#include "track.h" +#include "playlist.h" + +#define _DATABASE_NAME_ "/library.sqlite3" // represents database storage // it store date into some database (e.g. tracks) +using SomePlayer::DataObjects::Playlist; +using SomePlayer::DataObjects::Track; + namespace SomePlayer { namespace Storage { class DbStorage : public Storage { + public: + DbStorage(QString path); + ~DbStorage(); + QList getArtists(); + QList getAlbumsForArtist(QString artist); + QList getTracksForAlbum(QString album, QString artist); // hm... + + Playlist getFavorites(); + Playlist getMostPlayed(); + Playlist getNeverPlayed(); + Playlist getRecentlyAdded(); + + void removeTrack(Track track); + void addToFavorites(Track track); + + void updateTrack(Track track); + void addTrack(Track track); + + private: + QSqlDatabase db; + void _create_database_structure(); + void _prepare_queries(); + + int _check_add_artist(QString artist); + int _check_add_album(QString album, int artist_id); + + // queries + QSqlQuery *_get_artists_query; + QSqlQuery *_get_albums_for_artist_query; + QSqlQuery *_get_tracks_for_album_query; + QSqlQuery *_get_favorites_query; + QSqlQuery *_get_most_played_query; + QSqlQuery *_get_never_played_query; + QSqlQuery *_get_recently_added_query; + + QSqlQuery *_check_artist_query; + QSqlQuery *_check_album_query; + QSqlQuery *_check_track_query; + + QSqlQuery *_insert_artist_query; + QSqlQuery *_insert_album_query; + + QSqlQuery *_insert_track_query; + QSqlQuery *_insert_date_query; }; }; }; diff --git a/src/filestorage.cpp b/src/filestorage.cpp index 966af8c..27df605 100644 --- a/src/filestorage.cpp +++ b/src/filestorage.cpp @@ -1 +1,29 @@ #include "filestorage.h" + +using namespace SomePlayer::Storage; + +FileStorage::FileStorage(QString path) { + _path_prefix = path; +} + +QList FileStorage::getPlaylists() { + QList stub; + return stub; +} + +void FileStorage::savePlaylist(Playlist playlist) { +} + +void FileStorage::removePlaylist(Playlist playlist) { +} + +void FileStorage::removePlaylist(QString name) { +} + +Playlist FileStorage::getCurrentPlaylist() { + Playlist stub; + return stub; +} + +void FileStorage::saveCurrentPlaylist(Playlist playlist) { +} diff --git a/src/filestorage.h b/src/filestorage.h index bc9c803..144828e 100644 --- a/src/filestorage.h +++ b/src/filestorage.h @@ -3,14 +3,32 @@ #include "someplayer.h" #include "storage.h" +#include "playlist.h" + +#define _CURRENT_PLAYLIST_NAME_ "___current" +#define _PLAYLIST_FILE_EXTENSION_ ".m3u" // represents file-level storage // it store data into separate files (e.g. playlist) +using SomePlayer::DataObjects::Playlist; + namespace SomePlayer { namespace Storage { class FileStorage : public Storage { + public: + FileStorage(QString path); + + QList getPlaylists(); + void savePlaylist(Playlist playlist); + void removePlaylist(Playlist playlist); + void removePlaylist(QString name); + + Playlist getCurrentPlaylist(); + void saveCurrentPlaylist(Playlist playlist); + private: + QString _path_prefix; }; }; diff --git a/src/library.cpp b/src/library.cpp index 6d25561..bfd8a1d 100644 --- a/src/library.cpp +++ b/src/library.cpp @@ -1 +1,90 @@ #include "library.h" + +using namespace SomePlayer::DataObjects; +using namespace SomePlayer::Storage; + +Library::Library(QString databasePath, QString playlistsPath) { + _library_storage = new DbStorage(databasePath); + _playlist_storage = new FileStorage(playlistsPath); +} + +Library::~Library() { + delete _library_storage; + delete _playlist_storage; +} + +void Library::addDirectory(QString path) { + /// TODO: implement this +} + +void Library::addFile(QString path) { + /// TODO: implement this +} + +QList Library::getArtists() { + return _library_storage->getArtists(); +} + +QList Library::getAlbumsForArtist(QString artist) { + return _library_storage->getAlbumsForArtist(artist); +} + +QList Library::getTracksForAlbum(QString album, QString artist) { + return _library_storage->getTracksForAlbum(album, artist); +} + + +// dynamic: + +Playlist Library::getFavorites() { + return _library_storage->getFavorites(); +} + +Playlist Library::getMostPlayed() { + return _library_storage->getMostPlayed(); +} + +Playlist Library::getNeverPlayed() { + return _library_storage->getNeverPlayed(); +} + +Playlist Library::getRecentlyAdded() { + return _library_storage->getRecentlyAdded(); +} + + +void Library::removeTrack(Track track) { + _library_storage->removeTrack(track); +} + +void Library::addToFavorites(Track track) { + _library_storage->addToFavorites(track); +} + +void Library::updateTrack(Track track) { + _library_storage->updateTrack(track); +} + + +// playlists: + +QList Library::getPlaylists() { + return _playlist_storage->getPlaylists(); +} + +void Library::savePlaylist(Playlist playlist) { + _playlist_storage->savePlaylist(playlist); +} + +void Library::removePlaylist(Playlist playlist) { + _playlist_storage->removePlaylist(playlist); +} + + +Playlist Library::getCurrentPlaylist() { + return _playlist_storage->getCurrentPlaylist(); +} + +void Library::saveCurrentPlaylist(Playlist playlist) { + _playlist_storage->saveCurrentPlaylist(playlist); +} diff --git a/src/library.h b/src/library.h index b98aabe..1a4ecc7 100644 --- a/src/library.h +++ b/src/library.h @@ -2,15 +2,56 @@ #define LIBRARY #include "someplayer.h" +#include "track.h" +#include "playlist.h" +#include "dbstorage.h" +#include "filestorage.h" // represents media library: tracks, playlists // it uses different media storages for tracks and playlists -// but dinamyc playlits will be stored with tracks into the same storage +// but dynamic playlits will be stored with tracks into the same storage + +using SomePlayer::DataObjects::Track; +using SomePlayer::DataObjects::Playlist; +using SomePlayer::Storage::DbStorage; +using SomePlayer::Storage::FileStorage; namespace SomePlayer { namespace DataObjects { class Library { + public: + Library(QString databasePath, QString playlistsPath); + ~Library(); + + void addDirectory(QString path); + void addFile(QString path); + + QList getArtists(); + QList getAlbumsForArtist(QString artist); + QList getTracksForAlbum(QString album, QString artist); + + Playlist getFavorites(); + Playlist getMostPlayed(); + Playlist getNeverPlayed(); + Playlist getRecentlyAdded(); + + void removeTrack(Track); + void addTrack(Track); + void addToFavorites(Track); + + void updateTrack(Track); + + QList getPlaylists(); + void savePlaylist(Playlist playlist); + void removePlaylist(Playlist playlist); + + Playlist getCurrentPlaylist(); + void saveCurrentPlaylist(Playlist playlist); + + private: + DbStorage *_library_storage; + FileStorage *_playlist_storage; }; }; diff --git a/src/main.cpp b/src/main.cpp index 68544fe..35ab0cb 100644 --- a/src/main.cpp +++ b/src/main.cpp @@ -6,6 +6,7 @@ int main(int argc, char *argv[]) QApplication a(argc, argv); a.setApplicationName("someplayer"); MainWindow w; + #if defined(Q_WS_S60) w.showMaximized(); #else diff --git a/src/playlist.h b/src/playlist.h index 6898eae..1e7b7fc 100644 --- a/src/playlist.h +++ b/src/playlist.h @@ -1,8 +1,6 @@ #ifndef PLAYLIST #define PLAYLIST -#include -#include #include "track.h" #include "someplayer.h" diff --git a/src/someplayer.h b/src/someplayer.h index 3213fea..5fb43f6 100644 --- a/src/someplayer.h +++ b/src/someplayer.h @@ -10,4 +10,13 @@ namespace SomePlayer { }; }; +// common includes + +#include +#include +#include +#include + +#define _DYNAMIC_PLAYLIST_MAX_COUNT_ 50 + #endif diff --git a/src/track.cpp b/src/track.cpp index 728056c..4dcd9b9 100644 --- a/src/track.cpp +++ b/src/track.cpp @@ -5,19 +5,37 @@ using namespace SomePlayer::DataObjects; Track::Track() { } -Track::Track(TrackMetadata metadata, QUrl source) { +Track::Track(int id, TrackMetadata metadata, QString source) { + _id = id; _metadata = metadata; _source = source; } -TrackMetadata Track::metadata() { +Track::Track(const Track &track) { + this->_metadata = track.metadata(); + this->_source = track.source(); +} + +TrackMetadata Track::metadata() const { return _metadata; } -QUrl Track::source() const { +QString Track::source() const { return _source; } -void Track::setSource(QUrl source) { +int Track::id() const { + return _id; +} + +void Track::setSource(QString source) { _source = source; } + +int Track::count() const{ + return _count; +} + +void Track::setCount(int count) { + _count = count; +} diff --git a/src/track.h b/src/track.h index 36c2278..2ee621f 100644 --- a/src/track.h +++ b/src/track.h @@ -3,7 +3,6 @@ #include "someplayer.h" #include "trackmetainformation.h" -#include // represents some track: metainformation + source url @@ -14,14 +13,20 @@ namespace SomePlayer { { public: Track(); - Track(TrackMetadata metadata, QUrl source); - TrackMetadata metadata(); //read-write - QUrl source() const; - void setSource (QUrl source); + Track(const Track &track); + Track(int id, TrackMetadata metadata, QString source); + TrackMetadata metadata() const; //read-write + QString source() const; + int id() const; + void setSource (QString source); + int count() const; //count of plays + void setCount(int count); //for restoring from database and counting from player private: TrackMetadata _metadata; - QUrl _source; + QString _source; + int _count; + int _id; }; }; diff --git a/src/trackmetainformation.cpp b/src/trackmetainformation.cpp index 0ef9e5c..e88f7ae 100644 --- a/src/trackmetainformation.cpp +++ b/src/trackmetainformation.cpp @@ -5,11 +5,14 @@ using namespace SomePlayer::DataObjects; TrackMetadata::TrackMetadata() { } -TrackMetadata::TrackMetadata(QString title = "", QString artist = "", QString album = "", QString genre = "") { +TrackMetadata::TrackMetadata(QString title = "", QString artist = "", QString album = "") { _metadata["TITLE"] = title; _metadata["ARTIST"] = artist; _metadata["ALBUM"] = album; - _metadata["GENRE"] = genre; +} + +TrackMetadata::TrackMetadata(const TrackMetadata &metadata) { + this->_metadata = metadata._metadata; } QString TrackMetadata::title() { @@ -36,14 +39,6 @@ QString TrackMetadata::album() { } } -QString TrackMetadata::genre() { - if (_metadata.contains("GENRE")) { - return _metadata["GENRE"]; - } else { - return "Unknown genre"; - } -} - void TrackMetadata::setTitle(QString title) { _metadata["TITLE"] = title; } @@ -55,7 +50,3 @@ void TrackMetadata::setArtist(QString artist) { void TrackMetadata::setAlbum(QString album) { _metadata["ALBUM"] = album; } - -void TrackMetadata::setGenre(QString genre) { - _metadata["GENRE"] = genre; -} diff --git a/src/trackmetainformation.h b/src/trackmetainformation.h index 2010fa4..2496d19 100644 --- a/src/trackmetainformation.h +++ b/src/trackmetainformation.h @@ -1,8 +1,6 @@ #ifndef TRACK_METAINFORMATION #define TRACK_METAINFORMATION -#include -#include #include "someplayer.h" // represents track metainformation only. @@ -14,17 +12,16 @@ namespace SomePlayer { public: TrackMetadata(); - TrackMetadata(QString title, QString artist, QString album, QString genre); + TrackMetadata(QString title, QString artist, QString album); + TrackMetadata(const TrackMetadata &metadata); QString title(); QString artist(); QString album(); - QString genre(); void setTitle(QString title); void setArtist(QString artist); void setAlbum(QString album); - void setGenre(QString genre); private: QMap _metadata;