brands_artists.rb

require 'rubygems'
require 'mysql'

sql = %[
  select b.pid, b.title as brand_title, c.musicbrainz_gid as mbid, c.name as artist_name, count(DISTINCT se.id) as 'plays'
  FROM contributors c
  JOIN segments s ON s.primary_contributor_id = c.id
  JOIN segment_events se ON se.segment_id = s.id
  JOIN versions v ON se.version_id = v.id
  JOIN broadcasts br ON v.id = br.version_id
  JOIN programmes e ON v.programme_id = e.id
  JOIN programmes b ON e.root_id = b.id
  GROUP BY e.root_id, s.primary_contributor_id
  ORDER BY b.pid, plays DESC
]

connection = Mysql::new("publishing-devel-db.ips.radio.bbc.co.uk", "dynpub", "dynpub", "p_trunk_livefeed")
result = connection.query(sql)

file = File.open('brands_artists.txt', 'w')

brands, artists = {}, {}

current_brand = nil
current = ''
result.each do |r| 
  pid, brand_title, mbid, artist_name, plays = r
  next if mbid.nil?
  
  p [pid, brand_title, mbid, artist_name, plays]
  if (current_brand.nil? or pid!=current_brand)
    brands[pid] ||= brand_title
    file.puts current unless current.empty?      
    current_brand = pid
    current = "#{pid} "
  end
  artists[mbid] ||= artist_name
  current << "#{mbid} #{plays}, "
end 

File.open('artists.yml', 'w') { |f| f.puts artists.to_yaml }
File.open('brands.yml', 'w') { |f| f.puts brands.to_yaml }
        
file.close