Click here to Skip to main content
12,634,330 members (28,319 online)
Click here to Skip to main content
Add your own
alternative version


59 bookmarked

sqlTunes - Query Your iTunes Library

, 7 Mar 2008 CC (ASA 2.5)
Rate this:
Please Sign up or sign in to vote.
sqlTunes is a small tool that exports iTunes library information to the SQL server.


I wrote this tiny tool to compensate the limitations of the otherwise perfect iTunes player in reports generation. The program exports your iTunes library data to a Microsoft SQL Server (hence the name – sqlTunes) database which can then be queried using T-SQL. I am going to quickly review the underlying data structure of the iTunes library and then I will list some of the reports you can run on it.

Library Structure

iTunes stores the library data in two files – a proprietary binary file called iTunes Library.itl and its XML counterpart called iTunes Music Library.xml. Both files are located in the My Documents\My Music\iTunes\ folder.

The XML file is nothing more than just a representation of a generic dictionary. First it lists the library information, then the track data, and in the end the playlists. Here is a sample XML:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN"
<plist version="1.0">
    <key>Major Version</key><integer>1</integer>
    <key>Minor Version</key><integer>1</integer>
    <key>Application Version</key><string>6.0.1</string>
    <key>Music Folder</key>
    <key>Library Persistent ID</key>
            <key>Track ID</key><integer>36</integer>

As simple as that! sqlTunes ignores everything except the Tracks section which it reads recursively and fills in the Dictionary<string, object> object. It then builds INSERT statements and exports the data to the SQL Server.

SQL Structure

The structure is an exact copy of the library data. The database consists of one table called Track which is defined as:

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

This is probably not the best example to learn database normalisation but certainly enough to run our reports. sqlTunes will delete and re-create the table on each run. The database must exist, it will not create it.


And finally, here is the fun part. Let us start with something simple, say we want to know the average bit rate of the entire library:

SELECT CAST(ROUND(AVG(CAST([Bit Rate] AS float)),2) AS varchar)
AS [Average Bit Rate]
FROM Track

Now, let us get something more useful. This query lists your entire album collection:

SELECT DISTINCT Artist, Album, [Year], Genre
FROM Track
ORDER BY Artist, [Year], Album

This is a more correct version, it lists only the full albums. You will need to set the Track Count values to use it effectively:

SELECT Artist, Album, [Year], Genre
FROM Track
GROUP BY Artist, [Year], Album, Genre
HAVING COUNT(*) = MAX([Track Count])
ORDER BY Artist, [Year], Album

Likewise, this one lists the incomplete albums:

SELECT Artist, [Year], Album, Genre
FROM Track
GROUP BY Artist, [Year], Album, Genre
HAVING COUNT(*) < MAX([Track Count])
ORDER BY Artist, [Year], Album

Want to know the albums without the Track Count value? Here you go:

SELECT DISTINCT Artist, Album, [Year], Genre
FROM Track
WHERE ISNULL([Track Count],0)=0
ORDER BY Artist, [Year], Album

This one returns all the rated albums sorted by their rating. Handy if you have MP3s but want to update your CD collection:

SELECT Artist, Album, [Year], COUNT(*) AS [Songs Rated],
AVG(CAST(Rating AS float)) AS [Album Rating]
FROM Track
GROUP BY Artist, Album, [Year]
ORDER BY [Album Rating] DESC, [Songs Rated] DESC

This is the same for artists, can be useful when you are looking for new albums to buy:

SELECT Artist, COUNT(*) AS [Songs Rated], AVG(CAST(Rating AS float)) AS [Artist Rating]
FROM Track
ORDER BY [Artist Rating] DESC, [Songs Rated] DESC

This query reveals your genre preferences:

SELECT Genre, AVG(CAST(Rating AS float)) AS [Genre Rating], COUNT(*) AS [Songs Rated]
FROM Track
ORDER BY [Genre Rating] DESC, [Songs Rated] DESC

Further Developments

If this program proves to be useful, I will continue developing it in these three directions:

  1. Adding more reports - Please do post your requests, my imagination is limited but I can speak T-SQL :)
  2. Enhancing the interface - E.g. run reports directly from sqlTunes.
  3. Support more databases - Most iTunes users do not have Microsoft SQL Server. A file based database like SQLite or even Access will do the job as well.



  • 2005-11-08: Initial version
  • 2008-03-04: iTunes 7 compatibility


This article, along with any associated source code and files, is licensed under The Creative Commons Attribution-ShareAlike 2.5 License


About the Author

Alexander Kojevnikov
Software Developer (Senior)
Australia Australia
No Biography provided

You may also be interested in...

Comments and Discussions

SuggestionAdd 'Compilation' iTunes field Pin
Member 1033575114-Oct-13 18:52
memberMember 1033575114-Oct-13 18:52 
GeneralMy vote of 5 Pin
Kanasz Robert26-Sep-12 5:58
mvpKanasz Robert26-Sep-12 5:58 
SuggestionLeast/Most Liked albums (Based on Rating tag) Pin
AlfaEcoTangoRomeo8-Jun-12 8:17
memberAlfaEcoTangoRomeo8-Jun-12 8:17 
-- least liked albums
select album
, case when count(distinct artist) > 1
'VA (' + cast(count(distinct artist) as varchar(200)) + ') Artist'
else (
select max(artist)
from track a
where a.album = b.album)
end artist
, count(1) [Total tracks]
, sum(case when Rating >0 then 1 else 0 end) [Liked tracks]
, 100*sum(case when Rating >0 then 1 else 0 end)/count(1) [Album Ratio]
, sum (size) /(1024*1024) [Total size (In MB)]
, sum( case when Rating >0 then size else 0 end ) /(1024*1024) [Liked size (In MB)]
, (sum( case when Rating >0 then size else 0 end)) / sum (size) [Size Ratio]
, sum( [Total Time] /(1000) )/(60) [Total Time (min)]
, sum( case when Rating >0 then [Total Time] else 0 end/(1000) ) /(60) [Liked Time (In Min)]
, 100*(sum( case when Rating >0 then [Total Time] else 0 end)) / sum ([Total Time]) [Time Ratio]
from Track b
group by album
having sum( case when Rating >0 then [Total Time] else 0 end) < sum ([Total Time]) / 2 --> u liked less than half of the album
and sum (size) /(1024*1024) > 100 --> just big albums (over 100 MB)
order by 11
QuestionConnect to Access? Pin
Drew Barnard3-Apr-12 14:06
memberDrew Barnard3-Apr-12 14:06 
QuestionThanks and field request Pin
Member 810174821-Jul-11 3:16
memberMember 810174821-Jul-11 3:16 
GeneralThanks Pin
raskenn29-Oct-10 15:43
memberraskenn29-Oct-10 15:43 
GeneralMy vote of 5 Pin
raskenn29-Oct-10 15:40
memberraskenn29-Oct-10 15:40 
GeneraliTunes 8 Pin
Kilkenny827-Jul-09 1:38
memberKilkenny827-Jul-09 1:38 
GeneralRe: iTunes 8 Pin
Alexander Kojevnikov7-Jul-09 1:45
memberAlexander Kojevnikov7-Jul-09 1:45 
GeneralDatabase Normalization Pin
idynamix27-Apr-09 7:49
memberidynamix27-Apr-09 7:49 
QuestionTracktime and Size reports Pin
zsukasa5118-Aug-08 12:42
memberzsukasa5118-Aug-08 12:42 
General"Composer" field Pin
Steven de Mena30-Mar-08 20:45
memberSteven de Mena30-Mar-08 20:45 
GeneraliTunes 7 Pin
Alexander Kojevnikov7-Mar-08 13:26
memberAlexander Kojevnikov7-Mar-08 13:26 
GeneralSuggestion... Pin
Mike Doyon7-Mar-08 12:31
memberMike Doyon7-Mar-08 12:31 
GeneralProblem running from source - Window class name is not valid. Pin
pccm24686-Jan-08 6:56
memberpccm24686-Jan-08 6:56 
QuestioniTunes 7+ Pin
Jeff S.17-Dec-07 13:27
memberJeff S.17-Dec-07 13:27 
GeneralExtension for other databases and m3u Pin
j.wefer18-Nov-07 7:07
memberj.wefer18-Nov-07 7:07 
GeneralUnable to connect Pin
Davidoff19876-Nov-07 8:53
memberDavidoff19876-Nov-07 8:53 
Generalfeature request- output sql text file for mySQL Pin
alpineedge328-Apr-07 15:57
memberalpineedge328-Apr-07 15:57 
QuestionHow about just reading the XML into a DataTable? Pin
JoeRip15-Dec-06 16:39
memberJoeRip15-Dec-06 16:39 
GeneralVerry cool until Itunes 7 Pin
WebzenNJ28-Sep-06 7:34
memberWebzenNJ28-Sep-06 7:34 
GeneralRe: Verry cool until Itunes 7 Pin
Alexander Kojevnikov28-Sep-06 7:59
memberAlexander Kojevnikov28-Sep-06 7:59 
QuestionRe: Verry cool until Itunes 7 Pin
streamlines4-Jan-07 6:39
memberstreamlines4-Jan-07 6:39 
GeneraliTunes 7.0 Pin
hootch9925-Sep-06 9:33
memberhootch9925-Sep-06 9:33 
AnswerRe: iTunes 7.0 Pin
RichTeel25-May-07 8:42
memberRichTeel25-May-07 8:42 
QuestionWhere's the Import? Pin
Code Deamon4-Jul-06 3:29
memberCode Deamon4-Jul-06 3:29 
Questioncreate a file like itunes.sql Pin
drubin4-Apr-06 13:12
memberdrubin4-Apr-06 13:12 
General(407) Proxy Authentication Required Pin
Steve Crane15-Mar-06 3:29
memberSteve Crane15-Mar-06 3:29 
GeneralCan't Connect! Pin
rentonsa25-Jan-06 11:07
memberrentonsa25-Jan-06 11:07 
GeneralRe: Can't Connect! Pin
Alexander Kojevnikov26-Jan-06 14:48
memberAlexander Kojevnikov26-Jan-06 14:48 
GeneralRe: Can't Connect! Pin
rentonsa19-Mar-06 7:27
memberrentonsa19-Mar-06 7:27 
QuestionWhat about direct interaction? Pin
UnderWing16-Jan-06 18:46
memberUnderWing16-Jan-06 18:46 
AnswerRe: What about direct interaction? Pin
Alexander Kojevnikov19-Jan-06 16:48
memberAlexander Kojevnikov19-Jan-06 16:48 
GeneralSweet... Pin
Patrick Luijpers16-Jan-06 11:13
memberPatrick Luijpers16-Jan-06 11:13 
GeneralRe: Sweet... Pin
Alexander Kojevnikov16-Jan-06 12:59
memberAlexander Kojevnikov16-Jan-06 12:59 
GeneralExcellent! Pin
daveburke23-Dec-05 12:52
memberdaveburke23-Dec-05 12:52 
GeneralRe: Excellent! Pin
Alexander Kojevnikov27-Dec-05 14:46
memberAlexander Kojevnikov27-Dec-05 14:46 
GeneralSQLTunes is a trademarked name used by Pin
vonkessel26-Nov-05 15:57
membervonkessel26-Nov-05 15:57 
GeneralRe: SQLTunes is a trademarked name used by Pin
Alexander Kojevnikov26-Nov-05 20:22
memberAlexander Kojevnikov26-Nov-05 20:22 
GeneralKind'a neat, but... Pin
jconwell8-Nov-05 6:17
memberjconwell8-Nov-05 6:17 
GeneralRe: Kind'a neat, but... Pin
FZelle8-Nov-05 10:06
memberFZelle8-Nov-05 10:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161208.2 | Last Updated 7 Mar 2008
Article Copyright 2005 by Alexander Kojevnikov
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid