Skip to main content
Topic: foo_uie_sql_tree (Read 56880 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_uie_sql_tree

Reply #150
I'm wondering if I can use this component to display %album rating%, preferably by writing a tag to the files, in ELPlaylist, as averaged from individual ratings thorugh a calculation posted earlier. I've reached the thread by looking up "calculate album rating" on the searchbar, and though I know my way around foobar pretty well, I don't get how this works. Could someone quickly guide me through what I want? All I figured out is I can open a terminal, a blank view window, and add columns from preferences (which keep disappearing after I clumsily add them...)

Re: foo_uie_sql_tree

Reply #151
I'm wondering if I can use this component to display %album rating%, preferably by writing a tag to the files,
Yes, writing a tag for album rating to the files is possible. But first you should be more specific on this:
All I figured out is I can open a terminal, a blank view window, and add columns from preferences (which keep disappearing after I clumsily add them...)
Please, describe exactly, what you are doing, best with screen shots.

Re: foo_uie_sql_tree

Reply #152
Thanks a lot for the quick assistance.

The way I understand this component functionality is, from a list of tags stored in rows on the MediaLibrary window, it can retrieve data with which it can make advanced calculations, which can then be turned into new tags. Am I getting it right?

So far I've:
1. Installed the foo_sqlite and foo_uie_sql_tree components
2. Added a SQL Tree panel to my layout, which shows <Examples> > Library folders
3. From the Preferences > MediaLibrary, clicked "Add defaults", resulting in a long list of rows presumably read from my library. After I click "OK" something off happens - the Preferences window closes, and when I open it again the rows are empty. If I click again on "Add defaults," and then click "Apply", I get further on what's wrong with an error message. Here I thought one of my tags contains a faulty value, so I try to add only the tags I'm actually gonna be using, like %rating%. I tried 3 ways to do it, but they all result in the same aforementioned error when I press apply.

After getting this solved I believe I'd have to open SQLite console, paste the code I linked in my previous post, click "Execute" (at which point it would generate the values), and then write these values to the files in some way.

Hopefully I'm on the right path!

Re: foo_uie_sql_tree

Reply #153
The way I understand this component functionality is, from a list of tags stored in rows on the MediaLibrary window, it can retrieve data with which it can make advanced calculations, which can then be turned into new tags. Am I getting it right?
I wouldn't describe it in this way, but basically your understanding is correct.

Here I thought one of my tags contains a faulty value, so I try to add only the tags
The problem here are the tag names in the list which starts with a double quote, which are shown in your first screen shot. The empty tag name at the beginning of the list could also cause some troubles. In a future version, such kind of tags will be ignored.

so I try to add only the tags I'm actually gonna be using, like %rating%. I tried 3 ways to do it, but they all result in the same aforementioned error when I press apply
This is a bit strange as if the tags with the double quotes are not in the list, the mentioned error message can normally not occur. You also shouldn't change the settings for the multivalue handling of the rating tag. Just keep the default values and leave the values in the Tag group untouched. The meaning of the entries are described in the help file (Help -> SQLite viewer).

You also need to define a tag for the album rating, e.g. "album rating" (without the double quotes)

After getting this solved I believe I'd have to open SQLite console, paste the code I linked in my previous post, click "Execute" (at which point it would generate the values), and then write these values to the files in some way.
Actually, it's not that easy:

To avoid messing up completely your media library, you should use only the active playlist for updating your tags. Therefore you also need to set up the tags properly for the playlist table like for the media library table in the preferences.

After this is done you need to create an instance of the virtual table for the playlist table, which is updatable as described in the help file:
Code: [Select]
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split);
This is a one time task and you can run this statement in the SQLite console.

You can update your tags with the following SQL statement (I didn't test it, but I use similar statements, which are working in this way).
Code: [Select]
-- Creating a temporary table for the album ratings for performance reasons is at least necessary for larger playlist
DROP TABLE IF EXISTS tmp_AlbumRating;
CREATE TABLE tmp_AlbumRating AS
SELECT "%album artist%" AS album_artist, -- %album artist% needs to be defined as a TFExpression type in the preferences. This is automatically done, when using the default values
           album,
           avg(CAST(rating AS REAL)) AS "album rating" -- as tags are always text fields, the rating tag needs to be explicitely casted to a numeric value
FROM Playlist_View
WHERE playlist_index=active_playlist() -- use only the entries from the active playlist to avoid completely messing up the whole library
GROUP BY 1,2;

CREATE UNIQUE INDEX tmp_AlbumRating_pk ON tmp_AlbumRating(album_artist,album);

UPDATE Playlist_Updatable
SET "album rating"= (SELECT "album rating"
                                 FROM tmp_AlbumRating
                                 WHERE album_artist=Playlist_Updatable."%album artist%"
                                       AND album=Playlist_Updatable.album
WHERE playlist_index=active_playlist();

In the SQL tree you could create a node then, which contains the script above in the batch section (the query section remains empty), use "Execute SQL" as click action and "Send to SQLite console" as middle click action.


 
SimplePortal 1.0.0 RC1 © 2008-2019