Home of the Squeezebox™ & Transporter® network music players.
Results 1 to 10 of 31

Hybrid View

  1. #1
    Senior Member
    Join Date
    Jul 2010
    Posts
    258

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

    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
    258
    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,549
    > 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
    258
    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,549

    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

Posting Permissions

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