HydrogenAudio

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

Title: [fb2k v2] SQL Tree (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.

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

Prerequisites:

Download (https://www.foobar2000.org/components/view/foo_uie_sql_tree)
Version history (https://www.foobar2000.org/components/view/foo_uie_sql_tree/releases)

Title: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2011-09-27 12:57:30
Thanks fbuser. A great component. Works well and fast even with >100k tracks.

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

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

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

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

Here the SQL syntax:

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

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

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

Alessandro

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

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

Now i have:

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


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

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

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


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

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


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


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


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

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

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

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


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

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

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

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

Code: [Select]
//Album length

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


//Album playcount

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


//Album rating

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


//Albums per artist

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


//Albums per date

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


//Albums per playlist

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


//Artist length

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


//Artist playcount

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


//Artist rating

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


//Artists per date

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


//Artists per playlist

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


//Date playcount

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


//Date rating

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


//Media library length

SELECT format_length(sum(length_seconds)) length
  FROM medialibrary


//Playlist playcount

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


//Playlist rating

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


//Tracks per album

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

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

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

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

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

Call path:
entry=>app_mainloop

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

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

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

Crash location:
Module: foo_uie_sql_tree
Offset: 1C799h

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

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

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

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

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

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


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

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

Example:

Instead of medialibrary from the playlist All Music.

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

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

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

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

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


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

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

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


(only 'last played' considered)

Omitting WHERE clause: overall top artists played



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

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

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

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


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

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

For the batch, I have this:

Code: [Select]
DROP VIEW IF EXISTS random_view4;

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

DROP VIEW IF EXISTS random_view5;

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

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

The query is easy too:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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


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

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

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

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


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


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

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


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


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

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

Alessandro

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

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

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

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

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

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

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

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

....but any chance of some screenshots?   

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


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

....but any chance of some screenshots?   

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thanx again!

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

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

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

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

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

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


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


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


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


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

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

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

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

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

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

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

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

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

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


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

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

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

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


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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

OK, fair enough.

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

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

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

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


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

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

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

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


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

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



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

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

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

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


OUTPUT->

comment_mv                          content_group_mv          sub_genre_mv
progressive alternative rock      indie                              progressive alternative rock indie

However, when I add the following statement:

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

there is no output at all.  I have also tried

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

Any suggestions / tips?

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

HTH.

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

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

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

2)  It doesn't seem to be possible to "send to playlist" if the query includes "count(whatever) count" in the SELECT clause.  It'll send to the tree console, but it's impossible to populate a playlist.  Is this a bug / should populating a playlist selecting count be possible?

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

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


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


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

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

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

2)  It doesn't seem to be possible to "send to playlist" if the query includes "count(whatever) count" in the SELECT clause.  It'll send to the tree console, but it's impossible to populate a playlist.  Is this a bug / should populating a playlist selecting count be possible?
As count() is an aggregrate function, this is not possible. See here (http://www.palm-internet.eu/foobar2000/components/foo_uie_sql_tree/QueryNodeDialogAction.html#click_action).

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

4)  Finally, is there an easier way to edit the organization of the SQL tree (manually by text editor?).  If I want to change my folder structure I have to create new queries from scratch and copy-paste or re-write, it's really slow to reorganize the tree...
The next version will support copying/moving nodes.
Title: foo_uie_sql_tree
Post by: r0k on 2012-10-09 10:16:43
2)  It doesn't seem to be possible to "send to playlist" if the query includes "count(whatever) count" in the SELECT clause.  It'll send to the tree console, but it's impossible to populate a playlist.  Is this a bug / should populating a playlist selecting count be possible?

But depending on what you want to count, it can be possible to create a view that contains your agregate function, and then use that view in a nested query to populate your playlist with something like :
SELECT ... whatever you want
FROM library
WHERE track/album/watever column IN SELECT ... FROM your-view WHERE your-count IS xxx


I would have to know exactly what you want to do, and then look at SQLite doc to help you more. If you are somewhat familiar with SQLite you can probably figure it yourself faster
Title: foo_uie_sql_tree
Post by: neothe0ne on 2012-10-09 10:26:39
Thanks, #3 indeed is missing NULL genres, now the numbers add up.
Title: foo_uie_sql_tree
Post by: Alatar on 2013-01-04 07:19:56
Hello,

I was playing with this plugin and run into problems. Queries were super slow, so I created one script which 'caches' some data in temp tables and here began strange things ...
Tree Looks fine but playlist get updated only once after running query which create temp tables. I can unfold tree, run my script, select some node and it show playlist properly but any subsequent use gives nothing. And one thing, maybe it's sqlite normal but creating temp tables and feeding it with query data is 5x faster than running this query on it's own

Update script, batch only:
Code: [Select]
DROP TABLE IF EXISTS artist_info;
CREATE TEMPORARY TABLE artist_info(
  artist TEXT PRIMARY KEY,
  ca INTEGER
);
 
INSERT INTO artist_info
SELECT
  artist,
  count(distinct album) as ca
FROM
  MediaLibrary
GROUP BY
  artist
;

DROP TABLE IF EXISTS album_info;
CREATE TEMPORARY TABLE album_info (
  dir TEXT PRIMARY KEY,
  artist TEXT,
  album TEXT
);
INSERT INTO album_info
SELECT
  substr(path,0,length(path)-length(filename_ext)) as dir,
  CASE WHEN
    count(distinct ml.artist)>1 THEN 'Various Artists'
      WHEN
    ai.ca=1 THEN ml.artist||" - "|| ml.album
      ELSE
    ml.artist
  END AS artist,
  CASE WHEN ai.ca=1 THEN null ELSE ml.album END as album
FROM
  MediaLibrary as ml
LEFT JOIN
  artist_info AS ai WHERE ai.artist=ml.artist
GROUP BY
  dir
;

DROP TABLE IF EXISTS tree_info;
CREATE TEMPORARY TABLE tree_info(
  MetaDB_Handle INTEGER PRIMARY KEY,
  artist TEXT,
  album TEXT
);
INSERT INTO tree_info
SELECT
  MetaDB_Handle,
  ai.artist,
  ai.album
FROM MediaLibrary as ml
LEFT JOIN album_info as ai WHERE glob(ai.dir||'\*',ml.path)
;
CREATE INDEX album_artist_idx ON tree_info (artist,album);

Tree view script (uses Advanced->omit Null)
Code: [Select]
SELECT 
  artist,
  album
FROM tree_info
GROUP BY artist,album
Title: foo_uie_sql_tree
Post by: fbuser on 2013-01-04 08:16:52
Queries were super slow
This usually happens for complex queries, which are referring the virtual tables (MediaLibrary,Playist) more than once.

Tree Looks fine but playlist get updated only once after running query which create temp tables. I can unfold tree, run my script, select some node and it show playlist properly but any subsequent use gives nothing.
This is the expected behaviour. The column MetaDB_Handle is only accessible when the virtual tables are queried directly. Writing this column into a temporary table makes it useless. For the first use it works probably only because of internal caching mechanisms, but even this will not produce reliable results.

So, you should not create the table tree_info. Instead you should try to use the query for building this table combined with the query using this table directly.

And one thing, maybe it's sqlite normal but creating temp tables and feeding it with query data is 5x faster than running this query on it's own
In general, depending on the use case increasing the performance by working with temporary tables is something you can observe for all relational database systems.

If you are referring to a single query, it is probably more a matter what you are actually measuring. Filling a temporary table is a database internal action. But if you are filling the tree itself or the list in the SQL Tree console additional actions need to be executed.
Title: foo_uie_sql_tree
Post by: newmatrix on 2014-12-06 16:58:04
It is possible to add to this wonderful component options - "transparent background" and "off vertical scrollbar" ?
Title: foo_uie_sql_tree
Post by: fbuser on 2014-12-06 17:24:44
It is possible to add to this wonderful component options - "transparent background" and "off vertical scrollbar" ?
Sorry, but I won't add these two options.
Title: foo_uie_sql_tree
Post by: angriestchair on 2015-11-04 23:35:57
This is very nice so far. I haven't tried too many queries out yet, but getting there. Is it possible to add the tree console and new query dialogs (mostly for the query editing) as panels? If not, could the functionality be added in a update? Also, could an option be added to set the default values in the action tab? I keep forgetting to set mine to open the console and then wonder why it won't when I click it.

Thanks for this though. :D I've missed my database class.
Title: foo_uie_sql_tree
Post by: Sue Dunham on 2015-11-15 05:29:14
I've been having trouble with a corner case of one query that mostly works. Some albums have split values in a custom [artist sort] field to facilitate cross-referencing. For instance, Layla and Other Assorted Love Songs is sorted on both "Derek Dominos" and "Clapton Eric Dominos". Those sort values don't actually appear, but "Derek and the Dominos" does in both cases.

Furthermore, the list of artists is broadly grouped by first initial, so that both of those entries appear under "C to D". The trouble seems to be that when one album appears twice under the same initials heading the songs will be sent to the playlist unsorted. It works fine when the cross-references are in different initials headings, as in "Hitchcock Robyn Soft Boys" and "Soft Boys", but "C to D" is messing up two more albums (Cult/Death Cult, Davis Coltrane/Coltrane Davis) and "P to S" claims the last problem on Pop Iggy/Stooges.

All of the other albums—cross-referenced or not—work fine; it's just these four that have me scratching my head.

Batch
Code: [Select]
DROP VIEW IF EXISTS album_artist_query2;
CREATE VIEW album_artist_query2 AS
SELECT coalesce([album artist],[artist],"(no artist)") coal_artist,
       coalesce([album artist sort],[album artist],[artist sort],[artist],"(no artist)") coal_artist_sort,
       album,
       coalesce([album sort],album,"(no album)") album_sort,
       coalesce([date sort],date,"unkn") date_display,
       coalesce([release sort],'01') release_sort,
       discnumber,
       tracknumber,
       MetaDb_Handle
FROM MediaLibrary
WHERE path NOT LIKE "%\hide\%";

DROP VIEW IF EXISTS album_artist_init_query;
CREATE VIEW album_artist_init_query AS
SELECT (CASE WHEN (coal_artist_sort >="0" AND coal_artist_sort <="Bzzz") THEN "# to B"
        WHEN coal_artist_sort >="C" and coal_artist_sort <="Dzzz" THEN "C to D"
        WHEN coal_artist_sort >="E" and coal_artist_sort <="Gzzz" THEN "E to G"
        WHEN coal_artist_sort >="H" and coal_artist_sort <="Lzzz" THEN "H to L"
        WHEN coal_artist_sort >="M" and coal_artist_sort <="Ozzz" THEN "M to O"
        WHEN coal_artist_sort >="P" and coal_artist_sort <="Szzz" THEN "P to S"
        ELSE "T to Z, Various" END) init_group,
       album||" ("||date_display||")" album_display,
       date_display,
       coal_artist,
       coal_artist_sort,
       album_sort,
       release_sort,
       discnumber,
       tracknumber,
       MetaDb_Handle
FROM album_artist_query2;


Query
Code: [Select]
SELECT init_group,
       coal_artist,
       album_display
FROM album_artist_init_query
GROUP BY album_display,coal_artist_sort,init_group
ORDER BY coal_artist_sort,date_display,album_sort||coal_artist_sort,album_display,
         release_sort,discnumber,tracknumber
Title: foo_uie_sql_tree
Post by: fbuser on 2015-11-15 10:06:48
Is it possible to add the tree console and new query dialogs (mostly for the query editing) as panels?
No, it is not possible and at least for the query dialog it won't be possible in the future. For the tree console, I have not decided yet, but probably it won't also be possible.

Also, could an option be added to set the default values in the action tab?
Yes, this could be added, but to be honest, it will not happen in the near future as my spare time to work on this is currently very limited.

I've been having trouble with a corner case of one query that mostly works. Some albums have split values in a custom [artist sort] field to facilitate cross-referencing. For instance, Layla and Other Assorted Love Songs is sorted on both "Derek Dominos" and "Clapton Eric Dominos". Those sort values don't actually appear, but "Derek and the Dominos" does in both cases.
I could reproduce this. The problem is that the duplicates are removed in an arbitrary order, if the option "Remove duplicates when sending to a playlist" is checked (this is the default).

You could untick this option, but it would mean, that the affected songs will be displayed twice in the playlist. Afterwards you could call "Edit -> Remove duplicates" from the menu, which is removing the duplicates in a proper way.

I will set this issue on my fix list, but as I said above, it will take some time to do it.

By the way, the order-by clause of your final query should have init_group in the first position, otherwise you could have duplicate tree entries. That this is currently not the case is just a coincidence.

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

Thanks for the update.

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

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

1. I already noticed this, too. It will be fixed with the next release of foo_sqlite (coming soon).
2. I'm not really sure, what you mean. Do you mean the graphical glitches after expanding or collapsing a node? If yes, it will be fixed with the next release of foo_uie_sql_tree.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 11:28:32
Hi fbuser
Good to see you found some time to update this component. Thank you for your efforts, I've been using this component for several years now and use it as my way into my large (100k+ tracks) library.

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

Thanks again.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 11:49:16
I'd also request that you add a Refresh option to just refresh the query under the menu.
It's already there and was already there from the beginning.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 11:52:43
Quote
It's already there and was already there from the beginning.

You are correct both are there if you right click on the root node but right clicking on sub-nodes only shows Refresh all  Ctrl+F5
Title: Re: foo_uie_sql_tree
Post by: grimes on 2016-08-03 14:19:27
Thanks fbuser, works like a charm. 8)

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

Error while preparing the first statement of:
SELECT format_length(sum(length_seconds)) length
FROM medialibrary
WHERE CAST(format_length(sum(length_seconds)) AS TEXT) = '15wk 3d 21:31:24'
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 14:46:18
You are correct both are there if you right click on the root node but right clicking on sub-nodes only shows Refresh all  Ctrl+F5
This is correct. Actually this shouldn't also be shown, when clicking on a subnode. The only option there should be "Properties", because all other options belong to the tree and not a single subnode. At the time I implemented this component, I had the opinion, that the options, which are affecting the whole tree, should also be available, when clicking on a node. But in a sense of a clean UI this is not correct. As you cannot refresh a subnode, you don't have a relevant menu entry for this.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 14:50:42
Thanks fbuser, works like a charm. 8)

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

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

Edit: I looked only on the select statement in the error message, but not on the original statement. So, the reason for this error is as written in my last sentence. So, to get it working use "Send to SQLite console" as action for the relevant node and put the query into the batch section of the node.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2016-08-03 15:14:43
Fixed, thanks.
Remark: I use media library length only for maintenence purposes. My playlist "All Music" should have the same length as media library length.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 16:24:15
You are correct both are there if you right click on the root node but right clicking on sub-nodes only shows Refresh all  Ctrl+F5
This is correct. Actually this shouldn't also be shown, when clicking on a subnode. The only option there should be "Properties", because all other options belong to the tree and not a single subnode. At the time I implemented this component, I had the opinion, that the options, which are affecting the whole tree, should also be available, when clicking on a node. But in a sense of a clean UI this is not correct. As you cannot refresh a subnode, you don't have a relevant menu entry for this.

So imagine an expanded tree where the first level of sub-nodes takes several screens/panel heights. I have to scroll up to the root node to refresh the query. Not very helpful but if that's how you have designed it, so be it.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2016-08-03 17:23:11
I have to scroll up to the root node to refresh the query.
This can be easily done with the windows standard behavior by pressing the "cursor left" key once if the node is collapsed or twice if the node is expanded.
Title: Re: foo_uie_sql_tree
Post by: Black_Over_Bills_Mothers on 2016-08-03 17:40:11
I have to scroll up to the root node to refresh the query.
This can be easily done with the windows standard behavior by pressing the "cursor left" key once if the node is collapsed or twice if the node is expanded.

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

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

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

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


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


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

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

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

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

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

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

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

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

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

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

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

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

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);

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

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

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

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

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

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

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

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

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

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

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);

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

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

CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Error while preparing the first statement of:

SELECT * FROM MEDIALIBRARY

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

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

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

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

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

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

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

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

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

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

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

CREATE UNIQUE INDEX tmp_paths_in_uq ON tmp_paths(path);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

For query it's:

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

And the batch tab when I've pressed Validate:

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

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

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

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

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

Code: [Select]
Invalid node query

Invalid query statement:

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

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

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

Code: [Select]
Invalid node query

SQLite Error: (1) no such table: Albums

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Which is weird because average rating works.

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

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

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

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

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

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

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

CREATE UNIQUE INDEX tmp_artist_playcount_uq ON tmp_artist_playcount(artist);

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

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

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

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

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

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

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

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

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

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

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

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

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

I don't even know where to start.

Thanks in advance.

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

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


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

Thanks but it's giving me this error.

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

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


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

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

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

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

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

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

That brings up that EP alright.

What does the % in EP% do?

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

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

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

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

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

Leave all other fields as they are.

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

Thanks very much for your help. That worked.

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

One last thing is it possible to query all tags.

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

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

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

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

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

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

Thanks for the example.

Edit:

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

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

See screenshot below of the track Voilà.

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

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

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

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

This is probably the missing piece I did not do.

I am using SQL Tree inside foo_popup_panels.  Therefore, I can close (hide) the window and reopen it without database activity.  (I noticed that if I use SQL Tree from the Library menu of foobar2000, there is database activity each time.)
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2021-12-13 12:35:00
Complications gather. As above, I've been running scripts with code like this in the batch tab with Execute SQL as the action:

Code: [Select]
SELECT eval(readfile('sql/file.sql'))

Now I'd like to run several such files but have not found the way to do so. Here are some alternatives that haven't worked, unless I've screwed something up elsewhere.

Code: [Select]
SELECT eval(readfile('sql/file.sql'));
SELECT eval(readfile('sql/file_2.sql'));

Code: [Select]
SELECT eval(
            readfile('sql/file.sql')
         || readfile('sql/file_2.sql')
            );

Code: [Select]
SELECT eval(
            quote(readfile('sql/file.sql'))
         || quote(readfile('sql/file_2.sql'))
            );

Is there a way to do something like this? Is this maybe something addressed in the next versions of the components, when they come? Am I better off combining my files elsewhere and running one big file here?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-12-14 17:28:21
Is there a way to do something like this?
As you didn't mention, what your actual problem is and you also didn't provide the full configuration for the relevant tree node, I can only guess that you experience crashes. In this case it's probably the same problem as you reported already here (https://hydrogenaud.io/index.php?topic=90978.msg1001412#msg1001412).  The same workaround is the same:

Code: [Select]
SELECT eval(readfile('sql/file.sql')) WHERE 1=2;
SELECT eval(readfile('sql/file_2.sql')) WHERE 1=2;

Another possibility could be to clear the query part completely, if you only want to use "Execute SQL" as action. At least for me this worked without crash.

Is this maybe something addressed in the next versions of the components, when they come?
Yes it is, see link above. However I still can't predict, when it will be. I'm making progresses with the substantial, mostly internal, changes I'm currently working on, but not as fast as I'd like to. But I'm quite confident, that it will be in the next 3-3,5 months, if nothing unexpected happens, which will block me from working on the changes.

Am I better off combining my files elsewhere and running one big file here?
No, this should not be necessary.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2021-12-15 20:27:05
No, this isn't a crashing issue. In this instance, there isn't anything in the Query tab. Essentially, one of the scripts is run to update data in a table, and the second then rewrites a regex in a regexes table that queries the first table for its values. I have tables that contain prefixes like "The", "Les" and the like or delimiter-type words like "And" or "With", etc., and then the regex definitions group_concat() those columns into regex option groups. In some cases, further scripts may update further regexes that also query the same table. At present, each such script has its own tree node to execute it; I'm trying to combine existing scripts to run sequentially so that updating the table will refresh all dependent regexes.

What I'm seeing is that the expected change in the regex at the end of the process is not occurring. The old value is still present. Running the script from its own node will make the change, but running it second in a multi-script node does not. I believe I did try the WHERE 1 = 2 workaround as well as the attempts I mentioned, but that didn't change the result in this instance.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2021-12-15 21:39:59
First what you describe smells like it should be implemented with triggers instead of multiple scripts. Second there are several possibilities why the second script might not see the changes of the first script. Actually I'm pretty sure, that it is not a problem of foo_uie_sqltree (or foo_sqlite) in the first place. It's seems to be more related to the behavior of SQLite or the extensions (eval, readfile), which you are using in your scripts.

But I'd need to see your scripts to be able to be more specific on this matter.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2021-12-17 00:37:36
Ah yes, triggers. You're probably right that that would be the way to go. This approach with the regexes being built by querying tables is new, and I haven't thought it all the way through yet. Formerly the same data was typed into several different regex scripts, and I am trying to solve the maintenance issues with that.

Thanks for the tip. :)
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2022-02-09 06:07:34
I've gotten into a bad situation.  There's this "Rebuild Media Library" batch command:
Code: [Select]
BEGIN TRANSACTION;
DROP TABLE IF EXISTS MediaLibrary;
CREATE VIRTUAL TABLE MediaLibrary USING MetaDB_Module(add_defaults);
COMMIT;
This has normally worked fine to add new fields to be used in queries.

Now,

1. After clicking this once, it seems to complete.  There's no feedback.  If I exit foobar2000 and re-open it, my SQL Tree is blank and cannot be right-clicked.

2. If I don't exit foobar2000 and click it a 2nd time,
Code: [Select]
Execution error:
SQLite Error: (1) Unable to declare virtual table MediaLibrary:
SQLite Error: (1) near "/": syntax error

Error while executing:
CREATE VIRTUAL TABLE MediaLibrary USING MetaDB_Module(add_defaults)

3. If I click it a third time,
Code: [Select]
Execution error:
SQLite Error: (1) Unable to declare virtual table MediaLibrary:
SQLite Error: (1) near "/": syntax error

Error while preparing the first statement of:
DROP TABLE IF EXISTS MediaLibrary;
CREATE VIRTUAL TABLE MediaLibrary USING MetaDB_Module(add_defaults);
COMMIT;

What is going on?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-02-09 20:48:27
I've gotten into a bad situation.  There's this "Rebuild Media Library" batch command:

This has normally worked fine to add new fields to be used in queries.
This is actually not necessary as the virtual tables are rebuilt automatically, when you add/change/remove fields. But doing it normally doesn't hurt.

What is going on?
I can only guess, but the only explanation I have on my mind, is that you defined a new field containing a single quote (') in its name. For building the virtual table the column names are surrounded by single quotes and having a single quote in the name will break the virtual table creation.

Unfortunately there is currently no check for such a problem. This will be added for the next version and instead of single quotes the column names will be surrounded by backticks (`).
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-13 08:53:36
A new version is out. Please, check the first post for details.
Title: Re: foo_uie_sql_tree
Post by: ASopH on 2022-03-15 14:35:36
hi,

I've crash reports by quitting foobar, older version with older sqlite dosn't. Without installing sql-tree there're no crash reports.
bwt. an very useful plugin :-)
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-15 14:46:27
This crash might be related.
EDIT: It happened after a double-click on track.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-15 15:13:16
Oh, this update is glorious. I've been waiting for it, and it looks like a lot of fun. Thank you for your hard work  ;D

Naturally, I have come across a couple nits in the Edit Query window.

Generally too, the "Documentation" link on the foobar2000 Component page (https://www.foobar2000.org/components/view/foo_uie_sql_tree) goes to an empty wiki page instead of the old pages you had linked formerly. Are new pages on the way?

Thanks again. Being able to grab the scripts like that is really going to help me quite a lot.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-15 18:44:09
Now I'm encountering a weird bug I'm not sure how I can reproduce involving an interaction with the venerable Panel Stack Splitter. I have a button in a Spider Monkey Panel that toggles which panels are visible in the PSS by alternately creating or deleting a blank text file. The PSS then uses the presence/absence of this file to determine which panels to show via a per-second script. Yes, it's hackish, rickety, and only works when a track is playing, but it's all I've found to do that till now.  :P 

Anyway, if I use the SQL Tree edit query window to link and import a sql file for the query or batch panel, this button stops toggling the PSS. The SMP toggles its icon, the text file is created or removed, but the PSS doesn't do anything with it. Restarting foobar2000 restores order.

So, um, yeah, fix that please?  ;D
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-15 18:55:14
I've crash reports by quitting foobar, older version with older sqlite dosn't. Without installing sql-tree there're no crash reports.
Thanks for the report. I can't reproduce the crash, but I will change the code in question to hopefully fix it for foo_uie_sql_tree as it could have an impact on the crash. Nevertheless the crash is actually caused by foo_spider_monkey_panel, so that @TheQwertiest might also want to look into it.

This crash might be related.
Although it didn't look at all like that at the first glance, it seems to be indeed related, although in this case foo_sqlite is causing the crash.
EDIT: It happened after a double-click on track.
This is a bit strange, but as the relevant code is shared by foo_sqlite and foo_uie_sql_tree, the change mentioned above might help here too.

The window itself is pretty big and can't be resized. Nothing major, but I'd like to scrunch it up a bit.
You're right. I should change this in a future version, but it doesn't have the highest priority.

Can one type a script name directly into the textbox at the top of the query and batch tabs? I seem to only be able to input a filename by clicking the "…" button and navigating.
This is correct. I was a bit lazy here and it saves me from adding additional input checking. I'm not sure, if I will change this. Maybe.

I'm having character coding issues coming from my UTF-8 sql files that look like they're being read as cp1252. I have a "•" being used as a visual delimiter between an album and an artist in one node, and that is rendered "•" in the query textbox and the nodes, though the Result node options display is fine.
Confirmed. I'll try to fix it without the need of this
I'd love an Advanced option to set the default script

Generally too, the "Documentation" link on the foobar2000 Component page (https://www.foobar2000.org/components/view/foo_uie_sql_tree) goes to an empty wiki page instead of the old pages you had linked formerly. Are new pages on the way?
Probably not. There is no real benefit to view them in the internet, while you already have them locally available in the component folder and you are able to open them from the application. I used the old pages in the past mainly to have the possibility to directly link to them from a thread in this forum.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-15 19:09:09
Anyway, if I use the SQL Tree edit query window to link and import a sql file for the query or batch panel, this button stops toggling the PSS. The SMP toggles its icon, the text file is created or removed, but the PSS doesn't do anything with it. Restarting foobar2000 restores order.
When opening the dialog for selecting a file, I'm currently setting the initial path for the dialog to the components directory (but I'll change it to set it to the profile folder as the first idea for using the components directory actually doesn't work and no worry the scripts will still be loaded relative to the component directory). Actually, that should not have an impact on the behavior that you described. It's only a rough guess, but it looks like you are using a relative path to check your file and it can't be found anymore after you opened the dialog.

So, um, yeah, fix that please?  ;D
:D
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-15 20:03:51
Another bug: linking a sql file for the query isn't itself sufficient to enable the OK button. Text must be pasted in manually to do so.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-15 22:22:45
Another issue: once a file has been linked in the query pane, I can't remove the link. "Delete" in the context menu is disabled, and I'm not sure how to enable it.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-17 00:12:56
Weird query issue. If I put this into the batch tab (pasting directly or linking):
Code: [Select]
DROP VIEW IF EXISTS view_albumartist;
CREATE VIEW         view_albumartist AS
SELECT CASE albumartist
       WHEN 'Various'
       THEN 'Various'
       ELSE upper(
                  substr(
                         albumartistsortcoal
                       , 1, 1
                         )
                  )
       END
    AS initial
     , albumartistcoal
     , albumartistsortcoal
     , albumcoal
     , albumsortcoal
     , datesortcoal
     , releasesortorder
     , discnumber
     , tracknumber
     , path
     , subsong
  FROM view_medialibrary
 WHERE CAST(ifnull(hideflags, 0) AS INTEGER) < 2
   AND filename_ext NOT LIKE '%.wav'
       ;


DROP VIEW IF EXISTS tree_library_albumartist;
CREATE VIEW         tree_library_albumartist AS
SELECT   (CASE WHEN initial >= ' ' AND initial <= 'B' THEN '# to B'
               WHEN initial >= 'C' AND initial <= 'D' THEN 'C to D'
               WHEN initial >= 'E' AND initial <= 'G' THEN 'E to G'
               WHEN initial >= 'H' AND initial <= 'L' THEN 'H to L'
               WHEN initial >= 'M' AND initial <= 'O' THEN 'M to O'
               WHEN initial >= 'P' AND initial <= 'S' THEN 'P to S'
                                                      ELSE 'T to Z, Various'
         END)
      AS initgroup
       , albumartistcoal
       , albumcoal
      ||' (' || substr(datesortcoal, 1, 4) || ')'
      AS albumdate
       , path
       , subsong
    FROM view_albumartist
ORDER BY albumartistsortcoal
       , datesortcoal
       , albumsortcoal
       , releasesortorder
       , discnumber
       , tracknumber

…and then this into the query tab:
Code: [Select]
SELECT initgroup
     , albumartistcoal
     , albumdate
  FROM tree_library_albumartist

…then things work as expected.

However, if I put just that first SELECT statement in the batch and the SELECT portion of the second one (minus the path and subsong fields) into the query, then the treeview will fill with all the right nodes, but nothing will be sent to the playlist on clicking.

Further puzzler: the working version will not work if the Omit not existing files when sending to a playlist box is checked, only working when it is not. I like to think that the files exist: they play like they do.  ;)
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-17 10:56:44
Not sure if this is a bug or an expected change in behavior, but I formerly had temporary tables in the batch portions of nodes that the query portions can no longer see in time. Removing TEMPORARY from the sql restores the functionality.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-20 22:12:00
I've crash reports by quitting foobar
This crash might be related.
@ASopH, @grimes: I couldn't reproduce the reported crashes. However, I made a adjustment of the affected code. Could you please test with the new versions of foo_uie_sql_tree (ASopH) and foo_sqlite (grimes), if the crashes still happen? If yes, could you please post the crash reports again? Thanks a lot.

@Sue Dunham: First of all thanks a lot for all your detailed bug reports. They were very helpful. Actually, I overlooked the one from 2022-03-17 00:12:56, but I will also look into it.

Can one type a script name directly into the textbox at the top of the query and batch tabs? I seem to only be able to input a filename by clicking the "…" button and navigating.
I didn't change this, but by accident I figured out, that you can paste the whole path into the filename box of the open file dialog. It's not that convenient as directly pasting a path into the textbox, but there is no need to always navigate.

I'm having character coding issues coming from my UTF-8 sql files that look like they're being read as cp1252. I have a "•" being used as a visual delimiter between an album and an artist in one node, and that is rendered "•" in the query textbox and the nodes, though the Result node options display is fine. I'd love an Advanced option to set the default script encoding.
Actually, it worked with UTF-8 files with BOM before. Only UTF-8 files without BOM were not properly recognized. But this is now also the case. So there is no need for an option to set the default script encoding.

Anyway, if I use the SQL Tree edit query window to link and import a sql file for the query or batch panel, this button stops toggling the PSS. The SMP toggles its icon, the text file is created or removed, but the PSS doesn't do anything with it. Restarting foobar2000 restores order.
As already assumed the open file dialog changed the current directory.

So, um, yeah, fix that please?  ;D
It's done.

Another bug: linking a sql file for the query isn't itself sufficient to enable the OK button. Text must be pasted in manually to do so.
It's fixed.

Another issue: once a file has been linked in the query pane, I can't remove the link. "Delete" in the context menu is disabled, and I'm not sure how to enable it.
You can't, but there is now an additional button to remove the link.

Not sure if this is a bug or an expected change in behavior, but I formerly had temporary tables in the batch portions of nodes that the query portions can no longer see in time.
This is most likely not a bug, but it's not really an expected change also. I tested this scenario and I couldn't reproduce your problem, but this doesn't mean that it cannot happen.

What you are describing, normally happens, if a sql statement is running on a different connection, than the one, which is creating the temporary table. And I think, this is what is happening here. In the old version there was only one permanent database connection and you could therefore rely on having the temporary table available, whenever you need it. This is not the case anymore. A database connection is requested from a connection pool, whenever one is needed and given back to it, when it is not needd ynamore. Although in most cases you will use the same database connection for batch and query part, it cannot be guaranteed.

If your temporary tables are not to big you could try to create them in memory schema, by attaching it like this:
Code: [Select]
ATTACH DATABASE 'file::memory:?cache=shared' AS memtemp;


Title: Re: foo_uie_sql_tree
Post by: ASopH on 2022-03-21 07:33:55
Hi,

seems to be fixed. Thank you :-)
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-21 08:46:01
crash still happens.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-23 21:52:27
crash still happens.
I still can't reproduce the crash, but there is something strange in your bug report concerning the last log messages. The last message is "Create table `sql_SQLiteTags` ..." which is normally immediate followed by "updating table `sql_SQLiteTags` ...", but this is missing. Between these two messages are only few checks in the database. So maybe there is an issue with the database. To check this, you could try the following:

In any case I'd be glad, if you could provide me the up to 3 old dabase files, so that I can do some tests on my own.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-23 23:23:23
Crash still happens.

What I've done now:

The console output is:
Quote
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Create sqltree tables ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Get new pool database connection, db pool size: 0, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Create table sql_ObjectVersion ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: Create table sql_ObjectVersion: 0:00.010924
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db: 0:00.012490
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: Get new pool database connection, db pool size: 0, available connections: 0: 0:00.018491
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: creating table `ust_TreeItem` ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_TreeItem`: 0:00.001845
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_TreePath` ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_TreePath`: 0:00.001114
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_QueryNodeDetail` ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_QueryNodeDetail`: 0:00.001792
[01:02:41] [foo_uie_sql_tree] 01:02:41.591: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db: 0:00.014596
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Create sqltree tables: 0:00.045786
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Get new pool database connection, db pool size: 0, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db: 0:00.001489
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Get new pool database connection, db pool size: 0, available connections: 0: 0:00.004170
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Get new pool database connection, db pool size: 0, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db: 0:00.001613
[01:02:41] [foo_sqlite] 01:02:41.607: Create MetaDB_Module ...
[01:02:41] [foo_sqlite] 01:02:41.607: Create MetaDB_Module: 0:00.000139
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize: 0:00.004036
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Get new pool database connection, db pool size: 0, available connections: 0: 0:00.009339
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Create example nodes ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Get new pool database connection, db pool size: 1, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db: 0:00.001540
[01:02:41] [foo_sqlite] 01:02:41.622: Create MetaDB_Module ...
[01:02:41] [foo_sqlite] 01:02:41.622: Create MetaDB_Module: 0:00.000157
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize: 0:00.002033
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Get new pool database connection, db pool size: 1, available connections: 0: 0:00.005188
[01:02:41] [foo_uie_sql_tree] 01:02:41.622
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQLite Error: (1) no such column: album artist

Error while preparing the first statement of:
SELECT ifnull([album artist],artist) album_artist,
       '['||date||'] '||album album_col,
        (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number
FROM MediaLibrary
GROUP BY 1,album,discnumber
ORDER BY ifnull([album artist],artist) COLLATE NaturalNoCase,'['||date||'] '||album desc,discnumber
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Create example nodes: 0:00.009555
I must confess, that I don't have tagged album artist in my whole library (don't have compilations etc.).
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-24 22:52:54
I must confess, that I don't have tagged album artist in my whole library (don't have compilations etc.).
That is not what is causing the error message. In fact you're databases are quite strange. It seems that there someting went wrong during the upgrade to version 2.0.0. You have still tables in foo_sqlite.db, which should have been moved to foo_sqlite.user.db and even more strange you have a new internal table in foo_sqlite.user.db.

Additionally the VACUUM command, which you executed to re-create the database, dropped more than half of the tables in your foo_sqlite.db. And this is the reason, why you received now the errro message. I also ran the VACUUM command on your old foo_sqlite.db and it worked as it should. All tables where available in foo_sqlite_new.db. Although executing of PRAGMA integrity_check didn't show any error, it could be that your database is somehow broken.

Nevertheless your database problems are probably not responsible for the crash. The first access to your foo_sqlite.db is much later than it normally should be and during the first access some initialization is executed for the database. There could be reasons why the initialization takes place so late, but to avoid problems relating to the late initialization, the initialization is now forced to be executed during the application startup.This cannot solve your database problems, but it should solve the crash you had.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-25 03:43:58
Crash is fixed. Thank you.

Database problem: The reason for the problems could be that I was upgrading and then downgrading again (because of the bug). I attach the hopefully repaired and not broken version of the database files.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-25 07:46:21
Here the tree database.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-25 17:48:01
However, if I put just that first SELECT statement in the batch and the SELECT portion of the second one (minus the path and subsong fields) into the query, then the treeview will fill with all the right nodes, but nothing will be sent to the playlist on clicking.
Actually, I could not reproduce this, but there was an issue with SELECT statements containing CASE expressions. Although the relevant CASE expression for your second SELECT is wrapped in parantheses and should not have been affected, the fix might have helped. If this is still not working, please check the console after you clicked on the node. The generated node query is now printed to the console and should help for further investigations.

Further puzzler: the working version will not work if the Omit not existing files when sending to a playlist box is checked, only working when it is not.
This is fixed now.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-25 18:05:24
Database problem: The reason for the problems could be that I was upgrading and then downgrading again (because of the bug).
This explains most of your database problem. Only the dropped tables after the VACUUM is still a mystery. This is pure SQLite code and definitely works. The only thing what this could explain, is that you somehow mixed up the files.

I attach the hopefully repaired and not broken version of the database files.
Yes, all databases are ok. The foo_sqlite.db in the configuration2 archive is quite large and could be compressed by executing
Code: [Select]
ATTACH fb2k_profile_path()||'\configuration\foo_sqlite.db' AS sqlite;
VACUUM sqlite;
but this is not really necessary.

Here the tree database.
This database was not affected by the problems, but it is also ok.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-25 18:32:47
After some time, the data are transferred mostly from foo_sqlite.db to foo_sqlite.user.db. I have compressed the user database. Is this correct?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-25 19:31:41
After some time, the data are transferred mostly from foo_sqlite.db to foo_sqlite.user.db.
Yes, this is part of the migration to version 2.0.0

I have compressed the user database. Is this correct?
It's neither correct nor wrong. It does not have an impact on the functionality. It just saves you some MB disk space (not really much in our days). It will become more interesting when your database grows due to heavy activities with inserting and deleting data to several GB.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-25 19:58:37
I'm having a persistent crash now when trying to run a particular UPDATE query on PlaylistUpdatable. This query was working previously today, and then just stopped. It's a long script, but a second query that runs most of it but then SELECTs the results to the SQLite console rather than updating works fine.

Since this has been happening, my restarts are now not working correctly. I have about 10 queries refreshing on load, and now foobar2000 seems to get hung up after just two of them are loaded. the rest show your progress bars for a while without progress. I wind up just aborting them.

I tried to do the VACUUM thing you showed up above into a new db. Even that took way too long before I just wound up aborting it too. I did try just a plain VACUUM directly in the main db, not having read that perhaps some tables may have gone missing doing that.

Anyway, here are several crash reports from the same issue and some of the other files.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-25 20:08:11
Album column size is too wide in sqlite console window. How can I adjust.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-25 20:20:39
I tried to do the VACUUM thing you showed up above into a new db. Even that took way too long before I just wound up aborting it too.
This is something which never will help in such cases. I asked @Grimes only to do this to exclude the quite low possibility, that his database is corrupted.

I did try just a plain VACUUM directly in the main db, not having read that perhaps some tables may have gone missing doing that.
No, tables are not gone missing when doing VACUUM. It just looked like that it was the case for @Grimes databases. But it definitely wasn't the case.

Anyway, here are several crash reports from the same issue and some of the other files.
I will check them

Album column size is too wide in sqlite console window. How can I adjust.
We should better continue to discuss foo_sqlite related issues in the relevant thread, but the short answer is: You can't besides doing it manually, if you have such long album names in the query, which causes the column to be adjusted accordingly.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-25 20:44:42
A bit more: since the crashes, sqlite was usually hanging trying to refresh those 10 nodes on loading, but sometimes not. In those former cases, sqlite wouldn't run very well in the console either. I have since unticked "Refresh on load" for all of them, and two startups since have been fine. Those nodes will refresh manually in the usual time, and the console runs in a timely manner as well.

Also, the UPDATE query that triggered everything will execute properly again. It hadn't before for several different restarts.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-25 20:45:59
Anyway, here are several crash reports from the same issue and some of the other files.
I will check them
The reason for the problem could be the same as for @grimes' crashes. So, you should first upgrade foo_sqlite and foo_uie_sql_tree to their latest version. If the problem still occurs, please post the new crash reports. In this case it could also be helpful to include all database files for foo_uie_sql_tree.db. The main file is missing in your archive.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-25 21:02:37
Thank you for the magnificent work. Small issue: tree, rightclick node | Edit | Query. Window is too large to hit ok. Scale (Skalierung) under Windows 10: 125%.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-25 21:32:01
Another issue: node Examples | Library | Genre

foobar2000 Console output:
Quote
[22:23:46] [foo_uie_sql_tree] 22:23:46.435: Get tracks for query node "Genre": 0:00.000537
[22:23:46] [foo_uie_sql_tree] 22:23:46.435
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQLite Error: (1) no such column: metadb_handle

Error while preparing the first statement of:
SELECT path,
       subsong
FROM genre_query

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[22:24:25] [foo_sqlite] 22:24:25.394: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db ...
[22:24:25] [foo_sqlite] 22:24:25.397: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db: 0:00.002724
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-25 22:01:41
Another issue: node Examples | Library | Genre
This is caused by the breaking change in foo_sqlite 2.0.0 and it is unfortunately not safe to do the migration automatically in this case. The error is caused by genre_query still having metadb_handle in its definition which is not valid anymore and SQLite always checks a view definition for its validity even on dropping it.

For newly created example nodes this problem does not exists, as the view has a different name than before for the genre query. So deleting the old example nodes and re-creating them, will solve the problem.

However, this will keep the wrongly defined view in the database.  To get rid of it, you can execute the following commands:
Code: [Select]
PRAGMA writable_schema=ON;
DROP VIEW genre_query;
PRAGMA writable_schema=OFF;
This will allow to drop the view without checking its validity.
Title: Re: foo_uie_sql_tree
Post by: grimes on 2022-03-25 22:48:18
Another crash (Similar to Sue Dunham's crash).
This happens, when a query is executed shortly after foobar2000 start. A simple query is hanging (after 2min: aborting query then crash).
EDIT: I had a series of 4 crashes and couldn't reproduce the crash afterwards.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-30 18:45:05
I have a general question about migrating my SQL Tree from my main foobar2000 to a new clean one. I don't have any trouble migrating everything to a new foobar, but I can't get my nodes to appear when just migrating the SQLite components.

I am copying the following files and directories from one portable installation to another:

foobar2000/
└── profile/
    ├── configuration/
    │   ├── foo_sqlite.db
    │   ├── foo_sqlite.dll.cfg
    │   ├── foo_sqlite.materialize.db
    │   ├── foo_sqlite.tags.db
    │   ├── foo_sqlite.user.db
    │   ├── foo_uie_sql_tree.db
    │   └── foo_uie_sql_tree.dll.cfg
    └── user-components/
        ├── foo_sqlite/
        └── foo_uie_sql_tree/


The components install, but instead of all my nodes, the general example nodes appear instead. Looking under the hood in foo_uie_sql_tree.db, I see that new records have been added to ust_TreeItem holding these nodes. Presumably, the new #ROOT# record is tied to the current panel, a regular Default UI popup as opposed to the panel in my busy and involved Columns UI layout.

So my question: is there something else I should be copying over to tie the old nodes to the new panel? Barring that—and understanding that there is danger ahead—could I try editing ust_TreeItem to change the tri_parent_id of my top-level nodes to that of the new #ROOT#? Is there another edit to make instead/as well?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-30 18:57:57
Barring that—and understanding that there is danger ahead—could I try editing ust_TreeItem to change the tri_parent_id of my top-level nodes to that of the new #ROOT#?
Yes and besides that nothing more is necessary.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-30 22:06:49
Barring that—and understanding that there is danger ahead—could I try editing ust_TreeItem to change the tri_parent_id of my top-level nodes to that of the new #ROOT#?
Yes and besides that nothing more is necessary.

I'm having trouble with this: it keeps making more example nodes. I edit foo_uie_sql_tree.db in the SQLite Console, Deleting the example nodes and changing tri_parent_id to the tri_node_id of the vacated root, close foobar2000, reopen, and new example nodes appear, further on in ust_TreeItem. I've tried leaving the example nodes there, same result. I've tried editing the db with a different program, same result.

How can I prevent the creation of yet more example nodes?
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-03-31 15:26:22
New bug: I open a node, and the newly opened node moves to the top of the Treeview display, which is fine. However, when I close the node and the display flips back to show the top (there are now few enough nodes showing that the entire list can be displayed in the panel), the topmost node now under the mouse pointer captures part of that node-closing click and acts as if it were clicked as well. This happens repeatedly, so I don't think I'm just fat fingering it.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-03-31 22:22:19
How can I prevent the creation of yet more example nodes?
The example nodes are only automatically created, if the root node id of the panel can't be found in the database. So, although what you are doing according your description should work, it seems that you are doing something wrong.

By the way, your sqltree from the crash archive in the foo_sqlite thread was loaded in a new clean foobar2000 installation for me because its root node id is 1.

New bug
Maybe an old one. Anyway, it's reproducible for me. Until it is fixed you might consider to open an close nodes by clicking on the expansion button on the left side of the label instead of double clicking on the label.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2022-04-04 00:00:23
This SQL query to find different album artists worked before but now it doesn't since an update along the line somewhere.

Any help would be appreciated. Thanks.

The error is:
Code: [Select]
SQLite Error: (1) no such column: %BOOTLEG%

The query:
Code: [Select]
SELECT album,"album artist"
FROM MediaLibrary
WHERE album IN
      (SELECT album
      FROM MediaLibrary
      WHERE album NOT LIKE "%BOOTLEG%"
          AND  album NOT LIKE "%Singles%"
          AND  album NOT LIKE "%[SC]"
      GROUP BY 1
      HAVING count(distinct coalesce("album artist",''))>1)
ORDER BY album
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-04-04 06:20:27
This SQL query to find different album artists worked before but now it doesn't since an update along the line somewhere.

Any help would be appreciated. Thanks.
Strings in SQL are surrounded by single quotes not double quotes. With double quotes SQLite assumes a column name. Therefore the error message.

You should try this:
Code: [Select]
SELECT album,"album artist"
FROM MediaLibrary
WHERE album IN
      (SELECT album
      FROM MediaLibrary
      WHERE album NOT LIKE '%BOOTLEG%'
          AND  album NOT LIKE '%Singles%'
          AND  album NOT LIKE '%[SC]'
      GROUP BY 1
      HAVING count(distinct coalesce("album artist",''))>1)
ORDER BY album
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2022-04-04 23:16:33
Strings in SQL are surrounded by single quotes not double quotes. With double quotes SQLite assume a column name. Therefore the error message.
Thanks for that and the info. All working fine now.
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2022-04-08 01:47:10
Hello fbuser, I updated to 3.0.3 and I am quite pleased with some performance improvements I've seen in some queries (5 minutes to 5 seconds!), but I'm having trouble with other queries.

Some stores sell uncompressed FLAC, or embed massive cover art, so I had a query to find files that are "too large" so that I could re-encode them:

Code: [Select]
SELECT m.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM medialibrary m
JOIN
(SELECT path, [bits per sample], samplerate, channels,
 MAX(ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3)) AS MB_per_min, COUNT(*) AS cnt
FROM medialibrary

WHERE [bits per sample] > 16 OR [samplerate] > 44100

GROUP BY path, [bits per sample], samplerate, channels
) c
ON m.path = c.path
  AND m.[bits per sample] = c.[bits per sample]
  AND m.samplerate = c.samplerate
  AND m.channels = c.channels

WHERE
    (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)

 OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)

ORDER BY m.path

However, this is no longer working with the latest components:
Quote
SQLite Error: (1) ambiguous column name: path
...

I tried to qualify the inner queries, but wasn't able to figure out how to fix this query.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-04-08 22:33:39
I am quite pleased with some performance improvements I've seen in some queries (5 minutes to 5 seconds!)
There were several enhancements for the SQLite query optimizer implemented in the last years, which probably results in better execution plans for your queries. Also 5 minutes appear to be quite long, even if you have a big media library.

I tried to qualify the inner queries, but wasn't able to figure out how to fix this query.
You need to an alias to the "path" column of the sub-select query as due to dropping the metadb_handle pseudo column now the columns "path" and "subsong" are automatically added without any qualifier to the query of a query node, when the result is sent to a playlist.

So your query could look like this:
Code: [Select]
SELECT m.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM medialibrary m
JOIN
(SELECT path subselect_path, [bits per sample], samplerate, channels,
 MAX(ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3)) AS MB_per_min, COUNT(*) AS cnt
FROM medialibrary

WHERE [bits per sample] > 16 OR [samplerate] > 44100

GROUP BY path, [bits per sample], samplerate, channels
) c
ON m.path = c.subselect_path
  AND m.[bits per sample] = c.[bits per sample]
  AND m.samplerate = c.samplerate
  AND m.channels = c.channels

WHERE
    (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)

 OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)

ORDER BY m.path

However, as grouping by path could only be useful for multitrack files with different subsongs (and only in very rare cases), as for single track files the path is always already unique your query could also be written like this:
Code: [Select]
SELECT c.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM (
  SELECT path, subsong, [bits per sample], samplerate, channels,
         ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3) AS MB_per_min
  FROM medialibrary
  WHERE [bits per sample] > 16 OR [samplerate] > 44100
) c
WHERE
    (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)

 OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)

ORDER BY c.path
As there is no need for the group-by, there is also no need for the join. Adding the subsong column to the sub-select is necessary as the medialibrary is now only referenced in the sub-select and as mentionend "path" and "subsong" will be added automatically.

Actually, also the colums of the main select could be replaced by just 1, if you use the query only for sending results to a playlist., as creating a tree structure with the currently used columns doesn't appear to be useful for me.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-04-10 19:06:04
Thank you for the magnificent work. Small issue: tree, rightclick node | Edit | Query. Window is too large to hit ok. Scale (Skalierung) under Windows 10: 125%.
This should be no problem anymore. The dialog is now resizable and the initial size is smaller.

New bug: I open a node, and the newly opened node moves to the top of the Treeview display, which is fine. However, when I close the node and the display flips back to show the top (there are now few enough nodes showing that the entire list can be displayed in the panel), the topmost node now under the mouse pointer captures part of that node-closing click and acts as if it were clicked as well.
It's fixed now.
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2022-06-02 14:55:21
I have a bug report for SQL Tree 3.0.4 / SQLite View 2.1.1 - if I move a playlist, then my query no longer selects from the correct playlist.
Code: [Select]
DROP TABLE IF EXISTS MediaLibrary_NoSplit;
CREATE VIRTUAL TABLE MediaLibrary_NoSplit USING MetaDB_Module(no_multivalue_split);
;
SELECT P.discnumber, P.tracknumber, P.title, P.artist, ...
FROM playlist P
LEFT JOIN (
 SELECT artist...
 FROM MediaLibrary_NoSplit
 WHERE ...
 GROUP BY artist, ...
 COLLATE BINARY
 HAVING cnt > 0
) m
ON P.artist = m.artist
COLLATE BINARY
WHERE P.playlist_name = 'my playlist'
GROUP BY P.discnumber, P.tracknumber, P.title, P.artist
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-06-02 19:46:40
if I move a playlist, then my query no longer selects from the correct playlist.
What do you mean with "move a playlist" and what should be the "correct playlist"? Which result do you expect and which result do you get?
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2022-06-02 22:38:30
if I move a playlist, then my query no longer selects from the correct playlist.
What do you mean with "move a playlist" and what should be the "correct playlist"? Which result do you expect and which result do you get?

Let's say my playlists currently are like this:

10. my playlist
11. something else

Then I move "my playlist" down, so that "something else" takes its spot.  The query then selects the contents of "something else" instead of "my playlist".  This is still happening on the newest version(s) for me.
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2022-06-03 01:44:53
I'm also experiencing a behavior change where a query that selects 0 items that is supposed to "Send to target playlist" and has "Activate playlist" enabled, does not activate the playlist anymore.  Not sure if intentional change or a bug.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-06-03 18:14:41
Then I move "my playlist" down, so that "something else" takes its spot.  The query then selects the contents of "something else" instead of "my playlist".  This is still happening on the newest version(s) for me.
I can't confirm this. For me it works as expected. So, there must be something more:

I'm also experiencing a behavior change where a query that selects 0 items that is supposed to "Send to target playlist" and has "Activate playlist" enabled, does not activate the playlist anymore.  Not sure if intentional change or a bug.
Yes, I noticed this too, but I consider it more an unintended change of behavior in the first place. However, in a few cases I had the impression, that worked as before. So I might look into it sooner or later.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2022-06-03 20:09:11
So I got everything working as I liked, bumped the version number of my theme (pretentious, sure, but I'm trying to stay organized), and moved the working foobar2000 to a new folder. The old folder is now gone. Things work fine based on the SQL that has been loaded into the nodes after linking external files to them.

Only now that I want to edit the underlying files and update those node SQLs, I see that they are all linked with absolute links on the query and batch tabs to files in the old folder. I cannot do the quick refresh as I had hoped without re-linking the SQL file to the node in the batch tab.

I hoped there was a quicker way to do this for all of my nodes, perhaps by updating sqltree.ust_TreeItem. However, when I opened that up, I see that it already has those file locations stored in tri_sql_query_file and tri_sql_batch_file as relative paths.

Where is SQL Tree getting the absolute paths from, and how can I redirect them?

[edit: I spoke too soon. Many of the paths are stored as relative paths, but not all. Is this something that was changed between versions, and its a matter of my old nodes having absolute paths stored and the newer ones having relative paths?]
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-06-04 09:34:51
I hoped there was a quicker way to do this for all of my nodes, perhaps by updating sqltree.ust_TreeItem.
Yes, that would be an option.

Where is SQL Tree getting the absolute paths from
You should only have an absolute path, if the path is outside of your profile folder at the time, when you create the link.

Is this something that was changed between versions, and its a matter of my old nodes having absolute paths stored and the newer ones having relative paths?
Most likely, yes. There was an issue with the path handling, which was fixed in version 3.0.1. Although it actually caused making relativ paths also, if the files were outside of the profile folder, it might have had also other impacts.
Title: Re: foo_uie_sql_tree
Post by: neothe0ne on 2022-06-05 00:15:51
I can't confirm this. For me it works as expected. So, there must be something more:
  • How exactly do you move the playlist down, i.e. with which tool?
  • How many playlists do you have?
  • Does the position of the playlist before you move it matter?
  • Does it make a difference, if you move a playlist up or down?
  • Do you have any further details, which might help to identify the cause of the problem?

1. I believe it is a "Playlist switcher" in a Popup panel.  I drag the playlist up or down.
2. I have 450+ playlists open.
3. The position of the playlist does not matter, after trying a few.
4. It does not matter whether I move it up or down.  If I move it up, then the query starts using the lower playlist.  If I move it down, then the query starts using the upper playlist.  If I move a different playlist below my target playlist, to a position higher up than my target playlist, then it causes the same effect.

If I exit foobar2000 and reopen, then the query will select from the correctly named playlist (until I move playlists again, which will then require restarting foobar2000 again for the query to use the correctly named playlist).
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2022-06-19 18:54:57
I believe it is a "Playlist switcher" in a Popup panel.  I drag the playlist up or down.
I did the same for my tests without any problem.
I have 450+ playlists open.
Ok, I have only about 15 playlists, but this should not really make a difference.

If I exit foobar2000 and reopen, then the query will select from the correctly named playlist
And the big question for me is why, as the relevant playlist information is initialized before any query, which uses the virtual playlist table, is initialized and I see no way how this could be unintendently bypassed.

So maybe some more information will help to identify the cause of the problem:
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2023-01-02 22:20:14
I'm trying to get any incomplete albums. This works below but not where there's more than one CD. I format the CD as I do in the screenshot.

So DISCNUMBER, TOTALDISCS, TRACKNUMBER and TOTALTRACKS are what I use for those fields.

How can I fix this below in order for it to work for my use case? Thanks in advance.

Code: [Select]
SELECT a."%album artist%" "Album Artist",
      '['||coalesce(a.date,'')||']  '||a.album "Date - Album",
      title "Title"
FROM MediaLibrary a JOIN (SELECT album,"%album artist%"
                          FROM MediaLibrary
                          GROUP BY 1,2
                          HAVING count(*)<>max(CAST(totaltracks AS INTEGER))) b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
        a.date,
        a.album,
        discnumber,
        tracknumber
Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2023-01-03 17:18:00
Maybe this (UNTESTED):
Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date,'') || ']  ' || a.album "Date - Album",
       a.discnumber, a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND a.discnumber       = b.discnumber
 ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, discnumber, tracknumber
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2023-01-03 20:19:02
Maybe this (UNTESTED):
Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date,'') || ']  ' || a.album "Date - Album",
       a.discnumber, a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND a.discnumber       = b.discnumber
 ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, discnumber, tracknumber

Thanks, I had an error (see attached) with this above which I fixed with the below. But I notice there's no reference to tracknumber because it's not giving me the correct results.

Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date,'') || ']  ' || a.album "Date - Album",
       a.discnumber "Disc Number", a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND a.discnumber       = b.discnumber
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, a.discnumber, tracknumber
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2023-01-03 23:05:13
I've tried this below which I found in this thread. It works when I execute it in the SQLite Console but not when I add it to the query in the edit query node.

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

It returns this error attached.
Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2023-01-04 08:43:25
I notice there's no reference to tracknumber
Not sure what this means but maybe the problem is that discnumber is not present for single disc albums? If that's the case try:
Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '[' || coalesce(a.date, '') || ']  ' || a.album "Date - Album",
       coalesce(a.discnumber, '1') "Disc Number", a.tracknumber, a.title "Title"
  FROM MediaLibrary a
  JOIN (SELECT album, coalesce(discnumber, '1') discnumber, "%album artist%"
          FROM MediaLibrary
         GROUP BY 1, 2, 3
        HAVING count(*) <> max(CAST(totaltracks AS INTEGER))) b
            ON a.album            = b.album
           AND a."%album artist%" = b."%album artist%"
           AND coalesce(a.discnumber, '1') = b.discnumber
 ORDER BY a."%album artist%" COLLATE NaturalNoCase,
          a.date, a.album, coalesce(a.discnumber, '1'), a.tracknumber
Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2023-01-04 09:09:14
I've tried this below which I found in this thread. It works when I execute it in the SQLite Console but not when I add it to the query in the edit query node.
Probably the problem is the ORDER BY clause: does it work if you remove it?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2023-01-04 19:35:40
I've tried this below which I found in this thread. It works when I execute it in the SQLite Console but not when I add it to the query in the edit query node.
This does not work for several reasons:


What is your goal with this query?
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2023-01-04 21:21:03
@mobyduck‍ thanks but that returns false results when there are for example two CDs as per the example attached. No the ORDER BY wasn't the issue. I tried removing that.

@fbuser‍ The goal is to find albums where there are missing tracks and set it up in the query tab.

I have my DISCNUMBER, TOTALDISCS, TRACKNUMBER and TOTALTRACKS total tracks formatted like below like so.

All of my albums are in one folder. The filename for the example below is like this where it's DISCNUMBER.TRACKNUMBER. ARTIST - ALBUM.ext
Code: [Select]
1.001. The XX - Intro.flac
1.002. The XX - VCR.flac
1.003. The XX - Crystalised.flac
1.004. The XX - Islands.flac
1.005. The XX - Heart Skipped A Beat.flac
1.006. The XX - Fantasy.flac
1.007. The XX - Shelter.flac
1.008. The XX - Basic Space.flac
1.009. The XX - Infinity.flac
1.010. The XX - Night Time.flac
1.011. The XX - Stars.flac
2.001. The XX - Teardrops.flac
2.002. The XX - Do You Mind.flac
2.003. The XX - Hot Like Fire.flac
2.004. The XX - Blood Red Moon.flac
2.005. The XX - Insects.flac

Formatting of DISCNUMBER, TOTALDISCS, TRACKNUMBER and TOTALTRACKS
(https://i.imgur.com/fTSmRpE.png)
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2023-01-04 21:50:55
@fbuser‍ The goal is to find albums where there are missing tracks and set it up in the query tab.
But then the query which you posted before as correction of the proposal of @mobyduck should do what you want in general. What do you mean with your comment
Quote
Thanks, I had an error (see attached) with this above which I fixed with the below. But I notice there's no reference to tracknumber because it's not giving me the correct results.
in this context?
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2023-01-04 22:44:07
But then the query which you posted before as correction of the proposal of @mobyduck should do what you want in general. What do you mean with your comment
in this context?

It fixed the error it gave but didn't lead to the correct results (should of mentioned that, sorry) so that's when I searched the thread and found this below:

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

I was after this to work when I use it in the query tab to then click on the query to create a playlist with the results.
Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2023-01-05 13:36:03
If you issue the following query, do you get the expected results (all incomplete albums/discs)?
Code: [Select]
SELECT album, coalesce(discnumber, '1') discnumber, "%album artist%"
  FROM MediaLibrary
 GROUP BY 1, 2, 3
HAVING count(*) <> max(CAST(totaltracks AS INTEGER))
Never mind sorry, just read you previous post: TOTALTRACKS is the number of tracks on ALL discs?

Apologies for the multiple edits.
Title: Re: foo_uie_sql_tree
Post by: stevehero on 2023-01-05 18:54:28
If you issue the following query, do you get the expected results (all incomplete albums/discs)?

Apologies for the multiple edits.

Don't worry at all. I do when I execute it in the SQLite Console it appears to return the correct results and works very fast. But adding it to a query to add the results to a playlist takes a long time and results in my entire library getting sent to the playlist.

Edit:
I've checked one album from the results in the SQLite Console and it's not what I was after. There are 29 tracks in the album and the total track tag is 29 so they shouldn't be showing up. Thanks for your help so far.

I'd love to know SQL. I know a few languages but this baffles me. Basically, if I can get the number of songs in an album and compare that to the TOTALTRACKS field, then I'd say I'm good then.

Solution:
Finally got it working as I wanted with this. Fluked this TBH. Just used a very similar one I had for getting different WWW tags for each album.

Code: [Select]
-- Finds incomplete albums. If the TOTALTRACKS field is 10 then this
-- will find any albums containing 9 or fewer tracks.

SELECT "%album artist%", album, totaltracks
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(*) <> max(CAST(totaltracks AS INTEGER)))
ORDER BY album, discnumber, tracknumber

Title: Re: foo_uie_sql_tree
Post by: mobyduck on 2023-01-06 08:20:59
I'd love to know SQL. I know a few languages but this baffles me.
Yeah, I think it's an amazing (and underrated) programming language.
Finally got it working as I wanted
I was going to suggest something similar: I probably was mislead by the two different values for totaltracks in your first screenshot. Anyway, glad you solved it.

Regards.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2023-03-02 18:24:13
I started poking around again with the crashing error I've had. I don't know what's wrong with my computer that you can't reproduce it there, but I'm getting it in spades here. Alas, this is the only Windows box in the house.

I'll tell you about that later, but first—in working with clean installs—I noticed some wonky behavior with the Tree. Here's what I did.

The first error was upon first opening the SQL Tree: it failed to build all the sample nodes, not recognizing the field [album artist]. I cleared that up by going to Preferences → Media Library → SQLite utilities → MediaLibrary table and pressing the Add defaults button. I don't remember the plugin glitching like that before,, but perhaps I haven't kept up with the beginner's instructions.

Next, when I got (in a new, clean installation) the sample nodes done, I tried but could not refresh them. I looked in vain for a Refresh option in the context menu. There was a Refresh All if I clicked on the folder icon, but neither that nor the plain Refresh when clicking the node label. The Refresh All didn't work when I tried to do it.

Then I just pressed the node and sent everything to a new playlist. This populated that node.

I don't have this trouble in my main fb2k installation, but this fresh one was giving me these issues.

OK, so with the node having sent everything to a new playlist, I was primed to test the trouble I still do have in my main fb2k: if I add a new playlist and then query the Playlist table, foobar crashes. If before querying I close and reopen foobar, things work as expected.

The new install crashed too. With the new playlist created, I opened the SQLite Console and tried to run select * from Playlist. Crash.

Here's a zip of the crash reports, the dbs, and some screen grabs of the context menus.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2023-03-02 19:24:28
The first error was upon first opening the SQL Tree: it failed to build all the sample nodes, not recognizing the field [album artist]. I cleared that up by going to Preferences → Media Library → SQLite utilities → MediaLibrary table and pressing the Add defaults button. I don't remember the plugin glitching like that before
Then we are two  :D Actually, I noticed this  a while ago too, but decided to ignore it for the moment. But this is definitely not ideal, so I will fix it for the next release.

Next, when I got (in a new, clean installation) the sample nodes done, I tried but could not refresh them. I looked in vain for a Refresh option in the context menu. There was a Refresh All if I clicked on the folder icon, but neither that nor the plain Refresh when clicking the node label. The Refresh All didn't work when I tried to do it.
Thanks for the report. I'll check it.

Here's a zip of the crash reports, the dbs, and some screen grabs of the context menus.
Thanks for all your efforts. I'll check it and can hopefully fix it then.
Title: Re: foo_uie_sql_tree
Post by: Sue Dunham on 2023-03-05 16:48:50
I'm seeing another issue recently as I've started adding keyboard shortcuts. The behavior of them is different when the SQL Tree has the focus. For instance, `ctrl+P` will open the preferences as usual, but a chime will sound as well. This doesn't happen when other components have the focus.

Other, custom commands don't function at all. I've made `ctrl+space` run the Play or pause command. When SQL Tree has the focus, the chime will sound but a playing track won't pause.

I'm having a couple other issues while experimenting with the DUI. First, the Edge style dropdown doesn't appear in the Options... dialog under that interface. I'd really like that edge to go away there,

I have issues too trying to get my CUI nodes to show up in a DUI tree, which always seems to stick with the example nodes. I paste in the tree.db and .cfg files from one portable fb2k to another, but the one interface doesn't seem to cross over to the other. Is there a way I can manipulate the foo_uie_sql_tree.db tables to indicate which of the <ROOT> nodes is the active one?
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2023-03-07 21:52:17
I'm seeing another issue recently as I've started adding keyboard shortcuts. The behavior of them is different when the SQL Tree has the focus. For instance, `ctrl+P` will open the preferences as usual, but a chime will sound as well. This doesn't happen when other components have the focus.
With a few exceptions for those components like foo_uie_explorer or foo_uie_albumlist, which are also using a treeview control. This is Windows standard behavior. However, I implemented a nasty hack for the next release, which suppresses this standard behavior.

Other, custom commands don't function at all. I've made `ctrl+space` run the Play or pause command. When SQL Tree has the focus, the chime will sound but a playing track won't pause.
Because SQL Tree uses `ctrl+space` by itself to execute the middle-click action of a query node. For the next release a more fine-grained behavior is implemented, so that it only will be used, if a query node with a configured middle-click action is the active node in the tree.

I'm having a couple other issues while experimenting with the DUI. First, the Edge style dropdown doesn't appear in the Options... dialog under that interface. I'd really like that edge to go away there,
It's implemented for the next release. It was not available so far as changing the edge style is no DUI feature in opposite to CUI. But in the end, it's also possible to use it inside a DUI panel.

I have issues too trying to get my CUI nodes to show up in a DUI tree, which always seems to stick with the example nodes. I paste in the tree.db and .cfg files from one portable fb2k to another, but the one interface doesn't seem to cross over to the other. Is there a way I can manipulate the foo_uie_sql_tree.db tables to indicate which of the <ROOT> nodes is the active one?
No. The active node is not stored in the database but in the config of the relevant panel. You would need to identify the root node of the DUI panel and link the CUI nodes to this root node.

BTW, the issues you reported on Thursday are also fixed for the next release. I could reproduce the crash as you described it, but only when I added accidently an UNC path from a network share as library folder. With a relativ path from the same drive, it didn't happen.

For the next release (a fb2k v2 only release), I already changed the path handling a bit before and maybe due to this the described crash is not occurring anymore.
Title: Re: foo_uie_sql_tree
Post by: fbuser on 2023-04-22 14:35:17
New version, see first post.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-04-22 15:15:32
Hi,

Example script "Rebuild medialibrary tables" crashes foobar (x64) - x32 not tested. :o
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-04-22 15:29:56
Example script "Rebuild medialibrary tables" crashes foobar (x64) - x32 not tested. :o
Not reproducible for me. Can you please post the crash report?
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-04-22 15:31:07
Same here (64bit)
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-04-22 15:43:37
Here you are :-)

Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-04-22 16:55:24
As I said, it's not reproducible for me so I can only guess, but it is actually a problem of foo_sqlite. Does the problem also occur with the attached file? If yes, please also post the crash reports again.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-04-22 17:19:13
Much better. No crash.
With Rebuild ML tables, I got a popup. Repeating the command and no error message.
Examples | Library | Album and Genre reads without tags.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-04-22 17:24:45
Hi,
same here - no crash report!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-04-22 17:40:50
Thank you for the feedback. A new version for foo_sqlite with the fix is available now. To git rid of the popup just remove the transaction handling commands from the relevant script (BEGIN TRANSACTION; and COMMIT;) or redownload foo_uie_sql_tree (no new version, just scripts were updated).
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-04-22 18:01:46
Maintenance works now.
Library nodes are not tagged.
Queries are working, except length_seconds.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-04-22 22:39:40
Thanks for reporting. Both issues are again foo_sqlite issues and are fixed with the latest foo_sqlite version.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-04-22 22:57:27
Fix confirmed. Thank you.

Album: Strange Tracknumber sorting.

Request: My console is "spammed" by foo_uie_sql_tree status messages. Please make per option a verbose message system version for diagnostic purposes and a short version only with warnings and errors. Thank you.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-01 18:10:38
Dark mode missing in "Delete node" confirmation message box.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-05-01 19:19:39
Dark mode missing in "Delete node" confirmation message box.
Like for mostly all other system message boxes used by my components having the OS not using dark mode. Fixed so far for foo_sqlite and foo_uie_sql_tree. The others will follow.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-03 21:34:23
I'm trying to add custom icons for the nodes with the Advanced options > Icon file paths setting. The first .bmp file appears as expected, but I haven't been able to add any more than that. I've tried both relative and absolute paths separated by pipes, but only the first is available to be chosen.

Incidentally, is it possible to put some kind of profile_path variable in a relative path? I've been using .\profile\… and working with a portable installation, but I'd like to be able to handle standard installations as well.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-05 10:54:42
How do I copy my query tree from Default UI to Columns UI?
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-05-07 22:10:09
How do I copy my query tree from Default UI to Columns UI?
Start the application with Default UI. Open the menu "Library -> SQL Tree" and copy all your root nodes from the tree panel in your layout to the tree in the popup window, either by using the context menu or by dragging them while pressing CTRL.

Then start the application with Columns UI. Open again the menu "Library -> SQL Tree" and copy all your root nodes to the tree panel in your layout.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-07 23:35:41
Works in DUI with Library -> [Shift] SQLTree. Thanks
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-05-08 15:44:21
Hi,
copy SQLTree from DUI to CUI doesn't work for me.
If I copy the tree <Meine>, on right-click I'd no past on CUI side. I think, I need a instruction for a noob :-)
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-08 16:13:29
:P
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-05-08 16:34:24
Hello Grimes,

thank you ... but Point 7 "context menu <Meine>: Copy" - whitch copy: DUI or CUI
I don't have a SQLTree build In only Library->SQLTree
Copy from DUI Library_SQLTree to CUI Library_SQLTree doesn't work for me
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-08 17:23:03
It works, because Main menu | Library | SQL Tree (the popup window)  is independent from User interface. (Shift is "Hochstelltaste".)
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-05-08 18:19:32
Hi grimes,

copy from popup window SQLTree (DUI) to popup window SQLTree (CUI) doesn't seem to work for me.

MEINE in DUI copied and past in popup window in CUI (both opened with SHIFT) can't go. PASTE is grayed out.
CTRL-C and CTRL-V doesn't work for me too. I think, I made it by hand :-(
thank you :-)
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-08 18:26:01
No. Copy from DUI Panel to DUI popup window!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-05-08 18:36:02
yes, then open CUI - open popup window
copy DUI MEINE from popup window and paste it in CUI popup window (here PASTE is greyed out)!
or am I completely wrong ? ::)
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-08 18:43:24
Ok, then it works on DUI side. CUI: Open popup window, copy <Meine> and paste in CUI panel.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-05-08 18:45:46
this works for me too, but copy from popup DUI to popup CUI doesn't work. This is what I'm want to do.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: grimes on 2023-05-08 18:49:01
How do you switch from DUI to CUI? See screenshot.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: ASopH on 2023-05-08 18:50:48
Oh, once standart install, second is a portable. It seems there is no ex-/import possible!
I see the problem!
thank you grimes
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-08 21:16:04
I'm still having trouble getting my bitmaps to appear in the icon selection dialog. I had three of them entered in the Properties > Advanced > Tools > SQL Tree > Icon file paths (separate by |) box with the previous version of the component. They all had direct links, but only the first appeared.

I upgraded to the new version of the component, and still just one appeared in that dialog and also in the SQL Tree itself for the folder I had selected it for. I then tried to change them to relative links, and probably misdirected ones. The one bitmap that showed disappeared from the dialog, the tree, and also the button in the Edit Folder dialog. There was just a part of the text from the Advanced setting appearing in the button.

Now, try as I might, I cannot get even one image to appear anywhere. they are all in a directory at profile\customizations\img\nodes\. I've tried the following:

Which file is this linked relative to? They're all 16 x 16 .bmp files.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-05-08 22:52:04
I'm still having trouble getting my bitmaps to appear in the icon selection dialog. I had three of them entered in the Properties > Advanced > Tools > SQL Tree > Icon file paths (separate by |) box with the previous version of the component. They all had direct links, but only the first appeared.
I tried with your images using relative paths and even the first one didn't work for me. In general I need to check it, but you should combine all your images to one bitmap (see attachment). Using this it works for me.

I've tried the following:
  • customizations\img\nodes\library.bmp
  • \customizations\img\nodes\library.bmp
  • .\customizations\img\nodes\library.bmp
  • ..\customizations\img\nodes\library.bmp
  • ..\..\customizations\img\nodes\library.bmp
  • C:\<direct_path>\customizations\img\nodes\library.bmp

Which file is this linked relative to? They're all 16 x 16 .bmp files.
It's relative to the profile folder. Therefore the first one is the correct one. But the direct path (the last one) should also work.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-10 00:10:45
Ah yes, that works now. I thought I remembered from last time I tried to do this a while back that there was a certain way to set the file up (with all of the images in a row like that), but I couldn't find what it was.

Thanks. All better. ;D
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-10 22:44:23
I guess I spoke too soon, and I'm back to the pain on the node icons. It seems that when I change the Advanced > Tools > SQL Tree > Icon file paths setting, I have difficulty getting my icons to show up in the dialog for selecting icons afterwards. It worked the first time, and it eventually worked again when I used the file you made above, but I have no idea how that happened.

Now, I've swapped the file for another, and I was expecting my nodes to display the wrong ones, since the order changed. Instead, they displayed nothing. So I set them all back to using their parent node settings and cleared all of my changes out. I've restarted fb2k several times. Still no icons in the dialog.

I tried uninstalling SQLite Utilities and SQL Tree. I left the

I tried a different fb2k installation where I had put a value in the advances properties but not yet used any of the icons. I updated that, and that installation doesn't show any of my icons either.

I can't find how to reliably get my icons into the dialog. I anticipate more trial and error with them as I see what looks good and what doesn't. Help!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-05-13 11:10:00
The problem with your icons is that they are 32bit bitmap files. Those are not supported. When I created the previously attached file with IrfanView it was automatically converted to a 24bit bitmap file and therefore it could be loaded. Converting your single icon files to 24bit makes them loadable too. However the preferred way is to use combined bitmap files.

The next release will show an error dialog containing all the files which not could be loaded.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-13 13:43:17
Ah, thanks for that explanation. Perhaps I can work with that.

I did try using again the file that you linked here and didn't have any success. Maybe I had something else wrong.

I noticed too that—while the icon numbers in the dialog are a 1-based sequence—the values in the database seem to start from zero. This doesn't affect anything, and I probably shouldn't go mucking about in the databases, but it's something to keep in mind when I do.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-13 14:40:51
OK, I got it working, but I have questions. I have a Python script that takes Material Design Icons svg files, colors them, and converts them to a merged bmp. This file is not able to be read, but opening it up in IrfanView shows the result to be 24 BPP, and going over to Image > Show Channel > has the Alpha channel greyed out. However, the size in the status bar reads "11.13 KB / 8.29 KB".

If I then use IrfanView to Save As this bmp to another, I get a working file with a size of "8.30 KB / 8.29 KB".

So I need to add another step to my script, but my output seems to be both 24 BPP and not 24 BPP, assuming those are the right 24-bit property.

How can I better inspect the difference between the two images and determine what accounts for it?
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2023-05-13 18:04:27
This file is not able to be read, but opening it up in IrfanView shows the result to be 24 BPP
IrfanView shows actually the result of the in memory image after converting it from 32bit in the status bar. Selecting the menu "Image -> Information..." gives you the information, that the original image is 32bit. And, of course, this explains also the size difference.

But why do you use a Python script for creating the final icon bitmap file. It appears a bit overcomplicated to me. This can be easily done with IrfanView by selecting the menu "Image -> Merge images (Simple Panorama image)..." (*)

(*) The files which are used in the attached screen shot are your 32bit BMP files, but it can also be done directly with the SVG files in the same way.

Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-13 19:49:06
Hey, these little scripts are the one coding thing I'm kinda getting good at. Don't take that away from me! ;D

It's the script that produced all of those bmp images in the first place. I saw a chance to learn more stuff and lock in all the coloring and resizing too. It's easier now that its done than hand-making the source files was before, especially with another color scheme for a different profile.

After casting about fruitlessly with ImageMagick SVG delegates and whatnot, I got an effective function to add at the end. ;D  :'(

Code: (python) [Select]
    def convert_again(self, file_name: str) -> None:
        """Clean up weird conversion from Wand for fb2k SQL Tree.

        The final output was not loading successfully in the foobar2000
        SQL Tree component, but reconverting with IrfanView did the
        trick. I don't know if Wand/ImageMagick is properly configured
        to use Inkscape as a delegate for SVG conversion.

        This does not protect paths containing spaces or maybe even
        capital letters.
        """
        subprocess.run([IRFANVIEW, file_name, f'/convert={file_name}'])

[EDIT:] Oh hang on, I got it to work a better way without my convert_again kludge. The key was changing the format of those intermediate files from bmp to png and then saving the merged file as a bmp and prepending the filename in that save with BMP3:. Omit any of those bits—and I have—and no dice.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2023-05-19 10:31:49
I'm having new trouble with the node images. In trying a new installation, I get the new dialog that the image could not be loaded. However, this is an image that works in another installation.

I made a copy of that installation, removed the file from the Advanced Preferences, and restarted fb2k. No images appeared in the nodes as expected. Then I entered the file back into the the Preferences and restarted again. This time, the dialog came up, and the previously-working image failed to load.

For good measure, I opened the image up in IrfanView and saved it as itself. That image didn't work either.

{Edit:] Another test. I had made the same image in two different color schemes for two different installations. With a fresh copy of one of those installations, I swapped out the bmp file with the differently-colored one without editing the Preferences. Opening fb2k, the new file was applied, and the colors had changed.

Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-02-22 21:45:48
I'm trying to make tree node with conditional sub-nodes based on the multi-value genre field.  For example:
Code: [Select]
Track #1 Genre - Soundtrack; Score; Anime; Electronic
Track #2 Genre - Soundtrack; Score; Anime; Rock
Track #3 Genre - Soundtrack; Score; Movie; Jazz
Track #4 Genre - Soundtrack; Stage & Screen; Soul; Funk
Track #5 Genre - Soundtrack; Stage & Screen; Rock; Progressive

Ideally, it should look like this:
Code: [Select]
Soundtracks
--Score
----Anime
------Electronic
------Rock
----Movie
------Jazz
--Stage & Screen
----Soul
----Funk
----Progressive
----Rock

This is the Query I tried and it works for the 1st level of sub-nodes, but both the Score and Stage & Screen nodes include ALL of the tracks with Soundtrack in the genre.  I didn't even get to trying to adding the 3rd and 4th levels of sub-nodes.

Code: [Select]
SELECT
CASE
WHEN genre_mv IN ('Score', 'Stage & Screen') THEN genre_mv
END Category
FROM MediaLibrary
WHERE genre LIKE 'Soundtrack%'
GROUP BY genre_mv

Also, I have a genre field without split values and I have a genre_mv field with split multivalue tag checked.

Thanks for any help!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2024-02-24 21:06:13
Using multivalue splitting is the wrong approach here as it duplicates the rows of the result which leads to having an own node for each value of the column.

Instead you should first define a function to convert the multivalue column to a JSON array in the SQLite console:
Code: [Select]
SELECT define('mv2json','''["''||replace(coalesce(:v,''''),'' · '',''","'')||''"]''');

This function is persistent and therefore needs to be created only once. In case you defined an own multivalue separator for your genre column you need to replace this
Code: [Select]
... '' · '' ...

by this
Code: [Select]
... ''<your multivalue separator>'' ...

Afterwards you can define your query like this:

Code: [Select]
WITH Genres AS (
  SELECT mv2json(genre) genres
  FROM MediaLibrary
)
SELECT
  genres->>0,
  genres->>1,
  genres->>2,
  ...
FROM Genres

Add as many columns as you need to cover the maximum numbers of values in your genre tag.

Finally, tick "Omit <null>" in the advanced tab.

I did not test it in detail, but it should work like this.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-02-24 21:19:28
fbuser, you literally replied while I was writing this new post; awkward timing, but deeply appreciated none-the-less.  I made a bunch of changes since my last one, but I'm going to read your reply and see what changes I should make.

Multi-Genre Examples:
Code: [Select]
Track #1 Genre - Soundtrack; Score; Anime; Electronic
Track #2 Genre - Soundtrack; Score; Anime; Rock
Track #3 Genre - Soundtrack; Score; Movie; Jazz
Track #4 Genre - Soundtrack; Stage & Screen; Soul; Funk
Track #5 Genre - Soundtrack; Stage & Screen; Rock; Progressive

Ideally, it should look like this:
Soundtracks
Code: [Select]
--Score
----Anime
------Electronic
------Rock
----Movie
------Jazz
--Stage & Screen
----Soul
----Funk
----Progressive
----Rock

The album sub-nodes would follow their respective genres.  Also, I have a genre field without split values and I have a genre_mv field with split multivalue tag checked.

If I use GROUP BY at the end of the query, it kind of looks like what I want (see screenshot) but I know I can't use GROUP BY because it will only return a single album for each genre_mv.

Batch:
Code: [Select]
DROP TABLE IF EXISTS tabletest;
CREATE TABLE tabletest AS
SELECT
       genre,
       genre_mv,
       album,
       discnumber,
       tracknumber,
       title,
       [original album],
       [album artist],
       path,
       subsong

FROM MediaLibrary
WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%'

ORDER BY genre_mv

Query:
Code: [Select]
SELECT
    CASE
        WHEN genre LIKE '%Score%' THEN 'Score'
        WHEN genre LIKE '%Stage & Screen%' THEN 'Stage & Screen'
        END AS Category1,
    CASE
        WHEN genre LIKE '%Score%' AND genre_mv IN ('Anime', 'Movie', 'Musical', 'Television', 'Video Game') THEN genre_mv
        WHEN genre LIKE '%Stage & Screen%' AND genre_mv NOT IN ('Soundtrack', 'Score', 'Stage & Screen') THEN genre_mv
        END AS Category2,
    album

FROM tabletest
GROUP BY Category1, Category2;

I still a have a lot to learn about SQL so thanks for any help!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-02-25 16:37:37
Please ignore that last post.  Regarding the JSON Array:

I just want to make sure that I add a semi-colon to the Multivalue Separator when I edit the genre column in the MediaLibrary Table and I leave the Split multivalue tag unchecked (see screenshot)

To define the function, I change the separator to a semi-colon and execute it in an SQL Console window:
Code: [Select]
SELECT define('mv2json','''["''||replace(coalesce(:v,''''),'';'',''","'')||''"]''')
It seems I can only define it once.  If I change the name from "mv2json" then I can I can run it again, otherwise I get this error:
Code: [Select]
SQLite Error: (5) database is locked
If I want to change the name of the function to "multivaluegenre2jsonarray", how do I delete the old one?

Also, after I define the JSON array function and run the following query with Omit Null checked, it works great in SQLite console but when I click OK I get the following error:
Code: [Select]
SQLite Error: (1) no such function: mv2json
I made sure "mv2json" was entered correctly in the query and when I defined the function.

Query:
Code: [Select]
WITH Genres AS (
  SELECT mv2json(genre) genres
  FROM MediaLibrary
)

SELECT
  genres->>0 AS Genre1,
  genres->>1 AS Genre2,
  genres->>2 AS Genre3,
  genres->>3 AS Genre4

FROM Genres
ORDER BY Genre1, Genre2, Genre3, Genre4
Also, for unrelated reasons, Foobar2000 crashed a bunch of times while I was working on this.  I don't know if that contributed to the locked database problem.  Thanks for your help!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2024-02-25 22:41:16
It seems I can only define it once.
Yes, this is what I meant with "persistent".

If I change the name from "mv2json" then I can I can run it again
That's because you defined a new function with another name.

If I want to change the name of the function to "multivaluegenre2jsonarray", how do I delete the old one?
With
Code: [Select]
SELECT undefine('mv2json');

Also, after I define the JSON array function and run the following query with Omit Null checked, it works great in SQLite console but when I click OK I get the following error:
Code: [Select]
SQLite Error: (1) no such function: mv2json
This should not be the case. Is the function listed, when you execute the following command?
Code: [Select]
SELECT * FROM sqlean_define;
You might need to restart the application to get finally rid of it.

Query:
...
This query wouldn't work, even if you wouldn't get the "no such function" error. First because I missed to add path and subsong to the Genres CTE, second because of the "order by" that you added. Actually you would be able to create the tree, but clicking on a node would throw an error.

The make it work the query should be re-written like this:
Code: [Select]
SELECT
  Genre1,
  Genre2,
  Genre3,
  Genre4
FROM (
  WITH Genres AS (
    SELECT path,
           subsong,
           mv2json(genre) genres
    FROM MediaLibrary
  )
  SELECT
    path,
    subsong,
    genres->>0 AS Genre1,
    genres->>1 AS Genre2,
    genres->>2 AS Genre3,
    genres->>3 AS Genre4
  FROM Genres
)
ORDER BY Genre1, Genre2, Genre3, Genre4

Also, for unrelated reasons, Foobar2000 crashed a bunch of times while I was working on this.  I don't know if that contributed to the locked database problem.
Possibly. I already reduced the functionality of the define extension (see here (https://github.com/nalgeon/sqlean/blob/main/docs/define.md).) because the internal default function handling was instable. However, after the adjustments I didn't face any issues anymore.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: stevehero on 2024-03-09 23:13:51
Is there a way to edit the queries outside foobar? I want to uncheck the checkbox and set 2000 in these settings for all my queries.




Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2024-03-10 16:20:56
Yes, it is possible and also inside foobar2000, but not really recommended.

Especially as the desired changes at least the one for the node limit doesn't appear reasonable to me. As long as you don't have many hundreds of nodes the default of 100,000 should not cause any problems. With such a low limit you only increase the chance, that a query fails depending on your library size. Furthermore there is also an overall node limit in the advanced properties (default 1,000,000) which prevents too much memory usage.

Anyway, you can change it by executing the following code in the SQLite console at your own risk:

Code: [Select]
ATTACH fb2k_profile_path()||'\configuration\foo_uie_sql_tree.db' AS sqltree;

UPDATE ust_TreeItem
SET tri_node_limit=2000,
    tri_include_refresh_all=0
WHERE tri_node_type=3
  AND tri_sql_query<>'';

Afterwards you need to restart the application.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: stevehero on 2024-03-10 22:42:58
Afterwards you need to restart the application.
Thanks very much. I edited the .db file in SQLiteStudio.

I have an issue. The program crashes if I refresh all nodes. Here's my db file for you to try. I'm using the last v2.2 preview of foobar2000.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2024-03-11 11:00:00
In the table ust_treeitem, the color values are stored as one number. How are those numbers calculated? I'd like to do those calculations for new colors to update the table with.

For instance, my background of rgb(25, 25, 35) is stored in tri_color_back as 2300185.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Case on 2024-03-11 11:50:11
color values are stored as one number. How are those numbers calculated?
Red + Green×256 + Blue×256².

Your example color for example is 25 + 25*256 + 35*65536 = 2300185.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Sue Dunham on 2024-03-11 22:03:06
Red + Green×256 + Blue×256².

Your example color for example is 25 + 25*256 + 35*65536 = 2300185.

Ah, gotcha, thanks. I tried some online converter and got the wrong number, not really knowing my big end from my little.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: Gabriel Schwartz on 2024-03-13 23:39:47
The Imcomplete Albums gives incorrect result as album with multiple discs are grouped.
I found this work for me:

SELECT "%album artist%"||' - '||album "Artist - Album",
       date,
       discnumber,
       count(*) count,
       max(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1,2,3
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY "%album artist%", discnumber
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-03-16 14:27:38
Thanks for the tips and explanations back in Reply #366.  Here's the latest version of my Soundtrack SQL Tree and I would really appreciate any advice how to make it simpler or faster.  I'm new to SQL so please forgive any embarrassing mistakes.   O:)

Here's some background about what I'm trying to do.  MediaMonkey is my main player / library manager and I rely heavily on the MagicNodes plugin which uses SQL queries to build tree nodes and sub-nodes; I'm sure that sounds familiar.   :))

But, MagicNodes can't generate sections / folders with its queries, as far as I can tell.  What I mean by section is using the AS statement to name a section of sub-nodes.  I also wanted to add visual dividers like "---------".

Here's a reminder of the what my genres look like:
Code: [Select]
Track #1 Genre - Soundtrack; Score; Anime; Electronic
Track #2 Genre - Soundtrack; Score; Anime; Rock
Track #3 Genre - Soundtrack; Score; Movie; Jazz
Track #4 Genre - Soundtrack; Stage & Screen; Soul; Funk
Track #5 Genre - Soundtrack; Stage & Screen; Rock; Progressive

Please see the screenshot to see the results of the query.  Everything above the dividers are the Sections.  I think it came out great!  But, I'm sure there's room for improvements.  For example, I created a table from MediaLibrary called MediaTable so I could insert the dividers.  Then I created SectionTable from MediaTable to create the sections and their sub-nodes.  But, I think I read in this thread that nested tables will slow the query.  Also, I'm not sure if I should be using the Batch section to create the tables.  I'm using the UNION ALL statements to make sure that a track that qualifies for the Artists section and the Series section will appear under both.  Here it is...

BATCH:
Code: [Select]
----------------------------------------------------- Create MediaTable From MediaLibrary

DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
  SELECT

    path,
    subsong,
    genre,
    genre_mv,
    multivaluegenre2jsonarray(genre) genresplit,
    grouping,
    [original album],
    [original title],
    [album artist],
    album,
    discnumber,
    tracknumber

  FROM MediaLibrary
  WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%'
  ORDER BY album, discnumber, tracknumber;

----------------------------------------------------- Insert Dummy Dividers Into MediaTable

INSERT INTO MediaTable(genre, genresplit, album)
VALUES

--('Soundtrack;Score;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;---------------')),'---------------'), --Only for Genres Section
('Soundtrack;Score;Anime;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Anime;---------------')),'---------------'),
('Soundtrack;Score;Movie;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Movie;---------------')),'---------------'),
('Soundtrack;Score;Musical;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Musical;---------------')),'---------------'),
('Soundtrack;Score;Television;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Television;---------------')),'---------------'),
('Soundtrack;Score;Video Game;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Video Game;---------------')),'---------------'),
('Soundtrack;Stage & Screen;---------------',(multivaluegenre2jsonarray('Soundtrack;Stage & Screen;---------------')),'---------------');

----------------------------------------------------- Create SectionsTable From MediaTable

DROP TABLE IF EXISTS SectionsTable;
CREATE TABLE SectionsTable AS

----------------------------------------------------- Artists Section

SELECT DISTINCT
  genresplit->>1 AS Category1,

  CASE
    WHEN genresplit LIKE '%Score%' THEN genresplit->>2
    WHEN genresplit LIKE '%Stage & Screen%' THEN 'Artists'
    END AS Category2,

  CASE
    WHEN genresplit LIKE '%Score%' THEN 'Artists'
    WHEN genresplit LIKE '%Stage & Screen%' THEN [album artist]
    END AS Category3,

  CASE
    WHEN genresplit LIKE '%Score%' THEN [album artist]
    WHEN genresplit LIKE '%Stage & Screen%' THEN album
    END AS Category4,

  CASE
    WHEN genresplit LIKE '%Score%' THEN album
    END AS Category5,

   path,
   subsong,
   genre,
   genresplit,
   grouping,
   [original album],
   [original title],
   [album artist],
   album,
   discnumber,
   tracknumber

FROM MediaTable
WHERE [album artist] IS NOT NULL AND [album artist] <> 'Various Artists'

----------------------------------------------------- Universes/Series Section

UNION ALL

SELECT DISTINCT
  genresplit->>1 AS Category1,

  CASE
    WHEN genresplit LIKE '%Score%' THEN genresplit->>2
    WHEN genresplit LIKE '%Stage & Screen%' AND [original album] IS NOT NULL THEN 'Series'
    WHEN genresplit LIKE '%Stage & Screen%' AND [ORIGINAL TITLE] IS NOT NULL THEN 'Series'
    END AS Category2,

  CASE
    WHEN genresplit LIKE '%Score%' THEN 'Universe'
    WHEN genresplit LIKE '%Stage & Screen%' AND [original album] IS NOT NULL THEN [original album]
    WHEN genresplit LIKE '%Stage & Screen%' AND [ORIGINAL TITLE] IS NOT NULL THEN [ORIGINAL TITLE]
    END AS Category3,

  CASE
    WHEN genresplit LIKE '%Score%' AND [original album] IS NOT NULL THEN [original album]
    WHEN genresplit LIKE '%Score%' AND [ORIGINAL TITLE] IS NOT NULL THEN [ORIGINAL TITLE]
    WHEN genresplit LIKE '%Stage & Screen%' THEN album
    END AS Category4,

  CASE
    WHEN genresplit LIKE '%Score%' THEN album
    END AS Category5,

    path,
    subsong,
    genre,
    genresplit,
    grouping,
    [original album],
    [original title],
    [album artist],
    album,
    discnumber,
    tracknumber

FROM MediaTable
WHERE [original album] IS NOT NULL OR [ORIGINAL TITLE] IS NOT NULL

----------------------------------------------------- Everything Else

UNION ALL

SELECT DISTINCT

genresplit->>1 AS Category1,

CASE
  WHEN genre <> 'Soundtrack;Score;---------------' THEN genresplit->>2
  END AS Category2,

CASE
  WHEN genresplit LIKE '%Score%' THEN album
  WHEN genresplit LIKE '%Stage & Screen%' THEN album
  END AS Category3,

NULL AS Category4,
NULL AS Category5,

path,
subsong,
genre,
genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,
tracknumber

FROM MediaTable
WHERE ([album artist] IS NULL  OR [album artist] = 'Various Artists') AND [original album] IS NULL AND [ORIGINAL TITLE] IS NULL;

QUERY:
Code: [Select]
SELECT
Category1,
Category2,
Category3,
Category4,
Category5

FROM SectionsTable
ORDER BY
Category1,

CASE
  WHEN Category2 = 'Artists' THEN 0
  WHEN Category2 = 'Genres' THEN 1
  WHEN Category2 = 'Series' THEN 2
  WHEN Category2 = 'Universe' THEN 2
  WHEN Category2 = '---------------' THEN 3
  ELSE Category2
  END,

CASE
  WHEN Category3 = 'Artists' THEN 0
  WHEN Category3 = 'Genres' THEN 1
  WHEN Category3 = 'Series' THEN 2
  WHEN Category3 = 'Universe' THEN 2
  WHEN Category3 = '---------------' THEN 3
  ELSE Category3
  END,

Category4,
Category5
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2024-03-16 18:39:00
I would really appreciate any advice how to make it simpler or faster.
Simpler: Probably not possible. SQL queries are tending to become quite complicated, if special conditions need to be fulfilled like here. Faster: Optimizing SQL queries can in general only be done based on the size and the content of the underlying data. But for sure the "ORDER BY" which is used for the creating of the MediaTable just decreases the performance without having any use. Furthermore at least creating an index on the "album artist" column of the MediaTable could be helpful.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-03-16 19:33:29
I'm going to take this as a compliment.
Simpler: Probably not possible

Regarding the ORDER BY used to create MediaTable, I added that because I had a problem with track numbers in the playlist, albums in the playlist would be sorted like this:
Code: [Select]
1, 10, 11, 12...19, 2, 20, 21, 22...29, etc.

I had a really hard time fixing it until I added that ORDER BY statement in the MediaTable creation.  I assume it had something to do with tracks for albums appearing twice in the query results.  It would happen for most albums, but not all, and the problem wasn't the tags since they looked okay in MP3Tag.  If you have some ideas that fix the problem, I'd love to hear them.

I created an album artist index for MediaTable and the results are faster.
Code: [Select]
CREATE INDEX AlbumArtistIndex ON MediaTable ([album artist]);

Thanks for the advice!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2024-03-16 21:32:48
Regarding the ORDER BY used to create MediaTable, I added that because I had a problem with track numbers in the playlist, albums in the playlist would be sorted like this:
Code: [Select]
1, 10, 11, 12...19, 2, 20, 21, 22...29, etc.
It's because all columns are considered as text values by default. One possibility to fix this is to query the tracknumber as
Code: [Select]
(CAST(tracknumber AS INTEGER) tracknumber
when creating the MediaTable.

I had a really hard time fixing it until I added that ORDER BY statement in the MediaTable creation.
This was only by coincidence. An ORDER BY affects only the final query. There is no predictable order in which rows are stored in a table nor is there one when querying a table without an ORDER BY.

I assume it had something to do with tracks for albums appearing twice in the query results.
This happens because you still have genre_mv in the query which creates the MediaTable. Just drop it.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-03-17 02:39:39
I removed the genre_mv and the ORDER BY statement and I added the CAST function, but the track numbers are in a random order now, not 1, 10, 11, 12...2, 20, etc, like before.  Also, I understand that ORDER BY is useless in the CREATE TABLE, but for whatever reason, the tracknumbers sort correctly when I add it back in.

Code: [Select]
DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
  SELECT

    path,
    subsong,
    genre,
--  genre_mv,
    multivaluegenre2jsonarray(genre) genresplit,
    grouping,
    [original album],
    [original title],
    [album artist],
    album,
    discnumber,
    CAST(tracknumber AS INTEGER) tracknumber

  FROM MediaLibrary
  WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%'
-- ORDER BY album, discnumber, tracknumber
;
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: fbuser on 2024-03-17 06:56:08
I removed the genre_mv and the ORDER BY statement and I added the CAST function, but the track numbers are in a random order now,
Seems that I forgot to mention, that you have to move the ORDER BY of the table creation to the final query.
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-03-17 13:08:08
Well, hot damn! That worked!  I used to have the ORDER BY at the bottom of the query, but I was still getting the 1, 10, 11, etc. track number order.  So, I guess it was something else that was causing the problem.

Thanks for helping me fix it the right way!
Title: Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)
Post by: chrisdukes on 2024-03-18 00:21:33
I ran into a problem with track numbers that have letters; I have a lot of vinyl rips that often use A1, or AA3, etc in the tracknumber.  The CAST(tracknumber AS INTEGER) function worked great for the number only track numbers, but track numbers with letters and double digits sort like this, A1, A10, A11, A12...B1, B10, B11, B12, etc.

So, I changed my first CREATE TABLE to this and it works, but I was wondering if you know a better way:
Code: [Select]
DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
SELECT

path,
subsong,
genre,
multivaluegenre2jsonarray(genre) genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,

CASE
WHEN tracknumber LIKE '%0%' THEN substr(tracknumber, 1, instr(tracknumber, '0') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '0')))
WHEN tracknumber LIKE '%1%' THEN substr(tracknumber, 1, instr(tracknumber, '1') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '1')))
WHEN tracknumber LIKE '%2%' THEN substr(tracknumber, 1, instr(tracknumber, '2') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '2')))
WHEN tracknumber LIKE '%3%' THEN substr(tracknumber, 1, instr(tracknumber, '3') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '3')))
WHEN tracknumber LIKE '%4%' THEN substr(tracknumber, 1, instr(tracknumber, '4') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '4')))
WHEN tracknumber LIKE '%5%' THEN substr(tracknumber, 1, instr(tracknumber, '5') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '5')))
WHEN tracknumber LIKE '%6%' THEN substr(tracknumber, 1, instr(tracknumber, '6') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '6')))
WHEN tracknumber LIKE '%7%' THEN substr(tracknumber, 1, instr(tracknumber, '7') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '7')))
WHEN tracknumber LIKE '%8%' THEN substr(tracknumber, 1, instr(tracknumber, '8') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '8')))
WHEN tracknumber LIKE '%9%' THEN substr(tracknumber, 1, instr(tracknumber, '9') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '9')))
ELSE CAST(tracknumber AS INTEGER)
END AS tracknumber_sorted

FROM MediaLibrary
WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%';