MS SQL Server: Executing SQL Script from Command Line

PROBLEM

When opening a 150MB SQL script file in Microsoft SQL Server Management Studio, the following error appears:-

SOLUTION

Instead of opening the large SQL script file and execute it, we can execute it directly from command line.

sqlcmd -E -d[database_name] -i[sql_file_path]

… where -E uses trusted connection, -d points to the database and -i points to the SQL script file path.

For example,

sqlcmd -E -dshittydb -ic:\Users\shittyuser\shittydb.sql

IntelliJ: Overriding Log4J Configuration Globally for JUnit

PROBLEM

Most of the time, we may have several Log4J configurations depending on the environments, for example:-

  • log4j.xml (Log4J) or log4j2.xml (Log4J2) – Production configuration using socket appender.
  • log4j-dev.xml (Log4J) or log4j2-dev.xml (Log4J2) – Development configuration using console appender.

Since log4j.xml and log4j2.xml are the default configuration files for Log4J and Log4J2, these configurations will always be used unless we override the configuration file path.

In another word, if we don’t override the configuration file path and we run our JUnit test cases offline from IntelliJ, it may take a very long time to execute them due to the broken socket connection. Further, it is not a good idea to clutter our production log files with non-production logs.

SOLUTION

To fix this, we need to configure IntelliJ to always use our development Log4J configuration.

First, on the menu bar, select Run -> Edit Configurations...

Then, delete all the existing JUnit configuration files.

Finally, expand Defaults -> JUnit.

Under VM options, specify the following system property:-

  • For Log4J, specify -Dlog4j.configuration=log4j-dev.xml
  • For Log4J2, specify -Dlog4j.configurationFile=log4j2-dev.xml

Please note the slight change on the system property name depending on the Log4J version.

Now, when we run our JUnit test cases from IntelliJ, it will always pick up the correct custom Log4J configuration file.

Java + Groovy: Creating Immutable List

Java: Mutable List

// class java.util.Arrays$ArrayList
final List mutableList = Arrays.asList(1, 2, 3);

Java: Immutable List

// class java.util.Collections$UnmodifiableRandomAccessList
final List immutableList = Collections.unmodifiableList(Arrays.asList(1, 2, 3));

Java: Immutable List using Guava

// class com.google.common.collect.RegularImmutableList
final ImmutableList guavaImmutableList = ImmutableList.of(1, 2, 3);

Groovy: Immutable List

// class java.util.Collections$UnmodifiableRandomAccessList
final def groovyImmutableList = [1, 2, 3].asImmutable()

IntelliJ: Selectively Disable Line Wrap

PROBLEM

Sometimes, we have very lengthy statements that look like this:-

@Service
public class LengthOfStayHeuristicServiceImpl extends HeuristicService {
    @Override
    public Double compute(HeuristicBean heuristicBean) {
        ...
				
        setValue(map, surgeryLocationMap, LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_LUMBAR_OR_SACRAL_AND_LUMBOSACRAL_MINUS_CERVICAL_AND_NOT_SPECIFIED);
        setValue(map, surgeryLocationMap, LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_LUMBAR_OR_SACRAL_MINUS_LUMBOSACRAL);
        setValue(map, surgeryLocationMap, LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_CERVICAL_MINUS_NOT_SPECIFIED);

        return new LengthOfStayAlgorithm(map).run();
    }
}

When we reformat the code in IntelliJ, it becomes like this:-

@Service
public class LengthOfStayHeuristicServiceImpl extends HeuristicService {
    @Override
    public Double compute(HeuristicBean heuristicBean) {
        ...
				
        setValue(map,
                 surgeryLocationMap,
                 LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_LUMBAR_OR_SACRAL_AND_LUMBOSACRAL_MINUS_CERVICAL_AND_NOT_SPECIFIED);
        setValue(map,
                 surgeryLocationMap,
                 LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_LUMBAR_OR_SACRAL_MINUS_LUMBOSACRAL);
        setValue(map,
                 surgeryLocationMap,
                 LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_CERVICAL_MINUS_NOT_SPECIFIED);

        return new LengthOfStayAlgorithm(map).run();
    }
}

There are times we really don’t want the long statements to wrap around because they look very messy.

SOLUTION

While there is no option to selectively disable just the line wrap in IntelliJ, there is a way to selectively disable code formatting.

First, we need to enable the Formatter Control.

Now, we can annotate our code like this:-

@Service
public class LengthOfStayHeuristicServiceImpl extends HeuristicService {
    @Override
    public Double compute(HeuristicBean heuristicBean) {
        ...
				
        // @formatter:off
        setValue(map, surgeryLocationMap, LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_LUMBAR_OR_SACRAL_AND_LUMBOSACRAL_MINUS_CERVICAL_AND_NOT_SPECIFIED);
        setValue(map, surgeryLocationMap, LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_LUMBAR_OR_SACRAL_MINUS_LUMBOSACRAL);
        setValue(map, surgeryLocationMap, LengthOfStayAlgorithm.VariableEnum.SURGERY_LOCATION_CERVICAL_MINUS_NOT_SPECIFIED);
        // @formatter:on

        return new LengthOfStayAlgorithm(map).run();
    }
}

When we reformat the code, that portion of code will remain unformatted.

Java: Promoting Testability by Having Enum Implementing an Interface

OVERVIEW

This post illustrates how we can easily write a better test case without polluting our production code with non-production code by performing a minor refactoring to the production code.

PROBLEM

Let’s assume we have a simple Data Reader that reads all the lines of a given algorithm data file and returns them:-

public class DataReader {
    public List<String> getDataLines(AlgorithmEnum algorithm) {
        // we have `StS-data.txt`, `CtE-data.txt` and `TtI-data.txt` under `src/main/resources` dir
        String fileName = String.format("%s-data.txt", algorithm.getShortName());
        Scanner scanner = new Scanner(getClass().getClassLoader().getResourceAsStream(fileName));

        List<String> list = new ArrayList<String>();

        while (scanner.hasNextLine()) {
            list.add(scanner.nextLine());
        }

        return list;
    }
}

This API accepts an AlgorithmEnum and it looks something like this:-

public enum AlgorithmEnum {
    SKIN_TO_SKIN("StS"),
    CLOSURE_TO_EXIT("CtE"),
    TIME_TO_INCISION("TtI");

    private String shortName;
    
    AlgorithmEnum(String shortName) {
        this.shortName = shortName;
    }

    public String getShortName() {
        return shortName;
    }
}

Let’s assume each algorithm data file has millions of data lines.

So, how do we test this code?

SOLUTION 1: Asserting Actual Line Count == Expected Line Count

One straightforward way is to:-

  • Pass in one of the Enum constants (AlgorithmEnum.SKIN_TO_SKIN, etc) into DataReader.getDataLines(..)
  • Get the actual line counts
  • Assert the actual line counts against the expected line counts

public class DataReaderTest {
    @Test
    public void testGetDataLines() {
        List<String> lines = new DataReader().getDataLines(AlgorithmEnum.SKIN_TO_SKIN);
        assertThat(lines, hasSize(7500));
    }
}    

This is a pretty weak test because we only check the line counts. Since we are dealing with a lot of data lines, it becomes impossible to verify the correctness of each data line.

SOLUTION 2: Adding a Test Constant to AlgorithmEnum

Another approach is to add a test constant to AlgorithmEnum:-

public enum AlgorithmEnum {
    SKIN_TO_SKIN("StS"),
    CLOSURE_TO_EXIT("CtE"),
    TIME_TO_INCISION("TtI"),
		
    // added a constant for testing purpose
    TEST_ABC("ABC");

    private String shortName;
    
    AlgorithmEnum(String shortName) {
        this.shortName = shortName;
    }

    public String getShortName() {
        return shortName;
    }
}

Now, we can easily test the code with our test data stored at src/test/resources/ABC-data.txt:-

public class DataReaderTest {
    @Test
    public void testGetDataLines() {
        List<String> lines = new DataReader().getDataLines(AlgorithmEnum.TEST_ABC);
        assertThat(lines, is(Arrays.asList("line 1", "line 2", "line 3")));
    }
}

While this approach works, we pretty much polluted our production code with non-production code, which may become a maintenance nightmare as the project grows larger in the future.

SOLUTION 3: AlgorithmEnum Implements an Interface

Instead of writing a mediocre test case or polluting the production code with non-production code, we can perform a minor refactoring to our existing production code.

First, we create a simple interface:-

public interface Algorithm {
    String getShortName();
}

Then, we have AlgorithmEnum to implement Algorithm:-

public enum AlgorithmEnum implements Algorithm {
    SKIN_TO_SKIN("StS"),
    CLOSURE_TO_EXIT("CtE"),
    TIME_TO_INCISION("TtI");

    private String shortName;

    AlgorithmEnum(String shortName) {
        this.shortName = shortName;
    }

    public String getShortName() {
        return shortName;
    }
}

Now, instead of passing AlgorithmEnum into getDataLines(...), we will pass in Algorithm interface.

public class DataReader {
    public List<String> getDataLines(Algorithm algorithm) {
        String fileName = String.format("%s-data.txt", algorithm.getShortName());
        Scanner scanner = new Scanner(getClass().getClassLoader().getResourceAsStream(fileName));
    
        List<String> list = new ArrayList<String>();
    
        while (scanner.hasNextLine()) {
            list.add(scanner.nextLine());
        }

        return list;
    }
}

With these minor changes, we can easily unit test the code with our mock data stored under src/test/resources directory.

public class DataReaderTest {
    @Test
    public void testGetDataLines() {
        List<String> lines = new DataReader().getDataLines(new Algorithm() {
            @Override
            public String getShortName() {
                // we have `ABC-data.txt` under `src/test/resources` dir
                return "ABC";
            }
        });

        assertThat(lines, is(Arrays.asList("line 1", "line 2", "line 3")));
    }
}

Spock: Reading Test Data from CSV File

Following up on my recent post about creating a Spock specification to read the test data from a CSV file without loading all the data into the memory, I created a CSVReader that implements Iterable that allows me to pull this off. You may download the source code here.

With this implementation, I can now write an elegant Spock specification:-

class MySpockSpec extends Specification {
    @Unroll
    def "#firstNum + 1 == #secondNum"() {
        expect:
        Integer.valueOf(firstNum as String) + 1 == Integer.valueOf(secondNum as String)

        where:
        [firstNum, secondNum] << new CSVReader(getClass().getClassLoader().getResourceAsStream("test.csv"))
    }
}