Database Structure: Difference between revisions

Latest revision as of 19:43, 11 April 2024

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 (Post 1 2 by Jiri with more detail)
USERLOCALE - Sorts according to users locale in regional settings. (Implemented in 3.1.2.1279)
NUMERICSTRING - Natural song order respecting numbers stored as strings. Useful for Track# and Disc# fields.

Due the customized collations MediaMonkey for Windows extension Library can be downloaded from http://www.happymonkeying.com/beta/SQLite3MMExt.dll and then used in any app that can load and use external extension like free Database Browser for SQLite

Functions

HASSUBFOLDERS(string) - Used internally by MediaMonkey.
INSTR(string1, string2) - Returns 1-based index of the first occurrence of string2 in string1, if not found 0 is returned. Case sensitive.
LIKE(string1, string2) - Used to replace internal SQLite LIKE operator with one that supports Unicode strings. SEE NOTE!
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.
GETEXTENSION(string) - Returns the extension from a filename. (Introduced in MM 3.1)
SOUNDEX(string) - Returns a four-character string, based on how the word is pronounced in English. Similar spelled names are supposed to returns the same string. (Introduced in MM 3.1)

Note regarding LIKE

This MM-specific implementation of LIKE, automatically uses the @-sign as the escape character and it is always applied. It means that you need to modify the SearchString to correctly use this function.

Here is one example of an VBScript function which prepares the search string correctly.

Function FixLikeSearchString(Str)
  Str = Replace(Str,"@","@@")
  Str = Replace(Str,"_","@_")
  Str = Replace(Str,"%","@%")
  Str = Replace(Str,"'","''") '<--- Single quotes are escaped with another single quote
  FixLikeSearchString=Str
End Function

If you for some reason, within an SQL-statement, need to link two fields using LIKE (instead of =) then use this syntax:

SomeField LIKE REPLACE(REPLACE(REPLACE(SomeOtherField,'@','@@')'_','@_')'%','@%')

Default MediaMonkey 5+ Database location

Standard installation: C:\Users\<USERNAME>AppData\Roaming\MediaMonkey5\MM5.DB

Portable installation: <INSTALL LOCATION>\Portable\MM5.DB

Default MediaMonkey 3 and MediaMonkey 4 Database location

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

Windows Vista and Windows 7, before 2016-01-06: C:\Users\<USERNAME>\AppData\Local\MediaMonkey\MM.DB

Windows 7 through 11 (64-bit), after 2016-01-06: C:\Users\<USERNAME>\AppData\Roaming\MediaMonkey\MM.DB

NOTE: When you are looking for the default mediamonkey.ini or mm.db, you may have to enable "View hidden/system files" in your windows explorer options or search.

Default MediaMonkey 2 Database location

My Documents\My Music\Mediamonkey\Mediamonkey.mdb

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 its AlbumArtist(s)
ArtistsSongs: Specifies the relation between a Song and its Artist(s), Composer(s), Conductor(s) and Lyricist(s)
Covers
DBInfo
DeviceFilters
DeviceTracks
Devices
Downloads
Filters: Contains all Filters/Views configured in MM
Folders
FoldersHier
Genres: Contains all Genres with track Count
GenresSongs: Specifies the relation between a Song and its Genre(s)
LastTreePath
Lists: Contains all Classifications and their sort order in the tree
ListsSongs: Specifies the relation between a Song and its various Classifications
Medias: Contains all the media sources that have been used
OrganizeRules
PathProcessing
Pinned: Stores links to Pinned items (Added in version 5.0)
Played
PlaylistSongs Specifies the relation between a Song and what Static Playlist(s) it appears in
Playlists : Contains all Static Playlists and Auto-Playlists
PodcastDirs
PodcastEpisodes Specifies the relation between a Song and a Podcast
Podcasts Specifies podcast subscriptions
Radio: Stores links to the Radio node (Added in version 5.0)
RemoteClients
Songs : Contains all songs with all their properties
SynchAlbum
SynchArtist
SynchLocation
SynchPlaylist
SynchPodcast
URLRequestCache: Caches the results of some web requests.

MediaMonkey 3 Database structure

(only most important tables)

MediaMonkey 2.5 and lower