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):
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);
Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 145113 times)
previous topic - next topic