foo_uie_sql_tree Options

[r0k]

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]

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

[Alatar]

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

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
artist,
album
FROM tree_info
GROUP BY artist,album

[fbuser]

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.