From: "Berger, Daniel" <Daniel.Berger@...>
Date: 2005-08-11T23:35:04+09:00
Subject: Re: Ruby report generation tool

> -----Original Message-----
> From: Greg Brown [mailto:greg7224@gmail.com] 
> Sent: Wednesday, August 10, 2005 9:46 PM
> To: ruby-talk ML
> Subject: Ruby report generation tool
> 
> 
> For as long as I can remember the end of the summer meant 
> slaving over some Free Software project before I went back to 
> school.  This year will be no exception.  I am currently 
> trying to develop and mature a pure ruby reporting tool based 
> on a very clever hack that James Edward Gray II shared with 
> me (along with some great ideas).  Basically, I am trying to 
> make a tool that will allow you to run queries against a 
> database and then get them to painless output to basically 
> whatever format you'd like weilding the power of Erb and 
> other great tools such as PDF::Writer.  So far, the system I 
> have built is functional but far from robust.  It allows you 
> to execute SQL statements passed as strings, passed in from 
> files, or even passed in from the database itself and then 
> gives you a row by row iterator which can be called from 
> within a template OR a pure ruby file.  I am also currently 
> working on implementing a simple DSL wrapper around SQL to 
> allow easy generation of queries.  I have a few ideas for 
> functions I'd like to add, but I figured the best bet would 
> be to ask the community what kinds of features they'd like to 
> see in a pure ruby report generation tool.  If you let me 
> know what you'd like to see in such a tool soon, there is a 
> good chance it will end up in Ruport 0.1.0 ([Ru]by
> Re[port]s) which will be released on August 28th on 
> RubyForge.  So... if you had your ideal reporting tool in 
> Ruby, what would it be like?

<snip>

Funny you should mention it.  I've created a generic reporting framework

for use at work.

I start with a predictable filesystem layout:

report/
   archive
   bin
   lib
   log
   mail
   sql

Then I use a predictable naming convention.  If you have a script called
"foo.rb", then it automatically uses log/foo.log for logging, looks in
mail/foo.mail for any email addresses, and looks for all related sql in
sql/foo.sql.  All configurable of course.

It's setup to make automatic handling of database connections, preparing
sql statements, email, logging, and archiving as painless as possible
(for me, anyway).

For the sql itself, I use a specific format.  Each sql statement is in
its own paragraph and tagged with a comment.  That comment is used by
method_missing behind the scenes to dynamically two seperate methods,
one that returns the sql statement itself (a string) and one that makes
the call out to the database.

So, for example, if you have a sql statement in the foo.sql file that
looks like this:

--main
select *
from foo

Then you get a Report#main_sql and Report#main_data method.  The former 
just returns the sql, while the latter makes a call out to the database.

Here's a typical example for a simple report:

# foo.rb
require "report/genericreport"

report = GenericReport.new
report.start_log
report.init("db","foo") # Connect to db, prepare all sql statements

# Use the logger instance directly
report.logger.info{ report.report_name + " started at " + Time.now.to_s
}

# Execute the main query
report.main_data{ |rec|
   File.open(report.csv_file, "w+"){ |file|
      file.puts(rec.join(", "))
   }
}

# Sends an email to everyone in mail/foo.mail
report.send_email("report finished")

# Move the .csv file to the archive directory
File.move(report.csv_file, report.archive_dir)

report.logger.info{ report.report_name + " finished at " + Time.now.to_s
}

# Cleans up all database and statement handles and the logger instance
report.finish

# END foo.rb

There's more to it than that, but that's the basic idea.  I've set it up
to do some handling of CSV files, since that's mostly what we use (and
Excel reads them fine). I've never been asked to put a report in PDF
format, actually.  Any 
reports that wanted special formatting like that I would probably handle
separately, or create a subclass that handled it.

Anyway, is something like that sorta what you had in mind?  I never
released it because I kinda figured everyone liked to do reporting their
own way.  No?

Regards,

Dan