[broconne]

Does anyone know the optimal method for retrieving album review text for the currently playing song?

I could not find a programmatic way to review text in either infolabels or the xbmc python library.

HTTP-APIs QueryMusicDatabase seemed to have the most potential, however I have run into two problems.

1) There seems to be no method to match up the currently playing song with a songID. If the song is being played in library mode the sondID seems to be available as part of the file location, the last part before mp3 in the api command GetCurrentlyPlaying , however it is not available if something is being played off a share - even if that song has been placed in the library and review text has been obtained. So, anyone know of a method to get the song ID?

2) Without a songID I ended up creating a query with all known information about the currently playing song. They query works great from a browser but fails when called from xbmc.executehttpapi. I read in xbmc AJAX interface skin thread that excutehttpapi with QueryMusicDatabase seems to fail if the query has a comma? Does anyone know if that is accurate or is something more complicated going on? It seems to be the case because my query starts off with "select strReview from albuminfo,..." and returns every album review in the database. Do I need to escape the commas?
Here is the code I am using:

Code:

from spyceModule import spyceModule
import xbmc
import urllib

class albumreview(spyceModule):
    
    def getReview(self, musicinfo):
        sqlStatement = 'select strReview from albuminfo,album,artist,song,genre where album.idAlbum = albuminfo.idAlbum and album.idArtist = artist.idArtist and song.idArtist = artist.idArtist and song.idAlbum = album.idAlbum and album.idGenre = genre.idGenre '
        sqlStatement += 'and album.strAlbum = \'' + self.escape(musicinfo.getAlbum()) + '\' '
        sqlStatement += 'and artist.strArtist = \'' + self.escape(musicinfo.getArtist()) + '\' '
        sqlStatement += 'and song.strTitle = \'' + self.escape(musicinfo.getTitle()) + '\' '
        sqlStatement += 'and genre.strGenre = \'' + self.escape(musicinfo.getGenre()) + '\' '
        sqlStatement += 'NOT NULL LIMIT 1'
#        print urllib.quote(sqlStatement)
        return xbmc.executehttpapi("QueryMusicDatabase(" + sqlStatement + ")" )
        
        
    def escape(self,string):
        return string.replace('\'','\'\'')

Now if I print the quoted string, put it in a browser as the argument to http://xbmc/xbmcCmds/xbmcHttp?command=Qu...icDatabase it works correctly.
Is this a bug or is there something I misunderstand?

Thanks!

[jmarshall]

Check out the functions in MusicDatabase.cpp which has the queries for returning the song id from the path. Using the same queries would allow you to get the actual album id which means a trivial lookup from there. At least then it'll be consistent and you won't be relying on tag data not matching allmusic.com data.

As for why it doesn't work from executehttpapi, it may be due to charset encoding or something - you'll have to do some debugging work on XBMC to determine what is going on.

A debug log may tell you what if anything is being called from the executehttpapi call?

Cheers,
Jonathan

[broconne]

jmarhsall,
Thanks for the tip. I think that has me pointed in the right direction. From what I can tell after a little digging if the filename starts with musicdb:// it will not match strPath in the songview. Is that correct? Is the best method to take the last numbers before .mp3 as the songid (which it seems to be) and match on that? Or is there a better method?

[jmarshall]

Best to use the songid, yes.

[broconne]

Thanks JMarshall - I appreciate the assistance

I am including a working albumreview spyce module in this message in case it is helpful to other people. This module will get the review text for the currently playing song if it exists.

Code:

from spyceModule import spyceModule
import xbmc
import urllib
import re
import os.path

class albumreview(spyceModule):
    
    def getReview(self, musicinfo):
        
        filepath = xbmc.Player().getPlayingFile()
        
        if self.__isMusicDB(filepath):
            sqlStatement = self.__buildSongIDQuery(filepath)
        else:
            sqlStatement = self.__buildFilePathQuery(filepath)
            
        apiCommand =  'QueryMusicDatabase(' + sqlStatement + ')'
    
        queryResult = xbmc.executehttpapi(apiCommand)
        
        reviewText = queryResult.replace("<li>","")
    
        return reviewText
        

        
    def __isMusicDB(self,filepath):
        return re.match('musicdb://',filepath)
    
        
    def __buildSongIDQuery(self,filepath):
        songID = self.__getSongID(filepath)
        sqlStatement = 'select strReview from albuminfo where idAlbum = (select idAlbum from song where song.idSong = '
        sqlStatement += songID
        sqlStatement += ')'
        return sqlStatement
    
    
    def __buildFilePathQuery(self,filepath):
        path,filename = self.__splitFilePath(filepath)
        sqlStatement = 'select strReview from albuminfo where idAlbum = (select idAlbum from songview where songview.strPath = \''
        sqlStatement += self.__escape(path)
        sqlStatement += '\' and songview.strFileName = \''
        sqlStatement += self.__escape(filename)
        sqlStatement += '\')'
        return sqlStatement
        
        
    def __escape(self,string):
        return string.replace('\'','\'\'')
    
    
    def __splitFilePath(self,filepath):
        path,file = os.path.split(filepath)
        path += '/'
        return path,file
    
    
    def __getSongID(self,filepath):
        path,file = os.path.split(filepath)
        songID,extension = file.split('.')
        return so

ngID

[broconne]

Part of the code got cut off sorry.

Code:

from spyceModule import spyceModule
import xbmc
import urllib
import re
import os.path

class albumreview(spyceModule):
    
    def getReview(self, musicinfo):
        
        filepath = xbmc.Player().getPlayingFile()
        
        if self.__isMusicDB(filepath):
            sqlStatement = self.__buildSongIDQuery(filepath)
        else:
            sqlStatement = self.__buildFilePathQuery(filepath)
            
        apiCommand =  'QueryMusicDatabase(' + sqlStatement + ')'
    
        queryResult = xbmc.executehttpapi(apiCommand)
        
        reviewText = queryResult.replace("<li>","")
    
        return reviewText
        

        
    def __isMusicDB(self,filepath):
        return re.match('musicdb://',filepath)
    
        
    def __buildSongIDQuery(self,filepath):
        songID = self.__getSongID(filepath)
        sqlStatement = 'select strReview from albuminfo where idAlbum = (select idAlbum from song where song.idSong = '
        sqlStatement += songID
        sqlStatement += ')'
        return sqlStatement
    
    
    def __buildFilePathQuery(self,filepath):
        path,filename = self.__splitFilePath(filepath)
        sqlStatement = 'select strReview from albuminfo where idAlbum = (select idAlbum from songview where songview.strPath = \''
        sqlStatement += self.__escape(path)
        sqlStatement += '\' and songview.strFileName = \''
        sqlStatement += self.__escape(filename)
        sqlStatement += '\')'
        return sqlStatement
        
        
    def __escape(self,string):
        return string.replace('\'','\'\'')
    
    
    def __splitFilePath(self,filepath):
        path,file = os.path.split(filepath)
        path += '/'
        return path,file
    
    
    def __getSongID(self,filepath):
        path,file = os.path.split(filepath)
        songID,extension = file.split('.')
        return songID

[jmarshall]

Nice work