sqlTunes是一款小巧的工具，旨在弥补iTunes播放器在报告生成方面的不足。通过这个程序，可以将iTunes音乐库数据导出到Microsoft SQL Server数据库中，然后使用T-SQL进行查询。本文将快速回顾iTunes库数据的结构，并列出一些可以运行的报告。
iTunes将音乐库数据存储在两个文件中：一个称为iTunes Library.itl的专有二进制文件，以及它的XML对应文件iTunes Music Library.xml。这两个文件都位于"My Documents\My Music\iTunes\"文件夹中。
XML文件本质上是一个通用字典的表示。首先列出库信息，然后是音轨数据，最后是播放列表。以下是XML文件的示例：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Major Version</key>
<integer>1</integer>
<key>Minor Version</key>
<integer>1</integer>
<key>Application Version</key>
<string>6.0.1</string>
<key>Features</key>
<integer>1</integer>
<key>Music Folder</key>
<string>file://localhost/C:/Archive/Audio/iTunes/</string>
<key>Library Persistent ID</key>
<string>F8D57E57036B9A4E</string>
<key>Tracks</key>
<dict>
<key>36</key>
<dict>
<key>Track ID</key>
<integer>36</integer>
<key>Name</key>
<string>...</string>
<key>Artist</key>
<string>...</string>
<key>Album</key>
<string>...</string>
...
</dict>
...
</dict>
<key>Playlists</key>
<array>
...
</array>
</dict>
</plist>
sqlTunes忽略除了音轨部分以外的所有内容，它递归地读取音轨部分并填充到Dictionary
结构是库数据的精确副本。数据库由一个名为Track的表组成，定义如下：
CREATE TABLE [dbo].[Track] (
[Track ID] [int] NOT NULL,
[Name] [nvarchar] (200) NULL,
[Artist] [nvarchar] (100) NULL,
[Album] [nvarchar] (100) NULL,
[Grouping] [nvarchar] (100) NULL,
[Genre] [nvarchar] (100) NULL,
[Kind] [nvarchar] (100) NULL,
[Size] [int] NULL,
[Total Time] [int] NULL,
[Track Number] [int] NULL,
[Track Count] [int] NULL,
[Year] [int] NULL,
[Date Modified] [datetime] NULL,
[Date Added] [datetime] NULL,
[Bit Rate] [int] NULL,
[Sample Rate] [int] NULL,
[Comments] [nvarchar] (200) NULL,
[Play Count] [int] NULL,
[Play Date] [bigint] NULL,
[Play Date UTC] [datetime] NULL,
[Rating] [int] NULL,
[Track Type] [nvarchar] (100) NULL,
[Location] [nvarchar] (500) NULL,
[File Folder Count] [int] NULL,
[Library Folder Count] [int] NULL
) ON [PRIMARY]
这可能不是学习数据库规范化的最佳示例，但足以运行报告。sqlTunes将在每次运行时删除并重新创建表。数据库必须存在，它不会创建它。
最后是有趣的部分。让从一些简单的东西开始，比如想知道整个库的平均比特率：
SELECT CAST(ROUND(AVG(CAST([Bit Rate] AS float)), 2) AS varchar) AS [Average Bit Rate] FROM Track
现在，让得到一些更有用的东西。这个查询列出了整个专辑收藏：
SELECT DISTINCT Artist, Album, [Year], Genre FROM Track ORDER BY Artist, [Year], Album
这是一个更正确的版本，它只列出完整的专辑。需要设置Track Count值才能有效使用它：
SELECT Artist, Album, [Year], Genre FROM Track GROUP BY Artist, [Year], Album, Genre HAVING COUNT(*) = MAX([Track Count]) ORDER BY Artist, [Year], Album
同样，这个列出了不完整的专辑：
SELECT Artist, [Year], Album, Genre FROM Track GROUP BY Artist, [Year], Album, Genre HAVING COUNT(*) < MAX([Track Count]) ORDER BY Artist, [Year], Album
想知道没有Track Count值的专辑吗？这里就是：
SELECT DISTINCT Artist, Album, [Year], Genre FROM Track WHERE ISNULL([Track Count], 0) = 0 ORDER BY Artist, [Year], Album
这个返回所有评级的专辑，按它们的评级排序。如果有MP3但想更新CD收藏，这很有用：
SELECT Artist, Album, [Year], COUNT(*) AS [Songs Rated], AVG(CAST(Rating AS float)) AS [Album Rating] FROM Track WHERE Rating IS NOT NULL GROUP BY Artist, Album, [Year] ORDER BY [Album Rating] DESC, [Songs Rated] DESC
这对艺术家来说也是一样的，当寻找新专辑购买时可能会很有用：
SELECT Artist, COUNT(*) AS [Songs Rated], AVG(CAST(Rating AS float)) AS [Artist Rating] FROM Track WHERE Rating IS NOT NULL GROUP BY Artist ORDER BY [Artist Rating] DESC, [Songs Rated] DESC
这个查询揭示了流派偏好：
SELECT Genre, AVG(CAST(Rating AS float)) AS [Genre Rating], COUNT(*) AS [Songs Rated] FROM Track WHERE Rating IS NOT NULL GROUP BY Genre ORDER BY [Genre Rating] DESC, [Songs Rated] DESC