Skip to main content

Topic: foo_uie_sql_tree (Read 46639 times) previous topic - next topic

0 Members and 2 Guests are viewing this topic.
  • r0k
  • [*][*]
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

  • neothe0ne
  • [*][*][*][*]
foo_uie_sql_tree
Reply #101
Thanks, #3 indeed is missing NULL genres, now the numbers add up.

  • Alatar
  • [*]
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

  • fbuser
  • [*][*][*][*][*]
  • Developer
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.

  • newmatrix
  • [*]
foo_uie_sql_tree
Reply #104
It is possible to add to this wonderful component options - "transparent background" and "off vertical scrollbar" ?
  • Last Edit: 06 December, 2014, 11:58:34 AM by newmatrix

  • fbuser
  • [*][*][*][*][*]
  • Developer
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
  • Last Edit: 15 November, 2015, 12:40:38 AM by Sue Dunham

  • fbuser
  • [*][*][*][*][*]
  • Developer
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.