Song Ratings not exporting from Library

  Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
buddiemac Offline
Junior Member
Posts: 46
Joined: Jul 2010
Reputation: 0
Location: Northeast US
Post: #1
I add song ratings within XBMC to each song (NOT from meta mp3 tags) in the music library.

I assumed they are saved within the music.db data base. When I export the music data base I do not find any song rating fields. I find album rating fields (loaded from scrapers).

I would like to export my library with song ratings - how can this be done?
find quote
hirschma Offline
Junior Member
Posts: 4
Joined: Jan 2009
Reputation: 0
Post: #2
The song ratings are in the Music.db file. This really, really should be part of the exported data.

I had the same issue, plus I was using FLAC files, which have some issues regarding song ratings within a tag as far as XBMC is concerned. My solution was to write a script to extract that ratings info from the music.db file, and put them into a FLAC tag that XBMC will understand without issue. This might be useful for your purposes.

Note that my scripting abilities are not the best, but this might get you started to your own solution:

Code:
#!/bin/bash

DBDIR=/path/to/db/file
DBFILE=MyMusic7.db
MAINPATH=/needed this to address personal weirdnes
FLACDIR=/path/to/flacs
WORKDIR=/tmp/audio/
DELPATH="smb://SERVER/sharedir/"

#test for required software

command -v sqlite3 >/dev/null 2>&1 || { echo >&2 "I require sqlite3 but it's not installed.  Aborting."; exit 1; }
command -v metaflac >/dev/null 2>&1 || { echo >&2 "I require metaflac but it's not installed.  Aborting."; exit 1; }

#test for flac directory
if [ ! -d $FLACDIR ]
        then
                echo "I can't find flac files! exiting."
                exit 1
fi

#test for Work directory

if [ ! -d $WORKDIR ]
        then
                echo "$WORKDIR does not exist, creating."
                mkdir -p $WORKDIR
        else
                echo "Using existing $WORKDIR ..."
fi
# main loop start

sqlite3 $DBDIR$DBFILE "select rating,strPath,strFileName from song natural left outer join path where rating <> 0" | while read RECORD; do
echo "-------------------------------"
RATING=$(echo $RECORD | cut -d \| -f 1 )
RAWPATH=$(echo $RECORD | cut -d \| -f 2 )
SONGFILE=$(echo $RECORD | cut -d \| -f 3 )
SONGPATH=${RAWPATH#$DELPATH}
FULLPATH="$MAINPATH""$SONGPATH""$SONGFILE"
        if [ ! -f "$FULLPATH" ]
                then
                        echo "I couldn't find song files! Something is wrong!" $FULLPATH
        fi
RAWEXRATING=`metaflac --show-tag=RATING "$FULLPATH"`
EXRATING=$(echo $RAWEXRATING | cut -d \= -f 2 )
        if [ -z "$EXRATING" ]
                then
                        EXRATING=0
        fi
        if [ "$EXRATING" -eq "$RATING" ]
                then
                        echo "No change to song rating for "$FULLPATH
                else
                        echo "Backing up $FULLPATH"
                        echo "to $FULLPATH.bak"
                        cp "$FULLPATH" "$FULLPATH.bak"
                        echo "Clearing out old song rating metadata..."
                        metaflac --remove-tag="RATING" "$FULLPATH"
                        echo "Current rating was "$EXRATING
                        echo "Rating now set to "$RATING
                        metaflac --set-tag="RATING=$RATING" "$FULLPATH"
        fi
done

exit

I'm sure someone with more talent than me can make this pretty, and adapt it for import/export of ratings data.
find quote
wilson.joe Offline
Fan
Posts: 448
Joined: Nov 2010
Reputation: 6
Location: Salem, Or
Post: #3
thanks for sharing... Smile

In a world without walls and fences who needs windows and gates, open source, opens minds, so open yours today.

[Image: widget]
find quote
buddiemac Offline
Junior Member
Posts: 46
Joined: Jul 2010
Reputation: 0
Location: Northeast US
Post: #4
hirschma,

A belated thanks. I will give this a try when I have time.
find quote
sea3pea0 Offline
Junior Member
Posts: 49
Joined: Oct 2013
Reputation: 0
Post: #5
This script by hirschma is exactly what I need, but for a windows environment. Anybody with any coding skills feel inclined to translate this into something for windows? Has anyone had any issues with it? Anyway thanks for the contribution, perhaps this will get people with windows closer to a solution to getting their song ratings from the db to the files themselves.
find quote
Sebastian Offline
Junior Member
Posts: 28
Joined: May 2008
Reputation: 0
Post: #6
Hi have had the same problem, It will be great if XBMC (now Kodi) Team can implement a solution for that I have a lot of things in mind on how to do it

x) write stars in file tags, (can be turned on-off by configuration, and format can be choosed in configuration)
x) when exporting libraries writing a .nfo for each album with this knd of information
x) have a cloud server to store all libraries data as backup and be able to retrieve it...

in the mean while, this issue cn be solved with a sqlite script that can copy the rating value (last played, playcount, etc) from the old database to the new one... those are the steps to perform it:

1) download sqlitestudie, very helpfull for working with your database
2) copy the old MusicXX.db to a place where it can be found
3) scan the music lib in the new xbmc, and import .nfo files
4) locate the new MusicXX.db in your computer
5) open the sqlitestudio and creates both connections to both databases, for the old database name the connection OldDatabase
6) connect both conection and open a sql editor based on the connection of the new database

and run this query:


Code:
replace into song
(
  idSong, idAlbum, idPath, strArtists, strGenres, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, iTimesPlayed,
  iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment
)
select
  s.idSong, s.idAlbum, s.idPath, s.strArtists, s.strGenres, s.strTitle, s.iTrack, s.iDuration, s.iYear, s.dwFileNameCRC, s.strFileName, s.strMusicBrainzTrackID, s2.iTimesPlayed,
  s.iStartOffset, s.iEndOffset, s.idThumb, s2.lastplayed, s2.rating, s.comment
  
from song s
left join path on path.idPath = s.idPath
  left join OldDatabase.path p2 on p2.strPath = path.strPath
left join OldDatabase.song s2 on p2.idPath = s2.idPath and s2.strFileName = s.strFileName
where s2.strFileName is not null
find quote