Skip to main content

Notice

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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





foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

 

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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