1 /*
2 * Cantata
3 *
4 * Copyright (c) 2017-2020 Craig Drummond <craig.p.drummond@gmail.com>
5 *
6 * ----
7 *
8 * This program is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 2 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 * General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program; see the file COPYING. If not, write to
20 * the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
21 * Boston, MA 02110-1301, USA.
22 */
23
24 #include "librarydb.h"
25 #include <QCoreApplication>
26 #include <QSqlDatabase>
27 #include <QSqlError>
28 #include <QSqlQuery>
29 #include <QFile>
30 #include <QRegExp>
31 #include <QRandomGenerator>
32 #include <QDebug>
33 #include <algorithm>
34
35 static const int constSchemaVersion=5;
36
37 bool LibraryDb::dbgEnabled=false;
38 #define DBUG if (dbgEnabled) qWarning() << metaObject()->className() << __FUNCTION__ << (void *)this
39
40 const QLatin1String LibraryDb::constFileExt(".sql");
41 const QLatin1String LibraryDb::constNullGenre("-");
42
toAlbumSort(const QString & str)43 LibraryDb::AlbumSort LibraryDb::toAlbumSort(const QString &str)
44 {
45 for (int i=0; i<AS_Count; ++i) {
46 if (albumSortStr((AlbumSort)i)==str) {
47 return (AlbumSort)i;
48 }
49 }
50 return AS_AlArYr;
51 }
52
albumSortStr(AlbumSort m)53 QString LibraryDb::albumSortStr(AlbumSort m)
54 {
55 switch(m) {
56 case AS_ArAlYr: return "artist";
57 default:
58 case AS_AlArYr: return "album";
59 case AS_YrAlAr: return "year";
60 case AS_Modified: return "modified";
61
62 // Re-added in 2.1
63 case AS_ArYrAl: return "aryral";
64 case AS_AlYrAr: return "alyral";
65 case AS_YrArAl: return "yraral";
66 }
67 }
68
albumsSortAlArYr(const LibraryDb::Album & a,const LibraryDb::Album & b)69 static bool albumsSortAlArYr(const LibraryDb::Album &a, const LibraryDb::Album &b)
70 {
71 const QString &an=a.sort.isEmpty() ? a.name : a.sort;
72 const QString &bn=b.sort.isEmpty() ? b.name : b.sort;
73 int cmp=an.localeAwareCompare(bn);
74 if (cmp!=0) {
75 return cmp<0;
76 }
77
78 const QString &aa=a.artistSort.isEmpty() ? a.artist : a.artistSort;
79 const QString &ba=b.artistSort.isEmpty() ? b.artist : b.artistSort;
80 cmp=aa.localeAwareCompare(ba);
81 if (cmp!=0) {
82 return cmp<0;
83 }
84 return a.year<b.year;
85 }
86
albumsSortArAlYr(const LibraryDb::Album & a,const LibraryDb::Album & b)87 static bool albumsSortArAlYr(const LibraryDb::Album &a, const LibraryDb::Album &b)
88 {
89 const QString &aa=a.artistSort.isEmpty() ? a.artist : a.artistSort;
90 const QString &ba=b.artistSort.isEmpty() ? b.artist : b.artistSort;
91 int cmp=aa.localeAwareCompare(ba);
92 if (cmp!=0) {
93 return cmp<0;
94 }
95
96 const QString &an=a.sort.isEmpty() ? a.name : a.sort;
97 const QString &bn=b.sort.isEmpty() ? b.name : b.sort;
98 cmp=an.localeAwareCompare(bn);
99 if (cmp!=0) {
100 return cmp<0;
101 }
102
103 return a.year<b.year;
104 }
105
albumsSortAlYrAr(const LibraryDb::Album & a,const LibraryDb::Album & b)106 static bool albumsSortAlYrAr(const LibraryDb::Album &a, const LibraryDb::Album &b)
107 {
108 const QString &an=a.sort.isEmpty() ? a.name : a.sort;
109 const QString &bn=b.sort.isEmpty() ? b.name : b.sort;
110 int cmp=an.localeAwareCompare(bn);
111 if (cmp!=0) {
112 return cmp<0;
113 }
114
115 if (a.year!=b.year) {
116 return a.year<b.year;
117 }
118
119 const QString &aa=a.artistSort.isEmpty() ? a.artist : a.artistSort;
120 const QString &ba=b.artistSort.isEmpty() ? b.artist : b.artistSort;
121 return aa.localeAwareCompare(ba)<0;
122 }
123
albumsSortArYrAl(const LibraryDb::Album & a,const LibraryDb::Album & b)124 static bool albumsSortArYrAl(const LibraryDb::Album &a, const LibraryDb::Album &b)
125 {
126 const QString &aa=a.artistSort.isEmpty() ? a.artist : a.artistSort;
127 const QString &ba=b.artistSort.isEmpty() ? b.artist : b.artistSort;
128 int cmp=aa.localeAwareCompare(ba);
129 if (cmp!=0) {
130 return cmp<0;
131 }
132
133 if (a.year!=b.year) {
134 return a.year<b.year;
135 }
136
137 const QString &an=a.sort.isEmpty() ? a.name : a.sort;
138 const QString &bn=b.sort.isEmpty() ? b.name : b.sort;
139 return an.localeAwareCompare(bn)<0;
140 }
141
albumsSortYrAlAr(const LibraryDb::Album & a,const LibraryDb::Album & b)142 static bool albumsSortYrAlAr(const LibraryDb::Album &a, const LibraryDb::Album &b)
143 {
144 if (a.year!=b.year) {
145 return a.year<b.year;
146 }
147
148 const QString &an=a.sort.isEmpty() ? a.name : a.sort;
149 const QString &bn=b.sort.isEmpty() ? b.name : b.sort;
150 int cmp=an.localeAwareCompare(bn);
151 if (cmp!=0) {
152 return cmp<0;
153 }
154
155 const QString &aa=a.artistSort.isEmpty() ? a.artist : a.artistSort;
156 const QString &ba=b.artistSort.isEmpty() ? b.artist : b.artistSort;
157 return aa.localeAwareCompare(ba)<0;
158 }
159
albumsSortYrArAl(const LibraryDb::Album & a,const LibraryDb::Album & b)160 static bool albumsSortYrArAl(const LibraryDb::Album &a, const LibraryDb::Album &b)
161 {
162 if (a.year!=b.year) {
163 return a.year<b.year;
164 }
165
166 const QString &aa=a.artistSort.isEmpty() ? a.artist : a.artistSort;
167 const QString &ba=b.artistSort.isEmpty() ? b.artist : b.artistSort;
168 int cmp=aa.localeAwareCompare(ba);
169 if (cmp!=0) {
170 return cmp<0;
171 }
172
173 const QString &an=a.sort.isEmpty() ? a.name : a.sort;
174 const QString &bn=b.sort.isEmpty() ? b.name : b.sort;
175 return an.localeAwareCompare(bn)<0;
176 }
177
albumsSortModified(const LibraryDb::Album & a,const LibraryDb::Album & b)178 static bool albumsSortModified(const LibraryDb::Album &a, const LibraryDb::Album &b)
179 {
180 if (a.lastModified==b.lastModified) {
181 return albumsSortAlArYr(a, b);
182 }
183 return a.lastModified>b.lastModified;
184 }
185
songSort(const Song & a,const Song & b)186 static bool songSort(const Song &a, const Song &b)
187 {
188 if (Song::SingleTracks==a.type && Song::SingleTracks==b.type) {
189 int cmp=a.title.localeAwareCompare(b.title);
190 if (0!=cmp) {
191 return cmp<0;
192 }
193 cmp=a.artist.localeAwareCompare(b.artist);
194 if (0!=cmp) {
195 return cmp<0;
196 }
197 }
198
199 if (a.disc!=b.disc) {
200 return a.disc<b.disc;
201 }
202 if (a.track!=b.track) {
203 return a.track<b.track;
204 }
205 if (a.displayYear()!=b.displayYear()) {
206 return a.displayYear()<b.displayYear();
207 }
208 int cmp=a.title.localeAwareCompare(b.title);
209 if (0!=cmp) {
210 return cmp<0;
211 }
212 cmp=a.name().localeAwareCompare(b.name());
213 if (0!=cmp) {
214 return cmp<0;
215 }
216 cmp=a.displayGenre().localeAwareCompare(b.displayGenre());
217 if (0!=cmp) {
218 return cmp<0;
219 }
220 if (a.time!=b.time) {
221 return a.time<b.time;
222 }
223 return a.file.compare(b.file)<0;
224 }
225
songsSortAlAr(const Song & a,const Song & b)226 static bool songsSortAlAr(const Song &a, const Song &b)
227 {
228 const QString an=a.hasAlbumSort() ? a.albumSort() : a.album;
229 const QString bn=b.hasAlbumSort() ? b.albumSort() : b.album;
230 int cmp=an.localeAwareCompare(bn);
231 if (cmp!=0) {
232 return cmp<0;
233 }
234
235 const QString aa=a.hasArtistSort() ? a.artistSort() : a.albumArtist();
236 const QString ba=b.hasArtistSort() ? b.artistSort() : b.albumArtist();
237 cmp=aa.localeAwareCompare(ba);
238 if (cmp!=0) {
239 return cmp<0;
240 }
241
242 // if (a.displayYear()!=b.displayYear()) {
243 // return a.displayYear()<b.displayYear();
244 // }
245 return songSort(a, b);
246 }
247
songsSortArAl(const Song & a,const Song & b)248 static bool songsSortArAl(const Song &a, const Song &b)
249 {
250 const QString aa=a.hasArtistSort() ? a.artistSort() : a.albumArtist();
251 const QString ba=b.hasArtistSort() ? b.artistSort() : b.albumArtist();
252 int cmp=aa.localeAwareCompare(ba);
253 if (cmp!=0) {
254 return cmp<0;
255 }
256
257 const QString an=a.hasAlbumSort() ? a.albumSort() : a.album;
258 const QString bn=b.hasAlbumSort() ? b.albumSort() : b.album;
259 cmp=an.localeAwareCompare(bn);
260 if (cmp!=0) {
261 return cmp<0;
262 }
263
264 // if (a.displayYear()!=b.displayYear()) {
265 // return a.displayYear()<b.displayYear();
266 // }
267 return songSort(a, b);
268 }
269
270 //static bool songsSortAlYrAr(const Song &a, const Song &b)
271 //{
272 // const QString an=a.hasAlbumSort() ? a.albumSort() : a.album;
273 // const QString bn=b.hasAlbumSort() ? b.albumSort() : b.album;
274 // int cmp=an.localeAwareCompare(bn);
275 // if (cmp!=0) {
276 // return cmp<0;
277 // }
278
279 // if (a.displayYear()!=b.displayYear()) {
280 // return a.displayYear()<b.displayYear();
281 // }
282
283 // const QString aa=a.hasArtistSort() ? a.artistSort() : a.albumArtist();
284 // const QString ba=b.hasArtistSort() ? b.artistSort() : b.albumArtist();
285 // cmp=aa.localeAwareCompare(ba);
286 // if (cmp!=0) {
287 // return cmp<0;
288 // }
289
290 // return songSort(a, b);
291 //}
292
293 //static bool songsSortArYrAl(const Song &a, const Song &b)
294 //{
295 // const QString aa=a.hasArtistSort() ? a.artistSort() : a.albumArtist();
296 // const QString ba=b.hasArtistSort() ? b.artistSort() : b.albumArtist();
297 // int cmp=aa.localeAwareCompare(ba);
298 // if (cmp!=0) {
299 // return cmp<0;
300 // }
301
302 // if (a.displayYear()!=b.displayYear()) {
303 // return a.displayYear()<b.displayYear();
304 // }
305
306 // const QString an=a.hasAlbumSort() ? a.albumSort() : a.album;
307 // const QString bn=b.hasAlbumSort() ? b.albumSort() : b.album;
308 // cmp=an.localeAwareCompare(bn);
309 // if (cmp!=0) {
310 // return cmp<0;
311 // }
312
313 // return songSort(a, b);
314 //}
315
316 //static bool songsSortYrAlAr(const Song &a, const Song &b)
317 //{
318 // if (a.displayYear()!=b.displayYear()) {
319 // return a.displayYear()<b.displayYear();
320 // }
321
322 // const QString an=a.hasAlbumSort() ? a.albumSort() : a.album;
323 // const QString bn=b.hasAlbumSort() ? b.albumSort() : b.album;
324 // int cmp=an.localeAwareCompare(bn);
325 // if (cmp!=0) {
326 // return cmp<0;
327 // }
328
329 // const QString aa=a.hasArtistSort() ? a.artistSort() : a.albumArtist();
330 // const QString ba=b.hasArtistSort() ? b.artistSort() : b.albumArtist();
331 // cmp=aa.localeAwareCompare(ba);
332 // if (cmp!=0) {
333 // return cmp<0;
334 // }
335
336 // return songSort(a, b);
337 //}
338
339 //static bool songsSortYrArAl(const Song &a, const Song &b)
340 //{
341 // if (a.displayYear()!=b.displayYear()) {
342 // return a.displayYear()<b.displayYear();
343 // }
344
345 // const QString aa=a.hasArtistSort() ? a.artistSort() : a.albumArtist();
346 // const QString ba=b.hasArtistSort() ? b.artistSort() : b.albumArtist();
347 // int cmp=aa.localeAwareCompare(ba);
348 // if (cmp!=0) {
349 // return cmp<0;
350 // }
351
352 // const QString an=a.hasAlbumSort() ? a.albumSort() : a.album;
353 // const QString bn=b.hasAlbumSort() ? b.albumSort() : b.album;
354 // cmp=an.localeAwareCompare(bn);
355 // if (cmp!=0) {
356 // return cmp<0;
357 // }
358
359 // return songSort(a, b);
360 //}
361
362 //static bool songsSortModified(const Song &a, const Song &b)
363 //{
364 // if (a.lastModified==b.lastModified) {
365 // return songsSortAlArYr(a, b);
366 // }
367 // return a.lastModified>b.lastModified;
368 //}
369
artistSort(const Song & s)370 static QString artistSort(const Song &s)
371 {
372 if (s.useComposer() && !s.composer().isEmpty()) {
373 return s.composer();
374
375 }
376 if (!s.artistSortString().isEmpty()) {
377 return s.artistSortString();
378 }
379 return Song::sortString(s.albumArtist());
380 }
381
albumSort(const Song & s)382 static QString albumSort(const Song &s)
383 {
384 if (!s.albumSort().isEmpty()) {
385 return s.albumSort();
386 }
387 return Song::sortString(s.album);
388 }
389
390 // Code taken from Clementine's LibraryQuery
391 class SqlQuery
392 {
393 public:
SqlQuery(const QString & colSpec,QSqlDatabase & database)394 SqlQuery(const QString &colSpec, QSqlDatabase &database)
395 : db(database)
396 , fts(false)
397 , columSpec(colSpec)
398 , limit(0)
399 {
400 }
401
addWhere(const QString & column,const QVariant & value,const QString & op="=")402 void addWhere(const QString &column, const QVariant &value, const QString &op="=")
403 {
404 // ignore 'literal' for IN
405 if (!op.compare("IN", Qt::CaseInsensitive)) {
406 QStringList final;
407 for(const QString &singleValue: value.toStringList()) {
408 final.append("?");
409 boundValues << singleValue;
410 }
411 whereClauses << QString("%1 IN (" + final.join(",") + ")").arg(column);
412 } else {
413 // Do integers inline - sqlite seems to get confused when you pass integers
414 // to bound parameters
415 if (QVariant::Int==value.type()) {
416 whereClauses << QString("%1 %2 %3").arg(column, op, value.toString());
417 } else if ("genre"==column) {
418 QString clause("(");
419 for (int i=0; i<Song::constNumGenres; ++i) {
420 clause+=QString("%1 %2 ?").arg(column+QString::number(i+1), op);
421 if (i<(Song::constNumGenres-1)) {
422 clause+=" OR ";
423 }
424 boundValues << value;
425 }
426 clause+=")";
427 whereClauses << clause;
428 } else {
429 whereClauses << QString("%1 %2 ?").arg(column, op);
430 boundValues << value;
431 }
432 }
433 }
434
setFilter(const QString & filter,const QString yearFilter)435 void setFilter(const QString &filter, const QString yearFilter)
436 {
437 if (!filter.isEmpty()) {
438 whereClauses << "songs_fts match ?";
439 boundValues << "\'"+filter+"\'";
440 fts=true;
441 }
442 if (!yearFilter.isEmpty()) {
443 whereClauses << yearFilter;
444 }
445 }
446
setOrder(const QString & o)447 void setOrder(const QString &o)
448 {
449 order=o;
450 }
451
setLimit(int l)452 void setLimit(int l)
453 {
454 limit=l;
455 }
456
exec()457 bool exec()
458 {
459 QString sql=fts
460 ? QString("SELECT %1 FROM songs INNER JOIN songs_fts AS fts ON songs.ROWID = fts.ROWID").arg(columSpec)
461 : QString("SELECT %1 FROM songs").arg(columSpec);
462
463 if (!whereClauses.isEmpty()) {
464 sql+=" WHERE " + whereClauses.join(" AND ");
465 }
466 if (!order.isEmpty()) {
467 sql+=" ORDER by "+order;
468 }
469 if (limit>0) {
470 sql+=" LIMIT "+QString::number(limit);
471 }
472 query = QSqlQuery(db);
473 query.prepare(sql);
474
475 for (const QVariant &value: boundValues) {
476 query.addBindValue(value);
477 }
478 return query.exec();
479 }
480
next()481 bool next() { return query.next(); }
executedQuery() const482 QString executedQuery() const { return query.executedQuery(); }
size() const483 int size() const { return query.size(); }
value(int col) const484 QVariant value(int col) const { return query.value(col); }
realQuery() const485 const QSqlQuery & realQuery() const { return query; }
486
487 private:
488 QSqlDatabase &db;
489 QSqlQuery query;
490 bool fts;
491 QString columSpec;
492 QStringList whereClauses;
493 QVariantList boundValues;
494 QString order;
495 int limit;
496 };
497
LibraryDb(QObject * p,const QString & name)498 LibraryDb::LibraryDb(QObject *p, const QString &name)
499 : QObject(p)
500 , dbName(name)
501 , currentVersion(0)
502 , newVersion(0)
503 , db(nullptr)
504 , insertSongQuery(nullptr)
505 {
506 DBUG;
507 }
508
~LibraryDb()509 LibraryDb::~LibraryDb()
510 {
511 reset();
512 }
513
clear()514 void LibraryDb::clear()
515 {
516 if (db) {
517 DBUG;
518 erase();
519 currentVersion=0;
520 init(dbFileName);
521 }
522 }
523
erase()524 void LibraryDb::erase()
525 {
526 reset();
527 if (!dbFileName.isEmpty() && QFile::exists(dbFileName)) {
528 QFile::remove(dbFileName);
529 }
530 }
531
532 enum SongFields {
533 SF_file,
534 SF_artist ,
535 SF_artistId,
536 SF_albumArtist,
537 SF_artistSort,
538 SF_composer,
539 SF_album,
540 SF_albumId,
541 SF_albumSort,
542 SF_title,
543 SF_genre1,
544 SF_genre2,
545 SF_genre3,
546 SF_genre4,
547 SF_track,
548 SF_disc,
549 SF_time,
550 SF_year,
551 SF_origYear,
552 SF_type,
553 SF_lastModified
554 };
555
init(const QString & dbFile)556 bool LibraryDb::init(const QString &dbFile)
557 {
558 if (dbFile!=dbFileName) {
559 reset();
560 dbFileName=dbFile;
561 }
562 if (db) {
563 return true;
564 }
565
566 DBUG << dbFile << dbName;
567 currentVersion=0;
568 db=new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", dbName.isEmpty() ? QLatin1String(QSqlDatabase::defaultConnection) : dbName));
569 if (!db || !db->isValid()) {
570 emit error(tr("Database error - please check Qt SQLite driver is installed"));
571 return false;
572 }
573 db->setDatabaseName(dbFile);
574 DBUG << (void *)db;
575 if (!db->open()) {
576 delete db;
577 db=nullptr;
578 DBUG << "Failed to open";
579 return false;
580 }
581
582 if (!createTable("versions(collection integer, schema integer)")) {
583 DBUG << "Failed to create versions table";
584 return false;
585 }
586 QSqlQuery query("select collection, schema from versions", *db);
587 int schemaVersion=0;
588 if (query.next()) {
589 currentVersion=query.value(0).toUInt();
590 schemaVersion=query.value(1).toUInt();
591 }
592 if (schemaVersion>0 && schemaVersion!=constSchemaVersion) {
593 DBUG << "Scheme version changed";
594 currentVersion=0;
595 erase();
596 return init(dbFile);
597 }
598 if (0==currentVersion || (schemaVersion>0 && schemaVersion!=constSchemaVersion)) {
599 QSqlQuery(*db).exec("delete from versions");
600 QSqlQuery(*db).exec("insert into versions (collection, schema) values(0, "+QString::number(constSchemaVersion)+")");
601 }
602 DBUG << "Current version" << currentVersion;
603
604 // NOTE: The order here MUST match SongFields enum above!!!
605 if (createTable("songs ("
606 "file text, "
607 "artist text, "
608 "artistId text, "
609 "albumArtist text, "
610 "artistSort text, "
611 "composer text, "
612 "album text, "
613 "albumId text, "
614 "albumSort text, "
615 "title text, "
616 "genre1 text, "
617 "genre2 text, "
618 "genre3 text, "
619 "genre4 text, "
620 "track integer, "
621 "disc integer, "
622 "time integer, "
623 "year integer, "
624 "origYear integer, "
625 "type integer, "
626 "lastModified integer, "
627 "primary key (file))")) {
628 QSqlQuery fts(*db);
629 if (!fts.exec("create virtual table if not exists songs_fts using fts4(fts_artist, fts_artistId, fts_album, fts_albumId, fts_title, tokenize=unicode61)")) {
630 DBUG << "Failed to create FTS table" << fts.lastError().text() << "trying again with simple tokenizer";
631 if (!fts.exec("create virtual table if not exists songs_fts using fts4(fts_artist, fts_artistId, fts_album, fts_albumId, fts_title, tokenize=simple)")) {
632 DBUG << "Failed to create FTS table" << fts.lastError().text();
633 }
634 }
635 } else {
636 DBUG << "Failed to create songs table";
637 return false;
638 }
639 emit libraryUpdated();
640 DBUG << "Created";
641 return true;
642 }
643
insertSong(const Song & s)644 void LibraryDb::insertSong(const Song &s)
645 {
646 if (!insertSongQuery) {
647 insertSongQuery=new QSqlQuery(*db);
648 insertSongQuery->prepare("insert into songs(file, artist, artistId, albumArtist, artistSort, composer, album, albumId, albumSort, title, genre1, genre2, genre3, genre4, track, disc, time, year, origYear, type, lastModified) "
649 "values(:file, :artist, :artistId, :albumArtist, :artistSort, :composer, :album, :albumId, :albumSort, :title, :genre1, :genre2, :genre3, :genre4, :track, :disc, :time, :year, :origYear, :type, :lastModified)");
650 }
651 QString albumId=s.albumId();
652 insertSongQuery->bindValue(":file", s.file);
653 insertSongQuery->bindValue(":artist", s.artist);
654 insertSongQuery->bindValue(":artistId", s.albumArtistOrComposer());
655 insertSongQuery->bindValue(":albumArtist", s.albumartist);
656 insertSongQuery->bindValue(":artistSort", artistSort(s));
657 insertSongQuery->bindValue(":composer", s.composer());
658 insertSongQuery->bindValue(":album", s.album==albumId ? QString() : s.album);
659 insertSongQuery->bindValue(":albumId", albumId);
660 insertSongQuery->bindValue(":albumSort", albumSort(s));
661 insertSongQuery->bindValue(":title", s.title);
662 for (int i=0; i<Song::constNumGenres; ++i) {
663 insertSongQuery->bindValue(":genre"+QString::number(i+1), s.genres[i].isEmpty() ? constNullGenre : s.genres[i]);
664 }
665 insertSongQuery->bindValue(":track", s.track);
666 insertSongQuery->bindValue(":disc", s.disc);
667 insertSongQuery->bindValue(":time", s.time);
668 insertSongQuery->bindValue(":year", s.year);
669 insertSongQuery->bindValue(":origYear", s.origYear);
670 insertSongQuery->bindValue(":type", s.type);
671 insertSongQuery->bindValue(":lastModified", s.lastModified);
672 if (!insertSongQuery->exec()) {
673 qWarning() << "insert failed" << insertSongQuery->lastError().text() << newVersion << s.file;
674 }
675 }
676
getGenres()677 QList<LibraryDb::Genre> LibraryDb::getGenres()
678 {
679 DBUG;
680 QMap<QString, QSet<QString> > map;
681 if (0!=currentVersion && db) {
682 QString queryStr("distinct ");
683 for (int i=0; i<Song::constNumGenres; ++i) {
684 queryStr+="genre"+QString::number(i+1)+", ";
685 }
686 queryStr+="artistId";
687 SqlQuery query(queryStr, *db);
688 query.setFilter(filter, yearFilter);
689
690 query.exec();
691 DBUG << query.executedQuery();
692 while (query.next()) {
693 for (int i=0; i<Song::constNumGenres; ++i) {
694 QString genre=query.value(i).toString();
695 if (!genre.isEmpty() && genre!=constNullGenre) {
696 map[genre].insert(query.value(Song::constNumGenres).toString());
697 }
698 }
699 }
700 }
701
702 QList<LibraryDb::Genre> genres;
703 QMap<QString, QSet<QString> >::ConstIterator it=map.constBegin();
704 QMap<QString, QSet<QString> >::ConstIterator end=map.constEnd();
705 for (; it!=end; ++it) {
706 DBUG << it.key();
707 genres.append(Genre(it.key(), it.value().size()));
708 }
709 std::sort(genres.begin(), genres.end());
710 return genres;
711 }
712
getArtists(const QString & genre)713 QList<LibraryDb::Artist> LibraryDb::getArtists(const QString &genre)
714 {
715 DBUG << genre;
716 QMap<QString, QString> sortMap;
717 QMap<QString, int> albumMap;
718 if (0!=currentVersion && db) {
719 SqlQuery query("distinct artistId, albumId, artistSort", *db);
720 query.setFilter(filter, yearFilter);
721 if (!genre.isEmpty()) {
722 query.addWhere("genre", genre);
723 } else if (!genreFilter.isEmpty()) {
724 query.addWhere("genre", genreFilter);
725 }
726 query.exec();
727 DBUG << query.executedQuery();
728 while (query.next()) {
729 QString artist=query.value(0).toString();
730 albumMap[artist]++;
731 sortMap[artist]=query.value(2).toString();
732 }
733 }
734
735 QList<LibraryDb::Artist> artists;
736 QMap<QString, int>::ConstIterator it=albumMap.constBegin();
737 QMap<QString, int>::ConstIterator end=albumMap.constEnd();
738 for (; it!=end; ++it) {
739 // DBUG << it.key();
740 artists.append(Artist(it.key(), sortMap[it.key()], it.value()));
741 }
742 std::sort(artists.begin(), artists.end());
743 return artists;
744 }
745
getAlbums(const QString & artistId,const QString & genre,AlbumSort sort)746 QList<LibraryDb::Album> LibraryDb::getAlbums(const QString &artistId, const QString &genre, AlbumSort sort)
747 {
748 timer.start();
749 DBUG << artistId << genre;
750 QList<Album> albums;
751 if (0!=currentVersion && db) {
752 bool wantModified=AS_Modified==sort;
753 bool wantArtist=artistId.isEmpty();
754 QString queryString="album, albumId, albumSort, artist, albumArtist, composer";
755 for (int i=0; i<Song::constNumGenres; ++i) {
756 queryString+=", genre"+QString::number(i+1);
757 }
758 queryString+=", type, year, origYear, time";
759 if (wantModified) {
760 queryString+=", lastModified";
761 }
762 if (wantArtist) {
763 queryString+=", artistId, artistSort";
764 }
765 SqlQuery query(queryString, *db);
766 query.setFilter(filter, yearFilter);
767 if (!artistId.isEmpty()) {
768 query.addWhere("artistId", artistId);
769 }
770 if (!genre.isEmpty()) {
771 query.addWhere("genre", genre);
772 } else if (!genreFilter.isEmpty()) {
773 query.addWhere("genre", genreFilter);
774 }
775 query.exec();
776 int count=0;
777 QMap<QString, Album> entries;
778 QMap<QString, QSet<QString> > albumIdArtists; // Map of albumId -> albumartists/composers
779 while (query.next()) {
780 count++;
781 int col=0;
782 QString album=query.value(col++).toString();
783 QString albumId=query.value(col++).toString();
784 QString albumSort=query.value(col++).toString();
785
786 Song s;
787 s.artist=query.value(col++).toString();
788 s.albumartist=query.value(col++).toString();
789 s.setComposer(query.value(col++).toString());
790 s.album=album.isEmpty() ? albumId : album;
791 for (int i=0; i<Song::constNumGenres; ++i) {
792 QString genre=query.value(col++).toString();
793 if (genre!=constNullGenre) {
794 s.addGenre(genre);
795 }
796 }
797 s.type=(Song::Type)query.value(col++).toInt();
798 s.year=query.value(col++).toInt();
799 s.origYear=query.value(col++).toInt();
800 if (Song::SingleTracks==s.type) {
801 s.album=Song::singleTracks();
802 s.albumartist=Song::variousArtists();
803 s.year = s.origYear = 0;
804 }
805 album=s.albumName();
806 int time=query.value(col++).toInt();
807 int lastModified=wantModified ? query.value(col++).toInt() : 0;
808 QString artist=wantArtist ? query.value(col++).toString() : QString();
809 QString artistSort=wantArtist ? query.value(col++).toString() : QString();
810 // If listing albums not filtered on artist, then if we have a unqique id for the album use that.
811 // This will allow us to grouup albums with different composers when the composer tweak is set
812 // Issue #1025
813 bool haveUniqueId=wantArtist && !albumId.isEmpty() && !album.isEmpty() && albumId!=album;
814 QString key=haveUniqueId ? albumId : ('{'+albumId+"}{"+(wantArtist ? artist : artistId)+'}');
815 QMap<QString, Album>::iterator it=entries.find(key);
816
817 if (it==entries.end()) {
818 entries.insert(key, Album(album.isEmpty() ? albumId : album, albumId, albumSort, artist, artistSort, s.displayYear(), 1, time, lastModified, haveUniqueId));
819 } else {
820 Album &al=it.value();
821 if (wantModified) {
822 al.lastModified=qMax(al.lastModified, lastModified);
823 }
824 al.year=qMax(al.year, (int)s.displayYear());
825 al.duration+=time;
826 al.trackCount++;
827 }
828 if (haveUniqueId) {
829 QMap<QString, QSet<QString> >::iterator aIt = albumIdArtists.find(key);
830 if (aIt == albumIdArtists.end()) {
831 albumIdArtists.insert(key, QSet<QString>() << artist);
832 } else {
833 aIt.value().insert(artist);
834 }
835 }
836 }
837
838 QMap<QString, QSet<QString> >::ConstIterator aIt = albumIdArtists.constBegin();
839 QMap<QString, QSet<QString> >::ConstIterator aEnd = albumIdArtists.constEnd();
840 for(; aIt!=aEnd; ++aIt) {
841 if (aIt.value().count()>1) {
842 QStringList artists = aIt.value().toList();
843 artists.sort();
844 Album &al = entries.find(aIt.key()).value();
845 al.artist = artists.join(", ");
846 al.artistSort = QString();
847 }
848 }
849
850 albums=entries.values();
851 DBUG << count << albums.count();
852 }
853
854 DBUG << "After select" << timer.elapsed();
855 switch(sort) {
856 case AS_AlArYr:
857 std::sort(albums.begin(), albums.end(), albumsSortAlArYr);
858 break;
859 case AS_AlYrAr:
860 std::sort(albums.begin(), albums.end(), albumsSortAlYrAr);
861 break;
862 case AS_ArAlYr:
863 std::sort(albums.begin(), albums.end(), albumsSortArAlYr);
864 break;
865 case AS_ArYrAl:
866 std::sort(albums.begin(), albums.end(), albumsSortArYrAl);
867 break;
868 case AS_YrAlAr:
869 std::sort(albums.begin(), albums.end(), albumsSortYrAlAr);
870 break;
871 case AS_YrArAl:
872 std::sort(albums.begin(), albums.end(), albumsSortYrArAl);
873 break;
874 case AS_Modified:
875 std::sort(albums.begin(), albums.end(), albumsSortModified);
876 break;
877 default:
878 break;
879 }
880 DBUG << "After sort" << timer.elapsed();
881 return albums;
882 }
883
getTracks(const QString & artistId,const QString & albumId,const QString & genre,AlbumSort sort,bool useFilter)884 QList<Song> LibraryDb::getTracks(const QString &artistId, const QString &albumId, const QString &genre, AlbumSort sort, bool useFilter)
885 {
886 DBUG << artistId << albumId << genre << sort;
887 QList<Song> songs;
888 if (0!=currentVersion && db) {
889 SqlQuery query("*", *db);
890 if (useFilter) {
891 query.setFilter(filter, yearFilter);
892 }
893 if (!artistId.isEmpty()) {
894 query.addWhere("artistId", artistId);
895 }
896 if (!albumId.isEmpty()) {
897 query.addWhere("albumId", albumId);
898 }
899 if (!genre.isEmpty()) {
900 query.addWhere("genre", genre);
901 } else if (useFilter && !genreFilter.isEmpty()) {
902 query.addWhere("genre", genreFilter);
903 }
904 query.exec();
905 DBUG << query.executedQuery();
906 while (query.next()) {
907 songs.append(getSong(query.realQuery()));
908 }
909 }
910
911 switch(sort) {
912 case AS_AlArYr:
913 std::sort(songs.begin(), songs.end(), songsSortAlAr);
914 break;
915 case AS_ArAlYr:
916 std::sort(songs.begin(), songs.end(), songsSortArAl);
917 break;
918 // case AS_Year:
919 // std::sort(songs.begin(), songs.end(), songsSortYrAlAr);
920 // break;
921 // case AS_Modified:
922 // std::sort(songs.begin(), songs.end(), songsSortModified);
923 // break;
924 default:
925 std::sort(songs.begin(), songs.end(), songSort);
926 break;
927 }
928 return songs;
929 }
930
getTracks(int rowFrom,int count)931 QList<Song> LibraryDb::getTracks(int rowFrom, int count)
932 {
933 QList<Song> songList;
934 if (db) {
935 SqlQuery query("*", *db);
936 query.addWhere("rowid", rowFrom, ">");
937 query.addWhere("rowid", rowFrom+count, "<=");
938 query.addWhere("type", 0);
939 query.exec();
940 DBUG << query.executedQuery();
941 while (query.next()) {
942 songList.append(getSong(query.realQuery()));
943 }
944 }
945 return songList;
946 }
947
trackCount()948 int LibraryDb::trackCount()
949 {
950 if (!db) {
951 return 0;
952 }
953 SqlQuery query("(count())", *db);
954 query.addWhere("type", 0);
955 query.exec();
956 DBUG << query.executedQuery();
957 int numTracks=query.next() ? query.value(0).toInt() : 0;
958 DBUG << numTracks;
959 return numTracks;
960 }
961
songs(const QStringList & files,bool allowPlaylists) const962 QList<Song> LibraryDb::songs(const QStringList &files, bool allowPlaylists) const
963 {
964 QList<Song> songList;
965 if (0!=currentVersion && db) {
966 for (const QString &f: files) {
967 SqlQuery query("*", *db);
968 query.addWhere("file", f);
969 query.exec();
970 DBUG << query.executedQuery();
971 if (query.next()) {
972 Song song=getSong(query.realQuery());
973 if (allowPlaylists || Song::Playlist!=song.type) {
974 songList.append(song);
975 }
976 }
977 }
978 }
979
980 return songList;
981 }
982
getAlbumsWithArtistOrComposer(const QString & artist)983 QList<LibraryDb::Album> LibraryDb::getAlbumsWithArtistOrComposer(const QString &artist)
984 {
985 QList<LibraryDb::Album> albums;
986 if (0!=currentVersion && db) {
987 SqlQuery query("distinct album, albumId, albumSort", *db);
988 query.addWhere("artist", artist);
989 query.exec();
990 DBUG << query.executedQuery();
991 while (query.next()) {
992 QString album=query.value(0).toString();
993 QString albumId=query.value(1).toString();
994 albums.append(Album(album.isEmpty() ? albumId : album, albumId, query.value(2).toString(), artist));
995 }
996 }
997
998 if (albums.isEmpty()) {
999 // No artist albums? Try composer...
1000 SqlQuery query("distinct album, albumId, albumSort", *db);
1001 query.addWhere("composer", artist);
1002 query.exec();
1003 DBUG << query.executedQuery();
1004 while (query.next()) {
1005 QString album=query.value(0).toString();
1006 QString albumId=query.value(1).toString();
1007 albums.append(Album(album.isEmpty() ? albumId : album, albumId, query.value(2).toString(), artist));
1008 }
1009 }
1010
1011 std::sort(albums.begin(), albums.end(), albumsSortArAlYr);
1012
1013 return albums;
1014 }
1015
getRandomAlbum(const QString & genre,const QString & artist)1016 LibraryDb::Album LibraryDb::getRandomAlbum(const QString &genre, const QString &artist)
1017 {
1018 Album al;
1019 if (0!=currentVersion && db) {
1020 SqlQuery query("artistId, albumId", *db);
1021 query.setOrder("random()");
1022 query.setLimit(1);
1023 if (!artist.isEmpty()) {
1024 query.addWhere("artistId", artist);
1025 }
1026 if (!genre.isEmpty()) {
1027 query.addWhere("genre", genre);
1028 } else if (!genreFilter.isEmpty()) {
1029 query.addWhere("genre", genreFilter);
1030 }
1031 if (!yearFilter.isEmpty()) {
1032 query.addWhere("year", yearFilter);
1033 }
1034 query.exec();
1035 DBUG << query.executedQuery();
1036 if (query.next()) {
1037 al.artist=query.value(0).toString();
1038 al.id=query.value(1).toString();
1039 }
1040 }
1041 return al;
1042 }
1043
getRandomAlbum(const QStringList & genres,const QStringList & artists)1044 LibraryDb::Album LibraryDb::getRandomAlbum(const QStringList &genres, const QStringList &artists)
1045 {
1046 if (genres.isEmpty() && artists.isEmpty()) {
1047 return getRandomAlbum(QString(), QString());
1048 }
1049
1050 QList<Album> albums;
1051
1052 for (const QString &genre: genres) {
1053 albums.append(getRandomAlbum(genre, QString()));
1054 }
1055
1056 for (const QString &artist: artists) {
1057 albums.append(getRandomAlbum(QString(), artist));
1058 }
1059
1060 if (albums.isEmpty()) {
1061 return Album();
1062 }
1063
1064 return albums.at(QRandomGenerator::global()->bounded(albums.count()));
1065 }
1066
get(const QString & type)1067 QSet<QString> LibraryDb::get(const QString &type)
1068 {
1069 if (detailsCache.contains(type)) {
1070 return detailsCache[type];
1071 }
1072 QSet<QString> set;
1073 if (!db) {
1074 return set;
1075 }
1076
1077 QStringList columns;
1078 bool isGenre="genre"==type;
1079 bool isAlbum="album"==type;
1080
1081 if (isGenre) {
1082 for (int i=0; i<Song::constNumGenres; ++i) {
1083 columns << type+QString::number(i+1);
1084 }
1085 } else if (isAlbum) {
1086 columns << type << "albumId";
1087 } else {
1088 columns << type;
1089 }
1090
1091 for (const QString &col: columns) {
1092 SqlQuery query("distinct "+col, *db);
1093 query.exec();
1094 DBUG << query.executedQuery();
1095 while (query.next()) {
1096 if (isGenre) {
1097 for (int i=0; i<Song::constNumGenres; ++i) {
1098 QString val=query.value(i).toString();
1099 if (!val.isEmpty() && constNullGenre!=val) {
1100 set.insert(val);
1101 }
1102 }
1103 } else if (isAlbum) {
1104 // For albums, we have album and albumId
1105 // album is only stored if it is different to albumId - in this case albumId would be the musicbrainz id
1106 QString val=query.value(0).toString();
1107 if (val.isEmpty()) {
1108 val=query.value(1).toString();
1109 }
1110 if (!val.isEmpty()) {
1111 set.insert(val);
1112 }
1113 } else {
1114 QString val=query.value(0).toString();
1115 if (!val.isEmpty()) {
1116 set.insert(val);
1117 }
1118 }
1119 }
1120 }
1121 detailsCache[type]=set;
1122 return set;
1123 }
1124
getDetails(QSet<QString> & artists,QSet<QString> & albumArtists,QSet<QString> & composers,QSet<QString> & albums,QSet<QString> & genres)1125 void LibraryDb::getDetails(QSet<QString> &artists, QSet<QString> &albumArtists, QSet<QString> &composers, QSet<QString> &albums, QSet<QString> &genres)
1126 {
1127 artists=get("artist");
1128 albumArtists=get("albumArtist");
1129 composers=get("composer");
1130 albums=get("album");
1131 genres=get("genre");
1132 }
1133
songExists(const Song & song)1134 bool LibraryDb::songExists(const Song &song)
1135 {
1136 if (!db) {
1137 return false;
1138 }
1139 SqlQuery query("file", *db);
1140 query.addWhere("artistId", song.albumArtistOrComposer());
1141 query.addWhere("albumId", song.albumId());
1142 query.addWhere("title", song.title);
1143 query.addWhere("track", song.track);
1144 query.addWhere("disc", song.disc);
1145 query.exec();
1146 return query.next();
1147 }
1148
1149 static const quint16 constMinYear=1500;
1150 static const quint16 constMaxYear=2500; // 2500 (bit hopeful here :-) )
1151
setFilter(const QString & f,const QString & genre)1152 bool LibraryDb::setFilter(const QString &f, const QString &genre)
1153 {
1154 QString newFilter=f.trimmed().toLower();
1155 QString year;
1156 if (!f.isEmpty()) {
1157 QStringList strings(newFilter.split(QRegExp("\\s+")));
1158 static QList<QLatin1Char> replaceChars=QList<QLatin1Char>() << QLatin1Char('(') << QLatin1Char(')') << QLatin1Char('"')
1159 << QLatin1Char(':') << QLatin1Char('-') << QLatin1Char('#');
1160 QStringList tokens;
1161 for (QString str: strings) {
1162 if (str.startsWith('#')) {
1163 QStringList parts=str.mid(1).split('-');
1164 if (1==parts.length()) {
1165 int val=parts.at(0).simplified().toUInt();
1166 if (val>=constMinYear && val<=constMaxYear) {
1167 if (Song::useOriginalYear()) {
1168 year = QString("( (origYear = %1) OR (origYear = 0 AND year = %1) )").arg(val);
1169 } else {
1170 year = QString("year = %1").arg(val);
1171 }
1172 continue;
1173 }
1174 } else if (2==parts.length()) {
1175 int from=parts.at(0).simplified().toUInt();
1176 int to=parts.at(1).simplified().toUInt();
1177 if (from>=constMinYear && from<=constMaxYear && to>=constMinYear && to<=constMaxYear) {
1178 if (Song::useOriginalYear()) {
1179 year = QString("( (origYear >= %1 AND origYear <= %2) OR (origYear = 0 AND year >= %1 AND year <= %2))")
1180 .arg(qMin(from, to)).arg(qMax(from, to));
1181 } else {
1182 year = QString("year >= %1 AND year <= %2").arg(qMin(from, to)).arg(qMax(from, to));
1183 }
1184 continue;
1185 }
1186 }
1187 }
1188 for (const QLatin1Char ch: replaceChars) {
1189 str.replace(ch, '?');
1190 }
1191 if (str.length()>0) {
1192 tokens.append(str+QLatin1String("*"));
1193 }
1194 }
1195 newFilter=tokens.join(" ");
1196 DBUG << newFilter;
1197 }
1198 bool modified=newFilter!=filter || genre!=genreFilter || year!=yearFilter;
1199 filter=newFilter;
1200 genreFilter=genre;
1201 yearFilter=year;
1202 return modified;
1203 }
1204
updateStarted(time_t ver)1205 void LibraryDb::updateStarted(time_t ver)
1206 {
1207 DBUG << (void *)db;
1208 if (!db) {
1209 return;
1210 }
1211 newVersion=ver;
1212 timer.start();
1213 db->transaction();
1214 if (currentVersion>0) {
1215 clearSongs(false);
1216 }
1217 }
1218
insertSongs(QList<Song> * songs)1219 void LibraryDb::insertSongs(QList<Song> *songs)
1220 {
1221 DBUG << (int)(songs ? songs->size() : -1);
1222 if (!songs) {
1223 return;
1224 }
1225
1226 for (const Song &s: *songs) {
1227 insertSong(s);
1228 }
1229 delete songs;
1230 }
1231
updateFinished()1232 void LibraryDb::updateFinished()
1233 {
1234 if (!db) {
1235 return;
1236 }
1237 DBUG << timer.elapsed();
1238 DBUG << "update fts" << timer.elapsed();
1239 QSqlQuery(*db).exec("insert into songs_fts(fts_artist, fts_artistId, fts_album, fts_albumId, fts_title) "
1240 "select artist, artistId, album, albumId, title from songs");
1241 QSqlQuery(*db).exec("update versions set collection ="+QString::number(newVersion));
1242 DBUG << "commit" << timer.elapsed();
1243 db->commit();
1244 currentVersion=newVersion;
1245 DBUG << "complete" << timer.elapsed();
1246 emit libraryUpdated();
1247 }
1248
abortUpdate()1249 void LibraryDb::abortUpdate()
1250 {
1251 if (db) {
1252 db->rollback();
1253 }
1254 }
1255
createTable(const QString & q)1256 bool LibraryDb::createTable(const QString &q)
1257 {
1258 if (!db) {
1259 return false;
1260 }
1261 QSqlQuery query(*db);
1262 if (!query.exec("create table if not exists "+q)) {
1263 qWarning() << "Failed to create table" << query.lastError().text();
1264 return false;
1265 }
1266 return true;
1267 }
1268
getSong(const QSqlQuery & query)1269 Song LibraryDb::getSong(const QSqlQuery &query)
1270 {
1271 Song s;
1272 s.file=query.value(SF_file).toString();
1273 s.artist=query.value(SF_artist).toString();
1274 s.albumartist=query.value(SF_albumArtist).toString();
1275 s.setComposer(query.value(SF_composer).toString());
1276 s.album=query.value(SF_album).toString();
1277 QString val=query.value(SF_albumId).toString();
1278 if (s.album.isEmpty()) {
1279 s.album=val;
1280 val=QString();
1281 }
1282 if (!val.isEmpty() && val!=s.album) {
1283 s.setMbAlbumId(val);
1284 }
1285 s.title=query.value(SF_title).toString();
1286 for (int i=SF_genre1; i<SF_genre1+Song::constNumGenres; ++i) {
1287 QString genre=query.value(i).toString();
1288 if (genre!=constNullGenre) {
1289 s.addGenre(genre);
1290 }
1291 }
1292 s.track=query.value(SF_track).toUInt();
1293 s.disc=query.value(SF_disc).toUInt();
1294 s.time=query.value(SF_time).toUInt();
1295 s.year=query.value(SF_year).toUInt();
1296 s.origYear=query.value(SF_origYear).toUInt();
1297 s.type=(Song::Type)query.value(SF_type).toUInt();
1298 val=query.value(SF_artistSort).toString();
1299 if (!val.isEmpty() && val!=s.albumArtist()) {
1300 s.setArtistSort(val);
1301 }
1302 val=query.value(SF_albumSort).toString();
1303 if (!val.isEmpty() && val!=s.album) {
1304 s.setAlbumSort(val);
1305 }
1306 s.lastModified=query.value(SF_lastModified).toUInt();
1307
1308 return s;
1309 }
1310
reset()1311 void LibraryDb::reset()
1312 {
1313 bool removeDb=nullptr!=db;
1314 delete insertSongQuery;
1315 if (db) {
1316 db->close();
1317 }
1318 delete db;
1319
1320 insertSongQuery=nullptr;
1321 db=nullptr;
1322 if (removeDb) {
1323 QSqlDatabase::removeDatabase(dbName);
1324 }
1325 }
1326
clearSongs(bool startTransaction)1327 void LibraryDb::clearSongs(bool startTransaction)
1328 {
1329 if (!db) {
1330 return;
1331 }
1332 if (startTransaction) {
1333 db->transaction();
1334 }
1335 QSqlQuery(*db).exec("delete from songs");
1336 QSqlQuery(*db).exec("delete from songs_fts");
1337 detailsCache.clear();
1338 if (startTransaction) {
1339 db->commit();
1340 }
1341 }
1342
1343 #include "moc_librarydb.cpp"
1344