Database Structure (MM4)

From MediaMonkey Wiki
Jump to navigation Jump to search


MediaMonkey 3.0

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


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

Database structure

(only most important tables)





MediaMonkey v2.5 and lower

Database structure


AddSongInfo

Value field is "TextData"


DataType

Value AIF Code Description
100 AIF_COPYRIGHT Copyright
101 AIF_PUBLISHER Publisher
102 AIF_ENCODER Encoder
103 AIF_ENCODEDBY Encoded By
200 AIF_LYRICIST Lyricist
201 AIF_BAND Band
202 AIF_CONDUCTOR Conductor
203 AIF_REMIXER Remixer
204 AIF_INVOLVEDPEOPLE Involved People
205 AIF_MUSICCOMPOSER (See note)
300 AIF_ORIGTITLE Original Title
301 AIF_ORIGARTIST Original Artist
302 AIF_ORIGLYRICIST Original Lyricist
400 AIF_GROUPDESC
401 AIF_SUBTITLE
402 AIF_ISRC
500 AIF_INITIALKEY
501 AIF_LANGUAGE
502 AIF_MEDIATYPE
503 AIF_RATINGSTRING
1000 AIF_WEB_COMMERCIAL
1001 AIF_WEB_COPYRIGHT
1002 AIF_WEB_FILEPAGE
1003 AIF_WEB_ARTIST
1004 AIF_WEB_SOURCE
1005 AIF_WEB_RADIO
1006 AIF_WEB_PAYMENT
1007 AIF_WEB_PUBLISHER
1100 AIF_WEB_USER

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.

AddSongInfoInt

Value field is "IntData" (linked to Lists.ID)


DataType (partially linked to Lists.IDListType)

Value AIF Code Description
10000 AIF_ORIGYEAR Original Year
10101 AIF_TEMPO Tempo
10102 AIF_MOOD Mood
10103 AIF_OCCASION Occasion
10104 AIF_QUALITY Quality

<references/>


Memos

Value field is "MemoText"


MemoType

Value AIF Code Description
20000 AIF_LYRICS Lyrics
20001 AIF_COMMENT Comment