How to merge two users' listen statistics in sqlite3
from GravitySpoiled@lemmy.ml to navidrome@discuss.tchncs.de on 25 Feb 2024 09:20
https://lemmy.ml/post/12382966

I want to share this as I wanted this for a long time. Finally, I sat down and wrote it. It merges the listens and star ratings of two accounts USER_ID_OLD and USER_ID_NEW. If you want to use it yourself, you have to replace those values with your values. The upper part is descriptive to explore the database and find the fields.

Use at your own risk. backup first. cp navidrome.db navidrome.db.bu. Found mistakes? Please report. Read all lines prior to executing.

# open database
sqlite3 navidrome.db

# show content
.tables

# show users
SELECT * FROM user;

# delete all playlists
DELETE FROM playlist;

PRAGMA table_info(annotation);

SELECT user_id, item_id, play_count FROM annotation ORDER BY play_count DESC LIMIT 10;

UPDATE annotation AS a
SET play_count = (
    SELECT SUM(play_count) 
    FROM annotation AS b 
    WHERE b.item_id = a.item_id
);

UPDATE annotation AS a
SET rating = (
    SELECT MAX(rating)
    FROM annotation AS b 
    WHERE b.item_id = a.item_id
);

UPDATE annotation AS a
SET starred = (
    SELECT MAX(starred)
    FROM annotation AS b 
    WHERE b.item_id = a.item_id
);

UPDATE annotation AS a
SET play_date = (
    SELECT MAX(play_date)
    FROM annotation AS b 
    WHERE b.item_id = a.item_id
);


DELETE FROM annotation
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM annotation
    GROUP BY item_id
);

UPDATE annotation SET user_id='USER_ID_OLD' WHERE user_id='USER_ID_NEW';

SELECT user_id, item_id, play_count FROM annotation ORDER BY play_count DESC LIMIT 10;

.quit

Edit: reading it again, it might only work correctly if there are two users.

#navidrome