Databases: Difference between revisions
>Alexpoet (fixed typo) |
>Alexpoet (Cleaned up the text and standardized the page layout) |
||
Line 1: | Line 1: | ||
{{Incomplete}} | {{Incomplete}} | ||
=XBMC Database | ==XBMC Database Overview== | ||
XBMC uses [http://www.sqlite.org/ SQLite], an open source light-weight SQL database-engine, to store all its library related data ([[Music Library|Music]], [[Video Library|Video]], and Program databases). By default, the database files (*.db) are stored in [[The UserData Folder]], specifically $HOME\UserData\Database\. | XBMC uses [http://www.sqlite.org/ SQLite], an open source light-weight SQL database-engine, to store all its library related data ([[Music Library|Music]], [[Video Library|Video]], and Program databases). By default, the database files (*.db) are stored in [[The UserData Folder]], specifically $HOME\UserData\Database\. | ||
Line 6: | Line 6: | ||
These entries are added to the database whether the affected video is part of the Video Library or not. | These entries are added to the database whether the affected video is part of the Video Library or not. | ||
== | |||
==Reading the Database== | |||
===Building SQL Queries=== | |||
[TO DO] | |||
===Using 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 [http://sqlitebrowser.sourceforge.net SQLiteBrowser] or [http://bobmanc.home.comcast.net/sqlitecc.html SQLiteCC], (more available management-tools can be found in [http://www.sqlite.org/cvstrac/wiki?p=ManagementTools sqlite.org WIKI]).<br> | 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 [http://sqlitebrowser.sourceforge.net SQLiteBrowser] or [http://bobmanc.home.comcast.net/sqlitecc.html SQLiteCC], (more available management-tools can be found in [http://www.sqlite.org/cvstrac/wiki?p=ManagementTools sqlite.org WIKI]).<br> | ||
We recommend you use [http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index SQLiteSpy version 1.5.2] or higher. | We recommend you use [http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index SQLiteSpy version 1.5.2] or higher. | ||
*[[HOW-TO: Use your computer to edit XBMC's (SQL) database-files]]. | *[[HOW-TO: Use your computer to edit XBMC's (SQL) database-files]]. | ||
== | ===Using 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 HTTPAPI call QueryMusicDatabase or QueryVideoDatabase. | 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 HTTPAPI call QueryMusicDatabase or QueryVideoDatabase. | ||
== | ==The Music Library== | ||
Stored in userdata/Database/MyMusic7.db | 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. | 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. | The video database is stored in userdata/Database/MyVideos34.db. | ||
===Tables=== | |||
====actorlinkepisode==== | |||
This table links actors to episodes and stores role information. | This table links actors to episodes and stores role information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 34: | Line 43: | ||
|} | |} | ||
====actorlinkmovie==== | |||
This table links actors to movies and stores role information. | This table links actors to movies and stores role information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 46: | Line 55: | ||
|} | |} | ||
====actorlinktvshow==== | |||
This table links actors to tv shows and stores role information. | This table links actors to tv shows and stores role information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 58: | Line 67: | ||
|} | |} | ||
====actors==== | |||
This table stores actor, artist, director, and writer information. | This table stores actor, artist, director, and writer information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 70: | Line 79: | ||
|} | |} | ||
====artistlinkmusicvideo==== | |||
This table links artists to music videos. | This table links artists to music videos. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 80: | Line 89: | ||
|} | |} | ||
====bookmark==== | |||
This table stores bookmarks. | This table stores bookmarks. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 100: | Line 109: | ||
|} | |} | ||
====directorlinkepisode==== | |||
This table links directors to tv show episodes. | This table links directors to tv show episodes. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 112: | Line 121: | ||
|} | |} | ||
====directorlinkmovie==== | |||
This table links directors to movies. | This table links directors to movies. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 122: | Line 131: | ||
|} | |} | ||
====directorlinkmusicvideo==== | |||
This table links directors to music videos. | This table links directors to music videos. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 132: | Line 141: | ||
|} | |} | ||
====directorlinktvshow==== | |||
This table links directors to tv shows. | This table links directors to tv shows. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 142: | Line 151: | ||
|} | |} | ||
====episode==== | |||
This table stores television episode information. | This table stores television episode information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 194: | Line 203: | ||
|} | |} | ||
====files==== | |||
This table stores filenames and links the path. | This table stores filenames and links the path. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 206: | Line 215: | ||
|} | |} | ||
====genre==== | |||
This table stores Genre information. | This table stores Genre information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 216: | Line 225: | ||
|} | |} | ||
====genrelinkmovie==== | |||
This table links genres to movies. | This table links genres to movies. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 226: | Line 235: | ||
|} | |} | ||
====genrelinkmusicvideo==== | |||
This table links genres to music videos. | This table links genres to music videos. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 236: | Line 245: | ||
|} | |} | ||
====genrelinktvshow==== | |||
This table links genres to tv show. | This table links genres to tv show. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 246: | Line 255: | ||
|} | |} | ||
====movie==== | |||
This table stores movie information. | This table stores movie information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 298: | Line 307: | ||
|} | |} | ||
====movielinktvshow==== | |||
This table links movies to tv shows. | This table links movies to tv shows. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 308: | Line 317: | ||
|} | |} | ||
====musicvideo==== | |||
[TODO] | [TODO] | ||
====path==== | |||
This table stores path information. | This table stores path information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 332: | Line 342: | ||
|} | |} | ||
====settings==== | |||
This table stores settings for individual files. | This table stores settings for individual files. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 386: | Line 396: | ||
|} | |} | ||
====stacktimes==== | |||
This table stores stack times for files. | This table stores stack times for files. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 396: | Line 406: | ||
|} | |} | ||
====studio==== | |||
This table stores studio information. | This table stores studio information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 406: | Line 416: | ||
|} | |} | ||
====studiolinkmovie==== | |||
This table links studios to movies. | This table links studios to movies. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 416: | Line 426: | ||
|} | |} | ||
====studiolinkmusicvideo==== | |||
This table links studios to music videos. | This table links studios to music videos. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 426: | Line 436: | ||
|} | |} | ||
====tvshow==== | |||
This table stores television show information. | This table stores television show information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 475: | Line 485: | ||
|c20 || text || [unknown] | |c20 || text || [unknown] | ||
|} | |} | ||
==== tvshowlinkepisode ==== | |||
This table is a simple linker table to join TV Shows and Episodes. | This table is a simple linker table to join TV Shows and Episodes. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 484: | Line 495: | ||
|idEpisode || integer || Foreign Key to [http://www.xbmc.org/wiki/?title=The_XBMC_Database#episode episode table] | |idEpisode || integer || Foreign Key to [http://www.xbmc.org/wiki/?title=The_XBMC_Database#episode episode table] | ||
|} | |} | ||
====tvshowlinkpath==== | |||
This table links tv shows to its path. | This table links tv shows to its path. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 494: | Line 506: | ||
|} | |} | ||
====version==== | |||
This table stores database information. | This table stores database information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 504: | Line 516: | ||
|} | |} | ||
====writerlinkepisode==== | |||
This table links writers to tv show episodes. | This table links writers to tv show episodes. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 514: | Line 526: | ||
|} | |} | ||
====writerlinkmovie==== | |||
This table links writers to movies. | This table links writers to movies. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 524: | Line 536: | ||
|} | |} | ||
===Views=== | |||
====episodeview==== | |||
View that joins episode to file and tvshow (through tvshowlinkepisode) and path. | View that joins episode to file and tvshow (through tvshowlinkepisode) and path. | ||
====movieview==== | |||
View that joins movie to file and path. | View that joins movie to file and path. | ||
====musicvideoview==== | |||
View that joins movie to file and path. | View that joins movie 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. | 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== | |||
This database contains the state of views and sorting methods per path while navigating XBMC. | This database contains the state of views and sorting methods per path while navigating XBMC. | ||
It is stored in userdata/Database/ViewModes.db. | It is stored in userdata/Database/ViewModes.db. | ||
===Tables=== | |||
====version==== | |||
This table stores database information. | This table stores database information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 548: | Line 566: | ||
|} | |} | ||
====view==== | |||
This table stores view information. | This table stores view information. | ||
{|class="usertable" border="1" | {|class="usertable" border="1" | ||
Line 566: | Line 584: | ||
|} | |} | ||
=TO-DO (what needs to be added to this database article)= | |||
==TO-DO (what needs to be added to this database article)== | |||
*The layout, etc. will be explained in more detail here later. | *The layout, etc. will be explained in more detail here later. | ||
Revision as of 22:16, 5 March 2009
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 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.
Reading the Database
Building SQL Queries
[TO DO]
Using 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.
Using 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 HTTPAPI call QueryMusicDatabase or QueryVideoDatabase.
The Music Library
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
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.
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.
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 | [unknown] |
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 |
genre
This table stores Genre information.
Column Name | Data Type | Description |
---|---|---|
idGenre | integer | Primary Key |
strGenre | text | Genre label |
genrelinkmovie
This table links genres to movies.
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.
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 | 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 |
c12 | text | MPAA Rating |
c13 | text | [unknown] |
c14 | text | Genre |
c15 | text | Director |
c16 | text | [unknown] |
c17 | text | [unknown] |
c18 | text | Studio |
c19 | text | [unknown] |
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 |
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 television show information.
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 | [unknown] |
c13 | text | [unknown] |
c14 | text | [unknown] |
c15 | text | [unknown] |
c16 | text | [unknown] |
c17 | text | [unknown] |
c18 | text | [unknown] |
c19 | text | [unknown] |
c20 | text | [unknown] |
tvshowlinkepisode
This table is a simple linker table to join TV Shows and Episodes.
Column Name | Data Type | Description |
---|---|---|
idShow | integer | Foreign Key to tvshow table |
idEpisode | integer | Foreign Key to episode table |
tvshowlinkpath
This table links tv shows 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
episodeview
View that joins episode to file and tvshow (through tvshowlinkepisode) and path.
movieview
View that joins movie to file and path.
musicvideoview
View that joins movie 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
This database contains the state of views and sorting methods per path while navigating XBMC. It is stored in userdata/Database/ViewModes.db.
Tables
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 view information.
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.