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 111263 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_uie_sql_tree

Reply #300
I'm also experiencing a behavior change where a query that selects 0 items that is supposed to "Send to target playlist" and has "Activate playlist" enabled, does not activate the playlist anymore.  Not sure if intentional change or a bug.

Re: foo_uie_sql_tree

Reply #301
Then I move "my playlist" down, so that "something else" takes its spot.  The query then selects the contents of "something else" instead of "my playlist".  This is still happening on the newest version(s) for me.
I can't confirm this. For me it works as expected. So, there must be something more:
  • How exactly do you move the playlist down, i.e. with which tool?
  • How many playlists do you have?
  • Does the position of the playlist before you move it matter?
  • Does it make a difference, if you move a playlist up or down?
  • Do you have any further details, which might help to identify the cause of the problem?

I'm also experiencing a behavior change where a query that selects 0 items that is supposed to "Send to target playlist" and has "Activate playlist" enabled, does not activate the playlist anymore.  Not sure if intentional change or a bug.
Yes, I noticed this too, but I consider it more an unintended change of behavior in the first place. However, in a few cases I had the impression, that worked as before. So I might look into it sooner or later.

Re: foo_uie_sql_tree

Reply #302
So I got everything working as I liked, bumped the version number of my theme (pretentious, sure, but I'm trying to stay organized), and moved the working foobar2000 to a new folder. The old folder is now gone. Things work fine based on the SQL that has been loaded into the nodes after linking external files to them.

Only now that I want to edit the underlying files and update those node SQLs, I see that they are all linked with absolute links on the query and batch tabs to files in the old folder. I cannot do the quick refresh as I had hoped without re-linking the SQL file to the node in the batch tab.

I hoped there was a quicker way to do this for all of my nodes, perhaps by updating sqltree.ust_TreeItem. However, when I opened that up, I see that it already has those file locations stored in tri_sql_query_file and tri_sql_batch_file as relative paths.

Where is SQL Tree getting the absolute paths from, and how can I redirect them?

[edit: I spoke too soon. Many of the paths are stored as relative paths, but not all. Is this something that was changed between versions, and its a matter of my old nodes having absolute paths stored and the newer ones having relative paths?]

Re: foo_uie_sql_tree

Reply #303
I hoped there was a quicker way to do this for all of my nodes, perhaps by updating sqltree.ust_TreeItem.
Yes, that would be an option.

Where is SQL Tree getting the absolute paths from
You should only have an absolute path, if the path is outside of your profile folder at the time, when you create the link.

Is this something that was changed between versions, and its a matter of my old nodes having absolute paths stored and the newer ones having relative paths?
Most likely, yes. There was an issue with the path handling, which was fixed in version 3.0.1. Although it actually caused making relativ paths also, if the files were outside of the profile folder, it might have had also other impacts.

Re: foo_uie_sql_tree

Reply #304
I can't confirm this. For me it works as expected. So, there must be something more:
  • How exactly do you move the playlist down, i.e. with which tool?
  • How many playlists do you have?
  • Does the position of the playlist before you move it matter?
  • Does it make a difference, if you move a playlist up or down?
  • Do you have any further details, which might help to identify the cause of the problem?

1. I believe it is a "Playlist switcher" in a Popup panel.  I drag the playlist up or down.
2. I have 450+ playlists open.
3. The position of the playlist does not matter, after trying a few.
4. It does not matter whether I move it up or down.  If I move it up, then the query starts using the lower playlist.  If I move it down, then the query starts using the upper playlist.  If I move a different playlist below my target playlist, to a position higher up than my target playlist, then it causes the same effect.

If I exit foobar2000 and reopen, then the query will select from the correctly named playlist (until I move playlists again, which will then require restarting foobar2000 again for the query to use the correctly named playlist).

Re: foo_uie_sql_tree

Reply #305
I believe it is a "Playlist switcher" in a Popup panel.  I drag the playlist up or down.
I did the same for my tests without any problem.
I have 450+ playlists open.
Ok, I have only about 15 playlists, but this should not really make a difference.

If I exit foobar2000 and reopen, then the query will select from the correctly named playlist
And the big question for me is why, as the relevant playlist information is initialized before any query, which uses the virtual playlist table, is initialized and I see no way how this could be unintendently bypassed.

So maybe some more information will help to identify the cause of the problem:
  • What is the complete query, that you are using? In your first post addressing this issue, you omitted some parts of your query.
  • How exactly do you call it? I guess you have a query node in the SQL tree as the issue is reported in this thread. What is the configuration of this node?
  • What results do you get, if you use a materialized playlist table (see foo_sqlite help file how to create it) instead of the virtual table?
  • What is the result of the following SQL statement in the SQLite console?
    Code: [Select]
    SELECT sql
    FROM sqlite_master
    WHERE type='table'
      AND tbl_name='Playlist'

Re: foo_uie_sql_tree

Reply #306
I'm trying to get any incomplete albums. This works below but not where there's more than one CD. I format the CD as I do in the screenshot.

So DISCNUMBER, TOTALDISCS, TRACKNUMBER and TOTALTRACKS are what I use for those fields.

How can I fix this below in order for it to work for my use case? Thanks in advance.

Code: [Select]
SELECT a."%album artist%" "Album Artist",
      '['||coalesce(a.date,'')||']  '||a.album "Date - Album",
      title "Title"
FROM MediaLibrary a JOIN (SELECT album,"%album artist%"
                          FROM MediaLibrary
                          GROUP BY 1,2
                          HAVING count(*)<>max(CAST(totaltracks AS INTEGER))) b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
        a.date,
        a.album,
        discnumber,
        tracknumber

Re: foo_uie_sql_tree

Reply #307
Maybe this (UNTESTED):
Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date,'') || ']  ' || a.album "Date - Album",
       a.discnumber, a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND a.discnumber       = b.discnumber
 ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, discnumber, tracknumber

Re: foo_uie_sql_tree

Reply #308
Maybe this (UNTESTED):
Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date,'') || ']  ' || a.album "Date - Album",
       a.discnumber, a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND a.discnumber       = b.discnumber
 ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, discnumber, tracknumber

Thanks, I had an error (see attached) with this above which I fixed with the below. But I notice there's no reference to tracknumber because it's not giving me the correct results.

Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date,'') || ']  ' || a.album "Date - Album",
       a.discnumber "Disc Number", a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND a.discnumber       = b.discnumber
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, a.discnumber, tracknumber

Re: foo_uie_sql_tree

Reply #309
I've tried this below which I found in this thread. It works when I execute it in the SQLite Console but not when I add it to the query in the edit query node.

Code: [Select]
SELECT album,
      count(*) count,
      max(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY (total_tracks-count) desc

It returns this error attached.

Re: foo_uie_sql_tree

Reply #310
I notice there's no reference to tracknumber
Not sure what this means but maybe the problem is that discnumber is not present for single disc albums? If that's the case try:
Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date, '') || ']  ' || a.album "Date - Album",
       coalesce(a.discnumber, '1') "Disc Number", a.tracknumber, a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, coalesce(discnumber, '1') discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND coalesce(a.discnumber, '1') = b.discnumber
 ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, coalesce(a.discnumber, '1'), a.tracknumber

Re: foo_uie_sql_tree

Reply #311
I've tried this below which I found in this thread. It works when I execute it in the SQLite Console but not when I add it to the query in the edit query node.
Probably the problem is the ORDER BY clause: does it work if you remove it?

Re: foo_uie_sql_tree

Reply #312
I've tried this below which I found in this thread. It works when I execute it in the SQLite Console but not when I add it to the query in the edit query node.
This does not work for several reasons:

  • it's logically not possible to map the result of an aggregate query to single tracks
  • ordering the result completely different from the tree node hierarchie will create a tree with more or less one node per track already on the highest hierarchie level

What is your goal with this query?

Re: foo_uie_sql_tree

Reply #313
@mobyduck‍ thanks but that returns false results when there are for example two CDs as per the example attached. No the ORDER BY wasn't the issue. I tried removing that.

@fbuser‍ The goal is to find albums where there are missing tracks and set it up in the query tab.

I have my DISCNUMBER, TOTALDISCS, TRACKNUMBER and TOTALTRACKS total tracks formatted like below like so.

All of my albums are in one folder. The filename for the example below is like this where it's DISCNUMBER.TRACKNUMBER. ARTIST - ALBUM.ext
Code: [Select]
1.001. The XX - Intro.flac
1.002. The XX - VCR.flac
1.003. The XX - Crystalised.flac
1.004. The XX - Islands.flac
1.005. The XX - Heart Skipped A Beat.flac
1.006. The XX - Fantasy.flac
1.007. The XX - Shelter.flac
1.008. The XX - Basic Space.flac
1.009. The XX - Infinity.flac
1.010. The XX - Night Time.flac
1.011. The XX - Stars.flac
2.001. The XX - Teardrops.flac
2.002. The XX - Do You Mind.flac
2.003. The XX - Hot Like Fire.flac
2.004. The XX - Blood Red Moon.flac
2.005. The XX - Insects.flac

Formatting of DISCNUMBER, TOTALDISCS, TRACKNUMBER and TOTALTRACKS


 

Re: foo_uie_sql_tree

Reply #314
@fbuser‍ The goal is to find albums where there are missing tracks and set it up in the query tab.
But then the query which you posted before as correction of the proposal of @mobyduck should do what you want in general. What do you mean with your comment
Quote
Thanks, I had an error (see attached) with this above which I fixed with the below. But I notice there's no reference to tracknumber because it's not giving me the correct results.
in this context?

Re: foo_uie_sql_tree

Reply #315
But then the query which you posted before as correction of the proposal of @mobyduck should do what you want in general. What do you mean with your comment
in this context?

It fixed the error it gave but didn't lead to the correct results (should of mentioned that, sorry) so that's when I searched the thread and found this below:

Code: [Select]
SELECT album,
      count(*) count,
      max(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY (total_tracks-count) desc

I was after this to work when I use it in the query tab to then click on the query to create a playlist with the results.

Re: foo_uie_sql_tree

Reply #316
If you issue the following query, do you get the expected results (all incomplete albums/discs)?
Code: [Select]
SELECT album, coalesce(discnumber, '1') discnumber, "%album artist%"
  FROM MediaLibrary
 GROUP BY 1, 2, 3
HAVING count(*) <> max(CAST(totaltracks AS INTEGER))
Never mind sorry, just read you previous post: TOTALTRACKS is the number of tracks on ALL discs?

Apologies for the multiple edits.

Re: foo_uie_sql_tree

Reply #317
If you issue the following query, do you get the expected results (all incomplete albums/discs)?

Apologies for the multiple edits.

Don't worry at all. I do when I execute it in the SQLite Console it appears to return the correct results and works very fast. But adding it to a query to add the results to a playlist takes a long time and results in my entire library getting sent to the playlist.

Edit:
I've checked one album from the results in the SQLite Console and it's not what I was after. There are 29 tracks in the album and the total track tag is 29 so they shouldn't be showing up. Thanks for your help so far.

I'd love to know SQL. I know a few languages but this baffles me. Basically, if I can get the number of songs in an album and compare that to the TOTALTRACKS field, then I'd say I'm good then.

Solution:
Finally got it working as I wanted with this. Fluked this TBH. Just used a very similar one I had for getting different WWW tags for each album.

Code: [Select]
-- Finds incomplete albums. If the TOTALTRACKS field is 10 then this
-- will find any albums containing 9 or fewer tracks.

SELECT "%album artist%", album, totaltracks
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(*) <> max(CAST(totaltracks AS INTEGER)))
ORDER BY album, discnumber, tracknumber


Re: foo_uie_sql_tree

Reply #318
I'd love to know SQL. I know a few languages but this baffles me.
Yeah, I think it's an amazing (and underrated) programming language.
Finally got it working as I wanted
I was going to suggest something similar: I probably was mislead by the two different values for totaltracks in your first screenshot. Anyway, glad you solved it.

Regards.

Re: foo_uie_sql_tree

Reply #319
I started poking around again with the crashing error I've had. I don't know what's wrong with my computer that you can't reproduce it there, but I'm getting it in spades here. Alas, this is the only Windows box in the house.

I'll tell you about that later, but first—in working with clean installs—I noticed some wonky behavior with the Tree. Here's what I did.
  • Made a clean portable installation of fb2k 1.6.16
  • Added a music folder to the Media Library
  • Installed the current versions of SQLite Utilities and SQL Tree

The first error was upon first opening the SQL Tree: it failed to build all the sample nodes, not recognizing the field [album artist]. I cleared that up by going to Preferences → Media Library → SQLite utilities → MediaLibrary table and pressing the Add defaults button. I don't remember the plugin glitching like that before,, but perhaps I haven't kept up with the beginner's instructions.

Next, when I got (in a new, clean installation) the sample nodes done, I tried but could not refresh them. I looked in vain for a Refresh option in the context menu. There was a Refresh All if I clicked on the folder icon, but neither that nor the plain Refresh when clicking the node label. The Refresh All didn't work when I tried to do it.

Then I just pressed the node and sent everything to a new playlist. This populated that node.

I don't have this trouble in my main fb2k installation, but this fresh one was giving me these issues.

OK, so with the node having sent everything to a new playlist, I was primed to test the trouble I still do have in my main fb2k: if I add a new playlist and then query the Playlist table, foobar crashes. If before querying I close and reopen foobar, things work as expected.

The new install crashed too. With the new playlist created, I opened the SQLite Console and tried to run select * from Playlist. Crash.

Here's a zip of the crash reports, the dbs, and some screen grabs of the context menus.

Re: foo_uie_sql_tree

Reply #320
The first error was upon first opening the SQL Tree: it failed to build all the sample nodes, not recognizing the field [album artist]. I cleared that up by going to Preferences → Media Library → SQLite utilities → MediaLibrary table and pressing the Add defaults button. I don't remember the plugin glitching like that before
Then we are two  :D Actually, I noticed this  a while ago too, but decided to ignore it for the moment. But this is definitely not ideal, so I will fix it for the next release.

Next, when I got (in a new, clean installation) the sample nodes done, I tried but could not refresh them. I looked in vain for a Refresh option in the context menu. There was a Refresh All if I clicked on the folder icon, but neither that nor the plain Refresh when clicking the node label. The Refresh All didn't work when I tried to do it.
Thanks for the report. I'll check it.

Here's a zip of the crash reports, the dbs, and some screen grabs of the context menus.
Thanks for all your efforts. I'll check it and can hopefully fix it then.

Re: foo_uie_sql_tree

Reply #321
I'm seeing another issue recently as I've started adding keyboard shortcuts. The behavior of them is different when the SQL Tree has the focus. For instance, `ctrl+P` will open the preferences as usual, but a chime will sound as well. This doesn't happen when other components have the focus.

Other, custom commands don't function at all. I've made `ctrl+space` run the Play or pause command. When SQL Tree has the focus, the chime will sound but a playing track won't pause.

I'm having a couple other issues while experimenting with the DUI. First, the Edge style dropdown doesn't appear in the Options... dialog under that interface. I'd really like that edge to go away there,

I have issues too trying to get my CUI nodes to show up in a DUI tree, which always seems to stick with the example nodes. I paste in the tree.db and .cfg files from one portable fb2k to another, but the one interface doesn't seem to cross over to the other. Is there a way I can manipulate the foo_uie_sql_tree.db tables to indicate which of the <ROOT> nodes is the active one?

Re: foo_uie_sql_tree

Reply #322
I'm seeing another issue recently as I've started adding keyboard shortcuts. The behavior of them is different when the SQL Tree has the focus. For instance, `ctrl+P` will open the preferences as usual, but a chime will sound as well. This doesn't happen when other components have the focus.
With a few exceptions for those components like foo_uie_explorer or foo_uie_albumlist, which are also using a treeview control. This is Windows standard behavior. However, I implemented a nasty hack for the next release, which suppresses this standard behavior.

Other, custom commands don't function at all. I've made `ctrl+space` run the Play or pause command. When SQL Tree has the focus, the chime will sound but a playing track won't pause.
Because SQL Tree uses `ctrl+space` by itself to execute the middle-click action of a query node. For the next release a more fine-grained behavior is implemented, so that it only will be used, if a query node with a configured middle-click action is the active node in the tree.

I'm having a couple other issues while experimenting with the DUI. First, the Edge style dropdown doesn't appear in the Options... dialog under that interface. I'd really like that edge to go away there,
It's implemented for the next release. It was not available so far as changing the edge style is no DUI feature in opposite to CUI. But in the end, it's also possible to use it inside a DUI panel.

I have issues too trying to get my CUI nodes to show up in a DUI tree, which always seems to stick with the example nodes. I paste in the tree.db and .cfg files from one portable fb2k to another, but the one interface doesn't seem to cross over to the other. Is there a way I can manipulate the foo_uie_sql_tree.db tables to indicate which of the <ROOT> nodes is the active one?
No. The active node is not stored in the database but in the config of the relevant panel. You would need to identify the root node of the DUI panel and link the CUI nodes to this root node.

BTW, the issues you reported on Thursday are also fixed for the next release. I could reproduce the crash as you described it, but only when I added accidently an UNC path from a network share as library folder. With a relativ path from the same drive, it didn't happen.

For the next release (a fb2k v2 only release), I already changed the path handling a bit before and maybe due to this the described crash is not occurring anymore.

Re: foo_uie_sql_tree

Reply #323
New version, see first post.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #324
Hi,

Example script "Rebuild medialibrary tables" crashes foobar (x64) - x32 not tested. :o