Hydrogenaudio Forums

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.

SimplePortal 1.0.0 RC1 © 2008-2019