Wednesday, March 17, 2010

Up and running with Ruport

The need to revamp an aging report generator at work led to a bit of research and thus to Ruport, an impressive Ruby framework for report generation.

Ruport has suffered the same fate as many a young open source project: fast and frenzied development ("release early and often" and all that cal) has resulted in enough API changes that many of the examples and documentation useless. The best info to date is in the Ruport Book, but even this book does not get one up-and-running with anything more than the most trivial projects (e.g. printing reports for a CSV table).

Let's define "up and running" as "generating reports from data in an existing database" -- the problem that most people are likely trying to solve, but which somehow doesn't appear as a ready example in most of the Ruport documentation. What follows is a quick demonstration of getting Ruport "up and running".

First, install the requisite gems. Note: PostgreSQL is being used as the database in this example instead of MySQL, both because it is a better RDBMS and because the world has enough MySQL examples already.

gem install ruport      
# DBI support                                                      
gem install ruport-util
gem install pg
gem install dbi
gem install dbd-pg
# ActiveRecord and AAR support
gem install activerecord
gem install acts_as_reportable
# Extras - not covered in this example
gem install gruff
gem install documatic

The database is assumed to have the following configuration:

* host : db
* port : 15434
* database: stuff
* user: dba
* password : secret

The 'stuff' database has the table 'complaint' which will the reports will pull data from. Note that the name is not 'complaints', as ActiveRecord would expect (and create) -- the point here is to connect to an existing database, most likely managed by someone else (who, even more likely, is neither a Rails developer nor a fan of the 'database is subservient to the code' approach).

Generating a Report Using the Ruby DBI

Connecting to a database table via the Ruby DBI requires the Ruport::Query class, which has been moved to the ruport-util gem. Ruport::Query maintains a list of named database sources, with the default source being named, surprisingly, :default.

Generating a report therefore becomes a matter of adding a database connection via Ruport::Query#add_source, then instantiating a Ruport::Query and printing its result:

require 'rubygems'
require 'ruport'
require 'ruport/util'

Ruport::Query.add_source( :default, 

                          :user => 'dba', 
                          :password => 'secret',
                          :dsn => 'dbi:Pg:database=stuff;host=db;port=15434' 

                        )
puts Ruport::Query.new( [['select * from complaint']] ).result

All in all, nice and straightforward, except for that argument to Ruport::Query#new. According to the docs, this should be as simple as

Ruport::Query.new( 'select * from complaint' )

...but this fails, and a look at the source shows that Ruport::Query invokes the each() method of the sql argument (which, being a String, as no each() method). To make matters worse, if sql is an array, then the each() method of sql.first() is invoked, so the nested array is needed. This is very likely a bug, and may be fixed in future versions, rendering this example and caveat obsolete.

Generating a Report Using ActiveRecord

The ActiveRecord support in Ruport is a bit more complicated. In particular, the acts_as_reportable (AAR) mixin must be added to every ActiveRecord table class.

Note that with ActiveRecord, a table must be wrapped by a class derived from ActiveRecord::Base. For existing databases, this class can be empty -- although it may be necessary to override the table_name method if the database schema does not conform to ActiveRecord's expectations.

require 'rubygems'
require 'ruport'
require 'ruport/acts_as_reportable'

ActiveRecord::Base::establish_connection( :adapter='postgresql',
                                          :host=>'db', 

                                          :port='15434',
                                          :database=>'stuff'
                                          :username => 'dba',
                                          :password => 'secret' )

class Complaint < ActiveRecord::Base
   acts_as_reportable
   def table_name()
       return 'complaint'
  end
end

puts Complaint.report_table


The report_table method is what AAR exposes to generate a Ruport report for the table.

These quick examples should suffice to get Ruport connected to an existing database without spelunking through the docs and gem source. Once connected, it's simply a matter of playing with Ruport and following the (rest of the) docs.