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
