Small Python script to remove invalid paths from the Synology Media Server database

synomanager.py

#!/usr/bin/python
# synomanager.py 
# Usage: ./synomanager.py [-r]
# Running without any arguments will notify you of invalid paths
# only. To remove the invalid paths run with -r or --remove 

import psycopg2
import sys
import os.path
import getopt

usage = 'Usage: synomanager.py [-r]'
has_missing = False
invalid_count = 0
con = None

tables = [ \
    'directory', 'music', 'personal_directory', 'personal_music', \
    'personal_playlist', 'photo', 'playlist', 'video' \
]

def main(argv):
    remove = False
    try:
        opts, args = getopt.getopt(argv, 'hr', ['help', 'remove'])
    except:
        print usage
        sys.exit(2)

    for opt, arg in opts:
        if opt in ('-h', '--help'):
            print usage
            sys.exit(2)
        elif opt in ('-r', '--remove'):
            remove = True

    if remove:
        print 'Invalid paths will be removed from the media database'

    try:
        con = psycopg2.connect(database='mediaserver', user='admin')
        cur = con.cursor()
        find_invalid_paths(cur, remove)

        print "%d invalid record(s) found." % invalid_count
        if not remove and has_missing:
            print "Rerun with -r flag to remove invalid files from database."

    except psycopg2.DatabaseError, e:
        print 'Error %s' % e
        sys.exit(1)

    finally:
        if con:
            #commit any pending changes
            con.commit()
            con.close()

def find_invalid_paths(cur, remove=False):
    for table in tables:
        print "Checking %s..." % table
        cur.execute('SELECT id, path FROM %s' % table)
        missing = find_invalid_paths_iter(cur)
        if len(missing) > 0 and remove:
            remove_invalid_records(cur, table, missing)

def find_invalid_paths_iter(cur):
    missing = []
    global has_missing
    global invalid_count

    for rec in cur:
        if not os.path.exists(rec[1]):
            print "MISSING: %s" % rec[1]
            has_missing = True
            invalid_count += 1
            missing.append(rec)

    print "Processed %s records." % cur.rowcount
    return missing

def remove_invalid_records(cur, table, records):
    for id, path in records:
        cur.execute(('DELETE FROM %s' % table) + ' WHERE id = %s', (id, ))
        print 'REMOVING: %s' % path

if __name__ == "__main__":
    main(sys.argv[1:])