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