Skip to content

Instantly share code, notes, and snippets.

@lukasvermeer
Last active March 26, 2021 21:20
Show Gist options
  • Save lukasvermeer/d77bde9a0ae8cc247f56 to your computer and use it in GitHub Desktop.
Save lukasvermeer/d77bde9a0ae8cc247f56 to your computer and use it in GitHub Desktop.
# 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
@LumKitty
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment