#include <QSqlQuery>
#include <QSqlResult>
#include <QDebug>
+#include <QFileInfo>
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()) {
"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)");
_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'))");
_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");
_remove_track_query = new QSqlQuery(db);
_remove_track_query->prepare("DELETE FROM tracks WHERE id = :id");
+
+ _remove_empty_albums_query = new QSqlQuery(db);
+ _remove_empty_albums_query->prepare("DELETE FROM album WHERE album.id IN "
+ "(SELECT id FROM "
+ "(SELECT COUNT(tracks.id) AS cnt, album.id FROM "
+ "album LEFT OUTER JOIN tracks ON album.id = tracks.album_id "
+ "GROUP BY album.id) WHERE cnt = 0)");
+
+ _remove_empty_artists_query = new QSqlQuery(db);
+ _remove_empty_artists_query->prepare("DELETE FROM artist WHERE artist.id IN "
+ "(SELECT id FROM "
+ "(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() {
"title text, "
"utitle text, "
"source text, "
+ "directory integer, "
"count integer default 0, "
"length integer default 0, "
"foreign key(artist_id) references artist(id), "
"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() {
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();
}
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);
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
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) {
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()) {
query->exec();
if (query->next()) { // found track in library
int id = query->value(0).toInt();
- qWarning() << "found " << id;
query = _remove_track_query;
query->bindValue(":id", id);
query->exec();
addTrack(track);
+ _cleanup();
}
return track;
}
}
}
-QList<Track> DbStorage::search(QString pattern) {
+QList<Track> DbStorage::searchTracks(QString pattern) {
QList <Track> 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()) {
}
return found;
}
+
+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<QString> DbStorage::getDirectories() {
+ QSqlQuery *query = _get_directories_query;
+ query->exec();
+ QList<QString> 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;
+}
projects / someplayer / blobdiff