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
|