Unit Testing Tips and Tricks: Database Interaction

1
Unit Testing Tips and Tricks Database
Interaction

• Louis Thomas

2
Overview

• Attitudes Toward Testing
• What is a Unit Test
• Common Unit Testing Patterns
• Unit Testing Database Interactions
• Acceptance Tests With Databases

3
Attitudes Toward Testing
4
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!

5
What Is A Unit Test
6
There Are Many Kinds Of Tests

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

7
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.
• Fast.
• No configuration or external dependencies.

8
Common Unit Testing Patterns
9
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.

10
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?)

11
Loosening The Coupling

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

12
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.

13
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()
14
Mock Objects

• A mock object is a stand-in for a complex system
  object.
• Start out as simple as possible (throw exceptions
  on all methods).
• Add recording of incoming method calls.

15
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"
16
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)
17
Mock Objects, contd

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

18
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()
19
Mock Objects, contd

• Add whatever functionality 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.

20
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)")")

21
Mock Object Frameworks

• If you dont want to create mock objects by hand,
  consider a framework that does it by reflection
• EasyMock (http//easymock.org)
• jMock (http//www.jmock.org/)

22
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
  utility class (object mother) so you can use it
  as necessary thereafter.

23
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

24
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()
25
Unit Testing Database Interactions
26
Testing Database Interactions

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

27
Example writeChangeStepsToDatabase
public static void writeChangeStepsToDatabase(
Statement dbStatement,
InstrumentListChangeRequest instrumentListChangeRe
quest, int nParentId) throws SQLException
InstrumentListChangeRequest.ChangeStep
changeSteps instrumentListChangeReque
st.getChangeStepsSnapshot() StringBuffer
stringBuffer new StringBuffer() for
(int nIndex 0 nIndexltchangeSteps.length
nIndex) InstrumentListChangeReques
t.ChangeStep changeStep changeStepsnIndex
stringBuffer.setLength(0)
changeStep.persistTo(stringBuffer)
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(stringBuffer.toString())
")" // send it to the database
int nRows dbStatement.executeUpdate(SqlFo
rmatHelper.showSql(sSql)) if
(0nRows) throw new
SQLException("Failed to write to database.")

28
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())
29
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)
//...
30
Example setUpChangeStepTable
public class CaxManagementSimulatedDatabaseTables
public static void setUpChangeStepTable(Mock
Statement mockStatement,
SimulatedDatabase.Table changeStepTable)
//... mockStatement.registerUpdateHandler(
"INSERT INTO "ChangeStepSqlHelper.TA
BLE_NAME" (" ChangeStepSqlHelper.FIE
LD_PARENT_ID", ",
getInsertNewHandler(changeStepTable, new String
ChangeStepSqlHelper.FIELD_PARENT_ID,
ChangeStepSqlHelper.FIELD_STEP_NUM,
ChangeStepSqlHelper.FIELD_STEP_DETAIL))
private static MockStatement.UpdateHandler
getInsertNewHandler(final SimulatedDatabase.Table
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
31
Testing Database Interactions

• Read must return something trickier.
• 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
  expectations and returns, but will probably
  refactor into more general objects.

32
Example readChangeStepsFromDatabase
public static InstrumentListChangeRequest
readChangeStepsFromDatabase(Statement
dbStatement, int nParentId, int
nExpectedSteps) throws SQLException
InstrumentListChangeRequest.ChangeStep
changeSteps new InstrumentListChangeReq
uest.ChangeStepnExpectedSteps int
nFoundSteps 0 // process all the rows
String sSql "SELECT FROM "TABLE_NAME" WHERE
" FIELD_PARENT_ID" "FORMAT_PARENT_ID.
format(nParentId) ResultSet resultSet
dbStatement.executeQuery(SqlFormatHelper.showSql(s
Sql)) try while (resultSet.next())
int nStepNum resultSet.getInt(FIE
LD_STEP_NUM) checkForNull(resultSet,
FIELD_STEP_NUM) String
sPersistedChangeStep resultSet.getString(FIELD_S
TEP_DETAIL) checkForNull(resultSet,
FIELD_STEP_DETAIL)
InstrumentListChangeRequest.ChangeStep changeStep
new InstrumentListChangeRequest
.ChangeStep(new BloombergTokenizer(sPersistedChang
eStep)) //
33
Example readChangeStepsFromDatabase
// 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".")
changeStepsnStepNum changeStep
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
instrumentListChangeRequest new
InstrumentListChangeRequest()
instrumentListChangeRequest.setChangeSteps(changeS
teps) return instrumentListChangeRequest
34
Example testReadChangeStepsFromDatabase
public void testReadChangeStepsFromDatabase()
throws Exception MockStatement
mockStatement new MockStatement()
setUpChangeStepTable(mockStatement)
InstrumentListChangeRequest instrumentListChangeRe
quest InstrumentListChangeRequest.ChangeStep
changeSteps instrumentListChangeRequest
ChangeStepSqlHelper.readChangeStepsFromDatabase(mo
ckStatement, 5, 1) changeSteps
instrumentListChangeRequest.getChangeStepsSnapshot
() assertEquals(1, changeSteps.length)
assertNull(changeSteps0.getOldInstrument())
assertNotNull(changeSteps0.getNewInstrument())
assertEquals(new InstrumentSnapshotId("0.CTO"
), changeSteps0.getNewInstrument().getInstrument
SnapshotId()) instrumentListChangeRequest
ChangeStepSqlHelper.readChangeStepsFromDatabase(mo
ckStatement, 10, 2) changeSteps
instrumentListChangeRequest.getChangeStepsSnapshot
() assertEquals(2, changeSteps.length)
assertNull(changeSteps0.getOldInstrument())
assertNotNull(changeSteps0.getNewInstrument())
assertEquals(new InstrumentSnapshotId("0.B"),
changeSteps0.getNewInstrument().getInstrumentSn
apshotId()) assertNotNull(changeSteps1.getO
ldInstrument()) assertNotNull(changeSteps1.
getNewInstrument()) assertEquals(new
InstrumentSnapshotId("0.A"), changeSteps1.getNew
Instrument().getInstrumentSnapshotId())
assertEquals("a-old", ((Equity)changeSteps1.getO
ldInstrument()).getCompanyName())
35
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)
//...
36
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.
FIELD_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 ids
new StringcolumnNames.length
SimpleSqlTokenizer simpleSqlTokenizer new
SimpleSqlTokenizer(sql) for (int
nIdIndex 0 nIdIndexltids.length nIdIndex)
idsnIdIndex
simpleSqlTokenizer.getNextParameter()
//...
37
Example setUpChangeStepTable
// create a new table containing
all the matching rows final
SimulatedDatabase.Table resultTable new
SimulatedDatabase.Table(table.getColum
nNames()) for (Iterator itr
table.getRowIterator() itr.hasNext())
SimulatedDatabase.Table.Row row
(SimulatedDatabase.Table.Row)itr.next()
boolean bMatched true
for (int nIdIndex 0 nIdIndexltids.length
nIdIndex) if
(!idMatch(idsnIdIndex, row.get(columnNamesnIdIn
dex))) bMatched
false 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)


38
Example BaseMockResultSet
public abstract class BaseMockResultSet
implements ResultSet protected int m_nIndex
-1 private boolean m_bWasNull false
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_bWasNull bWasNull public Object
setWasNull(Object object) m_bWasNull
nullobject return object
public boolean wasNull() throws SQLException
return m_bWasNull //...
39
Example BaseMockResultSet
//... public String getString(String
columnName) throws SQLException Object
columnValue setWasNull(getObjectInternal(columnN
ame)) if (nullcolumnValue)
return null else
return columnValue.toString()
public int getInt(String columnName) throws
SQLException Object columnValue
setWasNull(getObjectInternal(columnName))
if (nullcolumnValue) return -1
else String sValue
columnValue.toString() try
return Integer.parseInt(sValue)
catch (NumberFormatException e)
throw new SQLException("Value "sValue" of
column "columnName " can't
be converted to int. "e)

No other methods used, so no other methods
implemented.
40
Example SimulatedDatabase.Table
public static class Table private
String m_columnNames private int
m_nColumns private Map
m_columnNameToColumnIndexMap private
List m_rowsnew ArrayList() public
Table(String columnNames)
Require.neqNull(columnNames, "columnNames")
Require.gtZero(columnNames.length,
"columnNames.length")
m_columnNamescolumnNames
m_nColumnsm_columnNames.length
createColumnMap() public
String getColumnNames() return
m_columnNames 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)
41
Example SimulatedDatabase.Table
//... 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 //...
42
Example SimulatedDatabase.Table
//... private void createColumnMap()
m_columnNameToColumnIndexMapnew
HashMap() for (int nIndex0
nIndexltm_nColumns nIndex)
String sColumnNamem_columnNamesnIndex
m_columnNameToColumnIndexMap.put(sColumnNa
me, new Integer(nIndex))
private int getIndexForName(String
sColumnName) throws SQLException
Integer columnIndex(Integer)m_columnNameToColumnI
ndexMap.get(sColumnName) if
(nullcolumnIndex) throw new
SQLException("Unknown column name
\""sColumnName"\".")
return columnIndex.intValue() //...
43
Example SimulatedDatabase.Table.Row
public class Row private
Object m_objects public Row()
m_objects new 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

44
Acceptance Tests With Databases
45
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.

46
Acceptance Tests With Databases, Contd

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

47
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
  database. (Use Excel to edit.) (Just 214 lines.)
• ExecuteSqlScript processes a text file of SQL
  commands. (Just 222 lines.)
• Used to create tables, etc.
• ExecuteDatabaseSetupScript allows me to write
  little scripts (Just 205 lines.)
• Knows about 5 commands, including BulkLoadData
  and ExecuteSqlScript

48
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
49
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
50
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
51
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.

52
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()
53
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
54
Example defineTestResources
private void defineTestResources()
DatabaseTestingResource schemaResource new
DatabaseTestingResource(TEST_RESOURCE_SCHEMA)
m_testingResourceManager.put(TEST_RESOURCE_SCH
EMA, schemaResource) DatabaseTestingResource
fixedTablesResource new
DatabaseTestingResource(TEST_RESOURCE_FIXED_TABLES
) fixedTablesResource.addDependency(schemaRes
ource) m_testingResourceManager.put(TEST_RESO
URCE_FIXED_TABLES, fixedTablesResource)
DatabaseTestingResource instTableResource new
DatabaseTestingResource(TEST_RESOURCE_INST_
TABLE) instTableResource.addDependency(schema
Resource) m_testingResourceManager.put(TEST_R
ESOURCE_INST_TABLE, instTableResource)
//... private void setUpInstrumentService()
m_testingResourceManager.get(TEST_RESOURCE_ACTIV
E_SESSION).setUp() m_deephavenInstrumentServi
ce ((DeephavenInstrumentServiceTestingResource)
m_testingResourceManager.get(TEST_RESOURCE
_SERVICE)).getDeephavenInstrumentService()
m_instrumentSession3 ((InstrumentServiceSessionT
estingResource) m_testingResourceManager.g
et(TEST_RESOURCE_ACTIVE_SESSION)).getInstrumentSes
sion3() private void finished()
m_testingResourceManager.tearDownAll()
55
Example testReadTablesAndMetadata
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) //... //
done m_testingResourceManager.markResourceAsDi
rty(TEST_RESOURCE_ACTIVE_SESSION)
56
In Closing
57
But Wait, Theres More

• All tests from developer point of view 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.

58
Summary

• Attitudes Toward Testing
• What is a Unit Test
• Common Unit Testing Patterns
• Unit Testing Database Interactions
• Acceptance Tests With Databases
• Questions?