Databases: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
mNo edit summary
No edit summary
(121 intermediate revisions by 20 users not shown)
Line 1: Line 1:
{{Cleanup}}
{{Template:DatabasesNavBox}}
{{incomplete}}
{{mininav| [[Development]] {{l2| [[Userdata]] }} }}
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 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 Library|Music]] and [[Video Library|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.
= Introduction =
<section begin="intro" />Kodi 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 in userdata/Database.


=== Building SQL Queries ===
Performing certain actions on video files will also add content to the database even if it has not been scanned or scraped. For example if you change any OSD setting while watching it, this will be saved in the database as a preference. Resume points are also stored in this database for unscraped videos.<section end="intro" />
 
 
 
= Using the Databases =
Databases are automatically maintained whenever you use Kodi. You can activate the most powerful database functionality by setting the Content property on all of your media sources, and using Kodi 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 Library|Music]] and [[Video Library|Video]] files on their respective pages.
 
The following sections detail the database structure, tables, fields and their properties.
 
 
 
== 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 [http://www.1keydata.com/sql/sql.html this one].
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 [http://www.1keydata.com/sql/sql.html 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:
For most Kodi 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 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 all the tables you need to get information from
Line 25: Line 31:
This query grabs all of the information for every movie in the Video Library.  
This query grabs all of the information for every movie in the Video Library.  
<pre><nowiki>select * from movie</nowiki></pre>
<pre><nowiki>select * from movie</nowiki></pre>
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.
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.  
This query narrows down the results to just those movies released in 2007.  
<pre><nowiki>select * from movie where c07 = 2007</nowiki></pre>
<pre><nowiki>select * from movie where c07 = 2007</nowiki></pre>
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.  
 
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.
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.
<pre><nowiki>select idMovie,idFile,c05,c07,c09,c00,c03,c02 from movie;</nowiki></pre>
<pre><nowiki>select idMovie,idFile,c05,c07,c09,c00,c03,c02 from movie;</nowiki></pre>


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:
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:
<pre><nowiki> 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;</nowiki></pre>
<pre><nowiki> 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;</nowiki></pre>


Line 41: Line 49:
This query gets just the path and filename of all of the video files from season two of Chuck.
This query gets just the path and filename of all of the video files from season two of Chuck.
<pre><nowiki></nowiki></pre>
<pre><nowiki></nowiki></pre>
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.
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.
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 [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.
* [[HOW-TO: Use your computer to edit XBMC's (SQL) database-files]].
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 [[Web Server HTTP API|HTTP API]] call [[Web Server HTTP API#Commands that Retrieve Information|QueryMusicDatabase]] or [[Web Server HTTP API#Commands that Retrieve Information|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.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idAlbum || integer || Primary Key
|-
|strAlbum || text || [unknown]
|-
|idArtist || integer || Foreign key to [[The XBMC Database#artist|artist table]]
|-
|strExtraArtists || text || [unknown]
|-
|idGenre || integer || Foreign key to [[The XBMC Database#genre(music)|genre table]]
|-
|strExtraGenres || text || [unknown]
|-
|iYear || integer || [unknown]
|-
|idThumb || integer || Foreign key to [[The XBMC Database#thumb(music)|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 ====
== Accessing the Databases with Kodi Python ==
Links genres to albums
Many Python plugins (and some scripts) can use the information in the Kodi database to offer users additional convenience and functionality. The easiest way to access the databases via Kodi Python is using [[JSON RPC]]


==== exgenresong ====
Links genres to songs


==== genre(music) ====
This table contains genre titles.


==== karaokedata ====
== Tags ==
This table contains karaoke specific information for certain songs
Incorporated into the tables below are the related music metadata tags that are read from music files, and the NFO XML tags exported and imported by Kodi.
{{see also | Music tagging | Import-export library | NFO files}}


==== 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 ====
= Database Versions =
{|class="wikitable"
<section begin="database versions" />The following tables shows what database version is used for various Kodi versions. This can be useful to see what versions of Kodi can use [[MySQL]] sharing.
! Column Name || Data Type || Description
|-
|idThumb || integer || Primary Key
|-
|strThumb || text || [unknown]
|}


==== version(music) ====
{|class="prettytable"
{|class="wikitable"
! Kodi version !! Date !! MyVideos !! MyMusic !! Textures !! Addons !! ViewModes !! TV !! EPG !! ADSP
! Column Name || Data Type || Description
|-
|-
|idVersion || integer || Version of the music database
| style="width:120px;" | v10 - Dharma
| style="width:120px;" | December 2010
| style="width:80px; text-align:center;" | 37
| style="width:80px; text-align:center;" | 7
| style="width:80px; text-align:center;" | 1
| style="width:80px; text-align:center;" | 1
| style="width:80px; text-align:center;" | 1
| style="width:60px; text-align:center;" | N/A
| style="width:60px; text-align:center;" | N/A
| style="width:60px; text-align:center;" | N/A
|-
|-
|idCompressCount || integer || Compression Count?
| style="width:120px;" | v11 - Eden
|}
| style="width:120px;" | March 2012
 
| style="width:80px; text-align:center;" | 60
== The Video Library ==
| style="width:80px; text-align:center;" | 18
This database contains all information concerning TV shows, movies, and music videos. It is used in the Videos portion of XBMC.
| style="width:80px; text-align:center;" | 6
 
| style="width:80px; text-align:center;" | 15
The video database is stored in userdata/Database/MyVideos60.db.
| style="width:80px; text-align:center;" | 4
 
| style="width:60px; text-align:center;" | N/A
=== Views ===
| style="width:60px; text-align:center;" | N/A
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.
| style="width:60px; text-align:center;" | N/A
 
==== 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 [[The XBMC Database#episode|episode]], [[The XBMC Database#movie|movie]], [[The XBMC Database#settings|settings]], and [[The XBMC Database#tvshow|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.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|-
|idActor || integer || Foreign key to [[The XBMC Database#actors|actors table]]
| style="width:120px;" | v12 - Frodo
| style="width:120px;" | January 2013
| style="width:80px; text-align:center;" | 75
| style="width:80px; text-align:center;" | 32
| style="width:80px; text-align:center;" | 13
| style="width:80px; text-align:center;" | 15
| style="width:80px; text-align:center;" | 4
| style="width:60px; text-align:center;" | 22
| style="width:60px; text-align:center;" | 7
| style="width:60px; text-align:center;" | N/A
|-
|-
|idEpisode || integer || Foreign key to [[The XBMC Database#episode|episode table]]
| style="width:120px;" | v13 - Gotham
| style="width:120px;" | May 2014
| style="width:80px; text-align:center;" | 78
| style="width:80px; text-align:center;" | 46
| style="width:80px; text-align:center;" | 13
| style="width:80px; text-align:center;" | 16
| style="width:80px; text-align:center;" | 6
| style="width:60px; text-align:center;" | 22
| style="width:60px; text-align:center;" | 7
| style="width:60px; text-align:center;" | N/A
|-
|-
|strRole || text || Role the actor played in this episode
| style="width:120px;" | v14 - Helix
|}
| style="width:120px;" | December 2014
 
| style="width:80px; text-align:center;" | 90
==== actorlinkmovie ====
| style="width:80px; text-align:center;" | 48
This table links actors to movies and stores role information.
| style="width:80px; text-align:center;" | 13
{|class="wikitable"
| style="width:80px; text-align:center;" | 16
! Column Name || Data Type || Description
| style="width:80px; text-align:center;" | 6
| style="width:60px; text-align:center;" | 26
| style="width:60px; text-align:center;" | 8
| style="width:60px; text-align:center;" | N/A
|-
|-
|idActor || integer || Foreign key to [[The XBMC Database#actors|actors table]]
| style="width:120px;" | v15 - Isengard
| style="width:120px;" | July 2015
| style="width:80px; text-align:center;" | 93
| style="width:80px; text-align:center;" | 52
| style="width:80px; text-align:center;" | 13
| style="width:80px; text-align:center;" | 19
| style="width:80px; text-align:center;" | 6
| style="width:60px; text-align:center;" | 29
| style="width:60px; text-align:center;" | 10
| style="width:60px; text-align:center;" | N/A
|-
|-
|idMovie || integer || Foreign key to [[The XBMC Database#movie|movie table]]
| style="width:120px;" | v16 - Jarvis
| style="width:120px;" | February 2016
| style="width:80px; text-align:center;" | 99
| style="width:80px; text-align:center;" | 56
| style="width:80px; text-align:center;" | 13
| style="width:80px; text-align:center;" | 20
| style="width:80px; text-align:center;" | 6
| style="width:60px; text-align:center;" | 29
| style="width:60px; text-align:center;" | 11
| style="width:60px; text-align:center;" | 0
|-
|-
|strRole || text || Role the actor played in this movie
| style="width:120px;" | v17 - Krypton
|}
| style="width:120px;" | February 2017
 
| style="width:80px; text-align:center;" | 107
==== actorlinktvshow ====
| style="width:80px; text-align:center;" | 60
This table links actors to TV shows and stores role information.
| style="width:80px; text-align:center;" | 13
{|class="wikitable"
| style="width:80px; text-align:center;" | 27
! Column Name || Data Type || Description
| style="width:80px; text-align:center;" | 6
|-
| style="width:60px; text-align:center;" | 29
|idActor || integer || Foreign key to [[The XBMC Database#actors|actors table]]
| style="width:60px; text-align:center;" | 11
| style="width:60px; text-align:center;" | 0
|-
|-
|idShow || integer || Foreign key to [[The XBMC Database#tvshow|tvshow table]]
| style="width:120px; background:#a0e75a;" | v18 - Leia
| style="width:120px; background:#a0e75a;" | January 2019
| style="width:80px; text-align:center; background:#a0e75a;" | 116
| style="width:80px; text-align:center; background:#a0e75a;" | 72
| style="width:80px; text-align:center; background:#a0e75a;" | 13
| style="width:80px; text-align:center; background:#a0e75a;" | 27
| style="width:80px; text-align:center; background:#a0e75a;" | 6
| style="width:60px; text-align:center; background:#a0e75a;" | 32
| style="width:60px; text-align:center; background:#a0e75a;" | 12
| style="width:60px; text-align:center; background:#a0e75a;" | 0
|-
|-
|strRole || text || Role the actor played in this TV show
| style="width:120px; background: skyBlue;" | v19 - Matrix
| style="width:120px; background: skyBlue;" | June 2020
| style="width:80px; text-align:center; background: skyBlue;" | 117
| style="width:80px; text-align:center; background: skyBlue;" | 77
| style="width:80px; text-align:center; background: skyBlue;" | 13
| style="width:80px; text-align:center; background: skyBlue;" | 27
| style="width:80px; text-align:center; background: skyBlue;" | 6
| style="width:60px; text-align:center; background: skyBlue;" | 36
| style="width:60px; text-align:center; background: skyBlue;" | 13
| style="width:60px; text-align:center; background: skyBlue;" | 0
|}
|}


==== actors ====
{| class="prettytable"
This table stores actor, artist, director, and writer information.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|-
|idActor || integer || Primary Key
! colspan="2"| Legend
|-
|-
|strActor|| integer || Name of the actor, artist, director, or writer
| style="background:#a0e75a;"| Green
| Current release
|-
|-
|strThumb || text || Thumbnail URL
| style="background:skyBlue;"| Blue
| Future release
|}
|}
<section end="database versions" />


==== artistlinkmusicvideo ====
This table links artists to music videos.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idArtist || integer || Foreign key to [[The XBMC Database#actors|actors table]]
|-
|idMVideo || integer || Foreign key to [[The XBMC Database#musicvideo|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.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idBookmark || integer || Primary Key
|-
|idFile || integer || Foreign key to [[The XBMC Database#files|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 ====
== Links ==
This table links directors to TV show episodes.
* http://www.rieter.net/content/xot/xbmc-database-versions/ - another site keeping track of Kodi DB changes
{|class="wikitable"
* https://github.com/xbmc/xbmc/commits/master/xbmc/video/VideoDatabase.cpp - Tracking the Video Database schema version
! Column Name || Data Type || Description
* https://github.com/xbmc/xbmc/commits/master/xbmc/music/MusicDatabase.cpp - Tracking the Music Database schema version
|-
|idDirector || integer || Foreign key to [[The XBMC Database#actors|actors table]]
|-
|idEpisode || integer || Foreign key to [[The XBMC Database#episode|episode table]]
|-
|strRole || text || [Appears to be unused]
|}
 
==== directorlinkmovie ====
This table links directors to movies.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idDirector || integer || Foreign key to [[The XBMC Database#actors|actors table]]
|-
|idMovie || integer || Foreign key to [[The XBMC Database#movie|movie table]]
|}


==== directorlinkmusicvideo ====
This table links directors to music videos.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idDirector || integer || Foreign key to [[The XBMC Database#actors|actors table]]
|-
|idMVideo || integer || Foreign key to [[The XBMC Database#musicvideo|musicvideo table]]
|}


==== directorlinktvshow ====
This table links directors to TV shows.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idDirector || integer || Foreign key to [[The XBMC Database#actors|actors table]]
|-
|idShow || integer || Foreign key to [[The XBMC Database#tvshow|tvshow table]]
|}


==== episode ====
== See also ==
This table stores television episode information. Information concerning the series is stored in [[The XBMC Database#tvshow|tvshow]]. To link an episode to its parent series, use [[The XBMC Database#tvshowlinkepisode|tvshowlinkepisode]].
* [[Releases]]
{|class="wikitable"
! 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 minutes
|-
|c10 || text ||  Director
|-
|c11 || text ||  [unknown - listed as Indentifier]
|-
|c12 || text ||  Season Number
|-
|c13 || text ||  Episode Number
|-
|c14 || text ||  [unknown - listed as Original Title, unused?]
|-
|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 [[The XBMC Database#files|files table]]
|}


==== files ====
This table stores filenames and links the path.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idFile || integer || Primary Key
|-
|idPath || integer || Foreign key to [[The XBMC Database#path|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 [[The XBMC Database#movie|movie]] and [[The XBMC Database#tvshow|tvshow]], so a join isn't necessary.
{|class="wikitable"
! 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.)
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idGenre || integer || Foreign key to [[The XBMC Database#genre|genre table]]
|-
|idMovie || integer || Foreign key to [[The XBMC Database#movie|movie table]]
|}


==== genrelinkmusicvideo ====
This table links genres to music videos.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idGenre || integer || Foreign key to [[The XBMC Database#genre|genre table]]
|-
|idMVideo || integer || Foreign key to [[The XBMC Database#musicvideo|musicvideo table]]
|}


==== genrelinktvshow ====
This table links genres to TV show. (The contents are also stored in tvshow.c08, though.)
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idGenre || integer || Foreign key to [[The XBMC Database#genre|genre table]]
|-
|idShow || integer || Foreign key to [[The XBMC Database#tvshow|tvshow table]]
|}


==== movie ====
This table stores movie information.
{|class="wikitable"
! 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[http://trac.xbmc.org/changeset/29886/trunk]
|-
|c23 || text ||  idPath
|-
|idFile || integer ||  Foreign Key to [[The XBMC Database#files|files table]]
|}
==== movielinktvshow ====
This table links movies to TV shows.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idMovie || integer || Foreign key to [[The XBMC Database#movie|movie table]]
|-
|idShow || integer || Foreign key to [[The XBMC Database#tvshow|tvshow table]]
|}
==== musicvideo ====
{|class="wikitable"
! 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 [[The XBMC Database#files|files table]]
|}
==== path ====
This table stores path information.
{|class="wikitable"
! 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.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idFile || integer || Foreign Key to [[The XBMC Database#files|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).
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idFile || integer || Foreign key to [[The XBMC Database#files|files table]]
|-
|times|| text || Times
|}
==== streamdetails ====
This table contains information regarding codecs used, aspect ratios etc
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idFile || integer || Foreign Key to [[The XBMC Database#files|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.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idStudio || integer || Primary Key
|-
|strStudio || text || Studio Label
|}
==== studiolinkmovie ====
This table links studios to movies.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idStudio || integer || Foreign key to [[The XBMC Database#studio|studio table]]
|-
|idMovie || integer || Foreign key to [[The XBMC Database#movie|movie table]]
|}
==== studiolinkmusicvideo ====
This table links studios to music videos.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idStudio || integer || Foreign key to [[The XBMC Database#studio|studio table]]
|-
|idMVideo || integer || Foreign key to [[The XBMC Database#movievideo|movievideo table]]
|}
==== tvshow ====
This table stores information about a television series. Information concerning the show's episodes is stored in [[The XBMC Database#episode|episode]]. To link a TV show to its episodes, use [[The XBMC Database#tvshowlinkepisode|tvshowlinkepisode]].
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idShow || integer || Primary Key
|-
|c00 || text ||  Show Title
|-
|c01 || text || Show Plot Summary
|-
|c02 || text ||  Status
|-
|c03 || text ||  Votes
|-
|c04 || text ||  Rating
|-
|c05 || text ||  First Aired
|-
|c06 || text ||  Thumbnail URL
|-
|c07 || text ||  [unknown - Spoof Thumbnail URL?]
|-
|c08 || text ||  Genre
|-
|c09 || text ||  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.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idShow || integer || Foreign Key to [[The XBMC Database#tvshow|tvshow table]]
|-
|idEpisode || integer || Foreign Key to [[The XBMC Database#episode|episode table]]
|}
==== tvshowlinkpath ====
This table links a TV show to its path.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idShow || integer || Foreign key to [[The XBMC Database#tvshow|tvshow table]]
|-
|idPath || integer || Foreign key to [[The XBMC Database#path|path table]]
|}
==== version ====
This table stores database information.
{|class="wikitable"
! 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.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idWriter || integer || Foreign key to [[The XBMC Database#actors|actors table]]
|-
|idEpisode || integer || Foreign key to [[The XBMC Database#episode|episode table]]
|}
==== writerlinkmovie ====
This table links writers to movies.
{|class="wikitable"
! Column Name || Data Type || Description
|-
|idWriter || integer || Foreign key to [[The XBMC Database#actors|actors table]]
|-
|idMovie || integer || Foreign key to [[The XBMC Database#movie|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 [[The XBMC Database#view|view]].
==== version ====
This table stores database information.
{|class="wikitable"
! 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.
{|class="wikitable"
! 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)
|}


{{Top}}


==See also==
* [[HOW-TO:Synchronize multiple XBMC libraries]]


{{updated|18}}
[[Category:PVR]]
[[Category:Index]]
[[Category:Manual]]
[[Category:Add-ons]]
[[Category:Live_TV]]
[[Category:Karellen]]
[[Category:Development]]
[[Category:Development]]
[[Category:XBMC Manual]]
[[Category:Video library]]
[[Category:Music library]]
[[Category:Advanced topics]]

Revision as of 21:11, 18 June 2020

Kodi Databases

See also:

Home icon grey.png   ▶ Development
▶ Userdata
▶ Databases



Introduction

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

Performing certain actions on video files will also add content to the database even if it has not been scanned or scraped. For example if you change any OSD setting while watching it, this will be saved in the database as a preference. Resume points are also stored in this database for unscraped videos.


Using the Databases

Databases are automatically maintained whenever you use Kodi. You can activate the most powerful database functionality by setting the Content property on all of your media sources, and using Kodi 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.

The following sections detail the database structure, tables, fields and their properties.


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

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


Tags

Incorporated into the tables below are the related music metadata tags that are read from music files, and the NFO XML tags exported and imported by Kodi.


Database Versions

The following tables shows what database version is used for various Kodi versions. This can be useful to see what versions of Kodi can use MySQL sharing.

Kodi version Date MyVideos MyMusic Textures Addons ViewModes TV EPG ADSP
v10 - Dharma December 2010 37 7 1 1 1 N/A N/A N/A
v11 - Eden March 2012 60 18 6 15 4 N/A N/A N/A
v12 - Frodo January 2013 75 32 13 15 4 22 7 N/A
v13 - Gotham May 2014 78 46 13 16 6 22 7 N/A
v14 - Helix December 2014 90 48 13 16 6 26 8 N/A
v15 - Isengard July 2015 93 52 13 19 6 29 10 N/A
v16 - Jarvis February 2016 99 56 13 20 6 29 11 0
v17 - Krypton February 2017 107 60 13 27 6 29 11 0
v18 - Leia January 2019 116 72 13 27 6 32 12 0
v19 - Matrix June 2020 117 77 13 27 6 36 13 0
Legend
Green Current release
Blue Future release



Links


See also




Return to top