File indexing completed on 2024-05-19 04:50:17
0001 /**************************************************************************************** 0002 * Copyright (c) 2006,2007 Nikolaj Hald Nielsen <nhn@kde.org> * 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 "MagnatuneDatabaseHandler.h" 0018 0019 #include <core-impl/storage/StorageManager.h> 0020 #include <core/support/Debug.h> 0021 #include <core/storage/SqlStorage.h> 0022 0023 using namespace Meta; 0024 0025 MagnatuneDatabaseHandler::MagnatuneDatabaseHandler() 0026 {} 0027 0028 0029 MagnatuneDatabaseHandler::~MagnatuneDatabaseHandler() 0030 {} 0031 0032 void 0033 MagnatuneDatabaseHandler::createDatabase( ) 0034 { 0035 //Get database instance 0036 auto sqlDb = StorageManager::instance()->sqlStorage(); 0037 0038 QString autoIncrement = "AUTO_INCREMENT"; 0039 0040 // create table containing tracks 0041 QString queryString = "CREATE TABLE magnatune_tracks (" 0042 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') + 0043 "name " + sqlDb->textColumnType() + QLatin1Char(',') + 0044 "track_number INTEGER," 0045 "length INTEGER," 0046 "album_id INTEGER," 0047 "artist_id INTEGER," 0048 "preview_lofi " + sqlDb->exactTextColumnType() + QLatin1Char(',') + 0049 "preview_ogg " + sqlDb->exactTextColumnType() + QLatin1Char(',') + 0050 "preview_url " + sqlDb->exactTextColumnType() + ") ENGINE = MyISAM;"; 0051 0052 debug() << "Creating magnatune_tracks: " << queryString; 0053 0054 0055 QStringList result = sqlDb->query( queryString ); 0056 0057 sqlDb->query( "CREATE INDEX magnatune_tracks_album_id ON magnatune_tracks(album_id);" ); 0058 sqlDb->query( "CREATE INDEX magnatune_tracks_artist_id ON magnatune_tracks(artist_id);" ); 0059 0060 //Create album table 0061 queryString = "CREATE TABLE magnatune_albums (" 0062 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') + 0063 "name " + sqlDb->textColumnType() + QLatin1Char(',') + 0064 "year INTEGER," 0065 "artist_id INTEGER," 0066 "album_code " + sqlDb->textColumnType() + QLatin1Char(',') + 0067 "cover_url " + sqlDb->exactTextColumnType() + QLatin1Char(',') + 0068 "description " + sqlDb->exactTextColumnType() + ") ENGINE = MyISAM;"; 0069 0070 debug() << "Creating Magnatune_albums: " << queryString; 0071 0072 result = sqlDb->query( queryString ); 0073 0074 sqlDb->query( "CREATE INDEX magnatune_albums_name ON magnatune_albums(name);" ); 0075 sqlDb->query( "CREATE INDEX magnatune_albums_artist_id ON magnatune_albums(artist_id);" ); 0076 0077 0078 //Create artist table 0079 queryString = "CREATE TABLE magnatune_artists (" 0080 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') + 0081 "name " + sqlDb->textColumnType() + QLatin1Char(',') + 0082 "artist_page " + sqlDb->exactTextColumnType() + QLatin1Char(',') + 0083 "description " + sqlDb->textColumnType() + QLatin1Char(',') + 0084 "photo_url " + sqlDb->exactTextColumnType() + ") ENGINE = MyISAM;"; 0085 0086 debug() << "Creating magnatune_artist: " << queryString; 0087 0088 result = sqlDb->query( queryString ); 0089 0090 sqlDb->query( "CREATE INDEX magnatune_artists_name ON magnatune_artists(name);" ); 0091 0092 //create genre table 0093 queryString = "CREATE TABLE magnatune_genre (" 0094 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') + 0095 "name " + sqlDb->textColumnType() + QLatin1Char(',') + 0096 "album_id INTEGER" + ") ENGINE = MyISAM;"; 0097 0098 result = sqlDb->query( queryString ); 0099 0100 sqlDb->query( "CREATE INDEX magnatune_genre_name ON magnatune_genre(name);" ); 0101 sqlDb->query( "CREATE INDEX magnatune_genre_album_id ON magnatune_genre(album_id);" ); 0102 0103 0104 //create moods table 0105 queryString = "CREATE TABLE magnatune_moods (" 0106 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') + 0107 "track_id INTEGER," + 0108 "mood " + sqlDb->textColumnType() + ") ENGINE = MyISAM;"; 0109 0110 debug() << "Creating magnatune_moods: " << queryString; 0111 0112 result = sqlDb->query( queryString ); 0113 0114 0115 0116 } 0117 0118 void 0119 MagnatuneDatabaseHandler::destroyDatabase( ) 0120 { 0121 auto sqlDb = StorageManager::instance()->sqlStorage(); 0122 QStringList result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_tracks;" ); 0123 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_albums;" ); 0124 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_artists;" ); 0125 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_genre;" ); 0126 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_moods;" ); 0127 0128 0129 /* that would only work for db2/oracle. Other databases connect the index to the table (which we just dropped) 0130 result = sqlDb->query( "DROP INDEX magnatune_tracks_artist_id;"); 0131 result = sqlDb->query( "DROP INDEX magnatune_tracks_album_id;"); 0132 result = sqlDb->query( "DROP INDEX magnatune_album_name;"); 0133 result = sqlDb->query( "DROP INDEX magnatune_album_artist_id;"); 0134 result = sqlDb->query( "DROP INDEX magnatune_artist_name;"); 0135 result = sqlDb->query( "DROP INDEX magnatune_genre_album_id;"); 0136 result = sqlDb->query( "DROP INDEX magnatune_genre_name;"); 0137 */ 0138 0139 /* if ( sqlDb->type() == DbConnection::postgresql ) 0140 { 0141 sqlDb->query( QString( "DROP SEQUENCE magnatune_track_seq;" ) ); 0142 sqlDb->query( QString( "DROP SEQUENCE magnatune_album_seq;" ) ); 0143 sqlDb->query( QString( "DROP SEQUENCE magnatune_artist_seq;" ) ); 0144 sqlDb->query( QString( "DROP SEQUENCE magnatune_moods_seq;" ) ); 0145 }*/ 0146 } 0147 0148 int 0149 MagnatuneDatabaseHandler::insertTrack( ServiceTrack *track ) 0150 { 0151 MagnatuneTrack * mTrack = static_cast<MagnatuneTrack *> ( track ); 0152 0153 auto sqlDb = StorageManager::instance()->sqlStorage(); 0154 QString queryString = "INSERT INTO magnatune_tracks ( name, track_number, length, " 0155 "album_id, artist_id, preview_lofi, preview_ogg, preview_url ) VALUES ( '" 0156 + sqlDb->escape( mTrack->name()) + "', " 0157 + QString::number( mTrack->trackNumber() ) + ", " 0158 + QString::number( mTrack->length() * 1000 ) + ", " 0159 + QString::number( mTrack->albumId() ) + ", " 0160 + QString::number( mTrack->artistId() ) + ", '" 0161 + sqlDb->escape( mTrack->lofiUrl() ) + "', '" 0162 + sqlDb->escape( mTrack->oggUrl() ) + "', '" 0163 + sqlDb->escape( mTrack->uidUrl() ) + "' );"; 0164 0165 0166 // debug() << "Adding Magnatune track " << queryString; 0167 int trackId = sqlDb->insert( queryString, nullptr ); 0168 0169 return trackId; 0170 0171 0172 } 0173 0174 int 0175 MagnatuneDatabaseHandler::insertAlbum( ServiceAlbum *album ) 0176 { 0177 0178 MagnatuneAlbum * mAlbum = static_cast<MagnatuneAlbum *> ( album ); 0179 0180 QString queryString; 0181 auto sqlDb = StorageManager::instance()->sqlStorage(); 0182 queryString = "INSERT INTO magnatune_albums ( name, year, artist_id, " 0183 "album_code, cover_url, description ) VALUES ( '" 0184 + sqlDb->escape( sqlDb->escape( mAlbum->name() ) ) + "', " 0185 + QString::number( mAlbum->launchYear() ) + ", " 0186 + QString::number( mAlbum->artistId() ) + ", '" 0187 + sqlDb->escape( mAlbum->albumCode() ) + "', '" 0188 + sqlDb->escape( mAlbum->coverUrl() ) + "', '" 0189 + sqlDb->escape( mAlbum->description() )+ "' );"; 0190 0191 //debug() << "Adding Magnatune album " << queryString; 0192 0193 return sqlDb->insert( queryString, nullptr ); 0194 } 0195 0196 0197 0198 int 0199 MagnatuneDatabaseHandler::insertArtist( ServiceArtist *artist ) 0200 { 0201 MagnatuneArtist * mArtist = static_cast<MagnatuneArtist *> ( artist ); 0202 0203 QString queryString; 0204 auto sqlDb = StorageManager::instance()->sqlStorage(); 0205 queryString = "INSERT INTO magnatune_artists ( name, artist_page, description, " 0206 "photo_url ) VALUES ( '" 0207 + sqlDb->escape( mArtist->name() ) + "', '" 0208 + sqlDb->escape( mArtist->magnatuneUrl().url() ) + "', '" 0209 + sqlDb->escape( mArtist->description() ) + "', '" 0210 + sqlDb->escape( mArtist->photoUrl().url() ) + "' );"; 0211 0212 //debug() << "Adding Magnatune artist " << queryString; 0213 0214 return sqlDb->insert( queryString, nullptr ); 0215 } 0216 0217 0218 void 0219 MagnatuneDatabaseHandler::begin( ) 0220 { 0221 0222 auto sqlDb = StorageManager::instance()->sqlStorage(); 0223 0224 QString queryString = "BEGIN;"; 0225 0226 sqlDb->query( queryString ); 0227 } 0228 0229 void 0230 MagnatuneDatabaseHandler::commit( ) 0231 { 0232 auto sqlDb = StorageManager::instance()->sqlStorage(); 0233 QString queryString = "COMMIT;"; 0234 0235 sqlDb->query( queryString ); 0236 sqlDb->query( "FLUSH TABLES;" ); 0237 } 0238 0239 void MagnatuneDatabaseHandler::insertMoods(int trackId, const QStringList &moods) 0240 { 0241 0242 QString queryString; 0243 auto sqlDb = StorageManager::instance()->sqlStorage(); 0244 0245 foreach( const QString &mood, moods ) { 0246 queryString = "INSERT INTO magnatune_moods ( track_id, mood ) VALUES ( " 0247 + QString::number( trackId ) + ", '" 0248 + sqlDb->escape( mood ) + "' );"; 0249 0250 0251 //debug() << "Adding Magnatune mood: " << queryString; 0252 sqlDb->insert( queryString, nullptr ); 0253 } 0254 } 0255 0256 int MagnatuneDatabaseHandler::getArtistIdByExactName(const QString & name) 0257 { 0258 0259 auto sqlDb = StorageManager::instance()->sqlStorage(); 0260 0261 QString queryString = "SELECT id from magnatune_artists WHERE name='" + sqlDb->escape( name ) + "';"; 0262 QStringList result = sqlDb->query( queryString ); 0263 0264 //debug() << "Looking for id of artist " << name << ":"; 0265 0266 if ( result.size() < 1 ) return -1; 0267 int artistId = result.first().toInt(); 0268 0269 //debug() << " Found: " << QString::number( artistId ) << ":"; 0270 0271 return artistId; 0272 0273 } 0274 0275 int MagnatuneDatabaseHandler::getAlbumIdByAlbumCode(const QString & albumcode) 0276 { 0277 auto sqlDb = StorageManager::instance()->sqlStorage(); 0278 0279 QString queryString = "SELECT id from magnatune_albums WHERE album_code='" + sqlDb->escape( albumcode ) + "';"; 0280 QStringList result = sqlDb->query( queryString ); 0281 0282 //debug() << "Looking for id of album " << albumcode << ":"; 0283 0284 if ( result.size() < 1 ) return -1; 0285 int albumId = result.first().toInt(); 0286 0287 //debug() << " Found: " << QString::number( albumId ) << ":"; 0288 0289 return albumId; 0290 } 0291 0292 int MagnatuneDatabaseHandler::insertGenre(ServiceGenre * genre) 0293 { 0294 QString queryString; 0295 auto sqlDb = StorageManager::instance()->sqlStorage(); 0296 queryString = "INSERT INTO magnatune_genre ( album_id, name " 0297 ") VALUES ( " 0298 + QString::number ( genre->albumId() ) + ", '" 0299 + sqlDb->escape( genre->name() ) + "' );"; 0300 0301 //debug() << "Adding Jamendo genre " << queryString; 0302 0303 return sqlDb->insert( queryString, nullptr ); 0304 } 0305 0306 0307 0308 0309 0310