My Projects/dbbuild {image:My Projects/dbbuild/jazoonlogo_web.gif|Jazoon08|gif} 1 Introduction As follow up of the presentation {link:3020 - Let the Continuous Build Embrace the Database|http://jazoon.com/jazoon08/en/conference/presentations/tl/3020} at {link:Jazoon 08|http://jazoon.com} the following software is published. Note that it is {link:License Free Software|http://en.wikipedia.org/wiki/License-free_software} so feel free to use. We are open for discussions and suggestions but the software is distributed as-is - without support warranty or whatsoever. Many thanks to {link:Telekurs Card Solutions|http://www.telekurs.com} for allowing us to publish this work! 1 Continuous Database Build This software distribution allows you to set up a continuous database build as described in the presentation. It consists of two parts: - Setup: Run this scripts once on the database schemas where your continuous database build should operate or where it should be installed. It adds a few infrastructure objects that are required to keep track of the SQL scripts that have been installed in the database. - Build: This is the collection of shell and SQL scripts that is able to run a database build. It can be run by a continuous integration system but as well directly from a shell. We use the scripts under Linux (SuSE) and Solaris 8. There are some Flash movies showing a continuous database build system in action: - {link:Create new table and add new column|http://www.stefanrufer.ch/java/jazoon08/jazoon08-3020_demo-1.htm} - {link:Rename a column|http://www.stefanrufer.ch/java/jazoon08/jazoon08-3020_demo-2.htm} - {link:Install a database release using the shell script built by the database build|http://www.stefanrufer.ch/java/jazoon08/jazoon08-3020_demo-3.htm} Downloads: - {link:Download Setup Part.|space/My Projects/dbbuild/database-2008-06-21-011038.zip} - {link:Download Build Part.|space/My Projects/dbbuild/setup-2008-06-21-011038.zip} Contact: - Michael Wernli (michael dot wernli at telekurs dot com) 1 JUnit Extension This software distribution allows you to implement JUnit based test cases that clean up all database modifications after a test run using a rollback operation. A simple example test case is included below. Downloads: - {link:Download JAR.|http://stefanrufer.ch/mvn/internal/ch/stefanrufer/libraries/junitextension/junitextension/1.0.4/junitextension-1.0.4.jar} - {link:Download Source JAR.|http://stefanrufer.ch/mvn/internal/ch/stefanrufer/libraries/junitextension/junitextension/1.0.4/junitextension-1.0.4-sources.jar} - {link:Maven Site Documentation|http://stefanrufer.ch/mvn/internal/ch/stefanrufer/libraries/junitextension/junitextension/1.0.4/site/} - {link:JUnit Extension Auto Rollback Demo Project|space/My+Projects/dbbuild/junitextension-demo-autorollback.zip} - ready to run from Eclipse - {link:Multimodule Maven and Eclipse Setup Demo|space/My+Projects/dbbuild/junitextension-demo-config.zip} - a minimal multimodule build setup that demonstrates how to define database connection information in one place for both Maven 2 and Eclipse project setups. Contact: - Stefan Rufer (mail@stefanrufer.ch) {code:java} package ch.stefanrufer.libraries.junitextension.jdbc; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; /** * Demo how to use the {@link AutoRollbackTestCase}. * * It specifically shows how all of the insert/update/delete * operations in each test method work isolated because a * rollback is executed during test tear down. */ public class ExampleRollbackTestCase extends AutoRollbackTestCase { /** * Set up test table in database. * * @param ds the initialized data source * @throws SQLException if DB access fails */ @Override public void setUp(DataSource ds) throws SQLException { Statement stmt = ds.getConnection().createStatement(); stmt.execute("CREATE TABLE testtable (testcolumn VARCHAR(1))"); stmt.close(); } /** * Try to execute a DB insert and see if the table really contains * the row at the end. * * @throws SQLException if DB access fails */ public void testDbInsert() throws SQLException { Statement stmt = getDataSource().getConnection().createStatement(); int rowCount = stmt.executeUpdate("DELETE FROM testtable"); assertEquals("Excpect table to be empty initially", 0, rowCount); rowCount = stmt.executeUpdate("INSERT INTO testtable values ('X')"); assertEquals("Expect insert to affect exactly 1 row", 1, rowCount); ResultSet rs = stmt.executeQuery("SELECT * FROM testtable"); assertTrue("expect 1 row", rs.next()); assertEquals("X", rs.getString("testcolumn")); assertFalse("expect only 1 row", rs.next()); } /** * Try to execute a DB update and see if the table really contains * the updated values at the end. * * @throws SQLException if DB access fails */ public void testDbUpdate() throws SQLException { Statement stmt = getDataSource().getConnection().createStatement(); stmt.executeUpdate("INSERT INTO testtable values ('X')"); int rowCount = stmt.executeUpdate("UPDATE testtable SET testcolumn='Y' WHERE testcolumn='X'"); assertEquals("expect exactly 1 update", 1, rowCount); ResultSet rs = stmt.executeQuery("SELECT * FROM testtable"); // works because we are always on the same connection assertTrue("expect 1 row", rs.next()); assertEquals("Y", rs.getString("testcolumn")); assertFalse("expect exactly 1 row", rs.next()); } /** * Check if deleting everything works as expected. * * @throws SQLException if DB access fails */ public void testDelete() throws SQLException { Statement stmt = getDataSource().getConnection().createStatement(); stmt.executeUpdate("INSERT INTO testtable values ('X')"); stmt.executeUpdate("INSERT INTO testtable values ('Y')"); int rowCount = stmt.executeUpdate("DELETE FROM testtable"); assertEquals("Excpect 2 rows to be deleted", 2, rowCount); ResultSet rs = stmt.executeQuery("SELECT * FROM testtable"); assertFalse("expect 0 rows after delete", rs.next()); } /** * Check if the test table that we have is initially empty. * * @throws SQLException if DB access fails */ public void testInitiallyEmpty() throws SQLException { Statement stmt = getDataSource().getConnection().createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM testtable"); // previous inserts in other unit tests must not affect this assertFalse("expect 0 rows", rs.next()); } /** * Tear down test table in database. * * @param ds the initialized data source * @throws SQLException if DB access fails */ @Override public void tearDown(DataSource ds) throws SQLException { Statement stmt = ds.getConnection().createStatement(); stmt.execute("DROP TABLE testtable"); stmt.close(); } } {code} 2009-07-14 23:36:47.37 2008-06-21 01:19:39.291 Sat Jun 21 02:06:35 CEST 2008 Sat Jun 21 01:30:37 CEST 2008 Tue Jun 24 18:35:11 CEST 2008 Tue Jun 24 18:35:23 CEST 2008 Sat Jun 21 02:06:52 CEST 2008