Kodi MyVideos sqlite db cleanup script

video_db_cleanup.py

#!/usr/bin/env python

"""
A simple script to clean kodi's MyVideos sqlite db from obsolete junk.

It will remove all references to non-existing video files and paths.
Records referencing plugins (i.e. YouTube) are ignored.

Bookmarks, settings and streamdetails tables are touched as well, just in case.

DB vacuum is performed in the end.

Tested on Kodi 21 "Omega".

NB: Change DRY_RUN variable to False for changes to commit.
"""

import os
import sqlite3


DRY_RUN = True
DB_PATH = "/storage/.kodi/userdata/Database/MyVideos131.db"


def dir_exist(path):
    if path is None:
        return False

    if path.startswith("plugin:"):
        return True

    return os.path.isdir(path)


def file_exist(path):
    if path is None:
        return False

    if path.startswith("plugin:"):
        return True

    return os.path.isfile(path)

  def main():
    connection = sqlite3.connect(DB_PATH)
    cursor = connection.cursor()

    # Discover absent paths
    paths_to_delete = []
    for row in cursor.execute("SELECT idPath, strPath FROM path ORDER BY idPath DESC"):
        if not dir_exist(row[1]):
            paths_to_delete.append((row[0],))

    # Delete absent paths
    cursor.executemany("DELETE FROM path WHERE idPath = ?", tuple(paths_to_delete))
    print(f"Deleted {len(paths_to_delete)} absent paths")

    # Delete files referencing absent paths
    result = cursor.execute(
        "DELETE FROM files WHERE idPath NOT IN (SELECT idPath FROM path)"
    )
    print(f"Deleted {result.rowcount} files referencing absent paths")

    # Discover absent files on present paths
    files_to_delete = []
    for row in cursor.execute(
        "SELECT files.idFile, (strPath || strFilename) AS path FROM files LEFT JOIN path ON files.idPath = path.idPath WHERE strPath NOT LIKE 'plugin:%'"
    ):
        if not file_exist(row[1]):
            files_to_delete.append((row[0],))

    # Delete absent files
    cursor.executemany("DELETE FROM files WHERE idFile = ?", tuple(files_to_delete))
    print(f"Deleted {len(files_to_delete)} absent files")

    # Delete missing files bookmarks
    result = cursor.execute(
        "DELETE FROM bookmark WHERE idFile NOT IN (SELECT idFile FROM files)"
    )
    print(f"Deleted {result.rowcount} bookmarks")

    # Delete missing files settings
    result = cursor.execute(
        "DELETE FROM settings WHERE idFile NOT IN (SELECT idFile FROM files);"
    )
    print(f"Deleted {result.rowcount} settings")

    # Delete missing files streamdetails
    result = cursor.execute(
        "DELETE FROM settings WHERE idFile NOT IN (SELECT idFile FROM files);"
    )
    print(f"Deleted {result.rowcount} streamdetails")

    if not DRY_RUN:
        connection.commit()
        print("Vacuuming DB file")
        connection.execute("VACUUM")
    connection.close()