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

foo_uie_sql_tree

Reply #100
2)  It doesn't seem to be possible to "send to playlist" if the query includes "count(whatever) count" in the SELECT clause.  It'll send to the tree console, but it's impossible to populate a playlist.  Is this a bug / should populating a playlist selecting count be possible?

But depending on what you want to count, it can be possible to create a view that contains your agregate function, and then use that view in a nested query to populate your playlist with something like :
SELECT ... whatever you want
FROM library
WHERE track/album/watever column IN SELECT ... FROM your-view WHERE your-count IS xxx


I would have to know exactly what you want to do, and then look at SQLite doc to help you more. If you are somewhat familiar with SQLite you can probably figure it yourself faster

foo_uie_sql_tree

Reply #101
Thanks, #3 indeed is missing NULL genres, now the numbers add up.

foo_uie_sql_tree

Reply #102
Hello,

I was playing with this plugin and run into problems. Queries were super slow, so I created one script which 'caches' some data in temp tables and here began strange things ...
Tree Looks fine but playlist get updated only once after running query which create temp tables. I can unfold tree, run my script, select some node and it show playlist properly but any subsequent use gives nothing. And one thing, maybe it's sqlite normal but creating temp tables and feeding it with query data is 5x faster than running this query on it's own

Update script, batch only:
Code: [Select]
DROP TABLE IF EXISTS artist_info;
CREATE TEMPORARY TABLE artist_info(
  artist TEXT PRIMARY KEY,
  ca INTEGER
);
 
INSERT INTO artist_info
SELECT
  artist,
  count(distinct album) as ca
FROM
  MediaLibrary
GROUP BY
  artist
;

DROP TABLE IF EXISTS album_info;
CREATE TEMPORARY TABLE album_info (
  dir TEXT PRIMARY KEY,
  artist TEXT,
  album TEXT
);
INSERT INTO album_info
SELECT
  substr(path,0,length(path)-length(filename_ext)) as dir,
  CASE WHEN
    count(distinct ml.artist)>1 THEN 'Various Artists'
      WHEN
    ai.ca=1 THEN ml.artist||" - "|| ml.album
      ELSE
    ml.artist
  END AS artist,
  CASE WHEN ai.ca=1 THEN null ELSE ml.album END as album
FROM
  MediaLibrary as ml
LEFT JOIN
  artist_info AS ai WHERE ai.artist=ml.artist
GROUP BY
  dir
;

DROP TABLE IF EXISTS tree_info;
CREATE TEMPORARY TABLE tree_info(
  MetaDB_Handle INTEGER PRIMARY KEY,
  artist TEXT,
  album TEXT
);
INSERT INTO tree_info
SELECT
  MetaDB_Handle,
  ai.artist,
  ai.album
FROM MediaLibrary as ml
LEFT JOIN album_info as ai WHERE glob(ai.dir||'\*',ml.path)
;
CREATE INDEX album_artist_idx ON tree_info (artist,album);

Tree view script (uses Advanced->omit Null)
Code: [Select]
SELECT 
  artist,
  album
FROM tree_info
GROUP BY artist,album

foo_uie_sql_tree

Reply #103
Queries were super slow
This usually happens for complex queries, which are referring the virtual tables (MediaLibrary,Playist) more than once.

Tree Looks fine but playlist get updated only once after running query which create temp tables. I can unfold tree, run my script, select some node and it show playlist properly but any subsequent use gives nothing.
This is the expected behaviour. The column MetaDB_Handle is only accessible when the virtual tables are queried directly. Writing this column into a temporary table makes it useless. For the first use it works probably only because of internal caching mechanisms, but even this will not produce reliable results.

So, you should not create the table tree_info. Instead you should try to use the query for building this table combined with the query using this table directly.

And one thing, maybe it's sqlite normal but creating temp tables and feeding it with query data is 5x faster than running this query on it's own
In general, depending on the use case increasing the performance by working with temporary tables is something you can observe for all relational database systems.

If you are referring to a single query, it is probably more a matter what you are actually measuring. Filling a temporary table is a database internal action. But if you are filling the tree itself or the list in the SQL Tree console additional actions need to be executed.

foo_uie_sql_tree

Reply #104
It is possible to add to this wonderful component options - "transparent background" and "off vertical scrollbar" ?

foo_uie_sql_tree

Reply #105
It is possible to add to this wonderful component options - "transparent background" and "off vertical scrollbar" ?
Sorry, but I won't add these two options.

foo_uie_sql_tree

Reply #106
This is very nice so far. I haven't tried too many queries out yet, but getting there. Is it possible to add the tree console and new query dialogs (mostly for the query editing) as panels? If not, could the functionality be added in a update? Also, could an option be added to set the default values in the action tab? I keep forgetting to set mine to open the console and then wonder why it won't when I click it.

Thanks for this though. :D I've missed my database class.

foo_uie_sql_tree

Reply #107
I've been having trouble with a corner case of one query that mostly works. Some albums have split values in a custom [artist sort] field to facilitate cross-referencing. For instance, Layla and Other Assorted Love Songs is sorted on both "Derek Dominos" and "Clapton Eric Dominos". Those sort values don't actually appear, but "Derek and the Dominos" does in both cases.

Furthermore, the list of artists is broadly grouped by first initial, so that both of those entries appear under "C to D". The trouble seems to be that when one album appears twice under the same initials heading the songs will be sent to the playlist unsorted. It works fine when the cross-references are in different initials headings, as in "Hitchcock Robyn Soft Boys" and "Soft Boys", but "C to D" is messing up two more albums (Cult/Death Cult, Davis Coltrane/Coltrane Davis) and "P to S" claims the last problem on Pop Iggy/Stooges.

All of the other albums—cross-referenced or not—work fine; it's just these four that have me scratching my head.

Batch
Code: [Select]
DROP VIEW IF EXISTS album_artist_query2;
CREATE VIEW album_artist_query2 AS
SELECT coalesce([album artist],[artist],"(no artist)") coal_artist,
       coalesce([album artist sort],[album artist],[artist sort],[artist],"(no artist)") coal_artist_sort,
       album,
       coalesce([album sort],album,"(no album)") album_sort,
       coalesce([date sort],date,"unkn") date_display,
       coalesce([release sort],'01') release_sort,
       discnumber,
       tracknumber,
       MetaDb_Handle
FROM MediaLibrary
WHERE path NOT LIKE "%\hide\%";

DROP VIEW IF EXISTS album_artist_init_query;
CREATE VIEW album_artist_init_query AS
SELECT (CASE WHEN (coal_artist_sort >="0" AND coal_artist_sort <="Bzzz") THEN "# to B"
        WHEN coal_artist_sort >="C" and coal_artist_sort <="Dzzz" THEN "C to D"
        WHEN coal_artist_sort >="E" and coal_artist_sort <="Gzzz" THEN "E to G"
        WHEN coal_artist_sort >="H" and coal_artist_sort <="Lzzz" THEN "H to L"
        WHEN coal_artist_sort >="M" and coal_artist_sort <="Ozzz" THEN "M to O"
        WHEN coal_artist_sort >="P" and coal_artist_sort <="Szzz" THEN "P to S"
        ELSE "T to Z, Various" END) init_group,
       album||" ("||date_display||")" album_display,
       date_display,
       coal_artist,
       coal_artist_sort,
       album_sort,
       release_sort,
       discnumber,
       tracknumber,
       MetaDb_Handle
FROM album_artist_query2;


Query
Code: [Select]
SELECT init_group,
       coal_artist,
       album_display
FROM album_artist_init_query
GROUP BY album_display,coal_artist_sort,init_group
ORDER BY coal_artist_sort,date_display,album_sort||coal_artist_sort,album_display,
         release_sort,discnumber,tracknumber

foo_uie_sql_tree

Reply #108
Is it possible to add the tree console and new query dialogs (mostly for the query editing) as panels?
No, it is not possible and at least for the query dialog it won't be possible in the future. For the tree console, I have not decided yet, but probably it won't also be possible.

Also, could an option be added to set the default values in the action tab?
Yes, this could be added, but to be honest, it will not happen in the near future as my spare time to work on this is currently very limited.

I've been having trouble with a corner case of one query that mostly works. Some albums have split values in a custom [artist sort] field to facilitate cross-referencing. For instance, Layla and Other Assorted Love Songs is sorted on both "Derek Dominos" and "Clapton Eric Dominos". Those sort values don't actually appear, but "Derek and the Dominos" does in both cases.
I could reproduce this. The problem is that the duplicates are removed in an arbitrary order, if the option "Remove duplicates when sending to a playlist" is checked (this is the default).

You could untick this option, but it would mean, that the affected songs will be displayed twice in the playlist. Afterwards you could call "Edit -> Remove duplicates" from the menu, which is removing the duplicates in a proper way.

I will set this issue on my fix list, but as I said above, it will take some time to do it.

By the way, the order-by clause of your final query should have init_group in the first position, otherwise you could have duplicate tree entries. That this is currently not the case is just a coincidence.


Re: foo_uie_sql_tree

Reply #109
Version 2.0 released. See first post for details.

Re: foo_uie_sql_tree

Reply #110
Hello,

Thanks for the update.

I just upgraded from from version 1.04. After the upgrade I noticed a few issues:
  • The "Sent to SQLite console" action seems not to work.
    After mouse click on the Query the SQLite console opens but it is empty.
    The query text is correctly displayed in the edit window. When I copy/paste it to the SQLite console it executes correctly.
     
  • Layout frezees when opening or closing nodes.
    This happens for the Root node and second level nodes after all queries are refreshing a query.
    The nodes under queries are not affected. I guess it is related to the results set size of the query as it seem to happen only when refresing queries which return >2k rows.

I'm using foobar v.1.3.11b4 and Columns UI 0.5.0.

Re: foo_uie_sql_tree

Reply #111
Thanks for the report.

1. I already noticed this, too. It will be fixed with the next release of foo_sqlite (coming soon).
2. I'm not really sure, what you mean. Do you mean the graphical glitches after expanding or collapsing a node? If yes, it will be fixed with the next release of foo_uie_sql_tree.

Re: foo_uie_sql_tree

Reply #112
Hi fbuser
Good to see you found some time to update this component. Thank you for your efforts, I've been using this component for several years now and use it as my way into my large (100k+ tracks) library.

I see you have added a Properties entry on the context menu, thanks. I'd also request that you add a Refresh option to just refresh the query under the menu.

Thanks again.


Re: foo_uie_sql_tree

Reply #114
Quote
It's already there and was already there from the beginning.

You are correct both are there if you right click on the root node but right clicking on sub-nodes only shows Refresh all  Ctrl+F5

Re: foo_uie_sql_tree

Reply #115
Thanks fbuser, works like a charm. 8)

Only a small issue:
I try to calculate media library length with:
Code: [Select]
SELECT format_length(sum(length_seconds)) length
   FROM medialibrary
Clicking on subnode 15wk 3d 21:31:24 gives error:
Quote
Execution error:
SQLite Error: (1) misuse of aggregate: sum()

Error while preparing the first statement of:
SELECT format_length(sum(length_seconds)) length
FROM medialibrary
WHERE CAST(format_length(sum(length_seconds)) AS TEXT) = '15wk 3d 21:31:24'

Re: foo_uie_sql_tree

Reply #116
You are correct both are there if you right click on the root node but right clicking on sub-nodes only shows Refresh all  Ctrl+F5
This is correct. Actually this shouldn't also be shown, when clicking on a subnode. The only option there should be "Properties", because all other options belong to the tree and not a single subnode. At the time I implemented this component, I had the opinion, that the options, which are affecting the whole tree, should also be available, when clicking on a node. But in a sense of a clean UI this is not correct. As you cannot refresh a subnode, you don't have a relevant menu entry for this.

Re: foo_uie_sql_tree

Reply #117
Thanks fbuser, works like a charm. 8)

Only a small issue:
I try to calculate media library length with:
Code: [Select]
SELECT format_length(sum(length_seconds)) length
   FROM medialibrary
Clicking on subnode 15wk 3d 21:31:24 gives error:
Quote
Execution error:
SQLite Error: (1) misuse of aggregate: sum()

Error while preparing the first statement of:
SELECT format_length(sum(length_seconds)) length
FROM medialibrary
WHERE CAST(format_length(sum(length_seconds)) AS TEXT) = '15wk 3d 21:31:24'
The select statement is wrong as you cannot use aggregate functions in the where clause of a query. It is not clear for me, what you want to achieve with this select statement. It would be possible to rewrite the query in a proper way, but semantically it would not make much sense. Also be aware, that you can define queries with aggregate functions only in the batch part of a node, not in the query part.

Edit: I looked only on the select statement in the error message, but not on the original statement. So, the reason for this error is as written in my last sentence. So, to get it working use "Send to SQLite console" as action for the relevant node and put the query into the batch section of the node.


Re: foo_uie_sql_tree

Reply #119
You are correct both are there if you right click on the root node but right clicking on sub-nodes only shows Refresh all  Ctrl+F5
This is correct. Actually this shouldn't also be shown, when clicking on a subnode. The only option there should be "Properties", because all other options belong to the tree and not a single subnode. At the time I implemented this component, I had the opinion, that the options, which are affecting the whole tree, should also be available, when clicking on a node. But in a sense of a clean UI this is not correct. As you cannot refresh a subnode, you don't have a relevant menu entry for this.

So imagine an expanded tree where the first level of sub-nodes takes several screens/panel heights. I have to scroll up to the root node to refresh the query. Not very helpful but if that's how you have designed it, so be it.

Re: foo_uie_sql_tree

Reply #120
I have to scroll up to the root node to refresh the query.
This can be easily done with the windows standard behavior by pressing the "cursor left" key once if the node is collapsed or twice if the node is expanded.

Re: foo_uie_sql_tree

Reply #121
I have to scroll up to the root node to refresh the query.
This can be easily done with the windows standard behavior by pressing the "cursor left" key once if the node is collapsed or twice if the node is expanded.

So two keys to be pressed on a keyboard that isn't to hand since I'm using the mouse before I can get to select Refresh from the context menu ... Still a pain but it I should be grateful that you have done so much so far.

Re: foo_uie_sql_tree

Reply #122
2. I'm not really sure, what you mean. Do you mean the graphical glitches after expanding or collapsing a node? If yes, it will be fixed with the next release of foo_uie_sql_tree.
That's what I meant (I thought this behavior was performance related). Thanks for the fixes.

Re: foo_uie_sql_tree

Reply #123
I see you've completed the update. Thank you for continuing to develop this wonderful component. However, I seem still to be having the trouble I reported above with the unsorted tracks. Unticking the "Remove duplicates..." box works as you described. I think I have everything updated: foobar 1.3.12, SQLite Viewer 1.0.2, and SQL Tree 2.0.1. I even corrected the query as you suggested, thanks.

On an unrelated note, I've been trying to wrap my head around the genre query in your example nodes. Could you direct me to where the tf() function is documented? Google leads me in several different directions, but "term frequency" seems to be the likeliest.

Re: foo_uie_sql_tree

Reply #124
I have to scroll up to the root node to refresh the query.
This can be easily done with the windows standard behavior by pressing the "cursor left" key once if the node is collapsed or twice if the node is expanded.
One can also doubleclick the vertical line between the appropriate nodes to collapse it.