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

Re: foo_uie_sql_tree

Reply #175
I got duplicates, when the track is only named "Allegro", but different piece. Can you add same file size to query? This will find real duplicates.

Re: foo_uie_sql_tree

Reply #176
I got duplicates, when the track is only named "Allegro", but different piece. Can you add same file size to query? This will find real duplicates.
This is problematic because as more conditions you add as as more you get the chance to miss some duplicates. Furthermore file sizes differ for different formats. There is no 100% solution for this problem. Besides a similar query like the one, which I already posted I use a few more queries, which are more specific and in addition to that I use an own tag, which let me mark tracks as checked, if they are considered as duplicates even, if they are actually no duplicates. But this is taylored to my very own tagging schema and won't help here.

But assuming that duplicate tracks don't differ in length more than 15 seconds (although sometimes tracks are just cut earlier) the batch part could look like the following (the query part remains the same, but note: I slightly fixed my original post to properly sort for length and removed an useless index):

Code: [Select]
DROP TABLE IF EXISTS tmp_artist_title;
CREATE TABLE tmp_artist_title AS
SELECT strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title"
FROM MediaLibrary
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
GROUP BY 1,2;

CREATE UNIQUE INDEX tmp_artist_title_pk ON tmp_artist_title(title,artist);

DROP TABLE IF EXISTS tmp_artist_title_path;
CREATE TABLE tmp_artist_title_path AS
SELECT path,
       subsong,
       strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "artist",
       strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789') AS "title",
       length_seconds
FROM MediaLibrary a
WHERE title IS NOT NULL
  AND strfilter(lower(unaccent(title)),'abcdefghijklmnopqrstuvwxyz0123456789')<>''
  AND EXISTS (SELECT NULL
              FROM tmp_artist_title
              WHERE artist=strfilter(lower(unaccent(a.artist)),'abcdefghijklmnopqrstuvwxyz0123456789')
                AND title=strfilter(lower(unaccent(a.title)),'abcdefghijklmnopqrstuvwxyz0123456789'))
ORDER BY 3,4,5;

DROP TABLE IF EXISTS tmp_paths;
CREATE TABLE tmp_paths AS
WITH Paths AS (
SELECT a.path path1,
       a.subsong subsong1,
       b.path path2,
       b.subsong subsong2
FROM tmp_artist_title_path a JOIN tmp_artist_title_path b ON a.rowid=b.rowid+1
WHERE a.artist=b.artist
  AND a.title=b.title
  AND abs(a.length_seconds-b.length_seconds)<15)
SELECT path1 path,
       subsong1 subsong
FROM Paths
UNION
SELECT path2, subsong2
FROM Paths;

CREATE UNIQUE INDEX tmp_paths_pk ON tmp_paths(path, subsong);

Re: foo_uie_sql_tree

Reply #177
Thousand thanks for your hard work. I reduced from 15sec to 1sec. This reduced the amount of duplicates from 4000 to 1300. Thats much better. Live versions of the same song are not duplicates.

I modified query a little bit (+Album column)
Code: [Select]
SELECT upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)) Letter,
       proper(artist) Artist, album Album
FROM MediaLibrary a
WHERE EXISTS (SELECT NULL
              FROM tmp_paths
              WHERE path=a.path
                AND subsong=a.subsong)
ORDER BY upper(substr(strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789'),1,1)),
         strfilter(lower(unaccent(artist)),'abcdefghijklmnopqrstuvwxyz0123456789') COLLATE NaturalNoCase,
         length_seconds,
         title

Re: foo_uie_sql_tree

Reply #178
I reduced from 15sec to 1sec. This reduced the amount of duplicates from 4000 to 1300.
But you will most likely miss duplicates with this. But this is, of course, your decision.
Live versions of the same song are not duplicates.
Because of this, I exclude in some of my queries live tracks, based on my own tagging schema.

I modified query a little bit (+Album column)
This is will lead to surprising results in case you have two albums with one than more pair of duplicates. You will most likely get branches in the tree like this:
Artist
  Album1
  Album2
  Album1
  Album2

It's because the sorting for album is missing in the query. However, if you add this to the query to get the desired result (only one node per album), you will get the following order for the tracks of these two albums in the playlist:
Album1, Title1
Album1, Title2
Album2, Title1
Album2, Title2

So duplicate tracks aren't always next to each other anymore in the playlist.

Apart from this, just look at my original post for the query. Like mentioned, it's slightly adjusted for proper length sorting.

Re: foo_uie_sql_tree

Reply #179
I'm still getting an error with your updated code HERE.

For query it's:



And the batch tab when I've pressed Validate:


Re: foo_uie_sql_tree

Reply #180
For query it's:
Ah, yes. You need to execute the batch at least once. Otherwise you won't have the table you are selecting from.

And the batch tab when I've pressed Validate:
Of course, and this will ever be the case. It has been now answered several times in the last 24 hours, also to one of your posts: Validate doesn't work with DDL statements. It's simply not possible without executing the code in its whole, but this is not different from actually executing the code.

Re: foo_uie_sql_tree

Reply #181
@fbuser‍, sorry I missed that. Works great. Do you have a donation link?

One more q. The built-in example for finding incomplete albums.

How can I change it so that it adds the items to a new playlist as opposed to the SQL console.

When I change the option under action to do this send to target playlist, I get this error.

Code: [Select]
Invalid node query

Invalid query statement:

What should I enter in the Query tab for this to work?

Re: foo_uie_sql_tree

Reply #182
"send to target playlist" works here

Re: foo_uie_sql_tree

Reply #183
@fbuser‍, sorry I missed that. Works great.
No problem.
Do you have a donation link?
Thanks for asking, but no.
What should I enter in the Query tab for this to work?
Code: [Select]
WITH Albums AS (
  SELECT album,"%album artist%"
  FROM MediaLibrary
  GROUP BY 1,2
  HAVING count(*)<>max(CAST(totaltracks AS INTEGER))
)
SELECT a."%album artist%" "Album Artist",
       '['||coalesce(a.date,'')||']  '||a.album "Date - Album",
       title "Title"
FROM MediaLibrary a JOIN Albums b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
         a.date,
         a.album,
         discnumber,
         tracknumber;

Note: This will fail for albums of an artist who has more than one album with the same name. In this case other, user specific criteria needs to be used to uniquely identify an album.


Re: foo_uie_sql_tree

Reply #185
@fbuser

Thanks, I get this error now. I executed the batch beforehand just in case that was needed first.

Code: [Select]
Invalid node query

SQLite Error: (1) no such table: Albums

Error while preparing the first statement of:
SELECT MetaDb_Handle
FROM MediaLibrary a JOIN Albums b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
         a.date,
         a.album,
         discnumber,
         tracknumber

Re: foo_uie_sql_tree

Reply #186
Sorry, I misunderstood.

Re: foo_uie_sql_tree

Reply #187
@fbuser‍Thanks, I get this error now. I executed the batch beforehand just in case that was needed first.
No, there is no batch in this case. Just put the code into the query tab. That was what you asked for. ;)

Re: foo_uie_sql_tree

Reply #188
But "send to target playlist" doesn't work.


Re: foo_uie_sql_tree

Reply #190
But "send to target playlist" doesn't work.
For the code, that I provided, it definitely does.

I still get the same error as my example above. Sorry to be a pain.
In this case you are probably still using your example, which is wrong. Just put my code above into the query tab and leave the batch tab empty.

Re: foo_uie_sql_tree

Reply #191
In this case you are probably still using your example, which is wrong. Just put my code above into the query tab and leave the batch tab empty.
 
I'm definitely only using the Query tab and using your code in this post for it.

So basically,
1. Create a new query.
2. Paste your code to the query tab.
3. Send to target playlist and give it a name.
4. Error as I've mentioned above (not when pressing okay, but when I click on the query made).

Re: foo_uie_sql_tree

Reply #192
In this case you are probably still using your example, which is wrong. Just put my code above into the query tab and leave the batch tab empty.

I'm definitely only using the Query tab and using your code in this post for it.

So basically,
1. Create a new query.
2. Paste your code to the query tab.
3. Send to target playlist and give it a name.
4. Error as I've mentioned above (not when pressing okay, but when I click on the query made).

Oops, my fault. I'm using work-in-progress versions of foo_sqlite and foo_uie_sql_tree, which already support using CTEs (WITH clause) in the query tab of foo_uie_sql_tree. Use this code instead, it's doing the same without a CTE:

Code: [Select]
SELECT a."%album artist%" "Album Artist",
       '['||coalesce(a.date,'')||']  '||a.album "Date - Album",
       title "Title"
FROM MediaLibrary a JOIN (SELECT album,"%album artist%"
                          FROM MediaLibrary
                          GROUP BY 1,2
                          HAVING count(*)<>max(CAST(totaltracks AS INTEGER))) b ON a.album=b.album AND a."%album artist%"=b."%album artist%"
ORDER BY a."%album artist%" COLLATE NaturalNoCase,
         a.date,
         a.album,
         discnumber,
         tracknumber

Re: foo_uie_sql_tree

Reply #193
@fbuser‍ thanks very much. Do you have a method for a donation? This plugin is so powerful and credit to you for doing it.

Re: foo_uie_sql_tree

Reply #194
Sorry if this has been addressed in the thread. I gave it a quick look and couldn't find it.

What it issue is.
1. Added SQL Tree to a panel in columns UI.
2. Queries don't match that of the Library>SQL Tree. Only the <Examples> folder shows up.
3. Also if I add any in the panel Library>SQL Tree both don't update and vice versa. Even after restarting fb2k.
4. See screenshot of the panel and the menu item open.

I can drag and drop between the menu item and the panel but is there any way to sync them or is this a limitation?


Re: foo_uie_sql_tree

Reply #195
You can do Copy & Paste.


Re: foo_uie_sql_tree

Reply #197
What it issue is.
1. Added SQL Tree to a panel in columns UI.
2. Queries don't match that of the Library>SQL Tree. Only the <Examples> folder shows up.
3. Also if I add any in the panel Library>SQL Tree both don't update and vice versa. Even after restarting fb2k.
4. See screenshot of the panel and the menu item open.
Actually, not an issue, this works as designed.

I can drag and drop between the menu item and the panel but is there any way to sync them or is this a limitation?
Not a limitation, more a wrong expectation. ;D All trees are independent from each other and you could also have multiple panels in ColumnsUI (or DUI) each with an own tree. The "menu item" is actually a DUI standalone panel.

if there's two separate databases.
Not two databases but as mentioned above this wouldn't be the reason for the described behavior.

Re: foo_uie_sql_tree

Reply #198
@fbuser‍, thanks for the explanation. The more I think about it, the more it makes sense. Just got a little fright when my panel displayed none of my queries.

Re: foo_uie_sql_tree

Reply #199
A cosmetic request for the panel. An edge style would be nice to have. Thanks.


 
SimplePortal 1.0.0 RC1 © 2008-2021