Mercurial > hg > camir-aes2014
view core/magnatagatune/makro_insert_mtt_allmusic_genres.m @ 0:e9a9cd732c1e tip
first hg version after svn
author | wolffd |
---|---|
date | Tue, 10 Feb 2015 15:05:51 +0000 |
parents | |
children |
line wrap: on
line source
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