Skip to main content

Notice

Please note that most of the software linked on this forum is likely to be safe to use. If you are unsure, feel free to ask in the relevant topics, or send a private message to an administrator or moderator. To help curb the problems of false positives, or in the event that you do find actual malware, you can contribute through the article linked here.
Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 137946 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

[fb2k v2] SQL Tree (foo_uie_sql_tree)

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.

Upgrade Note: If you're upgrading from an older version than 4.0.0, it is highly recommended to backup your current configuration before.

Prerequisites:
  • foobar 2000 2.0+
  • DefaultUI or ColumnsUI 2.0+
  • foo_sqlite

Download
Version history


foo_uie_sql_tree

Reply #1
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.


foo_uie_sql_tree

Reply #3
any chance to get a dui version? :-)


foo_uie_sql_tree

Reply #5
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?

foo_uie_sql_tree

Reply #6
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.


foo_uie_sql_tree

Reply #8
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.


foo_uie_sql_tree

Reply #10
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



foo_uie_sql_tree

Reply #11
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

foo_uie_sql_tree

Reply #12
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.

foo_uie_sql_tree

Reply #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


foo_uie_sql_tree

Reply #14
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?

foo_uie_sql_tree

Reply #15
Goto View -> Popup panels -> Configure -> <your panel> and select "Hide on close"

foo_uie_sql_tree

Reply #16
@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.

foo_uie_sql_tree

Reply #17
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"

foo_uie_sql_tree

Reply #18
If there was a port of this to the Default UI, I'd use/test it.


foo_uie_sql_tree

Reply #20
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
Warning: fb2k is addictive.

foo_uie_sql_tree

Reply #21
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

foo_uie_sql_tree

Reply #22
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

foo_uie_sql_tree

Reply #23
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"?

foo_uie_sql_tree

Reply #24
^from earlier in the thread....

any chance to get a dui version? :-)
No. But you can use this component with 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.