IPB

Welcome Guest ( Log In | Register )

5 Pages V  < 1 2 3 4 > »   
Reply to this topicStart new topic
foo_uie_sql_tree
marc2003
post Oct 1 2011, 20:57
Post #26





Group: Members
Posts: 4330
Joined: 27-January 05
From: England
Member No.: 19379



oh right. it's just that no answer looks pretty unequivocal to me so i thought you must have missed it. tongue.gif
Go to the top of the page
+Quote Post
grimes
post Oct 1 2011, 22:10
Post #27





Group: Developer
Posts: 302
Joined: 12-November 07
From: Frankfurt
Member No.: 48701



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



Go to the top of the page
+Quote Post
chiwou
post Oct 1 2011, 22:33
Post #28





Group: Members
Posts: 236
Joined: 5-November 04
From: Spitzbergen
Member No.: 17985



QUOTE (fbuser @ Oct 1 2011, 14:39) *
QUOTE (chiwou @ Oct 1 2011, 13:42) *
btw: it's the panel on the left side smile.gif
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
Go to the top of the page
+Quote Post
fbuser
post Oct 1 2011, 23:10
Post #29





Group: Developer
Posts: 673
Joined: 26-September 07
Member No.: 47369



QUOTE (chiwou @ Oct 1 2011, 22:33) *
QUOTE (fbuser @ Oct 1 2011, 14:39) *
QUOTE (chiwou @ Oct 1 2011, 13:42) *
btw: it's the panel on the left side smile.gif
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?
Go to the top of the page
+Quote Post
chiwou
post Oct 1 2011, 23:44
Post #30





Group: Members
Posts: 236
Joined: 5-November 04
From: Spitzbergen
Member No.: 17985



I delete the database and settings from my profile folder, now it seems too work, but I'm gonna keep an eye on that wink.gif
Go to the top of the page
+Quote Post
Canar
post Oct 2 2011, 02:48
Post #31





Group: Super Moderator
Posts: 3327
Joined: 26-July 02
From: princegeorge.ca
Member No.: 2796



QUOTE (marc2003 @ Oct 1 2011, 12:57) *
oh right. it's just that no answer looks pretty unequivocal to me so i thought you must have missed it. tongue.gif
Hope springs eternal in the human breast...


--------------------
∑:<
Go to the top of the page
+Quote Post
aliendesaster
post Oct 13 2011, 18:29
Post #32





Group: Members
Posts: 129
Joined: 29-December 03
From: Berlin
Member No.: 10789



I love it. So far, did not have time to test it thoroughly, but I'll do.
Go to the top of the page
+Quote Post
tg2k3
post Oct 14 2011, 00:58
Post #33





Group: Members
Posts: 45
Joined: 10-June 05
Member No.: 22635



is there any way to to search the library outside of foobar with this? e.g. having a another software querying the sqlite database?
Go to the top of the page
+Quote Post
fbuser
post Oct 14 2011, 15:54
Post #34





Group: Developer
Posts: 673
Joined: 26-September 07
Member No.: 47369



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
Go to the top of the page
+Quote Post
exalaskan
post Oct 25 2011, 17:54
Post #35





Group: Members
Posts: 3
Joined: 27-January 11
Member No.: 87716



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.
Go to the top of the page
+Quote Post
exalaskan
post Oct 25 2011, 20:04
Post #36





Group: Members
Posts: 3
Joined: 27-January 11
Member No.: 87716



QUOTE (exalaskan @ Oct 25 2011, 11:54) *
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)
Go to the top of the page
+Quote Post
Black_Over_Bills...
post Nov 8 2011, 13:14
Post #37





Group: Members
Posts: 124
Joined: 16-April 08
From: England
Member No.: 52832



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!
Go to the top of the page
+Quote Post
fbuser
post Nov 8 2011, 19:07
Post #38





Group: Developer
Posts: 673
Joined: 26-September 07
Member No.: 47369



QUOTE (Black_Over_Bills_Mothers @ Nov 8 2011, 13: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.
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.

QUOTE (Black_Over_Bills_Mothers @ Nov 8 2011, 13:14) *
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

QUOTE (Black_Over_Bills_Mothers @ Nov 8 2011, 13:14) *
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.
Go to the top of the page
+Quote Post
Black_Over_Bills...
post Nov 9 2011, 00:22
Post #39





Group: Members
Posts: 124
Joined: 16-April 08
From: England
Member No.: 52832



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?
Go to the top of the page
+Quote Post
Zarty
post Nov 9 2011, 13:19
Post #40





Group: Members
Posts: 86
Joined: 30-September 11
Member No.: 94061



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
Go to the top of the page
+Quote Post
fbuser
post Nov 9 2011, 18:35
Post #41





Group: Developer
Posts: 673
Joined: 26-September 07
Member No.: 47369



QUOTE (Black_Over_Bills_Mothers @ Nov 9 2011, 00:22) *
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.

QUOTE (Zarty @ Nov 9 2011, 13:19) *
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.
Go to the top of the page
+Quote Post
Zarty
post Nov 9 2011, 22:27
Post #42





Group: Members
Posts: 86
Joined: 30-September 11
Member No.: 94061



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
Go to the top of the page
+Quote Post
Black_Over_Bills...
post Nov 19 2011, 08:47
Post #43





Group: Members
Posts: 124
Joined: 16-April 08
From: England
Member No.: 52832



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.
Go to the top of the page
+Quote Post
fbuser
post Nov 19 2011, 09:25
Post #44





Group: Developer
Posts: 673
Joined: 26-September 07
Member No.: 47369



QUOTE (Black_Over_Bills_Mothers @ Nov 19 2011, 08:47) *
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.
Go to the top of the page
+Quote Post
Black_Over_Bills...
post Nov 19 2011, 09:48
Post #45





Group: Members
Posts: 124
Joined: 16-April 08
From: England
Member No.: 52832



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.
Go to the top of the page
+Quote Post
Black_Over_Bills...
post Nov 19 2011, 15:30
Post #46





Group: Members
Posts: 124
Joined: 16-April 08
From: England
Member No.: 52832



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
Go to the top of the page
+Quote Post
Black_Over_Bills...
post Nov 20 2011, 09:05
Post #47





Group: Members
Posts: 124
Joined: 16-April 08
From: England
Member No.: 52832



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.
Go to the top of the page
+Quote Post
gob
post Dec 7 2011, 07:14
Post #48





Group: Members
Posts: 412
Joined: 13-July 03
From: bc.ca
Member No.: 7732



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

This post has been edited by gob: Dec 7 2011, 07:46
Go to the top of the page
+Quote Post
chiwou
post Dec 13 2011, 18:56
Post #49





Group: Members
Posts: 236
Joined: 5-November 04
From: Spitzbergen
Member No.: 17985



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
Go to the top of the page
+Quote Post
fbuser
post Dec 13 2011, 20:02
Post #50





Group: Developer
Posts: 673
Joined: 26-September 07
Member No.: 47369



Finally, some feedback to the feature requests above, although the requesters probably would like to get other answers.

QUOTE (Black_Over_Bills_Mothers @ Nov 19 2011, 15:30) *
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.

QUOTE (Black_Over_Bills_Mothers @ Nov 20 2011, 09:05) *
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.

QUOTE (gob @ Dec 7 2011, 07:14) *
. A standard foobar2000 context menu for node children with the sql_tree context menu available via a modifier.
Won't implement
QUOTE (gob @ Dec 7 2011, 07:14) *
. 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.
QUOTE (gob @ Dec 7 2011, 07:14) *
. Hide node icons
Not decided yet, see above.
QUOTE (gob @ Dec 7 2011, 07:14) *
. Hide horizontal/vertical scroll bar
Won't implement.
QUOTE (gob @ Dec 7 2011, 07:14) *
. Option to hide +/- for the root node
Won't implement. Be aware that, you can have multiple root nodes, not only one.
QUOTE (gob @ Dec 7 2011, 07:14) *
. 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.

QUOTE (gob @ Dec 7 2011, 07:14) *
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.


QUOTE (chiwou @ Dec 13 2011, 18:56) *
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'


Go to the top of the page
+Quote Post

5 Pages V  < 1 2 3 4 > » 
Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 21st April 2014 - 01:00