excalibur wrote: > Hi Erland, > > I finally mustered enough courage to engage in a SQL project. I > contacted you on this topic a couple of years ago. At that time you were > not interested in looking into this. > > I would like to be able to delete music files/songs based on their > strackstat rating. Is this possible already with the GUI ? If not me and > a friend will try to do this with a script. > The functionality is not available in the plugin and it's unlikely that I'll add something that modifies or delete music files.
If you are not using the music folder browse method and you are not doing this because you want to save storage space, I would personally recommend that you rather delete the tracks from the LMS database than deleting them physically from the disk. I think you should be able to do this by adding the necessary SQL statement in SQL/SQLite/schema_optimize.sql of your LMS installation. If I remember correctly the schema_optimize.sql script is executed at the end of every rescan. It might be good enough to just delete them from the "tracks" table using a SQL like this in schema_optimize.sql: Code: -------------------- DELETE FROM tracks JOIN track_statistics ON tracks.url=track_statistics.url WHERE track_statistics.rating>0 AND track_statistics.rating<40; -------------------- If this doesn't work, you might have to remove the entries from "genre_track","playlist_track" and "contributor_track" tables before you remove them from "tracks" table. You don't have to remove them from the "track_statistics" and "tracks_persistent" tables if you don't want to, the information in "track_statistics" and "track_persistent" will just be ignored if they don't exist in "tracks" table. excalibur wrote: > > I intend to test wit a small subset of a copy of my collection. All > songs with a rating of less than 40% should be deleted from the > filesystem. I anticipate that the SB db will be updated accordingly next > time music scan is run. > Correct, the LMS database will be updated next time you do a full rescan, I don't think a scan for new/changed files will remove deleted files correctly. excalibur wrote: > > Can you give me the info I need to access the strackstat db? > You will find the description of the TrackStat tables on the wiki: http://wiki.slimdevices.com/index.php/TrackStat_plugin#Database_structure The SQL to use to get the files would probably be something like this: Code: -------------------- SELECT DISTINCT tracks.url FROM tracks JOIN track_statistics ON tracks.url=track_statistics.url WHERE track_statistics.rating>0 AND track_statistics.rating<40; -------------------- Something that's going to complicate things is that this will return url and they will even be URL encoded urls. So it will return something like Code: -------------------- file:///mnt/flacmusic/Christina%20Aguilera%20-%20Back%20To%20Basics%20Disc%202/I%20Got%20Trouble.flac -------------------- And to get the file, you need to first url decode it to get: Code: -------------------- file:///mnt/flacmusic/Christina Aguilera - Back To Basics Disc 2/I Got Trouble.flac -------------------- And then convert it from a URL to a file to get: Code: -------------------- /mnt/flacmusic/Christina Aguilera - Back To Basics Disc 2/I Got Trouble.flac -------------------- If you are doing something in perl, there is a conversion function Code: -------------------- Slim::Utils::Misc::pathFromFileURL -------------------- in LMS source code which will convert the URL encoded url to a file, you will find it in the Misc.pm file: https://github.com/Logitech/slimserver/blob/public/7.8/Slim/Utils/Misc.pm Erland Isaksson ('My homepage' (http://erland.isaksson.info)) (Developer of 'many plugins/applets (both free and commercial)' (http://wiki.slimdevices.com/index.php/User:Erland). If you like to encourage future presence on this forum and/or third party plugin/applet development, 'consider purchasing some plugins' (http://license.isaksson.info)) You may also want to try my Android apps 'Squeeze Display' (https://play.google.com/store/apps/details?id=info.isaksson.squeezedisplay) and 'RSS Photo Show' (https://play.google.com/store/apps/details?id=info.isaksson.rssphotoshow) *Interested in the future of music streaming ? 'ickStream - A world of music at your fingertips' (http://forums.slimdevices.com/showthread.php?98467-Pre-Announcement-ickStream&p=743516)*. ------------------------------------------------------------------------ erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 _______________________________________________ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins