Database Structure: Difference between revisions

Revision as of 21:23, 23 January 2008

Database type

SQLite version 3

Encoding: UTF-16le
Text collate IUNICODE (except where indicated with a (*))

Additions to SQLite

Check out SQLite documentation. MediaMonkey adds some more functions that you can use in SQL queries:

Collations

IUNICODE - Unicode language neutral case insensitive
NUMERICSTRING - Natural song order respecting numbers stored as strings. Useful for Track# and Disc# fields.

Functions

hassubfolders(string) - Used internally by MediaMonkey.
instr(string1, string2) - Returns 1-based index of the first occurence of string1 in string2, returns -1 otherwise. Case sensitive.
like(string1, string2) - Used to replace internal SQLite LIKE operator with one that supports Unicode strings.
nextpathpart(string) - Used internally by MediaMonkey.
sin(number) - Mathematical sinus function.
stricompw(string1, string2) - Compares two Unicode strings, case insensitive.
upperw(string) - Unicode string upper case.

Database location

Windows XP: C:\Documents and Settings\<USERNAME>\Local Settings\Application Data\MediaMonkey\MM.DB
Windows Vista: C:\Users\<USERNAME>\AppData\Local\MediaMonkey\MM.DB

Used data types

INTEGER The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT The value is a text string, stored using the database encoding (UTF-16-LE).

Structure explanation

The most important table is the Songs table, which is the starting point for all song-specific info. To allow rapid requests, most of the information is present in the table itself, e.g. the Artist name is spelled out. The link between table Songs and other tables like Artists, Albums, ... happens with intermediary tables, e.g. ArtistsSongs. This allows us to assign multiple songs to one artist, AND multiple artists to one song.

The database contains custom events, called "triggers", that are executed when e.g. an artist is assigned to a track. For that example, the trigger will automatically increment the Artists table's Tracks counter field.

Database tables

Albums: Contains all Albums and some Album specific info.
Artists: Contains all Artists, AlbumArtists, Composers, Conductors and Lyricists with their track count.
ArtistsAlbums: Specifies the relation between an Album and AlbumArtist(s)
ArtistsSongs: Specifies the relation between a Song and Artist(s), Composer(s), Conductor(s) and Lyricist(s)
Covers
DBInfo
DeviceFilters
DeviceTracks
Devices
Filters
Folders
FoldersHier
Genres: Contains all Genres with track Count
GenresSongs: Specifies the relation between a Song and Genre(s)
LastTreePath
Lists
ListsSongs: Specifies the relation between a Song and all various Classifications.
Medias
OrganizeRules
PathProcessing
Played
PlaylistSongs
Playlists
PodcastDirs
PodcastEpisodes
Podcasts
Songs
SynchAlbum
SynchArtist
SynchLocation
SynchPlaylist
SynchPodcast

MediaMonkey v2.5 and lower