File indexing completed on 2025-01-05 04:25:57
0001 /**************************************************************************************** 0002 * Copyright (c) 2007 Maximilian Kossick <maximilian.kossick@googlemail.com> * 0003 * Copyright (c) 2008 Daniel Winter <dw@danielwinter.de> * 0004 * * 0005 * This program is free software; you can redistribute it and/or modify it under * 0006 * the terms of the GNU General Public License as published by the Free Software * 0007 * Foundation; either version 2 of the License, or (at your option) any later * 0008 * version. * 0009 * * 0010 * This program is distributed in the hope that it will be useful, but WITHOUT ANY * 0011 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A * 0012 * PARTICULAR PURPOSE. See the GNU General Public License for more details. * 0013 * * 0014 * You should have received a copy of the GNU General Public License along with * 0015 * this program. If not, see <http://www.gnu.org/licenses/>. * 0016 ****************************************************************************************/ 0017 0018 #define DEBUG_PREFIX "SqlQueryMaker" 0019 0020 #include "SqlQueryMaker.h" 0021 0022 #include "SqlCollection.h" 0023 #include "SqlQueryMakerInternal.h" 0024 #include <core/storage/SqlStorage.h> 0025 #include "core/support/Debug.h" 0026 #include "core-impl/collections/db/MountPointManager.h" 0027 0028 #include <QWeakPointer> 0029 #include <QStack> 0030 0031 #include <ThreadWeaver/Job> 0032 #include <ThreadWeaver/ThreadWeaver> 0033 #include <ThreadWeaver/Queue> 0034 0035 using namespace Collections; 0036 0037 class SqlWorkerThread : public QObject, public ThreadWeaver::Job 0038 { 0039 Q_OBJECT 0040 public: 0041 SqlWorkerThread( SqlQueryMakerInternal *queryMakerInternal ) 0042 : QObject() 0043 , ThreadWeaver::Job() 0044 , m_queryMakerInternal( queryMakerInternal ) 0045 , m_aborted( false ) 0046 { 0047 //nothing to do 0048 } 0049 0050 ~SqlWorkerThread() override 0051 { 0052 delete m_queryMakerInternal; 0053 } 0054 0055 void requestAbort() override 0056 { 0057 m_aborted = true; 0058 } 0059 0060 SqlQueryMakerInternal* queryMakerInternal() const 0061 { 0062 return m_queryMakerInternal; 0063 } 0064 0065 protected: 0066 void run(ThreadWeaver::JobPointer self = QSharedPointer<ThreadWeaver::Job>(), ThreadWeaver::Thread *thread = nullptr) override 0067 { 0068 Q_UNUSED(self); 0069 Q_UNUSED(thread); 0070 m_queryMakerInternal->run(); 0071 if( m_aborted ) 0072 setStatus(Status_Aborted); 0073 else 0074 setStatus(Status_Running); 0075 } 0076 void defaultBegin(const ThreadWeaver::JobPointer& self, ThreadWeaver::Thread *thread) override 0077 { 0078 Q_EMIT started(self); 0079 ThreadWeaver::Job::defaultBegin(self, thread); 0080 } 0081 0082 void defaultEnd(const ThreadWeaver::JobPointer& self, ThreadWeaver::Thread *thread) override 0083 { 0084 ThreadWeaver::Job::defaultEnd(self, thread); 0085 if (!self->success()) { 0086 Q_EMIT failed(self); 0087 } 0088 Q_EMIT done(self); 0089 } 0090 0091 private: 0092 SqlQueryMakerInternal *m_queryMakerInternal; 0093 0094 bool m_aborted; 0095 Q_SIGNALS: 0096 /** This signal is emitted when this job is being processed by a thread. */ 0097 void started(ThreadWeaver::JobPointer); 0098 /** This signal is emitted when the job has been finished (no matter if it succeeded or not). */ 0099 void done(ThreadWeaver::JobPointer); 0100 /** This job has failed. 0101 * This signal is emitted when success() returns false after the job is executed. */ 0102 void failed(ThreadWeaver::JobPointer); 0103 }; 0104 0105 struct SqlQueryMaker::Private 0106 { 0107 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 }; 0108 int linkedTables; 0109 QueryMaker::QueryType queryType; 0110 QString query; 0111 QString queryReturnValues; 0112 QString queryFrom; 0113 QString queryMatch; 0114 QString queryFilter; 0115 QString queryOrderBy; 0116 bool withoutDuplicates; 0117 int maxResultSize; 0118 AlbumQueryMode albumMode; 0119 LabelQueryMode labelMode; 0120 SqlWorkerThread *worker; 0121 0122 QStack<bool> andStack; 0123 0124 QStringList blockingCustomData; 0125 Meta::TrackList blockingTracks; 0126 Meta::AlbumList blockingAlbums; 0127 Meta::ArtistList blockingArtists; 0128 Meta::GenreList blockingGenres; 0129 Meta::ComposerList blockingComposers; 0130 Meta::YearList blockingYears; 0131 Meta::LabelList blockingLabels; 0132 bool blocking; 0133 bool used; 0134 qint64 returnValueType; 0135 }; 0136 0137 SqlQueryMaker::SqlQueryMaker( SqlCollection* collection ) 0138 : QueryMaker() 0139 , m_collection( collection ) 0140 , d( new Private ) 0141 { 0142 d->worker = nullptr; 0143 d->queryType = QueryMaker::None; 0144 d->linkedTables = 0; 0145 d->withoutDuplicates = false; 0146 d->albumMode = AllAlbums; 0147 d->labelMode = QueryMaker::NoConstraint; 0148 d->maxResultSize = -1; 0149 d->andStack.clear(); 0150 d->andStack.push( true ); //and is default 0151 d->blocking = false; 0152 d->used = false; 0153 d->returnValueType = 0; 0154 } 0155 0156 SqlQueryMaker::~SqlQueryMaker() 0157 { 0158 disconnect(); 0159 abortQuery(); 0160 if( d->worker ) 0161 { 0162 d->worker->deleteLater(); 0163 } 0164 delete d; 0165 } 0166 0167 void 0168 SqlQueryMaker::abortQuery() 0169 { 0170 if( d->worker ) 0171 { 0172 d->worker->requestAbort(); 0173 d->worker->disconnect( this ); 0174 if( d->worker->queryMakerInternal() ) 0175 d->worker->queryMakerInternal()->disconnect( this ); 0176 } 0177 } 0178 0179 void 0180 SqlQueryMaker::run() 0181 { 0182 if( d->queryType == QueryMaker::None || (d->blocking && d->used) ) 0183 { 0184 debug() << "sql querymaker used without reset or initialization" << Qt::endl; 0185 return; //better error handling? 0186 } 0187 if( d->worker && !d->worker->isFinished() ) 0188 { 0189 //the worker thread seems to be running 0190 //TODO: wait or job to complete 0191 0192 } 0193 else 0194 { 0195 SqlQueryMakerInternal *qmi = new SqlQueryMakerInternal( m_collection ); 0196 qmi->setQuery( query() ); 0197 qmi->setQueryType( d->queryType ); 0198 0199 if ( !d->blocking ) 0200 { 0201 connect( qmi, &Collections::SqlQueryMakerInternal::newAlbumsReady, this, &SqlQueryMaker::newAlbumsReady, Qt::DirectConnection ); 0202 connect( qmi, &Collections::SqlQueryMakerInternal::newArtistsReady, this, &SqlQueryMaker::newArtistsReady, Qt::DirectConnection ); 0203 connect( qmi, &Collections::SqlQueryMakerInternal::newGenresReady, this, &SqlQueryMaker::newGenresReady, Qt::DirectConnection ); 0204 connect( qmi, &Collections::SqlQueryMakerInternal::newComposersReady, this, &SqlQueryMaker::newComposersReady, Qt::DirectConnection ); 0205 connect( qmi, &Collections::SqlQueryMakerInternal::newYearsReady, this, &SqlQueryMaker::newYearsReady, Qt::DirectConnection ); 0206 connect( qmi, &Collections::SqlQueryMakerInternal::newTracksReady, this, &SqlQueryMaker::newTracksReady, Qt::DirectConnection ); 0207 connect( qmi, &Collections::SqlQueryMakerInternal::newResultReady, this, &SqlQueryMaker::newResultReady, Qt::DirectConnection ); 0208 connect( qmi, &Collections::SqlQueryMakerInternal::newLabelsReady, this, &SqlQueryMaker::newLabelsReady, Qt::DirectConnection ); 0209 d->worker = new SqlWorkerThread( qmi ); 0210 connect( d->worker, &SqlWorkerThread::done, this, &SqlQueryMaker::done ); 0211 ThreadWeaver::Queue::instance()->enqueue( QSharedPointer<ThreadWeaver::Job>(d->worker) ); 0212 } 0213 else //use it blocking 0214 { 0215 connect( qmi, &Collections::SqlQueryMakerInternal::newAlbumsReady, this, &SqlQueryMaker::blockingNewAlbumsReady, Qt::DirectConnection ); 0216 connect( qmi, &Collections::SqlQueryMakerInternal::newArtistsReady, this, &SqlQueryMaker::blockingNewArtistsReady, Qt::DirectConnection ); 0217 connect( qmi, &Collections::SqlQueryMakerInternal::newGenresReady, this, &SqlQueryMaker::blockingNewGenresReady, Qt::DirectConnection ); 0218 connect( qmi, &Collections::SqlQueryMakerInternal::newComposersReady, this, &SqlQueryMaker::blockingNewComposersReady, Qt::DirectConnection ); 0219 connect( qmi, &Collections::SqlQueryMakerInternal::newYearsReady, this, &SqlQueryMaker::blockingNewYearsReady, Qt::DirectConnection ); 0220 connect( qmi, &Collections::SqlQueryMakerInternal::newTracksReady, this, &SqlQueryMaker::blockingNewTracksReady, Qt::DirectConnection ); 0221 connect( qmi, &Collections::SqlQueryMakerInternal::newResultReady, this, &SqlQueryMaker::blockingNewResultReady, Qt::DirectConnection ); 0222 connect( qmi, &Collections::SqlQueryMakerInternal::newLabelsReady, this, &SqlQueryMaker::blockingNewLabelsReady, Qt::DirectConnection ); 0223 qmi->run(); 0224 delete qmi; 0225 } 0226 } 0227 d->used = true; 0228 } 0229 0230 void 0231 SqlQueryMaker::done( ThreadWeaver::JobPointer job ) 0232 { 0233 Q_UNUSED( job ) 0234 0235 d->worker = nullptr; // d->worker *is* the job, prevent stale pointer 0236 Q_EMIT queryDone(); 0237 } 0238 0239 QueryMaker* 0240 SqlQueryMaker::setQueryType( QueryType type ) 0241 { 0242 // we need the unchanged m_queryType in the blocking result methods so prevent 0243 // reseting queryType without reseting the QM 0244 if ( d->blocking && d->used ) 0245 return this; 0246 0247 switch( type ) { 0248 case QueryMaker::Track: 0249 //make sure to keep this method in sync with handleTracks(QStringList) and the SqlTrack ctor 0250 if( d->queryType == QueryMaker::None ) 0251 { 0252 d->queryType = QueryMaker::Track; 0253 d->linkedTables |= Private::URLS_TAB; 0254 d->linkedTables |= Private::TAGS_TAB; 0255 d->linkedTables |= Private::GENRE_TAB; 0256 d->linkedTables |= Private::ARTIST_TAB; 0257 d->linkedTables |= Private::ALBUM_TAB; 0258 d->linkedTables |= Private::COMPOSER_TAB; 0259 d->linkedTables |= Private::YEAR_TAB; 0260 d->linkedTables |= Private::STATISTICS_TAB; 0261 d->queryReturnValues = Meta::SqlTrack::getTrackReturnValues(); 0262 } 0263 return this; 0264 0265 case QueryMaker::Artist: 0266 if( d->queryType == QueryMaker::None ) 0267 { 0268 d->queryType = QueryMaker::Artist; 0269 d->withoutDuplicates = true; 0270 d->linkedTables |= Private::ARTIST_TAB; 0271 //reading the ids from the database means we don't have to query for them later 0272 d->queryReturnValues = "artists.name, artists.id"; 0273 } 0274 return this; 0275 0276 case QueryMaker::Album: 0277 if( d->queryType == QueryMaker::None ) 0278 { 0279 d->queryType = QueryMaker::Album; 0280 d->withoutDuplicates = true; 0281 d->linkedTables |= Private::ALBUM_TAB; 0282 //add whatever is necessary to identify compilations 0283 d->queryReturnValues = "albums.name, albums.id, albums.artist"; 0284 } 0285 return this; 0286 0287 case QueryMaker::AlbumArtist: 0288 if( d->queryType == QueryMaker::None ) 0289 { 0290 d->queryType = QueryMaker::AlbumArtist; 0291 d->withoutDuplicates = true; 0292 d->linkedTables |= Private::ALBUMARTIST_TAB; 0293 d->linkedTables |= Private::ALBUM_TAB; 0294 d->queryReturnValues = "albumartists.name, albumartists.id"; 0295 } 0296 return this; 0297 0298 case QueryMaker::Composer: 0299 if( d->queryType == QueryMaker::None ) 0300 { 0301 d->queryType = QueryMaker::Composer; 0302 d->withoutDuplicates = true; 0303 d->linkedTables |= Private::COMPOSER_TAB; 0304 d->queryReturnValues = "composers.name, composers.id"; 0305 } 0306 return this; 0307 0308 case QueryMaker::Genre: 0309 if( d->queryType == QueryMaker::None ) 0310 { 0311 d->queryType = QueryMaker::Genre; 0312 d->withoutDuplicates = true; 0313 d->linkedTables |= Private::GENRE_TAB; 0314 d->queryReturnValues = "genres.name, genres.id"; 0315 } 0316 return this; 0317 0318 case QueryMaker::Year: 0319 if( d->queryType == QueryMaker::None ) 0320 { 0321 d->queryType = QueryMaker::Year; 0322 d->withoutDuplicates = true; 0323 d->linkedTables |= Private::YEAR_TAB; 0324 d->queryReturnValues = "years.name, years.id"; 0325 } 0326 return this; 0327 0328 case QueryMaker::Custom: 0329 if( d->queryType == QueryMaker::None ) 0330 d->queryType = QueryMaker::Custom; 0331 return this; 0332 0333 case QueryMaker::Label: 0334 if( d->queryType == QueryMaker::None ) 0335 { 0336 d->queryType = QueryMaker::Label; 0337 d->withoutDuplicates = true; 0338 d->queryReturnValues = "labels.label,labels.id"; 0339 d->linkedTables |= Private::LABELS_TAB; 0340 } 0341 return this; 0342 0343 case QueryMaker::None: 0344 return this; 0345 } 0346 return this; 0347 } 0348 0349 QueryMaker* 0350 SqlQueryMaker::addMatch( const Meta::TrackPtr &track ) 0351 { 0352 QString url = track->uidUrl(); 0353 if( !url.isEmpty() ) 0354 /* 0355 QUrl kurl( url ); 0356 if( kurl.scheme() == "amarok-sqltrackuid" ) 0357 */ 0358 { 0359 d->queryMatch += QStringLiteral( " AND urls.uniqueid = '%1' " ).arg( url /*kurl.url()*/ ); 0360 } 0361 else 0362 { 0363 QString path; 0364 /* 0365 if( kurl.isLocalFile() ) 0366 { 0367 path = kurl.path(); 0368 } 0369 else 0370 */ 0371 { 0372 path = track->playableUrl().path(); 0373 } 0374 int deviceid = m_collection->mountPointManager()->getIdForUrl( QUrl::fromUserInput(path) ); 0375 QString rpath = m_collection->mountPointManager()->getRelativePath( deviceid, path ); 0376 d->queryMatch += QString( " AND urls.deviceid = %1 AND urls.rpath = '%2'" ) 0377 .arg( QString::number( deviceid ), escape( rpath ) ); 0378 } 0379 return this; 0380 } 0381 0382 0383 QueryMaker* 0384 SqlQueryMaker::addMatch( const Meta::ArtistPtr &artist, ArtistMatchBehaviour behaviour ) 0385 { 0386 d->linkedTables |= Private::ARTIST_TAB; 0387 if( behaviour == AlbumArtists || behaviour == AlbumOrTrackArtists ) 0388 d->linkedTables |= Private::ALBUMARTIST_TAB; 0389 0390 QString artistQuery; 0391 QString albumArtistQuery; 0392 0393 if( artist && !artist->name().isEmpty() ) 0394 { 0395 artistQuery = QStringLiteral("artists.name = '%1'").arg( escape( artist->name() ) ); 0396 albumArtistQuery = QStringLiteral("albumartists.name = '%1'").arg( escape( artist->name() ) ); 0397 } 0398 else 0399 { 0400 artistQuery = "( artists.name IS NULL OR artists.name = '')"; 0401 albumArtistQuery = "( albumartists.name IS NULL OR albumartists.name = '')"; 0402 } 0403 0404 switch( behaviour ) 0405 { 0406 case TrackArtists: 0407 d->queryMatch += " AND " + artistQuery; 0408 break; 0409 case AlbumArtists: 0410 d->queryMatch += " AND " + albumArtistQuery; 0411 break; 0412 case AlbumOrTrackArtists: 0413 d->queryMatch += " AND ( (" + artistQuery + " ) OR ( " + albumArtistQuery + " ) )"; 0414 break; 0415 } 0416 return this; 0417 } 0418 0419 QueryMaker* 0420 SqlQueryMaker::addMatch( const Meta::AlbumPtr &album ) 0421 { 0422 d->linkedTables |= Private::ALBUM_TAB; 0423 0424 // handle singles 0425 if( !album || album->name().isEmpty() ) 0426 d->queryMatch += QString( " AND ( albums.name IS NULL OR albums.name = '' )" ); 0427 else 0428 d->queryMatch += QString( " AND albums.name = '%1'" ).arg( escape( album->name() ) ); 0429 0430 if( album ) 0431 { 0432 //handle compilations 0433 Meta::ArtistPtr albumArtist = album->albumArtist(); 0434 if( albumArtist ) 0435 { 0436 d->linkedTables |= Private::ALBUMARTIST_TAB; 0437 d->queryMatch += QString( " AND albumartists.name = '%1'" ).arg( escape( albumArtist->name() ) ); 0438 } 0439 else 0440 { 0441 d->queryMatch += " AND albums.artist IS NULL"; 0442 } 0443 } 0444 return this; 0445 } 0446 0447 QueryMaker* 0448 SqlQueryMaker::addMatch( const Meta::GenrePtr &genre ) 0449 { 0450 d->linkedTables |= Private::GENRE_TAB; 0451 d->queryMatch += QString( " AND genres.name = '%1'" ).arg( escape( genre->name() ) ); 0452 return this; 0453 } 0454 0455 QueryMaker* 0456 SqlQueryMaker::addMatch( const Meta::ComposerPtr &composer ) 0457 { 0458 d->linkedTables |= Private::COMPOSER_TAB; 0459 d->queryMatch += QString( " AND composers.name = '%1'" ).arg( escape( composer->name() ) ); 0460 return this; 0461 } 0462 0463 QueryMaker* 0464 SqlQueryMaker::addMatch( const Meta::YearPtr &year ) 0465 { 0466 // handle tracks without a year 0467 if( !year ) 0468 { 0469 d->queryMatch += " AND year IS NULL"; 0470 } 0471 else 0472 { 0473 d->linkedTables |= Private::YEAR_TAB; 0474 d->queryMatch += QString( " AND years.name = '%1'" ).arg( escape( year->name() ) ); 0475 } 0476 return this; 0477 } 0478 0479 QueryMaker* 0480 SqlQueryMaker::addMatch( const Meta::LabelPtr &label ) 0481 { 0482 AmarokSharedPointer<Meta::SqlLabel> sqlLabel = AmarokSharedPointer<Meta::SqlLabel>::dynamicCast( label ); 0483 QString labelSubQuery; 0484 if( sqlLabel ) 0485 { 0486 labelSubQuery = "SELECT url FROM urls_labels WHERE label = %1"; 0487 labelSubQuery = labelSubQuery.arg( sqlLabel->id() ); 0488 } 0489 else 0490 { 0491 labelSubQuery = "SELECT a.url FROM urls_labels a INNER JOIN labels b ON a.label = b.id WHERE b.label = '%1'"; 0492 labelSubQuery = labelSubQuery.arg( escape( label->name() ) ); 0493 } 0494 d->linkedTables |= Private::TAGS_TAB; 0495 QString match = " AND tracks.url in (%1)"; 0496 d->queryMatch += match.arg( labelSubQuery ); 0497 return this; 0498 } 0499 0500 QueryMaker* 0501 SqlQueryMaker::addFilter( qint64 value, const QString &filter, bool matchBegin, bool matchEnd ) 0502 { 0503 // special case for albumartist... 0504 if( value == Meta::valAlbumArtist && filter.isEmpty() ) 0505 { 0506 d->linkedTables |= Private::ALBUMARTIST_TAB; 0507 d->linkedTables |= Private::ALBUM_TAB; 0508 d->queryFilter += QString( " %1 ( albums.artist IS NULL or albumartists.name = '') " ).arg( andOr() ); 0509 } 0510 else if( value == Meta::valLabel ) 0511 { 0512 d->linkedTables |= Private::TAGS_TAB; 0513 QString like = likeCondition( filter, !matchBegin, !matchEnd ); 0514 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) "; 0515 d->queryFilter += filter.arg( andOr(), like ); 0516 } 0517 else 0518 { 0519 QString like = likeCondition( filter, !matchBegin, !matchEnd ); 0520 d->queryFilter += QString( " %1 %2 %3 " ).arg( andOr(), nameForValue( value ), like ); 0521 } 0522 return this; 0523 } 0524 0525 QueryMaker* 0526 SqlQueryMaker::excludeFilter( qint64 value, const QString &filter, bool matchBegin, bool matchEnd ) 0527 { 0528 // special case for album... 0529 if( value == Meta::valAlbumArtist && filter.isEmpty() ) 0530 { 0531 d->linkedTables |= Private::ALBUMARTIST_TAB; 0532 d->queryFilter += QString( " %1 NOT ( albums.artist IS NULL or albumartists.name = '') " ).arg( andOr() ); 0533 } 0534 else if( value == Meta::valLabel ) 0535 { 0536 d->linkedTables |= Private::TAGS_TAB; 0537 QString like = likeCondition( filter, !matchBegin, !matchEnd ); 0538 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) "; 0539 d->queryFilter += filter.arg( andOr(), like ); 0540 } 0541 else 0542 { 0543 QString like = likeCondition( filter, !matchBegin, !matchEnd ); 0544 d->queryFilter += QString( " %1 NOT %2 %3 " ).arg( andOr(), nameForValue( value ), like ); 0545 } 0546 return this; 0547 } 0548 0549 QueryMaker* 0550 SqlQueryMaker::addNumberFilter( qint64 value, qint64 filter, QueryMaker::NumberComparison compare ) 0551 { 0552 QString comparison; 0553 switch( compare ) 0554 { 0555 case QueryMaker::Equals: 0556 comparison = '='; 0557 break; 0558 case QueryMaker::GreaterThan: 0559 comparison = '>'; 0560 break; 0561 case QueryMaker::LessThan: 0562 comparison = '<'; 0563 break; 0564 } 0565 0566 // note: a NULL value in the database means undefined and not 0! 0567 d->queryFilter += QString( " %1 %2 %3 %4 " ).arg( andOr(), nameForValue( value ), comparison, QString::number( filter ) ); 0568 0569 return this; 0570 } 0571 0572 QueryMaker* 0573 SqlQueryMaker::excludeNumberFilter( qint64 value, qint64 filter, QueryMaker::NumberComparison compare ) 0574 { 0575 QString comparison; 0576 switch( compare ) 0577 { 0578 case QueryMaker::Equals: 0579 comparison = "!="; 0580 break; 0581 case QueryMaker::GreaterThan: //negating greater than is less or equal 0582 comparison = "<="; 0583 break; 0584 case QueryMaker::LessThan: //negating less than is greater or equal 0585 comparison = ">="; 0586 break; 0587 } 0588 0589 // note: a NULL value in the database means undefined and not 0! 0590 // We can't exclude NULL values here because they are not defined! 0591 d->queryFilter += QString( " %1 (%2 %3 %4 or %2 is null)" ).arg( andOr(), nameForValue( value ), comparison, QString::number( filter ) ); 0592 0593 return this; 0594 } 0595 0596 QueryMaker* 0597 SqlQueryMaker::addReturnValue( qint64 value ) 0598 { 0599 if( d->queryType == QueryMaker::Custom ) 0600 { 0601 if ( !d->queryReturnValues.isEmpty() ) 0602 d->queryReturnValues += ','; 0603 d->queryReturnValues += nameForValue( value ); 0604 d->returnValueType = value; 0605 } 0606 return this; 0607 } 0608 0609 QueryMaker* 0610 SqlQueryMaker::addReturnFunction( ReturnFunction function, qint64 value ) 0611 { 0612 if( d->queryType == QueryMaker::Custom ) 0613 { 0614 if( !d->queryReturnValues.isEmpty() ) 0615 d->queryReturnValues += ','; 0616 QString sqlfunction; 0617 switch( function ) 0618 { 0619 case QueryMaker::Count: 0620 sqlfunction = "COUNT"; 0621 break; 0622 case QueryMaker::Sum: 0623 sqlfunction = "SUM"; 0624 break; 0625 case QueryMaker::Max: 0626 sqlfunction = "MAX"; 0627 break; 0628 case QueryMaker::Min: 0629 sqlfunction = "MIN"; 0630 break; 0631 default: 0632 sqlfunction = "Unknown function in SqlQueryMaker::addReturnFunction, function was: " + QString::number( function ); 0633 } 0634 d->queryReturnValues += QString( "%1(%2)" ).arg( sqlfunction, nameForValue( value ) ); 0635 d->returnValueType = value; 0636 } 0637 return this; 0638 } 0639 0640 QueryMaker* 0641 SqlQueryMaker::orderBy( qint64 value, bool descending ) 0642 { 0643 if ( d->queryOrderBy.isEmpty() ) 0644 d->queryOrderBy = " ORDER BY "; 0645 else 0646 d->queryOrderBy += ','; 0647 d->queryOrderBy += nameForValue( value ); 0648 d->queryOrderBy += QString( " %1 " ).arg( descending ? "DESC" : "ASC" ); 0649 return this; 0650 } 0651 0652 QueryMaker* 0653 SqlQueryMaker::limitMaxResultSize( int size ) 0654 { 0655 d->maxResultSize = size; 0656 return this; 0657 } 0658 0659 QueryMaker* 0660 SqlQueryMaker::setAlbumQueryMode( AlbumQueryMode mode ) 0661 { 0662 if( mode != AllAlbums ) 0663 { 0664 d->linkedTables |= Private::ALBUM_TAB; 0665 } 0666 d->albumMode = mode; 0667 return this; 0668 } 0669 0670 QueryMaker* 0671 SqlQueryMaker::setLabelQueryMode( LabelQueryMode mode ) 0672 { 0673 d->labelMode = mode; 0674 return this; 0675 } 0676 0677 QueryMaker* 0678 SqlQueryMaker::beginAnd() 0679 { 0680 d->queryFilter += andOr(); 0681 d->queryFilter += " ( 1 "; 0682 d->andStack.push( true ); 0683 return this; 0684 } 0685 0686 QueryMaker* 0687 SqlQueryMaker::beginOr() 0688 { 0689 d->queryFilter += andOr(); 0690 d->queryFilter += " ( 0 "; 0691 d->andStack.push( false ); 0692 return this; 0693 } 0694 0695 QueryMaker* 0696 SqlQueryMaker::endAndOr() 0697 { 0698 d->queryFilter += ')'; 0699 d->andStack.pop(); 0700 return this; 0701 } 0702 0703 void 0704 SqlQueryMaker::linkTables() 0705 { 0706 switch( d->queryType ) 0707 { 0708 case QueryMaker::Track: 0709 { 0710 d->queryFrom += " tracks"; 0711 if( d->linkedTables & Private::TAGS_TAB ) 0712 d->linkedTables ^= Private::TAGS_TAB; 0713 break; 0714 } 0715 case QueryMaker::Artist: 0716 { 0717 d->queryFrom += " artists"; 0718 if( d->linkedTables != Private::ARTIST_TAB ) 0719 d->queryFrom += " JOIN tracks ON tracks.artist = artists.id"; 0720 if( d->linkedTables & Private::ARTIST_TAB ) 0721 d->linkedTables ^= Private::ARTIST_TAB; 0722 break; 0723 } 0724 case QueryMaker::Album: 0725 case QueryMaker::AlbumArtist: 0726 { 0727 d->queryFrom += " albums"; 0728 if( d->linkedTables != Private::ALBUM_TAB && d->linkedTables != ( Private::ALBUM_TAB | Private::ALBUMARTIST_TAB ) ) 0729 d->queryFrom += " JOIN tracks ON tracks.album = albums.id"; 0730 if( d->linkedTables & Private::ALBUM_TAB ) 0731 d->linkedTables ^= Private::ALBUM_TAB; 0732 break; 0733 } 0734 case QueryMaker::Genre: 0735 { 0736 d->queryFrom += " genres"; 0737 if( d->linkedTables != Private::GENRE_TAB ) 0738 d->queryFrom += " INNER JOIN tracks ON tracks.genre = genres.id"; 0739 if( d->linkedTables & Private::GENRE_TAB ) 0740 d->linkedTables ^= Private::GENRE_TAB; 0741 break; 0742 } 0743 case QueryMaker::Composer: 0744 { 0745 d->queryFrom += " composers"; 0746 if( d->linkedTables != Private::COMPOSER_TAB ) 0747 d->queryFrom += " JOIN tracks ON tracks.composer = composers.id"; 0748 if( d->linkedTables & Private::COMPOSER_TAB ) 0749 d->linkedTables ^= Private::COMPOSER_TAB; 0750 break; 0751 } 0752 case QueryMaker::Year: 0753 { 0754 d->queryFrom += " years"; 0755 if( d->linkedTables != Private::YEAR_TAB ) 0756 d->queryFrom += " JOIN tracks on tracks.year = years.id"; 0757 if( d->linkedTables & Private::YEAR_TAB ) 0758 d->linkedTables ^= Private::YEAR_TAB; 0759 break; 0760 } 0761 case QueryMaker::Label: 0762 { 0763 d->queryFrom += " labels"; 0764 if( d->linkedTables != Private::LABELS_TAB ) 0765 d->queryFrom += " INNER JOIN urls_labels ON labels.id = urls_labels.label" 0766 " INNER JOIN tracks ON urls_labels.url = tracks.url"; 0767 if( d->linkedTables & Private::LABELS_TAB ) 0768 d->linkedTables ^= Private::LABELS_TAB; 0769 break; 0770 } 0771 case QueryMaker::Custom: 0772 { 0773 switch( d->returnValueType ) 0774 { 0775 default: 0776 case Meta::valUrl: 0777 { 0778 d->queryFrom += " tracks"; 0779 if( d->linkedTables & Private::TAGS_TAB ) 0780 d->linkedTables ^= Private::TAGS_TAB; 0781 break; 0782 } 0783 case Meta::valAlbum: 0784 { 0785 d->queryFrom += " albums"; 0786 if( d->linkedTables & Private::ALBUM_TAB ) 0787 d->linkedTables ^= Private::ALBUM_TAB; 0788 if( d->linkedTables & Private::URLS_TAB ) 0789 d->linkedTables ^= Private::URLS_TAB; 0790 break; 0791 } 0792 case Meta::valArtist: 0793 { 0794 d->queryFrom += " artists"; 0795 if( d->linkedTables & Private::ARTIST_TAB ) 0796 d->linkedTables ^= Private::ARTIST_TAB; 0797 if( d->linkedTables & Private::URLS_TAB ) 0798 d->linkedTables ^= Private::URLS_TAB; 0799 break; 0800 } 0801 case Meta::valGenre: 0802 { 0803 d->queryFrom += " genres"; 0804 if( d->linkedTables & Private::GENRE_TAB ) 0805 d->linkedTables ^= Private::GENRE_TAB; 0806 if( d->linkedTables & Private::URLS_TAB ) 0807 d->linkedTables ^= Private::URLS_TAB; 0808 break; 0809 } 0810 } 0811 } 0812 case QueryMaker::None: 0813 { 0814 //??? 0815 break; 0816 } 0817 } 0818 if( !d->linkedTables ) 0819 return; 0820 0821 if( d->linkedTables & Private::URLS_TAB ) 0822 d->queryFrom += " INNER JOIN urls ON tracks.url = urls.id"; 0823 if( d->linkedTables & Private::ARTIST_TAB ) 0824 d->queryFrom += " LEFT JOIN artists ON tracks.artist = artists.id"; 0825 if( d->linkedTables & Private::ALBUM_TAB ) 0826 d->queryFrom += " LEFT JOIN albums ON tracks.album = albums.id"; 0827 if( d->linkedTables & Private::ALBUMARTIST_TAB ) 0828 d->queryFrom += " LEFT JOIN artists AS albumartists ON albums.artist = albumartists.id"; 0829 if( d->linkedTables & Private::GENRE_TAB ) 0830 d->queryFrom += " LEFT JOIN genres ON tracks.genre = genres.id"; 0831 if( d->linkedTables & Private::COMPOSER_TAB ) 0832 d->queryFrom += " LEFT JOIN composers ON tracks.composer = composers.id"; 0833 if( d->linkedTables & Private::YEAR_TAB ) 0834 d->queryFrom += " LEFT JOIN years ON tracks.year = years.id"; 0835 if( d->linkedTables & Private::STATISTICS_TAB ) 0836 { 0837 if( d->linkedTables & Private::URLS_TAB ) 0838 { 0839 d->queryFrom += " LEFT JOIN statistics ON urls.id = statistics.url"; 0840 } 0841 else 0842 { 0843 d->queryFrom += " LEFT JOIN statistics ON tracks.url = statistics.url"; 0844 } 0845 } 0846 } 0847 0848 void 0849 SqlQueryMaker::buildQuery() 0850 { 0851 //URLS is always required for dynamic collection 0852 d->linkedTables |= Private::URLS_TAB; 0853 linkTables(); 0854 QString query = "SELECT "; 0855 if ( d->withoutDuplicates ) 0856 query += "DISTINCT "; 0857 query += d->queryReturnValues; 0858 query += " FROM "; 0859 query += d->queryFrom; 0860 0861 // dynamic collection (only mounted file systems are considered) 0862 if( (d->linkedTables & Private::URLS_TAB) && m_collection->mountPointManager() ) 0863 { 0864 query += " WHERE 1 "; 0865 IdList list = m_collection->mountPointManager()->getMountedDeviceIds(); 0866 if( !list.isEmpty() ) 0867 { 0868 QString commaSeparatedIds; 0869 foreach( int id, list ) 0870 { 0871 if( !commaSeparatedIds.isEmpty() ) 0872 commaSeparatedIds += ','; 0873 commaSeparatedIds += QString::number( id ); 0874 } 0875 query += QString( " AND urls.deviceid in (%1)" ).arg( commaSeparatedIds ); 0876 } 0877 } 0878 0879 switch( d->albumMode ) 0880 { 0881 case OnlyNormalAlbums: 0882 query += " AND albums.artist IS NOT NULL "; 0883 break; 0884 case OnlyCompilations: 0885 query += " AND albums.artist IS NULL "; 0886 break; 0887 case AllAlbums: 0888 //do nothing 0889 break; 0890 } 0891 if( d->labelMode != QueryMaker::NoConstraint ) 0892 { 0893 switch( d->labelMode ) 0894 { 0895 case QueryMaker::OnlyWithLabels: 0896 query += " AND tracks.url IN "; 0897 break; 0898 0899 case QueryMaker::OnlyWithoutLabels: 0900 query += " AND tracks.url NOT IN "; 0901 break; 0902 0903 case QueryMaker::NoConstraint: 0904 //do nothing, will never be called 0905 break; 0906 } 0907 query += " (SELECT DISTINCT url FROM urls_labels) "; 0908 } 0909 0910 query += d->queryMatch; 0911 if ( !d->queryFilter.isEmpty() ) 0912 { 0913 query += " AND ( 1 "; 0914 query += d->queryFilter; 0915 query += " ) "; 0916 } 0917 query += d->queryOrderBy; 0918 if ( d->maxResultSize > -1 ) 0919 query += QString( " LIMIT %1 OFFSET 0 " ).arg( d->maxResultSize ); 0920 query += ';'; 0921 d->query = query; 0922 } 0923 0924 QString 0925 SqlQueryMaker::query() 0926 { 0927 if ( d->query.isEmpty() ) 0928 buildQuery(); 0929 return d->query; 0930 } 0931 0932 QStringList 0933 SqlQueryMaker::runQuery( const QString &query ) 0934 { 0935 return m_collection->sqlStorage()->query( query ); 0936 } 0937 0938 void 0939 SqlQueryMaker::setBlocking( bool enabled ) 0940 { 0941 d->blocking = enabled; 0942 } 0943 0944 QStringList 0945 SqlQueryMaker::collectionIds() const 0946 { 0947 QStringList list; 0948 list << m_collection->collectionId(); 0949 return list; 0950 } 0951 0952 Meta::TrackList 0953 SqlQueryMaker::tracks() const 0954 { 0955 return d->blockingTracks; 0956 } 0957 0958 Meta::AlbumList 0959 SqlQueryMaker::albums() const 0960 { 0961 return d->blockingAlbums; 0962 } 0963 0964 Meta::ArtistList 0965 SqlQueryMaker::artists() const 0966 { 0967 return d->blockingArtists; 0968 } 0969 0970 Meta::GenreList 0971 SqlQueryMaker::genres() const 0972 { 0973 return d->blockingGenres; 0974 } 0975 0976 Meta::ComposerList 0977 SqlQueryMaker::composers() const 0978 { 0979 return d->blockingComposers; 0980 } 0981 0982 Meta::YearList 0983 SqlQueryMaker::years() const 0984 { 0985 return d->blockingYears; 0986 } 0987 0988 QStringList 0989 SqlQueryMaker::customData() const 0990 { 0991 return d->blockingCustomData; 0992 } 0993 0994 Meta::LabelList 0995 SqlQueryMaker::labels() const 0996 { 0997 return d->blockingLabels; 0998 } 0999 1000 QString 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 1089 SqlQueryMaker::andOr() const 1090 { 1091 return d->andStack.top() ? " AND " : " OR "; 1092 } 1093 1094 QString 1095 SqlQueryMaker::escape( const QString &text ) const //krazy:exclude=constref 1096 { 1097 return m_collection->sqlStorage()->escape( text ); 1098 } 1099 1100 QString 1101 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 1144 SqlQueryMaker::blockingNewAlbumsReady(const Meta::AlbumList &albums) 1145 { 1146 d->blockingAlbums = albums; 1147 } 1148 1149 void 1150 SqlQueryMaker::blockingNewArtistsReady(const Meta::ArtistList &artists) 1151 { 1152 d->blockingArtists = artists; 1153 } 1154 1155 void 1156 SqlQueryMaker::blockingNewGenresReady(const Meta::GenreList &genres) 1157 { 1158 d->blockingGenres = genres; 1159 } 1160 1161 void 1162 SqlQueryMaker::blockingNewComposersReady(const Meta::ComposerList &composers) 1163 { 1164 d->blockingComposers = composers; 1165 } 1166 1167 void 1168 SqlQueryMaker::blockingNewYearsReady(const Meta::YearList &years) 1169 { 1170 d->blockingYears = years; 1171 } 1172 1173 void 1174 SqlQueryMaker::blockingNewTracksReady(const Meta::TrackList &tracks) 1175 { 1176 d->blockingTracks = tracks; 1177 } 1178 1179 void 1180 SqlQueryMaker::blockingNewResultReady(const QStringList &customData) 1181 { 1182 d->blockingCustomData = customData; 1183 } 1184 1185 void 1186 SqlQueryMaker::blockingNewLabelsReady(const Meta::LabelList &labels ) 1187 { 1188 d->blockingLabels = labels; 1189 } 1190 1191 #include "SqlQueryMaker.moc"