Continuous Database Build
And: Practical Approaches For DB Based Functional Testing
Stefan Rufer – Netcetera AG Michael Wernli – Telekurs Card Solutions AG 3020
2
Speakers
> St efan Rufer – St udied business IT at t he Universit y of Applied Sciences in Bern – Senior Soft ware Engineer and Architect at Net cetera – Main int erest: Server side application development using JEE > Michael Wernli – Cert ified engineer in IT – Senior Soft ware Engineer at Telekurs Card Solut ions – As Application DBA in charge of large- scale cashless payment t ransaction syst em.
3
Why are we here?
> You will see how a cont inuous dat abase build improved our overall build st abilit y and helped us to focus on business problems during integration tests.
4
Motivation
> “Who t he #@!* dropped t he salary colum n in the build database?!” – Dude, you didn‘t run the script t o add it in t he first place… > Did t he build just turn red… – because someone forgot t o clean up t he build database – AGAIN?
> svn revert drop-that-table.sql – Hmm, the t able is st ill missing in t he DB…? > ……
5
Motivation
6
AGENDA
> Continuous Build For The Database > Re- Runnable Functional JUnit Tests > Eclipse/ Maven Setup For Functional JUnit Tests > Outlook / References
7
> Continuous Build For The Database > Re- Runnable Functional JUnit Tests > Eclipse/ Maven Setup For Functional JUnit Tests > Outlook / References
8
The Problem
> 100..200 SQL script s (DDL+ DML) per major release > Script s have t o be runnable first - t o- last at all t imes > 20 developer/ build/ int egrat ion databases t o keep in sync
Poor Dat abase Administ rat or!
9
The Idea
> Inst all dat abase script s as soon as they are commited to t he subversion reposit ory. > Run SQL script against reference database. If installat ion t erminat es without error, populat e to all other dat abases. > St ore in the dat abase what script was installed in what subversion (SVN) revision.
10
Basic Setup of a Continuous Database Build
11
Demo Part I
12
Different behaviour of database sources and Java sources during build
13
Demo Part II
14
We need re- runnable (idempotent) database source scripts
15
Delta and Source Scripts
> Most difficult is data definit ion or dat a modificat ion script s. – delta-scripts: „run once“ – Add idempot ency manually > Packages, triggers, views can be reinstalled easily. – source-scripts: „run however“ – Idempotent by design
16
Branching for database source scripts is a bit different
17
Demo Part III
18
Quality Checks
> Autom at ed quality checks for – Primary key const raint names – Unique key const raint names – Foreign key constraint names – Check constraint names – Index names for all of the above – Sequence names > Hooks provided to st art qualit y checks
19
> Continuous Build For The Database > Re- Runnable Functional JUnit Tests > Eclipse/ Maven Setup For Functional JUnit Tests > Outlook / References
20
JUnit Tests Modifying Database
21
How This All Relates
> JUnit t est s that do insert/ updat e/ delete must not leave t heir t races in the build dat abase. Aut o rollback JUnit class > Good ex perience by providing personal dat abase t o each developer. – But : How t o keep it up t o dat e? Cont inuous Database Build. > Developers want t o swit ch between a personal and a build dat abase. Eclipse/ Maven Set up
22
The Idea
> AutoRollbackTest Case (ex t ends JUnits class Test Case) > Dat asource and JDBC connection managed int ernally > Rollback called during t earDown method of t est no t races!
23
Undo, Undo!
class ExampleDBTestCase extends AutoRollbackTestCase { public void testDbInsert() throws SQLException { Statement stmt = getDataSource().getConnection().createStatement(); stmt.executeUpdate("INSERT INTO testtable values ('X')"); ResultSet rs = stmt.executeQuery("SELECT * FROM testtable"); assertTrue("expect one row", rs.next()); assertEquals("X", rs.getString("testrow")); assertFalse("expect only one row", rs.next()); } }
24
Little Burden – Big Deal
> JDBC insert/ updat e/ delete st at ements are a crucial part of ent erprise applications. They need t o be t ested! > Avoid the burden of cleaning up the database (someone will forget anyway…) > Unit test s t hat roll back at t he end offer a simple way t o run SQL: – Verify correct SQL synt ax – Verify dat abase st at e after updat e > Spring offers t he possibilit ies of AutoRollbackTest Case (and much more), check out spring-test.jar / org.springframework.test
25
> Continuous Build For The Database > Re- Runnable Functional JUnit Tests > Eclipse/ Maven Setup For Functional JUnit Tests > Outlook / References
26
Properties Overloading
> ConfiguredTest Case reads junit.propert ies > If junit_frit z.propert ies is available for user „frit z“, it is read as well Overwrit es propert ies in junit .properties > How does t his look like?
junit.properties user=build password=build url=jdbc:derby:builddb example=buildsrv:80 junit_fritz.properties user=integration password=integration
reuse URL
example=localhost:8080
27
KISS – But How?
> Scope: Mult imodule build set up using Maven 2 and Eclipse > Eclipse and Maven classpat h handling different > Eclipse: Mult iple project s referencing each ot her results in a „global“ classpat h. Where t o place dat abase connect ion informat ion for unit test s? > Maven: Each module needs junit .propert ies in src/test/resources if we want t o avoid t est resources in t he main source t ree. Where t o place dat abase connect ion informat ion for unit test s?
28
Eclipse Setup For Functional JUnit Tests
> User adds his one and only junit _USERNAME.propert ies file t o „t oplevel“ project > Eclipse uses this for all JUnit runs as it is always on the classpath User specific dat abase connect ion set t ings in Eclipse.
29
Maven Setup For Functional JUnit Tests
> junit .propert ies file in each submodule > junit .propert ies cont ains variables: junit.properties
db.user=${db.user} db.password=${db.password} db.schema=${db.schema} db.url=${db.url} pom.x ml
> Values defined in Maven POM…build
build build dpas10
…and inject ed during Maven lifecycle process-test-resources
30
Test- properties: Define once, use everywhere
31
> Continuous Build For The Database > Re- Runnable Functional JUnit Tests > Eclipse/ Maven Setup For Functional JUnit Tests > Outlook / References
32
Outlook
> Continuous Dat abase Script Suite currently t ailored t o Oracle syst em s – Consider generic implement at ion or at least identify and isolat e specialized point s > Test dat a management is t he nex t big t opic on our radar. As we have t o select sensible stuff from 1TB of dat a we need some clever ideas… > Int egrat e Maven/ Eclipse setup with a tool like MavenIDE.
33
Links / References
> Full source for continuous database build system http:/ / www.stefanrufer.ch/ dbbuild > Spring Testing
htt p:/ / stat ic.springframework.org/ spring/ docs/ 2.5.x / reference/ test
34
The End
35
Two things to remember
Store in the database how the database was built.
Write JUnit tests that roll back.
Stefan Rufer Netcetera AG Michael Wernli michael.wernli@telekurs.com Telekurs Card Solutions
stefan.rufer@netcetera.ch www.netcetera.ch
www.telekurs.com