virtual library: database operation

[afriend]

Hello.

I have a virtual library with ~10,000 tracks based on keywords in the comment tag.
For each track of that virtual library I would like to 'convert' the keywords in the comment tag into rating values (favstars1 = rating value 1) and write them to the tracks_persistent database.

I thought converting the comment tag keywords into rating values (0-5) and writing them to the tracks_persistent database would probably look something like this:

	# first get the library_id of the virtual library:
	# library_id => Slim::Music::VirtualLibraries->getRealId('RATED');

	# ??? then for each track of this virtual library in some 'repeat loop' you could do this:	

	my $rating_keyword_prefix = "favstars";
	my $thiscomment = $track->comment;
	my $ratinglevel = 0;
	
	if (defined $thiscomment && $thiscomment ne '') {
		if ($thiscomment =~ /$rating_keyword_prefix(\d)){
			($ratinglevel) = $thiscomment =~ /$rating_keyword_prefix(\d);
		}
	}

	my $ratingStatisticValue = ($ratinglevel * 20);
	my $trackURL = $track->url;
	my $urlmd5 = md5_hex($trackURL);

	my $sql = "UPDATE tracks_persistent set rating=$ratingStatisticValue where urlmd5 = ?";
	my $dbh = getCurrentDBH();
	my $sth = $dbh->prepare( $sql );
	eval {
		$sth->bind_param(1, $urlmd5);
		$sth->execute();
		commit($dbh);
	};
	if( $@ ) {
		$log->warn("Database error: $DBI::errstr\n");
		eval {
			rollback($dbh);
		};
	}
	$sth->finish();

But what would the 'wrapper' look like, i.e. how do I get all the tracks of my virtual library and cycle through them in a 'repeat loop'?

It's basically an internal database operation. Supposing this works how fast/slow would you estimate this to be for 10,000 tracks?

Thank you.


BTW this example uses $track->url to connect a track from the main library.db (tracks) to the same track in tracks_persistent. Would $track->id work as well?

[mherger]

I wonder whether writing a plugin isn't overkill. After all you
shouldn't have to do this all the time. You could probably achieve the
same with only a few lines of SQL?

--

Michael

[afriend]

I wonder whether writing a plugin isn't overkill. After all you
shouldn't have to do this all the time. You could probably achieve the
same with only a few lines of SQL?

I agree. Totally. You mean lines that you'd pack into a script and execute after each rescan (maybe even automatically)? I'd have to install/build SQLite on my Mac first, I suppose.
Would be similar to the SQL code used to create a dynamic playlists or virtual libraries?

select tracks.url from tracks
	join comments on
		tracks.id=comments.track and comments.value like '%%favstars_%%'
	where
		audio=1

I'm a big fan of (simple) scripts but unfortunately in this case, I don't know enough SQL to translate the comment value into a rating value and then write it to tracks_persistent.
If you have any hints I'd be more than willing to try this. Thank you.

[mherger]

> I agree. Totally. You mean lines that you'd pack into a script and
> execute after each rescan (maybe even automatically)? I'd have to
> install/build SQLite on my Mac first, I suppose.

You'd have to run this after every rescan? Why don't you store the ratings in a tag that is designed for the purpose?...

> I'm a big fan of (simple) scripts but unfortunately in this case, I
> don't know SQL(ite). If you have any hints I'd be more than willing to
> try this. Thank you.

If you really need to run this after every scan, then a plugin might still be the way to go. Or you'd run your script on a cron job or something.

Here's a SQL script that might work for you. You'd have to run it for each rating individually. But it should only take a few seconds to execute:

ATTACH persist AS persist;

UPDATE persist.tracks_persistent
   SET rating = 1
 WHERE tracks_persistent.urlmd5 IN (
    SELECT tracks.urlmd5
      FROM tracks
           JOIN comments ON comments.track = tracks.id
     WHERE comments.value LIKE '%favstars1%'
);

[afriend]

Why don't you store the ratings in a tag that is designed for the purpose?...

Is there such a standard file tag that most applications across different OS can read and write to? I rate my tracks outside of LMS with applications like iTunes, Winamp, Clementine and whatnot. I would really welcome such a tag, it would make my life quite a bit easier.
Right now the comment tag is in a way my dedicated rating tag that has read and write support everywhere. And as an added bonus: in LMS the comment tag automatically imported/scanned so I don't have to use a plugin like CustomScan.

If you really need to run this after every scan, then a plugin might still be the way to go. Or you'd run your script on a cron job or something.

Or a plugin with the sole purpose of calling a script after the rescan is complete, if that's possible? But honestly, I'd just be happy it worked and be totally fine with calling the script manually or via cron.

Here's a SQL script that might work for you. You'd have to run it for each rating individually. But it should only take a few seconds to execute:

ATTACH persist AS persist;

UPDATE persist.tracks_persistent
   SET rating = 1
 WHERE tracks_persistent.urlmd5 IN (
    SELECT tracks.urlmd5
      FROM tracks
           JOIN comments ON comments.track = tracks.id
     WHERE comments.value LIKE '%favstars1%'
);

Thank you VERY much. It looks great. Can I just ask:

#1 How would I run the SQL code? Like this?

mysql -u yourusername -p yourpassword databasename < SQLCODE.txt

Which database name would I choose? I mean there is 'library.db' and 'persist.db'? If I've set a username/password in LMS (for the WebGUI and API) do I need to fill this in here too?

Sorry about all these questions but I've never executed sql code from a terminal (script or otherwise) before.

#2 If I do a wipe/delete db & rescan, is the persistent wiped clean as well? If not, I suppose I'd have to set the rating of all tracks to 0 or unrated first to catch those tracks whose rating has dropped to 0. Correct?

Anyway, I'm really grateful for your help! Thanks again!

[mherger]

>> Why don't you store the ratings in a tag that is designed for the
>> purpose?...
>
> Is there such a standard -file- tag that most applications across
> different OS can read *and* write to?

Wikipedia says "There is a loose de facto standard for implementation of
song ratings.". https://en.wikipedia.org/wiki/ID3#ID...ting_tag_issue

It's up to the application, of course. And while I never used ratings
myself, I know that five vs. 10 stars etc. has been a topic of
discussion in these forums, too.

That said I bet support applications is better than implementing your own?

> Or a plugin with the sole purpose of calling a script after the rescan
> is complete, if that's possible?

What you'd need is an importer plugin which is called after the scan.
But I can't really give you the full code here... Please look into eg.
the fulltext search plugin. It shows how you can define a plugin to be
used in the scanner.

In initPlugin() you could do some initialization work and register your
importer module.

startScan() is the work horse where you'd do the actual work. You could
basically run the SQL I posted in a small loop from 1..5, with rating
and favstars set accordingly.

> #1 How would I run the SQL code? Like this?
>
> Code:
> --------------------
> mysql -u yourusername -p yourpassword databasename < SQLCODE.txt

I don't know whether you're using MySQL. But LMS users SQLite by
default. In that case it would be something like:

sqlite3 library.db < SQLCODE.txt

But implementing an importer plugin shouldn't be too hard, and much more
elegant than this ;-).

> #2 If I do a *wipe*/delete db & rescan, is the persistent wiped clean as
> well?

No, as the name says it's the data that's supposed to persist across scans.

> If not, I suppose I'd have to set the rating of all tracks to 0 or
> unrated -first- to catch those tracks whose rating has dropped to 0.
> Correct?

My script should work at any time, and update the table whether there's
a rating value or not.

--

Michael

[afriend]

But implementing an importer plugin shouldn't be too hard, and much more
elegant than this ;-).

I don't think I can do elegant. I guess I'm just a code brute. ;-)

package Slim::Plugin::Comments2Ratings::Plugin;

use strict;

use Slim::Utils::Log;
use Slim::Utils::Scanner::API;
use Slim::Utils::Strings qw(string);
use Slim::Utils::Text;

use constant SQL_CODE => q{
ATTACH persist AS persist;

WITH RECURSIVE
	cnt(x) AS (
	 SELECT 1
	 UNION ALL
	 SELECT x+1 FROM cnt
	  LIMIT 5
	)

	UPDATE persist.tracks_persistent
	  SET rating = x FROM cnt
	WHERE tracks_persistent.urlmd5 IN (
	   SELECT tracks.urlmd5
		 FROM tracks
			  JOIN comments ON comments.track = tracks.id
		WHERE comments.value LIKE printf('%%favstars' || x FROM cnt || '%%')
	);
};

my $log = Slim::Utils::Log->addLogCategory({
	'category'     => 'plugin.comments2ratings',
	'defaultLevel' => 'WARN',
	'description'  => 'PLUGIN_COMMENTS2RATINGS',
});

my $scanlog = logger('scan');

sub initPlugin {
	my $class = shift;

	Slim::Music::Import->addImporter('Slim::Plugin::Comments2Ratings::Plugin', {
		'type'         => 'post',
		'weight'       => 95,
		'use'          => 1,
	});

	# no need to continue in scanner mode
	return if main::SCANNER;

	# don't continue if the library hasn't been initialized yet, or if a schema change is going to trigger a rescan anyway
	return unless Slim::Schema->hasLibrary() && !Slim::Schema->schemaUpdated;
}

# importer modules, run in the scanner
sub startScan {
	my $class = shift;

	my $dbh = Slim::Schema->dbh;

	$dbh->do( sprintf(SQL_CODE));

	Slim::Music::Import->endImporter(__PACKAGE__);
}

1;

I've stripped the parts from fulltextsearch that I assumed not relevant, changed some bits here and there and added the SQL code. As this is totally uncharted territory for me could you please tell me if there are any obvious errors in there, something missing, something I don't need...?

My script should work at any time, and update the table whether there's
a rating value or not.

Sorry, if I'm a bit thick here but the SQL code above would also unrate a previously rated track after I've deleted any mention of favstars in the comments tag, yes?

Thank you for your invaluable help.

[mherger]

> I've stripped the parts from fulltextsearch that I assumed not relevant,
> changed some bits here and there and added the SQL code. As this is
> totally uncharted territory for me could you please tell me if there are
> any obvious errors in there, something missing, something I don't
> need...?

I'm not seeing anything obviously wrong (but didn't run the code). You
could remove even more from initPlugin: nothing beyond addImporter is
needed.

Oh, and I learned a thing about loops in SQL. Never done that. I'd have
done it in Perl :-).

> Sorry, if I'm a bit thick here but the SQL code above would also unrate
> a previously rated track after I've deleted any mention of -favstars- in
> the comments tag, yes?

You're right, there's a problem. "un-rated" tracks would not be updated.
You'd have to add another query to set the rating to 0 if there's no
comment.

--

Michael

[erland]

>> Why don't you store the ratings in a tag that is designed for the
>> purpose?...
>
> Is there such a standard -file- tag that most applications across
> different OS can read *and* write to?

Wikipedia says "There is a loose de facto standard for implementation of
song ratings.". https://en.wikipedia.org/wiki/ID3#ID...ting_tag_issue

It's up to the application, of course. And while I never used ratings
myself, I know that five vs. 10 stars etc. has been a topic of
discussion in these forums, too.

That said I bet support applications is better than implementing your own?

In case anyone is interested in previous discussion/work.

Previous discussion/analysis thread regarding interpretation of rating values in tags:
https://forums.slimdevices.com/showt...-RATINGS-coded

The RatingTag module in CustomScan plugin does its best to try to interpret it:
https://github.com/erland/lms-custom...s/RatingTag.pm
(scanTrack function)

To summarize it, trying to implement something that works correctly independent which software that writes the rating into tags is hard while implementing something that works as long as you always set the rating with a specific software (and that doesn’t change over time) is relatively easy.

[afriend]

I'm not seeing anything obviously wrong (but didn't run the code).

I had to change the SQL code and run the repeat loop in perl but in the end it worked out.

As I'm trying to fit everything comment-tag related into one single plugin I'm seeing some unexpected problems:

#1
Inside the initPlugin block I have 2 Slim::Control::Request::addDispatch lines. They work but the scanner log complains that there's an

Undefined subroutine &Slim::Control::Request::addDispatch

Is there any way to get rid of this warning in the scanner log?


#2
I'm trying to add a track (track_id provided) to a playlist named Rated High. If the playlist doesn't exists yet, create it and add the track then (unless this happens automatically in LMS?!).

Since this is a plugin I'm not sure that I wanted to know if doing it via requests like Slim::Control::Request::executeRequest($client, ['playlists', 0, 1, 'search:Rated High]); is the correct way.

A typical response would

method:slim.request,id:1,params:[,[playlists,0,1,search:Rated High]],result:count:1,playlists_loop:[id:26,playlist:Rate HIGH]

What's the fastest way to extract the playlist id (26 in this case) from the response? (which I guess I'd need to do Slim::Control::Request::executeRequest($client, ['playlists', 'edit', 'cmd:add', 'playlist_id:'.$playlistID_Rates_High, 'url:'.$trackURL]); )

Thank you.



P.S. Is there a way to add inline code in a forum post or is markdown restricted code blocks here?