Liz Douglass

Posts Tagged ‘Oracle

Testing database naming conventions in Java

leave a comment »

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
Advertisements

Written by lizdouglass

April 19, 2009 at 7:27 am

Posted in Building, Java, JUnit

Tagged with , ,

LiquiBase: Inserting data from a CSV file

with one comment

BA couple of weeks ago Nick pointed out to me that it was possible to insert data into a LiquiBase change set from a CSV file. This Load Data feature was added in version 1.7.0 of LiquiBase and is particularly useful for two reasons:

  1. It keeps the length of the changelog file down
  2. It’s easier to edit a CSV file than it is an XML file, especially when you’re adding a large amount of data to a table

While this is a great option there are a couple of things worth keeping an eye out for if you go down this path…

First, dates:
As I’ve written about before, we are using HSQLDB for development but need to support Oracle. We’ve had relatively few problems in working with both, except for the date compatibility issue.

When inserting XML date data into table entries we were using SYSDATE as the value. This works fine in XML, but does not work when loading data from a CSV file. In the CSV case LiquiBase attempts to create a formatted timestamp from the ‘SYSDATE’ string. We tried a number of ways to enter dates that were compatible in both HSQLDB and Oracle, including adding extra columns in the CSV to cater for the different databases (see below). We also tried updating the table date columns after inserting the entries using the CSV file, but this was messy because those columns have not-null constraints.

The solution to the date compatibility problem turns out is very simple (drumroll) – just define the date in one of the date formats in the Liquibase.util.ISODateFormat class (below) and LiquiBase will generate a database specific date for you!

yyyy-MM-dd'T'HH:mm:ss
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd'T'HH:mm:ss.S

Second, columns:

The documentation for the CSV load data feature gives the impression that you need to map each column in the CSV to one in the table. In fact LiquiBase will map the CSV file column headings to table column headings automatically. You only need to map the columns that have a different heading in the CSV to that in the table. Note however that we found it was necessary to map the columns containing date data, even if the headings were the same:

    <loadData tableName="My_Table" file="My_File.csv">
        <column name="CREATED_DATE" header="CREATED_DATE" type="DATE"/>
    </loadData>

Unfortunately the automatic mapping also means that you cannot have extra columns in the CSV.

Written by lizdouglass

February 1, 2009 at 11:06 am

Posted in Java

Tagged with , , ,

Things I like about Buildr

with 3 comments

A few months ago the idea of myself writing such words (“Things I like about Buildr“) seemed very unlikely and I dare say that my project buddies may be surprised at the statement. Buildr, for those unfamiliar, is a build tool for Java applications that is written in Ruby. It’s key benefit is its concise and extensible nature. A bit of the wow factor of Buildr is a bit lost on me because I’ve not yet used Maven and I’ve only used Ant in some pretty straightforward ways… and I’d not used Rake prior to using Buildr. (So a strong starting point I’m sure you’d agree. Hhhmm, anyway…)

Our project buildfile started off looking very much like the example in the Buildr documentation, but has since grown significantly in functionality but not proportionally in size. We have complimented the Buildr functionality with handfuls of Rake tasks that perform other bits and pieces associated with our build, such as:

  • Deploying a WAR package to a Tomcat server
  • Starting and stopping database and Selenium servers
  • Updating and rolling back our HSQLDB and Oracle databases using LiquiBase
  • Checking the contents of the build artifacts produced using the Buildr check functionality

The time we’ve spent trawling through the documentation and getting ankle deep in the source has paid off. I want to capture how we’ve set up some of these build activities. So without further introduction and in no particular order, here are some of my favourites (drumroll):

1. Pretty packaging

In my earlier post I described how we used One-JAR to create a single JAR containing all the bits required to update/rollback a database using LiquiBase. Below is the sub-project task that is used to package the lot. The nicest things about this sub-project are how easily we’ve packaged the included JARs, and how easily we’ve added entries into the manifest file:

        define "assembly" do
            package(:jar).path("lib").tap do | p |
                p.include artifact(LIQUIBASE_JAR).to_s
                p.include artifact(HSQLDB_CLIENT_JAR).to_s
            end
            package(:jar).path("main").tap do | p |
                p.include project('database-migration').package(:jar)
            end
            package(:jar).include file('../changelog.xml')
            package(:jar).with :manifest=>manifest.merge(
                    'One-Jar-Main-Class'=>'my.package.DatabaseMigration',
                            'Main-Class'=>'com.simontuffs.onejar.Boot'
            )
        end

2. Delightful dependencies

Some of our rake tasks require us to depend on the existence of a WAR or JAR that has been packaged in one of our buildfile sub-projects. For example, the task that deploys to a Tomcat server depends on a WAR file being generated from one of our sub-projects. Thanks to Buildr, you can do something like this:

task :foo => ['rake:MyProject:MySubProject:package'] do

Note that it is also possible and extemely easy to refer to an artifact that one of your projects has created by using syntax like this:

"#{GROUP}:MyProject-MySubProject:war:#{VERSION_NUMBER}"

3. Fabulous filtering

We are using HSQLDB for development because it’s really light. Our application also needs to support Oracle though, and hence we wanted to run some of our acceptance tests using both databases. To achieve this we created another Buildr sub-project that runs some of the tests against Oracle (we already had the one that runs all the acceptance tests with HSQLDB). There are two things about this sub-project (below) that impress me. The first is that we reuse code using Buildr’s filter task. The second is how easily we can run only a subset of the tests using ‘test.include’:

            define 'my-sub-project' do
                filter(project('bar').compile.sources).into(_('my_directory_1')).run
                filter(project('bar').test.compile.sources).into(_('my_directory_2')).run
                compile.from(_('my_directory_1'))
                compile.into(_('target/classes'))
                test.compile.from(_('my_directory_2'))
                test.compile.into(_('target/test/classes'))
                test.compile.with _('target/classes')
                test.using :properties => { :my_property => property_value }
                test.include '*only_these_tests*'
                test.setup(
                    ...
                )
                test.teardown(
                    ...
                )
            end

4. Cheeky checking

Buildr callbacks can be used to extend projects. We’ve combined the after-define callback with the handy project check task to test that we’ve built what we expect to and created a number of modules like this one:

module CheckJarExists
    include Extension

    after_define do |project|
        project.enhance do
            project.check_jar_exists project
        end
    end

    def check_jar_exists project
        project.check project.package(:jar), 'should exist' do
            puts 'checking jar package exists....'
            it.should exist
        end
    end
end

And just in-case you’re like me and unsure of how to include this module in a project task, here’s how we did it:

define "subproject" do
      extend CheckJarExists
      package(:jar)
end

I’m sure there’s lots more favourites out there in the deep blue Buildr sea… happy fishing!

Written by lizdouglass

December 20, 2008 at 11:34 pm

Posted in Building, JARs, Java, Ruby

Tagged with , , , , , ,