Database Structure (MM4)
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:
- 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 220.127.116.119)
- 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
- 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 3 Database location
Windows XP: C:\Documents and Settings\<USERNAME>\Local Settings\Application Data\MediaMonkey\MM.DB
Windows Vista and Windows 7: C:\Users\<USERNAME>\AppData\Local\MediaMonkey\MM.DB or (on a Win7-64 platform as of 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).
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.
- 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)
- Filters: Contains all Filters/Views configured in MM
- Genres: Contains all Genres with track Count
- GenresSongs: Specifies the relation between a Song and its Genre(s)
- 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
- PlaylistSongs Specifies the relation between a Song and what Static Playlist(s) it appears in
- Playlists : Contains all Static Playlists and Auto-Playlists
- PodcastEpisodes Specifies the relation between a Song and a Podcast
- Podcasts Specifies podcast subscriptions
- Songs : Contains all songs with all their properties
(only most important tables)
MediaMonkey v2.5 and lower
Value field is "TextData"
Note: Apparently, the value for the composer is stored in the Author field of the Songs table rather than in the AddSongInfo table under MediaMonkey 2.5. The situation in other versions is unknown.
Value field is "IntData" (linked to Lists.ID)
DataType (partially linked to Lists.IDListType)
Value field is "MemoText"