IPB

Welcome Guest ( Log In | Register )

5 Pages V  « < 3 4 5  
Reply to this topicStart new topic
foo_uie_sql_tree
r0k
post Oct 9 2012, 10:16
Post #101





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



QUOTE (neothe0ne @ Oct 7 2012, 09:53) *
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 unsure.gif
Go to the top of the page
+Quote Post
neothe0ne
post Oct 9 2012, 10:26
Post #102





Group: Members
Posts: 295
Joined: 25-September 05
Member No.: 24684



Thanks, #3 indeed is missing NULL genres, now the numbers add up.
Go to the top of the page
+Quote Post
Alatar
post Jan 4 2013, 08:19
Post #103





Group: Members
Posts: 1
Joined: 4-January 13
Member No.: 105575



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 ohmy.gif

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
Go to the top of the page
+Quote Post
fbuser
post Jan 4 2013, 09:16
Post #104





Group: Developer
Posts: 675
Joined: 26-September 07
Member No.: 47369



QUOTE (Alatar @ Jan 4 2013, 08:19) *
Queries were super slow
This usually happens for complex queries, which are referring the virtual tables (MediaLibrary,Playist) more than once.

QUOTE (Alatar @ Jan 4 2013, 08:19) *
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.

QUOTE (Alatar @ Jan 4 2013, 08:19) *
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 ohmy.gif
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.
Go to the top of the page
+Quote Post

5 Pages V  « < 3 4 5
Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 24th July 2014 - 17:15