IT-eye

It’s a big pain having to manually format source code. Usually i write all my code without indenting. There are basically two ways to automatically format your code: using your IDE or using a build script. I prefer the latter. Source formatting in the IDE has two problems. First, you need to manually invoke the formatter. Second, developers can change the formatting preferences, which leads to formatting differences between developers.

Using a build script, in my case an Ant build script, guarantees that my files are formatted, even if i forget to invoke the format action in the IDE. In my opinion, source files need to be formatted almost after every change, to avoid having unformatted source files in CVS. Comparing different versions of a file is a lot easier when the files are formatted. The build script also solves the second problem. All developers use the same build script, which means that formatting is consistent.

For java source code it’s easy to define an Ant task which will format the source code. There are quite a number of formatting tools you can run from Ant: jalopy, jindent, and some others. But for sql source code i haven’t found any useable tools. That is, until Oracle released Project Raptor.

Project Raptor doesn’t contain an Ant task for sql formatting, but it does contain some java code which formats sql source. So it’s not difficult to create an Ant task to format sql source code.

Here’s an example of how i would like to use this formatter:

<taskdef name="format-sql" classname="..." classpathref="lib.path" />
<format-sql>
  <fileset dir="sql">
    <include name="**/*.sql"/>
  </fileset>
</format-sql>

The first step is to create a class which extends Task. In the execute method we’ll loop through all the sql source files as specified using the nested fileset task.

public void execute() throws BuildException {

    Iterator fileSetListIterator = fileSetList.iterator();
    while (fileSetListIterator.hasNext()) {
        FileSet fileSet = (FileSet)fileSetListIterator.next();
        DirectoryScanner ds = fileSet.getDirectoryScanner(getProject());

        String[] files = ds.getIncludedFiles();
        for (int i = 0; i < files.length; i++) {
            try {
               format(new File(ds.getBasedir(), files[i]));
            } catch (Exception e) {
                throw new BuildException(e.getMessage());
            }
        }
    }
}

For every file the format method is called. This is where we need to call Raptors sql formatter. The jar file which contains the sql formatter is oracle.dbtools.sqlformatter.10.1.3.jar. You can find this file in %RAPTOR_HOME%\jdev\extensions. You need to place this jar in your classpath. Doing the formatting is actually pretty straight forward. You create a file reader and writer for the source and destination files, and instantiate PrettyPLSQL to do the formatting:

private void format(File file) throws FileNotFoundException, IOException,
                                      TokenStreamException {
    System.out.println("Formattings: " + file);
    boolean status = false;
    File backupFile = new File(file.getAbsolutePath() + ".bck");
    File outputFile = new File(file.getAbsolutePath());
    // delete existing backup file
    status = backupFile.delete();
    // rename file to backup file
    status = file.renameTo(backupFile);
    if (!status) {
        throw new BuildException("Failed to create backup file " +
                                 backupFile.getAbsolutePath());
    }
    // create reader and writer
    FileReader reader = new FileReader(backupFile);
    FileWriter writer = new FileWriter(outputFile);
    // format sql file
    System.setProperty("ANTLR_USE_DIRECT_CLASS_LOADING", "true");
    PrettyPLSQL prettyPlsql = new PrettyPLSQL();
    PLSQLLexer lexer = new PLSQLLexer(reader);
    writer.write(prettyPlsql.format(lexer, getFormatOptions()));
    // close reader and writer
    writer.close();
    reader.close();
}

The getFormatOptions method returns a FormatOptions object. You can use this object, for example, to specify if identifiers need to be uppercase or lowercase.

During testing i got some ClassNotFound exceptions. Turns out that you can specify how certain classes need to be loaded using the ANTLR_USE_DIRECT_CLASS_LOADING property. Jad is a big help to discover these things.

More info:

• Writing Your Own Task
• Ant 1.6 for Task Writers