Database Structure

2009-06-18T13:42:12Z

Dazb: /* Note regarding LIKE */ Typo "statemant"

{{DatabaseTablesList}}

==MediaMonkey 3.0==

== Database type ==
[http://www.sqlite.org SQLite] version 3
* Encoding: UTF-16le
* Text collate IUNICODE (except where indicated with a (*))

== Additions to SQLite ==
Check out [http://www.sqlite.org/docs.html SQLite documentation]. MediaMonkey adds some more functions that you can use in SQL queries:

=== Collations ===
* IUNICODE - Unicode language neutral case insensitive (Post [http://www.mediamonkey.com/forum/viewtopic.php?p=127635#127635 1] [http://www.mediamonkey.com/forum/viewtopic.php?p=79915#79915 2] 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. 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.
<source lang="vb">
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
</source>

If you for some reason, within an SQL-statement, need to link two fields using LIKE (instead of =) then use this syntax:
<source lang="sql">
SomeField LIKE REPLACE(REPLACE(REPLACE(SomeOtherField,'@','@@')'_','@_')'%','@%')
</source>

== Default Mediamonkey 3 Database location ==
'''Windows XP:''' C:\Documents and Settings\<USERNAME>\Local Settings\Application Data\MediaMonkey\MM.DB
<br>'''Windows Vista:''' C:\Users\<USERNAME>\AppData\Local\MediaMonkey\MM.DB

== 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.
<br>'''REAL''' The value is a floating point value, stored as an 8-byte IEEE floating point number.
<br>'''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 table|Albums]]: Contains all Albums and some Album specific info
*[[Artists table|Artists]]: Contains all Artists, AlbumArtists, Composers, Conductors and Lyricists with their track count
*[[ArtistsAlbums table|ArtistsAlbums]]: Specifies the relation between an Album and its AlbumArtist(s)
*[[ArtistsSongs table|ArtistsSongs]]: Specifies the relation between a Song and its Artist(s), Composer(s), Conductor(s) and Lyricist(s)
*[[Covers table|Covers]]
*[[DBInfo table|DBInfo]]
*[[DeviceFilters table|DeviceFilters]]
*[[DeviceTracks table|DeviceTracks]]
*[[Devices table|Devices]]
*[[Filters table|Filters]]: Contains all Filters/Views configured in MM
*[[Folders table|Folders]]
*[[FoldersHier table|FoldersHier]]
*[[Genres table|Genres]]: Contains all Genres with track Count
*[[GenresSongs table|GenresSongs]]: Specifies the relation between a Song and its Genre(s)
*[[LastTreePath table|LastTreePath]]
*[[Lists table|Lists]]: Contains all Classifications and their sort order in the tree
*[[ListsSongs table|ListsSongs]]: Specifies the relation between a Song and its various Classifications
*[[Medias table|Medias]]
*[[OrganizeRules table|OrganizeRules]]
*[[PathProcessing table|PathProcessing]]
*[[Played table|Played]]
*[[PlaylistSongs table|PlaylistSongs]] Specifies the relation between a Song and what '''Static Playlist(s)''' it appears in
*[[Playlists table|Playlists]] : Contains all Static Playlists and Auto-Playlists
*[[PodcastDirs table|PodcastDirs]]
*[[PodcastEpisodes table|PodcastEpisodes]] Specifies the relation between a Song and a Podcast
*[[Podcasts table|Podcasts]] Specifies podcast subscriptions
*[[Songs table|Songs]] : Contains all songs with all their properties
*[[SynchAlbum table|SynchAlbum]]
*[[SynchArtist table|SynchArtist]]
*[[SynchLocation table|SynchLocation]]
*[[SynchPlaylist table|SynchPlaylist]]
*[[SynchPodcast table|SynchPodcast]]

== Database structure ==
''(only most important tables)''

[[Image:MM3DBStructure.PNG|center|650px]]

----

----

==MediaMonkey v2.5 and lower==

===Database structure===
[[Image:DatabaseRelationsMM2.gif|center|650px]]

===AddSongInfo===
''Value field is "TextData"''

'''DataType'''
{| border="2"
|-
! 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)
{| border="2"
|-
! 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'''
{| border="2"
|-
! Value || AIF Code || Description
|-
| 20000 || AIF_LYRICS || Lyrics
|-
| 20001 || AIF_COMMENT || Comment
|}

MediaMonkey Wiki - User contributions [en]

Database Structure