editing library paths without losing ratings/art
#1
Hello,

i want to change my library paths from
Music/Artist/Year - Album
to
Music/BEGINNING_NUMBER_OR_LETTER_OF_ARTIST/Artist/Year - Album

Reason
I have now over 5000 artist folders (most of them art only from compilation artists) and it's messy.

This would be better:
Image



Now my question:
Using sth like DB browser for SQL lite to edit "path" in MyMusicXX.db like shown below,
is it possible to change paths and still keep my song ratings and art stuff... or will my database be messed up then?


Image


Any help is appreciated.
KODI Player: Nvidia Shield TV Pro [2019] w/ Wolf Launcher + Synology DS418 | Receiver: Marantz SR6013 | TV: LG 55SJ800V | Toaster: Severin AT 2515

How to:
Create custom video nodes like Documentaries, Concerts, Kids etc
Reply
#2
I understand what you want to do and why, but unfortunately just editing the path table won't do it. If you use embedded or local art then there will be folders to adjust in the url field of the art table, and entries in the source and source_path tables too.

However there is a bigger issue, the path table also stores hash values made from name, size and timestamp that are used when checking if a folder needs to be rescanned during library update. These values won't match the hash for the new folder, so if you run library update everything will get rescanned as if it changed.

If you get the path changes correct everywhere then that approach could work, but I have not tried it myself. Maybe take a copy of the db (copy the MyMusic72.db file) and try it, but going back will be hard once you have moved the music files.

Of course the better solution would be to use export to single file, move the files, clean library, scan into library to make new entries and then import to fetch the album artist artist addiional data and art. But sadly the song playback history (userrating, last played and playcount) does not get exported - on my list for v19

Since rescanning is inevitable, rather than messing with the path values DB directly it may be better to use the export/import route but take extra steps to restore the song playback history. I think that there is an addon that can save this data to TADB, and later restore it from there maybe @docwra  can advise on that.

What I have done when I needed to do similar (+v60 to v72 convertion) is use DB Browser and write the SQL to grab the playback history data from the old version and apply to the new. If you are not afraid of SQL then I can let you have details of what I did.
Reply
#3
(2019-03-09, 11:33)DaveBlake Wrote: I understand what you want to do and why, but unfortunately just editing the path table won't do it. If you use embedded or local art then there will be folders to adjust in the url field of the art table, and entries in the source and source_path tables too.

However there is a bigger issue, the path table also stores hash values made from name, size and timestamp that are used when checking if a folder needs to be rescanned during library update. These values won't match the hash for the new folder, so if you run library update everything will get rescanned as if it changed.

If you get the path changes correct everywhere then that approach could work, but I have not tried it myself. Maybe take a copy of the db (copy the MyMusic72.db file) and try it, but going back will be hard once you have moved the music files.

Of course the better solution would be to use export to single file, move the files, clean library, scan into library to make new entries and then import to fetch the album artist artist addiional data and art. But sadly the song playback history (userrating, last played and playcount) does not get exported - on my list for v19

Since rescanning is inevitable, rather than messing with the path values DB directly it may be better to use the export/import route but take extra steps to restore the song playback history. I think that there is an addon that can save this data to TADB, and later restore it from there maybe @docwra  can advise on that.

What I have done when I needed to do similar (+v60 to v72 convertion) is use DB Browser and write the SQL to grab the playback history data from the old version and apply to the new. If you are not afraid of SQL then I can let you have details of what I did.
 I tried the sync to TADB addon, but it doesn't work.
It writes the track rating to every track it finds with that name, regardless of what album (studio version, live, etc.)
So the SQL route is the only option i guess.

It would be easier if mp3/flac just had a "rating" tag, so you could write the rating straight to the file.
That way, i could use the same track rating in KODI as well in other software like Traktor Pro.
Seems like there just isn't an easy solution to this right now.
KODI Player: Nvidia Shield TV Pro [2019] w/ Wolf Launcher + Synology DS418 | Receiver: Marantz SR6013 | TV: LG 55SJ800V | Toaster: Severin AT 2515

How to:
Create custom video nodes like Documentaries, Concerts, Kids etc
Reply
#4
On a sidenote, I'd also love to see an add-on which will write ratings to file tags in Kodi. Kodi core has a "no-file manipulation" approach which I respect but for music it gets tedious when different softwares and/or different platforms for playback and administration are used.
Reply
#5
(2019-03-09, 22:02)VelvetKevorkian Wrote: I tried the sync to TADB addon, but it doesn't work. It writes the track rating to every track it finds with that name, regardless of what album (studio version, live, etc.)
So the SQL route is the only option i guess.
That is a shame, sounds like is just uses titles and names, a comprehensive solution would need to use mbids too. In my manual SQL approach I match up the new album id to the originals first (using name and year or mbid) then use those to help match the songs ids along with artist and title or song mbid. Create a temp table with old and new song ids and old playback history values, finally update based on that.

(2019-03-09, 22:02)VelvetKevorkian Wrote: It would be easier if mp3/flac just had a "rating" tag, so you could write the rating straight to the file.
That way, i could use the same track rating in KODI as well in other software like Traktor Pro.
Seems like there just isn't an easy solution to this right now. 
Having Kodi write to music files is something many would not like, people (including me) are paranoid about ruining their media files. It is something that an addon could do, or a separate app could could use JSON API to access the Kodi data and then write to a tag.

But no real easy solutions for now.
Reply
#6
Hmmm... the comment field could be used, since there is no dedicated rating tag.
Maybe someone can write an addon/script which writes the star rating as "*" to the comment tag.
When scanning new audio files, if there is "****" in the comment tag, KODI sets a 4-star-rating in library after scanning.
But i'm open to other ideas.
KODI Player: Nvidia Shield TV Pro [2019] w/ Wolf Launcher + Synology DS418 | Receiver: Marantz SR6013 | TV: LG 55SJ800V | Toaster: Severin AT 2515

How to:
Create custom video nodes like Documentaries, Concerts, Kids etc
Reply
#7
(2019-03-09, 22:02)VelvetKevorkian Wrote: I tried the sync to TADB addon, but it doesn't work.
It writes the track rating to every track it finds with that name, regardless of what album (studio version, live, etc.)
So the SQL route is the only option i guess.
 
Yep this is how it works, but live versions tend to be named differently which is why it works that way. It does use the MBIDs but we designed it to catch all songs instead of only one.

I use the TADB Add-on and the last.fm script I posted a while back and it works pretty well syncing all my devices with the extra "rating" and "plays" data.

You are correct though, it does have the downside that it applies to all tracks named the same.
Reply
#8
(2019-03-10, 17:35)VelvetKevorkian Wrote: Hmmm... the comment field could be used, since there is no dedicated rating tag.
Maybe someone can write an addon/script which writes the star rating as "*" to the comment tag.
When scanning new audio files, if there is "****" in the comment tag, KODI sets a 4-star-rating in library after scanning.
But i'm open to other ideas.
There most certainly is a ratings tag in most tag formats e.g. RATING in Vorbis comments (FLAC) (out of 100)  and POPM in ID3 v2.4 (out of 255). Kodi reads user rating from these tags during scanning if they have been populated and coverts to an internal value out of 10. But Kodi does not write to music files, and I would oppose efforts to have core start writing to music files (of course that does not mean it will never happen).

Someone with basic coding skills could write a tool that read rating from Kodi using JSON API, and wrote to it to the tag, no need for trying to change Kodi. No need to think about trying to co-op the comment tag!

But there is more to playback history than rating, lastplayed and playcount are also useful data to maintain. I will hopefuilly get to adding this to export/import, that is the Kodi based solution, but just not enough days in the year!
Reply
#9
Moved your music files, once this PR https://github.com/xbmc/xbmc/pull/15864 has merged users will be able to export and import their the song playback history - play count, lastplayed date, rating, votes and userrating - all the dynamic data that can change during playback and is not held in the music file itself.

This will mean that users can rebuild their music library from scratch and restore the ratings and play count data that they would otherwise have lost.

No more suggestions from me about trying to do it manually with SQL, or need to save ratings (only) to a cloud. Smile
Reply
#10
Lightbulb 
@VelvetKevorkian Here be dragons...  use at own risk...  Backup before you start messing with anything...
Code:
UPDATE path SET strPath = REPLACE(strPath,"smb://OLD/Music/Roger Waters", "smb://NEW/Music/FLAC/Roger Waters");
UPDATE art SET url = REPLACE(url,"smb://OLD/Music/Roger Waters", "smb://NEW/Music/FLAC/Roger Waters");
I am often moving albums around on the hard disk.  I'll nip into that database using SQL Browser Lite and run the above commands on the Execute SQL page.  One updates the main path table, the other updates the relevant artwork.

Notice how I am pattern matching on part of the path.  I am actually moving a dozen albums at the same time on the hard disk, then these two commands mimic what I have just moved.

After manually moving my folders, I'll close KODI, open the database, run the command, do a visual check of the tables, write the changes to the database, restart KODI, and then do a "Update Library" in the Music section.  And just to be sure I haven't left any mess behind I often do a Clean Library too.

I think the fact that the hash is now wrong kicks KODI to rescan those files, which is often what I want it to do.

This hasn't yet broken anything.  Well, I haven't noticed any issues...

Personally, the idea of exporting everything and then importing it back in is scarier sounding to me Big Grin.  I don't like the idea that all the artwork gets exported in lower quality.  A step I don't really understand the logic of.

Edit: Just update this to add that this was originally cribbed from the Kodi KB with a few tweaks to better handle spaces.  (Use speech marks around the paths)
https://kodi.wiki/view/HOW-TO:Update_SQL...files_move
Reply
#11
(2019-04-07, 16:19)BatterPudding Wrote: Personally, the idea of exporting everything and then importing it back in is scarier sounding to me Big Grin.  I don't like the idea that all the artwork gets exported in lower quality.  A step I don't really understand the logic of.
Wait until you cock-up the SQL and don't notice immediately Devil
Seriously I am fine with grown-up users manipulating the DB, after all it is their data own data. But as a dev I want to make the tools so that users don't have to get their hands dirty in that way.

Image res - art gets exported at the resolution that Kodi uses it, it exports what it has. Now you may have original art at a higher res and have fed that into Kodi compressing it along the way. What you want to avoid is Kodi exporting what it has out over your original (possibly) higher res art. It won't make any difference to what you see in Kodi if you later use that lower res, but most would be upset to have lost an original.
Reply
#12
(2019-04-07, 18:43)DaveBlake Wrote: Wait until you cock-up the SQL and don't notice immediately Devil
Seriously I am fine with grown-up users manipulating the DB, after all it is their data own data. But as a dev I want to make the tools so that users don't have to get their hands dirty in that way.
The main bit there is the double checking what I have been editing.  Is that path really exactly right.  Did it do the change I expected.

As it is in the WIKI I didn't worry too much about exposing these bits to noobs.  It should be too scary looking for most, but if someone is already hacking in SQL Browser Lite it will help them.

The best part of breaking something is what gets learnt when you fix the mess. Wink
 
(2019-04-07, 18:43)DaveBlake Wrote: Image res - art gets exported at the resolution that Kodi uses it, it exports what it has. Now you may have original art at a higher res and have fed that into Kodi compressing it along the way. What you want to avoid is Kodi exporting what it has out over your original (possibly) higher res art. It won't make any difference to what you see in Kodi if you later use that lower res, but most would be upset to have lost an original. 
Handy to know that Kodi exports what it is displaying.  For some reason I thought it did that differently.  Thanks for clearing that up... and I may even brave an export test later. 

In my case it would be impossible to overwrite the originals as the Kodi PC has zero write access to the server with the music and video files on.  I am very much in that group mentioned above in this thread that doesn't trust ANY program near my music.  The idea of any application faffing around with my precious music to add a rating in to it horrifies me.

I'm also protecting against network viruses by avoiding the "everyone write" access that tends to get recommended by some mad people in certain parts of this forum...
Reply
#13
Question 
Weird - the forum seems to have eaten my post from the weekend...  Huh
Reply
#14
(2019-04-15, 15:38)BatterPudding Wrote: Weird - the forum seems to have eaten my post from the weekend...  Huh

Eaten no, but that post was not on the topic of path changes or ratings but 2 other topics, so it was split into two new threads
https://forum.kodi.tv/showthread.php?tid=343025
https://forum.kodi.tv/showthread.php?tid=343026
and answered. Sorry you didn't spot them, I did ping you in them.
Reply
#15
(2019-04-15, 17:44)DaveBlake Wrote:  did ping you in them. 
Not sure what that means in Forum language, but nothing appeared in my email.  And I had left this thread open in the browser so didn't spot the moved posts under different titles.  I was trying to keep the question hidden as it was geeky database related and only needed a "yes \ no".  And the second part of the question followed on from the above comments about exporting ratings.
Reply

Logout Mark Read Team Forum Stats Members Help
editing library paths without losing ratings/art0