Be careful using GStrings for SQL

If you are like me, you love the ease with which you can create Groovy scripts to manipulate your databases. Gone are the days of writing utility classes to make these changes, thanks to Groovy and the SQL package. But if you aren't careful, you can run into a couple of strange errors if you use the GStrings.

For the below example, I have a table named LOCATION in my 'test' database that has two columns: location_id and location_name. The example below works fine and you notice that I am not using any variables in the SQL statement

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def sqlStatement = """select * from location where location_id > 3"""

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

Next, try making the SQL a bit more dynamic by specifying the table name, column name and key value as variables within the GString and watch what happens.

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def tableName = 'location'
def columnName = 'location_id'
def key = 3
def sqlStatement = """select * from $tableName where $columnName > $key"""

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

WARNING: Failed to execute: select * from ? where ? > ? because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''location' where 'location_id' > 3' at line 1

The quick fix I found was to call the toString() method on the GString and this resolves the problem. I found a line in "Groovy In Action" which helps explain the problem:

The special use of GStrings as SQL statements limits the use of placeholders to places where a question mark would otherwise be allowed in a prepared statement.

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def tableName = 'location'
def columnName = 'location_id'
def key = 3
def sqlStatement = """select * from $tableName where $columnName > $key""".toString()

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

Keep this in mind the next time you use the Groovy SQL class!

Get value from SQL into an Ant property

Recently I had to adjust some of our JMS configuration and needed make sure we set the ServerPeerID configuration value to a unique value among our servers. We have a unique server number in our database so I figured that I would just use that value. In order to do this, I needed to get the database value into an Ant property, which is then used to generate the configuration file, which includes the ServerPeerID. Below is the Ant task and steps I used to do this.

Steps:

1. Define where the SQL results will be written
2. Make sure the file does not already exist
3. SQL task to get the result
4. Load the file back into a property
   
Use the propertyregex task to strip out just the unique number

Warning: The syntax highlighter appears to be reformatting my XML and not allowing the self closing tags. Probably my problem but you should be able to get the idea from the

   
   
   
      
      SELECT PREF_VALUE FROM PREFERENCE WHERE PREFERENCE_ID = 71;                
    
     
    
    

Groovy Sockets example

I found several sites and blogs with Groovy socket examples but none that showed a matched pair of the server and the client sending data back and forth. Collecting bits from different sources, I put together the following server and client scripts using Groovy.

The sample works by sending a line of data as a request; thus the server calls readLine() and the client sends a new line character at the end of the input. The server echos the request back to the client along with a timestamp. For those that skip reading the documentation, ServerSocket's accept method "Accepts a connection and passes the resulting Socket to the closure which runs in a new Thread." so this server creates a new thread for each request.

Groovy Socket server

import java.net.ServerSocket
def server = new ServerSocket(4444)

while(true) {
    server.accept { socket ->
        println "processing new connection..." 
        socket.withStreams { input, output ->
            def reader = input.newReader()
            def buffer = reader.readLine()
            println "server received: $buffer"
            now = new Date()
            output << "echo-response($now): " + buffer + "\n"
        }
        println "processing/thread complete."
    }
}

Groovy Socket client

s = new Socket("localhost", 4444);
s.withStreams { input, output ->
  output << "echo testing ...\n"
  buffer = input.newReader().readLine()
  println "response = $buffer"
}

Give it try! Hope this helps...

Trouble connecting to Web Start client?

Do you have problems getting either the debugger or JConsole attached or connected to your Web Start client?  We worked around this for way too long until we finally found the trick: environment variable JAVAWS_VM_ARGS.

JNLP allows for vm args to be passed to the Java runtime but there is a limited set of arguments that are allowed to be passed on.  Anything not in that list of secure properties is ignored.  You can find the list of secure properties in the Developers Guide.

As I said ealier, if you want to attach a debugger or JConsole to you Web Start application, then you need to use the JAVAWS_VM_ARGS parameter.   For example, if I want to attached to my web start client for debugging purposes then I can do the following:

set JAVAWS_VM_ARGS=-Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005


in my environment, start the client and then attach to the process using port 5005.  The same goes for attaching to JConsole, set the properties you need in JAVAWS_VM_ARGS, so com.sun.management.jmxremote.* should all be set inside JAVAWS_VM_ARGS.

Hope this help!!!

Ant task ported to Groovy - Part 3

Welcome to the final posting of this series where I have been porting one of my Ant tasks from Java to Groovy.  If you haven't read part 1 or part 2, then you might want to take a quick pass through those before reading this posting.  In this posting, I will show the following:

• FileValidator class ported to Groovy
  
• Using HTML Builder to generate the HTML report 
  
• Full listings of the scripts & classes created

FileValidator ported to Groovy
One of the last tasks left to do is to port the FileValidator class from Java over to Groovy.  The FileValidator class is created in the process() method of the Validator class and called for every file to be validated.  This is another example of where the Groovy code is a lot shorter and more concise than the Java code.   The FileValidator class handles the iteration thru a file, line by line, checking each regular expression checking for a potential error condition.

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;

/**
 * File validator runs a list of regular expressions against the file
 * attempting to validate the file.  Errors are kept in a list and
 * then the caller can get the list of errors and log them - right
 * now the ant task will log them. 
 *
 */
public class FileValidator {

    private List regExDescriptors;
    private File file;
    private List errors;
    
    /**
     * Default constructor
     * @param list List of RegExDescriptors used to validate a file
     * @param file File to be validated
     */
    public FileValidator(List list, File file) {
        regExDescriptors = new ArrayList(list);
        this.file = file;
        errors = new ArrayList();
    }
    
    public List getErrors() {
        return errors;
    }
    
    public void validate() {
      // see if we should exclude this file  
      processExcludes();
      
      // Run the regEx(s) against the file to check for problems
      BufferedReader in = null;
      try {
          in = new BufferedReader(new FileReader(file));
          String str;
          int lineNumber = 1;
          while ((str = in.readLine()) != null) {
              for (Iterator iter = regExDescriptors.iterator(); iter.hasNext();) {
                  RegExDescriptor validator = (RegExDescriptor) iter.next();
                  Matcher matcher = validator.getMatcher(str);
                  if (matcher.find()) {
                   errors.add( validator.getDescription() + " on line " + lineNumber);
                  }
              }
              lineNumber++;
          }
      } catch (IOException e) {
          e.printStackTrace();
      } finally {
          if (in != null)
              try {
                  in.close();
              } catch (IOException e) {
              }
      }  
    }

    private void processExcludes()
    {
        String fullName;
        try
        {
            fullName = file.getCanonicalPath();
        }
        catch (IOException e)
        {
            fullName = file.getName();
            e.printStackTrace();
        }
        for (Iterator iter = regExDescriptors.iterator(); iter.hasNext();)
        {
            RegExDescriptor descriptor = (RegExDescriptor) iter.next();
            if (descriptor.excludeFile(fullName))
            {
                iter.remove();
            }
        }
    }    
} 

Groovy code for file validation

//**************************************
// process each file, line by line
//**************************************
files.each {fileName ->
  def errors = []
  def lines = new File(fileName).readLines()
  def lineNumber = 1
  lines.each {line ->
    // for each line - iterate thru descriptors looking for matches if file not excluded
    descriptorList.each {
      if (!(fileName =~ /$it.exclude/)) {
        if ((line =~ /$it.regex/).find()) {
          errors.add(it.description + " on line " + lineNumber)
          errCnt++
        }
      }
    }
    lineNumber++
  }
  if (errors)
    results.put((fileName), errors)
}

Generating the results report
The last part of the port was to create an html output report similar to a JUnit report.  The Java code could have been better but it was simply a large set of write statements.  I decided to use the HTML markup builder to generate the output report.

//*******************************************************
// generate the report
//*******************************************************
def writer = new FileWriter("$opt.r")
def html = new groovy.xml.MarkupBuilder(writer)
def rptDate = new Date()

html.html {
  head{
    title 'Validator Report'
  }
  body {
    h1 "File Validation - as of $rptDate"
    h2 "Validation Descriptors for file extension $opt.e"
    table ('border':0, 'cellpadding':5, 'cellspacing':2, 'width':'80%'){
      tr ('bgcolor':'#a6caf0') {
        th ("Description")
        th ("Validating Regular Expression")
      }
      //iterate thru descriptors - listing the regular expressions used in validation
      descriptorList.each { descriptor ->
        tr ('bgcolor':'#eeeee0') {
          td (descriptor.description)
          td (descriptor.regex)
        }
      }
    }
    // Summary table - just a file & error count
    h2 'Validation Summary'
    table ('border':0, 'cellpadding':5, 'cellspacing':2, 'width':'80%'){
      tr ('bgcolor':'#a6caf0') {
        th ("Files Processed")
        th ("Error Count")
      }
      tr ('bgcolor':'#eeeee0')  {
        td ("$files.size")
        td ("$errCnt")
      }
    }
    if (errCnt)
    {
      // Error details table
      h2 "Validation Details for extension $opt.e "
      table ('border':0, 'cellpadding':5, 'cellspacing':2, 'width':'100%'){
        tr ('bgcolor':'#a6caf0') {
          th ("File")
          th ("Errors")
          th ("Details")
        }
        //iterate thru results map key is filename and value is list of error text)
        results.each { k, v ->
            tr ('bgcolor':'#eeeee0')  {
               td (k)
               td (v.size())
               td {
                 ul {
                   //iterate thru the list of errors
                   v.each {
                     li (it)
                   }
                 }
               }
            }
        }
      }
    }
  }
}
writer.toString()

Porting Results
Initial Java Ant task:  3 classes and 326 lines of code
Groovy port: 1 script of 102 lines of code and 1 class with 9 lines of code.   There are about 40 lines of processing code and the rest of the code/lines are from the html markup builder for the report.

Shorter and more concise, you decide!  

Groovy script and class source
Here's the full Validator.groovy script

/**
 *  Validator script ported from Java Ant task
 */

//********************************************
// handle command line parms - all required
//********************************************
def cli = new CliBuilder(usage: 'groovy Validator -e extension -p propertyFile -d directory -r reportFile')

cli.h(longOpt: 'help', 'usage information')
cli.e(longOpt: 'extension', args: 1, required: true, 'file extension to be validated')
cli.p(longOpt: 'prop', args: 1, required: true, 'property file containing regular expresssion')
cli.d(longOpt: 'directory', args: 1, required: true, 'base directory to start file search')
cli.r(longOpt: 'reportFile', args: 1, required: true, 'output file for validation report')

def opt = cli.parse(args)
if (!opt) return
if (opt.h) cli.usage()

println "Processing $opt.e files \n\tusing $opt.p \n\tfrom directory $opt.d \n\tand generate report output to $opt.r"
println 'extracting files...'

//*******************************************
// get all the files for provided extension
//*******************************************
def files = new FileNameFinder().getFileNames(opt.d, "**/*.$opt.e")
println "processing $files.size files..."

//**************************************************************
// load properties into list of ValidatorDescriptor objects
//**************************************************************
Properties properties = new Properties();
try {
  properties.load(new FileInputStream(opt.p));
} catch (IOException e) {}

def config = new ConfigSlurper().parse(properties)
def descriptorList = []
config."$opt.e".each {
  descriptorList << new ValidatorDescriptor(it.value)
}

def errCnt = 0
def results = [:]

//**************************************
// process each file, line by line
//**************************************
files.each {fileName ->
  def errors = []
  def lines = new File(fileName).readLines()
  def lineNumber = 1
  lines.each {line ->
    // for each line - iterate thru descriptors looking for matches if file not excluded
    descriptorList.each {
      if (!(fileName =~ /$it.exclude/)) {
        if ((line =~ /$it.regex/).find()) {
          errors.add(it.description + " on line " + lineNumber)
          errCnt++
        }
      }
    }
    lineNumber++
  }
  if (errors)
    results.put((fileName), errors)
}

//*******************************************************
// generate the report
//*******************************************************
def writer = new FileWriter("$opt.r")
def html = new groovy.xml.MarkupBuilder(writer)
def rptDate = new Date()

html.html {
  head{
    title 'Validator Report'
  }
  body {
    h1 "File Validation - as of $rptDate"
    h2 "Validation Descriptors for file extension $opt.e"
    table ('border':0, 'cellpadding':5, 'cellspacing':2, 'width':'80%'){
      tr ('bgcolor':'#a6caf0') {
        th ("Description")
        th ("Validating Regular Expression")
      }
      //iterate thru descriptors - listing the regular expressions used in validation
      descriptorList.each { descriptor ->
        tr ('bgcolor':'#eeeee0') {
          td (descriptor.description)
          td (descriptor.regex)
        }
      }
    }
    // Summary table - just a file & error count
    h2 'Validation Summary'
    table ('border':0, 'cellpadding':5, 'cellspacing':2, 'width':'80%'){
      tr ('bgcolor':'#a6caf0') {
        th ("Files Processed")
        th ("Error Count")
      }
      tr ('bgcolor':'#eeeee0')  {
        td ("$files.size")
        td ("$errCnt")
      }
    }
    if (errCnt)
    {
      // Error details table
      h2 "Validation Details for extension $opt.e "
      table ('border':0, 'cellpadding':5, 'cellspacing':2, 'width':'100%'){
        tr ('bgcolor':'#a6caf0') {
          th ("File")
          th ("Errors")
          th ("Details")
        }
        //iterate thru results map key is filename and value is list of error text)
        results.each { k, v ->
            tr ('bgcolor':'#eeeee0')  {
               td (k)
               td (v.size())
               td {
                 ul {
                   //iterate thru the list of errors
                   v.each {
                     li (it)
                   }
                 }
               }
            }
        }
      }
    }
  }
}
writer.toString()

println "validation complete - possible error count = $errCnt"

//********************************
// for testing launch browser
//********************************
//"c:/program files/Internet Explorer/iexplore.exe $opt.r".execute()

Here's the one short class - ValidatorDescriptor.groovy

class ValidatorDescriptor {
    String description
    String exclude
    String regex

    String toString() {
       "regEx="+regex + " description=" + description + (exclude ? " exclude("+exclude+")" : "")
    }
}

And finally, my set of properties used for validation:

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

#
# Find instances of trailing spaces after the semicolon.
# This causes MySQL problems.
#
#
sql.1.regex=;\\s+$
sql.1.description=Trailing spaces after semicolon
sql.1.exclude=SQLServer|Oracle

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


#
# Find spaces before ending semicolon (but not DELIMITER ; which is used for MySQL Stored Procedures)
#
sql.4.regex=^(?!DELIMITER)\\s+;$
sql.4.description=Spaces before ending semicolon
sql.4.exclude=SQLServer|Oracle


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

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

Summary
There you go - another itch successfully scratched!   Maybe this task/script will be of help to others.  The design was to make this a generic file validation utility and the SQL validation is the only use we have found so far.  Enjoy!

Ant task ported to Groovy - Part 2

Welcome back if you read part 1 of this posting.  If you haven't read part 1, you may want to take a quick look at that before reading this part of the posting.  This posting (part 2) will show the following changes or ports from Java to Groovy for the validator ant task.

• CliBuilder for command line interface
• ValidatorDescriptor class
• Consuming the properties file
  

Switch from Ant to a Groovy task
As I mentioned in part 1, this port will not be an exact port because I am creating a groovy script rather than an Ant/Gant task - partly because I haven't played with Gant yet!  Some of the existing Ant task code is shown below. For those familiar with creating Ant tasks, you will notice the class extends Task which is an abstract class.   The code overrides the execute() method.   The validator task checks the input and output parameters and then calls the validate() method to validate the files.

import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.PatternSyntaxException;

import org.apache.tools.ant.BuildException;
import org.apache.tools.ant.Task;

public class Validator extends Task {

    private static final String DEFAULT_EXCLUDE = "";
    
    // constants used to create the property file keys 
    // --> extension..regex or extension..description 
    private static final String REGEX       = ".regex";
    private static final String DESCRIPTION = ".description";
    private static final String EXCLUDE     = ".exclude";
    
    private String extension;
    private String directory;
    private String propertyFile;
    private String reportFile;
    
    private int fileCnt;
    private int errorCnt;
    private List descriptors;
    private Properties props;
    private Map errorMap;

    /**
     * Default constructor
     */
    public Validator() {
        props = new Properties();
        descriptors = new ArrayList();
        errorMap = new HashMap();
        errorCnt = 0;
        fileCnt = 0;
    }

    /**
     * Sets the extension for the task
     * @param extension extension of the files to be validated
     */
    public void setExtension(String extension) {
        this.extension = extension;
    }
    
    /**
     * Sets the root directory to be processed.  This directory
     * and all of its subdirectories will be processed.
     * @param directory directory to be processed.  
     */
    public void setDirectory(String directory) {
        this.directory = directory;
    }
    
    /**
     * Sets the root directory to be processed.  This directory
     * and all of its subdirectories will be processed.
     * @param directory directory to be processed.  
     */
    public void setPropertyFile(String fileName) {
        this.propertyFile = fileName;
    }

    /**
     * Set the report file to be created as output
     * @param reportFile name of the html file to be
     *   generated to show any errors
     */    
    public void setReportFile(String reportFile) {
  this.reportFile = reportFile;
 }
    
 /**
     * Creates an xml file from the bugs file
     */
    public void execute() throws BuildException {
        checkParameters();
        try {
            validate();
        } catch (BuildException e) {
            throw e;
        }
    }

    /**
     * Check that all required attributes have been set
     * @throws BuildException if any of the required attributes
     *      have not been set.
     */
    private void checkParameters() {
           if (extension == null || extension.length() == 0  
                ||   directory == null || directory.length() == 0 
                ||   reportFile == null || reportFile.length() == 0 
                || propertyFile == null || propertyFile.length() == 0) 
               throw new BuildException("Extension, directory, reportFile and property file attributes "
                      + "must be defined for task <" + getTaskName() + "/>" );
    }

    private void validate() {
        File dir = new File(directory);
        log("Processing file extension : "+extension+"\n");
        
        try {
            props.load(new FileInputStream(propertyFile));
                      
            setDescriptors();
            visitAllDirsAndFiles(dir);
            
            writeReport();
            
            log("Validator processed "+fileCnt+ " files and found "+errorCnt+" possible errors!");
            
        } catch (Exception e) {
            log(e.toString());
        }
    }
   private void writeReport() {
     try {
  FileWriter writer = new FileWriter(reportFile);
  writeHeaders(writer);
  writeContents(writer);
  writeTrailer(writer);
  writer.close();
 } catch (IOException e) {
  throw new BuildException(e);
 }
   }

   // some code omitted here - but shown later!!!
}

Groovy replacement using CliBuilder
While not a whole lot shorter than the Ant equivalent, it is short, concise and a good example of CliBuilder usage.

def cli = new CliBuilder (usage: 'groovy Validator -e extension -p propertyFile -d directory -r reportFile')

cli.h(longOpt: 'help', 'usage information')
cli.e(longOpt:'extension',  args:1, required:true, 'file extension to be validated')
cli.p(longOpt:'prop',       args:1, required:true, 'property file containing regular expresssion')
cli.d(longOpt:'directory',  args:1, required:true, 'base directory to start file search')
cli.r(longOpt:'reportFile', args:1, required:true, 'output file for validation report')

def opt = cli.parse(args)
if (!opt) return
if (opt.h) cli.usage()

println "start processing $opt.e files using $opt.p from $opt.d and output to $opt.r"

RegExDescriptor becomes ValidatorDescriptor
First the java...

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

/**
 * Regular Expression Descriptor used to help
 * validate source files.  
 *
 */
public class RegExDescriptor {

    private String description;
    private Pattern excludePattern;
    private Pattern pattern;

    public RegExDescriptor(String regExString, String description, String exclude) throws PatternSyntaxException {        this.description = description;
        pattern = Pattern.compile(regExString, Pattern.CASE_INSENSITIVE);
        if (exclude != null && exclude.length() > 0)
            excludePattern = Pattern.compile(exclude);
        else
            excludePattern = null;
    }

    public Matcher getMatcher(String line) {
        return pattern.matcher(line);
    }

    public String getDescription() {
        return description;
    }
    
    public boolean excludeFile(String name) {
        if (excludePattern == null) 
            return false;
        Matcher matcher = excludePattern.matcher(name);
        return matcher.find();
    }

    public String getPattern()  {
     return pattern.pattern();
    }
    
    public String toString() {
        StringBuffer sb = new StringBuffer();
        sb.append("Validator description=").append(description);
        sb.append(" regEx=").append(pattern.pattern());
        if (excludePattern != null)
            sb.append(" exclude regEx=").append(excludePattern.pattern());
        return sb.toString();
    }
}

Now the Groovy equivalent, which I renamed to ValidatorDescriptor. Like a lot of Java classes ported to Groovy, it is significantly shorter since we don't need to specify the getters and setters. The toString() method is really needed but I used it in testing to display the ValidatorDescriptor classes created from the properties file.

class ValidatorDescriptor {
    String description
    String exclude
    String regex

    String toString() {
       "regEx="+regex + " description=" + description + (exclude ? " exclude("+exclude+")" : "")
    }
}

Consuming the properties file
The next task is to read the properties file and create a list of objects representing each of the errors I am attempting to capture.   If you remember from part 1 of the posting, there can be three properties: regex, description and exclude.  The java processing to read the file and create the list of RegExDescriptors was located in two steps: the first was to read the file into a Properties object and then call the setDescriptors method in the Validator class as part of the main method called by Ant.

The idea is to group the properties together by the extension and the number that is part of the key. Each matching set is used to construct the RegExDescriptor.  The RegExDescriptor creates a Pattern for the regular expression checking for the error and another for the file name exclusion check.  The patterns are compiled at construction time.

props.load(new FileInputStream(propertyFile));
setDescriptors();

// later in Validator
/*
     * Setup the regular expression descriptors 
     * using the extension to determine which properties
     * are to be used when validating the files.
     */
    private void setDescriptors()
    {
        // strip the leading . and add one to end
        String prefix = extension;
        char c = extension.charAt(0);
        if (c == '.')
            prefix = prefix.substring(1);
        prefix = prefix + ".";
        
        int idx = 1;
        boolean done = false;
        
        // build list of validators using the extension to create the property keys
        while(!done) {
            String regexKey = prefix + idx + REGEX;
            if (props.containsKey(regexKey)) {
                String descriptionKey = prefix + idx + DESCRIPTION;
                String excludeKey = prefix + idx + EXCLUDE;
                try {
                    idx++;
                    String exclude = props.getProperty(excludeKey, DEFAULT_EXCLUDE);
                    descriptors.add( new RegExDescriptor(props.getProperty(regexKey),
                                                       props.getProperty(descriptionKey),
                                                       exclude) ); 
                } catch (PatternSyntaxException e) {
                    StringBuffer sb = new StringBuffer(100);
                    sb.append("Exception compiling regular expression key(").append(regexKey);
                    sb.append(") :").append(props.getProperty(regexKey)).append("\n").append(e);
                    sb.append("\nSkipping this expression...\n");
                    log(sb.toString());
                }
            } else {
                done = true;
            }
        }
    }

Now for the Groovy implementation.  It starts out the same, loading a Properties object.  Next, we use the ConfigSlurper class to group and parse the properties.  An empty list to hold the descriptors is defined and then we iterate across the extension (sql in my case) keys and build the new ValidatorDescriptor objects. Notice the use of the GString aware string as part of the key for calling the each closure.
 

Properties properties = new Properties();
  try {
     properties.load(new FileInputStream('validator.properties'));
  } catch (IOException e) {}

  def config = new ConfigSlurper().parse(properties)
  def descriptorList = []
  config."$extension".each {
     descriptorList << new ValidatorDescriptor(it.value)
  }

In the next (and last) posting in this series, we will cover the following:

• Replace the FileValidator class
• Using the HTML Builder to generate a report
• Complete listing of the script and classes

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