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 140286 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_uie_sql_tree

Reply #225
That's not bringing up any albums that have different values for the added_timestamp tag as you can see here's an EP where there are two values on the same EP.
This is strange, but for me query is working for the released foo_sqlite version just with another tag, which is defined as title format expression. And actually I don't see any reason, why it shouldn't work for you. What is the result of
Code: [Select]
SELECT "%album artist%",album,"%added_timestamp%"
FROM MediaLibrary
WHERE album LIKE 'Baroque EP%'

Re: foo_uie_sql_tree

Reply #226
Code: [Select]
SELECT "%album artist%",album,"%added_timestamp%"
FROM MediaLibrary
WHERE album LIKE 'Baroque EP%'

That brings up that EP alright.

What does the % in EP% do?

I tried to rebuild the database from examples that are built in to see if that would help but no joy.


Re: foo_uie_sql_tree

Reply #228
Sorry, but I need to see the exact result from the SQLite console.
Thanks for letting me know that.

Here are the results for the two queries you've given. It appears there's no tag values returned for the added_timestamp tag.


Re: foo_uie_sql_tree

Reply #229
It appears there's no tag values returned for the added_timestamp tag.
And this is exactly the problem. You obviously missed this
Quote
but if you add %added_timestamp%, similar to the default column %album artist%, to the media library table in the preferences
So you need to go in the preferences to "Media Library -> SQLite viewer -> MediaLibrary table -> Add ..." and change the following fields:

Type: TFExpression
Name: %added_timestamp%
Title format expression: [%added_timestamp%]

Leave all other fields as they are.


Re: foo_uie_sql_tree

Reply #230
So you need to go in the preferences to "Media Library -> SQLite viewer -> MediaLibrary table -> Add ..." and change the following fields:

Thanks very much for your help. That worked.

I changed the query slightly to sort by album and tracknumber and only display albums that have a totaltracks greater than 1.

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct "%added_timestamp%")>1)
ORDER BY album, tracknumber

Re: foo_uie_sql_tree

Reply #231
Need help again ;)

I'm trying to modify that code you gave to get albums that have different www and album artist tags.

I've tried to change added_timestamp but that doesn't work.

For album artist tag.
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
      FROM MediaLibrary
      WHERE "totaltracks" > 1
      GROUP BY 1
      HAVING count(distinct "%album artist%")>1)
ORDER BY album, discnumber, tracknumber

For www tag.
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct "%www%")>0)
ORDER BY album, discnumber, tracknumber

Re: foo_uie_sql_tree

Reply #232
For album artist tag.
This cannot work as you identify an album uniquely by %album artist% and album. You will never have more than one distinct value for %album artist% in this case. If you are using an own tag to uniquely identify an album, e.g. "%album id%", you should use this instead of "%album artist%"||album. Otherwise you cannot get what you want.


For www tag.
It does not work, because this part of the query is wrong:
Code: [Select]
HAVING count(distinct "%www%")>0
It has to be
Code: [Select]
HAVING count(distinct "%www%")>1

Re: foo_uie_sql_tree

Reply #233
BTW, you don't need to define %www% as TFExpression, you can just define www as normal tag, which is slightly faster and the relevant part of the query would be this:
Code: [Select]
HAVING count(distinct www)>1
Double quotes are not necessary here as www doesn't contain any special characters.[/code]

Re: foo_uie_sql_tree

Reply #234
Thanks for your help.

Good job I format my album tag album [catolog #] so it will work fine. I just need to filter out albums that have the names Singles, SC and BOOTLEG.

Edit:
Got this album artist to work by using the % which I assume matches any character like you mentioned in a previous post.

Here's that attempt at that.
Code: [Select]
SELECT album,"album artist"
FROM MediaLibrary
WHERE album IN
      (SELECT album
       FROM MediaLibrary
       WHERE album NOT LIKE "%BOOTLEG%"
           AND  album NOT LIKE "%Singles%"
           AND  album NOT LIKE "%[SC]"
       GROUP BY 1
       HAVING count(distinct "album artist")>1)
ORDER BY album, discnumber, tracknumber

Then for Catalog # it doesn't work, even though it is in the MediaLibrary table.

I've removed one catalog # from one track in an album to test but nothing is showing.

This does work, kinda. I cleared the tag so the contents weren't the same string although it was empty and it didn't show.

Any way to modify this so that works when the tags are empty also?

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct "catalog #")>1)
ORDER BY album, discnumber, tracknumber


Re: foo_uie_sql_tree

Reply #236
Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
      FROM MediaLibrary
      WHERE "totaltracks" > 1
      GROUP BY 1
      HAVING count(distinct coalesce("catalog #",''))>1
ORDER BY album, discnumber, tracknumber

Returns an error:
Code: [Select]
Execution error:
SQLite Error: (1) near "tracknumber": syntax error

Error while preparing the first statement of:
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
       FROM MediaLibrary
       WHERE "totaltracks" > 1
       GROUP BY 1
       HAVING count(distinct coalesce("catalog #",''))>1
ORDER BY album, discnumber, tracknumber

Re: foo_uie_sql_tree

Reply #237
Returns an error:
Execution error:
SQLite Error: (1) near "tracknumber": syntax error
Yes, because you dropped the parenthesis after the having clause. Try this:

Code: [Select]
SELECT "%album artist%",album
FROM MediaLibrary
WHERE "%album artist%"||album IN
      (SELECT "%album artist%"||album
      FROM MediaLibrary
      WHERE "totaltracks" > 1
      GROUP BY 1
      HAVING count(distinct coalesce("catalog #",''))>1)
ORDER BY album, discnumber, tracknumber

Re: foo_uie_sql_tree

Reply #238
I'm just getting to trying your suggestion now, and I'm still having crashes. I've added MetaDb_Handle to the sql file (and removed the GROUP BY, since that was giving me trouble. I guess I still don't get when I want that and when I don't, but never mind that for now). It now looks like this:
Code: [Select]
DROP VIEW IF EXISTS tree_albumartist;
CREATE VIEW         tree_albumartist AS
SELECT   coalesce(
                  [album artist]
                , artist
                , '{no artist}'
                  )
      AS coalartist
       , album
       , MetaDb_Handle
    FROM MediaLibrary
ORDER BY coalartist
       , album
       , discnumber
       , tracknumber

The batch tab is unchanged:
Code: [Select]
SELECT eval(readfile('sql/tree_albumartist.sql'))

The query tab now looks like this:
Code: [Select]
SELECT coalartist, album FROM tree_albumartist

I can execute the batch as is in a separate tree node, remove it from the node with the query, and refresh the query just fine. It is just when that query and batch are in the same node that there is a crash on the refresh. In that case, I can click on the node and send every track to a playlist or to the SQL console, but a refresh crashes with either action selected.

Re: foo_uie_sql_tree

Reply #239
Yes, because you dropped the parenthesis after the having clause. Try this:
Silly me. Thanks so much for your help.

One last thing is it possible to query all tags.

What I was after was to check all tags for any unwanted whitespaces. If SQL can support regular expression then this would find any trailing, leading and additional spaces with a regular expression like so below.

Code: [Select]
^\s+|\s+$|\s+(?=\s)

My bad attempt at it:
Code: [Select]
SELECT *
FROM MediaLibrary
WHERE * LIKE "% " OR * LIKE " %" OR * LIKE "%  %'

Re: foo_uie_sql_tree

Reply #240
I can execute the batch as is in a separate tree node, remove it from the node with the query, and refresh the query just fine. It is just when that query and batch are in the same node that there is a crash on the refresh. In that case, I can click on the node and send every track to a playlist or to the SQL console, but a refresh crashes with either action selected.
Ok, I found the root cause of the problem and it is already fixed for the next version. However the same as mentioned already for foo_sqlite applies also here, that the release will need some time due to some substantial internal changes for foo_sqlite and also for foo_uie_sql_tree.

The problem in this case is that the batch part is returning a result due to the select. For the time being you can therefore change the batch like this
Code: [Select]
SELECT eval(readfile('sql/tree_albumartist.sql')) WHERE 1=2
as a workaround.

One last thing is it possible to query all tags.
No, you always need to explicitely specify the wanted columns in the where clause, e.g. for artist and title:
Code: [Select]
SELECT *
FROM MediaLibrary
WHERE 1 IN (regexp_search('^\s+|\s+$|\s{2,}',artist),regexp_search('^\s+|\s+$|\s{2,}',title))
You don't need the lookahead in the regular expression here and you could put the regular expression into an own table to not repeat the expression literally for each column.

Re: foo_uie_sql_tree

Reply #241
No, you always need to explicitely specify the wanted columns in the where clause, e.g. for artist and title:
Code: [Select]
SELECT *
FROM MediaLibrary
WHERE 1 IN (regexp_search('^\s+|\s+$|\s{2,}',artist),regexp_search('^\s+|\s+$|\s{2,}',title))
You don't need the lookahead in the regular expression here and you could put the regular expression into an own table to not repeat the expression literally for each column.
That's a pity, I thought there was a way to perhaps do a loop over all the columns in the db.

That was a regular expression for replacing whitespace and I forgot to remove the lookahead.

Thanks for the example.

Edit:

It shows up a track. I've checked both the artist and title for whitespaces.

Although it appears to be the à that is causing an issue in this.

See screenshot below of the track Voilà.

Changing the regular expression to the code below resolves it but it would be interesting to know why it's not working with \s and would be the better solution.

Code: [Select]
^ +| +$| {2,}

Re: foo_uie_sql_tree

Reply #242
but it would be interesting to know why it's not working with \s
Most likely because the regular expression implementation of the released version of foo_sqlite does not support unicode strings. This is also something, which is already implemented for the next version. As mentioned in the foo_sqlite thread it will need some time before it can be released and I hope it will be this year, but I cannot guarantee it.

Re: foo_uie_sql_tree

Reply #243
Most likely because the regular expression implementation of the released version of foo_sqlite does not support unicode strings. This is also something, which is already implemented for the next version. As mentioned in the foo_sqlite thread it will need some time before it can be released and I hope it will be this year, but I cannot guarantee it.
No worries. Thanks.

Re: foo_uie_sql_tree

Reply #244
I ran into a bug where I tried to delete a specific item in the SQL Tree, but it ended up deleting ALL items and folders entirely so that the SQL Tree window was left blank.  I wasn't able to get my configuration back by manually using the files from the configuration folder - I ended up reverting to an entire foobar2000 directory snapshot I had taken a couple days earlier.

I have 24 items or folders side-by-side at the root level.  Under each folder are then more items or folders.  I'm not aware of a way to multi-select things in the SQL Tree window, so I am still not sure how I managed to delete 24 parents and all their children in a single mouse click.

Re: foo_uie_sql_tree

Reply #245
I ran into a bug where I tried to delete a specific item in the SQL Tree, but it ended up deleting ALL items and folders entirely so that the SQL Tree window was left blank.
First of all, thanks for reporting the problem. So far there were only a very few reports with the same behavior after a version upgrade of foo_uie_sql_tree. What is actually happen here, is that the information for the insivible root node of the panel gets somehow lost. Unfortunately this behavior is not reproducible for me.

I wasn't able to get my configuration back by manually using the files from the configuration folder - I ended up reverting to an entire foobar2000 directory snapshot I had taken a couple days earlier.
This should normally not be necessary, but besides the configuration files for foo_uie_sql_tree (foo_uie_sql_tree.dll.cfg and foo_uie_sql_tree.db) you also need to restore either foo_ui_columns.dll.cfg or foo_ui_std.dll.cfg depending on the user interface you are using.

Re: foo_uie_sql_tree

Reply #246
Quote
you also need to restore either foo_ui_columns.dll.cfg or foo_ui_std.dll.cfg depending on the user interface you are using.

This is probably the missing piece I did not do.

I am using SQL Tree inside foo_popup_panels.  Therefore, I can close (hide) the window and reopen it without database activity.  (I noticed that if I use SQL Tree from the Library menu of foobar2000, there is database activity each time.)

Re: foo_uie_sql_tree

Reply #247
Complications gather. As above, I've been running scripts with code like this in the batch tab with Execute SQL as the action:

Code: [Select]
SELECT eval(readfile('sql/file.sql'))

Now I'd like to run several such files but have not found the way to do so. Here are some alternatives that haven't worked, unless I've screwed something up elsewhere.

Code: [Select]
SELECT eval(readfile('sql/file.sql'));
SELECT eval(readfile('sql/file_2.sql'));

Code: [Select]
SELECT eval(
            readfile('sql/file.sql')
         || readfile('sql/file_2.sql')
            );

Code: [Select]
SELECT eval(
            quote(readfile('sql/file.sql'))
         || quote(readfile('sql/file_2.sql'))
            );

Is there a way to do something like this? Is this maybe something addressed in the next versions of the components, when they come? Am I better off combining my files elsewhere and running one big file here?

Re: foo_uie_sql_tree

Reply #248
Is there a way to do something like this?
As you didn't mention, what your actual problem is and you also didn't provide the full configuration for the relevant tree node, I can only guess that you experience crashes. In this case it's probably the same problem as you reported already here.  The same workaround is the same:

Code: [Select]
SELECT eval(readfile('sql/file.sql')) WHERE 1=2;
SELECT eval(readfile('sql/file_2.sql')) WHERE 1=2;

Another possibility could be to clear the query part completely, if you only want to use "Execute SQL" as action. At least for me this worked without crash.

Is this maybe something addressed in the next versions of the components, when they come?
Yes it is, see link above. However I still can't predict, when it will be. I'm making progresses with the substantial, mostly internal, changes I'm currently working on, but not as fast as I'd like to. But I'm quite confident, that it will be in the next 3-3,5 months, if nothing unexpected happens, which will block me from working on the changes.

Am I better off combining my files elsewhere and running one big file here?
No, this should not be necessary.

Re: foo_uie_sql_tree

Reply #249
No, this isn't a crashing issue. In this instance, there isn't anything in the Query tab. Essentially, one of the scripts is run to update data in a table, and the second then rewrites a regex in a regexes table that queries the first table for its values. I have tables that contain prefixes like "The", "Les" and the like or delimiter-type words like "And" or "With", etc., and then the regex definitions group_concat() those columns into regex option groups. In some cases, further scripts may update further regexes that also query the same table. At present, each such script has its own tree node to execute it; I'm trying to combine existing scripts to run sequentially so that updating the table will refresh all dependent regexes.

What I'm seeing is that the expected change in the regex at the end of the process is not occurring. The old value is still present. Running the script from its own node will make the change, but running it second in a multi-script node does not. I believe I did try the WHERE 1 = 2 workaround as well as the attempts I mentioned, but that didn't change the result in this instance.