Databases: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
>Alexpoet
(Added video modes database description)
(v21 Updates)
 
(186 intermediate revisions by 39 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==
= Introduction =
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.
<section begin="intro" />Kodi uses [http://www.sqlite.org/ SQLite], an open source light-weight SQL database-engine, to store all the library, add-on, EPG, textures and PVR related data using a number of specific databases.<section end="intro" /> By default, the database files (*.db) are stored in the '''''[[Kodi data folder]]\[[Userdata]]''''' folder, specifically in \Database\.


==Database Structure==
===Music Database===
Stored in userdata/Database/MyMusic7.db


===Video Database===
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.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|idActor || integer || Foreign key to [http://wiki.xbmc.org/?title=The_XBMC_Database#actors actors table]
|-
|idEpisode || integer || Foreign key to episode table
|-
|strRole || text || Role the actor played in this episode
|}


=====actorlinkmovie=====
= Using the Databases =
This table links actors to movies and stores role information.
The databases are automatically maintained by {{Kodi}}. It is recommended that {{kodi}} users do not access the database without good working knowledge of relational databases and SQL.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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=====
Database software is required to access the SQL databases. '''[https://sqlitebrowser.org/ DB Browser for SQLite]''' is available for Windows, macOS, Linux and FreeBSD.
This table stores actor, artist, director, and writer information.
{|class="usertable" border="1"
! 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.
{|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=====
= Accessing the Databases with Kodi Python =
This table links directors to tv show episodes.
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 API]]'''
{|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=====
This table links directors to movies.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|idDirector || integer || Foreign key to actors table
|-
|idMVideo || integer || Foreign key to musicvideo table
|}


=====directorlinktvshow=====
= Database Versions =
This table links directors to tv shows.
<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
|-
|idShow || integer || Foreign key to tvshow table
|}


=====episode=====
{|class="prettytable"
This table stores television episode information.
! Kodi version !! Date !! MyVideos !! MyMusic !! Textures !! Addons !! ViewModes !! TV !! EPG !! ADSP
{|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]
|-
|c12 || text ||  Season
|-
|-
|c13 || text ||  Episode Number
| 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
|-
|-
|c14 || text ||  [unknown]
| 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
|-
|-
|c15 || text ||  [unknown]
| 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
|-
|-
|c16 || 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
|-
|-
|c17 || 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
|-
|-
|c18 || 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
|-
|-
|c19 || 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
|-
|-
|c20 || 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
|-
|-
|idFle || integer ||  Foreign key to the files table
| style="width:120px;" | v18 - Leia
|}
| style="width:120px;" | January 2019
 
| style="width:80px; text-align:center;" | 116
=====files=====
  | style="width:80px; text-align:center;" | 72
This table stores filenames and links the path.
| style="width:80px; text-align:center;" | 13
{|class="usertable" border="1"
| 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;" | 32
| style="width:60px; text-align:center;" | 12
| style="width:60px; text-align:center;" | 0
|-
|-
|idFile || integer || Primary Key
| style="width:120px;" | v19 - Matrix
| style="width:120px;" | December 2020
| style="width:80px; text-align:center;" | 119
| style="width:80px; text-align:center;" | 82
| style="width:80px; text-align:center;" | 13
| style="width:80px; text-align:center;" | 33
| style="width:80px; text-align:center;" | 6
| style="width:60px; text-align:center;" | 38
| style="width:60px; text-align:center;" | 13
| style="width:60px; text-align:center;" | N/A
|-
|-
|idPath || integer || Foreign key to path table
| style="width:120px; background: #a0e75a;" | v20 - Nexus
| style="width:120px; background: #a0e75a;" | 11 January 2023
| style="width:80px; text-align:center; background: #a0e75a;" | 121
| style="width:80px; text-align:center; background: #a0e75a;" | 82
| style="width:80px; text-align:center; background: #a0e75a;" | 13
| style="width:80px; text-align:center; background: #a0e75a;" | 33
| style="width:80px; text-align:center; background: #a0e75a;" | 6
| style="width:60px; text-align:center; background: #a0e75a;" | 40
| style="width:60px; text-align:center; background: #a0e75a;" | 16
| style="width:60px; text-align:center; background: #a0e75a;" | N/A
|-
|-
|strFilename || text || Full name of file including extension
| style="width:120px; background: skyBlue;" | v21 - Omega
| style="width:120px; background: skyBlue;" | 2 April 2024
| style="width:80px; text-align:center; background: skyBlue;" | 131
| style="width:80px; text-align:center; background: skyBlue;" | 83
| style="width:80px; text-align:center; background: skyBlue;" | 13
| style="width:80px; text-align:center; background: skyBlue;" | 33
| style="width:80px; text-align:center; background: skyBlue;" | 6
| style="width:60px; text-align:center; background: skyBlue;" | 44
| style="width:60px; text-align:center; background: skyBlue;" | 16
| style="width:60px; text-align:center; background: skyBlue;" | N/A
|}
|}


=====genre=====
{| class="prettytable"
This table stores Genre information.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|idGenre || integer || Primary Key
|-
|-
|strGenre || text || Genre label
! colspan="2"| Legend
|}
 
=====genrelinkmovie=====
This table links genres to movies.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|-
|idGenre || integer || Foreign key to genre table
| style="background:#a0e75a;"| Green
| Current release
|-
|-
|idMovie || integer || Foreign key to movie table
| style="background:skyBlue;"| Blue
| Future release
|}
|}
<section end="database versions" />


=====genrelinkmusicvideo=====
= See also =
This table links genres to music videos.
* [[Releases]]
{|class="usertable" border="1"
* 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
|-
|idGenre || integer || Foreign key to genre table
|-
|idMVideo || integer || Foreign key to musicvideo table
|}


=====genrelinktvshow=====
This table links genres to tv show.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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="usertable" border="1"
! 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.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|idFile || integer || Foreign key to files table
|-
|times|| text || Times
|}
=====studio=====
This table stores studio information.
{|class="usertable" border="1"
! Column Name || Data Type || Description
|-
|idStudio || integer || Primary Key
|-
|strStudio || text || Studio Label
|}
=====studiolinkmovie=====
This table links studios to movies.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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.
{|class="usertable" border="1"
! 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="usertable" border="1"
! Column Name || Data Type || Description
|-
|idWriter || integer || Foreign key to actors table
|-
|idEpisode || integer || Foreign key to episodes table
|}
=====writerlinkmovie=====
This table links writers to movies.
{|class="usertable" border="1"
! 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.


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


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


[[Category:Inner Workings]]
[[Category:PVR]]
[[category:Development]]
[[Category:Add-ons]]
[[Category:To-Do]]
[[Category:Karellen]]
[[category:Library]]
[[Category:Development]]
[[category:Video Library]]
[[Category:Video library]]
[[category:Music Library]]
[[Category:Music library]]
[[Category:Skin_development]]

Latest revision as of 22:08, 1 April 2024

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 the library, add-on, EPG, textures and PVR related data using a number of specific databases. By default, the database files (*.db) are stored in the Kodi data folder\Userdata folder, specifically in \Database\.


Using the Databases

The databases are automatically maintained by Kodi. It is recommended that Kodi users do not access the database without good working knowledge of relational databases and SQL.

Database software is required to access the SQL databases. DB Browser for SQLite is available for Windows, macOS, Linux and FreeBSD.


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 API


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 December 2020 119 82 13 33 6 38 13 N/A
v20 - Nexus 11 January 2023 121 82 13 33 6 40 16 N/A
v21 - Omega 2 April 2024 131 83 13 33 6 44 16 N/A
Legend
Green Current release
Blue Future release


See also



Return to top
Retrieved from "https://kodi.wiki/index.php?title=Databases&oldid=250339"