Java Ant task ported to Groovy - Part 1

After reading one of Mr. Haki's recent posts, it reminded me of an Ant task that I had written a couple of years ago that we use in our nightly build. I started thinking about how much shorter and concise the code would be if written in Groovy.  Rather than doing it all in one large posting, I have decided to break it out into 2 or 3 posts and will include all the final Groovy code in the last posting.

The Ant task: Validator
Our product supports several different databases and while can you generally code SQL to the 'lowest common denominator" for a lot of statements, we were encountering problems with our 'upgrade' scripts mostly due to syntax that was more lenient in Oracle and SQLServer but more restrictive in MySQL.   Since the update scripts are not run every day/night, I came up with the idea of using some regular expressions to try and catch these errors as early in the cycle as possible, which is the scheduled nightly build.

Inputs & Outputs
The validator task takes three input parameters and one output parameters.

Inputs

1. extension - the extension for the file types to be validated
2. directory - the directory from which to start checking files
3. propertyFile- a properties file containing sets of properties for each validation to be performed

Outputs

1. reportFile - name of the output html file to be created with the results of the validation

Java implementation 

The java implementation of this tasks consists of 3 classes.  The first class, Validator, handles the Ant processing, loads the property file into a list of objects, RegExDescriptors, contains a main loop to visit all directories and files starting at the directory parameter and lastly, writes an output report.   The second class, FileValidator, validates each file line-by-line and records any 'matches' against the regular expressions that represent an error in the SQL runtime syntax.  Lastly, property settings are grouped together into a RegExDescriptor, that contains the description of the error being validated, the regular expression used to 'match' the error and an exclusion regular expression, which is used to exclude some files from some tests. For example, Oracle scripts using the 'nvarchar' data type would be an error so we only want to run that validation against Oracle scripts and not against the SQLServer or MySQL scripts.

Sample property file
This is a sample file containing some of the regular expressions currently being used.   These are subject to change since, in some cases, backward slash had to be escaped in the pattern, like the last example sql.3.regex.

#################################################################
#
# SQL file regular expressions
#
#################################################################

#
# Instances of lines starting with GO
#
sql.1.regex=^GO
sql.1.description=GO statement

#
# Oracle scripts using nvarchar
#
sql.2.regex=nvarchar
sql.2.description=Oracle scripts using nvarchar
sql.2.exclude=SQLServer|MySQL|Store Update

#
# MySQL scripts using ntext
#
sql.3.regex=\\sntext
sql.3.description=MySQL scripts using ntext data type
sql.3.exclude=SQLServer|Oracle

Minor limitation

One minor limitation of this tool is that it only works on a single line at a time.  SQL statements that are longer than a single line are not validated as one SQL statement but simply as lines of text.  This has not been a problem as you can see from the sample validations listed above.  The errors we try to catch are a mixture of syntax and runtime errors we have encountered.

Additionally, this will not be a 'exact' port, meaning, it will not be an Ant/Gant task, but merely a Groovy script, which could then easily be converted for use in those tools.

Port to Groovy 
The first part of the port is easy - replacing all the code that recursively starts at a specified directory and creates a list of files matching the extension provided. First the Java version:

/*
     * Process all files and directories under dir
     */ 
    private void visitAllDirsAndFiles(File dir) {
        process(dir);
    
        if (dir.isDirectory()) {
            FileFilter fileFilter = new FileFilter() {
                public boolean accept(File file) {
                    return file.isDirectory() || file.getName().endsWith(extension);
                } };
            
            File[] children = dir.listFiles(fileFilter);
            for (int i=0; i<children.length; i++) {
                visitAllDirsAndFiles(children[i]);
            }
        }

Now the Groovy version

def sqlFiles = new FileNameFinder().getFileNames(dir', '**/*.sql')

Notice the Ant-like "**/*" prefix for the extension. Groovy does all the work and returns a list of files matching the criteria!

Upcoming 

• In Part 2
  
• Using the CliBuilder for a command line interface (instead of Ant)
  
• ValidatorDescriptor as a replacement for RegExDescriptor
• Consuming the property file
• In Part 3 
• Using the HTML Builder to generate a report
• Complete listing of the script and classes
• Replace the FileValidator class