Skip to main content

Notice

Please note that most of the software linked on this forum is likely to be safe to use. If you are unsure, feel free to ask in the relevant topics, or send a private message to an administrator or moderator. To help curb the problems of false positives, or in the event that you do find actual malware, you can contribute through the article linked here.
Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 140427 times) previous topic - next topic
0 Members and 2 Guests are viewing this topic.

Re: foo_uie_sql_tree

Reply #200
A cosmetic request for the panel. An edge style would be nice to have. Thanks.
It's already there (only for Columns UI). Right click in the relevant panel (not the one you can open from the menu, because it is DUI) and choose options.


Re: foo_uie_sql_tree

Reply #202
I have a requirement where I'd like to filter for all albums in one playlist that do not have album artist set correctly. Incorrect would be defined as the album having different track artists but an empty album artist (Album Artist could be either "various artists" or a real album artist.).

I was thinking of solving this by hashing numerically the artist for each grouping and than use an operation to determine if this is the same for each row. However I saw that sqlite has no such function built in.

Maybe there is an easier way of doing this? Many thanks in advance...

Re: foo_uie_sql_tree

Reply #203
In general you can start with this query:

Code: [Select]
SELECT artist,
       album
FROM MediaLibrary
WHERE (album, totaltracks) IN (SELECT album, totaltracks
                FROM MediaLibrary
                WHERE "album artist" IS NULL
                GROUP BY album, totaltracks
                HAVING COUNT(DISTINCT artist)>1)
ORDER BY artist, album

Depending on your library size and structure you might run into performance issues. In this case you need to write the result of the subquery to a temporary table , add an index on album to it and rewrite the query by joining MediaLibrary and the temporary table.

If the the tags album and totaltracks are not sufficient to uniquely identify an album you need ot adjust the combination of album and totaltracks according to your tagging scheme.

Re: foo_uie_sql_tree

Reply #204
Excuse me , I have a question need help very much . :'(
Edit the data in path
all playlist path : J:\ => I:\ (ex : J:\POP\A => I:\POP\A )

Quote
Execution error:
SQLite Error: (8) attempt to write a readonly database

Error while executing:
update Playlist
set path =
(
SELECT replace( path, 'J:\', 'I:\' ) path FROM Playlist
where path like 'J:\%'
)

Please help me :'(

Re: foo_uie_sql_tree

Reply #205
Unfortunately it is not possible, what you want to achieve. Besides the fact, that you are using a readonly version of the virtual playlist table (please consult the help file how to use a read/write version of it), you can only update tags in this way, but not file attributes. You might try the plugin foo_playlist_revive to get what you want.

Re: foo_uie_sql_tree

Reply #206
Unfortunately it is not possible, what you want to achieve. Besides the fact, that you are using a readonly version of the virtual playlist table (please consult the help file how to use a read/write version of it), you can only update tags in this way, but not file attributes. You might try the plugin foo_playlist_revive to get what you want.

I appreciate your help very much.  :-[
Now...music folders import ,  this process need to wait for a while.
Can I ask one more question?
Does the "Playlist name"  also have similar plug-ins to rename batches to replace keywords?
Quote
ex1:
Playlist name
[POP] AAA => [J-POP] AAA
[POP] BBB =>  [J-POP] BBB
[POP] CCC => [J-POP] CCC

ex2:
Playlist name
[C89] AAA => [C99] AAA
[C89] BBB =>  [C99] BBB
[C89] CCC => [C99] CCC
Originally wanted to use SQL to solve...but... :'(


Re: foo_uie_sql_tree

Reply #207
I'm not aware of any dedicated plugin, which is able to do this, but you could write a script for foo_spider_monkey_panel, which is doing what you want.

Re: foo_uie_sql_tree

Reply #208
I'm not aware of any dedicated plugin, which is able to do this, but you could write a script for foo_spider_monkey_panel, which is doing what you want.

Ok!! I will try to study it. Thank you very much for your help and answers today. O:)
Have a good one!

Re: foo_uie_sql_tree

Reply #209
I'm trying to get average playcount, but the fields just end up empty (except for the first one which shows the wrong value):



Which is weird because average rating works.

I'm using  a snippet that seems to have worked for some people years ago.

Edit: I found the solution. Playback Statistics data aren't normal tag fields, that's why in SQLite viewer settings, I had to change play_count from "Tag" to "TFExpression" and enter %play_count% in the text field so that it'd read the foo_playback_statistics data instead of Foobar's native play_count tag.

Re: foo_uie_sql_tree

Reply #210
This is most likely a problem with your data. You should try to use "CAST(play_count AS INT)" instead of just "play_count" for avg() as SQLite tries to identify the datatype of the field automatically. This doesn't always work as expected.

If this doesn't work, how is play_count defined for the MediaLibrary table and what is the result of the query (assuming that Absu has also play_count values)

SELECT artist,album, tracknumber, title, play_count
FROM MediaLibrary
WHERE artist IN ('Gates Of Ishtar','Absu')
ORDER BY 1,2,3;

Re: foo_uie_sql_tree

Reply #211
Thanks but see my edit, it was because the data isn't stored in a tag at all when you have foo_playcount installed.

Anyway, I have another problem: When I use "ORDER BY play_count DESC" and send the results to a playlist, their order is just tracks ordered by playcount with no regard for albums. I tried "ORDER BY avg(play_count)" but that gives me a syntax error. How can I make it so that the results in the playlist are grouped by album and albums ordered by average playcount? Sorry if that's a dumb question, it's my first time messing with SQL.

Re: foo_uie_sql_tree

Reply #212
This is a bit more complicated.

Put this into the Batch tab of your query node (not tested by myself):

Code: [Select]
DROP TABLE IF EXISTS tmp_artist_playcount;
CREATE TABLE tmp_artist_playcount AS
SELECT artist,avg(play_count) play_count
FROM MediaLibrary
GROUP BY artist;

CREATE UNIQUE INDEX tmp_artist_playcount_uq ON tmp_artist_playcount(artist);

and this into the Query tab (you might adjust the queried columns and the order to your needs):
Code: [Select]
SELECT ml.artist "Artist"
FROM MediaLibrary ml JOIN tmp_artist_playcount t ON ml.artist=t.artist
ORDER BY  play_count DESC, ml.artist, album, tracknumber

Re: foo_uie_sql_tree

Reply #213
Hi. I have a problem it seems. Queries are not populating any items in a tree.
I set "Click action: execute SQL"
but when I click it only briefly shows "database activity" and no elements in a tree are appearing after that.
(queries are working fine in SQLite console.)

Please help. Maybe i'm not using it the right way.

Foobar2000 1.6.5
SQLite viewer 1.1.0
SQL tree 2.0.3
Windows 10 x64

Re: foo_uie_sql_tree

Reply #214
Maybe i'm not using it the right way.
Yes  ;D, but the help needs to be more specific here.

ExecuteSQL is just executing the batch part of the node in the background, nothing more. If you want to have the tree populated without any further action, you don't need the click action at all. Just right click on the node and select "Refresh".

Re: foo_uie_sql_tree

Reply #215
ExecuteSQL is just executing the batch part of the node in the background, nothing more. If you want to have the tree populated without any further action, you don't need the click action at all. Just right click on the node and select "Refresh".
Refreshing it works! thank you for helping me out.
It was not clear for me that Refreshing will lead to building the tree. :)

EDIT: option "Refresh query: on use" is not triggering refresh on clicking. is it right? how can i trigger it?
On help page we read "whenever it is "used", i.e. expanding the node or clicking on the node". but i can't expand it when there is no "plus" button yet.
maybe this trigger works after nodes have been populated already and not when they are absent.

Re: foo_uie_sql_tree

Reply #216
option "Refresh query: on use" is not triggering refresh on clicking. is it right? how can i trigger it?
It will only be refreshed, if it could be needed, i.e. if the media library was changed since the last refresh in case the query is using the MediaLibrary table, or if the playlists were changed if the query is using the playlist table.

but i can't expand it when there is no "plus" button yet.
If this option is selected, the button for expanding the node is always shown, even if it has no items. But it looks like, that the button is only set either after a refresh or a program restart, but not by just selecting the relevant option.

 

Re: foo_uie_sql_tree

Reply #217
But it looks like, that the button is only set either after a refresh or a program restart, but not by just selecting the relevant option.
Yes! I had to reopen/restart SLQ tree to make "plus" button show up. Thank you for all clarifications. :)

Re: foo_uie_sql_tree

Reply #218
Great news! I found a new way to break your stuff. :D

I'd like to back my SQL code out of the nodes themselves and reference it in external files. For instance, a file in a directory called sql off the foobar2000 directory (in a portable installation) is called tree_albumartist.sql and contains the following:
Code: [Select]
DROP VIEW IF EXISTS tree_albumartist;
CREATE VIEW         tree_albumartist AS
SELECT   coalesce(
                  [album artist]
                , artist
                , '{no artist}'
                  )
       , album
    FROM MediaLibrary
GROUP BY coalesce(
                [album artist]
              , artist
              , '{no artist}'
                )
       , album
ORDER BY coalesce(
                [album artist]
              , artist
              , '{no artist}'
                )
       , album
       , discnumber
       , tracknumber

Then in the batch tab of a query, I write this:
Code: [Select]
SELECT eval(readfile('sql/tree_albumartist.sql'))

This works if the query action is set to Execute SQL or Send to SQLite console. However, if the action is to send it to a playlist, foobar2000 crashes. In that case, I also have some code in the query tab:
Code: [Select]
SELECT * FROM tree_albumartist

I can work around this if there's nothing in the batch that needs to be run every time the query is refreshed by executing it in a different node, and then SELECT *… as above in the node I want to use it with.

Is there a better way to achieve this sort of thing?

Here's the crash report from a clean installation.

Re: foo_uie_sql_tree

Reply #219
I have a feature request as well. I'd like to have the option to hide the Refresh all action from out of the context menu. I rarely want to do that, but I accidentally invoke it from time to time. Usually I'm trying to refresh one query, and I miss the node when I right-click around it and get the nearby whitespace instead. Then I absentmindedly select the Refresh all command, missing the fact that the Refresh one has rightly not shown up above it.

Whenever the next version hits, that is. Thanks for the wonderful components. :D

Re: foo_uie_sql_tree

Reply #220
I was wondering if I could get help with a query to get all albums (on a per album basis) where the ADDED_TIMESTAMP tag is different.

I don't even know where to start.

Thanks in advance.


Re: foo_uie_sql_tree

Reply #221
Great news! I found a new way to break your stuff. :D
:D

Is there a better way to achieve this sort of thing?
Thanks for reporting the issue. Of course it shouldn't crash, but you should get an error message in such a case. You can probably make it working by adding the pseudo column "metadb_handle" to the select clause for the view.
I have a feature request as well. I'd like to have the option to hide the Refresh all action from out of the context menu.
This is a quite reasonable request. Actually I'm not really happy anymore with that option. The possibility to hide it will be added to the next version and will be the default.
I was wondering if I could get help with a query to get all albums (on a per album basis) where the ADDED_TIMESTAMP tag is different.
It's not tested, but if you add %added_timestamp%, similar to the default column %album artist%, to the media library table in the preferences, you should get the desired result with the following query:
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE ("%album artist%",album) IN
      (SELECT "%album artist%",album
       FROM MediaLibrary
       GROUP BY "%album artist%",album
       HAVING count(distinct "%added_timestamp%")>1)



Re: foo_uie_sql_tree

Reply #222
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE ("%album artist%",album) IN
      (SELECT "%album artist%",album
       FROM MediaLibrary
       GROUP BY "%album artist%",album
       HAVING count(distinct "%added_timestamp%")>1)

Thanks but it's giving me this error.

I've tried to fix it with this but I haven't a clue when it comes to SQL.

Code: [Select]
SELECT a."%album artist%" "Album Artist"
FROM MediaLibrary a JOIN (SELECT album,"%album artist%"
      FROM MediaLibrary
      GROUP BY "%album artist%",album
      HAVING count(distinct "%ADDED_TIMESTAMP%")>1)



Re: foo_uie_sql_tree

Reply #223
Thanks but it's giving me this error.
This seems to be a problem with the SQLite version (3.20.0), which is used by the released version of foo_sqlite. It is no problem with my work-in-progress version of foo_sqlite, which is using SQLite 3.36.0. Maybe such constructs just weren't supported with the older SQLite version. However, you can rewrite the query as follows, so that it shold work also with the released version of foo_sqlite:

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       GROUP BY 1
       HAVING count(distinct "%added_timestamp%")>1)

Re: foo_uie_sql_tree

Reply #224
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       GROUP BY 1
       HAVING count(distinct "%added_timestamp%")>1)

That's not bringing up any albums that have different values for the added_timestamp tag as you can see here's an EP where there are two values on the same EP.

To test if I could query the added_timestamp tag I tried this and it brings up my entire collection so querying that tag isn't the issue.

Code: [Select]
SELECT "%added_timestamp%"
FROM MediaLibrary