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

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #400
When calculating the average playcount for every album, how do I ignore tracks with playcount 0?
You can use this for calculating the average:
Code: [Select]
avg(nullif(CAST(playcount AS INTEGER),0))
This replaces all 0 playcounts with NULL and NULL values are ignored by the avg() function.


Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #401
Awesome, thanks.

 

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #402
What are some things that could be done in the component to improve performance? Loading nodes takes quite a while, perhaps due to some Wine-related bottleneck; it's often an issue on my laptop. Just wondering if there are some possible optimizations I can do in the queries or the settings.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #403
Quote
Can you only refresh a tree if there's a query?
This question is not clear. Could you please elaborate? Do you mean with tree actually a tree node? Do you mean with query the query tab?
You basically answered it in the previous question.  Thanks!
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #404
Just wondering if there are some possible optimizations I can do in the queries or the settings.
Only for the queries, if any. However, there is no general advice how to optimize a query. Query optimizing can only be done individually depending on the query itself and also depending on the size and sometimes even the content of the database.


Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #405
I'm want to standardized my tag fields by combining redundant fields and eliminating fields I never use.  For example, my library has 10 variations of %Album Artist%: album artist credit, albumartist, album_artist, albumartist_credit, etc.  I want to convert all of them to %Album Artist% or just delete the field.

So, to do that, I would like to make query that lists every field name and populates a playlist with all of the tracks that use that field.

This is what I have so far.  It lists all the Field Names (903 in total!), but I can't add their tracks to a playlist.  I assume that's because the Pragma function only pulls the Field Names from the database, not the tracks / file paths that use them.  Also, I filtered out all of the standard metadata and file info fields.  Thanks for any help!

Code: [Select]
SELECT
    SUBSTR(name, 1, 1) AS first_letter,
    name
FROM
    pragma_table_info('MediaLibrary')
WHERE
name != 'bitrate' AND
name != 'channels' AND
name != 'codec' AND
name != 'filesize' AND
name != 'filesize_natural' AND
name != 'length' AND
name != 'length_ex' AND
name != 'length_samples' AND
name != 'length_seconds' AND
name != 'length_seconds_fp' AND
name != 'samplerate' AND
name != 'album artist' AND
name != 'album' AND
name != 'artist' AND
name != 'comment' AND
name != 'composer' AND
name != 'date' AND
name != 'discnumber' AND
name != 'genre' AND
name != 'performer' AND
name != 'original album' AND
name != 'totaldiscs' AND
name != 'totaltracks' AND
name != 'tracknumber' AND
name != 'title'
ORDER BY
    first_letter, name
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #406
So I was trying to use DATEDIFF(day, CURRENT_TIMESTAMP, max_lastplayed) to get the number of days since last played, but the component doesn't seem to know what to do with the interval parameter ("day") and shows an error. Not sure how else to get the difference other than clunky string manipulation.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #407
So I was trying to use DATEDIFF(day, CURRENT_TIMESTAMP, max_lastplayed) to get the number of days since last played, but the component doesn't seem to know what to do with the interval parameter ("day") and shows an error.
Of course. DATEDIFF is not a known function in SQLite. See here for the supported date functions in SQLite.

When it comes to functions in SQL there is no real standard. So you cannot go to just an arbitrary SQL web page and use what you find there. The best is that you stick with the SQLite documentation and the custom sql functions documented in the SQLite utilities help file.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #408
So, to do that, I would like to make query that lists every field name and populates a playlist with all of the tracks that use that field.
This is neither possible nor necessary. You event don't need SQL for it. You can search with the standard foobar2000 query syntax for the non standard fields one by one, e.g. "NOT albumartist MISSING".

With SQL it would be possible in a similar way. Besides this it can help you only be creating a list in the SQLite console with your query (just without the first_letter column) but not as tree. Of course, you can define a tree node which executes your query in the SQLite console.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #409
So I was trying to use DATEDIFF(day, CURRENT_TIMESTAMP, max_lastplayed) to get the number of days since last played, but the component doesn't seem to know what to do with the interval parameter ("day") and shows an error.
Of course. DATEDIFF is not a known function in SQLite. See here for the supported date functions in SQLite.

When it comes to functions in SQL there is no real standard. So you cannot go to just an arbitrary SQL web page and use what you find there. The best is that you stick with the SQLite documentation and the custom sql functions documented in the SQLite utilities help file.

Damn, the timediff() function also doesn't work, but I was able to achieve what I wanted with julianday(), thx.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #410
As I mentioned in my previous post, I'm trying to audit several fields I think are interfering with sorting in some of my other apps.  I'm trying to build a tree with nodes for each Composer field in my library with a count of tracks that have that field.  The nodes are created, their count is appended to the end, and they're sorted properly.  But, when I click on the node nothing happens.  The Action is set to send to a playlist.

Batch
Code: [Select]
DROP TABLE IF EXISTS BadFieldTable;
CREATE TABLE BadFieldTable AS

SELECT CASE WHEN [composers] IS NOT NULL THEN 'composers' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composers] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersort] IS NOT NULL THEN 'composersort' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersort] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersortorder] IS NOT NULL THEN 'composersortorder' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersortorder] IS NOT NULL
UNION ALL SELECT CASE WHEN [conductor] IS NOT NULL THEN 'conductor' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [conductor] IS NOT NULL
;
Query
Code: [Select]
SELECT
Node1 || ' (' || COUNT(path) || ')' AS NodeCount
FROM BadFieldTable
GROUP BY Node1
ORDER BY COUNT(path) DESC, album, discnumber, tracknumber COLLATE NaturalNoCase

This Query sends tracks to the playlist as expected, so I guess it has something to the do with the COUNT(path) that I added.
Code: [Select]
SELECT
Node1
FROM BadFieldTable
GROUP BY Node1
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

I found this post in the forum and the part where fbuser says "You need to define queries in that way, that the result is one track per row" maybe my problem.  But, modifying the suggestions to my problem didn't work.   :'(

Thanks for any help!
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #411
This Query sends tracks to the playlist as expected
Only because you didn't use any aggregate functions. Normally you cannot use GROUP BY clauses in such a query, if you want to send tracks to a playlist.

so I guess it has something to the do with the COUNT(path) that I added.
Exactly.

I found this post in the forum and the part where fbuser says "You need to define queries in that way, that the result is one track per row" maybe my problem.  But, modifying the suggestions to my problem didn't work.   :'(
You need to use a window function instead of an aggregate function. So instead of "count(path)" in conjunction with "GROUP BY Node1" use "count(path) OVER(PARTITION BY Node1)" without "GROUP BY Node1"

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #412
I made the changes but the sub-nodes won't add tracks to the playlist.  The parent node adds all the tracks, but not the sub-nodes.

Query
Code: [Select]
SELECT
Node1 || ' (' || count(Node1) OVER(PARTITION BY Node1) || ')'
FROM BadFieldTable
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

Batch
Code: [Select]
DROP TABLE IF EXISTS BadFieldTable;
CREATE TABLE BadFieldTable AS

SELECT CASE WHEN [composers] IS NOT NULL THEN 'composers' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composers] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersort] IS NOT NULL THEN 'composersort' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersort] IS NOT NULL
UNION ALL SELECT CASE WHEN [composersortorder] IS NOT NULL THEN 'composersortorder' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [composersortorder] IS NOT NULL
UNION ALL SELECT CASE WHEN [conductor] IS NOT NULL THEN 'conductor' END AS Node1, album, discnumber, tracknumber, path, subsong FROM MediaLibrary WHERE [conductor] IS NOT NULL
;
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #413
I made the changes but the sub-nodes won't add tracks to the playlist.  The parent node adds all the tracks, but not the sub-nodes.
If you didn't deactivate it, you should get an error message, when it is not possible to send the tracks of a node to a playlist. In any case there is always the error message printed to the foobar2000 console. Additionally there is also the executes query for the node logged. Please post this information.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #414
I keep the SQL Error window active and I kept the Foobar2K Console window open  and I cleared it after I did each of the following:

I can execute the Batch in the SQL Lite Console without an error.
I can execute the Query in the SQL Lite Console without an error.  It returns the expected number of rows.  It also returns all of the paths when I add it to the select statement.
The Query successfully Validates
The Tree is named Bad Fields Forum Test.  When I click it I get the progress bar and the playlist populates as expected.
Console:
Code: [Select]
[foo_uie_sql_tree] 21:01:40.488: Get tracks for query node "Bad Fields Forum Test" ...
[foo_uie_sql_tree] 21:01:40.492
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query node SQL:

SELECT path,
       subsong
FROM BadFieldTable
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

[foo_uie_sql_tree] 21:01:40.800: Get tracks for query node "Bad Fields Forum Test": 0:00.312742

When I click on the 1st sub-node, composers (67), nothing's added to the playlist.
Code: [Select]
[foo_uie_sql_tree] 21:14:54.386: Get tracks for query node "Bad Fields Forum Test" ...
[foo_uie_sql_tree] 21:14:54.387
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query node SQL:

SELECT path,
       subsong
FROM BadFieldTable
WHERE CAST(Node1 AS TEXT) = 'composers (67)'
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

[foo_uie_sql_tree] 21:14:54.387: Get tracks for query node "Bad Fields Forum Test": 0:00.002451

Let me know if you need me to do anything else.

Unrelated Topic: In Playback Statistics, do I have to use Automatically Synchronize File Tags to use Played Count in SQL Tree?
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #415
Ok, now the problem is clear. There is no row having he value "composers (67)" in BadFieldTable. With the following query and batch it should work:

Query:
Code: [Select]
SELECT
  Node1
FROM BadFieldTable
ORDER BY Node1, album, discnumber, tracknumber COLLATE NaturalNoCase

Batch:
Code: [Select]
DROP TABLE IF EXISTS BadFieldTable;
CREATE TABLE BadFieldTable AS
WITH BadFields AS (
  SELECT
     composers,
     composersort,
     composersortorder,
     conductor,
     album,
     discnumber,
     tracknumber,
     path,
     subsong
 FROM MediaLibrary
 WHERE coalesce(composers, composersort, composersortorder, conductor) IS NOT NULL
)
SELECT
  fieldname || ' (' || count(fieldname) OVER(PARTITION BY fieldname) || ')' Node1,
  *
FROM (
  SELECT
    'composers' fieldname,
    *
  FROM BadFields
  WHERE composers IS NOT NULL
  UNION ALL
  SELECT
    'composersort',
    *
  FROM BadFields
  WHERE composersort IS NOT NULL
  UNION ALL
  SELECT 'composersortorder',
    *
  FROM BadFields
  WHERE composersortorder IS NOT NULL
  UNION ALL
  SELECT 'conductor',
    *
  FROM BadFields
  WHERE conductor IS NOT NULL
);

Unrelated Topic: In Playback Statistics, do I have to use Automatically Synchronize File Tags to use Played Count in SQL Tree?
No, but as there are no real tags then you need to define the provided information as TFExpression columns in foo_sqlite.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #416
There is no mention of it in the docs so I assume it's not currently possible, but it would be neat if one could use SQL query syntax to generate titles for target playlists.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #417
There is no mention of it in the docs so I assume it's not currently possible, but it would be neat if one could use SQL query syntax to generate titles for target playlists.
Although not explicetly, it acutally is:
Quote
Inserting entries into the Playlist table will result in adding items to the relevant playlists.
...
If the specified playlist doesn't exist, it will be created.

The only limitiation is that you also need to add tracks to the new playlist. This
Code: [Select]
INSERT INTO PlaylistUpdatable(path,playlist_name)
SELECT '','New Playlist';
will create the playlist "New Playlist" with one empty, invalid track. For sure it would be better to populate this playlist with existing tracks, e.g.
Code: [Select]
INSERT INTO PlaylistUpdatable(path,subsong,playlist_name)
SELECT path,subsong,'Motörhead'
FROM MediaLibrary
WHERE artist='Motörhead'
assuming that there are tracks from Motörhead in your library.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #418
Oh, awesome.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #419
Ok, now the problem is clear.

Yup, that did it and it's working perfectly now!  Thanks again.

Also, what's the best way to move my trees from the Default UI to Columns UI?
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #420
Also, is there a way to assign each query's node to its own playlist?  For example:
Code: [Select]
Grouping
---Album   >>> Album Playlist
---Compilation   >>> Compilation Playlist
---EP   >>> EP Playlist
Could I assign Album, Compilation, and EP to their own playlists?  I know I can assign 1 playlist for the query with Activate / Target Playlist.
I also found these in the forum/help files and they worked as is, but I couldn't get them to work with an alias from a created table:
Code: [Select]
INSERT INTO PlaylistUpdatable(path,subsong)
SELECT path,subsong
FROM MediaLibrary
WHERE artist='Metallica';
Code: [Select]
INSERT INTO PlaylistUpdatable(path,playlist_name)
SELECT a.path,b.playlist_name
FROM MediaLibrary a,
     (SELECT 'Metallica' "playlist_name"
      UNION ALL
      SELECT 'Heavy Metal') b
WHERE artist='Metallica';
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6