Difference between revisions of "MediaMonkey Database structure"

From MediaMonkey Wiki
Jump to: navigation, search
(Database tables)
Line 66: Line 66:
 
*[[Playlists table|Playlists]] : Contains all Static Playlists and Auto-Playlists
 
*[[Playlists table|Playlists]] : Contains all Static Playlists and Auto-Playlists
 
*[[PodcastDirs table|PodcastDirs]]
 
*[[PodcastDirs table|PodcastDirs]]
*[[PodcastEpisodes table|PodcastEpisodes]]
+
*[[PodcastEpisodes table|PodcastEpisodes]] Specifies the relation between a Song and a Podcast
*[[Podcasts table|Podcasts]]
+
*[[Podcasts table|Podcasts]] Specifies podcast subscriptions
 
*[[Songs table|Songs]] : Contains all songs with all their properties
 
*[[Songs table|Songs]] : Contains all songs with all their properties
 
*[[SynchAlbum table|SynchAlbum]]
 
*[[SynchAlbum table|SynchAlbum]]

Revision as of 16:58, 20 March 2008


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 by Jiri with more detail)
  • 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 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.
  • 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

Database structure

(only most important tables)

MM3DBStructure.PNG





MediaMonkey v2.5 and lower

Database structure

DatabaseRelationsMM2.gif


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