src/querybuilder.cpp

   1 /* This file is part of the KDE project
   2     Copyright (C) 2004 Mark Kretschmann <markey@web.de>
   3     Copyright (C) 2004 Christian Muehlhaeuser <chris@chris.de>
   4     Copyright (C) 2004 Sami Nieminen <sami.nieminen@iki.fi>
   5     Copyright (C) 2005 Ian Monroe <ian@monroe.nu>
   6     Copyright (C) 2005 Jeff Mitchell <kde-dev@emailgoeshere.com>
   7     Copyright (C) 2005 Isaiah Damron <xepo@trifault.net>
   8     Copyright (C) 2005-2007 Alexandre Pereira de Oliveira <aleprj@gmail.com>
   9     Copyright (C) 2006 Jonas Hurrelmann <j@outpo.st>
  10     Copyright (C) 2006 Shane King <kde@dontletsstart.com>
  11     Copyright (C) 2006 Peter C. Ndikuwera <pndiku@gmail.com>
  12
  13    This program is free software; you can redistribute it and/or
  14    modify it under the terms of the GNU General Public License
  15    as published by the Free Software Foundation; either version 2
  16    of the License, or (at your option) any later version.
  17
  18    This program is distributed in the hope that it will be useful,
  19    but WITHOUT ANY WARRANTY; without even the implied warranty of
  20    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21    GNU General Public License for more details.
  22
  23    You should have received a copy of the GNU General Public License
  24    along with this program; if not, write to the Free Software
  25    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
  26 */
  27
  28 #include "querybuilder.h"
  29
  30 #include "amarokconfig.h"
  31 #include "collectiondb.h"
  32 #include "expression.h"
  33 #include "mountpointmanager.h"
  34
  35 #include <KLocale>
  36
  37 //////////////////////////////////////////////////////////////////////////////////////////
  38 // CLASS QueryBuilder
  39 //////////////////////////////////////////////////////////////////////////////////////////
  40
  41 QueryBuilder::QueryBuilder()
  42 {
  43     m_OR.push(false);
  44     clear();
  45     // there are a few string members with a large number of appends. to
  46     // avoid reallocations, pre-reserve 1024 bytes and try never to assign
  47     // it, instead doing setLength(0) and appends
  48     // Note: unfortunately, QT3's setLength(), which is also called from append,
  49     // squeezes the string if it's less than 4x the length. So this is useless.
  50     // Uncomment after porting to QT4 if it's smarter about this, as the docs say.
  51 //     m_query.reserve(1024);
  52 //     m_values.reserve(1024);
  53 //     m_tables.reserve(1024);
  54 }
  55
  56
  57 void
  58 QueryBuilder::linkTables( int tables )
  59 {
  60     m_tables.setLength(0);
  61
  62     m_tables += tableName( tabSong );
  63
  64     if ( !(tables & tabSong ) )
  65     {
  66         // check if only one table is selected (does somebody know a better way to check that?)
  67         if (tables == tabAlbum || tables==tabArtist || tables==tabGenre || tables == tabYear || tables == tabStats || tables == tabPodcastEpisodes || tables == tabPodcastFolders || tables == tabPodcastChannels || tables == tabLabels) {
  68         m_tables.setLength( 0 );
  69         m_tables += tableName(tables);
  70     }
  71         else
  72             tables |= tabSong;
  73     }
  74
  75     if ( tables & tabSong )
  76     {
  77         if ( tables & tabAlbum )
  78             ((m_tables += " LEFT JOIN ") += tableName( tabAlbum)) += " ON album.id=tags.album";
  79         if ( tables & tabArtist )
  80             ((m_tables += " LEFT JOIN ") += tableName( tabArtist)) += " ON artist.id=tags.artist";
  81         if ( tables & tabComposer )
  82             ((m_tables += " LEFT JOIN ") += tableName( tabComposer)) += " ON composer.id=tags.composer";
  83         if ( tables & tabGenre )
  84             ((m_tables += " LEFT JOIN ") += tableName( tabGenre)) += " ON genre.id=tags.genre";
  85         if ( tables & tabYear )
  86             ((m_tables += " LEFT JOIN ") += tableName( tabYear)) += " ON year.id=tags.year";
  87         if ( tables & tabStats )
  88         {
  89             ((m_tables += " LEFT JOIN ") += tableName( tabStats))
  90                                       += " ON statistics.url=tags.url AND statistics.deviceid = tags.deviceid";
  91             //if ( !m_url.isEmpty() ) {
  92             //    QString url = QString( '.' ) + m_url;
  93             //    m_tables += QString( " OR statistics.deviceid = -1 AND statistics.url = '%1'" )
  94             //                                    .arg( CollectionDB::instance()->escapeString( url ) );
  95             //}
  96         }
  97         if ( tables & tabLyrics )
  98             ((m_tables += " LEFT JOIN ") += tableName( tabLyrics))
  99                                       += " ON lyrics.url=tags.url AND lyrics.deviceid = tags.deviceid";
 100
 101         if ( tables & tabDevices )
 102             ((m_tables += " LEFT JOIN ") += tableName( tabDevices )) += " ON tags.deviceid = devices.id";
 103         if ( tables & tabLabels )
 104             ( m_tables += " LEFT JOIN tags_labels ON tags.url = tags_labels.url AND tags.deviceid = tags_labels.deviceid" )
 105                 += " LEFT JOIN labels ON tags_labels.labelid = labels.id";
 106     }
 107 }
 108
 109
 110 void
 111 QueryBuilder::addReturnValue( int table, qint64 value, bool caseSensitive /* = false, unless value refers to a string */ )
 112 {
 113     caseSensitive |= value == valName || value == valTitle || value == valComment;
 114
 115     if ( !m_values.isEmpty() && m_values != "DISTINCT " ) m_values += ',';
 116
 117     if ( value == valDummy )
 118         m_values += "''";
 119     else
 120     {
 121         if ( caseSensitive && CollectionDB::instance()->getType() == DbConnection::mysql )
 122             m_values += "BINARY ";
 123         m_values += tableName( table ) + '.';
 124         m_values += valueName( value );
 125     }
 126
 127     m_linkTables |= table;
 128     m_returnValues++;
 129     if ( value & valURL )
 130     {
 131         // make handling of deviceid transparent to calling code
 132         m_deviceidPos = m_returnValues + 1;  //the return value after the url is the deviceid
 133         m_values += ',';
 134         m_values += tableName( table );
 135         m_values += '.';
 136         m_values += valueName( valDeviceId );
 137     }
 138 }
 139
 140 void
 141 QueryBuilder::addReturnFunctionValue( int function, int table, qint64 value)
 142 {
 143     // translate NULL and 0 values into the default value for percentage/rating
 144     // First translate 0 to NULL via NULLIF, then NULL to default via COALESCE
 145     bool defaults = function == funcAvg && ( value & valScore || value & valRating );
 146
 147     if ( !m_values.isEmpty() && m_values != "DISTINCT " ) m_values += ',';
 148     m_values += functionName( function ) + '(';
 149     if ( defaults )
 150         m_values += "COALESCE(NULLIF(";
 151     m_values += tableName( table ) + '.';
 152     m_values += valueName( value );
 153     if ( defaults )
 154     {
 155         m_values += ", 0), ";
 156         if ( value & valScore )
 157             m_values += "50";
 158         else
 159             m_values += '6';
 160         m_values += ')';
 161     }
 162     m_values += ") AS ";
 163     m_values += functionName( function )+tableName( table )+valueName( value );
 164
 165     m_linkTables |= table;
 166     if ( !m_showAll ) m_linkTables |= tabSong;
 167     m_returnValues++;
 168 }
 169
 170 uint
 171 QueryBuilder::countReturnValues()
 172 {
 173     return m_returnValues;
 174 }
 175
 176 void
 177 QueryBuilder::addUrlFilters( const QStringList& filter )
 178 {
 179     if ( !filter.isEmpty() )
 180     {
 181         m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolF() + ' ';
 182
 183         for ( uint i = 0; i < filter.count(); i++ )
 184         {
 185                 int deviceid = MountPointManager::instance()->getIdForUrl( filter[i] );
 186                 QString rpath = MountPointManager::instance()->getRelativePath( deviceid , filter[i] );
 187                 m_where += "OR (tags.url = '" + CollectionDB::instance()->escapeString( rpath ) + "' ";
 188                 m_where += QString( "AND tags.deviceid = %1 ) " ).arg( QString::number( deviceid ) );
 189                 //TODO MountPointManager fix this
 190         }
 191
 192         m_where += " ) ";
 193     }
 194
 195     m_linkTables |= tabSong;
 196 }
 197
 198 void
 199 QueryBuilder::setGoogleFilter( int defaultTables, QString query )
 200 {
 201     //TODO MountPointManager fix google syntax
 202     //no clue about what needs to be done atm
 203     ParsedExpression parsed = ExpressionParser::parse( query );
 204
 205     for( uint i = 0, n = parsed.count(); i < n; ++i ) //check each part for matchiness
 206     {
 207         beginOR();
 208         for( uint ii = 0, nn = parsed[i].count(); ii < nn; ++ii )
 209         {
 210             const expression_element &e = parsed[i][ii];
 211             QString s = e.text;
 212             int mode;
 213             switch( e.match )
 214             {
 215                 case expression_element::More:     mode = modeGreater; break;
 216                 case expression_element::Less:     mode = modeLess;    break;
 217                 case expression_element::Contains:
 218                 default:                           mode = modeNormal;  break;
 219             }
 220             bool exact = false; // enable for numeric values
 221
 222             int table = -1;
 223             qint64 value = -1;
 224             if( e.field == "artist" )
 225                 table = tabArtist;
 226             else if( e.field == "composer" )
 227                 table = tabComposer;
 228             else if( e.field == "album" )
 229                 table = tabAlbum;
 230             else if( e.field == "title" )
 231                 table = tabSong;
 232             else if( e.field == "genre" )
 233                 table = tabGenre;
 234             else if( e.field == "year" )
 235             {
 236                 table = tabYear;
 237                 value = valName;
 238                 exact = true;
 239             }
 240             else if( e.field == "score" )
 241             {
 242                 table = tabStats;
 243                 value = valScore;
 244                 exact = true;
 245             }
 246             else if( e.field == "rating" )
 247             {
 248                 table = tabStats;
 249                 value = valRating;
 250                 exact = true;
 251                 s = QString::number( int( s.toFloat() * 2 ) );
 252             }
 253             else if( e.field == "directory" )
 254             {
 255                 table = tabSong;
 256                 value = valDirectory;
 257             }
 258             else if( e.field == "length" )
 259             {
 260                 table = tabSong;
 261                 value = valLength;
 262                 exact = true;
 263             }
 264             else if( e.field == "playcount" )
 265             {
 266                 table = tabStats;
 267                 value = valPlayCounter;
 268                 exact = true;
 269             }
 270             else if( e.field == "samplerate" )
 271             {
 272                 table = tabSong;
 273                 value = valSamplerate;
 274                 exact = true;
 275             }
 276             else if( e.field == "track" )
 277             {
 278                 table = tabSong;
 279                 value = valTrack;
 280                 exact = true;
 281             }
 282             else if( e.field == "disc" || e.field == "discnumber" )
 283             {
 284                 table = tabSong;
 285                 value = valDiscNumber;
 286                 exact = true;
 287             }
 288             else if( e.field == "size" || e.field == "filesize" )
 289             {
 290                 table = tabSong;
 291                 value = valFilesize;
 292                 exact = true;
 293                 if( s.toLower().endsWith( "m" ) )
 294                     s = QString::number( s.left( s.length()-1 ).toLong() * 1024 * 1024 );
 295                 else if( s.toLower().endsWith( "k" ) )
 296                     s = QString::number( s.left( s.length()-1 ).toLong() * 1024 );
 297             }
 298             else if( e.field == "filename" || e.field == "url" )
 299             {
 300                 table = tabSong;
 301                 value = valURL;
 302             }
 303             else if( e.field == "filetype" || e.field == "type" )
 304             {
 305                 table = tabSong;
 306                 value = valURL;
 307                 mode = modeEndMatch;
 308                 s.prepend( '.' );
 309             }
 310             else if( e.field == "bitrate" )
 311             {
 312                 table = tabSong;
 313                 value = valBitrate;
 314                 exact = true;
 315             }
 316             else if( e.field == "comment" )
 317             {
 318                 table = tabSong;
 319                 value = valComment;
 320             }
 321             else if( e.field == "bpm" )
 322             {
 323                 table = tabSong;
 324                 value = valBPM;
 325                 exact = true;
 326             }
 327             else if( e.field == "lyrics" )
 328             {
 329                 table = tabLyrics;
 330                 value = valLyrics;
 331             }
 332             else if( e.field == "device" )
 333             {
 334                 table = tabDevices;
 335                 value = valDeviceLabel;
 336             }
 337             else if( e.field == "mountpoint" )
 338             {
 339                 table = tabDevices;
 340                 value = valMountPoint;
 341             }
 342             else if( e.field == "label" )
 343             {
 344                 table = tabLabels;
 345                 value = valName;
 346             }
 347
 348             if( e.negate )
 349             {
 350                 if( value >= 0 )
 351                     excludeFilter( table, value, s, mode, exact );
 352                 else
 353                     excludeFilter( table >= 0 ? table : defaultTables, s );
 354             }
 355             else
 356             {
 357                 if( value >= 0 )
 358                     addFilter( table, value, s, mode, exact );
 359                 else
 360                     addFilter( table >= 0 ? table : defaultTables, s );
 361             }
 362         }
 363         endOR();
 364     }
 365 }
 366
 367 void
 368 QueryBuilder::addFilter( int tables, const QString& filter )
 369 {
 370     if ( !filter.isEmpty() )
 371     {
 372         m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolF() + ' ';
 373
 374         if ( tables & tabAlbum )
 375             m_where += "OR album.name " + CollectionDB::likeCondition( filter, true, true );
 376         if ( tables & tabArtist )
 377             m_where += "OR artist.name " + CollectionDB::likeCondition( filter, true, true );
 378         if ( tables & tabComposer )
 379             m_where += "OR composer.name " + CollectionDB::likeCondition( filter, true, true );
 380         if ( tables & tabGenre )
 381             m_where += "OR genre.name " + CollectionDB::likeCondition( filter, true, true );
 382         if ( tables & tabYear )
 383             m_where += "OR year.name " + CollectionDB::likeCondition( filter, false, false );
 384         if ( tables & tabSong )
 385             m_where += "OR tags.title " + CollectionDB::likeCondition( filter, true, true );
 386         if ( tables & tabLabels )
 387             m_where += "OR labels.name " + CollectionDB::likeCondition( filter, true, true );
 388
 389         if ( i18n( "Unknown" ).contains( filter, false ) )
 390         {
 391             if ( tables & tabAlbum )
 392                 m_where += "OR album.name = '' ";
 393             if ( tables & tabArtist )
 394                 m_where += "OR artist.name = '' ";
 395             if ( tables & tabComposer )
 396                 m_where += "OR composer.name = '' ";
 397             if ( tables & tabGenre )
 398                 m_where += "OR genre.name = '' ";
 399             if ( tables & tabYear )
 400                 m_where += "OR year.name = '' ";
 401             if ( tables & tabSong )
 402                 m_where += "OR tags.title = '' ";
 403         }
 404         if ( ( tables & tabArtist ) && i18n( "Various Artists" ).contains( filter, false ) )
 405             m_where += QString( "OR tags.sampler = %1 " ).arg( CollectionDB::instance()->boolT() );
 406         m_where += " ) ";
 407     }
 408
 409     m_linkTables |= tables;
 410 }
 411
 412 void
 413 QueryBuilder::addFilter( int tables, qint64 value, const QString& filter, int mode, bool exact )
 414 {
 415     //true for INTEGER fields (see comment of coalesceField(int, qint64)
 416     bool useCoalesce = coalesceField( tables, value );
 417     m_where += ANDslashOR() + " ( ";
 418
 419     QString m, s;
 420     if (mode == modeLess || mode == modeGreater)
 421     {
 422         QString escapedFilter;
 423         if (useCoalesce && DbConnection::sqlite == CollectionDB::instance()->getDbConnectionType())
 424             escapedFilter = CollectionDB::instance()->escapeString( filter );
 425         else
 426             escapedFilter = '\'' + CollectionDB::instance()->escapeString( filter ) + "' ";
 427         s = ( mode == modeLess ? "< " : "> " ) + escapedFilter;
 428     }
 429     else
 430     {
 431         if (exact)
 432             if (useCoalesce && DbConnection::sqlite == CollectionDB::instance()->getDbConnectionType())
 433                 s = " = " +CollectionDB::instance()->escapeString( filter ) + ' ';
 434             else
 435                 s = " = '" + CollectionDB::instance()->escapeString( filter ) + "' ";
 436         else
 437             s = CollectionDB::likeCondition( filter, mode != modeBeginMatch, mode != modeEndMatch );
 438     }
 439
 440     if( coalesceField( tables, value ) )
 441         m_where += QString( "COALESCE(%1.%2,0) " ).arg( tableName( tables ) ).arg( valueName( value ) ) + s;
 442     else
 443         m_where += QString( "%1.%2 " ).arg( tableName( tables ) ).arg( valueName( value ) ) + s;
 444
 445     if ( !exact && (value & valName) && mode == modeNormal && i18n( "Unknown").contains( filter, false ) )
 446         m_where += QString( "OR %1.%2 = '' " ).arg( tableName( tables ) ).arg( valueName( value ) );
 447
 448     m_where += " ) ";
 449
 450     m_linkTables |= tables;
 451 }
 452
 453 void
 454 QueryBuilder::addFilters( int tables, const QStringList& filter )
 455 {
 456     if ( !filter.isEmpty() )
 457     {
 458         m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolT() + ' ';
 459
 460         for ( uint i = 0; i < filter.count(); i++ )
 461         {
 462             m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolF() + ' ';
 463
 464             if ( tables & tabAlbum )
 465                 m_where += "OR album.name " + CollectionDB::likeCondition( filter[i], true, true );
 466             if ( tables & tabArtist )
 467                 m_where += "OR artist.name " + CollectionDB::likeCondition( filter[i], true, true );
 468             if ( tables & tabComposer )
 469                 m_where += "OR composer.name " + CollectionDB::likeCondition( filter[i], true, true );
 470             if ( tables & tabGenre )
 471                 m_where += "OR genre.name " + CollectionDB::likeCondition( filter[i], true, true );
 472             if ( tables & tabYear )
 473                 m_where += "OR year.name " + CollectionDB::likeCondition( filter[i], false, false );
 474             if ( tables & tabSong )
 475                 m_where += "OR tags.title " + CollectionDB::likeCondition( filter[i], true, true );
 476             if ( tables & tabLabels )
 477                 m_where += "OR labels.name " + CollectionDB::likeCondition( filter[i], true, true );
 478
 479             if ( i18n( "Unknown" ).contains( filter[i], false ) )
 480             {
 481                 if ( tables & tabAlbum )
 482                     m_where += "OR album.name = '' ";
 483                 if ( tables & tabArtist )
 484                     m_where += "OR artist.name = '' ";
 485                 if ( tables & tabComposer )
 486                     m_where += "OR composer.name = '' ";
 487                 if ( tables & tabGenre )
 488                     m_where += "OR genre.name = '' ";
 489                 if ( tables & tabYear )
 490                     m_where += "OR year.name = '' ";
 491                 if ( tables & tabSong )
 492                     m_where += "OR tags.title = '' ";
 493             }
 494             if ( i18n( "Various Artists" ).contains( filter[ i ], false ) && ( tables & tabArtist ) )
 495                 m_where += "OR tags.sampler = " + CollectionDB::instance()->boolT() + ' ';
 496             m_where += " ) ";
 497         }
 498
 499         m_where += " ) ";
 500     }
 501
 502     m_linkTables |= tables;
 503 }
 504
 505 void
 506 QueryBuilder::excludeFilter( int tables, const QString& filter )
 507 {
 508     if ( !filter.isEmpty() )
 509     {
 510         m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolT() + ' ';
 511
 512
 513         if ( tables & tabAlbum )
 514             m_where += "AND album.name NOT " + CollectionDB::likeCondition( filter, true, true );
 515         if ( tables & tabArtist )
 516             m_where += "AND artist.name NOT " + CollectionDB::likeCondition( filter, true, true );
 517         if ( tables & tabComposer )
 518             m_where += "AND composer.name NOT " + CollectionDB::likeCondition( filter, true, true );
 519         if ( tables & tabGenre )
 520             m_where += "AND genre.name NOT " + CollectionDB::likeCondition( filter, true, true );
 521         if ( tables & tabYear )
 522             m_where += "AND year.name NOT " + CollectionDB::likeCondition( filter, false, false );
 523         if ( tables & tabSong )
 524             m_where += "AND tags.title NOT " + CollectionDB::likeCondition( filter, true, true );
 525         if ( tables & tabLabels )
 526             m_where += "AND labels.name NOT " + CollectionDB::likeCondition( filter, true, true );
 527
 528         if ( i18n( "Unknown" ).contains( filter, false ) )
 529         {
 530             if ( tables & tabAlbum )
 531                 m_where += "AND album.name <> '' ";
 532             if ( tables & tabArtist )
 533                 m_where += "AND artist.name <> '' ";
 534             if ( tables & tabComposer )
 535                 m_where += "AND composer.name <> '' ";
 536             if ( tables & tabGenre )
 537                 m_where += "AND genre.name <> '' ";
 538             if ( tables & tabYear )
 539                 m_where += "AND year.name <> '' ";
 540             if ( tables & tabSong )
 541                 m_where += "AND tags.title <> '' ";
 542         }
 543
 544        if ( i18n( "Various Artists" ).contains( filter, false ) && (  tables & tabArtist ) )
 545             m_where += "AND tags.sampler = " + CollectionDB::instance()->boolF() + ' ';
 546
 547
 548         m_where += " ) ";
 549     }
 550
 551     m_linkTables |= tables;
 552 }
 553
 554 void
 555 QueryBuilder::excludeFilter( int tables, qint64 value, const QString& filter, int mode, bool exact )
 556 {
 557     m_where += ANDslashOR() + " ( ";
 558
 559     QString m, s;
 560     if (mode == modeLess || mode == modeGreater)
 561         s = ( mode == modeLess ? ">= '" : "<= '" ) + CollectionDB::instance()->escapeString( filter ) + "' ";
 562     else
 563     {
 564         if (exact)
 565         {
 566             bool isNumber;
 567             filter.toInt( &isNumber );
 568             if (isNumber)
 569                 s = " <> " + CollectionDB::instance()->escapeString( filter ) + ' ';
 570             else
 571                 s = " <> '" + CollectionDB::instance()->escapeString( filter ) + "' ";
 572         }
 573         else
 574             s = "NOT " + CollectionDB::instance()->likeCondition( filter, mode != modeBeginMatch, mode != modeEndMatch ) + ' ';
 575     }
 576
 577     if( coalesceField( tables, value ) )
 578         m_where += QString( "COALESCE(%1.%2,0) " ).arg( tableName( tables ) ).arg( valueName( value ) ) + s;
 579     else
 580         m_where += QString( "%1.%2 " ).arg( tableName( tables ) ).arg( valueName( value ) ) + s;
 581
 582     if ( !exact && (value & valName) && mode == modeNormal && i18n( "Unknown").contains( filter, false ) )
 583         m_where += QString( "AND %1.%2 <> '' " ).arg( tableName( tables ) ).arg( valueName( value ) );
 584
 585     m_where += " ) ";
 586
 587     m_linkTables |= tables;
 588 }
 589
 590 void
 591 QueryBuilder::addMatch( int tables, const QString& match, bool interpretUnknown /* = true */, bool caseSensitive /* = true */ )
 592 {
 593     QString matchCondition = caseSensitive ? CollectionDB::exactCondition( match ) : CollectionDB::likeCondition( match );
 594
 595     (((m_where += ANDslashOR()) += " ( ") += CollectionDB::instance()->boolF()) += ' ';
 596     if ( tables & tabAlbum )
 597         (m_where += "OR album.name ") += matchCondition;
 598     if ( tables & tabArtist )
 599         (m_where += "OR artist.name ") += matchCondition;
 600     if ( tables & tabComposer )
 601         (m_where += "OR composer.name ") += matchCondition;
 602     if ( tables & tabGenre )
 603         (m_where += "OR genre.name ") += matchCondition;
 604     if ( tables & tabYear )
 605         (m_where += "OR year.name ") += matchCondition;
 606     if ( tables & tabSong )
 607         (m_where += "OR tags.title ") += matchCondition;
 608     if ( tables & tabLabels )
 609         (m_where += "OR labels.name ") += matchCondition;
 610
 611     static QString i18nUnknown = i18n("Unknown");
 612
 613     if ( interpretUnknown && match == i18nUnknown )
 614     {
 615         if ( tables & tabAlbum ) m_where += "OR album.name = '' ";
 616         if ( tables & tabArtist ) m_where += "OR artist.name = '' ";
 617         if ( tables & tabComposer ) m_where += "OR composer.name = '' ";
 618         if ( tables & tabGenre ) m_where += "OR genre.name = '' ";
 619         if ( tables & tabYear ) m_where += "OR year.name = '' ";
 620     }
 621     if ( tables & tabLabels && match.isEmpty() )
 622         m_where += " OR labels.name IS NULL ";
 623     m_where += " ) ";
 624
 625     m_linkTables |= tables;
 626 }
 627
 628
 629 void
 630 QueryBuilder::addMatch( int tables, qint64 value, const QString& match, bool interpretUnknown /* = true */, bool caseSensitive /* = true */  )
 631 {
 632     m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolF() + ' ';
 633     if ( value & valURL )
 634         m_url = match;
 635     //FIXME max: doesn't work yet if we are querying for the mount point part of a directory
 636     if ( value & valURL || value & valDirectory )
 637     {
 638         int deviceid = MountPointManager::instance()->getIdForUrl( match );
 639         QString rpath = MountPointManager::instance()->getRelativePath( deviceid, match );
 640         //we are querying for a specific path, so we don't need the tags.deviceid IN (...) stuff
 641         //which is automatially appended if m_showAll = false
 642         m_showAll = true;
 643         m_where += QString( "OR %1.%2 " )
 644             .arg( tableName( tables ) )
 645             .arg( valueName( value ) );
 646         m_where += caseSensitive ? CollectionDB::exactCondition( rpath ) : CollectionDB::likeCondition( rpath );
 647         m_where += QString( " AND %1.deviceid = %2 " ).arg( tableName( tables ) ).arg( deviceid );
 648         if ( deviceid != -1 )
 649         {
 650             //handle corner case
 651             QString rpath2( '.' + match );
 652             m_where += QString( " OR %1.%2 " ).arg( tableName( tables ) ).arg( valueName( value ) );
 653             m_where += caseSensitive ? CollectionDB::exactCondition( rpath2 ) : CollectionDB::likeCondition( rpath2 );
 654             m_where += QString( " AND %1.deviceid = -1 " ).arg( tableName( tables ) );
 655         }
 656     }
 657     else
 658     {
 659         m_where += QString( "OR %1.%2 " ).arg( tableName( tables ) ).arg( valueName( value ) );
 660         m_where += caseSensitive ? CollectionDB::exactCondition( match ) : CollectionDB::likeCondition( match );
 661     }
 662
 663     if ( ( value & valName ) && interpretUnknown && match == i18n( "Unknown" ) )
 664         m_where += QString( "OR %1.%2 = '' " ).arg( tableName( tables ) ).arg( valueName( value ) );
 665
 666     m_where += " ) ";
 667
 668     m_linkTables |= tables;
 669 }
 670
 671
 672 void
 673 QueryBuilder::addMatches( int tables, const QStringList& match, bool interpretUnknown /* = true */, bool caseSensitive /* = true */ )
 674 {
 675     QStringList matchConditions;
 676     for ( uint i = 0; i < match.count(); i++ )
 677         matchConditions << ( caseSensitive ? CollectionDB::exactCondition( match[i] ) : CollectionDB::likeCondition( match[i] ) );
 678
 679     m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolF() + ' ';
 680
 681     for ( uint i = 0; i < match.count(); i++ )
 682     {
 683         if ( tables & tabAlbum )
 684             m_where += "OR album.name " + matchConditions[ i ];
 685         if ( tables & tabArtist )
 686             m_where += "OR artist.name " + matchConditions[ i ];
 687         if ( tables & tabComposer )
 688             m_where += "OR composer.name " + matchConditions[ i ];
 689         if ( tables & tabGenre )
 690             m_where += "OR genre.name " + matchConditions[ i ];
 691         if ( tables & tabYear )
 692             m_where += "OR year.name " + matchConditions[ i ];
 693         if ( tables & tabSong )
 694             m_where += "OR tags.title " + matchConditions[ i ];
 695         if ( tables & tabStats )
 696             m_where += "OR statistics.url " + matchConditions[ i ];
 697         if ( tables & tabLabels )
 698             (m_where += "OR labels.name ") += matchConditions[ i ];
 699
 700
 701         if ( interpretUnknown && match[i] == i18n( "Unknown" ) )
 702         {
 703             if ( tables & tabAlbum ) m_where += "OR album.name = '' ";
 704             if ( tables & tabArtist ) m_where += "OR artist.name = '' ";
 705             if ( tables & tabComposer ) m_where += "OR composer.name = '' ";
 706             if ( tables & tabGenre ) m_where += "OR genre.name = '' ";
 707             if ( tables & tabYear ) m_where += "OR year.name = '' ";
 708         }
 709         if ( tables & tabLabels && match[i].isEmpty() )
 710             m_where += " OR labels.name IS NULL ";
 711     }
 712
 713     m_where += " ) ";
 714     m_linkTables |= tables;
 715 }
 716
 717 void
 718 QueryBuilder::excludeMatch( int tables, const QString& match )
 719 {
 720     m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolT() + ' ';
 721     if ( tables & tabAlbum ) m_where += "AND album.name <> '" + CollectionDB::instance()->escapeString( match ) + "' ";
 722     if ( tables & tabArtist ) m_where += "AND artist.name <> '" + CollectionDB::instance()->escapeString( match ) + "' ";
 723     if ( tables & tabComposer ) m_where += "AND composer.name <> '" + CollectionDB::instance()->escapeString( match ) + "' ";
 724     if ( tables & tabGenre ) m_where += "AND genre.name <> '" + CollectionDB::instance()->escapeString( match ) + "' ";
 725     if ( tables & tabYear ) m_where += "AND year.name <> '" + CollectionDB::instance()->escapeString( match ) + "' ";
 726     if ( tables & tabSong ) m_where += "AND tags.title <> '" + CollectionDB::instance()->escapeString( match ) + "' ";
 727     if ( tables & tabLabels ) m_where += "AND labels.name <> '" + CollectionDB::instance()->escapeString( match ) + "' ";
 728
 729     if ( match == i18n( "Unknown" ) )
 730     {
 731         if ( tables & tabAlbum ) m_where += "AND album.name <> '' ";
 732         if ( tables & tabArtist ) m_where += "AND artist.name <> '' ";
 733         if ( tables & tabComposer ) m_where += "AND composer.name <> '' ";
 734         if ( tables & tabGenre ) m_where += "AND genre.name <> '' ";
 735         if ( tables & tabYear ) m_where += "AND year.name <> '' ";
 736     }
 737     m_where += " ) ";
 738
 739     m_linkTables |= tables;
 740 }
 741
 742
 743 void
 744 QueryBuilder::exclusiveFilter( int tableMatching, int tableNotMatching, qint64 value )
 745 {
 746     m_where += " AND ";
 747     m_where += tableName( tableNotMatching ) + '.';
 748     m_where += valueName( value );
 749     m_where += " IS null ";
 750
 751     m_linkTables |= tableMatching;
 752     m_linkTables |= tableNotMatching;
 753 }
 754
 755
 756 void
 757 QueryBuilder::addNumericFilter(int tables, qint64 value, const QString &n,
 758                                int mode /* = modeNormal */,
 759                                const QString &endRange /* = QString::null */ )
 760 {
 761     m_where.append( ANDslashOR() ).append( " ( " );
 762
 763     if ( coalesceField( tables, value) )
 764         m_where.append("COALESCE(");
 765
 766     m_where.append( tableName( tables ) ).append( '.' ).append( valueName( value ) );
 767
 768     if ( coalesceField( tables, value) )
 769         m_where.append(",0)");
 770
 771     switch (mode) {
 772     case modeNormal:
 773         m_where.append( " = " ); break;
 774     case modeLess:
 775         m_where.append( " < " ); break;
 776     case modeGreater:
 777         m_where.append( " > " ); break;
 778     case modeBetween:
 779         m_where.append( " BETWEEN " ); break;
 780     case modeNotBetween:
 781         m_where.append(" NOT BETWEEN "); break;
 782     default:
 783         qWarning( "Unhandled mode in addNumericFilter, using equals: %d", mode );
 784         m_where.append( " = " );
 785     }
 786
 787     m_where.append( n );
 788     if ( mode == modeBetween || mode == modeNotBetween )
 789         m_where.append( " AND " ).append( endRange );
 790
 791     m_where.append( " ) " );
 792     m_linkTables |= tables;
 793 }
 794
 795
 796
 797 void
 798 QueryBuilder::setOptions( int options )
 799 {
 800     if ( options & optNoCompilations || options & optOnlyCompilations )
 801         m_linkTables |= tabSong;
 802
 803     if ( options & optNoCompilations ) m_where += QString("AND tags.sampler = %1 ").arg(CollectionDB::instance()->boolF());
 804     if ( options & optOnlyCompilations ) m_where += QString("AND tags.sampler = %1 ").arg(CollectionDB::instance()->boolT());
 805
 806     if (CollectionDB::instance()->getType() == DbConnection::postgresql && options & optRemoveDuplicates && options & optRandomize)
 807     {
 808             m_values = "DISTINCT " + CollectionDB::instance()->randomFunc() + " AS __random "+ m_values;
 809             if ( !m_sort.isEmpty() )
 810                 m_sort += ',';
 811             m_sort += CollectionDB::instance()->randomFunc() + ' ';
 812     }
 813     else
 814     {
 815             if ( options & optRemoveDuplicates )
 816                 m_values = "DISTINCT " + m_values;
 817             if ( options & optRandomize )
 818             {
 819                 if ( !m_sort.isEmpty() ) m_sort += ',';
 820                 m_sort += CollectionDB::instance()->randomFunc() + ' ';
 821             }
 822     }
 823
 824     if ( options & optShowAll ) m_showAll = true;
 825 }
 826
 827
 828 void
 829 QueryBuilder::sortBy( int table, qint64 value, bool descending )
 830 {
 831     //shall we sort case-sensitively? (not for integer columns!)
 832     bool b = true;
 833     if ( value & valID || value & valTrack || value & valScore || value & valRating || value & valLength || value & valBitrate ||
 834          value & valSamplerate || value & valPlayCounter || value & valAccessDate || value & valCreateDate ||
 835          value & valFilesize || value & valDiscNumber ||
 836          table & tabYear )
 837         b = false;
 838
 839         // only coalesce for certain columns
 840         bool c = false;
 841     if ( value & valScore || value & valRating || value & valPlayCounter || value & valAccessDate || value & valCreateDate )
 842                 c = true;
 843
 844     if ( !m_sort.isEmpty() ) m_sort += ',';
 845     if ( b ) m_sort += "LOWER( ";
 846     if ( c ) m_sort += "COALESCE( ";
 847
 848     m_sort += tableName( table ) + '.';
 849     m_sort += valueName( value );
 850
 851     if ( c ) m_sort += ", 0 )";
 852
 853     if ( b ) m_sort += " ) ";
 854     if ( descending ) m_sort += " DESC ";
 855
 856     if (CollectionDB::instance()->getType() == DbConnection::postgresql)
 857     {
 858         if (!m_values.isEmpty()) m_values += ',';
 859         if ( b ) m_values += "LOWER( ";
 860         m_values += tableName( table ) + '.';
 861         m_values += valueName( value );
 862         if ( b ) m_values += ')';
 863         m_values += " as __discard ";
 864     }
 865
 866     m_linkTables |= table;
 867 }
 868
 869 void
 870 QueryBuilder::sortByFunction( int function, int table, qint64 value, bool descending )
 871 {
 872     // This function should be used with the equivalent addReturnFunctionValue (with the same function on same values)
 873     // since it uses the "func(table.value) AS functablevalue" definition.
 874
 875     // this column is already coalesced, but need to reconstruct for postgres
 876     bool defaults = function == funcAvg && ( value & valScore || value & valRating );
 877
 878     //shall we sort case-sensitively? (not for integer columns!)
 879     bool b = true;
 880     if ( value & valID || value & valTrack || value & valScore || value & valRating || value & valLength || value & valBitrate ||
 881          value & valSamplerate || value & valPlayCounter || value & valAccessDate || value & valCreateDate ||
 882          value & valFilesize || value & valDiscNumber ||
 883          table & tabYear )
 884         b = false;
 885
 886     // only coalesce for certain columns
 887     bool c = false;
 888     if ( !defaults && ( value & valScore || value & valRating || value & valPlayCounter || value & valAccessDate || value & valCreateDate ) )
 889         c = true;
 890
 891     if ( !m_sort.isEmpty() ) m_sort += ',';
 892     //m_sort += functionName( function ) + '(';
 893     if ( b ) m_sort += "LOWER( ";
 894     if ( c && CollectionDB::instance()->getType() != DbConnection::mysql) m_sort += "COALESCE( ";
 895
 896     QString columnName;
 897
 898     if (CollectionDB::instance()->getType() == DbConnection::postgresql)
 899     {
 900         columnName = functionName( function ) + '(';
 901         if ( defaults )
 902             columnName += "COALESCE(NULLIF(";
 903         columnName += tableName( table )+'.'+valueName( value );
 904         if ( defaults )
 905         {
 906             columnName += ", 0), ";
 907             if ( value & valScore )
 908                 columnName += "50";
 909             else
 910                 columnName += '6';
 911             columnName += ')';
 912         }
 913         columnName += ')';
 914     }
 915     else
 916         columnName = functionName( function )+tableName( table )+valueName( value );
 917
 918     m_sort += columnName;
 919
 920     if ( c && CollectionDB::instance()->getType() != DbConnection::mysql) m_sort += ", 0 )";
 921
 922     if ( b ) m_sort += " ) ";
 923     //m_sort += " ) ";
 924     if ( descending ) m_sort += " DESC ";
 925
 926     m_linkTables |= table;
 927 }
 928
 929 void
 930 QueryBuilder::groupBy( int table, qint64 value )
 931 {
 932     if ( !m_group.isEmpty() ) m_group += ',';
 933
 934     //Do case-sensitive comparisons for MySQL too. See also QueryBuilder::addReturnValue
 935     if ( DbConnection::mysql == CollectionDB::instance()->getDbConnectionType() &&
 936          ( value == valName || value == valTitle || value == valComment ) )
 937     {
 938         m_group += "BINARY ";
 939     }
 940
 941     m_group += tableName( table ) + '.';
 942     m_group += valueName( value );
 943
 944     m_linkTables |= table;
 945 }
 946
 947 void
 948 QueryBuilder::having( int table, qint64 value, int function, int mode, const QString& match )
 949 {
 950     if( !m_having.isEmpty() ) m_having += " AND ";
 951
 952     QString fn = functionName( function );
 953     fn.isEmpty() ?
 954         m_having += tableName( table ) + '.' + valueName( value ) :
 955         m_having += functionName( function )+'('+tableName( table )+'.'+valueName( value )+')';
 956
 957     switch( mode )
 958     {
 959         case modeNormal:
 960             m_having += '=' + match;
 961             break;
 962
 963         case modeLess:
 964             m_having += '<' + match;
 965             break;
 966
 967         case modeGreater:
 968             m_having += '>' + match;
 969
 970         default:
 971             break;
 972     }
 973 }
 974
 975 void
 976 QueryBuilder::setLimit( int startPos, int length )
 977 {
 978     m_limit = QString( " LIMIT %2 OFFSET %1 " ).arg( startPos ).arg( length );
 979 }
 980
 981 void
 982 QueryBuilder::shuffle( int table, qint64 value )
 983 {
 984     if ( !m_sort.isEmpty() ) m_sort += " ,  ";
 985     if ( table == 0 || value == 0 ) {
 986         // simple random
 987         m_sort += CollectionDB::instance()->randomFunc();
 988     } else {
 989         // This is the score weighted random order.
 990
 991         // The RAND() function returns random values equally distributed between 0.0
 992         // (inclusive) and 1.0 (exclusive).  The obvious way to get this order is to
 993         // put every track <score> times into a list, sort the list by RAND()
 994         // (i.e. shuffle it) and discard every occurrence of every track but the very
 995         // first of each.  By putting every track into the list only once but applying
 996         // a transfer function T_s(x) := 1-(1-x)^(1/s) where s is the score, to RAND()
 997         // before sorting the list, exactly the same distribution of tracks can be
 998         // achieved (for a proof write to Stefan Siegel <kde@sdas.de>)
 999
1000         // In the query below a simplified function is used: The score is incremented
1001         // by one to prevent division by zero, RAND() is used instead of 1-RAND()
1002         // because it doesn't matter if it becomes zero (the exponent is always
1003         // non-zero), and finally POWER(...) is used instead of 1-POWER(...) because it
1004         // only changes the order type.
1005         m_sort += QString("POWER( %1, 1.0 / (%2.%3 + 1) ) DESC")
1006             .arg( CollectionDB::instance()->randomFunc() )
1007             .arg( tableName( table ) )
1008             .arg( valueName( value ) );
1009
1010         m_linkTables |= table;
1011     }
1012 }
1013
1014
1015 /* NOTE: It's important to keep these two functions and the const in sync! */
1016 /* NOTE: It's just as important to keep tags.url first! */
1017 const int
1018 QueryBuilder::dragFieldCount = 21;
1019
1020 QString
1021 QueryBuilder::dragSQLFields()
1022 {
1023     return "tags.url, tags.deviceid, album.name, artist.name, composer.name, "
1024            "genre.name, tags.title, year.name, "
1025            "tags.comment, tags.track, tags.bitrate, tags.discnumber, "
1026            "tags.length, tags.samplerate, tags.filesize, "
1027            "tags.sampler, tags.filetype, tags.bpm, "
1028            "statistics.percentage, statistics.rating, statistics.playcounter, "
1029            "statistics.accessdate";
1030 }
1031
1032 void
1033 QueryBuilder::initSQLDrag()
1034 {
1035     clear();
1036     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );
1037     addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
1038     addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
1039     addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
1040     addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
1041     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
1042     addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
1043     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
1044     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
1045     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBitrate );
1046     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
1047     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valLength );
1048     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valSamplerate );
1049     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFilesize );
1050     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valIsCompilation );
1051     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFileType );
1052     addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBPM );
1053     addReturnValue( QueryBuilder::tabStats, QueryBuilder::valScore );
1054     addReturnValue( QueryBuilder::tabStats, QueryBuilder::valRating );
1055     addReturnValue( QueryBuilder::tabStats, QueryBuilder::valPlayCounter );
1056     addReturnValue( QueryBuilder::tabStats, QueryBuilder::valAccessDate );
1057 }
1058
1059
1060 void
1061 QueryBuilder::buildQuery( bool withDeviceidPlaceholder )
1062 {
1063     if ( m_query.isEmpty() )
1064     {
1065         linkTables( m_linkTables );
1066         m_query += "SELECT ";
1067         m_query += m_values;
1068         m_query += " FROM ";
1069         m_query += m_tables;
1070         m_query += ' ';
1071         m_query += m_join;
1072         m_query += " WHERE ";
1073         m_query += CollectionDB::instance()->boolT();
1074         m_query += ' ';
1075         m_query += m_where;
1076         if ( !m_showAll && ( m_linkTables & tabSong || m_tables.contains( tableName( tabSong) ) ) )     //Only stuff on mounted devices, unless you use optShowAll
1077         {
1078             if ( withDeviceidPlaceholder )
1079                 m_query += "(*MountedDeviceSelection*)";
1080             else
1081             {
1082                 IdList list = MountPointManager::instance()->getMountedDeviceIds();
1083                 //debug() << "number of device ids " << list.count();
1084                 m_query += " AND tags.deviceid IN (";
1085                 oldForeachType( IdList, list )
1086                 {
1087                     if ( it != list.begin() ) m_query += ',';
1088                     m_query += QString::number( *it );
1089                 }
1090                 m_query += ')';
1091             }
1092         }
1093         // GROUP BY must be before ORDER BY for sqlite
1094         // HAVING must be between GROUP BY and ORDER BY
1095         if ( !m_group.isEmpty() )  { m_query += " GROUP BY "; m_query += m_group; }
1096         if ( !m_having.isEmpty() ) { m_query += " HAVING "; m_query += m_having; }
1097         if ( !m_sort.isEmpty() )   { m_query += " ORDER BY "; m_query += m_sort; }
1098         m_query += m_limit;
1099         m_query += ';';
1100     }
1101 }
1102
1103 // get the builded SQL-Query (used in smartplaylisteditor soon)
1104 QString
1105 QueryBuilder::getQuery()
1106 {
1107     if ( m_query.isEmpty())
1108     {
1109         buildQuery();
1110     }
1111     return m_query;
1112 }
1113
1114 QStringList
1115 QueryBuilder::run()
1116 {
1117     buildQuery();
1118     //debug() << m_query;
1119     QStringList rs = CollectionDB::instance()->query( m_query );
1120     //calling code is unaware of the dynamic collection implementation, it simply expects an URL
1121     if( m_deviceidPos > 0 )
1122     {
1123         return cleanURL( rs );
1124     }
1125     else
1126         return rs;
1127 }
1128
1129
1130 void
1131 QueryBuilder::clear()
1132 {
1133     m_query.setLength(0);
1134     m_values.setLength(0);
1135     m_tables.setLength(0);
1136     m_join.setLength(0);
1137     m_where.setLength(0);
1138     m_sort.setLength(0);
1139     m_group.setLength(0);
1140     m_limit.setLength(0);
1141     m_having.setLength(0);
1142
1143     m_linkTables = 0;
1144     m_returnValues = 0;
1145
1146     m_showAll = false;
1147     m_deviceidPos = 0;
1148 }
1149
1150
1151 qint64
1152 QueryBuilder::valForFavoriteSorting() {
1153     qint64 favSortBy = valRating;
1154     if ( !AmarokConfig::useScores() && !AmarokConfig::useRatings() )
1155         favSortBy = valPlayCounter;
1156     else if( !AmarokConfig::useRatings() )
1157         favSortBy = valScore;
1158     return favSortBy;
1159 }
1160
1161 void
1162 QueryBuilder::sortByFavorite() {
1163     if ( AmarokConfig::useRatings() )
1164         sortBy(tabStats, valRating, true );
1165     if ( AmarokConfig::useScores() )
1166         sortBy(tabStats, valScore, true );
1167     sortBy(tabStats, valPlayCounter, true );
1168
1169 }
1170
1171 void
1172 QueryBuilder::sortByFavoriteAvg() {
1173     // Due to MySQL4 weirdness, we need to add the function we're using to sort
1174     // as return values as well.
1175     if ( AmarokConfig::useRatings() ) {
1176         sortByFunction(funcAvg, tabStats, valRating, true );
1177         addReturnFunctionValue( funcAvg, tabStats, valRating );
1178     }
1179     if ( AmarokConfig::useScores() ) {
1180         sortByFunction(funcAvg, tabStats, valScore, true );
1181         addReturnFunctionValue( funcAvg, tabStats, valScore );
1182     }
1183     sortByFunction(funcAvg, tabStats, valPlayCounter, true );
1184     addReturnFunctionValue( funcAvg, tabStats, valPlayCounter );
1185
1186     //exclude unrated and unplayed
1187     if( !m_having.isEmpty() )
1188         m_having += " AND ";
1189     m_having += " (";
1190     if (AmarokConfig::useRatings() )
1191         m_having += QString("%1(%2.%3) > 0 OR ")
1192                    .arg( functionName( funcAvg ), tableName(tabStats), valueName(valRating) );
1193     m_having += QString("%1(%2.%3) > 0")
1194                    .arg( functionName( funcAvg ), tableName(tabStats), valueName(valPlayCounter) );
1195     m_having += ')';
1196 }
1197
1198 // Helper method -- given a value, returns the index of the bit that is
1199 // set, if only one, otherwise returns -1
1200 // Binsearch seems appropriate since the values enum has 40 members
1201 template<class ValueType>
1202 static inline int
1203 searchBit( ValueType value, int numBits ) {
1204    int low = 0, high = numBits - 1;
1205    while( low <= high ) {
1206        int mid = (low + high) / 2;
1207        ValueType compare = static_cast<ValueType>( 1 ) << mid;
1208        if ( value == compare ) return mid;
1209        else if ( value < compare ) high = mid - 1;
1210        else low = mid + 1;
1211    }
1212
1213    return -1;
1214 }
1215
1216 QString
1217 QueryBuilder::tableName( int table )
1218 {
1219     // optimize for 1 table which is by far the most frequent case
1220     static const QString tabNames[] = {
1221         "album",
1222         "artist",
1223         "composer",
1224         "genre",
1225         "year",
1226         "<unused>",             // 32 is missing from the enum
1227         "tags",
1228         "statistics",
1229         "lyrics",
1230         "podcastchannels",
1231         "podcastepisodes",
1232         "podcasttables",
1233         "devices",
1234         "labels"
1235     };
1236
1237     int oneBit = searchBit( table, sizeof( tabNames ) / sizeof( QString ) );
1238     if ( oneBit >= 0 ) return tabNames[oneBit];
1239
1240     // slow path: multiple tables. This seems to be unneeded at the moment,
1241     // but leaving it here since it appears to be intended usage
1242     QString tables;
1243
1244     if ( CollectionDB::instance()->getType() != DbConnection::postgresql )
1245     {
1246         if ( table & tabSong )   tables += ",tags";
1247     }
1248     if ( table & tabArtist ) tables += ",artist";
1249     if ( table & tabComposer ) tables += ",composer";
1250     if ( table & tabAlbum )  tables += ",album";
1251     if ( table & tabGenre )  tables += ",genre";
1252     if ( table & tabYear )   tables += ",year";
1253     if ( table & tabStats )  tables += ",statistics";
1254     if ( table & tabLyrics )  tables += ",lyrics";
1255     if ( table & tabPodcastChannels ) tables += ",podcastchannels";
1256     if ( table & tabPodcastEpisodes ) tables += ",podcastepisodes";
1257     if ( table & tabPodcastFolders ) tables += ",podcasttables";
1258     if ( CollectionDB::instance()->getType() == DbConnection::postgresql )
1259     {
1260         if ( table & tabSong )   tables += ",tags";
1261     }
1262
1263     if ( table & tabDevices ) tables += ",devices";
1264     if ( table & tabLabels ) tables += ",labels";
1265     // when there are multiple tables involved, we always need table tags for linking them
1266     return tables.mid( 1 );
1267 }
1268
1269
1270 const QString &
1271 QueryBuilder::valueName( qint64 value )
1272 {
1273    static const QString values[] = {
1274        "id",
1275        "name",
1276        "url",
1277        "title",
1278        "track",
1279        "percentage",
1280        "comment",
1281        "bitrate",
1282        "length",
1283        "samplerate",
1284        "playcounter",
1285        "createdate",
1286        "accessdate",
1287        "percentage",
1288        "artist",
1289        "album",
1290        "year",
1291        "genre",
1292        "dir",
1293        "lyrics",
1294        "rating",
1295        "composer",
1296        "discnumber",
1297        "filesize",
1298        "filetype",
1299        "sampler",
1300        "bpm",
1301        "copyright",
1302        "parent",
1303        "weblink",
1304        "autoscan",
1305        "fetchtype",
1306        "autotransfer",
1307        "haspurge",
1308        "purgeCount",
1309        "isNew",
1310        "deviceid",
1311        "url",
1312        "label",
1313        "lastmountpoint",
1314        "type"
1315    };
1316
1317    int oneBit = searchBit( value, sizeof( values ) / sizeof( QString ) );
1318    if ( oneBit >= 0 ) return values[oneBit];
1319
1320    static const QString error( "<ERROR valueName>" );
1321    return error;
1322 }
1323
1324 /*
1325  * Return true if we should call COALESCE(..,0) for this DB field
1326  * (field names sourced from the old smartplaylistbrowser.cpp code)
1327  * Warning: addFilter( int, qint64, const QString&, int bool )
1328  * expects this method to return true for all statistics table clomuns of type INTEGER
1329  * Sqlite doesn't like comparing strings to an INTEGER column.
1330  */
1331 bool
1332 QueryBuilder::coalesceField( int table, qint64 value )
1333 {
1334     if( tableName( table ) == "statistics" &&
1335         ( valueName( value ) == "playcounter" ||
1336           valueName( value ) == "rating" ||
1337           valueName( value ) == "percentage" ||
1338           valueName( value ) == "accessdate" ||
1339           valueName( value ) == "createdate"
1340         )
1341     )
1342        return true;
1343    return false;
1344 }
1345
1346 QString
1347 QueryBuilder::functionName( int function )
1348 {
1349     QString functions;
1350
1351     if ( function & funcCount )     functions += "Count";
1352     if ( function & funcMax )       functions += "Max";
1353     if ( function & funcMin )       functions += "Min";
1354     if ( function & funcAvg )       functions += "Avg";
1355     if ( function & funcSum )       functions += "Sum";
1356
1357     return functions;
1358 }
1359
1360 // FIXME: the two functions below are inefficient, but this patch is getting too
1361 // big already. They are not on any critical path right now. Ovy
1362 int
1363 QueryBuilder::getTableByName(const QString &name)
1364 {
1365     for ( int i = 1; i <= tabLabels; i <<= 1 )
1366     {
1367         if (tableName(i) == name) return i;
1368     }
1369     return -1;
1370 }
1371
1372 qint64
1373 QueryBuilder::getValueByName(const QString &name)
1374 {
1375     for ( qint64 i = 1; i <= valType; i <<= 1 ) {
1376         if (valueName(i) == name) return i;
1377     }
1378
1379     return -1;
1380 }
1381
1382 bool
1383 QueryBuilder::getField(const QString &tableValue, int *table, qint64 *value)
1384 {
1385     int dotIndex = tableValue.find( '.' ) ;
1386     if ( dotIndex < 0 ) return false;
1387     int tmpTable = getTableByName( tableValue.left(dotIndex) );
1388     quint64 tmpValue = getValueByName( tableValue.mid( dotIndex + 1 ) );
1389     if ( tmpTable >= 0 && value ) {
1390         *table = tmpTable;
1391         *value = tmpValue;
1392         return true;
1393     }
1394     else
1395     {
1396         qFatal("invalid table.value: %s", qPrintable(tableValue));
1397         return false;
1398     }
1399 }
1400
1401
1402
1403 QStringList
1404 QueryBuilder::cleanURL( QStringList result )
1405 {
1406     //this method replaces the fields for relative path and devive/media id with a
1407     //single field containing the absolute path for each row
1408     int count = 1;
1409     for( QMutableStringListIterator iter( result ); iter.hasNext(); )
1410     {
1411         QString rpath;
1412         if ( (count % (m_returnValues + 1)) + 1== m_deviceidPos )
1413         {
1414             //this block is reached when the iterator points at the relative path
1415             //deviceid is next
1416             QString rpath = iter.next();
1417             int deviceid = iter.peekNext().toInt();
1418             QString abspath = MountPointManager::instance()->getAbsolutePath( deviceid, rpath );
1419             iter.setValue( abspath );
1420             iter.next();
1421             iter.remove();
1422             //we advanced the iterator over two fields in this iteration
1423             ++count;
1424         }
1425         else
1426             iter.next();
1427         ++count;
1428     }
1429     return result;
1430 }