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