Or, Use SQL Squirrel and JDBC to dedupe MusikCube playlists
I use MusikCube a lot. That said, I sometimes share files from various machines that have duplicate files, and rather than hand-edit the playlists after each synchronization to eliminate the duplicate albums, I want an automated way. Since MusikCube runs on SQLite, why not do it in SQL?
First download the SQLiteJDBC JDBC driver and install to the SQL Squirrel lib folder. Restart the client and add a new Driver to the list using name=SQLite, example URL = jdbc:sqlite:test.db, classname= org.sqlite.JDBC and Website URL = http://www.zentus.com/sqlitejdbc/
First problem – how to access the database. Doc is sketchy, but based on some investigation of the org.sqlite.JDBC.java code I found that it uses everything following the “jdbc:sqlite:” prefix as a new File() argument (unless nothing is found, than an in-memory DB is assumed). So I can access the db using the JDBC URL: jdbc:sqlite:C:/Documents and Settings/username/.musikproject/musik_u.db. I love source code.
Note: I do not put any work into playlists. I am a big fan of letting the metadata do the work. I am comfortable with nuking the DB a starting over, if necessary. If you have lots of valuable annotations, you are editing in the wrong place. So if my tests blow up the DB, so what.
Then from a SQL Server site article, How to remove duplicate rows…, I can compile this query:
SELECT title, album, count(*)
FROM songs
GROUP BY title, album
HAVING count(*) > 1
I easily get 100 rows with 2 hits each.
Alternate form of query (from here)
select s1.filename, s1.songid, s2.songid, s1.title, CASE WHEN substr(s1.filename,1)=’f’ OR substr(s2.filename,1)=’f’ THEN 1 ELSE 2 END as fred
from songs s1, songs s2
where s1.songid < s2.songid and s1.title = s2.title AND s1.artist = s2.artist AND s1.album = s2.album AND s1.artist = 'Aerosmith'
and also
select s1.filename,
( CASE
— my box has top priority
WHEN (substr(s1.filename, 4, length(‘mp3’)) = ‘mp3’ ) THEN 1
— handle \\m51098 machine names
WHEN (substr(s1.filename, 3, length(‘m51098’)) = ‘m51098’ )THEN 2
WHEN (substr(s1.filename, 3, length(‘m51165’)) = ‘m51165’ )THEN 3
WHEN (substr(s1.filename, 3, length(‘m51134’)) = ‘m51134’ )THEN 4
ELSE 10 END ) AS priority
from songs s1
where 1 < (select count(*) from songs s2 where s1.title = s2.title AND s1.artist = s2.artist AND s1.album = s2.album AND s1.artist = 'Aerosmith') -- limit to Aerosmith for speed AND s1.artist = 'Aerosmith'
More to come…
Tools:
SQurrel SQL JDBC client
SQLite browser
SQLite core function reference
SQLite page
SQLiteJDBC driver