Migrate Kodi Watched Status

# This might break all the things. Be careful.
#
# So I got a new Kodi media thing.
# And I wanted to create a new (clear) library.
# But I also wanted to keep watched status for things I'd watched.
# What follows are manual steps I took.
#

# dump the data on the old server
sqlite3 ~/.kodi/userdata/Database/MyVideos93.db

-- this part I forgot to log properly, so writing this from memory ...
.headers on
.mode csv
.output kodi_watched.csv
select strFileName,lastPlayed,playCount from files where playCount > 0;

.exit

# make sure we have a backup on the new server (aka bruce).
ssh bruce
cp .kodi/userdata/Database/MyVideos90.db .kodi/userdata/Database/MyVideos90.db.backup
exit

# retrieve the file
scp bruce:.kodi/userdata/Database/MyVideos90.db .

sqlite3 MyVideos90.db 

-- need temp tables to get the data
create table temp_watched(strFileName,lastPlayed,playCount);
.mode csv
.import kodi_watched.csv temp_watched

-- sanity checks
select count(*) from files where playCount is not null;
select count(*) from temp_watched where playCount is not null;

-- update rows when they exist in new database
update files set 
    playCount = (select playCount from temp_watched where strFileName = files.strFileName),
    lastPlayed = (select lastPlayed from temp_watched where strFileName = files.strFileName);

-- sanity check
select count(*) from files where playCount is not null;

-- drop temp tables
drop table temp_watched;

.exit

# upload the file to bruce
scp MyVideos90.db bruce:.kodi/userdata/Database/MyVideos90.db

This works well, but it doesn't update the watched counts in the TV series list, causing Kodi to show incorrect information on the list of all TV shows screen. There's a simple fix though.

1. Install the WatchedList addon, and run it. This will backup the watched markers.
2. Quit Kodi, delete the database you modified and restore the backup you took.
3. Run the WatchedList addon again. It will restore your backup and handle the rest of the Kodi database.
4. (Optional) Keep that addon installed, so you don't have to mess with this again!

As a bonus, since you're restoring a DB that hasn't been hacked by hand, hopefully less things will break.