Groovy Excel Report Library

I just finished a series of reports for one of my customers using grails as the backend and Flex for the user interface. As with many of my reports for this customer, they requested the output be delivered as CSV files. Easy, and very boring.

So, with the help of JExcel and the jxl APIs from Andy Khan, I created an alternate output of an excel spreadsheet rather than CSV flat file. This has the additional benefit of sending multi-dimensional results, e.g., summary and details on two separate sheets, output statistics and charting on a third. This is much more useful for the end-user.

To make life easy, I created a wrapper around the jxl libraries to make it possible to use an alternate excel API such as Apache POI. At the same time, I created a set of conventions that use a template spreadsheet to easily create all the output without having to program any of the "view" components (i.e., classic MVC). The controller class is Workbook and the Model data is configured in Worksheet objects. All the formatting for dates, numbers, layout, etc. is in configured in templates (xsl files) easily configured and modified by non-programmers.

Since the target for this is inside war-deployed web applications I had to configure an external templates folder to enable report format changes without having to re-war/deploy or bounce the server. The templates folder is part of a more extensive Exchange class that coordinates remote data exchanges to a known area outside the web-space.

Conventions: Below is a list of convetions used to build reports...

• xls template contains all formatting and sheets for the report
• key words embedded into the template provides cell locations where data is to be written. values stored in the Worksheet object are used by the Workbook controller to update the spreadsheet output.
  
• a special type of keyword called "ReportData" is used to insert multilple rows into the spreadsheet, based on query result sets (or any list of data).

The Worksheet models also have a place holder for "options" that change/enhance the controller's behavior as it builds output. Examples include "greenbar" to create green-bar reports, "showItemNumbers" to write out the actual row number to the left of the ReportData.

Working Example: To demonstrate the report library I created a "Customer Invoice" report. The template has three sheets, includes graphic images, and shows two of the three "ReportData" sets in green bar (and blue bar) with automatic item numbering.

The Code: The full report can be built and run with just a few lines of code. Here is a typical example that prints the invoice, details, and expenses for a single period.























The three sheets are defined as details, expenses and invoice. Invoice details and expense rows are fetched for a specific period with GORM in the typical way. Report fields are extracted to match the template report data layout. The main invoice sheet has a few extra named values (line 28-31) and constructs two lines of data (lines 33 & 34).

Report Output: Here are the three sheets created by the code above.

Dynamic Grails Application Configuration

If you deploy your grails applications with war files you may find it necessary implement an application configuration mechanism that lives outside the web-space. My first though was to use and external file, probably xml and read it in when needed. But, I decided that persisting configurations to the database provided a much more universal solution. It also avails itself to adding a UI or acting as a remote web-service.

The Domain Object, AppConfig is very simple. It has a name, parameter, value and status:

class AppConfig {
 String name
 String parameter
 String value
 String status
}

The "name" and "parameter" columns uniquely identify the configuration setting and the "value" is a string representation of the parameter's value. The status can be anything, but I find it useful to set the target environment, e.g., 'dev' or 'test'. I usually use the class name as the configuration name, so looping through a set of parameters is as easy as this:

AppConfig.findAllByName( this.class.name ).each { p ->
 switch(p.parameter) {
   case 'enabled': enabled = (p.value == 'true' ? true : false)
   case 'other': other = p.value
   case 'anInt: myInt = Integer.parseInt( p.value )
 }
}

I use a standard dataset loader to read in and insert settings on application startup taking care not to clobber existing values. Configurations are read in during development, test and production.

A typical use is to set parameters for Quartz Jobs. This way, the parameters are read each time the job starts to dynamically configure actions. The most common is to enable or disable the job. This comes in handy if a job goes bad but you can't bring down the web server.

Groovy Polymorphics with Closures

Let me begin by stating the problem: I need an object that can locate files in a specific folder, parse the selected files, then execute database updates against the parsed results. The twist is that the update process might be inside a grails application using GORM, or, it might be a standalone jdbc process that has it's own data access mechanism. Here is the proposed object:

class Process {
 def findFiles() { /* return a list of files */ }
 def parseFile(file) { /* parse the file, return the data */ }

 // the update function (closure)
 def update

 // the runner
 def run() {
   def list = findFiles()
   records = []
   list.each { records << parseFile( it ) }    
   update( records )  
 }
}  

Forget about the empty methods for now, let's just assume they work. Notice that run() finds the files, parses them, then updates them. Here is how the target object is used:

def process = new Process()
process.update = (isGorm ? gormUpdater : jdbcUpdater)
process.run()

The first thing you might notice is that 'update' in the Process class has no body--it's simply a tag for the closure name. When the class becomes an object, update is assigned the closure method based on the runtime environment. Here is how a very simple GORM closure would be defined:

myGormUpdater = { records ->
records.each { row ->
  row.save()
}
}

Simple. Loop through the records and save each one. The polymorphic magic is implemented with a closure, not a sub class. This eliminates class bloat by using portable stand-alone methods assigned to normal variables of a class--like named function pointers. An added benefit is that the closure can be used on similar classes that require the same functionality.

Real World Scenario: Lets say I have a small group of software engineers. Two of my engineers are data-feed experts, another three are database domain experts. Me, well, I'm the manager.

So, since I have the big picture, I design the base class and create test fixture data. Then, I tell my feed experts to give me methods for parsing a set of files. They don't have to worry about where the files are, just how to parse and create datasets. At the same time I assign the database ops to the database group. They don't have to concern themselves with where the data comes from, just how to convert datasets into database updates for all our target databases.

Even though I'm a manager, I contribute in a real way to the code base. And, to keep my team productive, I create the test data for the parser and database groups. The experts are assigned tasks that they handle best.

Combine this with daily scrum communications and, for the hands-on CTO, this is very Groovalicious!

Flex on Grails

Borrowing from Flex/Rails libraries created about a year ago, I have created a Flex/Grails application using similar HTTP/XML protocols. The application requests report data via HTTP, the server creates the report, persisting it to a file, then the client pulls the data back with a file request. This was the only way I could get Flex/Flash to open a "file-save" dialog to let the user write to their local hard-drive.

I used Flex's HTTPService object to do the initial report request, then FileReference with additional URLVariables parameters to retrieve the file. Grails/GORM made the data retrieval easy and the controller/service packages were simple implementations. This was comparable to Flex/Rails implementation that I worked on a year ago.

The server side runs in a war distribution so the output file had to exist outside the webspace. The Flex application requests the file with an HTTP post using URLRequest with specific parameters (URLVariables) to retrieve the file. Because the server is deployed with only war files, the request is made to a specific controller with a "getfile" action that retrieves the file from someplace on the server's drive (obviously not in the war file) and is consistent with where the file was originally written. Once the file is returned to the client it is removed, so it can only be read back a single time.

This first iteration returns a CSV file, but creating a full xls spreadsheet or a PDF document would be easy to implement. There is also an xml document that can be returned to enable showing the report in a local viewer, e.g., a data grid.

Replacing GORM with Groovy Sql for Lightwieght Jobs

GORM, the Grails Object-Relational Mapping persistence framework provides a rational way to use Hibernate based on pojo (or groovy pogo) domain classes and basic conventions. Create a domain class, define some constraints, start up grails and viola! tables, columns, indexes are created without coding a line of SQL/DDL or creating annoying XML mappings.

But, what if you have a simple standalone process that requires single or multiple database access? Currently GORM has trouble working outside of grails, and creating a new grails project just to support a simple, no-client script is overkill. For that, I use groovy's database access classes in groovy.sql and a small custom library to easily access multiple data sources and provide simple query tools optimized for batch operations.

Here's an example: Tracking multiple UPS shipments. The application must access existing shipment requests from a local database, then query the UPS tracking remote service to discover shipment status. When a shipment is picked up or delivered, another local database is updated and a third system is updated via XML feed.

To summarize, the requirements are:

• provide simultaneous access to multiple databases
• query UPS tracking via HTTP requests
• create XML responses and write to a messaging system
  

Cron triggers this process multiple times during the working day. It runs on the production machine (1MB slice at slicehost), so should be kind to existing web and database applications serving the user base.

Design Implementation:
As always, I start with the tests. Using groovy test case, the database queries, inserts, and updates tests were created, then the target classes and methods. Queries are always the easiest, and updates are straight forward as well. Inserts with auto generated IDs are a bit tricker, but here is where groovy sql comes to the rescue.

Creating new tables presented a new problem: should I create SQL/DDL scripts? No! For this, it was easy enough to access the current grails application and create the new domain classes, run grails to have Hibernate create the new tables.

The groovy Sql class provides a method called executeInsert(). The magic in this method is that it returns an array of all auto-generated keys. So, if I'm working in MySql or Oracle or whatever, inserts all look the same--no database specific code required.

Database access defaults to the production environment but database tests must operate on the test database (similar to grails). This is easy to accomplish by simply overriding the default behavior in the test scripts.

Conclusions:

• Use groovy Sql for small, non-web applications that require single or multiple database access.
• Use Grails/GORM for all database/table creation and maintenance (no DDL scripts). This also has the additional benefit of enabling access to new tables through grails if required.
  
• Use executeInsert() to return auto-generated keys when inserting new rows.
  

MySQL InnoDB Dialect

I missed a configuration detail in conf/DataSource.groovy prior to going to production on a recent grails project. I didn't notice the damage until I looked closely at the backup script created by mysqldump. The re-build script called out ENGINE=MyISAM rather than InnoDB. So, no transactions, cascading, or other basic features.

The Fix: To correct the problem, I inserted the following into the hibernate section:

dialect='org.hibernate.dialect.MySQL5InnoDBDialect'

When I ran my tests, there were a couple of changes that need to be made mainly because transactions were now working correctly. The next step was to repair the production database.

The Repair: The sql rebuild script needed to be modified to replace MyISAM with InnoDB--a perfect job for groovy. Here is the script:

#!/usr/bin/env groovy
file = new File( 'production.sql' )

new File('fix.sql').withPrintWriter { writer ->
file.eachLine { line ->
  writer.println(line.replace('MyISAM', 'InnoDB'))
}
}

Simple to implement. Very groovy, and now with transactions!

Alternate Logging in Grails

For most cases, the Grails logging system, based on log4j, works fine, especially for development. But when in production, if you deploy using a single jar, it gets a bit trickier when trying to enable and disable logging. So, to that end, I have created a quick and dirty logging system using groovy's advanced file features.

Groovy Files: In java, log files are simple to configure, but you end up writing the same old try/catch/finally code--very old style. In groovy, all you need is a file name and you are good to go. Here is a groovy example:

def file = new File("test.log")
file.write("this")

No try/catch. No worrying about opening or closing. Just name it and write to it. Very groovy.

Applications Log Files: Each application has it's own specific configuration. So, in the grails-app/conf folder is the perfect place an for ApplicationConfig class to hold the application's configurations. Inside this class are a few methods that define the external logging system. The methods include getLogDirectory(), getLogProperties(), getLogfile(name), etc. For our purposes, getLogfile() is the only method we need to invoke. Again, an example:

def logger = new ApplicationConfig().getLogfile('my-service.log')
logger?.append("my message")

And that's it. So, logger? only appends the message if a log file exists. The log file is external to the web container, and easy to create (touch my-service.log) or remove. If it exists, the it gets logged to. If not, then no logging occurs. The sys-admin, armed with a list of available log file targets, can create or remove logs on the fly without touching the web server. Again, very groovy.