src/db/collectionDB.cpp

0001 /*
0002    Babe - tiny music player
0003    Copyright (C) 2017  Camilo Higuita
0004    This program is free software; you can redistribute it and/or modify
0005    it under the terms of the GNU General Public License as published by
0006    the Free Software Foundation; either version 3 of the License, or
0007    (at your option) any later version.
0008    This program is distributed in the hope that it will be useful,
0009    but WITHOUT ANY WARRANTY; without even the implied warranty of
0010    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
0011    GNU General Public License for more details.
0012    You should have received a copy of the GNU General Public License
0013    along with this program; if not, write to the Free Software Foundation,
0014    Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301  USA
0015
0016    */
0017
0018 #include "collectionDB.h"
0019
0020 #include <QString>
0021 #include <QStringList>
0022 #include <QUuid>
0023 #include <QDateTime>
0024
0025 #include <MauiKit3/Core/fmh.h>
0026 #include <MauiKit3/FileBrowsing/fmstatic.h>
0027
0028 using namespace BAE;
0029
0030 CollectionDB::CollectionDB(QObject *parent)
0031     : QObject(parent)
0032 {
0033     this->name = QUuid::createUuid().toString();
0034
0035     if (!FMH::fileExists(QUrl::fromUserInput(BAE::CollectionDBPath + BAE::DBName))) {
0036         QDir collectionDBPath_dir(BAE::CollectionDBPath);
0037         if (!collectionDBPath_dir.exists())
0038             collectionDBPath_dir.mkpath(".");
0039
0040         this->openDB(this->name);
0041         qDebug() << "Collection doesn't exists, trying to create it" << BAE::CollectionDBPath + BAE::DBName;
0042         this->prepareCollectionDB();
0043
0044     } else
0045         this->openDB(this->name);
0046 }
0047
0048 void CollectionDB::prepareCollectionDB()
0049 {
0050     QSqlQuery query(this->m_db);
0051
0052     QFile file(":/db/script.sql");
0053     qDebug() << file.exists();
0054
0055     if (!file.exists()) {
0056         QString log = QStringLiteral("Fatal error on build database. The file '");
0057         log.append(file.fileName() + QStringLiteral("' for database and tables creation query cannot be not found!"));
0058         qDebug() << log;
0059         return;
0060     }
0061
0062     if (!file.open(QIODevice::ReadOnly)) {
0063         qDebug() << QStringLiteral("Fatal error on try to create database! The file with sql queries for database creation cannot be opened!");
0064         return;
0065     }
0066
0067     bool hasText;
0068     QString line;
0069     QByteArray readLine;
0070     QString cleanedLine;
0071     QStringList strings;
0072
0073     while (!file.atEnd()) {
0074         hasText = false;
0075         line = "";
0076         readLine = "";
0077         cleanedLine = "";
0078         strings.clear();
0079         while (!hasText) {
0080             readLine = file.readLine();
0081             cleanedLine = readLine.trimmed();
0082             strings = cleanedLine.split("--");
0083             cleanedLine = strings.at(0);
0084             if (!cleanedLine.startsWith("--") && !cleanedLine.startsWith("DROP") && !cleanedLine.isEmpty())
0085                 line += cleanedLine;
0086             if (cleanedLine.endsWith(";"))
0087                 break;
0088             if (cleanedLine.startsWith("COMMIT"))
0089                 hasText = true;
0090         }
0091         if (!line.isEmpty()) {
0092             if (!query.exec(line)) {
0093                 qDebug() << "exec failed" << query.lastQuery() << query.lastError();
0094             }
0095
0096         } else
0097             qDebug() << "exec wrong" << query.lastError();
0098     }
0099     file.close();
0100 }
0101
0102 bool CollectionDB::check_existance(const QString &tableName, const QString &searchId, const QString &search)
0103 {
0104     auto queryStr = QString("SELECT %1 FROM %2 WHERE %3 = \"%4\"").arg(searchId, tableName, searchId, search);
0105     auto query = this->getQuery(queryStr);
0106
0107     if (!query.exec()) {
0108         qDebug() << query.lastError().text();
0109         return false;
0110     }
0111
0112     if (query.first())
0113         return true;
0114
0115     return false;
0116 }
0117
0118 bool CollectionDB::insert(const QString &tableName, const QVariantMap &insertData)
0119 {
0120     if (tableName.isEmpty()) {
0121         qDebug() << QStringLiteral("Fatal error on insert! The table name is empty!");
0122         return false;
0123
0124     } else if (insertData.isEmpty()) {
0125         qDebug() << QStringLiteral("Fatal error on insert! The insertData is empty!");
0126         return false;
0127     }
0128
0129     QStringList strValues;
0130     QStringList fields = insertData.keys();
0131     QVariantList values = insertData.values();
0132     int totalFields = fields.size();
0133     for (int i = 0; i < totalFields; ++i)
0134         strValues.append("?");
0135
0136     QString sqlQueryString = "INSERT INTO " + tableName + " (" + QString(fields.join(",")) + ") VALUES(" + QString(strValues.join(",")) + ")";
0137     QSqlQuery query(this->m_db);
0138     query.prepare(sqlQueryString);
0139
0140     int k = 0;
0141     foreach (const QVariant &value, values)
0142         query.bindValue(k++, value);
0143
0144     return query.exec();
0145 }
0146
0147 bool CollectionDB::update(const QString &tableName, const FMH::MODEL &updateData, const QVariantMap &where)
0148 {
0149     if (tableName.isEmpty()) {
0150         qDebug() << QStringLiteral("Fatal error on insert! The table name is empty!");
0151         return false;
0152     } else if (updateData.isEmpty()) {
0153         qDebug() << QStringLiteral("Fatal error on insert! The insertData is empty!");
0154         return false;
0155     }
0156
0157     QStringList set;
0158     const auto updateKeys = updateData.keys();
0159     for (const auto &key : updateKeys)
0160     {
0161         set.append(QString("%1 = \"%2\"").arg(FMH::MODEL_NAME[key], updateData[key]));
0162     }
0163
0164     QStringList condition;
0165     const auto whereKeys = where.keys();
0166     for (const auto &key : whereKeys)
0167     {
0168         condition.append(QString("%1 = \"%2\"").arg(key, where[key].toString()));
0169     }
0170
0171     const QString sqlQueryString = "UPDATE " + tableName + " SET " + QString(set.join(",")) + " WHERE " + QString(condition.join(" AND "));
0172
0173     auto query = this->getQuery(sqlQueryString);
0174
0175     qDebug() << sqlQueryString;
0176     return this->execQuery(query);
0177 }
0178
0179 bool CollectionDB::update(const QString &table, const QString &column, const QVariant &newValue, const QVariant &op, const QString &id)
0180 {
0181     const auto queryStr = QString("UPDATE %1 SET %2 = \"%3\" WHERE %4 = \"%5\"").arg(table, column, newValue.toString().replace("\"", "\"\""), op.toString(), id);
0182     auto query = this->getQuery(queryStr);
0183     return query.exec();
0184 }
0185
0186 bool CollectionDB::remove(const QString &table, const QString &column, const QVariantMap &where)
0187 {
0188     Q_UNUSED(table)
0189     Q_UNUSED(column)
0190     Q_UNUSED(where)
0191
0192     return false;
0193 }
0194
0195 bool CollectionDB::execQuery(QSqlQuery &query) const
0196 {
0197     if (query.exec())
0198         return true;
0199     qDebug() << "ERROR ON EXEC QUERY";
0200     qDebug() << query.lastError() << query.lastQuery();
0201     return false;
0202 }
0203
0204 bool CollectionDB::execQuery(const QString &queryTxt)
0205 {
0206     auto query = this->getQuery(queryTxt);
0207     return this->execQuery(query);
0208 }
0209
0210 void CollectionDB::openDB(const QString &name)
0211 {
0212     if (!QSqlDatabase::contains(name)) {
0213         this->m_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), name);
0214         this->m_db.setDatabaseName(BAE::CollectionDBPath + BAE::DBName);
0215     }
0216
0217     if (!this->m_db.isOpen()) {
0218         if (!this->m_db.open())
0219             qDebug() << "ERROR OPENING DB" << this->m_db.lastError().text() << m_db.connectionName();
0220         else {
0221             qDebug() << "Setting pragma WAL";
0222             this->execQuery("PRAGMA journal_mode=WAL");
0223         }
0224     }
0225 }
0226
0227 bool CollectionDB::addTrack(const FMH::MODEL &track)
0228 {
0229     if (track.isEmpty())
0230         return false;
0231
0232     const auto url = track[FMH::MODEL_KEY::URL];
0233     if (check_existance(TABLEMAP[BAE::TABLE::TRACKS], BAE::KEYMAP[BAE::KEY::URL], url)) {
0234         return false;
0235     }
0236
0237     const auto title = track[FMH::MODEL_KEY::TITLE];
0238     const auto artist = track[FMH::MODEL_KEY::ARTIST];
0239     const auto album = track[FMH::MODEL_KEY::ALBUM];
0240     const auto genre = track[FMH::MODEL_KEY::GENRE];
0241     const auto year = track[FMH::MODEL_KEY::RELEASEDATE];
0242     const auto sourceUrl = track[FMH::MODEL_KEY::SOURCE];
0243     const auto duration = track[FMH::MODEL_KEY::DURATION];
0244     const auto trackNumber = track[FMH::MODEL_KEY::TRACK];
0245     const auto comment = track[FMH::MODEL_KEY::COMMENT];
0246
0247     /* first needs to insert the source, album and artist*/
0248     const QVariantMap sourceMap{{FMH::MODEL_NAME[FMH::MODEL_KEY::URL], sourceUrl}, {FMH::MODEL_NAME[FMH::MODEL_KEY::SOURCETYPE], sourceType(url)}};
0249
0250     if (insert(TABLEMAP[BAE::TABLE::SOURCES], sourceMap))
0251         Q_EMIT sourceInserted(sourceMap);
0252
0253     const QVariantMap artistMap{{FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist}, {FMH::MODEL_NAME[FMH::MODEL_KEY::WIKI], ""}};
0254
0255     if (insert(TABLEMAP[TABLE::ARTISTS], artistMap))
0256         Q_EMIT artistInserted(artistMap);
0257
0258     const QVariantMap albumMap{{FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], album}, {FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist}, {FMH::MODEL_NAME[FMH::MODEL_KEY::WIKI], ""}};
0259
0260     if (insert(TABLEMAP[TABLE::ALBUMS], albumMap))
0261         Q_EMIT albumInserted(albumMap);
0262
0263     const QVariantMap trackMap{{FMH::MODEL_NAME[FMH::MODEL_KEY::URL], url},
0264                                {FMH::MODEL_NAME[FMH::MODEL_KEY::SOURCE], sourceUrl},
0265                                {FMH::MODEL_NAME[FMH::MODEL_KEY::TRACK], trackNumber},
0266                                {FMH::MODEL_NAME[FMH::MODEL_KEY::TITLE], title},
0267                                {FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist},
0268                                {FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], album},
0269                                {FMH::MODEL_NAME[FMH::MODEL_KEY::DURATION], duration},
0270                                {FMH::MODEL_NAME[FMH::MODEL_KEY::COUNT], 0},
0271                                {FMH::MODEL_NAME[FMH::MODEL_KEY::RATE], 0},
0272                                {FMH::MODEL_NAME[FMH::MODEL_KEY::RELEASEDATE], year},
0273                                {FMH::MODEL_NAME[FMH::MODEL_KEY::ADDDATE], QDateTime::currentDateTime()},
0274                                {FMH::MODEL_NAME[FMH::MODEL_KEY::LYRICS], ""},
0275                                {FMH::MODEL_NAME[FMH::MODEL_KEY::GENRE], genre},
0276                                {FMH::MODEL_NAME[FMH::MODEL_KEY::WIKI], ""},
0277                                {FMH::MODEL_NAME[FMH::MODEL_KEY::COMMENT], comment}};
0278
0279     if (this->insert(BAE::TABLEMAP[BAE::TABLE::TRACKS], trackMap)) {
0280         qDebug() << "TrackInserted!!!!!!" << trackMap;
0281         Q_EMIT trackInserted(trackMap);
0282         return true;
0283     }
0284
0285     return false;
0286 }
0287
0288 bool CollectionDB::updateTrack(const FMH::MODEL &track)
0289 {
0290     if (this->check_existance(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::URL], track[FMH::MODEL_KEY::URL])) {
0291         QVariantMap artistMap{{FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], track[FMH::MODEL_KEY::ARTIST]}};
0292
0293         insert(TABLEMAP[TABLE::ARTISTS], artistMap);
0294
0295         QVariantMap albumMap{{FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], track[FMH::MODEL_KEY::ALBUM]}, {FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], track[FMH::MODEL_KEY::ARTIST]}};
0296
0297         insert(TABLEMAP[TABLE::ALBUMS], albumMap);
0298
0299         QVariantMap condition{{FMH::MODEL_NAME[FMH::MODEL_KEY::URL], track[FMH::MODEL_KEY::URL]}};
0300
0301         if (this->update(TABLEMAP[TABLE::TRACKS], track, condition))
0302         {
0303             if (cleanAlbums())
0304             {
0305                 cleanArtists();
0306             }
0307         }
0308
0309         return true;
0310     }
0311
0312     return false;
0313 }
0314
0315 bool CollectionDB::rateTrack(const QString &path, const int &value)
0316 {
0317     if (update(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::RATE], value, FMH::MODEL_NAME[FMH::MODEL_KEY::URL], path))
0318         return true;
0319     return false;
0320 }
0321
0322 bool CollectionDB::lyricsTrack(const FMH::MODEL &track, const QString &value)
0323 {
0324     if (update(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::LYRICS], value, FMH::MODEL_NAME[FMH::MODEL_KEY::URL], track[FMH::MODEL_KEY::URL]))
0325     {
0326         return true;
0327     }
0328     return false;
0329 }
0330
0331 bool CollectionDB::albumTrack(const FMH::MODEL &track, const QString &value)
0332 {
0333     auto album = track[FMH::MODEL_KEY::ALBUM];
0334     auto artist = track[FMH::MODEL_KEY::ARTIST];
0335     //    auto url = track[FMH::MODEL_KEY::URL];
0336
0337     auto queryTxt = QString("SELECT * %1 WHERE %2 = %3 AND %4 = %5").arg(TABLEMAP[TABLE::ALBUMS], FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], album, FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist);
0338
0339     auto result = this->getDBData(queryTxt);
0340     if (result.isEmpty())
0341         return false;
0342
0343     auto oldAlbum = result.first();
0344     QVariantMap albumMap{{FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], value}, {FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], oldAlbum[FMH::MODEL_KEY::ARTIST]}, {FMH::MODEL_NAME[FMH::MODEL_KEY::WIKI], oldAlbum[FMH::MODEL_KEY::WIKI]}};
0345
0346     if (!insert(TABLEMAP[TABLE::ALBUMS], albumMap))
0347         return false;
0348
0349     // update albums SET album = "newalbumname" WHERE album = "albumname" NAD artist = "aretist name";
0350     queryTxt = QString("UPDATE %1 SET %2 = %3 AND %4 = %5 WHERE %2 = %6 AND %4 = %5")
0351             .arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], value, FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], oldAlbum[FMH::MODEL_KEY::ARTIST], oldAlbum[FMH::MODEL_KEY::ALBUM]);
0352     auto query = this->getQuery(queryTxt);
0353
0354     if (!execQuery(query))
0355         return false;
0356
0357     queryTxt = QString("DELETE FROM %1 WHERE %2 = %3 AND %4 = %5").arg(TABLEMAP[TABLE::ALBUMS], FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], oldAlbum[FMH::MODEL_KEY::ALBUM], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist);
0358     query.prepare(queryTxt);
0359
0360     if (!execQuery(query))
0361         return false;
0362
0363     return true;
0364 }
0365
0366 bool CollectionDB::playedTrack(const QString &url)
0367 {
0368     auto query = getQuery();
0369     query.prepare("UPDATE TRACKS SET count = count + 1 WHERE url = :url");
0370     query.bindValue(":url", url);
0371
0372     if (query.exec())
0373     {
0374 //        query.clar();
0375         auto query2 = getQuery();
0376
0377         query2.prepare("UPDATE TRACKS SET lastsync = :date WHERE url = :url");
0378         query2.bindValue(":date", QDateTime::currentDateTime());
0379         query2.bindValue(":url", url);
0380
0381         query2.exec();
0382         return true;
0383     }
0384
0385     return false;
0386 }
0387
0388 FMH::MODEL_LIST CollectionDB::getDBData(const QStringList &urls)
0389 {
0390     FMH::MODEL_LIST mapList;
0391
0392     for (const auto &url : urls) {
0393         const auto queryTxt = QString("SELECT * FROM %1 t INNER JOIN albums a on a.album = t.album and a.artist = t.artist WHERE t.%2 = \"%3\"").arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::URL], url);
0394
0395         mapList << this->getDBData(queryTxt);
0396     }
0397
0398     return mapList;
0399 }
0400
0401 FMH::MODEL_LIST CollectionDB::getDBData(const QString &queryTxt, std::function<bool(FMH::MODEL &item)> modifier)
0402 {
0403     FMH::MODEL_LIST mapList;
0404
0405     auto query = this->getQuery(queryTxt);
0406
0407     if (query.exec()) {
0408         const auto keys = FMH::MODEL_NAME.keys();
0409
0410         while (query.next()) {
0411             FMH::MODEL data;
0412             for (const auto &key : keys)
0413             {
0414                 if (query.record().indexOf(FMH::MODEL_NAME[key]) > -1)
0415                 {
0416                     data.insert(key, query.value(FMH::MODEL_NAME[key]).toString());
0417                 }
0418             }
0419
0420             if (modifier) {
0421                 if (!modifier(data))
0422                 {
0423                     continue;
0424                 }
0425             }
0426
0427             mapList << data;
0428         }
0429
0430     } else
0431         qDebug() << query.lastError() << query.lastQuery();
0432
0433     return mapList;
0434 }
0435
0436 FMH::MODEL_LIST CollectionDB::getAlbumTracks(const QString &album, const QString &artist, const FMH::MODEL_KEY &orderBy, const BAE::W &order)
0437 {
0438     const auto queryTxt = QString("SELECT * FROM %1 WHERE %2 = \"%3\" AND %4 = \"%5\" ORDER by %6 %7")
0439             .arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist, FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], album, FMH::MODEL_NAME[orderBy], SLANG[order]);
0440
0441     return this->getDBData(queryTxt);
0442 }
0443
0444 FMH::MODEL_LIST CollectionDB::getArtistTracks(const QString &artist, const FMH::MODEL_KEY &orderBy, const BAE::W &order)
0445 {
0446     const auto queryTxt =
0447             QString("SELECT * FROM %1 WHERE %2 = \"%3\" ORDER by %4 %5, %6 %5").arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist, FMH::MODEL_NAME[orderBy], SLANG[order], FMH::MODEL_NAME[FMH::MODEL_KEY::TRACK]);
0448
0449     return this->getDBData(queryTxt);
0450 }
0451
0452 QStringList CollectionDB::getArtistAlbums(const QString &artist)
0453 {
0454     QStringList albums;
0455
0456     const auto queryTxt = QString("SELECT %4 FROM %1 WHERE %2 = \"%3\" ORDER BY %4 ASC").arg(TABLEMAP[TABLE::ALBUMS], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist, FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM]);
0457     auto query = this->getDBData(queryTxt);
0458
0459     for (const auto &track : query)
0460         albums << track[FMH::MODEL_KEY::ALBUM];
0461
0462     return albums;
0463 }
0464
0465 FMH::MODEL_LIST CollectionDB::getSearchedTracks(const FMH::MODEL_KEY &where, const QString &search)
0466 {
0467     QString queryTxt;
0468
0469     if (where == FMH::MODEL_KEY::COUNT || where == FMH::MODEL_KEY::RATE)
0470         queryTxt = QString("SELECT t.* FROM %1 t inner join albums al on al.album = t.album and t.artist = al.artist WHERE %2 = \"%3\"").arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[where], search);
0471     else if (where == FMH::MODEL_KEY::WIKI)
0472
0473         queryTxt = QString("SELECT DISTINCT t.* FROM %1 t INNER JOIN %2 al ON t.%3 = al.%3 INNER JOIN %4 ar ON t.%5 = ar.%5 WHERE al.%6 LIKE \"%%7%\" OR ar.%6 LIKE \"%%7%\" COLLATE NOCASE")
0474                 .arg(TABLEMAP[TABLE::TRACKS], TABLEMAP[TABLE::ALBUMS], FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], TABLEMAP[TABLE::ARTISTS], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], FMH::MODEL_NAME[where], search);
0475     else
0476         queryTxt = QString("SELECT t.* FROM %1 t inner join albums al on al.album = t.album and t.artist = al.artist WHERE t.%2 LIKE \"%%3%\" ORDER BY strftime(\"%s\", t.addDate) desc LIMIT 1000")
0477                 .arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[where], search);
0478
0479     return this->getDBData(queryTxt);
0480 }
0481
0482 FMH::MODEL_LIST CollectionDB::getRecentTracks(const int &limit, const FMH::MODEL_KEY &orderBy, const BAE::W &order)
0483 {
0484     const auto queryTxt = QString("SELECT * FROM %1 ORDER BY strftime(\"%s\",%2) %3 LIMIT %4").arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[orderBy], SLANG[order], QString::number(limit));
0485
0486     return this->getDBData(queryTxt);
0487 }
0488
0489 FMH::MODEL_LIST CollectionDB::getMostPlayedTracks(const int &greaterThan, const int &limit, const FMH::MODEL_KEY &orderBy, const BAE::W &order)
0490 {
0491     auto queryTxt =
0492             QString("SELECT * FROM %1 WHERE %2 > %3 ORDER BY %4 %5 LIMIT %6").arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::COUNT], QString::number(greaterThan), FMH::MODEL_NAME[orderBy], SLANG[order], QString::number(limit));
0493
0494     return this->getDBData(queryTxt);
0495 }
0496
0497 int CollectionDB::getTrackStars(const QString &path)
0498 {
0499     int stars = 0;
0500     auto query = this->getDBData(QString("SELECT %1 FROM %2 WHERE %3 = \"%4\"").arg(FMH::MODEL_NAME[FMH::MODEL_KEY::RATE], TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::URL], path));
0501
0502     for (auto track : query)
0503         stars = track[FMH::MODEL_KEY::RATE].toInt();
0504
0505     return stars;
0506 }
0507
0508 bool CollectionDB::removeTrack(const QString &path)
0509 {
0510     auto queryTxt = QString("DELETE FROM %1 WHERE %2 =  \"%3\"").arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::URL], path);
0511     auto query = this->getQuery(queryTxt);
0512     if (query.exec()) {
0513         if (cleanAlbums())
0514             cleanArtists();
0515         return true;
0516     }
0517     return false;
0518 }
0519
0520 QSqlQuery CollectionDB::getQuery(const QString &queryTxt) const
0521 {
0522     return QSqlQuery(queryTxt, this->m_db);
0523 }
0524
0525 QSqlQuery CollectionDB::getQuery() const
0526 {
0527     QSqlQuery query(this->m_db);
0528     return query;
0529 }
0530
0531 bool CollectionDB::removeSource(const QString &url)
0532 {
0533     const auto path = url.endsWith("/") ? url.chopped(1) : url;
0534
0535     auto queryTxt = QString("DELETE FROM %1 WHERE %2 LIKE \"%3%\"").arg(TABLEMAP[TABLE::TRACKS], FMH::MODEL_NAME[FMH::MODEL_KEY::SOURCE], path);
0536
0537     auto query = this->getQuery(queryTxt);
0538     query.prepare(queryTxt);
0539
0540     if (query.exec()) {
0541         queryTxt = QString("DELETE FROM %1 WHERE %2 LIKE \"%3%\"").arg(TABLEMAP[TABLE::SOURCES], FMH::MODEL_NAME[FMH::MODEL_KEY::URL], path);
0542         query.prepare(queryTxt);
0543         if (query.exec()) {
0544             if (cleanAlbums())
0545                 cleanArtists();
0546             return true;
0547         }
0548     }
0549
0550     return false;
0551 }
0552
0553 sourceTypes CollectionDB::sourceType(const QString &url)
0554 {
0555     /*for now*/
0556     Q_UNUSED(url);
0557     return sourceTypes::LOCAL;
0558 }
0559
0560 /*******************OLD STUFF********************/
0561
0562 void CollectionDB::removeMissingTracks()
0563 {
0564     auto tracks = this->getDBData("select url from tracks");
0565
0566     for (auto track : tracks)
0567         if (!FMH::fileExists(track[FMH::MODEL_KEY::URL]))
0568             this->removeTrack(track[FMH::MODEL_KEY::URL]);
0569 }
0570
0571 bool CollectionDB::removeArtist(const QString &artist)
0572 {
0573     const auto queryTxt = QString("DELETE FROM %1 WHERE %2 = \"%3\" ").arg(TABLEMAP[TABLE::ARTISTS], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist);
0574     auto query = this->getQuery(queryTxt);
0575
0576     return query.exec();
0577 }
0578
0579 bool CollectionDB::cleanArtists()
0580 {
0581     //    delete from artists where artist in (select artist from artists except select distinct artist from tracks);
0582     const auto queryTxt = QString("DELETE FROM %1 WHERE %2 IN (SELECT %2 FROM %1 EXCEPT SELECT DISTINCT %2 FROM %3)").arg(TABLEMAP[TABLE::ARTISTS], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], TABLEMAP[TABLE::TRACKS]);
0583     qDebug() << queryTxt;
0584
0585     auto query = this->getQuery(queryTxt);
0586     Q_EMIT this->artistsCleaned(query.numRowsAffected());
0587     return query.exec();
0588 }
0589
0590 bool CollectionDB::removeAlbum(const QString &album, const QString &artist)
0591 {
0592     const auto queryTxt = QString("DELETE FROM %1 WHERE %2 = \"%3\" AND %4 = \"%5\"").arg(TABLEMAP[TABLE::ALBUMS], FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], album, FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], artist);
0593
0594     auto query = this->getQuery(queryTxt);
0595     return query.exec();
0596 }
0597
0598 bool CollectionDB::cleanAlbums()
0599 {
0600     //    delete from albums where (album, artist) in (select a.album, a.artist from albums a except select distinct album, artist from tracks);
0601     const auto queryTxt = QString("DELETE FROM %1 WHERE (%2, %3) IN (SELECT %2, %3 FROM %1 EXCEPT SELECT DISTINCT %2, %3  FROM %4)")
0602             .arg(TABLEMAP[TABLE::ALBUMS], FMH::MODEL_NAME[FMH::MODEL_KEY::ALBUM], FMH::MODEL_NAME[FMH::MODEL_KEY::ARTIST], TABLEMAP[TABLE::TRACKS]);
0603     qDebug() << queryTxt;
0604     auto query = this->getQuery(queryTxt);
0605     Q_EMIT albumsCleaned(query.numRowsAffected());
0606     return query.exec();
0607 }