1 /****************************************************************************************
2 * Copyright (c) 2006,2007 Nikolaj Hald Nielsen <nhn@kde.org> *
3 * *
4 * This program is free software; you can redistribute it and/or modify it under *
5 * the terms of the GNU General Public License as published by the Free Software *
6 * Foundation; either version 2 of the License, or (at your option) any later *
7 * version. *
8 * *
9 * This program is distributed in the hope that it will be useful, but WITHOUT ANY *
10 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A *
11 * PARTICULAR PURPOSE. See the GNU General Public License for more details. *
12 * *
13 * You should have received a copy of the GNU General Public License along with *
14 * this program. If not, see <http://www.gnu.org/licenses/>. *
15 ****************************************************************************************/
16
17 #include "MagnatuneDatabaseHandler.h"
18
19 #include <core-impl/storage/StorageManager.h>
20 #include <core/support/Debug.h>
21 #include <core/storage/SqlStorage.h>
22
23 using namespace Meta;
24
MagnatuneDatabaseHandler()25 MagnatuneDatabaseHandler::MagnatuneDatabaseHandler()
26 {}
27
28
~MagnatuneDatabaseHandler()29 MagnatuneDatabaseHandler::~MagnatuneDatabaseHandler()
30 {}
31
32 void
createDatabase()33 MagnatuneDatabaseHandler::createDatabase( )
34 {
35 //Get database instance
36 auto sqlDb = StorageManager::instance()->sqlStorage();
37
38 QString autoIncrement = "AUTO_INCREMENT";
39
40 // create table containing tracks
41 QString queryString = "CREATE TABLE magnatune_tracks ("
42 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') +
43 "name " + sqlDb->textColumnType() + QLatin1Char(',') +
44 "track_number INTEGER,"
45 "length INTEGER,"
46 "album_id INTEGER,"
47 "artist_id INTEGER,"
48 "preview_lofi " + sqlDb->exactTextColumnType() + QLatin1Char(',') +
49 "preview_ogg " + sqlDb->exactTextColumnType() + QLatin1Char(',') +
50 "preview_url " + sqlDb->exactTextColumnType() + ") ENGINE = MyISAM;";
51
52 debug() << "Creating magnatune_tracks: " << queryString;
53
54
55 QStringList result = sqlDb->query( queryString );
56
57 sqlDb->query( "CREATE INDEX magnatune_tracks_album_id ON magnatune_tracks(album_id);" );
58 sqlDb->query( "CREATE INDEX magnatune_tracks_artist_id ON magnatune_tracks(artist_id);" );
59
60 //Create album table
61 queryString = "CREATE TABLE magnatune_albums ("
62 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') +
63 "name " + sqlDb->textColumnType() + QLatin1Char(',') +
64 "year INTEGER,"
65 "artist_id INTEGER,"
66 "album_code " + sqlDb->textColumnType() + QLatin1Char(',') +
67 "cover_url " + sqlDb->exactTextColumnType() + QLatin1Char(',') +
68 "description " + sqlDb->exactTextColumnType() + ") ENGINE = MyISAM;";
69
70 debug() << "Creating Magnatune_albums: " << queryString;
71
72 result = sqlDb->query( queryString );
73
74 sqlDb->query( "CREATE INDEX magnatune_albums_name ON magnatune_albums(name);" );
75 sqlDb->query( "CREATE INDEX magnatune_albums_artist_id ON magnatune_albums(artist_id);" );
76
77
78 //Create artist table
79 queryString = "CREATE TABLE magnatune_artists ("
80 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') +
81 "name " + sqlDb->textColumnType() + QLatin1Char(',') +
82 "artist_page " + sqlDb->exactTextColumnType() + QLatin1Char(',') +
83 "description " + sqlDb->textColumnType() + QLatin1Char(',') +
84 "photo_url " + sqlDb->exactTextColumnType() + ") ENGINE = MyISAM;";
85
86 debug() << "Creating magnatune_artist: " << queryString;
87
88 result = sqlDb->query( queryString );
89
90 sqlDb->query( "CREATE INDEX magnatune_artists_name ON magnatune_artists(name);" );
91
92 //create genre table
93 queryString = "CREATE TABLE magnatune_genre ("
94 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') +
95 "name " + sqlDb->textColumnType() + QLatin1Char(',') +
96 "album_id INTEGER" + ") ENGINE = MyISAM;";
97
98 result = sqlDb->query( queryString );
99
100 sqlDb->query( "CREATE INDEX magnatune_genre_name ON magnatune_genre(name);" );
101 sqlDb->query( "CREATE INDEX magnatune_genre_album_id ON magnatune_genre(album_id);" );
102
103
104 //create moods table
105 queryString = "CREATE TABLE magnatune_moods ("
106 "id INTEGER PRIMARY KEY " + autoIncrement + QLatin1Char(',') +
107 "track_id INTEGER," +
108 "mood " + sqlDb->textColumnType() + ") ENGINE = MyISAM;";
109
110 debug() << "Creating magnatune_moods: " << queryString;
111
112 result = sqlDb->query( queryString );
113
114
115
116 }
117
118 void
destroyDatabase()119 MagnatuneDatabaseHandler::destroyDatabase( )
120 {
121 auto sqlDb = StorageManager::instance()->sqlStorage();
122 QStringList result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_tracks;" );
123 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_albums;" );
124 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_artists;" );
125 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_genre;" );
126 result = sqlDb->query( "DROP TABLE IF EXISTS magnatune_moods;" );
127
128
129 /* that would only work for db2/oracle. Other databases connect the index to the table (which we just dropped)
130 result = sqlDb->query( "DROP INDEX magnatune_tracks_artist_id;");
131 result = sqlDb->query( "DROP INDEX magnatune_tracks_album_id;");
132 result = sqlDb->query( "DROP INDEX magnatune_album_name;");
133 result = sqlDb->query( "DROP INDEX magnatune_album_artist_id;");
134 result = sqlDb->query( "DROP INDEX magnatune_artist_name;");
135 result = sqlDb->query( "DROP INDEX magnatune_genre_album_id;");
136 result = sqlDb->query( "DROP INDEX magnatune_genre_name;");
137 */
138
139 /* if ( sqlDb->type() == DbConnection::postgresql )
140 {
141 sqlDb->query( QString( "DROP SEQUENCE magnatune_track_seq;" ) );
142 sqlDb->query( QString( "DROP SEQUENCE magnatune_album_seq;" ) );
143 sqlDb->query( QString( "DROP SEQUENCE magnatune_artist_seq;" ) );
144 sqlDb->query( QString( "DROP SEQUENCE magnatune_moods_seq;" ) );
145 }*/
146 }
147
148 int
insertTrack(ServiceTrack * track)149 MagnatuneDatabaseHandler::insertTrack( ServiceTrack *track )
150 {
151 MagnatuneTrack * mTrack = static_cast<MagnatuneTrack *> ( track );
152
153 auto sqlDb = StorageManager::instance()->sqlStorage();
154 QString queryString = "INSERT INTO magnatune_tracks ( name, track_number, length, "
155 "album_id, artist_id, preview_lofi, preview_ogg, preview_url ) VALUES ( '"
156 + sqlDb->escape( mTrack->name()) + "', "
157 + QString::number( mTrack->trackNumber() ) + ", "
158 + QString::number( mTrack->length() * 1000 ) + ", "
159 + QString::number( mTrack->albumId() ) + ", "
160 + QString::number( mTrack->artistId() ) + ", '"
161 + sqlDb->escape( mTrack->lofiUrl() ) + "', '"
162 + sqlDb->escape( mTrack->oggUrl() ) + "', '"
163 + sqlDb->escape( mTrack->uidUrl() ) + "' );";
164
165
166 // debug() << "Adding Magnatune track " << queryString;
167 int trackId = sqlDb->insert( queryString, nullptr );
168
169 return trackId;
170
171
172 }
173
174 int
insertAlbum(ServiceAlbum * album)175 MagnatuneDatabaseHandler::insertAlbum( ServiceAlbum *album )
176 {
177
178 MagnatuneAlbum * mAlbum = static_cast<MagnatuneAlbum *> ( album );
179
180 QString queryString;
181 auto sqlDb = StorageManager::instance()->sqlStorage();
182 queryString = "INSERT INTO magnatune_albums ( name, year, artist_id, "
183 "album_code, cover_url, description ) VALUES ( '"
184 + sqlDb->escape( sqlDb->escape( mAlbum->name() ) ) + "', "
185 + QString::number( mAlbum->launchYear() ) + ", "
186 + QString::number( mAlbum->artistId() ) + ", '"
187 + sqlDb->escape( mAlbum->albumCode() ) + "', '"
188 + sqlDb->escape( mAlbum->coverUrl() ) + "', '"
189 + sqlDb->escape( mAlbum->description() )+ "' );";
190
191 //debug() << "Adding Magnatune album " << queryString;
192
193 return sqlDb->insert( queryString, 0 );
194 }
195
196
197
198 int
insertArtist(ServiceArtist * artist)199 MagnatuneDatabaseHandler::insertArtist( ServiceArtist *artist )
200 {
201 MagnatuneArtist * mArtist = static_cast<MagnatuneArtist *> ( artist );
202
203 QString queryString;
204 auto sqlDb = StorageManager::instance()->sqlStorage();
205 queryString = "INSERT INTO magnatune_artists ( name, artist_page, description, "
206 "photo_url ) VALUES ( '"
207 + sqlDb->escape( mArtist->name() ) + "', '"
208 + sqlDb->escape( mArtist->magnatuneUrl().url() ) + "', '"
209 + sqlDb->escape( mArtist->description() ) + "', '"
210 + sqlDb->escape( mArtist->photoUrl().url() ) + "' );";
211
212 //debug() << "Adding Magnatune artist " << queryString;
213
214 return sqlDb->insert( queryString, 0 );
215 }
216
217
218 void
begin()219 MagnatuneDatabaseHandler::begin( )
220 {
221
222 auto sqlDb = StorageManager::instance()->sqlStorage();
223
224 QString queryString = "BEGIN;";
225
226 sqlDb->query( queryString );
227 }
228
229 void
commit()230 MagnatuneDatabaseHandler::commit( )
231 {
232 auto sqlDb = StorageManager::instance()->sqlStorage();
233 QString queryString = "COMMIT;";
234
235 sqlDb->query( queryString );
236 sqlDb->query( "FLUSH TABLES;" );
237 }
238
insertMoods(int trackId,const QStringList & moods)239 void MagnatuneDatabaseHandler::insertMoods(int trackId, const QStringList &moods)
240 {
241
242 QString queryString;
243 auto sqlDb = StorageManager::instance()->sqlStorage();
244
245 foreach( const QString &mood, moods ) {
246 queryString = "INSERT INTO magnatune_moods ( track_id, mood ) VALUES ( "
247 + QString::number( trackId ) + ", '"
248 + sqlDb->escape( mood ) + "' );";
249
250
251 //debug() << "Adding Magnatune mood: " << queryString;
252 sqlDb->insert( queryString, nullptr );
253 }
254 }
255
getArtistIdByExactName(const QString & name)256 int MagnatuneDatabaseHandler::getArtistIdByExactName(const QString & name)
257 {
258
259 auto sqlDb = StorageManager::instance()->sqlStorage();
260
261 QString queryString = "SELECT id from magnatune_artists WHERE name='" + sqlDb->escape( name ) + "';";
262 QStringList result = sqlDb->query( queryString );
263
264 //debug() << "Looking for id of artist " << name << ":";
265
266 if ( result.size() < 1 ) return -1;
267 int artistId = result.first().toInt();
268
269 //debug() << " Found: " << QString::number( artistId ) << ":";
270
271 return artistId;
272
273 }
274
getAlbumIdByAlbumCode(const QString & albumcode)275 int MagnatuneDatabaseHandler::getAlbumIdByAlbumCode(const QString & albumcode)
276 {
277 auto sqlDb = StorageManager::instance()->sqlStorage();
278
279 QString queryString = "SELECT id from magnatune_albums WHERE album_code='" + sqlDb->escape( albumcode ) + "';";
280 QStringList result = sqlDb->query( queryString );
281
282 //debug() << "Looking for id of album " << albumcode << ":";
283
284 if ( result.size() < 1 ) return -1;
285 int albumId = result.first().toInt();
286
287 //debug() << " Found: " << QString::number( albumId ) << ":";
288
289 return albumId;
290 }
291
insertGenre(ServiceGenre * genre)292 int MagnatuneDatabaseHandler::insertGenre(ServiceGenre * genre)
293 {
294 QString queryString;
295 auto sqlDb = StorageManager::instance()->sqlStorage();
296 queryString = "INSERT INTO magnatune_genre ( album_id, name "
297 ") VALUES ( "
298 + QString::number ( genre->albumId() ) + ", '"
299 + sqlDb->escape( genre->name() ) + "' );";
300
301 //debug() << "Adding Jamendo genre " << queryString;
302
303 return sqlDb->insert( queryString, 0 );
304 }
305
306
307
308
309
310
311