1 #include "library/crate/cratestorage.h"
2 
3 #include "library/crate/crateschema.h"
4 #include "library/dao/trackschema.h"
5 #include "library/queryutil.h"
6 
7 #include "util/db/dbconnection.h"
8 
9 #include "util/db/sqllikewildcards.h"
10 #include "util/db/fwdsqlquery.h"
11 
12 #include "util/logger.h"
13 
14 
15 namespace {
16 
17 const mixxx::Logger kLogger("CrateStorage");
18 
19 const QString CRATETABLE_LOCKED = "locked";
20 
21 const QString CRATE_SUMMARY_VIEW = "crate_summary";
22 
23 const QString CRATESUMMARY_TRACK_COUNT = "track_count";
24 const QString CRATESUMMARY_TRACK_DURATION = "track_duration";
25 
26 const QString kCrateTracksJoin = QString(
27         "LEFT JOIN %3 ON %3.%4=%1.%2").arg(
28                 CRATE_TABLE,
29                 CRATETABLE_ID,
30                 CRATE_TRACKS_TABLE,
31                 CRATETRACKSTABLE_CRATEID);
32 
33 const QString kLibraryTracksJoin = kCrateTracksJoin + QString(
34         " LEFT JOIN %3 ON %3.%4=%1.%2").arg(
35                 CRATE_TRACKS_TABLE,
36                 CRATETRACKSTABLE_TRACKID,
37                 LIBRARY_TABLE,
38                 LIBRARYTABLE_ID);
39 
40 const QString kCrateSummaryViewSelect = QString(
41         "SELECT %1.*,"
42             "COUNT(CASE %2.%4 WHEN 0 THEN 1 ELSE NULL END) AS %5,"
43             "SUM(CASE %2.%4 WHEN 0 THEN %2.%3 ELSE 0 END) AS %6 "
44             "FROM %1").arg(
45                 CRATE_TABLE,
46                 LIBRARY_TABLE,
47                 LIBRARYTABLE_DURATION,
48                 LIBRARYTABLE_MIXXXDELETED,
49                 CRATESUMMARY_TRACK_COUNT,
50                 CRATESUMMARY_TRACK_DURATION);
51 
52 const QString kCrateSummaryViewQuery = QString(
53             "CREATE TEMPORARY VIEW IF NOT EXISTS %1 AS %2 %3 GROUP BY %4.%5").arg(
54                     CRATE_SUMMARY_VIEW,
55                     kCrateSummaryViewSelect,
56                     kLibraryTracksJoin,
57                     CRATE_TABLE,
58                     CRATETABLE_ID);
59 
60 
61 class CrateQueryBinder {
62   public:
CrateQueryBinder(FwdSqlQuery & query)63     explicit CrateQueryBinder(FwdSqlQuery& query)
64         : m_query(query) {
65     }
66     virtual ~CrateQueryBinder() = default;
67 
bindId(const QString & placeholder,const Crate & crate) const68     void bindId(const QString& placeholder, const Crate& crate) const {
69         m_query.bindValue(placeholder, crate.getId());
70     }
bindName(const QString & placeholder,const Crate & crate) const71     void bindName(const QString& placeholder, const Crate& crate) const {
72         m_query.bindValue(placeholder, crate.getName());
73     }
bindLocked(const QString & placeholder,const Crate & crate) const74     void bindLocked(const QString& placeholder, const Crate& crate) const {
75         m_query.bindValue(placeholder, crate.isLocked());
76     }
bindAutoDjSource(const QString & placeholder,const Crate & crate) const77     void bindAutoDjSource(const QString& placeholder, const Crate& crate) const {
78         m_query.bindValue(placeholder, crate.isAutoDjSource());
79     }
80 
81   protected:
82     FwdSqlQuery& m_query;
83 };
84 
85 const QChar kSqlListSeparator(',');
86 
87 // It is not possible to bind multiple values as a list to a query.
88 // The list of track ids has to be transformed into a single list
89 // string before it can be used in an SQL query.
joinSqlStringList(const QList<TrackId> & trackIds)90 QString joinSqlStringList(const QList<TrackId>& trackIds) {
91     QString joinedTrackIds;
92     // Reserve memory up front to prevent reallocation. Here we
93     // assume that all track ids fit into 6 decimal digits and
94     // add 1 character for the list separator.
95     joinedTrackIds.reserve((6 + 1) * trackIds.size());
96     for (const auto& trackId: trackIds) {
97         if (!joinedTrackIds.isEmpty()) {
98             joinedTrackIds += kSqlListSeparator;
99         }
100         joinedTrackIds += trackId.toString();
101     }
102     return joinedTrackIds;
103 }
104 
105 } // anonymous namespace
106 
107 
108 
CrateQueryFields(const FwdSqlQuery & query)109 CrateQueryFields::CrateQueryFields(const FwdSqlQuery& query)
110     : m_iId(query.fieldIndex(CRATETABLE_ID)),
111       m_iName(query.fieldIndex(CRATETABLE_NAME)),
112       m_iLocked(query.fieldIndex(CRATETABLE_LOCKED)),
113       m_iAutoDjSource(query.fieldIndex(CRATETABLE_AUTODJ_SOURCE)) {
114 }
115 
116 
populateFromQuery(const FwdSqlQuery & query,Crate * pCrate) const117 void CrateQueryFields::populateFromQuery(
118         const FwdSqlQuery& query,
119         Crate* pCrate) const {
120     pCrate->setId(getId(query));
121     pCrate->setName(getName(query));
122     pCrate->setLocked(isLocked(query));
123     pCrate->setAutoDjSource(isAutoDjSource(query));
124 }
125 
126 
CrateTrackQueryFields(const FwdSqlQuery & query)127 CrateTrackQueryFields::CrateTrackQueryFields(const FwdSqlQuery& query)
128     : m_iCrateId(query.fieldIndex(CRATETRACKSTABLE_CRATEID)),
129       m_iTrackId(query.fieldIndex(CRATETRACKSTABLE_TRACKID)) {
130 }
131 
TrackQueryFields(const FwdSqlQuery & query)132 TrackQueryFields::TrackQueryFields(const FwdSqlQuery& query)
133     : m_iTrackId(query.fieldIndex(CRATETRACKSTABLE_TRACKID)) {
134 }
135 
CrateSummaryQueryFields(const FwdSqlQuery & query)136 CrateSummaryQueryFields::CrateSummaryQueryFields(const FwdSqlQuery& query)
137     : CrateQueryFields(query),
138       m_iTrackCount(query.fieldIndex(CRATESUMMARY_TRACK_COUNT)),
139       m_iTrackDuration(query.fieldIndex(CRATESUMMARY_TRACK_DURATION)) {
140 }
141 
populateFromQuery(const FwdSqlQuery & query,CrateSummary * pCrateSummary) const142 void CrateSummaryQueryFields::populateFromQuery(
143         const FwdSqlQuery& query,
144         CrateSummary* pCrateSummary) const {
145     CrateQueryFields::populateFromQuery(query, pCrateSummary);
146     pCrateSummary->setTrackCount(getTrackCount(query));
147     pCrateSummary->setTrackDuration(getTrackDuration(query));
148 }
149 
repairDatabase(const QSqlDatabase & database)150 void CrateStorage::repairDatabase(const QSqlDatabase& database) {
151     // NOTE(uklotzde): No transactions
152     // All queries are independent so there is no need to enclose some
153     // or all of them in a transaction. Grouping into transactions would
154     // improve the overall performance at the cost of increased resource
155     // utilization. Since performance is not an issue for a maintenance
156     // operation the decision was not to use any transactions.
157 
158     // NOTE(uklotzde): Nested scopes
159     // Each of the following queries is enclosed in a nested scope.
160     // When leaving this scope all resources allocated while executing
161     // the query are released implicitly and before executing the next
162     // query.
163 
164     // Crates
165     {
166         // Delete crates with empty names
167         FwdSqlQuery query(database, QString(
168                 "DELETE FROM %1 WHERE %2 IS NULL OR TRIM(%2)=''").arg(
169                         CRATE_TABLE,
170                         CRATETABLE_NAME));
171         if (query.execPrepared() && (query.numRowsAffected() > 0)) {
172             kLogger.warning()
173                     << "Deleted" << query.numRowsAffected()
174                     << "crates with empty names";
175         }
176     }
177     {
178         // Fix invalid values in the "locked" column
179         FwdSqlQuery query(database, QString(
180                 "UPDATE %1 SET %2=0 WHERE %2 NOT IN (0,1)").arg(
181                         CRATE_TABLE,
182                         CRATETABLE_LOCKED));
183         if (query.execPrepared() && (query.numRowsAffected() > 0)) {
184             kLogger.warning()
185                     << "Fixed boolean values in table" << CRATE_TABLE
186                     << "column" << CRATETABLE_LOCKED
187                     << "for" << query.numRowsAffected() << "crates";
188         }
189     }
190     {
191         // Fix invalid values in the "autodj_source" column
192         FwdSqlQuery query(database, QString(
193                 "UPDATE %1 SET %2=0 WHERE %2 NOT IN (0,1)").arg(
194                         CRATE_TABLE,
195                         CRATETABLE_AUTODJ_SOURCE));
196         if (query.execPrepared() && (query.numRowsAffected() > 0)) {
197             kLogger.warning()
198                     << "Fixed boolean values in table" << CRATE_TABLE
199                     << "column" << CRATETABLE_AUTODJ_SOURCE
200                     << "for" << query.numRowsAffected() << "crates";
201         }
202     }
203 
204     // Crate tracks
205     {
206         // Remove tracks from non-existent crates
207         FwdSqlQuery query(database, QString(
208                 "DELETE FROM %1 WHERE %2 NOT IN (SELECT %3 FROM %4)").arg(
209                         CRATE_TRACKS_TABLE,
210                         CRATETRACKSTABLE_CRATEID,
211                         CRATETABLE_ID,
212                         CRATE_TABLE));
213         if (query.execPrepared() && (query.numRowsAffected() > 0)) {
214             kLogger.warning()
215                     << "Removed" << query.numRowsAffected()
216                     << "crate tracks from non-existent crates";
217         }
218     }
219     {
220         // Remove library purged tracks from crates
221         FwdSqlQuery query(database, QString(
222                 "DELETE FROM %1 WHERE %2 NOT IN (SELECT %3 FROM %4)").arg(
223                         CRATE_TRACKS_TABLE,
224                         CRATETRACKSTABLE_TRACKID,
225                         LIBRARYTABLE_ID,
226                         LIBRARY_TABLE));
227         if (query.execPrepared() && (query.numRowsAffected() > 0)) {
228             kLogger.warning()
229                     << "Removed" << query.numRowsAffected()
230                     << "library purged tracks from crates";
231         }
232     }
233 }
234 
connectDatabase(const QSqlDatabase & database)235 void CrateStorage::connectDatabase(const QSqlDatabase& database) {
236     m_database = database;
237     createViews();
238 }
239 
disconnectDatabase()240 void CrateStorage::disconnectDatabase() {
241     // Ensure that we don't use the current database connection
242     // any longer.
243     m_database = QSqlDatabase();
244 }
245 
246 
createViews()247 void CrateStorage::createViews() {
248     VERIFY_OR_DEBUG_ASSERT(FwdSqlQuery(m_database, kCrateSummaryViewQuery).execPrepared()) {
249         kLogger.critical()
250                 << "Failed to create database view for crate summaries!";
251     }
252 }
253 
254 
countCrates() const255 uint CrateStorage::countCrates() const {
256     FwdSqlQuery query(m_database, QString(
257             "SELECT COUNT(*) FROM %1").arg(
258                     CRATE_TABLE));
259     if (query.execPrepared() && query.next()) {
260         uint result = query.fieldValue(0).toUInt();
261         DEBUG_ASSERT(!query.next());
262         return result;
263     } else {
264         return 0;
265     }
266 }
267 
268 
readCrateById(CrateId id,Crate * pCrate) const269 bool CrateStorage::readCrateById(CrateId id, Crate* pCrate) const {
270     FwdSqlQuery query(m_database, QString(
271             "SELECT * FROM %1 WHERE %2=:id").arg(
272                     CRATE_TABLE,
273                     CRATETABLE_ID));
274     query.bindValue(":id", id);
275     if (query.execPrepared()) {
276         CrateSelectResult crates(std::move(query));
277         if ((pCrate != nullptr) ? crates.populateNext(pCrate) : crates.next()) {
278             VERIFY_OR_DEBUG_ASSERT(!crates.next()) {
279                 kLogger.warning()
280                         << "Ambiguous crate id:" << id;
281             }
282             return true;
283         } else {
284             kLogger.warning()
285                     << "Crate not found by id:" << id;
286         }
287     }
288     return false;
289 }
290 
291 
readCrateByName(const QString & name,Crate * pCrate) const292 bool CrateStorage::readCrateByName(const QString& name, Crate* pCrate) const {
293     FwdSqlQuery query(m_database, QString(
294             "SELECT * FROM %1 WHERE %2=:name").arg(
295                     CRATE_TABLE,
296                     CRATETABLE_NAME));
297     query.bindValue(":name", name);
298     if (query.execPrepared()) {
299         CrateSelectResult crates(std::move(query));
300         if ((pCrate != nullptr) ? crates.populateNext(pCrate) : crates.next()) {
301             VERIFY_OR_DEBUG_ASSERT(!crates.next()) {
302                 kLogger.warning()
303                         << "Ambiguous crate name:" << name;
304             }
305             return true;
306         } else {
307             if (kLogger.debugEnabled()) {
308                 kLogger.debug()
309                         << "Crate not found by name:"
310                         << name;
311             }
312         }
313     }
314     return false;
315 }
316 
317 
selectCrates() const318 CrateSelectResult CrateStorage::selectCrates() const {
319     FwdSqlQuery query(m_database,
320             mixxx::DbConnection::collateLexicographically(QString(
321                     "SELECT * FROM %1 ORDER BY %2").arg(
322                             CRATE_TABLE,
323                             CRATETABLE_NAME)));
324 
325     if (query.execPrepared()) {
326         return CrateSelectResult(std::move(query));
327     } else {
328         return CrateSelectResult();
329     }
330 }
331 
332 
selectCratesByIds(const QString & subselectForCrateIds,SqlSubselectMode subselectMode) const333 CrateSelectResult CrateStorage::selectCratesByIds(
334         const QString& subselectForCrateIds,
335         SqlSubselectMode subselectMode) const {
336     QString subselectPrefix;
337     switch (subselectMode) {
338     case SQL_SUBSELECT_IN:
339         if (subselectForCrateIds.isEmpty()) {
340             // edge case: no crates
341             return CrateSelectResult();
342         }
343         subselectPrefix = "IN";
344         break;
345     case SQL_SUBSELECT_NOT_IN:
346         if (subselectForCrateIds.isEmpty()) {
347             // edge case: all crates
348             return selectCrates();
349         }
350         subselectPrefix = "NOT IN";
351         break;
352     }
353     DEBUG_ASSERT(!subselectPrefix.isEmpty());
354     DEBUG_ASSERT(!subselectForCrateIds.isEmpty());
355 
356     FwdSqlQuery query(m_database,
357             mixxx::DbConnection::collateLexicographically(QString(
358                     "SELECT * FROM %1 WHERE %2 %3 (%4) ORDER BY %5").arg(
359                             CRATE_TABLE,
360                             CRATETABLE_ID,
361                             subselectPrefix,
362                             subselectForCrateIds,
363                             CRATETABLE_NAME)));
364 
365     if (query.execPrepared()) {
366         return CrateSelectResult(std::move(query));
367     } else {
368         return CrateSelectResult();
369     }
370 }
371 
372 
selectAutoDjCrates(bool autoDjSource) const373 CrateSelectResult CrateStorage::selectAutoDjCrates(bool autoDjSource) const {
374     FwdSqlQuery query(m_database,
375             mixxx::DbConnection::collateLexicographically(QString(
376                     "SELECT * FROM %1 WHERE %2=:autoDjSource ORDER BY %3").arg(
377                             CRATE_TABLE,
378                             CRATETABLE_AUTODJ_SOURCE,
379                             CRATETABLE_NAME)));
380     query.bindValue(":autoDjSource", autoDjSource);
381     if (query.execPrepared()) {
382         return CrateSelectResult(std::move(query));
383     } else {
384         return CrateSelectResult();
385     }
386 }
387 
388 
selectCrateSummaries() const389 CrateSummarySelectResult CrateStorage::selectCrateSummaries() const {
390     FwdSqlQuery query(m_database,
391             mixxx::DbConnection::collateLexicographically(QString(
392                     "SELECT * FROM %1 ORDER BY %2").arg(
393                             CRATE_SUMMARY_VIEW,
394                             CRATETABLE_NAME)));
395     if (query.execPrepared()) {
396         return CrateSummarySelectResult(std::move(query));
397     } else {
398         return CrateSummarySelectResult();
399     }
400 }
401 
readCrateSummaryById(CrateId id,CrateSummary * pCrateSummary) const402 bool CrateStorage::readCrateSummaryById(CrateId id, CrateSummary* pCrateSummary) const {
403     FwdSqlQuery query(m_database, QString(
404             "SELECT * FROM %1 WHERE %2=:id").arg(
405                     CRATE_SUMMARY_VIEW,
406                     CRATETABLE_ID));
407     query.bindValue(":id", id);
408     if (query.execPrepared()) {
409         CrateSummarySelectResult crateSummaries(std::move(query));
410         if ((pCrateSummary != nullptr) ? crateSummaries.populateNext(pCrateSummary) : crateSummaries.next()) {
411             VERIFY_OR_DEBUG_ASSERT(!crateSummaries.next()) {
412                 kLogger.warning()
413                         << "Ambiguous crate id:" << id;
414             }
415             return true;
416         } else {
417             kLogger.warning()
418                     << "Crate summary not found by id:" << id;
419         }
420     }
421     return false;
422 }
423 
424 
countCrateTracks(CrateId crateId) const425 uint CrateStorage::countCrateTracks(CrateId crateId) const {
426     FwdSqlQuery query(m_database, QString(
427             "SELECT COUNT(*) FROM %1 WHERE %2=:crateId").arg(
428                     CRATE_TRACKS_TABLE,
429                     CRATETRACKSTABLE_CRATEID));
430     query.bindValue(":crateId", crateId);
431     if (query.execPrepared() && query.next()) {
432         uint result = query.fieldValue(0).toUInt();
433         DEBUG_ASSERT(!query.next());
434         return result;
435     } else {
436         return 0;
437     }
438 }
439 
440 
441 //static
formatSubselectQueryForCrateTrackIds(CrateId crateId)442 QString CrateStorage::formatSubselectQueryForCrateTrackIds(
443         CrateId crateId) {
444     return QString("SELECT %1 FROM %2 WHERE %3=%4").arg(
445             CRATETRACKSTABLE_TRACKID,
446             CRATE_TRACKS_TABLE,
447             CRATETRACKSTABLE_CRATEID,
448             crateId.toString());
449 }
450 
formatQueryForTrackIdsByCrateNameLike(const QString & crateNameLike) const451 QString CrateStorage::formatQueryForTrackIdsByCrateNameLike(
452         const QString& crateNameLike) const {
453     FieldEscaper escaper(m_database);
454     QString escapedCrateNameLike = escaper.escapeString(kSqlLikeMatchAll + crateNameLike + kSqlLikeMatchAll);
455     return QString("SELECT DISTINCT %1 FROM %2 JOIN %3 ON %4=%5 WHERE %6 LIKE %7 ORDER BY %1").arg(
456             CRATETRACKSTABLE_TRACKID,
457             CRATE_TRACKS_TABLE,
458             CRATE_TABLE,
459             CRATETRACKSTABLE_CRATEID,
460             CRATETABLE_ID,
461             CRATETABLE_NAME,
462             escapedCrateNameLike);
463 }
464 
465 //static
formatQueryForTrackIdsWithCrate()466 QString CrateStorage::formatQueryForTrackIdsWithCrate() {
467     return QString("SELECT DISTINCT %1 FROM %2 JOIN %3 ON %4=%5 ORDER BY %1").arg(
468             CRATETRACKSTABLE_TRACKID,
469             CRATE_TRACKS_TABLE,
470             CRATE_TABLE,
471             CRATETRACKSTABLE_CRATEID,
472             CRATETABLE_ID);
473 }
474 
475 
476 
selectCrateTracksSorted(CrateId crateId) const477 CrateTrackSelectResult CrateStorage::selectCrateTracksSorted(CrateId crateId) const {
478     FwdSqlQuery query(m_database, QString(
479             "SELECT * FROM %1 WHERE %2=:crateId ORDER BY %3").arg(
480                     CRATE_TRACKS_TABLE,
481                     CRATETRACKSTABLE_CRATEID,
482                     CRATETRACKSTABLE_TRACKID));
483     query.bindValue(":crateId", crateId);
484     if (query.execPrepared()) {
485         return CrateTrackSelectResult(std::move(query));
486     } else {
487         return CrateTrackSelectResult();
488     }
489 }
490 
491 
selectTrackCratesSorted(TrackId trackId) const492 CrateTrackSelectResult CrateStorage::selectTrackCratesSorted(TrackId trackId) const {
493     FwdSqlQuery query(m_database, QString(
494             "SELECT * FROM %1 WHERE %2=:trackId ORDER BY %3").arg(
495                     CRATE_TRACKS_TABLE,
496                     CRATETRACKSTABLE_TRACKID,
497                     CRATETRACKSTABLE_CRATEID));
498     query.bindValue(":trackId", trackId);
499     if (query.execPrepared()) {
500         return CrateTrackSelectResult(std::move(query));
501     } else {
502         return CrateTrackSelectResult();
503     }
504 }
505 
selectCratesWithTrackCount(const QList<TrackId> & trackIds) const506 CrateSummarySelectResult CrateStorage::selectCratesWithTrackCount(const QList<TrackId>& trackIds) const {
507     FwdSqlQuery query(m_database, QString(
508         "SELECT *, ("
509         "    SELECT COUNT(*) FROM %1 WHERE %2.%3 = %1.%4 and %1.%5 in (%9)"
510         " ) AS %6, 0 as %7 FROM %2 ORDER BY %8").arg(
511                 CRATE_TRACKS_TABLE,
512                 CRATE_TABLE,
513                 CRATETABLE_ID,
514                 CRATETRACKSTABLE_CRATEID,
515                 CRATETRACKSTABLE_TRACKID,
516                 CRATESUMMARY_TRACK_COUNT,
517                 CRATESUMMARY_TRACK_DURATION,
518                 CRATETABLE_NAME,
519                 joinSqlStringList(trackIds)));
520 
521     if (query.execPrepared()) {
522         return CrateSummarySelectResult(std::move(query));
523     } else {
524         return CrateSummarySelectResult();
525     }
526 }
527 
selectTracksSortedByCrateNameLike(const QString & crateNameLike) const528 CrateTrackSelectResult CrateStorage::selectTracksSortedByCrateNameLike(const QString& crateNameLike) const {
529     FwdSqlQuery query(m_database, QString(
530             "SELECT %1,%2 FROM %3 JOIN %4 ON %5 = %6 WHERE %7 LIKE :crateNameLike ORDER BY %1").arg(
531                     CRATETRACKSTABLE_TRACKID,
532                     CRATETRACKSTABLE_CRATEID,
533                     CRATE_TRACKS_TABLE,
534                     CRATE_TABLE,
535                     CRATETABLE_ID,
536                     CRATETRACKSTABLE_CRATEID,
537                     CRATETABLE_NAME));
538     query.bindValue(":crateNameLike", QVariant(kSqlLikeMatchAll + crateNameLike + kSqlLikeMatchAll));
539 
540     if (query.execPrepared()) {
541         return CrateTrackSelectResult(std::move(query));
542     } else {
543         return CrateTrackSelectResult();
544     }
545 }
546 
selectAllTracksSorted() const547 TrackSelectResult CrateStorage::selectAllTracksSorted() const {
548     FwdSqlQuery query(m_database, QString(
549             "SELECT DISTINCT %1 FROM %2 ORDER BY %1").arg(
550                     CRATETRACKSTABLE_TRACKID, // %1
551                     CRATE_TRACKS_TABLE)); // %2
552     if (query.execPrepared()) {
553         return TrackSelectResult(std::move(query));
554     } else {
555         return TrackSelectResult();
556     }
557 }
558 
collectCrateIdsOfTracks(const QList<TrackId> & trackIds) const559 QSet<CrateId> CrateStorage::collectCrateIdsOfTracks(const QList<TrackId>& trackIds) const {
560     // NOTE(uklotzde): One query per track id. This could be optimized
561     // by querying for chunks of track ids and collecting the results.
562     QSet<CrateId> trackCrates;
563     for (const auto& trackId: trackIds) {
564         // NOTE(uklotzde): The query result does not need to be sorted by crate id
565         // here. But since the corresponding FK column is indexed the impact on the
566         // performance should be negligible. By reusing an existing query we reduce
567         // the amount of code and the number of prepared SQL queries.
568         CrateTrackSelectResult crateTracks(selectTrackCratesSorted(trackId));
569         while (crateTracks.next()) {
570             DEBUG_ASSERT(crateTracks.trackId() == trackId);
571             trackCrates.insert(crateTracks.crateId());
572         }
573     }
574     return trackCrates;
575 }
576 
577 
onInsertingCrate(const Crate & crate,CrateId * pCrateId)578 bool CrateStorage::onInsertingCrate(
579         const Crate& crate,
580         CrateId* pCrateId) {
581     VERIFY_OR_DEBUG_ASSERT(!crate.getId().isValid()) {
582         kLogger.warning()
583                 << "Cannot insert crate with a valid id:" << crate.getId();
584         return false;
585     }
586     FwdSqlQuery query(m_database, QString(
587             "INSERT INTO %1 (%2,%3,%4) VALUES (:name,:locked,:autoDjSource)").arg(
588                     CRATE_TABLE,
589                     CRATETABLE_NAME,
590                     CRATETABLE_LOCKED,
591                     CRATETABLE_AUTODJ_SOURCE));
592     VERIFY_OR_DEBUG_ASSERT(query.isPrepared()) {
593         return false;
594     }
595     CrateQueryBinder queryBinder(query);
596     queryBinder.bindName(":name", crate);
597     queryBinder.bindLocked(":locked", crate);
598     queryBinder.bindAutoDjSource(":autoDjSource", crate);
599     VERIFY_OR_DEBUG_ASSERT(query.execPrepared()) {
600         return false;
601     }
602     if (query.numRowsAffected() > 0) {
603         DEBUG_ASSERT(query.numRowsAffected() == 1);
604         if (pCrateId != nullptr) {
605             *pCrateId = CrateId(query.lastInsertId());
606             DEBUG_ASSERT(pCrateId->isValid());
607         }
608         return true;
609     } else {
610         return false;
611     }
612 }
613 
614 
onUpdatingCrate(const Crate & crate)615 bool CrateStorage::onUpdatingCrate(
616         const Crate& crate) {
617     VERIFY_OR_DEBUG_ASSERT(crate.getId().isValid()) {
618         kLogger.warning()
619                 << "Cannot update crate without a valid id";
620         return false;
621     }
622     FwdSqlQuery query(m_database, QString(
623             "UPDATE %1 SET %2=:name,%3=:locked,%4=:autoDjSource WHERE %5=:id").arg(
624                     CRATE_TABLE,
625                     CRATETABLE_NAME,
626                     CRATETABLE_LOCKED,
627                     CRATETABLE_AUTODJ_SOURCE,
628                     CRATETABLE_ID));
629     VERIFY_OR_DEBUG_ASSERT(query.isPrepared()) {
630         return false;
631     }
632     CrateQueryBinder queryBinder(query);
633     queryBinder.bindId(":id", crate);
634     queryBinder.bindName(":name", crate);
635     queryBinder.bindLocked(":locked", crate);
636     queryBinder.bindAutoDjSource(":autoDjSource", crate);
637     VERIFY_OR_DEBUG_ASSERT(query.execPrepared()) {
638         return false;
639     }
640     if (query.numRowsAffected() > 0) {
641         VERIFY_OR_DEBUG_ASSERT(query.numRowsAffected() <= 1) {
642             kLogger.warning()
643                     << "Updated multiple crates with the same id" << crate.getId();
644         }
645         return true;
646     } else {
647         kLogger.warning()
648                 << "Cannot update non-existent crate with id" << crate.getId();
649         return false;
650     }
651 }
652 
653 
onDeletingCrate(CrateId crateId)654 bool CrateStorage::onDeletingCrate(
655         CrateId crateId) {
656     VERIFY_OR_DEBUG_ASSERT(crateId.isValid()) {
657         kLogger.warning()
658                 << "Cannot delete crate without a valid id";
659         return false;
660     }
661     {
662         FwdSqlQuery query(m_database, QString(
663                 "DELETE FROM %1 WHERE %2=:id").arg(
664                         CRATE_TRACKS_TABLE,
665                         CRATETRACKSTABLE_CRATEID));
666         VERIFY_OR_DEBUG_ASSERT(query.isPrepared()) {
667             return false;
668         }
669         query.bindValue(":id", crateId);
670         VERIFY_OR_DEBUG_ASSERT(query.execPrepared()) {
671             return false;
672         }
673         if (query.numRowsAffected() <= 0) {
674             if (kLogger.debugEnabled()) {
675                 kLogger.debug()
676                         << "Deleting empty crate with id"
677                         << crateId;
678             }
679         }
680     }
681     {
682         FwdSqlQuery query(m_database, QString(
683                 "DELETE FROM %1 WHERE %2=:id").arg(
684                         CRATE_TABLE,
685                         CRATETABLE_ID));
686         VERIFY_OR_DEBUG_ASSERT(query.isPrepared()) {
687             return false;
688         }
689         query.bindValue(":id", crateId);
690         VERIFY_OR_DEBUG_ASSERT(query.execPrepared()) {
691             return false;
692         }
693         if (query.numRowsAffected() > 0) {
694             VERIFY_OR_DEBUG_ASSERT(query.numRowsAffected() <= 1) {
695                 kLogger.warning()
696                         << "Deleted multiple crates with the same id" << crateId;
697             }
698             return true;
699         } else {
700             kLogger.warning()
701                     << "Cannot delete non-existent crate with id" << crateId;
702             return false;
703         }
704     }
705 }
706 
707 
onAddingCrateTracks(CrateId crateId,const QList<TrackId> & trackIds)708 bool CrateStorage::onAddingCrateTracks(
709         CrateId crateId,
710         const QList<TrackId>& trackIds) {
711     FwdSqlQuery query(m_database, QString(
712             "INSERT OR IGNORE INTO %1 (%2, %3) VALUES (:crateId,:trackId)").arg(
713                     CRATE_TRACKS_TABLE,
714                     CRATETRACKSTABLE_CRATEID,
715                     CRATETRACKSTABLE_TRACKID));
716     if (!query.isPrepared()) {
717         return false;
718     }
719     query.bindValue(":crateId", crateId);
720     for (const auto& trackId: trackIds) {
721         query.bindValue(":trackId", trackId);
722         if (!query.execPrepared()) {
723             return false;
724         }
725         if (query.numRowsAffected() == 0) {
726             // track is already in crate
727             if (kLogger.debugEnabled()) {
728                 kLogger.debug()
729                         << "Track" << trackId
730                         << "not added to crate" << crateId;
731             }
732         } else {
733             DEBUG_ASSERT(query.numRowsAffected() == 1);
734         }
735     }
736     return true;
737 }
738 
739 
onRemovingCrateTracks(CrateId crateId,const QList<TrackId> & trackIds)740 bool CrateStorage::onRemovingCrateTracks(
741         CrateId crateId,
742         const QList<TrackId>& trackIds) {
743     // NOTE(uklotzde): We remove tracks in a loop
744     // analogously to adding tracks (see above).
745     FwdSqlQuery query(m_database, QString(
746             "DELETE FROM %1 WHERE %2=:crateId AND %3=:trackId").arg(
747                     CRATE_TRACKS_TABLE,
748                     CRATETRACKSTABLE_CRATEID,
749                     CRATETRACKSTABLE_TRACKID));
750     if (!query.isPrepared()) {
751         return false;
752     }
753     query.bindValue(":crateId", crateId);
754     for (const auto& trackId: trackIds) {
755         query.bindValue(":trackId", trackId);
756         if (!query.execPrepared()) {
757             return false;
758         }
759         if (query.numRowsAffected() == 0) {
760             // track not found in crate
761             if (kLogger.debugEnabled()) {
762                 kLogger.debug()
763                         << "Track" << trackId
764                         << "not removed from crate" << crateId;
765             }
766         } else {
767             DEBUG_ASSERT(query.numRowsAffected() == 1);
768         }
769     }
770     return true;
771 }
772 
773 
onPurgingTracks(const QList<TrackId> & trackIds)774 bool CrateStorage::onPurgingTracks(
775         const QList<TrackId>& trackIds) {
776     // NOTE(uklotzde): Remove tracks from crates one-by-one.
777     // This might be optimized by deleting multiple track ids
778     // at once in chunks with a maximum size.
779     FwdSqlQuery query(m_database, QString(
780             "DELETE FROM %1 WHERE %2=:trackId").arg(
781                     CRATE_TRACKS_TABLE,
782                     CRATETRACKSTABLE_TRACKID));
783     if (!query.isPrepared()) {
784         return false;
785     }
786     for (const auto& trackId: trackIds) {
787         query.bindValue(":trackId", trackId);
788         if (!query.execPrepared()) {
789             return false;
790         }
791     }
792     return true;
793 }
794