XBMC Library Cleanup Part II - Missing Artwork

XBMC does a great job of fetching movie artwork from various source, most notably TheMovieDB. In the rare event that XBMC doesn’t find artwork you probably notice right away if you’re adding one movie at a time. But if you’re adding many movies at once – for instance when rebuilding the database from scratch – it can be a rather tedious process to scroll through your library looking for missing artwork. Fortunately, you can also query XBMC’s video database to get a list of movies with missing artwork.

As inthe tutorial on detecting missing or duplicate movies, this post first shows SQL queries to find out whether a particular movie is lacking artwork. In a second step a small Python script is listed that uses those queries to get a report for an entire folder of movies.

SQL Walkthrough

Step 1: Connect to the Video Library

The database is named MyVideos\*\*.db, where ** denotes the version number. On Windows you usually find the file under C:\Users\USERNAME\AppData\Roaming\XBMC\userdata\Database</span> (see the entry on the XBMC wiki for more information).

To query the database you’ll need a SQLite interface. Some popular choices on Windows are SQLite3Explorer{.highlight}, the SQLite Manager Extension for Firefox{.highlight} and the plain command-line shell{.highlight} – which I’m going to use in the following.

For the command-line program simply provide the database as the first argument to connect:

>sqlite3.exe <path_to_db>\MyVideos75.db 
SQLite version 3.8.2 2013-12-06 14:53:30 
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Step 2: From movie path to idPath

Let’s assume your movies are in separate folders that match the movie title, e.g.,

X:\Movies\3 Idiots (2009)\
    3 Idiots (2009).mkv
    extrathumbs\
        thumb1.jpg
        thumb2.jpg
    extrafanart\
        fanart1.jpg

The starting point is the folder that contains the video (X:\Movies\3 Idiots (2009))). The table path maps the path to a unique idPath:

SELECT idPath FROM path WHERE strPath="X:\Movies\3 Idiots (2009)\";
/* example result: 29 */

(Don’t forget the closing / or </span>). If there isn’t an entry for your movie path, then the movie won’t show up in XBMC and you should fix that first.

Step 3: From idPath to idFile

Given idPath we can get the idFile using table files:

SELECT idFile FROM files WHERE idPath=29;
/* example result: 19 */

Step 4: From idFile to idMovie

Given idFile we can get the idMovie using table movie:

SELECT idMovie FROM movie WHERE idFile=19;
/* example result: 23 */

Step 5: List available artwork for idMovie

Given idMovie we can get a list or artwork using table art:

.headers on
.mode column
.width 10, 20
SELECT media_type,type FROM art WHERE media_id=23;
/* example result:
media_type  type
----------  --------------------
episode     thumb
movie       banner
movie       clearart
movie       clearlogo
movie       fanart
movie       poster
set         fanart
set         poster
tvshow      banner
tvshow      characterart
tvshow      clearart
tvshow      clearlogo
tvshow      fanart
tvshow      landscape
tvshow      poster
*/

As you can see, the art table is a little special because it lists art for both movies, tvshows, tv episodes and movie sets. It uses the field media_id to reference at most one item of each category. So if we search for idMovie = 23 we will get a list of artwork (5 different ones) for our movie Three Idiots, but also artwork for a tvshow, a tv episode and a movie set, all of which are unrelated to the movie and simply share the same media_type.

So let’s add the WHERE-clause media_type=’movie’ to filter out unrelated library items. In exchange let’s list the url field which indicates the source of the artwork:

.width 8, 40
SELECT type,url FROM art WHERE media_id=23 AND media_type='movie';
/* example result:
type       url
---------  ----------------------------------------
banner     http://assets.fanart.tv/fanart/movies/20
clearart   http://assets.fanart.tv/fanart/movies/20
clearlogo  http://assets.fanart.tv/fanart/movies/20
fanart     X:\Movies\3 Idiots (2009)\fanart.jpg
poster     X:\Movies\3 Idiots (2009)\folder.jpg
*/

Our movie has five kinds of artwork. The fanart and poster come from the local storage, the bannar, clearart and clearlogo from fanart.tv. The most relevant artwork are fanart and poster; the former serves as the big backdrop image in many views like cover flow, the latter is usually used as the DVD/Bluray cover. If one of those to where missing, XBMC will either display a default image (in case of the backdrop) or worse, try to generate a thumbnail for you by getting a screencap from the movie. This will usually look very ugly, see this post’s featured image.

All in one query

Of course we can combine all steps into one query

SELECT type FROM art WHERE media_id=(
    SELECT idMovie FROM movie WHERE idFile=(
        SELECT idFile FROM files WHERE idPath=(
            SELECT idPath FROM path WHERE strPath="X:\Movies\3 Idiots (2009)\"
        )
    )
)
AND media_type='movie';
/* example result:
banner
clearart
clearlogo
fanart
poster
*/

Automate the Procedure with Python

The following code iterates over all movies in a movie folder and executes the above SQL queries for each one of them. It then reports missing artwork.

Code

import argparse
import sqlite3 as lite
import os
from collections import defaultdict
 
def _open_db(db):
    try:
        con = lite.connect(db)
        cur = con.cursor()
        return (cur,con)
    except lite.Error as e:
        print("Could not open database %s: %s" % (db,e))
        quit(1)
 
def detect_missing_artwork(db, movie_folder, arts):
    """ detect missing fanart 
    
        lookup all movies from a folder in video db
        and report if any artwork from 'arts' is missing 
       
        Args:
            db: path to 'MyVideos**.db' (XBMC video library)
            movie_folder: path to directory with movies in separate folders
            arts: list of missing art to report, i.e., ['fanart', 'poster']
            
        Returns:
            flaws: dictionary of flaws.
                   key: movie name
                   value: list of missing artwork
    """
 
    (cur,con) = _open_db(db)
    flaws = defaultdict(list)
    movies = os.listdir(movie_folder)
 
    # iterate over all subdirectories of movies, i.e., over all movies on disk
    for i, movie in enumerate(movies):
        # show progress
        print "\r{0:>3}/{1:<3} {2:<100}:".format(i, len(movies), movie),
        path = os.path.join(movie_folder, movie)
                
        sql = """
            SELECT type FROM art WHERE media_id=(
                SELECT idMovie FROM movie WHERE idFile=(
                    SELECT idFile FROM files WHERE idPath=(
                        SELECT idPath FROM path WHERE strPath=?                            
                    )
                )
            )
            AND media_type='movie';
        """        
        if os.path.exists(os.path.join(path,'VIDEO_TS')):
            strPath = path + "\\VIDEO_TS\\"            
        else:
            strPath = path + "\\"
        cur.execute(sql, (strPath,))
        present = [r[0] for r in cur.fetchall()]                
        for type_ in arts: 
            if type_ not in present:
                flaws[movie].append(type_) 
        
    # clean progress line
    print "\r" + 100*" " + "\r",
    con.close()
    return flaws
    
if __name__=="__main__":
    parser = argparse.ArgumentParser(description='find flaws in XBMC video database')
    parser.add_argument('db', help='path to myvideos**.db library')
    parser.add_argument('movie_folder', help='path to movie folder')   
    parser.add_argument('-a', '--artwork', help='one or more artwork to report is missing', 
            nargs='+', default=['fanart', 'poster'])

    args = parser.parse_args()
    
    flaws = detect_missing_artwork(args.db, args.movie_folder, args.artwork)
    for movie, flaw in sorted(flaws.items()):
        print("{0} missing {1} ".format(movie, ' and '.join(flaw)))

link to Github Gist.

Example Output

Chinmoku (1971) missing fanart
Day of Redemption (2004) missing fanart
Garbage (2013) missing fanart and poster
In the Hive (2012) missing poster
Sacrificial Freshmen (2011) missing poster
The Fault in Our Stars (2014) missing fanart
The Preacher's Daughter (2012) missing fanart
The Vanishing (1988) missing fanart and poster