comparison core/magnatagatune/makro_insert_mtt_allmusic_genres.m @ 0:e9a9cd732c1e tip

first hg version after svn

+function [amginfo, notFound]= makro_insert_mtt_allmusic_genres()
+%
+% gets isrc number of (MSD) tracks and then retrieves genre from allmusic
+
+
+% db connection
+ db = adodb_connect('Driver={MySQL ODBC 5.1 Driver}; Server=chivm.soi.city.ac.uk; Database=camir_ooo_0.1; UID=daniel; PWD=11a11a;',2000);
+% db = adodb_connect('Driver={MySQL ODBC 5.2w Driver}; Server=chivm.soi.city.ac.uk; Database=camir_ooo_0.1; UID=daniel; PWD=11a11a;',2000);
+
+% ---
+% 1. get all / a song from song table
+% NOTE: we start ftom the last annotated song
+% ---
+
+% sql = ['SELECT max(songId) FROM linksonggenre_amg'];
+% [~, id] = adodb_query(db, sql); 
+% id = id{1}
+
+% select only mtt songs
+sql = sprintf('SELECT * FROM songlibrary where id < 1020');
+[a] = adodb_query(db, sql);
+
+sevenapikey = '7drb8p23ggb7';
+roviapikey = 'mwtpx4bjc6wkz4j6u5t2ktr8';
+roviapisecret = 'UMfYTxg6qT';
+
+for i=1:numel(a.id)
+% ---
+% 3. Where isrc code exists: get all genre tags from allmusic/robe
+%    Optional: get style tags ?
+% ---
+     clip = CASIMIRClip(a.id{i});
+     if isempty(clip.child_clip.isrc())
+         continue;
+     end
+     
+     % ---
+     % we update the isrc's and mbids along the way
+     % ---
+     sql = ['UPDATE songlibrary SET isrcId='  sprintf('"%s"',clip.child_clip.isrc()) ', artistMBid=' sprintf('"%s"',clip.child_clip.artist_mbid())  ' WHERE (id =' sprintf('%i',a.id{i}) ')'];
+     [~, id] = adodb_query(db, sql); 
+     
+     % ---
+     % have we had this song before? then lets avoid duplicates
+     % ---
+     sql = ['SELECT songId FROM linksonggenre_amg WHERE (songId =' sprintf('%i',a.id{i}) ')'];
+     [~, id] = adodb_query(db, sql); 
+     if ~isempty(id)
+         continue;
+     end
+    % ---
+    % NOTE: this key is valid for 5 minutes only.
+    %       the sharedsecret is never given to the API :)
+    % ---
+    sig = amg_rovi_signature(roviapikey, roviapisecret);
+    try
+        % song info API
+        url = sprintf('http://api.rovicorp.com/data/v1/song/info?apikey=%s&sig=%s&isrcid=%s',roviapikey,sig,clip.child_clip.isrc());
+        [tmp, status] = curl(url);
+        
+        % after curl we can directly decode
+        tmpstr = json_decode(tmp);
+        
+        if strcmp(tmpstr.status,'error')
+            notFound(i) = tmpstr;
+            cprint(1,'%i of %i - %s',i,numel(a.id),notFound(i).messages.text);
+            pause(rand(2));
+            continue;
+        end
+        amginfo(i) = tmpstr;
+
+    %   .song.genres      id: 'MA0000002692'
+    %                   name: 'Latin'
+    %                 weight: 10
+
+        % ---
+        % 4. for each genre: 
+        %    - save into genre tagstable
+        %    - associate with song id in linksonggenre
+        % ---
+        for j = 1:numel(amginfo(i).song.genres)
+
+            % first insert the genre
+            genreId = insertGenre(db,  amginfo(i).song.genres(j).name,  amginfo(i).song.genres(j).id);
+
+            sql = 'REPLACE INTO linksonggenre_amg (songId, genreId) VALUES (%i, %i)';
+            sql = sprintf(sql, a.id{i}, genreId);
+            [res] = adodb_query(db, sql);
+        end
+        cprint(1,'%i of %i - saved %i genres',i,numel(a.id),numel( amginfo(i).song.genres));
+        pause(rand(10));
+    catch
+        cprint(1,'%i of %i - error response: %s',i,numel(a.id), tmp);
+        pause(rand(10));
+    end
+
+end
+end
+
+function id = insertGenre(db, name, locId)
+
+    % ---
+    % check if genre also exists,
+    % return id if so
+    % ---
+    sql = ['SELECT id FROM `genre_amg` WHERE (musicDbLocId ="' locId '")'];
+    [~, id] = adodb_query(db, sql);
+    
+    if ~isempty(id)
+        id = id{1};
+        return;
+    else
+        % insert genre into table
+        sql = [ 'INSERT INTO genre_amg (name,musicDbLocId) ' ...
+            'VALUES ("' name '","' locId '")'];
+        [res] = adodb_query(db, sql);
+        
+        sql = 'SELECT max(id) FROM genre_amg';
+        [~, id] = adodb_query(db, sql); 
+        id = id{1};
+        cprint(1,'inserted genre %i:%s',name)
+    end
+end
+
+