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 137897 times) previous topic - next topic
0 Members and 2 Guests are viewing this topic.

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

Reply #78
Look here and the following.


foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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


foo_uie_sql_tree

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

foo_uie_sql_tree

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



foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

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


foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

Reply #96
Can't you simply do
Code: [Select]
WHERE content_group_mv LIKE '%progressive%'
  OR comment LIKE '%progressive%'
?

HTH.

Alessandro

foo_uie_sql_tree

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

foo_uie_sql_tree

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

foo_uie_sql_tree

Reply #99
1)  What's the difference between "Query" and "Batch"?
Query 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?
As count() is an aggregrate function, this is not possible. See here.

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.

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.