Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Retroactively update plex's updated_at, created_at and added_at dates by the files mtime
#!/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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.