Databases

From Official Kodi Wiki
Revision as of 10:04, 26 February 2018 by Karellen (talk | contribs)
Jump to navigation Jump to search
Home icon grey.png   ▶ Development
▶ userdata folder
▶ 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.

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.


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.

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.


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.


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.

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.


albumartistview

This table combines album with each album artist.

Column Name Data Type Description
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.

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.

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
strFanart text URLs of available artist fanart
dateAdded text Music file timestamp of newest song by artist


songartistview

A view that joins song to artist and artist role.

Column Name Data Type Description
idSong integer ID from song table
idArtist integer ID from artist table
idrole integer ID from role table
strRole text Description of role
strArtist varchar(256) Artist name
strMusicBrainzArtistID text Musicbrainz artist ID Hash
iOrder integer Order artist appears in song credits


songview

A view that joins song to album and path.

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
iDuration integer Song duration in seconds
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


Tables

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

Column Name Data Type Description MusicFileTag ** NFO (xml) Tag
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,

ALBUMARTISTS,
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>
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

Column Name Data Type Description
idArtist integer Foreign key to artist table
idAlbum integer Foreign key to album table
iOrder integer Order of artist names for credits
strArtist text Artist name (denormalised)


album_genre

This links albums with their genres.

Column Name Data Type Description
idGenre integer Foreign key to genre(music) table
idAlbum integer Foreign key to 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.

Column Name Data Type Description NFO (xml) Tag
idAlbumInfoSong integer Primary Key
idAlbumInfo integer Foreign key to 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

Column Name Data Type Description NFO (xml) Tag
art_id integer Primary Key
media_id integer Foreign key to album or 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).

Column Name Data Type Description MusicFileTag ** NFO (xml) Tag
idArtist integer ID from artist table
strArtist varchar(256) Artist name ARTIST, ARTISTS,
ALBUMARTIST and

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

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.

Column Name Data Type Description MusicFileTag
idPath integer Foreign key to 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.

Column Name Data Type Description NFO (xml) Tag
idArtist integer Foreign key to 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.

Column Name Data Type Description MusicFileTag ** NFO (xml) Tag
idGenre integer Primary Key
strGenre varchar(256) Genre Name GENRE <genre></genre> ^^
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.

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.

Column Name Data Type Description MusicFileTag **
idrole integer Primary Key
strRole text Description of role

ARTIST, ARRANGER, COMPOSER, CONDUCTOR,
DJMIXER, ENGINEER, LYRICIST, MIXER,
PRODUCER, REMIXER and PERFORMER

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.

Column Name Data Type Description MusicFileTag **
idSong integer ID from song table
idAlbum integer ID from album table
idPath integer Foreign key to 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
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
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.

Column Name Data Type Description
idArtist integer Foreign key to artist table
idSong integer Foreign key to song table
idRole integer Foreign key to role table
iOrder integer Order in artist credits
strArtist text Name of artist (denormalised)


song_genre

This table links the songs with their genres.

Column Name Data Type Description
idGenre integer Foreign key to genre(music) table
idSong integer Foreign key to song table
iOrder integer Genre order when concatenating to single string


version(music)

Column Name Data Type Description
idVersion integer Version of the music database
iCompressCount integer Number of times database has been compressed


Return to 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 video database is stored in userdata/Database/MyVideosXX.db, where XX is the version number.

The current database in use for v17.x is MyVideos107.db which is what is detailed on this page


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.

Column Name Data Type Description
idEpisode integer Primary Key
idFile integer Foreign key to the 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 tvshow table
userrating text User Rating
idSeason text Foreign key to the 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.

Column Name Data Type Description
idMovie integer Primary Key
idFile integer Foreign Key to files table
c00 text Local Movie Title
c01 text Movie Plot
c02 text Movie Plot Outline
c03 text Movie Tagline
c04 text Rating Votes link to Rating Table
c05 text Link to Rating Table
c06 text Writers
c07 text Year Released
c08 text Image URL
c09 text IMDB ID link to uniqueid Table
c10 text Title formatted for sorting
c11 text Runtime (UPnP devices see this as seconds)
c12 text MPAA Rating
c13 text 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[1])
c22 text Path to playable file
c23 text idPath
idSet integer Foreign Key to 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.

Column Name Data Type Description
idMVideo integer Primary Key
idFile integer Foreign Key to 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

View that joins tvshow to path. Also produces information about total number of episodes as well as number of watched and unwatched episodes.

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

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 episode, movie, settings, and 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.

Column Name Data Type Description Movies
TV Show
TV Episode
Music Videos
actor_id integer Primary Key
name integer Name of the actor, artist, director, or writer
<actor>
  <name></name>
  <thumb></thumb>
</actor>

<artist></artist>

art_urls text Image URL


actor_link

This table links actors to Movies, TV Shows, Episodes, Music Videos and stores role information.

Column Name Data Type Description Movies
TV Show
TV Episode
Music Videos
actor_id integer Foreign key to actors table
media_id integer Foreign key to episode table, tv show table, movie table,music video table
media_type text Movie, TV Show, Episode, Music Video <movie></movie>
<tvshow></tvshow>
<episodedetails></episodedetails>
<musicvideo></musicvideo>
role text Role the actor played
<actor>
  <role></role>
  <order></order>
</actor>

n/a

cast_order integer Order actors will be displayed


art

This table stores URLs for video art metadata.

Column Name Data Type Description Movies TV Shows TV Episode 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
<thumb aspect="poster" preview=""></thumb>

and

<fanart>
  <thumb preview=""></thumb>
</fanart>
<fanart url="">
    <thumb dim="" colors="" preview=""></thumb>
</fanart>

and

<actor>
  <thumb></thumb>
</actor>

and

<thumb aspect="" type="" season=""></thumb>
<fanart>
  <thumb preview=""></thumb>
</fanart>

and

<actor>
  <thumb></thumb>
</actor>

and

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

Column Name Data Type Description Movies
TV Show
TV Episode
idBookmark integer Primary Key
idFile integer Foreign key to files table
timeInSeconds double Time in seconds of bookmark location
<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.

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.

Column Name Data Type Description
country_id integer Foreign key to country table
media_id integer Foreign key to movie table
media_type integer Movie


director_link

This table links directors to Movies, TV show episodes and Music Videos

Column Name Data Type Description
actor_id integer Foreign key to actors table
media_id integer Foreign key to episode table, movie table,music video table
media_type text Movie, Music Video, Episode


episode

This table stores television episode information. Information concerning the series is stored in tvshow. To link an episode to its parent series, use tvshowlinkepisode.

Column Name Data Type Description TV Episode
idEpisode integer Primary Key
idFile integer Foreign key to the files table
c00 text Episode Title <title></title>
c01 text Plot Summary <plot></plot>
c02 text [unknown - listed as Votes]
c03 text Link to 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]
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 tvshow table
userrating text User Rating <userrating></userrating>
idSeason text Foreign key to the seasons table


files

This table stores filenames and links the path.

Column Name Data Type Description Movies
TV Shows
TV Episodes
Music Videos
idFile integer Primary Key
idPath integer Foreign key to 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 movie and tvshow, so a join isn't necessary.

Column Name Data Type Description Movies
TV Shows
TV Episodes
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.)

Column Name Data Type Description
genre_id integer Foreign key to genre table
media_id integer Foreign key to movie table, tv show table, music video table
media_type text Movie, Music Video, TV Show


movie

This table stores movie information.

Column Name Data Type Description Movies
idMovie integer Primary Key
idFile integer Foreign Key to 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 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 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
<fanart>
  <thumb preview=""></thumb>
</fanart>
c21 text Country (Added in r29886[2]) <country></country>
c22 text Path to playable file
c23 text idPath
idSet integer Foreign Key to sets table
userrating integer Rating applied by user <userrating></userrating>
premiered text Date movie premiered <premiered></premiered>
Notes
** Overwritten on Play


movielinktvshow

This table links movies to TV shows.

Column Name Data Type Description
idMovie integer Foreign key to movie table
idShow integer Foreign key to tvshow table


musicvideo

Column Name Data Type Description Music Videos
idMVideo integer Primary Key
idFile integer Foreign Key to 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>
<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.

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

Column Name Data Type Description Movies
TV Shows
TV Episode
rating_id integer Primary Key
media_id integer Foreign key to episode table, tv show table, movie table,
media_type text Movies, TV Show, TV Episode <movie></movie>
<tvshow></tvshow>
<episodedetails></episodedetails>
rating_type text default
rating float rating from scraper site
<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.

Column Name Data Type Description TV Show
idSeason integer Primary Key
idShow integer Foreign key to 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).

Column Name Data Type Description Movies
idSet integer Primary Key
strSet text The name of the set
<set>
  <name></name>
  <overview></overview>
</set>
strOverview text The description of the set


settings

This table stores settings for individual files.

Column Name Data Type Description
idFile integer Foreign Key to 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).

Column Name Data Type Description
idFile integer Foreign key to files table
times text Times


streamdetails

This table contains information regarding codecs used, aspect ratios etc

Column Name Data Type Description Movies **
TV Episode **
Music Videos **
idFile integer Foreign Key to files table
iStreamType integer 0 = video, 1 = audio, 2 = subtitles
strVideoCodec text Video codex (xvid etc)
<fileinfo>
  <streamdetails>
    <video>
      <codec></codec>
      <aspect></aspect>
      <width></width>
      <height></height>
      <durationinseconds>5311</durationinseconds>
      <stereomode></stereomode>
    </video>
  </streamdetails>
</fileinfo>
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)
<fileinfo>
  <streamdetails>
    <video>
      <audio>
        <codec></codec>
        <channels></channels>
        <language></language>
      </audio>
    </video>
  </streamdetails>
</fileinfo>
<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
<fileinfo>
  <streamdetails>
    <video>
      <subtitle>
        <language>en</language>
      </subtitle>
    </video>
  </streamdetails>
</fileinfo>
strVideoLanguage text Language of the Video
Notes
** Settings Will be overwritten on first play


studio

This table stores studio information.

Column Name Data Type Description Movies
TV Shows
TV Episodes
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

Column Name Data Type Description
studio_id integer Foreign key to studio table
media_id integer Foreign key to movie table, tv show table, music video table
media_type text Movie, Music Video, TV Show


tag

This stores tags.

Column Name Data Type Description Movies
TV Shows
Music Videos
tag_id integer Primary Key
name integer Tag <tag></tag>


taglinks

This table links tags to various media.

Column Name Data Type Description
tag_id integer Foreign key to 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 episode. To link a TV show to its episodes, use tvshowlinkepisode.

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

Column Name Data Type Description
idShow integer Foreign key to tvshow table
idPath integer Foreign key to path table


uniqueid

This table links a Movie, TV show and Episode to its the scraper site

Column Name Data Type Description Movies
TV Shows
TV Episodes
uniqueid integer Primary Key
media_id integer Foreign key to a media table
media_type text Media type for link <movie></movie>
<tvshow></tvshow>
<episodedetails></episodedetails>
value text ID at scraper site <id></id>
type text Scraper site


version

This table stores database information.

Column Name Data Type Description
idVersion integer Version of database
idCompressCount integer Number of times database has been compressed


writer_link

This table links writers stored in the actors table to movies and episodes.

Column Name Data Type Description
actor_id integer Foreign key to actors table
media_id integer Foreign key to a media table
media_type text Media type for link


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

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


version

This table stores database information.

Column Name Data Type Description
idVersion integer Version of database
idCompressCount integer Number of times database has been compressed

view

This table stores details on the saved view mode for all known paths.

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


Return to top