File indexing completed on 2025-01-19 04:24:22
0001 /**************************************************************************************** 0002 * Copyright (c) 2007 Maximilian Kossick <maximilian.kossick@googlemail.com> * 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 #include "DatabaseUpdater.h" 0018 0019 #include "amarokconfig.h" 0020 #include "core/support/Amarok.h" 0021 #include "core/support/Debug.h" 0022 #include <core/storage/SqlStorage.h> 0023 #include "SqlCollection.h" 0024 0025 #include <QDateTime> 0026 #include <QDir> 0027 #include <QFile> 0028 #include <QMultiMap> 0029 #include <QTextStream> 0030 0031 #include <KMessageBox> 0032 0033 static const int DB_VERSION = 15; 0034 0035 int 0036 DatabaseUpdater::expectedDatabaseVersion() 0037 { 0038 return DB_VERSION; 0039 } 0040 0041 DatabaseUpdater::DatabaseUpdater( Collections::SqlCollection *collection ) 0042 : m_collection( collection ) 0043 , m_debugDatabaseContent( false ) 0044 { 0045 m_debugDatabaseContent = Amarok::config( "SqlCollection" ).readEntry( "DebugDatabaseContent", false ); 0046 } 0047 0048 DatabaseUpdater::~DatabaseUpdater() 0049 { 0050 //nothing to do 0051 } 0052 0053 bool 0054 DatabaseUpdater::needsUpdate() const 0055 { 0056 return adminValue( "DB_VERSION" ) != DB_VERSION; 0057 } 0058 0059 bool 0060 DatabaseUpdater::schemaExists() const 0061 { 0062 return adminValue( "DB_VERSION" ) != 0; 0063 } 0064 0065 bool 0066 DatabaseUpdater::update() 0067 { 0068 DEBUG_BLOCK 0069 int dbVersion = adminValue( "DB_VERSION" ); 0070 0071 debug() << "Database version: " << dbVersion; 0072 0073 if( dbVersion == 0 ) 0074 { 0075 createTables(); 0076 QString query = QString( "INSERT INTO admin(component, version) VALUES ('DB_VERSION', %1);" ).arg( DB_VERSION ); 0077 m_collection->sqlStorage()->query( query ); 0078 return true; 0079 } 0080 0081 if( dbVersion < DB_VERSION ) 0082 { 0083 debug() << "Database out of date: database version is" << dbVersion << ", current version is" << DB_VERSION; 0084 switch( dbVersion ) 0085 { 0086 case 1: 0087 upgradeVersion1to2(); 0088 Q_FALLTHROUGH(); 0089 case 2: 0090 upgradeVersion2to3(); 0091 Q_FALLTHROUGH(); 0092 case 3: 0093 upgradeVersion3to4(); 0094 Q_FALLTHROUGH(); 0095 case 4: 0096 upgradeVersion4to5(); 0097 Q_FALLTHROUGH(); 0098 case 5: 0099 upgradeVersion5to6(); 0100 Q_FALLTHROUGH(); 0101 case 6: 0102 upgradeVersion6to7(); 0103 Q_FALLTHROUGH(); 0104 case 7: 0105 upgradeVersion7to8(); 0106 Q_FALLTHROUGH(); 0107 case 8: 0108 //removes stray rows from albums that were caused by the initial full scan 0109 upgradeVersion8to9(); 0110 Q_FALLTHROUGH(); 0111 case 9: 0112 //removes stray rows from albums that were caused by the initial full scan 0113 upgradeVersion9to10(); 0114 Q_FALLTHROUGH(); 0115 case 10: 0116 upgradeVersion10to11(); 0117 Q_FALLTHROUGH(); 0118 case 11: 0119 upgradeVersion11to12(); 0120 Q_FALLTHROUGH(); 0121 case 12: 0122 upgradeVersion12to13(); 0123 Q_FALLTHROUGH(); 0124 case 13: 0125 upgradeVersion13to14(); 0126 Q_FALLTHROUGH(); 0127 case 14: 0128 upgradeVersion14to15(); 0129 dbVersion = 15; // be sure to update this manually when introducing new version! 0130 } 0131 0132 QString query = QString( "UPDATE admin SET version = %1 WHERE component = 'DB_VERSION';" ).arg( dbVersion ); 0133 m_collection->sqlStorage()->query( query ); 0134 0135 //NOTE: A rescan will be triggered automatically as a result of an upgrade. Don't trigger it here, as the 0136 //collection isn't fully initialized and this will trigger a crash/assert. 0137 return true; 0138 } 0139 0140 if( dbVersion > DB_VERSION ) 0141 { 0142 KMessageBox::error(nullptr, 0143 "<p>The Amarok collection database was created by a newer version of Amarok, " 0144 "and this version of Amarok cannot use it.</p>", 0145 "Database Type Unknown"); 0146 // FIXME: maybe we should tell them how to delete the database? 0147 // FIXME: exit() may be a little harsh, but QCoreApplication::exit() doesn't seem to work 0148 exit(1); 0149 } 0150 0151 return false; 0152 } 0153 0154 void 0155 DatabaseUpdater::upgradeVersion1to2() 0156 { 0157 DEBUG_BLOCK 0158 0159 m_collection->sqlStorage()->query( "ALTER TABLE tracks " 0160 "ADD COLUMN albumgain FLOAT, " 0161 "ADD COLUMN albumpeakgain FLOAT, " 0162 "ADD COLUMN trackgain FLOAT," 0163 "ADD COLUMN trackpeakgain FLOAT;" ); 0164 } 0165 0166 void 0167 DatabaseUpdater::upgradeVersion2to3() 0168 { 0169 DEBUG_BLOCK; 0170 0171 auto storage = m_collection->sqlStorage(); 0172 storage->query( "DROP TABLE devices;" ); 0173 0174 QString create = "CREATE TABLE devices " 0175 "(id " + storage->idType() + 0176 ",type " + storage->textColumnType() + 0177 ",label " + storage->textColumnType() + 0178 ",lastmountpoint " + storage->textColumnType() + 0179 ",uuid " + storage->textColumnType() + 0180 ",servername " + storage->textColumnType() + 0181 ",sharename " + storage->textColumnType() + ");"; 0182 storage->query( create ); 0183 storage->query( "CREATE INDEX devices_type ON devices( type );" ); 0184 storage->query( "CREATE UNIQUE INDEX devices_uuid ON devices( uuid );" ); 0185 storage->query( "CREATE INDEX devices_rshare ON devices( servername, sharename );" ); 0186 0187 } 0188 0189 void 0190 DatabaseUpdater::upgradeVersion3to4() 0191 { 0192 auto storage = m_collection->sqlStorage(); 0193 0194 storage->query( "CREATE TABLE statistics_permanent " 0195 "(url " + storage->exactTextColumnType() + 0196 ",firstplayed DATETIME" 0197 ",lastplayed DATETIME" 0198 ",score FLOAT" 0199 ",rating INTEGER DEFAULT 0" 0200 ",playcount INTEGER)" ); 0201 storage->query( "CREATE UNIQUE INDEX ON statistics_permanent(url)" ); 0202 //Note: the above index query is invalid, but kept here for posterity 0203 0204 storage->query( "CREATE TABLE statistics_tag " 0205 "(name " + storage->textColumnType() + 0206 ",artist " + storage->textColumnType() + 0207 ",album " + storage->textColumnType() + 0208 ",firstplayed DATETIME" 0209 ",lastplayed DATETIME" 0210 ",score FLOAT" 0211 ",rating INTEGER DEFAULT 0" 0212 ",playcount INTEGER)" ); 0213 storage->query( "CREATE UNIQUE INDEX ON statistics_tag(name,artist,album)" ); 0214 //Note: the above index query is invalid, but kept here for posterity 0215 } 0216 0217 void 0218 DatabaseUpdater::upgradeVersion4to5() 0219 { 0220 auto storage = m_collection->sqlStorage(); 0221 0222 //first the database 0223 storage->query( "ALTER DATABASE amarok DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci" ); 0224 0225 //now the tables 0226 0227 //first, drop tables that can easily be recreated by doing an update 0228 QStringList dropTables; 0229 dropTables << "jamendo_albums" << "jamendo_artists" << "jamendo_genre" << "jamendo_tracks"; 0230 dropTables << "magnatune_albums" << "magnatune_artists" << "magnatune_genre" << "magnatune_moods" << "magnatune_tracks"; 0231 dropTables << "opmldirectory_albums" << "opmldirectory_artists" << "opmldirectory_genre" << "opmldirectory_tracks"; 0232 0233 foreach( const QString &table, dropTables ) 0234 storage->query( "DROP TABLE " + table ); 0235 0236 //now, the rest of them 0237 QStringList tables; 0238 tables << "admin" << "albums" << "amazon" << "artists" << "bookmark_groups" << "bookmarks"; 0239 tables << "composers" << "devices" << "directories" << "genres" << "images" << "labels" << "lyrics"; 0240 tables << "playlist_groups" << "playlist_tracks" << "playlists"; 0241 tables << "podcastchannels" << "podcastepisodes"; 0242 tables << "statistics" << "statistics_permanent" << "statistics_tag"; 0243 tables << "tracks" << "urls" << "urls_labels" << "years"; 0244 0245 foreach( const QString &table, tables ) 0246 storage->query( "ALTER TABLE " + table + " DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci" ); 0247 0248 //now the columns (ugh) 0249 //first, varchar 0250 typedef QPair<QString, int> vcpair; 0251 QMultiMap<QString, vcpair> columns; 0252 columns.insert( "admin", vcpair( "component", 255 ) ); 0253 columns.insert( "albums", vcpair( "name", textColumnLength() ) ); 0254 columns.insert( "amazon", vcpair( "asin", 20 ) ); 0255 columns.insert( "amazon", vcpair( "locale", 2 ) ); 0256 columns.insert( "amazon", vcpair( "filename", 33 ) ); 0257 columns.insert( "artists", vcpair( "name", textColumnLength() ) ); 0258 columns.insert( "bookmark_groups", vcpair( "name", 255 ) ); 0259 columns.insert( "bookmark_groups", vcpair( "description", 255 ) ); 0260 columns.insert( "bookmark_groups", vcpair( "custom", 255 ) ); 0261 columns.insert( "bookmarks", vcpair( "name", 255 ) ); 0262 columns.insert( "bookmarks", vcpair( "url", 1024 ) ); 0263 columns.insert( "bookmarks", vcpair( "description", 1024 ) ); 0264 columns.insert( "bookmarks", vcpair( "custom", 255 ) ); 0265 columns.insert( "composers", vcpair( "name", textColumnLength() ) ); 0266 columns.insert( "devices", vcpair( "type", 255 ) ); 0267 columns.insert( "devices", vcpair( "label", 255 ) ); 0268 columns.insert( "devices", vcpair( "lastmountpoint", 255 ) ); 0269 columns.insert( "devices", vcpair( "uuid", 255 ) ); 0270 columns.insert( "devices", vcpair( "servername", 255 ) ); 0271 columns.insert( "devices", vcpair( "sharename", 255 ) ); 0272 columns.insert( "directories", vcpair( "dir", 1024 ) ); 0273 columns.insert( "genres", vcpair( "name", 255 ) ); 0274 columns.insert( "images", vcpair( "path", 255 ) ); 0275 columns.insert( "labels", vcpair( "label", textColumnLength() ) ); 0276 columns.insert( "lyrics", vcpair( "url", 1024 ) ); 0277 columns.insert( "playlist_groups", vcpair( "name", 255 ) ); 0278 columns.insert( "playlist_groups", vcpair( "description", 255 ) ); 0279 columns.insert( "playlist_tracks", vcpair( "url", 1024 ) ); 0280 columns.insert( "playlist_tracks", vcpair( "title", 255 ) ); 0281 columns.insert( "playlist_tracks", vcpair( "album", 255 ) ); 0282 columns.insert( "playlist_tracks", vcpair( "artist", 255 ) ); 0283 columns.insert( "playlist_tracks", vcpair( "uniqueid", 128 ) ); 0284 columns.insert( "playlists", vcpair( "name", 255 ) ); 0285 columns.insert( "playlists", vcpair( "description", 255 ) ); 0286 columns.insert( "playlists", vcpair( "urlid", 1024 ) ); 0287 columns.insert( "podcastchannels", vcpair( "copyright", 255 ) ); 0288 columns.insert( "podcastchannels", vcpair( "directory", 255 ) ); 0289 columns.insert( "podcastchannels", vcpair( "labels", 255 ) ); 0290 columns.insert( "podcastchannels", vcpair( "subscribedate", 255 ) ); 0291 columns.insert( "podcastepisodes", vcpair( "guid", 1024 ) ); 0292 columns.insert( "podcastepisodes", vcpair( "mimetype", 255 ) ); 0293 columns.insert( "podcastepisodes", vcpair( "pubdate", 255 ) ); 0294 columns.insert( "statistics_permanent", vcpair( "url", 1024 ) ); 0295 columns.insert( "statistics_tag", vcpair( "name", 255 ) ); 0296 columns.insert( "statistics_tag", vcpair( "artist", 255 ) ); 0297 columns.insert( "tracks", vcpair( "title", textColumnLength() ) ); 0298 columns.insert( "urls", vcpair( "rpath", 1024 ) ); 0299 columns.insert( "urls", vcpair( "uniqueid", 128 ) ); 0300 columns.insert( "years", vcpair( "name", textColumnLength() ) ); 0301 0302 QMultiMap<QString, vcpair>::const_iterator i, iEnd; 0303 for( i = columns.constBegin(), iEnd = columns.constEnd(); i != iEnd; ++i ) 0304 { 0305 storage->query( "ALTER TABLE " + i.key() + " MODIFY " + i.value().first + " VARBINARY(" + QString::number( i.value().second ) + ')' ); 0306 storage->query( "ALTER IGNORE TABLE " + i.key() + " MODIFY " + i.value().first + 0307 " VARCHAR(" + QString::number( i.value().second ) + ") CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL" ); 0308 } 0309 0310 columns.clear(); 0311 0312 //text fields, not varchars 0313 columns.insert( "lyrics", vcpair( "lyrics", 0 ) ); 0314 columns.insert( "podcastchannels", vcpair( "url", 0 ) ); 0315 columns.insert( "podcastchannels", vcpair( "title", 0 ) ); 0316 columns.insert( "podcastchannels", vcpair( "weblink", 0 ) ); 0317 columns.insert( "podcastchannels", vcpair( "image", 0 ) ); 0318 columns.insert( "podcastchannels", vcpair( "description", 0 ) ); 0319 columns.insert( "podcastepisodes", vcpair( "url", 0 ) ); 0320 columns.insert( "podcastepisodes", vcpair( "localurl", 0 ) ); 0321 columns.insert( "podcastepisodes", vcpair( "title", 0 ) ); 0322 columns.insert( "podcastepisodes", vcpair( "subtitle", 0 ) ); 0323 columns.insert( "podcastepisodes", vcpair( "description", 0 ) ); 0324 columns.insert( "tracks", vcpair( "comment", 0 ) ); 0325 0326 storage->query( "DROP INDEX url_podchannel ON podcastchannels" ); 0327 storage->query( "DROP INDEX url_podepisode ON podcastepisodes" ); 0328 storage->query( "DROP INDEX localurl_podepisode ON podcastepisodes" ); 0329 for( i = columns.constBegin(), iEnd = columns.constEnd(); i != iEnd; ++i ) 0330 { 0331 storage->query( "ALTER TABLE " + i.key() + " MODIFY " + i.value().first + " BLOB" ); 0332 storage->query( "ALTER IGNORE TABLE " + i.key() + " MODIFY " + i.value().first + " TEXT CHARACTER SET utf8 NOT NULL" ); 0333 } 0334 storage->query( "CREATE FULLTEXT INDEX url_podchannel ON podcastchannels( url )" ); 0335 storage->query( "CREATE FULLTEXT INDEX url_podepisode ON podcastepisodes( url )" ); 0336 storage->query( "CREATE FULLTEXT INDEX localurl_podepisode ON podcastepisodes( localurl )" ); 0337 } 0338 0339 void 0340 DatabaseUpdater::upgradeVersion5to6() 0341 { 0342 DEBUG_BLOCK 0343 0344 auto storage = m_collection->sqlStorage(); 0345 0346 //first, drop tables that can easily be recreated by doing an update 0347 QStringList dropTables; 0348 dropTables << "jamendo_albums" << "jamendo_artists" << "jamendo_genre" << "jamendo_tracks"; 0349 dropTables << "magnatune_albums" << "magnatune_artists" << "magnatune_genre" << "magnatune_moods" << "magnatune_tracks"; 0350 dropTables << "opmldirectory_albums" << "opmldirectory_artists" << "opmldirectory_genre" << "opmldirectory_tracks"; 0351 0352 foreach( const QString &table, dropTables ) 0353 storage->query( "DROP TABLE " + table ); 0354 0355 //now, the rest of them 0356 QStringList tables; 0357 tables << "admin" << "albums" << "amazon" << "artists" << "bookmark_groups" << "bookmarks"; 0358 tables << "composers" << "devices" << "directories" << "genres" << "images" << "labels" << "lyrics"; 0359 tables << "playlist_groups" << "playlist_tracks" << "playlists"; 0360 tables << "podcastchannels" << "podcastepisodes"; 0361 tables << "statistics" << "statistics_permanent" << "statistics_tag"; 0362 tables << "tracks" << "urls" << "urls_labels" << "years"; 0363 0364 foreach( const QString &table, tables ) 0365 storage->query( "ALTER TABLE " + table + " ENGINE = MyISAM" ); 0366 0367 typedef QPair<QString, int> vcpair; 0368 QMultiMap<QString, vcpair> columns; 0369 columns.insert( "bookmarks", vcpair( "url", 1000 ) ); 0370 columns.insert( "bookmarks", vcpair( "description", 1000 ) ); 0371 columns.insert( "directories", vcpair( "dir", 1000 ) ); 0372 columns.insert( "lyrics", vcpair( "url", 324 ) ); 0373 columns.insert( "playlist_tracks", vcpair( "url", 1000 ) ); 0374 columns.insert( "playlists", vcpair( "urlid", 1000 ) ); 0375 columns.insert( "podcastepisodes", vcpair( "guid", 1000 ) ); 0376 columns.insert( "statistics_permanent", vcpair( "url", 324 ) ); 0377 columns.insert( "urls", vcpair( "rpath", 324 ) ); 0378 columns.insert( "devices", vcpair( "servername", 80 ) ); 0379 columns.insert( "devices", vcpair( "sharename", 240 ) ); 0380 columns.insert( "statistics_tag", vcpair( "name", 108 ) ); 0381 columns.insert( "statistics_tag", vcpair( "artist", 108 ) ); 0382 columns.insert( "statistics_tag", vcpair( "album", 108 ) ); 0383 0384 QMultiMap<QString, vcpair>::const_iterator i, iEnd; 0385 0386 for( i = columns.constBegin(), iEnd = columns.constEnd(); i != iEnd; ++i ) 0387 storage->query( "ALTER IGNORE TABLE " + i.key() + " MODIFY " + i.value().first + " VARCHAR(" + QString::number( i.value().second ) + ") " ); 0388 0389 storage->query( "CREATE INDEX devices_rshare ON devices( servername, sharename );" ); 0390 storage->query( "CREATE UNIQUE INDEX lyrics_url ON lyrics(url);" ); 0391 storage->query( "CREATE UNIQUE INDEX urls_id_rpath ON urls(deviceid, rpath);" ); 0392 storage->query( "CREATE UNIQUE INDEX stats_tag_name_artist_album ON statistics_tag(name,artist,album)" ); 0393 } 0394 0395 void 0396 DatabaseUpdater::upgradeVersion6to7() 0397 { 0398 DEBUG_BLOCK 0399 0400 auto storage = m_collection->sqlStorage(); 0401 0402 typedef QPair<QString, int> vcpair; 0403 QMultiMap<QString, vcpair> columns; 0404 columns.insert( "directories", vcpair( "dir", 1000 ) ); 0405 columns.insert( "urls", vcpair( "rpath", 324 ) ); 0406 columns.insert( "statistics_permanent", vcpair( "url", 324 ) ); 0407 0408 QMultiMap<QString, vcpair>::const_iterator i, iEnd; 0409 0410 for( i = columns.constBegin(), iEnd = columns.constEnd(); i != iEnd; ++i ) 0411 { 0412 storage->query( "ALTER IGNORE TABLE " + i.key() + " MODIFY " + i.value().first + 0413 " VARCHAR(" + QString::number( i.value().second ) + ") COLLATE utf8_bin NOT NULL" ); 0414 } 0415 0416 columns.clear(); 0417 0418 } 0419 0420 0421 void 0422 DatabaseUpdater::upgradeVersion7to8() 0423 { 0424 DEBUG_BLOCK 0425 0426 auto storage = m_collection->sqlStorage(); 0427 0428 QHash< int, int > trackLengthHash; 0429 0430 // First, get the lengths from the db and insert them into a hash 0431 const QStringList result = storage->query( "SELECT id, length FROM tracks" ); 0432 0433 QListIterator<QString> iter(result); 0434 while( iter.hasNext() ) 0435 trackLengthHash.insert( iter.next().toInt(), iter.next().toInt() ); 0436 0437 // Now Iterate over the hash, and insert each track back in, changing the length to milliseconds 0438 QHashIterator<int,int> iter2( trackLengthHash ); 0439 const QString updateString = QString( "UPDATE tracks SET length=%1 WHERE id=%2 ;"); 0440 while( iter2.hasNext() ) 0441 { 0442 iter2.next(); 0443 debug() << "Running the following query: " << updateString.arg( QString::number( iter2.value() * 1000 ), QString::number( iter2.key() ) ); 0444 storage->query( updateString.arg( QString::number( iter2.value() * 1000 ), QString::number( iter2.key() ) ) ); 0445 } 0446 } 0447 0448 void 0449 DatabaseUpdater::upgradeVersion8to9() 0450 { 0451 deleteAllRedundant( "album" ); 0452 } 0453 0454 void 0455 DatabaseUpdater::upgradeVersion9to10() 0456 { 0457 DEBUG_BLOCK 0458 0459 auto storage = m_collection->sqlStorage(); 0460 0461 //first the database 0462 storage->query( "ALTER DATABASE amarok DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin" ); 0463 0464 //now the tables 0465 0466 //first, drop tables that can easily be recreated by doing an update 0467 QStringList dropTables; 0468 dropTables << "jamendo_albums" << "jamendo_artists" << "jamendo_genre" << "jamendo_tracks"; 0469 dropTables << "magnatune_albums" << "magnatune_artists" << "magnatune_genre" << "magnatune_moods" << "magnatune_tracks"; 0470 dropTables << "opmldirectory_albums" << "opmldirectory_artists" << "opmldirectory_genre" << "opmldirectory_tracks"; 0471 0472 foreach( const QString &table, dropTables ) 0473 storage->query( "DROP TABLE " + table ); 0474 0475 //now, the rest of them 0476 QStringList tables; 0477 tables << "admin" << "albums" << "amazon" << "artists" << "bookmark_groups" << "bookmarks"; 0478 tables << "composers" << "devices" << "directories" << "genres" << "images" << "labels" << "lyrics"; 0479 tables << "playlist_groups" << "playlist_tracks" << "playlists"; 0480 tables << "podcastchannels" << "podcastepisodes"; 0481 tables << "statistics" << "statistics_permanent" << "statistics_tag"; 0482 tables << "tracks" << "urls" << "urls_labels" << "years"; 0483 0484 foreach( const QString &table, tables ) 0485 storage->query( "ALTER TABLE " + table + " DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin COLLATE utf8_bin ENGINE = MyISAM" ); 0486 0487 //now the columns (ugh) 0488 //first, varchar 0489 typedef QPair<QString, int> vcpair; 0490 QMultiMap<QString, vcpair> columns; 0491 columns.insert( "admin", vcpair( "component", 255 ) ); 0492 columns.insert( "albums", vcpair( "name", textColumnLength() ) ); 0493 columns.insert( "amazon", vcpair( "asin", 20 ) ); 0494 columns.insert( "amazon", vcpair( "locale", 2 ) ); 0495 columns.insert( "amazon", vcpair( "filename", 33 ) ); 0496 columns.insert( "artists", vcpair( "name", textColumnLength() ) ); 0497 columns.insert( "bookmark_groups", vcpair( "name", 255 ) ); 0498 columns.insert( "bookmark_groups", vcpair( "description", 255 ) ); 0499 columns.insert( "bookmark_groups", vcpair( "custom", 255 ) ); 0500 columns.insert( "bookmarks", vcpair( "name", 255 ) ); 0501 columns.insert( "bookmarks", vcpair( "url", 1000 ) ); 0502 columns.insert( "bookmarks", vcpair( "description", 1000 ) ); 0503 columns.insert( "bookmarks", vcpair( "custom", 255 ) ); 0504 columns.insert( "composers", vcpair( "name", textColumnLength() ) ); 0505 columns.insert( "devices", vcpair( "type", 255 ) ); 0506 columns.insert( "devices", vcpair( "label", 255 ) ); 0507 columns.insert( "devices", vcpair( "lastmountpoint", 255 ) ); 0508 columns.insert( "devices", vcpair( "uuid", 255 ) ); 0509 columns.insert( "devices", vcpair( "servername", 80 ) ); 0510 columns.insert( "devices", vcpair( "sharename", 240 ) ); 0511 columns.insert( "directories", vcpair( "dir", 1000 ) ); 0512 columns.insert( "genres", vcpair( "name", textColumnLength() ) ); 0513 columns.insert( "images", vcpair( "path", 255 ) ); 0514 columns.insert( "labels", vcpair( "label", textColumnLength() ) ); 0515 columns.insert( "lyrics", vcpair( "url", 324 ) ); 0516 columns.insert( "playlist_groups", vcpair( "name", 255 ) ); 0517 columns.insert( "playlist_groups", vcpair( "description", 255 ) ); 0518 columns.insert( "playlist_tracks", vcpair( "url", 1000 ) ); 0519 columns.insert( "playlist_tracks", vcpair( "title", 255 ) ); 0520 columns.insert( "playlist_tracks", vcpair( "album", 255 ) ); 0521 columns.insert( "playlist_tracks", vcpair( "artist", 255 ) ); 0522 columns.insert( "playlist_tracks", vcpair( "uniqueid", 128 ) ); 0523 columns.insert( "playlists", vcpair( "name", 255 ) ); 0524 columns.insert( "playlists", vcpair( "description", 255 ) ); 0525 columns.insert( "playlists", vcpair( "urlid", 1000 ) ); 0526 columns.insert( "podcastchannels", vcpair( "copyright", 255 ) ); 0527 columns.insert( "podcastchannels", vcpair( "directory", 255 ) ); 0528 columns.insert( "podcastchannels", vcpair( "labels", 255 ) ); 0529 columns.insert( "podcastchannels", vcpair( "subscribedate", 255 ) ); 0530 columns.insert( "podcastepisodes", vcpair( "guid", 1000 ) ); 0531 columns.insert( "podcastepisodes", vcpair( "mimetype", 255 ) ); 0532 columns.insert( "podcastepisodes", vcpair( "pubdate", 255 ) ); 0533 columns.insert( "statistics_permanent", vcpair( "url", 324 ) ); 0534 columns.insert( "statistics_tag", vcpair( "name", 108 ) ); 0535 columns.insert( "statistics_tag", vcpair( "artist", 108 ) ); 0536 columns.insert( "statistics_tag", vcpair( "album", 108 ) ); 0537 columns.insert( "tracks", vcpair( "title", textColumnLength() ) ); 0538 columns.insert( "urls", vcpair( "rpath", 324 ) ); 0539 columns.insert( "urls", vcpair( "uniqueid", 128 ) ); 0540 columns.insert( "years", vcpair( "name", textColumnLength() ) ); 0541 0542 QMultiMap<QString, vcpair>::const_iterator i, iEnd; 0543 0544 for( i = columns.constBegin(), iEnd = columns.constEnd(); i != iEnd; ++i ) 0545 { 0546 storage->query( "ALTER TABLE " + i.key() + " MODIFY " + i.value().first + " VARBINARY(" + QString::number( i.value().second ) + ')' ); 0547 storage->query( "ALTER IGNORE TABLE " + i.key() + " MODIFY " + i.value().first + 0548 " VARCHAR(" + QString::number( i.value().second ) + ") CHARACTER SET utf8 COLLATE utf8_bin NOT NULL" ); 0549 } 0550 0551 storage->query( "CREATE INDEX devices_rshare ON devices( servername, sharename );" ); 0552 storage->query( "CREATE UNIQUE INDEX lyrics_url ON lyrics(url);" ); 0553 storage->query( "CREATE UNIQUE INDEX urls_id_rpath ON urls(deviceid, rpath);" ); 0554 storage->query( "CREATE UNIQUE INDEX stats_tag_name_artist_album ON statistics_tag(name,artist,album)" ); 0555 0556 columns.clear(); 0557 0558 //text fields, not varchars 0559 columns.insert( "lyrics", vcpair( "lyrics", 0 ) ); 0560 columns.insert( "podcastchannels", vcpair( "url", 0 ) ); 0561 columns.insert( "podcastchannels", vcpair( "title", 0 ) ); 0562 columns.insert( "podcastchannels", vcpair( "weblink", 0 ) ); 0563 columns.insert( "podcastchannels", vcpair( "image", 0 ) ); 0564 columns.insert( "podcastchannels", vcpair( "description", 0 ) ); 0565 columns.insert( "podcastepisodes", vcpair( "url", 0 ) ); 0566 columns.insert( "podcastepisodes", vcpair( "localurl", 0 ) ); 0567 columns.insert( "podcastepisodes", vcpair( "title", 0 ) ); 0568 columns.insert( "podcastepisodes", vcpair( "subtitle", 0 ) ); 0569 columns.insert( "podcastepisodes", vcpair( "description", 0 ) ); 0570 columns.insert( "tracks", vcpair( "comment", 0 ) ); 0571 0572 storage->query( "DROP INDEX url_podchannel ON podcastchannels" ); 0573 storage->query( "DROP INDEX url_podepisode ON podcastepisodes" ); 0574 storage->query( "DROP INDEX localurl_podepisode ON podcastepisodes" ); 0575 for( i = columns.constBegin(), iEnd = columns.constEnd(); i != iEnd; ++i ) 0576 { 0577 storage->query( "ALTER TABLE " + i.key() + " MODIFY " + i.value().first + " BLOB" ); 0578 storage->query( "ALTER IGNORE TABLE " + i.key() + " MODIFY " + i.value().first + " TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL" ); 0579 } 0580 storage->query( "CREATE FULLTEXT INDEX url_podchannel ON podcastchannels( url )" ); 0581 storage->query( "CREATE FULLTEXT INDEX url_podepisode ON podcastepisodes( url )" ); 0582 storage->query( "CREATE FULLTEXT INDEX localurl_podepisode ON podcastepisodes( localurl )" ); 0583 } 0584 0585 void 0586 DatabaseUpdater::upgradeVersion10to11() 0587 { 0588 DEBUG_BLOCK 0589 //OK, this isn't really a database upgrade, but it does affect scanning. 0590 //New default is for the charset detector not to run; but those that have existing collection 0591 //won't like it if suddenly that changes their behavior, so set to true for existing collections 0592 AmarokConfig::setUseCharsetDetector( true ); 0593 } 0594 0595 void 0596 DatabaseUpdater::upgradeVersion11to12() 0597 { 0598 DEBUG_BLOCK 0599 //Counteract the above -- force it off for everyone except those explicitly enabling it. 0600 AmarokConfig::setUseCharsetDetector( false ); 0601 } 0602 0603 void 0604 DatabaseUpdater::upgradeVersion12to13() 0605 { 0606 DEBUG_BLOCK 0607 m_collection->sqlStorage()->query( "UPDATE urls SET uniqueid = REPLACE(uniqueid, 'MB_', 'mb-');" ); 0608 } 0609 0610 void 0611 DatabaseUpdater::upgradeVersion13to14() 0612 { 0613 DEBUG_BLOCK 0614 auto storage = m_collection->sqlStorage(); 0615 0616 /* Following commands transition lyrics table from text-based urls (in fact just rpath 0617 * parts) to references to urls table. */ 0618 0619 // first, rename column 0620 storage->query( "ALTER TABLE lyrics CHANGE url rpath VARCHAR(324) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL" ); 0621 // add integer column for url id 0622 storage->query( "ALTER TABLE lyrics ADD COLUMN url INT NULL DEFAULT NULL FIRST" ); 0623 // try to extract url id from urls table using rpath 0624 storage->query( "UPDATE lyrics l SET l.url = (SELECT u.id FROM urls u WHERE u.rpath = l.rpath LIMIT 1)" ); 0625 // delete entries with no matches in urls table; these should be just stale ones 0626 storage->query( "DELETE FROM lyrics WHERE url IS NULL" ); 0627 // make the url column non-null 0628 storage->query( "ALTER TABLE lyrics MODIFY url INT NOT NULL" ); 0629 // select duplicate ids into temporary table 0630 storage->query( "CREATE TEMPORARY TABLE duplicate_lyrics_ids ( id INT NOT NULL ) " 0631 "ENGINE=MEMORY SELECT dupl.id FROM lyrics orig " 0632 "LEFT JOIN lyrics dupl ON dupl.url = orig.url AND dupl.id > orig.id" ); 0633 // delete duplicate lyrics entries 0634 storage->query( "DELETE FROM lyrics WHERE id IN (SELECT id FROM duplicate_lyrics_ids)" ); 0635 // drop unwanted columns along with indexes defined on them 0636 storage->query( "ALTER TABLE lyrics DROP id, DROP rpath" ); 0637 // add primary key; should definitely not fail as we have removed duplicate entries 0638 storage->query( "ALTER TABLE lyrics ADD PRIMARY KEY(url)" ); 0639 } 0640 0641 void 0642 DatabaseUpdater::upgradeVersion14to15() 0643 { 0644 /* This update solves bug 302837. In short, updates 0645 * 4 -> 5, 5 -> 6, 6 -> 7 and 9 -> 10 ignored NULL status of some columns and replaced 0646 * them with NOT NULL columns, causing various consequences, one of them is Dynamic 0647 * Collection not working. Fix it back. 0648 * 0649 * A list of columns to fix was obtained by comparing a database created by 0650 * Amarok 2.1.1 and then upgraded to current version with a db freshly created by 0651 * Amarok 2.6-git. 0652 */ 0653 DEBUG_BLOCK 0654 auto storage = m_collection->sqlStorage(); 0655 0656 // zero length = TEXT datatype 0657 typedef QPair<QString, int> vcpair; 0658 QMultiMap<QString, vcpair> columns; 0659 0660 columns.insert( "admin", vcpair( "component", 255 ) ); 0661 columns.insert( "devices", vcpair( "type", 255 ) ); 0662 columns.insert( "devices", vcpair( "label", 255 ) ); 0663 columns.insert( "devices", vcpair( "lastmountpoint", 255 ) ); 0664 columns.insert( "devices", vcpair( "uuid", 255 ) ); 0665 columns.insert( "devices", vcpair( "servername", 80 ) ); 0666 columns.insert( "devices", vcpair( "sharename", 240 ) ); 0667 columns.insert( "labels", vcpair( "label", textColumnLength() ) ); 0668 columns.insert( "lyrics", vcpair( "lyrics", 0 ) ); 0669 columns.insert( "playlists", vcpair( "name", 255 ) ); 0670 columns.insert( "playlists", vcpair( "description", 255 ) ); 0671 columns.insert( "playlists", vcpair( "urlid", 1000 ) ); 0672 columns.insert( "playlist_groups", vcpair( "name", 255 ) ); 0673 columns.insert( "playlist_groups", vcpair( "description", 255 ) ); 0674 columns.insert( "playlist_tracks", vcpair( "url", 1000 ) ); 0675 columns.insert( "playlist_tracks", vcpair( "title", 255 ) ); 0676 columns.insert( "playlist_tracks", vcpair( "album", 255 ) ); 0677 columns.insert( "playlist_tracks", vcpair( "artist", 255 ) ); 0678 columns.insert( "playlist_tracks", vcpair( "uniqueid", 128 ) ); 0679 columns.insert( "podcastchannels", vcpair( "url", 0 ) ); 0680 columns.insert( "podcastchannels", vcpair( "title", 0 ) ); 0681 columns.insert( "podcastchannels", vcpair( "weblink", 0 ) ); 0682 columns.insert( "podcastchannels", vcpair( "image", 0 ) ); 0683 columns.insert( "podcastchannels", vcpair( "description", 0 ) ); 0684 columns.insert( "podcastchannels", vcpair( "copyright", 255 ) ); 0685 columns.insert( "podcastchannels", vcpair( "directory", 255 ) ); 0686 columns.insert( "podcastchannels", vcpair( "labels", 255 ) ); 0687 columns.insert( "podcastchannels", vcpair( "subscribedate", 255 ) ); 0688 columns.insert( "podcastepisodes", vcpair( "url", 0 ) ); 0689 columns.insert( "podcastepisodes", vcpair( "localurl", 0 ) ); 0690 columns.insert( "podcastepisodes", vcpair( "guid", 1000 ) ); 0691 columns.insert( "podcastepisodes", vcpair( "title", 0 ) ); 0692 columns.insert( "podcastepisodes", vcpair( "subtitle", 0 ) ); 0693 columns.insert( "podcastepisodes", vcpair( "description", 0 ) ); 0694 columns.insert( "podcastepisodes", vcpair( "mimetype", 255 ) ); 0695 columns.insert( "podcastepisodes", vcpair( "pubdate", 255 ) ); 0696 columns.insert( "statistics_tag", vcpair( "name", 108 ) ); 0697 columns.insert( "statistics_tag", vcpair( "artist", 108 ) ); 0698 columns.insert( "statistics_tag", vcpair( "album", 108 ) ); 0699 columns.insert( "tracks", vcpair( "title", textColumnLength() ) ); 0700 columns.insert( "tracks", vcpair( "comment", 0 ) ); 0701 columns.insert( "urls", vcpair( "uniqueid", 128 ) ); 0702 0703 QMapIterator<QString, vcpair> it( columns ); 0704 while( it.hasNext() ) 0705 { 0706 it.next(); 0707 QString table = it.key(); 0708 QString column = it.value().first; 0709 int length = it.value().second; 0710 0711 QString query; 0712 if( length > 0 ) 0713 query = QString( "ALTER TABLE `%1` CHANGE `%2` `%2` VARCHAR(%3) CHARACTER SET utf8 " 0714 "COLLATE utf8_bin NULL DEFAULT NULL" ).arg( table, column ).arg( length ); 0715 else 0716 query = QString( "ALTER TABLE `%1` CHANGE `%2` `%2` TEXT CHARACTER SET utf8 " 0717 "COLLATE utf8_bin" ).arg( table, column ); 0718 storage->query( query ); 0719 } 0720 0721 // there may be a stale unique index on the urls table, remove it if it is there: 0722 QStringList results = storage->query( "SHOW CREATE TABLE urls" ); 0723 bool oldIndexFound = results.value( 1 ).contains( "UNIQUE KEY `uniqueid`" ); 0724 if( oldIndexFound ) 0725 { 0726 debug() << "dropping obsolete INDEX uniqueid on table urls"; 0727 storage->query( "DROP INDEX uniqueid ON urls" ); 0728 } 0729 } 0730 0731 void 0732 DatabaseUpdater::cleanupDatabase() 0733 { 0734 // maybe clean up redundant information here? 0735 } 0736 0737 void 0738 DatabaseUpdater::checkTables( bool full ) 0739 { 0740 DEBUG_BLOCK 0741 0742 auto storage = m_collection->sqlStorage(); 0743 0744 QStringList res = storage->query( "SHOW TABLES" ); 0745 if( res.count() > 0 ) 0746 { 0747 foreach( const QString &table, res ) 0748 storage->query( "CHECK TABLE " + table + ( full ? " EXTENDED;" : " MEDIUM;" ) ); 0749 } 0750 } 0751 0752 0753 void 0754 DatabaseUpdater::createTables() const 0755 { 0756 DEBUG_BLOCK 0757 0758 auto storage = m_collection->sqlStorage(); 0759 0760 // see docs/database/amarokTables.svg for documentation about database layout 0761 { 0762 QString c = "CREATE TABLE admin (component " + storage->textColumnType() + ", version INTEGER) COLLATE = utf8_bin ENGINE = MyISAM;"; 0763 storage->query( c ); 0764 } 0765 { 0766 QString create = "CREATE TABLE devices " 0767 "(id " + storage->idType() + 0768 ",type " + storage->textColumnType() + 0769 ",label " + storage->textColumnType() + 0770 ",lastmountpoint " + storage->textColumnType() + 0771 ",uuid " + storage->textColumnType() + 0772 ",servername " + storage->textColumnType(80) + 0773 ",sharename " + storage->textColumnType(240) + ") COLLATE = utf8_bin ENGINE = MyISAM;"; 0774 storage->query( create ); 0775 storage->query( "CREATE INDEX devices_type ON devices( type );" ); 0776 storage->query( "CREATE UNIQUE INDEX devices_uuid ON devices( uuid );" ); 0777 storage->query( "CREATE INDEX devices_rshare ON devices( servername, sharename );" ); 0778 } 0779 { 0780 QString create = "CREATE TABLE urls " 0781 "(id " + storage->idType() + 0782 ",deviceid INTEGER" 0783 ",rpath " + storage->exactIndexableTextColumnType() + " NOT NULL" + 0784 ",directory INTEGER" 0785 ",uniqueid " + storage->exactTextColumnType(128) + " UNIQUE) COLLATE = utf8_bin ENGINE = MyISAM;"; 0786 storage->query( create ); 0787 storage->query( "CREATE UNIQUE INDEX urls_id_rpath ON urls(deviceid, rpath);" ); 0788 storage->query( "CREATE INDEX urls_uniqueid ON urls(uniqueid);" ); 0789 storage->query( "CREATE INDEX urls_directory ON urls(directory);" ); 0790 } 0791 { 0792 QString create = "CREATE TABLE directories " 0793 "(id " + storage->idType() + 0794 ",deviceid INTEGER" 0795 ",dir " + storage->exactTextColumnType() + " NOT NULL" + 0796 ",changedate INTEGER) COLLATE = utf8_bin ENGINE = MyISAM;"; 0797 storage->query( create ); 0798 storage->query( "CREATE INDEX directories_deviceid ON directories(deviceid);" ); 0799 } 0800 { 0801 QString create = "CREATE TABLE artists " 0802 "(id " + storage->idType() + 0803 ",name " + storage->textColumnType() + " NOT NULL) COLLATE = utf8_bin ENGINE = MyISAM;"; 0804 storage->query( create ); 0805 storage->query( "CREATE UNIQUE INDEX artists_name ON artists(name);" ); 0806 } 0807 { 0808 QString create = "CREATE TABLE images " 0809 "(id " + storage->idType() + 0810 ",path " + storage->textColumnType() + " NOT NULL) COLLATE = utf8_bin ENGINE = MyISAM;"; 0811 storage->query( create ); 0812 storage->query( "CREATE UNIQUE INDEX images_name ON images(path);" ); 0813 } 0814 { 0815 QString c = "CREATE TABLE albums " 0816 "(id " + storage->idType() + 0817 ",name " + storage->textColumnType() + " NOT NULL" 0818 ",artist INTEGER" + 0819 ",image INTEGER) COLLATE = utf8_bin ENGINE = MyISAM;"; 0820 storage->query( c ); 0821 storage->query( "CREATE INDEX albums_name ON albums(name);" ); 0822 storage->query( "CREATE INDEX albums_artist ON albums(artist);" ); 0823 storage->query( "CREATE INDEX albums_image ON albums(image);" ); 0824 storage->query( "CREATE UNIQUE INDEX albums_name_artist ON albums(name,artist);" ); 0825 //the index below should not be necessary. uncomment if a query plan shows it is 0826 //storage->query( "CREATE UNIQUE INDEX albums_artist_name ON albums(artist,name);" ); 0827 } 0828 { 0829 QString create = "CREATE TABLE genres " 0830 "(id " + storage->idType() + 0831 ",name " + storage->textColumnType() + " NOT NULL) COLLATE = utf8_bin ENGINE = MyISAM;"; 0832 storage->query( create ); 0833 storage->query( "CREATE UNIQUE INDEX genres_name ON genres(name);" ); 0834 } 0835 { 0836 QString create = "CREATE TABLE composers " 0837 "(id " + storage->idType() + 0838 ",name " + storage->textColumnType() + " NOT NULL) COLLATE = utf8_bin ENGINE = MyISAM;"; 0839 storage->query( create ); 0840 storage->query( "CREATE UNIQUE INDEX composers_name ON composers(name);" ); 0841 } 0842 { 0843 QString create = "CREATE TABLE years " 0844 "(id " + storage->idType() + 0845 ",name " + storage->textColumnType() + " NOT NULL) COLLATE = utf8_bin ENGINE = MyISAM;"; 0846 storage->query( create ); 0847 storage->query( "CREATE UNIQUE INDEX years_name ON years(name);" ); 0848 } 0849 { 0850 QString c = "CREATE TABLE tracks " 0851 "(id " + storage->idType() + 0852 ",url INTEGER" 0853 ",artist INTEGER" 0854 ",album INTEGER" 0855 ",genre INTEGER" 0856 ",composer INTEGER" 0857 ",year INTEGER" 0858 ",title " + storage->textColumnType() + 0859 ",comment " + storage->longTextColumnType() + 0860 ",tracknumber INTEGER" 0861 ",discnumber INTEGER" 0862 ",bitrate INTEGER" 0863 ",length INTEGER" 0864 ",samplerate INTEGER" 0865 ",filesize INTEGER" 0866 ",filetype INTEGER" //does this still make sense? 0867 ",bpm FLOAT" 0868 ",createdate INTEGER" // this is the track creation time 0869 ",modifydate INTEGER" // UNUSED currently 0870 ",albumgain FLOAT" 0871 ",albumpeakgain FLOAT" // decibels, relative to albumgain 0872 ",trackgain FLOAT" 0873 ",trackpeakgain FLOAT" // decibels, relative to trackgain 0874 ") COLLATE = utf8_bin ENGINE = MyISAM;"; 0875 0876 storage->query( c ); 0877 storage->query( "CREATE UNIQUE INDEX tracks_url ON tracks(url);" ); 0878 0879 QStringList indices; 0880 indices << "id" << "artist" << "album" << "genre" << "composer" << "year" << "title"; 0881 indices << "discnumber" << "createdate" << "length" << "bitrate" << "filesize"; 0882 foreach( const QString &index, indices ) 0883 { 0884 QString query = QString( "CREATE INDEX tracks_%1 ON tracks(%2);" ).arg( index, index ); 0885 storage->query( query ); 0886 } 0887 } 0888 { 0889 QString c = "CREATE TABLE statistics " 0890 "(id " + storage->idType() + 0891 ",url INTEGER NOT NULL" 0892 ",createdate INTEGER" // this is the first played time 0893 ",accessdate INTEGER" // this is the last played time 0894 ",score FLOAT" 0895 ",rating INTEGER NOT NULL DEFAULT 0" // the "default" undefined rating is 0. We cannot display anything else. 0896 ",playcount INTEGER NOT NULL DEFAULT 0" // a track is either played or not. 0897 ",deleted BOOL NOT NULL DEFAULT " + storage->boolFalse() + 0898 ") COLLATE = utf8_bin ENGINE = MyISAM;"; 0899 storage->query( c ); 0900 storage->query( "CREATE UNIQUE INDEX statistics_url ON statistics(url);" ); 0901 QStringList indices; 0902 indices << "createdate" << "accessdate" << "score" << "rating" << "playcount"; 0903 foreach( const QString &index, indices ) 0904 { 0905 QString q = QString( "CREATE INDEX statistics_%1 ON statistics(%2);" ).arg( index, index ); 0906 storage->query( q ); 0907 } 0908 } 0909 { 0910 QString q = "CREATE TABLE labels " 0911 "(id " + storage->idType() + 0912 ",label " + storage->textColumnType() + 0913 ") COLLATE = utf8_bin ENGINE = MyISAM;"; 0914 storage->query( q ); 0915 storage->query( "CREATE UNIQUE INDEX labels_label ON labels(label);" ); 0916 0917 QString r = "CREATE TABLE urls_labels(url INTEGER, label INTEGER);"; 0918 storage->query( r ); 0919 storage->query( "CREATE INDEX urlslabels_url ON urls_labels(url);" ); 0920 storage->query( "CREATE INDEX urlslabels_label ON urls_labels(label);" ); 0921 } 0922 { 0923 QString q = "CREATE TABLE amazon (" 0924 "asin " + storage->textColumnType( 20 ) + 0925 ",locale " + storage->textColumnType( 2 ) + 0926 ",filename " + storage->textColumnType( 33 ) + 0927 ",refetchdate INTEGER ) COLLATE = utf8_bin ENGINE = MyISAM;"; 0928 storage->query( q ); 0929 storage->query( "CREATE INDEX amazon_date ON amazon(refetchdate);" ); 0930 } 0931 { 0932 QString q = "CREATE TABLE lyrics (" 0933 "url INTEGER PRIMARY KEY" 0934 ",lyrics " + storage->longTextColumnType() + 0935 ") COLLATE = utf8_bin ENGINE = MyISAM;"; 0936 storage->query( q ); 0937 } 0938 storage->query( "INSERT INTO admin(component,version) " 0939 "VALUES('AMAROK_TRACK'," + QString::number( DB_VERSION ) + ");" ); 0940 { 0941 storage->query( "CREATE TABLE statistics_permanent " 0942 "(url " + storage->exactIndexableTextColumnType() + " NOT NULL" + 0943 ",firstplayed DATETIME" 0944 ",lastplayed DATETIME" 0945 ",score FLOAT" 0946 ",rating INTEGER DEFAULT 0" 0947 ",playcount INTEGER) COLLATE = utf8_bin ENGINE = MyISAM;" ); 0948 0949 //Below query is invalid! Fix it, and then put the proper query in an upgrade function! 0950 storage->query( "CREATE UNIQUE INDEX stats_perm_url ON statistics_permanent(url)" ); 0951 0952 storage->query( "CREATE TABLE statistics_tag " 0953 "(name " + storage->textColumnType(108) + 0954 ",artist " + storage->textColumnType(108) + 0955 ",album " + storage->textColumnType(108) + 0956 ",firstplayed DATETIME" 0957 ",lastplayed DATETIME" 0958 ",score FLOAT" 0959 ",rating INTEGER DEFAULT 0" 0960 ",playcount INTEGER) COLLATE = utf8_bin ENGINE = MyISAM" ); 0961 0962 //Below query is invalid! Fix it, and then put the proper query in an upgrade function! 0963 storage->query( "CREATE UNIQUE INDEX stats_tag_name_artist_album ON statistics_tag(name,artist,album)" ); 0964 } 0965 } 0966 0967 int 0968 DatabaseUpdater::adminValue( const QString &key ) const 0969 { 0970 auto storage = m_collection->sqlStorage(); 0971 0972 QStringList columns = storage->query( 0973 QString( "SELECT column_name FROM INFORMATION_SCHEMA.columns " 0974 "WHERE table_name='admin'" ) ); 0975 if( columns.isEmpty() ) 0976 return 0; //no table with that name 0977 0978 QStringList values = storage->query( 0979 QString( "SELECT version FROM admin WHERE component = '%1';") 0980 .arg(storage->escape( key ) ) ); 0981 if( values.isEmpty() ) 0982 return 0; 0983 0984 return values.first().toInt(); 0985 } 0986 0987 void 0988 DatabaseUpdater::deleteAllRedundant( const QString &type ) 0989 { 0990 auto storage = m_collection->sqlStorage(); 0991 0992 const QString tablename = type + 's'; 0993 if( type == "artist" ) 0994 storage->query( QString( "DELETE FROM artists " 0995 "WHERE id NOT IN ( SELECT artist FROM tracks WHERE artist IS NOT NULL ) AND " 0996 "id NOT IN ( SELECT artist FROM albums WHERE artist IS NOT NULL )") ); 0997 else 0998 storage->query( QString( "DELETE FROM %1 " 0999 "WHERE id NOT IN ( SELECT %2 FROM tracks WHERE %2 IS NOT NULL )" ). 1000 arg( tablename, type ) ); 1001 } 1002 1003 void 1004 DatabaseUpdater::deleteOrphanedByDirectory( const QString &table ) 1005 { 1006 auto storage = m_collection->sqlStorage(); 1007 QString query( "DELETE FROM %1 WHERE directory NOT IN ( SELECT id FROM directories )" ); 1008 storage->query( query.arg( table ) ); 1009 } 1010 1011 void 1012 DatabaseUpdater::deleteOrphanedByUrl( const QString &table ) 1013 { 1014 auto storage = m_collection->sqlStorage(); 1015 QString query( "DELETE FROM %1 WHERE url NOT IN ( SELECT id FROM urls )" ); 1016 storage->query( query.arg( table ) ); 1017 } 1018 1019 void 1020 DatabaseUpdater::removeFilesInDir( int deviceid, const QString &rdir ) 1021 { 1022 auto storage = m_collection->sqlStorage(); 1023 1024 QString select = QString( "SELECT urls.id FROM urls LEFT JOIN directories ON urls.directory = directories.id " 1025 "WHERE directories.deviceid = %1 AND directories.dir = '%2';" ) 1026 .arg( QString::number( deviceid ), storage->escape( rdir ) ); 1027 QStringList idResult = storage->query( select ); 1028 if( !idResult.isEmpty() ) 1029 { 1030 QString id; 1031 QString ids; 1032 QStringList::ConstIterator it = idResult.constBegin(), end = idResult.constEnd(); 1033 while( it != end ) 1034 { 1035 id = (*(it++)); 1036 if( !ids.isEmpty() ) 1037 ids += ','; 1038 ids += id; 1039 } 1040 QString drop = QString( "DELETE FROM tracks WHERE url IN (%1);" ).arg( ids ); 1041 storage->query( drop ); 1042 } 1043 } 1044 1045 void 1046 DatabaseUpdater::writeCSVFile( const QString &table, const QString &filename, bool forceDebug ) 1047 { 1048 auto storage = m_collection->sqlStorage(); 1049 1050 if( !forceDebug && !m_debugDatabaseContent ) 1051 return; 1052 1053 QString ctable = table; 1054 QStringList columns = storage->query( 1055 QString( "SELECT column_name FROM INFORMATION_SCHEMA.columns WHERE table_name='%1'" ) 1056 .arg( storage->escape( ctable ) ) ); 1057 1058 if( columns.isEmpty() ) 1059 return; //no table with that name 1060 1061 // ok. it was probably a little bit unlucky to name a table statistics 1062 // that clashes with INFORMATION_SCHEMA.statistics, a build in table. 1063 if( table == "statistics" && columns.count() > 15 ) 1064 { 1065 // delete all columns with full upper case name. Those are the builtins. 1066 for( int i = columns.count()-1; i>= 0; --i ) 1067 { 1068 if( columns[i].toUpper() == columns[i] ) 1069 columns.removeAt( i ); 1070 } 1071 } 1072 1073 QString select; 1074 foreach( const QString &column, columns ) 1075 { 1076 if( !select.isEmpty() ) 1077 select.append( ',' ); 1078 select.append( column ); 1079 } 1080 1081 QString query = "SELECT %1 FROM %2"; 1082 1083 QStringList result = storage->query( query.arg( select, storage->escape( table ) ) ); 1084 1085 QFile file( filename ); 1086 if( file.open( QFile::WriteOnly | QFile::Text | QFile::Truncate ) ) 1087 { 1088 QTextStream stream( &file ); 1089 int i = 0; 1090 //write header 1091 foreach( const QString &column, columns ) 1092 { 1093 stream << column; 1094 stream << ';'; 1095 } 1096 stream << '\n'; 1097 1098 foreach( const QString &data, result ) 1099 { 1100 stream << data; 1101 stream << ';'; 1102 ++i; 1103 if( i % columns.isEmpty() ) 1104 stream << '\n'; 1105 } 1106 file.close(); 1107 } 1108 } 1109