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