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