sqlTunes - 查询您的iTunes库

Alexander Kojevnikov

4.86/5 (12投票s)

2005 年 11 月 8 日

CC (ASA 2.5)

2分钟阅读

170550

1556

sqlTunes 是一个将 iTunes 音乐库信息导出到 SQL Server 的小工具。

引言

我编写了这个小工具，是为了弥补 iTunes 播放器在报表生成方面的局限性。该程序将你的 iTunes 音乐库数据导出到 Microsoft SQL Server (因此得名 sqlTunes) 数据库，然后可以使用 T-SQL 查询。我将快速回顾一下 iTunes 音乐库的底层数据结构，然后列出一些你可以运行的报表。

音乐库结构

iTunes 将音乐库数据存储在两个文件中 - 一个名为 iTunes Library.itl 的专有二进制文件，以及它的 XML 对应文件，名为 iTunes Music Library.xml。这两个文件都位于 我的文档\我的音乐\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:///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 忽略了除 Tracks 部分以外的所有内容，它递归读取 Tracks 部分并填充 Dictionary<string, object> 对象。然后，它构建 INSERT 语句并将数据导出到 SQL Server。

SQL 结构

该结构是音乐库数据的精确副本。数据库由一个名为 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

进一步开发

如果这个程序被证明是有用的，我将继续沿着这三个方向开发它

添加更多报表 - 请发布你的请求，我的想象力有限，但我会说 T-SQL :)
增强界面 - 例如，直接从 sqlTunes 运行报表。
支持更多数据库 - 大多数 iTunes 用户没有 Microsoft SQL Server。基于文件的数据库（如 SQLite 甚至 Access）也可以胜任这项工作。

许可证

sqlVault 是在 GNU 通用公共许可证 (GPL) 下发布的。

历史

2005-11-08：初始版本
2008-03-04：iTunes 7 兼容性