Find and delete duplicates from the collection database

From Strawberry Music Player Wiki
Revision as of 23:31, 2 January 2024 by Jonas (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

See: https://wiki.strawberrymusicplayer.org/wiki/Accessing_the_database on how to access the database.

Exit Strawberry first and backup the file "strawberry.db" in case something goes wrong.

You can also use Tools / Console in Strawberry, but it won't show errors, so it's safer to use the sqlite3 command directly.

Find duplicate songs

   SELECT s.rowid, s.url FROM songs s INNER JOIN (SELECT url, COUNT(*) FROM songs GROUP BY url HAVING count(*) > 1) d ON s.url = d.url ORDER BY s.url;

Delete duplicate songs

   DELETE FROM songs WHERE rowid NOT IN (SELECT min(rowid) FROM songs GROUP BY url);

Recreate FTS tables

This must be done, otherwise the table used for the collection search will still have duplicates.

   DELETE FROM songs_fts;
   INSERT INTO songs_fts (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment) SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment FROM songs;