Databases: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
(Replace Video Tables)
No edit summary
(19 intermediate revisions by 4 users not shown)
Line 1: Line 1:
{{mininav| [[Development]] {{l2| [[userdata folder]] }} }}
{{Template:DatabasesNavBox}}
{{see also|Databases/Versions | nfo}}
{{mininav| [[Development]] {{l2| [[Userdata]] }} }}
{{TOC right}}




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


In addition to indexing media files when activated by user-selected Content settings, Kodi 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.<section end="intro" />


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


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 ==
 
 
= Using the Databases =
Databasess 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.
Databasess 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.


Since Kodi 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 Kodi's databases, and give some brief examples of how to use it.
The following sections detail the database structure, tables, fields and their properties.






=== Building SQL Queries ===
== 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 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:
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 30: 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 labelled 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.  


Line 46: 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.


Line 52: Line 56:




=== Accessing the Databases with Kodi Python ===
== 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]]
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 ===
== 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.
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}}
{{see also | Music tagging | Import-export library | NFO files}}
 
 
 
== 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 Kodi.
 
The music database is stored in userdata/Database/MyMusicXX.db, where XX is the version number.
 
{{red|The current v17 database is MyMusic60.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.




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


==== albumartistview ====
This table combines album with each album artist.
{|class="prettytable"
{|class="prettytable"
! Column Name || Data Type || Description
! Kodi version !! Date !! MyVideos !! MyMusic !! Textures !! Addons !! ViewModes !! TV !! EPG !! ADSP
|-
|idAlbum || integer || ID from album table
|-
|idArtist || integer || ID from artist table
|-
|idrole || integer || = 0 (fake role ID field)
|-
|strRole || text || = "AlbumArtist" (fake role desc field)
|-
|strArtist || varchar(256) || Artist name
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID
|-
|iOrder || integer || Order artist appears in credits
|}
 
 
 
==== albumview ====
A view of the album table with some additional fields aggregated from the songs of that album.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idAlbum || integer || ID from Album Table
|-
|strAlbum || varchar(256) || Album Name
|-
|strMusicBrainzAlbumID || text || MusicBrainz album ID
|-
|strArtists || text || Album artist(s) description (not always a concatenation of names)
|-
|strGenres || text || Album genres (denormalised concatenation of values in genre table)
|-
|iYear || integer || Album release year
|-
|strMoods || text || Album moods
|-
|strStyles || text || Album styles
|-
|strThemes || text || Album themes
|-
|strReview || text || Album review
|-
|strLabel || text || Album recording label
|-
|strType || text || Album Type e.g. "Soundtrack / Live"
|-
|strImage || text || URLs for available remote album images
|-
|fRating || float || Album rating
|-
|iUserrating || integer || Album user rating (out of 10)
|-
|iVotes || integer || Votes (for album rating)
|-
|bCompilation || integer || Compilation flag
|-
|iTimesPlayed || integer ||Average of number of times songs on album are played
|-
|strReleaseType || text || Internal type - "album" or "single"
|-
|dateAdded || text || Music file timestamp of newest song on album
|-
|lastPlayed || text || Last date any song on album was played
|}
 
 
 
==== artistview ====
A view of the artist table with an additional field aggregated from the songs by that artist.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idArtist || integer || ID from artist table
|-
|strArtist || varchar(256) || Artist name
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID
|-
|strBorn || text || Artist birthday (and place)
|-
|strFormed || text || Band formation information
|-
|strGenres || text || Artist genres (just text, no link to genre table)
|-
|strMoods || text || Artist moods
|-
|strStyles || text || Artist styles
|-
|strInstruments || text || Artist instruments
|-
|strBiography || text || Artist biography
|-
|strDied || text || Date artist died (and place)
|-
|strDisbanded || text || Band disbanded information
|-
|strYearsActive || text || Years artist is active
|-
|-
|strImage || text || URLs of available artist images
| 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
|-
|-
|strFanart || text || URLs of available artist fanart
| 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
|-
|-
|dateAdded || text || Music file timestamp of newest song by artist
| 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
==== songartistview ====
| style="width:80px; text-align:center;" | 15
A view that joins song to artist and artist role.
| style="width:80px; text-align:center;" | 4
{|class="prettytable"
| style="width:60px; text-align:center;" | 22
! Column Name || Data Type || Description
| style="width:60px; text-align:center;" | 7
| style="width:60px; text-align:center;" | N/A
|-
|-
|idSong || integer || ID from song 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
|-
|-
|idArtist || integer || ID from artist table
| 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
|-
|-
|idrole || integer || ID from role 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
|-
|-
|strRole || text || Description of role
| 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
|-
|-
|strArtist || varchar(256) || Artist name
| 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
|-
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID Hash
| style="width:120px;" | v18 - Leia
| style="width:120px;" | January 2019
| style="width:80px; text-align:center;" | 116
| style="width:80px; text-align:center;" | 72
| 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;" | 32
| style="width:60px; text-align:center;" | 12
| style="width:60px; text-align:center;" | 0
|-
|-
|iOrder || integer || Order artist appears in song credits
| style="width:120px; background: salmon;" | v19 - Matrix
| style="width:120px; background: salmon;" | July 2019
| style="width:80px; text-align:center; background: salmon;" | 116
| style="width:80px; text-align:center; background: salmon;" | 72
| style="width:80px; text-align:center; background: salmon;" | 13
| style="width:80px; text-align:center; background: salmon;" | 27
| style="width:80px; text-align:center; background: salmon;" | 6
| style="width:60px; text-align:center; background: salmon;" | 34
| style="width:60px; text-align:center; background: salmon;" | 12
| style="width:60px; text-align:center; background: salmon;" | 0
|}
|}


 
{| class="prettytable"
 
==== songview ====
A view that joins song to album and path.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idSong || integer || ID from song table
|-
|strArtists || text || Song artist(s) description (not always a concatenation of names)
|-
|strGenres || text || Song Genres (denormalised concatenation of values in genre table)
|-
|strTitle || varchar(512) || Song title
|-
|-
|iTrack || integer || Song track number
! colspan="2"| Legend
|-
|-
|iDuration || integer || Song duration in seconds
| style="background:salmon;"| Red
|-
| In development and subject to change without notice
|iYear || integer || Songs release year
|-
|strFileName || text || Name of music file
|-
|strMusicBrainzTrackID || text || Musicbrainz track ID
|-
|iTimesPlayed || integer || Play count
|-
|iStartOffset || integer || Start position (in seconds) when song from cuesheet
|-
|iEndOffset || integer  || End position (in seconds) when song from cuesheet
|-
|lastplayed || varchar(20) || Date & time last played
|-
|rating || char || Song rating
|-
|userrating || integer || Song rated by user (out of 10)
|-
|votes || integer || Votes for song rating
|-
|comment || text || Song description or comment
|-
|idAlbum || integer || ID from album table
|-
|strAlbum || varchar(256) || Album title
|-
|strPath || varchar(512) || Full path for music file
|-
|bCompilation || integer || Compilation flag (song is on a compilation album)
|-
|strAlbumArtists || text || Album artist(s) description (not always a concatenation of names)
|-
|strAlbumReleaseType || text || Internal type - "album" or "single"
|-
|mood || text || Song mood
|-
|dateAdded || text ||  Music file timestamp
|}
|}
<section end="database versions" />






=== Tables ===
== Links ==
* 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
* https://github.com/xbmc/xbmc/commits/master/xbmc/music/MusicDatabase.cpp - Tracking the Music Database schema version




==== album ====
An album. This equates to what Musicbrainz call a "release" and represents the unique release (i.e. issuing) of an item on a specific date with specific release information such as the country, label, etc. Hence album title and artist do not have to be unique as long as we have the release ID (Musicbrainz album ID), Kodi can handle different versions of the same album e.g. original release and then a later remaster (both same title and artist).


{|class="prettytable"
== See also ==
! Column Name || Data Type || Description |||| [[Music_tagging#Data_Kodi_Scrapes|MusicFileTag]] ** || NFO (xml) Tag
* [[Releases]]
|-
|idAlbum || integer || ID from Album Table ||||
|-
|strAlbum || varchar(256) || Album Name |||| ALBUM || <title></title>
|-
|strMusicBrainzAlbumID || text || MusicBrainz album ID |||| MUSICBRAINZ_ALBUMID || <musicBrainzAlbumID></musicBrainzAlbumID>
|-
|strArtists || text || Album artist(s) description (not always a concatenation of names) |||| ALBUMARTIST,  <br />
ALBUMARTISTS, <br />
ARTIST and ARTISTS
|| <artistdesc></artistdesc>
|-
|strGenres || text || Album genres (denormalised concatenation of values in genre table) |||| GENRE || <genre></genre> ^^
|-
|iYear || integer || Album release year |||| YEAR or DATE || <year></year>
|-
|idThumb || integer || [Unused] ||||
|-
|bCompilation || integer || Compilation flag |||| COMPILATION || <compilation></compilation>
|-
|strMoods || text || Album moods ||||  || <mood></mood> ^^
|-
|strStyles || text || Album styles ||||  || <style></style> ^^
|-
|strThemes || text || Album themes ||||  || <theme></theme> ^^
|-
|strReview || text || Album review ||||  || <review></review>
|-
|strImage || text || URLs for available remote album images ||||  || <thumb preview="path"></thumb>
|-
|strLabel || text || Album recording label |||| LABEL || <label></label>
|-
|strType || text || Album Type e.g. "Soundtrack / Live" |||| RELEASETYPE || <type></type>
|-
|fRating || float || Album rating ||||  || <rating max="10"></rating>
|-
|iUserrating || integer || Album user rating (out of 10) ||||  || <userrating max="10"></userrating>
|-
|lastScraped || text || Date/time data scraped from online sources or NFO files ||||
|-
|strReleaseType || text || Internal type - "album" or "single" ||||  || <releasetype></releasetype>
|-
|iVotes || integer || Votes (for album rating) ||||  || <votes></votes>
|}
 
{|class="prettytable"
!'''Notes'''
|-
|** || MusicFileTag column shows related Vorbis/Flac tags. Click on column heading for other tag types
|-
|^^ || Multiple entries of the XML tag are allowed
|}
 
 
 
==== album_artist ====
This table links artists and albums
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idArtist || integer || Foreign key to [[Databases#artist|artist table]]
|-
|idAlbum || integer || Foreign key to [[Databases#album|album table]]
|-
|iOrder || integer || Order of artist names for credits
|-
|strArtist || text || Artist name (denormalised)
|}
 
 
 
==== album_genre ====
This links albums with their genres.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idGenre || integer || Foreign key to [[Databases#genre(music)|genre(music) table]]
|-
|idAlbum || integer || Foreign key to [[Databases#album|album table]]
|-
|iOrder || integer || Order of genre for concatentation
|}
 
 
 
==== albuminfosong ====
Track listing information for the tracks on an album. This is returned when additional information for an album is scraped from online sources or NFO files. It has no relationship with the songs actually in the user's music collection, it is just more descriptive details about the album.
 
{|class="prettytable"
! Column Name || Data Type || Description |||| NFO (xml) Tag
|-
|idAlbumInfoSong || integer || Primary Key ||  ||
|-
|idAlbumInfo || integer || Foreign key to [[Databases#album|album table]] ||||
|-
|iTrack || integer || Track number ||||
<track>
  <position></position>
</track>
|-
|strTitle || text || Track title ||||
<track>
  <title></title>
</track>
|-
|iDuration || integer || Track duration in seconds ||||
<track>
  <duration></duration>
</track>
|}
 
 
 
==== art ====
The current artwork for artists and albums
 
{|class="prettytable"
! Column Name || Data Type || Description ||||  NFO (xml) Tag
|-
|art_id || integer || Primary Key ||||
|-
|media_id || integer || Foreign key to [[Databases#album|album]] or [[Databases#artist|artist table]] ||||
|-
|media_type || text || "album" or "artist" ||||
|-
|type || text || art type e.g. "thumb", "fanart" etc. ||||
  <fanart></fanart>
OR 
  <thumb></thumb>
|-
|url || text || URL of image (can be remote, local or music file with art embedded) ||||
<art>
  <fanart>E:\Music\U2\fanart.jpg</fanart>
  <thumb>E:\Music\U2\folder.jpg</thumb>
</art>
|}
 
 
 
==== artist ====
An artist is generally a musician, group of musicians, or other music professional (like a producer or engineer). E.g. Bob Dylan, Pink Floyd, London Symphony Orchestra, Andrew Collins (producer).
 
{|class="prettytable"
! Column Name || Data Type || Description |||| [[Music_tagging#Data_Kodi_Scrapes|MusicFileTag]] ** || NFO (xml) Tag
|-
|idArtist || integer || ID from artist table ||||
|-
|strArtist || varchar(256) || Artist name |||| ARTIST, ARTISTS, <br /> ALBUMARTIST and <br />
ALBUMARTISTS
 
|| <name></name>
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID |||| MUSICBRAINZ_ARTISTID || <musicBrainzArtistID></musicBrainzArtistID>
|-
|strBorn || text || Artist birthday (and place) ||||  || <born></born>
|-
|strFormed || text || Band formation information ||||  || <formed></formed>
|-
|strGenres || text || Artist genres (just text, no link to genre table)  ||||  || <genre></genre> ^^
|-
|strMoods || text || Artist moods ||||  || <mood></mood> ^^
|-
|strStyles || text || Artist styles ||||  || <style></style> ^^
|-
|strInstruments || text || Artist instruments ||||  || <instruments></instruments>  ^^
|-
|strBiography || text || Artist biography ||||  || <biography></biography>
|-
|strDied || text || Date artist died (and place) ||||  || <died></died>
|-
|strDisbanded || text || Band disbanded information ||||  || <disbanded></disbanded>
|-
|strYearsActive || text || Years artist is active ||||  || <yearsactive></yearsactive> ^^
|-
|strImage || text || URLs of available artist images ||||  || <thumb preview=" "></thumb> ^^
|-
|strFanart || text || URLs of available artist fanart ||||  || <fanart><thumb preview=""></thumb></fanart> ^^
|-
|lastScraped || varchar(20) || Date last scraped metadata
|}
 
{|class="prettytable"
!'''Notes'''
|-
|** || MusicFileTag column shows Vorbis/Flac tags. Click on column heading for other tag types
|-
|^^ || Multiple entries of the tag are allowed
|}
 
 
 
==== content ====
The scraper addon and settings used for scraping additional information for specific albums or artists.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|strPath || text || Virtual path encoding what album or artist e.g. "musicdb://artists/235"
|-
|strScraperPath || text || Scraper name e.g. "metadata.artists.universal"
|-
|strContent || text || "artists" or "albums"
|-
|strSettings || text || Scraper settings as XML
|}
 
 
 
==== cue ====
Cuesheet information that has been extracted from a FLAC file. Generally Cuesheets as separate files are not handled in this way, only those embedded in FLAC files.
 
{|class="prettytable"
! Column Name || Data Type || Description |||| MusicFileTag
|-
|idPath || integer || Foreign key to [[Databases#path(music)|music path]] of where file is located ||||
|-
|strFileName || text || Name of FLAC file the cuesheet is embedded in ||||
|-
|strCuesheet || text || Cuesheet |||| CUESHEET
|}
 
 
 
==== discography ====
The discography of an artist. This is returned when additional information for an artist is scraped from online sources or NFO files. It has no relationship with the albums actually in the user's music collection, it is just details about the artist.
 
{|class="prettytable"
! Column Name || Data Type || Description |||| NFO (xml) Tag
|-
|idArtist || integer || Foreign key to [[Databases#artist|artist table]] ||||
|-
|strAlbum || text || Album title ||||
<album>
  <title></title>
</album>
|-
|strYear || text || Year album released ||||
<album>
  <year></year>
</album>
|}
 
 
 
==== genre(music) ====
A music genre. This is a lookup table that enumerates the song genre values read from the genre tags embedded in music files e.g. "Progressive Rock", "Baroque" etc. But could contain anything that has been given as a "genre", and is not strictly limited to music genres.
 
{|class="prettytable"
! Column Name || Data Type || Description |||| [[Music_tagging#Data_Kodi_Scrapes|MusicFileTag]] ** || NFO (xml) Tag
|-
|idGenre || integer || Primary Key ||||  ||
|-
|strGenre || varchar(256) || Genre Name |||| GENRE  || <genre></genre> ^^
|}
 
{|class="prettytable"
!'''Notes'''
|-
|** || MusicFileTag column shows Vorbis/Flac tags. Click on column heading for other tag types
|-
|^^ || Multiple entries of the tag are allowed
|}
 
 
 
==== path(music) ====
The path of a folder containing music that has been scanned into the music library. This holds hash values of name, size, and timestamp for each folder and sub folder that is used to determine if the conents have changed and thererfore the music files need rescanning as part of library update.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idPath || integer || Primary Key
|-
|strPath || varchar(512) || Media path
|-
|strHash || text || Hash value
|}
 
 
==== role ====
An artist role, a way that an artist has contributed to or been involved with a song. This could be by being in the song artist credits ("artist"), as a musician ("Guitar", "Orchestra" or "vocals" etc.), as composer, conductor, lyricist etc. or involved in some other way as music professional e.g. producer, engineer, mixer etc.  This is a lookup table that enumerates the various roles that have been read from tags embedded in music files.
 
{|class="prettytable"
! Column Name || Data Type || Description |||| [[Music_tagging#Data_Kodi_Scrapes|MusicFileTag]] **
|-
|idrole || integer || Primary Key ||||
|-
|strRole || text || Description of role ||||
ARTIST, ARRANGER, COMPOSER, CONDUCTOR, <br />
DJMIXER, ENGINEER, LYRICIST, MIXER, <br />
PRODUCER, REMIXER and PERFORMER <br />


|}


{|class="prettytable"
!'''Notes'''
|-
|** || MusicFileTag column shows Vorbis/Flac tags. Click on column heading for other tag types
|}






==== song ====
A song. It is the way that the individual track(s) from a music file are held in the music library. Generally a music file contains just one song, however it may contain more and have an associated cuesheet (separately or embedded) that identifies the individual tracks. A song may be from an album (the release of several tracks as one product so includes EPs etc. ), or a single. Singles from an artist (no album title given in the embedded tags) are internally linked with a fake album in the album table. 
The music library can contain songs with the same title, artist credits, track number and album as long as they come from separate music files. Unexpectedly Musicbrainz Track ID is not unique on an album, recordings are sometimes repeated e.g. "[silence]" or on a disc set.
{|class="prettytable"
! Column Name || Data Type || Description |||| [[Music_tagging#Data_Kodi_Scrapes|MusicFileTag]] **
|-
|idSong || integer || ID from song table ||||
|-
|idAlbum || integer || ID from album table ||||
|-
|idPath || integer || Foreign key to [[Databases#path|path table]] ||||
|-
|strArtists || text || Song artist(s) description (not always a concatenation of names) |||| ARTIST and ARTISTS
|-
|strGenres || text || Song Genres (denormalised concatenation of values in genre table) |||| GENRE
|-
|strTitle || varchar(512) || Song title |||| TITLE
|-
|iTrack || integer || Song disc (upper 4 bytes) and track number (lower 4 bytes)|||| TRACKNUMBER and <br /> DISCNUMBER
|-
|iDuration || integer || Song duration in seconds ||||
|-
|iYear || integer || Songs release year |||| YEAR or DATE
|-
|dwFileNameCRC || text || [unused] ||||
|-
|strFileName || text || Name of music file ||||
|-
|strMusicBrainzTrackID || text || Musicbrainz track ID |||| MUSICBRAINZ_TRACKID
|-
|iTimesPlayed || integer || Play count ||||
|-
|iStartOffset || integer || Start position (in seconds) when song from cuesheet ||||
|-
|iEndOffset || integer  || End position (in seconds) when song from cuesheet ||||
|-
|idThumb || integer || [unused]  ||||
|-
|lastplayed || varchar(20) || Date & time last played ||||
|-
|rating || char || Song rating ||||
|-
|userrating || integer || Song rated by user (out of 10) |||| RATING
|-
|comment || text || Song description or comment |||| COMMENT
|-
|mood || text || Song mood |||| MOOD
|-
|dateAdded || text ||  Music file timestamp ||||
|-
|votes || integer || Votes for song rating ||||
|}
{|class="prettytable"
!'''Notes'''
|-
|** || MusicFileTag column shows Vorbis/Flac tags. Click on column heading for other tag types
|}
==== song_artist ====
This table links songs to artists and the roles they contributed.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idArtist || integer || Foreign key to [[Databases#artist|artist table]]
|-
|idSong || integer || Foreign key to [[Databases#song|song table]]
|-
|idRole || integer || Foreign key to [[Databases#role|role table]]
|-
|iOrder || integer || Order in artist credits
|-
|strArtist || text || Name of artist (denormalised)
|}
==== song_genre ====
This table links the songs with their genres.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idGenre || integer || Foreign key to [[Databases#genre(music)|genre(music) table]]
|-
|idSong || integer || Foreign key to [[Databases#song|song table]]
|-
|iOrder || integer || Genre order when concatenating to single string
|}
==== version(music) ====
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idVersion || integer || Version of the music database
|-
|iCompressCount || integer || Number of times database has been compressed
|}




{{Top}}
{{Top}}
== The Video Library ==
This database contains all information concerning TV shows, movies, and music videos. It is used in the Videos portion of Kodi. The following information is for version 75.
The video database is stored in userdata/Database/MyVideosXX.db, where XX is the version number.
{{red|The current database in use for v17 is MyVideos107.db}}
=== Views ===
Views are standard queries, often long or complicated queries saved in the database for convenience. The views below allow you to easily access all the information about each of the main media types in the Video Library, across all the linking tables.
==== episodeview ====
A view that joins episode to file and tvshow (through tvshowlinkepisode) and path.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idEpisode || integer || Primary Key
|-
|idFile || integer ||  Foreign key to the [[Databases#files|files table]]
|-
|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 ||  Path to episode file
|-
|c19 || text ||  Used- unknown
|-
|c20 || text ||  Used- unknown
|-
|c21 || text ||  Not used
|-
|c22 || text ||  Not used
|-
|c23 || text ||  Not used
|-
|idShow || text ||  Foreign key to the [[Databases#tvshow|tvshow table]]
|-
|userrating || text ||  User Rating
|-
|idSeason || text ||  Foreign key to the [[Databases#seasons|seasons table]]
|-
|strFilename || text || Full name of file including extension
|-
|strPath || text || Path to playable file
|-
|playCount || integer || # of Times Played
|-
|lastPlayed || text || Date & Time Last Played
|-
|dateAdded || text || Date & Time Added to Library
|-
|strTitle || text || Name of program
|-
|genre || text || Genre
|-
|studio || text || Studio
|-
|premiered || text || Premiered Date
|-
|mpaa || text || MPAA Rating
|-
|resumeTimeInSeconds || double || Resume Point
|-
|totalTimeInSeconds || double || Length of video
|-
|rating || float || Rating
|-
|votes || integer || Votes for rating
|-
|rating_type || text || Type of rating
|-
|uniqueid_value || text ||
|-
|uniqueid_type || text ||
|}
==== movieview ====
A view that joins movie to file and path.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idMovie || integer || Primary Key
|-
|idFile || integer ||  Foreign Key to [[Databases#files|files table]]
|-
|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 ||  Image URL
|-
|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 ||  [http://www.imdb.com/chart/top IMDB Top 250] Ranking
|-
|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.kodi.tv/changeset/29886/trunk])
|-
|c22 || text ||  Path to playable file
|-
|c23 || text ||  idPath
|-
|idSet || integer ||  Foreign Key to [[Databases#sets|sets table]]
|-
|userrating || integer ||  Rating applied by user
|-
|premiered || text ||  Date movie premiered
|-
|strSet || text ||  Movie Set
|-
|strSetOverview || text ||  Movie Set plot
|-
|strFilename || text || Full name of file including extension
|-
|strPath || text || Path to playable file
|-
|playCount || integer || # of Times Played
|-
|lastPlayed || text || Date & Time Last Played
|-
|dateAdded || text || Date & Time Added to Library
|-
|resumeTimeInSeconds || double || Resume Point
|-
|totalTimeInSeconds || double || Length of video
|-
|rating || float || Rating
|-
|votes || integer || Votes for rating
|-
|rating_type || text || Type of rating
|-
|uniqueid_value || text ||
|-
|uniqueid_type || text ||
|}
==== musicvideoview ====
A view that joins musicvideo to file and path.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idMVideo || integer || Primary Key
|-
|idFile || integer ||  Foreign Key to [[Databases#files|files table]]
|-
|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
|-
|c13 || text ||
|-
|c14 || text ||
|-
|c15 || text ||
|-
|c16 || text ||
|-
|c17 || text ||
|-
|c18 || text ||
|-
|c19 || text ||
|-
|c20 || text ||
|-
|c21 || text ||
|-
|c22 || text ||
|-
|c23 || text ||
|-
|userrating || integer || User Rating
|-
|premiered || text || Premier of Music Video
|-
|strFileName || text || Full name of file including extension
|-
|strPath || text || Path URL
|-
|playCount || integer || # of Times Played
|-
|lastPlayed || text || Date & Time Last Played
|-
|dateAdded || text || Date & Time Added to Library
|-
|resumeTimeInSeconds || double || Time in seconds of bookmark location
|-
|totalTimeInSeconds || double || Time in seconds of the video
|}




==== tvshowview ====
{{updated|18}}
View that joins tvshow to path. Also produces information about total number of episodes as well as number of watched and unwatched episodes.
[[Category:PVR]]
{|class="prettytable"
! 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]
|-
|c21 || text || [unknown]
|-
|c22 || text || [unknown]
|-
|c23 || text || [unknown]
|-
|userrating || integer || User Rating
|-
|duration || integer || Total duration
|-
|idParentPath || integer ||
|-
|strPath || text || Path URL
|-
|dateAdded || text || Date & Time Added to Library
|-
|lastPlayed || text || Date & Time Last Played
|-
|totalCount || integer || # of Episodes
|-
|watchedcount || integer || # of Times Played
|-
|totalSeasons || integer || # of Seasons
|-
|rating || float || Rating
|-
|votes || integer || Votes for rating
|-
|rating_type || text || Type of rating
|-
|uniqueid_value || text ||
|-
|uniqueid_type || text ||
|}
 
 
 
==== tvshow counts ====
This table stores the TV Show watched count, total seasons, last played and date added data
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idShow || integer || Primary Key
|-
|lastPlayed || text || Date & Time Last Played
|-
|totalCount || integer || # of Episodes
|-
|watchedcount || integer || # of Times Played
|-
|totalSeasons || integer || # of Seasons
|-
|dateAdded || text || Date Added
|}
 
 
 
=== Tables ===
The information in the Video Library is organized into the following tables. Several large tables (such as [[Databases#episode|episode]], [[Databases#movie|movie]], [[Databases#settings|settings]], and [[Databases#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.
 
 
 
==== actor ====
This table stores actor, artist, director, and writer information.
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies<br />TV Show<br />TV Episode
! rowspan="1" style="text-align: centre;" | Music Videos
|-
|actor_id || integer || Primary Key || ||
|-
|name || integer || Name of the actor, artist, director, or writer
| rowspan="2" style="text-align: left;" |
<actor>
  <name></name>
  <thumb></thumb>
</actor>
| rowspan="2" style="text-align: left;" |
<artist></artist>
|-
|art_urls|| text || Image URL
|}
 
 
 
==== actor_link ====
This table links actors to Movies, TV Shows, Episodes, Music Videos and stores role information.
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies<br />TV Show<br />TV Episode
! rowspan="1" style="text-align: centre;" | Music Videos
|-
|actor_id || integer || Foreign key to [[Databases#actors|actors table]] || ||
|-
|media_id || integer || Foreign key to [[Databases#episode|episode table]], [[Databases#tvshow|tv show table]], [[Databases#movie|movie table]],[[Databases#musicvideo|music video table]] || ||
|-
|media_type || text || Movie, TV Show, Episode, Music Video || <movie></movie><br /><tvshow></tvshow><br /><episodedetails></episodedetails> || <musicvideo></musicvideo>
|-
|role || text || Role the actor played
| rowspan="2" style="text-align: left;" |
<actor>
  <role></role>
  <order></order>
</actor>
| rowspan="2" style="text-align: left;" |
n/a
|-
|cast_order || integer || Order actors will be displayed
|}
 
 
 
==== art ====
This table stores URLs for video art metadata.
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies
! rowspan="1" style="text-align: centre;" | TV Shows
! rowspan="1" style="text-align: centre;" | TV Episode
! rowspan="1" style="text-align: centre;" | Music Videos
|-
|art_id || integer || Primary Key || ||  || ||
|-
|media_id || integer || The id of the media this piece of art is for || ||  ||  ||
|-
|media_type || text || The type of media this art applies to - movie, set, tvshow, season, episode, musicvideo or actor || ||  || ||
|-
|type || text || The image type - poster, fanart, thumb, banner, landscape, clearlogo, clearart, characterart or discart ||||  || ||
|-
|url || text || Image URL
| rowspan="1" style="text-align: left;" |
<thumb aspect="poster" preview=""></thumb>
and<br />
<fanart>
  <thumb preview=""></thumb>
</fanart>
| rowspan="1" style="text-align: left;" |
<fanart url="">
    <thumb dim="" colors="" preview=""></thumb>
</fanart>
and<br />
<actor>
  <thumb></thumb>
</actor>
and<br />
<thumb aspect="" type="" season=""></thumb>
| rowspan="1" style="text-align: left;" |
<fanart>
  <thumb preview=""></thumb>
</fanart>
and
<actor>
  <thumb></thumb>
</actor>
and<br />
<thumb></thumb>
| rowspan="1" style="text-align: left;" |
<art>
  <fanart></fanart>
  <poster></poster>
</art>
|}
 
 
 
==== 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="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies<br />TV Show<br />TV Episode
|-
|idBookmark || integer || Primary Key ||
|-
|idFile || integer || Foreign key to [[Databases#files|files table]] ||
|-
|timeInSeconds || double || Time in seconds of bookmark location
| rowspan="2" style="text-align: left;" |
<resume>
  <position></position>
  <total></total>
</resume>
|-
|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) ||
|}
 
 
 
==== country ====
This table lists countries.
 
{|class="prettytable"
! Column Name || Data Type || Description || Movies
|-
|country_id || integer || Primary Key ||
|-
|name || text || Country Name || <country></country>
|}
 
 
 
==== country link ====
This table links countries to movies.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|country_id || integer || Foreign key to [[Databases#country|country table]]
|-
|media_id || integer ||Foreign key to [[Databases#movie|movie table]]
|-
|media_type || integer || Movie
|}
 
 
 
==== director_link ====
This table links directors to Movies, TV show episodes and Music Videos
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|actor_id || integer || Foreign key to [[Databases#actors|actors table]]
|-
|media_id || integer || Foreign key to [[Databases#episode|episode table]], [[Databases#movie|movie table]],[[Databases#musicvideo|music video table]]
|-
|media_type || text || Movie, Music Video, Episode
|}
 
 
 
==== episode ====
This table stores television episode information. Information concerning the series is stored in [[Databases#tvshow|tvshow]]. To link an episode to its parent series, use [[Databases#tvshowlinkepisode|tvshowlinkepisode]].
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | TV Episode
|-
|idEpisode || integer || Primary Key ||
|-
|idFile || integer ||  Foreign key to the [[Databases#files|files table]] ||
|-
|c00 || text ||  Episode Title || <title></title>
|-
|c01 || text ||  Plot Summary || <plot></plot>
|-
|c02 || text ||  [unknown - listed as Votes] ||
|-
|c03 || text ||  Link to [[Databases#rating|Rating Table]] ||
|-
|c04 || text ||  Writer || <credits></credits>
|-
|c05 || text ||  First Aired || <premiered></premiered>
|-
|c06 || text ||  Thumbnail URL || <thumb></thumb>
|-
|c07 || text ||  [unknown - listed as Thumbnail URL Spoof, unused?] ||
|-
|c08 || text ||  Has the episode been watched? (unused?) ||
|-
|c09 || text ||  Episode length in minutes (converted to sec in DB) || <runtime></runtime>
|-
|c10 || text ||  Director || <director></director>
|-
|c11 || text ||  [unknown - listed as Indentifier] || <code></code>
|-
|c12 || text ||  Season Number || <season></season>
|-
|c13 || text ||  Episode Number || <episode></episode>
|-
|c14 || text ||  Original Title || <originaltitle></originaltitle>
|-
|c15 || text ||  Season formatted for sorting || <displayseason></displayseason>
|-
|c16 || text ||  Episode formatted for sorting || <displayepisode></displayepisode>
|-
|c17 || text ||  Bookmark ||
|-
|c18 || text ||  Path to episode file ||
|-
|c19 || text ||  Used- unknown ||
|-
|c20 || text ||  Used- unknown ||
|-
|c21 || text ||  Not used ||
|-
|c22 || text ||  Not used ||
|-
|c23 || text ||  Not used ||
|-
|idShow || text ||  Foreign key to the [[Databases#tvshow|tvshow table]] ||
|-
|userrating || text ||  User Rating || <userrating></userrating>
|-
|idSeason || text ||  Foreign key to the [[Databases#seasons|seasons table]] ||
|}
 
 
 
==== files ====
This table stores filenames and links the path.
 
{|class="prettytable"
! Column Name || Data Type || Description || Movies<br />TV Shows<br />TV Episodes<br />Music Videos
|-
|idFile || integer || Primary Key ||
|-
|idPath || integer || Foreign key to [[Databases#path|path table]] ||
|-
|strFilename || text || Full name of file including extension ||
|-
|playCount || integer || # of Times Played || <playcount></playcount>
|-
|lastPlayed || text || Date & Time Last Played || <lastplayed></lastplayed>
|-
|dateAdded || text || Date & Time Added to Library || <dateadded></dateadded>
|}
 
 
 
==== genre ====
This table stores genre information. For convenience the contents are duplicated in [[Databases#movie|movie]] and [[Databases#tvshow|tvshow]], so a join isn't necessary.
 
{|class="prettytable"
! Column Name || Data Type || Description || Movies<br />TV Shows<br />TV Episodes<br />Music Videos
|-
|genre_id || integer || Primary Key ||
|-
|name || text || Genre label || <genre></genre>
|}
 
 
 
==== genre_link ====
This table links genres to movies. (The contents are also stored in movies.c14, though.)
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|genre_id || integer || Foreign key to [[Databases#genre|genre table]]
|-
|media_id || integer || Foreign key to [[Databases#movie|movie table]], [[Databases#tvshow|tv show table]], [[Databases#musicvideo|music video table]]
|-
|media_type || text || Movie, Music Video, TV Show
|}
 
 
 
==== movie ====
This table stores movie information.
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies
|-
|idMovie || integer || Primary Key ||
|-
|idFile || integer ||  Foreign Key to [[Databases#files|files table]] ||
|-
|c00 || text ||  Local Movie Title || <title></title>
|-
|c01 || text ||  Movie Plot || <plot></plot>
|-
|c02 || text ||  Movie Plot Outline || <outline></outline>
|-
|c03 || text ||  Movie Tagline || <tagline></tagline>
|-
|c04 || text ||  Rating Votes link to Rating table ||
|-
|c05 || text ||  Link to [[Databases#rating|Rating Table]] ||
|-
|c06 || text ||  Writers || <credits></credits>
|-
|c07 || text ||  Year Released ||
|-
|c08 || text ||  Image URL || <thumb aspect="poster" preview=""></thumb>
|-
|c09 || text ||  IMDB ID link to uniqueid table ||
|-
|c10 || text ||  Title formatted for sorting || <sorttitle></sorttitle>
|-
|c11 || text ||  Runtime minutes (UPnP devices see this as seconds) || <runtime></runtime> **
|-
|c12 || text ||  MPAA Rating || <mpaa></mpaa>
|-
|c13 || text ||  [http://www.imdb.com/chart/top IMDB Top 250] Ranking || <top250></top250>
|-
|c14 || text ||  Genre || <genre></genre>
|-
|c15 || text ||  Director ||<director></director>
|-
|c16 || text ||  Original Movie Title || <originaltitle></originaltitle>
|-
|c17 || text ||  [unknown - listed as Thumbnail URL Spoof] ||
|-
|c18 || text ||  Studio || <studio></studio>
|-
|c19 || text ||  Trailer URL || <trailer></trailer>
|-
|c20 || text ||  Fanart URLs
| rowspan="1" style="text-align: left;" |
<fanart>
  <thumb preview=""></thumb>
</fanart>
|-
|c21 || text ||  Country (Added in r29886[http://trac.kodi.tv/changeset/29886/trunk]) || <country></country>
|-
|c22 || text ||  Path to playable file ||
|-
|c23 || text ||  idPath ||
|-
|idSet || integer ||  Foreign Key to [[Databases#sets|sets table]] ||
|-
|userrating || integer ||  Rating applied by user || <userrating></userrating>
|-
|premiered || text ||  Date movie premiered || <premiered></premiered>
|}
 
{|class="prettytable"
!'''Notes'''
|-
|** || Overwritten on Play
|}
 
 
==== movielinktvshow ====
This table links movies to TV shows.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idMovie || integer || Foreign key to [[Databases#movie|movie table]]
|-
|idShow || integer || Foreign key to [[Databases#tvshow|tvshow table]]
|}
 
 
 
==== musicvideo ====
 
{|class="prettytable"
! Column Name || Data Type || Description || Music Videos
|-
|idMVideo || integer || Primary Key ||
|-
|idFile || integer ||  Foreign Key to [[Databases#files|files table]] ||
|-
|c00 || text || Title || <title></title>
|-
|c01 || text || Thumbnail URL || <thumb preview=""></thumb>
|-
|c02 || text || [unknown - listed as Thumbnail URL spoof] ||
|-
|c03 || text || Play count (unused?) ||
|-
|c04 || text || Run time || <runtime></runtime>
|-
|c05 || text || Director || <director></director>
|-
|c06 || text || Studios || <studio></studio>
|-
|c07 || text || Year || Not used
|-
|c08 || text || Plot || <plot></plot>
|-
|c09 || text || Album || <album></album>
|-
|c10 || text || Artist || <artist></artist>
|-
|c11 || text || Genre || <genre></genre>
|-
|c12 || text || Track || Not used
|-
|c13 || text || Path to playable file || <filenameandpath></filenameandpath><br /><basepath></basepath>
|-
|c14 || text || Unknown || Not used
|-
|c15 || text || Unknown  || Not used
|-
|c16 || text || Unknown || Not used
|-
|c17 || text || Unknown || Not used
|-
|c18 || text || Unknown || Not used
|-
|c19 || text || Unknown || Not used
|-
|c20 || text || Unknown || Not used
|-
|c21 || text || Unknown || Not used
|-
|c22 || text || Unknown || Not used
|-
|c23 || text || Unknown || Not used
|-
|userrating || integer ||  Rating applied by user || <userrating></userrating>
|-
|premiered || text ||  Date movie premiered || <year></year>
|}
 
 
 
==== path ====
This table stores path information.
 
{|class="prettytable"
! 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
|-
|noUpdate || bool || Exclude path from library update
|-
|exclude || bool ||
|-
|dateAdded || text ||
|-
|idParentPath || integer ||
|}
 
 
 
==== rating ====
This table stores the ratings for TV Shows, Episodes and Movies
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies <br />TV Shows<br />TV Episode
|-
|rating_id || integer || Primary Key ||
|-
|media_id || integer || Foreign key to [[Databases#episode|episode table]], [[Databases#tvshow|tv show table]], [[Databases#movie|movie table]], ||
|-
|media_type || text || Movies, TV Show, TV Episode || <movie></movie><br /><tvshow></tvshow><br /><episodedetails></episodedetails>
|-
|rating_type || text || default ||
|-
|rating || float || rating from scraper site
| rowspan="2" style="text-align: left;" |
<ratings>
  <rating name="default" max="10" default="true">
    <value></value>
    <votes></votes>
  </rating>
</ratings>
|-
|votes || integer || votes from scraper site
|}
 
 
 
==== seasons ====
This table stores the links between tv show and seasons.
 
{|class="prettytable"
! Column Name || Data Type || Description || TV Show
|-
|idSeason || integer || Primary Key ||
|-
|idShow|| integer || Foreign key to [[Databases#tvshow|tvshow table]] ||
|-
|season || integer || Season number || <season></season>
|-
|name || text || Season Name ||
|-
|userrating || integer || Season level User Rating ||
|}
 
 
 
==== sets ====
This table stores the id and name for movie sets. Sets are linked to movies in the movie table (idSet column).
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies
|-
|idSet || integer || Primary Key ||
|-
|strSet || text || The name of the set
| rowspan="2" style="text-align: left;" |
<set>
  <name></name>
  <overview></overview>
</set>
|-
|strOverview || text || The description of the set
|}
 
 
 
==== settings ====
This table stores settings for individual files.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idFile || integer || Foreign Key to [[Databases#files|files table]]
|-
|Deinterlace || bool || Deinterlace
|-
|ViewMode|| integer || ViewMode
|-
|ZoomAmount|| float || ZoomAmount
|-
|PixelRatio|| float || PixelRatio
|-
|VerticalShift || float||
|-
|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
|-
|Sharpness || float || Sharpness
|-
|NoiseReduction || float || Noise Reduction
|-
|NonLinStretch || bool || Non Linear Stretch
|-
|PostProcess || bool || Post Processing
|-
|ScalingMethod || integer || Scaling
|-
|DeinterlaceMode || integer || Deinterlace mode
|-
|StereoMode || integer || Stereo Mode
|-
|StereoInvert || bool || Stereo Inversion
|-
|VideoStream || integer || VideoStream
|}
 
 
 
==== stacktimes ====
This table stores playing times for files (used for playing multi-file videos).
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idFile || integer || Foreign key to [[Databases#files|files table]]
|-
|times|| text || Times
|}
 
 
 
==== streamdetails ====
This table contains information regarding codecs used, aspect ratios etc
 
{|class="prettytable"
! Column Name || Data Type || Description
! rowspan="1" style="text-align: centre;" | Movies **<br />TV Episode **
! rowspan="1" style="text-align: centre;" | Music Videos **
|-
|idFile || integer || Foreign Key to [[Databases#files|files table]] || ||
|-
|iStreamType || integer || 0 = video, 1 = audio, 2 = subtitles || ||
|-
|strVideoCodec|| text || Video codex (xvid etc)
| rowspan="6" style="text-align: left;" |
<fileinfo>
  <streamdetails>
    <video>
      <codec></codec>
      <aspect></aspect>
      <width></width>
      <height></height>
      <durationinseconds>5311</durationinseconds>
      <stereomode></stereomode>
    </video>
  </streamdetails>
</fileinfo>
| rowspan="6" style="text-align: left;" |
|-
|fVideoAspect|| real || Aspect ratio
|-
|iVideoWidth|| integer || Width of the video
|-
|iVideoHeight|| integer || Height of the video
|-
|iVideoDuration|| integer  || Actual runtime in sec
|-
|strStereoMode || text || Stereo Mode
|-
|strAudioCodec|| text || Audio codec (aac, mp3 etc)
| rowspan="3" style="text-align: left;" |
<fileinfo>
  <streamdetails>
    <video>
      <audio>
        <codec></codec>
        <channels></channels>
        <language></language>
      </audio>
    </video>
  </streamdetails>
</fileinfo>
| rowspan="3" style="text-align: left;" |
<fileinfo>
  <streamdetails>
    <video>
      <audio>
        <codec></codec>
        <channels></channels>
        <language></language>
      </audio>
    </video>
  </streamdetails>
</fileinfo>
|-
|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
| rowspan="1" style="text-align: left;" |
<fileinfo>
  <streamdetails>
    <video>
      <subtitle>
        <language>en</language>
      </subtitle>
    </video>
  </streamdetails>
</fileinfo>
| rowspan="1" style="text-align: left;" |
|-
|strVideoLanguage|| text || Language of the Video ||
|}
 
{|class="prettytable"
!'''Notes'''
|-
|** || Settings Will be overwritten on first play
|}
 
 
 
==== studio ====
This table stores studio information.
 
{|class="prettytable"
! Column Name || Data Type || Description || Movies<br />TV Shows<br />TV Episodes<br />Music Videos
|-
|studio_id || integer || Primary Key ||
|-
|name || text || Studio Label || <studio></studio>
|}
 
 
 
==== studio link ====
This table links studios to movies, music videos and tv shows
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|studio_id || integer || Foreign key to [[Databases#studio|studio table]]
|-
|media_id || integer || Foreign key to [[Databases#movie|movie table]], [[Databases#tvshow|tv show table]], [[Databases#musicvideo|music video table]]
|-
|media_type || text || Movie, Music Video, TV Show
|}
 
 
 
==== tag ====
This stores tags.
 
{|class="prettytable"
! Column Name || Data Type || Description || Movies<br />TV Shows<br />Music Videos
|-
|tag_id || integer || Primary Key ||
|-
|name || integer || Tag || <tag></tag>
|}
 
 
 
==== taglinks ====
This table links tags to various media.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|tag_id || integer || Foreign key to [[Databases#tag|tag table]]
|-
|media_id || integer || Foreign key to a media table
|-
|media_type || text || Media type for link
|}
 
 
 
==== tvshow ====
This table stores information about a television series. Information concerning the shows episodes is stored in [[Databases#episode|episode]]. To link a TV show to its episodes, use [[Databases#tvshowlinkepisode|tvshowlinkepisode]].
 
{|class="prettytable"
! Column Name || Data Type || Description || TV Show
|-
|idShow || integer || Primary Key ||
|-
|c00 || text ||  Show Title || <showtitle></showtitle>
|-
|c01 || text || Show Plot Summary || <plot>plot>
|-
|c02 || text ||  Status || <status></status>
|-
|c03 || text ||  Unknown ||
|-
|c04 || text ||  Link to [[Databases#rating|Rating Table]] ||
|-
|c05 || text ||  First Aired || <premiered></premiered>
|-
|c06 || text ||  Thumbnail URL || <thumb aspect="" type="" season=""></thumb>
|-
|c07 || text ||  [unknown - Spoof Thumbnail URL?] ||
|-
|c08 || text ||  Genre || <genre></genre>
|-
|c09 || text ||  Original Title || <originaltitle></originaltitle>
|-
|c10 || text ||  Episode Guide URL ||
<episodeguide>
  <url cache=""></url>
</episodeguide>
|-
|c11 || text ||  Fan Art URL ||
<fanart url="">
    <thumb dim="" colors="" preview=""></thumb>
</fanart>
|-
|c12 || text ||  SeriesId (when using thetvdb.com scraper) ||
|-
|c13 || text ||  Content Rating || <mpaa></mpaa>
|-
|c14 || text ||  Studio || <studio></studio>
|-
|c15 || text ||  Title formatted for sorting || <sorttitle></sorttitle>
|-
|c16 || text ||  Not Used ||
|-
|c17 || text ||  Not Used ||
|-
|c18 || text ||  Not Used ||
|-
|c19 || text ||  Not Used ||
|-
|c20 || text ||  [unknown] ||
|-
|c21 || text || [unknown] ||
|-
|c22 || text || [unknown] ||
|-
|c23 || text || [unknown] ||
|-
|userrating || integer ||  Rating applied by user || <userrating></userrating>
|-
|duration || text || Length of Episodes || <runtime></runtime>
|}
 
 
 
==== tvshowlinkpath ====
This table links a TV show to its path.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idShow || integer || Foreign key to [[Databases#tvshow|tvshow table]]
|-
|idPath || integer || Foreign key to [[Databases#path|path table]]
|}
 
 
 
==== uniqueid ====
This table links a Movie, TV show and Episode to its the scraper site
 
{|class="prettytable"
! Column Name || Data Type || Description || Movies<br />TV Shows<br />TV Episodes
|-
|uniqueid || integer || Primary Key ||
|-
|media_id || integer || Foreign key to a media table ||
|-
|media_type || text || Media type for link || <movie></movie><br /><tvshow></tvshow><br /><episodedetails></episodedetails>
|-
|value || text || ID at scraper site || <id></id>
|-
|type || text || Scraper site ||
|}
 
 
 
==== version ====
This table stores database information.
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idVersion || integer || Version of database
|-
|idCompressCount || integer || Number of times database has been compressed
|}
 
 
 
==== writer_link ====
This table links writers stored in the actors table to movies and episodes.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|actor_id || integer || Foreign key to [[Databases#actors|actors table]]
|-
|media_id || integer || Foreign key to a media table
|-
|media_type || text || Media type for link
|}
 
 
{{Top}}
 
== The View Modes Database ==
Kodi 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 Kodi.
 
The View Modes database is stored in userdata/Database/ViewModes.db.
 
{{red|The current database in use for v17 is ViewModes6.db}}
 
=== Tables ===
The View Modes database uses only two tables. Most of the useful information is in [[Databases#view|view]].
 
 
==== version ====
This table stores database information.
{|class="prettytable"
! 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="prettytable"
! 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)
|-
|sortAttributes || integer ||
|-
|skin || text || Skin settings apply to
|}
 
 
== See also ==
* [[Version table]]
* [[MySQL]]
 
 
 
{{Top}}
 
 
 
{{updated|17}}
[[Category:Index]]
[[Category:Index]]
[[Category:Manual]]
[[Category:Manual]]
[[Category:Add-ons]]
[[Category:Live_TV]]
[[Category:Karellen]]
[[Category:Development]]
[[Category:Video library]]
[[Category:Video library]]
[[Category:Music library]]
[[Category:Music library]]
[[Category:Advanced topics]]
[[Category:Advanced topics]]
[[Category:Development]]

Revision as of 23:53, 24 September 2019

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

Databasess 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 July 2019 116 72 13 27 6 34 12 0
Legend
Red In development and subject to change without notice



Links


See also




Return to top