require 'dbi' require 'scruffy' require 'socket' # HTML Formatting - Note: keep the header for XHTML/SVG support. htmlHeader = <<-'EOS' EOS begin dbh = DBI.connect("DBI:Mysql:webarchive:localhost", "root", "password") row = dbh.select_one("SELECT VERSION()") puts "Server version: " + row[0] # Open connection on port 9090 server = TCPServer.new(9090) while true Thread.start(server.accept) do |session| #p "#{session} spawned - processing request\n" request = session.gets # filter all non '/term1/term2' requests (otherwise crash on /favicon.ico) if not request.chomp.split[1].to_s =~ /^(\/(.*)\/(.*))/ p "#{Time.now.strftime('[Request at %I:%M:%S]')} Malformed request: #{request}" session.close next end session.print "HTTP/1.1 200/OK\rContent-type: application/xhtml+xml\r\n\r\n" session.print htmlHeader ## Log request to console... terms = request.split[1].split("/")[1,2] p "#{Time.now.strftime('[Request at %I:%M:%S]')} #{terms.join(' ')}" newsSources = dbh.select_all("select id, resource from types") sourceID = newsSources.collect { |r| r[0] } dates = ['2004 9', '2004 10', '2004 11', '2004 12', '2005 1', '2005 2', '2005 3', '2005 4', '2005 5' ] results, aggregate = [], [] terms.each { |q| sourceID.each { |s| query = "select count(*) as occurence, DATE_FORMAT(date, '%Y %c') as date from news where type = #{s} and (headline like '%#{q}%' or story like '%#{q}%') group by DATE_FORMAT(date, '%c,%y') order by DATE_FORMAT(date, '%Y %c')" result = dbh.select_all(query) map = [0, 0, 0, 0, 0, 0, 0, 0, 0] result.each { |r| map[dates.index(r[1])] = r[0] } results.push(map) } # Count-all query (area chart) query = "select count(*) as occurence, DATE_FORMAT(date, '%Y %c') as date from news where (headline like '%#{q}%' or story like '%#{q}%') group by DATE_FORMAT(date, '%c,%y') order by DATE_FORMAT(date, '%Y %c')" result = dbh.select_all(query) map = [0, 0, 0, 0, 0, 0, 0, 0, 0] result.each { |r| map[dates.index(r[1])] = r[0] } results.push(map) } # Count-all query (area chart) query = "select count(*) as occurence, DATE_FORMAT(date, '%Y %c') as date from news where (headline like '%#{terms[1]}%#{terms[0]}%' or headline like '%#{terms[0]}%#{terms[1]}%' or story like '%#{terms[0]}%#{terms[1]}%' or story like '%#{terms[1]}%#{terms[0]}%') group by DATE_FORMAT(date, '%c,%y') order by DATE_FORMAT(date, '%Y %c')" result = dbh.select_all(query) map = [0, 0, 0, 0, 0, 0, 0, 0, 0] result.each { |r| map[dates.index(r[1])] = r[0] } aggregate.push(map) # AND NOT query = "select count(*) as occurence, DATE_FORMAT(date, '%Y %c') as date from news where (headline like '%#{terms[0]}%' or story like '%#{terms[0]}%') and not (headline like '%#{terms[1]}%' or story like '%#{terms[1]}%') group by DATE_FORMAT(date, '%c,%y') order by DATE_FORMAT(date, '%Y %c')" result = dbh.select_all(query) map = [0, 0, 0, 0, 0, 0, 0, 0, 0] result.each { |r| map[dates.index(r[1])] = r[0] } aggregate.push(map) query = "select count(*) as occurence, DATE_FORMAT(date, '%Y %c') as date from news where (headline like '%#{terms[1]}%' or story like '%#{terms[1]}%') and not (headline like '%#{terms[0]}%' or story like '%#{terms[0]}%') group by DATE_FORMAT(date, '%c,%y') order by DATE_FORMAT(date, '%Y %c')" result = dbh.select_all(query) map = [0, 0, 0, 0, 0, 0, 0, 0, 0] result.each { |r| map[dates.index(r[1])] = r[0] } aggregate.push(map) # Build overall comparison graph graph = Scruffy::Graph.new(:theme => Scruffy::Themes::Keynote.new) graph.value_formatter = Scruffy::Formatters::Number.new(:precision => 0) graph.title = "#{terms[0].capitalize} vs #{terms[1].capitalize}" graph.renderer = Scruffy::Renderers::Split.new(:split_label => "#{terms[0].capitalize} (Top) / #{terms[1].capitalize} (Bottom)") graph.point_markers = ['Sept', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'Apr', 'May'] graph.add :area, "#{terms[0].capitalize}", results[3], :category => :top graph.add :area, "#{terms[1].capitalize}", results[7], :category => :bottom results.each { |e| tableName = newsSources[results.index(e)%3][1].chomp('_news') graph.add :line, tableName, e, :category => :top if results.index(e) < 3 graph.add :line, tableName, e, :category => :bottom if results.index(e) > 3 and results.index(e) < 7 } # chop off the XML declarations from XML generated by scruffy and send back to client svg = graph.render(:width => 800).split("\n") session.print svg[2, svg.size].join("\n") # Build cubed graph with specific stats graph = Scruffy::Graph.new graph.title = "Drill down" graph.renderer = Scruffy::Renderers::Cubed.new graph.point_markers = ['Sept', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'Apr', 'May'] graph.add :bar, "#{terms[0].capitalize}", results[3], :categories => [:top_left] graph.add :bar, "#{terms[1].capitalize}", results[7], :categories => [:bottom_left] graph.add :line, "#{terms[0].capitalize} AND #{terms[1].capitalize}", aggregate[0], :categories => [:top_right] graph.add :line, "#{terms[0].capitalize} NOT #{terms[1].capitalize}", aggregate[1], :categories => [:bottom_right] graph.add :line, "#{terms[1].capitalize} NOT #{terms[0].capitalize}", aggregate[2], :categories => [:bottom_right] # chop off the XML declarations from XML generated by scruffy and send back to client svg = graph.render(:width => 800).split("\n") session.print svg[2, svg.size].join("\n") session.print "" session.print "" session.close end end rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" ensure dbh.disconnect if dbh end