src/querybuilder.h

   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 #ifndef AMAROK_QUERYBUIDER_H
  29 #define AMAROK_QUERYBUIDER_H
  30
  31 #include "collectiondb.h"
  32
  33 #include <QtGlobal>
  34 #include <QString>
  35 #include <QStringList>
  36
  37 #include <Q3ValueStack>
  38
  39
  40 class AMAROK_EXPORT QueryBuilder
  41 {
  42     public:
  43         //attributes:
  44         enum qBuilderTables  { tabAlbum = 1, tabArtist = 2, tabComposer = 4, tabGenre = 8, tabYear = 16, tabSong = 64,
  45                                tabStats = 128, tabLyrics = 256, tabPodcastChannels = 512,
  46                                tabPodcastEpisodes = 1024, tabPodcastFolders = 2048,
  47                                tabDevices = 4096, tabLabels = 8192
  48                                /* dummy table for filtering */, tabDummy = 0 };
  49         enum qBuilderOptions { optNoCompilations = 1, optOnlyCompilations = 2, optRemoveDuplicates = 4,
  50                                optRandomize = 8,
  51                                optShowAll = 16 /* get all songs, not just mounted ones */ };
  52         /* This has been an enum in the past, but 32 bits wasn't enough anymore :-( */
  53         static const qint64 valDummy         = 0;
  54         static const qint64 valID            = 1LL << 0;
  55         static const qint64 valName          = 1LL << 1;
  56         static const qint64 valURL           = 1LL << 2;
  57         static const qint64 valTitle         = 1LL << 3;
  58         static const qint64 valTrack         = 1LL << 4;
  59         static const qint64 valScore         = 1LL << 5;
  60         static const qint64 valComment       = 1LL << 6;
  61         static const qint64 valBitrate       = 1LL << 7;
  62         static const qint64 valLength        = 1LL << 8;
  63         static const qint64 valSamplerate    = 1LL << 9;
  64         static const qint64 valPlayCounter   = 1LL << 10;
  65         static const qint64 valCreateDate    = 1LL << 11;
  66         static const qint64 valAccessDate    = 1LL << 12;
  67         //static const qint64 valPercentage    = 1LL << 13; // same as valScore
  68         static const qint64 valArtistID      = 1LL << 14;
  69         static const qint64 valAlbumID       = 1LL << 15;
  70         static const qint64 valYearID        = 1LL << 16;
  71         static const qint64 valGenreID       = 1LL << 17;
  72         static const qint64 valDirectory     = 1LL << 18;
  73         static const qint64 valLyrics        = 1LL << 19;
  74         static const qint64 valRating        = 1LL << 20;
  75         static const qint64 valComposerID    = 1LL << 21;
  76         static const qint64 valDiscNumber    = 1LL << 22;
  77         static const qint64 valFilesize      = 1LL << 23;
  78         static const qint64 valFileType      = 1LL << 24;
  79         static const qint64 valIsCompilation = 1LL << 25;
  80         static const qint64 valBPM           = 1LL << 26;
  81         // podcast relevant:
  82         static const qint64 valCopyright     = 1LL << 27;
  83         static const qint64 valParent        = 1LL << 28;
  84         static const qint64 valWeblink       = 1LL << 29;
  85         static const qint64 valAutoscan      = 1LL << 30;
  86         static const qint64 valFetchtype     = 1LL << 31;
  87         static const qint64 valAutotransfer  = 1LL << 32;
  88         static const qint64 valPurge         = 1LL << 33;
  89         static const qint64 valPurgeCount    = 1LL << 34;
  90         static const qint64 valIsNew         = 1LL << 35;
  91         // dynamic collection relevant:
  92         static const qint64 valDeviceId      = 1LL << 36;
  93         static const qint64 valRelativePath  = 1LL << 37;
  94         static const qint64 valDeviceLabel   = 1LL << 38;
  95         static const qint64 valMountPoint    = 1LL << 39;
  96         //label relevant
  97         static const qint64 valType         = 1LL << 40;
  98
  99         static qint64 valForFavoriteSorting();
 100         void sortByFavorite();
 101
 102         // sortByFavoriteAvg() add the average rating, if enabled, the average score, if enabled,
 103         // and the average playcounter as return values!
 104         void sortByFavoriteAvg();
 105
 106         enum qBuilderFunctions  { funcNone = 0, funcCount = 1, funcMax = 2, funcMin = 4, funcAvg = 8, funcSum = 16 };
 107
 108         // Note: modes beginMatch, endMatch are only supported for string filters
 109         // Likewise, modes between and notBetween are only supported for numeric filters
 110         enum qBuilderFilter  { modeNormal = 0, modeLess = 1, modeGreater = 2, modeEndMatch = 3, modeBeginMatch = 4, modeBetween = 5, modeNotBetween = 6};
 111
 112         QueryBuilder();
 113
 114         void addReturnValue( int table, qint64 value, bool caseSensitive = false /* unless value refers to a string */ );
 115         void addReturnFunctionValue( int function, int table, qint64 value);
 116         uint countReturnValues();
 117
 118         // Note: the filter chain begins in AND mode
 119         void beginOR(); //filters will be ORed instead of ANDed
 120         void endOR();   //don't forget to end it!
 121         void beginAND(); // These do the opposite; for recursive and/or
 122         void endAND();
 123
 124         void setGoogleFilter( int defaultTables, QString query );
 125
 126         void addUrlFilters( const QStringList& filter );
 127
 128         void addFilter( int tables, const QString& filter);
 129         void addFilter( int tables, qint64 value, const QString& filter, int mode = modeNormal, bool exact = false );
 130         void addFilters( int tables, const QStringList& filter );
 131         void excludeFilter( int tables, const QString& filter );
 132         void excludeFilter( int tables, qint64 value, const QString& filter, int mode = modeNormal, bool exact = false );
 133
 134         void addMatch( int tables, const QString& match, bool interpretUnknown = true, bool caseSensitive = true );
 135         void addMatch( int tables, qint64 value, const QString& match, bool interpretUnknown = true, bool caseSensitive = true );
 136         void addMatches( int tables, const QStringList& match, bool interpretUnknown = true, bool caseSensitive = true );
 137         void excludeMatch( int tables, const QString& match );
 138         void having( int table, qint64 value, int function, int mode, const QString& match );
 139
 140         void exclusiveFilter( int tableMatching, int tableNotMatching, qint64 value );
 141
 142         // For numeric filters:
 143         // modeNormal means strict equality; modeBeginMatch and modeEndMatch are not
 144         // allowed; modeBetween needs a second value endRange
 145         void addNumericFilter(int tables, qint64 value, const QString &n,
 146                               int mode = modeNormal,
 147                               const QString &endRange = QString());
 148
 149         void setOptions( int options );
 150         void sortBy( int table, qint64 value, bool descending = false );
 151         void sortByFunction( int function, int table, qint64 value, bool descending = false );
 152         void groupBy( int table, qint64 value );
 153         void setLimit( int startPos, int length );
 154
 155         // Returns the results in random order.
 156         // If a \p table and \p value are specified, uses weighted random order on
 157         // that field.
 158         // The shuffle is cumulative with other sorts, but any sorts after this are
 159         // pointless because of the precision of the random function.
 160         void shuffle( int table = 0, qint64 value = 0 );
 161
 162         static const int dragFieldCount;
 163         static QString dragSQLFields();
 164         void initSQLDrag();
 165
 166         void buildQuery( bool withDeviceidPlaceholder = false );
 167         QString getQuery();
 168         //use withDeviceidPlaceholder = false if the query isn't run immediately (*CurrentTimeT*)
 169         //and replace (*MountedDeviceSelection*) with CollectionDB::instance()->deviceIdSelection()
 170         QString query( bool withDeviceidPlaceholder = false ) { buildQuery( withDeviceidPlaceholder ); return m_query; }
 171         void clear();
 172
 173         QStringList run();
 174
 175         // Transform a string table.value "field" into enum values
 176         // @return true if we succeeded
 177         bool getField(const QString &tableValue, int *table, qint64 *value);
 178
 179     private:
 180         QString tableName( int table );
 181         const QString &valueName( qint64 value );
 182         QString functionName( int functions );
 183         bool coalesceField( int table, qint64 value );
 184
 185         int getTableByName(const QString &name);
 186         qint64 getValueByName(const QString &field);
 187
 188         QStringList cleanURL( QStringList result );
 189
 190         void linkTables( int tables );
 191
 192         Q3ValueStack<bool> m_OR;
 193         bool m_showAll;
 194         uint m_deviceidPos;
 195
 196         QString ANDslashOR() const;
 197
 198         QString m_query;
 199         QString m_values;
 200         QString m_tables;
 201         QString m_join;
 202         QString m_where;
 203         QString m_sort;
 204         QString m_group;
 205         QString m_limit;
 206         QString m_having;
 207
 208         QString m_url;      //url is used as primary key and linkTables needs to do some special stuff with it
 209
 210         int m_linkTables;
 211         uint m_returnValues;
 212 };
 213
 214 inline void QueryBuilder::beginOR()
 215 {
 216     m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolF() + ' ';
 217     m_OR.push(true);
 218 }
 219 inline void QueryBuilder::endOR()
 220 {
 221     m_where += " ) ";
 222     m_OR.pop();
 223 }
 224 inline void QueryBuilder::beginAND()
 225 {
 226     m_where += ANDslashOR() + " ( " + CollectionDB::instance()->boolT() + ' ';
 227     m_OR.push(false);
 228 }
 229 inline void QueryBuilder::endAND()
 230 {
 231     m_where += " ) ";
 232     m_OR.pop();
 233 }
 234 inline QString QueryBuilder::ANDslashOR() const { return m_OR.top() ? "OR" : "AND"; }
 235
 236 #endif