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