Unit Testing Tips and Tricks: Database Interaction

1
Unit Testing Tips and Tricks Database
Interaction

• Louis Thomas

2
Overview

• Preaching About Testing
• What is a Unit Test
• Common Unit Testing Patterns
• Unit Testing Database Interactions
• Acceptance Tests With Databases

3
Are You Test Infected?

• There are two kinds of people
• People who dont like writing tests
• Cant be done attitude
• People who do like writing tests
• I think I can attitude
• Expect a learning curve. Be creative!
• Unit tests are good even if you dont do Test
  Driven Development!

4
There Are Many Kinds Of Tests

• Acceptance tests, user tests, integration tests,
  unit tests black box, white box
• All tests have merit if they can detect bugs.
• Tests only have value if they are run!

5
Unit Tests

• From developer's point of view.
• Tests the smallest amount of a system that is
  interesting.
• Often just one part of one class!
• Highly automated

6
Unit Test Rule Of Thumb

• If you are having trouble writing a unit test
• or (for those of you who aren't test infected)if
  it is "impossible" to write a test for your
  system,
• You are trying to test to much. Test a smaller
  chunk.

7
But How?

• Sometimes objects have complex behaviors,
  extensive state, and tight relationships. This
  makes tests difficult set up is difficult and
  time consuming, and objects cannot be isolated.
• (But wait, thats not right! Right?)

8
Loosening The Coupling

• Introduce interfaces between complex objects.
• Create a mock object to stand in for the complex
  object.
• Repeat as needed. (Be creative.)

9
Creating Interfaces

• If it's our object, just create an interface!
• if it's not our object,
• create a mock that extends the object and
  overrides all its methods (works sometimes)
• create an interface anyway and create an adapter
  for the foreign object

10
Example WallClock
Interface
public interface WallClock long getTime()
Wrapper for normal system service
public class DefaultWallClock implements
WallClock public static final WallClock
INSTANCE new DefaultWallClock() public
long getTime() return
System.currentTimeMillis()
11
Mock Objects

• Start out as simple as possible (throw exceptions
  on all methods).
• Add recording of incoming method calls

12
Example MockClientSession
public class MockClientSession extends
ReportingMockObject implements ClientSession
public void flushOutgoingBuffer()
recordActivity("fOB") public void
setInterval(int nUpdateIntervalMilliseconds)
recordActivity("sI("nUpdateIntervalMilliseco
nds")") public void
notifyNewOutgoingData()
recordActivity("nNOD") public String
getClientName() recordActivity("gCN")
return "mockClient"
13
Example ReportingMockObject
public class ReportingMockObject
StringBuffer m_stringBuffer new
StringBuffer() public String
getActivityRecordAndReset() String
sActivityRecord m_stringBuffer.toString()
m_stringBuffer new StringBuffer()
return sActivityRecord public void
recordActivity(String sMessage)
m_stringBuffer.append(sMessage)
14
Mock Objects, contd

• Add facility for sending back canned responses
• (ex, setNextReply, setFailOnNextRequest)

15
Example MockWallClock
public class MockWallClock implements WallClock
private List m_nextTimesnew
LinkedList() public void addNextTime(long
nNextTime) m_nextTimes.add(new
Long(nNextTime) public void
addNextTimes(long nextTimes)
Require.neqNull(nextTimes, "nextTimes")
for (int nIndex0 nIndexltnextTimes.length
nIndex) addNextTime(nextTimesnInd
ex) public long getTime()
Assert.gtZero(m_nextTimes.size(),
"m_nextTimes.size()") return
((Long)m_nextTimes.remove(0)).longValue()
16
Mock Objects, contd

• Do whatever you need
• Often one mock object will support all tests for
  a given object, but can create special ones for
  certain tests
• Often, one mock object will support tests for
  many objects that interact with it

17
Example MockMultiTableSessionListener
public class MockMultiTableSessionListener
extends ReportingMockObject implements
MultiTableSession.Listener public
interface Thumbprinter String
getThumbprint(MultiTableSession.Update update)
String getThumbprint(SessionState
sessionState) private final
Thumbprinter m_thumbprinter private boolean
m_bLogSessionStateNotification true
public MockMultiTableSessionListener(Thumbprinter
thumbprinter) m_thumbprinter
thumbprinter public void
setLogSessionStateNotification(boolean
bLogSessionStateNotification)
m_bLogSessionStateNotification
bLogSessionStateNotification public
void sessionStateNotification(SessionState
sessionState) if (truem_bLogSessionSta
teNotification) recordActivity("sSN(
"m_thumbprinter.getThumbprint(sessionState)")")

18
Mock Object Frameworks

• EasyMock (http//easymock.org)
• jMock (http//www.jmock.org/)
• YMMV!

19
Object Mother (?)

• Sometimes you will need a complex data structure
  set up. Refactor mercilessly.
• Especially if you need canned data that is
  ancillary to the test, it is often worth while to
  factor creation out into a static method in a
  util class so you can use it as necessary
  thereafter.

20
Testing Accessor

• Problem there are private methods you would like
  to test, or private members you would like to
  inspect for your test
• You could make them public, but they really are
  private
• Alternative an inner class! TestingAccessor

21
Example TestingAccessor
//
// testing private
WallClock m_wallClock DefaultWallClock.INSTANCE
private IStepper m_getConStepper
DefaultStepper.INSTANCE private IStepper
m_maintStepper DefaultStepper.INSTANCE
public class TestingAccessor public
void setWallClock(WallClock wallClock)
m_wallClock wallClock public void
setGetConStepper(IStepper stepper)
m_getConStepper stepper public void
setMaintStepper(IStepper stepper)
m_maintStepper stepper public void
setNextOverdueConnectionCheck(long
tsNextOverdueConnectionCheck)
m_tsNextOverdueConnectionCheck
tsNextOverdueConnectionCheck public
int getAllConnectionsSize() return
m_allConnections.size() public int
getUnusedConnectionsSize() return
m_unusedConnections.size() public int
getTotalConnections() return m_nTotalConnections
public void cacheMaintenaceThread()
DBConnectionPool.this.cacheMaintenaceThread()
public void doNotifyAll()
synchronized (m_oStateLock) m_oStateLock.notifyA
ll() public TestingAccessor
getTestingAccessor() return new
TestingAccessor()
22
Testing Database Interactions

• All the database classes are interfaces already!
  How convenient!
• Create mocks and away you go
• Insert / update / delete straight forward

23
Example writeChangeStepsToDatabase
public static void writeChangeStepsToDatabase(
Statement dbStatement, InstrumentListChangeRequest
instrumentListChangeRequest, int
nParentId) throws SQLException
InstrumentListChangeRequest.ChangeStep
changeSteps instrumentListChangeRequest.getC
hangeStepsSnapshot() StringBuffer sb
new StringBuffer() for (int nIndex 0
nIndexltchangeSteps.length nIndex)
InstrumentListChangeRequest.ChangeStep
changeStep changeStepsnIndex
sb.setLength(0) changeStep.persistTo(
sb) String sSql "INSERT INTO
"TABLE_NAME "
("FIELD_PARENT_ID ",
"FIELD_STEP_NUM ",
"FIELD_STEP_DETAIL ") VALUES"
" ("FORMAT_PARENT_ID.format(nParen
tId) ", "FORMAT_STEP_NUM.format(
nIndex) ", "FORMAT_STEP_DETAIL.f
ormat(sb.toString()) ")"
// send it to the database int
nRows dbStatement.executeUpdate(SqlFormatHelper.
showSql(sSql)) if (0nRows)
throw new SQLException("Failed to write
to database.")
24
Example testWriteChangeStepsToDatabase
public class TestChangeStepSqlHelper extends
BaseTradingSystemTest public void
testWriteChangeStepsToDatabase() throws Exception
CurrencySnapshot instrument new
CurrencySnapshot(new InstrumentSnapshotId("0.FOO")
, new InstrumentLineageId(1), null,
ValidityPeriod.ALL_TIME, new CurrencyIsoSymbol("F"
), "F", "Foo") InstrumentListChangeReque
st instrumentListChangeRequest1 new
InstrumentListChangeRequest()
instrumentListChangeRequest1.addNewInstrument(inst
rument) InstrumentListChangeRequest
instrumentListChangeRequest2 new
InstrumentListChangeRequest()
instrumentListChangeRequest2.addNewInstrument(inst
rument) instrumentListChangeRequest2.addN
ewInstrument(instrument) MockStatement
mockStatement new MockStatement()
SimulatedDatabase.Table changeStepTable new
SimulatedDatabase.Table(ChangeStepSqlHelp
er.ALL_FIELDS) CaxManagementSimulatedData
baseTables.setUpChangeStepTable(mockStatement,
changeStepTable) ChangeStepSqlHelper.wri
teChangeStepsToDatabase(mockStatement,
instrumentListChangeRequest1, 1)
assertEquals(1, changeStepTable.getRowCount())
ChangeStepSqlHelper.writeChangeStepsToDataba
se(mockStatement, instrumentListChangeRequest2,
2) assertEquals(3, changeStepTable.getRow
Count())
25
Example MockStatement
public class MockStatement implements Statement
public interface UpdateHandler
int handleUpdate(String sql) throws
SQLException private Map
m_stringMatcherToUpdateHandlerMapnew TreeMap(new
SystemWebDirectory.StringMatchComparator(
)) public void registerUpdateHandler(String
sql, UpdateHandler updateHandler)
Object key if (sql.endsWith(""))
sqlsql.substring(0, sql.length()-1)
keynew SystemWebDirectory.StringPrefixMatc
her(sql) else keynew
SystemWebDirectory.StringMatcher(sql)
Object prevValuem_stringMatcherToUpdateHan
dlerMap.put(key, updateHandler)
Require.eqNull(prevValue, "prevValue")
public int executeUpdate(String sql) throws
SQLException UpdateHandler
updateHandler(UpdateHandler)m_stringMatcherToUpda
teHandlerMap.get(sql) if
(nullupdateHandler) throw new
SQLException("Unexpected update \""sql"\".")
else return
updateHandler.handleUpdate(sql)
26
Example setUpChangeStepTable
public class CaxManagementSimulatedDatabaseTables
public static void setUpChangeStepTable(Mock
Statement mockStatement, SimulatedDatabase.Table
changeStepTable) //
mockStatement.registerUpdateHandler(
"INSERT INTO "ChangeStepSqlHelper.TABLE_NAME"
(" ChangeStepSqlHelper.FIELD_PARENT_I
D", ", getInsertNewHandler(changeSte
pTable, new String ChangeStepSqlHelper.FIELD_PA
RENT_ID, ChangeStepSqlHelper.FIELD_ST
EP_NUM, ChangeStepSqlHelper.FIELD_STEP_DETAIL))
private static MockStatement.UpdateHandl
er getInsertNewHandler(final SimulatedDatabase.Tab
le table, final String columnNames)
return new MockStatement.UpdateHandler
() public int handleUpdate(String
sql) throws SQLException
SimulatedDatabase.Table.Row row
table.addRow()
SimpleSqlTokenizer simpleSqlTokenizer new
SimpleSqlTokenizer(sql) for (int
nIndex 0 nIndexltcolumnNames.length nIndex)
Object columnValue
simpleSqlTokenizer.getNextParameter()
String sColumnName columnNamesnIndex
row.set(sColumnName,
columnValue)
return 1
27
Testing Database Interactions, Contd

• Read trickier
• Can use hard coded expectations
• Mocks will act as factories statements return
  record sets
• load your mock statement with the mock
  record set to return.
• load your mock connection with the mock
  statement to return.
• Can start out with mocks with hard coded returns,
  but will probably refactor into more general
  objects.

28
Example testReadChangeStepsFromDatabase
public void testReadChangeStepsFromDatabase()
throws Exception MockStatement
mockStatementnew MockStatement()
setUpChangeStepTable(mockStatement)
InstrumentListChangeRequest instrumentListChangeRe
quest InstrumentListChangeRequest.ChangeS
tep changeSteps instrumentListChangeRe
questChangeStepSqlHelper.readChangeStepsFromDatab
ase(mockStatement, 5, 1)
changeStepsinstrumentListChangeRequest.getChangeS
tepsSnapshot() assertEquals(1,
changeSteps.length) assertNull(changeStep
s0.getOldInstrument())
assertNotNull(changeSteps0.getNewInstrument())
assertEquals(new InstrumentSnapshotId("0.C
TO"), changeSteps0.getNewInstrument().getInstrum
entSnapshotId()) instrumentListChangeReq
uestChangeStepSqlHelper.readChangeStepsFromDataba
se(mockStatement, 10, 2)
changeStepsinstrumentListChangeRequest.getChangeS
tepsSnapshot() assertEquals(2,
changeSteps.length) assertNull(changeStep
s0.getOldInstrument())
assertNotNull(changeSteps0.getNewInstrument())
assertEquals(new InstrumentSnapshotId("0.B
"), changeSteps0.getNewInstrument().getInstrumen
tSnapshotId()) assertNotNull(changeSteps
1.getOldInstrument())
assertNotNull(changeSteps1.getNewInstrument())
assertEquals(new InstrumentSnapshotId("0.A
"), changeSteps1.getNewInstrument().getInstrumen
tSnapshotId()) assertEquals("a-old",
((Equity)changeSteps1.getOldInstrument()).getCom
panyName())
29
Example readChangeStepsFromDatabase
public static InstrumentListChangeRequest
readChangeStepsFromDatabase(Statement
dbStatement, int nParentId, int nExpectedSteps)
throws SQLException InstrumentListChange
Request.ChangeStep changeStepsnew
InstrumentListChangeRequest.ChangeStepnExpectedSt
eps int nFoundSteps0 //
process all the rows ResultSet
resultSetdbStatement.executeQuery(SqlFormatHelper
.showSql("SELECT FROM "TABLE_NAME" WHERE
"FIELD_PARENT_ID" "FORMAT_PARENT_ID.format(nP
arentId))) try while
(resultSet.next()) int
nStepNumresultSet.getInt(FIELD_STEP_NUM)
checkForNull(resultSet,
FIELD_STEP_NUM) String
sPersistedChangeStepresultSet.getString(FIELD_STE
P_DETAIL) checkForNull(resultSet,
FIELD_STEP_DETAIL)
InstrumentListChangeRequest.ChangeStep
changeStepnew InstrumentListChangeRequest.ChangeS
tep(new BloombergTokenizer(sPersistedChangeStep))
if (nStepNumlt0
nStepNumgtnExpectedSteps)
throw new SQLException("Found change step
"nStepNum" but expected 0 lt changeStep lt
"nExpectedSteps".") else if
(null!changeStepsnStepNum)
throw new SQLException("Found second change
step "nStepNum".")
changeStepsnStepNumchangeStep
nFoundSteps
finally try
resultSet.close() catch
(SQLException e)
Syslog.warning(ChangeStepSqlHelper.class, "Failed
to close result set.", e)
if (nFoundSteps!nExpectedSteps)
throw new SQLException("Found only
"nFoundSteps" change steps out of
"nExpectedSteps" expected.")
InstrumentListChangeRequest instrumentListChangeRe
questnew InstrumentListChangeRequest()
instrumentListChangeRequest.setChangeSteps(changeS
teps) return instrumentListChangeRequest

30
Example MockStatement
public class MockStatement implements Statement
public interface QueryHandler
ResultSet handleQuery(String sql) throws
SQLException private Map
m_stringMatcherToQueryHandlerMapnew TreeMap(new
SystemWebDirectory.StringMatchComparator(
)) public void registerQueryHandler(String
sql, QueryHandler queryHandler) Object
key if (sql.endsWith(""))
sqlsql.substring(0, sql.length()-1)
keynew SystemWebDirectory.StringPrefixMatcher(sql
) else keynew
SystemWebDirectory.StringMatcher(sql)
Object prevValuem_stringMatcherToQueryHand
lerMap.put(key, queryHandler)
Require.eqNull(prevValue, "prevValue")
public ResultSet executeQuery(String sql) throws
SQLException QueryHandler
queryHandler(QueryHandler)m_stringMatcherToQueryH
andlerMap.get(sql) if (nullqueryHandler
) throw new SQLException("Unexpected
query \""sql"\".") else
return queryHandler.handleQuery(sql)

31
Example setUpChangeStepTable
public class CaxManagementSimulatedDatabaseTables
public static void setUpChangeStepTable(Mock
Statement mockStatement, SimulatedDatabase.Table
changeStepTable) //
mockStatement.registerQueryHandler(
"SELECT FROM "ChangeStepSqlHelper.TABLE_NAME"
WHERE " ChangeStepSqlHelper.FIELD_PAR
ENT_ID" ", getSelectByIdHandler(ch
angeStepTable, new String ChangeStepSqlHelper.F
IELD_PARENT_ID)) private static
MockStatement.QueryHandler getSelectByIdHandler(fi
nal SimulatedDatabase.Table table, final String
columnNames) return new
MockStatement.QueryHandler() public
ResultSet handleQuery(String sql) throws
SQLException // identify the
ids that must match String
idsnew StringcolumnNames.length
SimpleSqlTokenizer simpleSqlTokenizernew
SimpleSqlTokenizer(sql) for (int
nIdIndex0 nIdIndexltids.length nIdIndex)
idsnIdIndexsimpleSqlTokenizer.
getNextParameter()
// create a new table containing all the
matching rows final
SimulatedDatabase.Table resultTablenew
SimulatedDatabase.Table(table.getColumnNames())
for (Iterator itrtable.getRowItera
tor() itr.hasNext())
SimulatedDatabase.Table.Row row(SimulatedDatabase
.Table.Row)itr.next()
boolean bMatchedtrue for
(int nIdIndex0 nIdIndexltids.length nIdIndex)
if (!idMatch(idsnIdInde
x, row.get(columnNamesnIdIndex)))
bMatchedfalse
break
if
(truebMatched)
resultTable.addRow(row.getAll())
return
new BaseMockResultSet()
protected int getTableSize()
return resultTable.getRowCount()
public Object
getObjectInternal(String columnName) throws
SQLException return
resultTable.getRow(m_nIndex).get(columnName)


32
Example BaseMockResultSet
public abstract class BaseMockResultSet
implements ResultSet protected int
m_nIndex-1 private boolean
m_bWasNullfalse protected abstract int
getTableSize() protected abstract Object
getObjectInternal(String columnName) throws
SQLException public boolean next() throws
SQLException m_nIndex return
m_nIndexltgetTableSize() public void
close() throws SQLException // do
nothing public void setWasNull(boolean
bWasNull) m_bWasNullbWasNull
public Object setWasNull(Object object)
m_bWasNullnullobject return object
public boolean wasNull() throws
SQLException return m_bWasNull
public int getInt(String columnName) throws
SQLException Object columnValuesetWasNu
ll(getObjectInternal(columnName)) if
(nullcolumnValue) return -1
else String sValuecolumnValue.
toString() try
return Integer.parseInt(sValue)
catch (NumberFormatException e)
throw new SQLException("Value "sValue" of
column "columnName " can't
be converted to int. "e)
public String getString(String
columnName) throws SQLException Object
columnValuesetWasNull(getObjectInternal(columnNam
e)) if (nullcolumnValue)
return null else return
columnValue.toString()
33
Example SimulatedDatabase.Table
//-----------------------------------------------
----------------- public static class Table
private String m_columnNames
private int m_nColumns private List
m_rowsnew ArrayList() private Map
m_columnNameToColumnIndexMap
//------------------------------------------------
------------ public Table(String
columnNames) Require.neqNull(columnN
ames, "columnNames")
Require.gtZero(columnNames.length,
"columnNames.length")
m_columnNamescolumnNames
m_nColumnsm_columnNames.length
createColumnMap()
//------------------------------------------------
------------ public String
getColumnNames() return
m_columnNames
//------------------------------------------------
------------ private void
createColumnMap()
m_columnNameToColumnIndexMapnew HashMap()
for (int nIndex0 nIndexltm_nColumns
nIndex) String
sColumnNamem_columnNamesnIndex
m_columnNameToColumnIndexMap.put(sColumnName,
new Integer(nIndex))
//-------------------------------------------
----------------- private int
getIndexForName(String sColumnName) throws
SQLException Integer
columnIndex(Integer)m_columnNameToColumnIndexMap.
get(sColumnName) if
(nullcolumnIndex) throw new
SQLException("Unknown column name
\""sColumnName"\".")
return columnIndex.intValue()
//------------------------------------------------
------------ public int getRowCount()
return m_rows.size()
//-----------------------------------------------
------------- public Iterator
getRowIterator() return
m_rows.iterator()
//------------------------------------------------
------------ public Row getRow(int
nRowIndex) Require.geqZero(nRowIndex
, "nRowIndex") Require.lt(nRowIndex,
"nRowIndex", getRowCount(), "getRowCount()")
return (Row)m_rows.get(nRowIndex)
//------------------------------------
---------------------------- public void
addRow(Object objects)
Require.neqNull(objects, "objects")
Require.eq(objects.length, "objects.length",
m_nColumns, "m_nColumns") Row
rownew Row() m_rows.add(row)
for (int nIndex0 nIndexltobjects.length
nIndex) Object
objectobjectsnIndex
row.set(nIndex, object)
//-------------------------------------
----------------------- public Row
addRow() Row rownew Row()
m_rows.add(row) return row
//

//------------------------------------------------
------------ public class Row
private Object m_objects
public Row() m_objectsnew
Objectm_nColumns
public Object get(String sColumnName) throws
SQLException return
m_objectsgetIndexForName(sColumnName)
public void set(String
sColumnName, Object object) throws SQLException
m_objectsgetIndexForName(sColum
nName)object public
Object get(int nColumnIndex)
return m_objectsnColumnIndex
public void set(int nColumnIndex, Object
object) m_objectsnColumnIndex
object public
Object getAll() return
m_objects
34
Acceptance Tests With Databases

• An acceptance test Want to test the "whole" app.
• Good for testing that the database really likes
  the SQL we hard coded in the unit tests, and
  really responds the way we expect.

35
Acceptance Tests With Databases

• Will take longer than a unit test
• May need special environment (acquire exclusive
  access to the testing database)
• Should still use a small interesting subset of
  the the production dataset for speed.
• Should still be as automated as possible.
• Test code will still look similar to unit tests

36
Acceptance Tests With Databases, Contd

• Big question is, how can we automate? I built up
  a toolkit as I went.
• BulkLoadData reads CSV files and loads data into
  DB (use Excel to edit) (214 lines)
• ExecuteSqlScript processes a text file of SQL
  commands. (222 lines)
• Used to create tables, etc.
• ExecuteDatabaseSetupScript allows me to write
  little scripts (205 lines)
• Knows about 4 commands, including BulkLoadData
  and ExecuteSqlScript

37
Example BulkLoadData input file
null_value,null table,inst_definitions begin_data
_with_columns inst_snapshot_id,validity_begin,vali
dity_end,inst_lineage_id,alleged_type,equity_ticke
r_detail,cusip,isin,sedol, country_id,currency
_id,company_name,round_lot_size,registrar_venue_id
,opra_symbol_root, opra_symbol_suffix,underlyi
ng_id,strike_price,expiration_timestamp,parity 1,n
ull,null,1,3,A,a.cusip,null,null,1,1,null,100,5,
null,null,null,null,null,null 2,null,null,2,3,B,
null,null,null,1,1,null,100,4,null,null,null,null,
null,null end_data reset_sequence,inst_snapshot_id
_seq,3,1 reset_sequence,inst_lineage_id_seq,3,1 t
able,inst_definitions_audit begin_data_with_column
s revision,rev_begin,rev_begin_user,rev_begin_comm
ent,rev_end,rev_end_user,rev_end_comment,
inst_snapshot_id,validity_begin,validity_end,inst_
lineage_id,alleged_type,equity_ticker_detail,cusip
, isin,sedol,country_id,currency_id,company_na
me,round_lot_size,registrar_venue_id,
opra_symbol_root,opra_symbol_suffix,underlying_id,
strike_price,expiration_timestamp,parity 0,"to_dat
e('2005-01-01','YYYY-MM-DD')",lt,created
A,null,null,null,1,null,null,1,3,A,
a.cusip,null,null,1,1,null,100,5,null,null,null,nu
ll,null,null 0,"to_date('2005-01-01','YYYY-MM-DD')
",lt,null,null,null,null,2,null,null,2,3,B,
null,null,null,1,1,null,100,4,null,null,null,null,
null,null end_data
38
Example ExecuteSqlScript input file
def boolean number(1) main _at_ignore_errors_at_
drop table table_metadata create table
table_metadata ( table_id number not null,
table_name varchar2(50) not null, is_metatable
boolean not null, is_audited boolean not
null, is_editable boolean not null,
is_deletable boolean not null,
is_pend_changeable boolean not null,
display_name varchar2(100), java_table_handler
varchar2(500) ) tablespace instr_svc_data create
unique index table_metadata_pk on table_metadata
(table_id) tablespace instr_svc_idx alter
table table_metadata add (constraint
table_metadata_pk primary key(table_id)) _at_ignor
e_errors_at_ drop sequence table_id_seq create
sequence table_id_seq start with 1 increment by 1
39
Example ExecuteDatabaseSetupScript files
setupScript-schemaAndAllData.txt
select_db_config DeephavenInstrumentService execu
te_sql_script tearDownEntireSchema-1.0.txt execute
_sql_script createEntireSchema-1.0.txt execute_se
tup_script setupScript-allDataOnly.txt
setupScript-instDataOnly.txt
select_db_config DeephavenInstrumentService delet
e_all_from_table inst_definitions delete_all_from_
table inst_definitions_audit bulk_load_data
data-inst_definitions-1.0.csv
40
Acceptance Tests With Databases, Contd

• I built up a toolkit as I went, contd
• TestingResource framework
• I can define the testing resources that my test
  needs, setup/teardown methods, and dependencies.
• Resources should be able to set themselves up
  from any initial state (ex, delete all rows in
  table and reload)
• Now, the acceptance test can just declare all the
  resources it needs, and framework will set them
  up. Just needs to mark which resources it
  dirties, so they can be reset for subsequent
  tests.

41
TestingResource Framework
ltltInterfacegtgt

TestingResource


TestingResourceManager
getName() markAsDirty() tearDown() setUp() ad
dDependant() addDependancy()
-nameToTestingResourceMap
1
get() put() tearDownAll() markResourceAsDirty(
)
BaseTestingResource
1
-dependents -dependencies -name -isAlwaysSetUp -st
ate
1
doTearDown() doSetUp() assumeAlwaysSetUp()
DatabaseTestingResource
InstrumentServiceSessionTestingResource
DeephavenInstrumentServiceTestingResource
getInstrumentSession3()
getDeephavenInstrumentService()
42
TestingResource Framework
InstrumentServiceSessionTestingResource
Active Session
DeephavenInstrumentServiceTestingResource
Service
DatabaseTestingResource(s)
Fixed Tables
Instrument Tables
Change Management Tables
Schema
public void setUp() for (Iterator
itrm_dependencies.iterator() itr.hasNext())
TestingResource testingResource(Testin
gResource)itr.next()
testingResource.setUp() if
(STATE_SET_UP!m_nState) if
(STATE_TORN_DOWN!m_nState)
Syslog.info(this, "Noticed "getName()" is
dirty!") tearDown()
Syslog.info(this, "Setting up
"getName()".") if
(falsem_bIsAlwaysSetUp)
doSetUp()
m_nStateSTATE_SET_UP public
void tearDown() for (Iterator
itrm_dependents.iterator() itr.hasNext())
TestingResource testingResource(TestingR
esource)itr.next()
testingResource.tearDown() if
(STATE_TORN_DOWN!m_nState)
Syslog.info(this, "Tearing down
"getName()".") if
(falsem_bIsAlwaysSetUp)
doTearDown()
m_nStateSTATE_TORN_DOWN
43
Example testReadTablesAndMetadata
private void setUpInstrumentService()
m_testingResourceManager.get(TEST_RESOURCE_ACTIVE_
SESSION).setUp() m_deephavenInstrumentSer
vice((DeephavenInstrumentServiceTestingResource)m
_testingResourceManager.get(TEST_RESOURCE_SERVICE)
).getDeephavenInstrumentService()
m_instrumentSession3((InstrumentServiceSessionTes
tingResource)m_testingResourceManager.get(TEST_RES
OURCE_ACTIVE_SESSION)).getInstrumentSession3()
private void testReadTablesAndMetadata()
throws Exception Syslog.info(this,
" testReadTablesAndMetadata ")
setUpInstrumentService() // request
metadata for regions table ReplyCatcher
replyCatchernew ReplyCatcher()
m_instrumentSession3.requestLookUp(new
MultiTableSession.Filter new
SingleTableFilterImpl(DisConstants.Tables.METADATA
_OFDisConstants.Tables.REGIONS, new
FilterPredicates.Any()), replyCatcher, null)
replyCatcher.waitForReply()
MultiTableSession.RefreshUpdate
refreshUpdatescheckAndGetUpdatesFromReply(replyCa
tcher) Assert.eq(refreshUpdates.length,
"refreshUpdates.length", 3) Object
regionsMetadataTableDatanew Object
DisConstants.ColumnMetadata.COLUMN_ID,
DisConstants.ColumnMetadata.COLUMN_NAME,
DisConstants.ColumnMetadata.TABLE_ID,
new Integer(200), "region_id", new
Integer(9), new Integer(201),
"region_name", new Integer(9), new
Integer(202), "agg_id", new Integer(9),
checkUpdateHasRows(refreshUpdates,
regionsMetadataTableData) // test
request with output column list // test
AttriubteMatch filter replyCatchernew
ReplyCatcher() AttributeBundle.Key
outputKeysnew AttributeBundle.Key
DisConstants.Countries.THREE_LETTER_CODE,
FilterPredicates.AttributeMatch
filterPredicatenew FilterPredicates.AttributeMatc
h(DisConstants.Countries.REGION_ID, new
Integer(1)) m_instrumentSession3.requestL
ookUp(new MultiTableSession.Filter new
SingleTableFilterImpl(DisConstants.Tables.COUNTRIE
S, filterPredicate, outputKeys), replyCatcher,
null) replyCatcher.waitForReply()
refreshUpdatescheckAndGetUpdatesFromReply(reply
Catcher) Object countriesTableDatane
w Object DisConstants.Countries
.THREE_LETTER_CODE, DisConstants.Countries.REGION_
ID, "USA", null,
"CAN", null,
checkUpdateHasRows(refreshUpdates,
countriesTableData)
44
But Wait, Theres More

• All from developer PoV so far What about users?
• Yes! Help users create tests that make sense to
  them.
• Same rules still apply automated, decoupled,
  simple.
• Testing Stored Procedures and Referential
  Integrity
• Yes! All rules and behaviors are worth testing.
  (I havent done this yet.)
• Same rules still apply automated, decoupled,
  simple.

45
Summary

• Preaching about TDD
• What is a Unit Test
• Common Unit Testing Patterns
• Unit Testing Database Interactions
• Acceptance Tests With Databases
• Questions?