LucidDbSqlDiffTesting

Introduction

This page describes how to make use of some of the diff-based testing frameworks built into LucidDB for the purpose of test-driven ETL script development, as raised in this thread.

The necessary glue code is provided inline, as well as some simple example test scripts.

Setup

Create a directory for your tests outside of your LucidDB installation directory. We'll refer to the test directory as test, and the LucidDB installation directory as luciddb (see LucidDbGettingStarted for installation instructions).

Create Test Class

Copy and paste the Java code below into a file named test/SqlDiffTest.java:

import java.io.*;
import junit.extensions.*;
import junit.framework.*;
import net.sf.farrago.util.*;
import net.sf.farrago.test.*;

/**
 * SqlDiffTest runs a .sql script via sqlline and diffs
 * the output against a golden .ref file.
 */
public class SqlDiffTest extends FarragoTestCase
{

    public SqlDiffTest(String testName)
        throws Exception
    {
        super(testName);
    }

    public static Test suite()
        throws Exception
    {
        return gatherSuite(
            FarragoProperties.instance().testFilesetUnitsql.get(true),
            new SqlDiffTestFactory() {
                public FarragoTestCase createSqlTest(String testName)
                    throws Exception
                {
                    return new SqlDiffTest(testName);
                }
            });
    }

    protected static Test gatherSuite(
        String fileSet,
        SqlDiffTestFactory fac)
        throws Exception
    {
        StringReader stringReader = new StringReader(fileSet);
        LineNumberReader lineReader = new LineNumberReader(stringReader);
        TestSuite suite = new TestSuite();
        for (;;) {
            String file = lineReader.readLine();
            if (file == null) {
                break;
            }
            suite.addTest(fac.createSqlTest(file));
        }
        return wrappedSuite(suite);
    }
    
    // override FarragoTestCase
    public static Test wrappedSuite(TestSuite suite)
    {
        TestSetup wrapper =
            new TestSetup(suite) {
                protected void setUp()
                    throws Exception
                {
                    staticSetUp();
                }

                protected void tearDown()
                    throws Exception
                {
                    staticTearDown();
                }
            };
        return wrapper;
    }
    
    // override FarragoTestCase
    public static void staticSetUp()
        throws Exception
    {
        CleanupFactory.setFactory(
            new SqlDiffCleanupFactory());
        FarragoTestCase.staticSetUp();
    }
    
    // override FarragoTestCase
    public static void staticTearDown()
        throws Exception
    {
        FarragoTestCase.staticTearDown();
        CleanupFactory.resetFactory();
    }

    protected void runTest()
        throws Exception
    {
        // mask out source control Id, etc
        setRefFileDiffMasks();
        runSqlLineTest(getName());
    }

    public interface SqlDiffTestFactory
    {
        public FarragoTestCase createSqlTest(String testName)
            throws Exception;
    }
    
    /**
     * Custom implementation of CleanupFactory.
     */
    private static class SqlDiffCleanupFactory extends CleanupFactory
    {
        // override CleanupFactory
        public Cleanup newCleanup(String name) throws Exception
        {
            return new SqlDiffCleanup(name);
        }
    }
    
    /**
     * Custom implementation of Cleanup.
     */
    public static class SqlDiffCleanup extends Cleanup
    {
        public SqlDiffCleanup(String name)
            throws Exception
        {
            super(name);
        }

        public void execute()
            throws Exception
        {
        }
    }
}

(The base class FarragoTestCase normally tries to clean up by dropping all non-system objects at the start of each test run, but we don't want that behavior, so we override the Cleanup.execute method to stub it out.)

Create Ant Buildfile

Next, copy and paste the ant build script below into test/build.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project name="test" basedir="." default="test">

  <property name="luciddb.dir" location="/path/to/luciddb" />
  <filelist id="test.sql" dir="." files="test1.sql,test2.sql"/>
  <pathconvert property="test.sql" refid="test.sql"
    pathsep="${line.separator}" dirsep="${file.separator}" />

  <path id="test.classpath">
    <pathelement path="."/>
    <pathelement path="${luciddb.dir}/lib/mdrlibs/jmi.jar"/>
    <pathelement path="${luciddb.dir}/lib/farrago.jar"/>
    <pathelement path="${luciddb.dir}/lib/sqlline.jar"/>
    <pathelement path="${luciddb.dir}/plugin/LucidDbClient.jar"/>
  </path>
  <property name="test.classpath" refid="test.classpath"/>

  <target name="compile">
    <javac
      srcdir="."
      destdir="."
      classpathref="test.classpath">
      <include name="**/*.java"/>
    </javac>
  </target>

  <target name="test">
    <junit printsummary="true">
      <sysproperty key="net.sf.farrago.fileset.unitsql" value="${test.sql}"/>
      <sysproperty key="net.sf.farrago.test.jdbcDriverClass"
        value="com.lucidera.jdbc.LucidDbRmiDriver"/>
      <classpath>
        <pathelement path="${test.classpath}"/>
      </classpath>
      <formatter type="plain"/>
      <test name="SqlDiffTest" todir="."/>
    </junit>
  </target>

</project>

Edit the definition of the luciddb.dir property to point to your LucidDB installation directory.

Note that we use a filelist rather than a fileset for the list of .sql files to be run so that they will be executed in the specified order.

Build Test Class

Now, you'll need a copy of ant installed in order to compile the glue code.

$ ant compile
Buildfile: build.xml

compile:
    [javac] Compiling 1 source file to /home/cleanroom/test

BUILD SUCCESSFUL

Create Test Scripts

Copy and paste this to test/test1.sql:

call applib.drop_schema_if_exists('TEST1', 'CASCADE');

create schema test1;
create table test1.days2007(
date_key date not null primary key,
day_of_week varchar(20));

And test/test2.sql:

insert into test1.days2007
select time_key, day_of_week
from table(applib.fiscal_time_dimension(2007, 1, 1, 2007, 12, 31, 1));

select count(*) from test1.days2007;

Generate Golden Files

Now, make sure the LucidDB server is up, then run the test. The first time, it will fail, since no reference files exist yet:

$ ant test
Buildfile: build.xml

test:
    [junit] Running SqlDiffTest
    [junit] Tests run: 2, Failures: 2, Errors: 0, Time elapsed: 1.779 sec
    [junit] Test SqlDiffTest FAILED

BUILD SUCCESSFUL

This fact shows up in the test report in test/TEST-SqlDiffTest.txt:

...
Testcase: /home/cleanroom/test/test1.sql took 0.446 sec
        FAILED
Reference file /home/cleanroom/test/test1.ref does not exist
...
Testcase: /home/cleanroom/test/test2.sql took 1.137 sec
        FAILED
Reference file /home/cleanroom/test/test2.ref does not exist

Verify the output generated in test/test1.log:

0: jdbc:luciddb:rmi://> call applib.drop_schema_if_exists('TEST1', 'CASCADE');
0: jdbc:luciddb:rmi://> 
0: jdbc:luciddb:rmi://> create schema test1;
0: jdbc:luciddb:rmi://> create table test1.days2007(
> date_key date not null primary key,
> day_of_week varchar(20));
0: jdbc:luciddb:rmi://> 
0: jdbc:luciddb:rmi://> !quit

And test/test2.log:

0: jdbc:luciddb:rmi://> insert into test1.days2007
> select time_key, day_of_week
> from table(applib.fiscal_time_dimension(2007, 1, 1, 2007, 12, 31, 1));
0: jdbc:luciddb:rmi://> 
0: jdbc:luciddb:rmi://> select count(*) from test1.days2007;
+---------+
| EXPR$0  |
+---------+
| 365     |
+---------+
0: jdbc:luciddb:rmi://> 
0: jdbc:luciddb:rmi://> !quit

Now rename them to create the golden files:

$ mv test1.log test1.ref
$ mv test2.log test2.ref

Rerun

Now that the reference files exist, rerunning the test should succeed:

$ ant test
Buildfile: build.xml

test:
    [junit] Running SqlDiffTest
    [junit] Tests run: 2, Failures: 0, Errors: 0, Time elapsed: 1.573 sec
BUILD SUCCESSFUL

TBD

• Eclipse setup
• setting haltonfailure=true (normally this is what you want, but for the first run with multiple .sql files, it takes multiple runs to generate the .ref files)
• various sqlline options and other tricks