foo_uie_sql_tree Options

[marc2003]

oh right. it's just that no answer looks pretty unequivocal to me so i thought you must have missed it.

[grimes]

Top artists played this year

CODE

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

[chiwou]

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

[fbuser]

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?

[chiwou]

I delete the database and settings from my profile folder, now it seems too work, but I'm gonna keep an eye on that

[Canar]

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...

[aliendesaster]

I love it. So far, did not have time to test it thoroughly, but I'll do.

[tg2k3]

is there any way to to search the library outside of foobar with this? e.g. having a another software querying the sqlite database?

[fbuser]

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

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.

This post has been edited by fbuser: Oct 14 2011, 15:56

[exalaskan]

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

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 * FROM random_view4
UNION
SELECT * FROM random_view5;

Many thanks to fbuser for the component.

[exalaskan]

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

AND (((last_played < '2011-09-09') AND ((julianday('now') - julianday(last_played)) > 42)) OR (last_played == ''))

(sorry for the quick update)

[Black_Over_Bills...]

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!

[fbuser]

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.

[Black_Over_Bills...]

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?

[Zarty]

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

[fbuser]

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.

[Zarty]

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

[Black_Over_Bills...]

Lost all my queries ..... again!

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.

[fbuser]

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.

[Black_Over_Bills...]

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.

[Black_Over_Bills...]

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.

This post has been edited by Black_Over_Bills_Mothers: Nov 19 2011, 15:31

[Black_Over_Bills...]

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.

[gob]

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.

This post has been edited by gob: Dec 7 2011, 07:46

[chiwou]

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

this is what I've have

CODE

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

This post has been edited by chiwou: Dec 13 2011, 19:24

[fbuser]

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 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 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 genre_mv "Genre" FROM MediaLibrary
WHERE genre_mv='soundtrack'