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?
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 caFROM MediaLibraryGROUP BY artist;DROP TABLE IF EXISTS album_info;CREATE TEMPORARY TABLE album_info ( dir TEXT PRIMARY KEY, artist TEXT, album TEXT);INSERT INTO album_infoSELECT 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 albumFROM MediaLibrary as mlLEFT JOIN artist_info AS ai WHERE ai.artist=ml.artistGROUP 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_infoSELECT MetaDB_Handle, ai.artist, ai.albumFROM MediaLibrary as mlLEFT JOIN album_info as ai WHERE glob(ai.dir||'\*',ml.path);CREATE INDEX album_artist_idx ON tree_info (artist,album);
SELECT artist, albumFROM tree_infoGROUP BY artist,album
Queries were super slow
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
It is possible to add to this wonderful component options - "transparent background" and "off vertical scrollbar" ?
DROP VIEW IF EXISTS album_artist_query2;CREATE VIEW album_artist_query2 ASSELECT 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_HandleFROM MediaLibraryWHERE path NOT LIKE "%\hide\%";DROP VIEW IF EXISTS album_artist_init_query;CREATE VIEW album_artist_init_query ASSELECT (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_HandleFROM album_artist_query2;
SELECT init_group, coal_artist, album_displayFROM album_artist_init_queryGROUP BY album_display,coal_artist_sort,init_groupORDER BY coal_artist_sort,date_display,album_sort||coal_artist_sort,album_display, release_sort,discnumber,tracknumber
Is it possible to add the tree console and new query dialogs (mostly for the query editing) as panels?
Also, could an option be added to set the default values in the action tab?
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'd also request that you add a Refresh option to just refresh the query under the menu.
It's already there and was already there from the beginning.
SELECT format_length(sum(length_seconds)) length FROM medialibrary
Execution error:SQLite Error: (1) misuse of aggregate: sum()Error while preparing the first statement of:SELECT format_length(sum(length_seconds)) lengthFROM medialibraryWHERE CAST(format_length(sum(length_seconds)) AS TEXT) = '15wk 3d 21:31:24'
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
Thanks fbuser, works like a charm. Only a small issue:I try to calculate media library length with:Code: [Select]SELECT format_length(sum(length_seconds)) length FROM medialibraryClicking on subnode 15wk 3d 21:31:24 gives error:QuoteExecution error:SQLite Error: (1) misuse of aggregate: sum()Error while preparing the first statement of:SELECT format_length(sum(length_seconds)) lengthFROM medialibraryWHERE CAST(format_length(sum(length_seconds)) AS TEXT) = '15wk 3d 21:31:24'
Quote from: Black_Over_Bills_Mothers on 03 August, 2016, 06:52:43 AMYou are correct both are there if you right click on the root node but right clicking on sub-nodes only shows Refresh all Ctrl+F5This 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.
I have to scroll up to the root node to refresh the query.
Quote from: Black_Over_Bills_Mothers on 03 August, 2016, 11:24:15 AMI 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.
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.