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: foo_uie_sql_tree (Read 71073 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
quote author=stevehero link=msg=1001292 date=1627720170] It appears there's no tag values returned for the added_timestamp tag.[/quote]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.

 
SimplePortal 1.0.0 RC1 © 2008-2021