Databases: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
>Alexpoet
(Typo)
(210 intermediate revisions by 36 users not shown)
Line 1: Line 1:
{{Incomplete}}
{{Template:DatabasesNavBox}}
=XBMC Database Details=
{{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 $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.


==Accessing the Database via 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]].


==Accessing the Database via 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.


==Database Structure==
= Introduction =
===Music Database===
<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.
Stored in userdata/Database/MyMusic7.db


===Video 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.<section end="intro" />
Stored in userdata/Database/MyVideos34.db
 
====Tables====
 
=====actorlinkepisode=====
 
This table links actors to episodes and stores role information.
= Using the Databases =
{|class="usertable" border="1"
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.
! Column Name || Data Type || Description
 
|-
The following sections detail the database structure, tables, fields and their properties.
|idActor || integer || Foreign key to actors table
 
|-
 
|idEpisode || integer || Foreign key to episode table
 
|-
== Building SQL Queries ==
|strRole || text || Role the actor played in this episode
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].
|}
 
=====actorlinkmovie=====
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:
This table links actors to movies and stores role information.
* A list of all the data fields (columns in the database table) you want for each row.
{|class="usertable" border="1"
* A list of all the tables you need to get information from
! Column Name || Data Type || Description
* 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.
|-
 
|idActor || integer || Foreign key to actors table
Below are a few sample select statements, so you can see how it works.
|-
 
|idMovie || integer || Foreign key to movie table
This query grabs all of the information for every movie in the Video Library.
|-
<pre><nowiki>select * from movie</nowiki></pre>
|strRole || text || Role the actor played in this 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.
=====actorlinktvshow=====
 
This table links actors to tv shows and stores role information.
This query narrows down the results to just those movies released in 2007.  
{|class="usertable" border="1"
<pre><nowiki>select * from movie where c07 = 2007</nowiki></pre>
! Column Name || Data Type || Description
 
|-
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.
|idActor || integer || Foreign key to actors table
 
|-
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.
|idMovie || integer || Foreign key to tv show table
<pre><nowiki>select idMovie,idFile,c05,c07,c09,c00,c03,c02 from movie;</nowiki></pre>
|-
 
|strRole || text || Role the actor played in this tv show
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>
=====actors=====
 
This table stores actor, artist, director, and writer information.
You could also use less than or greater than symbols to get newer or older movies.
{|class="usertable" border="1"
 
! Column Name || Data Type || Description
This query gets just the path and filename of all of the video files from season two of Chuck.
|-
<pre><nowiki></nowiki></pre>
|idActor || integer || Primary Key
 
|-
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.
|strActor|| integer || Name of the actor, artist, director, or writer
 
|-
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.
|strThumb || text || Thumbnail URL
 
|}
 
 
== 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.
{{see also | Music tagging | Import-export library | NFO files}}


=====artistlinkmusicvideo=====
This table links artists to music videos.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|idArtist || integer || Foreign key to actors table
|-
|idMVideo || integer || Foreign key to musicvideo table
|}


=====bookmark=====
This table stores bookmarks.
{|class="usertable" border="1"
! 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=====
= Database Versions =
This table links directors to tv show episodes.
<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.
{|class="usertable" border="1"
! 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=====
{|class="prettytable"
[TODO]
! Kodi version !! Date !! MyVideos !! MyMusic !! Textures !! Addons !! ViewModes !! TV !! EPG !! ADSP
=====directorlinkmusicvideo=====
[TODO]
=====directorlinkmusictvshow=====
[TODO]
=====episode=====
This table stores television episode information.
{|class="usertable" border="1"
! 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]
| 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
|-
|-
|c12 || text ||  Season
| style="width:120px;" | v11 - Eden
| style="width:120px;" | March 2012
| style="width:80px; text-align:center;" | 60
| style="width:80px; text-align:center;" | 18
| style="width:80px; text-align:center;" | 6
| style="width:80px; text-align:center;" | 15
  | style="width:80px; text-align:center;" | 4
| style="width:60px; text-align:center;" | N/A
| style="width:60px; text-align:center;" | N/A
| style="width:60px; text-align:center;" | N/A
|-
|-
|c13 || text ||  Episode Number
| 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
|-
|-
|c14 || text ||  [unknown]
| 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
|-
|-
|c15 || text ||  [unknown]
| style="width:120px;" | v14 - Helix
| style="width:120px;" | December 2014
| style="width:80px; text-align:center;" | 90
| style="width:80px; text-align:center;" | 48
| 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;" | 26
| style="width:60px; text-align:center;" | 8
| style="width:60px; text-align:center;" | N/A
|-
|-
|c16 || text ||  [unknown]
| 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
|-
|-
|c17 || text ||  [unknown]
| 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
|-
|-
|c18 || text ||  [unknown]
| style="width:120px;" | v17 - Krypton
| style="width:120px;" | February 2017
| style="width:80px; text-align:center;" | 107
| style="width:80px; text-align:center;" | 60
| style="width:80px; text-align:center;" | 13
| style="width:80px; text-align:center;" | 27
  | 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
|-
|-
|c19 || text ||  [unknown]
| 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
|-
|-
|c20 || text ||  [unknown]
| style="width:120px; background: skyBlue;" | v19 - Matrix
|-
| style="width:120px; background: skyBlue;" | January 2020
|idFle || integer ||  Foreign key to the file for this episode
| style="width:80px; text-align:center; background: skyBlue;" | 116
| style="width:80px; text-align:center; background: skyBlue;" | 75
  | 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
|}
|}
=====files=====
 
[TODO]
{| class="prettytable"
=====genre=====
[TODO]
=====genrelinkmovie=====
[TODO]
=====genrelinkmusicvideo=====
[TODO]
=====genrelinktvshow=====
[TODO]
=====movie=====
[TODO]
=====movielinktvshow=====
[TODO]
=====musicvideo=====
[TODO]
=====path=====
[TODO]
=====settings=====
[TODO]
=====stacktimes=====
[TODO]
=====studio=====
[TODO]
=====studiolinkmovie=====
[TODO]
=====studiolinkmusicvideo=====
[TODO]
=====tvshow=====
This table stores television show information.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|-
|idShow || integer || Primary Key
! colspan="2"| Legend
|-
|-
|c00 || text ||  Show Title
| style="background:#a0e75a;"| Green
| Current release
|-
|-
|c01 || text || Show Summary
| style="background:skyBlue;"| Blue
|-
| Future release
|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 =====
<section end="database versions" />
This table is a simple linker table to join TV Shows and Episodes.
 
{|class="usertable" border="1"
 
! Column Name || Data Type || Description
 
|-
== Links ==
|idShow || integer || Foreign Key to tvshow table
* http://www.rieter.net/content/xot/xbmc-database-versions/ - another site keeping track of Kodi DB changes
|-
* https://github.com/xbmc/xbmc/commits/master/xbmc/video/VideoDatabase.cpp - Tracking the Video Database schema version
|idEpisode || integer || Foreign Key to episode table
* https://github.com/xbmc/xbmc/commits/master/xbmc/music/MusicDatabase.cpp - Tracking the Music Database schema version
|}
 
=====tvshowlinkpath=====
 
[TODO]
 
=====version=====
== See also ==
[TODO]
* [[Releases]]
=====writerlinkepisode=====
 
[TODO]
 
=====writerlinkmovie=====
 
[TODO]
 
 


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


===View Modes Database===
{{Top}}
Stored in userdata/Database/ViewModes.db


=TO-DO (what needs to be added to this database article)=
*The layout, etc. will be explained in more detail here later.


[[Category:Inner Workings]]
{{updated|18}}
[[category:Development]]
[[Category:PVR]]
[[Category:To-Do]]
[[Category:Index]]
[[category:Library]]
[[Category:Manual]]
[[category:Video Library]]
[[Category:Add-ons]]
[[category:Music Library]]
[[Category:Live_TV]]
[[Category:Karellen]]
[[Category:Development]]
[[Category:Video library]]
[[Category:Music library]]
[[Category:Advanced topics]]

Revision as of 06:55, 7 May 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 January 2020 116 75 13 27 6 36 13 0
Legend
Green Current release
Blue Future release



Links


See also




Return to top