XBMC databases

XBMC uses SQLite, an open source light-weight SQL database-engine, to store all its library related data (Music, Video, and Program databases). By default, the database files (*.db) are stored in The UserData Folder, specifically in userdata/Database.

In addition to indexing media files when activated by user-selected Content settings, XBMC also puts a video in its database if you change any OSD setting while watching it. Resume points are stored in this database as well. These entries are added to the database whether the affected video is part of the Video Library or not.

Using the Databases
The XBMC databases are automatically maintained whenever you use XBMC. You can activate the most powerful database functionality by setting the Content property on all of your media sources, and using XBMC Library Mode. This view mode allows you to browse your media based on the details available in the databases, rather than using simple folder and filenames for details. You can read more about Library Mode for Music and Video files on their respective pages.

Since XBMC maintains the databases on its own, the only time a developer really needs to access the databases is for display information. The following sections discuss how you can access the information contained in the XBMC databases, and give some brief examples of how to use it.

Building SQL Queries
SQLite queries can be incredibly powerful (and extraordinarily complicated). If you are not already familiar with SQL syntax, it would probably be a good idea to check out a general tutorial, such as this one.

For most XBMC development projects, you're going to be doing select statements. "Select" is a SQL command used to gather data (in the form of "rows") out of a SQL database. Your select statement will include:


 * A list of all the data fields (columns in the database table) you want for each row.
 * A list of all the tables you need to get information from
 * A list of comparisons used to narrow down your results. This last component is optional, but it makes sure your results are relevant and is often used to link database entries across multiple tables.

Below are a few sample select statements, so you can see how it works.

This query grabs all of the information for every movie in the Video Library. select * from movie Note that "*" is used to indicate all fields. Also,there is no "where" clause in this statement, so it returns every row in the table.

This query narrows down the results to just those movies released in 2007. select * from movie where c07 = 2007 Note that the column containing the movie's release year is labelled simply "c07." The tables further down this page help you find out which columns contain the information you're looking for.

This query example is more semantic. Lists your movies including, in this order, internal ID, internal file ID, rating, movie year, IMDB ID, movie name and movie plot. select idMovie,idFile,c05,c07,c09,c00,c03,c02 from movie;

Now the following query is a bit more complex because it joins 3 movie-related tables (movie, files and path) to list a single useful view of your movies. In human language it lists movie ID, movie year, IMDB rating, IMDB ID, movie name and full path of the movie file, ordered by IMDB rating with highest rating appearing first: select idMovie,c07,c05,c09,c00,path.strPath||files.strFilename from movie, files,path where movie.idFile=files.idFile and files.idPath=path.idPath order by c05 desc;

You could also use less than or greater than symbols to get newer or older movies.

This query gets just the path and filename of all of the video files from season two of Chuck. If you're not familiar with SQL queries, this query probably looks pretty complicated. It serves as a good demonstration of why there are so many tables in the list below, and how to use them. Many of the elements of a TV show's path and filename and used repeatedly, so SQL allows us to save space and speed up our searches by storing each of those elements just once, in one place, and referencing them repeatedly by the same ID.

In this case, the root path that contains your video files is a long string that repeats at the beginning of many files. The name of a TV series, too, is repeated in every single episode of that series, so it makes the most sense to save the series name once (along with all information relevant to the series). We do that in the table tvshow, and every episode of the TV show can access all of that information using just the TV show's ID.

Accessing the Databases with XBMC Python
Many Python plugins (and some scripts) can use the information in the XBMC database to offer users additional convenience and functionality. The easiest way to access the XBMC database via XBMC Python is using JSON RPC

The Music Library
This database contains all information concerning music files that you've added to the Music Library. It is used in the Music portion of XBMC. The following information is for version 32.

The music database is stored in userdata/Database/MyMusicXX.db, where XX is the version number.

Views
Views are standard queries, often long or complicated queries saved in the database for convenience. The views below allow you to easily access all the information about songs and albums in the Music Library, across all the linking tables.

albumview
A view that joins album to artist, genre, thumb, and albuminfo.

artistview
A view that joins artist to artistinfo.

songview
A view that joins song to album, path, artist, genre, thumb, and karaokedata.

album
This table contains basic album information.

album_genre
This links albums with their genres.

albuminfo
This table contains additional information about an album, such as Rating, Moods, Styles, Reviews, Image URL, and type.

albuminfosong
This table links songs to albums and stores the duration of each track.

artist
This table stores the name of each artist.

artistinfo
This table stores relevant information about each artist, such as when they were born, Fan Art URL's, biographical information, etc.

content
This table is related to the scraper.

discography
Links albums to artists with the year produced.

exartistablum
Links artists to albums

exartistsong
Links artists to songs

exgenrealbum
Links genres to albums

exgenresong
Links genres to songs

genre(music)
This table contains genre titles.

karaokedata
This table contains karaoke specific information for certain songs

path(music)
This table contains paths and hashes of files in the Music Database.

song
This table contains song information such as Name, Track Title, MusicBrainz information, times played, last played, rating, etc.

song_artist
This table links songs to artists.

song_genre
This table links the songs with their genre.

The Video Library
This database contains all information concerning TV shows, movies, and music videos. It is used in the Videos portion of XBMC. The following information is for version 75.

The video database is stored in userdata/Database/MyVideosXX.db, where XX is the version number.

Views
Views are standard queries, often long or complicated queries saved in the database for convenience. The views below allow you to easily access all the information about each of the main media types in the Video Library, across all the linking tables.

episodeview
A view that joins episode to file and tvshow (through tvshowlinkepisode) and path.

movieview
A view that joins movie to file and path.

musicvideoview
A view that joins musicvideo to file and path.

tvshowview
View that joins tvshow to path. Also produces information about total number of episodes as well as number of watched and unwatched episodes.

Tables
The information in the Video Library is organized into the following tables. Several large tables (such as episode, movie, settings, and tvshow) contain the bulk of the information, while most of the others are used to link a long string to a common ID key.

actorlinkepisode
This table links actors to episodes and stores role information.

actorlinkmovie
This table links actors to movies and stores role information.

actorlinktvshow
This table links actors to TV shows and stores role information.

actors
This table stores actor, artist, director, and writer information.

art
This table stores URLs for video art metadata.

artistlinkmusicvideo
This table links artists to music videos.

bookmark
This table stores bookmarks, which are timestamps representing the point in a video where a user stopped playback, an explicit bookmark requested by the user, or an automatically generated episode bookmark.

country
This table lists countries.

countrylinkmovie
This table links countries to movies.

directorlinkepisode
This table links directors to TV show episodes.

directorlinkmovie
This table links directors to movies.

directorlinkmusicvideo
This table links directors to music videos.

directorlinktvshow
This table links directors to TV shows.

episode
This table stores television episode information. Information concerning the series is stored in tvshow. To link an episode to its parent series, use tvshowlinkepisode.

files
This table stores filenames and links the path.

genre
This table stores genre information. For convenience the contents are duplicated in movie and tvshow, so a join isn't necessary.

genrelinkmovie
This table links genres to movies. (The contents are also stored in movies.c14, though.)

genrelinkmusicvideo
This table links genres to music videos.

genrelinktvshow
This table links genres to TV show. (The contents are also stored in tvshow.c08, though.)

movie
This table stores movie information.

movielinktvshow
This table links movies to TV shows.

path
This table stores path information.

seasons
This table stores the links between tv show and seasons.

sets
This table stores the id and name for movie sets. Sets are linked to movies in the movie table (idSet column).

settings
This table stores settings for individual files.

stacktimes
This table stores playing times for files (used for playing multi-file videos).

streamdetails
This table contains information regarding codecs used, aspect ratios etc

studio
This table stores studio information.

studiolinkmovie
This table links studios to movies.

studiolinkmusicvideo
This table links studios to music videos.

studiolinktvshow
This table links studios to tv shows.

tag
This stores tags.

taglinks
This table links tags to various media.

tvshow
This table stores information about a television series. Information concerning the shows episodes is stored in episode. To link a TV show to its episodes, use tvshowlinkepisode.

tvshowlinkepisode
This table links TV shows (series) to episodes.

tvshowlinkpath
This table links a TV show to its path.

version
This table stores database information.

writerlinkepisode
This table links writers to TV show episodes.

writerlinkmovie
This table links writers to movies.

The View Modes Database
XBMC can track a user's View Mode for every path, so you could browse movies using DVD Thumbs and browse TV shows using Fanart. This database contains the last view and sorting method a user chose for each path while navigating XBMC.

The View Modes database is stored in userdata/Database/ViewModes.db.

Tables
The View Modes database uses only two tables. Most of the useful information is in view.

version
This table stores database information.

view
This table stores details on the saved view mode for all known paths.