IPB

Welcome Guest ( Log In | Register )

5 Pages V  « < 2 3 4 5 >  
Reply to this topicStart new topic
foo_uie_sql_tree
fbuser
post Jan 21 2012, 00:05
Post #76





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



QUOTE (ainz @ Jan 20 2012, 23:30) *
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.
Go to the top of the page
+Quote Post
ainz
post Jan 21 2012, 02:06
Post #77





Group: Members
Posts: 17
Joined: 28-October 11
Member No.: 94757



QUOTE (fbuser @ Jan 20 2012, 23:05) *
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...

This post has been edited by ainz: Jan 21 2012, 02:10
Go to the top of the page
+Quote Post
camperdave
post Mar 27 2012, 18:52
Post #78





Group: Members
Posts: 3
Joined: 7-May 11
Member No.: 90440



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.
Go to the top of the page
+Quote Post
fbuser
post Mar 27 2012, 19:35
Post #79





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



Look here and the following.
Go to the top of the page
+Quote Post
camperdave
post Mar 27 2012, 20:09
Post #80





Group: Members
Posts: 3
Joined: 7-May 11
Member No.: 90440



QUOTE (fbuser @ Mar 27 2012, 12:35) *
Look here and the following.


Thanks, removing and re-adding the panel (not the component) worked just fine!
Go to the top of the page
+Quote Post
r0k
post May 4 2012, 17:05
Post #81





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



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
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 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.
Go to the top of the page
+Quote Post
fbuser
post May 4 2012, 17:52
Post #82





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



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
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.
Go to the top of the page
+Quote Post
r0k
post May 4 2012, 21:04
Post #83





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



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

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 crying.gif . 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?
Go to the top of the page
+Quote Post
r0k
post May 13 2012, 16:03
Post #84





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



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 wink.gif
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?
Go to the top of the page
+Quote Post
fbuser
post May 13 2012, 21:43
Post #85





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



QUOTE (r0k @ May 4 2012, 21:04) *
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.

QUOTE (r0k @ May 13 2012, 16:03) *
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.

QUOTE (r0k @ May 13 2012, 16:03) *
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.

QUOTE (r0k @ May 13 2012, 16:03) *
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.

QUOTE (r0k @ May 13 2012, 16:03) *
Full custom tables with different columns would be great
As mentioned above, that's what views are for.
Go to the top of the page
+Quote Post
r0k
post May 14 2012, 08:42
Post #86





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



QUOTE (fbuser @ May 13 2012, 22:43) *
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.
Go to the top of the page
+Quote Post
r0k
post May 18 2012, 16:30
Post #87





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



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.
Go to the top of the page
+Quote Post
D.Sync
post May 23 2012, 09:49
Post #88





Group: Members
Posts: 177
Joined: 22-March 07
Member No.: 41742



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

Go to the top of the page
+Quote Post
r0k
post May 23 2012, 14:18
Post #89





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



I have changed my totaltracks to be the grand total for multi-disc albums. Easy and ugly wink.gif
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
Go to the top of the page
+Quote Post
fbuser
post May 23 2012, 17:37
Post #90





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



QUOTE (D.Sync @ May 23 2012, 09:49) *
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 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


Go to the top of the page
+Quote Post
D.Sync
post May 24 2012, 06:19
Post #91





Group: Members
Posts: 177
Joined: 22-March 07
Member No.: 41742



QUOTE (fbuser @ May 23 2012, 11:37) *
QUOTE (D.Sync @ May 23 2012, 09:49) *
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 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.
Go to the top of the page
+Quote Post
fbuser
post Jun 3 2012, 18:41
Post #92





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



QUOTE (r0k @ May 18 2012, 16:30) *
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.

This post has been edited by fbuser: Jun 3 2012, 18:42
Go to the top of the page
+Quote Post
r0k
post Jun 3 2012, 20:58
Post #93





Group: Members
Posts: 74
Joined: 8-September 11
Member No.: 93574



Great. I have updated it and so far i can switch layouts without loosing my queries cool.gif
Does the update to SQLite 3.7.12.1 affects existing queries?
Thanks for the update.
Go to the top of the page
+Quote Post
fbuser
post Jun 3 2012, 21:14
Post #94





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



QUOTE (r0k @ Jun 3 2012, 20:58) *
Does the update to SQLite 3.7.12.1 affects existing queries?
No, you can find a change log for SQLite here.
Go to the top of the page
+Quote Post
akispavlopoulos
post Jun 29 2012, 17:37
Post #95





Group: Members
Posts: 21
Joined: 3-August 08
Member No.: 56649



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!
Go to the top of the page
+Quote Post
m00zikD00d
post Sep 14 2012, 00:28
Post #96





Group: Members
Posts: 12
Joined: 7-April 08
Member No.: 52624



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
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
WHERE sub_genre = '%progressive%'


there is no output at all. I have also tried

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

This post has been edited by m00zikD00d: Sep 14 2012, 00:37
Go to the top of the page
+Quote Post
mobyduck
post Sep 14 2012, 07:52
Post #97





Group: Members
Posts: 573
Joined: 24-April 03
From: Italy
Member No.: 6159



Can't you simply do
CODE

WHERE content_group_mv LIKE '%progressive%'
OR comment LIKE '%progressive%'
?

HTH.

Alessandro
Go to the top of the page
+Quote Post
fbuser
post Sep 14 2012, 16:40
Post #98





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



QUOTE (m00zikD00d @ Sep 14 2012, 00:28) *
CODE
WHERE sub_genre = '%progressive%'
This is obvious. You cannot use wildcards here. The % characters are taken literally for a straight comparison.

QUOTE (m00zikD00d @ Sep 14 2012, 00:28) *
there is no output at all. I have also tried
CODE
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.
Go to the top of the page
+Quote Post
neothe0ne
post Oct 7 2012, 08:53
Post #99





Group: Members
Posts: 295
Joined: 25-September 05
Member No.: 24684



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 path
FROM MediaLibrary
WHERE CAST(ratingcd AS INT) IS 0
ORDER BY path


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


BUT! 2504 items:
CODE
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...

This post has been edited by neothe0ne: Oct 7 2012, 09:05
Go to the top of the page
+Quote Post
fbuser
post Oct 7 2012, 09:46
Post #100





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



QUOTE (neothe0ne @ Oct 7 2012, 08:53) *
1) What's the difference between "Query" and "Batch"?
Query Batch

QUOTE (neothe0ne @ Oct 7 2012, 08:53) *
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.

QUOTE (neothe0ne @ Oct 7 2012, 08:53) *
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.

QUOTE (neothe0ne @ Oct 7 2012, 08:53) *
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.
Go to the top of the page
+Quote Post

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

 



RSS Lo-Fi Version Time is now: 19th September 2014 - 03:24