Ruby report generation tool

(Berger, Daniel) #1

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

(James Edward Gray II) #2

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

[snip very interesting project description]

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.

I've done quite a bit of this kind of thing for one company I work for. We CSV the data into Excel and then just hit it with formatting macros to pretty it up. I've looked at using Excel's XML format in the past, which allows most formatting, but its lack of chart support (when I last checked, some time ago) was a show stopper for my company.

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?

I can't speak for everyone, but I do a lot of custom reporting in my work (which is what led me to feed Greg the hack in the first place) and I find your software very interesting. I would download it and play with it, if released.

James Edward Gray II

···

On Aug 11, 2005, at 9:35 AM, Berger, Daniel wrote:

(Greg Brown) #3

Funny you should mention it. I've created a generic reporting framework
for use at work.

<snip project details>

Any reports that wanted special formatting like that I would probably handle
separately, or create a subclass that handled it.

<snip>

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?

Yes. Everyone does like to do reporting their own way :slight_smile:
Ruport's goal will be to facilitate this, not get in the way of it.

I'm basically going to collect all the little things we all need to do
that get annoying and put them together in a generic framework so that
you can have the low level drudgery done for you and focus on the
actual task. This is similarly inspired by Jame's HighLine library and
it's purpose. Ruport is not going to be a massively heavy
auto-generator, just a tool to help along the way.

If you publish your code, I know you'll have at least two people
playing with it already.
If you do so, I encourage you to be nice and make it GPL compliant so I
can steal all your good ideas.

As far as the PDF example goes... that was a pretty bad example. I
promise a good one once Ruport is more well formed :wink:

(Berger, Daniel) #4

James Edward Gray II wrote:

<snip>

I've done quite a bit of this kind of thing for one company I work for. We CSV the data into Excel and then just hit it with formatting macros to pretty it up. I've looked at using Excel's XML format in the past, which allows most formatting, but its lack of chart support (when I last checked, some time ago) was a show stopper for my company.

The Spreadsheet::WriteExcel port I did was originally for reporting needs. However, I decided that PHB's wasted too much of my time with inane crap like, "Can you make the column headers blue?". From now on they get CSV files only. Nyah.

I can't speak for everyone, but I do a lot of custom reporting in my work (which is what led me to feed Greg the hack in the first place) and I find your software very interesting. I would download it and play with it, if released.

I'll try to get something out tonight or tomorrow. Otherwise it will have to wait a while due to work issues.

Regards,

Dan

PS - The html-table package was also written with reports in mind. :slight_smile:

(Daniel Berger) #5

James Edward Gray II wrote:

<snip>

I can't speak for everyone, but I do a lot of custom reporting in my
work (which is what led me to feed Greg the hack in the first place)
and I find your software very interesting. I would download it and
play with it, if released.

Ok, I've put something together.

Download it from
http://rubyforge.org/frs/download.php/5524/gruf-0.0.1.tar.gz

Regards,

Dan

(Greg Brown) #6

Daniel Berger wrote:

Ok, I've put something together.
Download it from
http://rubyforge.org/frs/downl oad.php/5524/gruf-0.0.1.tar.gz

Neat! I will look at this more later but it definitely has some of the
things I'm looking for. Is it okay if I integrate some of this into
Ruport?

(James Edward Gray II) #7

Thanks for sharing this. I'm playing with it and getting a lot of good ideas. :smiley:

James Edward Gray II

···

On Aug 11, 2005, at 4:46 PM, Daniel Berger wrote:

James Edward Gray II wrote:

<snip>

I can't speak for everyone, but I do a lot of custom reporting in my
work (which is what led me to feed Greg the hack in the first place)
and I find your software very interesting. I would download it and
play with it, if released.

Ok, I've put something together.

Download it from
http://rubyforge.org/frs/download.php/5524/gruf-0.0.1.tar.gz

(Daniel Berger) #8

Greg Brown wrote:

> Daniel Berger wrote:

> Ok, I've put something together.
> Download it from
> http://rubyforge.org/frs/downl oad.php/5524/gruf-0.0.1.tar.gz

Neat! I will look at this more later but it definitely has some of the
things I'm looking for. Is it okay if I integrate some of this into
Ruport?

Go ahead. :slight_smile:

Dan

(Daniel Berger) #9

James Edward Gray II wrote:

···

On Aug 11, 2005, at 4:46 PM, Daniel Berger wrote:

> James Edward Gray II wrote:
>
> <snip>
>
>
>> I can't speak for everyone, but I do a lot of custom reporting in my
>> work (which is what led me to feed Greg the hack in the first place)
>> and I find your software very interesting. I would download it and
>> play with it, if released.
>>
>
> Ok, I've put something together.
>
> Download it from
> http://rubyforge.org/frs/download.php/5524/gruf-0.0.1.tar.gz

Thanks for sharing this. I'm playing with it and getting a lot of
good ideas. :smiley:

Thanks. I was just thinking that I should add a "config" directory for
those things you can't really hard code in a generic way, such as a
mailhost, etc. I'd use YAML.

Regards,

Dan

(Greg Brown) #10

Neat! I will look at this more later but it definitely has some of the
things I'm looking for. Is it okay if I integrate some of this into
Ruport?

Go ahead. :slight_smile:

Thank you Daniel. You will be given credit for anything I use. Anyone
who has any ideas or suggestions for a reporting engine in pure ruby
feel free to speak up as my first version will be arbitrarily released
on August 28th on rubyforge. (Project name: Ruport)

(Greg Brown) #11

Daniel Berger wrote:

I was just thinking that I should add a "config" directory for
those things you can't really hard code in a generic way, such as a
mailhost, etc. I'd use YAML.

This exists in Ruport :slight_smile:
The more I look through your code, the happier I am that you have
released it.

Thank you!

(Daniel Berger) #12

Greg Brown wrote:

Daniel Berger wrote:
> I was just thinking that I should add a "config" directory for
> those things you can't really hard code in a generic way, such as a
> mailhost, etc. I'd use YAML.

This exists in Ruport :slight_smile:
The more I look through your code, the happier I am that you have
released it.

Thank you!

Without looking at Ruport, I added support for YAML config files in
gruf. So, say you have a config file that looks like this:

# foo.config
host: blah.com
mailhost: bar.com
ENV:
   LD_LIBRARY_PATH: /usr/lib
   ORACLE_HOME: /opt/oracle

Then you call Report#configure. This will automatically generate
"host" and "mailhost" methods (accessors) for you, with the values set
appropriately. The ENV tag is special in that it simply sets
environment variables for your script (which I need sometimes).

Checked into CVS. :slight_smile:

Regards,

Dan

(Greg Brown) #13

Daniel Berger wrote:

Without looking at Ruport, I added support for YAML config files in
gruf. So, say you have a config file that looks like this:

You'd need to break into some computers to see Ruport just yet :slight_smile: It
hasn't been released yet! However, the development head will be moved
to CVS on rubyforge when the first release comes out. So it's only
going to be a secret until the 28th

# foo.config
host: blah.com
mailhost: bar.com
ENV:
   LD_LIBRARY_PATH: /usr/lib
   ORACLE_HOME: /opt/oracle

Then you call Report#configure. This will automatically generate
"host" and "mailhost" methods (accessors) for you, with the values set
appropriately. The ENV tag is special in that it simply sets
environment variables for your script (which I need sometimes).

Very neat! I'll definitely look into something like this for Ruport.
I don't handle ENV variables yet, though my config setup (or Jame's
actually) is essentially the same.

-Greg

(Daniel Berger) #14

Greg Brown wrote:

Daniel Berger wrote:

> Without looking at Ruport, I added support for YAML config files in
> gruf. So, say you have a config file that looks like this:

You'd need to break into some computers to see Ruport just yet :slight_smile: It
hasn't been released yet! However, the development head will be moved
to CVS on rubyforge when the first release comes out. So it's only
going to be a secret until the 28th

>
> # foo.config
> host: blah.com
> mailhost: bar.com
> ENV:
> LD_LIBRARY_PATH: /usr/lib
> ORACLE_HOME: /opt/oracle
>
> Then you call Report#configure. This will automatically generate
> "host" and "mailhost" methods (accessors) for you, with the values set
> appropriately. The ENV tag is special in that it simply sets
> environment variables for your script (which I need sometimes).

Very neat! I'll definitely look into something like this for Ruport.
I don't handle ENV variables yet, though my config setup (or Jame's
actually) is essentially the same.

-Greg

I've put release 0.0.2 out there which has the config support, and a
few other minor tweaks and fixes.

http://rubyforge.org/frs/download.php/5594/gruf-0.0.2.tar.gz

Regards,

Dan