[ikiller]

[EDIT]: I have come upon this problem again when moving Kodi to another box, and when moving to a MySQL database. The problem is that Export Music does not export the song ratings, it seems to only export scraped artist and album data. So when setting up a new library importing your exported library will not keep your ratings, what's worse, when scraping a library your ratings within the Kodi Music database may be overwritten with ratings from ID3 tags or online sources. My second post in this thread includes a working script that can copy ratings, last played, and itimes played to a new SQLite based database from and old SQLite Music database. I will be adding a new post that includes code to copy ratings from an SQlite to a MySQL database.
[/EDIT]

Hello all,

I am trying to move from Eden to Gotham, but my music library database seems corrupt in that Gotham is unable to upgrade it (end up with an empty library), and the library export from Eden does not work (tried to do it manually and everything). So I have made a fresh Gotham install with xbmcbuntu, and I added my music to a new fresh library, and I was hoping I could use my old music database to update the ratings field of the new database. I am doing this because my ratings are very important to me and represent years of listening and rating songs. I am able to open the old database, view the contents and use sql commands on it, but xbmc does not seem to be able to work with it.

What I want to do is this:
Match songs via path and filename (song.strFilename, & path.strPath), and update the rating in the new database with the value found in the old database. I had trouble doing this because I don't know sql very well, but I also tried to match on (song.strTitle & song.strFilename) and that didn't work. I am trying to match on two field since I am sure that the filenames aren't unique, and the Titles aren't unique. But filename and path are 100% unique due to the filesystem.

I have tried to do this (using just title and filename) with the following command but it doesn't work (MyMusic18.db is my old database):

Code:

ATTACH "/path/to/MyMusic18.db" as MM18;
UPDATE song
SET
  rating = (
    SELECT rating
    FROM MM18.song
    WHERE
        song.strFileName = MM18.song.strFileName AND
      song.strTitle = MM18.song.strTitle
  )
WHERE EXISTS (
  SELECT *
  FROM song
  WHERE
        song.strFileName = MM18.song.strFileName AND
      song.strTitle = MM18.song.strTitle
);

This didn't work, all of the ratings turned out to be 3 in the new database. I even manually changed one rating, then ran the command code again and ended up with all 3s again. I really don't know what I am doing, but I really like to update the song.rating using a match on filename and path. I know that path would require a join with the path table and the song table but again I am lost on how to do this. Any help would be greatly appreciated.

Thanks!!!


Why am I doing this?:
I have a big investment in my music database ratings. I have a large messy music collection, and I have been using ratings as a method of music organization/curation that I started back when I had a mythtv box. When I transitioned from mythtv to xbmc, I made a script to write the ratings from my mythtv music database to ID3 tags of the songs so that when scanned with xbmc that the ratings would be used. Now I am trying to upgrade form Eden to Gotham, and my music database seems to be corrupt since I cannot successfully export my music library (just doesn't work). I love using the ratings, since I can just play unrated songs (0 or 3 rating) and find rare gems that are in my music collection that I didn't know existed. This works great with the smart playlists. I can just play songs based on how much I like them. This was a lifesaver for my wedding since I could easily locate all of our favorite music to make an awesome mix.

[jmarshall]

I suspect you want to specify MM18.song.rating in your first select, and MM18.song as the table in the second.

See here for example:

http://stackoverflow.com/questions/38457...-user-name

[ikiller]

Unfortunately that didn't work for me. I went ahead and wrote a python script to do what I needed. I also went ahead and populated the lastplayed and iTimesPlayed:

Code:

#! /usr/bin/env python

#
# Script populates the ratings, iTimesPlayed, lastplayed field in a XBMC music
# database (new) with the values from another XBMC music database (old).  This
# was written to save my ratings from an old database that was corrupt and would
# not export when I upgraded from Eden to Gotham.
# assumes same episode title and base filename (NOT fully qualified filename)

import sqlite3

olddb = "MyMusic18.db"
newdb = "MyMusic46.db"

old_con = sqlite3.connect(olddb)
new_con = sqlite3.connect(newdb)
new_con.text_factory = str
old_con.text_factory = str
oldc = old_con.cursor()
newc = new_con.cursor()
newc.execute("""select idSong,strPath,strFileName from song natural left outer join path""")
newdata = newc.fetchall()
for row in newdata:
        print "processing song ", row[0]
        print "file: " + row[1] + row[2]
        oldc.execute('SELECT rating,iTimesPlayed,lastplayed from song natural left outer join path where strPath = ? and strFileName = ?', (row[1], row[2]))
        old=oldc.fetchall()
        if len(old)==0:
                print "No matching file in old database!!"
        else:
                if old[0][2] is None:
                        print "Populating the song rating = " + old[0][0] + " and iTimesPlayed = ",  old[0][1]
                else:
                        print "Populating the song rating = " + old[0][0] + " last played = " + old[0][2] + " and iTimesPlayed = ",  old[0][1]

                newc.execute('UPDATE song SET rating = ? WHERE idSong = ?', (old[0][0], row[0]))
                newc.execute('UPDATE song SET iTimesPlayed = ? WHERE idSong = ?', (old[0][1], row[0]))
                newc.execute('UPDATE song SET lastplayed = ? WHERE idSong = ?', (old[0][2], row[0]))
                print "Success"
                print
oldc.close()
newc.close()

[jmarshall]

Glad you got it working. And thanks for posting the code for others to use

[ikiller]

I have written a new python script that copies song Ratings, last played, and itimes played from an old SQLite kodi Music database to a MySQL kodi music database. This script also includes code that deals with the old database using a different path than the new database.

Code:

#! /usr/bin/env python

#
# Script populates the ratings, iTimesPlayed, lastplayed field in a XBMC music
# database (new) with the values from another XBMC music database (old).  This
# was written to save my ratings from an old database that was corrupt and would
# not export when I upgraded from Eden to Gotham.
# assumes same episode title and base filename (NOT fully qualified filename)

import sqlite3
import MySQLdb
new_con = MySQLdb.connect(host="###",    # your host, usually localhost
                     user="kodi",         # your username
                     passwd="kodi",  # your password
                     db="MyMusic56")        # name of the data base
olddb = "MyMusic48.db"
#cur = db.cursor()
old_con = sqlite3.connect(olddb)
new_con.text_factory = str
old_con.text_factory = str
oldc = old_con.cursor()
newc = new_con.cursor()
newc.execute("""select idSong,strPath,strFileName from song natural left outer join path""")
newdata = newc.fetchall()
oldpath = "/home/myhome/Music"
newpath = "smb://192.168.0.4/Music"

for row in newdata:
        print "processing song ", row[0]
        print "file: " + row[1].replace(newpath, oldpath) + row[2]
        oldc.execute('SELECT rating,iTimesPlayed,lastplayed from song natural left outer join path where strPath = ? and strFileName = ?', (row[1].replace(newpath, oldpath), row[2]))
        old=oldc.fetchall()
        if len(old)==0:
                print "No matching file in old database!!"
        else:
                if old[0][2] is None:
                        print "Populating the song rating = " + old[0][0] + " and iTimesPlayed = ",  old[0][1]
                else:
                        print "Populating the song rating = " + old[0][0] + " last played = " + old[0][2] + " and iTimesPlayed = ",  old[0][1]
                newc.execute('UPDATE song SET rating = %s WHERE idSong = %s', (old[0][0], row[0]))
                newc.execute('UPDATE song SET iTimesPlayed = %s WHERE idSong = %s', (old[0][1], row[0]))
                newc.execute('UPDATE song SET lastplayed = %s WHERE idSong = %s', (old[0][2], row[0]))
                print "Success"
                print
oldc.close()
old_con.close()
newc.close()
new_con.commit()
new_con.close()

[docwra]

That's actually pretty cool!

I am working on cloud ratings at the moment synced with TheAudioDB site to Kodi's local database via an Add-on. This will allow users to backup their ratings, or restore them to any client around your house.

If you sign up on there and rate all your songs using the web interface, it should be possible to sync those back down any time in the future.

[ikiller]

That sounds useful for the future. Right now my music is a complete mess (21k songs), but I have been able to hold onto my ratings by referencing the filename and path (700+ songs with 4+ ratings). My next step is to get my music tagged with a musicbrainz ID, so I am not tied to my existing file structure. Ultimately I want to change my folder structure and clean up my collection while keeping my ratings.

My planned steps:
1. Use MusicBrainz Picard to tag the music with the MusicBrainz id, while keep the existing folder structure and filenames
2. Re-scrape library data
3. Copy the ratings back into the DB using path and filename as reference using python script.
4. Somehow backup my ratings with Musicbrainz ID (use your Add-on??).
5. Cleanup my music folder structure, tags, file names, upgrade file quality.
6. Re-import and re-scrape music into fresh database
7. Restore ratings using Musicbrainz ID (and your Add-On??).
8. Find holes in my music collection.

I still think it is crazy that song ratings aren't exported from the music library.

[DaveBlake]

I still think it is crazy that song ratings aren't exported from the music library.

Songs aren't exported period, eveything about songs except for user rating comes from the music file itself. Album ratings, as something that can be scraped, are exported.

So there is a rational, not total craziness, but whoever added the ability to rate songs should have completed the job with a facility to save those values in a portable and permanent way.

[ikiller]

It may be rational, but it is not clear to the average user that not all of the Music Database is exported. The wiki states that the export function "Exports the Library database to either one, or multiple XML files."

This issue originally popped up because I was unable to export my music library because I had never scraped it, it just fails without a warning. It took me a bit of work to figure out that music ratings were not exported when all documentation seemed to indicate that it should be. I appreciate the music ratings and I hope they stay. I don't mind doing a bit of coding to make it work for me and I hope my posts and code help others who also like the feature. I guess I should try to get the wiki changed to be clearer.

[DaveBlake]

Wiki improvements very welcome

I agree that the lack of export unless artists and albums have been scraped is also unhelpful, for example the empty files would be a really useful step towards manual NFO creation. In the current design users end up rescraping from scratch the data they already have, and needless server traffic. It is an area I would like to improve.

I also agree that Kodi needs a facility to make the song data edited/added by Kodi e.g. user rating, times played, lastplayed (or any other new properties that may be added manually via Kodi - see custom tagging discussion) portable and permanent. I don't think there is any reason to have the song data that is derrived from music file tags exported to xml, that data is already in separate files, but happy to discuss it.

I had not intended to hijact this thread, but it is useful to hear feature design ideas and requirements.

[thewarm]

I have a collection of 500+ (best of) singles all with the proper file layout. I do not use SQL. I found that I can save the mymusic60 and mymusic62 database files to restore "my ratings" to my main Windows computer. Kodi 17.1
How do I transfer My Ratings to another computer please?

[VelvetKevorkian]

Unfortunately that didn't work for me. I went ahead and wrote a python script to do what I needed. I also went ahead and populated the lastplayed and iTimesPlayed:

Code:

#! /usr/bin/env python

#
# Script populates the ratings, iTimesPlayed, lastplayed field in a XBMC music
# database (new) with the values from another XBMC music database (old).  This
# was written to save my ratings from an old database that was corrupt and would
# not export when I upgraded from Eden to Gotham.
# assumes same episode title and base filename (NOT fully qualified filename)

import sqlite3

olddb = "MyMusic18.db"
newdb = "MyMusic46.db"

old_con = sqlite3.connect(olddb)
new_con = sqlite3.connect(newdb)
new_con.text_factory = str
old_con.text_factory = str
oldc = old_con.cursor()
newc = new_con.cursor()
newc.execute("""select idSong,strPath,strFileName from song natural left outer join path""")
newdata = newc.fetchall()
for row in newdata:
        print "processing song ", row[0]
        print "file: " + row[1] + row[2]
        oldc.execute('SELECT rating,iTimesPlayed,lastplayed from song natural left outer join path where strPath = ? and strFileName = ?', (row[1], row[2]))
        old=oldc.fetchall()
        if len(old)==0:
                print "No matching file in old database!!"
        else:
                if old[0][2] is None:
                        print "Populating the song rating = " + old[0][0] + " and iTimesPlayed = ",  old[0][1]
                else:
                        print "Populating the song rating = " + old[0][0] + " last played = " + old[0][2] + " and iTimesPlayed = ",  old[0][1]

                newc.execute('UPDATE song SET rating = ? WHERE idSong = ?', (old[0][0], row[0]))
                newc.execute('UPDATE song SET iTimesPlayed = ? WHERE idSong = ?', (old[0][1], row[0]))
                newc.execute('UPDATE song SET lastplayed = ? WHERE idSong = ?', (old[0][2], row[0]))
                print "Success"
                print
oldc.close()
newc.close()

I have a Jarvis musicdb with song ratings.
Now i want to rescrape all my audio to a new Krypton build...
can i somehow use this code to copy over/"infuse" the song ratings from my Jarvis db into the new Krypton db?