XBMC databases

From XBMC4Xbox
(Redirected from The XBMC Database)
Jump to navigation Jump to search

Return to: XBMC Online Manual

<<Edit this toc>>

Template:Incomplete

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 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 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



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.



songview


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



Tables


album


This table contains basic album information.






Column Name
Data Type
Description




idAlbum
integer
Primary Key




strAlbum
text
[unknown]




idArtist
integer
Foreign key to artist table




strExtraArtists
text
[unknown]




idGenre
integer
Foreign key to genre table




strExtraGenres
text
[unknown]




iYear
integer
[unknown]




idThumb
integer
Foreign key to thumb table



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 tables stores relevant information about each artist, such as when they were born, Fan Art URLs, 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.



thumb





Column Name
Data Type
Description




idThumb
integer
Primary Key




strThumb
text
[unknown]



version(music)





Column Name
Data Type
Description




idVersion
integer
Version of the music database




idCompressCount
integer
Compression Count?



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.



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.






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, an explicit bookmark requested by the user, or an automatically generated episode bookmark.






Column Name
Data Type
Description




idBookmark
integer
Primary Key




idFile
integer
Foreign key to files table




timeInSeconds
double
Time in seconds of bookmark location




totalTimeInSeconds
integer
Time in seconds of the video




thumbNailImage
text
Thumbnail for bookmark




player
text
Player used to store bookmark




playerState
text
Player's internal state in XML




type
integer
Type of bookmark (0=standard, 1=resume, 2=episode



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 - listed as Votes]




c03
text
Rating




c04
text
Writer




c05
text
First Aired




c06
text
Thumbnail URL




c07
text
[unknown - listed as Thumbnail URL Spoof, unused?]




c08
text
Has the episode been watched? (unused?)




c09
text
Episode length in seconds (?)




c10
text
Director




c11
text
[unknown - listed as Indentifier]




c12
text
Season




c13
text
Episode Number




c14
text
[unknown - listed as Original Title]




c15
text
Season formatted for sorting




c16
text
Episode formatted for sorting




c17
text
Bookmark




c18
text
Not used




c19
text
Not used




c20
text
Not used




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
Title formatted for sorting




c11
text
Runtime [UPnP devices see this as seconds]




c12
text
MPAA Rating




c13
text
[unknown - listed as Top250]




c14
text
Genre




c15
text
Director




c16
text
Original Movie Title




c17
text
[unknown - listed as Thumbnail URL Spoof]




c18
text
Studio




c19
text
Trailer URL




c20
text
Fanart URLs




c21
text
Country (Added in r29886[1]




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





Column Name
Data Type
Description




idMVideo
integer
Primary Key




c00
text
Title




c01
text
Thumbnail URL




c02
text
[unknown - listed as Thumbnail URL spoof]




c03
text
Play count (unused?)




c04
text
Run time




c05
text
Director




c06
text
Studios




c07
text
Year




c08
text
Plot




c09
text
Album




c10
text
Artist




c11
text
Genre




c12
text
Track




idFile
integer
Foreign Key to files table



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 playing times for files (used for playing multi-file videos).






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 Plot Summary




c02
text
Status




c03
text
[unknown - Votes?]




c04
text
Rating




c05
text
First Aired




c06
text
Thumbnail URL




c07
text
[unknown - Spoof Thumbnail URL?]




c08
text
Genre




c09
text
[unknown - listed as Original Title]




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
Title formatted for sorting




c16
text
Not Used




c17
text
Not Used




c18
text
Not Used




c19
text
Not Used




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








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 music database information








Retrieved from ‘https://www.xbmc4xbox.org.uk/mediawiki/index.php?title=XBMC_databases&oldid=15715’