Mercurial > hg > camir-aes2014

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

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
first hg version after svn
author wolffd
date Tue, 10 Feb 2015 15:05:51 +0000
parents
children
rev   line source
wolffd@0 1 function [amginfo, notFound]= makro_insert_mtt_allmusic_genres()
wolffd@0 2 %
wolffd@0 3 % gets isrc number of (MSD) tracks and then retrieves genre from allmusic
wolffd@0 4
wolffd@0 5
wolffd@0 6 % db connection
wolffd@0 7 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 8 % 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 9
wolffd@0 10 % ---
wolffd@0 11 % 1. get all / a song from song table
wolffd@0 12 % NOTE: we start ftom the last annotated song
wolffd@0 13 % ---
wolffd@0 14
wolffd@0 15 % sql = ['SELECT max(songId) FROM linksonggenre_amg'];
wolffd@0 16 % [~, id] = adodb_query(db, sql);
wolffd@0 17 % id = id{1}
wolffd@0 18
wolffd@0 19 % select only mtt songs
wolffd@0 20 sql = sprintf('SELECT * FROM songlibrary where id < 1020');
wolffd@0 21 [a] = adodb_query(db, sql);
wolffd@0 22
wolffd@0 23 sevenapikey = '7drb8p23ggb7';
wolffd@0 24 roviapikey = 'mwtpx4bjc6wkz4j6u5t2ktr8';
wolffd@0 25 roviapisecret = 'UMfYTxg6qT';
wolffd@0 26
wolffd@0 27 for i=1:numel(a.id)
wolffd@0 28 % ---
wolffd@0 29 % 3. Where isrc code exists: get all genre tags from allmusic/robe
wolffd@0 30 % Optional: get style tags ?
wolffd@0 31 % ---
wolffd@0 32 clip = CASIMIRClip(a.id{i});
wolffd@0 33 if isempty(clip.child_clip.isrc())
wolffd@0 34 continue;
wolffd@0 35 end
wolffd@0 36
wolffd@0 37 % ---
wolffd@0 38 % we update the isrc's and mbids along the way
wolffd@0 39 % ---
wolffd@0 40 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 41 [~, id] = adodb_query(db, sql);
wolffd@0 42
wolffd@0 43 % ---
wolffd@0 44 % have we had this song before? then lets avoid duplicates
wolffd@0 45 % ---
wolffd@0 46 sql = ['SELECT songId FROM linksonggenre_amg WHERE (songId =' sprintf('%i',a.id{i}) ')'];
wolffd@0 47 [~, id] = adodb_query(db, sql);
wolffd@0 48 if ~isempty(id)
wolffd@0 49 continue;
wolffd@0 50 end
wolffd@0 51 % ---
wolffd@0 52 % NOTE: this key is valid for 5 minutes only.
wolffd@0 53 % the sharedsecret is never given to the API :)
wolffd@0 54 % ---
wolffd@0 55 sig = amg_rovi_signature(roviapikey, roviapisecret);
wolffd@0 56 try
wolffd@0 57 % song info API
wolffd@0 58 url = sprintf('http://api.rovicorp.com/data/v1/song/info?apikey=%s&sig=%s&isrcid=%s',roviapikey,sig,clip.child_clip.isrc());
wolffd@0 59 [tmp, status] = curl(url);
wolffd@0 60
wolffd@0 61 % after curl we can directly decode
wolffd@0 62 tmpstr = json_decode(tmp);
wolffd@0 63
wolffd@0 64 if strcmp(tmpstr.status,'error')
wolffd@0 65 notFound(i) = tmpstr;
wolffd@0 66 cprint(1,'%i of %i - %s',i,numel(a.id),notFound(i).messages.text);
wolffd@0 67 pause(rand(2));
wolffd@0 68 continue;
wolffd@0 69 end
wolffd@0 70 amginfo(i) = tmpstr;
wolffd@0 71
wolffd@0 72 % .song.genres id: 'MA0000002692'
wolffd@0 73 % name: 'Latin'
wolffd@0 74 % weight: 10
wolffd@0 75
wolffd@0 76 % ---
wolffd@0 77 % 4. for each genre:
wolffd@0 78 % - save into genre tagstable
wolffd@0 79 % - associate with song id in linksonggenre
wolffd@0 80 % ---
wolffd@0 81 for j = 1:numel(amginfo(i).song.genres)
wolffd@0 82
wolffd@0 83 % first insert the genre
wolffd@0 84 genreId = insertGenre(db, amginfo(i).song.genres(j).name, amginfo(i).song.genres(j).id);
wolffd@0 85
wolffd@0 86 sql = 'REPLACE INTO linksonggenre_amg (songId, genreId) VALUES (%i, %i)';
wolffd@0 87 sql = sprintf(sql, a.id{i}, genreId);
wolffd@0 88 [res] = adodb_query(db, sql);
wolffd@0 89 end
wolffd@0 90 cprint(1,'%i of %i - saved %i genres',i,numel(a.id),numel( amginfo(i).song.genres));
wolffd@0 91 pause(rand(10));
wolffd@0 92 catch
wolffd@0 93 cprint(1,'%i of %i - error response: %s',i,numel(a.id), tmp);
wolffd@0 94 pause(rand(10));
wolffd@0 95 end
wolffd@0 96
wolffd@0 97 end
wolffd@0 98 end
wolffd@0 99
wolffd@0 100 function id = insertGenre(db, name, locId)
wolffd@0 101
wolffd@0 102 % ---
wolffd@0 103 % check if genre also exists,
wolffd@0 104 % return id if so
wolffd@0 105 % ---
wolffd@0 106 sql = ['SELECT id FROM `genre_amg` WHERE (musicDbLocId ="' locId '")'];
wolffd@0 107 [~, id] = adodb_query(db, sql);
wolffd@0 108
wolffd@0 109 if ~isempty(id)
wolffd@0 110 id = id{1};
wolffd@0 111 return;
wolffd@0 112 else
wolffd@0 113 % insert genre into table
wolffd@0 114 sql = [ 'INSERT INTO genre_amg (name,musicDbLocId) ' ...
wolffd@0 115 'VALUES ("' name '","' locId '")'];
wolffd@0 116 [res] = adodb_query(db, sql);
wolffd@0 117
wolffd@0 118 sql = 'SELECT max(id) FROM genre_amg';
wolffd@0 119 [~, id] = adodb_query(db, sql);
wolffd@0 120 id = id{1};
wolffd@0 121 cprint(1,'inserted genre %i:%s',name)
wolffd@0 122 end
wolffd@0 123 end
wolffd@0 124
wolffd@0 125