HydrogenAudio

Hosted Forums => foobar2000 => 3rd Party Plugins - (fb2k) => Topic started by: fbuser on 2011-09-25 21:52:20

Title: foo_uie_sql_tree
Post by: fbuser on 2011-09-25 21:52:20
foo_uie_sql_tree is a foobar2000 component for viewing the media library in a tree structure using SQL queries. The component provides a panel for ColumnsUI or DefaultUI, which is a prerequisite for using this component.

The underlying database engine is SQLite provided by foo_sqlite, which is also a prerequisite for using this component, and with the exception of a few restrictions for building the tree queries the whole functionality of SQLite is supported.

Prerequisites:
foobar 2000 1.3 or higher with DefaultUI or ColumnsUI 0.5.0 or higher
foo_sqlite (http://www.foobar2000.org/components/view/foo_sqlite)

Upgrade Note: If you're upgrading from version 1.04 or lower, you need to install also foo_sqlite. In case of problems during the upgrade, it is highly recommended to backup your current configuration before.

Latest changes:
2.0:
-fixed: the remove duplicates option when sending items to a playlist for a query node removed the duplicates in an arbitrary order, so they were not properly grouped in the playlist
- fixed: in the node definition dialog an existing query with no batch definition could not be cleared even when a batch definition was created
- fixed: the "Query active" box overlapped the scrollbars of the tree
- fixed: The scrollbars were still not placed at the very edge of the panel, if the panel was resized
- fixed: crash when pressing a key or a mouse button during the loading of the tree
- changed: renamed "Query active" to "Database activity ..."
- added: STRG-A selects the whole content in all multiline edit fields
- added: set some default values for a query node in the advanced preferences section
- added: support for DefaultUI
- removed: SQL processing priority settings
- other: extract the SQLite basic functionality and the tree console to foo_sqlite
- other: compiled with SDK-2015-08-03
- other: compiled with ColumnsUI SDK 6.5.7
- other: at least ColumnsUI 0.5.0 is required now, if ColumnsUI is used
- other: Enhanced performance for the automatic sorting after changing a query node label

1.1.2 (not published):
- other: compiled with SDK-2015-01-15
- other: upgrade to SQLite 3.8.8.3
- other: include SQLite extension functions from Liam Healy

1.1.1 (not published):
- fixed: node filter was not applied, when the node result was sent to the SQL Tree console
- other: upgrade to SQLite 3.8.0.2

1.1.0 (not published):
- fixed: scrollbars were not placed to the very edge of the panel
- fixed: inserting nodes at the beginning of an existing query definition results in random colors for the node
- fixed: text background for an unselected node was drawn larger than for a selected node
- fixed: parallel use of playist actions in the tree and querying of the column metadb_handle led to wrong results for the playlist action
- fixed: cross joins didn't work for the virtual tables
- fixed: parameters, which were used during the creation of a virtual table were ignored after a reconnect to the database
- fixed: execution time in SQL Tree Console was not reset after close
- changed: no action will be executed when left click on tree item when shift is pressed
- changed: no action will be executed when middle click on tree item when shift or ctrl is pressed
- changed: execute button actions on button up instead of button down
- changed: use "SQL Tree playlist" as default name for target playlist actions, if target playlist name is empty
- added: support undo for playlist changes
- added: possibility to define context specific icons and colors for query result nodes
- added: context menu entry "Properties" for queries.
- added: action "Choose from menu"
- added: action "Send to Library Viewer Selection playlist"
- added: support for selection holder
- added: possibility to cut/copy/paste (+ drag/drop) query+folder nodes
- added: SQL functions playing_playlist(), active_playlist()
- added: SQL functions tf_playing_item(tf_expression), tf_focus_item(playlist_index,tf_expression), tf_focus_item_pn(playlist_name,tf_expression)
- added: SQL function file_exists(file)
- added: predefined fields for the virtual Playlist table: item_is_playing, item_has_focus, item_is_selected
- added: possibility to update tags with the SQL UPDATE statement
- added: possibility to add or remove items from a playlist with the SQL INSERT or DELETE statement
- added: use automatic index on all columns, which don't have the column type "Predefined" and also for the playlist column playlist_name, playlist_index and playlist_item.
- added: the SQL Tree console remembers the last executed query after closing and reopening it
- other: upgrade to SQLite 3.7.17

Download the latest version: here (http://www.foobar2000.org/components/view/foo_uie_sql_tree)
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-09-27 12:57:30
Thanks fbuser. A great component. Works well and fast even with >100k tracks.

The only thing I would ask is if you could provide some means of formatting the tree such as node spacing and line colours.

Thanks again for all your work within foobar.
Title: foo_uie_sql_tree
Post by: fbuser on 2011-09-27 18:03:52
The only thing I would ask is if you could provide some means of formatting the tree such as ... line colours.
The line color can already be set. See here (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/foo_uie_sql_tree_help.html#SQL_tree_options).
Title: foo_uie_sql_tree
Post by: tg2k3 on 2011-09-27 20:03:53
any chance to get a dui version? :-)
Title: foo_uie_sql_tree
Post by: fbuser on 2011-09-27 21:11:58
any chance to get a dui version? :-)
No. But you can use this component with foo_popup_panels (http://www.foobar2000.org/components/view/foo_popup_panels) also in the Default UI.
Title: foo_uie_sql_tree
Post by: grimes on 2011-09-28 12:16:51
Thanks for great component, fbuser!!

"Incomplete albums" was useful for me, because i could correct several errors in my media library. Thank you for that.

Tried to make a SQL statement for "Artist rating".

Here the SQL syntax:

SELECT sum(rating)/count(title) as rate,artist FROM medialibrary GROUP BY artist ORDER BY rate DESC

Unfortunately column rate is rounded to 5,4,3,2,1 and artist are listed for each rating value alphabetical.

How can i sort by for example 5.00. 4.99, 4.97?
Title: foo_uie_sql_tree
Post by: mobyduck on 2011-09-28 14:45:15
A shot in the dark, didn't test it actually:
Code: [Select]
  SELECT artist, avg(rating * 100) as rate
    FROM medialibrary
GROUP BY artist
ORDER BY 2 DESC
HTH.

Alessandro

PS: re-reading carefully, I realize you are perhaps saying that the rating field is treated as text, not as number; if this is the case (but shouldn't be throwing an error then?), I'm afraid I can't help you, sorry.

PS2: maybe try avg(NUMERIC(rating))... I'm not very SQLite-fluent.
Title: foo_uie_sql_tree
Post by: grimes on 2011-09-28 15:13:14
That helps a lot. Thanks.

Now i have:

(http://www.abload.de/img/2011-09-2816-03-59_foo9knw.png) (http://www.abload.de/image.php?img=2011-09-2816-03-59_foo9knw.png)


(http://www.abload.de/img/2011-09-2816-06-07_foog8n9.png) (http://www.abload.de/image.php?img=2011-09-2816-06-07_foog8n9.png)
Title: foo_uie_sql_tree
Post by: fbuser on 2011-09-28 19:13:25
SELECT sum(rating)/count(title) as rate,artist FROM medialibrary GROUP BY artist ORDER BY rate DESC

Unfortunately column rate is rounded to 5,4,3,2,1 and artist are listed for each rating value alphabetical.
Yes, because you are dividing two integer values and so the result is also an integer. To get real values you need to cast one value as real, e.g. cast(sum(rating) as real)/count(title).

Of course, it is better to use the avg() function, but there is no need to multiply and devide by 100. As you probably don't need all digits after the decimal point, the expression for the rating field could look like this: round(avg(rating),2)


PS: re-reading carefully, I realize you are perhaps saying that the rating field is treated as text, not as number; if this is the case (but shouldn't be throwing an error then?)
No, the rating field is not treated as text as mentioned above. But even if it was, no error would be thrown.
Title: foo_uie_sql_tree
Post by: Falstaff on 2011-09-28 21:27:58
Thanx for this component, very good work fbuser!
Title: foo_uie_sql_tree
Post by: grimes on 2011-09-28 21:36:11
Made some maybe useful additional SQL statements for the Statistics tree:

Album rating
Code: [Select]
SELECT round(avg(rating),2) as rating, artist, album
    FROM medialibrary
GROUP BY album
ORDER BY rating DESC


Artist playcount
Code: [Select]
SELECT round(avg(play_count),2) as playcount, artist
    FROM medialibrary
GROUP BY artist
ORDER BY playcount DESC


Album playcount
Code: [Select]
SELECT round(avg(play_count),2) as playcount, artist, album
    FROM medialibrary
GROUP BY album
ORDER BY playcount DESC


Title: foo_uie_sql_tree
Post by: fbuser on 2011-09-28 22:18:56
The first and the third query will normally work as expected. However they are not properly defined. If the rating (or play_count) and the album are the same, the artist will be picked randomly.

SQLite is here less restrictive than other database engines, which wouldn't allow a non-aggregate value in the select list (like artist here), if it isn't also in the group-by clause.

Therefore, you should also add the artist to the group-by clause to get always the expected result:

Code: [Select]
SELECT round(avg(rating),2) as rating, artist, album
    FROM medialibrary
GROUP BY album,artist
ORDER BY rating DESC


Code: [Select]
SELECT round(avg(play_count),2) as playcount, artist, album
    FROM medialibrary
GROUP BY album,artist
ORDER BY playcount DESC
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-09-29 12:19:35
I've been using this component for a few days now and I love it.

I've realised that I'm missing a few context menu entries such as Open Folder and Properties. I'm used to having these in AlbumList component.

Is there any chance that they could be implemented in this component?

Thanks again.
Title: foo_uie_sql_tree
Post by: grimes on 2011-09-30 09:06:13
My queries:

Code: [Select]
//Album length

SELECT album, format_length_hours(sum(length_seconds)) length
  FROM medialibrary
GROUP BY album
ORDER BY album ASC


//Album playcount

SELECT artist, album, round(avg(play_count),2) as playcount
  FROM medialibrary
GROUP BY album, artist
ORDER BY playcount DESC


//Album rating

SELECT artist, album, round(avg(rating),2) as rating
    FROM medialibrary
GROUP BY album, artist
ORDER BY rating DESC


//Albums per artist

SELECT artist, count(DISTINCT album) album_count
  FROM medialibrary
GROUP BY artist
ORDER BY album_count DESC


//Albums per date

SELECT date, count(DISTINCT album) album_count
  FROM medialibrary
GROUP BY date
ORDER BY album_count DESC


//Albums per playlist

SELECT playlist_name, count(DISTINCT album) album_count
  FROM Playlist
GROUP BY playlist_index
ORDER BY album_count DESC


//Artist length

SELECT artist, format_length(sum(length_seconds)) length
  FROM medialibrary
GROUP BY artist
ORDER BY sum(length_seconds) DESC


//Artist playcount

SELECT artist, round(avg(play_count),2) as playcount
  FROM medialibrary
GROUP BY artist
ORDER BY playcount DESC


//Artist rating

SELECT artist, round(avg(rating),2) as rating
  FROM medialibrary
GROUP BY artist
ORDER BY rating DESC


//Artists per date

SELECT date, count(DISTINCT artist) artist_count
  FROM medialibrary
GROUP BY date
ORDER BY artist_count DESC


//Artists per playlist

SELECT playlist_name, count(DISTINCT artist) artist_count
  FROM Playlist
GROUP BY playlist_index
ORDER BY artist_count DESC


//Date playcount

SELECT date, round(avg(play_count),2) playcount
  FROM medialibrary
GROUP BY date
ORDER BY playcount DESC


//Date rating

SELECT date, round(avg(rating),2) rating
  FROM medialibrary
GROUP BY date
ORDER BY rating DESC


//Media library length

SELECT format_length(sum(length_seconds)) length
  FROM medialibrary


//Playlist playcount

SELECT playlist_name, round(avg(play_count),2) playcount
  FROM Playlist
GROUP BY playlist_index
ORDER BY playcount DESC


//Playlist rating

SELECT playlist_name, round(avg(rating),2) rating
  FROM Playlist
GROUP BY playlist_index
ORDER BY rating DESC


//Tracks per album

SELECT totaltracks, artist, album
  FROM medialibrary
GROUP BY album, artist
ORDER BY totaltracks DESC

Title: foo_uie_sql_tree
Post by: kurtyer on 2011-09-30 20:42:17
I'm using the plugin with foo_popup_panels and any new queries I create disappear every time I close the panel.
Do I need to do something specific in order to save them?
Title: foo_uie_sql_tree
Post by: fbuser on 2011-09-30 21:12:45
Goto View -> Popup panels -> Configure -> <your panel> and select "Hide on close"
Title: foo_uie_sql_tree
Post by: derty2 on 2011-10-01 01:53:49
@fbuser, interesting component, well done ++++

Would you consider adding more fingertip functionality to the context menu?
It would be nice if one could simply right-click on a node and instantly choose either "Send to [...] playlist" OR "Send to console".

@grimes, thanks a lot for sharing your scripts, very helpful +++
considering that I knew nothing about SQL scripting syntax before this plugin came along, grimes' scripts are very helpful.
I'm sure many other users feel the same way. If you have more useful scripts to share, please keep them coming, thanks.
Title: foo_uie_sql_tree
Post by: derty2 on 2011-10-01 02:23:49
foobar2000 crashed --> location = Module: foo_uie_sql_tree

View Report:
Code: [Select]
Illegal operation:
Code: C0000005h, flags: 00000000h, address: 028DC799h
Access violation, operation: read, address: 0000001Ch

Call path:
entry=>app_mainloop

Code bytes (028DC799h):
028DC759h:  5B 8B 8C 24 14 01 00 00 33 CC E8 E3 80 09 00 8B
028DC769h:  E5 5D C3 CC CC CC CC 85 C9 74 08 8B 01 8B 10 6A
028DC779h:  01 FF D2 B0 01 C3 CC C3 CC CC CC CC CC CC CC CC
028DC789h:  CC CC CC CC CC CC CC 8B 06 8B 50 28 8B CE FF D2
028DC799h:  80 78 1C 00 0F 84 35 01 00 00 83 7E 04 00 57 74
028DC7A9h:  0F 8B 4E 04 8B 01 8B 50 28 FF D2 8B 78 04 EB 09
028DC7B9h:  8B 46 34 8B B8 B4 00 00 00 8B 16 8B 42 28 8B CE
028DC7C9h:  FF D0 89 78 04 83 7E 04 00 74 0F 8B 4E 04 8B 11

Stack (0012F254h):
0012F234h:  00000000 00000000 00000000 00000000
0012F244h:  00000000 00000000 00000000 00000000
0012F254h:  028D38D4 3EFD9039 15994EB8 02A04CF8
0012F264h:  15B80349 00000200 00000000 00B500C8
0012F274h:  08732050 00000850 00000107 0488B2B8
0012F284h:  02995D70 00000000 00000000 00000000
0012F294h:  0000007D 02995F48 00000000 00000000
0012F2A4h:  00000000 02995D70 186D0F50 00000001
0012F2B4h:  00000080 00000000 003A03AC 02A04CF8
0012F2C4h:  BE38B411 00016CEF 02995F74 048039C8
0012F2D4h:  00000002 00000002 15994EF0 00000000
0012F2E4h:  02996C88 048054C8 00000003 00000004
0012F2F4h:  02A04D18 00000000 003A03AC 00000002
0012F304h:  00000000 01000000 00000001 0482B2C0
0012F314h:  00000000 15994EB8 0012F358 7E42B317
0012F324h:  00030000 00000002 0012F39C 7E42B326
0012F334h:  00994EB8 02A04CF8 15B80349 0012F334
0012F344h:  0012F338 0012F4FC 7E44048F 7E42B330
0012F354h:  FFFFFFFF 7E42B326 7E4278D0 0012F38C
0012F364h:  0012F39C 7E4278E0 00000000 00000000

Registers:
EAX: 00000000, EBX: 00000000, ECX: 0488B2B8, EDX: 028D7800
ESI: 0488B2B8, EDI: 15994EB8, EBP: 0012F3F4, ESP: 0012F254

Crash location:
Module: foo_uie_sql_tree
Offset: 1C799h

Loaded modules:
foobar2000                      loaded at 00400000h - 005F2000h
ntdll                            loaded at 7C900000h - 7C9AF000h
kernel32                        loaded at 7C800000h - 7C8F6000h
COMCTL32                        loaded at 773D0000h - 774D3000h
msvcrt                          loaded at 77C10000h - 77C68000h
ADVAPI32                        loaded at 77DD0000h - 77E6B000h
RPCRT4                          loaded at 77E70000h - 77F02000h
Secur32                          loaded at 77FE0000h - 77FF1000h
GDI32                            loaded at 77F10000h - 77F59000h
USER32                          loaded at 7E410000h - 7E4A1000h
SHLWAPI                          loaded at 77F60000h - 77FD6000h
DSOUND                          loaded at 59C50000h - 59C59000h
oleaut32                        loaded at 77120000h - 771AB000h
ole32                            loaded at 774E0000h - 7761D000h
IMAGEHLP                        loaded at 76C90000h - 76CB8000h
shell32                          loaded at 7C9C0000h - 7D1D7000h
UxTheme                          loaded at 5AD70000h - 5ADA8000h
zlib1                            loaded at 5A4C0000h - 5A4D4000h
shared                          loaded at 10000000h - 1002B000h
dbghelp                          loaded at 59A60000h - 59B01000h
VERSION                          loaded at 77C00000h - 77C08000h
COMDLG32                        loaded at 763B0000h - 763F9000h
CRYPT32                          loaded at 77A80000h - 77B15000h
MSASN1                          loaded at 77B20000h - 77B32000h
gdiplus                          loaded at 4EC50000h - 4EDF6000h
foo_ui_hacks                    loaded at 03100000h - 03140000h
wtsapi32                        loaded at 76F50000h - 76F58000h
WINSTA                          loaded at 76360000h - 76370000h
NETAPI32                        loaded at 5B860000h - 5B8B5000h
psapi                            loaded at 76BF0000h - 76BFB000h
winmm                            loaded at 76B40000h - 76B6D000h
FileBXH                          loaded at 00E70000h - 00EBE000h
RBHook                          loaded at 00EE0000h - 00EEE000h
dsound                          loaded at 73F10000h - 73F6C000h
WINTRUST                        loaded at 76C30000h - 76C5E000h
wdmaud                          loaded at 72D20000h - 72D29000h
msacm32                          loaded at 72D10000h - 72D18000h
MSACM32                          loaded at 77BE0000h - 77BF5000h
midimap                          loaded at 77BD0000h - 77BD7000h
foo_input_alac                  loaded at 012D0000h - 012ED000h
foo_albumlist                    loaded at 01310000h - 0136D000h
foo_channel_mixer                loaded at 01390000h - 013CF000h
foo_discogs                      loaded at 013F0000h - 01451000h
WININET                          loaded at 771B0000h - 7725A000h
foo_input_dtshd                  loaded at 01520000h - 0166A000h
foo_out_ks                      loaded at 01480000h - 014AA000h
SETUPAPI                        loaded at 77920000h - 77A13000h
foo_covers2                      loaded at 014D0000h - 014F7000h
foo_rgscan                      loaded at 01680000h - 016CA000h
foo_out_wasapi                  loaded at 016F0000h - 01716000h
foo_jesus                        loaded at 01740000h - 0175A000h
foo_ui_columns                  loaded at 01770000h - 01900000h
urlmon                          loaded at 7E1E0000h - 7E282000h
USP10                            loaded at 74D90000h - 74DFB000h
foo_playcount                    loaded at 01940000h - 0197E000h
foo_bookmarks                    loaded at 019D0000h - 01A44000h
foo_input_tak                    loaded at 01A50000h - 01A94000h
tak_deco_lib                    loaded at 01AA0000h - 01AC0000h
foo_converter                    loaded at 01BD0000h - 01C4B000h
foo_cdda                        loaded at 01C70000h - 01CBE000h
foo_input_std                    loaded at 01CE0000h - 01E43000h
foo_uie_vis_channel_spectrum    loaded at 01E70000h - 01EAD000h
MSIMG32                          loaded at 76380000h - 76385000h
foo_wave_seekbar                loaded at 01ED0000h - 02031000h
MSVCP100                        loaded at 78050000h - 780B9000h
MSVCR100                        loaded at 78AA0000h - 78B5F000h
WS2_32                          loaded at 71AB0000h - 71AC7000h
WS2HELP                          loaded at 71AA0000h - 71AA8000h
foo_texttools                    loaded at 020A0000h - 020D4000h
foo_input_dvda                  loaded at 02100000h - 0215E000h
foo_facets                      loaded at 02180000h - 02335000h
foo_uie_library_tree            loaded at 02360000h - 023A7000h
foo_freedb2                      loaded at 023D0000h - 02410000h
foo_utils                        loaded at 02430000h - 02474000h
foo_dsp_crossfeed                loaded at 024A0000h - 024CA000h
foo_uie_vis_peakmeter_spectrum  loaded at 024F0000h - 0252F000h
foo_uie_playlists_dropdown      loaded at 02550000h - 0258C000h
foo_input_monkey                loaded at 025B0000h - 025F9000h
foo_out_asio                    loaded at 02620000h - 02656000h
foo_uie_panel_splitter          loaded at 02680000h - 026D9000h
foo_taskbar_gestures            loaded at 03200000h - 03226000h
foo_uie_ptb                      loaded at 02710000h - 02735000h
foo_hdcd                        loaded at 02760000h - 0279F000h
foo_dsp_delta                    loaded at 027C0000h - 027DF000h
foo_abx                          loaded at 02800000h - 02832000h
foo_runcmd                      loaded at 02860000h - 02893000h
foo_uie_sql_tree                loaded at 028C0000h - 029E7000h
foo_spdif                        loaded at 02A10000h - 02A26000h
MSVCR80                          loaded at 78130000h - 781CB000h
foo_uie_tabs                    loaded at 02A50000h - 02A78000h
foo_quicksearch                  loaded at 02AA0000h - 02AF7000h
WindowsCodecs                    loaded at 4CC40000h - 4CCF3000h
foo_dbsearch                    loaded at 02B20000h - 02B90000h
foo_verifier                    loaded at 02BB0000h - 02BF3000h
foo_uie_explorer                loaded at 02C20000h - 02C5D000h
CLBCATQ                          loaded at 76FD0000h - 7704F000h
COMRes                          loaded at 77050000h - 77115000h
foo_seek                        loaded at 02C90000h - 02CB8000h
foo_vst                          loaded at 02CE0000h - 02D34000h
foo_run                          loaded at 02D60000h - 02DBF000h
foo_dsp_std                      loaded at 02DE0000h - 02E28000h
foo_ui_std                      loaded at 02E50000h - 02F68000h
foo_masstag                      loaded at 02F90000h - 02FE4000h
foo_fileops                      loaded at 03010000h - 03057000h
foo_uie_console                  loaded at 03080000h - 0309A000h
foo_bitcompare                  loaded at 030C0000h - 030E8000h
foo_input_tta                    loaded at 03150000h - 03192000h
foo_input_dts                    loaded at 03230000h - 03293000h
foo_quicktag                    loaded at 032A0000h - 032D4000h
foo_uie_wsh_panel_mod            loaded at 032E0000h - 033A2000h
IMM32                            loaded at 76390000h - 763AD000h
SXS                              loaded at 7E720000h - 7E7D0000h
SSSensor                        loaded at 048B0000h - 048C5000h
jscript                          loaded at 75C50000h - 75CCD000h
xpsp2res                        loaded at 0C3F0000h - 0C6B5000h
scrrun                          loaded at 735A0000h - 735CA000h
wshom                            loaded at 60280000h - 602A1000h
MPR                              loaded at 71B20000h - 71B32000h
browseui                        loaded at 75F80000h - 7607D000h
dciman32                        loaded at 73BC0000h - 73BC6000h
USERENV                          loaded at 769C0000h - 76A74000h
snap_libW                        loaded at 04A00000h - 04A13000h
KsUser                          loaded at 73EE0000h - 73EE4000h
shdocvw                          loaded at 7E290000h - 7E401000h
CRYPTUI                          loaded at 754D0000h - 75550000h
WLDAP32                          loaded at 76F60000h - 76F8C000h
appHelp                          loaded at 77B40000h - 77B62000h

Stack dump analysis:
Address: 028D38D4h (foo_uie_sql_tree+138D4h)
Address: 02995D70h (foo_uie_sql_tree+D5D70h)
Address: 02995F48h (foo_uie_sql_tree+D5F48h)
Address: 02995D70h (foo_uie_sql_tree+D5D70h)
Address: 02995F74h (foo_uie_sql_tree+D5F74h)
Address: 02996C88h (foo_uie_sql_tree+D6C88h)
Address: 7E42B317h (USER32+1B317h), symbol: "MoveWindow" (+79h)
Address: 7E42B326h (USER32+1B326h), symbol: "MoveWindow" (+88h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E42B330h (USER32+1B330h), symbol: "MoveWindow" (+92h)
Address: 7E42B326h (USER32+1B326h), symbol: "MoveWindow" (+88h)
Address: 7E4278D0h (USER32+178D0h), symbol: "GetWindowTextLengthW" (+9Ah)
Address: 7E4278E0h (USER32+178E0h), symbol: "GetWindowTextLengthW" (+AAh)
Address: 7C90E453h (ntdll+E453h), symbol: "KiUserCallbackDispatcher" (+13h)
Address: 7E42B341h (USER32+1B341h), symbol: "MoveWindow" (+A3h)
Address: 7E46CF6Eh (USER32+5CF6Eh), symbol: "TrackPopupMenuEx" (+Ch)
Address: 7E465339h (USER32+55339h), symbol: "TrackPopupMenu" (+1Bh)
Address: 0298A860h (foo_uie_sql_tree+CA860h)
Address: 028E2F39h (foo_uie_sql_tree+22F39h)
Address: 033A03ACh (foo_uie_wsh_panel_mod+C03ACh)
Address: 028DEEDDh (foo_uie_sql_tree+1EEDDh)
Address: 00E73FA0h (FileBXH+3FA0h), symbol: "CallWndRetProc" (+0h)
Address: 7E46AFCEh (USER32+5AFCEh), symbol: "GetRawInputDeviceInfoA" (+C1h)
Address: 7E4194BEh (USER32+94BEh), symbol: "GetWindowLongA" (+61h)
Address: 7E428E0Dh (USER32+18E0Dh), symbol: "DefWindowProcW" (+EDh)
Address: 004803C8h (foobar2000+803C8h)
Address: 7E42B401h (USER32+1B401h), symbol: "CallNextHookEx" (+3Bh)
Address: 028F7310h (foo_uie_sql_tree+37310h)
Address: 7E4188D1h (USER32+88D1h), symbol: "GetWindowLongW" (+2Bh)
Address: 7E4188DAh (USER32+88DAh), symbol: "GetWindowLongW" (+34h)
Address: 028F7310h (foo_uie_sql_tree+37310h)
Address: 028F740Eh (foo_uie_sql_tree+3740Eh)
Address: 028F7310h (foo_uie_sql_tree+37310h)
Address: 029869E8h (foo_uie_sql_tree+C69E8h)
Address: 7E418734h (USER32+8734h), symbol: "GetDC" (+6Dh)
Address: 028F7310h (foo_uie_sql_tree+37310h)
Address: 028F7310h (foo_uie_sql_tree+37310h)
Address: 7E423CE4h (USER32+13CE4h), symbol: "EnumDisplaySettingsA" (+27Dh)
Address: 028F7310h (foo_uie_sql_tree+37310h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E423D08h (USER32+13D08h), symbol: "EnumDisplaySettingsA" (+2A1h)
Address: 7E423B30h (USER32+13B30h), symbol: "EnumDisplaySettingsA" (+C9h)
Address: 028F7310h (foo_uie_sql_tree+37310h)
Address: 019E0EE0h (foo_bookmarks+10EE0h)
Address: 7E428EECh (USER32+18EECh), symbol: "DefWindowProcW" (+1CCh)
Address: 7C90E453h (ntdll+E453h), symbol: "KiUserCallbackDispatcher" (+13h)
Address: 7E423AB1h (USER32+13AB1h), symbol: "EnumDisplaySettingsA" (+4Ah)
Address: 7E4194BEh (USER32+94BEh), symbol: "GetWindowLongA" (+61h)
Address: 7E428E0Dh (USER32+18E0Dh), symbol: "DefWindowProcW" (+EDh)
Address: 7E42B372h (USER32+1B372h), symbol: "MoveWindow" (+D4h)
Address: 7E428DD9h (USER32+18DD9h), symbol: "DefWindowProcW" (+B9h)
Address: 7E428D77h (USER32+18D77h), symbol: "DefWindowProcW" (+57h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E428D90h (USER32+18D90h), symbol: "DefWindowProcW" (+70h)
Address: 7740A9E5h (COMCTL32+3A9E5h), symbol: "Ordinal384" (+1EB35h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E44215Bh (USER32+3215Bh), symbol: "DeregisterShellHookWindow" (+1E9Bh)
Address: 7E42B401h (USER32+1B401h), symbol: "CallNextHookEx" (+3Bh)
Address: 7E418734h (USER32+8734h), symbol: "GetDC" (+6Dh)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E418816h (USER32+8816h), symbol: "GetDC" (+14Fh)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E418830h (USER32+8830h), symbol: "GetDC" (+169h)
Address: 7E42A013h (USER32+1A013h), symbol: "IsWindowUnicode" (+A1h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E42A039h (USER32+1A039h), symbol: "CallWindowProcW" (+1Bh)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 028DF27Ah (foo_uie_sql_tree+1F27Ah)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 028DF192h (foo_uie_sql_tree+1F192h)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E418734h (USER32+8734h), symbol: "GetDC" (+6Dh)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E418816h (USER32+8816h), symbol: "GetDC" (+14Fh)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E42B326h (USER32+1B326h), symbol: "MoveWindow" (+88h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E418830h (USER32+8830h), symbol: "GetDC" (+169h)
Address: 7E428EA0h (USER32+18EA0h), symbol: "DefWindowProcW" (+180h)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E428EB0h (USER32+18EB0h), symbol: "DefWindowProcW" (+190h)
Address: 7E428EECh (USER32+18EECh), symbol: "DefWindowProcW" (+1CCh)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7C90E453h (ntdll+E453h), symbol: "KiUserCallbackDispatcher" (+13h)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E428E53h (USER32+18E53h), symbol: "DefWindowProcW" (+133h)
Address: 7E4194BEh (USER32+94BEh), symbol: "GetWindowLongA" (+61h)
Address: 7E42F5C6h (USER32+1F5C6h), symbol: "SetWindowTextA" (+5Bh)
Address: 7E42B326h (USER32+1B326h), symbol: "MoveWindow" (+88h)
Address: 7E428DD9h (USER32+18DD9h), symbol: "DefWindowProcW" (+B9h)
Address: 7E428D77h (USER32+18D77h), symbol: "DefWindowProcW" (+57h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E428D90h (USER32+18D90h), symbol: "DefWindowProcW" (+70h)
Address: 7740A9E5h (COMCTL32+3A9E5h), symbol: "Ordinal384" (+1EB35h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E41970Eh (USER32+970Eh), symbol: "IsChild" (+0h)
Address: 017D3F9Eh (foo_ui_columns+63F9Eh)
Address: 7E418734h (USER32+8734h), symbol: "GetDC" (+6Dh)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E418816h (USER32+8816h), symbol: "GetDC" (+14Fh)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E418830h (USER32+8830h), symbol: "GetDC" (+169h)
Address: 7E42A013h (USER32+1A013h), symbol: "IsWindowUnicode" (+A1h)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 7E42A039h (USER32+1A039h), symbol: "CallWindowProcW" (+1Bh)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 028DF27Ah (foo_uie_sql_tree+1F27Ah)
Address: 7740A559h (COMCTL32+3A559h), symbol: "Ordinal384" (+1E6A9h)
Address: 028DF192h (foo_uie_sql_tree+1F192h)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E418734h (USER32+8734h), symbol: "GetDC" (+6Dh)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E418816h (USER32+8816h), symbol: "GetDC" (+14Fh)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E418830h (USER32+8830h), symbol: "GetDC" (+169h)
Address: 7E4189CDh (USER32+89CDh), symbol: "GetWindowLongW" (+127h)
Address: 028DF150h (foo_uie_sql_tree+1F150h)
Address: 7E4191C6h (USER32+91C6h), symbol: "GetMessageW" (+0h)
Address: 7C90FE10h (ntdll+FE10h), symbol: "RtlSetLastWin32Error" (+0h)
Address: 7E44048Fh (USER32+3048Fh), symbol: "DeregisterShellHookWindow" (+1CFh)
Address: 7E4189F0h (USER32+89F0h), symbol: "GetWindowLongW" (+14Ah)
Address: 7E418A10h (USER32+8A10h), symbol: "DispatchMessageW" (+Fh)
Address: 0046EF01h (foobar2000+6EF01h)
Address: 0046FDB2h (foobar2000+6FDB2h)
Address: 0057EC58h (foobar2000+17EC58h)
Address: 0057EC58h (foobar2000+17EC58h)
Address: 0056C840h (foobar2000+16C840h)
Address: 0057EC3Ch (foobar2000+17EC3Ch)
Address: 7C9101BBh (ntdll+101BBh), symbol: "RtlAllocateHeap" (+117h)
Address: 7C90E900h (ntdll+E900h), symbol: "strchr" (+113h)
Address: 7C9101C0h (ntdll+101C0h), symbol: "RtlAllocateHeap" (+11Ch)
Address: 7C9101BBh (ntdll+101BBh), symbol: "RtlAllocateHeap" (+117h)
Address: 00539B0Dh (foobar2000+139B0Dh)
Address: 00540311h (foobar2000+140311h)
Address: 0047EE10h (foobar2000+7EE10h)
Address: 0053D990h (foobar2000+13D990h)
Address: 00540311h (foobar2000+140311h)
Address: 0055B08Bh (foobar2000+15B08Bh)
Address: 100027D8h (shared+27D8h), symbol: "uPrintCrashInfo_OnEvent" (+B1h)
Address: 00574AF4h (foobar2000+174AF4h)
Address: 1000281Eh (shared+281Eh), symbol: "uCallStackTracker::uCallStackTracker" (+31h)
Address: 00574AF4h (foobar2000+174AF4h)
Address: 0046FFCDh (foobar2000+6FFCDh)
Address: 00400000h (foobar2000+0h)
Address: 00543627h (foobar2000+143627h)
Address: 005AA3A0h (foobar2000+1AA3A0h)
Address: 0053D19Ah (foobar2000+13D19Ah)
Address: 00539DDFh (foobar2000+139DDFh)
Address: 00539DD9h (foobar2000+139DD9h)
Address: 005667A8h (foobar2000+1667A8h)
Address: 005667ACh (foobar2000+1667ACh)
Address: 00563F51h (foobar2000+163F51h)
Address: 0053D990h (foobar2000+13D990h)
Address: 00539DD9h (foobar2000+139DD9h)
Address: 0055B65Dh (foobar2000+15B65Dh)
Address: 0053AFD2h (foobar2000+13AFD2h)
Address: 00400000h (foobar2000+0h)
Address: 005419FFh (foobar2000+1419FFh)
Address: 0053D990h (foobar2000+13D990h)
Address: 7C817067h (kernel32+17067h), symbol: "RegisterWaitForInputIdle" (+49h)
Address: 7C839AC0h (kernel32+39AC0h), symbol: "ValidateLocale" (+2B0h)
Address: 7C817070h (kernel32+17070h), symbol: "RegisterWaitForInputIdle" (+52h)
Address: 0053B025h (foobar2000+13B025h)
Address: 0057005Ch (foobar2000+17005Ch)
Address: 004E0049h (foobar2000+E0049h)
Address: 004F0044h (foobar2000+F0044h)
Address: 00530057h (foobar2000+130057h)
Address: 0057005Ch (foobar2000+17005Ch)
Address: 005C0073h (foobar2000+1C0073h)

Environment:
App: foobar2000 v1.1.7
OS: Windows 5.1.2600 Service Pack 3 x86
CPU: Intel® Core™2 Quad CPU    Q9550  @ 2.83GHz, features: MMX SSE SSE2 SSE3 SSE4.1
Audio: Realtek HD Audio output
UI: Columns UI 0.3.8.8

Components:
Core (2011-06-05 09:16:20 UTC)
    foobar2000 core 1.1.7
foo_abx.dll (2009-06-07 13:25:26 UTC)
    ABX Comparator 1.3.4
foo_albumlist.dll (2011-06-05 09:14:24 UTC)
    Album List 4.5
foo_bitcompare.dll (2008-12-05 14:08:02 UTC)
    Binary Comparator 1.2
foo_bookmarks.dll (2011-01-23 12:46:40 UTC)
    Bookmarks 0.3.4.1
foo_cdda.dll (2011-06-05 09:14:20 UTC)
    CD Audio Decoder 3.0
foo_channel_mixer.dll (2011-06-01 23:18:00 UTC)
    Channel Mixer 0.9.6.7
foo_converter.dll (2011-06-05 09:13:50 UTC)
    Converter 1.5
foo_covers2.dll (2010-12-23 21:51:58 UTC)
    Locate Covers 0.06
foo_dbsearch.dll (2010-12-23 21:51:58 UTC)
    Database Search 1.4
foo_discogs.dll (2011-06-03 13:15:11 UTC)
    Discogs Tagger 1.26
foo_dsp_crossfeed.dll (2010-12-23 21:51:58 UTC)
    Crossfeed 1.1.1
foo_dsp_delta.dll (2008-10-24 05:57:16 UTC)
    Noise Sharpening DSP 1.0.0
foo_dsp_std.dll (2011-06-05 09:14:22 UTC)
    Standard DSP Array 1.0
foo_facets.dll (2011-08-18 19:06:16 UTC)
    Facets 1.0
foo_fileops.dll (2011-06-05 09:13:12 UTC)
    File Operations 2.1.3
foo_freedb2.dll (2011-06-05 09:13:14 UTC)
    freedb Tagger 0.6.4
foo_hdcd.dll (2011-02-22 06:28:28 UTC)
    HDCD decoder 1.11
foo_input_alac.dll (2011-02-26 11:53:14 UTC)
    ALAC Decoder 1.0.7
foo_input_dts.dll (2010-12-23 21:51:58 UTC)
    DTS decoder 0.3.0
foo_input_dtshd.dll (2010-10-18 12:17:02 UTC)
    DTS-HD Decoder 0.1.2
foo_input_dvda.dll (2011-03-11 16:49:08 UTC)
    DVD-Audio Decoder and Watermark Detector 0.4.7
foo_input_monkey.dll (2011-02-24 11:13:54 UTC)
    Monkey's Audio Decoder 2.1.5
foo_input_std.dll (2011-06-05 09:13:58 UTC)
    Standard Input Array 1.0
foo_input_tak.dll (2010-01-09 18:11:58 UTC)
    TAK Decoder 0.4.4
foo_input_tta.dll (2010-12-23 21:51:58 UTC)
    TTA Audio Decoder 3.2
foo_jesus.dll (2010-09-22 15:34:06 UTC)
    Autosave & Autobackup 10
foo_masstag.dll (2010-12-23 21:51:58 UTC)
    Masstagger 1.8.4
foo_out_asio.dll (2010-12-23 21:51:58 UTC)
    ASIO support 1.2.7
foo_out_ks.dll (2010-12-23 21:51:58 UTC)
    Kernel Streaming Output 1.2.2
foo_out_wasapi.dll (2010-12-23 21:51:58 UTC)
    WASAPI output support 2.1
foo_playcount.dll (2011-07-13 10:47:18 UTC)
    Playback Statistics 3.0.2
foo_quicksearch.dll (2011-05-29 09:26:56 UTC)
    Quick Search Toolbar 3.2
foo_quicktag.dll (2010-09-01 09:15:08 UTC)
    Quick Tagger 1.0.3
foo_rgscan.dll (2011-06-05 09:13:54 UTC)
    ReplayGain Scanner 2.1.2
foo_run.dll (2010-12-23 21:51:58 UTC)
    Run services 0.3.7
foo_runcmd.dll (2010-01-07 09:32:34 UTC)
    Run Command 1.1
foo_seek.dll (2011-07-20 20:13:32 UTC)
    Seek 1.0
foo_spdif.dll (2007-08-24 07:33:28 UTC)
    SPDIF support 1.3
foo_taskbar_gestures.dll (2011-02-23 02:28:06 UTC)
    TaskBar Gestures 2011-02-22
foo_texttools.dll (2010-12-23 21:51:58 UTC)
    Text Tools 1.0.5
foo_ui_columns.dll (2011-02-28 02:22:58 UTC)
    Columns UI 0.3.8.8
foo_ui_hacks.dll (2011-06-05 18:44:58 UTC)
    UI Hacks 2011-06-05
foo_ui_std.dll (2011-06-05 09:14:24 UTC)
    Default User Interface 0.9.5
foo_uie_console.dll (2010-12-23 21:51:58 UTC)
    Console panel 0.4
foo_uie_explorer.dll (2010-12-23 21:51:58 UTC)
    Explorer Tree 2.04.8
foo_uie_library_tree.dll (2011-07-24 17:08:24 UTC)
    Library Tree 0.3.4.6.1
foo_uie_panel_splitter.dll (2010-12-23 21:51:58 UTC)
    Panel Stack Splitter 0.3.8.3(alpha)
foo_uie_playlists_dropdown.dll (2009-09-23 20:44:46 UTC)
    Playlists Dropdown 0.7.6
foo_uie_ptb.dll (2008-02-16 07:45:34 UTC)
    Playback Toolbars 0.2
foo_uie_sql_tree.dll (2011-09-25 21:31:00 UTC)
    SQL Tree 1.0
foo_uie_tabs.dll (2009-05-13 11:46:00 UTC)
    Tabbed Panel Modified 0.2.8
foo_uie_vis_channel_spectrum.dll (2010-12-23 21:51:58 UTC)
    Channel Spectrum panel 0.17.2
foo_uie_vis_peakmeter_spectrum.dll (2010-03-07 08:24:48 UTC)
    Peakmeter Spectrum Visualisation 0.2.0.0 beta
foo_uie_wsh_panel_mod.dll (2011-03-21 05:09:12 UTC)
    WSH Panel Mod 1.4.3
foo_utils.dll (2010-12-23 21:51:58 UTC)
    Playlist Tools 0.6.2 beta 6
foo_verifier.dll (2009-10-05 10:39:20 UTC)
    File Integrity Verifier 1.1
foo_vst.dll (2011-03-05 07:19:04 UTC)
    VST 2.4 adapter 0.9.0.3
foo_wave_seekbar.dll (2011-09-11 05:28:10 UTC)
    Waveform seekbar 0.2.13.10

Recent events:
Opening track for playback: "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1987) cd. japan. toshiba emi (cp32-5108)\01.cue" / index: 7
Wave cache: finished analysis of "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1987) cd. japan. toshiba emi (cp32-5108)\01.cue" / index: 8
Opening track for playback: "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1987) cd. japan. toshiba emi (cp32-5108)\01.cue" / index: 8
Wave cache: finished analysis of "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1982) vinyl. uk original (pbthal 200911)\01.flac"
Opening track for playback: "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1982) vinyl. uk original (pbthal 200911)\01.flac"
Opening track for playback: "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1982) vinyl. uk original (pbthal 200911)\02.flac"
[foo_uie_sql_tree] Row limit (5000 rows) for the SQL tree console exceeded.
Opening track for playback: "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1982) vinyl. uk original (pbthal 200911)\03.flac"
Opening track for playback: "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1982) vinyl. uk original (pbthal 200911)\04.flac"
Wave cache: finished analysis of "F:\music\00\rock\iron maiden\(1982) the number of the beast\(1982) vinyl. uk original (pbthal 200911)\05.flac"
Title: foo_uie_sql_tree
Post by: ExUser on 2011-10-01 02:53:31
If there was a port of this to the Default UI, I'd use/test it.
Title: foo_uie_sql_tree
Post by: marc2003 on 2011-10-01 04:16:38
^from earlier in the thread....

any chance to get a dui version? :-)
No. But you can use this component with foo_popup_panels (http://www.foobar2000.org/components/view/foo_popup_panels) also in the Default UI.


obviously this would require that you have columns UI installed while you're using default UI.
Title: foo_uie_sql_tree
Post by: zaede on 2011-10-01 12:57:13
Very nice component !

How can i choose from which playlist the selection should be ?

Example:

Instead of medialibrary from the playlist All Music.

SELECT round(avg(play_count),2) as playcount, artist, album
    FROM Medialibrary (playlist_name, all Music)
GROUP BY album
ORDER BY playcount DESC

I think it`s this (Specify, that the virtual table implements access to the playlists. It can only be omitted for accessing the playlists, if the table name is the same as the parameter name) but I don`t understand.

Thanks
Peter
Title: foo_uie_sql_tree
Post by: grimes on 2011-10-01 13:07:49
You can make the selection by using a WHERE clause:

Code: [Select]
SELECT artist, album, round(avg(play_count),2) playcount
    FROM Playlist
WHERE playlist_name = "All Music"
GROUP BY album, artist
ORDER BY playcount DESC
Title: foo_uie_sql_tree
Post by: chiwou on 2011-10-01 13:42:54
small issue, I can't see anything if I add to my columns ui config, but it works in the popup panels
btw: it's the panel on the left side
(http://dl.dropbox.com/u/70350/Bugs/sqltree_bug.jpg)
Title: foo_uie_sql_tree
Post by: fbuser on 2011-10-01 14:39:45
btw: it's the panel on the left side
Did you remove the <Default> node? What happens, when you right click in the panel and choose "Create default nodes"?
Title: foo_uie_sql_tree
Post by: ExUser on 2011-10-01 20:24:52
^from earlier in the thread....

any chance to get a dui version? :-)
No. But you can use this component with foo_popup_panels (http://www.foobar2000.org/components/view/foo_popup_panels) also in the Default UI.


obviously this would require that you have columns UI installed while you're using default UI.

I read that, yeah. What I said still stands.
Title: foo_uie_sql_tree
Post by: marc2003 on 2011-10-01 20:57:49
oh right. it's just that no answer looks pretty unequivocal to me so i thought you must have missed it.
Title: foo_uie_sql_tree
Post by: grimes on 2011-10-01 22:10:21
Top artists played this year

Code: [Select]
SELECT COUNT(artist) artist_count, artist, strftime('%Y', last_played_timestamp / 10000000 - 11644473600, 'unixepoch') year
   FROM medialibrary
WHERE year = strftime('%Y','now')
GROUP BY artist
ORDER BY artist_count DESC


(only 'last played' considered)

Omitting WHERE clause: overall top artists played



Title: foo_uie_sql_tree
Post by: chiwou on 2011-10-01 22:33:26
btw: it's the panel on the left side
Did you remove the <Default> node? What happens, when you right click in the panel and choose "Create default nodes"?

yeah I tried that, I can see it on the redraw, so my guess is that everything is there, but it won't render correctly
Title: foo_uie_sql_tree
Post by: fbuser on 2011-10-01 23:10:21
btw: it's the panel on the left side
Did you remove the <Default> node? What happens, when you right click in the panel and choose "Create default nodes"?

yeah I tried that, I can see it on the redraw, so my guess is that everything is there, but it won't render correctly
Which settings do you use for the panel? Do you use the UI attributes or own attributes for the colors? In both cases, how do they look like?
Title: foo_uie_sql_tree
Post by: chiwou on 2011-10-01 23:44:06
I delete the database and settings from my profile folder, now it seems too work, but I'm gonna keep an eye on that
Title: foo_uie_sql_tree
Post by: ExUser on 2011-10-02 02:48:10
oh right. it's just that no answer looks pretty unequivocal to me so i thought you must have missed it.
Hope springs eternal in the human breast...
Title: foo_uie_sql_tree
Post by: aliendesaster on 2011-10-13 18:29:40
I love it. So far, did not have time to test it thoroughly, but I'll do.
Title: foo_uie_sql_tree
Post by: tg2k3 on 2011-10-14 00:58:29
is there any way to to search the library outside of foobar with this? e.g. having a another software querying the sqlite database?
Title: foo_uie_sql_tree
Post by: fbuser on 2011-10-14 15:54:21
In the tree console you could create a real database table (or you can also create a respective node in the tree, which does the same):

Code: [Select]
DROP TABLE IF EXISTS MediaLibrary_Snapshot;
CREATE TABLE MediaLibrary_Snapshot
AS SELECT * FROM MediaLibrary;


This database table can be queried with other software. Querying the virtual MediaLibrary table directly outside of foobar2000 is not possible.
Title: foo_uie_sql_tree
Post by: exalaskan on 2011-10-25 17:54:39
Just wanted to say thanks to fbuser for this fantastic component and figured I'd post my setup in case others might find it useful....

My use-case is I like random plays of my higher-rated music without repeats.

For the batch, I have this:

Code: [Select]
DROP VIEW IF EXISTS random_view4;

CREATE TEMP VIEW random_view4 AS
  SELECT last_played, MetaDB_Handle
    FROM medialibrary
  WHERE rating = 4
    AND last_played < '2011-09-09' AND ((julianday('now') - julianday(last_played)) > 42)
ORDER BY random()
  LIMIT 40;

DROP VIEW IF EXISTS random_view5;

CREATE TEMP VIEW random_view5 AS
  SELECT last_played, MetaDB_Handle
    FROM medialibrary
  WHERE rating = 5
    AND last_played < '2011-08-10' AND ((julianday('now') - julianday(last_played)) > 21)
ORDER BY random()
  LIMIT 20;

Simple enough. The dates and limits are arbitrary and work for me. Once I play through all of my 5's or 4's, I'll reset the date that gets compared to last_played to start the list over. The "julianday" portions are for when I reset the date -- this keeps recently-played tracks (three weeks for 5's and six for 4's) from ending up in the playlist.

The query is easy too:

Code: [Select]
SELECT * FROM random_view4
UNION
SELECT * FROM random_view5;

Many thanks to fbuser for the component.
Title: foo_uie_sql_tree
Post by: exalaskan on 2011-10-25 20:04:33
My use-case is I like random plays of my higher-rated music without repeats.

Update. I didn't realize I wasn't including tracks that haven't been played yet. Just change the last_played line to do a check against an empty string.

Code: [Select]
     AND (((last_played < '2011-09-09') AND ((julianday('now') - julianday(last_played)) > 42)) OR (last_played == ''))

(sorry for the quick update)
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-11-08 12:14:14
Lost all my queries!
I have just updated from 1.1.9 beta to 1.1.9 release and lost all my SQL tree queries.

I'm using SQL Tree within Panel splitter but all other panels okay. The SQL Tree panel contains nothing. Clicking 'Create example nodees' does nothing. Trying to add new folder or query calls up dialog form but after clicking OK - nothing displayed in the panel.

The only way to be able to re-create any folders or queries is to remove the panel from the config and then add a new one.

Any ideas? I could live with it if we could export\import queries!
Title: foo_uie_sql_tree
Post by: fbuser on 2011-11-08 18:07:35
Lost all my queries!
I have just updated from 1.1.9 beta to 1.1.9 release and lost all my SQL tree queries.
There is no relation between the release update and the loss of your queries. The problem you described can only happen, if your foo_ui_columns.dll.cfg file gets corrupted somehow. I'm aware, that there is a weak point in the way, it is currently implemented in case of such failures, but I don't have a better idea at the moment for a safer implementation. You should always have a proper backup process to overcome such situations.

The only way to be able to re-create any folders or queries is to remove the panel from the config and then add a new one.
Or you recover the following files from your backup (maybe you also use foo_jesus?): foo_ui_columns.dll.cfg, foo_uie_sql_tree.db, foo_uie_sql_tree.dll.cfg

I could live with it if we could export\import queries!
This might be implemented later for exchanging queries, but it does not substitute a proper backup process.
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-11-08 23:22:09
fbuser
Thanks for the info. I already use foo_jesus, I just didn't know what files to restore. At least there is a way out next time.

Just a small reminder. Any chance of a few common context menu items such as Properties?
Title: foo_uie_sql_tree
Post by: Zarty on 2011-11-09 12:19:45
Thanks for this component.  I'm having fun playing with it right now!  I have a question... Using SQL tree console, when I get a result from my queries that will consist of a short-list of tracks (Artist, Album, Title), is there a way to direct these track related results to the selection Playlist, a specified Playlist and/or the Playback Queue?

Cheers,
Zarty
Title: foo_uie_sql_tree
Post by: fbuser on 2011-11-09 17:35:17
Just a small reminder. Any chance of a few common context menu items such as Properties?
The item for Properties is already on the todo list for the next release, but I won't add any other items.

Using SQL tree console, when I get a result from my queries that will consist of a short-list of tracks (Artist, Album, Title), is there a way to direct these track related results to the selection Playlist, a specified Playlist and/or the Playback Queue?
No, this is not the purpose of the SQL tree console, that's what the panel is for, although sending to the playback queue is not supported and won't be in the future. The result rows in the console might match tracks one by one by coincidence, but generally they don't.
Title: foo_uie_sql_tree
Post by: Zarty on 2011-11-09 21:27:43
Quote
No, this is not the purpose of the SQL tree console, that's what the panel is for, although sending to the playback queue is not supported and won't be in the future. The result rows in the console might match tracks one by one by coincidence, but generally they don't.

Yes, now I see.  I looked at the panel initially but didn't click on the library nodes to see the playlist that is created.  This is even better than I thought.

Zarty
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-11-19 07:47:59
Lost all my queries ..... again!

Quote
There is no relation between the release update and the loss of your queries.


Well it happened the very next startup after installing 1.1.10 beta 1. However, thanks to info. supplied, I restores the three files and all my queries re-appeared.

Still a great plug-in and looking forward to next release.
Title: foo_uie_sql_tree
Post by: fbuser on 2011-11-19 08:25:07
Well it happened the very next startup after installing 1.1.10 beta 1.
That is strange. The relevant code was more or less implemetend from the beginning and I made several release updates during the development of the component and even after your first report on this without such a problem.
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-11-19 08:48:53
Hi fbuser
If it helps, I noticed that only the file foo_ui_columns.dll.cfg was a different size in the backup so it may not be your plug-in at all.
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-11-19 14:30:25
I don't mean to bother you (fbuser) and I know how reluctant you are to add additional features to this plug-in but if I don't ask I certainly won't get. So if you would be so kind as to consider the features below I would be greateful;

1) Context menu entry: Open containing folder
2) Options to not display a node icon
3) Ability to change vertical intra-node spacing (padding) and horizontal indentation
4) Allow sub-item and leaf count to be placed on right of node text
5) Provide additional double-click action configuration

I think that about does it for me and would make this plug-in perfect.

Thanks again for your time and effort in making this plug-in.
Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-11-20 08:05:58
Bug: The status of Start playback option for the middle click action isn't saved. If I check this and then restart it is back unchecked. I've not tested same for Click action.
Title: foo_uie_sql_tree
Post by: gob on 2011-12-07 06:14:56
I finally had a chance to test this component out. It looks promising for sure, a few more UI options and it would essentially obsolete the foo_playlist_tree component.

. A standard foobar2000 context menu for node children with the sql_tree context menu available via a modifier.
. Double click, enter, space bar actions
. Hide node icons
. Hide horizontal/vertical scroll bar
. Option to hide +/- for the root node
. Option to make the scroll bars stick to the very edge of the panel

Also, the undo options seem to be unusable after a playlist has been modified by foo_sql_tree.
Title: foo_uie_sql_tree
Post by: chiwou on 2011-12-13 17:56:57
Hi how can I display all albums that have the genre tag soundtrack

this is what I've have
Code: [Select]
SELECT genre "Genre" FROM MediaLibrary
WHERE genre LIKE 'soundtrack'

works great but only for albums which have 'Soundtrack' as their ONLY tag not for all with multiple values
Title: foo_uie_sql_tree
Post by: fbuser on 2011-12-13 19:02:36
Finally, some feedback to the feature requests above, although the requesters probably would like to get other answers.

1) Context menu entry: Open containing folder
2) Options to not display a node icon
3) Ability to change vertical intra-node spacing (padding) and horizontal indentation
4) Allow sub-item and leaf count to be placed on right of node text
5) Provide additional double-click action configuration

1) Won't implement
2) Not decided yet
3) Won't implement
4) Won't implement
5) Won't implement, because it is not possible to do it in a clean way. Either  the double click action would always also perform the click action or a delay for the click action needed to be defined to distinguish a click from a double click.

Bug: The status of Start playback option for the middle click action isn't saved. If I check this and then restart it is back unchecked. I've not tested same for Click action.
Thanks for reporting. Will be fixed with the next release. It only affects middle click action, not the click action.

. A standard foobar2000 context menu for node children with the sql_tree context menu available via a modifier.
Won't implement
. Double click, enter, space bar actions
Won't implement. The reason for double click, see above. Space is already bound to the click action, Enter to the edit command.
. Hide node icons
Not decided yet, see above.
. Hide horizontal/vertical scroll bar
Won't implement.
. Option to hide +/- for the root node
Won't implement. Be aware that, you can have multiple root nodes, not only one.
. Option to make the scroll bars stick to the very edge of the panel
I'm not sure, what you mean. I can see, that there is a glitch, which places the scrollbars two pixels next to the edges. This fill be fixed with the next release.

Also, the undo options seem to be unusable after a playlist has been modified by foo_sql_tree.
Will be changed for the next release.


Hi how can I display all albums that have the genre tag soundtrack

this is what I've have
Code: [Select]
SELECT genre "Genre" FROM MediaLibrary
WHERE genre LIKE 'soundtrack'

works great but only for albums which have the 'Soundtrack' as their ONLY tag not for all with multiple values
First of all your query is wrong for your purpose. The correct query would look like this:

Code: [Select]
SELECT genre "Genre" FROM MediaLibrary
WHERE genre LIKE '%soundtrack%'

But a better approach would be to define a new column for the MediaLibrary table in the preferences:
1. Go to Preferences -> Media Library -> SQL tree -> MediaLibrary table
2. Press Add
3. Define the new column with the following values:
  Type: Tag
  Name: genre_mv (or any other name you like)
  Tag -> Name: genre
  Split multivalue tag: Checked

Then your query would be:
Code: [Select]
SELECT genre_mv "Genre" FROM MediaLibrary
WHERE genre_mv='soundtrack'


Title: foo_uie_sql_tree
Post by: chiwou on 2011-12-13 23:43:29
thanks both tipps worked like a charm, do you know a good online documentation for sqlite, I would like to know the difference between '%string%' and 'string'
but if I use is to exclude all with soundtrack it doesn't work just to add != 'string' to the query, I really need to increase my sql skills  this is a great component to add additional organization in fb2k, thanks

btw small issue, the scrollbars are not add the borders of the window, there slightly (2px) inward

//Update
Nevermind
Code: [Select]
WHERE genre_mv != 'soundtrack' AND genre NOT LIKE '%soundtrack%'

this did trick
Title: foo_uie_sql_tree
Post by: chiwou on 2011-12-14 01:00:15
and after a few hours of testing the possibility to move queries in another folder would be great and maybe an autorefresh on startup for the queries with sub content
Title: foo_uie_sql_tree
Post by: chiwou on 2011-12-14 11:40:12
okay found the option for the refresh :| I just like to nag
Title: foo_uie_sql_tree
Post by: ainz on 2011-12-26 17:04:39
Superb component... an everything-in-one library viewer.
Drag-and-drop folder/query re-organization in the tree view would be very useful.
Title: foo_uie_sql_tree
Post by: ainz on 2011-12-26 17:10:15
thanks both tipps worked like a charm, do you know a good online documentation for sqlite
...


http://www.sqlite.org/lang_expr.html (http://www.sqlite.org/lang_expr.html)
Title: foo_uie_sql_tree
Post by: ainz on 2011-12-26 21:16:27
Code: [Select]
SELECT round(avg(rating),2) as rating, artist, album
    FROM medialibrary
GROUP BY album,artist
ORDER BY rating DESC


There's a small problem here relating to the queries generated for the rating child nodes in the tree.
The code generates a WHERE clause for the select parameter, e.g.
Code: [Select]
SELECT MetaDb_Handle
FROM medialibrary
WHERE CAST(round(avg(rating),2) AS TEXT) = '4.0'
ORDER BY rating DESC

... which gives an SQL error
Code: [Select]
SQLite Error: (1) misuse of aggregate function avg()


When the top-level SELECT uses an aggregate function, a GROUP BY / HAVING clause is needed for the SQL to be valid.
Title: foo_uie_sql_tree
Post by: fbuser on 2011-12-27 11:11:45
It's mentioned in the documentation (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/QueryNodeDialogAction.html#click_action)
Title: foo_uie_sql_tree
Post by: ainz on 2011-12-27 22:11:56
It's mentioned in the documentation (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/QueryNodeDialogAction.html#click_action)


I see. Is there an option to suppress generation of the child nodes for a particular query? It would be useful for queries like this.

On a different note, might it be possible to add a configurable click-action that sends the selection to a new playlist that's automatically named according to the query node (or chain of nodes)? I always create new playlists for groups of tracks I'm about to queue for playback... I suspect others may use foobar like this, too.
Title: foo_uie_sql_tree
Post by: mobyduck on 2011-12-28 08:09:42
Perhaps something like:
Code: [Select]
  SELECT q.rating, q.artist, q.album
    FROM (SELECT MetaDb_Handle, artist, album, round(avg(rating),2) as rating
            FROM medialibrary
        GROUP BY artist) AS q
ORDER BY q.rating DESC
HTH.

Alessandro

[Edit]Scratch that, sorry. Can't work.
Title: foo_uie_sql_tree
Post by: litolf on 2012-01-14 09:15:13
Hello fbuser,

i tried, to use your component, the installation seemed to succeed.
After restarting fb, SQL Tree was listed in the "Installed components", but ALL preference pages only showed "Please select a subpage".
When i closed fb, it(fb) crashed.

I run fb in linux with wine.
I tried many components and all of them (even wsh-panel_mod and foo_upnp) are working like a charm.

I started fb in a terminal, and searched for messages, which could give a hint, but could'nt find any usefull output.

I want to find out, if there is a chance, to use your component in a linux/wine-environment.

So my questions are:
Does your component use any undocumented windows-functions?
If true, there will be no chance, to use it in wine-environment.

Does your component use any other dll's in /system32 ?
If true, there might be a chance, because i can replace the "faked wine dll's" with native ones register them and tell wine, to use them.
I did it this way for wsh-panel_mod and it works.

I took a quick look at your component in a Windows-environment and think it's superb.
But, as i have no windows by my own, i would be very happy, if you could give some guidance, to use it in linux/wine.
Title: foo_uie_sql_tree
Post by: extracampine on 2012-01-14 10:48:35
You know, this is maybe a CRAZY idea....

....but any chance of some screenshots?   

Keen to see what this plugin looks like / what it can do!
Title: foo_uie_sql_tree
Post by: fbuser on 2012-01-14 11:01:49
After restarting fb, SQL Tree was listed in the "Installed components", but ALL preference pages only showed "Please select a subpage".
I doubt, that this has something to do with SQL Tree in the first place, when all preference pages are displayed wrongly. It looks for me more like a general problem.
Does your component use any undocumented windows-functions?
No.
Does your component use any other dll's in /system32 ?
No.


Title: foo_uie_sql_tree
Post by: fbuser on 2012-01-14 11:05:18
You know, this is maybe a CRAZY idea....

....but any chance of some screenshots?   

Keen to see what this plugin looks like / what it can do!
Might be reading the documentation (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/foo_uie_sql_tree_help.html) an idea? 
Title: foo_uie_sql_tree
Post by: extracampine on 2012-01-14 13:55:41
Lol....thanks.

But still...
Title: foo_uie_sql_tree
Post by: litolf on 2012-01-15 09:29:43
After restarting fb, SQL Tree was listed in the "Installed components", but ALL preference pages only showed "Please select a subpage".
I doubt, that this has something to do with SQL Tree in the first place, when all preference pages are displayed wrongly. It looks for me more like a general problem.
sorry, "ALL preference pages" meant "ALL SQL Tree preference pages"

To clarify this, i copied the complete fb-portable-Install from my Linux-machine to an USB-Disk an ran it on a WIN-XP-SP2-machine......no crash anymore, everything's OK there.
So it's a wine issue and i will post the bug to the wine-devs. To make their live easier, i made a fresh fb-install with CUI and SQL tree as the only components.
As expected, closing fb ends in a crash with following report.

Code: [Select]
Illegal operation:
Code: E06D7363h, flags: 00000001h, address: 7B839672h
Additional parameters: 19930520h 0033F7F4h 00584A04h
Message: Invalid path syntax
Last win32 error: 123

Call path:
entry=>initquit::on_quit=>filesystem::g_get_stats


Code bytes (7B839672h):
7B839632h:  60 8B 45 0C 8B 55 14 8B 75 08 8B 4D 10 83 E0 01
7B839642h:  89 45 AC 85 D2 8B 83 B8 FF FF FF 89 75 A8 C7 45
7B839652h:  B0 00 00 00 00 89 45 B4 74 04 85 C9 75 20 C7 45
7B839662h:  B8 00 00 00 00 8D 45 A8 89 04 24 E8 B6 CA FE FF
7B839672h:  83 EC 04 8D 65 F8 5B 5E 5D C2 10 00 66 90 83 F9
7B839682h:  0F B8 0F 00 00 00 0F 46 C1 89 45 B8 8D 4D BC C1
7B839692h:  E0 02 89 44 24 08 89 54 24 04 89 0C 24 E8 BC 4F
7B8396A2h:  FE FF EB C1 8D 76 00 8D BC 27 00 00 00 00 55 89

Stack (0033F6F4h):
0033F6D4h:  0033F7A8 00000001 0033F758 7BC71D58
0033F6E4h:  00000001 000028D8 7B839672 0033F700
0033F6F4h:  0033F784 0000000C 7BC46913 E06D7363
0033F704h:  00000001 00000000 7B839672 00000003
0033F714h:  19930520 0033F7F4 00584A04 7BC4773D
0033F724h:  00110000 00000002 7BC3511F 0033F7D0
0033F734h:  7BC48082 0092B060 7BCA6FF4 00110000
0033F744h:  7BCA6FF4 0033F7A8 7B83962A 00000000
0033F754h:  00932528 0033F790 0053E330 E06D7363
0033F764h:  00000001 00000003 0033F784 E06D7363
0033F774h:  00000001 00000000 00000000 00000003
0033F784h:  19930520 0033F7F4 00584A04 0033F884
0033F794h:  0052E42E 0033F7F4 00584A04 00932528
0033F7A4h:  0033F898 005803B4 000000B8 00000088
0033F7B4h:  0092B000 0092B000 7BC3511F 7BC3511F
0033F7C4h:  7B894FF4 0033F878 7BCA6FF4 7BCA6FF4
0033F7D4h:  0092B000 7BCA6FF4 0033F83C 7BC472E3
0033F7E4h:  0092B060 0092B000 00930A58 0033F820
0033F7F4h:  00580534 005803B4 C0000000 7BC4781A
0033F804h:  7BC479FD 009325E0 00932520 00000000

Registers:
EAX: 7B82612D, EBX: 7B894FF4, ECX: 00000000, EDX: E06D7363
ESI: E06D7363, EDI: 0033F8C0, EBP: 0033F758, ESP: 0033F6F4

Crash location:
Module: kernel32
Offset: 29672h
Symbol: "RaiseException" (+52h)

Loaded modules:
msimg32                          loaded at 69680000h - 6968E000h
foo_ui_std                      loaded at 014A0000h - 015B9000h
foo_input_std                    loaded at 01220000h - 01390000h
foo_uie_sql_tree                loaded at 00FE0000h - 0110A000h
usp10                            loaded at 69650000h - 6967A000h
mpr                              loaded at 69620000h - 69642000h
wininet                          loaded at 695C0000h - 6961C000h
urlmon                          loaded at 7BD30000h - 7BD94000h
foo_ui_columns                  loaded at 00D40000h - 00ED0000h
winealsa                        loaded at 694A0000h - 694BD000h
mmdevapi                        loaded at 69470000h - 69492000h
imm32                            loaded at 6F4B0000h - 6F4C6000h
winex11                          loaded at 68EF0000h - 68F84000h
crypt32                          loaded at 775F0000h - 7768B000h
ws2_32                          loaded at 71CC0000h - 71CEF000h
iphlpapi                        loaded at 6AED0000h - 6AEF0000h
netapi32                        loaded at 68E20000h - 68E47000h
secur32                          loaded at 68E00000h - 68E1C000h
oleaut32                        loaded at 68D10000h - 68DF0000h
gdiplus                          loaded at 68CA0000h - 68CFE000h
winspool                        loaded at 68C60000h - 68C8A000h
comdlg32                        loaded at 68B70000h - 68C50000h
psapi                            loaded at 68B60000h - 68B6E000h
dbghelp                          loaded at 68B00000h - 68B5A000h
imagehlp                        loaded at 737C0000h - 737CF000h
shared                          loaded at 10000000h - 1002B000h
msvcrt                          loaded at 711A0000h - 7121C000h
zlib1                            loaded at 5A4C0000h - 5A4D4000h
shell32                          loaded at 688F0000h - 68AFD000h
uxtheme                          loaded at 6FCD0000h - 6FCF4000h
shlwapi                          loaded at 68890000h - 688E7000h
msacm32                          loaded at 68860000h - 6887D000h
rpcrt4                          loaded at 687F0000h - 68855000h
ole32                            loaded at 686F0000h - 687DF000h
winmm                            loaded at 68640000h - 686D8000h
dsound                          loaded at 685F0000h - 68633000h
version                          loaded at 685E0000h - 685EE000h
advapi32                        loaded at 68580000h - 685D5000h
gdi32                            loaded at 6D1A0000h - 6D24B000h
user32                          loaded at 68450000h - 68575000h
comctl32                        loaded at 68350000h - 68435000h
kernel32                        loaded at 7B810000h - 7B9B7000h
ntdll                            loaded at 7BC10000h - 7BCC3000h
foobar2000                      loaded at 00400000h - 005FB000h

Stack dump analysis:
Address: 7BC46913h (ntdll+36913h), symbol: "HEAP_MakeInUseBlockFree" (+C3h)
Address: 7B839672h (kernel32+29672h), symbol: "RaiseException" (+52h)
Address: 00584A04h (foobar2000+184A04h)
Address: 7BC4773Dh (ntdll+3773Dh), symbol: "HEAP_FindFreeBlock" (+Dh)
Address: 7BC3511Fh (ntdll+2511Fh), symbol: "RtlLeaveCriticalSection" (+Fh)
Address: 7BC48082h (ntdll+38082h), symbol: "RtlReAllocateHeap" (+2D2h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7B83962Ah (kernel32+2962Ah), symbol: "RaiseException" (+Ah)
Address: 0053E330h (foobar2000+13E330h)
Address: 00584A04h (foobar2000+184A04h)
Address: 0052E42Eh (foobar2000+12E42Eh)
Address: 00584A04h (foobar2000+184A04h)
Address: 005803B4h (foobar2000+1803B4h)
Address: 7BC3511Fh (ntdll+2511Fh), symbol: "RtlLeaveCriticalSection" (+Fh)
Address: 7BC3511Fh (ntdll+2511Fh), symbol: "RtlLeaveCriticalSection" (+Fh)
Address: 7B894FF4h (kernel32+84FF4h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BC472E3h (ntdll+372E3h), symbol: "RtlFreeHeap" (+173h)
Address: 00580534h (foobar2000+180534h)
Address: 005803B4h (foobar2000+1803B4h)
Address: 7BC4781Ah (ntdll+3781Ah), symbol: "HEAP_FindFreeBlock" (+EAh)
Address: 7BC479FDh (ntdll+379FDh), symbol: "RtlAllocateHeap" (+Dh)
Address: 00539E2Ch (foobar2000+139E2Ch)
Address: 00539D72h (foobar2000+139D72h)
Address: 00402B45h (foobar2000+2B45h)
Address: 00521497h (foobar2000+121497h)
Address: 00402C9Fh (foobar2000+2C9Fh)
Address: 0044ACEBh (foobar2000+4ACEBh)
Address: 00570E20h (foobar2000+170E20h)
Address: 010A8B98h (foo_uie_sql_tree+C8B98h)
Address: 005546A0h (foobar2000+1546A0h)
Address: 0044B65Eh (foobar2000+4B65Eh)
Address: 0100807Dh (foo_uie_sql_tree+2807Dh)
Address: 005A67D8h (foobar2000+1A67D8h)
Address: 010A7253h (foo_uie_sql_tree+C7253h)
Address: 01007FB4h (foo_uie_sql_tree+27FB4h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 010A725Dh (foo_uie_sql_tree+C725Dh)
Address: 0100E8F4h (foo_uie_sql_tree+2E8F4h)
Address: 010B7DD8h (foo_uie_sql_tree+D7DD8h)
Address: 7BC479FDh (ntdll+379FDh), symbol: "RtlAllocateHeap" (+Dh)
Address: 010963C5h (foo_uie_sql_tree+B63C5h)
Address: 00FE85F0h (foo_uie_sql_tree+85F0h)
Address: 00FE8599h (foo_uie_sql_tree+8599h)
Address: 010B8A88h (foo_uie_sql_tree+D8A88h)
Address: 01096120h (foo_uie_sql_tree+B6120h)
Address: 00FE869Fh (foo_uie_sql_tree+869Fh)
Address: 00FE871Ah (foo_uie_sql_tree+871Ah)
Address: 010AE64Fh (foo_uie_sql_tree+CE64Fh)
Address: 00FE5242h (foo_uie_sql_tree+5242h)
Address: 010BB03Ch (foo_uie_sql_tree+DB03Ch)
Address: 010BB790h (foo_uie_sql_tree+DB790h)
Address: 0054F308h (foobar2000+14F308h)
Address: 7BC3511Fh (ntdll+2511Fh), symbol: "RtlLeaveCriticalSection" (+Fh)
Address: 00402DADh (foobar2000+2DADh)
Address: 00590414h (foobar2000+190414h)
Address: 010B7DD8h (foo_uie_sql_tree+D7DD8h)
Address: 010B7DD8h (foo_uie_sql_tree+D7DD8h)
Address: 014A713Ch (foo_ui_std+713Ch)
Address: 010AE6CAh (foo_uie_sql_tree+CE6CAh)
Address: 00FE972Ah (foo_uie_sql_tree+972Ah)
Address: 010DABD0h (foo_uie_sql_tree+FABD0h)
Address: 010BB03Ch (foo_uie_sql_tree+DB03Ch)
Address: 010BB790h (foo_uie_sql_tree+DB790h)
Address: 0150455Ch (foo_ui_std+6455Ch)
Address: 010B7DD8h (foo_uie_sql_tree+D7DD8h)
Address: 0047D7C5h (foobar2000+7D7C5h)
Address: 0042F448h (foobar2000+2F448h)
Address: 004BC938h (foobar2000+BC938h)
Address: 00FEA0E5h (foo_uie_sql_tree+A0E5h)
Address: 0042F448h (foobar2000+2F448h)
Address: 010AE861h (foo_uie_sql_tree+CE861h)
Address: 0046E5FAh (foobar2000+6E5FAh)
Address: 010DABD0h (foo_uie_sql_tree+FABD0h)
Address: 0054BF99h (foobar2000+14BF99h)
Address: 0046FBB9h (foobar2000+6FBB9h)
Address: 0053B4B5h (foobar2000+13B4B5h)
Address: 6D1B8E0Ah (gdi32+18E0Ah), symbol: "nulldrv_RestoreDC" (+49Ah)
Address: 6D234FF4h (gdi32+94FF4h)
Address: 00570E20h (foobar2000+170E20h)
Address: 00570E20h (foobar2000+170E20h)
Address: 7BC46913h (ntdll+36913h), symbol: "HEAP_MakeInUseBlockFree" (+C3h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 0057FD04h (foobar2000+17FD04h)
Address: 0057FCE8h (foobar2000+17FCE8h)
Address: 7BC47C36h (ntdll+37C36h), symbol: "RtlAllocateHeap" (+246h)
Address: 7BC464ABh (ntdll+364ABh), symbol: "validate_block_pointer" (+2Bh)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BC3511Fh (ntdll+2511Fh), symbol: "RtlLeaveCriticalSection" (+Fh)
Address: 7BC3511Fh (ntdll+2511Fh), symbol: "RtlLeaveCriticalSection" (+Fh)
Address: 00539E2Ch (foobar2000+139E2Ch)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 00540996h (foobar2000+140996h)
Address: 0053DE20h (foobar2000+13DE20h)
Address: 00540996h (foobar2000+140996h)
Address: 0053E6B8h (foobar2000+13E6B8h)
Address: 0053B4B5h (foobar2000+13B4B5h)
Address: 0055BCDBh (foobar2000+15BCDBh)
Address: 100027D8h (shared+27D8h)
Address: 00575C44h (foobar2000+175C44h)
Address: 0053B4B5h (foobar2000+13B4B5h)
Address: 1000281Eh (shared+281Eh)
Address: 00575C44h (foobar2000+175C44h)
Address: 0046FD2Dh (foobar2000+6FD2Dh)
Address: 00400000h (foobar2000+0h)
Address: 0053B4B5h (foobar2000+13B4B5h)
Address: 7B894FF4h (kernel32+84FF4h)
Address: 00543B27h (foobar2000+143B27h)
Address: 005AB400h (foobar2000+1AB400h)
Address: 0053D62Ah (foobar2000+13D62Ah)
Address: 0053A0FEh (foobar2000+13A0FEh)
Address: 0053A0F8h (foobar2000+13A0F8h)
Address: 005677D4h (foobar2000+1677D4h)
Address: 005677D8h (foobar2000+1677D8h)
Address: 7B894FF4h (kernel32+84FF4h)
Address: 00564471h (foobar2000+164471h)
Address: 0053DE20h (foobar2000+13DE20h)
Address: 0053A0F8h (foobar2000+13A0F8h)
Address: 0055BF5Dh (foobar2000+15BF5Dh)
Address: 0053B462h (foobar2000+13B462h)
Address: 00400000h (foobar2000+0h)
Address: 0053B4B5h (foobar2000+13B4B5h)
Address: 7B894FF4h (kernel32+84FF4h)
Address: 0053DE20h (foobar2000+13DE20h)
Address: 7B85961Ch (kernel32+4961Ch), symbol: "call_process_entry" (+Ch)
Address: 7BC4BD3Ah (ntdll+3BD3Ah), symbol: "RtlImageNtHeader" (+Ah)
Address: 7B894FF4h (kernel32+84FF4h)
Address: 7B85A88Fh (kernel32+4A88Fh), symbol: "start_process" (+5Fh)
Address: 0053B4B5h (foobar2000+13B4B5h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BC71D90h (ntdll+61D90h), symbol: "call_thread_func_wrapper" (+Ch)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BC7484Dh (ntdll+6484Dh), symbol: "call_thread_func" (+7Dh)
Address: 7B85A830h (kernel32+4A830h), symbol: "start_process" (+0h)
Address: 7BC89300h (ntdll+79300h), symbol: "__wine_exception_handler" (+0h)
Address: 7B8396B0h (kernel32+296B0h), symbol: "UnhandledExceptionFilter" (+0h)
Address: 7BCA6FF4h (ntdll+96FF4h)
Address: 7BC747D9h (ntdll+647D9h), symbol: "call_thread_func" (+9h)
Address: 6814DFF4h (libwine.so.1+12DFF4h)
Address: 7BC71D6Eh (ntdll+61D6Eh), symbol: "RtlRaiseException" (+22h)
Address: 7B85A830h (kernel32+4A830h), symbol: "start_process" (+0h)
Address: 6814DFF4h (libwine.so.1+12DFF4h)
Address: 7BC49F3Eh (ntdll+39F3Eh)
Address: 7B85A830h (kernel32+4A830h), symbol: "start_process" (+0h)
Address: 680296ADh (libwine.so.1+96ADh), symbol: "wine_call_on_stack" (+1Dh)
Address: 7B85A830h (kernel32+4A830h), symbol: "start_process" (+0h)
Address: 01000000h (foo_uie_sql_tree+20000h)
Address: 01000000h (foo_uie_sql_tree+20000h)
Address: 01000000h (foo_uie_sql_tree+20000h)
Address: 01000000h (foo_uie_sql_tree+20000h)
Address: 01000000h (foo_uie_sql_tree+20000h)
Address: 01000000h (foo_uie_sql_tree+20000h)
Address: 01000000h (foo_uie_sql_tree+20000h)
Address: 681A1311h (libc.so.6+24311h)

Environment:
App: foobar2000 v1.1.10
OS: wine-1.3.36, on: Linux / 3.0.0-14-generic
CPU: Intel® Pentium® M processor 1700MHz, features: MMX SSE SSE2
Audio: default; Intel 82801DB-ICH4 - Intel 82801DB-ICH4; Intel 82801DB-ICH4 - Intel 82801DB-ICH4 - IEC958
UI: Columns UI 0.3.8.8

Components:
Core (2011-12-02 15:07:36 UTC)
    foobar2000 core 1.1.10
foo_input_std.dll (2011-12-02 15:05:54 UTC)
    Standard Input Array 1.0
foo_ui_columns.dll (2012-01-15 08:27:12 UTC)
    Columns UI 0.3.8.8
foo_ui_std.dll (2011-12-02 15:06:24 UTC)
    Default User Interface 0.9.5
foo_uie_sql_tree.dll (2012-01-15 08:31:40 UTC)
    SQL Tree 1.0.2

Recent events:
Watching: C:\users\me\Eigene Musik
Startup time : 0:00.836293
Shutting down...

Is there any additional advice from you i can forward to the wine-devs ?
Title: foo_uie_sql_tree
Post by: fbuser on 2012-01-15 21:14:37
sorry, "ALL preference pages" meant "ALL SQL Tree preference pages"
That is strange. The preference pages are integrated straight forward via implementing some interfaces. If there was something wrong with the implementation, I would expect, that it fails either on each platform or not at all.

Code: [Select]
Illegal operation:
Code: E06D7363h, flags: 00000001h, address: 7B839672h
Additional parameters: 19930520h 0033F7F4h 00584A04h
Message: Invalid path syntax
Last win32 error: 123

Call path:
entry=>initquit::on_quit=>filesystem::g_get_stats
Is there any additional advice from you i can forward to the wine-devs ?
I can only guess here. The function filesystem::g_get_stats is not called directly from SQL Tree. However, it is called indeed indirectly in initquit:on_quit. A check, if the database file exists is possibly done here, where the file name is an URL of the form: file:///<path to configuration directory>/foo_uie_sql_tree.db. But if the crash is really related to this, I would expect it already on program start, as the metionend check is actually done there and should not be done anymore on program shut down.

Anyway, I uploaded a new version of SQL Tree, which removes an additional parameter from the URL, which is actually not needed for checking the existence of the database file. Maybe this helps.
Title: foo_uie_sql_tree
Post by: litolf on 2012-01-16 09:27:54
Anyway, I uploaded a new version of SQL Tree, which removes an additional parameter from the URL, which is actually not needed for checking the existence of the database file. Maybe this helps.

Yea, it does!!!
Thanx a lot!!!

To close the case, i made some additional tests:
Normal-/Portable-fb-Install with CUI & SQL Tree only on wine stable release -> works
Normal-/Portable-fb-Install with CUI & SQL Tree and  additional components* on wine 1.2 stable release -> works
Normal-/Portable-fb-Install with CUI & SQL Tree and  additional components* on wine 1.3 latest developer release -> works

The above test-cases were made on Ubuntu 10.04 LTS (Gnome) and Lubuntu 11.10 (OpenBox).
So, it can be supposed, that your component works in all up-to-date linux/wine environments.
With this in mind, you can be shure, i'm not the only guy, you made happy.

Thanx again!

Title: foo_uie_sql_tree
Post by: litolf on 2012-01-17 09:42:59
@fbuser

---feedback---
Being glad, SQL Tree works in wine now, it took only 20 minutes to port all my TF-"queries" (8 queries with about 5 levelsl) to proper SQL-queries and setup a nice SQL Tree with different node-apearances according to their level.
For me, the UI of SQL Tree is selfexplaining and the backend does it's job properly.
The ability, to make a snapshot of an existing Medialibrary in a relational database, which is accessible outside fb opens horizons for SQL-devs.
The ability of per node settings for icon,colour and font is a great improvement to CUI's AlbumListPanel, which does its job but becomes a textdesert with increasing nodedepth.

---feature request---
But at the moment for my setup there is one showstopper, for a complete replacement of CUI's AlbumListPanel.
My setup heavily relies on the ability of an libraryviewer, to propagate the selection event (eventsink is in several in wsh_panel_mod scripts -> on_selection_changed(metadb)).

Usecase:
On the left side of my ui resides a libraryviewer.
The right side is horizontally divided in 2 parts.
An upper section to view all info about the nowplaying track.
A lower section to view all info about the selected item in the libraryviewer.
The sense of this:
You can hear Symphony 1, read in the upper section it's workdescription, which may have some hint to another work or composer.
Now you can select this other work or composer in the libraryviewer and read the according info in the lower section.

In summary this is a feature request of my SelectionViewers to get the according event from your SQl Tree View. 
Title: foo_uie_sql_tree
Post by: ainz on 2012-01-19 03:09:53
Here are a couple of queries I'm using, for anyone else that might find them useful...

Artist / Release
Code: [Select]
SELECT
    coalesce([album artist],artist,'.Unknown Artist') album_artist
    ,ifnull(substr(date,1,4),'Undated')
        ||' – '||ifnull(album,'Untitled') release
FROM MediaLibrary
ORDER BY
    coalesce([album artist],artist,'.Unknown Artist') COLLATE NaturalNoCase
    ,ifnull(substr(date,1,4),'Undated')
        ||' – '||ifnull(album,'Untitled') COLLATE NaturalNoCase desc
    ,discnumber,tracknumber


Style / Artist / Release
Code: [Select]
SELECT
    coalesce([style],genre,'~Unknown Style') genrestyle
    ,coalesce([album artist],artist,'Unknown Artist') album_artist
    ,ifnull(substr(date,1,4),'Undated')
        ||' – '||ifnull(album,'Untitled') release
FROM MediaLibrary
ORDER BY
    coalesce([style],genre,'~Unknown Style')
    ,coalesce([album artist],artist,'Unknown Artist') COLLATE NaturalNoCase
    ,ifnull(substr(date,1,4),'Undated')
        ||' – '||ifnull(album,'Untitled') COLLATE NaturalNoCase desc
    ,discnumber,tracknumber


Year / Artist / Release
Code: [Select]
SELECT
    ifnull(substr(date,1,4),'.Undated') date_col
    ,coalesce([album artist],artist,'Unknown Artist') album_artist
    ,ifnull(substr(date,1,4),'.Undated')
        ||' – '||ifnull(album,'Untitled') release
FROM MediaLibrary
ORDER BY
    ifnull(substr(date,1,4),'.Undated') desc
    ,coalesce([album artist],artist,'Unknown Artist') COLLATE NaturalNoCase
    ,ifnull(substr(date,1,4),'.Undated')
        ||' – '||ifnull(album,'Untitled') COLLATE NaturalNoCase desc
    ,discnumber,tracknumber
Title: foo_uie_sql_tree
Post by: litolf on 2012-01-20 12:24:50
Could'nt wait to see SQL Tree on my productive sytem (55"-screen) in the livingroom.
So i found a workaround for those, who have a setup with wsh-panel-mod, containing code, which relies on the ability of an libraryviewer, to propagate the selection event, delivering a metadb-object.
If anyone is interested, i will post it. (here?)
As every workaround has drawbacks, my feature request for the selection-event is still topical.


Bugreport
Bug: Altering the "SQL query" in tab "Query" query, results in loss of existing settings in tab "Action"
Bug: Altering settings for Action does'nt enable the OK Button, i first have to click on tab "Query", then OK Button is enabled to save settings for actions.
Bug: Click-action is set to: "Send to targetplaylist" A query like below, does'nt send anything to the pl, clicking the Node containing the concatenation "werkartist" or below:
Code: [Select]
SELECT land,composer,genre,gattung,werk || ':' || artist AS werkartist,title
FROM Medialibrary
GROUP BY land,composer,genre,gattung,werkartist,title


---------------------------------------------------------
i am using fb/SQL Tree in linux/wine environment
Title: foo_uie_sql_tree
Post by: fbuser on 2012-01-20 16:04:51
Bug: Altering the "SQL query" in tab "Query" query, results in loss of existing settings in tab "Action"
Not reproducible. Works fine here.

Bug: Altering settings for Action does'nt enable the OK Button, i first have to click on tab "Query", then OK Button is enabled to save settings for actions.
The first part is not a bug, the second is not reproducible.

The OK Button is only disabled, when "SQL query" in the "Query" tab  is empty and "SQL batch" in the "Batch" tab is empty as well or "Label" in the "Main" tab is empty.

Altering the Action settings will not change the status of the OK Button. Also just clicking on the "Query" tab will not change the status of the OK Button.

Bug: Click-action is set to: "Send to targetplaylist" A query like below, does'nt send anything to the pl, clicking the Node containing the concatenation "werkartist" or below:
Not reproducible. Works fine here. Is there any error message in the console?
Title: foo_uie_sql_tree
Post by: litolf on 2012-01-20 20:00:57
Bug: Click-action is set to: "Send to targetplaylist" A query like below, does'nt send anything to the pl, clicking the Node containing the concatenation "werkartist" or below:
Not reproducible. Works fine here. Is there any error message in the console?
Just for clarification

Code: [Select]
SELECT land,composer,genre,gattung,werk || ':' || artist AS werkartist,title
FROM Medialibrary
GROUP BY land,composer,genre,gattung,werkartist,title

click on :
land -> works
composer -> works
genre -> works
gattung -> works
werkartist -> does'nt work
title -> does'nt work
No error message in the console!

I think, i will borow a native Windows-system, to verify wether this and any future assumed bug may be a wine issue.
If no one is averse of this, i will post "bugs", even though they only occure in wine-environment (with hint to this), because a few posts ago this helped, that SQL Tree runs in wine at all.
Title: foo_uie_sql_tree
Post by: fbuser on 2012-01-20 21:37:48
Bug: Click-action is set to: "Send to targetplaylist" A query like below, does'nt send anything to the pl, clicking the Node containing the concatenation "werkartist" or below:
Oops, I overread, that it only happens for the concatenated column. But this is not a bug. It is a limitation of the parser as written in the documentation (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/QueryNodeDialogQuery.html):

Quote
For a proper parsing of the select list, expressions containing spaces must be enclosed in parentheses (e.g. case-when expressions)
Either omit the spaces in the expression: werk||':'||artist AS werkartist
or enclose the expression in parantheses as mentioned in the documentation: (werk || ':' || artist) AS werkartist
Title: foo_uie_sql_tree
Post by: ainz on 2012-01-20 22:30:19
So the spaces/parentheses restriction was preventing the leaf nodes in my queries from working.

The cursor keys can be used to quickly expand/collapse and navigate a large tree, but the contents of the target playlist don't change unless the mouse button is clicked. Is this related to the selection-event propagation mentioned previously? It's odd for the behaviour to differ between keyboard and mouse input.
Title: foo_uie_sql_tree
Post by: litolf on 2012-01-20 22:52:07
Thanx again to fbuser and shame on me for not reading the doc carefully.
Omitting the spaces in the expression makes the query working as expected.
Title: foo_uie_sql_tree
Post by: fbuser on 2012-01-20 23:05:40
The cursor keys can be used to quickly expand/collapse and navigate a large tree, but the contents of the target playlist don't change unless the mouse button is clicked.
Or <space> is pressed for the click action, respectively <ctrl>+<space> for the middle click action. It was missing in the documentation.
Title: foo_uie_sql_tree
Post by: ainz on 2012-01-21 01:06:24
Or <space> is pressed for the click action, respectively <ctrl>+<space> for the middle click action. It was missing in the documentation.


OK, so it's different to the Old Library Viewer in this respect by design. It would be easier to flick through library content (viewing each node's content in the target playlist) using cursor keys alone, though (e.g. on a HTPC using a remote). This behaviour wouldn't suit slow/complex queries, so there could be an option to set the behaviour at the query level...
Title: foo_uie_sql_tree
Post by: camperdave on 2012-03-27 18:52:21
Hey, I've been having some issues with this plugin lately. Namely, none of my queries or folders show up in its panel!

If I right click on the SQL Tree panel and click "Create Example Nodes" the panel flashes briefly, but I can't see what it says. After that quick flash, the panel remains blank. No matter how I refresh or attempt to add queries and folders, I can never get anything to appear in the list of queries.

All of the dialogs to create queries however seem to come up ok?

Sorry it's not much to go on, but any advice on fixing this would be much obliged.
Title: foo_uie_sql_tree
Post by: fbuser on 2012-03-27 19:35:27
Look here (http://www.hydrogenaudio.org/forums/index.php?s=&showtopic=90978&view=findpost&p=774921) and the following.
Title: foo_uie_sql_tree
Post by: camperdave on 2012-03-27 20:09:07
Look here (http://www.hydrogenaudio.org/forums/index.php?s=&showtopic=90978&view=findpost&p=774921) and the following.


Thanks, removing and re-adding the panel (not the component) worked just fine!
Title: foo_uie_sql_tree
Post by: r0k on 2012-05-04 17:05:36
Hello.
I'm trying to make a node to rertrieve only music located in my main library folder, and excluding other folders containing recently ripped or downloaded music. I'm using WHERE to achieve this. However the node doesn't list any album at all.
This is the batch part of the node.
Code: [Select]
DROP VIEW IF EXISTS album_query;
CREATE VIEW album_query AS
SELECT path,
       upper(substr(tf(metadb_handle,'$stripprefix(%album artist%)'),1,1)) first_letter,
       ifnull([album artist],artist) album_artist,
       '['||date||'] '||album album_col,
       (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number,
       MetaDb_Handle
FROM MediaLibrary
WHERE path LIKE 'e:\music%'
GROUP BY 1,album,discnumber
ORDER BY album_artist, album_col, disc_number, tracknumber

And the query
Code: [Select]
SELECT first_letter,
       album_artist,
       album_col
FROM album_query

I took most of the lines from the examples, only adding the WHERE clause after reading some SQLite documentation.
Title: foo_uie_sql_tree
Post by: fbuser on 2012-05-04 17:52:38
Have a look at the foobar2000 console. You should see there an error message starting with:

Execution error:
SQLite Error: (1) unable to use function tf in the requested context

The reason is mentioned in the comments in the batch part of the example you referred to:
Quote
- When the tf() function is used, no group-by clause can be used, due to a limitation of SQLite

Therefore you need to remove the GROUP BY clause from your view definition.

Further the column path is not required in your view definition although it doesn't do any harm.  Also as you don't use the discnumber for display purposes, you can simplify the usage in your view definition for it.

So, your batch part of the node could look like this:
Code: [Select]
DROP VIEW IF EXISTS album_query;
CREATE VIEW album_query AS
SELECT upper(substr(tf(metadb_handle,'$stripprefix(%album artist%)'),1,1)) first_letter,
       ifnull([album artist],artist) album_artist,
       '['||date||'] '||album album_col,
       MetaDb_Handle
FROM MediaLibrary
WHERE path LIKE 'e:\music%'
ORDER BY album_artist, album_col,discnumber,tracknumber


BTW, it is always a good idea to run the SELECT part of a view definition inside the SQL tree console to easily detect and correct errors.
Title: foo_uie_sql_tree
Post by: r0k on 2012-05-04 21:04:32
Thanks. I merged parts of several examples and lost track of the tips doing this and since my SQL experience is now exactly one full day long i was a little lost 
I managed to keep the ability to group by getting the first letter directly in the table with this titleformat column. This also allows me to remove accented first letters.
Code: [Select]
$puts(_name,$if2($meta(album artist),$meta(artist)))
$puts(_name,$lower($get(_name)))
$puts(_name,$replace($get(_name),é,e,è,e,ê,e,à,a,â,a))
$puts(_name,$caps($get(_name)))
$left($get(_name),1)

Thanks for this great component. It adds a lot of possibilities to fb2k 

Oh, btw, i noticed that if i put two SQLtrees in different layouts, they both contain the same tree structure. When i tried yesterday i had two different trees but then i got the corrupted cfgs bug  . Well, now i'm rebuilding a new config from scratch and installed foo_jesus. My old config was mostly panel UI anyway so there were no big loss.
What bothers me is, what's the normal behaviour for several SQL trees. Is it possible to create different trees by running different instances of the panel in different layouts or not? Is it even safe to run several instances of SQL tree in different layouts or not?
Title: foo_uie_sql_tree
Post by: r0k on 2012-05-13 16:03:01
Multiple Libraries?

Hello. This may be a feature request but maybe it's already possible. I would like to be able to create multiple SQL tables similar to the MediaLibrary. Multiple libraries have been a long lasting request for foobar2000 and your plugin have the power to make them available, at least to a certain point.
Now, i know i can use views to simulate multiple tables, i've spend some time studying SQLite those last days, however Views are actually sub-requests so i guess there is a performance cost at using views instead of multiple tables for multiple libraries.

Since there is not a lot of documentation on how the MetaDb_Module works, i don't know how i could use it to create custom tables, if possible at all. By custom tables, i don't mean tables with different columns, i guess this is impossible without changing some code, but at least tables that would filter parts of the foobar library.

Full custom tables with different columns would be great if you ever happen to have time to kill 
I know that suggesting is easier than coding so i never complain when my suggestions are ignored.

BTW, did you see my last post or did you miss the questions i asked after editing it?
Title: foo_uie_sql_tree
Post by: fbuser on 2012-05-13 21:43:03
Oh, btw, i noticed that if i put two SQLtrees in different layouts, they both contain the same tree structure. When i tried yesterday i had two different trees but then i got the corrupted cfgs bug
Ah, I never thought about this. Of course, this is the reason, why this problem is reported here quite often, but I don't have these problems: I never use different layouts. This will be fixed with the next release. Until then it is not possible to use SQL Tree in different layouts.

Multiple libraries have been a long lasting request for foobar2000 and your plugin have the power to make them available, at least to a certain point.
Only in that way, that you can define views with a defined filter.

Now, i know i can use views to simulate multiple tables, i've spend some time studying SQLite those last days, however Views are actually sub-requests so i guess there is a performance cost at using views instead of multiple tables for multiple libraries.
Regarding the performance costs, you are guessing right. However, there is no other way to separate parts of the media library. Defining multiple tables is only necessary in very special scenarios. Normally, you don't need them. Especially, you can't use them for filtering. For filtering you need to use views.

but at least tables that would filter parts of the foobar library.
Even, if it would be possible to define tables, which filter parts of the library, the filtering had to be done in a similar way as for views, although it would be probably a bit faster doing this internally without using views. But probably not that faster, that it would be worth to implement it.

Full custom tables with different columns would be great
As mentioned above, that's what views are for.
Title: foo_uie_sql_tree
Post by: r0k on 2012-05-14 08:42:44
Even, if it would be possible to define tables, which filter parts of the library, the filtering had to be done in a similar way as for views, although it would be probably a bit faster doing this internally without using views. But probably not that faster, that it would be worth to implement it.

OK, fair enough.

I removed SQLtree from my second layout. I'll use another library browser there for now.
Title: foo_uie_sql_tree
Post by: r0k on 2012-05-18 16:30:26
Mhh. Sorry to bother you again but i have some troubles with this component.
It have worked nicely for quite some time, but since i started working on my layouts again yesterday, i have nothing but corrupt cfgs all the time.
Fortunately i have foo_jesus watching my back but i keep restoring backups. What's worse, even backups from a moment where your sql_tree was working won't necessarily restore it. Actually only backups from 2 days ago are sure to restore it (backup is done each time fb2k starts).
I can get sql_tree working again simply by restoring foo_uie_sql_tree.db and foo_uie_sql_tree.dll.cfg fortunately but it's still quite annoying.
I havn't been able to surely identify another component causing issues. I first thought the issue was caused by me trying to use ELplaylist but i removed it yesterday (uninstalled the component) and had to resore sql_tree again several times today.

There is definitely something wrong, i hope you can find out what as it's a great component. At worst i will remove it while i'm messing with layouts and install it back after i'm done as it seems to be more stable when layout isn't edited. It would help if you include a way to export and import node queries (well, i can still copy-paste to a text file)

Let me know if there is something i can do to help you find what's wrong, i realize this post is not very helpful as a bug report.
Title: foo_uie_sql_tree
Post by: D.Sync on 2012-05-23 09:49:41
What query should I type if I want to search for incomplete albums. E.g. the total number of tracks in an album IS NOT THE SAME as the totaltracks tag field. I had did some modification on the sample query as follows but the 'count' column shows the total tracks for an album (i.e. all tracks from all discs for that album) whereas the 'total_tracks' column only show the total tracks for Disc 1. So what I intend to do is for the 'total_tracks' column to show all the tracks number for all discs for an album.

Code: [Select]
SELECT [b]album[/b],
       count(*) count,
       max(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY (total_tracks-count) desc


For example,
Akumajo Dracula Best Music Collections BOX spans across 18 Discs, but the above query shows:
'490','Akumajo Dracula Best Music Collections BOX','1149','59' // 59 is the total tracks for disc 1

What I want is to show the total tracks for all discs.
'490','Akumajo Dracula Best Music Collections BOX','1149','1149' // 1149 is the total tracks for all 18 discs.

Title: foo_uie_sql_tree
Post by: r0k on 2012-05-23 14:18:30
I have changed my totaltracks to be the grand total for multi-disc albums. Easy and ugly 
You might also try this :
SELECT album,
     count(*) count,
     maxsum(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY (total_tracks-count) desc
Title: foo_uie_sql_tree
Post by: fbuser on 2012-05-23 17:37:01
What I want is to show the total tracks for all discs.
'490','Akumajo Dracula Best Music Collections BOX','1149','1149' // 1149 is the total tracks for all 18 discs.

Try
Code: [Select]
SELECT album,discnumber,
       count(*) count,
       max(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1,2
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY (total_tracks-count) desc


Title: foo_uie_sql_tree
Post by: D.Sync on 2012-05-24 06:19:14
What I want is to show the total tracks for all discs.
'490','Akumajo Dracula Best Music Collections BOX','1149','1149' // 1149 is the total tracks for all 18 discs.

Try
Code: [Select]
SELECT album,discnumber,
       count(*) count,
       max(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1,2
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY (total_tracks-count) desc



Thanks that actually did it.
Title: foo_uie_sql_tree
Post by: fbuser on 2012-06-03 18:41:27
Let me know if there is something i can do to help you find what's wrong, i realize this post is not very helpful as a bug report.
Never mind. I knew the reason anyway after you mentioned, that there are problems with different layouts. It should be fixed now.
Title: foo_uie_sql_tree
Post by: r0k on 2012-06-03 20:58:31
Great. I have updated it and so far i can switch layouts without loosing my queries 
Does the update to SQLite 3.7.12.1 affects existing queries?
Thanks for the update.
Title: foo_uie_sql_tree
Post by: fbuser on 2012-06-03 21:14:49
Does the update to SQLite 3.7.12.1 affects existing queries?
No, you can find a change log for SQLite here (http://www.sqlite.org/changes.html).
Title: foo_uie_sql_tree
Post by: akispavlopoulos on 2012-06-29 17:37:29
Hi!

I've just found this plugin. I don't know anything about SQL, or what exactly this plugin does but I want to ask if it can "export" foobar's database in SQL type. I have found a software for my media player that can take any sql type database and make html views in my media player. So is this possible with this plugin and how?

Thanks!
Title: foo_uie_sql_tree
Post by: m00zikD00d on 2012-09-14 00:28:54
OK, I am trying to figure out how to get this component to work properly, but I have no experience with SQL Statements....  My music has sub genre tags located in the comment field or content group field or in both, so when I use the following statement, I receive the appropriate output in the sql console (content group and comment are combined into the newly created sub_genre column).

Code: [Select]
SELECT
album,
comment_mv,
content_group_mv,
ifnull(content_group_mv,'')||''||ifnull(comment,'') sub_genre
FROM MediaLibrary


OUTPUT->

comment_mv                          content_group_mv          sub_genre_mv
progressive alternative rock      indie                              progressive alternative rock indie

However, when I add the following statement:

Code: [Select]
WHERE sub_genre = '%progressive%'

there is no output at all.  I have also tried

Code: [Select]
WHERE sub_genre LIKE '%progressive%'

Any suggestions / tips?

Also, it seems to work when I use the comment_mv column along with the LIKE statement or the content_group_mv along with the LIKE statement, but not when I use the sub_genre column....
Title: foo_uie_sql_tree
Post by: mobyduck on 2012-09-14 07:52:34
Can't you simply do
Code: [Select]
WHERE content_group_mv LIKE '%progressive%'
  OR comment LIKE '%progressive%'
?

HTH.

Alessandro
Title: foo_uie_sql_tree
Post by: fbuser on 2012-09-14 16:40:14
Code: [Select]
WHERE sub_genre = '%progressive%'
This is obvious. You cannot use wildcards here. The % characters are taken literally for a straight comparison.

there is no output at all.  I have also tried
Code: [Select]
WHERE sub_genre LIKE '%progressive%'
This should work in general and I see no reason why it doesn't work. If it suites your needs you should follow mobyduck's advice.
Title: foo_uie_sql_tree
Post by: neothe0ne on 2012-10-07 08:53:40
Some questions.

1)  What's the difference between "Query" and "Batch"?

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?

3)  There might be a glitch with "WHERE... tag NOT LIKE '%string%'".  I'm getting incorrect (too small) results when searching for songs with a certain rating (stored using foo_customdb, and using a TFExpression for sqltree) and chaining a bunch of conditions (genre, language tags, etc.).  (It only gets worse if I chain multiple "where... not like... and not like...")

E.g.,
4105 items:
Code: [Select]
SELECT path
FROM MediaLibrary
WHERE CAST(ratingcd AS INT) IS 0
ORDER BY path


658 items:
Code: [Select]
SELECT path
FROM MediaLibrary
WHERE CAST(ratingcd AS INT) IS 0
AND genre LIKE '%Karaoke%'
ORDER BY path


BUT! 2504 items:
Code: [Select]
SELECT path
FROM MediaLibrary
WHERE CAST(ratingcd AS INT) IS 0
AND genre NOT LIKE '%Karaoke%'
ORDER BY path

Last I checked, 4105-658 != 2504.  Or am I using '%string%' wrong?

4)  Finally, is there an easier way to edit the organization of the SQL tree (manually by text editor?).  If I want to change my folder structure I have to create new queries from scratch and copy-paste or re-write, it's really slow to reorganize the tree...
Title: foo_uie_sql_tree
Post by: fbuser on 2012-10-07 09:46:06
1)  What's the difference between "Query" and "Batch"?
Query (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/QueryNodeDialogQuery.html) Batch (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/QueryNodeDialogBatch.html)

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?
As count() is an aggregrate function, this is not possible. See here (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/QueryNodeDialogAction.html#click_action).

Last I checked, 4105-658 != 2504.  Or am I using '%string%' wrong?
You need to add the result of
Quote
SELECT path
FROM MediaLibrary
WHERE CAST(ratingcd AS INT) IS 0
AND genre IS NULL
ORDER BY path
to get the missing items. Columns with NULL values need to be queried differently. See here (http://www.w3schools.com/sql/sql_null_values.asp).

4)  Finally, is there an easier way to edit the organization of the SQL tree (manually by text editor?).  If I want to change my folder structure I have to create new queries from scratch and copy-paste or re-write, it's really slow to reorganize the tree...
The next version will support copying/moving nodes.
Title: foo_uie_sql_tree
Post by: r0k on 2012-10-09 10:16:43
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
Title: foo_uie_sql_tree
Post by: neothe0ne on 2012-10-09 10:26:39
Thanks, #3 indeed is missing NULL genres, now the numbers add up.
Title: foo_uie_sql_tree
Post by: Alatar on 2013-01-04 07:19:56
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
Title: foo_uie_sql_tree
Post by: fbuser on 2013-01-04 08:16:52
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.
Title: foo_uie_sql_tree
Post by: newmatrix on 2014-12-06 16:58:04
It is possible to add to this wonderful component options - "transparent background" and "off vertical scrollbar" ?
Title: foo_uie_sql_tree
Post by: fbuser on 2014-12-06 17:24:44
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.
Title: foo_uie_sql_tree
Post by: angriestchair on 2015-11-04 23:35:57
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.
Title: foo_uie_sql_tree
Post by: Sue Dunham on 2015-11-15 05:29:14
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
Title: foo_uie_sql_tree
Post by: fbuser on 2015-11-15 10:06:48
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.

Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-02 10:38:21
Version 2.0 released. See first post for details.
Title: Re: foo_uie_sql_tree
Post by: eisn on 2016-08-02 20:50:53
Hello,

Thanks for the update.

I just upgraded from from version 1.04. After the upgrade I noticed a few issues:

I'm using foobar v.1.3.11b4 and Columns UI 0.5.0.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-02 21:35:16
Thanks for the report.

1. I already noticed this, too. It will be fixed with the next release of foo_sqlite (coming soon).
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.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 11:28:32
Hi fbuser
Good to see you found some time to update this component. Thank you for your efforts, I've been using this component for several years now and use it as my way into my large (100k+ tracks) library.

I see you have added a Properties entry on the context menu, thanks. I'd also request that you add a Refresh option to just refresh the query under the menu.

Thanks again.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 11:49:16
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.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 11:52:43
Quote
It's already there and was already there from the beginning.

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
Title: Re: foo_uie_sql_tree
Post by: grimes on 2016-08-03 14:19:27
Thanks fbuser, works like a charm. 8)

Only a small issue:
I try to calculate media library length with:
Code: [Select]
SELECT format_length(sum(length_seconds)) length
   FROM medialibrary
Clicking on subnode 15wk 3d 21:31:24 gives error:
Quote
Execution error:
SQLite Error: (1) misuse of aggregate: sum()

Error while preparing the first statement of:
SELECT format_length(sum(length_seconds)) length
FROM medialibrary
WHERE CAST(format_length(sum(length_seconds)) AS TEXT) = '15wk 3d 21:31:24'
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 14:46:18
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
This 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.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 14:50:42
Thanks fbuser, works like a charm. 8)

Only a small issue:
I try to calculate media library length with:
Code: [Select]
SELECT format_length(sum(length_seconds)) length
   FROM medialibrary
Clicking on subnode 15wk 3d 21:31:24 gives error:
Quote
Execution error:
SQLite Error: (1) misuse of aggregate: sum()

Error while preparing the first statement of:
SELECT format_length(sum(length_seconds)) length
FROM medialibrary
WHERE CAST(format_length(sum(length_seconds)) AS TEXT) = '15wk 3d 21:31:24'
The select statement is wrong as you cannot use aggregate functions in the where clause of a query. It is not clear for me, what you want to achieve with this select statement. It would be possible to rewrite the query in a proper way, but semantically it would not make much sense. Also be aware, that you can define queries with aggregate functions only in the batch part of a node, not in the query part.

Edit: I looked only on the select statement in the error message, but not on the original statement. So, the reason for this error is as written in my last sentence. So, to get it working use "Send to SQLite console" as action for the relevant node and put the query into the batch section of the node.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2016-08-03 15:14:43
Fixed, thanks.
Remark: I use media library length only for maintenence purposes. My playlist "All Music" should have the same length as media library length.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 16:24:15
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
This 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.

So imagine an expanded tree where the first level of sub-nodes takes several screens/panel heights. I have to scroll up to the root node to refresh the query. Not very helpful but if that's how you have designed it, so be it.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 17:23:11
I 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.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 17:40:11
I 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.

So two keys to be pressed on a keyboard that isn't to hand since I'm using the mouse before I can get to select Refresh from the context menu ... Still a pain but it I should be grateful that you have done so much so far.
Title: Re: foo_uie_sql_tree
Post by: eisn on 2016-08-03 20:11:58
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.
That's what I meant (I thought this behavior was performance related). Thanks for the fixes.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2017-01-04 22:50:50
I see you've completed the update. Thank you for continuing to develop this wonderful component. However, I seem still to be having the trouble I reported above with the unsorted tracks. Unticking the "Remove duplicates..." box works as you described. I think I have everything updated: foobar 1.3.12, SQLite Viewer 1.0.2, and SQL Tree 2.0.1. I even corrected the query as you suggested, thanks.

On an unrelated note, I've been trying to wrap my head around the genre query in your example nodes. Could you direct me to where the tf() function is documented? Google leads me in several different directions, but "term frequency" seems to be the likeliest.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2017-01-04 22:58:56
I 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.
One can also doubleclick the vertical line between the appropriate nodes to collapse it.
Title: Re: foo_uie_sql_tree
Post by: foosion on 2017-01-05 07:49:34
On an unrelated note, I've been trying to wrap my head around the genre query in your example nodes. Could you direct me to where the tf() function is documented? Google leads me in several different directions, but "term frequency" seems to be the likeliest.
It is short for "title format". The foo_sqlite documentation contains a section on custom SQL functions (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/CustomSQLFunctions.html).
Title: Re: foo_uie_sql_tree
Post by: Hans-Bambel on 2017-02-08 11:05:48
I'm trying to run some SQL-queries but somehow they don't seem to work properly, because the results are not grouped as they should (according to the query).
I'm not familiar with SQL queries so I tried some from Grimes

Here is one:
//Artist playcount
Code: [Select]
SELECT artist, round(avg(play_count),2) as playcount
FROM medialibrary
GROUP BY artist
ORDER BY playcount DESC
Because I'm using Playback statistics (http://wiki.hydrogenaud.io/index.php?title=Foobar2000:Components/Playback_Statistics_v3.x_(foo_playcount)) I get an error when I try to copy and execute this query, because
SQLite Error: no such column: play_count
So I changed the first instance of play_count to "%play_count%". But this didn't work (I thought the "as playcount" acts as a variable definition) so I changed all instances of play_count with "%play_count%".
This lets me execute the query, but it doesn't group by artist nor is it in any descending order.
Can someone help me with this?

Additionally, I want a query to list all Artists where I have more than x songs from. Any help?
Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2017-02-08 14:54:20
I want a query to list all Artists where I have more than x songs from. Any help?
Maybe something like this will do (replace 10 with the desired minimum number):
Code: [Select]
SELECT artist, count(*) AS songs
  FROM medialibrary
 GROUP BY artist
HAVING count(*) > 10
Title: Re: foo_uie_sql_tree
Post by: Hans-Bambel on 2017-02-08 17:35:42
I want a query to list all Artists where I have more than x songs from. Any help?
Maybe something like this will do (replace 10 with the desired minimum number):
Code: [Select]
SELECT artist, count(*) AS songs
  FROM medialibrary
 GROUP BY artist
HAVING count(*) > 10


Thank you, but I still get all my Songs shown as a result. So not even that works...
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2017-02-08 21:06:39
Here is one:
In general, the query is correct. How is your %play_count% column defined for the MediaLibrary table (Preferences -> Media Library -> SQLite viewer -> MediaLibrary table)?


Additionally, I want a query to list all Artists where I have more than x songs from. Any help?
The query provided by mobyduck is also correct for this purpose. So could you please post some screen shots, which show, what you defined where?
Title: Re: foo_uie_sql_tree
Post by: Hans-Bambel on 2017-02-08 23:37:29
In general, the query is correct. How is your %play_count% column defined for the MediaLibrary table (Preferences -> Media Library -> SQLite viewer -> MediaLibrary table)?
I don't have such a column there. So I guess that's the problem. I added the following now:
Name: play_count
Type: TFExpression
Collation: NaturalNoCase(What's this?)
Title format expression: [%play_count%]

with this the "Artist playcount" query puts my most played song at the top of the playlist, but it is not grouped by artists. How do I make that happen?

Thank you already! I didn't know that you had to insert a column for play_count in the SQLite Viewer preferences.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2017-02-09 20:53:48
Collation: NaturalNoCase(What's this?)
It sorts the column in a natural, case insensitive order. This is actually not relevant for number only columns like play_count, but it's not doing any harm. For this case you could also use Binary, which could you save some milliseconds execution time.

with this the "Artist playcount" query puts my most played song at the top of the playlist
This is not possible. Queries, which contain aggregate functions like count() or avg() cannot be used for sending the result to a playlist. You would get an error message, if you try this. You can only send the result of such queries to the SQLite Viewer console.

Thank you already! I didn't know that you had to insert a column for play_count in the SQLite Viewer preferences.
It's because play_count is not a tag, which could be added automatically to the table columns during the first use of the component. If you tag your files later with tags, which you didn't use so far, you also need to add them manually or by pressing the button "Add defaults" in the preferences dialog.

As already asked, it would be helpful, if you could post some screen shots from your settings and your result.
Title: Re: foo_uie_sql_tree
Post by: kode54 on 2017-02-09 21:17:20
And before anyone asks, "natural" sorting means that it orders numbers by value, regardless of whether they happen to be padded to a fixed number of digits. Of course, this form of sorting tends to irk me sometimes, as it never takes into account hexadecimal numbering.
Title: Re: foo_uie_sql_tree
Post by: Hans-Bambel on 2017-02-09 23:25:50
Aaah, okay. When I sent the query to the SQLite Viewer Console it showed me the correct results.
But what kind of queries can I make to create a playlist? I thought I could use queries as a "smarter" and more elegant way to make autoplaylists.
I attached my preferences of the SQLite Viewer, and my query which now in the "Action" tab is changed "Send to SQLite Console".
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2017-02-10 19:16:02
Thanks for the screen shots. Now it's more clear what you're doing. As you didn't added any sorting to the query, you didn't get an error. On the other hand, you need to sort the result, to get the desired order in the playlist.

But what kind of queries can I make to create a playlist?
You need to define queries in that way, that the result is one track per row. So, to get a playlist filled for the two cases mentioned in this thread, you could use the following queries:

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
     (SELECT artist,avg(play_count) avg_play_count
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
ORDER BY b.avg_play_count DESC, a.artist,album,discnumber,tracknumber

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
     (SELECT artist,count(*) count_tracks
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
WHERE count_tracks>10
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber

But be aware, that especially for large libraries, queries which are using the MediaLibrary table more than once, are quite slow.

To get it a bit faster, you could use a temporary table for the grouped subqueries. For the second query, you could add the following code to the batch field of your definition.

Code: [Select]
DROP TABLE IF EXISTS artist_count_tracks;
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
FROM MediaLibrary
GROUP BY artist
HAVING count_tracks>10;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);

The code for the query field, would look like this:
Code: [Select]
SELECT a.artist,b.count_tracks
FROM MediaLibrary a JOIN artist_count_tracks b ON b.artist=a.artist
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber

The predicate for joining both tables needs to be written as "b.artist=a.artist". If you change the order to "a.artist=b.artist", the index artist_count_tracks_index won't be used for some reason, which makes everything even slower.

I thought I could use queries as a "smarter" and more elegant way to make autoplaylists.
Actually, not. Autoplaylist are playlist, which are updating themselves, if the tags in the library change. This won't happen with playlists, which are fed by SQL Tree, because they are simply static playlist. Of course, sending the result again to the playlist, would update the playlist according to the current tags in the media library.

But you can indeed create playlists, which cannot be created with the standard components, which are using title format, like all queries, which build relations between tracks.

Edit: Fixed wrong SQL.
Title: Re: foo_uie_sql_tree
Post by: Hans-Bambel on 2017-02-11 13:31:06
You need to define queries in that way, that the result is one track per row. So, to get a playlist filled for the two cases mentioned in this thread, you could use the following queries:

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
    (SELECT artist,avg(play_count) avg_play_count
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
ORDER BY b.avg_play_count DESC, a.artist,album,discnumber,tracknumber

Code: [Select]
SELECT 1
FROM MediaLibrary a JOIN
    (SELECT artist,count(*) count_tracks
      FROM MediaLibrary
      GROUP BY artist) b ON a.artist=b.artist
WHERE count_tracks>10
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber

But be aware, that especially for large libraries, queries which are using the MediaLibrary table more than once, are quite slow.

Thank you very much, these are working well now!
But the next one doesn't.
To get it a bit faster, you could use a temporary table for the grouped subqueries. For the second query, you could add the following code to the batch field of your definition.

Code: [Select]
DROP TABLE IF EXISTS artist_count_tracks;
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
FROM MediaLibrary
WHERE count_tracks>10
GROUP BY artist;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);

When I add this to the batch field and click Validate/Execute I get the following Error:
Code: [Select]
Execution error:
SQLite Error: (1) misuse of aggregate: count()

Error while preparing the first statement of:
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
FROM MediaLibrary
WHERE count_tracks>10
GROUP BY artist;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);


The code for the query field, would look like this:
Code: [Select]
SELECT a.artist,b.count_tracks
FROM MediaLibrary a JOIN artist_count_tracks b ON b.artist=a.artist
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber
When I enter this as query I get the error "no_such_table: artist_count_tracks". Probably because the batch didn't work.
Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2017-02-11 13:48:41
I think you have to use the HAVING clause, but this seems more flexible to me:
Code: [Select]
DROP TABLE IF EXISTS artist_count_tracks;
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
  FROM MediaLibrary
 GROUP BY artist;

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);
Code: [Select]
SELECT a.artist,b.count_tracks
  FROM MediaLibrary a
  JOIN artist_count_tracks b
    ON b.artist = a.artist
 WHERE b.count_tracks > 10
 ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber
HTH.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2017-02-11 14:26:29
I think you have to use the HAVING clause,
Of course, I fixed it.
but this seems more flexible to me:
To be honest, I don't see a better flexibility with your solution, but it's definitely less efficient, although in many cases you don't really notice the difference. With your approach the temporary table is larger, which means it needs more disk space and it also needs more time to create the table, because more records need to be written. Additionally the subsequent SELECT extracts the data from more records, which also costs some more time.
Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2017-02-11 15:46:48
I don't see a better flexibility with your solution
Well, it is if you want to do multiple queries each with a different minimum number of tracks.

Also, disk space can become an issue if you have a *huge* number of artists below the threshold, otherwise I don't think you'll notice a real difference.

Finally, if you go to the extent of creating a temp table, it might be a good idea adding other aggregate information about each artist and make it even more generally useful.

Anyway, just my two cents...
Title: Re: foo_uie_sql_tree
Post by: zoumbro on 2017-09-29 12:30:48
Hi guys,

I would like to sort my mp3 folders - no tracks- from most to less popular (using statistics or DAR). Is it possible?
Title: Re: foo_uie_sql_tree
Post by: Thegreen16 on 2019-02-10 01:29:23
I created an account just to say I really appreciate this component and I'm a little confused on the usage of the "Refresh query, on load" function. I have a query that creates a short list of songs with a specific rating, and the action is set to replace the past query with the present. My understanding of the refresh on load is that it will rerun the query, but how can I make it automatically update the playlist without opening the tree and clicking on the node?

tl;dr how do I automatically create a playlist from a query when foobar starts up
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2019-02-10 06:17:32
"Refresh on load" refreshes only the node query, it doesn't execute one of the node actions.
Title: Re: foo_uie_sql_tree
Post by: Thegreen16 on 2019-02-11 02:56:41
"Refresh on load" refreshes only the node query, it doesn't execute one of the node actions.

Interesting. If that's the case, what would be the SQL syntax for creating or updating a playlist?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2019-02-11 18:29:39
It's documented in the help file from foo_sqlite (SQLite Viewer - MetaDb_Module).
Title: Re: foo_uie_sql_tree
Post by: Thegreen16 on 2019-02-11 23:33:25
It's documented in the help file from foo_sqlite (SQLite Viewer - MetaDb_Module).

Thanks! :)
Title: Re: foo_uie_sql_tree
Post by: Thegreen16 on 2019-02-20 04:37:20
I keep getting "SQL Error:(1) table Playlist_Updateable already exists...Error while preparing the first statement of:.. " if I try to add the query to the Query tab of the node (it works in the Batch tab, but that doesn't seem to allow the Refresh Query on load feature to work). The query works through the SQLite console as well, but that doesn't help.

Code: [Select]
DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);
DELETE FROM Playlist_Updatable
WHERE playlist_name="Radio: Neil Young";
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle,'Radio: Neil Young'
FROM MediaLibrary
WHERE "album artist"='Neil Young' AND (RATING >= 2 OR RATING IS NULL)
ORDER BY RANDOM()
LIMIT 25

What am I doing wrong?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2019-02-20 21:03:09
You cannot add a sequence of statements (= batch) to the query tab of the node, so the statements must be put into the batch section. Further "Refresh on load" does exactly the same as manually refreshing the node by using the context menu for this node and it works definitely also with "on load". So something else must be wrong. So, please post the complete settings for the node here.

By the way, do you get the expected result in the SQLite console with your query? If yes, it would mean, that the "album artist" tag itself is filled in addition to the artist tag. With "album artist" there is no automatic fallback to the artist tag, if the "album artist" tag is not set. To get this, you would need to use "%album artist%" instead.

And one other thing: I would move the dropping and creation of the virtual table to the batch section of a separate node, which is only executed on demand, not automatically on load. There is no need to drop and create the virtual table on every load.
Title: Re: foo_uie_sql_tree
Post by: Thegreen16 on 2019-02-21 00:42:23
By the way, do you get the expected result in the SQLite console with your query?

Yes, it works in the console and in a node under the Batch tab (tested both with creating a separate node for the droping/creating the virtual table and both executed correctly). However, neither tests executed on load of Foobar, but they do work if I open the SQL tree view, even without clicking on anything it will update the playlists. The node uses all the default settings in the root pathway, except for enabling on load in the Advanced tab.

As another test, I created a new node with the Batch code (from the docs), and didn't activate/run it before closing foobar.
Code: [Select]
INSERT INTO Playlist_Updatable(metadb_handle)
SELECT metadb_handle
FROM MediaLibrary
WHERE artist='Metallica'

and I enabled on load from the advanced tab. No playlist was created on rebooting foobar. However, when I opened SQL tree from the context menu, without clicking anything, the playlist appeared.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2019-02-23 12:35:31
However, neither tests executed on load of Foobar, but they do work if I open the SQL tree view
Ok, that explains it. The "Refresh query, on load" can only work, it the tree view panel is opened. I need to update the documentation on this matter. So, unfortunately there is no way to get what you want, if the tree view panel is not opened.
Title: Re: foo_uie_sql_tree
Post by: Thegreen16 on 2019-02-24 18:06:35
So, unfortunately there is no way to get what you want, if the tree view panel is not opened.

I found a workaround. Integrating a SQL Tree into a panel through columns UI will refresh on load properly :D
Title: Re: foo_uie_sql_tree
Post by: firewater on 2019-05-18 02:29:16
I'm wondering if I can use this component to display %album rating%, preferably by writing a tag to the files, in ELPlaylist, as averaged from individual ratings thorugh a calculation posted earlier (https://hydrogenaud.io/index.php/topic,90978.msg770756.html#msg770756). I've reached the thread by looking up "calculate album rating" on the searchbar, and though I know my way around foobar pretty well, I don't get how this works. Could someone quickly guide me through what I want? All I figured out is I can open a terminal, a blank view window, and add columns from preferences (which keep disappearing after I clumsily add them...)
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2019-05-18 07:36:45
I'm wondering if I can use this component to display %album rating%, preferably by writing a tag to the files,
Yes, writing a tag for album rating to the files is possible. But first you should be more specific on this:
All I figured out is I can open a terminal, a blank view window, and add columns from preferences (which keep disappearing after I clumsily add them...)
Please, describe exactly, what you are doing, best with screen shots.
Title: Re: foo_uie_sql_tree
Post by: firewater on 2019-05-19 00:11:10
Thanks a lot for the quick assistance.

The way I understand this component functionality is, from a list of tags stored in rows on the MediaLibrary window, it can retrieve data with which it can make advanced calculations, which can then be turned into new tags. Am I getting it right?

So far I've:
1. Installed the foo_sqlite and foo_uie_sql_tree components
2. Added a SQL Tree panel to my layout, which shows <Examples> > Library folders
3. From the Preferences > MediaLibrary, clicked "Add defaults", resulting in a long list of rows presumably read from my library. After I click "OK" something off happens - the Preferences window closes, and when I open it again the rows are empty. If I click again on "Add defaults," and then click "Apply", I get further on what's wrong with an error message. Here I thought one of my tags contains a faulty value, so I try to add only the tags I'm actually gonna be using, like %rating%. I tried 3 ways to do it, but they all result in the same aforementioned error when I press apply.

After getting this solved I believe I'd have to open SQLite console, paste the code I linked in my previous post, click "Execute" (at which point it would generate the values), and then write these values to the files in some way.

Hopefully I'm on the right path!
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2019-05-19 09:55:11
The way I understand this component functionality is, from a list of tags stored in rows on the MediaLibrary window, it can retrieve data with which it can make advanced calculations, which can then be turned into new tags. Am I getting it right?
I wouldn't describe it in this way, but basically your understanding is correct.

Here I thought one of my tags contains a faulty value, so I try to add only the tags
The problem here are the tag names in the list which starts with a double quote, which are shown in your first screen shot. The empty tag name at the beginning of the list could also cause some troubles. In a future version, such kind of tags will be ignored.

so I try to add only the tags I'm actually gonna be using, like %rating%. I tried 3 ways to do it, but they all result in the same aforementioned error when I press apply
This is a bit strange as if the tags with the double quotes are not in the list, the mentioned error message can normally not occur. You also shouldn't change the settings for the multivalue handling of the rating tag. Just keep the default values and leave the values in the Tag group untouched. The meaning of the entries are described in the help file (Help -> SQLite viewer).

You also need to define a tag for the album rating, e.g. "album rating" (without the double quotes)

After getting this solved I believe I'd have to open SQLite console, paste the code I linked in my previous post, click "Execute" (at which point it would generate the values), and then write these values to the files in some way.
Actually, it's not that easy:

To avoid messing up completely your media library, you should use only the active playlist for updating your tags. Therefore you also need to set up the tags properly for the playlist table like for the media library table in the preferences.

After this is done you need to create an instance of the virtual table for the playlist table, which is updatable as described in the help file:
Code: [Select]
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split);
This is a one time task and you can run this statement in the SQLite console.

You can update your tags with the following SQL statement (I didn't test it, but I use similar statements, which are working in this way).
Code: [Select]
-- Creating a temporary table for the album ratings for performance reasons is at least necessary for larger playlist
DROP TABLE IF EXISTS tmp_AlbumRating;
CREATE TABLE tmp_AlbumRating AS
SELECT "%album artist%" AS album_artist, -- %album artist% needs to be defined as a TFExpression type in the preferences. This is automatically done, when using the default values
           album,
           avg(CAST(rating AS REAL)) AS "album rating" -- as tags are always text fields, the rating tag needs to be explicitely casted to a numeric value
FROM Playlist_View
WHERE playlist_index=active_playlist() -- use only the entries from the active playlist to avoid completely messing up the whole library
GROUP BY 1,2;

CREATE UNIQUE INDEX tmp_AlbumRating_pk ON tmp_AlbumRating(album_artist,album);

UPDATE Playlist_Updatable
SET "album rating"= (SELECT "album rating"
                                 FROM tmp_AlbumRating
                                 WHERE album_artist=Playlist_Updatable."%album artist%"
                                       AND album=Playlist_Updatable.album
WHERE playlist_index=active_playlist();

In the SQL tree you could create a node then, which contains the script above in the batch section (the query section remains empty), use "Execute SQL" as click action and "Send to SQLite console" as middle click action.

Title: Re: foo_uie_sql_tree
Post by: Chris Norman on 2020-06-06 20:13:40
Hi,

since a couple of days I am getting this message in foo_sqlite and the tree component will not show anything (likely because of this issue).

Code: [Select]
Execution error:
SQLite Error: (1) Unable to declare virtual table MediaLibrary:
SQLite Error: (1) duplicate column name: #helium albumid

Error while preparing the first statement of:

SELECT * FROM MEDIALIBRARY

Any ideas what to do? I have verified the library for the column but without success.

Thanks in advance
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-06-06 21:08:10
Check the entries in the preferences under "Media Library -> SQLite viewer -> Medialibrary table". Although it should not be possible if you don't change the internal sqlite tables manually, it seems that you have two or more entries with the same name here. If this is the case, delete all but one of them and the problem should disappear.
Title: Re: foo_uie_sql_tree
Post by: Chris Norman on 2020-06-10 23:08:21
Thanks for the hint I wasn't aware of that setup. Strangely all entries were duplicated. I created an auto hotkey script to have all duplicates removed. Now it works again.

Have a nice day!
Title: Re: foo_uie_sql_tree
Post by: regor on 2020-12-09 19:17:40
Hi,
I have been trying to create a 25 most played tracks playlist with this code:

Code: [Select]
SELECT DISTINCT title
FROM medialibrary
ORDER BY play_count
DESC LIMIT 25

I never used SQL before, but as far as I understand, select distinct should retrieve only different values from the table. In the SQL console it works, and 25 different tracks appear.
X

But as soon as I use that as a query and send it to a playlist, duplicate tracks are retrieved. They are tracks with same title but different path, etc. (i.e. 2 masterings or versions of the same album). Check capture 2.
X

Since the playback statistics plugin adds play_count according to "track - tittle", obviously 2 versions of the same track/album get the same statistics. Foobar don't consider them as duplicates, neither does your plugin option "remove duplicates before sending...".

Is there any way to filter those duplicates and get the same result than the console? I don't really care if duplicates are discarded just by selecting only the first result or whatever.

Thanks!
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-09 22:45:29
This SQL statement should do what you want:

Code: [Select]
SELECT a.title
FROM MediaLibrary a JOIN (SELECT title,max(path) path,max(subsong) subsong
                                            FROM MediaLibrary
                                            GROUP BY title
                                            ORDER BY play_count DESC
                                            LIMIT 25) b ON a.path=b.path AND a.subsong=b.subsong
Title: Re: foo_uie_sql_tree
Post by: regor on 2020-12-10 15:57:24
Nope. Now all tracks with "duplicates" are simply omitted. Note the yellow tracks are in both queries (yours and mine).
X.
X
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-10 20:05:38
Nope. Now all tracks with "duplicates" are simply omitted.
Actually is this by chance, but anyway the query is indeed wrong.

Put this into the batch section of the query due to performance reasons:
Code: [Select]
DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
SELECT max(path) path
FROM MediaLibrary
GROUP BY title;

CREATE UNIQUE INDEX tmp_paths_in_uq ON tmp_paths(path);

Then use this query:
Code: [Select]
SELECT title
FROM MediaLibrary a JOIN tmp_paths b ON a.path=b.path
ORDER BY play_count DESC
LIMIT 25

This will not work in case you are using files with multiple subsongs, like for example cuesheets, but without the possibility to use window functions, which are not supported by the currently used SQLite version, it's a bit difficult to get what you want.
Title: Re: foo_uie_sql_tree
Post by: regor on 2020-12-11 00:07:54
Now I get this error:
X

Btw I only have single track files, no cue files or subsongs. I do have multiple files named "01 - blablabla.flac", on different albums and/or different masterings of the same album.

If there is no "easy" solution, no worries. I already found how to do it on JavaScript with a SMP script (the "remove duplicates" part).
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-11 20:03:45
Now I get this error:
[attach type=thumb]18423[/attach]
As you didn't mention it, I can only guess, that you tried to validate the batch. As mentioned in the help file, this is not possible, if DDL statements are involved. Try just to execute it or run it in the console.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-11 21:15:49
I just got this installed as I read on different threads that this can find duplicates.

I not that great at SQL queries so any help would be appreciated.

Getting %artist% and %title% duplicates would be what I'm after. There is a post from 2007 seems to be an old syntax for finding duplicates. https://hydrogenaud.io/index.php?topic=51307.msg505964#msg505964

Thanks for this @fbuser‍.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-11 22:50:37
I just got this installed as I read on different threads that this can find duplicates.
Yes, it is possible.

There is a post from 2007 seems to be an old syntax for finding duplicates. https://hydrogenaud.io/index.php?topic=51307.msg505964#msg505964
It's because it's a different component.

Getting %artist% and %title% duplicates would be what I'm after.
Put this into the batch section of the query:
Code: [Select]
DROP TABLE IF EXISTS tmp_artist_title;
CREATE TABLE tmp_artist_title AS
SELECT strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
GROUP BY 1,2;

CREATE UNIQUE INDEX tmp_artist_title_pk ON tmp_artist_title(title,artist);

DROP TABLE IF EXISTS tmp_artist_title_path;
CREATE TABLE tmp_artist_title_path AS
SELECT path,
       subsong,
       strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary a
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
  AND EXISTS (SELECT NULL
              FROM tmp_artist_title
              WHERE artist=strfilter(lower(unaccent(a.artist)),'abcdefghijklmnopqrstuvwxyz0123456789')
                AND title=strfilter(lower(unaccent(a.title)),'abcdefghijklmnopqrstuvwxyz0123456789'))
ORDER BY 3,4;

DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
WITH Paths AS (
SELECT a.path path1,
       a.subsong subsong1,
       b.path path2,
       b.subsong subsong2
FROM tmp_artist_title_path a JOIN tmp_artist_title_path b ON a.rowid=b.rowid+1
WHERE a.artist=b.artist
  AND a.title=b.title)
SELECT path1 path,
       subsong1 subsong
FROM Paths
UNION
SELECT path2, subsong2
FROM Paths;

CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path,subsong);

Then use this query:
Code: [Select]
SELECT upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)) Letter,
       proper(artist) Artist
FROM MediaLibrary a
WHERE EXISTS (SELECT NULL
              FROM tmp_paths
              WHERE path=a.path
                AND subsong=a.subsong)
ORDER BY upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)),
         strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') COLLATE NaturalNoCase,
         CAST(length_seconds AS INTEGER),
         title

Note: this might also find tracks, which are actually no duplicates, because all non-alphanumeric characters and diacritics are removed from artist and title before they are compared case insensitive.

Remove all strfilter functions, if you don't want to filter out non-alphanumeric characters, e.g. lower(unaccent(artist)) for the artist.

Remove all unaccent functions, if you don't want diacritics to be removed, e.g. strfilter(lower(artist),'abcdefghijklmnopqrstuvwxyz0123456789') for the artist.

Remove all lower functions, if you want a case sensitive comparison, e.g. strfilter(unaccent(artist),'abcdefghijklmnopqrstuvwxyz0123456789') for the artist.

If you don't want all of them above just use the relevant column, e.g. just artist for the artist.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-11 23:20:43
@fbuser

wow, that's intense!! Thanks so much.

I'm getting an error for the query and the batch validate like this for query:
(https://i.imgur.com/dwJ2pAA.png)

And for the batch when I try to execute it:
Code: [Select]
Execution error:
SQLite Error: (1) no such table: MediaLibrary_View

Error while preparing the first statement of:
CREATE TABLE tmp_artist_title_path AS
SELECT path,
      subsong,
      strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
      strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary_View a
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
  AND EXISTS (SELECT NULL
              FROM tmp_artist_title
              WHERE artist=strfilter(lower(unaccent(a.artist)),'abcdefghijklmnopqrstuvwxyz0123456789')
                AND title=strfilter(lower(unaccent(a.title)),'abcdefghijklmnopqrstuvwxyz0123456789'))
ORDER BY 3,4;

CREATE UNIQUE INDEX tmp_artist_title_path_pk ON tmp_artist_title_path(path, subsong);

DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
WITH Paths AS (
SELECT a.path path1,
      a.subsong subsong1,
      b.path path2,
      b.subsong subsong2
FROM tmp_artist_title_path a JOIN tmp_artist_title_path b ON a.rowid=b.rowid+1
WHERE a.artist=b.artist
  AND a.title=b.title)
SELECT path1 path,
      subsong1 subsong
FROM Paths
UNION
SELECT path2, subsong2
FROM Paths;

CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path);
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-11 23:42:48
When I try to validate batch, I get:
SQLLite Error table tmp_artist_title already exists
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 06:44:43
@fbuser‍ I'm getting an error for the query and the batch validate like this for query:
When I try to validate batch, I get:
SQLLite Error table tmp_artist_title already exists
As mentioned in the help file, this is not possible, if DDL statements are involved. Try just to execute it or run it in the console.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 08:21:37
If I execute, I get error:
Code: [Select]
Execution error: SQLite Error: (1) no such table: MediaLibrary_View
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 08:44:25
If I execute, I get error:
Code: [Select]
Execution error: SQLite Error: (1) no such table: MediaLibrary_View
Ok, it's an adaption of my query to detect duplicates, which contains some specialties for my own purposes. I forgot to replace  a reference to my MediaLibrary view. Use MediaLibrary instead of MediaLibrary_View. It's corrected in my post.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 08:52:47
Now I get error:
Code: [Select]
Execution error:
SQLite Error: (19) UNIQUE constraint failed: tmp_paths.path

Error while executing:
CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path)
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 09:10:37
I omitted last line in batch
Now it gives a result (letter, artist) I don't really know, what that means.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 09:11:10
Now I get error:
Code: [Select]
Execution error:
SQLite Error: (19) UNIQUE constraint failed: tmp_paths.path

Error while executing:
CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path)
Thanks for testing it. It's just another error, which can't occur on my collection as I don't use cue sheets or other files with more than one subsong. It's corrected too in my post. The relevant part needs to be CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path, subsong)
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 09:12:01
I omitted last line in batch
Now it gives a result (letter, artist) I don't really know, what that means.
Indexes are used to speed up queries.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 09:18:10
Thank so much, it works.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 09:35:26
I got duplicates, when the track is only named "Allegro", but different piece. Can you add same file size to query? This will find real duplicates.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 12:11:50
I got duplicates, when the track is only named "Allegro", but different piece. Can you add same file size to query? This will find real duplicates.
This is problematic because as more conditions you add as as more you get the chance to miss some duplicates. Furthermore file sizes differ for different formats. There is no 100% solution for this problem. Besides a similar query like the one, which I already posted I use a few more queries, which are more specific and in addition to that I use an own tag, which let me mark tracks as checked, if they are considered as duplicates even, if they are actually no duplicates. But this is taylored to my very own tagging schema and won't help here.

But assuming that duplicate tracks don't differ in length more than 15 seconds (although sometimes tracks are just cut earlier) the batch part could look like the following (the query part remains the same, but note: I slightly fixed my original post to properly sort for length and removed an useless index):

Code: [Select]
DROP TABLE IF EXISTS tmp_artist_title;
CREATE TABLE tmp_artist_title AS
SELECT strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
GROUP BY 1,2;

CREATE UNIQUE INDEX tmp_artist_title_pk ON tmp_artist_title(title,artist);

DROP TABLE IF EXISTS tmp_artist_title_path;
CREATE TABLE tmp_artist_title_path AS
SELECT path,
       subsong,
       strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title",
       length_seconds
FROM MediaLibrary a
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
  AND EXISTS (SELECT NULL
              FROM tmp_artist_title
              WHERE artist=strfilter(lower(unaccent(a.artist)),'abcdefghijklmnopqrstuvwxyz0123456789')
                AND title=strfilter(lower(unaccent(a.title)),'abcdefghijklmnopqrstuvwxyz0123456789'))
ORDER BY 3,4,5;

DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
WITH Paths AS (
SELECT a.path path1,
       a.subsong subsong1,
       b.path path2,
       b.subsong subsong2
FROM tmp_artist_title_path a JOIN tmp_artist_title_path b ON a.rowid=b.rowid+1
WHERE a.artist=b.artist
  AND a.title=b.title
  AND abs(a.length_seconds-b.length_seconds)<15)
SELECT path1 path,
       subsong1 subsong
FROM Paths
UNION
SELECT path2, subsong2
FROM Paths;

CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path, subsong);
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 12:33:42
Thousand thanks for your hard work. I reduced from 15sec to 1sec. This reduced the amount of duplicates from 4000 to 1300. Thats much better. Live versions of the same song are not duplicates.

I modified query a little bit (+Album column)
Code: [Select]
SELECT upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)) Letter,
       proper(artist) Artist, album Album
FROM MediaLibrary a
WHERE EXISTS (SELECT NULL
              FROM tmp_paths
              WHERE path=a.path
                AND subsong=a.subsong)
ORDER BY upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)),
         strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') COLLATE NaturalNoCase,
         length_seconds,
         title
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 13:21:48
I reduced from 15sec to 1sec. This reduced the amount of duplicates from 4000 to 1300.
But you will most likely miss duplicates with this. But this is, of course, your decision.
Live versions of the same song are not duplicates.
Because of this, I exclude in some of my queries live tracks, based on my own tagging schema.

I modified query a little bit (+Album column)
This is will lead to surprising results in case you have two albums with one than more pair of duplicates. You will most likely get branches in the tree like this:
Artist
  Album1
  Album2
  Album1
  Album2

It's because the sorting for album is missing in the query. However, if you add this to the query to get the desired result (only one node per album), you will get the following order for the tracks of these two albums in the playlist:
Album1, Title1
Album1, Title2
Album2, Title1
Album2, Title2

So duplicate tracks aren't always next to each other anymore in the playlist.

Apart from this, just look at my original post for the query. Like mentioned, it's slightly adjusted for proper length sorting.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-12 17:30:26
I'm still getting an error with your updated code HERE (https://hydrogenaud.io/index.php?topic=90978.msg991410#msg991410).

For query it's:

(https://i.imgur.com/sKip8vf.png)

And the batch tab when I've pressed Validate:

(https://i.imgur.com/d2jQnJS.png)
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 18:10:32
For query it's:
Ah, yes. You need to execute the batch at least once. Otherwise you won't have the table you are selecting from.

And the batch tab when I've pressed Validate:
Of course, and this will ever be the case. It has been now answered several times in the last 24 hours, also to one of your posts: Validate doesn't work with DDL statements. It's simply not possible without executing the code in its whole, but this is not different from actually executing the code.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-12 18:20:15
@fbuser‍, sorry I missed that. Works great. Do you have a donation link?

One more q. The built-in example for finding incomplete albums.

How can I change it so that it adds the items to a new playlist as opposed to the SQL console.

When I change the option under action to do this send to target playlist, I get this error.

Code: [Select]
Invalid node query

Invalid query statement:

What should I enter in the Query tab for this to work?
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 18:54:36
"send to target playlist" works here
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 19:47:54
@fbuser‍, sorry I missed that. Works great.
No problem.
Do you have a donation link?
Thanks for asking, but no.
What should I enter in the Query tab for this to work?
Code: [Select]
WITH Albums AS (
  SELECT album,"%album artist%"
  FROM MediaLibrary
  GROUP BY 1,2
  HAVING count(*)<>max(CAST(totaltracks AS INTEGER))
)
SELECT a."%album artist%" "Album Artist",
       '['||coalesce(a.date,'')||']  '||a.album "Date - Album",
       title "Title"
FROM MediaLibrary a JOIN Albums b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
         a.date,
         a.album,
         discnumber,
         tracknumber;

Note: This will fail for albums of an artist who has more than one album with the same name. In this case other, user specific criteria needs to be used to uniquely identify an album.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 19:49:13
"send to target playlist" works here
This is not possible for the query, that @stevehero mentioned. You must be using a different query.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-12 19:54:16
@fbuser

Thanks, I get this error now. I executed the batch beforehand just in case that was needed first.

Code: [Select]
Invalid node query

SQLite Error: (1) no such table: Albums

Error while preparing the first statement of:
SELECT MetaDb_Handle
FROM MediaLibrary a JOIN Albums b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
         a.date,
         a.album,
         discnumber,
         tracknumber
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 20:14:34
Sorry, I misunderstood.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 20:53:14
@fbuser‍Thanks, I get this error now. I executed the batch beforehand just in case that was needed first.
No, there is no batch in this case. Just put the code into the query tab. That was what you asked for. ;)
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-12 21:25:09
But "send to target playlist" doesn't work.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-12 21:30:43
No, there is no batch in this case. Just put the code into the query tab. That was what you asked for. ;)
But "send to target playlist" doesn't work.
I still get the same error as my example above. Sorry to be a pain.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 21:42:06
But "send to target playlist" doesn't work.
For the code, that I provided, it definitely does.

I still get the same error as my example above. Sorry to be a pain.
In this case you are probably still using your example, which is wrong. Just put my code above into the query tab and leave the batch tab empty.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-12 21:50:33
In this case you are probably still using your example, which is wrong. Just put my code above into the query tab and leave the batch tab empty.
 
I'm definitely only using the Query tab and using your code in this post (https://hydrogenaud.io/index.php?topic=90978.msg991448#msg991448) for it.

So basically,
1. Create a new query.
2. Paste your code to the query tab.
3. Send to target playlist and give it a name.
4. Error as I've mentioned above (not when pressing okay, but when I click on the query made).
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-12 22:50:53
In this case you are probably still using your example, which is wrong. Just put my code above into the query tab and leave the batch tab empty.

I'm definitely only using the Query tab and using your code in this post (https://hydrogenaud.io/index.php?topic=90978.msg991448#msg991448) for it.

So basically,
1. Create a new query.
2. Paste your code to the query tab.
3. Send to target playlist and give it a name.
4. Error as I've mentioned above (not when pressing okay, but when I click on the query made).

Oops, my fault. I'm using work-in-progress versions of foo_sqlite and foo_uie_sql_tree, which already support using CTEs (WITH clause) in the query tab of foo_uie_sql_tree. Use this code instead, it's doing the same without a CTE:

Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '['||coalesce(a.date,'')||']  '||a.album "Date - Album",
       title "Title"
FROM MediaLibrary a JOIN (SELECT album,"%album artist%"
                          FROM MediaLibrary
                          GROUP BY 1,2
                          HAVING count(*)<>max(CAST(totaltracks AS INTEGER))) b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
         a.date,
         a.album,
         discnumber,
         tracknumber
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-12 22:56:37
@fbuser‍ thanks very much. Do you have a method for a donation? This plugin is so powerful and credit to you for doing it.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-16 14:20:57
Sorry if this has been addressed in the thread. I gave it a quick look and couldn't find it.

What it issue is.
1. Added SQL Tree to a panel in columns UI.
2. Queries don't match that of the Library>SQL Tree. Only the <Examples> folder shows up.
3. Also if I add any in the panel Library>SQL Tree both don't update and vice versa. Even after restarting fb2k.
4. See screenshot of the panel and the menu item open.

I can drag and drop between the menu item and the panel but is there any way to sync them or is this a limitation?

(https://i.imgur.com/sfcHSv2.png)
Title: Re: foo_uie_sql_tree
Post by: grimes on 2020-12-16 14:35:15
You can do Copy & Paste.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-16 14:47:37
You can do Copy & Paste.
Drag and drop is what I found to work but perhaps this isn't that big of a deal if there's two separate databases.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-16 19:38:14
What it issue is.
1. Added SQL Tree to a panel in columns UI.
2. Queries don't match that of the Library>SQL Tree. Only the <Examples> folder shows up.
3. Also if I add any in the panel Library>SQL Tree both don't update and vice versa. Even after restarting fb2k.
4. See screenshot of the panel and the menu item open.
Actually, not an issue, this works as designed.

I can drag and drop between the menu item and the panel but is there any way to sync them or is this a limitation?
Not a limitation, more a wrong expectation. ;D All trees are independent from each other and you could also have multiple panels in ColumnsUI (or DUI) each with an own tree. The "menu item" is actually a DUI standalone panel.

if there's two separate databases.
Not two databases but as mentioned above this wouldn't be the reason for the described behavior.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-16 20:13:28
@fbuser‍, thanks for the explanation. The more I think about it, the more it makes sense. Just got a little fright when my panel displayed none of my queries.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-20 00:42:30
A cosmetic request for the panel. An edge style would be nice to have. Thanks.

(https://i.imgur.com/rnDj4Iz.png)
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2020-12-20 06:51:06
A cosmetic request for the panel. An edge style would be nice to have. Thanks.
It's already there (only for Columns UI). Right click in the relevant panel (not the one you can open from the menu, because it is DUI) and choose options.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2020-12-20 14:07:42
It's already there (only for Columns UI). Right click in the relevant panel (not the one you can open from the menu, because it is DUI) and choose options.
Thanks.

(https://i.imgur.com/zhfNCGh.jpeg)
 
Title: Re: foo_uie_sql_tree
Post by: Chris Norman on 2021-01-17 12:07:23
I have a requirement where I'd like to filter for all albums in one playlist that do not have album artist set correctly. Incorrect would be defined as the album having different track artists but an empty album artist (Album Artist could be either "various artists" or a real album artist.).

I was thinking of solving this by hashing numerically the artist for each grouping and than use an operation to determine if this is the same for each row. However I saw that sqlite has no such function built in.

Maybe there is an easier way of doing this? Many thanks in advance...
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-01-17 14:37:56
In general you can start with this query:

Code: [Select]
SELECT artist,
       album
FROM MediaLibrary
WHERE (album, totaltracks) IN (SELECT album, totaltracks
                FROM MediaLibrary
                WHERE "album artist" IS NULL
                GROUP BY album, totaltracks
                HAVING COUNT(DISTINCT artist)>1)
ORDER BY artist, album

Depending on your library size and structure you might run into performance issues. In this case you need to write the result of the subquery to a temporary table , add an index on album to it and rewrite the query by joining MediaLibrary and the temporary table.

If the the tags album and totaltracks are not sufficient to uniquely identify an album you need ot adjust the combination of album and totaltracks according to your tagging scheme.
Title: Re: foo_uie_sql_tree
Post by: KABIKAKA on 2021-02-07 16:32:51
Excuse me , I have a question need help very much . :'(
Edit the data in path
all playlist path : J:\ => I:\ (ex : J:\POP\A => I:\POP\A )

Quote
Execution error:
SQLite Error: (8) attempt to write a readonly database

Error while executing:
update Playlist
set path =
(
SELECT replace( path, 'J:\', 'I:\' ) path FROM Playlist
where path like 'J:\%'
)

Please help me :'(
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-02-07 17:13:41
Unfortunately it is not possible, what you want to achieve. Besides the fact, that you are using a readonly version of the virtual playlist table (please consult the help file how to use a read/write version of it), you can only update tags in this way, but not file attributes. You might try the plugin foo_playlist_revive to get what you want.
Title: Re: foo_uie_sql_tree
Post by: KABIKAKA on 2021-02-07 18:43:34
Unfortunately it is not possible, what you want to achieve. Besides the fact, that you are using a readonly version of the virtual playlist table (please consult the help file how to use a read/write version of it), you can only update tags in this way, but not file attributes. You might try the plugin foo_playlist_revive to get what you want.

I appreciate your help very much.  :-[
Now...music folders import ,  this process need to wait for a while.
Can I ask one more question?
Does the "Playlist name"  also have similar plug-ins to rename batches to replace keywords?
Quote
ex1:
Playlist name
[POP] AAA => [J-POP] AAA
[POP] BBB =>  [J-POP] BBB
[POP] CCC => [J-POP] CCC

ex2:
Playlist name
[C89] AAA => [C99] AAA
[C89] BBB =>  [C99] BBB
[C89] CCC => [C99] CCC
Originally wanted to use SQL to solve...but... :'(

Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-02-07 19:00:06
I'm not aware of any dedicated plugin, which is able to do this, but you could write a script for foo_spider_monkey_panel, which is doing what you want.
Title: Re: foo_uie_sql_tree
Post by: KABIKAKA on 2021-02-07 19:32:45
I'm not aware of any dedicated plugin, which is able to do this, but you could write a script for foo_spider_monkey_panel, which is doing what you want.

Ok!! I will try to study it. Thank you very much for your help and answers today. O:)
Have a good one!
Title: Re: foo_uie_sql_tree
Post by: dpc666 on 2021-02-26 13:58:03
I'm trying to get average playcount, but the fields just end up empty (except for the first one which shows the wrong value):

(https://i.imgur.com/bcTQv8D.png)

Which is weird because average rating works.

I'm using  a snippet that seems to have worked for some people years ago.

Edit: I found the solution. Playback Statistics data aren't normal tag fields, that's why in SQLite viewer settings, I had to change play_count from "Tag" to "TFExpression" and enter %play_count% in the text field so that it'd read the foo_playback_statistics data instead of Foobar's native play_count tag.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-02-26 14:39:06
This is most likely a problem with your data. You should try to use "CAST(play_count AS INT)" instead of just "play_count" for avg() as SQLite tries to identify the datatype of the field automatically. This doesn't always work as expected.

If this doesn't work, how is play_count defined for the MediaLibrary table and what is the result of the query (assuming that Absu has also play_count values)

SELECT artist,album, tracknumber, title, play_count
FROM MediaLibrary
WHERE artist IN ('Gates Of Ishtar','Absu')
ORDER BY 1,2,3;
Title: Re: foo_uie_sql_tree
Post by: dpc666 on 2021-02-26 15:13:44
Thanks but see my edit, it was because the data isn't stored in a tag at all when you have foo_playcount installed.

Anyway, I have another problem: When I use "ORDER BY play_count DESC" and send the results to a playlist, their order is just tracks ordered by playcount with no regard for albums. I tried "ORDER BY avg(play_count)" but that gives me a syntax error. How can I make it so that the results in the playlist are grouped by album and albums ordered by average playcount? Sorry if that's a dumb question, it's my first time messing with SQL.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-02-26 15:45:36
This is a bit more complicated.

Put this into the Batch tab of your query node (not tested by myself):

Code: [Select]
DROP TABLE IF EXISTS tmp_artist_playcount;
CREATE TABLE tmp_artist_playcount AS
SELECT artist,avg(play_count) play_count
FROM MediaLibrary
GROUP BY artist;

CREATE UNIQUE INDEX tmp_artist_playcount_uq ON tmp_artist_playcount(artist);

and this into the Query tab (you might adjust the queried columns and the order to your needs):
Code: [Select]
SELECT ml.artist "Artist"
FROM MediaLibrary ml JOIN tmp_artist_playcount t ON ml.artist=t.artist
ORDER BY  play_count DESC, ml.artist, album, tracknumber
Title: Re: foo_uie_sql_tree
Post by: veksha on 2021-05-13 02:35:30
Hi. I have a problem it seems. Queries are not populating any items in a tree.
I set "Click action: execute SQL"
but when I click it only briefly shows "database activity" and no elements in a tree are appearing after that.
(queries are working fine in SQLite console.)

Please help. Maybe i'm not using it the right way.

Foobar2000 1.6.5
SQLite viewer 1.1.0
SQL tree 2.0.3
Windows 10 x64
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-05-13 08:35:44
Maybe i'm not using it the right way.
Yes  ;D, but the help needs to be more specific here.

ExecuteSQL is just executing the batch part of the node in the background, nothing more. If you want to have the tree populated without any further action, you don't need the click action at all. Just right click on the node and select "Refresh".
Title: Re: foo_uie_sql_tree
Post by: veksha on 2021-05-13 16:18:43
ExecuteSQL is just executing the batch part of the node in the background, nothing more. If you want to have the tree populated without any further action, you don't need the click action at all. Just right click on the node and select "Refresh".
Refreshing it works! thank you for helping me out.
It was not clear for me that Refreshing will lead to building the tree. :)

EDIT: option "Refresh query: on use" is not triggering refresh on clicking. is it right? how can i trigger it?
On help page we read "whenever it is "used", i.e. expanding the node or clicking on the node". but i can't expand it when there is no "plus" button yet.
maybe this trigger works after nodes have been populated already and not when they are absent.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-05-13 17:14:56
option "Refresh query: on use" is not triggering refresh on clicking. is it right? how can i trigger it?
It will only be refreshed, if it could be needed, i.e. if the media library was changed since the last refresh in case the query is using the MediaLibrary table, or if the playlists were changed if the query is using the playlist table.

but i can't expand it when there is no "plus" button yet.
If this option is selected, the button for expanding the node is always shown, even if it has no items. But it looks like, that the button is only set either after a refresh or a program restart, but not by just selecting the relevant option.
Title: Re: foo_uie_sql_tree
Post by: veksha on 2021-05-13 18:04:04
But it looks like, that the button is only set either after a refresh or a program restart, but not by just selecting the relevant option.
Yes! I had to reopen/restart SLQ tree to make "plus" button show up. Thank you for all clarifications. :)
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2021-07-28 13:29:47
Great news! I found a new way to break your stuff. :D

I'd like to back my SQL code out of the nodes themselves and reference it in external files. For instance, a file in a directory called sql off the foobar2000 directory (in a portable installation) is called tree_albumartist.sql and contains the following:
Code: [Select]
DROP VIEW IF EXISTS tree_albumartist;
CREATE VIEW         tree_albumartist AS
SELECT   coalesce(
                  [album artist]
                , artist
                , '{no artist}'
                  )
       , album
    FROM MediaLibrary
GROUP BY coalesce(
                [album artist]
              , artist
              , '{no artist}'
                )
       , album
ORDER BY coalesce(
                [album artist]
              , artist
              , '{no artist}'
                )
       , album
       , discnumber
       , tracknumber

Then in the batch tab of a query, I write this:
Code: [Select]
SELECT eval(readfile('sql/tree_albumartist.sql'))

This works if the query action is set to Execute SQL or Send to SQLite console. However, if the action is to send it to a playlist, foobar2000 crashes. In that case, I also have some code in the query tab:
Code: [Select]
SELECT * FROM tree_albumartist

I can work around this if there's nothing in the batch that needs to be run every time the query is refreshed by executing it in a different node, and then SELECT *… as above in the node I want to use it with.

Is there a better way to achieve this sort of thing?

Here's the crash report from a clean installation.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2021-07-28 13:36:38
I have a feature request as well. I'd like to have the option to hide the Refresh all action from out of the context menu. I rarely want to do that, but I accidentally invoke it from time to time. Usually I'm trying to refresh one query, and I miss the node when I right-click around it and get the nearby whitespace instead. Then I absentmindedly select the Refresh all command, missing the fact that the Refresh one has rightly not shown up above it.

Whenever the next version hits, that is. Thanks for the wonderful components. :D
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-07-28 16:04:55
I was wondering if I could get help with a query to get all albums (on a per album basis) where the ADDED_TIMESTAMP tag is different.

I don't even know where to start.

Thanks in advance.

Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-07-29 23:01:46
Great news! I found a new way to break your stuff. :D
:D

Is there a better way to achieve this sort of thing?
Thanks for reporting the issue. Of course it shouldn't crash, but you should get an error message in such a case. You can probably make it working by adding the pseudo column "metadb_handle" to the select clause for the view.
I have a feature request as well. I'd like to have the option to hide the Refresh all action from out of the context menu.
This is a quite reasonable request. Actually I'm not really happy anymore with that option. The possibility to hide it will be added to the next version and will be the default.
I was wondering if I could get help with a query to get all albums (on a per album basis) where the ADDED_TIMESTAMP tag is different.
It's not tested, but if you add %added_timestamp%, similar to the default column %album artist%, to the media library table in the preferences, you should get the desired result with the following query:
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE ("%album artist%",album) IN
      (SELECT "%album artist%",album
       FROM MediaLibrary
       GROUP BY "%album artist%",album
       HAVING count(distinct "%added_timestamp%")>1)


Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-07-30 12:25:54
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE ("%album artist%",album) IN
      (SELECT "%album artist%",album
       FROM MediaLibrary
       GROUP BY "%album artist%",album
       HAVING count(distinct "%added_timestamp%")>1)

Thanks but it's giving me this error.

I've tried to fix it with this but I haven't a clue when it comes to SQL.

Code: [Select]
SELECT a."%album artist%" "Album Artist"
FROM MediaLibrary a JOIN (SELECT album,"%album artist%"
      FROM MediaLibrary
      GROUP BY "%album artist%",album
      HAVING count(distinct "%ADDED_TIMESTAMP%")>1)


Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-07-30 21:36:18
Thanks but it's giving me this error.
This seems to be a problem with the SQLite version (3.20.0), which is used by the released version of foo_sqlite. It is no problem with my work-in-progress version of foo_sqlite, which is using SQLite 3.36.0. Maybe such constructs just weren't supported with the older SQLite version. However, you can rewrite the query as follows, so that it shold work also with the released version of foo_sqlite:

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       GROUP BY 1
       HAVING count(distinct "%added_timestamp%")>1)
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-07-30 22:27:57
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       GROUP BY 1
       HAVING count(distinct "%added_timestamp%")>1)

That's not bringing up any albums that have different values for the added_timestamp tag as you can see here's an EP where there are two values on the same EP.

To test if I could query the added_timestamp tag I tried this and it brings up my entire collection so querying that tag isn't the issue.

Code: [Select]
SELECT "%added_timestamp%"
FROM MediaLibrary

Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-07-30 22:55:38
That's not bringing up any albums that have different values for the added_timestamp tag as you can see here's an EP where there are two values on the same EP.
This is strange, but for me query is working for the released foo_sqlite version just with another tag, which is defined as title format expression. And actually I don't see any reason, why it shouldn't work for you. What is the result of
Code: [Select]
SELECT "%album artist%",album,"%added_timestamp%"
FROM MediaLibrary
WHERE album LIKE 'Baroque EP%'
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-07-30 23:37:42
Code: [Select]
SELECT "%album artist%",album,"%added_timestamp%"
FROM MediaLibrary
WHERE album LIKE 'Baroque EP%'

That brings up that EP alright.

What does the % in EP% do?

I tried to rebuild the database from examples that are built in to see if that would help but no joy.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-07-31 07:41:39
That brings up that EP alright.
Sorry, but I need to see the exact result from the SQLite console.

What does the % in EP% do?
This is a joker. I was just too lazy to specify the whole album title.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-07-31 09:29:30
Sorry, but I need to see the exact result from the SQLite console.
Thanks for letting me know that.

Here are the results for the two queries you've given. It appears there's no tag values returned for the added_timestamp tag.

Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-07-31 09:52:08
quote author=stevehero link=msg=1001292 date=1627720170] It appears there's no tag values returned for the added_timestamp tag.[/quote]And this is exactly the problem. You obviously missed this
Quote
but if you add %added_timestamp%, similar to the default column %album artist%, to the media library table in the preferences
So you need to go in the preferences to "Media Library -> SQLite viewer -> MediaLibrary table -> Add ..." and change the following fields:

Type: TFExpression
Name: %added_timestamp%
Title format expression: [%added_timestamp%]

Leave all other fields as they are.

Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-07-31 10:24:29
So you need to go in the preferences to "Media Library -> SQLite viewer -> MediaLibrary table -> Add ..." and change the following fields:

Thanks very much for your help. That worked.

I changed the query slightly to sort by album and tracknumber and only display albums that have a totaltracks greater than 1.

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct "%added_timestamp%")>1)
ORDER BY album, tracknumber
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-08-01 17:27:56
Need help again ;)

I'm trying to modify that code you gave to get albums that have different www and album artist tags.

I've tried to change added_timestamp but that doesn't work.

For album artist tag.
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
      FROM MediaLibrary
      WHERE "totaltracks" > 1
      GROUP BY 1
      HAVING count(distinct "%album artist%")>1)
ORDER BY album, discnumber, tracknumber

For www tag.
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct "%www%")>0)
ORDER BY album, discnumber, tracknumber
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-08-01 17:40:51
For album artist tag.
This cannot work as you identify an album uniquely by %album artist% and album. You will never have more than one distinct value for %album artist% in this case. If you are using an own tag to uniquely identify an album, e.g. "%album id%", you should use this instead of "%album artist%"||album. Otherwise you cannot get what you want.


For www tag.
It does not work, because this part of the query is wrong:
Code: [Select]
HAVING count(distinct "%www%")>0
It has to be
Code: [Select]
HAVING count(distinct "%www%")>1
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-08-01 17:59:36
BTW, you don't need to define %www% as TFExpression, you can just define www as normal tag, which is slightly faster and the relevant part of the query would be this:
Code: [Select]
HAVING count(distinct www)>1
Double quotes are not necessary here as www doesn't contain any special characters.[/code]
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-08-01 18:55:44
Thanks for your help.

Good job I format my album tag album [catolog #] so it will work fine. I just need to filter out albums that have the names Singles, SC and BOOTLEG.

Edit:
Got this album artist to work by using the % which I assume matches any character like you mentioned in a previous post.

Here's that attempt at that.
Code: [Select]
SELECT album,"album artist"
FROM MediaLibrary
WHERE album IN
      (SELECT album
       FROM MediaLibrary
       WHERE album NOT LIKE "%BOOTLEG%"
           AND  album NOT LIKE "%Singles%"
           AND  album NOT LIKE "%[SC]"
       GROUP BY 1
       HAVING count(distinct "album artist")>1)
ORDER BY album, discnumber, tracknumber

Then for Catalog # it doesn't work, even though it is in the MediaLibrary table.

I've removed one catalog # from one track in an album to test but nothing is showing.

This does work, kinda. I cleared the tag so the contents weren't the same string although it was empty and it didn't show.

Any way to modify this so that works when the tags are empty also?

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct "catalog #")>1)
ORDER BY album, discnumber, tracknumber
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-08-01 21:59:54
Any way to modify this so that works when the tags are empty also?
Using
Code: [Select]
HAVING count(distinct coalesce("catalog #",''))>1
should do this.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-08-01 23:49:14
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
      FROM MediaLibrary
      WHERE "totaltracks" > 1
      GROUP BY 1
      HAVING count(distinct coalesce("catalog #",''))>1
ORDER BY album, discnumber, tracknumber

Returns an error:
Code: [Select]
Execution error:
SQLite Error: (1) near "tracknumber": syntax error

Error while preparing the first statement of:
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct coalesce("catalog #",''))>1
ORDER BY album, discnumber, tracknumber
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-08-02 06:26:02
Returns an error:
Execution error:
SQLite Error: (1) near "tracknumber": syntax error
Yes, because you dropped the parenthesis after the having clause. Try this:

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
      FROM MediaLibrary
      WHERE "totaltracks" > 1
      GROUP BY 1
      HAVING count(distinct coalesce("catalog #",''))>1)
ORDER BY album, discnumber, tracknumber
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2021-08-02 12:21:08
I'm just getting to trying your suggestion now, and I'm still having crashes. I've added MetaDb_Handle to the sql file (and removed the GROUP BY, since that was giving me trouble. I guess I still don't get when I want that and when I don't, but never mind that for now). It now looks like this:
Code: [Select]
DROP VIEW IF EXISTS tree_albumartist;
CREATE VIEW         tree_albumartist AS
SELECT   coalesce(
                  [album artist]
                , artist
                , '{no artist}'
                  )
      AS coalartist
       , album
       , MetaDb_Handle
    FROM MediaLibrary
ORDER BY coalartist
       , album
       , discnumber
       , tracknumber

The batch tab is unchanged:
Code: [Select]
SELECT eval(readfile('sql/tree_albumartist.sql'))

The query tab now looks like this:
Code: [Select]
SELECT coalartist, album FROM tree_albumartist

I can execute the batch as is in a separate tree node, remove it from the node with the query, and refresh the query just fine. It is just when that query and batch are in the same node that there is a crash on the refresh. In that case, I can click on the node and send every track to a playlist or to the SQL console, but a refresh crashes with either action selected.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-08-03 00:09:40
Yes, because you dropped the parenthesis after the having clause. Try this:
Silly me. Thanks so much for your help.

One last thing is it possible to query all tags.

What I was after was to check all tags for any unwanted whitespaces. If SQL can support regular expression then this would find any trailing, leading and additional spaces with a regular expression like so below.

Code: [Select]
^\s+|\s+$|\s+(?=\s)

My bad attempt at it:
Code: [Select]
SELECT *
FROM MediaLibrary
WHERE * LIKE "% " OR * LIKE " %" OR * LIKE "%  %'
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-08-03 06:04:40
I can execute the batch as is in a separate tree node, remove it from the node with the query, and refresh the query just fine. It is just when that query and batch are in the same node that there is a crash on the refresh. In that case, I can click on the node and send every track to a playlist or to the SQL console, but a refresh crashes with either action selected.
Ok, I found the root cause of the problem and it is already fixed for the next version. However the same as mentioned already for foo_sqlite applies also here, that the release will need some time due to some substantial internal changes for foo_sqlite and also for foo_uie_sql_tree.

The problem in this case is that the batch part is returning a result due to the select. For the time being you can therefore change the batch like this
Code: [Select]
SELECT eval(readfile('sql/tree_albumartist.sql')) WHERE 1=2
as a workaround.

One last thing is it possible to query all tags.
No, you always need to explicitely specify the wanted columns in the where clause, e.g. for artist and title:
Code: [Select]
SELECT *
FROM MediaLibrary
WHERE 1 IN (regexp_search('^\s+|\s+$|\s{2,}',artist),regexp_search('^\s+|\s+$|\s{2,}',title))
You don't need the lookahead in the regular expression here and you could put the regular expression into an own table to not repeat the expression literally for each column.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-08-03 14:02:28
No, you always need to explicitely specify the wanted columns in the where clause, e.g. for artist and title:
Code: [Select]
SELECT *
FROM MediaLibrary
WHERE 1 IN (regexp_search('^\s+|\s+$|\s{2,}',artist),regexp_search('^\s+|\s+$|\s{2,}',title))
You don't need the lookahead in the regular expression here and you could put the regular expression into an own table to not repeat the expression literally for each column.
That's a pity, I thought there was a way to perhaps do a loop over all the columns in the db.

That was a regular expression for replacing whitespace and I forgot to remove the lookahead.

Thanks for the example.

Edit:

It shows up a track. I've checked both the artist and title for whitespaces.

Although it appears to be the à that is causing an issue in this.

See screenshot below of the track Voilà.

Changing the regular expression to the code below resolves it but it would be interesting to know why it's not working with \s and would be the better solution.

Code: [Select]
^ +| +$| {2,}
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-08-03 18:19:24
but it would be interesting to know why it's not working with \s
Most likely because the regular expression implementation of the released version of foo_sqlite does not support unicode strings. This is also something, which is already implemented for the next version. As mentioned in the foo_sqlite thread it will need some time before it can be released and I hope it will be this year, but I cannot guarantee it.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2021-08-03 19:06:58
Most likely because the regular expression implementation of the released version of foo_sqlite does not support unicode strings. This is also something, which is already implemented for the next version. As mentioned in the foo_sqlite thread it will need some time before it can be released and I hope it will be this year, but I cannot guarantee it.
No worries. Thanks.
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2021-08-03 23:08:42
I ran into a bug where I tried to delete a specific item in the SQL Tree, but it ended up deleting ALL items and folders entirely so that the SQL Tree window was left blank.  I wasn't able to get my configuration back by manually using the files from the configuration folder - I ended up reverting to an entire foobar2000 directory snapshot I had taken a couple days earlier.

I have 24 items or folders side-by-side at the root level.  Under each folder are then more items or folders.  I'm not aware of a way to multi-select things in the SQL Tree window, so I am still not sure how I managed to delete 24 parents and all their children in a single mouse click.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-08-04 07:45:11
I ran into a bug where I tried to delete a specific item in the SQL Tree, but it ended up deleting ALL items and folders entirely so that the SQL Tree window was left blank.
First of all, thanks for reporting the problem. So far there were only a very few reports with the same behavior after a version upgrade of foo_uie_sql_tree. What is actually happen here, is that the information for the insivible root node of the panel gets somehow lost. Unfortunately this behavior is not reproducible for me.

I wasn't able to get my configuration back by manually using the files from the configuration folder - I ended up reverting to an entire foobar2000 directory snapshot I had taken a couple days earlier.
This should normally not be necessary, but besides the configuration files for foo_uie_sql_tree (foo_uie_sql_tree.dll.cfg and foo_uie_sql_tree.db) you also need to restore either foo_ui_columns.dll.cfg or foo_ui_std.dll.cfg depending on the user interface you are using.
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2021-08-07 22:56:49
Quote
you also need to restore either foo_ui_columns.dll.cfg or foo_ui_std.dll.cfg depending on the user interface you are using.

This is probably the missing piece I did not do.

I am using SQL Tree inside foo_popup_panels.  Therefore, I can close (hide) the window and reopen it without database activity.  (I noticed that if I use SQL Tree from the Library menu of foobar2000, there is database activity each time.)
SimplePortal 1.0.0 RC1 © 2008-2021