Architecture and Data Blog

Lots of time hand coded SQL is written in the applications for performance sake, getting data using complex SQL out of the tables. As the database structure changes it becomes really hard to find out all the SQL that needs to be changed. In other cases SQL is generated based on certain conditions in the code and its hard to find if the generated SQL is valid after database changes are done.

Usually the problem surfaces when table structures are asked to be changed and team members reply by saying I will have to find all the places where this is used

How

Every project tends to have custom SQL in the form of either SQL in the client code such as Java or database code written in the form of stored procedures, views, triggers, packages and functions. As part of the iterative development cycles parts of the code and the database structure are bound to change.

Creating a suite of tests that can invoke the relevant code so that it can be verified that the code is valid with respect to syntax and also is functionally correct provides a safety to confidently make the changes required.

Lets look at Java code that is generating a SELECT statement based on the parameters it receives

sqlString.append("SELECT customerId, name, customernumber FROM customer WHERE 1 = 1 ");
if (countryId != null) {
    sqlString.append(" AND countryId = ").append(countryId);
}
if (regionId != null) {
    sqlString.append(" AND regionId = ").append(regionId);
}
if (salesPersonId != null) {
    sqlString.append(" AND salesPersonId = ").append(salesPersonId);
}
sqlString.append(" ORDER BY customerId");

As shown in the code snippet above, its hard to see the resulting SELECT generated, this SELECT statement generation can be verified by executing the method using techniques described in Behavior Driven Database Development as shown in the code snippet below

CustomerSearchBuilder searchBuilder = new CustomerSearchBuilder();

List<Customer> nullParameters = searchBuilder.searchCustomersFor(null, null, null);
assertEquals(4, nullParameters.size());

List<Customer> noSalesPerson = searchBuilder.searchCustomersFor(1L, 1L, null);
assertEquals(1, noSalesPerson.size());

List<Customer> noCountry = searchBuilder.searchCustomersFor(null, 1L, 99L);
assertEquals(1, noCountry.size());

List<Customer> noRegion = searchBuilder.searchCustomersFor(1L, null, 99L);
assertEquals(1, noRegion.size());

List<Customer> customers = searchBuilder.searchCustomersFor(1L, 1L, 99L);
assertEquals(1, customers.size());

Similar situations are also encountered when using other SQL constructs like INSERT code below shows the insert statement being used by the java code

try {
    stmt = DB.prepare("insert into customer" +
            "(customerid,customernumber,name,taxlocation) " +
            "values (?, ?, ?, ?)");
    stmt.setLong(1, customerToInsert.getCustomerId());
    stmt.setLong(2, customerToInsert.getCustomerNumber());
    stmt.setString(3, customerToInsert.getName());
    stmt.setString(4, customerToInsert.getTaxLocation());
    stmt.execute();
} catch (SQLException exc) {
    DB.HandleDBException(exc);
} finally {
    DB.cleanup(stmt);
}

The INSERT statement should be executed by the test method using a test customer, which should be removed after the test is done. The code below shows the test code.

public void testCustomerGatewayInsert() throws Exception {
    Customer insertCustomer = createTestCustomer("Mike", customerIdToUse);
    customerGateway.insert(insertCustomer);
    Customer foundCustomer = customerGateway.findByCustomerPOID(customerIdToUse);
    assertEquals("Incorrect Customer ID", insertCustomer.getCustomerId(), foundCustomer.getCustomerId());
    cleanupTestDataForCustomerId(insertCustomer.getCustomerId());
}

This method of automated execution of all the hand written SQL helps to execute all the database code used by the application. These kinds of tests are known as Integration Testing, if anything changes in the database all of the Database Integration Tests can be run to find out what portions of the application are affected, these integration tests can also be run as part of continuous integration build as described in Continuous Integration.