X-Git-Url: http://git.maemo.org/git/?a=blobdiff_plain;f=src%2Fdbstorage.cpp;h=03755dd263551f117440ce5e6f5abe0480bdd973;hb=2a1cf9a16d15506f9693338a8cd1d174e5e98805;hp=078c21f976515c330c317521186b62d1f2a22175;hpb=00e08012d5b22a9d7fe06cab84eb8e96db2a1f3d;p=someplayer diff --git a/src/dbstorage.cpp b/src/dbstorage.cpp index 078c21f..03755dd 100644 --- a/src/dbstorage.cpp +++ b/src/dbstorage.cpp @@ -21,12 +21,13 @@ #include #include #include +#include using namespace SomePlayer::Storage; using namespace SomePlayer::DataObjects; DbStorage::DbStorage(QString path) { - QString dbname = path+_DATABASE_NAME_; + QString dbname = path + _DATABASE_NAME_; db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName(dbname); if (!db.open()) { @@ -81,26 +82,45 @@ void DbStorage::_prepare_queries() { "WHERE track_id IN " "(SELECT track_id FROM adding_date ORDER BY date DESC LIMIT 0, :max)"); - _get_track_count = new QSqlQuery(db); - _get_track_count->prepare("SELECT count from tracks WHERE id = :id"); + _get_track_count_query = new QSqlQuery(db); + _get_track_count_query->prepare("SELECT count from tracks WHERE id = :id"); _get_tracks_by_pattern_query = new QSqlQuery(db); _get_tracks_by_pattern_query->prepare("SELECT id, title, artist, album, source, count, length, year FROM " "entire WHERE " - "utitle LIKE (SELECT '%' || :ptitle || '%') OR " - "album_uname LIKE (SELECT '%' || :palbum || '%') OR " - "artist_uname LIKE (SELECT '%' || :partist || '%') " + "utitle LIKE (SELECT '%' || :ptitle || '%') " "ORDER BY artist_uname, year"); _get_track_id_by_source_query = new QSqlQuery(db); _get_track_id_by_source_query->prepare("SELECT id FROM tracks WHERE source = :source"); + _get_directories_query = new QSqlQuery(db); + _get_directories_query->prepare("SELECT id, path FROM directories"); + + _get_artists_count_query = new QSqlQuery(db); + _get_artists_count_query->prepare("SELECT COUNT(id) AS cnt FROM artist"); + + _get_albums_count_query = new QSqlQuery(db); + _get_albums_count_query->prepare("SELECT COUNT(id) AS cnt FROM album"); + + _get_tracks_count_query = new QSqlQuery(db); + _get_tracks_count_query->prepare("SELECT COUNT(id) AS cnt FROM tracks"); + + _get_tracks_source_from_query = new QSqlQuery(db); + _get_tracks_source_from_query->prepare("SELECT source FROM tracks WHERE directory = :directory_id"); + _check_artist_query = new QSqlQuery(db); _check_artist_query->prepare("SELECT id FROM artist WHERE uname = :uname"); _check_album_query = new QSqlQuery(db); _check_album_query->prepare("SELECT id FROM album WHERE uname = :uname AND artist_id = :artist_id"); + _check_directory_query = new QSqlQuery(db); + _check_directory_query->prepare("SELECT id from directories WHERE path = :path"); + + _check_favorite_query = new QSqlQuery(db); + _check_favorite_query->prepare("SELECT 'yes' from favorites WHERE track_id in (SELECT id from tracks WHERE source = :source)"); + _insert_artist_query = new QSqlQuery(db); _insert_artist_query->prepare("INSERT INTO artist (name, uname) values (:name, :uname)"); @@ -108,7 +128,7 @@ void DbStorage::_prepare_queries() { _insert_album_query->prepare("INSERT INTO album (name, uname, artist_id, year) values (:name, :uname, :artist_id, :year)"); _insert_track_query = new QSqlQuery(db); - _insert_track_query->prepare("INSERT INTO tracks (title, utitle, artist_id, album_id, source, length) values (:title, :utitle, :artist_id, :album_id, :source, :length)"); + _insert_track_query->prepare("INSERT INTO tracks (title, utitle, artist_id, album_id, source, directory, length) values (:title, :utitle, :artist_id, :album_id, :source, :directory_id, :length)"); _insert_date_query = new QSqlQuery(db); _insert_date_query->prepare("INSERT INTO adding_date (track_id, date) values (:track_id, strftime('%s', 'now'))"); @@ -116,6 +136,9 @@ void DbStorage::_prepare_queries() { _insert_favorites_query = new QSqlQuery(db); _insert_favorites_query->prepare("INSERT INTO favorites (track_id) values (:track_id)"); + _insert_directory_query = new QSqlQuery(db); + _insert_directory_query->prepare("INSERT INTO directories (path) values (:path)"); + _update_track_count_query = new QSqlQuery(db); _update_track_count_query->prepare("UPDATE tracks SET count = :count where id = :id"); @@ -135,6 +158,15 @@ void DbStorage::_prepare_queries() { "(SELECT COUNT(tracks.id) AS cnt, artist.id FROM " "artist LEFT OUTER JOIN tracks ON artist.id = tracks.artist_id " "GROUP BY artist.id) WHERE cnt = 0)"); + + _remove_tracks_from_query = new QSqlQuery(db); + _remove_tracks_from_query->prepare("DELETE FROM tracks WHERE directory = :directory_id"); + + _remove_directory_query = new QSqlQuery(db); + _remove_directory_query->prepare("DELETE FROM directories WHERE path = :path"); + + _remove_track_from_favorites_query = new QSqlQuery(db); + _remove_track_from_favorites_query->prepare("DELETE FROM favorites WHERE track_id in (SELECT id FROM tracks WHERE source = :source)"); } void DbStorage::_create_database_structure() { @@ -156,6 +188,7 @@ void DbStorage::_create_database_structure() { "title text, " "utitle text, " "source text, " + "directory integer, " "count integer default 0, " "length integer default 0, " "foreign key(artist_id) references artist(id), " @@ -183,6 +216,8 @@ void DbStorage::_create_database_structure() { "on album.artist_id = artist.id) " "on aartist_id = tracks.artist_id " "and aalbum_id = tracks.album_id"); + + query->exec("create table directories (id integer primary key, path text)"); } DbStorage::~DbStorage() { @@ -195,16 +230,26 @@ DbStorage::~DbStorage() { delete _get_recently_added_query; delete _get_tracks_for_album_query; delete _get_tracks_by_pattern_query; + delete _get_directories_query; + delete _get_artists_count_query; + delete _get_albums_count_query; + delete _get_tracks_count_query; + delete _get_tracks_source_from_query; delete _check_album_query; delete _check_artist_query; + delete _check_directory_query; delete _get_track_id_by_source_query; delete _insert_album_query; delete _insert_artist_query; delete _insert_date_query; delete _insert_track_query; delete _insert_favorites_query; + delete _insert_directory_query; delete _update_track_count_query; delete _remove_track_query; + delete _remove_directory_query; + delete _remove_tracks_from_query; + delete _remove_track_from_favorites_query; db.close(); } @@ -366,6 +411,8 @@ void DbStorage::addTrack(Track track) { QString artist = track.metadata().artist(); QString album = track.metadata().album(); QString source = track.source(); + QFileInfo info(source); + QString path = info.canonicalPath(); int year = track.metadata().year(); int artist_id = _check_add_artist(artist); int album_id = _check_add_album(album, artist_id, year); @@ -386,6 +433,7 @@ void DbStorage::addTrack(Track track) { query->bindValue(":artist_id", artist_id); query->bindValue(":album_id", album_id); query->bindValue(":source", source); + query->bindValue(":directory", _check_add_directory(path)); query->bindValue(":length", track.metadata().length()); if (query->exec()) { //ok @@ -413,12 +461,15 @@ void DbStorage::addToFavorites(Track track) { QSqlQuery *query = _get_track_id_by_source_query; query->bindValue(":source", track.source()); query->exec(); - if (query->next()) { - int id = query->value(0).toInt(); - query = _insert_favorites_query; - query->bindValue(":track_id", id); + if (!query->next()) { + addTrack(track); query->exec(); + query->next(); } + int id = query->value(0).toInt(); + query = _insert_favorites_query; + query->bindValue(":track_id", id); + query->exec(); } void DbStorage::updateTrackCount(Track track) { @@ -427,7 +478,7 @@ void DbStorage::updateTrackCount(Track track) { query->exec(); if (query->next()) { int id = query->value(0).toInt(); - query = _get_track_count; + query = _get_track_count_query; query->bindValue(":id", id); query->exec(); if (query->next()) { @@ -498,12 +549,10 @@ int DbStorage::_check_add_album(QString album, int artist_id, int year) { } } -QList DbStorage::search(QString pattern) { +QList DbStorage::searchTracks(QString pattern) { QList found; QSqlQuery *query = _get_tracks_by_pattern_query; query->bindValue(":ptitle", pattern.toUpper()); // with :pattern only doesn't work - query->bindValue(":palbum", pattern.toUpper()); - query->bindValue(":partist", pattern.toUpper()); query->exec(); // id, title, artist, album, source, count, length, year while (query->next()) { @@ -527,3 +576,107 @@ void DbStorage::_cleanup() { _remove_empty_albums_query->exec(); _remove_empty_artists_query->exec(); } + +int DbStorage::_check_add_directory(QString path) { + QSqlQuery *query = _check_directory_query; + query->bindValue(":path", path); + query->exec(); + if (query->next()) { + return query->value(0).toInt(); + } else { + query = _insert_directory_query; + query->bindValue(":path", path); + query->exec(); + return _check_add_directory(path); + } +} + +QList DbStorage::getDirectories() { + QSqlQuery *query = _get_directories_query; + query->exec(); + QList directories; + while (query->next()) { + directories.append(query->value(1).toString()); + } + return directories; +} + +int DbStorage::getArtistsCount() { + QSqlQuery *query = _get_artists_count_query; + query->exec(); + if (query->next()) { + return query->value(0).toInt(); + } + return 0; +} + +int DbStorage::getAlbumsCount() { + QSqlQuery *query = _get_albums_count_query; + query->exec(); + if (query->next()) { + return query->value(0).toInt(); + } + return 0; +} + +int DbStorage::getTracksCount() { + QSqlQuery *query = _get_tracks_count_query; + query->exec(); + if (query->next()) { + return query->value(0).toInt(); + } + return 0; +} + +void DbStorage::deleteTracksFrom(QString path) { + QSqlQuery *query = _check_directory_query; + query->bindValue(":path", path); + query->exec(); + if (query->next()) { + int id = query->value(0).toInt(); + query = _remove_tracks_from_query; + query->bindValue(":directory_id", id); + query->exec(); + query = _remove_directory_query; + query->bindValue(":path", path); + query->exec(); + _cleanup(); + } +} + +void DbStorage::checkTracksFrom(QString path) { + QSqlQuery *query = _check_directory_query; + query->bindValue(":path", path); + query->exec(); + if (query->next()) { + int id = query->value(0).toInt(); + query = _get_tracks_source_from_query; + query->bindValue(":directory_id", id); + query->exec(); + while (query->next()) { + QString source = query->value(0).toString(); + if (!QFile::exists(source)) { + Track track(TrackMetadata(), source); // removeTrack uses only source field, so + removeTrack(track); // we can use this method + } + } + _cleanup(); + } +} + +void DbStorage::removeFromFavorites(Track track) { + QSqlQuery *query = _remove_track_from_favorites_query; + query->bindValue(":source", track.source()); + query->exec(); +} + +bool DbStorage::isFavorite(Track track) { + QSqlQuery *query = _check_favorite_query; + query->bindValue(":source", track.source()); + query->exec(); + if (query->next()) { + QString ans = query->value(0).toString(); + return ans == "yes"; + } + return false; +}