File indexing completed on 2024-05-19 04:50:13
0001 /**************************************************************************************** 0002 * Copyright (c) 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 "JamendoDatabaseHandler.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 JamendoDatabaseHandler::JamendoDatabaseHandler() 0026 { 0027 } 0028 0029 JamendoDatabaseHandler::~JamendoDatabaseHandler() 0030 { 0031 } 0032 0033 void 0034 JamendoDatabaseHandler::createDatabase( ) 0035 { 0036 //Get database instance 0037 auto db = StorageManager::instance()->sqlStorage(); 0038 0039 0040 QString autoIncrement = "AUTO_INCREMENT"; 0041 0042 // create table containing tracks 0043 QString queryString = "CREATE TABLE jamendo_tracks (" 0044 "id INTEGER PRIMARY KEY " + autoIncrement + ',' + 0045 "name " + db->textColumnType() + ',' + 0046 "track_number INTEGER," 0047 "length INTEGER," 0048 "preview_url " + db->exactTextColumnType() + ',' + 0049 "album_id INTEGER," 0050 "artist_id INTEGER ) ENGINE = MyISAM;"; 0051 0052 debug() << "Creating jamendo_tracks: " << queryString; 0053 0054 QStringList result = db->query( queryString ); 0055 db->query( "CREATE INDEX jamendo_tracks_id ON jamendo_tracks(id);" ); 0056 db->query( "CREATE INDEX jamendo_tracks_album_id ON jamendo_tracks(album_id);" ); 0057 db->query( "CREATE INDEX jamendo_tracks_artist_id ON jamendo_tracks(artist_id);" ); 0058 0059 //Create album table 0060 queryString = "CREATE TABLE jamendo_albums (" 0061 "id INTEGER PRIMARY KEY " + autoIncrement + ',' + 0062 "name " + db->textColumnType() + ',' + 0063 "description " + db->exactTextColumnType() + ',' + 0064 "popularity FLOAT, " + 0065 "cover_url " + db->exactTextColumnType() + ',' + 0066 "launch_year Integer, " 0067 "genre " + db->exactTextColumnType() + ',' + 0068 "artist_id INTEGER, " 0069 "mp3_torrent_url " + db->exactTextColumnType() + ',' + 0070 "ogg_torrent_url " + db->exactTextColumnType() + " ) ENGINE = MyISAM;"; 0071 0072 debug() << "Creating jamendo_albums: " << queryString; 0073 0074 result = db->query( queryString ); 0075 0076 db->query( "CREATE INDEX jamendo_albums_id ON jamendo_albums(id);" ); 0077 db->query( "CREATE INDEX jamendo_albums_name ON jamendo_albums(name);" ); 0078 db->query( "CREATE INDEX jamendo_albums_artist_id ON jamendo_albums(artist_id);" ); 0079 0080 //Create artist table 0081 queryString = "CREATE TABLE jamendo_artists (" 0082 "id INTEGER PRIMARY KEY " + autoIncrement + ',' + 0083 "name " + db->textColumnType() + ',' + 0084 "description " + db->textColumnType() + ',' + 0085 "country " + db->textColumnType() + ',' + 0086 "photo_url " + db->textColumnType() + ',' + 0087 "jamendo_url " + db->textColumnType() + ',' + 0088 "home_url " + db->textColumnType() + ") ENGINE = MyISAM;"; 0089 0090 debug() << "Creating jamendo_artists: " << queryString; 0091 0092 result = db->query( queryString ); 0093 0094 db->query( "CREATE INDEX jamendo_artists_id ON jamendo_artists(id);" ); 0095 db->query( "CREATE INDEX jamendo_artists_name ON jamendo_artists(name);" ); 0096 0097 //create genre table 0098 queryString = "CREATE TABLE jamendo_genre (" 0099 "id INTEGER PRIMARY KEY " + autoIncrement + ',' + 0100 "name " + db->textColumnType() + ',' + 0101 "album_id INTEGER" + ") ENGINE = MyISAM;"; 0102 0103 debug() << "Creating jamendo_genres: " << queryString; 0104 0105 result = db->query( queryString ); 0106 0107 db->query( "CREATE INDEX jamendo_genre_id ON jamendo_genre(id);" ); 0108 db->query( "CREATE INDEX jamendo_genre_name ON jamendo_genre(name);" ); 0109 db->query( "CREATE INDEX jamendo_genre_album_id ON jamendo_genre(album_id);" ); 0110 } 0111 0112 void 0113 JamendoDatabaseHandler::destroyDatabase( ) 0114 { 0115 debug() << "Destroy Jamendo database "; 0116 0117 auto db = StorageManager::instance()->sqlStorage(); 0118 0119 QStringList result = db->query( "DROP INDEX jamendo_tracks_id ON jamendo_tracks;"); 0120 result = db->query( "DROP INDEX jamendo_tracks_artist_id ON jamendo_tracks;"); 0121 result = db->query( "DROP INDEX jamendo_tracks_album_id ON jamendo_tracks;"); 0122 result = db->query( "DROP INDEX jamendo_albums_id ON jamendo_albums;"); 0123 result = db->query( "DROP INDEX jamendo_albums_name ON jamendo_albums;"); 0124 result = db->query( "DROP INDEX jamendo_albums_artist_id ON jamendo_albums;"); 0125 result = db->query( "DROP INDEX jamendo_artists_id ON jamendo_artists;"); 0126 result = db->query( "DROP INDEX jamendo_artists_name ON jamendo_artists;"); 0127 result = db->query( "DROP INDEX jamendo_genre_id ON jamendo_genre;"); 0128 result = db->query( "DROP INDEX jamendo_genre_album_id ON jamendo_genre;"); 0129 result = db->query( "DROP INDEX jamendo_genre_name ON jamendo_genre;"); 0130 0131 0132 result = db->query( "DROP TABLE IF EXISTS jamendo_tracks;" ); 0133 result = db->query( "DROP TABLE IF EXISTS jamendo_albums;" ); 0134 result = db->query( "DROP TABLE IF EXISTS jamendo_artists;" ); 0135 result = db->query( "DROP TABLE IF EXISTS jamendo_genre;" ); 0136 0137 //FIXME: We only support sqlite currently. DbConnection no longer exists. 0138 } 0139 0140 int 0141 JamendoDatabaseHandler::insertTrack( ServiceTrack *track ) 0142 { 0143 JamendoTrack * jTrack = static_cast<JamendoTrack *> ( track ); 0144 QString numberString; 0145 0146 auto db = StorageManager::instance()->sqlStorage(); 0147 QString queryString = "INSERT INTO jamendo_tracks ( id, name, track_number, length, " 0148 "album_id, artist_id, preview_url ) VALUES ( " 0149 + QString::number( jTrack->id() ) + ", '" 0150 + db->escape( jTrack->name() ) + "', " 0151 + QString::number( jTrack->trackNumber() ) + ", " 0152 + QString::number( jTrack->length() ) + ", " 0153 + QString::number( jTrack->albumId() ) + ", " 0154 + QString::number( jTrack->artistId() ) + ", '" 0155 + db->escape( jTrack->uidUrl() ) + "' );"; 0156 0157 // debug() << "Adding Jamendo track " << queryString; 0158 int trackId = db->insert( queryString, NULL ); 0159 0160 // Process moods: 0161 0162 /* QStringList moods = track->getMoods(); 0163 0164 foreach( QString mood, moods ) { 0165 queryString = "INSERT INTO jamendo_moods ( track_id, mood ) VALUES ( " 0166 + QString::number( trackId ) + ", '" 0167 + db->escape( mood ) + "' );"; 0168 0169 0170 //debug() << "Adding Jamendo mood: " << queryString; 0171 db->insert( queryString, NULL ); 0172 } 0173 */ 0174 return trackId; 0175 } 0176 0177 int 0178 JamendoDatabaseHandler::insertAlbum( ServiceAlbum *album ) 0179 { 0180 JamendoAlbum * jAlbum = static_cast<JamendoAlbum *> ( album ); 0181 0182 QString queryString, popularity; 0183 auto sqlDb = StorageManager::instance()->sqlStorage(); 0184 0185 popularity = QString::number( jAlbum->popularity() ); 0186 if( popularity == "nan" ) // sometimes this seems to happen, I don't know why 0187 popularity = '0'; 0188 0189 queryString = "INSERT INTO jamendo_albums ( id, name, description, " 0190 "popularity, cover_url, launch_year, genre, " 0191 "artist_id, mp3_torrent_url, ogg_torrent_url ) VALUES ( " 0192 + QString::number( jAlbum->id() ) + ", '" 0193 + sqlDb->escape( jAlbum->name() ) + "', '" 0194 + sqlDb->escape( jAlbum->description() )+ "', " 0195 + popularity + ", '" 0196 + sqlDb->escape( jAlbum->coverUrl() )+ "', " 0197 + QString::number( jAlbum->launchYear() ) + ", '" 0198 + sqlDb->escape( jAlbum->genre() )+ "', " 0199 + QString::number( jAlbum->artistId() ) + ", '" 0200 + sqlDb->escape( QString() ) + "', '" // Deprecated 0201 + sqlDb->escape( QString() ) + "' );"; // Deprecated 0202 0203 //debug() << "Adding Jamendo album " << queryString; 0204 0205 return sqlDb->insert( queryString, QString() ); 0206 } 0207 0208 0209 int 0210 JamendoDatabaseHandler::insertArtist( ServiceArtist *artist ) 0211 { 0212 JamendoArtist * jArtist = static_cast<JamendoArtist *> ( artist ); 0213 QString queryString; 0214 auto sqlDb = StorageManager::instance()->sqlStorage(); 0215 queryString = "INSERT INTO jamendo_artists ( id, name, description, " 0216 "country, photo_url, jamendo_url, home_url " 0217 ") VALUES ( " 0218 + QString::number( jArtist->id() ) + ", '" 0219 + sqlDb->escape( jArtist->name() ) + "', '" 0220 + sqlDb->escape( jArtist->description() ) + "', '" 0221 + sqlDb->escape( jArtist->country() ) + "', '" 0222 + sqlDb->escape( jArtist->photoURL() ) + "', '" 0223 + sqlDb->escape( jArtist->jamendoURL() ) + "', '" 0224 + sqlDb->escape( jArtist->homeURL() ) + "' );"; 0225 0226 //debug() << "Adding Jamendo artist " << queryString; 0227 0228 return sqlDb->insert( queryString, QString() ); 0229 /* 0230 QString m_country; 0231 QString m_photoURL; 0232 QString m_jamendoURL; 0233 QString m_homeURL;*/ 0234 } 0235 0236 int JamendoDatabaseHandler::insertGenre(ServiceGenre * genre) 0237 { 0238 QString queryString; 0239 auto sqlDb = StorageManager::instance()->sqlStorage(); 0240 queryString = "INSERT INTO jamendo_genre ( album_id, name " 0241 ") VALUES ( " 0242 + QString::number ( genre->albumId() ) + ", '" 0243 + sqlDb->escape( genre->name() ) + "' );"; 0244 0245 //debug() << "Adding Jamendo genre " << queryString; 0246 0247 return sqlDb->insert( queryString, 0 ); 0248 } 0249 0250 void 0251 JamendoDatabaseHandler::begin( ) 0252 { 0253 StorageManager *mgr = StorageManager::instance(); 0254 QString queryString = "BEGIN;"; 0255 mgr->sqlStorage()->query( queryString ); 0256 } 0257 0258 void 0259 JamendoDatabaseHandler::commit( ) 0260 { 0261 StorageManager *mgr = StorageManager::instance(); 0262 QString queryString = "COMMIT;"; 0263 mgr->sqlStorage()->query( queryString ); 0264 } 0265 0266 void 0267 JamendoDatabaseHandler::trimGenres( int minCount ) 0268 { 0269 QString query = QString("delete from jamendo_genre where name IN ( SELECT name from jamendo_genre GROUP BY jamendo_genre.name HAVING COUNT ( jamendo_genre.name ) < %1 );").arg( minCount ); 0270 0271 auto sqlDb = StorageManager::instance()->sqlStorage(); 0272 sqlDb->query( query ); 0273 0274 //also trim genre names that have only 1 or 2 chars 0275 query = QString ("delete from jamendo_genre where name REGEXP '^.{1,2}$';" ); 0276 sqlDb->query( query ); 0277 0278 } 0279