CD Statistics Howto

This article details how the CD statistics are generated on this site.

Firstly it is fairly obvious that the CD’s need to be in some file format on a server. I have all my CD’s stored in MP3 format although Squeezecenter can handle other formats (e.g. flac). This allows a decent quality and for them to be easily loaded onto portable players. The format of the files doesn’t matter for the purposes of this article.

The audio files are handled by a piece of software called Squeezecenter (formally Squeezebox server) which allows the files to be streamed to audio devices. I currently have two squeezebox players in the house.

The Squeezecenter software stores all the information about the audio files in a database. by default this database is SqLite although it is possible to configure it to use MySQL.

In order to get the detailed playing statistics about the various files it is necessary to add the TrackStat plugin to Squeezecenter. This adds extra tables to the database to hold play information about the tracks.

Then it is simply a matter of using the appropriate SQL queries to retrieve the information from the database and format it appropriately for the web page.

I use php to do this.

For example the SQL query to get the top ten tracks played is:

select t1.title, sum(t4.playcount) as f, t2.contributor, t3.name from tracks as t1, 
contributor_track as t2, contributors as t3, track_statistics as t4 where t1.id = t2.track 
and t1.url = t4.url and t2.contributor = t3.id and t2.role=1 and t4.playcount > 0 
group by t1.id order by f desc, title limit 10

This will return 10 rows ordered by the play count descending.

It is then a simple matter of formatting the output of this into a table.

The php code I use to produce a table of this information is:

$result = $conn->query($query);
print "<table>";
print "<tr><th colspan=\"2\">Top 10 Albums</th></tr>";
print "<tr><th>Album</th><th>Tracks Played</th></tr>\n";
while( $row = $result->fetcharray()) {
    print "<tr><td>$row[0]<BR><i>";
    if( $row[3] == 1 ) {
       print "Various Artists";
       }
    else {
       print "$row[2]";
       }
    print "</i></td>";
    $count = $row[1];
    print "<td valign=\"top\" align=\"right\">$count</td></tr>\n";
    }
 print "</table>";

Note that when using SqLite the main database is in the squeezecenter cache directory and is called library.db. The track play count data used by the trackstat plugin is in a file called persist.db. To get the data, both files need to be open.

Another thing to be aware of is that Squeezecenter by default has the file permissions set so that ordinary users cannot access them. As the web server runs as user apache (on my system) then this user cannot access the files.

This requires the file permissions to be changed to allow read access to the world. Be aware that this will obviously compromise security on shared systems. The web user only needs read access.

A slight complication is that Squeezecenter will reset the file permissions when it is restarted. This is annoying because after a system restart (normally due to updates being applied) the web user cannot access the database files and hence displays nothing.

I overcame this by adding a line in rc.local in /etc (a file which is executed at the end of the boot procedure) to change the file permissions.