Databases: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
>Zmx
mNo edit summary
>Twistedraisin
(updated episode table, c09 column and movie table c11 column)
Line 211: Line 211:
|c08 || text ||  Has the episode been watched?
|c08 || text ||  Has the episode been watched?
|-
|-
|c09 || text ||  [unknown]
|c09 || text ||  episode length in seconds (?)
|-
|-
|c10 || text ||  Director
|c10 || text ||  Director
Line 323: Line 323:
|c10 || text ||  [unknown]
|c10 || text ||  [unknown]
|-
|-
|c11 || text ||  Runtime
|c11 || text ||  Runtime [UPnP devices see this as seconds]
|-
|-
|c12 || text ||  MPAA Rating
|c12 || text ||  MPAA Rating

Revision as of 16:55, 16 May 2010

Template:XBMC faq toc Inline

Incomplete.png INCOMPLETE:
This page or section is incomplete. Please add information or correct uncertain data which is marked with a ?

XBMC Database Overview

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 $HOME\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 (add link).

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 labeled simply "c07." The tables further down this page help you find out which columns contain the information you're looking for.

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 a SQLite application

Database files can be transferred via FTP to a Windows-based computer and edited or analyzed with a SQLite graphical client. XBMC's version of the database engine should be compatible with the standard clients such as SQLiteBrowser or SQLiteCC, (more available management-tools can be found in sqlite.org WIKI).
We recommend you use SQLiteSpy version 1.5.2 or higher.

These applications are generally quite powerful. Once you find one you like, you can easily browse the XBMC databases. This is probably the best way to make modifications to the Video or Music Library.

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 the HTTP API call QueryMusicDatabase or QueryVideoDatabase.

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 music database is stored in userdata/Database/MyMusic7.db


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 video database is stored in userdata/Database/MyVideos34.db.

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.

Column Name Data Type Description
idActor integer Foreign key to actors table
idEpisode integer Foreign key to episode table
strRole text Role the actor played in this episode

actorlinkmovie

This table links actors to movies and stores role information.

Column Name Data Type Description
idActor integer Foreign key to actors table
idMovie integer Foreign key to movie table
strRole text Role the actor played in this movie

actorlinktvshow

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

Column Name Data Type Description
idActor integer Foreign key to actors table
idShow integer Foreign key to tvshow table
strRole text Role the actor played in this TV show

actors

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

Column Name Data Type Description
idActor integer Primary Key
strActor integer Name of the actor, artist, director, or writer
strThumb text Thumbnail URL

artistlinkmusicvideo

This table links artists to music videos.

Column Name Data Type Description
idArtist integer Foreign key to actors table
idMVideo integer Foreign key to musicvideo table

bookmark

This table stores bookmarks, which are timestamps representing the point in a video where a user stopped playback.

Column Name Data Type Description
idBookmark integer Primary Key
idFile integer Foreign key to files table
timeInSeconds double Time in seconds of bookmark
thumbNailImage text Thumbnail for bookmark
player text Player used to store bookmark
playerState text [unknown]
type integer [unknown]

directorlinkepisode

This table links directors to TV show episodes.

Column Name Data Type Description
idDirector integer Foreign key to actors table
idEpisode integer Foreign key to episode table
strRole text [Appears to be unused]

directorlinkmovie

This table links directors to movies.

Column Name Data Type Description
idDirector integer Foreign key to actors table
idMovie integer Foreign key to movie table

directorlinkmusicvideo

This table links directors to music videos.

Column Name Data Type Description
idDirector integer Foreign key to actors table
idMVideo integer Foreign key to musicvideo table

directorlinktvshow

This table links directors to TV shows.

Column Name Data Type Description
idDirector integer Foreign key to actors table
idShow integer Foreign key to tvshow table

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.

Column Name Data Type Description
idEpisode integer Primary Key
c00 text Episode Title
c01 text Plot Summary
c02 text [unknown]
c03 text Rating
c04 text Writer
c05 text First Aired
c06 text Thumbnail URL
c07 text [unknown]
c08 text Has the episode been watched?
c09 text episode length in seconds (?)
c10 text Director
c11 text [unknown]
c12 text Season
c13 text Episode Number
c14 text [unknown]
c15 text [unknown]
c16 text [unknown]
c17 text [unknown]
c18 text [unknown]
c19 text [unknown]
c20 text [unknown]
idFile integer Foreign key to the files table

files

This table stores filenames and links the path.

Column Name Data Type Description
idFile integer Primary Key
idPath integer Foreign key to path table
strFilename text Full name of file including extension
playCount integer
lastPlayed text

genre

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

Column Name Data Type Description
idGenre integer Primary Key
strGenre text Genre label

genrelinkmovie

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

Column Name Data Type Description
idGenre integer Foreign key to genre table
idMovie integer Foreign key to movie table

genrelinkmusicvideo

This table links genres to music videos.

Column Name Data Type Description
idGenre integer Foreign key to genre table
idMVideo integer Foreign key to musicvideo table

genrelinktvshow

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

Column Name Data Type Description
idGenre integer Foreign key to genre table
idShow integer Foreign key to tvshow table

movie

This table stores movie information.

Column Name Data Type Description
idMovie integer Primary Key
c00 text Local Movie Title
c01 text Movie Plot
c02 text Movie Plot Outline
c03 text Movie Tagline
c04 text Rating Votes
c05 text Rating
c06 text Writers
c07 text Year Released
c08 text Thumbnails
c09 text IMDB ID
c10 text [unknown]
c11 text Runtime [UPnP devices see this as seconds]
c12 text MPAA Rating
c13 text [unknown]
c14 text Genre
c15 text Director
c16 text Original Movie Title
c17 text [unknown]
c18 text Studio
c19 text Trailer URL
c20 text Fanart URLs
idFile integer Foreign Key to files table

movielinktvshow

This table links movies to TV shows.

Column Name Data Type Description
idMovie integer Foreign key to movie table
idShow integer Foreign key to tvshow table

musicvideo

[TODO]

path

This table stores path information.

Column Name Data Type Description
idPath integer Primary Key
strPath text Path URL
strContent text Type of content (tvshows, movies, etc...)
strScraper text XML file of scraper used for this path
strHash text Hash
scanRecursive integer Recursive scan setting
useFolderNames bool User folder names setting
strSettings text Custom settings used by scraper

settings

This table stores settings for individual files.

Column Name Data Type Description
idFile integer Foreign Key to files table
Interleaved bool Interleaved
Nocache bool NoCache
Deinterlace bool Deinterlace
FilmGrain integer FilmGrain
ViewMode integer ViewMode
ZoomAmount float ZoomAmount
PixelRatio float PixelRatio
AudioStream integer Selected audio stream
SubtitleStream integer Selected subtitle stream
SubtitleDelay float Amount of delay for subtitles
SubtitleOn bool Enable subtitles
Brightness integer Brightness
Contrast integer Contrast
Gamma integer Gamma
VolumeAmplification float VolumeAmplification
AudioDelay float AudioDelay
OutputToAllSpeakers bool OutputToAllSpeakers
ResumeTime integer ResumeTime
Crop bool Crop
CropLeft integer CropLeft
CropRight integer CropRight
CropTop integer CropTop
CropBottom integer CropBottom

stacktimes

This table stores stack times for files.

Column Name Data Type Description
idFile integer Foreign key to files table
times text Times

streamdetails

This table contains information regarding codecs used, aspect ratios etc

Column Name Data Type Description
idFile integer Foreign Key to files table
iStreamType integer 0 = video, 1 = audio, 2 = subtitles
strVideoCodec text Video codex (xvid etc)
fVideoAspect real Aspect ratio
iVideoWidth integer Width of the video
iVideoHeight integer Height of the video
strAudioCodec text Audio codec (aac, mp3 etc)
iAudioChannels integer Number of audio channels (2 for stereo, 6 for 5.1 etc)
strAudioLanguage text Language of the audio track
strSubtitleLanguage text Language of the subtitles

studio

This table stores studio information.

Column Name Data Type Description
idStudio integer Primary Key
strStudio text Studio Label

studiolinkmovie

This table links studios to movies.

Column Name Data Type Description
idStudio integer Foreign key to studio table
idMovie integer Foreign key to movie table

studiolinkmusicvideo

This table links studios to music videos.

Column Name Data Type Description
idStudio integer Foreign key to studio table
idMVideo integer Foreign key to movievideo table

tvshow

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

Column Name Data Type Description
idShow integer Primary Key
c00 text Show Title
c01 text Show Summary
c02 text [unknown]
c03 text [unknown]
c04 text Rating
c05 text First Aired
c06 text Thumbnail URL
c07 text [unknown]
c08 text Genre
c09 text [unknown]
c10 text Episode Guide URL
c11 text Fan Art URL
c12 text SeriesId (when using thetvdb.com scraper)
c13 text Content Rating
c14 text Network
c15 text [unknown]
c16 text [unknown]
c17 text [unknown]
c18 text [unknown]
c19 text [unknown]
c20 text [unknown]

tvshowlinkepisode

This table links TV shows (series) to episodes.

Column Name Data Type Description
idShow integer Foreign Key to tvshow table
idEpisode integer Foreign Key to episode table

tvshowlinkpath

This table links a TV show to its path.

Column Name Data Type Description
idShow integer Foreign key to tvshow table
idPath integer Foreign key to path table

version

This table stores database information.

Column Name Data Type Description
idVersion integer Version of database
idCompressCount integer Number of times database has been compressed

writerlinkepisode

This table links writers to TV show episodes.

Column Name Data Type Description
idWriter integer Foreign key to actors table
idEpisode integer Foreign key to episode table

writerlinkmovie

This table links writers to movies.

Column Name Data Type Description
idWriter integer Foreign key to actors table
idMovie integer Foreign key to movie table

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.


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.

Column Name Data Type Description
idVersion integer Version of database
idCompressCount integer Number of times database has been compressed

view

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

Column Name Data Type Description
idView integer Primary Key
window integer Window GUI ID
path text Path to trigger the view on
viewMode integer View Mode
sortMethod integer ID of sort method
sortOrder integer Sort order (ascending or descending)


TO-DO (what needs to be added to this database article)

  • The layout, etc. will be explained in more detail here later.
  • Need to add musicvideo table layout
  • Need to add music database information