Displaying Usage Statistics on a Bar Graph

My last post was about creating images of line graphs in Rails. Now I am going to have a go at histograms (bar graphs), using HTML to draw the graph, rather than creating an image.

The Data
I recently wanted to see how frequently entries were added to a table in my database. Each entry has a created_at column, so it should be easy to break this down by week or by month. Or so I thought. It was not as easy as I had hoped. There is no simple Rails way to group records within a set time interval. Instead, I had to use some actual SQL. And as my development PC is running MySQL and my server has PostgreSQL, I had to use two different dialects of SQL. It is not pretty, but this is what worked for me. First, I set up a SQL string (I did this in the controller while trying it out, but moved it to config/initializers/constants.rb later; you need to restart the web server after changing constants.rb):

module Sql
# SQL to get usage stats by week

# Different SQL for different dialects
# Both will generate the number of days since the year dot
# when the records was created
# MySQL has year and dayofyear functions
MYSQL_YEAR = 'year(created_at)'
MYSQL_DAY = 'dayofyear(created_at)'
# PostgreSQL uses the extract function for both
POSTGRESQL_YEAR = 'extract(year from created_at)'
POSTGRESQL_DAY = 'extract(doy from created_at)'
# Select the correct SQL based on the name of the database adapter
SQL_YEAR = ActiveRecord::Base.configurations[RAILS_ENV]['adapter'].match(/mysql/) ?
             MYSQL_YEAR : POSTGRESQL_YEAR
SQL_DAY = ActiveRecord::Base.configurations[RAILS_ENV]['adapter'].match(/mysql/) ?
             MYSQL_DAY : POSTGRESQL_DAY

# The rest of the SQL is the same
# The second line calculates the week number for the record
SQL =
<<END
select count(sample_reference) as count,
      floor((#{SQL_YEAR} * 365 + #{SQL_DAY}) / 7) as week,
      #{SQL_YEAR} as year,
      floor(#{SQL_DAY} / 7) as week_no
      from samples
      group by week
      order by week desc
      ;
END

end

You do have to careful when invoking SQL directly that you guard against SQL injection attacks. Not a problem in this case as the SQL command is in no way altered by the end-user.

SQL does have functions for getting the week number, however, funny things happen just before the new year, with records created in week zero, but in the previous year, so I abandoned using these functions. Months would have easier, but I wanted weeks.

The Controller
After all that, the method in my controller is fairly simple. Some sites I looked at used establish_connection before connection. As I understand it, establish_connection tells Rails that you want to potentially make a connection to a database, allowing you to create actual connections as oftyen as you like in the future. Rails does that automatically for ActiveRecord, so there is no need in this situation for me to. The map method adds a new key-value pair to each hash in the array; this will be used for the labels (eg "2009 week 3").

def graph
ary = ActiveRecord::Base.connection.select_all(Sql::SQL)
@result = ary.map do |e|
  e['label'] = "#{e['year']} week #{e['week_no']}"
  e
end
end

A Generic Partial
I created a partial to display the results in the bar graph. This is a general thing that could be used in any number of web pages to display this sort of data. It uses HTML to extend a small image across a table, according to the data. Note that this uses the find_best method I described on the blocks page; this pulls out the entry with the highest count, against which all the others are scaled.

<table>
<%
max = data.find_best { |x, y| x['count'].to_i < y['count'].to_i }['count'].to_i
data.each do |element|
  count = element['count'].to_i
  size = "#{count * 800 / max}x20"
%>
<tr>
  <td><%=h element['label'] %></td>
  <td><%=h count %></td>
  <td><%= image_tag 'bar.gif', :size => size %></td>
</tr>
<% end %>
</table>

The View
The partial is invoked like this:

<%= render :partial => 'bargraph',
              :locals => { :data => @result } %>

In the locals hash, :data is mapped to the array that holds the data. Each entry in the array must have a 'count' value, used for the size of the bar, and another value, the key for which is 'label'.

Struggling with Ruby: Contents Page

Displaying Trends on a Graph Image

This builds on my last two posts on drawing images for Rails with Java.

My objective here was to display values from a series of records on a graph to show possible trends (specifically analytical results for samples submitted to a lab). There are two quite separate issue here involving firstly extracting data from the database table, and secondly creating an image of a line graph on-the-fly.

Getting the Data
Step one, then, is to grab the data. The easy way is to just use find all:

ary = Worksheet.find :all

However, that could involve a lot of data coming across from your database that you do not need. We are only interested in the contents of one column. Better to just extract out the data from that column.

ary = Worksheet.find :all, :select => 'result'

This gives us an array of Worksheet objects, but each object has only the specific column set to a value. We need to grab those values, and the map method gives an easy way to convert to an array of numbers. This can all go inside a class method in Worksheet (in this case the name of the column is in a variable, column_name, to keep it general).

def self.collect_data column_name
Worksheet.find(:all, :select => column_name).map do |e|
e.send(column_name)
end
end

Draw the Graph
Step two is drawing the graph (see also here for drawing with Java on Rails). I created a new Ruby class in its own file in the models directory, using class methods. I wanted the graph to be able to scale itself, but for the lower and upper bounds to be round numbers; the graph_limits methods handles that.

Here is the basis of the class. Java needs to be included, and for convenience I have imported all the Java classes I will be using. Then I set up some constants:

include Java

class Graph

java_import java.io.ByteArrayOutputStream
java_import java.awt.image.BufferedImage
java_import java.awt.Color
java_import java.awt.RenderingHints
java_import java.awt.geom.GeneralPath
java_import java.awt.geom.Line2D
java_import java.awt.Font
java_import java.awt.BasicStroke
java_import javax.imageio.ImageIO

WIDTH = 800
HEIGHT = 600
FOREGROUND = Color.new 0.2, 0.2, 0.2
BACKGROUND = Color.new 0.95, 0.95, 0.95
PLOT = Color.blue
FONT = Font.new("SansSerif", Font::PLAIN, 22)

end

I wrote three helper methods (all protected). Note that the second uses the find_best method I described on the blocks page;

  # Converts the data point to a vertical
# position on the image,
# scaled between min and max.
def self.calc y, min, max
19.0 * HEIGHT / 20 - (y - min) *
   18 * HEIGHT / (max - min) / 20
end

# Determines the minimum and maximum
# values from the data, and rounds them
# down and up respectively to give lower
# and upper display limits.
def self.graph_limits data
# See my blocks page for find_best method
min = data.find_best { |x, y| x > y }
max = data.find_best { |x, y| x < y }
modifier = 10 **
        -(Math.log10(max - min).floor)
min = (min * modifier).floor *
        1.0 / modifier
max = (max * modifier).ceil *
        1.0  / modifier
# Ruby allows a method to return
# multiple values
return min, max
end

# Draws a line and label for the y-axis
def self.graph_line g2, text, h
g2.drawString(text, 0, h)
# Note that Line2D uses inner
#  classes, accessed via ::
g2.draw(Line2D::Double.new(WIDTH / 10,
            h, 9 * WIDTH / 10, h));
end

Now that we have the foundations, we can draw the graph.

  def self.line_graph data
min, max = graph_limits data

# Set up image object
off_image = BufferedImage.new(WIDTH, HEIGHT,
                 BufferedImage::TYPE_INT_ARGB)
g2 = off_image.createGraphics()
g2.setRenderingHint(RenderingHints::KEY_ANTIALIASING,
        RenderingHints::VALUE_ANTIALIAS_ON)

# Set up background
g2.setPaint(BACKGROUND)
g2.fillRect(0, 0, WIDTH - 1, HEIGHT - 1)
# Note: You get wierd results if you do not
# fill your background
g2.setPaint(FOREGROUND)
g2.drawRect(0, 0, WIDTH - 1, HEIGHT - 1)

# Set up the y-axis (no x-axis drawn)
g2.setFont(FONT)
graph_line g2, min.to_s[0..5], HEIGHT * 9 / 10
graph_line g2, max.to_s[0..5], HEIGHT / 10
graph_line g2, ((min + max) / 2).to_s[0..5],
                   HEIGHT / 2

# Convert data to image coordinates
x_points = Array.new(data.length) do |i|
 (i * 8.0 * WIDTH / (data.length - 1) / 10) +
       WIDTH / 10
end
y_points = Array.new(data.length) do |i|
  calc(data[i], min, max)
end

# Generate a GeneralPath object from the
# coordinates
polygon = GeneralPath.new(
               GeneralPath::WIND_EVEN_ODD,
               data.length)
polygon.moveTo(x_points[0], y_points[0])
(1...x_points.length).each do |index|
  polygon.lineTo(x_points[index],
                   y_points[index])
end

# Draw the GeneralPath object
g2.setPaint(PLOT)
g2.setStroke(BasicStroke.new(1.5))
g2.draw(polygon)

# Convert for web image
os = java.io.ByteArrayOutputStream.new
ImageIO.write(off_image, "gif", os)
String.from_java_bytes(os.toByteArray)
end

Ptting it Together
Step 3 is to put it all together. In my controller I put in a new method (this needs to be mentioned as a collection in your routes, as it has no specific record associated with it):

  def graph
respond_to do |format|
  format.html
  format.gif  do
    send_data Graph.line_graph(
              Worksheet.collect_data('result')),
              :type => "image/gif",
              :disposition => "inline"
  end
end
end

The important part is the bit starting with send_data. The collect_data method is invoked on Worksheet, returning an array of values from the "result" column of the database table. This is sent to the line_graph method of Graph, which generates the image.

Finally, we need a view. This must be called graph.html.erb, of course, and should include the following:

<%= image_tag "/Worksheet/graph/x.gif", :size => "800x600" %>

If this was an image for a specific record in your table, you would need the id of the record. As this image is for a collection of records, no id is applicable. Nevertheless, you need something there, so I have called in "x".

Struggling with Ruby: Contents Page

Creating Images For Rails With Java

Some time ago I had a look at RMagick to create images on-the-fly in a Rails application. The problem with RMagick is that it is not compatible with JRuby, and if you are going to be using TomCat as your webserver, you need to be using JRuby. However, JRuby does present a ready alternative through Java.

To look at this I created a new controller, images_controller, in an existing Rails project. In hindsight, images might have been a bad name, as there is a folder called public/images already. However, Rails was able to work out whether the image should come from public/images or via my images controller, so the issue is really only whether it is confusing to the developer.

The first thing you need to do is set up the new MIME types. There are various places you can do this, but the correct one, I believe, is in config/initializers/mime_types.rb. This is what I added:

Mime::Type.register "image/jpg", :jpg
Mime::Type.register "image/png", :png
Mime::Type.register "image/gif", :gif

Now let us create the controller. The first thing you need to do is include Java. Then you should import all the classes you will need. The second step is optional, but the alternative is writing out the full name of he Java classes every time you use them, which leads to longer and harder to read code. Note that unlike in Java you cannot use the * wildcard to import a whole bunch of classes in one line. Here is the start of my controller:

class ImagesController < ApplicationController
include Java

java_import java.io.ByteArrayOutputStream
java_import java.io.File

java_import java.awt.image.BufferedImage
java_import java.awt.Color
java_import java.awt.RenderingHints
java_import java.awt.geom.GeneralPath
java_import java.awt.Font

java_import javax.imageio.ImageIO
end

Now I need an action defined by a method, image. A clever feature of Rails is that you can respond differently depending on the file extention, which is what I do here. The respond_to block directs the action to the correct format (as long as the format is a known MIME type).

The HTML response is blank. Rails will do the default action, which is to return a page derived from the view images/image.html.erb. The other three responses follow the same format. Each invokes the send_data method, with the data (the image) as the first parameter, followed by a hash of options. The :type is simply the MIME type. The :disposition determines if the data is to be displayed ("inline"), or downloaded ("attachment"). For attachments, you can set a :filename parameter too, and there is also a :status parameter, which conveniently defaults to 200, success.

API for send_data:
http://api.rubyonrails.org/classes/ActionController/Streaming.html

def image
@name = params[:id]

respond_to do |format|
 format.html
 format.jpg  do
   send_data get_jpeg(@name), :type => "image/jpeg",
                     :disposition => "inline"
 end
 format.png  do
   send_data get_png(@name), :type => "image/png",
                     :disposition => "inline"
 end
 format.gif  do
   send_data get_gif(@name), :type => "image/gif",
                     :disposition => "inline"
 end
end
end

So that just leaves the methods that generate the image data. These should all be protected, by the way.

This first method simply grabs a file from public/images and displays it. Once it has the file, it creates an output stream, and writes the image file to that stream in the specified format. It then converts from the stream to a Ruby string, via a Java byte array.

def get_jpeg filename
imagefile = File.new("#{RAILS_ROOT}/public/images/#{filename}.jpg")
os = ByteArrayOutputStream.new
ImageIO.write(ImageIO.read(imagefile), "jpeg", os)
String.from_java_bytes(os.toByteArray)
end

Converting to another format is trivial (though noticeably slower).

def get_png filename
imagefile = File.new("#{RAILS_ROOT}/public/images/#{filename}.jpg")
os = ByteArrayOutputStream.new
ImageIO.write(ImageIO.read(imagefile), "png", os)
String.from_java_bytes(os.toByteArray)
end

This version does an operation, converting the image to its negative.

def get_jpeg filename
imagefile = File.new("#{RAILS_ROOT}/public/images/#{filename}.jpg")
bi = ImageIO.read(imagefile)
big = bi.getGraphics

# Create a look-up table as an array
lut = Array.new(256) { |j| 256 - j }
# Convert to Java byte array
jlut = lut.to_java :byte
# Convert to Java byte look-up table
blut = java.awt.image.ByteLookupTable.new(0, jlut)
# Convert to Java operation look-up table
op = java.awt.image.LookupOp.new(blut, nil)
# Apply the operation
dest = op.filter(bi, nil)
# Draw the new image
big.drawImage(dest, 0, 0, nil);
# Create an output stream, and write the image to it
os = ByteArrayOutputStream.new
ImageIO.write(dest, "jpeg", os)
String.from_java_bytes(os.toByteArray)
end

The next method actually generates a GIF image on-the-fly, putting the filename in the middle of a simple image. The basic methodology is to create a blank BufferedImage object (rather than from a file), use that to create a Graphics2d object (just as before). The image is built by calling methods on the Graphics2d object, and finally the last three lines create the output stream and write the image to it (just as before).

WIDTH = 400
HEIGHT = 300

def get_gif filename
off_image = BufferedImage.new(WIDTH, HEIGHT,
         BufferedImage::TYPE_INT_ARGB)

g2 = off_image.createGraphics()
g2.setRenderingHint(RenderingHints::KEY_ANTIALIASING,
         RenderingHints::VALUE_ANTIALIAS_ON)

# Set up background
g2.setPaint(Color.lightGray)
g2.draw3DRect(0, 0, WIDTH - 1, HEIGHT - 1, true);
g2.draw3DRect(3, 3, WIDTH - 7, HEIGHT - 7, false);

x = 7;
y = 7;
x3_points = [x, WIDTH - 2 * x, x, WIDTH - 2 * x]
y3_points = [y, HEIGHT - 2 * y, HEIGHT - 2 * y, y]
filled_polygon = GeneralPath.new(GeneralPath::WIND_EVEN_ODD,
              x3_points.length);
filled_polygon.moveTo(x3_points[0], y3_points[0])
(1...x3_points.length).each do |index|
 filled_polygon.lineTo(x3_points[index], y3_points[index])
end
filled_polygon.closePath()
g2.setPaint(Color.red)
g2.fill(filled_polygon)
g2.setPaint(Color.black)
g2.draw(filled_polygon)
g2.setPaint(Color.yellow)
g2.setFont(Font.new("Helvetica", Font::PLAIN, 22))
g2.drawString(filename, WIDTH / 2, HEIGHT / 2)

os = java.io.ByteArrayOutputStream.new
ImageIO.write(off_image, "gif", os)
String.from_java_bytes(os.toByteArray)
end

Okay, so we can generate images; how do we get them on to a web browser? The easy way (to test the above work) is to invoke it though the address bar. Say I have an image saved in public/images called sheep.jpg, I can use the following:

http://localhost:3000/images/image/sheep.jpg
# => Gives a negative of the image
http://localhost:3000/images/image/sheep.png
# => Gives the PNG converted image
http://localhost:3000/images/image/sheep.gif
# => Gives the generated GIF with "sheep" written on it

What we really want to to have those images embedded in a web page. So here is a view, views/images/image.html.erb, that will do just that:

<h1>My <%= @name %> image</h1>

<%# This image has been generated on the fly and served via my controller %>
<%= image_tag "/images/image/#{@name}.gif", :size => '400x300' %>

<%# This image has been processed and served via my controller %>
<%= image_tag "/images/image/#{@name}.jpg", :size => '400x300' %>

<%# This image has been processed and served via my controller; uses HTML tags directly %>
<img alt="Sheep" height="300" src="/images/image/<%= @name %>.jpg" width="400" />

<%# This image has been processed and served via my controller as a PNG %>
<%= image_tag "/images/image/#{@name}.png", :size => '400x300' %>

<%# This image came directly from public/images %>
<%= image_tag "/images/#{@name}.jpg", :size => '400x300' %>

<%# This image has been processed and served via my controller, va a helper method %>
<%= images_image_tag @name, :jpg, :size => '400x300' %>

One of those methods uses a helper method, and that would be my prefered way. Here is that method:

def images_image_tag name, type, options = {}
image_tag "/images/image/#{@name}.#{type.to_s}", options
end

Struggling with Ruby: Contents Page

JRuby and Java

If you are using JRuby you can access Java functionality in your Ruby code pretty easily. Two possible reasons to do this are to create an involved user interface (for simple GUIs the Shoes toolkit is excellent, but it is somewhat limited), and to create images on-the-fly for your Rails application (RMagick offers an alternative, but cannot be used with Tomcat, which requires JRuby). I may be posting on both these later, but first, I want to explore the basics of using Java from Ruby.

I am using NetBeans, which has a particular file structure for projects, and also makes the creation of a JAR file very easy (just press F11 and the main project gets built and compressed into a JAR file inside the dist folder). For testing purposes, I created a project called JavaForRuby (and so a package name javaforruby), with a simple test class, TestObject, which has two instance variables, with setters and getters, and a static method, getStatus, which returns a String object.

And so to the Ruby side. You need to be running JRuby, of course. I had some problems getting my Java classes to work, and this seemed to be resolved by using a more recent version of JRuby (1.3.0RC1), though I am sure it should work with older versions.

The first thing to do is introduce Java to Ruby, then you need to get your JAR linked up, and then your class loaded. Once that is done, the class can be accessed:

include Java

require "#{File.dirname(__FILE__)}/../../JavaForRuby/dist/javaforruby.jar"

include_class "javaforruby.TestObject"

p TestObject.getStatus

Creating and using an instance of a Java object is simple:

tobj = TestObject.new 'My Tester', 5
p tobj.class      # => Java::Javaforruby::TestObject
p tobj.java_class # => class javaforruby.TestObject
p tobj.get_name   # => "class javaforruby.TestObject"My Tester"
p tobj.get_value  # => 5
tobj.set_name 'My Renamed Tester'
p tobj.get_name   # => "My Renamed Tester"

Note that I used the conventions of Java to define the object, but the conventions of Ruby to access it from Ruby; JRuby has associated get_name in Ruby with getName() in Java.

Let us try using an array. JRuby adds a to_java method to Array, and this takes a single parameter, the class of the objects for the array.

ja = %w(one two three).to_java :string
p ja.java_class     # => [Ljava.lang.String;
p ja[0]             # => "one"
p ja[0].class       # => String
ja.each { |e| p e } # => "one"
                   "two"
                   "three"

What happens if you do not tell it the object type? Well, the each method still works the same; the system works out what each object is. Accessing elements by their index is not so good (I did wonder if fetch might work, but throws an NoMethodError exception).

ja = %w(one two three).to_java
p ja.java_class     # => [Ljava.lang.Object;
p ja[0]             # => #<Java::JavaLang::String:0x155d3a3 @java_object=#<Java::JavaObject:0x57e787>>
p ja[0].class       # => "Java::JavaLang::String
ja.each { |e| p e } # => "one"
                   "two"
                   "three"

We can combine the array and the custom class to make a Java array of Java objects in Ruby.

toja = [
TestObject.new('My First Tester', 5),
TestObject.new('Second', 93),
TestObject.new('Last Tester', 42),
].to_java

toja.each { |e| p e.get_name }

Hashes are no problem either.

hash = {:name => 'Fred', :age => 27}
java_hash = java.util.HashMap.new(hash)
p java_hash.get :name  # => "Fred"

Rather than using the full path name in the method call, you can import each class. You should be able to import a complete package, though I could not get it to work with JRuby 1.3.0RC1 (but I could with 1.1.6).

java_import "java.util.HashMap"
# import "java.util"
# include_package "java.util"

hash = {:name => 'Fred', :age => 27}
java_hash = HashMap.new(hash)
p java_hash.get :name

You can use import rather than java_import, and many tutorials indeed do this. However, import conflicts with Rake, and so, although your system will work as expected, your tests will fail with a NameError and complaints about a 'const_missing' (thanks to Charles Oliver for pointing this out).

Also, I had a problem with import java.io.File. Ruby warns that the constant File already exists ("warning: already initialized constant File"), and I think that this gets overwritten, and the code that is expecting the original value gets very confused... You need to restart your web server after that. Again, java_import seems to fix this.

See also:
http://wiki.jruby.org/wiki/Calling_Java_from_JRuby

Struggling with Ruby: Contents Page