File indexing completed on 2025-01-05 04:25:58
0001 /**************************************************************************************** 0002 * Copyright (c) 2010 Ralf Engels <ralf-engels@gmx.de> * 0003 * * 0004 * This program is free software; you can redistribute it and/or modify it under * 0005 * the terms of the GNU General Public License as published by the Free Software * 0006 * Foundation; either version 2 of the License, or (at your option) any later * 0007 * version. * 0008 * * 0009 * This program is distributed in the hope that it will be useful, but WITHOUT ANY * 0010 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A * 0011 * PARTICULAR PURPOSE. See the GNU General Public License for more details. * 0012 * * 0013 * You should have received a copy of the GNU General Public License along with * 0014 * this program. If not, see <http://www.gnu.org/licenses/>. * 0015 ****************************************************************************************/ 0016 0017 #define DEBUG_PREFIX "SqlRegistryP" 0018 #include "core/support/Debug.h" 0019 0020 #include "SqlRegistry_p.h" 0021 #include "SqlMeta.h" 0022 #include "SqlCollection.h" 0023 0024 void 0025 AbstractTrackTableCommitter::commit( const QList<Meta::SqlTrackPtr> &tracks ) 0026 { 0027 // Note: The code is greatly inspired by the old ScanResultProcessor 0028 // by jeffrai 0029 0030 // Note2: The code is optimized for batch update. 0031 // Reason: a single update is completely harmless and not frequent. 0032 // The real difficulty is the collection scanner and it's runtime 0033 // Especially with collections larger than 30000 tracks. 0034 0035 if( tracks.isEmpty() ) 0036 return; 0037 0038 m_storage = tracks.first()->sqlCollection()->sqlStorage(); 0039 0040 // -- get the maximum size for our commit 0041 static int maxSize = 0; 0042 if( maxSize == 0 ) 0043 { 0044 QStringList res = m_storage->query( "SHOW VARIABLES LIKE 'max_allowed_packet';" ); 0045 if( res.size() < 2 || res[1].toInt() == 0 ) 0046 { 0047 warning() << "Uh oh! For some reason MySQL thinks there isn't a max allowed size!"; 0048 return; 0049 } 0050 debug() << "obtained max_allowed_packet is " << res[1]; 0051 maxSize = res[1].toInt() / 3; //for safety, due to multibyte encoding 0052 } 0053 0054 0055 QStringList fields = getFields(); 0056 0057 const QString updateQueryStart = "UPDATE LOW_PRIORITY "+tableName()+" SET "; 0058 const QString insertQueryStart = "INSERT INTO "+tableName()+ 0059 " ("+fields.join(",")+") VALUES "; 0060 0061 QList< Meta::SqlTrackPtr > insertedTracks; 0062 QString insertQuery; 0063 insertQuery.reserve( 1024 ); // a sensible initial size 0064 0065 foreach( Meta::SqlTrackPtr track, tracks ) 0066 { 0067 QStringList values = getValues( track.data() ); 0068 0069 // -- update 0070 if( getId( track.data() ) > 0 ) 0071 { 0072 // we just commit all values to save code complexity. 0073 // we would need to track the real changed fields otherwise 0074 QString updateQuery; 0075 updateQuery.reserve( 256 ); // a sensible initial size 0076 for( int i = 0; i < fields.count() && i < values.count(); i++ ) 0077 { 0078 if( !updateQuery.isEmpty() ) 0079 updateQuery += ", "; 0080 updateQuery += fields.at( i ); 0081 updateQuery += '='; 0082 updateQuery += values.at( i ); 0083 } 0084 updateQuery = updateQueryStart + updateQuery + 0085 " WHERE id=" + QString::number( getId( track.data() ) ) + ';'; 0086 m_storage->query( updateQuery ); 0087 0088 } 0089 else 0090 // -- insert 0091 { 0092 QString newValues = '(' + values.join(",") + ')'; 0093 0094 // - if the insertQuery is long enough, commit it. 0095 if( insertQueryStart.length() + insertQuery.length() + newValues.length() + 1 >= maxSize - 3 ) // ";" 0096 { 0097 // commit 0098 insertQuery = insertQueryStart + insertQuery + ';'; 0099 int firstId = m_storage->insert( insertQuery, tableName() ); 0100 0101 // set the resulting ids 0102 if( firstId <= 0 ) 0103 warning() << "Insert failed."; 0104 for( int i = 0; i < insertedTracks.count(); i++ ) 0105 setId( const_cast<Meta::SqlTrack*>(insertedTracks.at( i ).data()), 0106 firstId + i ); 0107 0108 insertQuery.clear(); 0109 insertedTracks.clear(); 0110 } 0111 0112 if( !insertQuery.isEmpty() ) 0113 insertQuery += ','; 0114 insertQuery += newValues; 0115 insertedTracks.append( track ); 0116 } 0117 } 0118 0119 // - insert the rest 0120 if( !insertQuery.isEmpty() ) 0121 { 0122 // commit 0123 insertQuery = insertQueryStart + insertQuery + ';'; 0124 int firstId = m_storage->insert( insertQuery, tableName() ); 0125 0126 // set the resulting ids 0127 if( firstId <= 0 ) 0128 warning() << "Insert failed."; 0129 for( int i = 0; i < insertedTracks.count(); i++ ) 0130 setId( const_cast<Meta::SqlTrack*>(insertedTracks.at( i ).data()), 0131 firstId + i ); 0132 0133 insertQuery.clear(); 0134 insertedTracks.clear(); 0135 } 0136 } 0137 0138 0139 // --- some help functions for the query 0140 QString 0141 AbstractTrackTableCommitter::nullString( const QString &str ) const 0142 { 0143 if( str.isEmpty() ) 0144 return "NULL"; 0145 else 0146 return str; 0147 } 0148 0149 QString 0150 AbstractTrackTableCommitter::nullNumber( const qint64 number ) const 0151 { 0152 if( number <= 0 ) 0153 return "NULL"; 0154 else 0155 return QString::number( number ); 0156 } 0157 0158 QString 0159 AbstractTrackTableCommitter::nullNumber( const int number ) const 0160 { 0161 if( number <= 0 ) 0162 return "NULL"; 0163 else 0164 return QString::number( number ); 0165 } 0166 0167 QString 0168 AbstractTrackTableCommitter::nullNumber( const double number ) const 0169 { 0170 if( number <= 0 ) 0171 return "NULL"; 0172 else 0173 return QString::number( number ); 0174 } 0175 0176 QString 0177 AbstractTrackTableCommitter::nullDate( const QDateTime &date ) const 0178 { 0179 if( date.isValid() ) 0180 return QString::number( date.toSecsSinceEpoch() ); 0181 else 0182 return "NULL"; 0183 } 0184 0185 0186 QString 0187 AbstractTrackTableCommitter::escape( const QString &str ) const 0188 { 0189 return '\'' + m_storage->escape( str ) + '\''; 0190 } 0191 0192 0193 // ------------ urls --------------- 0194 0195 QString 0196 TrackUrlsTableCommitter::tableName() 0197 { 0198 return "urls"; 0199 } 0200 0201 int 0202 TrackUrlsTableCommitter::getId( Meta::SqlTrack *track ) 0203 { 0204 return track->m_urlId; 0205 } 0206 0207 void 0208 TrackUrlsTableCommitter::setId( Meta::SqlTrack *track, int id ) 0209 { 0210 track->m_urlId = id; 0211 } 0212 0213 QStringList 0214 TrackUrlsTableCommitter::getFields() 0215 { 0216 QStringList result; 0217 result << "deviceid" << "rpath" << "directory" << "uniqueid"; 0218 return result; 0219 } 0220 0221 QStringList 0222 TrackUrlsTableCommitter::getValues( Meta::SqlTrack *track ) 0223 { 0224 QStringList result; 0225 Q_ASSERT( track->m_deviceId != 0 && "refusing to write zero deviceId to urls table, please file a bug" ); 0226 result << QString::number( track->m_deviceId ); 0227 result << escape( track->m_rpath ); 0228 Q_ASSERT( track->m_directoryId > 0 && "refusing to write non-positive directoryId to urls table, please file a bug" ); 0229 result << nullNumber( track->m_directoryId ); 0230 result << escape( track->m_uid ); 0231 return result; 0232 } 0233 0234 0235 // ------------ tracks --------------- 0236 0237 QString 0238 TrackTracksTableCommitter::tableName() 0239 { 0240 return "tracks"; 0241 } 0242 0243 int 0244 TrackTracksTableCommitter::getId( Meta::SqlTrack *track ) 0245 { 0246 return track->m_trackId; 0247 } 0248 0249 void 0250 TrackTracksTableCommitter::setId( Meta::SqlTrack *track, int id ) 0251 { 0252 track->m_trackId = id; 0253 } 0254 0255 QStringList 0256 TrackTracksTableCommitter::getFields() 0257 { 0258 QStringList result; 0259 result << "url" << "artist" << "album" << "genre" << "composer" << "year" << 0260 "title" << "comment" << "tracknumber" << "discnumber" << "bitrate" << 0261 "length" << "samplerate" << "filesize" << "filetype" << "bpm" << "createdate" << 0262 "modifydate" << "albumgain" << "albumpeakgain" << "trackgain" << "trackpeakgain"; 0263 return result; 0264 } 0265 0266 QStringList 0267 TrackTracksTableCommitter::getValues( Meta::SqlTrack *track ) 0268 { 0269 QStringList result; 0270 Q_ASSERT( track->m_urlId > 0 && "refusing to write non-positive urlId to tracks table, please file a bug" ); 0271 result << QString::number( track->m_urlId ); 0272 result << QString::number( track->m_artist ? 0273 AmarokSharedPointer<Meta::SqlArtist>::staticCast( track->m_artist )->id() : 0274 -1 ); 0275 result << QString::number( track->m_album ? 0276 AmarokSharedPointer<Meta::SqlAlbum>::staticCast( track->m_album )->id() : 0277 -1 ); 0278 result << QString::number( track->m_genre ? 0279 AmarokSharedPointer<Meta::SqlGenre>::staticCast( track->m_genre )->id() : 0280 -1 ); 0281 result << QString::number( track->m_composer ? 0282 AmarokSharedPointer<Meta::SqlComposer>::staticCast( track->m_composer )->id() : 0283 -1 ); 0284 result << QString::number( track->m_year ? 0285 AmarokSharedPointer<Meta::SqlYear>::staticCast( track->m_year )->id() : 0286 -1 ); 0287 result << escape( track->m_title ); 0288 result << escape( track->m_comment ); 0289 result << nullNumber( track->m_trackNumber ); 0290 result << nullNumber( track->m_discNumber ); 0291 result << nullNumber( track->m_bitrate ); 0292 result << nullNumber( track->m_length ); 0293 result << nullNumber( track->m_sampleRate ); 0294 result << nullNumber( track->m_filesize ); 0295 result << nullNumber( int(track->m_filetype) ); 0296 result << nullNumber( track->m_bpm ); 0297 result << nullDate( track->m_createDate ); 0298 result << nullDate( track->m_modifyDate ); 0299 result << QString::number( track->m_albumGain ); 0300 result << QString::number( track->m_albumPeakGain ); 0301 result << QString::number( track->m_trackGain ); 0302 result << QString::number( track->m_trackPeakGain ); 0303 return result; 0304 } 0305 0306 // ------------ statistics --------------- 0307 0308 QString 0309 TrackStatisticsTableCommitter::tableName() 0310 { 0311 return "statistics"; 0312 } 0313 0314 int 0315 TrackStatisticsTableCommitter::getId( Meta::SqlTrack *track ) 0316 { 0317 return track->m_statisticsId; 0318 } 0319 0320 void 0321 TrackStatisticsTableCommitter::setId( Meta::SqlTrack *track, int id ) 0322 { 0323 track->m_statisticsId = id; 0324 } 0325 0326 QStringList 0327 TrackStatisticsTableCommitter::getFields() 0328 { 0329 QStringList result; 0330 result << "url" << "createdate" << "accessdate" << "score" << "rating" << "playcount" << "deleted"; 0331 return result; 0332 } 0333 0334 QStringList 0335 TrackStatisticsTableCommitter::getValues( Meta::SqlTrack *track ) 0336 { 0337 QStringList result; 0338 Q_ASSERT( track->m_urlId > 0 && "refusing to write non-positive urlId to statistics table, please file a bug" ); 0339 result << QString::number( track->m_urlId ); 0340 result << nullDate( track->m_firstPlayed ); 0341 result << nullDate( track->m_lastPlayed ); 0342 result << nullNumber( track->m_score ); 0343 result << QString::number( track->m_rating ); // NOT NULL 0344 result << QString::number( track->m_playCount ); // NOT NULL 0345 result << "0"; // not deleted 0346 return result; 0347 }