Databases/MyMusic: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
(Create Page)
 
(Updated for v19)
(One intermediate revision by the same user not shown)
Line 1: Line 1:
{{Template:DatabasesNavBox}}
{{Template:DatabasesNavBox}}
{{mininav| [[Development]] {{l2| [[Userdata]]}} | [[Databases]]  }}
{{mininav| [[Development]] {{l2| [[Userdata]]}} | [[Databases]]  }}




Line 10: Line 9:
The current stable release of {{kodi}} is using the following version of the database:
The current stable release of {{kodi}} is using the following version of the database:


;Kodi v17 - MyMusic60.db
;Kodi v19 - MyMusic82.db




'''{{red|Warning: The databases are critical components of {{kodi}}. We strongly urge that you do not interfere with them, unless you have knowledge and experience with database designs.}}'''
'''{{red|Warning: The databases are critical components of {{kodi}}. We strongly urge that you do not interfere with them, unless you have knowledge and experience with database designs.}}'''




Line 20: Line 18:
= Views =
= 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.
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.




Line 32: Line 29:
|idArtist || integer || ID from artist table
|idArtist || integer || ID from artist table
|-
|-
|idrole || integer || = 0 (fake role ID field)
|idRole || integer || 0 (fake role ID field)
|-
|-
|strRole || text || = "AlbumArtist" (fake role desc field)
|strRole || text || "AlbumArtist" (fake role desc field)
|-
|-
|strArtist || varchar(256) || Artist name
|strArtist || varchar(256) || Artist name
|-
|strSortName || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name]
|-
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID
|strMusicBrainzArtistID || text || Musicbrainz artist ID
Line 55: Line 54:
|-
|-
|strMusicBrainzAlbumID || text || MusicBrainz album ID
|strMusicBrainzAlbumID || text || MusicBrainz album ID
|-
|strReleaseGroupMBID || text || The ID that groups variations of the same release
|-
|-
|strArtists || text || Album artist(s) description (not always a concatenation of names)
|strArtists || text || Album artist(s) description (not always a concatenation of names)
|-
|strArtistSort || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name]
|-
|-
|strGenres || text || Album genres (denormalised concatenation of values in genre table)
|strGenres || text || Album genres (denormalised concatenation of values in genre table)
|-
|-
|iYear || integer || Album release year
|strReleaseDate || text || Release date of album (re-releases)
|-
|strOrigReleaseDate || text || Original release date of album
|-
|bBoxedSet || integer || 0 = Not part of a Box Set<br>1 = Part of a Box Set
|-
|-
|strMoods || text || Album moods
|strMoods || text || Album moods
Line 73: Line 80:
|-
|-
|strType || text || Album Type e.g. "Soundtrack / Live"
|strType || text || Album Type e.g. "Soundtrack / Live"
|-
|strReleaseStatus || text || See: [https://wiki.musicbrainz.org/Release#Status MusicBrainz Release Status]
|-
|-
|strImage || text || URLs for available remote album images
|strImage || text || URLs for available remote album images
Line 83: Line 92:
|-
|-
|bCompilation || integer || Compilation flag
|bCompilation || integer || Compilation flag
|-
|bScrapedMBID || integer || Indicates if mbid(s) have been fetched by scraping or from tags
|-
|lastScraped || varchar(20) || Date & time of last scrape
|-
|dateAdded || text || Music file timestamp of newest song on album
|-
|dateNew || text || Music file timestamp of newest song on album
|-
|dateModified || text || Music file timestamp of newest song on album
|-
|-
|iTimesPlayed || integer ||Average of number of times songs on album are played  
|iTimesPlayed || integer ||Average of number of times songs on album are played  
Line 88: Line 107:
|strReleaseType || text || Internal type - "album" or "single"
|strReleaseType || text || Internal type - "album" or "single"
|-
|-
|dateAdded || text || Music file timestamp of newest song on album
|iDiscTotal || integer || Number of discs in the Release
|-
|-
|lastPlayed || text || Last date any song on album was played
|lastPlayed || text || Last date any song on album was played
|-
|iAlbumDuration || integer || Duration of all songs from that album currently in the library
|}
|}


Line 103: Line 124:
|-
|-
|strArtist || varchar(256) || Artist name
|strArtist || varchar(256) || Artist name
|-
|strSortName || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name]
|-
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID
|strMusicBrainzArtistID || text || Musicbrainz artist ID
|-
|strType || text || Type of performer
|-
|strGender || text || Male or Female artist
|-
|strDisambiguation || text || See: [https://musicbrainz.org/doc/Disambiguation_Comment Disambiguation Comment] 
|-
|-
|strBorn || text || Artist birthday (and place)
|strBorn || text || Artist birthday (and place)
Line 128: Line 157:
|strImage || text || URLs of available artist images
|strImage || text || URLs of available artist images
|-
|-
|strFanart || text || URLs of available artist fanart
|bScrapedMBID || integer || Indicates if mbid(s) have been fetched by scraping or from tags
|-
|lastScraped || varchar(20) || Date & time of last scrape
|-
|-
|dateAdded || text || Music file timestamp of newest song by artist
|dateAdded || text || Music file timestamp of newest song by artist
|-
|dateNew || text || Music file timestamp of newest song on album
|-
|dateModified || text || Music file timestamp of newest song on album
|}
|}


Line 144: Line 179:
|idArtist || integer || ID from artist table
|idArtist || integer || ID from artist table
|-
|-
|idrole || integer || ID from role table
|idRole || integer || ID from role table
|-
|-
|strRole || text || Description of role
|strRole || text || Description of role
|-
|-
|strArtist || varchar(256) || Artist name
|strArtist || varchar(256) || Artist name
|-
|strSortName || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name]
|-
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID Hash
|strMusicBrainzArtistID || text || Musicbrainz artist ID Hash
Line 165: Line 202:
|-
|-
|strArtists || text || Song artist(s) description (not always a concatenation of names)
|strArtists || text || Song artist(s) description (not always a concatenation of names)
|-
|strArtistSort || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name]
|-
|-
|strGenres || text || Song Genres (denormalised concatenation of values in genre table)
|strGenres || text || Song Genres (denormalised concatenation of values in genre table)
Line 174: Line 213:
|iDuration || integer || Song duration in seconds
|iDuration || integer || Song duration in seconds
|-
|-
|iYear || integer || Songs release year
|strReleaseDate || text || Release date of album (re-releases)
|-
|strOrigReleaseDate || text || Original release date of album
|-
|strDiscSubtitle || text || Name of disc used in box sets
|-
|-
|strFileName || text || Name of music file
|strFileName || text || Name of music file
Line 188: Line 231:
|lastplayed || varchar(20) || Date & time last played
|lastplayed || varchar(20) || Date & time last played
|-
|-
|rating || char || Song rating
|rating || float || Song rating
|-
|-
|userrating || integer || Song rated by user (out of 10)
|userrating || integer || Song rated by user (out of 10)
Line 201: Line 244:
|-
|-
|strPath || varchar(512) || Full path for music file
|strPath || varchar(512) || Full path for music file
|-
|strReleaseStatus || text || See: [https://wiki.musicbrainz.org/Release#Status MusicBrainz Release Status]
|-
|-
|bCompilation || integer || Compilation flag (song is on a compilation album)
|bCompilation || integer || Compilation flag (song is on a compilation album)
|-
|bBoxedSet || integer || 0 = Not part of a Box Set<br>1 = Part of a Box Set
|-
|-
|strAlbumArtists || text || Album artist(s) description (not always a concatenation of names)
|strAlbumArtists || text || Album artist(s) description (not always a concatenation of names)
|-
|strAlbumArtistSort || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name]
|-
|-
|strAlbumReleaseType || text || Internal type - "album" or "single"
|strAlbumReleaseType || text || Internal type - "album" or "single"
|-
|-
|mood || text || Song mood
|mood || text || Song mood
|-
|strReplayGain || text || Contains ''album gain, album peak, track gain, track peak '' data in one string
|-
|iBPM || Integer || Beats per Minute of song.
|-
|iBitRate || integer || Bit rate of recording
|-
|iSampleRate || integer || Sample rate of recording
|-
|iChannels || integer || Number of audio channels in the recording
|-
|iAlbumDuration || integer || Duration of all songs from that album currently in the library
|-
|iDiscTotal || integer || Total No of discs in the box set
|-
|-
|dateAdded || text ||  Music file timestamp
|dateAdded || text ||  Music file timestamp
|-
|dateNew || text || Music file timestamp of newest song on album
|-
|dateModified || text || Music file timestamp of newest song on album
|}
|}


Line 231: Line 298:
|strMusicBrainzAlbumID || text || MusicBrainz album ID |||| MUSICBRAINZ_ALBUMID || <musicBrainzAlbumID></musicBrainzAlbumID>
|strMusicBrainzAlbumID || text || MusicBrainz album ID |||| MUSICBRAINZ_ALBUMID || <musicBrainzAlbumID></musicBrainzAlbumID>
|-
|-
|strArtists || text || Album artist(s) description (not always a concatenation of names) |||| ALBUMARTIST,  <br />
|strReleaseGroupMBID || text || The ID that groups variations of the same release |||| MusicBrainz Release Group Id || <musicbrainzreleasegroupid></musicbrainzreleasegroupid>
|-
|strArtistDisp || text || Album artist(s) description (not always a concatenation of names) |||| ALBUMARTIST,  <br />
ALBUMARTISTS, <br />
ALBUMARTISTS, <br />
ARTIST and ARTISTS
|| <artistdesc></artistdesc>
|| <artistdesc></artistdesc>
|-
|strArtistSort || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name] |||| ARTISTSORT
|-
|-
|strGenres || text || Album genres (denormalised concatenation of values in genre table) |||| GENRE || <genre></genre> ^^
|strGenres || text || Album genres (denormalised concatenation of values in genre table) |||| GENRE || <genre></genre> ^^
|-
|-
|iYear || integer || Album release year |||| YEAR or DATE || <year></year>
|strReleaseDate || text || Release date of album (re-releases) ||||||
|-
|-
|idThumb || integer || [Unused] ||||
|strOrigReleaseDate || text || Original release date of album ||||||
|-
|bBoxedSet || integer || 0 = Not part of a Box Set<br>1 = Part of a Box Set ||||||
|-
|-
|bCompilation || integer || Compilation flag |||| COMPILATION || <compilation></compilation>
|bCompilation || integer || Compilation flag |||| COMPILATION || <compilation></compilation>
Line 257: Line 329:
|-
|-
|strType || text || Album Type e.g. "Soundtrack / Live" |||| RELEASETYPE || <type></type>
|strType || text || Album Type e.g. "Soundtrack / Live" |||| RELEASETYPE || <type></type>
|-
|strReleaseStatus || text || See: [https://wiki.musicbrainz.org/Release#Status MusicBrainz Release Status] ||||||
|-
|-
|fRating || float || Album rating ||||  || <rating max="10"></rating>
|fRating || float || Album rating ||||  || <rating max="10"></rating>
|-
|iVotes || integer || Votes (for album rating) ||||  || <votes></votes>
|-
|-
|iUserrating || integer || Album user rating (out of 10) ||||  || <userrating max="10"></userrating>
|iUserrating || integer || Album user rating (out of 10) ||||  || <userrating max="10"></userrating>
|-
|-
|lastScraped || text || Date/time data scraped from online sources or NFO files ||||
|lastScraped || text || Date/time data scraped from online sources or NFO files ||||
|-
|bScrapedMBID || integer || Indicates if mbid(s) have been fetched by scraping or from tags |||| ||
|-
|-
|strReleaseType || text || Internal type - "album" or "single" ||||  || <releasetype></releasetype>
|strReleaseType || text || Internal type - "album" or "single" ||||  || <releasetype></releasetype>
|-
|-
|iVotes || integer || Votes (for album rating) |||| || <votes></votes>
|iDiscTotal || integer || Number of discs in the Release ||||||
|-
|iAlbumDuration || integer || Duration of all songs from that album currently in the library ||||||
|-
|idInfoSetting || integer || Indicates which scraper setting was used for that item. 0 = default scraper. <br>Linked to [[Databases/MyMusic#infosetting|infosetting table]] |||| ||
|-
|dateAdded || text ||  Music file timestamp ||||
|-
|dateNew || text || Music file timestamp of newest song on album ||||||
|-
|dateModified || text || Music file timestamp of newest song on album ||||||
|}
|}


Line 280: Line 368:


== album_artist ==  
== album_artist ==  
This table links artists and albums
This table links albums to the Source folder
{|class="prettytable"
{|class="prettytable"
! Column Name || Data Type || Description
! Column Name || Data Type || Description
|-
|-
|idArtist || integer || Foreign key to [[Databases#artist|artist table]]
|idArtist || integer || Foreign key to [[Databases/MyMusic#artist|artist table]]
|-
|-
|idAlbum || integer || Foreign key to [[Databases#album|album table]]
|idAlbum || integer || Foreign key to [[Databases/MyMusic#album|album table]]
|-
|-
|iOrder || integer || Order of artist names for credits
|iOrder || integer || Order of artist names for credits
Line 295: Line 383:




== album_genre ==  
== album_source ==  
This links albums with their genres.
This table links artists and albums
{|class="prettytable"
{|class="prettytable"
! Column Name || Data Type || Description
! Column Name || Data Type || Description
|-
|-
|idGenre || integer || Foreign key to [[Databases#genre(music)|genre(music) table]]
|idSource || integer || Foreign key to [[Databases/MyMusic#source|source table]]
|-
|-
|idAlbum || integer || Foreign key to [[Databases#album|album table]]
|idAlbum || integer || Foreign key to [[Databases/MyMusic#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>
|}
|}


Line 345: Line 403:
|art_id || integer || Primary Key ||||
|art_id || integer || Primary Key ||||
|-
|-
|media_id || integer || Foreign key to [[Databases#album|album]] or [[Databases#artist|artist table]] ||||
|media_id || integer || Foreign key to [[Databases/MyMusic#album|album]] or [[Databases/MyMusic#artist|artist table]] ||||
|-
|-
|media_type || text || "album" or "artist" ||||
|media_type || text || Used in combination with ''media_id''- "album" or "artist" ||||
|-
|-
|type || text || art type e.g. "thumb", "fanart" etc. ||||
|type || text || art type e.g. "thumb", "fanart" etc. ||||
Line 355: Line 413:
|-
|-
|url || text || URL of image (can be remote, local or music file with art embedded) ||||
|url || text || URL of image (can be remote, local or music file with art embedded) ||||
<art>
 
   <fanart>E:\Music\U2\fanart.jpg</fanart>
   <thumb aspect="poster" preview=""></thumb>
  <thumb>E:\Music\U2\folder.jpg</thumb>
 
</art>
  <fanart>
    <thumb preview=""></thumb>
  </fanart>
|}
|}


Line 373: Line 433:
|strArtist || varchar(256) || Artist name |||| ARTIST, ARTISTS, <br /> ALBUMARTIST and <br />
|strArtist || varchar(256) || Artist name |||| ARTIST, ARTISTS, <br /> ALBUMARTIST and <br />
ALBUMARTISTS  
ALBUMARTISTS  
|| <name></name>
|| <name></name>
|-
|-
|strMusicBrainzArtistID || text || Musicbrainz artist ID |||| MUSICBRAINZ_ARTISTID || <musicBrainzArtistID></musicBrainzArtistID>
|strMusicBrainzArtistID || text || Musicbrainz artist ID |||| MUSICBRAINZ_ARTISTID || <musicBrainzArtistID></musicBrainzArtistID>
|-
|strSortName || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name] |||| ALBUMARTISTSORT
|-
|strType || text || Type of performer |||| || <type></type>
|-
|strGender || text || Male or Female artist ||||  || <gender></gender>
|-
|strDisambiguation || text || See: [https://musicbrainz.org/doc/Disambiguation_Comment Disambiguation Comment]  ||||  || <disambiguation></disambiguation>
|-
|-
|strBorn || text || Artist birthday (and place) ||||  || <born></born>
|strBorn || text || Artist birthday (and place) ||||  || <born></born>
Line 400: Line 467:
|strImage || text || URLs of available artist images ||||  || <thumb preview=" "></thumb> ^^
|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 ||||  ||
|-
|bScrapedMBID || integer || Indicates if mbid(s) have been fetched by scraping or from tags |||| ||
|-
|-
|lastScraped || varchar(20) || Date last scraped metadata
|idInfoSetting || integer || Indicates which scraper setting was used for that item. 0 = default scraper. <br>Linked to [[Databases/MyMusic#infosetting|infosetting table]] |||| ||
|-
|dateAdded || text ||  Music file timestamp |||| ||
|-
|dateNew || text || Music file timestamp of newest song on album |||| ||
|-
|dateModified || text || Music file timestamp of newest song on album |||| ||
|}
|}


Line 415: Line 490:




== content ==  
== audiobook ==  
The scraper addon and settings used for scraping additional information for specific albums or artists.
For use with [[Audiobooks]]. Holds Resume Point information only.
{|class="prettytable"
{|class="prettytable"
! Column Name || Data Type || Description
! Column Name || Data Type || Description
|-
|-
|strPath || text || Virtual path encoding what album or artist e.g. "musicdb://artists/235"
|idBook || integer || Id of Resume Point
|-
|-
|strScraperPath || text || Scraper name e.g. "metadata.artists.universal"
|strBook || varchar(256) || Title link to [[Databases/MyMusic#album|album table]]
|-
|-
|strContent || text || "artists" or "albums"
|strAuthor || text || Author link to [[Databases/MyMusic#artist|artist table]]
|-
|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 ||||
|bookmark || integer || Resume Point (millisec)
|-
|-
|strFileName || text || Name of FLAC file the cuesheet is embedded in ||||
|file || text || Path to audiobook
|-
|-
|strCuesheet || text || Cuesheet |||| CUESHEET
|dateAdded || varchar(20) || Date & Time bookmark created
|}
|}


Line 452: Line 516:
! Column Name || Data Type || Description |||| NFO (xml) Tag
! Column Name || Data Type || Description |||| NFO (xml) Tag
|-
|-
|idArtist || integer || Foreign key to [[Databases#artist|artist table]] ||||
|idArtist || integer || Foreign key to [[Databases/MyMusic#artist|artist table]] ||||
|-
|-
|strAlbum || text || Album title ||||  
|strAlbum || text || Album title ||||  
Line 463: Line 527:
   <year></year>
   <year></year>
  </album>
  </album>
|-
|strReleaseGroupMBID || text || The ID that groups variations of the same release  ||||
|}
|}






== genre(music) ==  
== genre ==  
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.
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.


Line 488: Line 554:




== path(music) ==  
== infosetting ==
As scrapers and scraper settings can be changed per individual Album and Artist, this table details the settings of additional scrapers used. If only the default scraper for Album and Artist are used, there are no entries here.
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idSetting || integer || Primary Key
|-
|strScraperPath || text || Scraper details
|-
|strSettings || text || Scraper settings
|}
 
 
== path ==  
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.
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.


Line 500: Line 580:
|strHash || text || Hash value
|strHash || text || Hash value
|}
|}
== removed_link ==
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idArtist || integer ||
|-
|idMedia|| integer ||
|-
|idRole || integer ||
|}




Line 531: Line 626:


{|class="prettytable"
{|class="prettytable"
! Column Name || Data Type || Description |||| [[Music_tagging#Data_Kodi_Scrapes|MusicFileTag]] **
! Column Name || Data Type || Description |||| [[Music_tagging#Data_Kodi_Scrapes|MusicFileTag]] ** || XML Tags
|-
|idSong || integer || ID from song table |||| ||
|-
|idAlbum || integer || ID from album table |||| ||
|-
|idPath || integer || Foreign key to [[Databases/MyMusic#path|path table]] |||| ||
|-
|strArtistDisp || text || Album artist(s) description (not always a concatenation of names) |||| ALBUMARTIST,  <br />
ALBUMARTISTS, <br />
|| <artistdesc></artistdesc>
|-
|strArtistSort || text || See: [https://musicbrainz.org/doc/Style/Artist/Sort_Name Sort Name] |||| ALBUMARTISTSORT ||
|-
|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 ||
|-
|-
|idSong || integer || ID from song table ||||
|iDuration || integer || Song duration in seconds |||| ||
|-
|-
|idAlbum || integer || ID from album table ||||
|strReleaseDate || text || Release date of album (re-releases) ||||||
|-
|-
|idPath || integer || Foreign key to [[Databases#path|path table]] ||||
|strOrigReleaseDate || text || Original release date of album ||||||
|-
|-
|strArtists || text || Song artist(s) description (not always a concatenation of names) |||| ARTIST and ARTISTS
|strDiscSubtitle || text || Name of disc used in box sets ||||||
|-
|-
|strGenres || text || Song Genres (denormalised concatenation of values in genre table) |||| GENRE
|strFileName || text || Name of music file |||| ||
|-
|-
|strTitle || varchar(512) || Song title |||| TITLE
|strMusicBrainzTrackID || text || Musicbrainz track ID |||| MUSICBRAINZ_TRACKID ||
|-
|-
|iTrack || integer || Song disc (upper 4 bytes) and track number (lower 4 bytes)|||| TRACKNUMBER and <br /> DISCNUMBER
|iTimesPlayed || integer || Play count |||| ||
|-
|-
|iDuration || integer || Song duration in seconds ||||
|iStartOffset || integer || Start position (in seconds) when song from cuesheet |||| ||
|-
|-
|iYear || integer || Songs release year |||| YEAR or DATE
|iEndOffset || integer || End position (in seconds) when song from cuesheet |||| ||
|-
|-
|dwFileNameCRC || text || [unused] ||||
|lastplayed || varchar(20) || Date & time last played |||| ||
|-
|-
|strFileName || text || Name of music file ||||
|rating || float || Song rating |||| ||
|-
|-
|strMusicBrainzTrackID || text || Musicbrainz track ID |||| MUSICBRAINZ_TRACKID
|votes || integer || Votes for song rating |||| ||
|-
|-
|iTimesPlayed || integer || Play count ||||
|userrating || integer || Song rated by user (out of 10) |||| RATING ||
|-
|-
|iStartOffset || integer || Start position (in seconds) when song from cuesheet ||||
|comment || text || Song description or comment |||| COMMENT ||
|-
|-
|iEndOffset || integer  || End position (in seconds) when song from cuesheet ||||
|mood || text || Song mood |||| MOOD ||
|-
|-
|idThumb || integer || [unused]  ||||
|iBPM || Integer || Beats per Minute of song. ||||||
|-
|-
|lastplayed || varchar(20) || Date & time last played ||||
|iBitRate || integer || Bit rate of recording ||||||
|-
|-
|rating || char || Song rating ||||
|iSampleRate || integer || Sample rate of recording ||||||
|-
|-
|userrating || integer || Song rated by user (out of 10) |||| RATING
|iChannels || integer || Number of audio channels in the recording ||||||
|-
|-
|comment || text || Song description or comment |||| COMMENT
|strReplayGain || text || Contains ''album gain, album peak, track gain, track peak '' data in one string  ||  ||||
|-
|-
|mood || text || Song mood |||| MOOD
|dateAdded || text || Music file timestamp |||| ||
|-
|-
|dateAdded || text || Music file timestamp ||||
|dateNew || text || Music file timestamp of newest song on album |||| ||
|-
|-
|votes || integer || Votes for song rating ||||
|dateModified || text || Music file timestamp of newest song on album |||| ||
|}
|}


Line 593: Line 706:
! Column Name || Data Type || Description
! Column Name || Data Type || Description
|-
|-
|idArtist || integer || Foreign key to [[Databases#artist|artist table]]
|idArtist || integer || Foreign key to [[Databases/MyMusic#artist|artist table]]
|-
|-
|idSong || integer || Foreign key to [[Databases#song|song table]]
|idSong || integer || Foreign key to [[Databases/MyMusic#song|song table]]
|-
|-
|idRole || integer || Foreign key to [[Databases#role|role table]]
|idRole || integer || Foreign key to [[Databases/MyMusic#role|role table]]
|-
|-
|iOrder || integer || Order in artist credits
|iOrder || integer || Order in artist credits
Line 611: Line 724:
! Column Name || Data Type || Description
! Column Name || Data Type || Description
|-
|-
|idGenre || integer || Foreign key to [[Databases#genre(music)|genre(music) table]]
|idGenre || integer || Foreign key to [[Databases/MyMusic#genre|genre table]]
|-
|-
|idSong || integer || Foreign key to [[Databases#song|song table]]
|idSong || integer || Foreign key to [[Databases/MyMusic#song|song table]]
|-
|-
|iOrder || integer || Genre order when concatenating to single string
|iOrder || integer || Genre order when concatenating to single string
Line 620: Line 733:




== version(music) ==  
== source ==
List of Sources
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idSource || integer || Primary Key
|-
|strName || text || Folder name
|-
|strMultipath || text || Path or multipath:// of source folder(s)
|}
 
 
 
== source_path ==
A source can be multi-path (made as a collection of separate folders). The source_path table holds the separate paths per individual source
 
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idSource || integer
| rowspan="2" style="text-align: left;" | Primary Key and Foreign key to [[Databases/MyMusic#source|source table]]
|-
|idPath || integer
|-
|strPath || varchar(512) || Path to source folder
|}
 
 
 
== version ==
Database version
{|class="prettytable"
{|class="prettytable"
! Column Name || Data Type || Description
! Column Name || Data Type || Description
Line 628: Line 773:
|iCompressCount || integer || Number of times database has been compressed
|iCompressCount || integer || Number of times database has been compressed
|}
|}
== versiontagscan ==
Used to hold minimal scanning process related data
{|class="prettytable"
! Column Name || Data Type || Description
|-
|idVersion || integer || Keeps track of what version of db ''tag processing'' was used to populate the db
|-
|iNeedsScan || integer || Is set per Kodi release to indicate the minimum db version needed before a ''forced'' rescan on tags is necessary. Used when newer versions have related tag scanning changes
|-
|lastscanned || varchar(20) || Date & Time last scan was run. Useful because when rescanning the ids change and add-ons can check if their own data is still valid or a resync is required.
|-
|lastcleaned || varchar(20) ||
|-
|artistlinksupdated ||varchar(20) ||
|-
|genresupdated ||varchar(20) ||
|}




{{Top}}
{{Top}}
{{updated|19}}


{{updated|17}}
[[Category:Index]]
[[Category:Manual]]
[[Category:Karellen]]
[[Category:Karellen]]
[[Category:Development]]
[[Category:Development]]
[[Category:Music library]]
[[Category:Music library]]
[[Category:Advanced topics]]
[[Category:Skin_development]]

Revision as of 23:46, 24 June 2021

Kodi Databases

See also:

Home icon grey.png   ▶ Development
▶ Userdata		 ▶ Databases ▶ MyMusic


The Music Library

This database contains all information concerning music files that has been added to the Music Library. It is used in the Music portion of Kodi.

The current stable release of Kodi is using the following version of the database:

Kodi v19 - MyMusic82.db


Warning: The databases are critical components of Kodi. We strongly urge that you do not interfere with them, unless you have knowledge and experience with database designs.


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
strSortName text See: Sort 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
strReleaseGroupMBID text The ID that groups variations of the same release
strArtists text Album artist(s) description (not always a concatenation of names)
strArtistSort text See: Sort Name
strGenres text Album genres (denormalised concatenation of values in genre table)
strReleaseDate text Release date of album (re-releases)
strOrigReleaseDate text Original release date of album
bBoxedSet integer 0 = Not part of a Box Set
1 = Part of a Box Set
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"
strReleaseStatus text See: MusicBrainz Release Status
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
bScrapedMBID integer Indicates if mbid(s) have been fetched by scraping or from tags
lastScraped varchar(20) Date & time of last scrape
dateAdded text Music file timestamp of newest song on album
dateNew text Music file timestamp of newest song on album
dateModified text Music file timestamp of newest song on album
iTimesPlayed integer Average of number of times songs on album are played
strReleaseType text Internal type - "album" or "single"
iDiscTotal integer Number of discs in the Release
lastPlayed text Last date any song on album was played
iAlbumDuration integer Duration of all songs from that album currently in the library


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
strSortName text See: Sort Name
strMusicBrainzArtistID text Musicbrainz artist ID
strType text Type of performer
strGender text Male or Female artist
strDisambiguation text See: Disambiguation Comment
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
bScrapedMBID integer Indicates if mbid(s) have been fetched by scraping or from tags
lastScraped varchar(20) Date & time of last scrape
dateAdded text Music file timestamp of newest song by artist
dateNew text Music file timestamp of newest song on album
dateModified text Music file timestamp of newest song on album


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
strSortName text See: Sort 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)
strArtistSort text See: Sort Name
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
strReleaseDate text Release date of album (re-releases)
strOrigReleaseDate text Original release date of album
strDiscSubtitle text Name of disc used in box sets
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 float 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
strReleaseStatus text See: MusicBrainz Release Status
bCompilation integer Compilation flag (song is on a compilation album)
bBoxedSet integer 0 = Not part of a Box Set
1 = Part of a Box Set
strAlbumArtists text Album artist(s) description (not always a concatenation of names)
strAlbumArtistSort text See: Sort Name
strAlbumReleaseType text Internal type - "album" or "single"
mood text Song mood
strReplayGain text Contains album gain, album peak, track gain, track peak data in one string
iBPM Integer Beats per Minute of song.
iBitRate integer Bit rate of recording
iSampleRate integer Sample rate of recording
iChannels integer Number of audio channels in the recording
iAlbumDuration integer Duration of all songs from that album currently in the library
iDiscTotal integer Total No of discs in the box set
dateAdded text Music file timestamp
dateNew text Music file timestamp of newest song on album
dateModified text Music file timestamp of newest song on album
Return to top


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>
strReleaseGroupMBID text The ID that groups variations of the same release MusicBrainz Release Group Id <musicbrainzreleasegroupid></musicbrainzreleasegroupid>
strArtistDisp text Album artist(s) description (not always a concatenation of names) ALBUMARTIST,

ALBUMARTISTS,

<artistdesc></artistdesc>
strArtistSort text See: Sort Name ARTISTSORT
strGenres text Album genres (denormalised concatenation of values in genre table) GENRE <genre></genre> ^^
strReleaseDate text Release date of album (re-releases)
strOrigReleaseDate text Original release date of album
bBoxedSet integer 0 = Not part of a Box Set
1 = Part of a Box Set
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>
strReleaseStatus text See: MusicBrainz Release Status
fRating float Album rating <rating max="10"></rating>
iVotes integer Votes (for album rating) <votes></votes>
iUserrating integer Album user rating (out of 10) <userrating max="10"></userrating>
lastScraped text Date/time data scraped from online sources or NFO files
bScrapedMBID integer Indicates if mbid(s) have been fetched by scraping or from tags
strReleaseType text Internal type - "album" or "single" <releasetype></releasetype>
iDiscTotal integer Number of discs in the Release
iAlbumDuration integer Duration of all songs from that album currently in the library
idInfoSetting integer Indicates which scraper setting was used for that item. 0 = default scraper.
Linked to infosetting table
dateAdded text Music file timestamp
dateNew text Music file timestamp of newest song on album
dateModified text Music file timestamp of newest song on album
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 albums to the Source folder

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_source

This table links artists and albums

Column Name Data Type Description
idSource integer Foreign key to source table
idAlbum integer Foreign key to album table


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 Used in combination with media_id- "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) 
  <thumb aspect="poster" preview=""></thumb>

  <fanart>
    <thumb preview=""></thumb>
  </fanart>


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>
strSortName text See: Sort Name ALBUMARTISTSORT
strType text Type of performer <type></type>
strGender text Male or Female artist <gender></gender>
strDisambiguation text See: Disambiguation Comment <disambiguation></disambiguation>
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> ^^
lastScraped varchar(20) Date last scraped metadata
bScrapedMBID integer Indicates if mbid(s) have been fetched by scraping or from tags
idInfoSetting integer Indicates which scraper setting was used for that item. 0 = default scraper.
Linked to infosetting table
dateAdded text Music file timestamp
dateNew text Music file timestamp of newest song on album
dateModified text Music file timestamp of newest song on album
Notes
** MusicFileTag column shows Vorbis/Flac tags. Click on column heading for other tag types
^^ Multiple entries of the tag are allowed


audiobook

For use with Audiobooks. Holds Resume Point information only.

Column Name Data Type Description
idBook integer Id of Resume Point
strBook varchar(256) Title link to album table
strAuthor text Author link to artist table
bookmark integer Resume Point (millisec)
file text Path to audiobook
dateAdded varchar(20) Date & Time bookmark created


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>
strReleaseGroupMBID text The ID that groups variations of the same release


genre

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


infosetting

As scrapers and scraper settings can be changed per individual Album and Artist, this table details the settings of additional scrapers used. If only the default scraper for Album and Artist are used, there are no entries here.

Column Name Data Type Description
idSetting integer Primary Key
strScraperPath text Scraper details
strSettings text Scraper settings


path

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


removed_link

Column Name Data Type Description
idArtist integer
idMedia integer
idRole integer


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 ** XML Tags
idSong integer ID from song table
idAlbum integer ID from album table
idPath integer Foreign key to path table
strArtistDisp text Album artist(s) description (not always a concatenation of names) ALBUMARTIST,

ALBUMARTISTS,

<artistdesc></artistdesc>
strArtistSort text See: Sort Name ALBUMARTISTSORT
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
strReleaseDate text Release date of album (re-releases)
strOrigReleaseDate text Original release date of album
strDiscSubtitle text Name of disc used in box sets
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
lastplayed varchar(20) Date & time last played
rating float Song rating
votes integer Votes for song rating
userrating integer Song rated by user (out of 10) RATING
comment text Song description or comment COMMENT
mood text Song mood MOOD
iBPM Integer Beats per Minute of song.
iBitRate integer Bit rate of recording
iSampleRate integer Sample rate of recording
iChannels integer Number of audio channels in the recording
strReplayGain text Contains album gain, album peak, track gain, track peak data in one string
dateAdded text Music file timestamp
dateNew text Music file timestamp of newest song on album
dateModified text Music file timestamp of newest song on album
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 table
idSong integer Foreign key to song table
iOrder integer Genre order when concatenating to single string


source

List of Sources

Column Name Data Type Description
idSource integer Primary Key
strName text Folder name
strMultipath text Path or multipath:// of source folder(s)


source_path

A source can be multi-path (made as a collection of separate folders). The source_path table holds the separate paths per individual source

Column Name Data Type Description
idSource integer Primary Key and Foreign key to source table
idPath integer
strPath varchar(512) Path to source folder


version

Database version

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


versiontagscan

Used to hold minimal scanning process related data

Column Name Data Type Description
idVersion integer Keeps track of what version of db tag processing was used to populate the db
iNeedsScan integer Is set per Kodi release to indicate the minimum db version needed before a forced rescan on tags is necessary. Used when newer versions have related tag scanning changes
lastscanned varchar(20) Date & Time last scan was run. Useful because when rescanning the ids change and add-ons can check if their own data is still valid or a resync is required.
lastcleaned varchar(20)
artistlinksupdated varchar(20)
genresupdated varchar(20)



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