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.