virtual library: database operation

[afriend]

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.

Thank you. That is exactly how I felt/feel about it. Using the comment tag as a workaround/vehicle may not be the most elegant solution but it works in many places. And adding the rating to the persistent database doesn't require another scanning pass because the comment tag value is already in the library.db.

BTW just curious. I'm still using LMS 7.9.x. And some of your plugins like Dynamic Playlist, SQL Playlist + CustomSkip. And since my virtual library definitions always had some empty entries in the artists and genre menus, I've started using CustomBrowse a little again (because your Filtered Artists / Filtered Genres menus don't have that problem.

Are the current versions (maxVersion=7.9) of these plugins fully compatible with LMS 8.x. as far as you know? I'd assume so but it would be nice to know. Thanks.

[mherger]

> Inside the *initPlugin* block I have 2
> *Slim::Control::Request::addDispatch* lines. They work but the *scanner*
> log complains that there's an
>
> Code:
> --------------------
> Undefined subroutine &Slim::Control::Request::addDispatch

There is a flag which tells the code whether we're in the scanner or not:

if (!main::SCANNER) { ... }

> 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?

Can you share some code how you're doing the request, and what response
you get? Because if run inside a plugin you should get structured data back.

--

Michael

[afriend]

Can you share some code how you're doing the request, and what response
you get? Because if run inside a plugin you should get structured data back.

This is the part where the add/create playlist part should happen:

if ($rating>2) {
	$request = Slim::Control::Request::executeRequest($client, ['playlists', 0, 1, 'search:Rated High']);

	$existsPL = $request->getResult('count');
				
	if ($existsPL == 1) {
		my $playlistloopdata = $request->getResult('playlists_loop');
		
		$log->warn(Dumper($playlistloopdata));
		
		# this dump returns:
		# $VAR1 = [
		#	{
		#		'id' => 26,
		#		'playlist' => 'Rated High'
		#	}
		# ];	
		
		$playlistID_Rated_High = $playlistloopresults->id;
			
		#foreach my $hashref ($playlistloopdata) {
		#$playlistID_Rated_High = $hashref->{id};
		#}

	}elsif ($existsPL == 0){
		$request = Slim::Control::Request::executeRequest($client, ['playlists', 'new', 'name:Rated High']);
		$playlistID_Rate_HIGH = $request->getResult('playlist_id');
	}
	Slim::Control::Request::executeRequest($client, ['playlists', 'edit', 'cmd:add', 'playlist_id:'.$playlistID_Rated_High, 'url:'.$trackURL]);
}

[RobbH]

....
BTW just curious. I'm still using LMS 7.9.x. And some of your plugins like Dynamic Playlist, SQL Playlist + CustomSkip. And since my virtual library definitions always had some empty entries in the artists and genre menus, I've started using CustomBrowse a little again (because your Filtered Artists / Filtered Genres menus don't have that problem.

Are the current versions (maxVersion=7.9) of these plugins fully compatible with LMS 8.x. as far as you know? I'd assume so but it would be nice to know. Thanks.

I am over my head in any development-related discussions, but I hope it's helpful if I say that I use Dynamic Playlist and SQL Playlist with LMS 8. As far as I can tell, they work exactly as they always have. They do not support online music library integration, so online tracks in your library might as well not exist, as far as those plugins are concerned. I'm sure Erland will be able to give you more detailed information.

[mherger]

> my $playlistloopdata = $request->getResult('playlists_loop');
>
> $log->warn(Dumper($playlistloopdata));
>
> # this dump returns:
> # $VAR1 = [
> # {
> # 'id' => 26,
> # 'playlist' => 'Rated High'
> # }
> # ];
>
> $playlistID_Rated_High = $playlistloopresults->id;

That should probably be

$playlistloopresults->[0]->{id}

The result is an array ref of hash refs: one entry (hash ref) for each
track. ->[0] accesses the first item in the list, ->{id} the element
called id.

--

Michael

[afriend]

$playlistloopresults->[0]->{id}

Ok, that's embarrassing. I'd totally overlooked the square brackets in the dump.

I've included a settings page where you can set the rating keyword(s) (1 prefix + 1 suffix).

But somehow I can't seem to get the first part to work, the UNrating part that clears LMS ratings for tracks that no longer have the keyword in the comment tag.

sub startScan {
	my $class = shift;
	my $dbh = getCurrentDBH();

	my $rating_keyword_prefix = $prefs->get('rating_keyword_prefix');
	my $rating_keyword_suffix = $prefs->get('rating_keyword_suffix');

	my $sqlunrate = "UPDATE tracks_persistent
	  SET rating = NULL
	WHERE 	(tracks_persistent.rating > 0
			AND tracks_persistent.urlmd5 IN (
			   SELECT tracks.urlmd5
				 FROM tracks
					  JOIN comments ON comments.track = tracks.id
				WHERE comments.value NOT LIKE ? )
			);";

	my $sqlrate = "UPDATE tracks_persistent
		  SET rating = ?
		WHERE tracks_persistent.urlmd5 IN (
			SELECT tracks.urlmd5
				FROM tracks
			JOIN comments ON comments.track = tracks.id
				WHERE comments.value LIKE ?
		);";

	# unrate previously rated tracks if comment tag does no longer contain keyword(s)
	my $ratingkeyword_unrate = '%%'.$rating_keyword_prefix.'_'.$rating_keyword_suffix.'%%';

	my $sth = $dbh->prepare( $sqlunrate );
	eval {
		$sth->bind_param(1, $ratingkeyword_unrate);
		$sth->execute();
		commit($dbh);
	};
	if( $@ ) {
		$log->warn("Database error: $DBI::errstr\n");
		eval {
			rollback($dbh);
		};
	}
	$sth->finish();


	# rate tracks according to comment tag keyword
	my $rating = 1;

	until ($rating > 5) {
		my $rating100scalevalue = ($rating * 20);
		my $ratingkeyword = "%%".$rating_keyword_prefix.$rating.$rating_keyword_suffix."%%";
		my $sth = $dbh->prepare( $sqlrate );
		eval {
			$sth->bind_param(1, $rating100scalevalue);
			$sth->bind_param(2, $ratingkeyword);
			$sth->execute();
			commit($dbh);
		};
		if( $@ ) {
			$log->warn("Database error: $DBI::errstr\n");
			eval {
				rollback($dbh);
			};
		}

		$rating++;
		$sth->finish();
	}
	Slim::Music::Import->endImporter(__PACKAGE__);
}

The $sqlrate part works where comment tag keywords are translated into LMS rating.
And $sqlunrate part works when I put the string in the sqlpart:
WHERE comments.value NOT LIKE '%%favstars_somesuffix%%' )

Some problem with the bind_param getting the parameter into the sql part. But I can't seem to find it. Maybe another small error?

[mherger]

I just learnt a lesson: I didn't know about the "_" wildcard in SQL! Thanks! :-)

The difference I see between the two statements is that once you put the '%%' between single quotes, once between double quotes. In Perl double quotes mean that variables inside the string would be interpreted. That's why you might have to put a "%%". But with single quotes a single % sign should be good enough.

[afriend]

The difference I see between the two statements is that once you put the '%%' between single quotes, once between double quotes. In Perl double quotes mean that variables inside the string would be interpreted. That's why you might have to put a "%%". But with single quotes a single % sign should be good enough.

I should have mentioned that I'd already tried many variations on that. ;-)
Maybe the problem with the UNrate sql part is that there is no entry in the library.db's comments table for tracks without comments. Couldn't that be the reason why tracks without comments don't get unrated with the sqlunrate code I posted?
Do you have any idea how to adapt the sqlunrate code for tracks with comments (but no keywords included) and tracks without any comment tag value (= no listed in the comments table)?

[mherger]

> Maybe the problem with the UNrate sql part is that there is no entry in
> the library.db's comments table for tracks *without* comments. Couldn't
> that be the reason why tracks without comments don't get *un*rated with
> the sqlunrate code I posted?

Well, that's a good point! Did you try to test your SQL statement in
SQLite directly, instead of going through LMS? This would allow you to
get the query right before dealing with Perl specifics. I'm using
https://sqlitestudio.pl as a GUI to SQLite.

--

Michael

[afriend]

I'm using https://sqlitestudio.pl as a GUI to SQLite.

Thanks. I'll give it a try and report back about the sql code.

In the meantime I found sth else I can't explain:

I can add a track to a playlist but not delete it via the same method (just exchanging add for delete). The add version works, the delete version gives an error.

Slim::Control::Request::executeRequest($client, ['playlists', 'edit', 'cmd:delete', 'playlist_id:'.$playlistID, 'url:'.$trackURL]);

request dump:

$VAR1 = bless( {
                 '_useixhash' => 0,
                 '_requeststr' => 'playlists,edit',
                 '_params' => {
                                'url' => 'file:///Users/myuser/Music/MEDIA/04%20TRACKNAME.m4a',
                                'playlist_id' => '711',
                                'cmd' => 'delete'
                              },
                 '_request' => [
                                 'playlists',
                                 'edit'
                               ],
                 '_cb_enable' => 1,
                 '_status' => 102,
                 '_isQuery' => 0,
                 '_needClient' => 0,
                 '_langoverride' => undef,
                 '_cb_args' => undef,
                 '_func' => sub { "DUMMY" },
                 '_cb_func' => undef,
                 '_results' => {},
                 '_clientid' => '00:0c:23:ca:d9:dd'
               }, 'Slim::Control::Request' );

Result: Slim::Web::JSONRPC::requestMethod (455) Request failed with error: Bad params!

Why would the params be bad with delete but not with add? Any idea?

LMS 7.9.4 - 1603273368, macOS 10.15.7