Export to iTunes library.xml

[DC]

Hi.
ADDON IS UPDATED TO WORK WITH LATEST VERSION OF MMW AND OTHER APPS. IT CAN BE DOWNLOADED HERE
MORE INFO AT THIS POST

Has there been some work as to exporting a snapshot of the MM database to an iTunes library.xml file? I searched the forums and found a few requests about this but nothing substantial. As with the other requests I want to use a program which can interface with the iTunes library.xml file but no the MM database.

In this case it is Logitech's SqueezeCenter which is a Perl based server software for the Squeezebox family of audio streaming devices. Apparently I have two choices to make SqueezeCenter work with the MM data:
a) learn Perl and create a SqueezeCenter plugin for MM or
b) misuse the existing SqueezeCenter iTunes plugin which reads the library.xml file.

Since SqueezeCenter uses a MySQL database there is probably another option,
c) copy data directly from MM db to SC db.

I guess b) is the most versatile way and arguably also the easiest way. c) surely is more efficient.

EDIT: please see below for a script which implements approach B.

[trixmoto]

I have written a couple of scripts which read iTunes XML files and import the data into MM, but not an export script. This would certainly possible though! However, you're right that option C would probably be more efficient, although A would probably be even more efficient still.

[DazB]

Hi,

The XML file doesn't give you all the details - Apple isn't going to make it so easy... you need to piece it back together from three separate sources (four if you want the best quality artwork).

As it is a property list, it is really more of an ini file than a true XML structure, so significant performance gains can be had by preprocessing it with either Regular Expressions or XSLT - especially so for large libraries.

I have yet to work out how to generate the link to all the external (local) artwork - store purchases are easy, the others may be more of a curiosity as you only get lower quality versions (which can be got at via other means).

If your are still using your iTunes library then the good news is that the XML file is updated in real-time, the bad is that MM hasn't the flexibility/fields to make full use of the data. I have toyed with the idea of generating an XML file to be put in the same location as the tracks - MM3 will manage the moving of it and the data could be easily got at via scripting.

Although it will be reinventing the wheel as it were, I will be investigating further once I have finished playing with my touch.

Daz

[DC]

I have written a couple of scripts which read iTunes XML files and import the data into MM, but not an export script. This would certainly possible though! However, you're right that option C would probably be more efficient, although A would probably be even more efficient still.

The problem with A is Perl. For that I would have to learn Perl, then use some COM bridge from CPAN (if it exists). In my most humble opinion, Perl is a pimped awk. I don't understand why someone would create complete web applications and servers with it. For platform independency I would suggest Java which excels in all required areas. But that ship has sailed anyway.

That means I will have to install iTunes to get an example xml...

[DC]

The XML file doesn't give you all the details - Apple isn't going to make it so easy...
[...]
If your are still using your iTunes library...
[...]

Daz, your reply left me a bit confused. It looks as if you think I would want to import iTunes data into MM - on the contrary. I have no iTunes installed and am using the xml just as a vessel to transport data from MM to SqueezeCenter. Otherwise, SC would frequently rescan xx gigabytes which are already in MM's database...

[nohitter151]

What about http://www.mediamonkey.com/forum/viewto ... =2&t=21124 ?

[DC]

What about http://www.mediamonkey.com/forum/viewto ... =2&t=21124 ?

Thanks. So it uses the SqueezeCenter CLI to remote control the SqueezeBox, although only to sync with MM's Now Playing. Just installed that script. Unfortunately it does not work for me: after setting up the server IP I did not get error messages anymore but it seems to do nothing. It certainly does not sync Now Playing with the SB3. That means I have another option now: d) debug the script.

FWIW, one year ago I began a project to make the full SC CLI available to Windows scripting. I got half-way through when my spare time ran out and since then lost a bit of motivation...

[DC]

So I made a script that exports the database (songs and playlists) into iTunes' library.xml. That works fairly well and as I intended to I can import that xml from within SqueezeCenter again.

Just for kicks a few stats (based on a C2Q 3.0GHz, 4 GB RAM system):

• 11000 songs lead to a 13 MB xml file
• the export takes about 30s
• the first import in SqueezeCenter took about 9 minutes
• subsequent imports take about 1 minute

I think those values are acceptable. The script runs once per hour (timer controlled) and can also be called manually via File | Create Reports | Tracks and Playlists (iTunes library.xml). The data is then saved to a file "iTunes Music Library.xml" into the database folder. According to the Wiki this will be:

Windows XP: C:\Documents and Settings\<USERNAME>\Local Settings\Application Data\MediaMonkey
Windows Vista: C:\Users\<USERNAME>\AppData\Local\MediaMonkey

If you have changed the db location (like me) it will also consider that.

Please note: this script is not intended to export MM data to iTunes. I doubt that will work because some data (like iTunes internal persistent IDs or album ratings) is not available in MM. Also missing at this point: volume rating (gain, leveling, etc), album art and support for any non-mp3 typed files. The sole purpose of this script is to make MM data available in a format which can be read by other programs, which includes the iTunes plugin in SqueezeCenter.

Without further ado, here is the script which should be copied to "MM\Scripts\Auto\Export to iTunes library-xml.vbs":

Code: Select all

option explicit     ' report undefined variables, ...

' Customize options below; then (re)start MM.
const ENABLE_TIMER = true ' change to false to prevent automatic exporting once per hour
const QUERY_FOLDER = false ' set tp true to be asked each time where to save the iTunes xml file

' End of options.

'  ------------------------------------------------------------------
const EXPORTING = "itunes_export_active"

' Returns the XML suitable escaped version of the srcstring parameter.
' This function is based on MapXML found in other MM scripts, e.g.
' Export.vbs, but fixes a unicode issue and is probably faster.
' Note that a bug in AscW still prevents the correct handling of unicode
' codepoints > 65535.
function escapeXML(srcstring)
  dim i, codepoint, currentchar, replacement
  i = 1
  while i <= Len(srcstring)
    currentchar = Mid(srcstring, i, 1)
    replacement = Null
    if currentchar = "&" then
      replacement = "&"
    elseif currentchar = "<" then
      replacement = "<"
    elseif currentchar = ">" then
      replacement = ">"
    else
      codepoint = AscW(currentchar)
      if codepoint < 0 then ' adjust for negative (incorrect) values, see also http://support.microsoft.com/kb/272138
        codepoint = codepoint + 65536
      end if
      
      ' Important: reject control characters except tab, cr, lf. See also http://www.w3.org/TR/1998/REC-xml-19980210.html#NT-Char
      if codepoint > 127 or currentchar = vbTab or currentchar = vbLf or currentchar = vbCr then
        replacement = "&#" + CStr(codepoint) + ";"
      elseif codepoint < 32 then
        replacement = ""
      end if
    end if
    
    if not IsNull(replacement) then    
      srcstring = Mid(srcstring, 1, i - 1) + replacement + Mid(srcstring, i + 1, Len(srcstring))
      i = i + Len(replacement) - 1 ' 07.10.2010: no need to parse that #99999; stuff again although it does no harm
    end if
    i = i + 1
  wend
  escapeXML = srcstring
end function

' N must be numberic. Return value is N converted to a string, padded with
' a single "0" if N has only one digit.
function LdgZ(N)    
  if (N >= 0) and (N < 10) then 
    LdgZ = "0" & N 
  else 
    LdgZ = "" & N  
  end if  
end function  

' Adds a simple key/value pair to the XML accessible via textfile fout.
sub addKey(fout, key, val, keytype)
  if keytype = "string" then
    if val = "" then ' nested if because there is no shortcut boolean eval
      exit sub
    end if
  end if
  
  if keytype = "integer" then
    if val = 0 then ' nested if because there is no shortcut boolean eval
      exit sub
    end if
  end if
  
  if keytype = "date" then ' convert date into ISO-8601 format
    val = Year(val) & "-" & LdgZ(Month(val)) & "-" & LdgZ(Day(val)) _
      & "T" & LdgZ(Hour(val)) &  ":" & LdgZ(Minute(val)) & ":" & LdgZ(Second(val))
  end if
  
  fout.WriteLine "			<key>" & key & "</key><" & keytype & ">" & val & "</" & keytype & ">"
end sub

' Return the full path of the file to export to. The file will be located 
' in the same folder as the database because this folder is writable and user
' specific. For maximum compatibility we will use the original iTunes name
' which is "iTunes Music Library.xml".
' 29.03.2009: if the new option QUERY_FOLDER is set to true this function
' will query for the folder to save to instead.
function getExportFilename()
  dim path
  if QUERY_FOLDER then
    dim inif
    set inif = SDB.IniFile
    path = inif.StringValue("Scripts", "LastExportITunesXMLDir")
    path = SDB.SelectFolder(path, SDB.Localize("Select where to export the iTunes XML file to."))
    if path = "" then
      exit function
    end if
    if right(path, 1) <> "\" then
      path = path & "\"
    end if
    inif.StringValue("Scripts", "LastExportITunesXMLDir") = path
    set inif = Nothing  
  else
    dim dbpath : dbpath = SDB.Database.Path
    dim parts : parts = split(dbpath, "\")
    dim dbfilename : dbfilename = parts(UBound(parts))
    path = Mid(dbpath, 1, Len(dbpath) - Len(dbfilename))
  end if
  getExportFilename = path + "iTunes Music Library.xml"
end function

' Exports the full MM library and playlists into an iTunes compatible
' library.xml. This is not intended to make MM's database available to
' iTunes itself but to provide a bridge to other applications which are
' able to read the iTunes library xml.
sub export
  if SDB.Objects(EXPORTING) is nothing then
    SDB.Objects(EXPORTING) = SDB
  else
    MsgBox SDB.Localize("iTunes export is already in progress."), 64, "iTunes Export Script"
    exit sub
  end if

  dim filename, fso, iter, songCount, fout, progress, song, playlistCount
  dim progressText, i, j, tracks, playlist
  
  filename = getExportFilename()
  if filename = "" then
    SDB.Objects(EXPORTING) = nothing
    exit sub
  end if

  set fso = SDB.Tools.FileSystem
  set fout = fso.CreateTextFile(filename, true)

  set iter = SDB.Database.OpenSQL("select count(*) from SONGS")
  songCount = Int(iter.ValueByIndex(0)) ' needed for progress
  set iter = SDB.Database.OpenSQL("select count(*) from PLAYLISTS")
  playlistCount = CInt(iter.ValueByIndex(0)) 

  set progress = SDB.Progress
  progressText = SDB.Localize("Exporting to iTunes library.xml...")
  Progress.Text = progressText
  Progress.MaxValue = songCount + playlistCount * 50

  fout.WriteLine "<?xml version=""1.0"" encoding=""UTF-8""?>"
  fout.WriteLine "<!DOCTYPE plist PUBLIC ""-//Apple Computer//DTD PLIST 1.0//EN"" ""http://www.apple.com/DTDs/PropertyList-1.0.dtd"">"
  fout.WriteLine "<plist version=""1.0"">"
  fout.WriteLine "<dict>"
  fout.WriteLine "	<key>Major Version</key><integer>1</integer>"
  fout.WriteLine "	<key>Minor Version</key><integer>1</integer>"
  fout.WriteLine "	<key>Application Version</key><string>7.6</string>"
  fout.WriteLine "	<key>Features</key><integer>5</integer>" ' whatever that means
  fout.WriteLine "	<key>Show Content Ratings</key><true/>"
  ' Fields not available in MM:
  ' fout.WriteLine "	<key>Music Folder</key><string>file://localhost/C:/....../iTunes/iTunes%20Music/</string>"
  ' fout.WriteLine "	<key>Library Persistent ID</key><string>4A9134D6F642512F</string>"

  ' Songs
  ' 
  ' For each song write available tag values to the library.xml. At this time 
  ' this does not include artwork, volume leveling and album rating.
  if songCount > 0 then
    fout.WriteLine "	<key>Tracks</key>"
    fout.WriteLine "	<dict>"
    i = 0
    set iter = SDB.Database.QuerySongs("")
    while not iter.EOF and not Progress.Terminate and not Script.Terminate
      set song = iter.Item
      iter.next

      ' %d always inserts 0, don't know why
      i = i + 1
      progress.Text = progressText & " " & SDB.LocalizedFormat("%s / %s songs", CStr(i), CStr(songCount), 0)
      if i mod 50 = 0 then
        SDB.ProcessMessages
      end if

      fout.WriteLine "		<key>" & Song.id & "</key>"
      fout.WriteLine "		<dict>   "
      addKey fout, "Track ID", Song.id, "integer"
      addKey fout, "Name", escapeXML(Song.Title), "string"
      addKey fout, "Artist", escapeXML(Song.ArtistName), "string"
      addKey fout, "Composer", escapeXML(Song.MusicComposer), "string"
      addKey fout, "Album Artist", escapeXML(Song.AlbumArtistName), "string"
      addKey fout, "Album", escapeXML(Song.AlbumName), "string"
      addKey fout, "Kind", escapeXML("MPEG audio file"), "string"
      addKey fout, "Size", Song.FileLength, "integer"
      addKey fout, "Genre", escapeXML(Song.Genre), "string"
      addKey fout, "Total Time", Song.SongLength, "integer"
      addKey fout, "Track Number", Song.TrackOrder, "integer" ' potential type problem with TrackOrderStr
      addKey fout, "Disc Number", Song.DiscNumber, "integer" ' potential type problem with DiscNumberStr
      addKey fout, "Play Count", Song.PlayCounter, "integer"
      if Song.Rating >= 0 and Song.Rating <= 100 then
        addKey fout, "Rating", Song.Rating, "integer" ' rating seems to be compatible in range (although not stored in same id3 tag)
      end if
      addKey fout, "Year", Song.Year, "integer"
      addKey fout, "Date Modified", Song.FileModified, "date"
      addKey fout, "Date Added", Song.DateAdded, "date"
      addKey fout, "Bit Rate", Int(Song.Bitrate / 1000), "integer"
      addKey fout, "Sample Rate", Song.SampleRate, "integer"
      addKey fout, "Track Type", escapeXML("File"), "string"
      addKey fout, "File Folder Count", -1, "integer"
      addKey fout, "Library Folder Count", -1, "integer"
      addKey fout, "Comments", escapeXML(Song.Comment), "string"
      addKey fout, "Location", "file://localhost/" & Replace(Replace(Escape(Song.Path), "%5C", "/"), "%3A", ":"), "string"

      ' TODO artwork?
      ' addKey fout, "Artwork Count", 0, "integer"
      ' TODO convert to iTunes rating range. MM: -99999...?. iTunes: -255 (silent) .. 255
      ' fout.WriteLine "			<key>Volume Adjustment</key><integer>" & escapeXML(Song.Leveling) & "</integer>" 

      ' Fields not available in MM:
      ' fout.WriteLine "			<key>Disc Count</key><integer>" & escapeXML(Song.?) & "</integer>"
      ' fout.WriteLine "			<key>Album Rating</key><integer>" & escapeXML(Song.?) & "</integer>"
      ' fout.WriteLine "			<key>Persistent ID</key><string>5282DFDE369975A8</string>"

      fout.WriteLine "		</dict>"

      Progress.Increase
    wend
    fout.WriteLine "	</dict>"
  end if
  SDB.ProcessMessages
  
  ' Playlists
  '
  ' This part differs at least with the following items from an original iTunes 
  ' library.xml:
  ' - iTunes includes a playlist named "Library" with all songs, we don't
  ' - every iTunes playlist has a "Playlist Persistent ID", e.g. "4A9134D6F6425130"
  '   We don't have that data.
  '
  ' Also note: auto-playlists are evaluated once and are exported like that. They
  ' are not converted into iTunes auto-playlists. A consequence of this is that
  ' e.g. randomized or size-limited playlists will contain a static snapshot taken
  ' at export time.
  if playlistCount > 0 and not Progress.Terminate and not Script.Terminate then
    fout.WriteLine "	<key>Playlists</key>"
    fout.WriteLine "	<array>"
    
    ' Get playlists and store them into an array. Make sure that we do not have
    ' an open query while playlist.Tracks is evaluated because that will fail
    ' (it wants to start a db transaction but can't because a query is still open)
    dim playlists()
    set iter = SDB.Database.OpenSQL("select PlaylistName from PLAYLISTS")
    i = 0
    while not iter.EOF 
      set playlist = SDB.PlaylistByTitle(iter.StringByIndex(0))
      if playlist.Title <> "Accessible Tracks" then ' this would correspond to iTunes' "Library" playlist
        redim preserve playlists(i)
        set playlists(i) = playlist
        i = i + 1
      end if
      iter.next
    wend      
    set iter = nothing

    for each playlist in playlists
      set tracks = playlist.Tracks
      ' %d always inserts 0, don't know why
      i = i + 1
      progress.Text = progressText & " " & SDB.LocalizedFormat("playlist ""%s"" (%s songs)", playlist.Title, CStr(tracks.Count), 0)
      SDB.ProcessMessages

      fout.WriteLine "		<dict>"
      addKey fout, "Name", escapeXML(playlist.Title), "string"
      ' Apparently only used for "Library" playlist:
      ' addKey fout, "Master", Nothing, "true"
      ' addKey fout, "Visible", Nothing, "empty"
      addKey fout, "Playlist ID", playlist.ID, "integer"
      ' No MM field for this:
      ' addKey fout, "Playlist Persistent ID", "4A9134D6F6425130", "string"
      fout.WriteLine "			<key>All Items</key><true/>"
      if tracks.Count > 0 then      
        fout.WriteLine "			<key>Playlist Items</key>"
        fout.WriteLine "			<array>"
        for j = 0 to tracks.Count - 1
          fout.WriteLine "				<dict>"
          fout.WriteLine "					<key>Track ID</key><integer>" & tracks.Item(j).ID & "</integer>"
          fout.WriteLine "				</dict>"
        next 
        fout.WriteLine "			</array>"
      end if
      fout.WriteLine "		</dict>"
            
      progress.Value = progress.Value + 50
      if Progress.Terminate or Script.Terminate then
        exit for
      end if
    next 
    fout.WriteLine "	</array>"
  end if
  
  fout.WriteLine "</dict>"
  fout.WriteLine "</plist>"
  fout.Close ' Close the output file and finish

  dim ok : ok = not Progress.Terminate and not Script.Terminate
  set Progress = Nothing
  on error resume next
  if not ok then
    fso.DeleteFile(filename) ' remove the output file if terminated
  end if
  SDB.Objects(EXPORTING) = nothing
end sub

sub timedExport(exportTimer)
  if SDB.Objects(EXPORTING) is nothing then
    export
  end if
end sub

' Called when MM starts up, installs a timer to export the data
' frequently to the iTunes library.xml.
sub OnStartup
  if ENABLE_TIMER then
    dim exportTimer : set exportTimer = SDB.CreateTimer(3600000) ' export every 60 minutes
    Script.RegisterEvent exportTimer, "OnTimer", "timedExport"
  end if
end sub

In MM\Scripts\scripts.ini, append the following lines:

Code: Select all

[ExportITunes]
FileName=Auto\Export to iTunes library-xml.vbs
ProcName=Export
Order=5
DisplayName=Tracks and Playlists (&iTunes library.xml)
Description=Exports all tracks and playlists to an iTunes library.xml file
Language=VBScript
ScriptType=1

[trixmoto]

I'm glad you've come up with a working solution. You could try outputting the playlist name and total number of tracks before this line so that you know exactly what playlist is causing the problem. Maybe it appears to be hanging but it's actually an autoplaylist that is pulling in a massive number of tracks.

[DC]

I'm glad you've come up with a working solution. You could try outputting the playlist name and total number of tracks before this line so that you know exactly what playlist is causing the problem. Maybe it appears to be hanging but it's actually an autoplaylist that is pulling in a massive number of tracks.

Well, I did that already. Maybe I should have mentioned it. The playlist is a randomized auto-playlist with a 74m limitation. Therefore it has less than 20 tracks. But even if it had 2000 tracks: the first time always works smoothly, all playlists export in a second. Only the second time shows the problem (sometimes it is only the 3rd time). And there hasn't been any data change in between: I always restart MM and immediately execute the export twice.

Unfortunately this is quite a show stopper. Either I remove the playlist export which I don't really want to or MM will hang when data gets exported for the second time and it has to be killed (!).

PS: and I cannot output the number of tracks from within the script before that line because that would require evaluation of playlist.Tracks.Count - while playlist.Tracks already hangs.

[trixmoto]

Sounds like it could be an MM bug, I guess.

[DC]

Good news, the issue seems to be fixed. Basically, what I did before is this:

Code: Select all

set iter = SDB.Database.OpenSQL("select PlaylistName from PLAYLISTS")
while not iter.EOF 
  set playlist = SDB.PlaylistByTitle(iter.StringByIndex(0))
  set tracks = playlist.Tracks
  ...
wend

When I used the MM debug version I got a proper error report that playlist.Tracks cannot execute because it wants to open a transaction while a query (the playlist-name query) was still open. With that information I extracted the query loop and stored playlists temporarily in an array which I could iterate after that. Which did the trick (I also had to assign "Nothing" to the cursor-iterator variable).

I must admit though that I have never seen a DBMS which does not allow transactions while a query is still open. I wonder if this is something MM specific or an SQLite issue.

The script in the posting above includes the fix now.

[gozgap]

The script seems to work as an export tool to itunes, but play count, ratings, and album art are gone

[DC]

That is surprising because iTunes primarily uses the binary *.itl file which contains more data than the xml according to Apple. Play count and ratings are part of the xml export but seem to be ignored by iTunes then. Album art is another story: this is not in the xml but in some nested iTunes folders, the structure of which would have to be reverse engineered first (also, each album art file has a proprietary header).

[MM3FAN-Y]

Hi ...

i m not an advanced user ..

would appreciate if i can have an installer for the above script