Home of the Squeezebox™ & Transporter® network music players.
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31
  1. #1
    Senior Member
    Join Date
    Jul 2010
    Posts
    200

    virtual library: database operation

    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:

    Code:
    	# 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?

  2. #2
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,521

    virtual library: database operation

    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

  3. #3
    Senior Member
    Join Date
    Jul 2010
    Posts
    200
    Quote Originally Posted by mherger View Post
    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?
    Code:
    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.
    Last edited by afriend; 2020-11-06 at 09:38.

  4. #4
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,521
    > 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:

    Code:
    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%'
    );
    Michael

    "It doesn't work - what shall I do?" - "Please check your server.log and/or scanner.log file!"
    (LMS: Settings/Information)

  5. #5
    Senior Member
    Join Date
    Jul 2010
    Posts
    200
    Quote Originally Posted by mherger View Post
    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.


    Quote Originally Posted by mherger View Post
    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.

    Quote Originally Posted by mherger View Post
    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:

    Code:
    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?
    Code:
    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!

  6. #6
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,521

    virtual library: database operation

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

  7. #7
    Senior Member
    Join Date
    Jul 2010
    Posts
    200
    Quote Originally Posted by mherger View Post
    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. ;-)
    Code:
    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...?

    Quote Originally Posted by mherger View Post
    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.

  8. #8
    Babelfish's Best Boy mherger's Avatar
    Join Date
    Apr 2005
    Location
    Switzerland
    Posts
    20,521

    virtual library: database operation

    > 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

  9. #9
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,229
    Quote Originally Posted by mherger View Post
    >> 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.
    Erland Isaksson (My homepage)
    Developer of many plugins/applets
    Starting with LMS 8.0 I no longer support my plugins/applets (see here for more information )

  10. #10
    Senior Member
    Join Date
    Jul 2010
    Posts
    200
    Quote Originally Posted by mherger View Post
    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
    Code:
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •