Duplicate entries when importing database from Clementine

shadowfire

Re: [Clementine-->Strawberry migration](fixes to SQL)

Thanks, this mostly did the trick. I had to make a minor modification - replace double quotes ("") with single quotes ('') - because it gave me these errors:
no such column: "" - should this be a string literal in single-quotes?

Unfortunately the duplicate songs fix was not persistent; initially things seemed okay but when I selected the 'All tracks' playlist, then 'Smart playlists' -> 'All tracks' -> 'Replace current playlist' my total song count increased. Clementine originally reported 10,061 tracks, (which Strawberry reported the same after the import) but after replacing the tracks count increased to 13.669 tracks.

I can't figure out why some songs are duplicated while others aren't. My mp3s are spread out over several directories (for legacy reasons, I used to divide them up into chunks that could fit on a portable mp3 player or usb stick).
The directory table seems to be the same (without duplicate entries) after import. Also the 'url' field seems to be identically on the duplicates.

II'm scratching my head here, does someone know how Strawberry determine the unique id of a song?

jonas

@shadowfire
There is a database row ID (ROWID) which is unique to each song in the songs table.
The ROWID from the collection songs table is linked to collection_id in playlist_items.

shadowfire

Update: after studying both database tables I think I may have found what is causing this issue; apparently my clementine.db table 'subdirectories' has some sequential numbers missing in the 'directory_id' column. After migrating and refreshing the collection to Strawberry it seems to add duplicate entries for those missing directory_id's:

Before:

directory_id  path
3             /home/music/mp3-1
4             /home/music/mp3-2
5             /home/music/mp3-3
6             /home/music/mp3-4
7             /home/music/mp3-5
...

After:

directory_id  path
1             /home/music/mp3-1
2             /home/music/mp3-2
3             /home/music/mp3-1
4             /home/music/mp3-2
5             /home/music/mp3-3
6             /home/music/mp3-4
7             /home/music/mp3-5
...

Now all I need is to figure out an easy way on how to fix this... (maybe add in some dummy entries for 1 and 2?)

shadowfire

Okay, I figured out a workaround; this adds a bunch of dummy entries to the subdirectories table where directory_id is missing (I inserted this in the sqllite migration script from the original post):

INSERT INTO strawberry.subdirectories(directory_id,path,mtime) SELECT 1, '/path/to/nowhere', '' WHERE NOT EXISTS(SELECT 1 FROM strawberry.subdirectories WHERE directory_id = 1);
INSERT INTO strawberry.subdirectories(directory_id,path,mtime) SELECT 2, '/path/to/nowhere', '' WHERE NOT EXISTS(SELECT 1 FROM strawberry.subdirectories WHERE directory_id = 2);
INSERT INTO strawberry.subdirectories(directory_id,path,mtime) SELECT 3, '/path/to/nowhere', '' WHERE NOT EXISTS(SELECT 1 FROM strawberry.subdirectories WHERE directory_id = 3);
INSERT INTO strawberry.subdirectories(directory_id,path,mtime) SELECT 4, '/path/to/nowhere', '' WHERE NOT EXISTS(SELECT 1 FROM strawberry.subdirectories WHERE directory_id = 4);
INSERT INTO strawberry.subdirectories(directory_id,path,mtime) SELECT 5, '/path/to/nowhere', '' WHERE NOT EXISTS(SELECT 1 FROM strawberry.subdirectories WHERE directory_id = 5);
...

This seems to have fixed the issue.

shadowfire

Unfortunately this wasn't the right fix. After re-doing the procedure (with a full rescan in Clementine and a reset database in Strawberry) Strawberry duplicated every entry in my list.

Note that I have 36 directories in my library, the reason why this issue matters to me.

After pondering for a while I decided to compare table entries for a specific directory path from a song and then I noticed this:

clementine.db:
  table:         column(s):
  songs                    directory: 45
  directories    rowid: 36
  subdirectories rowid:  7 directory: 45

strawberry.db:
  table:         column(s):
  songs                    directory_id: 45
                           directory_id: 36 (duplicate that appeared after rescan)
  directories    rowid: 36
  subdirectories rowid: 36 directory_id: 45
                 rowid: 72 directory_id: 36 (duplicate that appeared after rescan)

I think the issue is that Clementine seems to associate songs->directory with subdirectories->directory while Strawberry associates directory_id with directories->rowid.

Unfortunately my sql skills are very rusty so I didn't want to spend even more time trying to come up with a fix. Instead I opted for a different procedure:

Removed strawberry.db and Strawberry cache directory, start Strawberry and exit to reset the database.
Migrated the directories table (using sql because of efficiency.)
Copied over album covers.
Started Strawberry again, did a full rescan, then exit.
Used this script by Rolf to import song statistics and ratings:
https://forum.strawberrymusicplayer.org/topic/113/import-ratings-from-other-players-esp-clementine
Started Strawberry again and did full rescan again.

After creating a playlist with the 'All tracks' smart playlist the total song count was almost the same. A minor discrepancy was caused by a corrupt file name (which broke the scan of one directory) and a few corrupted media files. But that's easy to fix.

i(Btw. I didn't care about migrating playlists since I've got only a hand full of smart playlists which I can easily recreate manually.)