Testing database naming conventions in Java

I was recently reminded about some tests that we ran as part of the continuous integration build on my last project – tests that made sure that the database table, column and constraint names adhered to the client’s standards. The idea is quite simple and did on a number of occasions during development prove to be very useful. These tests certainly avoided any last-minute pre-release manual testing.

The tests were all contained in a single class and ran from a Buildr project (below). The tests use JUnit and Hamcrest along with Java SQL:

public class DatabaseNamingStandardsTest {

    private static Set tableNames;
    private static Connection connection;

    @BeforeClass
    public static void connectToOracle() throws SQLException, 
                                                ClassNotFoundException, 
                                                IOException {

        String configFileName = System.getProperty("config_file");

        Properties properties = new Properties();
        properties.load(new FileInputStream(configFileName));

        String url = properties.getProperty("database.url");
        String driver = properties.getProperty("database.driver");
        String username = properties.getProperty("database.username");
        String password = properties.getProperty("database.password");

        Class.forName(driver);

        connection = null;
        connection = DriverManager.getConnection(url, username, password);

        tableNames = listOfTableNames();
    }

    @AfterClass
    public static void tearDownConnections() throws SQLException {
        connection.close();
    }

    private static Set listOfTableNames() throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
                "SELECT table_name\n" +
                "FROM user_tables");
        Set result = new HashSet();
        while (resultSet.next()) {
            String tableName = resultSet.getString("TABLE_NAME");
            result.add(tableName);
        }
        return result;
    }

    @Test
    public void tableNamesShouldBeOfMaximum25CharactersInLength() {
        for (String tableName : tableNames) {
            assertThat(tableName.length(), lessThan(26));
        }
    }

    @Test
    public void ColumnNamesShouldBeOfMaximum30CharactersInLength() throws SQLException {
        Statement statement = connection.createStatement();
        for (String tableName : tableNames) {
            ResultSet resultSet = statement.executeQuery(
                    "SELECT * \n"
                    +"FROM user_tab_columns \n"
                    +"WHERE table_name='" + tableName + "'");
            while (resultSet.next()) {
                String columnName = resultSet.getString("Column_Name");
                assertThat(columnName.length(), lessThan(31));
            }
        }
    }
....
}

And here is the Buildr project definition…

define 'database-naming-standards' do
  test.with LIQUIBASE_JAR, JUNIT_JAR, HAMCREST_JAR, ORACLE_CLIENT_JAR
  test.using :properties => { :config_file => ("../../#{$oracle_properties_file}")}
  test.setup(
    ORACLE_DB_SCHEMA.rollback_all,
    ORACLE_DB_SCHEMA.update
  )
  package(:jar)
end