1 /****************************************************************************************
2 * Copyright (c) 2007 Maximilian Kossick <maximilian.kossick@googlemail.com> *
3 * Copyright (c) 2008 Daniel Winter <dw@danielwinter.de> *
4 * *
5 * This program is free software; you can redistribute it and/or modify it under *
6 * the terms of the GNU General Public License as published by the Free Software *
7 * Foundation; either version 2 of the License, or (at your option) any later *
8 * version. *
9 * *
10 * This program is distributed in the hope that it will be useful, but WITHOUT ANY *
11 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A *
12 * PARTICULAR PURPOSE. See the GNU General Public License for more details. *
13 * *
14 * You should have received a copy of the GNU General Public License along with *
15 * this program. If not, see <http://www.gnu.org/licenses/>. *
16 ****************************************************************************************/
17
18 #define DEBUG_PREFIX "SqlQueryMaker"
19
20 #include "SqlQueryMaker.h"
21
22 #include "SqlCollection.h"
23 #include "SqlQueryMakerInternal.h"
24 #include <core/storage/SqlStorage.h>
25 #include "core/support/Debug.h"
26 #include "core-impl/collections/db/MountPointManager.h"
27
28 #include <QWeakPointer>
29 #include <QStack>
30
31 #include <ThreadWeaver/Job>
32 #include <ThreadWeaver/ThreadWeaver>
33 #include <ThreadWeaver/Queue>
34
35 using namespace Collections;
36
37 class SqlWorkerThread : public QObject, public ThreadWeaver::Job
38 {
39 Q_OBJECT
40 public:
SqlWorkerThread(SqlQueryMakerInternal * queryMakerInternal)41 SqlWorkerThread( SqlQueryMakerInternal *queryMakerInternal )
42 : QObject()
43 , ThreadWeaver::Job()
44 , m_queryMakerInternal( queryMakerInternal )
45 , m_aborted( false )
46 {
47 //nothing to do
48 }
49
~SqlWorkerThread()50 ~SqlWorkerThread() override
51 {
52 delete m_queryMakerInternal;
53 }
54
requestAbort()55 void requestAbort() override
56 {
57 m_aborted = true;
58 }
59
queryMakerInternal() const60 SqlQueryMakerInternal* queryMakerInternal() const
61 {
62 return m_queryMakerInternal;
63 }
64
65 protected:
run(ThreadWeaver::JobPointer self=QSharedPointer<ThreadWeaver::Job> (),ThreadWeaver::Thread * thread=0)66 void run(ThreadWeaver::JobPointer self = QSharedPointer<ThreadWeaver::Job>(), ThreadWeaver::Thread *thread = 0) override
67 {
68 Q_UNUSED(self);
69 Q_UNUSED(thread);
70 m_queryMakerInternal->run();
71 if( m_aborted )
72 setStatus(Status_Aborted);
73 else
74 setStatus(Status_Running);
75 }
defaultBegin(const ThreadWeaver::JobPointer & self,ThreadWeaver::Thread * thread)76 void defaultBegin(const ThreadWeaver::JobPointer& self, ThreadWeaver::Thread *thread) override
77 {
78 Q_EMIT started(self);
79 ThreadWeaver::Job::defaultBegin(self, thread);
80 }
81
defaultEnd(const ThreadWeaver::JobPointer & self,ThreadWeaver::Thread * thread)82 void defaultEnd(const ThreadWeaver::JobPointer& self, ThreadWeaver::Thread *thread) override
83 {
84 ThreadWeaver::Job::defaultEnd(self, thread);
85 if (!self->success()) {
86 Q_EMIT failed(self);
87 }
88 Q_EMIT done(self);
89 }
90
91 private:
92 SqlQueryMakerInternal *m_queryMakerInternal;
93
94 bool m_aborted;
95 Q_SIGNALS:
96 /** This signal is emitted when this job is being processed by a thread. */
97 void started(ThreadWeaver::JobPointer);
98 /** This signal is emitted when the job has been finished (no matter if it succeeded or not). */
99 void done(ThreadWeaver::JobPointer);
100 /** This job has failed.
101 * This signal is emitted when success() returns false after the job is executed. */
102 void failed(ThreadWeaver::JobPointer);
103 };
104
105 struct SqlQueryMaker::Private
106 {
107 enum { TAGS_TAB = 1, ARTIST_TAB = 2, ALBUM_TAB = 4, GENRE_TAB = 8, COMPOSER_TAB = 16, YEAR_TAB = 32, STATISTICS_TAB = 64, URLS_TAB = 128, ALBUMARTIST_TAB = 256, LABELS_TAB = 1024 };
108 int linkedTables;
109 QueryMaker::QueryType queryType;
110 QString query;
111 QString queryReturnValues;
112 QString queryFrom;
113 QString queryMatch;
114 QString queryFilter;
115 QString queryOrderBy;
116 bool withoutDuplicates;
117 int maxResultSize;
118 AlbumQueryMode albumMode;
119 LabelQueryMode labelMode;
120 SqlWorkerThread *worker;
121
122 QStack<bool> andStack;
123
124 QStringList blockingCustomData;
125 Meta::TrackList blockingTracks;
126 Meta::AlbumList blockingAlbums;
127 Meta::ArtistList blockingArtists;
128 Meta::GenreList blockingGenres;
129 Meta::ComposerList blockingComposers;
130 Meta::YearList blockingYears;
131 Meta::LabelList blockingLabels;
132 bool blocking;
133 bool used;
134 qint64 returnValueType;
135 };
136
SqlQueryMaker(SqlCollection * collection)137 SqlQueryMaker::SqlQueryMaker( SqlCollection* collection )
138 : QueryMaker()
139 , m_collection( collection )
140 , d( new Private )
141 {
142 d->worker = 0;
143 d->queryType = QueryMaker::None;
144 d->linkedTables = 0;
145 d->withoutDuplicates = false;
146 d->albumMode = AllAlbums;
147 d->labelMode = QueryMaker::NoConstraint;
148 d->maxResultSize = -1;
149 d->andStack.clear();
150 d->andStack.push( true ); //and is default
151 d->blocking = false;
152 d->used = false;
153 d->returnValueType = 0;
154 }
155
~SqlQueryMaker()156 SqlQueryMaker::~SqlQueryMaker()
157 {
158 disconnect();
159 abortQuery();
160 if( d->worker )
161 {
162 d->worker->deleteLater();
163 }
164 delete d;
165 }
166
167 void
abortQuery()168 SqlQueryMaker::abortQuery()
169 {
170 if( d->worker )
171 {
172 d->worker->requestAbort();
173 d->worker->disconnect( this );
174 if( d->worker->queryMakerInternal() )
175 d->worker->queryMakerInternal()->disconnect( this );
176 }
177 }
178
179 void
run()180 SqlQueryMaker::run()
181 {
182 if( d->queryType == QueryMaker::None || (d->blocking && d->used) )
183 {
184 debug() << "sql querymaker used without reset or initialization" << endl;
185 return; //better error handling?
186 }
187 if( d->worker && !d->worker->isFinished() )
188 {
189 //the worker thread seems to be running
190 //TODO: wait or job to complete
191
192 }
193 else
194 {
195 SqlQueryMakerInternal *qmi = new SqlQueryMakerInternal( m_collection );
196 qmi->setQuery( query() );
197 qmi->setQueryType( d->queryType );
198
199 if ( !d->blocking )
200 {
201 connect( qmi, &Collections::SqlQueryMakerInternal::newAlbumsReady, this, &SqlQueryMaker::newAlbumsReady, Qt::DirectConnection );
202 connect( qmi, &Collections::SqlQueryMakerInternal::newArtistsReady, this, &SqlQueryMaker::newArtistsReady, Qt::DirectConnection );
203 connect( qmi, &Collections::SqlQueryMakerInternal::newGenresReady, this, &SqlQueryMaker::newGenresReady, Qt::DirectConnection );
204 connect( qmi, &Collections::SqlQueryMakerInternal::newComposersReady, this, &SqlQueryMaker::newComposersReady, Qt::DirectConnection );
205 connect( qmi, &Collections::SqlQueryMakerInternal::newYearsReady, this, &SqlQueryMaker::newYearsReady, Qt::DirectConnection );
206 connect( qmi, &Collections::SqlQueryMakerInternal::newTracksReady, this, &SqlQueryMaker::newTracksReady, Qt::DirectConnection );
207 connect( qmi, &Collections::SqlQueryMakerInternal::newResultReady, this, &SqlQueryMaker::newResultReady, Qt::DirectConnection );
208 connect( qmi, &Collections::SqlQueryMakerInternal::newLabelsReady, this, &SqlQueryMaker::newLabelsReady, Qt::DirectConnection );
209 d->worker = new SqlWorkerThread( qmi );
210 connect( d->worker, &SqlWorkerThread::done, this, &SqlQueryMaker::done );
211 ThreadWeaver::Queue::instance()->enqueue( QSharedPointer<ThreadWeaver::Job>(d->worker) );
212 }
213 else //use it blocking
214 {
215 connect( qmi, &Collections::SqlQueryMakerInternal::newAlbumsReady, this, &SqlQueryMaker::blockingNewAlbumsReady, Qt::DirectConnection );
216 connect( qmi, &Collections::SqlQueryMakerInternal::newArtistsReady, this, &SqlQueryMaker::blockingNewArtistsReady, Qt::DirectConnection );
217 connect( qmi, &Collections::SqlQueryMakerInternal::newGenresReady, this, &SqlQueryMaker::blockingNewGenresReady, Qt::DirectConnection );
218 connect( qmi, &Collections::SqlQueryMakerInternal::newComposersReady, this, &SqlQueryMaker::blockingNewComposersReady, Qt::DirectConnection );
219 connect( qmi, &Collections::SqlQueryMakerInternal::newYearsReady, this, &SqlQueryMaker::blockingNewYearsReady, Qt::DirectConnection );
220 connect( qmi, &Collections::SqlQueryMakerInternal::newTracksReady, this, &SqlQueryMaker::blockingNewTracksReady, Qt::DirectConnection );
221 connect( qmi, &Collections::SqlQueryMakerInternal::newResultReady, this, &SqlQueryMaker::blockingNewResultReady, Qt::DirectConnection );
222 connect( qmi, &Collections::SqlQueryMakerInternal::newLabelsReady, this, &SqlQueryMaker::blockingNewLabelsReady, Qt::DirectConnection );
223 qmi->run();
224 delete qmi;
225 }
226 }
227 d->used = true;
228 }
229
230 void
done(ThreadWeaver::JobPointer job)231 SqlQueryMaker::done( ThreadWeaver::JobPointer job )
232 {
233 Q_UNUSED( job )
234
235 d->worker = 0; // d->worker *is* the job, prevent stale pointer
236 Q_EMIT queryDone();
237 }
238
239 QueryMaker*
setQueryType(QueryType type)240 SqlQueryMaker::setQueryType( QueryType type )
241 {
242 // we need the unchanged m_queryType in the blocking result methods so prevent
243 // reseting queryType without reseting the QM
244 if ( d->blocking && d->used )
245 return this;
246
247 switch( type ) {
248 case QueryMaker::Track:
249 //make sure to keep this method in sync with handleTracks(QStringList) and the SqlTrack ctor
250 if( d->queryType == QueryMaker::None )
251 {
252 d->queryType = QueryMaker::Track;
253 d->linkedTables |= Private::URLS_TAB;
254 d->linkedTables |= Private::TAGS_TAB;
255 d->linkedTables |= Private::GENRE_TAB;
256 d->linkedTables |= Private::ARTIST_TAB;
257 d->linkedTables |= Private::ALBUM_TAB;
258 d->linkedTables |= Private::COMPOSER_TAB;
259 d->linkedTables |= Private::YEAR_TAB;
260 d->linkedTables |= Private::STATISTICS_TAB;
261 d->queryReturnValues = Meta::SqlTrack::getTrackReturnValues();
262 }
263 return this;
264
265 case QueryMaker::Artist:
266 if( d->queryType == QueryMaker::None )
267 {
268 d->queryType = QueryMaker::Artist;
269 d->withoutDuplicates = true;
270 d->linkedTables |= Private::ARTIST_TAB;
271 //reading the ids from the database means we don't have to query for them later
272 d->queryReturnValues = "artists.name, artists.id";
273 }
274 return this;
275
276 case QueryMaker::Album:
277 if( d->queryType == QueryMaker::None )
278 {
279 d->queryType = QueryMaker::Album;
280 d->withoutDuplicates = true;
281 d->linkedTables |= Private::ALBUM_TAB;
282 //add whatever is necessary to identify compilations
283 d->queryReturnValues = "albums.name, albums.id, albums.artist";
284 }
285 return this;
286
287 case QueryMaker::AlbumArtist:
288 if( d->queryType == QueryMaker::None )
289 {
290 d->queryType = QueryMaker::AlbumArtist;
291 d->withoutDuplicates = true;
292 d->linkedTables |= Private::ALBUMARTIST_TAB;
293 d->linkedTables |= Private::ALBUM_TAB;
294 d->queryReturnValues = "albumartists.name, albumartists.id";
295 }
296 return this;
297
298 case QueryMaker::Composer:
299 if( d->queryType == QueryMaker::None )
300 {
301 d->queryType = QueryMaker::Composer;
302 d->withoutDuplicates = true;
303 d->linkedTables |= Private::COMPOSER_TAB;
304 d->queryReturnValues = "composers.name, composers.id";
305 }
306 return this;
307
308 case QueryMaker::Genre:
309 if( d->queryType == QueryMaker::None )
310 {
311 d->queryType = QueryMaker::Genre;
312 d->withoutDuplicates = true;
313 d->linkedTables |= Private::GENRE_TAB;
314 d->queryReturnValues = "genres.name, genres.id";
315 }
316 return this;
317
318 case QueryMaker::Year:
319 if( d->queryType == QueryMaker::None )
320 {
321 d->queryType = QueryMaker::Year;
322 d->withoutDuplicates = true;
323 d->linkedTables |= Private::YEAR_TAB;
324 d->queryReturnValues = "years.name, years.id";
325 }
326 return this;
327
328 case QueryMaker::Custom:
329 if( d->queryType == QueryMaker::None )
330 d->queryType = QueryMaker::Custom;
331 return this;
332
333 case QueryMaker::Label:
334 if( d->queryType == QueryMaker::None )
335 {
336 d->queryType = QueryMaker::Label;
337 d->withoutDuplicates = true;
338 d->queryReturnValues = "labels.label,labels.id";
339 d->linkedTables |= Private::LABELS_TAB;
340 }
341 return this;
342
343 case QueryMaker::None:
344 return this;
345 }
346 return this;
347 }
348
349 QueryMaker*
addMatch(const Meta::TrackPtr & track)350 SqlQueryMaker::addMatch( const Meta::TrackPtr &track )
351 {
352 QString url = track->uidUrl();
353 if( !url.isEmpty() )
354 /*
355 QUrl kurl( url );
356 if( kurl.scheme() == "amarok-sqltrackuid" )
357 */
358 {
359 d->queryMatch += QStringLiteral( " AND urls.uniqueid = '%1' " ).arg( url /*kurl.url()*/ );
360 }
361 else
362 {
363 QString path;
364 /*
365 if( kurl.isLocalFile() )
366 {
367 path = kurl.path();
368 }
369 else
370 */
371 {
372 path = track->playableUrl().path();
373 }
374 int deviceid = m_collection->mountPointManager()->getIdForUrl( QUrl::fromUserInput(path) );
375 QString rpath = m_collection->mountPointManager()->getRelativePath( deviceid, path );
376 d->queryMatch += QString( " AND urls.deviceid = %1 AND urls.rpath = '%2'" )
377 .arg( QString::number( deviceid ), escape( rpath ) );
378 }
379 return this;
380 }
381
382
383 QueryMaker*
addMatch(const Meta::ArtistPtr & artist,ArtistMatchBehaviour behaviour)384 SqlQueryMaker::addMatch( const Meta::ArtistPtr &artist, ArtistMatchBehaviour behaviour )
385 {
386 d->linkedTables |= Private::ARTIST_TAB;
387 if( behaviour == AlbumArtists || behaviour == AlbumOrTrackArtists )
388 d->linkedTables |= Private::ALBUMARTIST_TAB;
389
390 QString artistQuery;
391 QString albumArtistQuery;
392
393 if( artist && !artist->name().isEmpty() )
394 {
395 artistQuery = QStringLiteral("artists.name = '%1'").arg( escape( artist->name() ) );
396 albumArtistQuery = QStringLiteral("albumartists.name = '%1'").arg( escape( artist->name() ) );
397 }
398 else
399 {
400 artistQuery = "( artists.name IS NULL OR artists.name = '')";
401 albumArtistQuery = "( albumartists.name IS NULL OR albumartists.name = '')";
402 }
403
404 switch( behaviour )
405 {
406 case TrackArtists:
407 d->queryMatch += " AND " + artistQuery;
408 break;
409 case AlbumArtists:
410 d->queryMatch += " AND " + albumArtistQuery;
411 break;
412 case AlbumOrTrackArtists:
413 d->queryMatch += " AND ( (" + artistQuery + " ) OR ( " + albumArtistQuery + " ) )";
414 break;
415 }
416 return this;
417 }
418
419 QueryMaker*
addMatch(const Meta::AlbumPtr & album)420 SqlQueryMaker::addMatch( const Meta::AlbumPtr &album )
421 {
422 d->linkedTables |= Private::ALBUM_TAB;
423
424 // handle singles
425 if( !album || album->name().isEmpty() )
426 d->queryMatch += QString( " AND ( albums.name IS NULL OR albums.name = '' )" );
427 else
428 d->queryMatch += QString( " AND albums.name = '%1'" ).arg( escape( album->name() ) );
429
430 if( album )
431 {
432 //handle compilations
433 Meta::ArtistPtr albumArtist = album->albumArtist();
434 if( albumArtist )
435 {
436 d->linkedTables |= Private::ALBUMARTIST_TAB;
437 d->queryMatch += QString( " AND albumartists.name = '%1'" ).arg( escape( albumArtist->name() ) );
438 }
439 else
440 {
441 d->queryMatch += " AND albums.artist IS NULL";
442 }
443 }
444 return this;
445 }
446
447 QueryMaker*
addMatch(const Meta::GenrePtr & genre)448 SqlQueryMaker::addMatch( const Meta::GenrePtr &genre )
449 {
450 d->linkedTables |= Private::GENRE_TAB;
451 d->queryMatch += QString( " AND genres.name = '%1'" ).arg( escape( genre->name() ) );
452 return this;
453 }
454
455 QueryMaker*
addMatch(const Meta::ComposerPtr & composer)456 SqlQueryMaker::addMatch( const Meta::ComposerPtr &composer )
457 {
458 d->linkedTables |= Private::COMPOSER_TAB;
459 d->queryMatch += QString( " AND composers.name = '%1'" ).arg( escape( composer->name() ) );
460 return this;
461 }
462
463 QueryMaker*
addMatch(const Meta::YearPtr & year)464 SqlQueryMaker::addMatch( const Meta::YearPtr &year )
465 {
466 // handle tracks without a year
467 if( !year )
468 {
469 d->queryMatch += " AND year IS NULL";
470 }
471 else
472 {
473 d->linkedTables |= Private::YEAR_TAB;
474 d->queryMatch += QString( " AND years.name = '%1'" ).arg( escape( year->name() ) );
475 }
476 return this;
477 }
478
479 QueryMaker*
addMatch(const Meta::LabelPtr & label)480 SqlQueryMaker::addMatch( const Meta::LabelPtr &label )
481 {
482 AmarokSharedPointer<Meta::SqlLabel> sqlLabel = AmarokSharedPointer<Meta::SqlLabel>::dynamicCast( label );
483 QString labelSubQuery;
484 if( sqlLabel )
485 {
486 labelSubQuery = "SELECT url FROM urls_labels WHERE label = %1";
487 labelSubQuery = labelSubQuery.arg( sqlLabel->id() );
488 }
489 else
490 {
491 labelSubQuery = "SELECT a.url FROM urls_labels a INNER JOIN labels b ON a.label = b.id WHERE b.label = '%1'";
492 labelSubQuery = labelSubQuery.arg( escape( label->name() ) );
493 }
494 d->linkedTables |= Private::TAGS_TAB;
495 QString match = " AND tracks.url in (%1)";
496 d->queryMatch += match.arg( labelSubQuery );
497 return this;
498 }
499
500 QueryMaker*
addFilter(qint64 value,const QString & filter,bool matchBegin,bool matchEnd)501 SqlQueryMaker::addFilter( qint64 value, const QString &filter, bool matchBegin, bool matchEnd )
502 {
503 // special case for albumartist...
504 if( value == Meta::valAlbumArtist && filter.isEmpty() )
505 {
506 d->linkedTables |= Private::ALBUMARTIST_TAB;
507 d->linkedTables |= Private::ALBUM_TAB;
508 d->queryFilter += QString( " %1 ( albums.artist IS NULL or albumartists.name = '') " ).arg( andOr() );
509 }
510 else if( value == Meta::valLabel )
511 {
512 d->linkedTables |= Private::TAGS_TAB;
513 QString like = likeCondition( filter, !matchBegin, !matchEnd );
514 QString filter = " %1 tracks.url IN (SELECT a.url FROM urls_labels a INNER JOIN labels b ON a.label = b.id WHERE b.label %2) ";
515 d->queryFilter += filter.arg( andOr(), like );
516 }
517 else
518 {
519 QString like = likeCondition( filter, !matchBegin, !matchEnd );
520 d->queryFilter += QString( " %1 %2 %3 " ).arg( andOr(), nameForValue( value ), like );
521 }
522 return this;
523 }
524
525 QueryMaker*
excludeFilter(qint64 value,const QString & filter,bool matchBegin,bool matchEnd)526 SqlQueryMaker::excludeFilter( qint64 value, const QString &filter, bool matchBegin, bool matchEnd )
527 {
528 // special case for album...
529 if( value == Meta::valAlbumArtist && filter.isEmpty() )
530 {
531 d->linkedTables |= Private::ALBUMARTIST_TAB;
532 d->queryFilter += QString( " %1 NOT ( albums.artist IS NULL or albumartists.name = '') " ).arg( andOr() );
533 }
534 else if( value == Meta::valLabel )
535 {
536 d->linkedTables |= Private::TAGS_TAB;
537 QString like = likeCondition( filter, !matchBegin, !matchEnd );
538 QString filter = " %1 tracks.url NOT IN (SELECT a.url FROM urls_labels a INNER JOIN labels b ON a.label = b.id WHERE b.label %2) ";
539 d->queryFilter += filter.arg( andOr(), like );
540 }
541 else
542 {
543 QString like = likeCondition( filter, !matchBegin, !matchEnd );
544 d->queryFilter += QString( " %1 NOT %2 %3 " ).arg( andOr(), nameForValue( value ), like );
545 }
546 return this;
547 }
548
549 QueryMaker*
addNumberFilter(qint64 value,qint64 filter,QueryMaker::NumberComparison compare)550 SqlQueryMaker::addNumberFilter( qint64 value, qint64 filter, QueryMaker::NumberComparison compare )
551 {
552 QString comparison;
553 switch( compare )
554 {
555 case QueryMaker::Equals:
556 comparison = '=';
557 break;
558 case QueryMaker::GreaterThan:
559 comparison = '>';
560 break;
561 case QueryMaker::LessThan:
562 comparison = '<';
563 break;
564 }
565
566 // note: a NULL value in the database means undefined and not 0!
567 d->queryFilter += QString( " %1 %2 %3 %4 " ).arg( andOr(), nameForValue( value ), comparison, QString::number( filter ) );
568
569 return this;
570 }
571
572 QueryMaker*
excludeNumberFilter(qint64 value,qint64 filter,QueryMaker::NumberComparison compare)573 SqlQueryMaker::excludeNumberFilter( qint64 value, qint64 filter, QueryMaker::NumberComparison compare )
574 {
575 QString comparison;
576 switch( compare )
577 {
578 case QueryMaker::Equals:
579 comparison = "!=";
580 break;
581 case QueryMaker::GreaterThan: //negating greater than is less or equal
582 comparison = "<=";
583 break;
584 case QueryMaker::LessThan: //negating less than is greater or equal
585 comparison = ">=";
586 break;
587 }
588
589 // note: a NULL value in the database means undefined and not 0!
590 // We can't exclude NULL values here because they are not defined!
591 d->queryFilter += QString( " %1 (%2 %3 %4 or %2 is null)" ).arg( andOr(), nameForValue( value ), comparison, QString::number( filter ) );
592
593 return this;
594 }
595
596 QueryMaker*
addReturnValue(qint64 value)597 SqlQueryMaker::addReturnValue( qint64 value )
598 {
599 if( d->queryType == QueryMaker::Custom )
600 {
601 if ( !d->queryReturnValues.isEmpty() )
602 d->queryReturnValues += ',';
603 d->queryReturnValues += nameForValue( value );
604 d->returnValueType = value;
605 }
606 return this;
607 }
608
609 QueryMaker*
addReturnFunction(ReturnFunction function,qint64 value)610 SqlQueryMaker::addReturnFunction( ReturnFunction function, qint64 value )
611 {
612 if( d->queryType == QueryMaker::Custom )
613 {
614 if( !d->queryReturnValues.isEmpty() )
615 d->queryReturnValues += ',';
616 QString sqlfunction;
617 switch( function )
618 {
619 case QueryMaker::Count:
620 sqlfunction = "COUNT";
621 break;
622 case QueryMaker::Sum:
623 sqlfunction = "SUM";
624 break;
625 case QueryMaker::Max:
626 sqlfunction = "MAX";
627 break;
628 case QueryMaker::Min:
629 sqlfunction = "MIN";
630 break;
631 default:
632 sqlfunction = "Unknown function in SqlQueryMaker::addReturnFunction, function was: " + QString::number( function );
633 }
634 d->queryReturnValues += QString( "%1(%2)" ).arg( sqlfunction, nameForValue( value ) );
635 d->returnValueType = value;
636 }
637 return this;
638 }
639
640 QueryMaker*
orderBy(qint64 value,bool descending)641 SqlQueryMaker::orderBy( qint64 value, bool descending )
642 {
643 if ( d->queryOrderBy.isEmpty() )
644 d->queryOrderBy = " ORDER BY ";
645 else
646 d->queryOrderBy += ',';
647 d->queryOrderBy += nameForValue( value );
648 d->queryOrderBy += QString( " %1 " ).arg( descending ? "DESC" : "ASC" );
649 return this;
650 }
651
652 QueryMaker*
limitMaxResultSize(int size)653 SqlQueryMaker::limitMaxResultSize( int size )
654 {
655 d->maxResultSize = size;
656 return this;
657 }
658
659 QueryMaker*
setAlbumQueryMode(AlbumQueryMode mode)660 SqlQueryMaker::setAlbumQueryMode( AlbumQueryMode mode )
661 {
662 if( mode != AllAlbums )
663 {
664 d->linkedTables |= Private::ALBUM_TAB;
665 }
666 d->albumMode = mode;
667 return this;
668 }
669
670 QueryMaker*
setLabelQueryMode(LabelQueryMode mode)671 SqlQueryMaker::setLabelQueryMode( LabelQueryMode mode )
672 {
673 d->labelMode = mode;
674 return this;
675 }
676
677 QueryMaker*
beginAnd()678 SqlQueryMaker::beginAnd()
679 {
680 d->queryFilter += andOr();
681 d->queryFilter += " ( 1 ";
682 d->andStack.push( true );
683 return this;
684 }
685
686 QueryMaker*
beginOr()687 SqlQueryMaker::beginOr()
688 {
689 d->queryFilter += andOr();
690 d->queryFilter += " ( 0 ";
691 d->andStack.push( false );
692 return this;
693 }
694
695 QueryMaker*
endAndOr()696 SqlQueryMaker::endAndOr()
697 {
698 d->queryFilter += ')';
699 d->andStack.pop();
700 return this;
701 }
702
703 void
linkTables()704 SqlQueryMaker::linkTables()
705 {
706 switch( d->queryType )
707 {
708 case QueryMaker::Track:
709 {
710 d->queryFrom += " tracks";
711 if( d->linkedTables & Private::TAGS_TAB )
712 d->linkedTables ^= Private::TAGS_TAB;
713 break;
714 }
715 case QueryMaker::Artist:
716 {
717 d->queryFrom += " artists";
718 if( d->linkedTables != Private::ARTIST_TAB )
719 d->queryFrom += " JOIN tracks ON tracks.artist = artists.id";
720 if( d->linkedTables & Private::ARTIST_TAB )
721 d->linkedTables ^= Private::ARTIST_TAB;
722 break;
723 }
724 case QueryMaker::Album:
725 case QueryMaker::AlbumArtist:
726 {
727 d->queryFrom += " albums";
728 if( d->linkedTables != Private::ALBUM_TAB && d->linkedTables != ( Private::ALBUM_TAB | Private::ALBUMARTIST_TAB ) )
729 d->queryFrom += " JOIN tracks ON tracks.album = albums.id";
730 if( d->linkedTables & Private::ALBUM_TAB )
731 d->linkedTables ^= Private::ALBUM_TAB;
732 break;
733 }
734 case QueryMaker::Genre:
735 {
736 d->queryFrom += " genres";
737 if( d->linkedTables != Private::GENRE_TAB )
738 d->queryFrom += " INNER JOIN tracks ON tracks.genre = genres.id";
739 if( d->linkedTables & Private::GENRE_TAB )
740 d->linkedTables ^= Private::GENRE_TAB;
741 break;
742 }
743 case QueryMaker::Composer:
744 {
745 d->queryFrom += " composers";
746 if( d->linkedTables != Private::COMPOSER_TAB )
747 d->queryFrom += " JOIN tracks ON tracks.composer = composers.id";
748 if( d->linkedTables & Private::COMPOSER_TAB )
749 d->linkedTables ^= Private::COMPOSER_TAB;
750 break;
751 }
752 case QueryMaker::Year:
753 {
754 d->queryFrom += " years";
755 if( d->linkedTables != Private::YEAR_TAB )
756 d->queryFrom += " JOIN tracks on tracks.year = years.id";
757 if( d->linkedTables & Private::YEAR_TAB )
758 d->linkedTables ^= Private::YEAR_TAB;
759 break;
760 }
761 case QueryMaker::Label:
762 {
763 d->queryFrom += " labels";
764 if( d->linkedTables != Private::LABELS_TAB )
765 d->queryFrom += " INNER JOIN urls_labels ON labels.id = urls_labels.label"
766 " INNER JOIN tracks ON urls_labels.url = tracks.url";
767 if( d->linkedTables & Private::LABELS_TAB )
768 d->linkedTables ^= Private::LABELS_TAB;
769 break;
770 }
771 case QueryMaker::Custom:
772 {
773 switch( d->returnValueType )
774 {
775 default:
776 case Meta::valUrl:
777 {
778 d->queryFrom += " tracks";
779 if( d->linkedTables & Private::TAGS_TAB )
780 d->linkedTables ^= Private::TAGS_TAB;
781 break;
782 }
783 case Meta::valAlbum:
784 {
785 d->queryFrom += " albums";
786 if( d->linkedTables & Private::ALBUM_TAB )
787 d->linkedTables ^= Private::ALBUM_TAB;
788 if( d->linkedTables & Private::URLS_TAB )
789 d->linkedTables ^= Private::URLS_TAB;
790 break;
791 }
792 case Meta::valArtist:
793 {
794 d->queryFrom += " artists";
795 if( d->linkedTables & Private::ARTIST_TAB )
796 d->linkedTables ^= Private::ARTIST_TAB;
797 if( d->linkedTables & Private::URLS_TAB )
798 d->linkedTables ^= Private::URLS_TAB;
799 break;
800 }
801 case Meta::valGenre:
802 {
803 d->queryFrom += " genres";
804 if( d->linkedTables & Private::GENRE_TAB )
805 d->linkedTables ^= Private::GENRE_TAB;
806 if( d->linkedTables & Private::URLS_TAB )
807 d->linkedTables ^= Private::URLS_TAB;
808 break;
809 }
810 }
811 }
812 case QueryMaker::None:
813 {
814 //???
815 break;
816 }
817 }
818 if( !d->linkedTables )
819 return;
820
821 if( d->linkedTables & Private::URLS_TAB )
822 d->queryFrom += " INNER JOIN urls ON tracks.url = urls.id";
823 if( d->linkedTables & Private::ARTIST_TAB )
824 d->queryFrom += " LEFT JOIN artists ON tracks.artist = artists.id";
825 if( d->linkedTables & Private::ALBUM_TAB )
826 d->queryFrom += " LEFT JOIN albums ON tracks.album = albums.id";
827 if( d->linkedTables & Private::ALBUMARTIST_TAB )
828 d->queryFrom += " LEFT JOIN artists AS albumartists ON albums.artist = albumartists.id";
829 if( d->linkedTables & Private::GENRE_TAB )
830 d->queryFrom += " LEFT JOIN genres ON tracks.genre = genres.id";
831 if( d->linkedTables & Private::COMPOSER_TAB )
832 d->queryFrom += " LEFT JOIN composers ON tracks.composer = composers.id";
833 if( d->linkedTables & Private::YEAR_TAB )
834 d->queryFrom += " LEFT JOIN years ON tracks.year = years.id";
835 if( d->linkedTables & Private::STATISTICS_TAB )
836 {
837 if( d->linkedTables & Private::URLS_TAB )
838 {
839 d->queryFrom += " LEFT JOIN statistics ON urls.id = statistics.url";
840 }
841 else
842 {
843 d->queryFrom += " LEFT JOIN statistics ON tracks.url = statistics.url";
844 }
845 }
846 }
847
848 void
buildQuery()849 SqlQueryMaker::buildQuery()
850 {
851 //URLS is always required for dynamic collection
852 d->linkedTables |= Private::URLS_TAB;
853 linkTables();
854 QString query = "SELECT ";
855 if ( d->withoutDuplicates )
856 query += "DISTINCT ";
857 query += d->queryReturnValues;
858 query += " FROM ";
859 query += d->queryFrom;
860
861 // dynamic collection (only mounted file systems are considered)
862 if( (d->linkedTables & Private::URLS_TAB) && m_collection->mountPointManager() )
863 {
864 query += " WHERE 1 ";
865 IdList list = m_collection->mountPointManager()->getMountedDeviceIds();
866 if( !list.isEmpty() )
867 {
868 QString commaSeparatedIds;
869 foreach( int id, list )
870 {
871 if( !commaSeparatedIds.isEmpty() )
872 commaSeparatedIds += ',';
873 commaSeparatedIds += QString::number( id );
874 }
875 query += QString( " AND urls.deviceid in (%1)" ).arg( commaSeparatedIds );
876 }
877 }
878
879 switch( d->albumMode )
880 {
881 case OnlyNormalAlbums:
882 query += " AND albums.artist IS NOT NULL ";
883 break;
884 case OnlyCompilations:
885 query += " AND albums.artist IS NULL ";
886 break;
887 case AllAlbums:
888 //do nothing
889 break;
890 }
891 if( d->labelMode != QueryMaker::NoConstraint )
892 {
893 switch( d->labelMode )
894 {
895 case QueryMaker::OnlyWithLabels:
896 query += " AND tracks.url IN ";
897 break;
898
899 case QueryMaker::OnlyWithoutLabels:
900 query += " AND tracks.url NOT IN ";
901 break;
902
903 case QueryMaker::NoConstraint:
904 //do nothing, will never be called
905 break;
906 }
907 query += " (SELECT DISTINCT url FROM urls_labels) ";
908 }
909
910 query += d->queryMatch;
911 if ( !d->queryFilter.isEmpty() )
912 {
913 query += " AND ( 1 ";
914 query += d->queryFilter;
915 query += " ) ";
916 }
917 query += d->queryOrderBy;
918 if ( d->maxResultSize > -1 )
919 query += QString( " LIMIT %1 OFFSET 0 " ).arg( d->maxResultSize );
920 query += ';';
921 d->query = query;
922 }
923
924 QString
query()925 SqlQueryMaker::query()
926 {
927 if ( d->query.isEmpty() )
928 buildQuery();
929 return d->query;
930 }
931
932 QStringList
runQuery(const QString & query)933 SqlQueryMaker::runQuery( const QString &query )
934 {
935 return m_collection->sqlStorage()->query( query );
936 }
937
938 void
setBlocking(bool enabled)939 SqlQueryMaker::setBlocking( bool enabled )
940 {
941 d->blocking = enabled;
942 }
943
944 QStringList
collectionIds() const945 SqlQueryMaker::collectionIds() const
946 {
947 QStringList list;
948 list << m_collection->collectionId();
949 return list;
950 }
951
952 Meta::TrackList
tracks() const953 SqlQueryMaker::tracks() const
954 {
955 return d->blockingTracks;
956 }
957
958 Meta::AlbumList
albums() const959 SqlQueryMaker::albums() const
960 {
961 return d->blockingAlbums;
962 }
963
964 Meta::ArtistList
artists() const965 SqlQueryMaker::artists() const
966 {
967 return d->blockingArtists;
968 }
969
970 Meta::GenreList
genres() const971 SqlQueryMaker::genres() const
972 {
973 return d->blockingGenres;
974 }
975
976 Meta::ComposerList
composers() const977 SqlQueryMaker::composers() const
978 {
979 return d->blockingComposers;
980 }
981
982 Meta::YearList
years() const983 SqlQueryMaker::years() const
984 {
985 return d->blockingYears;
986 }
987
988 QStringList
customData() const989 SqlQueryMaker::customData() const
990 {
991 return d->blockingCustomData;
992 }
993
994 Meta::LabelList
labels() const995 SqlQueryMaker::labels() const
996 {
997 return d->blockingLabels;
998 }
999
1000 QString
nameForValue(qint64 value)1001 SqlQueryMaker::nameForValue( qint64 value )
1002 {
1003 switch( value )
1004 {
1005 case Meta::valUrl:
1006 d->linkedTables |= Private::URLS_TAB;
1007 return "urls.rpath"; //TODO figure out how to handle deviceid
1008 case Meta::valTitle:
1009 d->linkedTables |= Private::TAGS_TAB;
1010 return "tracks.title";
1011 case Meta::valArtist:
1012 d->linkedTables |= Private::ARTIST_TAB;
1013 return "artists.name";
1014 case Meta::valAlbum:
1015 d->linkedTables |= Private::ALBUM_TAB;
1016 return "albums.name";
1017 case Meta::valGenre:
1018 d->linkedTables |= Private::GENRE_TAB;
1019 return "genres.name";
1020 case Meta::valComposer:
1021 d->linkedTables |= Private::COMPOSER_TAB;
1022 return "composers.name";
1023 case Meta::valYear:
1024 d->linkedTables |= Private::YEAR_TAB;
1025 return "years.name";
1026 case Meta::valBpm:
1027 d->linkedTables |= Private::TAGS_TAB;
1028 return "tracks.bpm";
1029 case Meta::valComment:
1030 d->linkedTables |= Private::TAGS_TAB;
1031 return "tracks.comment";
1032 case Meta::valTrackNr:
1033 d->linkedTables |= Private::TAGS_TAB;
1034 return "tracks.tracknumber";
1035 case Meta::valDiscNr:
1036 d->linkedTables |= Private::TAGS_TAB;
1037 return "tracks.discnumber";
1038 case Meta::valLength:
1039 d->linkedTables |= Private::TAGS_TAB;
1040 return "tracks.length";
1041 case Meta::valBitrate:
1042 d->linkedTables |= Private::TAGS_TAB;
1043 return "tracks.bitrate";
1044 case Meta::valSamplerate:
1045 d->linkedTables |= Private::TAGS_TAB;
1046 return "tracks.samplerate";
1047 case Meta::valFilesize:
1048 d->linkedTables |= Private::TAGS_TAB;
1049 return "tracks.filesize";
1050 case Meta::valFormat:
1051 d->linkedTables |= Private::TAGS_TAB;
1052 return "tracks.filetype";
1053 case Meta::valCreateDate:
1054 d->linkedTables |= Private::TAGS_TAB;
1055 return "tracks.createdate";
1056 case Meta::valScore:
1057 d->linkedTables |= Private::STATISTICS_TAB;
1058 return "statistics.score";
1059 case Meta::valRating:
1060 d->linkedTables |= Private::STATISTICS_TAB;
1061 return "statistics.rating";
1062 case Meta::valFirstPlayed:
1063 d->linkedTables |= Private::STATISTICS_TAB;
1064 return "statistics.createdate";
1065 case Meta::valLastPlayed:
1066 d->linkedTables |= Private::STATISTICS_TAB;
1067 return "statistics.accessdate";
1068 case Meta::valPlaycount:
1069 d->linkedTables |= Private::STATISTICS_TAB;
1070 return "statistics.playcount";
1071 case Meta::valUniqueId:
1072 d->linkedTables |= Private::URLS_TAB;
1073 return "urls.uniqueid";
1074 case Meta::valAlbumArtist:
1075 d->linkedTables |= Private::ALBUMARTIST_TAB;
1076 //albumartist_tab means that the artist table is joined to the albums table
1077 //so add albums as well
1078 d->linkedTables |= Private::ALBUM_TAB;
1079 return "albumartists.name";
1080 case Meta::valModified:
1081 d->linkedTables |= Private::TAGS_TAB;
1082 return "tracks.modifydate";
1083 default:
1084 return "ERROR: unknown value in SqlQueryMaker::nameForValue(qint64): value=" + QString::number( value );
1085 }
1086 }
1087
1088 QString
andOr() const1089 SqlQueryMaker::andOr() const
1090 {
1091 return d->andStack.top() ? " AND " : " OR ";
1092 }
1093
1094 QString
escape(const QString & text) const1095 SqlQueryMaker::escape( const QString &text ) const //krazy:exclude=constref
1096 {
1097 return m_collection->sqlStorage()->escape( text );
1098 }
1099
1100 QString
likeCondition(const QString & text,bool anyBegin,bool anyEnd) const1101 SqlQueryMaker::likeCondition( const QString &text, bool anyBegin, bool anyEnd ) const
1102 {
1103 if( anyBegin || anyEnd )
1104 {
1105 QString escaped = text;
1106 //according to http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
1107 //the escape character (\ as we are using the default) is escaped twice when using like.
1108 //mysql_real_escape will escape it once, so we have to escape it another time here
1109 escaped = escaped.replace( '\\', "\\\\" ); // "////" will result in two backslahes
1110 escaped = escape( escaped );
1111 //as we are in pattern matching mode '_' and '%' have to be escaped
1112 //mysql_real_excape_string does not do that for us
1113 //see http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
1114 //and http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html
1115 //replace those characters after calling escape(), which calls the mysql
1116 //function in turn, so that mysql does not escape the escape backslashes
1117 escaped.replace( '%', "\\%" ).replace( '_', "\\_" );
1118
1119 QString ret = " LIKE ";
1120
1121 ret += '\'';
1122 if ( anyBegin )
1123 ret += '%';
1124 ret += escaped;
1125 if ( anyEnd )
1126 ret += '%';
1127 ret += '\'';
1128
1129 //Case insensitive collation for queries
1130 ret += " COLLATE utf8_unicode_ci ";
1131
1132 //Use \ as the escape character
1133 //ret += " ESCAPE '\\' ";
1134
1135 return ret;
1136 }
1137 else
1138 {
1139 return QStringLiteral( " = '%1' COLLATE utf8_unicode_ci " ).arg( escape( text ) );
1140 }
1141 }
1142
1143 void
blockingNewAlbumsReady(const Meta::AlbumList & albums)1144 SqlQueryMaker::blockingNewAlbumsReady(const Meta::AlbumList &albums)
1145 {
1146 d->blockingAlbums = albums;
1147 }
1148
1149 void
blockingNewArtistsReady(const Meta::ArtistList & artists)1150 SqlQueryMaker::blockingNewArtistsReady(const Meta::ArtistList &artists)
1151 {
1152 d->blockingArtists = artists;
1153 }
1154
1155 void
blockingNewGenresReady(const Meta::GenreList & genres)1156 SqlQueryMaker::blockingNewGenresReady(const Meta::GenreList &genres)
1157 {
1158 d->blockingGenres = genres;
1159 }
1160
1161 void
blockingNewComposersReady(const Meta::ComposerList & composers)1162 SqlQueryMaker::blockingNewComposersReady(const Meta::ComposerList &composers)
1163 {
1164 d->blockingComposers = composers;
1165 }
1166
1167 void
blockingNewYearsReady(const Meta::YearList & years)1168 SqlQueryMaker::blockingNewYearsReady(const Meta::YearList &years)
1169 {
1170 d->blockingYears = years;
1171 }
1172
1173 void
blockingNewTracksReady(const Meta::TrackList & tracks)1174 SqlQueryMaker::blockingNewTracksReady(const Meta::TrackList &tracks)
1175 {
1176 d->blockingTracks = tracks;
1177 }
1178
1179 void
blockingNewResultReady(const QStringList & customData)1180 SqlQueryMaker::blockingNewResultReady(const QStringList &customData)
1181 {
1182 d->blockingCustomData = customData;
1183 }
1184
1185 void
blockingNewLabelsReady(const Meta::LabelList & labels)1186 SqlQueryMaker::blockingNewLabelsReady(const Meta::LabelList &labels )
1187 {
1188 d->blockingLabels = labels;
1189 }
1190
1191 #include "SqlQueryMaker.moc"
1192