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

first hg version after svn

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