<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
···
-----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 toolFor 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?
