Retroactively update plex's updated_at, created_at and added_at dates by the files mtime

plex-update-mtime.py

#!/usr/bin/env python3
#
#   Most importantly:
#     1. Use at your own risk!
#     2. You may consider creating a backup because this script doesn't
#     3. Only tested on linux
#
#   What does this script do?
#   Whenever you scan for new items, plex will set the respective dates to
#   the CURRENT time rather than the files mtime.
#   This script will loop through all files, extract the files mtime and update
#   the values within the database.
#
import sys
import os
import sqlite3
import logging
from datetime import datetime
from contextlib import contextmanager

# Full path to plex's database file
# Change this if it is located elsewhere on your system
DB_PATH='/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db'


logging.basicConfig()
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)


@contextmanager
def db(db_path):
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        yield cursor

    finally:
        conn.commit()
        conn.close()


def get_data():
    data = list()
    with db(DB_PATH) as d:
        d.execute('SELECT id, media_item_id, file FROM media_parts');

        for media_part in d.fetchall():
            # No file path? Not interested
            if not media_part[2]:
                continue

            # Get a metadata_item_id
            d.execute('SELECT metadata_item_id FROM media_items WHERE id = ?',
                      (media_part[1],))
            media_item = d.fetchone()

            metadata_item_id = None

            # Get a recursive list of all metadata parents
            metadata_item_parents = set()
            if media_item is not None:
                metadata_item_id = media_item[0]

                d.execute('''
                    WITH tree AS (
                        SELECT id, parent_id
                        FROM metadata_items
                        WHERE id = ?
                        UNION ALL
                        SELECT c.id, c.parent_id
                        FROM metadata_items c
                        JOIN tree p on p.parent_id = c.id
                    )
                    SELECT *
                    FROM tree
                    WHERE id <> 1;''', (metadata_item_id,))

                for parent in d.fetchall():
                    metadata_item_parents.add(parent[1])

            # Get the physical mtime (the one from the file)
            mtime = datetime.fromtimestamp(
                int(os.path.getmtime(media_part[2])))

            # Add item to the data list
            data.append({
                'media_part_id': media_part[0],
                'media_item_id': media_part[1],
                'metadata_item_id': metadata_item_id,
                'metadata_item_parents': metadata_item_parents,
                'path': media_part[2],
                'mtime': mtime
            })

    return data

def update_media_parts(files):
    logger.info('Updating media_parts...')
    with db(DB_PATH) as d:
        for item in files:
            d.execute('''
                UPDATE media_parts
                SET updated_at = ?,
                    created_at = ?
                WHERE id = ?''', (item['mtime'], item['mtime'],
                                  item['media_part_id']))
    logger.info('...done updating {} entries'.format(len(files)))


def update_media_items(files):
    logger.info('Updating media_items...')
    with db(DB_PATH) as d:
        for item in files:

            d.execute('''
                UPDATE media_items
                SET updated_at = ?,
                    created_at = ?
                WHERE id = ?''', (item['mtime'], item['mtime'],
                                  item['media_item_id']))
    logger.info('...done updating {} entries'.format(len(files)))


def update_metadata_items(files):
    logger.info('Updating metadata_items...')
    with db(DB_PATH) as d:
        # `metadata_item_id` are always related to a physical file, hence
        # this loop will update only movies and episodes.
        for item in files:

            d.execute('''
                UPDATE metadata_items
                SET updated_at = ?,
                    created_at = ?,
                    added_at = ?
                WHERE id = ?''', (item['mtime'], item['mtime'], item['mtime'],
                                  item['metadata_item_id']))
        logger.info('...done updating {} entries'.format(len(files)))

        # This loop will update all seasons and shows using the date of the
        # youngest episode respectively
        logger.info('Updating metadata_item parents...')
        updated = set()
        for item in files:
            for parent in item['metadata_item_parents']:
                # Skip parent if already updated
                if parent in updated:
                    continue

                updated.add(parent)

                # Get youngest entry
                d.execute('''
                    SELECT
                        updated_at
                    FROM metadata_items
                    WHERE parent_id = ?
                    ORDER BY datetime(updated_at) ASC''', (parent,))
                row = d.fetchone()

                if row is None:
                    continue

                d.execute('''
                    UPDATE metadata_items
                    SET updated_at = ?,
                        created_at = ?,
                        added_at = ?
                    WHERE id = ?''', (item['mtime'], item['mtime'],
                                      item['mtime'], parent,))
        logger.info('...done updating {} entries'.format(len(updated)))


if __name__ == '__main__':
    data = get_data()

    update_media_parts(data)
    update_media_items(data)
    update_metadata_items(data)