Creating Trackstat

The last weekend i've build my own scrobbling application visible for the world. Of course it's not finished, but the framework is working and it does what i missed since i abandoned last.fm. I hacked a couple of things together using of course the database of the squeezeserver software. This wonderful piece of software keeps track of what you listen when you use either a hardware player, or the softsqueeze simulation software. There was only one thing that prevented using the listening history of the squeezeserver and that was the fact that each rescan of your library did reset your play count. This can be solved by installing the trackstat plugin for the squeezeserver. This plugin delivers a lot more goodies once you start rating your tracks, but you can read all that on the original plugin page.
I added an extra table to my squeezeserver database, track_statistic_monthly which holds the history for each track. In the end this will give me the opportunity for instance to see which songs i liked two years ago, but never listen to anymore.

CREATE TABLE `track_statistics_monthly` (
  `url` varchar(511) NOT NULL default '',
  `musicbrainz_id` varchar(40) default NULL,
  `playCount` int(10) unsigned default NULL,
  `added` int(10) unsigned default NULL,
  `lastPlayed` int(10) unsigned default NULL,
  `yearmonth` int(6) NOT NULL default '0',
  `thisMonth` int(11) NOT NULL default '0',
  `rating` int(10) unsigned default NULL,
  KEY `urlIndex` (`url`(255)),
  KEY `musicbrainzIndex` (`musicbrainz_id`),
  KEY `yearmonth` (`yearmonth`),
  KEY `thisMonth` (`thisMonth`)
)

both the yearmonth and the thisMonth are fields that don't exist in the original table. Each night this table get's updated by copying the current track_statistics data into the table and recalculate the thisMonth value.

function updateThisMonth () {
  $thisyearmonth = date('Ym', strtotime('+0 sec'));
  $lastyearmonth = date('Ym', strtotime('-1 month'));
  // first clear this month values
  mysql_query ("delete from track_statistics_monthly
      where yearmonth = "
.$thisyearmonth);
  // next copy current counts to the cache
  mysql_query("insert into track_statistics_monthly
      (url, musicbrainz_id,  playCount, added, lastPlayed, rating, yearmonth)
       select *, '"
.$thisyearmonth."' from track_statistics");
  // recalculate thisMonth;
  // check each track with a bigger playcount thismonth then lastmonth
  $res = mysql_query (
    "SELECT tm.*, lm.playCount as lastCount
     FROM `track_statistics_monthly` tm
     LEFT JOIN track_statistics_monthly lm
       ON tm.url = lm.url AND lm.yearmonth = '"
.$lastyearmonth."'
    WHERE tm.yearmonth = '"
.$thisyearmonth."' and tm.playCount > lm.playCount");
 
  while($arr = mysql_fetch_array($res)) {
    mysql_query (
      "UPDATE track_statistics_monthly SET thisMonth = ".$arr['playCount']." - ".$arr['lastCount'] ." WHERE url = ".$arr['url']."' and yearmonth = ".$arr['yearmonth']);
  }
}

Added this to my cron and never think about it again.

Now that my historical data has taken care of, time to think about an interface that anybody can enjoy and not necessarily view on the squeezeserver interface. What better place then my website? Which is running Drupal. I created a page and started coding inside the content (maybe i'll turn this in a module, but i'm afraid this is so specific that either nobody would use it or i end up answering questions about the module). First problem to overcome was to let Drupal connect through a socket and mysqli. Only a core-hack could help me out since Drupal has hard-coded NULL on the spot where the socket parameter should be passed to mysqli.
After that things only got better.
First i created my 'recently listened to' list with the following sql magic

SELECT *, tracks.id as trackid,
        tracks.title as tracktitle, albums.title as albumtitle
FROM track_statistics
LEFT JOIN tracks ON tracks.url = track_statistics.url
LEFT JOIN contributor_track
   ON tracks.id = contributor_track.track AND contributor_track.role = 1
LEFT JOIN contributors ON contributors.id = contributor_track.contributor
LEFT JOIN albums ON albums.id = tracks.album
WHERE playCount > 0  ;

As you might have noticed i didn't add a limit to this statement. i let the drupal_pager add this magic and create a sortable table with the following bit of code:
$header = array(
      array('data' => 'artist'),
      array('data' => 'title'),
      array('data' => 'last played',
            'field' => 'lastPlayed',
            'sort' => 'desc'),
      array('data' => 'total',
            'field' => 'playCount'),
);
$ts = tablesort_sql($header);
$res = pager_query($sql . $ts, $limit);

Because the table is sortable on the total play count i simultaneously created the best (overall) listened tracks as well.
(to be continued, i'll create some detail pages, grab images from the squeezeserver and draw charts with jquery.gchart which is an interface to google charts).
You can see my trackstat here.

previous item: next item:
thank you for watching  Creative Commons License